pears.NetClientCandidateSubmit

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetClientCandidateSubmit"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pLineID" CHAR(20),IN "pIsShift" INTEGER ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "sname" CHAR(100);
  DECLARE "RefCode" CHAR(20);
  DECLARE "VacID" CHAR(20);
  IF "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "sname" = (SELECT "name" FROM "person" WHERE "personid" = "pPersonID");
  IF "pIsShift" = 0 THEN
    IF(SELECT "count"() FROM "progress" WHERE "vacancyid" = "pLineID" AND "personid" = "ppersonid") = 0 THEN
      INSERT INTO "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) VALUES( "uniquekey"("ppersonid"+"plineid"),"pLineid","ppersonid","userstaffid",'A',CURRENT DATE ) ;
      SET "RefCode" = (SELECT "refcode" FROM "vacancy" WHERE "vacancyid" = "pLineID");
      SET "VacID" = "pLineID"
    ELSE
      SELECT '101:~This person is already in the short list';
      RETURN
    END IF
  ELSE SET "vacid" = (SELECT top 1 "vacancyid" FROM "tempshiftplan" WHERE "tempshiftplanid" = "pLineID" ORDER BY "tempshiftplanid" ASC);
    IF(SELECT "count"() FROM "progress" WHERE "vacancyid" = "vacid" AND "personid" = "ppersonid") = 0 THEN
      INSERT INTO "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) VALUES( "uniquekey"("ppersonid"+"plineid"),"vacid","ppersonid","userstaffid",'A',CURRENT DATE ) ;
      SET "RefCode" = (SELECT "referencecode" FROM "tempshiftplan" WHERE "tempshiftplanid" = "pLineID")
    ELSE
      SELECT '101:~This person is already in the short list';
      RETURN
    END IF END IF;
  IF "isnull"("RefCode",'') <> '' THEN
    SET "RefCode" = '('+"RefCode"+')'
  END IF;
  CALL "IQXNetPopup"("pWebUserID","pPersonID",NULL,"VacID",NULL,NULL,'SUBMITCANDIDATE','Candidate submitted by client '+"RefCode","sname");
  SELECT '0:~Success'
END /* DOC
2017-01-05 PC receive suggested candidate for shortlist of vacancy
2017-08-01 PC divert all entries to the progress table
2019-02-07 PC IW-809 modify to allow for entries into TempShiftProgress
 
Tests:
all invalid
pWebUserID valid
all valid for vacancy but no permission
all valid for vacancy with permission
all valid for vacancy repeated
all valid for shift with permission
all valid for shift repeated
 
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempshiftplan set referencecode='TestRefPlan' where tempshiftplanid='TEST';
delete from progress where vacancyid='TEST';
delete from iqxnetrightallowed where iqxnetrightid='SUBMITCANDIDATES' and iqxnetuserclassid='CLIENT';
delete from iqxnetrightassigned where iqxnetrightid='SUBMITCANDIDATES' and iqxnetuserid='test.client';
select * from NetClientCandidateSubmit(null,null,null,null);
expect pResult=99*;
select * from NetClientCandidateSubmit('test.client',null,null,null);
expect pResult=99*;
select * from NetClientCandidateSubmit('test.client','TEST','TEST',0);
expect pResult=99*;
INSERT INTO iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,MaintainOnWeb) values ('SUBMITCANDIDATES','CLIENT','*',1);
insert into iqxnetrightassigned (iqxnetuserid,iqxnetrightid,Assigned) values ('test.client','SUBMITCANDIDATES',1);
select * from NetClientCandidateSubmit('test.client','TEST','TEST',0);
expect pResult=0*;
select * from progress where vacancyid='test';
expect personid=TEST;
 
select top 1 *,replace(description,' ','_') as A from diary order by whenentered desc;
expect staffid=TEST,A=Candidate_submitted*;
delete from progress where vacancyid='TEST';
select * from NetClientCandidateSubmit('test.client','TEST','TEST',1);
expect pResult=0*;
select * from NetClientCandidateSubmit('test.client','TEST','TEST',1);
expect pResult=1*;
 
select top 1 *,replace(description,' ','_') as B from diary order by whenentered desc;
expect staffid=TEST,B=Candidate_submitted*;
update tempshiftplan set referencecode=null where tempshiftplanid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientCandidateSubmit" IS 
{CREATE PROCEDURE pears."NetClientCandidateSubmit"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pLineID" CHAR(20),IN "pIsShift" INTEGER) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
    DECLARE sname CHAR(100);
    DECLARE RefCode CHAR(20); 
    DECLARE VacID CHAR(20);
    IF "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 THEN
        SELECT '99:~Permission denied';
        RETURN
    END IF;
    SET sname=(SELECT name FROM person WHERE personid = pPersonID);
 
    IF pIsShift=0 THEN
        IF (SELECT COUNT(*) FROM progress WHERE vacancyid=pLineID AND personid=ppersonid) = 0 THEN
            INSERT INTO "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) VALUES( "uniquekey"("ppersonid"+"plineid"),"pLineid","ppersonid","userstaffid",'A',CURRENT DATE ); 
            SET RefCode = (SELECT refcode FROM vacancy WHERE vacancyid = pLineID);
            SET VacID = pLineID;
        ELSE
            SELECT '101:~This person is already in the short list';
            RETURN;
        END IF;
    ELSE
        SET vacid=(SELECT top 1 vacancyid FROM tempshiftplan WHERE tempshiftplanid=pLineID ORDER BY tempshiftplanid);
        IF (SELECT COUNT(*) FROM progress WHERE vacancyid=vacid AND personid=ppersonid) = 0 THEN
            INSERT INTO "progress"( "progressid","vacancyid","personid","staffid","status","actiondate" ) VALUES( "uniquekey"("ppersonid"+"plineid"),"vacid","ppersonid","userstaffid",'A',CURRENT DATE ); 
            SET RefCode = (SELECT referencecode FROM tempshiftplan WHERE tempshiftplanid = pLineID);
        ELSE
            SELECT '101:~This person is already in the short list';
            RETURN;
        END IF;
    END IF;
    IF "isnull"(RefCode,'') <> '' THEN 
        SET RefCode = '('+RefCode+')'
    END IF;
    CALL "IQXNetPopup"("pWebUserID","pPersonID",NULL,"VacID",NULL,NULL,'SUBMITCANDIDATE','Candidate submitted by client '+RefCode,sname);
    SELECT '0:~Success';
END
 
/* DOC
2017-01-05 PC receive suggested candidate for shortlist of vacancy
2017-08-01 PC divert all entries to the progress table
2019-02-07 PC IW-809 modify to allow for entries into TempShiftProgress
 
Tests:
    all invalid
    pWebUserID valid
    all valid for vacancy but no permission
    all valid for vacancy with permission
    all valid for vacancy repeated
    all valid for shift with permission
    all valid for shift repeated
 
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempshiftplan set referencecode='TestRefPlan' where tempshiftplanid='TEST';
delete from progress where vacancyid='TEST';
delete from iqxnetrightallowed where iqxnetrightid='SUBMITCANDIDATES' and iqxnetuserclassid='CLIENT';
delete from iqxnetrightassigned where iqxnetrightid='SUBMITCANDIDATES' and iqxnetuserid='test.client';
select * from NetClientCandidateSubmit(null,null,null,null);
expect pResult=99*;
select * from NetClientCandidateSubmit('test.client',null,null,null);
expect pResult=99*;
select * from NetClientCandidateSubmit('test.client','TEST','TEST',0);
expect pResult=99*;
INSERT INTO iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,MaintainOnWeb) values ('SUBMITCANDIDATES','CLIENT','*',1);
insert into iqxnetrightassigned (iqxnetuserid,iqxnetrightid,Assigned) values ('test.client','SUBMITCANDIDATES',1);
select * from NetClientCandidateSubmit('test.client','TEST','TEST',0);
expect pResult=0*;
select * from progress where vacancyid='test';
expect personid=TEST;
 
select top 1 *,replace(description,' ','_') as A from diary order by whenentered desc;
expect staffid=TEST,A=Candidate_submitted*;
delete from progress where vacancyid='TEST';
select * from NetClientCandidateSubmit('test.client','TEST','TEST',1);
expect pResult=0*;
select * from NetClientCandidateSubmit('test.client','TEST','TEST',1);
expect pResult=1*;
 
select top 1 *,replace(description,' ','_') as B from diary order by whenentered desc;
expect staffid=TEST,B=Candidate_submitted*;
update tempshiftplan set referencecode=null where tempshiftplanid='TEST';
*/
}