PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
+ PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label );' );
END;
$$ LANGUAGE PLPGSQL STRICT STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
+ DECLARE
+ attempt_value ALIAS FOR $1;
+ fail_value ALIAS FOR $2;
+ output TIMESTAMPTZ;
+ BEGIN
+ FOR output IN
+ EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ EXCEPTION
+ WHEN OTHERS THEN
+ FOR output IN
+ EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
DECLARE
attempt_value ALIAS FOR $1;
-- 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 || ';')
+ 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
+ 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
+ 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
+ SELECT INTO rcd * FROM config.rule_circ_duration
WHERE id = this_duration_rule;
- SELECT INTO rrf * FROM config.rule_recurring_fine
+ SELECT INTO rrf * FROM config.rule_recurring_fine
WHERE id = this_fine_rule;
- SELECT INTO rmf * FROM config.rule_max_fine
+ SELECT INTO rmf * FROM config.rule_max_fine
WHERE id = this_max_fine_rule;
-- Apply the rules to this circulation
max_fine_rule = rmf.name,
duration = rcd.normal,
recurring_fine = rrf.normal,
- max_fine = rmf.amount,
+ max_fine =
+ CASE rmf.is_percent
+ WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
+ ELSE rmf.amount
+ END,
renewal_remaining = rcd.max_renewals
FROM
config.rule_circ_duration rcd,
config.rule_recurring_fine rrf,
- config.rule_max_fine rmf
+ config.rule_max_fine rmf,
+ asset.copy ac
WHERE
rcd.id = ' || this_duration_rule || ' AND
rrf.id = ' || this_fine_rule || ' AND
rmf.id = ' || this_max_fine_rule || ' AND
+ ac.id = c.target_copy 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
+ IF (n % 100 = 0) THEN
+ RAISE INFO '%', n || ' of ' || n_circs
|| ' (' || (100*n/n_circs) || '%) circs updated.';
END IF;
foreach my $field ( $marc->field('856') ) {
if ( scalar(grep( /(netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
- scalar(grep( $shortname, $field->subfield('9'))) == 0 ) {
+ ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
$field->add_subfields( '9' => $shortname );
$field->update( ind2 => '0');
}
END;
$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
+
+ my $input = $_[0];
+ my %zipdata;
+
+ open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
+
+ while (<FH>) {
+ chomp;
+ my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
+ $zipdata{$zip} = [$city, $state, $county];
+ }
+
+ if (defined $zipdata{$input}) {
+ my ($city, $state, $county) = @{$zipdata{$input}};
+ return [$city, $state, $county];
+ } elsif (defined $zipdata{substr $input, 0, 5}) {
+ my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
+ return [$city, $state, $county];
+ } else {
+ return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
+ }
+
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
+
+DECLARE
+ ou INT;
+ org_unit_depth INT;
+ ou_parent INT;
+ parent_depth INT;
+ errors_found BOOLEAN;
+ ou_shortname TEXT;
+ parent_shortname TEXT;
+ ou_type_name TEXT;
+ parent_type TEXT;
+ type_id INT;
+ type_depth INT;
+ type_parent INT;
+ type_parent_depth INT;
+ proper_parent TEXT;
+
+BEGIN
+
+ errors_found := FALSE;
+
+-- Checking actor.org_unit_type
+
+ FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
+
+ SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
+ SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
+
+ IF type_parent IS NOT NULL THEN
+
+ SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
+
+ IF type_depth - type_parent_depth <> 1 THEN
+ SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
+ SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
+ RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
+ ou_type_name, type_depth, parent_type, type_parent_depth;
+ errors_found := TRUE;
+
+ END IF;
+
+ END IF;
+
+ END LOOP;
+
+-- Checking actor.org_unit
+
+ FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
+
+ SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
+ SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
+ SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
+ SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
+ SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
+ SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
+ SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
+
+ IF ou_parent IS NOT NULL THEN
+
+ IF (org_unit_depth - parent_depth <> 1) OR (
+ (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
+ ) THEN
+ RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
+ ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
+ errors_found := TRUE;
+ END IF;
+
+ END IF;
+
+ END LOOP;
+
+ IF NOT errors_found THEN
+ RAISE INFO 'No errors found.';
+ END IF;
+
+ RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;