pears.NetCandidateCompliance

CREATE PROCEDURE "pears"."NetCandidateCompliance"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "Description" CHAR(200),"Behaviour" tinyint,"Status" tinyint,"Score" SMALLINT,"AchievedScore" SMALLINT,"Info" CHAR(100),"Explanation" long VARCHAR ) 
BEGIN
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL WHERE 0 = 1
  ELSE
    SELECT "Description","Behaviour","Status","Score","AchievedScore","Info",
      (SELECT FIRST "email" FROM "tagCompliance" WHERE "taglocation" = "ct"."taglocation" AND "tagid" = "ct"."tagid") AS "Explanation"
      FROM "ComplianceTable"("pPersonID") AS "ct" WHERE "Behaviour" <= 2
  END IF
END /* DOC
2019-7-25 MHS created for iw-768
*/
/* TEST
call NetTestSetup('');
select * from NetCandidateCompliance('x12345yz');
expect EOF;
select * from NetCandidateCompliance('TEST','TEST');
-- Just run it. Not bothered about result;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateCompliance" IS 
{CREATE PROCEDURE pears.NetCandidateCompliance( IN pWebUserID CHAR(20), IN pPersonID CHAR(20) DEFAULT NULL ) 
RESULT( Description CHAR(200), Behaviour tinyint, STATUS tinyint, Score SMALLINT, AchievedScore SMALLINT, Info CHAR(100),Explanation long VARCHAR) 
BEGIN
SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
IF pPersonID IS NULL THEN
   SELECT NULL,NULL,NULL,NULL,NULL,NULL,NULL WHERE 0=1
ELSE
  SELECT Description,Behaviour,STATUS,Score,AchievedScore,Info, 
    (SELECT FIRST email FROM tagCompliance WHERE taglocation=ct.taglocation AND tagid=ct.tagid) AS Explanation
  FROM ComplianceTable(pPersonID) ct WHERE Behaviour <= 2
END IF;
END
 
/* DOC
2019-7-25 MHS created for iw-768
*/
/* TEST
call NetTestSetup('');
select * from NetCandidateCompliance('x12345yz');
expect EOF;
select * from NetCandidateCompliance('TEST','TEST');
-- Just run it. Not bothered about result;
*/
}