-- Stored procedures for EIAO DW, R2.2.
-- FIXME Does everything where on testrunid?


-- FIXME: Many procedures have been inserting into matviews that have primary keys.
-- This is dangerous since two GUI clients, C1 and C2, can then calculate the 
-- same value V. If C1 then inserts V into a matview and keeps its connection open
-- and uncommitted, C2 might calculate the same value and try to insert it. But
-- C2 will then hang until C1 commits or rollbacks (basically until C1 closes its
-- connection.
-- The areas in the code where this has happened have now been marked with the
-- comment "-- FIXME:MVI" (MVI for MatView Insertion).

DROP TYPE GenericCacheResult CASCADE;
CREATE TYPE GenericCacheResult AS(
   CacheHit boolean,
   Result FLOAT8
);

CREATE TYPE eiaodw.GenericCacheResult AS(
   CacheHit boolean,
   Result FLOAT8
);

-----------------------------------------------------------------------
-----------------------------------------------------------------------
-- C-WAM implementations


-- Given a ScenarioID and DisabilityGroupID, the procedure will
-- return the barrier probability for this scenario
CREATE OR REPLACE FUNCTION eiaodw.CWAM_scenario(
 ArgScenarioID eiaodw.Scenario.ScenarioID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE)
RETURNS NUMERIC AS $$
DECLARE
 res_cwam NUMERIC;
BEGIN
   SELECT INTO res_cwam CWAM FROM matviews.Scenario WHERE ScenarioID = ArgScenarioID AND DisabilityGroupID = ArgDisabilityGroupID AND TestRunID = ArgTestRunID;
   IF res_cwam IS NULL THEN
      -- should happen rarely - the ETL adds the calculated data now
      res_cwam := CWAM_scenario_calculate(ArgScenarioID, ArgDisabilityGroupID, ArgTestRunID);
      -- INSERT INTO matviews.Scenario(ScenarioID, TestRunID, DisabilityGroupID, CWAM) VALUES (ArgScenarioID, ArgTestRunID, ArgDisabilityGroupID, res_cwam);
      -- FIXME:MVI
   END IF;
   RETURN res_cwam;
END;
$$ LANGUAGE plpgsql STABLE; -- VOLATILE SECURITY DEFINER; -- due to MVI


CREATE OR REPLACE FUNCTION eiaodw.CWAM_scenario_calculate_total(
 ArgScenarioID eiaodw.Scenario.ScenarioID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 failed NUMERIC := 0::NUMERIC;
 total NUMERIC := 0::NUMERIC;
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgScenarioID);
   gcres := GC('CWAM_scenario_calculate_total', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO total COUNT(*) FROM ScenarioCoverage NATURAL JOIN Subject NATURAL JOIN TestResult NATURAL JOIN Result NATURAL JOIN BarrierComputationVersion
    WHERE TestRunID = ArgTestRunID AND ScenarioID = ArgScenarioID AND ResultTypeID=>0;
   IF total=0 THEN
      res := 0; -- n/a but in other calculations this need to be 0 FIXME should be null? and then if'ed in calling function?
   ELSE
      res := total::NUMERIC;
   END IF;
   gcres := GC('CWAM_scenario_calculate_total', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.CWAM_scenario_calculate_failed(
 ArgScenarioID eiaodw.Scenario.ScenarioID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 failed NUMERIC := 0::NUMERIC;
 total NUMERIC := 0::NUMERIC;
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgScenarioID);
   gcres := GC('CWAM_scenario_calculate_failed', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO failed COUNT(*) FROM ScenarioCoverage NATURAL JOIN Subject NATURAL JOIN TestResult NATURAL JOIN Result NATURAL JOIN BarrierComputationVersion
    WHERE TestRunID = ArgTestRunID AND ScenarioID = ArgScenarioID AND ResultTypeID>0;
   IF failed=0 THEN
      res := 0; -- n/a but in other calculations this need to be 0 FIXME should be null? and then if'ed in calling function?
   ELSE
      res := failed::NUMERIC;
   END IF;
   gcres := GC('CWAM_scenario_calculate_failed', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.CWAM_scenario_calculate(
 ArgScenarioID eiaodw.Scenario.ScenarioID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 failed NUMERIC := 0::NUMERIC;
 total NUMERIC := 0::NUMERIC;
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgScenarioID);
   gcres := GC('CWAM_scenario_calculate', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO failed COUNT(*) FROM ScenarioCoverage NATURAL JOIN Subject NATURAL JOIN TestResult NATURAL JOIN Result NATURAL JOIN BarrierComputationVersion
    WHERE TestRunID = ArgTestRunID AND ScenarioID = ArgScenarioID AND ResultTypeID>0;
   SELECT INTO total COUNT(*) FROM ScenarioCoverage NATURAL JOIN Subject NATURAL JOIN TestResult NATURAL JOIN Result NATURAL JOIN BarrierComputationVersion
    WHERE TestRunID = ArgTestRunID AND ScenarioID = ArgScenarioID;
   IF total=0 THEN
      res := 0; -- n/a but in other calculations this need to be 0 FIXME should be null? and then if'ed in calling function?
   ELSE
      res := failed::NUMERIC/total;
   END IF;
   gcres := GC('CWAM_scenario_calculate', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DpageTestRatio_failed(
 ArgScenarioID eiaodw.Scenario.ScenarioID%TYPE,
 ArgBarrierComputationID eiaodw.BarrierComputationVersion.BarrierComputationID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 failed NUMERIC := 0::NUMERIC;
 total NUMERIC := 0::NUMERIC;
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgScenarioID, ArgBarrierComputationID);
   gcres := GC('DpageTestRatio_failed', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO failed COUNT(*) FROM ScenarioCoverage NATURAL JOIN Subject NATURAL JOIN TestResult NATURAL JOIN Result NATURAL JOIN BarrierComputationVersion
    WHERE TestRunID = ArgTestRunID AND BarrierComputationID = ArgBarrierComputationID AND ScenarioID = ArgScenarioID AND ResultTypeID>0;
   IF total=0 THEN
      res := 0; -- n/a but in other calculations this need to be 0 FIXME should be null? and then if'ed in calling function?
   ELSE
      res := failed::numeric;
   END IF;
   gcres := GC('DpageTestRatio_failed', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DpageTestRatio_total(
 ArgScenarioID eiaodw.Scenario.ScenarioID%TYPE,
 ArgBarrierComputationID eiaodw.BarrierComputationVersion.BarrierComputationID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 failed NUMERIC := 0::NUMERIC;
 total NUMERIC := 0::NUMERIC;
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgScenarioID, ArgBarrierComputationID);
   gcres := GC('DpageTestRatio_total', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO total COUNT(*) FROM ScenarioCoverage NATURAL JOIN Subject NATURAL JOIN TestResult NATURAL JOIN Result NATURAL JOIN BarrierComputationVersion
    WHERE TestRunID = ArgTestRunID AND BarrierComputationID = ArgBarrierComputationID AND ScenarioID = ArgScenarioID;
   IF total=0 THEN
      res := 0; -- n/a but in other calculations this need to be 0 FIXME should be null? and then if'ed in calling function?
   ELSE
      res := total::numeric;
   END IF;
   gcres := GC('DpageTestRatio_total', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DpageTestRatio(
 ArgScenarioID eiaodw.Scenario.ScenarioID%TYPE,
 ArgBarrierComputationID eiaodw.BarrierComputationVersion.BarrierComputationID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
BEGIN
   SELECT INTO res TestRatio FROM matviews.ScenarioTestRatio WHERE ScenarioID = ArgScenarioID AND BarrierComputationID = ArgBarrierComputationID AND TestRunID = ArgTestRunID;
   IF res IS NULL THEN
      -- We assume that all non-0 values are precalculated. So if a value does not exist in the matview, the result is 0.
      RETURN 0::NUMERIC;
   END IF;
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.Dsite_calculate(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE,
 ArgColumn text
)
RETURNS numeric AS $$
DECLARE
 res_errormargin NUMERIC;
 res RECORD;
 res2 RECORD;
BEGIN
   SELECT INTO res * FROM matviews.Site WHERE SiteID = ArgSiteID AND DisabilityGroupID = ArgDisabilityGroupID AND TestRunID = ArgTestRunID;
   select into res2 stddev AS cwam_stddev FROM matviews.Site WHERE SiteID = ArgSiteID AND DisabilityGroupID = ArgDisabilityGroupID AND TestRunID = ArgTestRunID;
   IF res.SiteID IS NULL THEN
      -- Should happen very rarely as this is loaded by the ETL now.
      SELECT INTO res 
        COUNT(*) AS ScenarioCount,
        SUM(CWAM_scenario_calculate_failed(ScenarioID, ArgTestRunID)) AS cwam_fail,
        SUM(CWAM_scenario_calculate_total(ScenarioID, ArgTestRunID)) AS cwam_total,
        MAX(CWAM_scenario(ScenarioID, ArgDisabilityGroupID, ArgTestRunID)) AS cwam_max,
        MIN(CWAM_scenario(ScenarioID, ArgDisabilityGroupID, ArgTestRunID)) AS cwam_min
        FROM (SELECT DISTINCT ScenarioID FROM ScenarioCoverage NATURAL JOIN Subject WHERE SiteID = ArgSiteID AND TestRunID = ArgTestRunID) AS foo;
      select INTO res2
        SQRT((1::float/(res.ScenarioCount-1)) * SUM(CWAM_scenario(ScenarioID,ArgDisabilityGroupID, ArgTestRunID)-(res.cwam_fail/res.cwam_total))^2) as cwam_stddev
      from (SELECT DISTINCT ScenarioID FROM ScenarioCoverage NATURAL JOIN Subject WHERE SiteID = ArgSiteID AND TestRunID = ArgTestRunID) AS foo; 
      -- TODO: Check if this can be simplified. It is not beneficial to run two selects; if this can be avoided please do.
      -- NOTE: The simplification in http://en.wikipedia.org/wiki/Standard_deviation are based on the arithmetic mean, which our algorithm does not do.
      res_errormargin := 1.96 * (res2.cwam_stddev / SQRT(res.scenariocount));
      INSERT INTO matviews.Site (SiteID, TestRunID, DisabilityGroupID, CWAM, StdDev, ErrorMargin, MaxCWAM, MinCWAM, ScenarioCount) VALUES (ArgSiteID, ArgTestRunID, ArgDisabilityGroupID, res.cwam_mean, res2.cwam_stddev, res_errormargin, res.cwam_max, res.cwam_min, res.scenariocount);
      -- FIXME:MVI
	  SELECT INTO res * FROM matviews.Site WHERE SiteID = ArgSiteID AND DisabilityGroupID = ArgDisabilityGroupID AND TestRunID = ArgTestRunID;
   END IF;
   IF     LOWER(ArgColumn) = 'cwam'          THEN 
       IF res.cwam IS NULL THEN
           RETURN res.CWAM_fail/res.CWAM_total;
       ELSE
           RETURN res.cwam;
        END IF;
   ELSEIF LOWER(ArgColumn) = 'stddev'        THEN RETURN res2.cwam_StdDev;
   ELSEIF LOWER(ArgColumn) = 'errormargin'   THEN RETURN res.ErrorMargin;
   ELSEIF LOWER(ArgColumn) = 'maxcwam'       THEN RETURN res.MaxCWAM;
   ELSEIF LOWER(ArgColumn) = 'mincwam'       THEN RETURN res.MinCWAM;
   ELSEIF LOWER(ArgColumn) = 'scenariocount' THEN RETURN res.ScenarioCount;
   ELSE RAISE EXCEPTION 'Unknown column: %', ArgColumn;
   END IF;
END;
$$ LANGUAGE plpgsql STABLE; -- VOLATILE SECURITY DEFINER; -- due to MVI


CREATE OR REPLACE FUNCTION eiaodw.Dgroup_calculate(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE,
 ArgColumn text
)
RETURNS numeric AS $$
DECLARE
 res_errormargin NUMERIC;
 res RECORD;
 agrouphash INT;
BEGIN
   agrouphash := EIAOHash(ArgGroup);
   SELECT INTO res * FROM matviews.Group WHERE GroupHash = agrouphash AND "Group" = ArgGroup AND DisabilityGroupID = ArgDisabilityGroupID AND TestRunID = ArgTestRunID;
   IF res."Group" IS NULL THEN
      --RAISE NOTICE 'Calculating group %', ArgGroup;
      --fixme optimization would be to move cwam_site out, it is defined as volatile, but can be considered stable in this calculation
      SELECT INTO res
        COUNT(*) AS SiteCount,
        AVG(DsiteMean(SiteID, ArgDisabilityGroupID, ArgTestRunID)) AS cwam_mean,
        MIN(DsiteMean(SiteID, ArgDisabilityGroupID, ArgTestRunID)) AS cwam_min,
        MAX(DsiteMean(SiteID, ArgDisabilityGroupID, ArgTestRunID)) AS cwam_max,
        STDDEV(DsiteMean(SiteID, ArgDisabilityGroupID, ArgTestRunID)) AS cwam_stddev
        FROM setof_from_array(ArgGroup) AS SiteID;
      IF res.SiteCount IS NULL OR res.SiteCount = 0 THEN
         res_errormargin := NULL;
      ELSE
         res_errormargin := 1.96 * (res.cwam_stddev / SQRT(res.sitecount));
      END IF;
      INSERT INTO matviews.Group (GroupHash, "Group", TestRunID, DisabilityGroupID, CWAM, StdDev, ErrorMargin, MaxCWAM, MinCWAM, SiteCount) VALUES (agrouphash, ArgGroup, ArgTestRunID, ArgDisabilityGroupID, res.cwam_mean, res.cwam_stddev, res_errormargin, res.cwam_max, res.cwam_min, res.sitecount);
      SELECT INTO res * FROM matviews.Group WHERE "Group" = ArgGroup AND DisabilityGroupID = ArgDisabilityGroupID AND TestRunID = ArgTestRunID;
   END IF;
   IF     LOWER(ArgColumn) = 'cwam'          THEN RETURN res.CWAM;
   ELSEIF LOWER(ArgColumn) = 'stddev'        THEN RETURN res.StdDev;
   ELSEIF LOWER(ArgColumn) = 'errormargin'   THEN RETURN res.ErrorMargin;
   ELSEIF LOWER(ArgColumn) = 'maxcwam'       THEN RETURN res.MaxCWAM;
   ELSEIF LOWER(ArgColumn) = 'mincwam'       THEN RETURN res.MinCWAM;
   ELSEIF LOWER(ArgColumn) = 'sitecount'     THEN RETURN res.SiteCount;
   ELSE RAISE EXCEPTION 'Unknown column: %', ArgColumn;
   END IF;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION eiaodw.DsiteMean(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dsite_calculate(ArgSiteID, ArgDisabilityGroupID, ArgTestRunID, 'cwam'); --is cached
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteStddev(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dsite_calculate(ArgSiteID, ArgDisabilityGroupID, ArgTestRunID, 'stddev'); --is cached
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteList(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS SETOF eiaodw.Subject.Site%TYPE AS $$
DECLARE
 r RECORD;
BEGIN
   FOR r IN SELECT DISTINCT Site FROM datastaging.Site WHERE SiteID = ANY(ArgGroup) LOOP --AND TestRunID = ArgTestRunID was removed to go to datastaging, but i think it will have no impact as you cannot get the group without a testrunid
      RETURN NEXT r.Site;
   END LOOP;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DurlListForScenario(
 ArgScenarioID eiaodw.Scenario.ScenarioID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS SETOF eiaodw.Subject.URL%TYPE AS $$
DECLARE
 r record;
BEGIN
   FOR r IN SELECT DISTINCT URL FROM datastaging.Resource NATURAL JOIN datastaging.ResourceVersion NATURAL JOIN datastaging.ScenarioCoverage WHERE ScenarioID = ArgScenarioID AND TestRunID = ArgTestRunID LOOP
      RETURN NEXT r.URL;
   END LOOP;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DpageList(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS SETOF eiaodw.Scenario.ScenarioID%TYPE AS $$
DECLARE
 r RECORD;
BEGIN
   --FOR r IN SELECT DISTINCT ScenarioID FROM datastaging.ScenarioCoverage NATURAL JOIN datastaging.ResourceVersion NATURAL JOIN datastaging.Resource WHERE SiteID = ArgSiteID AND TestRunID = ArgTestRunID LOOP  --Commented by chr
    FOR r IN SELECT ScenarioID FROM Scenario WHERE SiteID = ArgSiteID AND TestRunID = ArgTestRunID LOOP
      RETURN NEXT r.ScenarioID;
   END LOOP;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DpageListLangSite(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS SETOF eiaodw.Scenario.ScenarioID%TYPE AS $$
DECLARE
 r RECORD;
BEGIN
   FOR r IN SELECT DISTINCT ScenarioID FROM datastaging.ScenarioCoverage NATURAL JOIN datastaging.ResourceVersion NATURAL JOIN datastaging.Resource NATURAL JOIN datastaging.LanguageUsage WHERE SiteID = ArgSiteID AND LanguageID = ArgLanguageID AND TestRunID = ArgTestRunID LOOP
      RETURN NEXT r.ScenarioID;
   END LOOP;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DpageListLangGroup(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS SETOF eiaodw.Scenario.ScenarioID%TYPE AS $$
DECLARE
 s RECORD;
 r RECORD;
BEGIN
   FOR r IN SELECT * FROM setof_from_array(ArgGroup) as SiteID LOOP
      FOR s IN SELECT * FROM DpageListLangSite(r.SiteID, ArgLanguageID, ArgTestRunID) as ScenarioID LOOP
         RETURN NEXT s.ScenarioID;
      END LOOP;
   END LOOP;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteTestStddev(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 gcargs INT[];
 gcres GenericCacheResult;
 res NUMERIC;
BEGIN
   gcargs := GCArg(ArgSiteID, ArgDisabilityGroupID);
   gcres := GC('DsiteTestStddev', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res STDDEV(CWAM_scenario(ScenarioID, ArgDisabilityGroupID, ArgTestRunID)) FROM DpageList(ArgSiteID,  ArgTestRunID) AS ScenarioID;
   gcres := GC('DsiteTestStddev', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteStddevLang(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 gcargs INT[];
 gcres GenericCacheResult;
 res NUMERIC;
BEGIN
   gcargs := GCArg(ArgSiteID, ArgLanguageID, ArgDisabilityGroupID);
   gcres := GC('DsiteStddevLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res STDDEV(CWAM_scenario(ScenarioID, ArgDisabilityGroupID, ArgTestRunID)) FROM DpageListLangSite(ArgSiteID, ArgLanguageID, ArgTestRunID) AS ScenarioID;
   gcres := GC('DsiteStddevLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;



CREATE OR REPLACE FUNCTION eiaodw.DpageNumber(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dsite_calculate(ArgSiteID, 0::smallint, ArgTestRunID, 'scenariocount'); -- is cached
END;
$$ LANGUAGE plpgsql STABLE;



CREATE OR REPLACE FUNCTION eiaodw.DsiteErrorMargin(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dsite_calculate(ArgSiteID, ArgDisabilityGroupID, ArgTestRunID, 'errormargin'); --is cached
END;
$$ LANGUAGE plpgsql STABLE;



CREATE OR REPLACE FUNCTION eiaodw.DsiteMin(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dsite_calculate(ArgSiteID, ArgDisabilityGroupID, ArgTestRunID, 'mincwam'); -- is cached
END;
$$ LANGUAGE plpgsql STABLE;




CREATE OR REPLACE FUNCTION eiaodw.DsiteMax(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dsite_calculate(ArgSiteID, ArgDisabilityGroupID, ArgTestRunID, 'maxcwam'); -- is cached
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteTestRatio(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgBarrierComputationID eiaodw.BarrierComputationVersion.BarrierComputationID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
BEGIN
   SELECT INTO res TestRatio FROM matviews.SiteTestRatio WHERE SiteID = ArgSiteID AND BarrierComputationID = ArgBarrierComputationID AND TestRunID = ArgTestRunID;
   IF res IS NULL THEN
      -- We assume that all non-0 values are precalculated. So if a value does not exist in the matview, the result is 0.
      RETURN 0::NUMERIC;
   END IF;
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteTestMin(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgBarrierComputationID eiaodw.BarrierComputationVersion.BarrierComputationID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgSiteID, ArgBarrierComputationID);
   gcres := GC('DsiteTestMin', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res MIN(DpageTestRatio(ScenarioID, ArgBarrierComputationID, ArgTestRunID)) FROM DpageList(ArgSiteID, ArgTestRunID) AS ScenarioID;
   gcres := GC('DsiteTestMin', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteTestMax(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgBarrierComputationID eiaodw.BarrierComputationVersion.BarrierComputationID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgSiteID, ArgBarrierComputationID);
   gcres := GC('DsiteTestMax', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res MAX(DpageTestRatio(ScenarioID, ArgBarrierComputationID, ArgTestRunID)) FROM DpageList(ArgSiteID, ArgTestRunID) AS ScenarioID;
   gcres := GC('DsiteTestMax', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;



CREATE OR REPLACE FUNCTION eiaodw.DpageNumberLangSite( --fixme this and others could be merged, performance will improve, but only marginal
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res INT;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgSiteID, ArgLanguageID);
   gcres := GC('DpageNumberLangSite', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res COUNT(*) FROM DpageListLangSite(ArgSiteID, ArgLanguageID, ArgTestRunID);
   gcres := GC('DpageNumberLangSite', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteMeanLang(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgSiteID, ArgLanguageID);
   gcres := GC('DsiteMeanLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res AVG(CWAM_scenario(ScenarioID, ArgDisabilityGroupID, ArgTestRunID)) FROM DpageListLangSite(ArgSiteID, ArgLanguageID, ArgTestRunID) as ScenarioID;
   gcres := GC('DsiteMeanLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DsiteMinLang(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgSiteID, ArgLanguageID);
   gcres := GC('DsiteMinLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res MIN(CWAM_scenario(ScenarioID, ArgDisabilityGroupID, ArgTestRunID)) FROM DpageListLangSite(ArgSiteID, ArgLanguageID, ArgTestRunID) as ScenarioID;
   gcres := GC('DsiteMinLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DsiteMaxLang(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgSiteID, ArgLanguageID);
   gcres := GC('DsiteMaxLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res MAX(CWAM_scenario(ScenarioID, ArgDisabilityGroupID, ArgTestRunID)) FROM DpageListLangSite(ArgSiteID, ArgLanguageID, ArgTestRunID) as ScenarioID;
   gcres := GC('DsiteMaxLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DsiteErrorMarginLang(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 sdev NUMERIC;
 pnumber INT;
 res NUMERIC;
BEGIN
   sdev := DsiteStddevLang(ArgSiteID, ArgLanguageID, ArgDisabilityGroupID, ArgTestRunID);
   pnumber := DpageNumberLangSite(ArgSiteID, ArgLanguageID, ArgTestRunID);
   res := 1.96*sdev/SQRT(pnumber);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DpageContent(
 ArgScenarioID eiaodw.Scenario.ScenarioID%TYPE,
 ArgMimeTypeID eiaodw.MimeType.MimeTypeID%TYPE,
 ArgInclusionTypeID eiaodw.InclusionType.InclusionTypeID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res INT;
BEGIN
   SELECT INTO res Result FROM matviews.ScenarioContent WHERE ScenarioID = ArgScenarioID AND MimeTypeID = ArgMimeTypeID AND InclusionTypeID = ArgInclusionTypeID AND TestRunID = ArgTestRunID;
   IF res IS NULL THEN
      -- We assume that all non-0 values are precalculated. So if a value does not exist in the matview, the result is 0.
      RETURN 0::NUMERIC;
   END IF;
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DgroupMean(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dgroup_calculate(ArgGroup, ArgDisabilityGroupID, ArgTestRunID, 'cwam'); -- is cached
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteContent(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgMimeTypeID eiaodw.MimeType.MimeTypeID%TYPE,
 ArgInclusionTypeID eiaodw.InclusionType.InclusionTypeID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
BEGIN
   SELECT INTO res Result FROM matviews.SiteContent WHERE SiteID = ArgSiteID AND MimeTypeID = ArgMimeTypeID AND InclusionTypeID = ArgInclusionTypeID AND TestRunID = ArgTestRunID;
   IF res IS NULL THEN
      -- We assume that all non-0 values are precalculated. So if a value does not exist in the matview, the result is 0.
      RETURN 0::NUMERIC;
   END IF;
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DgroupStddev(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dgroup_calculate(ArgGroup, ArgDisabilityGroupID, ArgTestRunID, 'stddev'); -- is cached
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DgroupStddevLang( -- fixme this is not the correct result/implementation?
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 ArgGroup2 smallint[];
 res NUMERIC := 0;
 gml NUMERIC;
 snl NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgLanguageID, ArgDisabilityGroupID);
   gcres := GC('DgroupStddevLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   ArgGroup2 := siteListLangGroup(ArgGroup, ArgLanguageID, ArgTestRunID);
   gml := DgroupMeanLang(ArgGroup2, ArgLanguageID, ArgDisabilityGroupID, ArgTestRunID); -- moved out for performance
   snl := DsiteNumberLang(ArgGroup2, ArgLanguageID, ArgTestRunID);
   IF snl <= 1 THEN
      res := NULL;
   ELSE
      SELECT INTO res SQRT(
        SUM(DsiteLangWeight(SiteID, ArgLanguageID, ArgTestRunID)*(DsiteMeanLang(SiteID, ArgLanguageID, ArgDisabilityGroupID, ArgTestRunID)-gml)^2)
        /
        (((snl-1)/snl)*SUM(DsiteLangWeight(SiteID, ArgLanguageID, ArgTestRunID)))
       ) FROM setof_from_array(ArgGroup2) AS SiteID;
   END IF;
   gcres := GC('DgroupStddevLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DsiteNumber(
 ArgGroup smallint[],
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dgroup_calculate(ArgGroup, 0::smallint, ArgTestRunID, 'sitecount'); -- is cached
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DgroupErrorMargin(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dgroup_calculate(ArgGroup, ArgDisabilityGroupID, ArgTestRunID, 'errormargin'); -- is cached
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DgroupMin(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dgroup_calculate(ArgGroup, ArgDisabilityGroupID, ArgTestRunID, 'mincwam'); --is cached
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DgroupMax(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
BEGIN
   RETURN Dgroup_calculate(ArgGroup, ArgDisabilityGroupID, ArgTestRunID, 'maxcwam'); --is cached
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DgroupTestRatio( --fixme could be merged with orthers, but performance improvement would be small
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgBarrierComputationID eiaodw.BarrierComputationVersion.BarrierComputationID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
 gcargs INT[];
 gcres eiaodw.GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgBarrierComputationID);
   gcres := GC('DgroupTestRatio', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res AVG(DsiteTestRatio(SiteID, ArgBarrierComputationID, ArgTestRunID)) FROM setof_from_array(ArgGroup) AS SiteID;
   gcres := GC('DgroupTestRatio', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DgroupTestMin(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgBarrierComputationID eiaodw.BarrierComputationVersion.BarrierComputationID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgBarrierComputationID);
   gcres := GC('DgroupTestMin', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res MIN(DsiteTestRatio(SiteID, ArgBarrierComputationID, ArgTestRunID)) FROM setof_from_array(ArgGroup) AS SiteID;
   gcres := GC('DgroupTestMin', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DgroupTestMax(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgBarrierComputationID eiaodw.BarrierComputationVersion.BarrierComputationID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgBarrierComputationID);
   gcres := GC('DgroupTestMax', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res MAX(DsiteTestRatio(SiteID, ArgBarrierComputationID, ArgTestRunID)) FROM setof_from_array(ArgGroup) AS SiteID;
   gcres := GC('DgroupTestMax', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DpageNumberLangGroup(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res INT := 0;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgLanguageID);
   gcres := GC('DpageNumberLangGroup', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res SUM(DpageNumberLangSite(SiteID, ArgLanguageID, ArgTestRunID)) FROM setof_from_array(ArgGroup) AS SiteID;
   gcres := GC('DpageNumberLangGroup', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DsiteLangWeight(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC := 0;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgSiteID, ArgLanguageID);
   gcres := GC('DsiteLangWeight', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   SELECT INTO res (DpageNumberLangSite(ArgSiteID, ArgLanguageID, ArgTestRunID)::NUMERIC/DpageNumber(ArgSiteID, ArgTestRunID));
   gcres := GC('DsiteLangWeight', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DgroupMeanLang(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 ArgGroup2 smallint[];
 res NUMERIC := 0;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgLanguageID, ArgDisabilityGroupID);
   gcres := GC('DgroupMeanLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   ArgGroup2 := siteListLangGroup(ArgGroup, ArgLanguageID, ArgTestRunID);
   SELECT INTO res SUM(DsiteLangWeight(SiteID, ArgLanguageID, ArgTestRunID)*DsiteMeanLang(SiteID, ArgLanguageID, ArgDisabilityGroupID, ArgTestRunID))/SUM(DsiteLangWeight(SiteID, ArgLanguageID, ArgTestRunID)) FROM setof_from_array(ArgGroup2) AS SiteID;
   gcres := GC('DgroupMeanLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DgroupMinLang(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 ArgGroup2 smallint[];
 res NUMERIC := 0;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgLanguageID, ArgDisabilityGroupID);
   gcres := GC('DgroupMinLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   ArgGroup2 := siteListLangGroup(ArgGroup, ArgLanguageID, ArgTestRunID);
   SELECT INTO res MIN(DsiteMeanLang(SiteID, ArgLanguageID, ArgDisabilityGroupID, ArgTestRunID)) FROM setof_from_array(ArgGroup2) AS SiteID;
   gcres := GC('DgroupMinLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DgroupMaxLang(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 ArgGroup2 smallint[];
 res NUMERIC := 0;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgLanguageID, ArgDisabilityGroupID);
   gcres := GC('DgroupMaxLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   ArgGroup2 := siteListLangGroup(ArgGroup, ArgLanguageID, ArgTestRunID);
   SELECT INTO res MAX(DsiteMeanLang(SiteID, ArgLanguageID, ArgDisabilityGroupID, ArgTestRunID)) FROM setof_from_array(ArgGroup2) AS SiteID;
   gcres := GC('DgroupMaxLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DsiteNumberLang(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 ArgGroup2 smallint[];
 res NUMERIC := 0;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgLanguageID);
   gcres := GC('DsiteNumberLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   ArgGroup2 := siteListLangGroup(ArgGroup, ArgLanguageID, ArgTestRunID);
   SELECT INTO res COUNT(*) FROM setof_from_array(ArgGroup2) AS SiteID;
   gcres := GC('DsiteNumberLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.DgroupErrorMarginLang(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgDisabilityGroupID eiaodw.DisabilityGroup.DisabilityGroupID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 sdev NUMERIC;
 pnumber NUMERIC;
 res NUMERIC;
 gcargs INT[];
 gcres GenericCacheResult;
BEGIN
   gcargs := GCArg(ArgGroup, ArgLanguageID, ArgDisabilityGroupID);
   gcres := GC('DgroupErrorMarginLang', gcargs, ArgTestRunID); IF gcres.CacheHit THEN RETURN gcres.Result; END IF;
   sdev := DgroupStddevLang(ArgGroup, ArgLanguageID, ArgDisabilityGroupID, ArgTestRunID);
   pnumber := DsiteNumberLang(ArgGroup, ArgLanguageID, ArgTestRunID);
   IF pnumber IS NULL OR pnumber = 0 THEN res := NULL;
   ELSE res := 1.96*sdev/SQRT(pnumber); END IF;
   gcres := GC('DgroupErrorMarginLang', gcargs, ArgTestRunID, res);
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.DgroupContent(
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgMimeTypeID eiaodw.MimeType.MimeTypeID%TYPE,
 ArgInclusionTypeID eiaodw.InclusionType.InclusionTypeID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NUMERIC AS $$
DECLARE
 res NUMERIC;
BEGIN
   SELECT INTO res Result FROM matviews.GroupContent WHERE "Group" = ArgGroup AND MimeTypeID = ArgMimeTypeID AND InclusionTypeID = ArgInclusionTypeID AND TestRunID = ArgTestRunID;
   IF res IS NULL THEN
      SELECT INTO res AVG(DsiteContent(SiteID, ArgMimeTypeID, ArgInclusionTypeID, ArgTestRunID)) FROM setof_from_array(ArgGroup) AS SiteID;
      IF res IS NULL THEN res := -1; END IF;
      --INSERT INTO matviews.GroupContent ("Group", MimeTypeID, InclusionTypeID, Result, TestRunID) VALUES (ArgGroup, ArgMimeTypeID, ArgInclusionTypeID, res, ArgTestRunID); -- FIXME: Commented due to problems with large groups. -- FIXME:MVI also applied
   END IF;
   IF res = -1 THEN res := NULL; END IF;
   return res;
END;
$$ LANGUAGE plpgsql STABLE; -- VOLATILE SECURITY DEFINER -- due to MVI


CREATE OR REPLACE FUNCTION eiaodw.setof_from_array(
 Arg smallint[]
)
RETURNS SETOF smallint AS $$
DECLARE
 q TEXT;
 i INT;
 maxi INT;
BEGIN
   q := '';
   i := array_lower(Arg, 1) - 1;
   maxi := array_upper(Arg, 1);
   LOOP
      i := i + 1;
      IF i IS NULL OR i > maxi THEN
         EXIT;
      ELSE
      RETURN NEXT Arg[i];
      END IF;
   END LOOP;
   RETURN;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.groupCache(
 ArgGroupType text,
 ArgGroupCode text,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS smallint[] AS $$
DECLARE
 res RECORD;
BEGIN
   SELECT INTO res * FROM matviews.GroupCache WHERE GroupType = ArgGroupType AND GroupCode = ArgGroupCode AND TestRunID = ArgTestRunID;
   RETURN res."Group";
END
$$ LANGUAGE plpgsql STABLE;

-- CREATE OR REPLACE FUNCTION eiaodw.groupCache(
--  ArgGroupType text,
--  ArgGroupCode text,
--  ArgTestRunID eiaodw.Subject.TestRunID%TYPE,
--  ArgGroup smallint[]
-- )
-- RETURNS boolean AS $$
-- BEGIN
--    INSERT INTO matviews.GroupCache (GroupType, GroupCode, "Group", TestRunID) VALUES (ArgGroupType, ArgGroupCode, ArgGroup, ArgTestRunID);
--    RETURN TRUE;
-- END;
-- $$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;



--Inserted by MORTEN
CREATE OR REPLACE FUNCTION eiaodw.nutsGroupUnknownNUTS2(
 ArgNutsCode varchar,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
-- A NUTS code can be of type 1, 2, or 3. Further, it can be
-- 'AC' (for Applicant Country).
RETURNS smallint[] AS $$
DECLARE
 r smallint[];
BEGIN
   -- Should be in the mat.views so if we don't find it ther
   -- there are not sites in the group.
   select into res siteid from datastaging.nutslevel3 natural join datastaging.nutslevel2 natural join datastaging.nutslevel1 natural join datastaging.country natural join matviews.site natural join datastaging.site  where nuts1code=ArgNutsCode and nuts2code is null;
   return res;
   IF res IS NOT NULL THEN
      RETURN res;
   ELSE
      RETURN '{}'::smallint[];
   END IF;
END;
$$ LANGUAGE plpgsql STABLE;




CREATE OR REPLACE FUNCTION eiaodw.nutsGroupEULevelSites(
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS smallint[] AS $$
DECLARE
 r smallint[];
 i int := 1;
 sid record;
BEGIN
   FOR sid IN
     select distinct siteid from datastaging.nutslevel3 natural join datastaging.nutslevel2 natural join datastaging.nutslevel1 natural join datastaging.country natural join matviews.site natural join datastaging.site  where countryabbreviation is null and nuts1code is null
   LOOP
       r[i] := sid.SiteID;
       i := i+1;
   END LOOP;
   IF r IS NULL THEN r := '{}'::smallint[]; END IF;
   return r;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.nutsGroupUnknownNUTS1(
 ArgNutsCode varchar,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
-- A NUTS code can be a two letter country abbreviation or 'EU', 'NOTEU', or
-- 'AC' (for Applicant Country).
RETURNS smallint[] AS $$
DECLARE
 r smallint[];
 i int := 1;
 sid record;
BEGIN
   FOR sid IN 
     select distinct siteid from datastaging.nutslevel3 natural join datastaging.nutslevel2 natural join datastaging.nutslevel1 natural join datastaging.country natural join matviews.site natural join datastaging.site  where countryabbreviation=ArgNutsCode and nuts1code is null
   LOOP
       r[i] := sid.SiteID;
       i := i+1;
   END LOOP;
   IF r IS NULL THEN r := '{}'::smallint[]; END IF;
   return r;
END;
$$ LANGUAGE plpgsql STABLE;




--End inserted by MORTEN



CREATE OR REPLACE FUNCTION eiaodw.nutsGroup(
 ArgNutsCode varchar,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
-- A NUTS code can be of type 1, 2, or 3. Further, it can be
-- a two letter country abbreviation or 'EU', 'NOTEU', or
-- 'AC' (for Applicant Country).
RETURNS smallint[] AS $$
DECLARE
 r smallint[];
BEGIN
   IF ArgNutsCode='All' THEN 
      RETURN allGroup(ArgTestRunID); 
   END IF;
   -- Should be in the mat.views so if we don't find it ther
   -- there are not sites in the group.
   r := groupCache('nuts', ArgNutsCode, ArgTestRunID);
   IF r IS NOT NULL THEN 
      RETURN r; 
   ELSE
      RETURN '{}'::smallint[];
   END IF;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.EUMembershipGroup(
 ArgEUMember eiaodw.Subject.EUMember%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS smallint[] AS $$
DECLARE
 r smallint[];
BEGIN
   IF ArgEUMember='All' THEN RETURN allGroup(ArgTestRunID); END IF;
   -- Should be in the mat.views so if we don't find it ther
   -- there are not sites in the group.
   r := groupCache('eumembership', ArgEUMember, ArgTestRunID);
   IF r IS NOT NULL THEN 
      RETURN r; 
   ELSE
      RETURN '{}'::smallint[];
   END IF;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.naceGroup(
 ArgNaceCode eiaodw.Subject.NaceCode%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS smallint[] AS $$
DECLARE
 r smallint[];
BEGIN
   IF ArgNaceCode='All' THEN RETURN allGroup(ArgTestRunID); END IF;
   -- Should be in the mat.views so if we don't find it ther
   -- there are not sites in the group.
   r := groupCache('nace', ArgNaceCode, ArgTestRunID);
   IF r IS NOT NULL THEN 
      RETURN r; 
   ELSE
      RETURN '{}'::smallint[];
   END IF;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.siteListLangGroup( -- fixme, would benifit from genericcache or perhaps intersect of a sitelistlang
 ArgGroup smallint[], --eiaodw.Subject.SiteID%TYPE[]
 ArgLanguageID eiaodw.Language.LanguageID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS smallint[] AS $$
DECLARE
 r smallint[0];
 r2 smallint[];
 i int := 1;
 sid record;
BEGIN
   FOR sid IN SELECT DISTINCT SiteID FROM
    datastaging.Site NATURAL JOIN datastaging.Resource NATURAL JOIN datastaging.ResourceVersion NATURAL JOIN datastaging.LanguageUsage
    WHERE SiteID = ANY(ArgGroup) AND LanguageID = ArgLanguageID AND TestRunID = ArgTestRunID LOOP
      r[i] := sid.SiteID;
      i := i+1;
   END LOOP;
   IF r IS NULL THEN r := '{}'::smallint[]; END IF;
   RETURN r;
END;
$$ LANGUAGE plpgsql volatile security definer;

CREATE OR REPLACE FUNCTION eiaodw.allGroup(
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS smallint[] AS $$
DECLARE
 r smallint[];
BEGIN
   -- Should be in the mat.views so if we don't find it ther
   -- there are not sites in the group.
   r := groupCache('all', 'all', ArgTestRunID);
   IF r IS NOT NULL THEN 
      RETURN r;
   ELSE
      RETURN '{}'::smallint[];
   END IF;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION eiaodw.naceNutsGroup(
 ArgNaceCode eiaodw.Subject.NaceCode%TYPE,
 ArgNutsCode varchar,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS smallint[] AS $$
DECLARE
 r smallint[0];
 i int := 1;
 sid record;
BEGIN
   FOR sid IN SELECT * FROM setof_from_array(naceGroup(ArgNaceCode,ArgTestRunID)) AS SiteID INTERSECT SELECT * FROM setof_from_array(nutsGroup(ArgNutsCode,ArgTestRunID)) LOOP
      r[i] := sid.SiteID;
      i := i+1;
   END LOOP;
   IF r IS NULL THEN r := '{}'::smallint[]; END IF;
   RETURN r;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION eiaodw.getMimeTypeID(
 ArgMimeType varchar
)
RETURNS smallint AS $$
DECLARE
 res smallint;
 mt text[];
BEGIN
   mt := string_to_array(ArgMimeType, '/');
   SELECT INTO res MimeTypeID FROM MimeType WHERE MimeType = mt[1] AND MimeSubType = mt[2];
   RETURN res;
END
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.getInclusionTypeID(
 ArgInclusionType varchar
)
RETURNS smallint AS $$
DECLARE
 res smallint;
BEGIN
   SELECT INTO res InclusionTypeID FROM InclusionType WHERE InclusionType = ArgInclusionType;
   RETURN res;
END
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.getScenarioID(--fixme bad bad bad request, is it needed?
 ArgURL eiaodw.Subject.URL%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS smallint AS $$
DECLARE
 res smallint;
BEGIN
   SELECT INTO res ScenarioID FROM ScenarioCoverage NATURAL JOIN Subject WHERE url = ArgURL;
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.getSiteID(
 ArgSite eiaodw.Subject.Site%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS smallint AS $$
DECLARE
 res smallint;
BEGIN
   --fixme testrunid not in datastaging.site!
   SELECT INTO res SiteID FROM datastaging.Site WHERE Site = ArgSite;
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.getBarrierComputationID(
 ArgBarrierComputationName eiaodw.BarrierComputationVersion.BarrierComputationName%TYPE
)
RETURNS smallint AS $$
DECLARE
 res smallint;
BEGIN
   SELECT INTO res BarrierComputationID FROM BarrierComputationVersion WHERE BarrierComputationName = ArgBarrierComputationName;
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;



CREATE OR REPLACE FUNCTION eiaodw.getLanguageID(
 ArgLanguage eiaodw.Language.Language%TYPE
)
RETURNS smallint AS $$
DECLARE
 res smallint;
BEGIN
   SELECT INTO res LanguageID FROM Language WHERE Language = ArgLanguage;
   IF res IS NULL THEN
      SELECT INTO res LanguageID FROM Language WHERE LanguageAbbreviation = ArgLanguage;
   END IF;
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE TYPE NutsNaceDescription AS(
   CountryAbbreviation TEXT,
   Nuts1Code TEXT,
   Nuts2Code TEXT,
   Nuts3Code TEXT,
   NaceCode TEXT
);

CREATE OR REPLACE FUNCTION eiaodw.getNutsNace(
 ArgSiteID eiaodw.Subject.SiteID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS NutsNaceDescription AS $$
DECLARE
 r NutsNaceDescription;
BEGIN
   SELECT into r DISTINCT CountryAbbreviation, Nuts1Code, Nuts2Code, Nuts3Code, NaceCode FROM datastaging.Site NATURAL JOIN datastaging.NutsLevel3 NATURAL JOIN datastaging.NutsLevel2 NATURAL JOIN datastaging.NutsLevel1 NATURAL JOIN datastaging.Country NATURAL JOIN datastaging.Nace WHERE SiteID = ArgSiteID; -- AND TestRunID = ArgTestRunID was removed when moving to datastaging
   RETURN r;
END;
$$ LANGUAGE plpgsql STABLE;


CREATE OR REPLACE FUNCTION eiaodw.EIAOHash(
 k int[]
)
RETURNS INT AS $$
--based on http://www.cs.hmc.edu/~geoff/classes/hmc.cs070.200109/homework10/hashfuncs.html (CRC variant)
DECLARE
 f8000000 int := -134217728; --0xf8000000 or 11111000000000000000000000000000b
 h int := 0;
 highorder int;
 i int;
 maxi int;
BEGIN
   IF k IS NULL THEN RETURN NULL; END IF; -- implied by function definition, only here to make it complete
   i := array_lower(k, 1) - 1;
   maxi := array_upper(k, 1);
   LOOP
      i := i + 1;
      IF i IS NULL OR i > maxi THEN
         EXIT;
      ELSE
         highorder := h & f8000000;
         h := h << 5;
         h := h # (highorder >> 27);
         h := h # k[i];
      END IF;
   END LOOP;
   RETURN h;
END;
$$ LANGUAGE plpgsql IMMUTABLE RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION eiaodw.GC(
 ArgFunctionName matviews.GenericCache.FunctionName%TYPE,
 ArgArguments matviews.GenericCache.Arguments%TYPE,
 ArgTestRunID matviews.GenericCache.TestRunID%TYPE
)
RETURNS GenericCacheResult AS $$
DECLARE
 Result GenericCacheResult;
BEGIN
   Result := GC(ArgFunctionName, ArgArguments, ArgTestRunID, TRUE);
   RETURN Result;
END;
$$ LANGUAGE plpgsql volatile security definer;

CREATE OR REPLACE FUNCTION eiaodw.GC(
 ArgFunctionName matviews.GenericCache.FunctionName%TYPE,
 ArgArguments matviews.GenericCache.Arguments%TYPE,
 ArgTestRunID matviews.GenericCache.TestRunID%TYPE,
 ArgHitMiss bool
)
RETURNS GenericCacheResult AS $$
DECLARE
 ArgArgumentsHash int4;
 ResRecord record;
 Result GenericCacheResult;
BEGIN
   ArgArgumentsHash := EIAOHash(ArgArguments);
   SELECT * INTO ResRecord
    FROM matviews.GenericCache
    WHERE FunctionName = ArgFunctionName AND ArgumentsHash = ArgArgumentsHash
    AND Arguments = ArgArguments AND TestRunID = ArgTestRunID LIMIT 1;
   Result.CacheHit := ResRecord.TestRunID IS NOT NULL;
   Result.Result := ResRecord.Result;
   IF FALSE AND ArgHitMiss THEN
      IF Result.CacheHit THEN RAISE NOTICE 'GenericCache hit'; ELSE RAISE NOTICE 'GenericCache miss'; END IF;
   END IF;
   RETURN Result;
END;
$$ LANGUAGE plpgsql volatile security definer;

CREATE OR REPLACE FUNCTION eiaodw.GC(
 ArgFunctionName matviews.GenericCache.FunctionName%TYPE,
 ArgArguments matviews.GenericCache.Arguments%TYPE,
 ArgTestRunID matviews.GenericCache.TestRunID%TYPE,
 ArgResult matviews.GenericCache.Result%TYPE
)
RETURNS GenericCacheResult AS $$
DECLARE
 ArgArgumentsHash int4;
 res GenericCacheResult;
BEGIN
   ArgArgumentsHash := EIAOHash(ArgArguments);
   res := GC(ArgFunctionName, ArgArguments, ArgTestRunID, FALSE);
   IF res.CacheHit THEN
      RAISE NOTICE 'GenericCache found previous results in cache, these results are overwritten';
      DELETE FROM matviews.GenericCache
       WHERE FunctionName = ArgFunctionName AND ArgumentsHash = ArgArgumentsHash
       AND Arguments = ArgArguments AND TestRunID = ArgTestRunID;
   END IF;
   INSERT INTO Matviews.GenericCache (FunctionName, ArgumentsHash, Arguments, TestRunID, Result)
    VALUES (ArgFunctionName, ArgArgumentsHash, ArgArguments, ArgTestRunID, ArgResult);
   res := GC(ArgFunctionName, ArgArguments, ArgTestRunID, FALSE);
   IF res.CacheHit IS NULL THEN RAISE EXCEPTION 'GenericCache failed to add result to cache'; END IF;
   RETURN res;
END;
$$ LANGUAGE plpgsql volatile security definer;


CREATE OR REPLACE FUNCTION eiaodw.exhautivesampled(
 ArgSiteID matviews.site.siteID%TYPE,
 ArgTestRunID eiaodw.Subject.TestRunID%TYPE
)
RETURNS boolean AS $$
DECLARE
 res boolean;
BEGIN
   select into res scenariocount+unavailablepages=downloadcount from matviews.site where siteid=ArgSiteID and testrunid=ArgTestRunId;
   RETURN res;
END;
$$ LANGUAGE plpgsql STABLE;



\i GCArg.sql