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_usr_circulation_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_type = 'circulation' AND xact_finish IS NULL
53 -- Not a view, but it's cross-schema..
54 CREATE TABLE config.idl_field_doc (
55 id BIGSERIAL PRIMARY KEY,
56 fm_class TEXT NOT NULL,
58 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
61 CREATE UNIQUE INDEX idl_field_doc_identity ON config.idl_field_doc (fm_class,field,owner);
64 CREATE OR REPLACE VIEW config.marc_field_for_ou AS
65 WITH RECURSIVE ou_marc_fields(id, marc_format, marc_record_type, tag,
66 name, description, fixed_field, repeatable,
67 mandatory, hidden, owner, depth) AS (
68 -- start with all MARC fields defined by the controlling national standard
69 SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0
70 FROM config.marc_field
73 -- as well as any purely local ones that have been added
74 SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0
75 FROM config.marc_field
76 WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag] NOT IN (
77 SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag]
78 FROM config.marc_field
82 -- and start walking down the org unit hierarchy,
83 -- letting entries for child OUs override field names,
84 -- descriptions, repeatability, and the like. Letting
85 -- fixed-fieldness be overridable is something that falls
86 -- from the implementation, but is unlikely to be useful
87 SELECT c.id, marc_format, marc_record_type, tag,
88 COALESCE(c.name, p.name),
89 COALESCE(c.description, p.description),
90 COALESCE(c.fixed_field, p.fixed_field),
91 COALESCE(c.repeatable, p.repeatable),
92 COALESCE(c.mandatory, p.mandatory),
93 COALESCE(c.hidden, p.hidden),
96 FROM config.marc_field c
97 JOIN ou_marc_fields p USING (marc_format, marc_record_type, tag)
98 JOIN actor.org_unit aou ON (c.owner = aou.id)
99 WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL))
101 SELECT id, marc_format, marc_record_type, tag,
102 name, description, fixed_field, repeatable,
103 mandatory, hidden, owner, depth
106 CREATE OR REPLACE VIEW config.marc_subfield_for_ou AS
107 WITH RECURSIVE ou_marc_subfields(id, marc_format, marc_record_type, tag, code,
108 description, repeatable,
109 mandatory, hidden, value_ctype, owner, depth) AS (
110 -- start with all MARC subfields defined by the controlling national standard
111 SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory,
112 hidden, value_ctype, owner, 0
113 FROM config.marc_subfield
116 -- as well as any purely local ones that have been added
117 SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory,
118 hidden, value_ctype, owner, 0
119 FROM config.marc_subfield
120 WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code] NOT IN (
121 SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code]
122 FROM config.marc_subfield
126 -- and start walking down the org unit hierarchy,
127 -- letting entries for child OUs override subfield
128 -- descriptions, repeatability, and the like.
129 SELECT c.id, marc_format, marc_record_type, tag, code,
130 COALESCE(c.description, p.description),
131 COALESCE(c.repeatable, p.repeatable),
132 COALESCE(c.mandatory, p.mandatory),
133 COALESCE(c.hidden, p.hidden),
134 COALESCE(c.value_ctype, p.value_ctype),
137 FROM config.marc_subfield c
138 JOIN ou_marc_subfields p USING (marc_format, marc_record_type, tag, code)
139 JOIN actor.org_unit aou ON (c.owner = aou.id)
140 WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL))
142 SELECT id, marc_format, marc_record_type, tag, code,
143 description, repeatable,
144 mandatory, hidden, value_ctype, owner, depth
145 FROM ou_marc_subfields;
147 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$
148 SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner
150 SELECT id, marc_format, marc_record_type, tag, name, description,
151 fixed_field, repeatable, mandatory, hidden, owner, depth,
152 MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag) AS winner
153 FROM config.marc_field_for_ou
155 OR owner IN (SELECT id FROM actor.org_unit_ancestors($3)))
157 AND marc_record_type = $2
163 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$
164 SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory,
165 hidden, value_ctype, owner
167 SELECT id, marc_format, marc_record_type, tag, code, description,
168 repeatable, mandatory, hidden, value_ctype, owner, depth,
169 MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag, code) AS winner
170 FROM config.marc_subfield_for_ou
172 OR owner IN (SELECT id FROM actor.org_unit_ancestors($3)))
174 AND marc_record_type = $2