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 CREATE INDEX lj_cat_call_idx ON joined_legacy (cat_key,call_key);
123 -- Import the call numbers
124 -- Getting the owning lib from the first available copy on the CN
125 INSERT INTO asset.call_number (creator,editor,record,label,owning_lib)
126 SELECT DISTINCT 1, 1, l.cat_key , l.call_num, ou.id
128 JOIN biblio.record_entry b ON (cat_key = b.id)
129 JOIN actor.org_unit ou ON (l.owning_library = ou.shortname);
133 -- Import base copy data
134 INSERT INTO asset.copy (circ_lib,creator,editor,create_date,barcode,status,location,loan_duration,fine_level,opac_visible,price,circ_modifier,call_number, alert_message)
135 SELECT DISTINCT ou.id AS circ_lib,
138 l.creation_date AS create_date,
139 l.item_id AS barcode,
144 CASE WHEN l.shadow IS TRUE THEN FALSE ELSE TRUE END AS opac_visible,
145 (l.price/100::numeric)::numeric(8,2) AS price,
146 l.item_type AS circ_modifier,
147 cn.id AS call_number,
148 pc.cnt || ' pieces' as alert_message
150 JOIN legacy_copy_status_map s_map
151 ON (s_map.name = l.current_location)
152 JOIN actor.org_unit ou
153 ON (l.owning_library = ou.shortname)
154 JOIN asset.copy_location cl
155 ON (ou.id = cl.owning_lib AND l.home_location = cl.name)
156 JOIN asset.call_number cn
157 ON (ou.id = cn.owning_lib AND l.cat_key = cn.record AND l.call_num = cn.label)
158 LEFT JOIN legacy_piece_count pc ON (pc.barcode = l.item_id);
160 -- Move copy notes into the notes table ... non-public
161 INSERT INTO asset.copy_note (owning_copy,creator,title,value)
167 JOIN asset.copy cp ON (cp.barcode = l.item_id)
168 WHERE l.item_comment IS NOT NULL
169 AND l.item_comment <> '';
172 INSERT INTO asset.stat_cat_entry_copy_map (stat_cat, stat_cat_entry, owning_copy)
177 JOIN asset.copy cp ON (cp.barcode = l.item_id)
178 JOIN asset.stat_cat_entry sce ON (sce.value = l.cat_1)
179 JOIN asset.stat_cat sc ON (sce.stat_cat = sc.id)
180 WHERE sc.name = 'Legacy CAT1';
182 INSERT INTO asset.stat_cat_entry_copy_map (stat_cat, stat_cat_entry, owning_copy)
187 JOIN asset.copy cp ON (cp.barcode = l.item_id)
188 JOIN asset.stat_cat_entry sce ON (sce.value = l.cat_2)
189 JOIN asset.stat_cat sc ON (sce.stat_cat = sc.id)
190 WHERE sc.name = 'Legacy CAT2';