3 CREATE OR REPLACE VIEW reporter.classic_current_circ AS
4 SELECT cl.shortname AS circ_lib,
6 circ.xact_start AS xact_start,
7 circ_type.type AS circ_type,
10 ol.shortname AS owning_lib_name,
12 lfm.value AS lit_form,
13 ifm.value AS item_form,
14 itm.value AS item_type,
15 sl.name AS shelving_location,
17 g.name AS profile_group,
18 dem.general_division AS demographic_general_division,
21 cn.label AS call_number_label,
22 call_number_dewey(cn.label) AS dewey,
24 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
28 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
32 END AS dewey_block_tens,
34 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
38 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
42 END AS dewey_block_hundreds,
44 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
48 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
54 10 * floor((call_number_dewey(cn.label)::float) / 10) + 9, '000'
58 END AS dewey_range_tens,
60 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
64 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
70 100 * floor((call_number_dewey(cn.label)::float) / 100) + 99, '000'
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);
106 CREATE OR REPLACE VIEW reporter.legacy_cat1 AS
110 FROM asset.stat_cat_entry
113 CREATE OR REPLACE VIEW reporter.legacy_cat2 AS
117 FROM asset.stat_cat_entry
121 CREATE OR REPLACE VIEW reporter.classic_current_billing_summary AS
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);
157 CREATE OR REPLACE VIEW reporter.classic_item_list AS
158 SELECT t.value as title,
164 cn.label as call_number_label,
166 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
170 10 * floor((call_number_dewey(cn.label)::float) / 10), '000'
174 END AS dewey_block_tens,
176 WHEN call_number_dewey(cn.label) ~ E'^[0-9.]+$'
180 100 * floor((call_number_dewey(cn.label)::float) / 100), '000'
184 END AS dewey_block_hundreds,
185 (SELECT COUNT(*) FROM action.circulation WHERE target_copy = cp.id) as use_count,
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,
194 ol.shortname AS owning_lib_name,
196 cl.shortname AS circ_lib_name,
208 circ_card.barcode as patron_barcode,
209 circ_u.first_given_name || ' ' || circ_u.family_name as patron_name
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);
228 CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS
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
242 CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS
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
253 CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS
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
268 CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS
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
280 CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS
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
296 CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS
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