Merge lp:~rpulaparti/mvhub/cron_script_to_set_program_max_last_update_1277300 into lp:mvhub

[RPulaparti]

Added a new Perl script to app-mvhub/bin directory with the set_program_max_last_update.pl to update the last
updated date to the latest record update date for a given agency.please refer to 1277300 for details.

[lp:~rpulaparti/mvhub/cron_script_to_set_program_max_last_update_1277300]

665. By Renuka Pulaparti <email address hidden> on 2015-04-07

666. By Renuka Pulaparti <email address hidden> on 2015-04-08

modified to do all updates with a single update statement

667. By Renuka Pulaparti <email address hidden> on 2015-04-28

added new files

668. By Renuka Pulaparti <email address hidden> on 2015-05-03

removed an old script

669. By Renuka Pulaparti <email address hidden> on 2015-05-03

refactored code

670. By Renuka Pulaparti <email address hidden> on 2015-05-03

added new cron script to the crontab

[lp:~rpulaparti/mvhub/cron_script_to_set_program_max_last_update_1277300]

671. By Renuka Pulaparti <email address hidden> on 2015-05-10

updated the sync dates files

672. By Renuka Pulaparti <email address hidden> on 2015-05-10

removed comments

[RPulaparti (rpulaparti)]

I removed the comments and made all the changes we discussed. I didn't
understand, what you meant by

"Probably more efficient to put line 130 above line 126 (out side the loop)
probably not necesary to get it each time through the loop"

My logic was for every row of the select statement results do an update
 for the given agency.

On Sat, May 9, 2015 at 4:38 PM, Dan MacNeil <email address hidden> wrote:

>
>
> Diff comments:
>
> > === added file 'app-mvhub/bin/sync_program_update_dates.pl'
> > --- app-mvhub/bin/sync_program_update_dates.pl 1970-01-01
> 00:00:00 +0000
> > +++ app-mvhub/bin/sync_program_update_dates.pl 2015-05-03
> 16:14:55 +0000
> > @@ -0,0 +1,21 @@
> > +#!/usr/bin/perl
> > +
> > +# LICENSE: GNU Affero General Public License v3
> > +# COPYRIGHT: Community Software Lab
> > +# CONTACT: <email address hidden>
> > +# PROJECT: https://launchpad.net/mvhub/
> > +
> > +# PURPOSE: cron script for setting all programs last_update to the max
> last update for a given agency.
> > +
> > +use strict;
> > +use warnings;
> > +
> > +use CGI;
> > +use DBI;
> > +use SQL::Library;
> > +use MVHub::Utils::DB;
> > +use MVHub::Utils::SyncProgramUpdateDates;
> > +
> > +{
> > + MVHub::Utils::SyncProgramUpdateDates::sync_program_dates();
> > +}
> >
> > === modified file 'app-mvhub/conf/sql_select.lib'
> > --- app-mvhub/conf/sql_select.lib 2013-01-02 16:42:41 +0000
> > +++ app-mvhub/conf/sql_select.lib 2015-05-03 16:14:55 +0000
> > @@ -826,6 +826,16 @@
> > WHERE agency_id = ? AND hidden_reason IS NULL
> > ORDER BY program_name
> >
> > +[PROGRAM_X_AGENCY_MAX_LAST_UPDATED]
> > +SELECT
> > + agency_id, contact_email, MAX(last_updated)
> > +FROM
> > + program
> > +GROUP BY
> > + agency_id,contact_email
> > +ORDER BY
> > + agency_id,contact_email
> > +
> > [PROGRAM_X_AGENCY_PROGRAM_ID]
> > SELECT *
> > FROM program
> >
> > === modified file 'app-mvhub/conf/sql_update.lib'
> > --- app-mvhub/conf/sql_update.lib 2013-01-02 16:42:41 +0000
> > +++ app-mvhub/conf/sql_update.lib 2015-05-03 16:14:55 +0000
> > @@ -82,3 +82,13 @@
> > WHERE
> > agency_id = ?
> > AND last_updated < ?
> > +
> > +[PROGRAM_X_SYNC_DATES]
> > + UPDATE
> > + program
> > + SET
> > + last_updated = ?
> > + WHERE agency_id = ?
> > + AND contact_email = ?
> > + AND last_updated < ?
> > +
> > \ No newline at end of file
> >
> > === modified file 'app-mvhub/setup/etc/cron.d/mvhub-cron'
> > --- app-mvhub/setup/etc/cron.d/mvhub-cron 2013-01-06 00:18:39 +0000
> > +++ app-mvhub/setup/etc/cron.d/mvhub-cron 2015-05-03 16:14:55 +0000
> > @@ -34,6 +34,8 @@
> > 28 1 * * * production $BIN_DIR/
> dump.pl --config=$CONF_DIR/$NSP_CONF_FILE
> > 30 2 * * * production $BIN_DIR/
> sitemap_generate.pl --config=$CONF_DIR/$MVH_CONF_FILE
> > 35 2 * * * production $BIN_DIR/
> sitemap_generate.pl --config=$CONF_DIR/$NSP_CONF_FILE
> > +00 3 * * * production $BIN_DIR/
> sync_program_update_dates.pl --config=$CONF_DIR/$MVH_CONF_FILE
> > +05 3 * * * production ...

[lp:~rpulaparti/mvhub/cron_script_to_set_program_max_last_update_1277300]

673. By Renuka Pulaparti <email address hidden> on 2015-05-10

broke down a single subroutine into three for easy testing

674. By Renuka Pulaparti <email address hidden> on 2015-05-12

moved the update statement out of the loop

675. By Renuka Pulaparti <email address hidden> on 2015-05-14

modified TestData

676. By Renuka Pulaparti <email address hidden> on 2015-05-14

Added new files

677. By Renuka Pulaparti <email address hidden> on 2015-05-15

ran perl tidy

678. By Renuka Pulaparti <email address hidden> on 2015-05-15

code refactoring

679. By Renuka Pulaparti <email address hidden> on 2015-05-15

minor change

680. By Renuka Pulaparti <email address hidden> on 2015-05-16

made the file executable

681. By Renuka Pulaparti <email address hidden> on 2015-05-17

added new script

682. By Renuka Pulaparti <email address hidden> on 2015-05-17

added parameters to the script

683. By Renuka Pulaparti <email address hidden> on 2015-06-13

code update

684. By Renuka Pulaparti <email address hidden> on 2015-06-13

perl tidy

685. By Renuka Pulaparti <email address hidden> on 2015-06-14

updated the cron script to call other scripts

686. By Renuka Pulaparti <email address hidden> on 2015-07-28

merged branch from Dan

=== added file 'app-mvhub/bin/sync_program_update_dates'
--- app-mvhub/bin/sync_program_update_dates	1970-01-01 00:00:00 +0000
+++ app-mvhub/bin/sync_program_update_dates	2015-07-28 01:03:21 +0000
@@ -0,0 +1,47 @@
+#!/usr/bin/perl
+
+# LICENSE: GNU Affero General Public License v3
+# COPYRIGHT: Community Software Lab
+# CONTACT: [e-mail]
+# PROJECT: https://launchpad.net/mvhub/
+
+# PURPOSE: cron script for setting all programs last_update to the max last update for a given agency  so the contact
+# doesn't have to do updates more  than once every 6 months.
+
+use strict;
+use warnings;
+
+use Carp;
+use CGI;
+use DBI;
+use Getopt::Long;
+use SQL::Library;
+
+use MVHub::Utils::DB;
+use MVHub::Utils::SyncProgramUpdateDates
+    qw( print_sync do_sync sync_program_dates );
+
+{    # main
+        # Command Line option handling
+    my $CFG_FILENAME = $ENV{MV_CONFIG_FILE};
+    my $EXECUTE      = 0;
+    Getopt::Long::GetOptions(
+        'config=s' => \$CFG_FILENAME,
+        'execute'  => \$EXECUTE
+    ) or die "GetOptions failed";
+
+    my $dbh = MVHub::Utils::DB::get_dbh($CFG_FILENAME);
+
+    if ($EXECUTE) {
+        MVHub::Utils::SyncProgramUpdateDates::sync_program_dates( $dbh,
+            \&do_sync );
+    }
+    else {
+        printf "%-15s %-9s %-41s %-15s \n",
+            qw/max_update_date agency_id contact_email update_date /;
+        MVHub::Utils::SyncProgramUpdateDates::sync_program_dates( $dbh,
+            \&print_sync );
+    }
+}
+
+exit 0    # 0 is UNIX for 'no error'

=== added file 'app-mvhub/bin/sync_program_update_dates_and_send_notification_email'
--- app-mvhub/bin/sync_program_update_dates_and_send_notification_email	1970-01-01 00:00:00 +0000
+++ app-mvhub/bin/sync_program_update_dates_and_send_notification_email	2015-07-28 01:03:21 +0000
@@ -0,0 +1,46 @@
+#!/usr/bin/perl 
+
+# LICENSE: GNU Affero General Public License v3
+# COPYRIGHT: Community Software Lab
+# CONTACT: [e-mail]
+# PROJECT: https://launchpad.net/mvhub/
+
+# PURPOSE: Every two weeks, we send a notification email to the users who need to update their info.Before we send a
+# notification, we need to sync the last updated dates for all programs for a given agency, so they don't receive too
+# many emails to update each program separately.
+
+use strict;
+use warnings;
+
+use Getopt::Long;
+
+use MVHub::Common;
+use MVHub::Utils;
+use MVHub::Wrap::ConfigSimple;
+use MVHub::Utils::DB;
+
+# Command Line option handling
+my $CFG_FILENAME = $ENV{MV_CONFIG_FILE};
+my $EXECUTE      = 0;
+Getopt::Long::GetOptions(
+    'config=s' => \$CFG_FILENAME,
+    'execute'  => \$EXECUTE
+) or die "GetOptions failed";
+
+my $CFG     = MVHub::Wrap::ConfigSimple->new($CFG_FILENAME);
+my $BIN_DIR = $CFG->param('ABSOLUTE_PATH.bin_dir');
+
+my @update_dates_args
+    = ( "$BIN_DIR/sync_program_update_dates.pl", '--config', $CFG_FILENAME );
+my @notification_email_args
+    = ( "$BIN_DIR/notification_email.pl", '--config', $CFG_FILENAME );
+
+if ($EXECUTE) {
+    push( @update_dates_args,       '--execute' );
+    push( @notification_email_args, '--execute' );
+}
+
+( system(@update_dates_args) == 0 ) or die "Sync program dates failed.\n";
+( system(@notification_email_args) == 0 ) or die "Notification emails failed";
+
+exit 0    # zero is unix for 'no error'

=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib	2013-01-02 16:42:41 +0000
+++ app-mvhub/conf/sql_select.lib	2015-07-28 01:03:21 +0000
@@ -937,6 +937,26 @@
   program 
 WHERE program_id = ?
 
+[PROGRAM_X_LAST_UPDATE_MAX_LAST_UPDATE]
+ SELECT
+        pm.max_last_updated, p.agency_id,LOWER(p.contact_email) AS contact_email,p.last_updated AS update_need_sync
+ FROM 
+	program p
+	INNER JOIN 
+		( 
+		  SELECT
+			  agency_id, LOWER(contact_email) AS contact_email, MAX(last_updated) AS max_last_updated
+	  	FROM
+  			program p
+	  	GROUP BY
+  			agency_id,contact_email
+	 	ORDER BY
+  			agency_id,contact_email
+  	) pm ON 
+			p.agency_id 		= pm.agency_id 
+		AND	LOWER(p.contact_email)	= pm.contact_email
+		AND 	p.last_updated		< pm.max_last_updated
+
 [PROGRAM_X_NAMES_THAT_REPEAT_AGENCY_NAME]
 SELECT program_name, agency_name FROM program p, agency a 
 WHERE p.agency_id = a.agency_id 

=== modified file 'app-mvhub/conf/sql_update.lib'
--- app-mvhub/conf/sql_update.lib	2013-01-02 16:42:41 +0000
+++ app-mvhub/conf/sql_update.lib	2015-07-28 01:03:21 +0000
@@ -82,3 +82,12 @@
    WHERE 
       agency_id = ?
       AND last_updated < ?
+      
+[PROGRAM_X_SYNC_DATES]
+  UPDATE 
+      program 
+  SET 
+      last_updated = ? 
+  WHERE  agency_id = ? 
+  AND contact_email = ? 
+  AND last_updated  = ?  

=== modified file 'app-mvhub/project-tools/templates/template.conf'
--- app-mvhub/project-tools/templates/template.conf	2012-12-29 16:37:34 +0000
+++ app-mvhub/project-tools/templates/template.conf	2015-07-28 01:03:21 +0000
@@ -118,6 +118,7 @@
 
 [RELATIVE_PATH]
 conf_dir=link-to-live-code/app-mvhub/conf/
+bin_dir=link-to-live-code/app-mvhub/bin/
 document_root=link-to-live-code/app-mvhub/DocumentRoot/
 template_conf_dir=link-to-live-code/app-mvhub/project-tools/templates/
 template_html_dir=link-to-live-code/app-mvhub/conf/templates/html/

=== modified file 'app-mvhub/setup/etc/cron.d/mvhub-cron'
--- app-mvhub/setup/etc/cron.d/mvhub-cron	2013-01-06 00:18:39 +0000
+++ app-mvhub/setup/etc/cron.d/mvhub-cron	2015-07-28 01:03:21 +0000
@@ -36,11 +36,11 @@
 35        2    *             *     *           production   $BIN_DIR/sitemap_generate.pl --config=$CONF_DIR/$NSP_CONF_FILE
 
 # restarting apache because we rotate/resolved logs and it is
-# bad karma to keep writing to renamed file
+# bad karma to keep writing to re-named file
 30    0    *             *     *           root	  /usr/sbin/apache2ctl graceful
 
 45    0    *             *     *           root	 $BIN_DIR/rm_session_files $CONF_DIR/$MVH_CONF_FILE
 46    0    *             *     *           root	 $BIN_DIR/rm_session_files $CONF_DIR/$NSP_CONF_FILE
 
-51    8    1,15          *     *           production   $BIN_DIR/notification_email.pl --config=$CONF_DIR/$MVH_CONF_FILE --execute
-59    8    1,15          *     *           production   $BIN_DIR/notification_email.pl --config=$CONF_DIR/$NSP_CONF_FILE --execute
+51    8    1,15          *     *           production   $BIN_DIR/sync_program_update_dates_and_send_notification_email --config=$CONF_DIR/$MVH_CONF_FILE --execute
+59    8    1,15          *     *           production   $BIN_DIR/sync_program_update_dates_and_send_notification_email --config=$CONF_DIR/$NSP_CONF_FILE --execute

=== added file 'lib-mvhub/lib/MVHub/Utils/SyncProgramUpdateDates.pm'
--- lib-mvhub/lib/MVHub/Utils/SyncProgramUpdateDates.pm	1970-01-01 00:00:00 +0000
+++ lib-mvhub/lib/MVHub/Utils/SyncProgramUpdateDates.pm	2015-07-28 01:03:21 +0000
@@ -0,0 +1,60 @@
+
+# LICENSE: GNU Affero General Public License v3
+# COPYRIGHT: Community Software Lab
+# CONTACT: [e-mail]
+# PROJECT: https://launchpad.net/mvhub/
+
+# PURPOSE: sub to sync all the last updated dates of all the program to the latest updated date for a given agency so###  the contact doesn't have to do updates more than once every 6 months.
+
+package MVHub::Utils::SyncProgramUpdateDates;
+
+use strict;
+use warnings;
+
+use Carp;
+use CGI;
+use DBI;
+use Params::Validate;
+
+use SQL::Library;
+
+use MVHub::Utils::DB;
+
+use base qw( Exporter );
+our @EXPORT = qw(
+    do_sync
+    print_sync
+    sync_program_dates
+);
+
+sub do_sync {
+    Params::Validate::validate_pos( @_, 1, 1 );
+    my ( $dbh, $program_row_aref ) = @_;
+
+    my $sql
+        = MVHub::Utils::DB::get_sql_update_statement('PROGRAM_X_SYNC_DATES');
+    $dbh->do( $sql, undef, @$program_row_aref );
+}
+
+sub print_sync {
+    Params::Validate::validate_pos( @_, 1, 1 );
+    my ( $dbh, $program_row_aref ) = @_;
+
+    printf "%-15s %-9s %-41s %-15s\n", @$program_row_aref;
+}
+
+sub sync_program_dates {
+    Params::Validate::validate_pos( @_, 1, 1 );
+    my ( $dbh, $sub_ref ) = @_;
+
+    my $sql = MVHub::Utils::DB::get_sql_select_statement(
+        'PROGRAM_X_LAST_UPDATE_MAX_LAST_UPDATE');
+
+    my $programs_to_update_aref = $dbh->selectall_arrayref($sql);
+
+    foreach my $program_rec_aref (@$programs_to_update_aref) {
+        &$sub_ref( $dbh, $program_rec_aref );
+    }
+}
+
+1;

=== added directory 'lib-mvhub/t/Utils/SyncProgramUpdateDates'
=== added file 'lib-mvhub/t/Utils/SyncProgramUpdateDates/sync_program_dates.t'
--- lib-mvhub/t/Utils/SyncProgramUpdateDates/sync_program_dates.t	1970-01-01 00:00:00 +0000
+++ lib-mvhub/t/Utils/SyncProgramUpdateDates/sync_program_dates.t	2015-07-28 01:03:21 +0000
@@ -0,0 +1,58 @@
+#!/usr/bin/perl
+
+# LICENSE: GNU Affero General Public License v3
+# COPYRIGHT: Community Software Lab
+# CONTACT: [e-mail]
+# PROJECT: https://launchpad.net/mvhub/
+
+# PURPOSE: test sub that syncs the programs dates to the last updated date.
+
+use strict;
+use warnings;
+
+use Carp;
+use DBI;
+
+use Test::More tests => 2 + 1;    # +1 for NoWarnings
+use Test::NoWarnings;
+use Test::Exception;
+
+use MVHub::Utils::SyncProgramUpdateDates
+    qw/ sync_program_dates  do_sync print_sync /;
+use MVHub::Utils::DB qw / get_dbh /;
+use MVHub::Wrap::ConfigSimple;
+
+use TestHelper qw/ add_test_data create_sqlite_db /;
+use TestData qw/ @program /;
+
+my $config_file = $ARGV[0] || $ENV{MV_TEST_CONFIG_FILE};
+my $cfg = MVHub::Wrap::ConfigSimple->new($config_file);
+
+my $dbh = MVHub::Utils::DB::get_dbh($config_file);
+TestHelper::create_sqlite_db($dbh) or croak "failed to setup test database\n";
+TestHelper::add_test_data($dbh);
+
+my $test_message;
+
+###
+$test_message = 'prints program dates to be updated ok';
+###
+lives_ok { sync_program_dates( $dbh, \&print_sync ) } $test_message;
+
+###
+$test_message = 'Program Test Data equals expected result after update';
+###
+
+sync_program_dates( $dbh, \&do_sync );
+is_deeply(
+    $dbh->selectall_arrayref("SELECT agency_id,last_updated FROM program"),
+    [   [ 1001, '2009-02-15' ],
+        [ 1002, '2008-12-02' ],
+        [ 1003, '2008-12-02' ],
+        [ 1004, '2008-12-12' ],
+        [ 1004, '2008-12-12' ],
+    ],
+    $test_message
+);
+
+unlink( $cfg->param('DATABASE.database_name') );

=== modified file 'lib-mvhub/t/conf/all.conf'
--- lib-mvhub/t/conf/all.conf	2012-12-24 17:16:42 +0000
+++ lib-mvhub/t/conf/all.conf	2015-07-28 01:03:21 +0000
@@ -92,6 +92,7 @@
 dir=/var/www/mvhub/
 
 [RELATIVE_PATH]
+bin_dir=link-to-live-code/app-mvhub/bin/
 conf_dir=link-to-live-code/app-mvhub/conf/
 document_root=link-to-live-code/app-mvhub/DocumentRoot/
 template_conf_dir=link-to-live-code/app-mvhub/project-tools/templates/

=== modified file 'lib-mvhub/t/lib/TestData.pm'
--- lib-mvhub/t/lib/TestData.pm	2013-02-28 03:48:09 +0000
+++ lib-mvhub/t/lib/TestData.pm	2015-07-28 01:03:21 +0000
@@ -131,8 +131,8 @@
         record_name        => 'Test Program 4',
         contact_first_name => 'Sue',
         contact_last_name  => 'Blue',
-        contact_email      => '[e-mail]',
-        email              => '[e-mail]',
+        contact_email      => '[e-mail]',
+        email              => '[e-mail]',
         last_updated       => '2008-12-02',
         reminders_sent     => '3',
     },
@@ -144,9 +144,10 @@
         contact_last_name  => 'Blue',
         contact_email      => '[e-mail]',
         email              => '[e-mail]',
-        last_updated       => '2008-12-02',
+        last_updated       => '2008-12-12',
         reminders_sent     => '2',
     },
+
 );
 
 our $notifications_href = {