pears.NetSearchDateOffsetValues

CREATE PROCEDURE "pears"."NetSearchDateOffsetValues"( IN "pWebUserID" CHAR(20),IN "pGroupID" CHAR(10),IN "pFormName" CHAR(25) ) 
RESULT( "StartDate" CHAR(10),"EndDate" CHAR(10) ) 
-- 20121109 look up default dates for searches on IQXNet.
-- 20130201 convert from Geneva to Standard Extended
BEGIN
  DECLARE "TagString" CHAR(2);
  DECLARE "StartOffset" CHAR(25);
  DECLARE "EndOffset" CHAR(25);
  DECLARE "StartDefault" INTEGER;
  DECLARE "EndDefault" INTEGER;
  DECLARE "StartUnit" CHAR(2);
  DECLARE "EndUnit" CHAR(2);
  -- defaults in case agency questions not set
  SET "StartDefault" = 31;
  SET "EndDefault" = 7;
  SET "StartUnit" = 'd';
  SET "EndUnit" = 'd';
  SET "TagString" = "pGroupID"+"pFormName";
  --look up value in question. d7= seven days, m1= one month. 
  SELECT "textvalue" INTO "StartOffset" FROM "tagvalue" WHERE "tagid" = "TagString"+'S' AND "taglocation" = 'X';
  SELECT "textvalue" INTO "EndOffset" FROM "tagvalue" WHERE "tagid" = "TagString"+'E' AND "taglocation" = 'X';
  IF "StartOffset" IS NOT NULL THEN
    SET "StartUnit" = "substring"("StartOffset",1,1);
    SET "StartDefault" = "substring"("StartOffset",2,5)
  END IF;
  IF "EndOffset" IS NOT NULL THEN
    SET "EndUnit" = "substring"("EndOffset",1,1);
    SET "EndDefault" = "substring"("EndOffset",2,5)
  END IF;
  IF "StartUnit" = 'd' THEN
    SELECT "dateformat"("dateadd"("dd",-"StartDefault","today"()),'dd/mm/yyyy') INTO "StartOffset" FROM "dummy"
  END IF;
  IF "StartUnit" = 'm' THEN
    SELECT "dateformat"("dateadd"("mm",-"StartDefault","today"()),'dd/mm/yyyy') INTO "StartOffset" FROM "dummy"
  END IF;
  IF "StartUnit" = 'b' THEN
    SELECT "string"('01/',"month"("today"()),'/',"year"("today"())) INTO "StartOffset" FROM "dummy"
  END IF;
  IF "StartUnit" = 'w' THEN
    SELECT "dateformat"("dateadd"("dd",-"datepart"("dw","today"())+2,"today"()),'dd/mm/yyyy') INTO "StartOffset" FROM "dummy"
  END IF;
  IF "EndUnit" = 'd' THEN
    SELECT "dateformat"("dateadd"("dd","EndDefault","today"()),'dd/mm/yyyy') INTO "EndOffset" FROM "dummy"
  END IF;
  IF "EndUnit" = 'm' THEN
    SELECT "dateformat"("dateadd"("mm","EndDefault","today"()),'dd/mm/yyyy') INTO "EndOffset" FROM "dummy"
  END IF;
  IF "EndUnit" = 'e' THEN
    SELECT "dateformat"("dateadd"("day",-1,CAST("dateformat"("dateadd"("month",1,"now"()),'yyyy-mm')+'-01' AS DATE)),'dd/mm/yyyy') INTO "EndOffset" FROM "dummy"
  END IF;
  IF "EndUnit" = 'w' THEN
    SELECT "dateformat"("dateadd"("dd",-"datepart"("dw","today"())+8,"today"()),'dd/mm/yyyy') INTO "EndOffset" FROM "dummy"
  END IF;
  SELECT "StartOffset","EndOffset"
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetSearchDateOffsetValues" IS 
{CREATE PROCEDURE pears."NetSearchDateOffsetValues"( IN "pWebUserID" CHAR(20),IN "pGroupID" CHAR(10),IN "pFormName" CHAR(25) ) 
RESULT( "StartDate" CHAR(10),"EndDate" CHAR(10) ) 
-- 20121109 look up default dates for searches on IQXNet.
-- 20130201 convert from Geneva to Standard Extended
BEGIN
  DECLARE "TagString" CHAR(2);
  DECLARE "StartOffset" CHAR(25);
  DECLARE "EndOffset" CHAR(25);
  DECLARE "StartDefault" INTEGER;
  DECLARE "EndDefault" INTEGER;
  DECLARE "StartUnit" CHAR(2);
  DECLARE "EndUnit" CHAR(2);
  -- defaults in case agency questions not set
  SET "StartDefault" = 31;
  SET "EndDefault" = 7;
  SET "StartUnit" = 'd';
  SET "EndUnit" = 'd';
  SET "TagString" = "pGroupID"+"pFormName";
  --look up value in question. d7= seven days, m1= one month. 
  SELECT "textvalue" INTO "StartOffset" FROM "tagvalue" WHERE "tagid" = "TagString"+'S' AND "taglocation" = 'X';
  SELECT "textvalue" INTO "EndOffset" FROM "tagvalue" WHERE "tagid" = "TagString"+'E' AND "taglocation" = 'X';
  IF "StartOffset" IS NOT NULL THEN
    SET "StartUnit" = "substring"("StartOffset",1,1);
    SET "StartDefault" = "substring"("StartOffset",2,5)
  END IF;
  IF "EndOffset" IS NOT NULL THEN
    SET "EndUnit" = "substring"("EndOffset",1,1);
    SET "EndDefault" = "substring"("EndOffset",2,5)
  END IF;
  IF "StartUnit" = 'd' THEN
    SELECT "dateformat"("dateadd"("dd",-"StartDefault","today"()),'dd/mm/yyyy') INTO "StartOffset" FROM "dummy"
  END IF;
  IF "StartUnit" = 'm' THEN
    SELECT "dateformat"("dateadd"("mm",-"StartDefault","today"()),'dd/mm/yyyy') INTO "StartOffset" FROM "dummy"
  END IF;
  IF "StartUnit" = 'b' THEN
    SELECT "string"('01/',"month"("today"()),'/',"year"("today"())) INTO "StartOffset" FROM "dummy"
  END IF;
  IF "StartUnit" = 'w' THEN
    SELECT "dateformat"("dateadd"("dd",-"datepart"("dw","today"())+2,"today"()),'dd/mm/yyyy') INTO "StartOffset" FROM "dummy"
  END IF;
  IF "EndUnit" = 'd' THEN
    SELECT "dateformat"("dateadd"("dd","EndDefault","today"()),'dd/mm/yyyy') INTO "EndOffset" FROM "dummy"
  END IF;
  IF "EndUnit" = 'm' THEN
    SELECT "dateformat"("dateadd"("mm","EndDefault","today"()),'dd/mm/yyyy') INTO "EndOffset" FROM "dummy"
  END IF;
  IF "EndUnit" = 'e' THEN
    SELECT "dateformat"("dateadd"("day",-1,CAST("dateformat"("dateadd"("month",1,"now"()),'yyyy-mm')+'-01' AS DATE)),'dd/mm/yyyy') INTO "EndOffset" FROM "dummy"
  END IF;
  IF "EndUnit" = 'w' THEN
    SELECT "dateformat"("dateadd"("dd",-"datepart"("dw","today"())+8,"today"()),'dd/mm/yyyy') INTO "EndOffset" FROM "dummy"
  END IF;
  SELECT "StartOffset","EndOffset"
END
}