· 8 years ago · Feb 13, 2018, 12:08 PM
1#!/usr/bin/python3
2
3import sys
4import psycopg2
5
6try:
7 #~ conn = psycopg2.connect("dbname='iii' host='sierra-db.library.org' port=1032 user='USERNAME_HERE' password='PASSWORD_HERE'")
8
9except:
10 print("unable to connect to the database")
11 conn.close()
12 conn = None
13 sys.exit(1)
14
15# sql statement (in the heredoc type style)
16sql = """\
17DROP TABLE IF EXISTS temp_item_lvl_holds;
18CREATE TEMP TABLE temp_item_lvl_holds AS
19SELECT
20h.id,
21(
22 SELECT
23 string_agg(v.field_content, ', ' order by v.occ_num)
24 FROM
25 sierra_view.varfield as v
26 WHERE
27 v.record_id = r.id
28 AND v.varfield_type_code = 'b'
29) as item_barcodes,
30(
31 SELECT
32 string_agg(v.field_content, ', ' order by v.occ_num)
33 FROM
34 sierra_view.varfield as v
35 WHERE
36 v.record_id = h.patron_record_id
37 AND v.varfield_type_code = 'b'
38) as patron_barcodes,
39pr.ptype_code::int as ptype,
40-- l.*,
41-- r.id,
42r.record_type_code || r.record_num || 'a' as item_record_num,
43i.item_status_code,
44c.checkout_gmt,
45c.loanrule_code_num,
46vr.record_type_code || vr.record_num || 'a' as vol_record_num,
47br.record_type_code || br.record_num || 'a' as bib_record_num,
48v.field_content as volume_number,
49h.placed_gmt,
50pn.last_name || ', ' || pn.first_name || coalesce (' ' || NULLIF(pn.middle_name, ''), '') as full_name,
51h.pickup_location_code,
52-- vr.id,
53p.best_title,
54p.best_author
55-- i.item_status_code,
56-- date_part('day', NOW()::timestamp - c.checkout_gmt::timestamp) as days_checked_out,
57-- h.*
58
59FROM
60sierra_view.hold as h
61
62-- this join will exclude anything that isn't a item level hold
63JOIN
64sierra_view.record_metadata as r
65ON
66 (r.id = h.record_id)
67 AND (r.record_type_code || r.campus_code = 'i')
68
69JOIN
70sierra_view.item_record as i
71ON
72 i.record_id = r.id
73
74LEFT OUTER JOIN
75sierra_view.checkout as c
76ON
77 c.item_record_id = r.id
78
79LEFT OUTER JOIN
80sierra_view.phrase_entry as e
81ON
82 e.record_id = r.id
83 AND e.index_tag = 'b'
84
85LEFT OUTER JOIN
86sierra_view.volume_record_item_record_link as l
87ON
88 l.item_record_id = r.id
89
90LEFT OUTER JOIN
91sierra_view.record_metadata as vr
92ON
93 vr.id = l.volume_record_id
94
95LEFT OUTER JOIN
96sierra_view.varfield AS v
97ON
98 (v.record_id = vr.id) AND (v.varfield_type_code = 'v')
99
100LEFT OUTER JOIN
101sierra_view.bib_record_item_record_link as bl
102ON
103 bl.item_record_id = r.id
104
105LEFT OUTER JOIN
106sierra_view.record_metadata as br
107ON
108 br.id = bl.bib_record_id
109
110LEFT OUTER JOIN
111sierra_view.bib_record_property as p
112ON
113 p.bib_record_id = br.id
114
115LEFT OUTER JOIN
116sierra_view.patron_record as pr
117ON
118 pr.record_id = h.patron_record_id
119
120LEFT OUTER JOIN
121sierra_view.patron_record_fullname as pn
122ON
123 pn.patron_record_id = h.patron_record_id
124
125
126WHERE
127-- item is not a circulating/active item OR item is checked out
128(
129 i.item_status_code NOT IN ('t', '!', '(') -- might need to include status '-' here as well
130 OR (
131 i.item_status_code = '-'
132 AND c.checkout_gmt IS NOT NULL
133 )
134)
135-- item is on shelf not checked out
136OR (
137 i.item_status_code = '-'
138 AND c.checkout_gmt IS NULL
139);
140---
141
142
143---
144-- create the table of item level holds on shelf not checked out
145DROP TABLE IF EXISTS temp_item_lvl_holds_on_shelf;
146CREATE TEMP TABLE temp_item_lvl_holds_on_shelf AS
147SELECT
148*
149
150FROM
151temp_item_lvl_holds as i
152
153WHERE
154-- item is on shelf
155(
156 i.item_status_code = '-'
157 AND i.checkout_gmt IS NULL
158);
159---
160
161
162---
163-- remove the on shelf not checked out holds
164DELETE FROM
165temp_item_lvl_holds as h
166
167WHERE h.id IN(
168 SELECT
169 t.id
170
171 FROM
172 temp_item_lvl_holds_on_shelf as t
173)
174;
175---
176
177
178---
179-- create the table of item level holds where item is not circulating / item checked out
180DROP TABLE IF EXISTS temp_item_lvl_holds_non_or_circ_checked_out;
181CREATE TEMP TABLE temp_item_lvl_holds_non_or_circ_checked_out AS
182SELECT
183*
184
185FROM
186temp_item_lvl_holds as i
187
188WHERE
189(
190 i.item_status_code NOT IN ('t', '!', '(') -- might need to include status '-' here as well
191 OR (
192 i.item_status_code = '-'
193 AND i.checkout_gmt IS NOT NULL
194 )
195);
196---
197
198
199---
200-- we shouldn't need to do this, since the temp_item_lvl_holds table should now be empty if the query was correct
201DELETE FROM
202temp_item_lvl_holds as h
203
204WHERE h.id IN(
205 SELECT
206 t.id
207
208 FROM
209 temp_item_lvl_holds_non_or_circ_checked_out as t
210);
211---
212"""
213
214# create a new cursor and execute
215try:
216 cur = conn.cursor()
217 cur.execute(sql)
218
219except:
220 conn.close()
221 conn = None
222 cur = None
223 print("error connecting or running query")
224 sys.exit(1)
225
226
227
228#~ for record in cur:
229 #~ print(record)
230
231if cur.rowcount != 0:
232 print("query successful ... producing results")
233else:
234 print("something went horribly wrong!")
235 cur.close()
236 conn.close()
237 conn = None
238 cur = None
239 sys.exit(1)
240
241
242
243# close our connections
244cur.close()
245conn.close()
246conn = None
247cur = None