Open-ILS/src/sql/Pg/example.reporter-extension.sql

   1 BEGIN;
   2
   3 CREATE OR REPLACE VIEW reporter.classic_current_circ AS
   4 SELECT  cl.shortname AS circ_lib,
   5         cl.id AS circ_lib_id,
   6         circ.xact_start AS xact_start,
   7         circ_type.type AS circ_type,
   8         cp.id AS copy_id,
   9         cp.circ_modifier,
  10         ol.shortname AS owning_lib_name,
  11         lm.value AS language,
  12         lfm.value AS lit_form,
  13         ifm.value AS item_form,
  14         itm.value AS item_type,
  15         sl.name AS shelving_location,
  16         p.id AS patron_id,
  17         g.name AS profile_group,
  18         dem.general_division AS demographic_general_division,
  19         circ.id AS id,
  20         cn.id AS call_number,
  21         cn.label AS call_number_label,
  22         call_number_dewey(cn.label) AS dewey,
  23         CASE
  24                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
  25                         THEN
  26                                 btrim(
  27                                         to_char(
  28                                                 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
  29                                         )
  30                                 )
  31                 ELSE NULL
  32         END AS dewey_block_tens,
  33         CASE
  34                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
  35                         THEN
  36                                 btrim(
  37                                         to_char(
  38                                                 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
  39                                         )
  40                                 )
  41                 ELSE NULL
  42         END AS dewey_block_hundreds,
  43         CASE
  44                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
  45                         THEN
  46                                 btrim(
  47                                         to_char(
  48                                                 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
  49                                         )
  50                                 )
  51                                 || '-' ||
  52                                 btrim(
  53                                         to_char(
  54                                                 10 * floor((call_number_dewey(cn.label)::float) / 10) + 9, '000'
  55                                         )
  56                                 )
  57                 ELSE NULL
  58         END AS dewey_range_tens,
  59         CASE
  60                 WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
  61                         THEN
  62                                 btrim(
  63                                         to_char(
  64                                                 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
  65                                         )
  66                                 )
  67                                 || '-' ||
  68                                 btrim(
  69                                         to_char(
  70                                                 100 * floor((call_number_dewey(cn.label)::float) / 100) + 99, '000'
  71                                         )
  72                                 )
  73                 ELSE NULL
  74         END AS dewey_range_hundreds,
  75         hl.id AS patron_home_lib,
  76         hl.shortname AS patron_home_lib_shortname,
  77         paddr.county AS patron_county,
  78         paddr.city AS patron_city,
  79         paddr.post_code AS patron_zip,
  80         sc1.stat_cat_entry AS stat_cat_1,
  81         sc2.stat_cat_entry AS stat_cat_2,
  82         sce1.value AS stat_cat_1_value,
  83         sce2.value AS stat_cat_2_value
  84   FROM  action.circulation circ
  85         JOIN reporter.circ_type circ_type ON (circ.id = circ_type.id)
  86         JOIN asset.copy cp ON (cp.id = circ.target_copy)
  87         JOIN asset.copy_location sl ON (cp.location = sl.id)
  88         JOIN asset.call_number cn ON (cp.call_number = cn.id)
  89         JOIN actor.org_unit ol ON (cn.owning_lib = ol.id)
  90         JOIN metabib.rec_descriptor rd ON (rd.record = cn.record)
  91         JOIN actor.org_unit cl ON (circ.circ_lib = cl.id)
  92         JOIN actor.usr p ON (p.id = circ.usr)
  93         JOIN actor.org_unit hl ON (p.home_ou = hl.id)
  94         JOIN permission.grp_tree g ON (p.profile = g.id)
  95         JOIN reporter.demographic dem ON (dem.id = p.id)
  96         JOIN actor.usr_address paddr ON (paddr.id = p.billing_address)
  97         LEFT JOIN config.language_map lm ON (rd.item_lang = lm.code)
  98         LEFT JOIN config.lit_form_map lfm ON (rd.lit_form = lfm.code)
  99         LEFT JOIN config.item_form_map ifm ON (rd.item_form = ifm.code)
 100         LEFT JOIN config.item_type_map itm ON (rd.item_type = itm.code)
 101         LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON (sc1.owning_copy = cp.id AND sc1.stat_cat = 1)
 102         LEFT JOIN asset.stat_cat_entry sce1 ON (sce1.id = sc1.stat_cat_entry)
 103         LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2)
 104         LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry);
 105
 106 CREATE OR REPLACE VIEW reporter.legacy_cat1 AS
 107 SELECT  id,
 108         owner,
 109         value
 110   FROM  asset.stat_cat_entry
 111   WHERE stat_cat = 1;
 112
 113 CREATE OR REPLACE VIEW reporter.legacy_cat2 AS
 114 SELECT  id,
 115         owner,
 116         value
 117   FROM  asset.stat_cat_entry
 118   WHERE stat_cat = 2;
 119
 120
 121 CREATE OR REPLACE VIEW reporter.classic_current_billing_summary AS
 122 SELECT  x.id AS id,
 123         x.usr AS usr,
 124         bl.shortname AS billing_location_shortname,
 125         bl.name AS billing_location_name,
 126         x.billing_location AS billing_location,
 127         c.barcode AS barcode,
 128         u.home_ou AS usr_home_ou,
 129         ul.shortname AS usr_home_ou_shortname,
 130         ul.name AS usr_home_ou_name,
 131         x.xact_start AS xact_start,
 132         x.xact_finish AS xact_finish,
 133         x.xact_type AS xact_type,
 134         x.total_paid AS total_paid,
 135         x.total_owed AS total_owed,
 136         x.balance_owed AS balance_owed,
 137         x.last_payment_ts AS last_payment_ts,
 138         x.last_payment_note AS last_payment_note,
 139         x.last_payment_type AS last_payment_type,
 140         x.last_billing_ts AS last_billing_ts,
 141         x.last_billing_note AS last_billing_note,
 142         x.last_billing_type AS last_billing_type,
 143         paddr.county AS patron_county,
 144         paddr.city AS patron_city,
 145         paddr.post_code AS patron_zip,
 146         g.name AS profile_group,
 147         dem.general_division AS demographic_general_division
 148   FROM  money.open_billable_xact_summary x
 149         JOIN actor.org_unit bl ON (x.billing_location = bl.id)
 150         JOIN actor.usr u ON (u.id = x.usr)
 151         JOIN actor.org_unit ul ON (u.home_ou = ul.id)
 152         JOIN actor.card c ON (u.card = c.id)
 153         JOIN permission.grp_tree g ON (u.profile = g.id)
 154         JOIN reporter.demographic dem ON (dem.id = u.id)
 155         JOIN actor.usr_address paddr ON (paddr.id = u.billing_address);
 156
 157 CREATE OR REPLACE VIEW reporter.classic_item_list AS
 158 SELECT  t.value as title,
 159     a.value as author,
 160     p.value as pubdate,
 161     cp.id,
 162     cp.price,
 163     cp.barcode,
 164     cn.label as call_number_label,
 165     CASE
 166         WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
 167             THEN
 168                 btrim(
 169                     to_char(
 170                         10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
 171                     )
 172                 )
 173         ELSE NULL
 174     END AS dewey_block_tens,
 175     CASE
 176         WHEN call_number_dewey(cn.label) ~  E'^[0-9.]+$'
 177             THEN
 178                 btrim(
 179                     to_char(
 180                         100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
 181                     )
 182                 )
 183         ELSE NULL
 184     END AS dewey_block_hundreds,
 185     (SELECT COUNT(*) FROM action.circulation WHERE target_copy = cp.id) as use_count,
 186     cp.circ_modifier,
 187     sl.name AS shelving_location,
 188     sc1.stat_cat_entry AS stat_cat_1,
 189     sc2.stat_cat_entry AS stat_cat_2,
 190     sce1.value AS stat_cat_1_value,
 191     sce2.value AS stat_cat_2_value,
 192     cp.edit_date,
 193     cp.create_date,
 194     ol.shortname AS owning_lib_name,
 195     cn.owning_lib,
 196     cl.shortname AS circ_lib_name,
 197     cl.id AS circ_lib,
 198     cp.creator,
 199     cp.age_protect,
 200     cp.opac_visible,
 201     cp.ref,
 202     cp.deposit_amount,
 203     cp.deleted,
 204     b.tcn_value,
 205     cp.status,
 206     circ.stop_fines,
 207     circ.due_date,
 208     circ_card.barcode as patron_barcode,
 209     circ_u.first_given_name || ' ' || circ_u.family_name as patron_name
 210   FROM  asset.copy cp
 211     JOIN asset.copy_location sl ON (cp.location = sl.id)
 212     JOIN asset.call_number cn ON (cp.call_number = cn.id)
 213     JOIN biblio.record_entry b ON (cn.record = b.id)
 214     JOIN actor.org_unit ol ON (cn.owning_lib = ol.id)
 215     JOIN actor.org_unit cl ON (cp.circ_lib = cl.id)
 216     LEFT JOIN metabib.full_rec t ON (cn.record = t.record AND t.tag = '245' and t.subfield = 'a')
 217     LEFT JOIN metabib.full_rec a ON (cn.record = a.record AND a.tag = '100' and a.subfield = 'a')
 218     LEFT JOIN metabib.full_rec p ON (cn.record = p.record AND p.tag = '260' and p.subfield = 'c')
 219     LEFT JOIN action.circulation circ ON (circ.target_copy = cp .id AND circ.checkin_time IS NULL)
 220     LEFT JOIN actor.usr circ_u ON (circ_u.id = circ.usr)
 221     LEFT JOIN actor.card circ_card ON (circ_u.id = circ_card.usr)
 222     LEFT JOIN asset.stat_cat_entry_copy_map sc1 ON (sc1.owning_copy = cp.id AND sc1.stat_cat = 1)
 223     LEFT JOIN asset.stat_cat_entry sce1 ON (sce1.id = sc1.stat_cat_entry)
 224     LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2)
 225     LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry);
 226
 227
 228 CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS
 229         SELECT  circ.id,
 230                 cn.owning_lib,
 231                 bill.billing_type,
 232                 SUM(bill.amount) AS billed
 233           FROM  action.circulation circ
 234                 JOIN money.billing bill ON (circ.id = bill.xact)
 235                 JOIN asset.copy cp ON (circ.target_copy = cp.id)
 236                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 237           WHERE circ.xact_finish IS NULL
 238                 AND NOT bill.voided
 239           GROUP BY 1,2,3
 240           ORDER BY 1,2,3;
 241
 242 CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS
 243         SELECT  owning_lib,
 244                 ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types,
 245                 SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
 246           FROM  money.open_circ_balance_by_owning_lib x
 247           GROUP BY 1;
 248
 249
 250
 251
 252
 253 CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS
 254         SELECT  circ.id,
 255                 circ.circ_lib,
 256                 cn.owning_lib,
 257                 bill.billing_type,
 258                 SUM(bill.amount) AS billed
 259           FROM  action.circulation circ
 260                 JOIN money.billing bill ON (circ.id = bill.xact)
 261                 JOIN asset.copy cp ON (circ.target_copy = cp.id)
 262                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 263           WHERE circ.xact_finish IS NULL
 264                 AND NOT bill.voided
 265           GROUP BY 1,2,3,4
 266           ORDER BY 1,2,3,4;
 267
 268 CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS
 269         SELECT  circ_lib,
 270                 owning_lib,
 271                 ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types,
 272                 SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
 273           FROM  money.open_circ_balance_by_circ_and_owning_lib x
 274           GROUP BY 1,2;
 275
 276
 277
 278
 279
 280 CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS
 281         SELECT  circ.id,
 282                 usr.home_ou,
 283                 cn.owning_lib,
 284                 bill.billing_type,
 285                 SUM(bill.amount) AS billed
 286           FROM  action.circulation circ
 287                 JOIN money.billing bill ON (circ.id = bill.xact)
 288                 JOIN asset.copy cp ON (circ.target_copy = cp.id)
 289                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 290                 JOIN actor.usr usr ON (circ.usr = usr.id)
 291           WHERE circ.xact_finish IS NULL
 292                 AND NOT bill.voided
 293           GROUP BY 1,2,3,4
 294           ORDER BY 1,2,3,4;
 295
 296 CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS
 297         SELECT  home_ou,
 298                 owning_lib,
 299                 ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types,
 300                 SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance
 301           FROM  money.open_circ_balance_by_usr_home_and_owning_lib x
 302           GROUP BY 1,2;
 303
 304 COMMIT;
 305
 306