| 2015-04-01 17:50:20 |
RPulaparti |
description |
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 program_id,contact_email,max(last_updated) from program group by program_id,contact_email order by 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 |
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 |
|
