cron script to setprogram.last_update to max(program.last_update) for group of contacts

[RPulaparti]

every 2 weeks , we send an email to everyone for every program that is more than 6 months since it was lasted updated
if somebody has multiple programs, and they don't go out of date on the same date, they get multiple emails
so the "Dog & Cat adoption agency" has 2 programs:
Dog adoption
Cat adoption

and one contact person "Bob" , responsible for keeping up to date.

Bob last updated or created Dog adoption on 01/01/2014 and cat adoption on 01/16/2014

2014-06-01 he gets an email for the Dog adoption
2014-07-01 he gets one for the Cat adoption
In July he hits reply saying " I just updated you are sending me too many emails"

so the DRAFT DRAFT DRAFT plan is to have a nightly job that sets the last update date for each program that a person has to the same as the newest record. so tonight the dog and cat adoption programs both get set to a 'last update' of 01/16/2014

Files to look at are:
./lib-mvhub/lib/MVHub/Notifications.pm
./app-mvhub/setup/etc/cron.d/mvhub-cron
app-mvhub/bin/*

more files to look at:
app-mvhub/conf/sql_*

It might be possible to do one big SQL statement and get everything in
but breaking things into readable steps will be easier for future generations
psql is your friend
psql -U omacneil omacneil.mvh
replace omacneil with your username
to get access to the mvhub test database

rpulaprti.nsp is your copy of the northshore port test data

mv_update_development

will reset your test database to the original date
inside psql
\d lists the tables
\d <tablename> lists the columns

SELECT
agency_id, contact_email, MAX(last_updated)
FROM
program
GROUP BY
agency_id,contact_email
ORDER BY
agency_id,contact_email

and then I'd be tempted to use perl / dbi / placeholders to supply parameters to an UPDATE statement
UPDATE program SET last_update = ? WHERE program_id=? and contact_email=?

man SQL::Library

it is probably simper than it looks
pretty much get the first results from query above, loop through them
feed them 1 at a time to an update statement
there are probably existing scripts that do something similar
probably Notifications.pm
grep is your friend

[RPulaparti (rpulaparti)]

This bug is a result of the discussion between me and Dan