From 8dfe6ad5b1dee8ce953af54918f219223df1b0e7 Mon Sep 17 00:00:00 2001
From: dbs <dbs@6d9bc8c9-1ec2-4278-b937-99fde70a366f>
Date: Thu, 2 Jul 2009 18:59:17 +0000
Subject: [PATCH] Add a daily script to ensure that due dates are not further
 out than a given user's expiry date

Currently limited to just JN Desmarais circulation transactions


git-svn-id: svn://svn.open-ils.org/ILS-Contrib/conifer/trunk@558 6d9bc8c9-1ec2-4278-b937-99fde70a366f
---
 tools/circ_date_to_expire_date.pl | 80 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 80 insertions(+)
 create mode 100644 tools/circ_date_to_expire_date.pl

diff --git a/tools/circ_date_to_expire_date.pl b/tools/circ_date_to_expire_date.pl
new file mode 100644
index 0000000000..93b2ce93eb
--- /dev/null
+++ b/tools/circ_date_to_expire_date.pl
@@ -0,0 +1,80 @@
+#!/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 = <<STMT;
+		SELECT aoc.due_date, au.expire_date
+		FROM action.open_circulation aoc 
+		INNER JOIN actor.usr au ON au.id = aoc.usr
+		WHERE aoc.due_date > au.expire_date
+		AND au.expire_date > NOW()
+		AND au.expire_date < NOW() + '2 years'::interval
+		AND au.home_ou = 103
+		ORDER BY au.expire_date
+STMT
+
+	my $update_stmt = <<UPDATE;
+		UPDATE action.circulation SET due_date = au.expire_date
+		FROM actor.usr au
+		WHERE action.circulation.id IN (
+			SELECT aoc.id
+			FROM actor.usr au
+			INNER JOIN action.circulation aoc ON aoc.usr = au.id
+			WHERE aoc.due_date > au.expire_date 
+			AND au.expire_date > NOW() 
+			AND au.expire_date < NOW() + '2 years'::interval 
+			AND au.home_ou = 103 
+			AND aoc.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";
+	}
+}
+
-- 
2.11.0