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

[Dan MacNeil]

merged trunk

eleminated some branches / duplicate code

better complies with wiki.thecsl.org/mediawiki/index.php/Standards:Code_style_guide

added path config so scripts run from more than ./

added exit 0 so sripts return NO_ERROR if they get to the end without trouble

changed SQL so print and do can use the same queries and what is printed is what is updated to facilitate eyeball sanity check / dry running

BUGS:

I added a lot of over-head calling sub routines and doing individual SQL UPDATE statements. If we were updating millions instead of 10s of records, this would be a big deal. As it is, the sync_* scripts take less than a second to run.

TDOO

You will have to run:

mv_update_development

   to bring the conf file changes into your enviroment.

You might run in the --execute mode and eyeball the datbase before and after , by our design it runs without feedback ( so as not to force cron to send emails when everything works)

Everytime you run:

  mv_update_development

...it refreshes the big postgresql test data.

=== modified file 'app-mvhub/DocumentRoot/static/mvh/html/links.shtml'
--- app-mvhub/DocumentRoot/static/mvh/html/links.shtml	2014-01-05 16:38:45 +0000
+++ app-mvhub/DocumentRoot/static/mvh/html/links.shtml	2015-07-26 16:05:28 +0000
@@ -45,12 +45,7 @@
 		     highest paid employees at an agency. Not directly 
 		     oriented to finding services. 
                     </p>
-	            <p><a href="http://www.catalogueforphilanthropy.org/">
-                        Massachusetts Philanthropic Directory
-	            </a><br>
-                        Primary audience is donors.
-                        3500+ private initiatives oriented toward the public good.
-                    </p>
+
                     <p><a href="http://bostonnavigator.org/">
 			Boston Navigator
 		    </a><br>

=== modified file 'app-mvhub/DocumentRoot/static/mvh/inc/footer.inc'
--- app-mvhub/DocumentRoot/static/mvh/inc/footer.inc	2014-01-09 19:45:44 +0000
+++ app-mvhub/DocumentRoot/static/mvh/inc/footer.inc	2015-07-26 16:05:28 +0000
@@ -10,7 +10,7 @@
 <!-- start footer.inc -->
  <div id='footer'>
   &copy; 2004  <a href="http://advancementplus.org/">Advancement Plus </a> 
-  &copy; 2007-2014
+  &copy; 2007-2015
  <a href="http://thecsl.org">Community Software Lab</a>
  </div>
 <script type="text/javascript">

=== modified file 'app-mvhub/DocumentRoot/static/nsp/html/links.shtml'
--- app-mvhub/DocumentRoot/static/nsp/html/links.shtml	2014-01-05 16:38:45 +0000
+++ app-mvhub/DocumentRoot/static/nsp/html/links.shtml	2015-07-26 16:05:28 +0000
@@ -48,13 +48,6 @@
 		     oriented to finding services. 
                     </p>
 
-	            <p><a href="http://www.catalogueforphilanthropy.org/">
-                        Massachusetts Philanthropic Directory
-	            </a><br>
-			Primary audience is  donors.
-			3500+ private initatives
-			oriented toward the public good. 
-                    </p>
 	            <p><a href="http://www.bostonnavigator.org/">
                         Boston Navigator
 	            </a><br>
@@ -63,12 +56,14 @@
 			school, on week ends and during summer.
                     </p>
 
-                    <p><a href="http://eccf.org/nonprofitdirectory113.html">ECCF
-                    Nonprofit Directory</a>
-                    <br>
-                    Essex Count Community Foundation's Directory of
-                    Non-Profits in Essex County Massachusetts,
-                    including the North Shore.
+                    <p>
+		      <a href="http://eccf.org/npd">
+		        ECCF Nonprofit Directory
+		      </a>
+                      <br>
+                      Essex Count Community Foundation's Directory of
+                      Non-Profits in Essex County Massachusetts,
+                      including the North Shore.
 		   </p>
 
                 </div><!-- id='content' -->

=== modified file 'app-mvhub/DocumentRoot/static/nsp/inc/footer.inc'
--- app-mvhub/DocumentRoot/static/nsp/inc/footer.inc	2014-01-09 19:45:44 +0000
+++ app-mvhub/DocumentRoot/static/nsp/inc/footer.inc	2015-07-26 16:05:28 +0000
@@ -9,7 +9,7 @@
 <!-- start footer.inc -->
  <div id='footer'>
     &copy; 2009-2014 <a href="http://nscap.org/">North Shore Community Action Programs </a>
-    &copy; 2002-2014
+    &copy; 2002-2015
   <a href="http://thecsl.org">Community  Software Lab</a>
   </div>
 <!-- stop footer.inc -->

=== renamed file 'app-mvhub/bin/sync_program_update_dates.pl' => 'app-mvhub/bin/sync_program_update_dates'
--- app-mvhub/bin/sync_program_update_dates.pl	2015-06-14 07:34:17 +0000
+++ app-mvhub/bin/sync_program_update_dates	2015-07-26 16:05:28 +0000
@@ -19,7 +19,7 @@
 
 use MVHub::Utils::DB;
 use MVHub::Utils::SyncProgramUpdateDates
-    qw(sync_program_dates do_sql print_sql);
+    qw( print_sync do_sync sync_program_dates );
 
 {    # main
         # Command Line option handling
@@ -34,11 +34,14 @@
 
     if ($EXECUTE) {
         MVHub::Utils::SyncProgramUpdateDates::sync_program_dates( $dbh,
-            \&do_sql );
+            \&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_sql );
+            \&print_sync );
     }
-
 }
+
+exit 0    # 0 is UNIX for 'no error'

=== renamed file 'app-mvhub/bin/update_dates_and_send_notification_emails.pl' => 'app-mvhub/bin/sync_program_update_dates_and_send_notification_email'
--- app-mvhub/bin/update_dates_and_send_notification_emails.pl	2015-06-14 07:34:17 +0000
+++ app-mvhub/bin/sync_program_update_dates_and_send_notification_email	2015-07-26 16:05:28 +0000
@@ -27,10 +27,13 @@
     '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
-    = ( './sync_program_update_dates.pl', '--config', $CFG_FILENAME );
+    = ( "$BIN_DIR/sync_program_update_dates.pl", '--config', $CFG_FILENAME );
 my @notification_email_args
-    = ( './notification_email.pl', '--config', $CFG_FILENAME );
+    = ( "$BIN_DIR/notification_email.pl", '--config', $CFG_FILENAME );
 
 if ($EXECUTE) {
     push( @update_dates_args,       '--execute' );
@@ -40,3 +43,4 @@
 ( 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	2015-05-03 16:04:46 +0000
+++ app-mvhub/conf/sql_select.lib	2015-07-26 16:05:28 +0000
@@ -826,16 +826,6 @@
 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 
@@ -947,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	2015-05-03 16:04:46 +0000
+++ app-mvhub/conf/sql_update.lib	2015-07-26 16:05:28 +0000
@@ -90,5 +90,4 @@
       last_updated = ? 
   WHERE  agency_id = ? 
   AND contact_email = ? 
-  AND last_updated < ?
-  
\ No newline at end of file
+  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-26 16:05:28 +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	2015-05-17 01:05:47 +0000
+++ app-mvhub/setup/etc/cron.d/mvhub-cron	2015-07-26 16:05:28 +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/update_dates_and_send_notification_emails.pl --config=$CONF_DIR/$MVH_CONF_FILE --execute
-59    8    1,15          *     *           production   $BIN_DIR/update_dates_and_send_notification_emails.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

=== modified file 'lib-mvhub/lib/MVHub.pm'
--- lib-mvhub/lib/MVHub.pm	2014-01-06 02:33:44 +0000
+++ lib-mvhub/lib/MVHub.pm	2015-07-26 16:05:28 +0000
@@ -17,11 +17,11 @@
 
 =head1 VERSION
 
-Version 0.42
+Version 0.43
 
 =cut
 
-our $VERSION = '0.42';
+our $VERSION = '0.43';
 
 =head1 AUTHOR
 

=== modified file 'lib-mvhub/lib/MVHub/Utils/SyncProgramUpdateDates.pm'
--- lib-mvhub/lib/MVHub/Utils/SyncProgramUpdateDates.pm	2015-06-14 07:34:17 +0000
+++ lib-mvhub/lib/MVHub/Utils/SyncProgramUpdateDates.pm	2015-07-26 16:05:28 +0000
@@ -14,6 +14,7 @@
 use Carp;
 use CGI;
 use DBI;
+use Params::Validate;
 
 use SQL::Library;
 
@@ -21,83 +22,39 @@
 
 use base qw( Exporter );
 our @EXPORT = qw(
+    do_sync
+    print_sync
     sync_program_dates
-    do_sql
-    print_sql
 );
 
+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 {
-    my $dbh = shift or croak 'missing param: $dbh';
-    my $sync_dates_sub_ref = shift
-        or croak 'missing param: $sync_dates_sub_ref';
-
-    &$sync_dates_sub_ref($dbh);
-}
-
-sub do_sql {
-    my $dbh = shift or croak 'missing param: $dbh';
+    Params::Validate::validate_pos( @_, 1, 1 );
+    my ( $dbh, $sub_ref ) = @_;
 
     my $sql = MVHub::Utils::DB::get_sql_select_statement(
-        'PROGRAM_X_AGENCY_MAX_LAST_UPDATED');
+        'PROGRAM_X_LAST_UPDATE_MAX_LAST_UPDATE');
 
     my $programs_to_update_aref = $dbh->selectall_arrayref($sql);
-    my $update_count            = 0;
-
-    $sql = MVHub::Utils::DB::get_sql_update_statement('PROGRAM_X_SYNC_DATES');
-
-    foreach my $row (@$programs_to_update_aref) {
-        my ( $agency_id, $contact_email, $max_last_updated ) = @$row;
-        my @bind_variables = (
-            $max_last_updated, $agency_id,
-            $contact_email,    $max_last_updated
-        );
-
-        my $rowcount = $dbh->do( $sql, undef, @bind_variables );
-        if ( $rowcount >= 1 ) {
-            $update_count += $rowcount;
-        }
-    }
-    print "no. of programs updated: $update_count\n";
-}
-
-sub print_sql {
-    my $dbh = shift or croak 'missing param: $dbh';
-
-    my $sql = MVHub::Utils::DB::get_sql_select_statement(
-        'PROGRAM_X_AGENCY_MAX_LAST_UPDATED');
-
-    my $agencies_to_print_aref = $dbh->selectall_arrayref($sql);
-    print
-        "Agency id, Program id, Contact email, Last updated, Max last updated, Needs Update?\n";
-
-   #$sql = MVHub::Utils::DB::get_sql_update_statement('PROGRAM_X_SYNC_DATES');
-    $sql
-        = "SELECT program_id, contact_email, last_updated FROM program WHERE agency_id = ? AND contact_email = ?";
-
-    my $no_of_programs_to_update = 0;
-    foreach my $a_row (@$agencies_to_print_aref) {
-        my ( $agency_id, $contact_email, $max_last_updated ) = @$a_row;
-
-        my @bind_variables = ( $agency_id, $contact_email );
-
-        my $programs_to_print_aref
-            = $dbh->selectall_arrayref( $sql, undef, @bind_variables );
-
-        foreach my $p_row (@$programs_to_print_aref) {
-            my ( $program_id, $contact_email, $last_updated ) = @$p_row;
-            my $needs_update;
-            if ( $last_updated lt $max_last_updated ) {
-                $needs_update = "Yes";
-                $no_of_programs_to_update++;
-            }
-            else { $needs_update = "No"; }
-
-            print
-                "$agency_id, $program_id, $contact_email, $last_updated, $max_last_updated, $needs_update\n";
-
-        }
-    }
-    print "programs need to be updated: $no_of_programs_to_update\n";
+
+    foreach my $program_rec_aref (@$programs_to_update_aref) {
+        &$sub_ref( $dbh, $program_rec_aref );
+    }
 }
 
 1;

=== modified file 'lib-mvhub/t/Utils/SyncProgramUpdateDates/sync_program_dates.t' (properties changed: -x to +x)
--- lib-mvhub/t/Utils/SyncProgramUpdateDates/sync_program_dates.t	2015-06-14 07:34:17 +0000
+++ lib-mvhub/t/Utils/SyncProgramUpdateDates/sync_program_dates.t	2015-07-26 16:05:28 +0000
@@ -13,12 +13,12 @@
 use Carp;
 use DBI;
 
-use Test::More tests => 4 + 1;    # +1 for NoWarnings
+use Test::More tests => 2 + 1;    # +1 for NoWarnings
 use Test::NoWarnings;
 use Test::Exception;
 
 use MVHub::Utils::SyncProgramUpdateDates
-    qw/ sync_program_dates print_sql do_sql /;
+    qw/ sync_program_dates  do_sync print_sync /;
 use MVHub::Utils::DB qw / get_dbh /;
 use MVHub::Wrap::ConfigSimple;
 
@@ -28,35 +28,30 @@
 my $config_file = $ARGV[0] || $ENV{MV_TEST_CONFIG_FILE};
 my $cfg = MVHub::Wrap::ConfigSimple->new($config_file);
 
-my $test_message;
-
-###
-$test_message = 'die with missing parameters';
-###
-dies_ok { sync_program_dates() } $test_message;
-
 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_sql ) } $test_message;
-
-###
-$test_message = 'updates program dates ok';
-###
-lives_ok { sync_program_dates( $dbh, \&do_sql ) } $test_message;
+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_sql );
+
+sync_program_dates( $dbh, \&do_sync );
 is_deeply(
-    $dbh->selectcol_arrayref("SELECT last_updated FROM program"),
-    [ '2009-02-15', '2008-12-02', '2008-12-02', '2008-12-12', '2008-12-12' ],
+    $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
 );
 

=== 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-26 16:05:28 +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	2015-05-14 23:56:55 +0000
+++ lib-mvhub/t/lib/TestData.pm	2015-07-26 16:05:28 +0000
@@ -147,6 +147,7 @@
         last_updated       => '2008-12-12',
         reminders_sent     => '2',
     },
+
 );
 
 our $notifications_href = {