-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathinventory-sort.sql
165 lines (164 loc) · 7.92 KB
/
inventory-sort.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
SELECT Barcode,
i1.location,
i1.sortcall,
CASE
WHEN GREATEST(i1.sortvol1, i1.sortvol2, sortvol3, sortvol4, sortvol5) > 1800 THEN
GREATEST(i1.sortvol1, i1.sortvol2, sortvol3, sortvol4, sortvol5)
END AS voldate,
i1.copynum,
i1.sortvol1,
i1.sortvol2,
i1.sortvol3,
i1.sortvol4,
i1.sortvol5,
CallNumber,
Title,
Status,
LastInventoried,
ItemNumber
FROM (
SELECT DISTINCT iprop.barcode AS Barcode,
iloc.name AS location,
CASE
WHEN iprop.call_number_norm is NULL
THEN bph.index_entry
ELSE iprop.call_number_norm
END AS sortcall,
CAST((
string_to_array(
TRIM(
regexp_replace(
regexp_replace(sfiv.content, '[^0-9]', ' ', 'g')
, '[ ]+', ' ', 'g')
)
, ' '
))[1] AS NUMERIC) AS sortvol1,
CAST((
string_to_array(
TRIM(
regexp_replace(
regexp_replace(sfiv.content, '[^0-9]', ' ', 'g')
, '[ ]+', ' ', 'g')
)
, ' '
))[2] AS NUMERIC) AS sortvol2,
CAST((
string_to_array(
TRIM(
regexp_replace(
regexp_replace(sfiv.content, '[^0-9]', ' ', 'g')
, '[ ]+', ' ', 'g')
)
, ' '
))[3] AS NUMERIC) AS sortvol3,
CAST((
string_to_array(
TRIM(
regexp_replace(
regexp_replace(sfiv.content, '[^0-9]', ' ', 'g')
, '[ ]+', ' ', 'g')
)
, ' '
))[4] AS NUMERIC) AS sortvol4,
CAST((
string_to_array(
TRIM(
regexp_replace(
regexp_replace(sfiv.content, '[^0-9]', ' ', 'g')
, '[ ]+', ' ', 'g')
)
, ' '
))[5] AS NUMERIC) AS sortvol5,
i.copy_num AS copynum,
-- sfiv.content AS volume,
CASE
WHEN iprop.call_number is NULL
THEN TRIM(regexp_replace(sfbc.content, '\|.', ' ', 'g'))
ELSE TRIM(regexp_replace(iprop.call_number, '\|.', ' ', 'g'))
END || ' ' ||
CASE
WHEN sfiv.content is NULL THEN ''
ELSE sfiv.content || ' '
END ||
CASE
WHEN i.copy_num is NULL THEN ''
ELSE 'c.' || i.copy_num
END AS CallNumber,
bibprop.best_title AS Title,
CASE
WHEN (cko.loanrule_code_num > 0 AND i.item_status_code = '-') THEN 'Checked Out'
ELSE isnam.name
END AS Status,
TO_CHAR(i.inventory_gmt, 'YYYY-MM-DD HH24:MI:SS') AS LastInventoried,
'i' || rmi.record_num ||
COALESCE(
CAST(
NULLIF(
(
(rmi.record_num % 10) * 2 +
(rmi.record_num / 10 % 10) * 3 +
(rmi.record_num / 100 % 10) * 4 +
(rmi.record_num / 1000 % 10) * 5 +
(rmi.record_num / 10000 % 10) * 6 +
(rmi.record_num / 100000 % 10) * 7 +
(rmi.record_num / 1000000) * 8
) % 11,
10
)
AS CHAR(1)
),
'x'
) AS ItemNumber
FROM sierra_view.item_record AS i
JOIN
sierra_view.item_record_property AS iprop
ON
iprop.item_record_id = i.record_id
JOIN
sierra_view.location_myuser AS iloc
ON
iloc.code = i.location_code
JOIN
sierra_view.item_status_property_myuser AS isnam
ON
isnam.code = i.item_status_code
JOIN
sierra_view.record_metadata As rmi
ON
rmi.id = i.record_id
JOIN
sierra_view.bib_record_item_record_link AS bilink
ON
bilink.item_record_id = i.record_id
JOIN
sierra_view.bib_record_property AS bibprop
ON
bibprop.bib_record_id = bilink.bib_record_id
JOIN
sierra_view.phrase_entry AS bph
ON
bibprop.bib_record_id = bph.record_id
AND
bph.index_tag = 'c'
JOIN
sierra_view.material_property_myuser AS mtnam
ON
mtnam.code = bibprop.material_code
LEFT JOIN
sierra_view.checkout AS cko
ON
i.record_id = cko.item_record_id
LEFT JOIN
sierra_view.subfield AS sfiv
ON
sfiv.record_id = i.record_id
AND
sfiv.field_type_code = 'v'
LEFT JOIN
sierra_view.subfield AS sfbc
ON
sfbc.record_id = bibprop.bib_record_id
AND
sfbc.field_type_code = 'c'
WHERE iloc.code = 'sdjr'
) AS i1;