From f5913c192c8f48c67e4e8d3b735b17c3e1629bdd Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Wed, 8 May 2013 09:32:39 -0400 Subject: [PATCH] Initial import of Conifer daily scripts Signed-off-by: Dan Scott --- tools/daily-scripts/OSUL-patrons-lang-prefs.sql | 10 ++ tools/daily-scripts/circ_date_to_expire_date.pl | 76 +++++++++++++++ tools/daily-scripts/delete_ill_books.pl | 99 ++++++++++++++++++++ tools/daily-scripts/end_of_the_day.pl | 104 +++++++++++++++++++++ tools/daily-scripts/reingest_uningested.pl | 72 ++++++++++++++ tools/daily-scripts/test_eg_services.pl | 48 ++++++++++ .../update_indexes_for_ampersand_equivalency.sql | 16 ++++ 7 files changed, 425 insertions(+) create mode 100644 tools/daily-scripts/OSUL-patrons-lang-prefs.sql create mode 100644 tools/daily-scripts/circ_date_to_expire_date.pl create mode 100644 tools/daily-scripts/delete_ill_books.pl create mode 100644 tools/daily-scripts/end_of_the_day.pl create mode 100644 tools/daily-scripts/reingest_uningested.pl create mode 100644 tools/daily-scripts/test_eg_services.pl create mode 100644 tools/daily-scripts/update_indexes_for_ampersand_equivalency.sql diff --git a/tools/daily-scripts/OSUL-patrons-lang-prefs.sql b/tools/daily-scripts/OSUL-patrons-lang-prefs.sql new file mode 100644 index 0000000000..be7ca94ad7 --- /dev/null +++ b/tools/daily-scripts/OSUL-patrons-lang-prefs.sql @@ -0,0 +1,10 @@ +-- Ensure that we have a stat cat entry for language preference for OSUL users +-- NOTE: We should cut over to usr_settings at some point +INSERT INTO actor.stat_cat_entry_usr_map (stat_cat_entry, stat_cat, target_usr) + SELECT 'English', 2, au.id + FROM actor.usr au + WHERE au.id NOT IN ( + SELECT target_usr + FROM actor.stat_cat_entry_usr_map + ) AND home_ou = 103 +; diff --git a/tools/daily-scripts/circ_date_to_expire_date.pl b/tools/daily-scripts/circ_date_to_expire_date.pl new file mode 100644 index 0000000000..50492edb25 --- /dev/null +++ b/tools/daily-scripts/circ_date_to_expire_date.pl @@ -0,0 +1,76 @@ +#!/usr/bin/perl -w +use strict; +use warnings; + +# Evergreen sets due dates that are past the user's expiry date + +# Let's fix that after the fact, for now, by setting the due dates to the user's expiry date + +use DBI; +use Getopt::Long; +use OpenSRF::EX qw/:try/; +use OpenSRF::Utils qw/:daemon/; +use OpenSRF::System; +use OpenSRF::AppSession; +use OpenSRF::Utils::SettingsClient; + +my ($config, $set_due_time) = ('/openils/conf/opensrf_core.xml', 0); + +GetOptions( + "bootstrap=s" => \$config, + "set_due_time" => \$set_due_time, +); + +OpenSRF::System->bootstrap_client( config_file => $config ); + +my $sc = OpenSRF::Utils::SettingsClient->new; +my $db_driver = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => 'driver' ); +my $db_host = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'host' ); +my $db_port = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'port' ); +my $db_name = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'db' ); +my $db_user = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'user' ); +my $db_pw = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'pw' ); + +my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host . ';port=' . $db_port; + +my $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1}); + +end_of_day($set_due_time); + +$dbh->disconnect; + +sub end_of_day { + my $set_due_time = shift; + + my $select_stmt = < au.expire_date + AND au.expire_date > NOW() + AND au.expire_date < NOW() + '2 years'::interval + AND au.home_ou IN (103, 110, 126) + ORDER BY au.expire_date +STMT + + my $update_stmt = < au.expire_date + AND au.expire_date > NOW() + AND au.expire_date < NOW() + '2 years'::interval + AND au.home_ou IN (103, 110, 126) + AND ac.checkin_time IS NULL +UPDATE + + + my $results = $dbh->selectall_arrayref($select_stmt); + print localtime() . " - found " . scalar(@$results) . " circulation transactions to update where due_date > expire_date\n"; + if ($set_due_time) { + my $stmt = $dbh->prepare($update_stmt); + my $updates = $stmt->execute(); + print "Updated $updates circulation transactions.\n"; + } +} + diff --git a/tools/daily-scripts/delete_ill_books.pl b/tools/daily-scripts/delete_ill_books.pl new file mode 100644 index 0000000000..f230433083 --- /dev/null +++ b/tools/daily-scripts/delete_ill_books.pl @@ -0,0 +1,99 @@ +#!/usr/bin/perl -w +use strict; + +use DBI; +use Getopt::Long; +use OpenSRF::EX qw/:try/; +use OpenSRF::Utils qw/:daemon/; +use OpenSRF::System; +use OpenSRF::AppSession; +use OpenSRF::Utils::SettingsClient; +use File::Find; + +my ($config) = ('/openils/conf/opensrf_core.xml'); + +GetOptions( + "bootstrap=s" => \$config, +); + +OpenSRF::System->bootstrap_client( config_file => $config ); + +my $sc = OpenSRF::Utils::SettingsClient->new; +my $db_driver = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => 'driver' ); +my $db_host = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'host' ); +my $db_port = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'port' ); +my $db_name = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'db' ); +my $db_user = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'user' ); +my $db_pw = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'pw' ); + +my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host . ';port=' . $db_port; + +my $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1}); + +delete_racer_callnumbers(); + +$dbh->disconnect; + +sub delete_racer_callnumbers { + my $select_stmt = <selectcol_arrayref($select_stmt); + print localtime() . " - found " . scalar(@$results) . " RACER book call numbers to delete:\n"; + if (scalar(@$results)) { + foreach (@$results) { + print "\t$_\n"; + } + my $stmt = $dbh->prepare($delete_stmt); + my $updates = $stmt->execute(); + } +} + diff --git a/tools/daily-scripts/end_of_the_day.pl b/tools/daily-scripts/end_of_the_day.pl new file mode 100644 index 0000000000..fafe56da9a --- /dev/null +++ b/tools/daily-scripts/end_of_the_day.pl @@ -0,0 +1,104 @@ +#!/usr/bin/perl -w + +# Sets the due time of items with a given loan period for a given library to 23:59:59 + +# This is a temporary workaround for Evergreen's assumption that the +# fine generating script will only run once a day, to avoid dinging a patron +# with an overdue charge at 48 hours + 5 minutes rather than at the end of the +# day that 48 hours falls on. + +# We also found that editing the due date for a given item sets the corresponding +# due time to 00:00 - which isn't great, as that means that it is due the minute +# the day starts. So, for now, we'll set all daily / weekly loans or those loans +# that are due exactly at midnight to being due at 23:59:59 - the very last second +# of the day on which it is due. This probably meets our patrons' expectations a bit +# better. + +use DBI; +use Getopt::Long; +use OpenSRF::EX qw/:try/; +use OpenSRF::Utils qw/:daemon/; +use OpenSRF::System; +use OpenSRF::AppSession; +use OpenSRF::Utils::SettingsClient; + +my ($config, $set_due_time) = ('/openils/conf/opensrf_core.xml', 0); + +GetOptions( + "bootstrap=s" => \$config, + "set_due_time" => \$set_due_time, +); + +OpenSRF::System->bootstrap_client( config_file => $config ); + +my $sc = OpenSRF::Utils::SettingsClient->new; +my $db_driver = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => 'driver' ); +my $db_host = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'host' ); +my $db_port = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'port' ); +my $db_name = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'db' ); +my $db_user = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'user' ); +my $db_pw = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'pw' ); + +my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host . ';port=' . $db_port; + +my $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1}); + +end_of_day($set_due_time); + +$dbh->disconnect; + +sub end_of_day { + my $set_due_time = shift; + + my $select_stmt = <selectcol_arrayref($select_stmt); + print localtime() . " - found " . scalar(@$results) . " circulation transactions to update:\n"; + foreach (@$results) { + print "\t$_\n"; + } + if ($set_due_time) { + my $stmt = $dbh->prepare($update_stmt); + my $updates = $stmt->execute(); + print "Updated $updates circulation transactions.\n"; + } +} diff --git a/tools/daily-scripts/reingest_uningested.pl b/tools/daily-scripts/reingest_uningested.pl new file mode 100644 index 0000000000..9aa16a4ebf --- /dev/null +++ b/tools/daily-scripts/reingest_uningested.pl @@ -0,0 +1,72 @@ +#!/usr/bin/perl -w +use strict; +use warnings; + +# Reingest biblio.record_entry records that didn't get ingested due to the simple_rec_sync bug +# Ingested records are expected to have an entry in the keyword index +# Might want to build a variation on this that reingests edited records on a nightly basis + +use DBI; +use Getopt::Long; +use OpenSRF::EX qw/:try/; +use OpenSRF::Utils qw/:daemon/; +use OpenSRF::System; +use OpenSRF::AppSession; +use OpenSRF::Utils::SettingsClient; + +my ($config, $reingest) = ('/openils/conf/opensrf_core.xml', 0); + +GetOptions( + "bootstrap=s" => \$config, + "reingest" => \$reingest, +); + +OpenSRF::System->bootstrap_client( config_file => $config ); + +my $sc = OpenSRF::Utils::SettingsClient->new; +my $db_driver = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => 'driver' ); +my $db_host = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'host' ); +my $db_port = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'port' ); +my $db_name = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'db' ); +my $db_user = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'user' ); +my $db_pw = $sc->config_value( apps => 'open-ils.storage' => app_settings => databases => database => 'pw' ); + +my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host . ';port=' . $db_port; + +my $dbh = DBI->connect($dsn,$db_user,$db_pw, {pg_enable_utf8 => 1, RaiseError => 1}); + +reingest_empty_records($reingest); + +$dbh->disconnect; + +sub reingest_empty_records { + my $select_stmt = < 0 + EXCEPT + SELECT mrd.source + FROM metabib.keyword_field_entry mrd +STMT + + my $results = $dbh->selectcol_arrayref($select_stmt); + print localtime() . " - found " . scalar(@$results) . " records to reingest\n"; + foreach (@$results) { + print "\t$_\n"; + } + if ($reingest) { + + foreach (@$results) { + my $r = OpenSRF::AppSession + ->create( 'open-ils.ingest' ) + ->request( 'open-ils.ingest.full.biblio.record' => $_ ); + + while (!$r->complete) { $r->recv }; + + # Sleep for 10 seconds between each request to prevent blocking + sleep(10); + } + } +} + diff --git a/tools/daily-scripts/test_eg_services.pl b/tools/daily-scripts/test_eg_services.pl new file mode 100644 index 0000000000..b2cc538e9e --- /dev/null +++ b/tools/daily-scripts/test_eg_services.pl @@ -0,0 +1,48 @@ +#/usr/bin/perl +use strict; +use OpenSRF::AppSession; +use OpenSRF::System; + +OpenSRF::System->bootstrap_client(config_file => '/openils/conf/opensrf_core.xml'); + +my @services = qw{ + opensrf.settings + opensrf.math + opensrf.dbmath + open-ils.acq + open-ils.cat + open-ils.supercat + open-ils.search + open-ils.circ + open-ils.actor + open-ils.auth + open-ils.storage + open-ils.penalty + open-ils.cstore + open-ils.collections + open-ils.ingest + open-ils.reporter + open-ils.reporter-store + open-ils.permacrud + open-ils.pcrud + open-ils.trigger + open-ils.fielder + open-ils.vandelay + open-ils.resolver +}; + +foreach my $service (@services) { + check_service($service); +} + +sub check_service { + my $service = shift; + my $session = OpenSRF::AppSession->create($service); + my $request = $session->request("opensrf.system.echo", "All is well"); + if ($request && $request->gather() eq "All is well") { + print "$service: All is well\n"; + } else { + print "$service: FAIL\n"; + } +} + diff --git a/tools/daily-scripts/update_indexes_for_ampersand_equivalency.sql b/tools/daily-scripts/update_indexes_for_ampersand_equivalency.sql new file mode 100644 index 0000000000..e38eab44b0 --- /dev/null +++ b/tools/daily-scripts/update_indexes_for_ampersand_equivalency.sql @@ -0,0 +1,16 @@ +-- Cheap way of making titles and the like that contain "&" +-- return results for well-meaning queries that use "and" +BEGIN; +UPDATE metabib.author_field_entry + SET value = value || ' and' + WHERE value LIKE '%&%' AND value NOT LIKE '% and %'; +UPDATE metabib.keyword_field_entry + SET value = value || ' and' + WHERE value LIKE '%&%' AND value NOT LIKE '% and %'; +UPDATE metabib.subject_field_entry + SET value = value || ' and' + WHERE value LIKE '%&%' AND value NOT LIKE '% and %'; +UPDATE metabib.title_field_entry + SET value = value || ' and' + WHERE value LIKE '%&%' AND value NOT LIKE '% and %'; +COMMIT; -- 2.11.0