1 DROP TABLE joined_legacy;
2 DROP TABLE legacy_copy_status_map;
6 -- Build the status map ... by hand, which suxorz.
7 CREATE TABLE legacy_copy_status_map (id int, name text);
8 COPY legacy_copy_status_map FROM STDIN;
83 -- First, we build shelving location
84 INSERT INTO asset.copy_location (name, owning_lib)
85 SELECT DISTINCT l.home_location, ou.id
86 FROM legacy_item l JOIN actor.org_unit ou
87 ON (l.owning_library = ou.shortname);
90 -- Now set their flags
91 UPDATE asset.copy_location
93 WHERE name IN ('BINDERY','DISCARD','GENEALOGY','GOV-DOC','INDEX',
94 'LIB-CLOSED','LONGOVERDUE','LOST','LOST-PAID','MAG',
95 'NEWS','ONLOAN','REF','REPAIR','SPEC-COL-R');
97 UPDATE asset.copy_location
98 SET opac_visible = FALSE
99 WHERE name IN ('DATALOA','DISCARD','FIXLIB','LIB-CLOSED', 'LONGOVERDUE',
100 'LOST','LOST-PAID','STORAGE', 'UNKNOWN');
103 -- Now the old stat-cat stuff
104 INSERT INTO asset.stat_cat (owner, name) VALUES (1, 'Legacy CAT1');
105 INSERT INTO asset.stat_cat_entry (stat_cat, owner, value)
106 SELECT DISTINCT currval('asset.stat_cat_id_seq'::regclass), 1, cat_1
109 INSERT INTO asset.stat_cat (owner, name) VALUES (1, 'Legacy CAT2');
110 INSERT INTO asset.stat_cat_entry (stat_cat, owner, value)
111 SELECT DISTINCT currval('asset.stat_cat_id_seq'::regclass), 1, cat_2
115 -- Create a temp table to speed up CN and copy inserts
116 CREATE TABLE joined_legacy AS
117 SELECT i.*, c.call_num
119 JOIN legacy_callnum c USING (cat_key,call_key);
121 -- Import the call numbers
122 -- Getting the owning lib from the first available copy on the CN
123 INSERT INTO asset.call_number (creator,editor,record,label,owning_lib)
124 SELECT DISTINCT 1, 1, l.cat_key , l.call_num, ou.id
126 JOIN biblio.record_entry b ON (cat_key = b.id)
127 JOIN actor.org_unit ou ON (l.owning_library = ou.shortname);
131 -- Import base copy data
132 INSERT INTO asset.copy (circ_lib,creator,editor,create_date,barcode,status,location,loan_duration,fine_level,opac_visible,price,circ_modifier,call_number)
133 SELECT DISTINCT ou.id AS circ_lib,
136 l.creation_date AS create_date,
137 l.item_id AS barcode,
142 CASE WHEN l.shadow IS TRUE THEN FALSE ELSE TRUE END AS opac_visible,
143 (l.price/100::numeric)::numeric(8,2) AS price,
144 l.item_type AS circ_modifier,
145 cn.id AS call_number,
146 pc.cnt || " pieces" as alert_message
148 JOIN legacy_copy_status_map s_map
149 ON (s_map.name = l.current_location)
150 JOIN actor.org_unit ou
151 ON (l.owning_library = ou.shortname)
152 JOIN asset.copy_location cl
153 ON (ou.id = cl.owning_lib AND l.home_location = cl.name)
154 JOIN asset.call_number cn
155 ON (ou.id = cn.owning_lib AND l.cat_key = cn.record AND l.call_num = cn.label)
156 LEFT JOIN legacy_piece_count pc ON (pc.barcode = l.item_id);
158 -- Move copy notes into the notes table ... non-public
159 INSERT INTO asset.copy_note (owning_copy,creator,title,value)
165 JOIN asset.copy cp ON (cp.barcode = l.item_id)
166 WHERE l.item_comment IS NOT NULL
167 AND l.item_comment <> '';