Munging ind2 in insert_856_9

[migration-tools.git] / sql / base / base.sql

diff --git a/sql/base/base.sql b/sql/base/base.sql
index ba413c7..b4c06ac 100644 (file)
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -833,7 +833,7 @@ DECLARE
   this_fine_rule       INT;
   this_max_fine_rule   INT;
   rcd                  config.rule_circ_duration%ROWTYPE;
-  rrf                  config.rule_recuring_fine%ROWTYPE;
+  rrf                  config.rule_recurring_fine%ROWTYPE;
   rmf                  config.rule_max_fine%ROWTYPE;
   circ                 INT;
   n                    INT := 0;
@@ -872,7 +872,7 @@ BEGIN
         );
     SELECT INTO rcd * FROM config.rule_circ_duration 
       WHERE id = this_duration_rule;
-    SELECT INTO rrf * FROM config.rule_recuring_fine 
+    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;
@@ -881,15 +881,15 @@ BEGIN
     EXECUTE ('UPDATE ' || tablename || ' c
     SET
       duration_rule = rcd.name,
-      recuring_fine_rule = rrf.name,
+      recurring_fine_rule = rrf.name,
       max_fine_rule = rmf.name,
       duration = rcd.normal,
-      recuring_fine = rrf.normal,
+      recurring_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_recurring_fine rrf,
       config.rule_max_fine rmf
     WHERE
       rcd.id = ' || this_duration_rule || ' AND
@@ -1011,3 +1011,123 @@ BEGIN
 END;
     
 $$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
+
+-- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
+--        Then SELECT migration_tools.create_cards('m_foo');
+
+DECLARE
+       u                    TEXT := schemaname || '.actor_usr_legacy';
+       c                    TEXT := schemaname || '.actor_card';
+  
+BEGIN
+
+       EXECUTE ('TRUNCATE ' || c || ';');
+       EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
+       EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
+
+  RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+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 ( scalar(grep( /(netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
+           scalar(grep( $shortname, $field->subfield('9'))) == 0 ) {
+        $field->add_subfields( '9' => $shortname );
+                               $field->update( ind2 => '0');
+      }
+    }
+
+    $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) RETURNS VOID AS $$
+
+DECLARE
+  old_volume   BIGINT;
+  new_volume   BIGINT;
+  bib          BIGINT;
+  owner        INTEGER;
+  old_label    TEXT;
+  remainder    BIGINT;
+
+BEGIN
+
+  -- Bail out if asked to change the label to ##URI##
+  IF new_label = '##URI##' THEN
+    RETURN;
+  END IF;
+
+  -- Gather information
+  SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
+  SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
+  SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
+
+  -- Bail out if the label already is ##URI##
+  IF old_label = '##URI##' THEN
+    RETURN;
+  END IF;
+
+  -- Bail out if the call number label is already correct
+  IF new_volume = old_volume THEN
+    RETURN;
+  END IF;
+
+  -- Check whether we already have a destination volume available
+  SELECT id INTO new_volume FROM asset.call_number 
+    WHERE 
+      record = bib AND
+      owning_lib = owner AND
+      label = new_label AND
+      NOT deleted;
+
+  -- Create destination volume if needed
+  IF NOT FOUND THEN
+    INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) 
+      VALUES (1, 1, bib, owner, new_label);
+    SELECT id INTO new_volume FROM asset.call_number
+      WHERE 
+        record = bib AND
+        owning_lib = owner AND
+        label = new_label AND
+        NOT deleted;
+  END IF;
+
+  -- Move copy to destination
+  UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
+
+  -- Delete source volume if it is now empty
+  SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
+  IF NOT FOUND THEN
+    DELETE FROM asset.call_number WHERE id = old_volume;
+  END IF;
+
+END;
+
+$$ LANGUAGE plpgsql;