· 7 years ago · Feb 08, 2019, 06:10 AM
1DROP TABLE IF EXISTS temp_map_item_type
2;
3
4
5CREATE TEMP TABLE temp_map_item_type AS
6SELECT
7p.code_num as code,
8n.name as name
9
10FROM
11sierra_view.itype_property as p
12
13JOIN
14sierra_view.itype_property_name as n
15ON
16 n.itype_property_id = p.id
17
18ORDER BY
19code
20;
21
22
23CREATE INDEX temp_map_item_type_code ON temp_map_item_type(code)
24;
25
26
27-- TESTING
28-- SELECT
29-- *
30-- FROM
31-- temp_map_item_type
32
33
34DROP TABLE IF EXISTS temp_item_export
35;
36
37
38-- give this query some extra time to complete (60000 milliseconds = 1 minute)
39-- (3600000 milliseconds = 60 minutes)
40set statement_timeout to 3600000; commit
41;
42
43
44CREATE TEMP TABLE temp_item_export AS
45SELECT
46r.id as item_record_id,
47r.record_num as item_record_num,
48br.id as bib_record_id,
49br.record_num as bib_record_num,
50r.creation_date_gmt::date AS creation_date,
51r.record_last_updated_gmt::date as record_last_updated,
52p.barcode,
53i.agency_code_num,
54i.location_code,
55i.checkout_statistic_group_code_num,
56i.checkin_statistics_group_code_num,
57c.checkout_gmt::date as checkout_date,
58c.due_gmt::date as due_date,
59(
60 SELECT
61 p.home_library_code
62
63 FROM
64 sierra_view.patron_record as p
65
66 WHERE
67 p.record_id = c.patron_record_id
68
69 LIMIT 1
70) as patron_branch_code,
71i.last_checkout_gmt::date as last_checkout_date,
72i.last_checkin_gmt::date as last_checkin_date,
73i.checkout_total,
74i.renewal_total,
75-- isbn being pulled from the bib record marc varfield
76(
77 SELECT
78 regexp_matches(
79 --regexp_replace(trim(v.field_content), '(\|[a-z]{1})', '', 'ig'), -- get the call number strip the subfield indicators
80 v.field_content,
81 '[0-9]{9,10}[x]{0,1}|[0-9]{12,13}[x]{0,1}', -- the regex to match on (10 or 13 digits, with the possibility of the 'X' character in the check-digit spot)
82 'i' -- regex flags; ignore case
83 )
84
85 FROM
86 sierra_view.varfield as v
87
88 WHERE
89 v.record_id = br.id
90 AND v.marc_tag || v.varfield_type_code = '020i'
91
92 ORDER BY
93 v.occ_num
94
95 LIMIT 1
96)[1]::varchar(30) as isbn,
97(
98 SELECT
99 t.name
100
101 FROM
102 temp_map_item_type AS t
103
104 WHERE
105 t.code = i.itype_code_num
106
107 LIMIT 1
108) as item_format,
109i.item_status_code,
110i.price::numeric(30,2)::money AS price,
111p.call_number_norm AS item_callnumber
112
113FROM
114sierra_view.record_metadata as r
115
116JOIN
117sierra_view.item_record_property as p
118ON
119 p.item_record_id = r.id
120
121JOIN
122sierra_view.item_record as i
123ON
124 i.record_id = r.id
125
126-- item may not be checked out, so we want to left join so we don't exclude items that are not checked out
127LEFT OUTER JOIN
128sierra_view.checkout as c
129ON
130 c.item_record_id = r.id
131
132JOIN
133sierra_view.bib_record_item_record_link as l
134ON
135 l.item_record_id = r.id
136
137JOIN
138sierra_view.record_metadata as br
139ON
140 br.id = l.bib_record_id
141
142WHERE
143r.record_type_code = 'i'
144AND r.campus_code = ''
145AND r.deletion_date_gmt is NULL
146
147-- TESTING -- only fetch a small sample
148-- LIMIT 10000
149;
150
151
152-- TESTING -- select data from the temp table for output
153-- SELECT * FROM temp_item_export
154-- ;