pears.ComplianceDeskFetch

CREATE PROCEDURE "pears"."ComplianceDeskFetch"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pTempDeskID" CHAR(20),"pDepartmentID" CHAR(2),"pStaffIDField" CHAR(30),"pstaffid" CHAR(20) ) 
BEGIN
  DECLARE "errMsg" long VARCHAR;
  DECLARE "warnMsg" long VARCHAR;
  DECLARE "currScore" DOUBLE;
  DECLARE "possScore" DOUBLE;
  DECLARE "stat" tinyint;
  DECLARE "percent" DOUBLE;
  DECLARE "bActive" tinyint;
  DECLARE "infoplus" CHAR(105);
  DECLARE "personcurrentstates" CHAR(30);
  DECLARE "StaffField" CHAR(30);
  DECLARE LOCAL TEMPORARY TABLE "TemppoolComplianceTmpTable"(
    "personid" CHAR(20) NULL,) NOT transactional;SET "StaffField" = "lower"("isnull"("pStaffIDField",''));
  SET "personcurrentstates" = '['+"isnull"("nullif"("WPKGetSwitchValue"('TDEXPIRYCOMPSTATES',''),''),"WPKGetSwitchValue"('PERSONCURRENTSTATES',''))+']';
  IF("StaffField" = 'staffid2') THEN
    INSERT INTO "TemppoolComplianceTmpTable"( "personid" ) SELECT DISTINCT "person"."personid" AS "pPersonID" FROM "TempPoolMember" KEY JOIN("person","Tempdesk") WHERE "temppoolmember"."tempdeskid" LIKE "pTempDeskID"
        AND "person"."status" LIKE "personcurrentstates" AND "isnull"("person"."divisionid",'') = any(SELECT "divisionid" FROM "DivisionsAllowed")
        AND "tempdesk"."departmentid" = "pDepartmentid" AND "person"."staffid2" = "pstaffid"
  ELSE IF("StaffField" = 'compliancestaffid') THEN
      INSERT INTO "TemppoolComplianceTmpTable"( "personid" ) SELECT DISTINCT "person"."personid" AS "pPersonID" FROM "TempPoolMember" KEY JOIN("person","Tempdesk") WHERE "temppoolmember"."tempdeskid" LIKE "pTempDeskID"
          AND "person"."status" LIKE "personcurrentstates" AND "isnull"("person"."divisionid",'') = any(SELECT "divisionid" FROM "DivisionsAllowed")
          AND "tempdesk"."departmentid" = "pDepartmentid" AND "person"."compliancestaffid" = "pstaffid"
    ELSE IF("StaffField" = 'staffid') THEN
        INSERT INTO "TemppoolComplianceTmpTable"( "personid" ) SELECT DISTINCT "person"."personid" AS "pPersonID" FROM "TempPoolMember" KEY JOIN("person","Tempdesk") WHERE "temppoolmember"."tempdeskid" LIKE "pTempDeskID"
            AND "person"."status" LIKE "personcurrentstates" AND "isnull"("person"."divisionid",'') = any(SELECT "divisionid" FROM "DivisionsAllowed")
            AND "tempdesk"."departmentid" = "pDepartmentid" AND "person"."staffid" = "pstaffid"
      ELSE
        INSERT INTO "TemppoolComplianceTmpTable"( "personid" ) SELECT DISTINCT "person"."personid" AS "pPersonID" FROM "TempPoolMember" KEY JOIN("person","Tempdesk") WHERE "temppoolmember"."tempdeskid" LIKE "pTempDeskID"
            AND "person"."status" LIKE "personcurrentstates" AND "isnull"("person"."divisionid",'') = any(SELECT "divisionid" FROM "DivisionsAllowed")
            AND "tempdesk"."departmentid" = "pDepartmentid"
      END IF END IF END IF;
  DELETE FROM "CompliancePersonStatus" WHERE "tempdeskid" = "ptempdeskid"; -- This field might legitimately contain %
  FOR "PFOR" AS "PCUR" no scroll cursor FOR
    SELECT DISTINCT "personid" AS "pPersonID" FROM "TemppoolComplianceTmpTable" FOR READ ONLY do
    SET "errMsg" = NULL;
    SET "warnMsg" = NULL;
    SET "currScore" = 0;
    SET "possScore" = 0;
    SET "bActive" = 0;
    FOR "PFOR1" AS "PCUR1" no scroll cursor FOR
      SELECT "Description","Status","Info","Score","AchievedScore"
        FROM "ComplianceTable"("pPersonID") FOR READ ONLY do
      SET "bActive" = 1;
      IF "Info" = 'Missing' THEN
        SET "infoplus" = ''
      ELSE
        SET "infoplus" = ' '+"info"
      END IF;
      CASE "status"
      WHEN 1 THEN SET "errMsg" = "string"("errMsg","trim"("Description"),"infoplus",'. ')
      WHEN 2 THEN SET "warnMsg" = "string"("warnMsg","trim"("Description"),"infoplus",'. ')
      END CASE;
      SET "currScore" = "currScore"+"isnull"("AchievedScore",0);
      SET "possScore" = "possScore"+"isnull"("Score",0)
    END FOR;
    IF "bActive" = 1 THEN
      IF "warnMsg" IS NOT NULL THEN
        SET "warnMsg" = "string"('Warning: ',"warnMsg")
      END IF;
      IF "errMsg" IS NOT NULL THEN
        SET "stat" = 1
      ELSE IF "warnMsg" IS NOT NULL THEN
          SET "stat" = 2
        ELSE
          SET "stat" = 0
        END IF END IF;
      IF "possScore" > 0 THEN
        SET "percent" = ("currScore"/"possScore")*100
      ELSE
        SET "percent" = 0
      END IF;
      INSERT INTO "CompliancePersonStatus"( "personid","tempdeskid","description","status","percentage","errordescription","warningdescription" ) VALUES
        ( "pPersonID","pTempDeskID","string"("errMsg","warnMsg"),"stat","percent","errmsg","warnmsg" ) 
    END IF
  END FOR
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."ComplianceDeskFetch" IS 
{CREATE PROCEDURE ComplianceDeskFetch 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(  IN pTempDeskID CHAR(20), pDepartmentID CHAR(2), pStaffIDField CHAR(30), pstaffid CHAR(20)  ) 
BEGIN
  DECLARE errMsg long VARCHAR;
  DECLARE warnMsg long VARCHAR;
  DECLARE currScore DOUBLE;
  DECLARE possScore DOUBLE;
  DECLARE stat tinyint;
  DECLARE percent DOUBLE;
  DECLARE bActive tinyint;
  DECLARE infoplus CHAR(105);
  DECLARE personcurrentstates CHAR(30);
  DECLARE StaffField CHAR(30); 
  DECLARE LOCAL TEMPORARY TABLE TemppoolComplianceTmpTable ( personid CHAR(20)) NOT transactional; 
  SET StaffField = LOWER(isnull (pStaffIDField,'')); 
  SET personcurrentstates = '['+isnull(NULLIF(WPKGetSwitchValue ('TDEXPIRYCOMPSTATES',''),''), WPKGetSwitchValue ('PERSONCURRENTSTATES','') ) +']';
  IF (StaffField='staffid2') THEN
      INSERT INTO  TemppoolComplianceTmpTable  (personid)  SELECT DISTINCT person.personid AS pPersonID FROM TempPoolMember KEY JOIN (person, Tempdesk) WHERE temppoolmember.tempdeskid LIKE pTempDeskID 
      AND person.status LIKE personcurrentstates AND isnull(person.divisionid,'') = any(SELECT divisionid FROM DivisionsAllowed) 
     AND tempdesk.departmentid = pDepartmentid AND person.staffid2 = pstaffid 
  ELSE IF  (StaffField='compliancestaffid') THEN    
      INSERT INTO TemppoolComplianceTmpTable  (personid) SELECT DISTINCT person.personid AS pPersonID FROM TempPoolMember KEY JOIN (person, Tempdesk) WHERE temppoolmember.tempdeskid LIKE pTempDeskID 
       AND person.status LIKE personcurrentstates AND isnull(person.divisionid,'') = any(SELECT divisionid FROM DivisionsAllowed) 
      AND tempdesk.departmentid = pDepartmentid AND person.compliancestaffid = pstaffid 
  ELSE IF  (StaffField='staffid') THEN
      INSERT INTO TemppoolComplianceTmpTable  (personid) SELECT DISTINCT person.personid AS pPersonID FROM TempPoolMember KEY JOIN (person, Tempdesk) WHERE temppoolmember.tempdeskid LIKE pTempDeskID 
      AND person.status LIKE personcurrentstates AND isnull(person.divisionid,'') = any(SELECT divisionid FROM DivisionsAllowed) 
      AND tempdesk.departmentid = pDepartmentid AND person.staffid =  pstaffid
  ELSE 
      INSERT INTO TemppoolComplianceTmpTable  (personid) SELECT DISTINCT person.personid AS pPersonID FROM TempPoolMember KEY JOIN (person, Tempdesk) WHERE temppoolmember.tempdeskid LIKE pTempDeskID 
      AND person.status LIKE personcurrentstates AND isnull(person.divisionid,'') = any(SELECT divisionid FROM DivisionsAllowed) 
      AND tempdesk.departmentid = pDepartmentid ; 
   END IF END IF END IF  ; 
  DELETE FROM CompliancePersonStatus WHERE tempdeskid = ptempdeskid; -- This field might legitimately contain %
  FOR PFOR AS PCUR no scroll cursor FOR
    SELECT DISTINCT personid AS pPersonID FROM TemppoolComplianceTmpTable FOR READ ONLY do
    SET errMsg = NULL;
    SET warnMsg = NULL;
    SET currScore = 0;
    SET possScore = 0;
    SET bActive = 0;
    FOR PFOR1 AS PCUR1 no scroll cursor FOR
      SELECT Description,STATUS,Info,Score,AchievedScore
        FROM ComplianceTable(pPersonID) FOR READ ONLY do
      SET bActive = 1;
      IF Info = 'Missing' THEN
        SET infoplus = ''
      ELSE
        SET infoplus = ' '+info
      END IF;
      CASE STATUS
      WHEN 1 THEN SET errMsg = string(errMsg,TRIM(Description),infoplus,'. ')
      WHEN 2 THEN SET warnMsg = string(warnMsg,TRIM(Description),infoplus,'. ')
      END CASE;
      SET currScore = currScore+isnull(AchievedScore,0);
      SET possScore = possScore+isnull(Score,0)
    END FOR;
    IF bActive = 1 THEN
      IF warnMsg IS NOT NULL THEN
        SET warnMsg = string('Warning: ',warnMsg)
      END IF;
      IF errMsg IS NOT NULL THEN
        SET stat = 1
      ELSE IF warnMsg IS NOT NULL THEN
          SET stat = 2
        ELSE
          SET stat = 0
        END IF END IF;
      IF possScore > 0 THEN
        SET percent = (currScore/possScore)*100
      ELSE
        SET percent = 0
      END IF;
      INSERT INTO CompliancePersonStatus( personid,tempdeskid,description,STATUS,percentage,errordescription, warningdescription )
          VALUES( pPersonID,pTempDeskID,string(errMsg,warnMsg),stat,percent,errmsg, warnmsg ) 
    END IF
  END FOR
END
}