Avoid collisions while refreshing OPAC visibility

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

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 8bcff20..0ebfb54 100644 (file)
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -1052,7 +1052,7 @@ DECLARE
   
 BEGIN
 
-       EXECUTE ('TRUNCATE ' || c || ';');
+       EXECUTE ('DELETE FROM ' || 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;');
 
@@ -1079,7 +1079,7 @@ CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEX
 
     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');
       }
@@ -1185,3 +1185,115 @@ CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETUR
   
 $$ 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;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$
+
+BEGIN  
+
+       DELETE FROM asset.opac_visible_copies;
+
+       INSERT INTO asset.opac_visible_copies (id, circ_lib, record)
+               SELECT DISTINCT
+                       cp.id, cp.circ_lib, cn.record
+               FROM
+                       asset.copy cp
+                       JOIN asset.call_number cn ON (cn.id = cp.call_number)
+                       JOIN actor.org_unit a ON (cp.circ_lib = a.id)
+                       JOIN asset.copy_location cl ON (cp.location = cl.id)
+                       JOIN config.copy_status cs ON (cp.status = cs.id)
+                       JOIN biblio.record_entry b ON (cn.record = b.id)
+               WHERE 
+                       NOT cp.deleted AND
+                       NOT cn.deleted AND
+                       NOT b.deleted AND
+                       cs.opac_visible AND
+                       cl.opac_visible AND
+                       cp.opac_visible AND
+                       a.opac_visible AND
+                       cp.id NOT IN (SELECT id FROM asset.opac_visible_copies);
+
+END;
+
+$$ LANGUAGE plpgsql;