2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
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.
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.
20 CREATE OR REPLACE VIEW reporter.classic_current_circ AS
21 SELECT cl.shortname AS circ_lib,
23 circ.xact_start AS xact_start,
24 circ_type.type AS circ_type,
27 ol.shortname AS owning_lib_name,
29 lfm.value AS lit_form,
30 ifm.value AS item_form,
31 itm.value AS item_type,
32 sl.name AS shelving_location,
34 g.name AS profile_group,
35 dem.general_division AS demographic_general_division,
38 cn.label AS call_number_label,
39 call_number_dewey(cn.label) AS dewey,
41 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
45 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
49 END AS dewey_block_tens,
51 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
55 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
59 END AS dewey_block_hundreds,
61 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
65 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
71 10 * floor((call_number_dewey(cn.label)::float) / 10) + 9, '000'
75 END AS dewey_range_tens,
77 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
81 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
87 100 * floor((call_number_dewey(cn.label)::float) / 100) + 99, '000'
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);
123 CREATE OR REPLACE VIEW reporter.legacy_cat1 AS
127 FROM asset.stat_cat_entry
130 CREATE OR REPLACE VIEW reporter.legacy_cat2 AS
134 FROM asset.stat_cat_entry
138 CREATE OR REPLACE VIEW reporter.classic_current_billing_summary AS
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);
174 CREATE OR REPLACE VIEW reporter.classic_item_list AS
181 cn.label as call_number_label,
183 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
187 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
191 END AS dewey_block_tens,
193 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
197 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
201 END AS dewey_block_hundreds,
202 erfcc.circ_count as use_count,
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,
211 ol.shortname AS owning_lib_name,
213 cl.shortname AS circ_lib_name,
225 circ_card.barcode as patron_barcode,
226 circ_u.first_given_name || ' ' || circ_u.family_name as patron_name
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);
243 CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS
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
257 CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS
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
268 CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS
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
283 CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS
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
295 CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS
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
311 CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS
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
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
334 WHERE parent_ou = owning_lib.parent_ou
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
355 WHERE parent_ou = owning_lib.parent_ou
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
376 WHERE parent_ou = owning_lib.parent_ou
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
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
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
401 WHERE parent_ou = owning_lib.parent_ou
403 ) AS system_month_to_date_circ,
404 -- month-to-date circ in branch
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
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
420 FROM action.all_circulation
421 WHERE EXTRACT(YEAR FROM xact_start) = EXTRACT(YEAR FROM now())
422 AND target_copy = acp.id
426 WHERE parent_ou = owning_lib.parent_ou
428 ) AS system_year_to_date_circ,
429 -- year-to-date circ in branch
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
440 FROM action.all_circulation
441 WHERE target_copy = acp.id
445 WHERE parent_ou = owning_lib.parent_ou
447 ) AS system_lifetime_circ,
448 -- lifetime circ in branch
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
456 FROM action.hold_request ahr
457 WHERE cancel_time IS NULL
458 AND expire_time > now()
459 AND fulfillment_time IS NULL
462 ) AS consortium_current_title_hold_count,
463 -- current title hold count in system
465 FROM action.hold_request ahr
466 WHERE cancel_time IS NULL
467 AND expire_time > now()
468 AND fulfillment_time IS NULL
474 WHERE parent_ou = owning_lib.parent_ou
476 ) AS system_current_title_hold_count,
477 -- current title hold count in branch
479 FROM action.hold_request ahr
480 WHERE cancel_time IS NULL
481 AND expire_time > now()
482 AND fulfillment_time IS NULL
485 AND pickup_lib = owning_lib.parent_ou
486 ) AS branch_current_title_hold_count,
487 -- consortium lifetime holds
489 FROM action.all_hold_request
490 WHERE current_copy = acp.id) AS consortium_lifetime_holds,
491 -- system lifetime holds
493 FROM action.all_hold_request
494 WHERE current_copy = acp.id
498 WHERE parent_ou = owning_lib.parent_ou
500 ) AS system_lifetime_holds,
501 -- branch lifetime holds
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
509 FROM action.transit_copy
510 WHERE target_copy = acp.id
511 ) AS consortium_lifetime_transits,
512 -- system lifetime transits
514 FROM action.transit_copy
515 WHERE target_copy = acp.id
519 WHERE parent_ou = owning_lib.parent_ou
521 ) AS system_lifetime_transits,
522 -- branch lifetime transits
524 FROM action.transit_copy
525 WHERE target_copy = acp.id
526 AND source = owning_lib.id
527 ) AS branch_lifetime_transits
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);