pears.ComplianceTableAdd

CREATE PROCEDURE "pears"."ComplianceTableAdd"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pPersonID" CHAR(20),IN "pComplianceDomainID" CHAR(20),IN "AtDate" DATE,INOUT "DupeCheck" long VARCHAR,INOUT "Cnt" SMALLINT,IN "pClientCode" CHAR(12) DEFAULT NULL ) 
BEGIN
  -- Result.Status values 0=ok 1=fail 2=warn 
  DECLARE "curdate" DOUBLE;
  DECLARE "tdesc" CHAR(200);
  DECLARE "tldesc" long VARCHAR;
  DECLARE "ttype" CHAR(1);
  DECLARE "texpiry" SMALLINT;
  DECLARE "texpirybehaviour" SMALLINT;
  DECLARE "tval" DOUBLE;
  DECLARE "xstatus" tinyint;
  DECLARE "xachieved" SMALLINT;
  DECLARE "xinfo" CHAR(100);
  DECLARE "xdate" CHAR(20);
  DECLARE "bok" tinyint;
  SET "DupeCheck" = "isnull"("DupeCheck",'');
  IF "charindex"("string"('|',"pComplianceDomainID",'|'),"DupeCheck") > 0 THEN
    RETURN
  END IF;
  SET "DupeCheck" = "string"("DupeCheck",'|',"pComplianceDomainID",'|');
  SET "Cnt" = "isnull"("Cnt",0);
  SET "curdate" = "datediff"("day","date"('1899-12-30'),"AtDate");
  FOR "PFOR" AS "PCUR" no scroll cursor FOR
    SELECT "cdtag"."tagid" AS "tid",
      CASE "cdtag"."taglocation"
      WHEN 'P' THEN 'P'
      WHEN 'A%' THEN "string"('A',"cd"."departmentid")
      WHEN 'R%' THEN "string"('R',"cd"."departmentid") END AS "tloc",
      IF "cdtag"."tagchoiceid" = 'N/A' THEN NULL ELSE "cdtag"."tagchoiceid" endif AS "tcid",
      "cdtag"."behaviour" AS "tbehaviour","cdtag"."score" AS "tscore","cdtag"."minvalue" AS "tmin",
      IF "cdtag"."taglocation" LIKE 'R%' THEN 'Ref' ELSE 'App' endif AS "dtype"
      FROM "ComplianceDomainTag" AS "cdtag" KEY JOIN "ComplianceDomain" AS "cd"
      WHERE "cdtag"."ComplianceDomainID" = "pComplianceDomainID"
      ORDER BY "cdtag"."SortOrder" ASC FOR READ ONLY do
    SELECT "string"("tag"."description","ifnull"("tc"."description",'',': '),"tc"."description"),
      "string"("tag"."longdescription","ifnull"("tc"."longdescription",'',': '),"tc"."longdescription"),
      "tag"."tagtype","tag"."expiry","tag"."expirybehaviour",
      IF "tag"."tagtype" IN( 'T','U' ) THEN
        (SELECT FIRST 1.0 FROM "tagvalue" WHERE "taglocation" = "tloc" AND "tagid" = "tid" AND "id" = "pPersonID" AND "textvalue" <> '')
      ELSE
        (SELECT "max"("value") FROM "tagvalue" WHERE "taglocation" = "tloc" AND "tagid" = "tid" AND "id" = "pPersonID" AND "value" <> 0 AND("tcid" IS NULL OR "tagchoiceid" = "tcid"))
      endif
      INTO "tdesc","tldesc","ttype","texpiry","texpirybehaviour","tval"
      FROM "tag" LEFT OUTER JOIN "tagchoice" AS "tc" ON "tc"."taglocation" = "tloc" AND "tc"."tagid" = "tid" AND "tc"."tagchoiceid" = "tcid"
      WHERE "tag"."taglocation" = "tloc" AND "tag"."tagid" = "tid";
    IF "tval" IS NULL THEN
      SET "xstatus" = "tbehaviour";
      SET "xachieved" = 0;
      SET "xinfo" = 'Missing'
    ELSE IF "ttype" = 'D' AND "texpirybehaviour" > 0 AND "tval"-"isnull"("texpiry",0) <= "curdate" THEN
        SET "xachieved" = 0;
        SET "xdate" = "dateformat"("dateadd"("day","tval","date"('1899-12-30')),'dd/mm/yy');
        IF "tval" <= "curdate" THEN
          SET "xstatus" = "texpirybehaviour";
          SET "xinfo" = "string"('Expired ',"xdate")
        ELSE
          SET "xstatus" = 2;
          SET "xinfo" = "string"('Expiring ',"xdate")
        END IF
      ELSE IF "tmin" > 1 AND "ttype" IN( 'N','G','S' ) AND "tval" < "tmin" THEN
          SET "xstatus" = "tbehaviour";
          SET "xachieved" = 0;
          SET "xinfo" = "string"('< ',"tmin")
        ELSE
          SET "xstatus" = 0;
          SET "xachieved" = "tscore";
          SET "xinfo" = 'Ok'
        END IF END IF END IF;
    SET "cnt" = "cnt"+1;
    IF "xstatus" = 3 THEN // Score ONLY
      SET "xstatus" = 0
    END IF;
    INSERT INTO "ComplianceTmpTbl"( "Description","LongDescription","Behaviour","Status","Score","AchievedScore","Info","SortOrder","TagLocation","TagID","TagChoiceID","type" ) VALUES
      ( "tdesc","tldesc","tbehaviour","xstatus","tscore","xachieved","xinfo","cnt","tloc","tid","tcid","dtype" ) 
  END FOR;
  FOR "PFOR" AS "PCURD" no scroll cursor FOR
    SELECT "cdt"."documenttypeid" AS "tDoctypeID","dt"."description" AS "dtdesc","isnull"("expirylead",0) AS "texpirylead",
      "cdt"."behaviour" AS "tbehaviour","cdt"."score" AS "tscore"
      FROM "ComplianceDocumentType" AS "cdt" KEY JOIN "documenttype" AS "dt"
      WHERE "cdt"."ComplianceDomainID" = "pComplianceDomainID" AND "isnull"("dt"."iscompliance",0) = 1 AND "isnull"("dt"."defunct",0) = 0
      ORDER BY "dt"."SortOrder" ASC FOR READ ONLY do
    SET "tdesc" = NULL;
    SELECT top 1 "string"("dtdesc",':',"o"."description"),"o"."expirydate"
      INTO "tdesc","texpiry"
      FROM "oledocument" AS "o" JOIN "blobstore" AS "b" ON("class" = 'O' AND "o"."oledocumentid" = "b"."id")
      WHERE "o"."documenttypeid" = "tDoctypeID" AND "o"."ownertype" = 'p' AND "ownerid" = "ppersonid"
      AND "whenarchived" IS NULL ORDER BY "o"."defunct" ASC,"whenarchived" ASC;
    IF "tdesc" IS NULL THEN
      SET "tdesc" = "dtdesc";
      SET "xstatus" = "tbehaviour";
      SET "xachieved" = 0;
      SET "xinfo" = 'Missing'
    ELSE IF(NOT(("texpiry" IS NOT NULL) AND("texpirylead" > 0))) OR("texpirybehaviour" = 0) THEN
        SET "xstatus" = 0;
        SET "xachieved" = "tscore";
        SET "xinfo" = 'Ok'
      ELSE
        IF "dateadd"("day","texpirylead","texpiry") <= "curdate" THEN
          SET "xachieved" = 0;
          SET "xstatus" = "texpirybehaviour";
          SET "xinfo" = "string"('Expired ',"xdate")
        ELSE
          SET "xstatus" = 2;
          SET "xinfo" = "string"('Expiring ',"xdate")
        END IF END IF END IF;
    SET "cnt" = "cnt"+1;
    IF "xstatus" = 3 THEN // Score ONLY
      SET "xstatus" = 0
    END IF;
    INSERT INTO "ComplianceTmpTbl"( "Description","LongDescription","Behaviour","Status","Score","AchievedScore","Info","SortOrder","type" ) VALUES
      ( "tdesc","tdesc","tbehaviour","xstatus","tscore","xachieved","xinfo","cnt",'Doc' ) 
  END FOR;
  FOR "PFOR" AS "PCURR" no scroll cursor FOR
    SELECT "referencecount","score" AS "rscore","string"(CASE "reftype"
      WHEN 1 THEN "isnull"((SELECT "WPKMaintainGetSwitchValue"('REFERTYPE1','','L')),'Personal')
      WHEN 2 THEN "isnull"((SELECT "WPKMaintainGetSwitchValue"('REFERTYPE2','','L')),'Professional')
      WHEN 3 THEN 'Other'
      WHEN 4 THEN "isnull"((SELECT "WPKMaintainGetSwitchValue"('REFERTYPE3','','L')),'/N/A') END,
      ' ( ',"referencecount",' required, ',"rcnt",' returned)') AS "rdesc",
      "isnull"((SELECT "count"() FROM "referencerequest" AS "r" WHERE "r"."reftype" = "c"."reftype" AND "personid" = "pPersonID" AND "whencompleted" IS NOT NULL),0) AS "rcnt",
      IF "rcnt" >= "referencecount" THEN "score" ELSE 0 endif AS "tscore"
      FROM "ComplianceReferenceType" AS "c" WHERE "referencecount" > 0 AND "c"."ComplianceDomainID" = "pComplianceDomainID" ORDER BY "c"."reftype" ASC do
    SET "cnt" = "cnt"+1;
    INSERT INTO "ComplianceTmpTbl"( "Description","LongDescription","Status","Score","AchievedScore","Info","SortOrder","type" ) VALUES
      ( "rdesc","rdesc",IF "tscore" = 1 THEN 0 ELSE 1 endif,"rscore","tscore",IF "tscore" = 1 THEN 'Ok' ELSE 'Missing' endif,"cnt",'Ref Count' ) 
  END FOR;
  FOR "PFOR1" AS "PCUR1" no scroll cursor FOR
    SELECT "ld"."ComplianceLinkedDomainID","ld"."IfTagLocation","ld"."IfTagID","ld"."IfTagChoiceID","ld"."IfTagChoiceNegate",(SELECT "DepartmentID" FROM "ComplianceDomain" WHERE "ComplianceDomainID" = "pComplianceDomainID") AS "DeptID"
      FROM "ComplianceLinkedDomain" AS "ld" JOIN "ComplianceDomain" AS "cd" ON "ld"."ComplianceLinkedDomainID" = "cd"."ComplianceDomainID"
      WHERE "ld"."ComplianceDomainID" = "pComplianceDomainID" AND("cd"."ClientTempComplianceCode" IS NULL OR "cd"."ClientTempComplianceCode" = "pClientCode") FOR READ ONLY do
    SET "bok" = 0;
    IF "IfTagLocation" IS NULL OR "IfTagID" IS NULL OR "IfTagChoiceID" IS NULL THEN
      SET "bok" = 1
    ELSE
      SELECT FIRST 1 INTO "bok" FROM "tagvalue" WHERE "id" = "pPersonID" AND "taglocation" = (IF "iftaglocation" = 'P' THEN 'P' ELSE 'A'+"DeptID" endif) AND "tagid" = "iftagid" AND "tagchoiceid" = "iftagchoiceid";
      IF "IfTagChoiceNegate" = 1 THEN
        IF "bok" = 1 THEN SET "bok" = 0
        ELSE SET "bok" = 1 END IF
      END IF
    END IF;
    IF "bok" = 1 THEN
      CALL "ComplianceTableAdd"("pPersonID","ComplianceLinkedDomainID","AtDate","DupeCheck","Cnt","pClientCode")
    END IF
  END FOR
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."ComplianceTableAdd" IS 
{CREATE PROCEDURE ComplianceTableAdd 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( IN pPersonID CHAR(20),IN pComplianceDomainID CHAR(20),IN AtDate DATE,INOUT DupeCheck long VARCHAR,INOUT Cnt SMALLINT,IN pClientCode CHAR(12) DEFAULT NULL ) 
BEGIN
  -- Result.Status values 0=ok 1=fail 2=warn 
  DECLARE curdate DOUBLE;
  DECLARE tdesc CHAR(200);
  DECLARE tldesc long VARCHAR;
  DECLARE ttype CHAR(1);
  DECLARE texpiry SMALLINT;
  DECLARE texpirybehaviour SMALLINT;
  DECLARE tval DOUBLE;
  DECLARE xstatus tinyint;
  DECLARE xachieved SMALLINT;
  DECLARE xinfo CHAR(100);
  DECLARE xdate CHAR(20);
  DECLARE bok tinyint;
  SET DupeCheck = isnull(DupeCheck,'');
  IF charindex(string('|',pComplianceDomainID,'|'),DupeCheck) > 0 THEN
    RETURN
  END IF;
  SET DupeCheck = string(DupeCheck,'|',pComplianceDomainID,'|');
  SET Cnt = isnull(Cnt,0);
  SET curdate = datediff(DAY,DATE('1899-12-30'),AtDate);
  FOR PFOR AS PCUR no scroll cursor FOR
    SELECT cdtag.tagid AS tid,
      CASE cdtag.taglocation 
         WHEN 'P' THEN 'P' 
         WHEN 'A%' THEN  string( 'A', cd.departmentid )  
         WHEN 'R%' THEN  string( 'R', cd.departmentid )  
         END  AS tloc,
      IF cdtag.tagchoiceid = 'N/A' THEN NULL ELSE cdtag.tagchoiceid endif AS tcid,
      cdtag.behaviour AS tbehaviour,cdtag.score AS tscore,cdtag.minvalue AS tmin,
      IF  cdtag.taglocation LIKE 'R%' THEN 'Ref' ELSE 'App' END IF AS dtype
      FROM ComplianceDomainTag AS cdtag KEY JOIN ComplianceDomain AS cd
      WHERE cdtag.ComplianceDomainID = pComplianceDomainID
      ORDER BY cdtag.SortOrder ASC FOR READ ONLY do
    SELECT string(tag.description,ifnull(tc.description,'',': '),tc.description),
      string(tag.longdescription,ifnull(tc.longdescription,'',': '),tc.longdescription),
      tag.tagtype,tag.expiry,tag.expirybehaviour,
      IF tag.tagtype IN( 'T','U' ) THEN
        (SELECT FIRST 1.0 FROM tagvalue WHERE taglocation = tloc AND tagid = tid AND id = pPersonID AND textvalue <> '')
      ELSE
        (SELECT MAX(VALUE) FROM tagvalue WHERE taglocation = tloc AND tagid = tid AND id = pPersonID AND VALUE <> 0 AND(tcid IS NULL OR tagchoiceid = tcid))
      endif
      INTO tdesc,tldesc,ttype,texpiry,texpirybehaviour,tval
      FROM tag LEFT OUTER JOIN tagchoice AS tc ON tc.taglocation = tloc AND tc.tagid = tid AND tc.tagchoiceid = tcid
      WHERE tag.taglocation = tloc AND tag.tagid = tid;
    IF tval IS NULL THEN
      SET xstatus = tbehaviour;
      SET xachieved = 0;
      SET xinfo = 'Missing'
    ELSE IF ttype = 'D' AND texpirybehaviour > 0 AND tval-isnull(texpiry,0) <= curdate THEN
        SET xachieved = 0;
        SET xdate = dateformat(dateadd(DAY,tval,DATE('1899-12-30')),'dd/mm/yy');
        IF tval <= curdate THEN
          SET xstatus = texpirybehaviour;
          SET xinfo = string('Expired ',xdate)
        ELSE
          SET xstatus = 2;
          SET xinfo = string('Expiring ',xdate)
        END IF
      ELSE IF tmin > 1 AND ttype IN( 'N','G','S' ) AND tval < tmin THEN
          SET xstatus = tbehaviour;
          SET xachieved = 0;
          SET xinfo = string('< ',tmin)
        ELSE
          SET xstatus = 0;
          SET xachieved = tscore;
          SET xinfo = 'Ok'
        END IF
      END IF END IF;
    SET cnt = cnt+1;
    IF xstatus = 3 THEN // Score ONLY
      SET xstatus = 0
    END IF;
    INSERT INTO ComplianceTmpTbl( Description,LongDescription,Behaviour,STATUS,Score,AchievedScore,Info,SortOrder,TagLocation,TagID,TagChoiceID,TYPE ) VALUES
      ( tdesc,tldesc,tbehaviour,xstatus,tscore,xachieved,xinfo,cnt,tloc,tid,tcid,dtype ) 
  END FOR;
  FOR PFOR AS PCURD no scroll cursor FOR
    SELECT cdt.documenttypeid  AS tDoctypeID, dt.description AS dtdesc, isnull(expirylead,0) AS texpirylead, 
      cdt.behaviour AS tbehaviour,cdt.score AS tscore
      FROM ComplianceDocumentType cdt KEY JOIN documenttype dt 
      WHERE cdt.ComplianceDomainID = pComplianceDomainID AND isnull(dt.iscompliance,0)=1 AND isnull(dt.defunct,0)=0 
      ORDER BY dt.SortOrder ASC FOR READ ONLY do
    SET tdesc = NULL;
    SELECT top 1 string(dtdesc,':', o.description) , o.expirydate
      INTO  tdesc,texpiry
      FROM oledocument o JOIN blobstore b ON (class = 'O' AND o.oledocumentid = b.id) 
      WHERE o.documenttypeid =  tDoctypeID AND o.ownertype = 'p' AND ownerid = ppersonid AND 
            whenarchived IS NULL  ORDER BY o.defunct,whenarchived;
    IF tdesc IS NULL THEN
      SET tdesc = dtdesc;
      SET xstatus = tbehaviour;
      SET xachieved = 0;
      SET xinfo = 'Missing' ;
    ELSE IF  (NOT  ((texpiry IS NOT  NULL) AND (texpirylead>0))) OR (texpirybehaviour=0)  THEN   
          SET xstatus = 0;
          SET xachieved = tscore;
          SET xinfo = 'Ok' ; 
    ELSE    
      IF  dateadd( DAY, texpirylead, texpiry)  <= curdate THEN
          SET xachieved = 0;
          SET xstatus = texpirybehaviour;
          SET xinfo = string('Expired ',xdate); 
     ELSE  
          SET xstatus = 2;
          SET xinfo = string('Expiring ',xdate);
        END IF END IF END IF  ;
    SET cnt = cnt+1;
    IF xstatus = 3 THEN // Score ONLY
      SET xstatus = 0
    END IF;
    INSERT INTO ComplianceTmpTbl( Description,LongDescription,Behaviour,STATUS,Score,AchievedScore,Info,SortOrder, TYPE) VALUES
      ( tdesc,tdesc,tbehaviour,xstatus,tscore,xachieved,xinfo,cnt,'Doc') 
  END FOR;
  FOR PFOR AS PCURR no scroll cursor FOR
 SELECT  referencecount, score AS rscore, string(CASE reftype  
  WHEN 1 THEN isnull((SELECT  WPKMaintainGetSwitchValue ('REFERTYPE1', '','L')), 'Personal') 
  WHEN 2 THEN isnull((SELECT  WPKMaintainGetSwitchValue ('REFERTYPE2', '','L')), 'Professional') 
  WHEN 3 THEN 'Other'
  WHEN 4 THEN  isnull((SELECT  WPKMaintainGetSwitchValue ('REFERTYPE3', '','L')), '/N/A') 
  END, ' ( ',referencecount,' required, ', rcnt, ' returned)') AS rdesc,
 isnull((SELECT COUNT(*) FROM referencerequest r WHERE r.reftype=c.reftype AND personid  = pPersonID AND whencompleted IS NOT NULL ),0) AS rcnt, 
 IF rcnt>=referencecount THEN score ELSE 0 END IF AS tscore 
  FROM   ComplianceReferenceType c WHERE referencecount>0 AND c.ComplianceDomainID= pComplianceDomainID  ORDER BY c.reftype do 
    SET cnt = cnt+1;
    INSERT INTO ComplianceTmpTbl( Description,LongDescription,STATUS, Score,AchievedScore,Info,SortOrder, TYPE) VALUES
      ( rdesc,rdesc, IF tscore=1 THEN 0 ELSE 1 END IF, rscore,tscore,  IF tscore=1 THEN 'Ok' ELSE 'Missing' END IF,   cnt,'Ref Count') 
  END FOR;
  FOR PFOR1 AS PCUR1 no scroll cursor FOR
    SELECT ld.ComplianceLinkedDomainID,ld.IfTagLocation,ld.IfTagID,ld.IfTagChoiceID,ld.IfTagChoiceNegate,(SELECT DepartmentID FROM ComplianceDomain WHERE ComplianceDomainID = pComplianceDomainID) AS DeptID
      FROM ComplianceLinkedDomain ld JOIN ComplianceDomain cd ON ld.ComplianceLinkedDomainID=cd.ComplianceDomainID
      WHERE ld.ComplianceDomainID = pComplianceDomainID AND (cd.ClientTempComplianceCode IS NULL OR cd.ClientTempComplianceCode=pClientCode) FOR READ ONLY do
    SET bok = 0;
    IF IfTagLocation IS NULL OR IfTagID IS NULL OR IfTagChoiceID IS NULL THEN
      SET bok = 1
    ELSE
      SELECT FIRST 1 INTO bok FROM tagvalue WHERE id = pPersonID AND taglocation = (IF iftaglocation = 'P' THEN 'P' ELSE 'A'+DeptID endif) AND tagid = iftagid AND tagchoiceid = iftagchoiceid;
      IF IfTagChoiceNegate=1 THEN
         IF bok=1 THEN SET bok=0 ELSE SET bok=1 END IF;
      END IF;
    END IF;
    IF bok = 1 THEN
      CALL ComplianceTableAdd(pPersonID,ComplianceLinkedDomainID,AtDate,DupeCheck,Cnt,pClientCode)
    END IF
  END FOR
END
}