X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=d9875943eb9857241520e0425da019a8448d9d14;hp=c99f6f1898eff17af427503766c1010308507b81;hb=5542995c2884a63e7c28df7f983f2cbbcd1a27f1;hpb=06ba6ba6e22c0c348cb3131df899eb35fd8c0d59

diff --git a/sql/base/base.sql b/sql/base/base.sql
index c99f6f1..d987594 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -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;
@@ -723,6 +723,54 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NU
     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
 --
@@ -1437,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 [...];
 
@@ -1469,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
@@ -2125,6 +2204,92 @@ CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[
     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) = @_;