X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=8423d084263d5bc602542267ea9567ec095133cb;hp=c478f4b58f0a01ef4ccd980d085f1765371a1301;hb=adfcc2989bbcc6e6c2aab72a4464fd31771d5354;hpb=da5ac37bf6cc2619decbe662ef0cc971de8078a8

diff --git a/sql/base/base.sql b/sql/base/base.sql
index c478f4b..8423d08 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -117,7 +117,7 @@ CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
         END;
         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
-        PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,action.circulation,action.hold_request,action.hold_notification,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.fund,acq.fund_allocation,acq.fund_tag,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note'' );' );
+        PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,action.circulation,action.hold_request,action.hold_notification,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.fund,acq.fund_allocation,acq.fund_tag,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint'' );' );
         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
@@ -646,7 +646,7 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE
         output DATE;
     BEGIN
         FOR output IN
-            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::date AS a;'
+            EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
         LOOP
             RETURN output;
         END LOOP;
@@ -688,7 +688,7 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUM
         output NUMERIC(8,2);
     BEGIN
         FOR output IN
-            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;'
+            EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
         LOOP
             RETURN output;
         END LOOP;
@@ -702,6 +702,75 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUM
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
+CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output NUMERIC(6,2);
+    BEGIN
+        FOR output IN
+            EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
+        LOOP
+            RETURN output;
+        END LOOP;
+    EXCEPTION
+        WHEN OTHERS THEN
+            FOR output IN
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
+            LOOP
+                RETURN output;
+            END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output NUMERIC(8,2);
+    BEGIN
+        IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
+            RAISE EXCEPTION 'too many digits';
+        END IF;
+        FOR output IN
+            EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),2))::numeric(8,2)) || '::NUMERIC(8,2) AS a;'
+        LOOP
+            RETURN output;
+        END LOOP;
+    EXCEPTION
+        WHEN OTHERS THEN
+            FOR output IN
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
+            LOOP
+                RETURN output;
+            END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output NUMERIC(6,2);
+    BEGIN
+        IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
+            RAISE EXCEPTION 'too many digits';
+        END IF;
+        FOR output IN
+            EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),2))::numeric(6,2)) || '::NUMERIC(6,2) AS a;'
+        LOOP
+            RETURN output;
+        END LOOP;
+    EXCEPTION
+        WHEN OTHERS THEN
+            FOR output IN
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
+            LOOP
+                RETURN output;
+            END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
 -- add_codabar_checkdigit
 --   $barcode      source barcode
 --
@@ -1184,6 +1253,111 @@ END;
 
 $$ LANGUAGE plpgsql;
 
+CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) 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_to_specific_circ('m_nwrl.action_circulation', 18391960);
+--
+
+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_recurring_fine%ROWTYPE;
+  rmf                  config.rule_max_fine%ROWTYPE;
+  n                    INT := 0;
+  n_circs              INT := 1;
+  
+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
+      (matchpoint).duration_rule,
+      (matchpoint).recurring_fine_rule,
+      (matchpoint).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_recurring_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,
+      recurring_fine_rule = rrf.name,
+      max_fine_rule = rmf.name,
+      duration = rcd.normal,
+      recurring_fine = rrf.normal,
+      max_fine =
+        CASE rmf.is_percent
+          WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
+          ELSE rmf.amount
+        END,
+      renewal_remaining = rcd.max_renewals,
+      grace_period = rrf.grace_period
+    FROM
+      config.rule_circ_duration rcd,
+      config.rule_recurring_fine rrf,
+      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
+        || ' (' || (100*n/n_circs) || '%) circs updated.';
+    END IF;
+
+  --END LOOP;
+
+  RETURN;
+END;
+
+$$ LANGUAGE plpgsql;
+
 
 
 
@@ -1311,7 +1485,7 @@ END;
 $$ LANGUAGE plpgsql;
 
 
-CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
+CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
 
   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
 
@@ -1343,6 +1517,37 @@ CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEX
 
 $$ LANGUAGE PLPERLU STABLE;
 
+CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
+
+  ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
+
+  my ($marcxml, $shortname) = @_;
+
+  use MARC::Record;
+  use MARC::File::XML;
+
+  my $xml = $marcxml;
+
+  eval {
+    my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+
+    foreach my $field ( $marc->field('856') ) {
+      if ( ! $field->as_string('9') ) {
+        $field->add_subfields( '9' => $shortname );
+      }
+    }
+
+    $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.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
 
 DECLARE
@@ -1913,3 +2118,233 @@ BEGIN
 END
 $func$
 LANGUAGE PLPGSQL;
+
+-- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
+CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
+    DECLARE
+        target_event_def ALIAS FOR $1;
+        orgs ALIAS FOR $2;
+    BEGIN
+        DROP TABLE IF EXISTS new_atevdefs;
+        CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
+        FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
+            INSERT INTO action_trigger.event_definition (
+                active
+                ,owner
+                ,name
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,delay
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            ) SELECT
+                'f'
+                ,orgs[i]
+                ,name || ' (clone of '||target_event_def||')'
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,delay
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            FROM
+                action_trigger.event_definition
+            WHERE
+                id = target_event_def
+            ;
+            RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
+            INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
+            INSERT INTO action_trigger.environment (
+                event_def
+                ,path
+                ,collector
+                ,label
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,path
+                ,collector
+                ,label
+            FROM
+                action_trigger.environment
+            WHERE
+                event_def = target_event_def
+            ;
+            INSERT INTO action_trigger.event_params (
+                event_def
+                ,param
+                ,value
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,param
+                ,value
+            FROM
+                action_trigger.event_params
+            WHERE
+                event_def = target_event_def
+            ;
+        END LOOP;
+        RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+-- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
+CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
+    DECLARE
+        target_event_def ALIAS FOR $1;
+        orgs ALIAS FOR $2;
+        new_interval ALIAS FOR $3;
+    BEGIN
+        DROP TABLE IF EXISTS new_atevdefs;
+        CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
+        FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
+            INSERT INTO action_trigger.event_definition (
+                active
+                ,owner
+                ,name
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,delay
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            ) SELECT
+                'f'
+                ,orgs[i]
+                ,name || ' (clone of '||target_event_def||')'
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,new_interval
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            FROM
+                action_trigger.event_definition
+            WHERE
+                id = target_event_def
+            ;
+            RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
+            INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
+            INSERT INTO action_trigger.environment (
+                event_def
+                ,path
+                ,collector
+                ,label
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,path
+                ,collector
+                ,label
+            FROM
+                action_trigger.environment
+            WHERE
+                event_def = target_event_def
+            ;
+            INSERT INTO action_trigger.event_params (
+                event_def
+                ,param
+                ,value
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,param
+                ,value
+            FROM
+                action_trigger.event_params
+            WHERE
+                event_def = target_event_def
+            ;
+        END LOOP;
+        RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
+    my ($marcxml, $tag, $subfield, $delimiter) = @_;
+
+    use MARC::Record;
+    use MARC::File::XML;
+    use MARC::Field;
+
+    my $field;
+    eval {
+        my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+        $field = $marc->field($tag);
+    };
+    return $field->as_string($subfield,$delimiter);
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
+    my ($marcxml, $tag, $subfield, $delimiter) = @_;
+
+    use MARC::Record;
+    use MARC::File::XML;
+    use MARC::Field;
+
+    my @fields;
+    eval {
+        my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+        @fields = $marc->field($tag);
+    };
+    my @texts;
+    foreach my $field (@fields) {
+        push @texts, $field->as_string($subfield,$delimiter);
+    }
+    return \@texts;
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
+    SELECT action.find_hold_matrix_matchpoint(
+        (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
+        (SELECT request_lib FROM action.hold_request WHERE id = $1),
+        (SELECT current_copy FROM action.hold_request WHERE id = $1),
+        (SELECT usr FROM action.hold_request WHERE id = $1),
+        (SELECT requestor FROM action.hold_request WHERE id = $1)
+    );
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
+    SELECT action.find_circ_matrix_matchpoint(
+        (SELECT circ_lib FROM action.circulation WHERE id = $1),
+        (SELECT target_copy FROM action.circulation WHERE id = $1),
+        (SELECT usr FROM action.circulation WHERE id = $1),
+        (SELECT COALESCE(
+                NULLIF(phone_renewal,false),
+                NULLIF(desk_renewal,false),
+                NULLIF(opac_renewal,false),
+                false
+            ) FROM action.circulation WHERE id = $1
+        )
+    );
+$$ LANGUAGE SQL;