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.attempt_isbn (BIGINT) RETURNS TEXT AS $$
  70   DECLARE
  71     bib ALIAS FOR $1;
  72     output TEXT[];  -- Coding this now in case we ever can send more than one isbn
  73     loopvar TEXT;
  74   BEGIN
  75
  76   -- mine metabib.real_full_rec
  77     FOR loopvar IN
  78       SELECT regexp_replace(value,'^\s*([^\s\(\)\:\.]*)[\s\(\)\:\.]*.*$','\1','g')
  79       FROM metabib.real_full_rec where
  80         tag='020' AND
  81         subfield in('a','z') AND
  82         length(regexp_replace(value,'^\s*([^\s\(\)\:\.]*)[\s\(\)\:\.]*.*$','\1','g')) < 14 AND
  83         record = bib
  84         ORDER BY length(regexp_replace(value,'^\s*([^\s\(\)\:\.]*)[\s\(\)\:\.]*.*$','\1','g')) DESC, regexp_replace(value,'^\s*([^\s\(\)\:\.]*)[\s\(\)\:\.]*.*$','\1','g')
  85     LOOP
  86       output = array_append(output, loopvar);
  87     END LOOP;
  88
  89   -- Fail over to reporter.materialized_simple_record
  90     IF array_length(output, 1) = 0
  91     THEN
  92       FOR loopvar IN
  93         SELECT regexp_replace(isbn,'^\s*([^\s\(\)\:\.]*)[\s\(\)\:\.]*.*$','\1','g'),* FROM
  94         (
  95           SELECT unnest(isbn) AS "isbn" FROM
  96           reporter.materialized_simple_record
  97           WHERE
  98           id = bib
  99         ) AS a
 100         WHERE
 101         length(regexp_replace(isbn,'^\s*([^\s\(\)\:\.]*)[\s\(\)\:\.]*.*$','\1','g')) < 14
 102         ORDER BY length(regexp_replace(isbn,'^\s*([^\s\(\)\:\.]*)[\s\(\)\:\.]*.*$','\1','g')) DESC, regexp_replace(isbn,'^\s*([^\s\(\)\:\.]*)[\s\(\)\:\.]*.*$','\1','g')
 103       LOOP
 104         output = array_append(output, loopvar);
 105       END LOOP;
 106     END IF;
 107     IF array_length(output, 1) = 0
 108     THEN
 109     FOR loopvar IN
 110         EXECUTE E'SELECT \'\' AS a;'
 111       LOOP
 112         output = array_append(output, loopvar);
 113       END LOOP;
 114     END IF;
 115
 116     RETURN output[1];
 117
 118   EXCEPTION
 119     WHEN OTHERS THEN
 120       FOR loopvar IN
 121         EXECUTE E'SELECT \'\' AS a;'
 122       LOOP
 123         output = array_append(output, loopvar);
 124       END LOOP;
 125   RETURN output[1];
 126   END;
 127 $$ LANGUAGE plpgsql STRICT STABLE;
 128
 129
 130 CREATE OR REPLACE FUNCTION collectionHQ.quote (TEXT) RETURNS TEXT AS $$
 131   DECLARE
 132     value ALIAS FOR $1;
 133     output TEXT;
 134   BEGIN
 135     IF value IS NOT NULL AND value <> '' THEN
 136       RETURN '##' || value || '##';
 137     ELSE
 138       RETURN '';
 139     END IF;
 140   END;
 141 $$ LANGUAGE PLPGSQL STRICT STABLE;
 142
 143 DROP FUNCTION IF EXISTS collectionHQ.write_item_rows_to_stdout (TEXT, INT);
 144 CREATE OR REPLACE FUNCTION collectionHQ.write_item_rows_to_stdout (TEXT, INT) RETURNS VOID AS $$
 145 -- Usage: SELECT collectionHQ.write_item_rows_to_stdout ('LIBRARYCODE',org_unit_id);
 146
 147   DECLARE
 148     item BIGINT;
 149     authority_code ALIAS FOR $1;
 150     org_unit_id ALIAS for $2;
 151     lms_bib_id BIGINT;
 152     library_code TEXT;
 153     last_circ_lib TEXT;
 154     bar_code TEXT;
 155     last_use_date TEXT;
 156     cumulative_use_total TEXT;
 157     cumulative_use_current TEXT;
 158     status TEXT;
 159     date_added TEXT;
 160     price TEXT;
 161     purchase_code TEXT;
 162     rotating_stock TEXT;
 163     lib_supsel_tag TEXT;
 164     gen_supsel_tag TEXT;
 165     notes TEXT;
 166     extract_date TEXT;
 167     collection_code TEXT;
 168     collection_code_level_2 TEXT;
 169     filter_level_1 TEXT;
 170     filter_level_2 TEXT;
 171     filter_level_3 TEXT;
 172     filter_level_4 TEXT;
 173     isbn TEXT := '';
 174     output TEXT := '';
 175     arrived TIMESTAMPTZ;
 176     num_rows INTEGER := 0;
 177
 178   BEGIN
 179
 180     FOR item IN
 181       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
 182     LOOP
 183
 184       SELECT cn.record, cn.label, collectionHQ.attempt_isbn(cn.record::BIGINT)
 185       INTO lms_bib_id, filter_level_1, isbn
 186       FROM asset.call_number cn, asset.copy c
 187       WHERE c.call_number = cn.id AND c.id =  item;
 188       SELECT collectionHQ.attempt_price(ac.price::TEXT), barcode, ac.status,
 189              REPLACE(create_date::DATE::TEXT, '-', ''),
 190              CASE WHEN floating::INT > 0 THEN 'Y' ELSE NULL END
 191       INTO price, bar_code, status, date_added, rotating_stock
 192       FROM asset.copy ac
 193       WHERE id = item;
 194       IF price IS NULL OR price = '' THEN
 195         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)
 196         INTO price
 197         FROM biblio.record_entry
 198         WHERE id = lms_bib_id;
 199       END IF;
 200       SELECT REPLACE(NOW()::DATE::TEXT, '-', '') INTO extract_date;
 201       SELECT ou.shortname INTO library_code FROM actor.org_unit ou, asset.copy c WHERE ou.id = c.circ_lib AND c.id = item;
 202       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)
 203         WHERE circ.target_copy = item ORDER BY circ.xact_start DESC LIMIT 1;
 204       SELECT REPLACE(xact_start::DATE::TEXT, '-', '') INTO last_use_date FROM action.circulation WHERE target_copy = item ORDER BY xact_start DESC LIMIT 1;
 205       SELECT circ_count INTO cumulative_use_total FROM extend_reporter.full_circ_count WHERE id = item;
 206       IF cumulative_use_total IS NULL THEN
 207         cumulative_use_total := '0';
 208       END IF;
 209       SELECT MAX(dest_recv_time) INTO arrived
 210       FROM action.transit_copy atc
 211       JOIN asset.copy ac ON (ac.id = atc.target_copy AND ac.circ_lib = atc.dest)
 212       WHERE ac.id = item;
 213       IF arrived IS NOT NULL THEN
 214         SELECT COUNT(*) INTO cumulative_use_current FROM action.circulation WHERE target_copy = item AND xact_start > arrived;
 215       ELSE
 216       cumulative_use_current := '0';
 217       END IF;
 218       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;
 219       SELECT l.name INTO collection_code FROM asset.copy c, asset.copy_location l WHERE c.location = l.id AND c.id = item;
 220
 221       purchase_code := ''; -- FIXME do we want something else here?
 222       lib_supsel_tag := ''; -- FIXME do we want something else here?
 223       gen_supsel_tag := ''; -- FIXME do we want something else here?
 224       collection_code_level_2 := ''; -- FIXME do we want something else here?
 225       filter_level_2 := ''; -- FIXME do we want something else here?
 226       filter_level_3 := ''; -- FIXME do we want something else here?
 227       filter_level_4 := ''; -- FIXME do we want something else here?
 228
 229       output := '##HOLD##,'
 230         || lms_bib_id || ','
 231         || COALESCE(collectionHQ.quote(authority_code), '') || ','
 232         || COALESCE(collectionHQ.quote(library_code), '') || ','
 233                 || COALESCE(collectionHQ.quote(last_circ_lib), '') || ','
 234         || COALESCE(collectionHQ.quote(bar_code), '') || ','
 235         || COALESCE(collectionHQ.quote(last_use_date), '') || ','
 236         || COALESCE(cumulative_use_total, '') || ','
 237         --|| COALESCE(cumulative_use_current, '') || ','
 238                 || '0 ,'
 239         || COALESCE(collectionHQ.quote(status), '') || ','
 240         || COALESCE(collectionHQ.quote(date_added), '') || ','
 241         || COALESCE(price, '') || ','
 242         || COALESCE(collectionHQ.quote(purchase_code), '') || ','
 243         || COALESCE(collectionHQ.quote(rotating_stock), '') || ','
 244         || COALESCE(collectionHQ.quote(lib_supsel_tag), '') || ','
 245         || COALESCE(collectionHQ.quote(gen_supsel_tag), '') || ','
 246         || COALESCE(collectionHQ.quote(notes), '') || ','
 247         || COALESCE(collectionHQ.quote(extract_date), '') || ','
 248         || COALESCE(collectionHQ.quote(collection_code), '') || ','
 249         || COALESCE(collectionHQ.quote(collection_code_level_2), '') || ','
 250         || COALESCE(collectionHQ.quote(filter_level_1), '') || ','
 251         || COALESCE(collectionHQ.quote(filter_level_2), '') || ','
 252         || COALESCE(collectionHQ.quote(filter_level_3), '') || ','
 253         || COALESCE(collectionHQ.quote(filter_level_4), '') || ','
 254         || COALESCE(collectionHQ.quote(isbn), '');
 255
 256        RAISE INFO '%', output;
 257
 258        num_rows := num_rows + 1;
 259        IF (num_rows::numeric % 1000.0 = 0.0) THEN RAISE INFO '% rows written', num_rows; END IF;
 260
 261     END LOOP;
 262
 263     RAISE INFO '% rows written in total.', num_rows;
 264
 265   END;
 266
 267 $$ LANGUAGE plpgsql;
 268
 269 DROP FUNCTION IF EXISTS collectionHQ.write_bib_rows_to_stdout(TEXT, INT);
 270 CREATE OR REPLACE FUNCTION collectionHQ.write_bib_rows_to_stdout (TEXT, INT) RETURNS VOID AS $$
 271 -- Usage: SELECT collectionHQ.write_bib_rows_to_stdout('LIBRARYCODE', org_unit_id);
 272
 273   DECLARE
 274     library_service_code ALIAS FOR $1;
 275     org_unit_id ALIAS FOR $2;
 276     isbn TEXT := '';
 277     title TEXT := '';
 278     author TEXT := '';
 279     edition_num TEXT := '';
 280     publication_date TEXT := '';
 281     publisher TEXT := '';
 282     price TEXT := '';
 283     lms_item_type TEXT := '';
 284     class_num TEXT := '';
 285     subject TEXT := '';
 286     genre TEXT := '';
 287     extract_date TEXT := '';
 288     output TEXT := '';
 289     lms_bib_id BIGINT;
 290     num_rows INTEGER := 0;
 291
 292   BEGIN
 293
 294     FOR lms_bib_id IN
 295       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
 296     LOOP
 297
 298       SELECT collectionHQ.attempt_isbn(r.id::BIGINT),
 299              SUBSTRING(r.title FROM 1 FOR 100),
 300              SUBSTRING(r.author FROM 1 FOR 50)
 301       INTO isbn, title, author
 302       FROM reporter.materialized_simple_record r
 303       WHERE id = lms_bib_id;
 304       SELECT
 305         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),
 306         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),
 307         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),
 308         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),
 309         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),
 310         (SELECT '!!!' || SUBSTRING(STRING_AGG("six50",'!!!') FROM 1 FOR 200) || '!!!' FROM
 311           (SELECT naco_normalize( unnest(XPATH('//marc:datafield[@tag="650"]/marc:subfield[@code="a"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))::TEXT) AS "six50"
 312                 FROM BIBLIO.RECORD_ENTRY WHERE ID=A.ID ) AS B
 313         ),
 314         (SELECT '!!!' || SUBSTRING(STRING_AGG("six55",'!!!') FROM 1 FOR 200) || '!!!'  FROM
 315               (SELECT naco_normalize( unnest(XPATH('//marc:datafield[@tag="655"]/marc:subfield[@code="a"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))::TEXT) AS "six55"
 316             FROM BIBLIO.RECORD_ENTRY WHERE ID=A.ID ) AS C
 317         )
 318
 319       INTO edition_num, publication_date, publisher, price, class_num, subject, genre
 320       FROM biblio.record_entry A
 321       WHERE id = lms_bib_id;
 322
 323       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;
 324       SELECT REPLACE(NOW()::DATE::TEXT, '-', '') INTO extract_date;
 325
 326       output :=
 327         '##BIB##,'
 328         || lms_bib_id || ','
 329         || COALESCE(collectionHQ.quote(library_service_code), '') || ','
 330         || COALESCE(collectionHQ.quote(isbn), '') || ','
 331         || COALESCE(collectionHQ.quote(title), '') || ','
 332         || COALESCE(collectionHQ.quote(author), '') || ','
 333         || COALESCE(collectionHQ.quote(edition_num), '') || ','
 334         || COALESCE(collectionHQ.quote(publication_date), '') || ','
 335         || COALESCE(collectionHQ.quote(publisher), '') || ','
 336         || COALESCE(price, '') || ','
 337         || COALESCE(collectionHQ.quote(lms_item_type), '') || ','
 338         || COALESCE(collectionHQ.quote(class_num), '') || ','
 339         || COALESCE(collectionHQ.quote(extract_date), '') || ','
 340         || COALESCE(collectionHQ.quote(subject), '') || ','
 341         || COALESCE(collectionHQ.quote(genre), '');
 342
 343        RAISE INFO '%', output;
 344
 345        num_rows := num_rows + 1;
 346        IF (num_rows::numeric % 1000.0 = 0.0) THEN RAISE INFO '% rows written', num_rows; END IF;
 347
 348     END LOOP;
 349
 350     RAISE INFO '% rows written in total.', num_rows;
 351
 352   END;
 353
 354 $$ LANGUAGE plpgsql;