collectionHQ/functions.sql

   1 --
   2 --   Functions used by collectionHQ reporting.  This only needs to be run once.
   3 --
   4
   5 --    Copyright (C) 2011-2012 Equinox Software Inc.
   6 --    Ben Ostrowsky <ben@esilibrary.com>
   7 --    Galen Charlton <gmc@esilibrary.com>
   8 --
   9 --    Original version sponsored by the King County Library System
  10 --
  11 --    This program is free software; you can redistribute it and/or modify
  12 --    it under the terms of the GNU General Public License as published by
  13 --    the Free Software Foundation; either version 2 of the License, or
  14 --    (at your option) any later version.
  15 --
  16 --    This program is distributed in the hope that it will be useful,
  17 --    but WITHOUT ANY WARRANTY; without even the implied warranty of
  18 --    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  19 --    GNU General Public License for more details.
  20 --
  21 --    You should have received a copy of the GNU General Public License
  22 --    along with this program; if not, write to the Free Software
  23 --    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  24
  25 CREATE SCHEMA collectionHQ;
  26
  27 CREATE OR REPLACE FUNCTION collectionHQ.attempt_year (TEXT) RETURNS TEXT AS $$
  28   DECLARE
  29     attempt_value ALIAS FOR $1;
  30     output TEXT;
  31   BEGIN
  32     FOR output IN
  33       EXECUTE 'SELECT SUBSTRING(REGEXP_REPLACE(' || quote_literal(attempt_value) || E', E\'[^0-9]\', \'\', \'g\') FROM 1 FOR 4) AS a;'
  34     LOOP
  35       RETURN output;
  36     END LOOP;
  37   EXCEPTION
  38     WHEN OTHERS THEN
  39       FOR output IN
  40         EXECUTE E'SELECT \'\' AS a;'
  41       LOOP
  42         RETURN output;
  43       END LOOP;
  44   END;
  45 $$ LANGUAGE PLPGSQL STRICT STABLE;
  46
  47
  48 CREATE OR REPLACE FUNCTION collectionHQ.attempt_price (TEXT) RETURNS TEXT AS $$
  49   DECLARE
  50     attempt_value ALIAS FOR $1;
  51     output TEXT;
  52   BEGIN
  53     FOR output IN
  54       EXECUTE 'SELECT (REGEXP_REPLACE(' || quote_literal(attempt_value) || E', E\'[^0-9\.]\', \'\', \'g\')::NUMERIC(8,2) * 100)::INTEGER AS a;'
  55     LOOP
  56       RETURN output;
  57     END LOOP;
  58   EXCEPTION
  59     WHEN OTHERS THEN
  60       FOR output IN
  61         EXECUTE E'SELECT \'\' AS a;'
  62       LOOP
  63         RETURN output;
  64       END LOOP;
  65   END;
  66 $$ LANGUAGE PLPGSQL STRICT STABLE;
  67
  68
  69 CREATE OR REPLACE FUNCTION collectionHQ.quote (TEXT) RETURNS TEXT AS $$
  70   DECLARE
  71     value ALIAS FOR $1;
  72     output TEXT;
  73   BEGIN
  74     IF value IS NOT NULL AND value <> '' THEN
  75       RETURN '##' || value || '##';
  76     ELSE
  77       RETURN '';
  78     END IF;
  79   END;
  80 $$ LANGUAGE PLPGSQL STRICT STABLE;
  81
  82 DROP FUNCTION IF EXISTS collectionHQ.write_item_rows_to_stdout (TEXT, INT);
  83 CREATE OR REPLACE FUNCTION collectionHQ.write_item_rows_to_stdout (TEXT, INT) RETURNS VOID AS $$
  84 -- Usage: SELECT collectionHQ.write_item_rows_to_stdout ('LIBRARYCODE',org_unit_id);
  85
  86   DECLARE
  87     item BIGINT;
  88     authority_code ALIAS FOR $1;
  89     org_unit_id ALIAS for $2;
  90     lms_bib_id BIGINT;
  91     library_code TEXT;
  92     last_circ_lib TEXT;
  93     bar_code TEXT;
  94     last_use_date TEXT;
  95     cumulative_use_total TEXT;
  96     cumulative_use_current TEXT;
  97     status TEXT;
  98     date_added TEXT;
  99     price TEXT;
 100     purchase_code TEXT;
 101     rotating_stock TEXT;
 102     lib_supsel_tag TEXT;
 103     gen_supsel_tag TEXT;
 104     notes TEXT;
 105     extract_date TEXT;
 106     collection_code TEXT;
 107     collection_code_level_2 TEXT;
 108     filter_level_1 TEXT;
 109     filter_level_2 TEXT;
 110     filter_level_3 TEXT;
 111     filter_level_4 TEXT;
 112     isbn TEXT := '';
 113     output TEXT := '';
 114     arrived TIMESTAMPTZ;
 115     num_rows INTEGER := 0;
 116
 117   BEGIN
 118
 119     FOR item IN
 120       SELECT id FROM asset.copy WHERE NOT deleted AND circ_lib IN (SELECT id FROM actor.org_unit_descendants(org_unit_id)) ORDER BY id
 121     LOOP
 122
 123       SELECT cn.record, cn.label
 124       INTO lms_bib_id, filter_level_1
 125       FROM asset.call_number cn, asset.copy c
 126       WHERE c.call_number = cn.id AND c.id =  item;
 127       SELECT r.isbn[1] INTO isbn
 128       FROM reporter.materialized_simple_record r
 129       WHERE id = lms_bib_id;
 130       SELECT collectionHQ.attempt_price(ac.price::TEXT), barcode, ac.status,
 131              REPLACE(create_date::DATE::TEXT, '-', ''),
 132              CASE WHEN floating::INT > 0 THEN 'Y' ELSE NULL END
 133       INTO price, bar_code, status, date_added, rotating_stock
 134       FROM asset.copy ac
 135       WHERE id = item;
 136       IF price IS NULL OR price = '' THEN
 137         SELECT collectionHQ.attempt_price((XPATH('//marc:datafield[@tag="020"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT)
 138         INTO price
 139         FROM biblio.record_entry
 140         WHERE id = lms_bib_id;
 141       END IF;
 142       SELECT REPLACE(NOW()::DATE::TEXT, '-', '') INTO extract_date;
 143       SELECT ou.shortname INTO library_code FROM actor.org_unit ou, asset.copy c WHERE ou.id = c.circ_lib AND c.id = item;
 144       SELECT aou.shortname, REPLACE(circ.xact_start::DATE::TEXT, '-', '') INTO last_circ_lib, last_use_date FROM actor.org_unit aou INNER JOIN action.all_circulation circ ON (circ.circ_lib = aou.id)
 145         WHERE circ.target_copy = item ORDER BY circ.xact_start DESC LIMIT 1;
 146       SELECT REPLACE(xact_start::DATE::TEXT, '-', '') INTO last_use_date FROM action.circulation WHERE target_copy = item ORDER BY xact_start DESC LIMIT 1;
 147       SELECT circ_count INTO cumulative_use_total FROM extend_reporter.full_circ_count WHERE id = item;
 148       IF cumulative_use_total IS NULL THEN
 149         cumulative_use_total := '0';
 150       END IF;
 151       SELECT MAX(dest_recv_time) INTO arrived
 152       FROM action.transit_copy atc
 153       JOIN asset.copy ac ON (ac.id = atc.target_copy AND ac.circ_lib = atc.dest)
 154       WHERE ac.id = item;
 155       IF arrived IS NOT NULL THEN
 156         SELECT COUNT(*) INTO cumulative_use_current FROM action.circulation WHERE target_copy = item AND xact_start > arrived;
 157       ELSE
 158       cumulative_use_current := '0';
 159       END IF;
 160       SELECT SUBSTRING(value FROM 1 FOR 100) INTO notes FROM asset.copy_note WHERE owning_copy = item AND title ILIKE '%collectionHQ%' ORDER BY id LIMIT 1;
 161       SELECT l.name INTO collection_code FROM asset.copy c, asset.copy_location l WHERE c.location = l.id AND c.id = item;
 162
 163       purchase_code := ''; -- FIXME do we want something else here?
 164       lib_supsel_tag := ''; -- FIXME do we want something else here?
 165       gen_supsel_tag := ''; -- FIXME do we want something else here?
 166       collection_code_level_2 := ''; -- FIXME do we want something else here?
 167       filter_level_2 := ''; -- FIXME do we want something else here?
 168       filter_level_3 := ''; -- FIXME do we want something else here?
 169       filter_level_4 := ''; -- FIXME do we want something else here?
 170
 171       output := '##HOLD##,'
 172         || lms_bib_id || ','
 173         || COALESCE(collectionHQ.quote(authority_code), '') || ','
 174         || COALESCE(collectionHQ.quote(library_code), '') || ','
 175                 || COALESCE(collectionHQ.quote(last_circ_lib), '') || ','
 176         || COALESCE(collectionHQ.quote(bar_code), '') || ','
 177         || COALESCE(collectionHQ.quote(last_use_date), '') || ','
 178         || COALESCE(cumulative_use_total, '') || ','
 179         --|| COALESCE(cumulative_use_current, '') || ','
 180                 || '0 ,'
 181         || COALESCE(collectionHQ.quote(status), '') || ','
 182         || COALESCE(collectionHQ.quote(date_added), '') || ','
 183         || COALESCE(price, '') || ','
 184         || COALESCE(collectionHQ.quote(purchase_code), '') || ','
 185         || COALESCE(collectionHQ.quote(rotating_stock), '') || ','
 186         || COALESCE(collectionHQ.quote(lib_supsel_tag), '') || ','
 187         || COALESCE(collectionHQ.quote(gen_supsel_tag), '') || ','
 188         || COALESCE(collectionHQ.quote(notes), '') || ','
 189         || COALESCE(collectionHQ.quote(extract_date), '') || ','
 190         || COALESCE(collectionHQ.quote(collection_code), '') || ','
 191         || COALESCE(collectionHQ.quote(collection_code_level_2), '') || ','
 192         || COALESCE(collectionHQ.quote(filter_level_1), '') || ','
 193         || COALESCE(collectionHQ.quote(filter_level_2), '') || ','
 194         || COALESCE(collectionHQ.quote(filter_level_3), '') || ','
 195         || COALESCE(collectionHQ.quote(filter_level_4), '') || ','
 196         || COALESCE(collectionHQ.quote(isbn), '');
 197
 198        RAISE INFO '%', output;
 199
 200        num_rows := num_rows + 1;
 201        IF (num_rows::numeric % 1000.0 = 0.0) THEN RAISE INFO '% rows written', num_rows; END IF;
 202
 203     END LOOP;
 204
 205     RAISE INFO '% rows written in total.', num_rows;
 206
 207   END;
 208
 209 $$ LANGUAGE plpgsql;
 210
 211 DROP FUNCTION IF EXISTS collectionHQ.write_bib_rows_to_stdout(TEXT, INT);
 212 CREATE OR REPLACE FUNCTION collectionHQ.write_bib_rows_to_stdout (TEXT, INT) RETURNS VOID AS $$
 213 -- Usage: SELECT collectionHQ.write_bib_rows_to_stdout('LIBRARYCODE', org_unit_id);
 214
 215   DECLARE
 216     library_service_code ALIAS FOR $1;
 217     org_unit_id ALIAS FOR $2;
 218     isbn TEXT := '';
 219     title TEXT := '';
 220     author TEXT := '';
 221     edition_num TEXT := '';
 222     publication_date TEXT := '';
 223     publisher TEXT := '';
 224     price TEXT := '';
 225     lms_item_type TEXT := '';
 226     class_num TEXT := '';
 227     extract_date TEXT := '';
 228     output TEXT := '';
 229     lms_bib_id BIGINT;
 230     num_rows INTEGER := 0;
 231
 232   BEGIN
 233
 234     FOR lms_bib_id IN
 235       SELECT DISTINCT bre.id FROM biblio.record_entry bre JOIN asset.call_number acn ON (acn.record = bre.id) WHERE acn.owning_lib IN (SELECT id FROM actor.org_unit_descendants(org_unit_id)) AND NOT acn.deleted AND NOT bre.deleted
 236     LOOP
 237
 238       SELECT r.isbn[1],
 239              SUBSTRING(r.title FROM 1 FOR 100),
 240              SUBSTRING(r.author FROM 1 FOR 50)
 241       INTO isbn, title, author
 242       FROM reporter.materialized_simple_record r
 243       WHERE id = lms_bib_id;
 244       SELECT
 245         SUBSTRING(naco_normalize((XPATH('//marc:datafield[@tag="250"][1]/marc:subfield[@code="a"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT, 'a') FROM 1 FOR 20),
 246         collectionHQ.attempt_year((XPATH('//marc:datafield[@tag="260"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT),
 247         SUBSTRING(naco_normalize((XPATH('//marc:datafield[@tag="260"][1]/marc:subfield[@code="b"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT, 'b') FROM 1 FOR 100),
 248         collectionHQ.attempt_price((XPATH('//marc:datafield[@tag="020"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT),
 249         SUBSTRING(naco_normalize((XPATH('//marc:datafield[@tag="082"][1]/marc:subfield[@code="a"][1]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT, 'a') FROM 1 FOR 20)
 250       INTO edition_num, publication_date, publisher, price, class_num
 251       FROM biblio.record_entry
 252       WHERE id = lms_bib_id;
 253
 254       SELECT circ_modifier INTO lms_item_type FROM asset.copy c, asset.call_number cn WHERE cn.record = lms_bib_id AND c.circ_lib IN (SELECT id FROM actor.org_unit_descendants(org_unit_id)) AND cn.id = c.call_number AND NOT cn.deleted AND NOT c.deleted LIMIT 1;
 255       SELECT REPLACE(NOW()::DATE::TEXT, '-', '') INTO extract_date;
 256
 257       output :=
 258         '##BIB##,'
 259         || lms_bib_id || ','
 260         || COALESCE(collectionHQ.quote(library_service_code), '') || ','
 261         || COALESCE(collectionHQ.quote(isbn), '') || ','
 262         || COALESCE(collectionHQ.quote(title), '') || ','
 263         || COALESCE(collectionHQ.quote(author), '') || ','
 264         || COALESCE(collectionHQ.quote(edition_num), '') || ','
 265         || COALESCE(collectionHQ.quote(publication_date), '') || ','
 266         || COALESCE(collectionHQ.quote(publisher), '') || ','
 267         || COALESCE(price, '') || ','
 268         || COALESCE(collectionHQ.quote(lms_item_type), '') || ','
 269         || COALESCE(collectionHQ.quote(class_num), '') || ','
 270         || COALESCE(collectionHQ.quote(extract_date), '');
 271
 272        RAISE INFO '%', output;
 273
 274        num_rows := num_rows + 1;
 275        IF (num_rows::numeric % 1000.0 = 0.0) THEN RAISE INFO '% rows written', num_rows; END IF;
 276
 277     END LOOP;
 278
 279     RAISE INFO '% rows written in total.', num_rows;
 280
 281   END;
 282
 283 $$ LANGUAGE plpgsql;