adding the create exceeds fines function

[migration-tools.git] / sql / base / 07-eg-specific.sql

diff --git a/sql/base/07-eg-specific.sql b/sql/base/07-eg-specific.sql
index 28d4360..799626a 100644 (file)
--- a/sql/base/07-eg-specific.sql
+++ b/sql/base/07-eg-specific.sql
@@ -1105,9 +1105,11 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURN
             || ' SET x_migrate = CASE'
             || ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
             || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
+            || ' WHEN BTRIM(desired_not_migrate) = ''Delete'' THEN FALSE'
             || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
             || ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
             || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
+            || ' WHEN BTRIM(desired_not_migrate) = ''Keep'' THEN TRUE'
             || ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
             || ' END';
 
@@ -1813,27 +1815,27 @@ $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 
 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
 DECLARE
-    x_barcode TEXT;
-    x_id BIGINT;
+    xxx_barcode TEXT;
+    xxx_id BIGINT;
     row_count NUMERIC;
     internal_collision_count NUMERIC := 0;
     incumbent_collision_count NUMERIC := 0;
 BEGIN
-    FOR x_barcode IN SELECT barcode FROM m_asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+    FOR xxx_barcode IN SELECT barcode FROM m_asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
     LOOP
-        FOR x_id IN SELECT id FROM m_asset_copy WHERE barcode = x_barcode
+        FOR xxx_id IN SELECT id FROM m_asset_copy WHERE barcode = xxx_barcode
         LOOP
-            UPDATE m_asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
+            UPDATE m_asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             internal_collision_count := internal_collision_count + row_count;
         END LOOP;
     END LOOP;
     RAISE INFO '% internal collisions', internal_collision_count;
-    FOR x_barcode IN SELECT a.barcode FROM asset.copy a, m_asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
+    FOR xxx_barcode IN SELECT a.barcode FROM asset.copy a, m_asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
     LOOP
-        FOR x_id IN SELECT id FROM m_asset_copy_legacy WHERE barcode = x_barcode
+        FOR xxx_id IN SELECT id FROM m_asset_copy_legacy WHERE barcode = xxx_barcode
         LOOP
-            UPDATE m_asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
+            UPDATE m_asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             incumbent_collision_count := incumbent_collision_count + row_count;
         END LOOP;
@@ -1847,42 +1849,42 @@ $function$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
 DECLARE
-    x_barcode TEXT;
-    x_id BIGINT;
+    xxx_barcode TEXT;
+    xxx_id BIGINT;
     row_count NUMERIC;
     internal_collision_count NUMERIC := 0;
     incumbent_barcode_collision_count NUMERIC := 0;
     incumbent_usrname_collision_count NUMERIC := 0;
 BEGIN
-    FOR x_barcode IN SELECT usrname FROM m_actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+    FOR xxx_barcode IN SELECT usrname FROM m_actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
     LOOP
-        FOR x_id IN SELECT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+        FOR xxx_id IN SELECT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
         LOOP
-            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
+            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             internal_collision_count := internal_collision_count + row_count;
         END LOOP;
     END LOOP;
     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
 
-    FOR x_barcode IN
+    FOR xxx_barcode IN
         SELECT a.barcode FROM actor.card a, m_actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
     LOOP
-        FOR x_id IN SELECT DISTINCT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+        FOR xxx_id IN SELECT DISTINCT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = xxx_barcode
         LOOP
-            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
+            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
         END LOOP;
     END LOOP;
     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
 
-    FOR x_barcode IN
+    FOR xxx_barcode IN
         SELECT a.usrname FROM actor.usr a, m_actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
     LOOP
-        FOR x_id IN SELECT DISTINCT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+        FOR xxx_id IN SELECT DISTINCT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = xxx_barcode
         LOOP
-            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
+            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
         END LOOP;
@@ -1895,27 +1897,27 @@ $function$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
 DECLARE
-    x_barcode TEXT;
-    x_id BIGINT;
+    xxx_barcode TEXT;
+    xxx_id BIGINT;
     row_count NUMERIC;
     internal_collision_count NUMERIC := 0;
     incumbent_collision_count NUMERIC := 0;
 BEGIN
-    FOR x_barcode IN SELECT barcode FROM m_asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+    FOR xxx_barcode IN SELECT barcode FROM m_asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
     LOOP
-        FOR x_id IN SELECT id FROM m_asset_copy WHERE barcode = x_barcode
+        FOR xxx_id IN SELECT id FROM m_asset_copy WHERE barcode = xxx_barcode
         LOOP
-            UPDATE m_asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
+            UPDATE m_asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             internal_collision_count := internal_collision_count + row_count;
         END LOOP;
     END LOOP;
     RAISE INFO '% internal collisions', internal_collision_count;
-    FOR x_barcode IN SELECT a.barcode FROM asset.copy a, m_asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
+    FOR xxx_barcode IN SELECT a.barcode FROM asset.copy a, m_asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
     LOOP
-        FOR x_id IN SELECT id FROM m_asset_copy_legacy WHERE barcode = x_barcode
+        FOR xxx_id IN SELECT id FROM m_asset_copy_legacy WHERE barcode = xxx_barcode
         LOOP
-            UPDATE m_asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
+            UPDATE m_asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             incumbent_collision_count := incumbent_collision_count + row_count;
         END LOOP;
@@ -1929,42 +1931,42 @@ $function$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
 DECLARE
-    x_barcode TEXT;
-    x_id BIGINT;
+    xxx_barcode TEXT;
+    xxx_id BIGINT;
     row_count NUMERIC;
     internal_collision_count NUMERIC := 0;
     incumbent_barcode_collision_count NUMERIC := 0;
     incumbent_usrname_collision_count NUMERIC := 0;
 BEGIN
-    FOR x_barcode IN SELECT usrname FROM m_actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+    FOR xxx_barcode IN SELECT usrname FROM m_actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
     LOOP
-        FOR x_id IN SELECT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+        FOR xxx_id IN SELECT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = xxx_barcode
         LOOP
-            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
+            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             internal_collision_count := internal_collision_count + row_count;
         END LOOP;
     END LOOP;
     RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
 
-    FOR x_barcode IN
+    FOR xxx_barcode IN
         SELECT a.barcode FROM actor.card a, m_actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
     LOOP
-        FOR x_id IN SELECT DISTINCT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+        FOR xxx_id IN SELECT DISTINCT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = xxx_barcode
         LOOP
-            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
+            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
         END LOOP;
     END LOOP;
     RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
 
-    FOR x_barcode IN
+    FOR xxx_barcode IN
         SELECT a.usrname FROM actor.usr a, m_actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
     LOOP
-        FOR x_id IN SELECT DISTINCT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+        FOR xxx_id IN SELECT DISTINCT id FROM m_actor_usr_legacy WHERE x_migrate AND usrname = xxx_barcode
         LOOP
-            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
+            UPDATE m_actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = xxx_id;
             GET DIAGNOSTICS row_count = ROW_COUNT;
             incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
         END LOOP;
@@ -2160,3 +2162,30 @@ EXCEPTION WHEN OTHERS THEN
 END;
 $func$ LANGUAGE plpgsql;
 
+DROP FUNCTION IF EXISTS migration_tools.create_exceeds_fines(INTEGER);
+CREATE OR REPLACE FUNCTION migration_tools.create_exceeds_fines(au_id INTEGER) RETURNS BOOLEAN AS $$
+DECLARE
+    au_profile          INTEGER;
+    au_org_unit         INTEGER;
+    au_balance_owed     NUMERIC;
+    gpt_id              INTEGER;
+    gpt_row             permission.grp_penalty_threshold%ROWTYPE;
+    ausp_id             INTEGER;
+BEGIN
+    SELECT profile, home_ou FROM actor.usr WHERE id = au_id INTO au_profile, au_org_unit;
+    SELECT balance_owed FROM money.usr_summary WHERE usr = au_id INTO au_balance_owed;
+    IF au_balance_owed IS NULL THEN RETURN FALSE; END IF;
+
+    FOR gpt_id IN SELECT id FROM permission.grp_penalty_threshold WHERE grp IN (SELECT id FROM permission.grp_ancestors(au_profile)) AND penalty = 1 AND org_unit IN (SELECT id FROM actor.org_unit_ancestors(au_org_unit)) LOOP
+        SELECT * FROM permission.grp_penalty_threshold WHERE id = gpt_id INTO gpt_row;
+        ausp_id := NULL;
+        SELECT id FROM actor.usr_standing_penalty WHERE usr = au_id AND org_unit = gpt_row.org_unit AND standing_penalty = 1 AND stop_date IS NULL INTO ausp_id;
+        IF au_balance_owed >= gpt_row.threshold AND ausp_id IS NULL THEN
+            INSERT INTO actor.usr_standing_penalty (org_unit,usr,standing_penalty,staff,note) VALUES (gpt_row.org_unit,au_id,1,1,'set during migration');
+        END IF;
+    END LOOP;
+    RETURN TRUE;
+END
+$$ LANGUAGE plpgsql;
+
+