X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=f1530956fa8fa87d7ab9cc93d7972432950932d0;hp=3119af29417ec55e09207e469f36e45ad8e45679;hb=7eaca0872b3ffe4e0da0deae04cc11b15c19ce32;hpb=43ebb132c5d77c04962449529251715e39dc1760

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 3119af2..f153095 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -910,3 +910,37 @@ BEGIN
 END;
 
 $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
+
+-- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
+--        Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
+
+-- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
+-- TODO: Add a similar tool for actor stat cats, which behave differently.
+
+DECLARE
+	c                    TEXT := schemaname || '.asset_copy_legacy';
+	sc									 TEXT := schemaname || '.asset_stat_cat';
+	sce									 TEXT := schemaname || '.asset_stat_cat_entry';
+	scecm								 TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
+	stat_cat						 INT;
+  stat_cat_entry       INT;
+  
+BEGIN
+
+  FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
+
+		EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
+
+		EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
+							SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
+							(SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
+
+  END LOOP;
+
+  RETURN;
+END;
+
+$$ LANGUAGE plpgsql;
+