2 CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$
4 migration_schema ALIAS FOR $1;
8 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';'
13 $$ LANGUAGE PLPGSQL STRICT STABLE;
15 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
17 migration_schema ALIAS FOR $1;
21 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
26 $$ LANGUAGE PLPGSQL STRICT STABLE;
28 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
30 migration_schema ALIAS FOR $1;
34 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
39 $$ LANGUAGE PLPGSQL STRICT STABLE;
41 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
43 migration_schema ALIAS FOR $1;
47 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
52 $$ LANGUAGE PLPGSQL STRICT STABLE;
54 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
56 migration_schema ALIAS FOR $1;
58 patron_table ALIAS FOR $2;
59 default_patron_profile ALIAS FOR $3;
62 sql_where1 TEXT := '';
63 sql_where2 TEXT := '';
64 sql_where3 TEXT := '';
67 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
69 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
71 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
72 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
73 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
74 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
75 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
76 --RAISE INFO 'sql = %', sql;
77 PERFORM migration_tools.exec( $1, sql );
79 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
81 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
83 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
86 $$ LANGUAGE PLPGSQL STRICT STABLE;
88 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
90 migration_schema ALIAS FOR $1;
92 item_table ALIAS FOR $2;
95 sql_where1 TEXT := '';
96 sql_where2 TEXT := '';
97 sql_where3 TEXT := '';
100 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
102 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
104 sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE ';
105 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
106 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
107 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
108 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
109 --RAISE INFO 'sql = %', sql;
110 PERFORM migration_tools.exec( $1, sql );
113 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
115 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
118 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
120 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
122 migration_schema ALIAS FOR $1;
123 base_copy_location_map TEXT;
124 item_table ALIAS FOR $2;
127 sql_where1 TEXT := '';
128 sql_where2 TEXT := '';
129 sql_where3 TEXT := '';
132 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
134 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
136 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
137 sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
138 sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
139 sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
140 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
141 --RAISE INFO 'sql = %', sql;
142 PERFORM migration_tools.exec( $1, sql );
145 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
147 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
150 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
152 -- circulate loan period max renewals max out fine amount fine interval max fine item field 1 item value 1 item field 2 item value 2 patron field 1 patron value 1 patron field 2 patron value 2
153 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
155 migration_schema ALIAS FOR $1;
157 circ_table ALIAS FOR $2;
158 item_table ALIAS FOR $3;
159 patron_table ALIAS FOR $4;
162 sql_where1 TEXT := '';
163 sql_where2 TEXT := '';
164 sql_where3 TEXT := '';
165 sql_where4 TEXT := '';
168 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
170 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
172 sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND ';
173 sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1);
174 sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2);
175 sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1);
176 sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2);
177 sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';';
178 --RAISE INFO 'sql = %', sql;
179 PERFORM migration_tools.exec( $1, sql );
182 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
184 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
187 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
189 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
193 -- First make sure the circ matrix is loaded and the circulations
194 -- have been staged to the extent possible (but at the very least
195 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
196 -- circ modifiers must also be in place.
198 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
206 this_duration_rule INT;
208 this_max_fine_rule INT;
209 rcd config.rule_circ_duration%ROWTYPE;
210 rrf config.rule_recurring_fine%ROWTYPE;
211 rmf config.rule_max_fine%ROWTYPE;
218 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
220 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
222 -- Fetch the correct rules for this circulation
229 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
232 FROM ' || tablename || ' WHERE id = ' || circ || ';')
233 INTO circ_lib, target_copy, usr, is_renewal ;
235 INTO this_duration_rule,
241 FROM action.find_circ_matrix_matchpoint(
247 SELECT INTO rcd * FROM config.rule_circ_duration
248 WHERE id = this_duration_rule;
249 SELECT INTO rrf * FROM config.rule_recurring_fine
250 WHERE id = this_fine_rule;
251 SELECT INTO rmf * FROM config.rule_max_fine
252 WHERE id = this_max_fine_rule;
254 -- Apply the rules to this circulation
255 EXECUTE ('UPDATE ' || tablename || ' c
257 duration_rule = rcd.name,
258 recuring_fine_rule = rrf.name,
259 max_fine_rule = rmf.name,
260 duration = rcd.normal,
261 recuring_fine = rrf.normal,
264 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
267 renewal_remaining = rcd.max_renewals
269 config.rule_circ_duration rcd,
270 config.rule_recuring_fine rrf,
271 config.rule_max_fine rmf,
274 rcd.id = ' || this_duration_rule || ' AND
275 rrf.id = ' || this_fine_rule || ' AND
276 rmf.id = ' || this_max_fine_rule || ' AND
277 ac.id = c.target_copy AND
278 c.id = ' || circ || ';');
280 -- Keep track of where we are in the process
282 IF (n % 100 = 0) THEN
283 RAISE INFO '%', n || ' of ' || n_circs
284 || ' (' || (100*n/n_circs) || '%) circs updated.';
294 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
298 -- First make sure the circ matrix is loaded and the circulations
299 -- have been staged to the extent possible (but at the very least
300 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
301 -- circ modifiers must also be in place.
303 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
311 this_duration_rule INT;
313 this_max_fine_rule INT;
314 rcd config.rule_circ_duration%ROWTYPE;
315 rrf config.rule_recurring_fine%ROWTYPE;
316 rmf config.rule_max_fine%ROWTYPE;
323 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
325 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
327 -- Fetch the correct rules for this circulation
334 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
337 FROM ' || tablename || ' WHERE id = ' || circ || ';')
338 INTO circ_lib, target_copy, usr, is_renewal ;
340 INTO this_duration_rule,
343 (matchpoint).duration_rule,
344 (matchpoint).recurring_fine_rule,
345 (matchpoint).max_fine_rule
346 FROM action.find_circ_matrix_matchpoint(
352 SELECT INTO rcd * FROM config.rule_circ_duration
353 WHERE id = this_duration_rule;
354 SELECT INTO rrf * FROM config.rule_recurring_fine
355 WHERE id = this_fine_rule;
356 SELECT INTO rmf * FROM config.rule_max_fine
357 WHERE id = this_max_fine_rule;
359 -- Apply the rules to this circulation
360 EXECUTE ('UPDATE ' || tablename || ' c
362 duration_rule = rcd.name,
363 recurring_fine_rule = rrf.name,
364 max_fine_rule = rmf.name,
365 duration = rcd.normal,
366 recurring_fine = rrf.normal,
369 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
372 renewal_remaining = rcd.max_renewals,
373 grace_period = rrf.grace_period
375 config.rule_circ_duration rcd,
376 config.rule_recurring_fine rrf,
377 config.rule_max_fine rmf,
380 rcd.id = ' || this_duration_rule || ' AND
381 rrf.id = ' || this_fine_rule || ' AND
382 rmf.id = ' || this_max_fine_rule || ' AND
383 ac.id = c.target_copy AND
384 c.id = ' || circ || ';');
386 -- Keep track of where we are in the process
388 IF (n % 100 = 0) THEN
389 RAISE INFO '%', n || ' of ' || n_circs
390 || ' (' || (100*n/n_circs) || '%) circs updated.';
400 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
402 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
404 my ($marcxml, $shortname) = @_;
412 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
414 foreach my $field ( $marc->field('856') ) {
415 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
416 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
417 $field->add_subfields( '9' => $shortname );
418 $field->update( ind2 => '0');
422 $xml = $marc->as_xml_record;
423 $xml =~ s/^<\?.+?\?>$//mo;
425 $xml =~ s/>\s+</></sgo;
430 $$ LANGUAGE PLPERLU STABLE;
432 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
434 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
436 my ($marcxml, $shortname) = @_;
444 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
446 foreach my $field ( $marc->field('856') ) {
447 if ( ! $field->as_string('9') ) {
448 $field->add_subfields( '9' => $shortname );
452 $xml = $marc->as_xml_record;
453 $xml =~ s/^<\?.+?\?>$//mo;
455 $xml =~ s/>\s+</></sgo;
460 $$ LANGUAGE PLPERLU STABLE;
462 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
466 DELETE FROM asset.opac_visible_copies;
468 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
470 cp.id, cp.circ_lib, cn.record
473 JOIN asset.call_number cn ON (cn.id = cp.call_number)
474 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
475 JOIN asset.copy_location cl ON (cp.location = cl.id)
476 JOIN config.copy_status cs ON (cp.status = cs.id)
477 JOIN biblio.record_entry b ON (cn.record = b.id)
486 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);