pears.NetClientVacanciesShortlistAction

CREATE PROCEDURE "pears"."NetClientVacanciesShortlistAction"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pPersonID" CHAR(20),IN "pStatusReasonID" CHAR(20),IN "pRejectionReasonID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXNet
BEGIN
  DECLARE "pProgressID" CHAR(20);
  DECLARE "RejectionReasonText" CHAR(100);
  DECLARE "pStaffID" CHAR(20);
  DECLARE "pVacID" CHAR(20);
  IF "isnull"("pVacancyID",'') = '' THEN
    SELECT '99:~Please check Vacancy';
    RETURN
  END IF;
  IF "isnull"("pPersonID",'') = '' THEN
    SELECT '98:~Please check Person';
    RETURN
  END IF;
  IF "isnull"("pStatusReasonID",'') = '' THEN
    SELECT '97:~Please check Status ';
    RETURN
  END IF;
  IF NOT "pWebUserID" = any(SELECT "isnull"((SELECT "iqxnetuserid" FROM "iqxnetuserlink" KEY JOIN "employment" KEY JOIN "vacancy" WHERE "vacancyid" = "pVacancyID"),(SELECT "iqxnetuserid" FROM "iqxnetuserlink" KEY JOIN "employment" KEY JOIN "vacancy" KEY JOIN "tempshiftplan" WHERE "tempshiftplanid" = "pVacancyID"))) THEN
    SELECT '96:~Please check Vacancy';
    RETURN
  END IF;
  SET "pVacID" = "isnull"((SELECT "vacancyid" FROM "tempshiftplan" WHERE "tempshiftplanid" = "pVacancyID"),"pVacancyID");
  IF "isnull"("pVacID",'') = '' THEN
    SELECT '95:~Please check Vacancy';
    RETURN
  END IF;
  IF "isnull"("pRejectionReasonID",'') <> '' THEN
    SET "RejectionReasonText" = (SELECT top 1 "reason" FROM "rejectionreason" WHERE "rejectreasonid" = "pRejectionReasonID")
  END IF;
  SET "pStaffID" = (SELECT top 1 "staffid" FROM "vacancy" WHERE "vacancyid" = "pVacID");
  SET "pProgressID" = (SELECT top 1 "progressid" FROM "progress" WHERE "vacancyid" = "pVacID" AND "personid" = "pPersonID");
  IF "isnull"("pProgressID",'') <> '' THEN
    UPDATE "progress" SET "status" = "pStatusReasonID","rejectionreason" = "RejectionReasonText" WHERE "progressid" = "pProgressID"
  ELSE
    INSERT INTO "progress"( "ProgressID","VacancyID","status","rejectionreason","staffid","personid" ) VALUES( "uniquekey"("pVacancyID"),"pVacID","pStatusReasonID","RejectionReasonText","pStaffID","pPersonID" ) 
  END IF;
  SELECT '0:~Success'
END /* DOC
2016-12-12 PC Create routine to set status for progress
2018-08-22 PC improve error handling
 
Tests:
invalid pWebUserID
valid pWebUserID, invalid pVacancyID
valid pWebUserID,pVacancyID, invalid pPersonID
valid pWebUserID,pVacancyID,pPersonID, invalid pStatusReasonID
valid pWebUserID,pPersonID, pStatusReasonID pRejectionReasonID as insert
valid pWebUserID,pPersonID, pStatusReasonID pRejectionReasonID, invalid pVacancyID
valid pWebUserID,pVacancyID,pPersonID, pStatusReasonID invalid pRejectionReasonID as update
valid pWebUserID,pVacancyID,pPersonID, pStatusReasonID, pRejectionReasonID
 
*/
/* 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';
insert into status (type,status,name,help) on existing update values ('R','~','test','test');
insert into rejectionreason on existing update values ('TEST','test',0);
insert into rejectionreason on existing update values ('TEST2','test2',1);
update vacancy set staffid='TEST' where vacancyid='TEST';
select * from NetClientVacanciesShortlistAction(null,null,null,null,null);
expect 99*;
select * from NetClientVacanciesShortlistAction('test.client',null,null,null,null);
expect 99*;
select * from NetClientVacanciesShortlistAction('test.client','TEST',null,null,null);
expect 98*;
select * from NetClientVacanciesShortlistAction('test.client','TEST','TEST',null,null);
expect 97*;
 
select * from NetClientVacanciesShortlistAction('test.client','TEST','TEST','~',null);
expect 0*;
select * from progress where vacancyid='TEST';
expect status=~,personid=TEST;
select * from NetClientVacanciesShortlistAction('test.client','TEST','TEST','~','TEST');
expect 0*;
update progress set rejectionreason='test' where vacancyid='test';
select * from progress where vacancyid='TEST';
expect status=~,personid=TEST,rejectionreason=test;
select * from NetClientVacanciesShortlistAction('test.client','TEST','TEST','~','TEST2');
expect 0*;
select * from progress where vacancyid='TEST';
expect status=~,personid=TEST,rejectionreason=test2;
update vacancy set staffid=null where vacancyid='TEST';
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacanciesShortlistAction" IS 
{CREATE PROCEDURE pears."NetClientVacanciesShortlistAction"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20), IN pPersonID CHAR(20), IN "pStatusReasonID" CHAR(20), IN pRejectionReasonID CHAR(20) DEFAULT NULL)
RESULT( "pResult" CHAR(250) ) 
// IQXNet
BEGIN
	DECLARE pProgressID CHAR(20);
    DECLARE RejectionReasonText CHAR(100);
    DECLARE pStaffID CHAR(20);
    DECLARE pVacID CHAR(20);
 
    IF isnull(pVacancyID,'')='' THEN
        SELECT '99:~Please check Vacancy';
        RETURN;
    END IF;
        IF isnull(pPersonID,'')='' THEN
        SELECT '98:~Please check Person';
        RETURN;
    END IF;
        IF isnull(pStatusReasonID,'')='' THEN
        SELECT '97:~Please check Status ';
        RETURN;
    END IF;
    IF pWebUserID NOT IN (SELECT isnull((SELECT iqxnetuserid FROM iqxnetuserlink KEY JOIN employment KEY JOIN vacancy WHERE vacancyid=pVacancyID),(SELECT iqxnetuserid FROM iqxnetuserlink KEY JOIN employment KEY JOIN vacancy KEY JOIN tempshiftplan WHERE tempshiftplanid=pVacancyID))) THEN
        SELECT '96:~Please check Vacancy';
        RETURN;
    END IF;
    SET pVacID=isnull((SELECT vacancyid FROM  tempshiftplan WHERE tempshiftplanid=pVacancyID),pVacancyID);
    IF isnull(pVacID,'')='' THEN
        SELECT '95:~Please check Vacancy';
        RETURN;
    END IF;
    IF isnull(pRejectionReasonID,'') !='' THEN
        SET RejectionReasonText = (SELECT top 1 reason FROM rejectionreason WHERE rejectreasonid=pRejectionReasonID);
    END IF;
    SET pStaffID=(SELECT top 1 staffid FROM vacancy WHERE vacancyid=pVacID);
    SET pProgressID=(SELECT top 1 progressid FROM progress WHERE vacancyid=pVacID AND personid=pPersonID);
    IF isnull (pProgressID,'') != ''  THEN
        UPDATE progress SET STATUS=pStatusReasonID,rejectionreason=RejectionReasonText WHERE progressid=pProgressID;
    ELSE
        INSERT INTO progress (ProgressID,VacancyID,STATUS,rejectionreason,staffid,personid) VALUES (uniquekey(pVacancyID),pVacID,pStatusReasonID,RejectionReasonText,pStaffID,pPersonID);
    END IF;
    SELECT '0:~Success';
END
 
/* DOC
2016-12-12 PC Create routine to set status for progress
2018-08-22 PC improve error handling
 
Tests:
    invalid pWebUserID
    valid pWebUserID, invalid pVacancyID
    valid pWebUserID,pVacancyID, invalid pPersonID
    valid pWebUserID,pVacancyID,pPersonID, invalid pStatusReasonID
    valid pWebUserID,pPersonID, pStatusReasonID pRejectionReasonID as insert
    valid pWebUserID,pPersonID, pStatusReasonID pRejectionReasonID, invalid pVacancyID
    valid pWebUserID,pVacancyID,pPersonID, pStatusReasonID invalid pRejectionReasonID as update
    valid pWebUserID,pVacancyID,pPersonID, pStatusReasonID, pRejectionReasonID
 
*/
 
/* 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';
insert into status (type,status,name,help) on existing update values ('R','~','test','test');
insert into rejectionreason on existing update values ('TEST','test',0);
insert into rejectionreason on existing update values ('TEST2','test2',1);
update vacancy set staffid='TEST' where vacancyid='TEST';
select * from NetClientVacanciesShortlistAction(null,null,null,null,null);
expect 99*;
select * from NetClientVacanciesShortlistAction('test.client',null,null,null,null);
expect 99*;
select * from NetClientVacanciesShortlistAction('test.client','TEST',null,null,null);
expect 98*;
select * from NetClientVacanciesShortlistAction('test.client','TEST','TEST',null,null);
expect 97*;
 
select * from NetClientVacanciesShortlistAction('test.client','TEST','TEST','~',null);
expect 0*;
select * from progress where vacancyid='TEST';
expect status=~,personid=TEST;
select * from NetClientVacanciesShortlistAction('test.client','TEST','TEST','~','TEST');
expect 0*;
update progress set rejectionreason='test' where vacancyid='test';
select * from progress where vacancyid='TEST';
expect status=~,personid=TEST,rejectionreason=test;
select * from NetClientVacanciesShortlistAction('test.client','TEST','TEST','~','TEST2');
expect 0*;
select * from progress where vacancyid='TEST';
expect status=~,personid=TEST,rejectionreason=test2;
update vacancy set staffid=null where vacancyid='TEST';
 
*/
}