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         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  t.value as title,
 176     a.value as author,
 177     p.value as 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     (SELECT COUNT(*) FROM action.circulation WHERE target_copy = cp.id) 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     b.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 asset.copy_location sl ON (cp.location = sl.id)
 229     JOIN asset.call_number cn ON (cp.call_number = cn.id)
 230     JOIN biblio.record_entry b ON (cn.record = b.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     LEFT JOIN metabib.full_rec t ON (cn.record = t.record AND t.tag = '245' and t.subfield = 'a')
 234     LEFT JOIN metabib.full_rec a ON (cn.record = a.record AND a.tag = '100' and a.subfield = 'a')
 235     LEFT JOIN metabib.full_rec p ON (cn.record = p.record AND p.tag = '260' and p.subfield = 'c')
 236     LEFT JOIN action.circulation circ ON (circ.target_copy = cp .id AND circ.checkin_time IS NULL)
 237     LEFT JOIN actor.usr circ_u ON (circ_u.id = circ.usr)
 238     LEFT JOIN actor.card circ_card ON (circ_u.id = circ_card.usr)
 239     LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON (sc1.owning_copy = cp.id AND sc1.stat_cat = 1)
 240     LEFT JOIN asset.stat_cat_entry sce1 ON (sce1.id = sc1.stat_cat_entry)
 241     LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2)
 242     LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry);
 243
 244
 245 CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS
 246         SELECT  circ.id,
 247                 cn.owning_lib,
 248                 bill.billing_type,
 249                 SUM(bill.amount) AS billed
 250           FROM  action.circulation circ
 251                 JOIN money.billing bill ON (circ.id = bill.xact)
 252                 JOIN asset.copy cp ON (circ.target_copy = cp.id)
 253                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 254           WHERE circ.xact_finish IS NULL
 255                 AND NOT bill.voided
 256           GROUP BY 1,2,3
 257           ORDER BY 1,2,3;
 258
 259 CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS
 260         SELECT  owning_lib,
 261                 ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types,
 262                 SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
 263           FROM  money.open_circ_balance_by_owning_lib x
 264           GROUP BY 1;
 265
 266
 267
 268
 269
 270 CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS
 271         SELECT  circ.id,
 272                 circ.circ_lib,
 273                 cn.owning_lib,
 274                 bill.billing_type,
 275                 SUM(bill.amount) AS billed
 276           FROM  action.circulation circ
 277                 JOIN money.billing bill ON (circ.id = bill.xact)
 278                 JOIN asset.copy cp ON (circ.target_copy = cp.id)
 279                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 280           WHERE circ.xact_finish IS NULL
 281                 AND NOT bill.voided
 282           GROUP BY 1,2,3,4
 283           ORDER BY 1,2,3,4;
 284
 285 CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS
 286         SELECT  circ_lib,
 287                 owning_lib,
 288                 ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types,
 289                 SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
 290           FROM  money.open_circ_balance_by_circ_and_owning_lib x
 291           GROUP BY 1,2;
 292
 293
 294
 295
 296
 297 CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS
 298         SELECT  circ.id,
 299                 usr.home_ou,
 300                 cn.owning_lib,
 301                 bill.billing_type,
 302                 SUM(bill.amount) AS billed
 303           FROM  action.circulation circ
 304                 JOIN money.billing bill ON (circ.id = bill.xact)
 305                 JOIN asset.copy cp ON (circ.target_copy = cp.id)
 306                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 307                 JOIN actor.usr usr ON (circ.usr = usr.id)
 308           WHERE circ.xact_finish IS NULL
 309                 AND NOT bill.voided
 310           GROUP BY 1,2,3,4
 311           ORDER BY 1,2,3,4;
 312
 313 CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS
 314         SELECT  home_ou,
 315                 owning_lib,
 316                 ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types,
 317                 SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
 318           FROM  money.open_circ_balance_by_usr_home_and_owning_lib x
 319           GROUP BY 1,2;
 320
 321 COMMIT;
 322
 323