pears.NetQuestSet

CREATE PROCEDURE "pears"."NetQuestSet"( IN "pWebUserID" CHAR(20),IN "qanswers" long VARCHAR DEFAULT NULL,IN "pID" CHAR(20) DEFAULT NULL,IN "pLocation" CHAR(2) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
//IQXWeb
BEGIN
  DECLARE "userclass" CHAR(20);
  SELECT "iqxnetuserclassid" INTO "userclass" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid";
  IF "pID" IS NULL THEN
    IF "userclass" = 'CANDIDATE' THEN
      SET "pID" = "NetCandidateValidPersonID"("pWebUserID",NULL)
    ELSE IF "userclass" = 'CLIENT' THEN
        IF(SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid") = 1 THEN
          SET "pID" = (SELECT FIRST "employment"."companyid" FROM "iqxnetuserlink" KEY JOIN "employment" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" ORDER BY 1 ASC)
        END IF END IF END IF;
    IF "pID" IS NULL THEN
      SELECT '1:~No default ID found';
      RETURN
    END IF END IF;
  IF "NetHasPermission"("pWebUserID","pID","pID") = 1 THEN
    CALL "IQXNetSaveQuestionnaire"("pID","qanswers","pLocation");
    SELECT '0:~Success'
  ELSE
    SELECT '99:~Permission denied'
  END IF
END /* DOC
 
2018-07-30 PC IW-513 Improve error handling
2018-10-22 MHS IW-456 multi-cand fix and tests
*/
/* TEST
select * from NetQuestSet('test.candidate','','~~','~~~');
expect pResult=99:*;
select * from NetQuestSet('test.candidate','',null,'~~~');
expect pResult=0:*;
select * from NetQuestSet('test.client','',null,'~~~');
expect pResult=0:*;
insert into iqxnetuserlink (iqxnetuserlinkid,iqxnetuserid,employmentid) values ('TESTXXX', 'test.client', 'TEST');
select * from NetQuestSet('test.client','',null,'~~~');
expect pResult=1:*;
delete from iqxnetuserlink where iqxnetuserlinkid='TESTXXX';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetQuestSet" IS 
{CREATE PROCEDURE pears."NetQuestSet"(IN pWebUserID CHAR(20),IN qanswers long VARCHAR DEFAULT NULL,IN pID CHAR(20) DEFAULT NULL, IN pLocation CHAR(2) DEFAULT NULL)
RESULT(pResult CHAR(250))
//IQXWeb
BEGIN
DECLARE userclass CHAR(20);
SELECT iqxnetuserclassid INTO userclass FROM iqxnetuser WHERE iqxnetuserid=pwebuserid;
IF pID IS NULL THEN
  IF userclass='CANDIDATE' THEN
    SET pID=NetCandidateValidPersonID(pWebUserID,NULL);
  ELSE IF userclass='CLIENT' THEN
    IF (SELECT COUNT(*) FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid)=1 THEN
      SET pID=(SELECT FIRST employment.companyid FROM iqxnetuserlink KEY JOIN employment WHERE iqxnetuserlink.iqxnetuserid = pwebuserid ORDER BY 1);
    END IF;
  END IF;
  END IF;
  IF pID IS NULL THEN
    SELECT '1:~No default ID found';
    RETURN;
  END IF;
END IF;
IF NetHasPermission(pWebUserID,pID,pID)=1 THEN
  CALL IQXNetSaveQuestionnaire(pID,qanswers,pLocation);
  SELECT '0:~Success';
ELSE
  SELECT '99:~Permission denied';
END IF;
END
 
/* DOC
 
2018-07-30 PC IW-513 Improve error handling
2018-10-22 MHS IW-456 multi-cand fix and tests
*/
 
/* TEST
select * from NetQuestSet('test.candidate','','~~','~~~');
expect pResult=99:*;
select * from NetQuestSet('test.candidate','',null,'~~~');
expect pResult=0:*;
select * from NetQuestSet('test.client','',null,'~~~');
expect pResult=0:*;
insert into iqxnetuserlink (iqxnetuserlinkid,iqxnetuserid,employmentid) values ('TESTXXX', 'test.client', 'TEST');
select * from NetQuestSet('test.client','',null,'~~~');
expect pResult=1:*;
delete from iqxnetuserlink where iqxnetuserlinkid='TESTXXX';
*/
}