BEGIN; CREATE TABLE legacy_lib_max_fine (lib text, max_fine numeric(6,2)); COPY legacy_lib_max_fine (lib, max_fine) FROM STDIN; CHRLS 10.00 FRRLS 10.00 HCLS 10.00 OCRL 10.00 OHOOP 10.00 OKRL 10.00 PPL 10.00 PMRLS 10.00 STRL 10.00 DTRL 100.00 SJRL 100.00 ARL 100.00 ECGRL 100.00 \. CREATE TABLE legacy_type_circ_map (item_type text, recuring_fine numeric(6,2), renewals int); COPY legacy_type_circ_map (item_type, recuring_fine, renewals) FROM STDIN; ART 0.10 0 ATLAS 0.50 2 AUDIOBOOK 0.50 2 AV 0.50 2 BESTSELLER 0.50 2 BOOK 0.10 2 CD 0.10 2 COMPUTER 0.10 2 DATALOAD 0.10 2 DEPOSIT 0.10 2 DVD 0.50 0 DVD-LONG 0.10 2 E-AUDIO 0.50 1 E-BOOK 0.50 1 EQUIP-LONG 0.50 0 EQUIPMENT 0.50 1 FACBESTSLR 0.10 2 FACNEWBK 0.10 2 FILMSTRIP 0.10 2 ILL-ITEM 0.50 0 INTERNET 0.10 1 KIT 0.10 2 LASERDISC 0.50 0 LIBRARYUSE 0.10 2 MAG-CIRC 0.10 2 MAG-NOCIRC 0.10 2 MAP 0.50 1 MICROFORM 0.10 2 MUSIC 0.10 2 NEW-AV 0.50 1 NEW-BOOK 0.10 2 NEWSPAPER 0.10 2 NILS-ITEM 0.10 2 OUTREACH 0.10 2 PAMPHLET 0.10 2 PAPERBACK 0.10 2 REALIA 0.10 2 RECORD 0.10 2 REFERENCE 0.00 0 RESERVE 0.00 0 ROOM 0.00 0 ROOMSATELL 0.00 0 SOFTWARE 0.10 2 SOFTWRLONG 0.10 2 STATE-BOOK 0.10 1 STATE-MFRM 0.10 2 TALKINGBK 0.00 0 TOY 0.10 2 UNKNOWN 0.10 2 VIDEO 0.10 0 VIDEO-LONG 0.10 2 VIDEO-SPEC 0.00 2 WEBSOURCE 0.00 2 \. -- First, we extract the real circs for users that we know about -- CREATE TABLE legacy_real_circ AS INSERT INTO action.circulation ( usr, xact_start, target_copy, circ_lib, circ_staff, renewal_remaining, due_date, stop_fines_time, duration, recuring_fine, max_fine, desk_renewal, duration_rule, recuring_fine_rule, max_fine_rule, stop_fines ) SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3) au.id AS usr, CASE WHEN lc.renewal_date = 'NEVER' THEN lc.charge_date::DATE ELSE lc.renewal_date::DATE END AS xact_start, cp.id AS target_copy, ou.id AS circ_lib, 1 AS circ_staff, tm.renewals AS renewal_remaining, CASE WHEN lc.due_date = 'NEVER' THEN (now() + '20 years')::DATE ELSE lc.due_date::DATE END AS due_date, CASE WHEN lc.claim_return_date = '0' THEN NULL ELSE lc.claim_return_date::DATE END AS stop_fines_time, ((CASE WHEN lc.due_date = 'NEVER' THEN (now() + '20 years')::DATE ELSE lc.due_date::DATE END - CASE WHEN lc.renewal_date = 'NEVER' THEN lc.charge_date::DATE ELSE lc.renewal_date::DATE END)||' days')::interval AS duration, tm.recuring_fine AS recuring_fine, COALESCE( mf.max_fine, 5.00 ) AS max_fine, CASE WHEN lc.renewal_date = 'NEVER' THEN FALSE ELSE TRUE END AS desk_renewal, 'IMPORT'::TEXT AS duration_rule, 'IMPORT'::TEXT AS recuring_fine_rule, 'IMPORT'::TEXT AS max_fine_rule, CASE WHEN lc.claim_return_date = '0' THEN NULL ELSE 'CLAIMSRETURNED' END AS stop_fines FROM legacy_charge lc JOIN joined_legacy li ON ( lc.charge_key1 = li.cat_key AND lc.charge_key2 = li.call_key AND lc.charge_key3 = li.item_key ) JOIN asset.copy cp ON (cp.barcode = li.item_id) JOIN legacy_type_circ_map tm ON (cp.circ_modifier = tm.item_type) JOIN actor.org_unit ou ON (lc.library = ou.shortname) JOIN actor.usr au ON (lc.user_key = au.id) LEFT JOIN legacy_baduser_map bu ON (bu.id = lc.user_key) LEFT JOIN legacy_lib_max_fine mf ON (ou.shortname LIKE mf.lib||'%') WHERE bu.id IS NULL ORDER BY lc.charge_key1, lc.charge_key2, lc.charge_key3, lc.charge_key4 DESC; -- Now build a table containing the status change info ... CREATE TABLE legacy_status_change_circ AS SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3) pol.profile AS profile, cp.id AS item FROM legacy_charge lc JOIN joined_legacy li ON ( lc.charge_key1 = li.cat_key AND lc.charge_key2 = li.call_key AND lc.charge_key3 = li.item_key ) JOIN asset.copy cp ON (cp.barcode = li.item_id) JOIN legacy_baduser_map bu ON (bu.id = lc.user_key) JOIN legacy_non_real_user pol ON (bu.barcode = pol.barcode) WHERE bu.type = 'N' ORDER BY lc.charge_key1, lc.charge_key2, lc.charge_key3, lc.charge_key4 DESC; -- ... and update the copies with it UPDATE asset.copy SET status = legacy_copy_status_map.id FROM legacy_status_change_circ JOIN legacy_copy_status_map ON (legacy_copy_status_map.name = legacy_status_change_circ.profile) WHERE asset.copy.id = legacy_status_change_circ.item; -- Next up, circ_lib changes based on recirc users ... CREATE TABLE legacy_lib_change_circ AS SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3) ou.id AS lib, cp.id AS item FROM legacy_charge lc JOIN joined_legacy li ON ( lc.charge_key1 = li.cat_key AND lc.charge_key2 = li.call_key AND lc.charge_key3 = li.item_key ) JOIN asset.copy cp ON (cp.barcode = li.item_id) JOIN legacy_baduser_map bu ON (bu.id = lc.user_key) JOIN legacy_recirc_lib pol ON (bu.barcode = pol.barcode) JOIN actor.org_unit ou ON (ou.shortname = pol.lib) WHERE bu.type = 'R' ORDER BY lc.charge_key1, lc.charge_key2, lc.charge_key3, lc.charge_key4 DESC; -- ... and apply that too. UPDATE asset.copy SET circ_lib = legacy_lib_change_circ.lib FROM legacy_lib_change_circ WHERE asset.copy.id = legacy_lib_change_circ.item; COMMIT;