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.
21 CREATE OR REPLACE VIEW money.billable_xact_summary_location_view AS
22 SELECT m.*, COALESCE(c.circ_lib, g.billing_location, r.pickup_lib) AS billing_location
23 FROM money.materialized_billable_xact_summary m
24 LEFT JOIN action.circulation c ON (c.id = m.id)
25 LEFT JOIN money.grocery g ON (g.id = m.id)
26 LEFT JOIN booking.reservation r ON (r.id = m.id);
28 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
29 SELECT * FROM money.billable_xact_summary_location_view
30 WHERE xact_finish IS NULL;
32 CREATE OR REPLACE VIEW money.open_usr_summary AS
35 sum(total_paid) AS total_paid,
36 sum(total_owed) AS total_owed,
37 sum(balance_owed) AS balance_owed
38 FROM money.materialized_billable_xact_summary
39 WHERE xact_finish IS NULL
42 CREATE OR REPLACE VIEW money.open_with_balance_usr_summary AS
45 sum(total_paid) AS total_paid,
46 sum(total_owed) AS total_owed,
47 sum(balance_owed) AS balance_owed
48 FROM money.materialized_billable_xact_summary
49 WHERE xact_finish IS NULL AND balance_owed <> 0.0
52 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
55 SUM(total_paid) AS total_paid,
56 SUM(total_owed) AS total_owed,
57 SUM(balance_owed) AS balance_owed
58 FROM money.materialized_billable_xact_summary
59 WHERE xact_type = 'circulation' AND xact_finish IS NULL
63 -- Not a view, but it's cross-schema..
64 CREATE TABLE config.idl_field_doc (
65 id BIGSERIAL PRIMARY KEY,
66 fm_class TEXT NOT NULL,
68 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
71 CREATE UNIQUE INDEX idl_field_doc_identity ON config.idl_field_doc (fm_class,field,owner);
74 CREATE OR REPLACE VIEW config.marc_field_for_ou AS
75 WITH RECURSIVE ou_marc_fields(id, marc_format, marc_record_type, tag,
76 name, description, fixed_field, repeatable,
77 mandatory, hidden, owner, depth) AS (
78 -- start with all MARC fields defined by the controlling national standard
79 SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0
80 FROM config.marc_field
83 -- as well as any purely local ones that have been added
84 SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0
85 FROM config.marc_field
86 WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag] NOT IN (
87 SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag]
88 FROM config.marc_field
92 -- and start walking down the org unit hierarchy,
93 -- letting entries for child OUs override field names,
94 -- descriptions, repeatability, and the like. Letting
95 -- fixed-fieldness be overridable is something that falls
96 -- from the implementation, but is unlikely to be useful
97 SELECT c.id, marc_format, marc_record_type, tag,
98 COALESCE(c.name, p.name),
99 COALESCE(c.description, p.description),
100 COALESCE(c.fixed_field, p.fixed_field),
101 COALESCE(c.repeatable, p.repeatable),
102 COALESCE(c.mandatory, p.mandatory),
103 COALESCE(c.hidden, p.hidden),
106 FROM config.marc_field c
107 JOIN ou_marc_fields p USING (marc_format, marc_record_type, tag)
108 JOIN actor.org_unit aou ON (c.owner = aou.id)
109 WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL))
111 SELECT id, marc_format, marc_record_type, tag,
112 name, description, fixed_field, repeatable,
113 mandatory, hidden, owner, depth
116 CREATE OR REPLACE VIEW config.marc_subfield_for_ou AS
117 WITH RECURSIVE ou_marc_subfields(id, marc_format, marc_record_type, tag, code,
118 description, repeatable,
119 mandatory, hidden, value_ctype, owner, depth) AS (
120 -- start with all MARC subfields defined by the controlling national standard
121 SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory,
122 hidden, value_ctype, owner, 0
123 FROM config.marc_subfield
126 -- as well as any purely local ones that have been added
127 SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory,
128 hidden, value_ctype, owner, 0
129 FROM config.marc_subfield
130 WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code] NOT IN (
131 SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code]
132 FROM config.marc_subfield
136 -- and start walking down the org unit hierarchy,
137 -- letting entries for child OUs override subfield
138 -- descriptions, repeatability, and the like.
139 SELECT c.id, marc_format, marc_record_type, tag, code,
140 COALESCE(c.description, p.description),
141 COALESCE(c.repeatable, p.repeatable),
142 COALESCE(c.mandatory, p.mandatory),
143 COALESCE(c.hidden, p.hidden),
144 COALESCE(c.value_ctype, p.value_ctype),
147 FROM config.marc_subfield c
148 JOIN ou_marc_subfields p USING (marc_format, marc_record_type, tag, code)
149 JOIN actor.org_unit aou ON (c.owner = aou.id)
150 WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL))
152 SELECT id, marc_format, marc_record_type, tag, code,
153 description, repeatable,
154 mandatory, hidden, value_ctype, owner, depth
155 FROM ou_marc_subfields;
157 CREATE OR REPLACE FUNCTION config.ou_marc_fields(marc_format INTEGER, marc_record_type config.marc_record_type, ou INTEGER) RETURNS SETOF config.marc_field AS $func$
158 SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner
160 SELECT id, marc_format, marc_record_type, tag, name, description,
161 fixed_field, repeatable, mandatory, hidden, owner, depth,
162 MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag) AS winner
163 FROM config.marc_field_for_ou
165 OR owner IN (SELECT id FROM actor.org_unit_ancestors($3)))
167 AND marc_record_type = $2
173 CREATE OR REPLACE FUNCTION config.ou_marc_subfields(marc_format INTEGER, marc_record_type config.marc_record_type, ou INTEGER) RETURNS SETOF config.marc_subfield AS $func$
174 SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory,
175 hidden, value_ctype, owner
177 SELECT id, marc_format, marc_record_type, tag, code, description,
178 repeatable, mandatory, hidden, value_ctype, owner, depth,
179 MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag, code) AS winner
180 FROM config.marc_subfield_for_ou
182 OR owner IN (SELECT id FROM actor.org_unit_ancestors($3)))
184 AND marc_record_type = $2