Open-ILS/src/sql/Pg/example.reporter-extension.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 BEGIN;
  19
  20 CREATE OR REPLACE VIEW reporter.classic_current_circ AS
  21 SELECT  cl.shortname AS circ_lib,
  22         cl.id AS circ_lib_id,
  23         circ.xact_start AS xact_start,
  24         circ_type.type AS circ_type,
  25         cp.id AS copy_id,
  26         cp.circ_modifier,
  27         ol.shortname AS owning_lib_name,
  28         lm.value AS language,
  29         lfm.value AS lit_form,
  30         ifm.value AS item_form,
  31         itm.value AS item_type,
  32         sl.name AS shelving_location,
  33         p.id AS patron_id,
  34         g.name AS profile_group,
  35         dem.general_division AS demographic_general_division,
  36         circ.id AS id,
  37         cn.id AS call_number,
  38         cn.label AS call_number_label,
  39         call_number_dewey(cn.label) AS dewey,
  40         CASE
  41                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
  42                         THEN
  43                                 btrim(
  44                                         to_char(
  45                                                 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
  46                                         )
  47                                 )
  48                 ELSE NULL
  49         END AS dewey_block_tens,
  50         CASE
  51                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
  52                         THEN
  53                                 btrim(
  54                                         to_char(
  55                                                 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
  56                                         )
  57                                 )
  58                 ELSE NULL
  59         END AS dewey_block_hundreds,
  60         CASE
  61                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
  62                         THEN
  63                                 btrim(
  64                                         to_char(
  65                                                 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
  66                                         )
  67                                 )
  68                                 || '-' ||
  69                                 btrim(
  70                                         to_char(
  71                                                 10 * floor((call_number_dewey(cn.label)::float) / 10) + 9, '000'
  72                                         )
  73                                 )
  74                 ELSE NULL
  75         END AS dewey_range_tens,
  76         CASE
  77                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
  78                         THEN
  79                                 btrim(
  80                                         to_char(
  81                                                 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
  82                                         )
  83                                 )
  84                                 || '-' ||
  85                                 btrim(
  86                                         to_char(
  87                                                 100 * floor((call_number_dewey(cn.label)::float) / 100) + 99, '000'
  88                                         )
  89                                 )
  90                 ELSE NULL
  91         END AS dewey_range_hundreds,
  92         hl.id AS patron_home_lib,
  93         hl.shortname AS patron_home_lib_shortname,
  94         paddr.county AS patron_county,
  95         paddr.city AS patron_city,
  96         paddr.post_code AS patron_zip,
  97         sc1.stat_cat_entry AS stat_cat_1,
  98         sc2.stat_cat_entry AS stat_cat_2,
  99         sce1.value AS stat_cat_1_value,
 100         sce2.value AS stat_cat_2_value
 101   FROM  action.circulation circ
 102         JOIN reporter.circ_type circ_type ON (circ.id = circ_type.id)
 103         JOIN asset.copy cp ON (cp.id = circ.target_copy)
 104         JOIN asset.copy_location sl ON (cp.location = sl.id)
 105         JOIN asset.call_number cn ON (cp.call_number = cn.id)
 106         JOIN actor.org_unit ol ON (cn.owning_lib = ol.id)
 107         JOIN metabib.rec_descriptor rd ON (rd.record = cn.record)
 108         JOIN actor.org_unit cl ON (circ.circ_lib = cl.id)
 109         JOIN actor.usr p ON (p.id = circ.usr)
 110         JOIN actor.org_unit hl ON (p.home_ou = hl.id)
 111         JOIN permission.grp_tree g ON (p.profile = g.id)
 112         JOIN reporter.demographic dem ON (dem.id = p.id)
 113         LEFT JOIN actor.usr_address paddr ON (paddr.id = p.billing_address)
 114         LEFT JOIN config.language_map lm ON (rd.item_lang = lm.code)
 115         LEFT JOIN config.lit_form_map lfm ON (rd.lit_form = lfm.code)
 116         LEFT JOIN config.item_form_map ifm ON (rd.item_form = ifm.code)
 117         LEFT JOIN config.item_type_map itm ON (rd.item_type = itm.code)
 118         LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON (sc1.owning_copy = cp.id AND sc1.stat_cat = 1)
 119         LEFT JOIN asset.stat_cat_entry sce1 ON (sce1.id = sc1.stat_cat_entry)
 120         LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2)
 121         LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry);
 122
 123 CREATE OR REPLACE VIEW reporter.legacy_cat1 AS
 124 SELECT  id,
 125         owner,
 126         value
 127   FROM  asset.stat_cat_entry
 128   WHERE stat_cat = 1;
 129
 130 CREATE OR REPLACE VIEW reporter.legacy_cat2 AS
 131 SELECT  id,
 132         owner,
 133         value
 134   FROM  asset.stat_cat_entry
 135   WHERE stat_cat = 2;
 136
 137
 138 CREATE OR REPLACE VIEW reporter.classic_current_billing_summary AS
 139 SELECT  x.id AS id,
 140         x.usr AS usr,
 141         bl.shortname AS billing_location_shortname,
 142         bl.name AS billing_location_name,
 143         x.billing_location AS billing_location,
 144         c.barcode AS barcode,
 145         u.home_ou AS usr_home_ou,
 146         ul.shortname AS usr_home_ou_shortname,
 147         ul.name AS usr_home_ou_name,
 148         x.xact_start AS xact_start,
 149         x.xact_finish AS xact_finish,
 150         x.xact_type AS xact_type,
 151         x.total_paid AS total_paid,
 152         x.total_owed AS total_owed,
 153         x.balance_owed AS balance_owed,
 154         x.last_payment_ts AS last_payment_ts,
 155         x.last_payment_note AS last_payment_note,
 156         x.last_payment_type AS last_payment_type,
 157         x.last_billing_ts AS last_billing_ts,
 158         x.last_billing_note AS last_billing_note,
 159         x.last_billing_type AS last_billing_type,
 160         paddr.county AS patron_county,
 161         paddr.city AS patron_city,
 162         paddr.post_code AS patron_zip,
 163         g.name AS profile_group,
 164         dem.general_division AS demographic_general_division
 165   FROM  money.open_billable_xact_summary x
 166         JOIN actor.org_unit bl ON (x.billing_location = bl.id)
 167         JOIN actor.usr u ON (u.id = x.usr)
 168         JOIN actor.org_unit ul ON (u.home_ou = ul.id)
 169         JOIN actor.card c ON (u.card = c.id)
 170         JOIN permission.grp_tree g ON (u.profile = g.id)
 171         JOIN reporter.demographic dem ON (dem.id = u.id)
 172         JOIN actor.usr_address paddr ON (paddr.id = u.billing_address);
 173
 174 CREATE OR REPLACE VIEW reporter.classic_item_list AS
 175 SELECT rmsr.title,
 176     rmsr.author,
 177     rmsr.pubdate,
 178     cp.id,
 179     cp.price,
 180     cp.barcode,
 181     cn.label as call_number_label,
 182     CASE
 183         WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
 184             THEN
 185                 btrim(
 186                     to_char(
 187                         10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
 188                     )
 189                 )
 190         ELSE NULL
 191     END AS dewey_block_tens,
 192     CASE
 193         WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
 194             THEN
 195                 btrim(
 196                     to_char(
 197                         100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
 198                     )
 199                 )
 200         ELSE NULL
 201     END AS dewey_block_hundreds,
 202     erfcc.circ_count as use_count,
 203     cp.circ_modifier,
 204     sl.name AS shelving_location,
 205     sc1.stat_cat_entry AS stat_cat_1,
 206     sc2.stat_cat_entry AS stat_cat_2,
 207     sce1.value AS stat_cat_1_value,
 208     sce2.value AS stat_cat_2_value,
 209     cp.edit_date,
 210     cp.create_date,
 211     ol.shortname AS owning_lib_name,
 212     cn.owning_lib,
 213     cl.shortname AS circ_lib_name,
 214     cl.id AS circ_lib,
 215     cp.creator,
 216     cp.age_protect,
 217     cp.opac_visible,
 218     cp.ref,
 219     cp.deposit_amount,
 220     cp.deleted,
 221     rmsr.tcn_value,
 222     cp.status,
 223     circ.stop_fines,
 224     circ.due_date,
 225     circ_card.barcode as patron_barcode,
 226     circ_u.first_given_name || ' ' || circ_u.family_name as patron_name
 227   FROM  asset.copy cp
 228     JOIN extend_reporter.full_circ_count erfcc ON (cp.id = erfcc.id)
 229     JOIN asset.copy_location sl ON (cp.location = sl.id)
 230     JOIN asset.call_number cn ON (cp.call_number = cn.id)
 231     JOIN actor.org_unit ol ON (cn.owning_lib = ol.id)
 232     JOIN actor.org_unit cl ON (cp.circ_lib = cl.id)
 233     JOIN reporter.materialized_simple_record rmsr ON (cn.record = rmsr.id)
 234     LEFT JOIN action.circulation circ ON (circ.target_copy = cp .id AND circ.checkin_time IS NULL)
 235     LEFT JOIN actor.usr circ_u ON (circ_u.id = circ.usr)
 236     LEFT JOIN actor.card circ_card ON (circ_u.card = circ_card.id)
 237     LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON (sc1.owning_copy = cp.id AND sc1.stat_cat = 1)
 238     LEFT JOIN asset.stat_cat_entry sce1 ON (sce1.id = sc1.stat_cat_entry)
 239     LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2)
 240     LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry);
 241
 242
 243 CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS
 244         SELECT  circ.id,
 245                 cn.owning_lib,
 246                 bill.billing_type,
 247                 SUM(bill.amount) AS billed
 248           FROM  action.circulation circ
 249                 JOIN money.billing bill ON (circ.id = bill.xact)
 250                 JOIN asset.copy cp ON (circ.target_copy = cp.id)
 251                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 252           WHERE circ.xact_finish IS NULL
 253                 AND NOT bill.voided
 254           GROUP BY 1,2,3
 255           ORDER BY 1,2,3;
 256
 257 CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS
 258         SELECT  owning_lib,
 259                 STRING_AGG(DISTINCT billing_type, ', ') AS billing_types,
 260                 SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
 261           FROM  money.open_circ_balance_by_owning_lib x
 262           GROUP BY 1;
 263
 264
 265
 266
 267
 268 CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS
 269         SELECT  circ.id,
 270                 circ.circ_lib,
 271                 cn.owning_lib,
 272                 bill.billing_type,
 273                 SUM(bill.amount) AS billed
 274           FROM  action.circulation circ
 275                 JOIN money.billing bill ON (circ.id = bill.xact)
 276                 JOIN asset.copy cp ON (circ.target_copy = cp.id)
 277                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 278           WHERE circ.xact_finish IS NULL
 279                 AND NOT bill.voided
 280           GROUP BY 1,2,3,4
 281           ORDER BY 1,2,3,4;
 282
 283 CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS
 284         SELECT  circ_lib,
 285                 owning_lib,
 286                 STRING_AGG(DISTINCT billing_type, ', ') AS billing_types,
 287                 SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
 288           FROM  money.open_circ_balance_by_circ_and_owning_lib x
 289           GROUP BY 1,2;
 290
 291
 292
 293
 294
 295 CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS
 296         SELECT  circ.id,
 297                 usr.home_ou,
 298                 cn.owning_lib,
 299                 bill.billing_type,
 300                 SUM(bill.amount) AS billed
 301           FROM  action.circulation circ
 302                 JOIN money.billing bill ON (circ.id = bill.xact)
 303                 JOIN asset.copy cp ON (circ.target_copy = cp.id)
 304                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 305                 JOIN actor.usr usr ON (circ.usr = usr.id)
 306           WHERE circ.xact_finish IS NULL
 307                 AND NOT bill.voided
 308           GROUP BY 1,2,3,4
 309           ORDER BY 1,2,3,4;
 310
 311 CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS
 312         SELECT  home_ou,
 313                 owning_lib,
 314                 STRING_AGG(DISTINCT billing_type, ', ') AS billing_types,
 315                 SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
 316           FROM  money.open_circ_balance_by_usr_home_and_owning_lib x
 317           GROUP BY 1,2;
 318
 319 CREATE OR REPLACE VIEW reporter.copy_statistics_view AS
 320 SELECT    acp.id as copy_id,
 321         owning_lib.id as owning_lib_id,
 322     -- last circulation date in corsortium
 323     (SELECT COALESCE(MAX(xact_start), NULL)
 324      FROM action.all_circulation
 325      WHERE target_copy = acp.id
 326     ) AS consortium_last_circ_date,
 327     -- last circulation date in system
 328     (SELECT COALESCE(MAX(xact_start), NULL)
 329      FROM action.all_circulation
 330      WHERE target_copy = acp.id
 331      AND circ_lib in (
 332         SELECT id
 333         FROM actor.org_unit
 334         WHERE parent_ou = owning_lib.parent_ou
 335         )
 336     ) AS system_last_circ_date,
 337     -- last circulation date in branch
 338     (SELECT COALESCE(MAX(xact_start), NULL)
 339      FROM action.all_circulation
 340      WHERE target_copy = acp.id
 341      AND circ_lib = owning_lib.id
 342     ) AS branch_last_circ_date,
 343     -- last checkin date in corsortium
 344     (SELECT COALESCE(MAX(checkin_time), NULL)
 345      FROM action.all_circulation
 346      WHERE target_copy = acp.id
 347     ) AS consortium_last_checkin_date,
 348     -- last checkin date in system
 349     (SELECT COALESCE(MAX(checkin_time), NULL)
 350      FROM action.all_circulation
 351      WHERE target_copy = acp.id
 352      AND circ_lib in (
 353         SELECT id
 354         FROM actor.org_unit
 355         WHERE parent_ou = owning_lib.parent_ou
 356         )
 357     ) AS system_last_checkin_date,
 358     -- last checkin date in branch
 359     (SELECT COALESCE(MAX(checkin_time), NULL)
 360      FROM action.all_circulation
 361      WHERE target_copy = acp.id
 362      AND circ_lib = owning_lib.id
 363     ) AS branch_last_checkin_date,
 364     -- last due date in consortium
 365     (SELECT COALESCE(MAX(due_date), NULL)
 366      FROM action.all_circulation
 367      WHERE target_copy = acp.id
 368     ) AS consortium_last_due_date,
 369     -- last due date in system
 370     (SELECT COALESCE(MAX(due_date), NULL)
 371      FROM action.all_circulation
 372      WHERE target_copy = acp.id
 373      AND circ_lib in (
 374         SELECT id
 375         FROM actor.org_unit
 376         WHERE parent_ou = owning_lib.parent_ou
 377         )
 378     ) AS system_last_due_date,
 379     -- last due date in branch
 380     (SELECT COALESCE(MAX(due_date), NULL)
 381      FROM action.all_circulation
 382      WHERE target_copy = acp.id
 383      AND circ_lib = owning_lib.id
 384     ) AS branch_last_due_date,
 385     -- month-to-date circ in corsortium
 386     (SELECT count(*)
 387      FROM action.all_circulation
 388      WHERE EXTRACT(YEAR FROM xact_start) = EXTRACT(YEAR FROM now())
 389      AND EXTRACT(MONTH FROM xact_start) = EXTRACT(MONTH FROM now())
 390      AND target_copy = acp.id
 391     ) AS consortium_month_to_date_circ,
 392     -- month-to-date circ in system
 393     (SELECT count(*)
 394      FROM action.all_circulation
 395      WHERE EXTRACT(YEAR FROM xact_start) = EXTRACT(YEAR FROM now())
 396      AND EXTRACT(MONTH FROM xact_start) = EXTRACT(MONTH FROM now())
 397      AND target_copy = acp.id
 398      AND circ_lib in (
 399        SELECT id
 400        FROM actor.org_unit
 401        WHERE parent_ou = owning_lib.parent_ou
 402        )
 403     ) AS system_month_to_date_circ,
 404     -- month-to-date circ in branch
 405     (SELECT count(*)
 406      FROM action.all_circulation
 407      WHERE EXTRACT(YEAR FROM xact_start) = EXTRACT(YEAR FROM now())
 408      AND EXTRACT(MONTH FROM xact_start) = EXTRACT(MONTH FROM now())
 409      AND target_copy = acp.id
 410      AND circ_lib = owning_lib.id
 411     ) AS branch_month_to_date_circ,
 412     -- year-to-date circ in consortium
 413     (SELECT count(*)
 414      FROM action.all_circulation
 415      WHERE EXTRACT(YEAR FROM xact_start) = EXTRACT(YEAR FROM now())
 416      AND target_copy = acp.id
 417     ) AS consortium_year_to_date_circ,
 418     -- year-to-date circ in system
 419     (SELECT count(*)
 420      FROM action.all_circulation
 421      WHERE EXTRACT(YEAR FROM xact_start) = EXTRACT(YEAR FROM now())
 422      AND target_copy = acp.id
 423      AND circ_lib in (
 424        SELECT id
 425        FROM actor.org_unit
 426        WHERE parent_ou = owning_lib.parent_ou
 427        )
 428     ) AS system_year_to_date_circ,
 429     -- year-to-date circ in branch
 430     (SELECT count(*)
 431      FROM action.all_circulation
 432      WHERE EXTRACT(YEAR FROM xact_start) = EXTRACT(YEAR FROM now())
 433      AND target_copy = acp.id
 434      AND circ_lib = owning_lib.id
 435     ) AS branch_year_to_date_circ,
 436     -- lifetime circ in consortium
 437     erfcc.circ_count AS consortium_lifetime_circ,
 438     -- lifetime circ in system
 439     (SELECT count(*)
 440      FROM action.all_circulation
 441      WHERE target_copy = acp.id
 442      AND circ_lib in (
 443         SELECT id
 444         FROM actor.org_unit
 445         WHERE parent_ou = owning_lib.parent_ou
 446         )
 447     ) AS system_lifetime_circ,
 448     -- lifetime circ in branch
 449     (SELECT count(*)
 450      FROM action.all_circulation
 451      WHERE target_copy = acp.id
 452      AND circ_lib = owning_lib.id
 453     ) AS branch_lifetime_circ,
 454     -- current title hold count in consortium
 455     (SELECT count(*)
 456      FROM action.hold_request ahr
 457      WHERE cancel_time IS NULL
 458      AND expire_time > now()
 459      AND fulfillment_time IS NULL
 460      AND target = bre.id
 461      AND hold_type = 'T'
 462     ) AS consortium_current_title_hold_count,
 463     -- current title hold count in system
 464     (SELECT count(*)
 465      FROM action.hold_request ahr
 466      WHERE cancel_time IS NULL
 467      AND expire_time > now()
 468      AND fulfillment_time IS NULL
 469      AND target = bre.id
 470      AND hold_type = 'T'
 471      AND pickup_lib IN (
 472         SELECT id
 473         FROM actor.org_unit
 474         WHERE parent_ou = owning_lib.parent_ou
 475         )
 476     ) AS system_current_title_hold_count,
 477     -- current title hold count in branch
 478     (SELECT count(*)
 479      FROM action.hold_request ahr
 480      WHERE cancel_time IS NULL
 481      AND expire_time > now()
 482      AND fulfillment_time IS NULL
 483      AND target = bre.id
 484      AND hold_type = 'T'
 485      AND pickup_lib = owning_lib.parent_ou
 486     ) AS branch_current_title_hold_count,
 487     -- consortium lifetime holds
 488     (SELECT count(*)
 489      FROM action.all_hold_request
 490      WHERE current_copy = acp.id) AS consortium_lifetime_holds,
 491     -- system lifetime holds
 492     (SELECT count(*)
 493      FROM action.all_hold_request
 494      WHERE current_copy = acp.id
 495      AND pickup_lib IN (
 496         SELECT id
 497         FROM actor.org_unit
 498         WHERE parent_ou = owning_lib.parent_ou
 499         )
 500     ) AS system_lifetime_holds,
 501     -- branch lifetime holds
 502     (SELECT count(*)
 503      FROM action.all_hold_request
 504      WHERE current_copy = acp.id
 505      AND pickup_lib = owning_lib.parent_ou
 506     ) AS branch_lifetime_holds,
 507     -- consortium lifetime transits
 508     (SELECT count(*)
 509      FROM action.transit_copy
 510      WHERE target_copy = acp.id
 511     ) AS consortium_lifetime_transits,
 512     -- system lifetime transits
 513     (SELECT count(*)
 514      FROM action.transit_copy
 515      WHERE target_copy = acp.id
 516      AND source IN (
 517         SELECT id
 518         FROM actor.org_unit
 519         WHERE parent_ou = owning_lib.parent_ou
 520         )
 521     ) AS system_lifetime_transits,
 522     -- branch lifetime transits
 523     (SELECT count(*)
 524      FROM action.transit_copy
 525      WHERE target_copy = acp.id
 526      AND source = owning_lib.id
 527     ) AS branch_lifetime_transits
 528 FROM    asset.copy acp
 529     INNER JOIN asset.call_number acn ON (acp.call_number = acn.id)
 530     INNER JOIN biblio.record_entry bre ON (acn.record = bre.id)
 531     INNER JOIN actor.org_unit owning_lib ON (acn.owning_lib = owning_lib.id)
 532     LEFT OUTER JOIN extend_reporter.full_circ_count erfcc ON (erfcc.id = acp.id);
 533
 534 COMMIT;