From 8d5945f1357a107a5db5ea23c7e23b08c37fe705 Mon Sep 17 00:00:00 2001
From: dbs <dbs@6d9bc8c9-1ec2-4278-b937-99fde70a366f>
Date: Thu, 16 Jul 2009 20:37:10 +0000
Subject: [PATCH] We circulate ILL books temporarily as part of the Conifer
 system by attaching their call number and barcode to a set of generic bib
 records.

Over time, the mass of call numbers attached to those bib records gets too big to retrieve in
the staff client holdings maintenance dialogue.

This script prunes the call numbers that have successfully circulated and for which no
outstanding fines remain.


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

diff --git a/tools/delete_ill_books.pl b/tools/delete_ill_books.pl
new file mode 100644
index 0000000000..b88b890898
--- /dev/null
+++ b/tools/delete_ill_books.pl
@@ -0,0 +1,97 @@
+#!/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 = <<SELECT;
+		SELECT acn.id
+		FROM asset.call_number acn
+		INNER JOIN asset.copy ac ON ac.call_number = acn.id
+		INNER JOIN action.circulation acirc ON ac.id = acirc.target_copy
+		AND acn.record IN (
+			SELECT bre.id
+			FROM biblio.record_entry bre
+			WHERE bre.id IN (
+				'2237519',
+				'2239801',
+				'2239798',
+				'2239797',
+				'2239799',
+				'2239800'
+                        )
+			AND bre.deleted IS FALSE
+		) AND acirc.checkin_time IS NOT NULL
+		AND acirc.id NOT IN (
+			SELECT id 
+			FROM money.open_billable_xact_summary
+		) AND acn.deleted IS FALSE
+
+SELECT
+
+	my $delete_stmt = <<DELETE;
+		DELETE FROM asset.call_number acn
+		USING  asset.copy ac 
+		INNER JOIN action.circulation acirc ON ac.id = acirc.target_copy
+		WHERE acn.id = ac.call_number 
+		AND acn.record IN (
+			SELECT bre.id
+			FROM biblio.record_entry bre
+			WHERE bre.id IN (
+				'2237519',
+				'2239801',
+				'2239798',
+				'2239797',
+				'2239799',
+				'2239800'
+                        )
+			AND bre.deleted IS FALSE
+		) AND acirc.checkin_time IS NOT NULL
+		AND acirc.id NOT IN (
+			SELECT id 
+			FROM money.open_billable_xact_summary
+		) AND acn.deleted IS FALSE
+DELETE
+
+	my $results = $dbh->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();
+	}
+}
+
-- 
2.11.0