Open-ILS/src/sql/Pg/reporter-schema.sql

   1 /*
   2  * Copyright (C) 2004-2008  Georgia Public Library Service
   3  * Copyright (C) 2007-2008  Equinox Software, Inc.
   4  * Mike Rylander <miker@esilibrary.com>
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU General Public License
   8  * as published by the Free Software Foundation; either version 2
   9  * of the License, or (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  * GNU General Public License for more details.
  15  *
  16  */
  17
  18 DROP SCHEMA IF EXISTS reporter CASCADE;
  19
  20 BEGIN;
  21
  22 CREATE SCHEMA reporter;
  23
  24 CREATE TABLE reporter.template_folder (
  25         id              SERIAL                          PRIMARY KEY,
  26         parent          INT                             REFERENCES reporter.template_folder (id) DEFERRABLE INITIALLY DEFERRED,
  27         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  28         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  29         name            TEXT                            NOT NULL,
  30         shared          BOOL                            NOT NULL DEFAULT FALSE,
  31         simple_reporter BOOL            NOT NULL DEFAULT FALSE,
  32         share_with      INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
  33 );
  34 CREATE INDEX rpt_tmpl_fldr_owner_idx ON reporter.template_folder (owner);
  35 CREATE UNIQUE INDEX rpt_template_folder_once_parent_idx ON reporter.template_folder (name,parent);
  36 CREATE UNIQUE INDEX rpt_template_folder_once_idx ON reporter.template_folder (name,owner,simple_reporter) WHERE parent IS NULL;
  37
  38 CREATE TABLE reporter.report_folder (
  39         id              SERIAL                          PRIMARY KEY,
  40         parent          INT                             REFERENCES reporter.report_folder (id) DEFERRABLE INITIALLY DEFERRED,
  41         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  42         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  43         name            TEXT                            NOT NULL,
  44         shared          BOOL                            NOT NULL DEFAULT FALSE,
  45     simple_reporter     BOOL            NOT NULL DEFAULT FALSE,
  46         share_with      INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
  47 );
  48 CREATE INDEX rpt_rpt_fldr_owner_idx ON reporter.report_folder (owner);
  49 CREATE UNIQUE INDEX rpt_report_folder_once_parent_idx ON reporter.report_folder (name,parent);
  50 CREATE UNIQUE INDEX rpt_report_folder_once_idx ON reporter.report_folder (name,owner,simple_reporter) WHERE parent IS NULL;
  51
  52 CREATE TABLE reporter.output_folder (
  53         id              SERIAL                          PRIMARY KEY,
  54         parent          INT                             REFERENCES reporter.output_folder (id) DEFERRABLE INITIALLY DEFERRED,
  55         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  56         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  57         name            TEXT                            NOT NULL,
  58         shared          BOOL                            NOT NULL DEFAULT FALSE,
  59         simple_reporter BOOL                            NOT NULL DEFAULT FALSE,
  60         share_with      INT                             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
  61 );
  62 CREATE INDEX rpt_output_fldr_owner_idx ON reporter.output_folder (owner);
  63 CREATE UNIQUE INDEX rpt_output_folder_once_parent_idx ON reporter.output_folder (name,parent);
  64 CREATE UNIQUE INDEX rpt_output_folder_once_idx ON reporter.output_folder (name,owner,simple_reporter) WHERE parent IS NULL;
  65
  66
  67 CREATE TABLE reporter.template (
  68         id              SERIAL                          PRIMARY KEY,
  69         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  70         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  71         name            TEXT                            NOT NULL,
  72         description     TEXT                            NOT NULL DEFAULT '',
  73         data            TEXT                            NOT NULL,
  74         folder          INT                             NOT NULL REFERENCES reporter.template_folder (id) DEFERRABLE INITIALLY DEFERRED
  75 );
  76 CREATE INDEX rpt_tmpl_owner_idx ON reporter.template (owner);
  77 CREATE INDEX rpt_tmpl_fldr_idx ON reporter.template (folder);
  78 CREATE UNIQUE INDEX rtp_template_folder_once_idx ON reporter.template (name,folder);
  79
  80 CREATE TABLE reporter.report (
  81         id              SERIAL                          PRIMARY KEY,
  82         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  83         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  84         name            TEXT                            NOT NULL DEFAULT '',
  85         description     TEXT                            NOT NULL DEFAULT '',
  86         template        INT                             NOT NULL REFERENCES reporter.template (id) DEFERRABLE INITIALLY DEFERRED,
  87         data            TEXT                            NOT NULL,
  88         folder          INT                             NOT NULL REFERENCES reporter.report_folder (id) DEFERRABLE INITIALLY DEFERRED,
  89         recur           BOOL                            NOT NULL DEFAULT FALSE,
  90         recurrence      INTERVAL
  91 );
  92 CREATE INDEX rpt_rpt_owner_idx ON reporter.report (owner);
  93 CREATE INDEX rpt_rpt_fldr_idx ON reporter.report (folder);
  94 CREATE UNIQUE INDEX rtp_report_folder_once_idx ON reporter.report (name,folder);
  95
  96 CREATE TABLE reporter.schedule (
  97         id              SERIAL                          PRIMARY KEY,
  98         report          INT                             NOT NULL REFERENCES reporter.report (id) DEFERRABLE INITIALLY DEFERRED,
  99         folder          INT                             NOT NULL REFERENCES reporter.output_folder (id) DEFERRABLE INITIALLY DEFERRED,
 100         runner          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 101         run_time        TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 102         start_time      TIMESTAMP WITH TIME ZONE,
 103         complete_time   TIMESTAMP WITH TIME ZONE,
 104         email           TEXT,
 105         excel_format    BOOL                            NOT NULL DEFAULT TRUE,
 106         html_format     BOOL                            NOT NULL DEFAULT TRUE,
 107         csv_format      BOOL                            NOT NULL DEFAULT TRUE,
 108         chart_pie       BOOL                            NOT NULL DEFAULT FALSE,
 109         chart_bar       BOOL                            NOT NULL DEFAULT FALSE,
 110         chart_line      BOOL                            NOT NULL DEFAULT FALSE,
 111         error_code      INT,
 112         error_text      TEXT
 113 );
 114 CREATE INDEX rpt_sched_runner_idx ON reporter.schedule (runner);
 115 CREATE INDEX rpt_sched_folder_idx ON reporter.schedule (folder);
 116 CREATE UNIQUE INDEX rpt_sched_recurrence_once_idx ON reporter.schedule (report,folder,runner,run_time,COALESCE(email,''));
 117
 118 CREATE OR REPLACE VIEW reporter.simple_record AS
 119 SELECT  r.id,
 120         s.metarecord,
 121         r.fingerprint,
 122         r.quality,
 123         r.tcn_source,
 124         r.tcn_value,
 125         title.value AS title,
 126         uniform_title.value AS uniform_title,
 127         author.value AS author,
 128         publisher.value AS publisher,
 129         SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
 130         series_title.value AS series_title,
 131         series_statement.value AS series_statement,
 132         summary.value AS summary,
 133         ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
 134         ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
 135         ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
 136         ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
 137         ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
 138         ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '600' AND subfield = 'a' AND record = r.id)) AS name_subject,
 139         ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '610' AND subfield = 'a' AND record = r.id)) AS corporate_subject,
 140         ARRAY((SELECT value FROM metabib.full_rec WHERE tag = '856' AND subfield IN ('3','y','u') AND record = r.id ORDER BY CASE WHEN subfield IN ('3','y') THEN 0 ELSE 1 END)) AS external_uri
 141   FROM  biblio.record_entry r
 142         JOIN metabib.metarecord_source_map s ON (s.source = r.id)
 143         LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
 144         LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
 145         LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag = '100' AND author.subfield = 'a')
 146         LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND (publisher.tag = '260' OR (publisher.tag = '264' AND publisher.ind2 = '1')) AND publisher.subfield = 'b')
 147         LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND (pubdate.tag = '260' OR (pubdate.tag = '264' AND pubdate.ind2 = '1')) AND pubdate.subfield = 'c')
 148         LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
 149         LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
 150         LEFT JOIN metabib.full_rec series_title ON (r.id = series_title.record AND series_title.tag IN ('830','440') AND series_title.subfield = 'a')
 151         LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
 152         LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
 153   GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
 154
 155 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
 156 SELECT  r.id,
 157     r.fingerprint,
 158     r.quality,
 159     r.tcn_source,
 160     r.tcn_value,
 161     evergreen.oils_json_to_text(d.title) AS title,
 162     evergreen.oils_json_to_text(d.author) AS author,
 163     evergreen.oils_json_to_text(d.publisher) AS publisher,
 164     evergreen.oils_json_to_text(d.pubdate) AS pubdate,
 165     CASE WHEN d.isbn = 'null'
 166         THEN NULL
 167         ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.isbn::JSON)))
 168     END AS isbn,
 169     CASE WHEN d.issn = 'null'
 170         THEN NULL
 171         ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.issn::JSON)))
 172     END AS issn
 173   FROM  biblio.record_entry r
 174         JOIN metabib.wide_display_entry d ON (r.id = d.source);
 175
 176 CREATE TABLE reporter.materialized_simple_record AS SELECT * FROM reporter.old_super_simple_record WHERE 1=0;
 177 ALTER TABLE reporter.materialized_simple_record ADD PRIMARY KEY (id);
 178
 179 CREATE VIEW reporter.super_simple_record AS SELECT * FROM reporter.materialized_simple_record;
 180
 181 CREATE OR REPLACE FUNCTION reporter.simple_rec_update (r_id BIGINT, deleted BOOL) RETURNS BOOL AS $$
 182 BEGIN
 183
 184     DELETE FROM reporter.materialized_simple_record WHERE id = r_id;
 185
 186     IF NOT deleted THEN
 187         INSERT INTO reporter.materialized_simple_record SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record WHERE id = r_id;
 188     END IF;
 189
 190     RETURN TRUE;
 191
 192 END;
 193 $$ LANGUAGE PLPGSQL;
 194
 195 CREATE OR REPLACE FUNCTION reporter.simple_rec_update (r_id BIGINT) RETURNS BOOL AS $$
 196     SELECT reporter.simple_rec_update($1, FALSE);
 197 $$ LANGUAGE SQL;
 198
 199 CREATE OR REPLACE FUNCTION reporter.simple_rec_delete (r_id BIGINT) RETURNS BOOL AS $$
 200     SELECT reporter.simple_rec_update($1, TRUE);
 201 $$ LANGUAGE SQL;
 202
 203 CREATE OR REPLACE FUNCTION reporter.simple_rec_trigger () RETURNS TRIGGER AS $func$
 204 BEGIN
 205     IF TG_OP = 'DELETE' THEN
 206         PERFORM reporter.simple_rec_delete(NEW.id);
 207     ELSE
 208         PERFORM reporter.simple_rec_update(NEW.id);
 209     END IF;
 210
 211     RETURN NEW;
 212 END;
 213 $func$ LANGUAGE PLPGSQL;
 214
 215 CREATE OR REPLACE FUNCTION reporter.disable_materialized_simple_record_trigger () RETURNS VOID AS $$
 216     DROP TRIGGER IF EXISTS bbb_simple_rec_trigger ON biblio.record_entry;
 217 $$ LANGUAGE SQL;
 218
 219 CREATE OR REPLACE FUNCTION reporter.enable_materialized_simple_record_trigger () RETURNS VOID AS $$
 220
 221     TRUNCATE TABLE reporter.materialized_simple_record;
 222
 223     INSERT INTO reporter.materialized_simple_record
 224         (id,fingerprint,quality,tcn_source,tcn_value,title,author,publisher,pubdate,isbn,issn)
 225         SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record;
 226
 227     CREATE TRIGGER bbb_simple_rec_trigger
 228         AFTER INSERT OR UPDATE OR DELETE ON biblio.record_entry
 229         FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger();
 230
 231 $$ LANGUAGE SQL;
 232
 233 CREATE OR REPLACE FUNCTION reporter.refresh_materialized_simple_record () RETURNS VOID AS $$
 234     SELECT reporter.disable_materialized_simple_record_trigger();
 235     SELECT reporter.enable_materialized_simple_record_trigger();
 236 $$ LANGUAGE SQL;
 237
 238 CREATE OR REPLACE VIEW reporter.asset_call_number_dewey AS
 239   SELECT id AS call_number,
 240     call_number_dewey(label) AS dewey,
 241     CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text
 242       THEN btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text))
 243       ELSE NULL::text
 244     END AS dewey_block_tens,
 245     CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text
 246       THEN btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text))
 247       ELSE NULL::text
 248     END AS dewey_block_hundreds,
 249     CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text
 250       THEN (btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text)) || '-'::text)
 251       || btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision) + 9::double precision, '000'::text))
 252       ELSE NULL::text
 253     END AS dewey_range_tens,
 254     CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text
 255       THEN (btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text)) || '-'::text)
 256       || btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision) + 99::double precision, '000'::text))
 257       ELSE NULL::text
 258     END AS dewey_range_hundreds
 259   FROM asset.call_number
 260   WHERE call_number_dewey(label) ~ '^[0-9]'::text;
 261
 262 CREATE OR REPLACE VIEW reporter.demographic AS
 263 SELECT  u.id,
 264     u.dob,
 265     CASE
 266         WHEN u.dob IS NULL
 267             THEN 'Adult'
 268         WHEN AGE(u.dob) > '18 years'::INTERVAL
 269             THEN 'Adult'
 270         ELSE 'Juvenile'
 271     END AS general_division,
 272     CASE
 273         WHEN u.dob IS NULL
 274             THEN 'No Date of Birth Entered'::text
 275         WHEN age(u.dob::timestamp with time zone) >= '0 years'::interval and age(u.dob::timestamp with time zone) < '6 years'::interval
 276             THEN 'Child 0-5 Years Old'::text
 277         WHEN age(u.dob::timestamp with time zone) >= '6 years'::interval and age(u.dob::timestamp with time zone) < '13 years'::interval
 278             THEN 'Child 6-12 Years Old'::text
 279         WHEN age(u.dob::timestamp with time zone) >= '13 years'::interval and age(u.dob::timestamp with time zone) < '18 years'::interval
 280             THEN 'Teen 13-17 Years Old'::text
 281         WHEN age(u.dob::timestamp with time zone) >= '18 years'::interval and age(u.dob::timestamp with time zone) < '26 years'::interval
 282             THEN 'Adult 18-25 Years Old'::text
 283         WHEN age(u.dob::timestamp with time zone) >= '26 years'::interval and age(u.dob::timestamp with time zone) < '50 years'::interval
 284             THEN 'Adult 26-49 Years Old'::text
 285         WHEN age(u.dob::timestamp with time zone) >= '50 years'::interval and age(u.dob::timestamp with time zone) < '60 years'::interval
 286             THEN 'Adult 50-59 Years Old'::text
 287         WHEN age(u.dob::timestamp with time zone) >= '60 years'::interval and age(u.dob::timestamp with time zone) < '70  years'::interval
 288             THEN 'Adult 60-69 Years Old'::text
 289         WHEN age(u.dob::timestamp with time zone) >= '70 years'::interval
 290             THEN 'Adult 70+'::text
 291         ELSE NULL::text
 292     END AS age_division
 293     FROM actor.usr u;
 294
 295 CREATE OR REPLACE VIEW reporter.circ_type AS
 296 SELECT  id,
 297         CASE WHEN opac_renewal OR phone_renewal OR desk_renewal OR auto_renewal
 298                 THEN 'RENEWAL'
 299                 ELSE 'CHECKOUT'
 300         END AS "type"
 301   FROM  action.circulation;
 302
 303 -- rhrr needs to be a real table, so it can be fast. To that end, we use
 304 -- a materialized view updated via a trigger.
 305 CREATE TABLE reporter.hold_request_record  AS
 306 SELECT  id,
 307         target,
 308         hold_type,
 309         CASE
 310                 WHEN hold_type = 'T'
 311                         THEN target
 312                 WHEN hold_type = 'I'
 313                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target)
 314                 WHEN hold_type = 'V'
 315                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
 316                 WHEN hold_type IN ('C','R','F')
 317                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
 318                 WHEN hold_type = 'M'
 319                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
 320                 WHEN hold_type = 'P'
 321                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target)
 322         END AS bib_record
 323   FROM  action.hold_request ahr;
 324
 325 CREATE UNIQUE INDEX reporter_hold_request_record_pkey_idx ON reporter.hold_request_record (id);
 326 CREATE INDEX reporter_hold_request_record_bib_record_idx ON reporter.hold_request_record (bib_record);
 327
 328 ALTER TABLE reporter.hold_request_record ADD PRIMARY KEY USING INDEX reporter_hold_request_record_pkey_idx;
 329
 330 CREATE OR REPLACE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
 331 BEGIN
 332     IF TG_OP = 'INSERT' THEN
 333         INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
 334         SELECT  NEW.id,
 335                 NEW.target,
 336                 NEW.hold_type,
 337                 CASE
 338                     WHEN NEW.hold_type = 'T'
 339                         THEN NEW.target
 340                     WHEN NEW.hold_type = 'I'
 341                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
 342                     WHEN NEW.hold_type = 'V'
 343                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
 344                     WHEN NEW.hold_type IN ('C','R','F')
 345                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
 346                     WHEN NEW.hold_type = 'M'
 347                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
 348                     WHEN NEW.hold_type = 'P'
 349                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
 350                 END AS bib_record;
 351     ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
 352         UPDATE  reporter.hold_request_record
 353           SET   target = NEW.target,
 354                 hold_type = NEW.hold_type,
 355                 bib_record = CASE
 356                     WHEN NEW.hold_type = 'T'
 357                         THEN NEW.target
 358                     WHEN NEW.hold_type = 'I'
 359                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
 360                     WHEN NEW.hold_type = 'V'
 361                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
 362                     WHEN NEW.hold_type IN ('C','R','F')
 363                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
 364                     WHEN NEW.hold_type = 'M'
 365                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
 366                     WHEN NEW.hold_type = 'P'
 367                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
 368                 END
 369          WHERE  id = NEW.id;
 370     END IF;
 371     RETURN NEW;
 372 END;
 373 $$ LANGUAGE PLPGSQL;
 374
 375 CREATE TRIGGER reporter_hold_request_record_trigger AFTER INSERT OR UPDATE ON action.hold_request
 376     FOR EACH ROW EXECUTE PROCEDURE reporter.hold_request_record_mapper();
 377
 378 CREATE OR REPLACE VIEW reporter.xact_billing_totals AS
 379 SELECT  b.xact,
 380         SUM( CASE WHEN b.voided THEN 0 ELSE amount END ) as unvoided,
 381         SUM( CASE WHEN b.voided THEN amount ELSE 0 END ) as voided,
 382         SUM( amount ) as total
 383   FROM  money.billing b
 384   GROUP BY 1;
 385
 386 CREATE OR REPLACE VIEW reporter.xact_paid_totals AS
 387 SELECT  b.xact,
 388         SUM( CASE WHEN b.voided THEN 0 ELSE amount END ) as unvoided,
 389         SUM( CASE WHEN b.voided THEN amount ELSE 0 END ) as voided,
 390         SUM( amount ) as total
 391   FROM  money.payment b
 392   GROUP BY 1;
 393
 394 CREATE OR REPLACE VIEW reporter.overdue_circs AS
 395 SELECT  *
 396   FROM  "action".circulation
 397   WHERE checkin_time is null
 398         AND (stop_fines NOT IN ('LOST','CLAIMSRETURNED') OR stop_fines IS NULL)
 399         AND due_date < now();
 400
 401 CREATE OR REPLACE VIEW reporter.overdue_reports AS
 402  SELECT s.id, c.barcode AS runner_barcode, r.name, s.run_time, s.run_time - now() AS scheduled_wait_time
 403    FROM reporter.schedule s
 404    JOIN reporter.report r ON r.id = s.report
 405    JOIN actor.usr u ON s.runner = u.id
 406    JOIN actor.card c ON c.id = u.card
 407   WHERE s.start_time IS NULL AND s.run_time < now();
 408
 409 CREATE OR REPLACE VIEW reporter.pending_reports AS
 410  SELECT s.id, c.barcode AS runner_barcode, r.name, s.run_time, s.run_time - now() AS scheduled_wait_time
 411    FROM reporter.schedule s
 412    JOIN reporter.report r ON r.id = s.report
 413    JOIN actor.usr u ON s.runner = u.id
 414    JOIN actor.card c ON c.id = u.card
 415   WHERE s.start_time IS NULL;
 416
 417 CREATE OR REPLACE VIEW reporter.currently_running AS
 418  SELECT s.id, c.barcode AS runner_barcode, r.name, s.run_time, s.run_time - now() AS scheduled_wait_time
 419    FROM reporter.schedule s
 420    JOIN reporter.report r ON r.id = s.report
 421    JOIN actor.usr u ON s.runner = u.id
 422    JOIN actor.card c ON c.id = u.card
 423   WHERE s.start_time IS NOT NULL AND s.complete_time IS NULL;
 424
 425 CREATE OR REPLACE VIEW reporter.completed_reports AS
 426   SELECT s.id AS run,
 427          r.id AS report,
 428          t.id AS template,
 429          t.owner AS template_owner,
 430          r.owner AS report_owner,
 431          s.runner AS runner,
 432          t.folder AS template_folder,
 433          r.folder AS report_folder,
 434          s.folder AS output_folder,
 435          r.name AS report_name,
 436          t.name AS template_name,
 437          s.start_time,
 438          s.run_time,
 439          s.complete_time,
 440          s.error_code,
 441          s.error_text
 442   FROM reporter.schedule s
 443     JOIN reporter.report r ON r.id = s.report
 444     JOIN reporter.template t ON t.id = r.template
 445   WHERE s.complete_time IS NOT NULL;
 446
 447 CREATE OR REPLACE FUNCTION reporter.intersect_user_perm_ou(context_ou BIGINT, staff_id BIGINT, perm_code TEXT)
 448 RETURNS BOOLEAN AS $$
 449   SELECT CASE WHEN context_ou IN (SELECT * FROM permission.usr_has_perm_at_all(staff_id::INT, perm_code)) THEN TRUE ELSE FALSE END;
 450 $$ LANGUAGE SQL;
 451
 452 COMMIT;
 453