1 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
10 temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
11 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
12 n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
13 IF n_digits = 7 AND areacode <> '' THEN
14 temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
15 output := (areacode || '-' || temp);
22 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
24 CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
28 RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
30 $$ LANGUAGE PLPGSQL STRICT STABLE;
32 CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
34 migration_schema ALIAS FOR $1;
38 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
43 $$ LANGUAGE PLPGSQL STRICT STABLE;
45 CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
47 migration_schema ALIAS FOR $1;
51 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
56 $$ LANGUAGE PLPGSQL STRICT STABLE;
58 CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
60 migration_schema ALIAS FOR $1;
64 EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
69 $$ LANGUAGE PLPGSQL STRICT STABLE;
71 CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
73 migration_schema ALIAS FOR $1;
75 patron_table ALIAS FOR $2;
76 default_patron_profile ALIAS FOR $3;
79 sql_where1 TEXT := '';
80 sql_where2 TEXT := '';
81 sql_where3 TEXT := '';
84 SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
86 EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
88 sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE ';
89 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);
90 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);
91 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);
92 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,'') || ';';
93 --RAISE INFO 'sql = %', sql;
94 PERFORM migration_tools.exec( $1, sql );
96 PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
98 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
100 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
103 $$ LANGUAGE PLPGSQL STRICT STABLE;
105 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
107 migration_schema ALIAS FOR $1;
109 item_table ALIAS FOR $2;
112 sql_where1 TEXT := '';
113 sql_where2 TEXT := '';
114 sql_where3 TEXT := '';
117 SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
119 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
121 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 ';
122 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);
123 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);
124 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);
125 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,'') || ';';
126 --RAISE INFO 'sql = %', sql;
127 PERFORM migration_tools.exec( $1, sql );
130 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
132 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
135 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
137 CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
139 migration_schema ALIAS FOR $1;
140 base_copy_location_map TEXT;
141 item_table ALIAS FOR $2;
144 sql_where1 TEXT := '';
145 sql_where2 TEXT := '';
146 sql_where3 TEXT := '';
149 SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
151 EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
153 sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
154 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);
155 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);
156 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);
157 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,'') || ';';
158 --RAISE INFO 'sql = %', sql;
159 PERFORM migration_tools.exec( $1, sql );
162 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
164 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
167 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
169 -- 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
170 CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
172 migration_schema ALIAS FOR $1;
174 circ_table ALIAS FOR $2;
175 item_table ALIAS FOR $3;
176 patron_table ALIAS FOR $4;
179 sql_where1 TEXT := '';
180 sql_where2 TEXT := '';
181 sql_where3 TEXT := '';
182 sql_where4 TEXT := '';
185 SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
187 EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
189 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 ';
190 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);
191 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);
192 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);
193 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);
194 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,'') || ';';
195 --RAISE INFO 'sql = %', sql;
196 PERFORM migration_tools.exec( $1, sql );
199 PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
201 WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
204 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
206 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$
210 -- First make sure the circ matrix is loaded and the circulations
211 -- have been staged to the extent possible (but at the very least
212 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
213 -- circ modifiers must also be in place.
215 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
223 this_duration_rule INT;
225 this_max_fine_rule INT;
226 rcd config.rule_circ_duration%ROWTYPE;
227 rrf config.rule_recurring_fine%ROWTYPE;
228 rmf config.rule_max_fine%ROWTYPE;
235 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
237 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
239 -- Fetch the correct rules for this circulation
246 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
249 FROM ' || tablename || ' WHERE id = ' || circ || ';')
250 INTO circ_lib, target_copy, usr, is_renewal ;
252 INTO this_duration_rule,
258 FROM action.find_circ_matrix_matchpoint(
264 SELECT INTO rcd * FROM config.rule_circ_duration
265 WHERE id = this_duration_rule;
266 SELECT INTO rrf * FROM config.rule_recurring_fine
267 WHERE id = this_fine_rule;
268 SELECT INTO rmf * FROM config.rule_max_fine
269 WHERE id = this_max_fine_rule;
271 -- Apply the rules to this circulation
272 EXECUTE ('UPDATE ' || tablename || ' c
274 duration_rule = rcd.name,
275 recuring_fine_rule = rrf.name,
276 max_fine_rule = rmf.name,
277 duration = rcd.normal,
278 recuring_fine = rrf.normal,
281 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
284 renewal_remaining = rcd.max_renewals
286 config.rule_circ_duration rcd,
287 config.rule_recuring_fine rrf,
288 config.rule_max_fine rmf,
291 rcd.id = ' || this_duration_rule || ' AND
292 rrf.id = ' || this_fine_rule || ' AND
293 rmf.id = ' || this_max_fine_rule || ' AND
294 ac.id = c.target_copy AND
295 c.id = ' || circ || ';');
297 -- Keep track of where we are in the process
299 IF (n % 100 = 0) THEN
300 RAISE INFO '%', n || ' of ' || n_circs
301 || ' (' || (100*n/n_circs) || '%) circs updated.';
311 CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$
315 -- First make sure the circ matrix is loaded and the circulations
316 -- have been staged to the extent possible (but at the very least
317 -- circ_lib, target_copy, usr, and *_renewal). User profiles and
318 -- circ modifiers must also be in place.
320 -- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
328 this_duration_rule INT;
330 this_max_fine_rule INT;
331 rcd config.rule_circ_duration%ROWTYPE;
332 rrf config.rule_recurring_fine%ROWTYPE;
333 rmf config.rule_max_fine%ROWTYPE;
340 EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
342 FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
344 -- Fetch the correct rules for this circulation
351 WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
354 FROM ' || tablename || ' WHERE id = ' || circ || ';')
355 INTO circ_lib, target_copy, usr, is_renewal ;
357 INTO this_duration_rule,
360 (matchpoint).duration_rule,
361 (matchpoint).recurring_fine_rule,
362 (matchpoint).max_fine_rule
363 FROM action.find_circ_matrix_matchpoint(
369 SELECT INTO rcd * FROM config.rule_circ_duration
370 WHERE id = this_duration_rule;
371 SELECT INTO rrf * FROM config.rule_recurring_fine
372 WHERE id = this_fine_rule;
373 SELECT INTO rmf * FROM config.rule_max_fine
374 WHERE id = this_max_fine_rule;
376 -- Apply the rules to this circulation
377 EXECUTE ('UPDATE ' || tablename || ' c
379 duration_rule = rcd.name,
380 recurring_fine_rule = rrf.name,
381 max_fine_rule = rmf.name,
382 duration = rcd.normal,
383 recurring_fine = rrf.normal,
386 WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
389 renewal_remaining = rcd.max_renewals,
390 grace_period = rrf.grace_period
392 config.rule_circ_duration rcd,
393 config.rule_recurring_fine rrf,
394 config.rule_max_fine rmf,
397 rcd.id = ' || this_duration_rule || ' AND
398 rrf.id = ' || this_fine_rule || ' AND
399 rmf.id = ' || this_max_fine_rule || ' AND
400 ac.id = c.target_copy AND
401 c.id = ' || circ || ';');
403 -- Keep track of where we are in the process
405 IF (n % 100 = 0) THEN
406 RAISE INFO '%', n || ' of ' || n_circs
407 || ' (' || (100*n/n_circs) || '%) circs updated.';
417 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
419 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
421 my ($marcxml, $shortname) = @_;
429 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
431 foreach my $field ( $marc->field('856') ) {
432 if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
433 ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
434 $field->add_subfields( '9' => $shortname );
435 $field->update( ind2 => '0');
439 $xml = $marc->as_xml_record;
440 $xml =~ s/^<\?.+?\?>$//mo;
442 $xml =~ s/>\s+</></sgo;
447 $$ LANGUAGE PLPERLU STABLE;
449 CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
451 ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
453 my ($marcxml, $shortname) = @_;
461 my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
463 foreach my $field ( $marc->field('856') ) {
464 if ( ! $field->as_string('9') ) {
465 $field->add_subfields( '9' => $shortname );
469 $xml = $marc->as_xml_record;
470 $xml =~ s/^<\?.+?\?>$//mo;
472 $xml =~ s/>\s+</></sgo;
477 $$ LANGUAGE PLPERLU STABLE;
479 CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
483 DELETE FROM asset.opac_visible_copies;
485 INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
487 cp.id, cp.circ_lib, cn.record
490 JOIN asset.call_number cn ON (cn.id = cp.call_number)
491 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
492 JOIN asset.copy_location cl ON (cp.location = cl.id)
493 JOIN config.copy_status cs ON (cp.status = cs.id)
494 JOIN biblio.record_entry b ON (cn.record = b.id)
503 cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);