From 239ff7ddf69a07eefdff52bd64fcab4e93d54054 Mon Sep 17 00:00:00 2001
From: Chris Sharp <csharp@georgialibraries.org>
Date: Tue, 25 Oct 2016 09:34:20 -0400
Subject: [PATCH] Adding find non acq copies on acq bibs script.

---
 reports/find-non-acq-copies.sh | 58 ++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 58 insertions(+)
 create mode 100755 reports/find-non-acq-copies.sh

diff --git a/reports/find-non-acq-copies.sh b/reports/find-non-acq-copies.sh
new file mode 100755
index 0000000..8ffe644
--- /dev/null
+++ b/reports/find-non-acq-copies.sh
@@ -0,0 +1,58 @@
+#!/bin/bash
+#
+# Copyright (C) 2016 Georgia Public Library Service
+# Chris Sharp <csharp@georgialibraries.org>
+#
+#    This program is free software: you can redistribute it and/or modify
+#    it under the terms of the GNU General Public License as published by
+#    the Free Software Foundation, either version 3 of the License, or
+#    (at your option) any later version.
+#
+#    This program is distributed in the hope that it will be useful,
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+#    GNU General Public License for more details.
+#
+#    You should have received a copy of the GNU General Public License
+#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+# Per policy, PINES libraries pull bib records directly from OCLC via 
+# z39.50, which creates the TCN with a prefix of 'ocn' or 'ocm'.  Since
+# we haven't standardized the bib source for acquisitions uploads, we
+# look for bib records without a TCN that begins with 'oc' and for items
+# without a barcode that begins with 'ACQ' - hopefully that nets the records
+# we're looking for without a whole lot of outliers.  We also only look at
+# bibs created for the past 2 years.
+#
+# This script assumes you have 'mutt' installed and configured to use an MTA.
+#
+
+DB_HOST="mydbhost"
+DB_USER="mydbuser"
+OUTFILE="non-acq-copies-on-acq-bibs-`date +%F`.csv"
+DESTMAIL="user@example.org"
+read -d '' SQL <<EOF 
+select  creator_home_ou.shortname as "Item Creator Home Library",
+        creator_card.barcode as "Item Creator Barcode",
+        creator.first_given_name as "Item Creator First Name",
+        creator.second_given_name as "Item Creator Middle Name",
+        creator.family_name as "Item Creator Last Name",
+        acp.barcode as "Item Barcode",
+        bre.tcn_value as "Bib TCN",
+        bre.id as "Bib ID",
+        acp.create_date as "Item Create Date/Time"
+from    asset.copy acp
+        join asset.call_number acn on (acp.call_number = acn.id)
+        join biblio.record_entry bre on (acn.record = bre.id)
+        join actor.usr creator on (acp.creator = creator.id)
+        join actor.card creator_card on (creator.card = creator_card.id)
+        join actor.org_unit creator_home_ou on (creator.home_ou = creator_home_ou.id)
+where   bre.tcn_value !~ '^oc'
+        and not acp.deleted
+        and bre.create_date between date(now() - '2 years'::interval) and date(now())
+        and acp.barcode !~ '^ACQ'
+order by 1, 2
+EOF
+
+psql -A -t -F';' -h $DB_HOST -U $DB_USER -o $OUTFILE -c "$SQL"
+echo "Non-Acq copies on Acq records report $OUTFILE attached." | mutt -s "Non-Acq Copies on Acq Bibs Report" -a $OUTFILE -- $DESTMAIL
-- 
2.11.0