3 -- First, we extract the real circs for users that we know about
4 CREATE TABLE legacy_real_circ (usr int, item int, start_date date, due_date date, item_type text, circ_lib int, claim_return_date date) AS
5 SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3)
8 CASE WHEN lc.renewal_date = 'NEVER'
9 THEN lc.charge_date::DATE
10 ELSE lc.renewal_date::DATE
12 CASE WHEN lc.due_date = 'NEVER'
13 THEN (now() + '20 years')::DATE
14 ELSE lc.due_date::DATE
18 CASE WHEN lc.claim_return_date = '0'
20 ELSE lc.claim_return_date::DATE
21 END AS claim_return_date
24 ON ( lc.charge_key1 = li.cat_key
25 AND lc.charge_key2 = li.call_key
26 AND lc.charge_key3 = li.item_key )
27 JOIN asset.copy cp ON (cp.barcode = li.item_id)
28 JOIN actor.org_unit ou ON (lc.library = ou.shortname)
29 JOIN actor.usr au ON (lc.user_key = au.id)
30 LEFT JOIN legacy_baduser_map bu ON (bu.id = lc.user_key)
39 -- Now build a table containing the status change info ...
40 CREATE TABLE legacy_status_change_circ AS
41 SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3)
42 pol.profile AS profile,
46 ON ( lc.charge_key1 = li.cat_key
47 AND lc.charge_key2 = li.call_key
48 AND lc.charge_key3 = li.item_key )
49 JOIN asset.copy cp ON (cp.barcode = li.item_id)
50 JOIN legacy_baduser_map bu ON (bu.id = lc.user_key)
51 JOIN legacy_non_real_user pol ON (bu.barcode = pol.barcode)
59 -- ... and update the copies with it
61 SET status = legacy_copy_status_map.id
62 FROM legacy_status_change_circ
63 JOIN legacy_copy_status_map ON (legacy_copy_status_map.name = legacy_status_change_circ.profile)
64 WHERE asset.copy.id = legacy_status_change_circ.item;
67 -- Next up, circ_lib changes based on recirc users ...
68 CREATE TABLE legacy_lib_change_circ AS
69 SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3)
74 ON ( lc.charge_key1 = li.cat_key
75 AND lc.charge_key2 = li.call_key
76 AND lc.charge_key3 = li.item_key )
77 JOIN asset.copy cp ON (cp.barcode = li.item_id)
78 JOIN legacy_baduser_map bu ON (bu.id = lc.user_key)
79 JOIN legacy_recirc_lib pol ON (bu.barcode = pol.barcode)
80 JOIN actor.org_unit ou ON (ou.shortname = pol.lib)
88 -- ... and apply that too.
90 SET circ_lib = legacy_lib_change_circ.lib
91 FROM legacy_lib_change_circ
92 WHERE asset.copy.id = legacy_lib_change_circ.item;