pears.NetAgencyCurrentRequirementCandidateSubmit

CREATE PROCEDURE "pears"."NetAgencyCurrentRequirementCandidateSubmit"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pLineID" CHAR(22),IN "pNote" long VARCHAR DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "SecAgID" CHAR(20);
  DECLARE "sname" CHAR(100);
  DECLARE "RefCode" CHAR(20);
  DECLARE "LineId" CHAR(20);
  DECLARE "LineType" CHAR(1);
  DECLARE "dStaffId" CHAR(20);
  DECLARE "dStaffName" CHAR(60);
  DECLARE "NoteText" CHAR(255);
  IF "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "SecAgID" = (SELECT top 1 "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" ORDER BY "isnull"("employment"."leavedate",CURRENT DATE) ASC);
  SET "LineType" = "left"("pLineID",1);
  IF "LineType" = 'V' THEN
    SET "LineID" = "right"("pLineID",20);
    SET "dStaffID" = (SELECT "staffid" FROM "vacancy" WHERE "vacancyid" = "LineID")
  END IF;
  IF "LineType" = 'P' THEN
    SET "LineID" = (SELECT "TempshiftPlanID" FROM "tempshiftplan" WHERE "tempshiftplanid" = "right"("pLineID",20));
    SET "dStaffID" = (SELECT "staffid" FROM "tempshiftplan" WHERE "tempshiftplanid" = "LineID")
  END IF;
  SET "dStaffName" = (SELECT "name" FROM "staff" WHERE "staffid" = "dStaffID");
  SET "NoteText" = 'Candidate submitted at '+CAST("now"() AS CHAR)+' by '+"dStaffName"+(IF "isnull"("pNote",'') <> '' THEN "pNote" ELSE NULL endif);
  IF "LineType" = 'V' THEN
    IF NOT "LineId" = any(SELECT "vacancyid" FROM "cascadedvacancy" WHERE "secondaryagencyid" = "secagid") THEN
      SELECT '98:~Permission denied';
      RETURN
    END IF;
    SET "sname" = (SELECT "name" FROM "person" WHERE "personid" = "ppersonid");
    IF(SELECT(IF "count"() = 0 THEN
          'FALSE'
        ELSE
          'TRUE'
        endif) AS "IsPresent"
        FROM "progress" AS "pr"
          LEFT OUTER JOIN "status" AS "s" ON "s"."status" = "pr"."status" WHERE "pr"."personid" = "pPersonID"
        AND "pr"."vacancyid" = "LineID"
        AND "s"."type" = 'R'
        AND "s"."final" = 0) = 'FALSE' THEN
      INSERT INTO "progress"( "progressid","vacancyid","personid","staffid","status","actiondate","note" ) VALUES( "uniquekey"("ppersonid"+"LineID"),"LineID","ppersonid","dStaffID",'A',CURRENT DATE,"NoteText" ) 
    ELSE
      SELECT '99:~'+"sname"+' has already been submitted';
      RETURN
    END IF END IF;
  IF "LineType" = 'P' THEN
    IF(SELECT "count"() FROM "cascadedshift" WHERE "secondaryagencyid" = "secagid" AND "tempshiftplanid" = "LineID") = 0 THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF;
    INSERT INTO "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status","note" ) VALUES( "uniquekey"("ppersonid"+"LineID"),"LineID","ppersonid","dStaffID",'A',"NoteText" ) 
  END IF;
  SELECT '0:~Success'
END /* DOC
2016-11-28 PC Test and doc
2018-06-07 PC revise tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
Tests:
all valid but not cascaded
all valid and cascaded for P
repeat all valid for V
 
*/
/* TEST
call NetTestSetup('');
delete from cascadedshift where cascadedshiftid='TEST';
delete from cascadedvacancy where vacancyid like 'TEST%';
delete from tempshiftplan where tempshiftplanid='TESTTESTTESTTESTTEST';
delete from progress where vacancyid='TESTTESTTESTTESTTEST';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) on existing update defaults off values ('SUBMITCANDIDATES','AGENCY','*','1');
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) on existing update defaults off values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 98*;
insert into tempshiftplan (tempshiftplanid,vacancyid,description) values ('TESTTESTTESTTESTTEST','TESTTESTTESTTESTTEST','test2');
insert into cascadedshift (cascadedshiftid,tempshiftplanid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TESTTESTTESTTESTTEST','TEST',now(*),1,9); 
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','PTest99');
expect 99*;
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','PTESTTESTTESTTESTTEST');
expect 0*;
select * from tempshiftprogress where tempshiftplanid='TESTTESTTESTTESTTEST';
expect PersonID=TEST;
expect EOF;
update tempshiftprogress set status='R' where tempshiftplanid='TESTTESTTESTTESTTEST';
insert into cascadedvacancy (CascadedVacancyID,VacancyID,SecondaryAgencyID,CascadeTime,CascadeLevel,method) values ('TEST2','TESTTESTTESTTESTTEST','TEST',now(*),1,1);
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 0:~Success;
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 99:~test*;
delete from cascadedvacancy where vacancyid like 'TEST%';
delete from cascadedshift where cascadedshiftid='TEST';
delete from tempshiftplan where tempshiftplanid='TESTTESTTESTTESTTEST';
delete from progress where vacancyid='TESTTESTTESTTESTTEST';
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCurrentRequirementCandidateSubmit" IS 
{CREATE PROCEDURE pears."NetAgencyCurrentRequirementCandidateSubmit"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pLineID" CHAR(22), IN pNote long VARCHAR DEFAULT NULL) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE SecAgID CHAR(20);
  DECLARE sname CHAR(100);
  DECLARE RefCode CHAR(20);
  DECLARE LineId CHAR(20);
  DECLARE LineType CHAR(1);
  DECLARE dStaffId CHAR(20);
  DECLARE dStaffName CHAR(60);
  DECLARE NoteText CHAR(255);
  IF "IQXNetHasPermission"("pWebUserID",'SUBMITCANDIDATES') = 0 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
 
  SET SecAgID = (SELECT top 1 "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" ORDER BY isnull(employment.leavedate,CURRENT DATE));
  SET LineType=LEFT(pLineID,1);
  IF LineType='V' THEN 
    SET LineID = RIGHT(pLineID,20); 
    SET dStaffID = (SELECT "staffid" FROM "vacancy" WHERE "vacancyid" = "LineID");
  END IF;
  IF LineType='P' THEN 
    SET LineID = (SELECT TempshiftPlanID FROM tempshiftplan WHERE tempshiftplanid=RIGHT(pLineID,20)); 
    SET dStaffID = (SELECT "staffid" FROM tempshiftplan WHERE tempshiftplanid = "LineID");
  END IF;
 
  SET dStaffName=(SELECT name FROM staff WHERE staffid=dStaffID);
 
  SET NoteText='Candidate submitted at '+ CAST(now(*) AS CHAR) + ' by ' + dStaffName + (IF isnull(pNote,'')!='' THEN pNote ELSE NULL endif);
  IF LineType='V' THEN 
    IF NOT "LineId" = any(SELECT "vacancyid" FROM "cascadedvacancy" WHERE "secondaryagencyid" = secagid) THEN
      SELECT '98:~Permission denied';
      RETURN
    END IF;
    SET sname = (SELECT "name" FROM "person" WHERE "personid" = "ppersonid");
    IF(SELECT(IF "count"() = 0 THEN 
    	'FALSE'
    ELSE 
	    'TRUE'
    endif) AS "IsPresent" 
  FROM 
	"progress" AS "pr" 
	LEFT OUTER JOIN "status" AS "s" ON "s"."status" = "pr"."status" WHERE "pr"."personid" = "pPersonID" 
	AND "pr"."vacancyid" = "LineID" 
	AND "s"."type" = 'R' 
	AND "s"."final" = 0) = 'FALSE' 
	THEN
    	INSERT INTO "progress"( "progressid","vacancyid","personid","staffid","status","actiondate",note ) VALUES( "uniquekey"("ppersonid"+"LineID"),"LineID","ppersonid","dStaffID",'A',CURRENT DATE,NoteText ) 
    ELSE
    	SELECT '99:~'+sname+' has already been submitted';
    	RETURN
    END IF 
  END IF;
  IF LineType='P' THEN 
	IF (SELECT COUNT(*) FROM "cascadedshift" WHERE "secondaryagencyid" = secagid AND tempshiftplanid=LineID ) = 0 THEN
    	SELECT '99:~Permission denied';
    	RETURN
    END IF;
    INSERT INTO "tempshiftprogress"( "tempshiftprogressid","tempshiftplanid","personid","staffid","status",note ) VALUES( "uniquekey"("ppersonid"+"LineID"),"LineID","ppersonid","dStaffID",'A' ,NoteText) 
  END IF;
  SELECT '0:~Success';
END
 
/* DOC
2016-11-28 PC Test and doc
2018-06-07 PC revise tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
Tests:
    all valid but not cascaded
    all valid and cascaded for P
    repeat all valid for V
 
*/
 
/* TEST
call NetTestSetup('');
delete from cascadedshift where cascadedshiftid='TEST';
delete from cascadedvacancy where vacancyid like 'TEST%';
delete from tempshiftplan where tempshiftplanid='TESTTESTTESTTESTTEST';
delete from progress where vacancyid='TESTTESTTESTTESTTEST';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) on existing update defaults off values ('SUBMITCANDIDATES','AGENCY','*','1');
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) on existing update defaults off values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 98*;
insert into tempshiftplan (tempshiftplanid,vacancyid,description) values ('TESTTESTTESTTESTTEST','TESTTESTTESTTESTTEST','test2');
insert into cascadedshift (cascadedshiftid,tempshiftplanid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TESTTESTTESTTESTTEST','TEST',now(*),1,9); 
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','PTest99');
expect 99*;
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','PTESTTESTTESTTESTTEST');
expect 0*;
select * from tempshiftprogress where tempshiftplanid='TESTTESTTESTTESTTEST';
expect PersonID=TEST;
expect EOF;
update tempshiftprogress set status='R' where tempshiftplanid='TESTTESTTESTTESTTEST';
insert into cascadedvacancy (CascadedVacancyID,VacancyID,SecondaryAgencyID,CascadeTime,CascadeLevel,method) values ('TEST2','TESTTESTTESTTESTTEST','TEST',now(*),1,1);
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 0:~Success;
select * from NetAgencyCurrentRequirementCandidateSubmit('test.agency','TEST','VTESTTESTTESTTESTTEST');
expect 99:~test*;
delete from cascadedvacancy where vacancyid like 'TEST%';
delete from cascadedshift where cascadedshiftid='TEST';
delete from tempshiftplan where tempshiftplanid='TESTTESTTESTTESTTEST';
delete from progress where vacancyid='TESTTESTTESTTESTTEST';
 
*/
}