[RussH]

I have a few scripts around opencats to generate reports and automate stuff. This one checks Company contacts and generates a list of all contacts that have not been contacted for at least six months. This gives recruiters a list of aged contacts they can go through.

The script runs the SQL query, and then emails the output as an excel attachment. You'll need to replace your databasepassword (of course) and the email recipient. You'll also need to install the utility to convert CSV to XLSX (link in the script comments)

Code: Select all

#!/bin/bash
# Proper header for a Bash script.

# Send summary of contacts with no updates for six months.
mysql -u opencatsdb -p opencats --password=yoursecretpasswordhere <<QUERY_INPUT

#sql commands go here
USE opencats;
DROP TEMPORARY TABLE IF EXISTS temptable;
DROP TEMPORARY TABLE IF EXISTS temptable2;

CREATE TEMPORARY TABLE temptable
        select contact.contact_id as contact_id,contact.first_name, contact.last_name, company.name as company, activity.date_modified as date, activity.activity_id as activity_id, activity.notes as notes, contact.phone_work as phone_work, contact.phone_cell as phone_cell
        from (contact inner join activity on contact.contact_id=activity.data_item_id INNER JOIN company ON contact.company_id = company.company_id);

# temptable2 only needed if MariaDB <=10.2, This issue was fixed in 10.2.5, otherwise if you have MariaDB v10.3 just reuse temptable in the inner join on line 19
CREATE TEMPORARY TABLE IF NOT EXISTS temptable2 AS (SELECT * FROM temptable);

select CONCAT(f.first_name," ",f.last_name) as Name, f.company, f.date, LEFT(f.notes, 30)  from (select contact_id, max(activity_id) as maxactivity from temptable group by contact_id) x inner join temptable2 as f on f.contact_id = x.contact_id and f.activity_id = x.maxactivity and f.date  < DATE_ADD(NOW(), INTERVAL -6 MONTH)

ORDER BY
                f.date DESC
INTO OUTFILE '/tmp/check-aged-contacts.csv'
                FIELDS TERMINATED BY '|'
                ENCLOSED BY '"'
                LINES TERMINATED BY '\n';

QUERY_INPUT
#
# convert the CSV output to XLSX - https://gitlab.com/DerLinkshaender/csv2xlsx/-/tree/master

csv2xlsx -infile "/tmp/check-aged-contacts.csv" -outfile "/tmp/check-aged-contacts.xlsx"

# Email the results to recipients
echo "FOR YOUR IMMEDIATE REVIEW." | mail -s "ACTION REQUIRED:contacts with no updates for six months." -a /tmp/check-aged-contacts.xlsx your-email-address.com


# clean-up the tmp file or MYSQL won't over-write
rm /tmp/check-aged-contacts.csv
rm /tmp/check-aged-contacts.xlsx

exit

[RussH]

Clearly, this could just as easily be used to generate a view for a list of 'Cold Contacts' in the contacts view, but my requirement was to have it emailed.