3 CREATE TABLE legacy_lib_max_fine (lib text, max_fine numeric(6,2));
4 COPY legacy_lib_max_fine (lib, max_fine) FROM STDIN;
20 CREATE TABLE legacy_type_circ_map (lib text, max_fine numeric(6,2), renewals int);
21 COPY legacy_type_circ_map (item_type, recuring_fine, renewals) FROM STDIN;
77 -- First, we extract the real circs for users that we know about
78 -- CREATE TABLE legacy_real_circ AS
79 INSERT INTO action.circulation
98 SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3)
100 CASE WHEN lc.renewal_date = 'NEVER' THEN lc.charge_date::DATE ELSE lc.renewal_date::DATE END AS xact_start,
101 cp.id AS target_copy,
104 tm.renewals AS renewal_remaining,
105 CASE WHEN lc.due_date = 'NEVER' THEN (now() + '20 years')::DATE ELSE lc.due_date::DATE END AS due_date,
106 CASE WHEN lc.claim_return_date = '0' THEN NULL ELSE lc.claim_return_date::DATE END AS stop_fines_time,
107 ((CASE WHEN lc.due_date = 'NEVER' THEN (now() + '20 years')::DATE ELSE lc.due_date::DATE END
109 CASE WHEN lc.renewal_date = 'NEVER' THEN lc.charge_date::DATE ELSE lc.renewal_date::DATE END)||' days')::interval AS duration,
110 tm.recuring_fine AS recuring_fine,
111 COALESCE( mf.max_fine, 5.00 ) AS max_fine,
112 CASE WHEN lc.renewal_date = 'NEVER' THEN FALSE ELSE TRUE END AS desk_renewal,
113 'IMPORT'::TEXT AS duration_rule,
114 'IMPORT'::TEXT AS recuring_fine_rule,
115 'IMPORT'::TEXT AS max_fine_rule,
116 CASE WHEN lc.claim_return_date = '0' THEN NULL ELSE 'CLAIMSRETURNED' END AS stop_fines
117 FROM legacy_charge lc
118 JOIN joined_legacy li
119 ON ( lc.charge_key1 = li.cat_key
120 AND lc.charge_key2 = li.call_key
121 AND lc.charge_key3 = li.item_key )
122 JOIN asset.copy cp ON (cp.barcode = li.item_id)
123 JOIN legacy_type_circ_map tm ON (cp.circ_modifier = tm.item_type)
124 JOIN actor.org_unit ou ON (lc.library = ou.shortname)
125 JOIN actor.usr au ON (lc.user_key = au.id)
126 LEFT JOIN legacy_baduser_map bu ON (bu.id = lc.user_key)
127 LEFT JOIN legacy_lib_max_fine mf ON (ou.shortname LIKE mf.lib||'%')
136 -- Now build a table containing the status change info ...
137 CREATE TABLE legacy_status_change_circ AS
138 SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3)
139 pol.profile AS profile,
141 FROM legacy_charge lc
142 JOIN joined_legacy li
143 ON ( lc.charge_key1 = li.cat_key
144 AND lc.charge_key2 = li.call_key
145 AND lc.charge_key3 = li.item_key )
146 JOIN asset.copy cp ON (cp.barcode = li.item_id)
147 JOIN legacy_baduser_map bu ON (bu.id = lc.user_key)
148 JOIN legacy_non_real_user pol ON (bu.barcode = pol.barcode)
156 -- ... and update the copies with it
158 SET status = legacy_copy_status_map.id
159 FROM legacy_status_change_circ
160 JOIN legacy_copy_status_map ON (legacy_copy_status_map.name = legacy_status_change_circ.profile)
161 WHERE asset.copy.id = legacy_status_change_circ.item;
164 -- Next up, circ_lib changes based on recirc users ...
165 CREATE TABLE legacy_lib_change_circ AS
166 SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3)
169 FROM legacy_charge lc
170 JOIN joined_legacy li
171 ON ( lc.charge_key1 = li.cat_key
172 AND lc.charge_key2 = li.call_key
173 AND lc.charge_key3 = li.item_key )
174 JOIN asset.copy cp ON (cp.barcode = li.item_id)
175 JOIN legacy_baduser_map bu ON (bu.id = lc.user_key)
176 JOIN legacy_recirc_lib pol ON (bu.barcode = pol.barcode)
177 JOIN actor.org_unit ou ON (ou.shortname = pol.lib)
185 -- ... and apply that too.
187 SET circ_lib = legacy_lib_change_circ.lib
188 FROM legacy_lib_change_circ
189 WHERE asset.copy.id = legacy_lib_change_circ.item;