have you installed OpenCATS? Proud of your customizations and want to share it? Post here and wait for the praise...

Moderators: RussH, cptr13

Forum rules: Just please remember to play nicely once you walk through the door. You can disagree with us, or any other commenters in this forum, but keep your comments directed to the topic at hand - not at the commenter.
#4842
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

It's essential to keep these synchronized to ensur[…]

Export part in Job Order order search

Same problem as mine. Maybe you're right, I'll che[…]

The error message you're encountering indicates th[…]

Errors restoring mysqldump backup

doesnot work for me