DECLARE
phone TEXT := $1;
areacode TEXT := $2;
+ temp TEXT := '';
output TEXT := '';
n_digits INTEGER := 0;
BEGIN
- output := REGEXP_REPLACE(phone, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
- n_digits := LENGTH(REGEXP_REPLACE(output, '[^0-9]', '', 'g'));
- IF n_digits = 7 THEN
- RETURN (areacode || '-' || output);
+ temp := phone;
+ temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
+ temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
+ n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
+ IF n_digits = 7 AND areacode <> '' THEN
+ temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
+ output := (areacode || '-' || temp);
ELSE
- RETURN output;
+ output := temp;
END IF;
+ RETURN output;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
+ my ($marcxml, $pos, $value) = @_;
+
+ use MARC::Record;
+ use MARC::File::XML;
+
+ my $xml = $marcxml;
+ eval {
+ my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+ my $leader = $marc->leader();
+ substr($leader, $pos, 1) = $value;
+ $marc->leader($leader);
+ $xml = $marc->as_xml_record;
+ $xml =~ s/^<\?.+?\?>$//mo;
+ $xml =~ s/\n//sgo;
+ $xml =~ s/>\s+</></sgo;
+ };
+ return $xml;
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
+ my ($marcxml, $pos, $value) = @_;
+
+ use MARC::Record;
+ use MARC::File::XML;
+
+ my $xml = $marcxml;
+ eval {
+ my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+ my $f008 = $marc->field('008');
+
+ if ($f008) {
+ my $field = $f008->data();
+ substr($field, $pos, 1) = $value;
+ $f008->update($field);
+ $xml = $marc->as_xml_record;
+ $xml =~ s/^<\?.+?\?>$//mo;
+ $xml =~ s/\n//sgo;
+ $xml =~ s/>\s+</></sgo;
+ }
+ };
+ return $xml;
+$$ LANGUAGE PLPERLU STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
+ DECLARE
+ profile ALIAS FOR $1;
+ BEGIN
+ RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
+ BEGIN
+ RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
+
+ my ($marcxml, $tags) = @_;
+
+ use MARC::Record;
+ use MARC::File::XML;
+
+ my $xml = $marcxml;
+
+ eval {
+ my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+ my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
+
+ my @incumbents = ();
+
+ foreach my $field ( $marc->fields() ) {
+ push @incumbents, $field->as_formatted();
+ }
+
+ foreach $field ( $to_insert->fields() ) {
+ if (!grep {$_ eq $field->as_formatted()} @incumbents) {
+ $marc->insert_fields_ordered( ($field) );
+ }
+ }
+
+ $xml = $marc->as_xml_record;
+ $xml =~ s/^<\?.+?\?>$//mo;
+ $xml =~ s/\n//sgo;
+ $xml =~ s/>\s+</></sgo;
+ };
+
+ return $xml;
+
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( tablename TEXT ) RETURNS VOID AS $$
+
+-- Usage:
+--
+-- First make sure the circ matrix is loaded and the circulations
+-- have been staged to the extent possible (but at the very least
+-- circ_lib, target_copy, usr, and *_renewal). User profiles and
+-- circ modifiers must also be in place.
+--
+-- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation');
+--
+
+DECLARE
+ circ_lib INT;
+ target_copy INT;
+ usr INT;
+ is_renewal BOOLEAN;
+ this_duration_rule INT;
+ this_fine_rule INT;
+ this_max_fine_rule INT;
+ rcd config.rule_circ_duration%ROWTYPE;
+ rrf config.rule_recuring_fine%ROWTYPE;
+ rmf config.rule_max_fine%ROWTYPE;
+ circ INT;
+ n INT := 0;
+ n_circs INT;
+
+BEGIN
+
+ EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
+
+ FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
+
+ -- Fetch the correct rules for this circulation
+ EXECUTE ('
+ SELECT
+ circ_lib,
+ target_copy,
+ usr,
+ CASE
+ WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
+ ELSE FALSE
+ END
+ FROM ' || tablename || ' WHERE id = ' || circ || ';')
+ INTO circ_lib, target_copy, usr, is_renewal ;
+ SELECT
+ INTO this_duration_rule,
+ this_fine_rule,
+ this_max_fine_rule
+ duration_rule,
+ recurring_fine_rule,
+ max_fine_rule
+ FROM action.find_circ_matrix_matchpoint(
+ circ_lib,
+ target_copy,
+ usr,
+ is_renewal
+ );
+ SELECT INTO rcd * FROM config.rule_circ_duration
+ WHERE id = this_duration_rule;
+ SELECT INTO rrf * FROM config.rule_recuring_fine
+ WHERE id = this_fine_rule;
+ SELECT INTO rmf * FROM config.rule_max_fine
+ WHERE id = this_max_fine_rule;
+
+ -- Apply the rules to this circulation
+ EXECUTE ('UPDATE ' || tablename || ' c
+ SET
+ duration_rule = rcd.name,
+ recuring_fine_rule = rrf.name,
+ max_fine_rule = rmf.name,
+ duration = rcd.normal,
+ recuring_fine = rrf.normal,
+ max_fine = rmf.amount,
+ renewal_remaining = rcd.max_renewals
+ FROM
+ config.rule_circ_duration rcd,
+ config.rule_recuring_fine rrf,
+ config.rule_max_fine rmf
+ WHERE
+ rcd.id = ' || this_duration_rule || ' AND
+ rrf.id = ' || this_fine_rule || ' AND
+ rmf.id = ' || this_max_fine_rule || ' AND
+ c.id = ' || circ || ';');
+
+ -- Keep track of where we are in the process
+ n := n + 1;
+ IF (n % 100 = 0) THEN
+ RAISE INFO '%', n || ' of ' || n_circs
+ || ' (' || (100*n/n_circs) || '%) circs updated.';
+ END IF;
+
+ END LOOP;
+
+ RETURN;
+END;
+
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
+
+-- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
+-- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
+
+-- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
+-- TODO: Add a similar tool for actor stat cats, which behave differently.
+
+DECLARE
+ c TEXT := schemaname || '.asset_copy_legacy';
+ sc TEXT := schemaname || '.asset_stat_cat';
+ sce TEXT := schemaname || '.asset_stat_cat_entry';
+ scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
+ stat_cat INT;
+ stat_cat_entry INT;
+
+BEGIN
+
+ FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
+
+ EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
+
+ EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
+ SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
+ (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
+
+ END LOOP;
+
+ RETURN;
+END;
+
+$$ LANGUAGE plpgsql;
+
projects / migration-tools.git / blobdiff