pears.NetAgencyCurrentRequirementConfirmInterest

CREATE PROCEDURE "pears"."NetAgencyCurrentRequirementConfirmInterest"( IN "pWebUserID" CHAR(20),IN "pLineID" CHAR(22),IN "pTransactionType" CHAR(1),IN "pNote" CHAR(255),IN "pDeclinedReason" CHAR(20) DEFAULT NULL ) 
RESULT( "retval" CHAR(250) ) 
BEGIN
  // IQXWeb
  DECLARE "CEKey" CHAR(20);
  DECLARE "dStaffID" CHAR(20);
  DECLARE "dEmploymentID" CHAR(20);
  DECLARE "ConfirmContactClass" CHAR(2);
  DECLARE "DeclineContactClass" CHAR(2);
  DECLARE "CommentClass" CHAR(2);
  DECLARE "SecondaryAgencyID" CHAR(20);
  DECLARE "LineID" CHAR(20);
  DECLARE "ShiftRef" CHAR(20);
  DECLARE "dWhoID" CHAR(20);
  DECLARE "DeclinedReason" CHAR(255);
  SET "CEKey" = "uniquekey"("pLineID");
  IF "isnull"("pTransactionType",'') NOT IN( 'C','D','N' ) THEN
    SELECT '99:~Invalid transaction type'
  END IF;
  IF "left"("pLineID",1) = 'V' THEN
    SET "LineID" = "right"("pLineID",20);
    SELECT "refcode" INTO "ShiftRef" FROM "vacancy" WHERE "vacancyID" = "LineID"
  END IF;
  IF "left"("pLineID",1) = 'P' THEN
    SET "LineID" = (SELECT "vacancyid" FROM "tempshiftplan" WHERE "tempshiftplanid" = "right"("pLineID",20));
    SELECT "ShiftSerialNumber" INTO "ShiftRef" FROM "tempshiftplan" WHERE "tempshiftplanid" = "right"("pLineID",20)
  END IF;
  SELECT top 1 "employment"."employmentid" INTO "dEmploymentID" FROM "iqxnetuserlink" KEY JOIN "employment" WHERE "iqxnetuserid" = "pWebUserID" ORDER BY "isnull"("employment"."leavedate",CURRENT DATE) DESC;
  SELECT "e"."personid" INTO "dWhoID" FROM "employment" AS "e" WHERE "e"."employmentid" = "dEmploymentID";
  SET "dStaffID" = (SELECT "staffid" FROM "vacancy" WHERE "vacancyid" = "LineID");
  SET "ConfirmContactClass" = (SELECT top 1 "classcode" FROM "contactclass" WHERE "classdescrip" = 'Confirm Interest' ORDER BY "sortorder" ASC);
  SET "DeclineContactClass" = (SELECT top 1 "classcode" FROM "contactclass" WHERE "classdescrip" = 'Decline Interest' ORDER BY "sortorder" ASC);
  SET "CommentClass" = (SELECT top 1 "classcode" FROM "contactclass" WHERE "classdescrip" = 'Client Comment' ORDER BY "sortorder" ASC);
  SET "SecondaryAgencyID" = (SELECT "CompanyID" FROM "Employment" WHERE "EmploymentID" = "dEmploymentID");
  -- Confirm
  IF "pTransactionType" = 'C' THEN
    INSERT INTO "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes","whoentered" ) VALUES
      ( "CEKey","LineID","dStaffID","dEmploymentID",CURRENT DATE,CURRENT TIME,'Confirm Interest:'+"ShiftRef","ConfirmContactClass","pNote","dWhoID" ) ;
    SELECT '0:~success#Confirm Interest at '+"dateformat"(CURRENT TIMESTAMP,'dd/mm/yyyy hh:mm')
  END IF;
  -- Decline
  IF "pTransactionType" = 'D' THEN
    -- Mark Shift or Vacancy Cascade record as declined
    IF "left"("pLineID",1) = 'P' THEN
      SET "DeclinedReason" = (SELECT top 1 "reason" FROM "rejectionreason" WHERE "rejectreasonid" = "pDeclinedReason" ORDER BY "sortorder" ASC);
      UPDATE "CascadedShift" SET "WhenDeclined" = CURRENT TIMESTAMP,"DeclinedNote" = "pNote","Details" = "DeclinedReason" WHERE "TempShiftPlanID" = "LineID" AND "SecondaryAgencyID" = "SecondaryAgencyID"
    END IF;
    IF "left"("pLineID",1) = 'V' THEN
      UPDATE "CascadedVacancy" SET "WhenDeclined" = CURRENT TIMESTAMP,"Details" = "pNote" WHERE "VacancyID" = "LineID" AND "SecondaryAgencyID" = "SecondaryAgencyID"
    END IF;
    INSERT INTO "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes","whoentered" ) VALUES
      ( "CEKey","LineID","dStaffID","dEmploymentID",CURRENT DATE,CURRENT TIME,'Decline Interest:'+"ShiftRef"+' '+"DeclinedReason","DeclineContactClass","pNote","dWhoID" ) ;
    SELECT '0:~success#Decline Interest at '+"dateformat"(CURRENT TIMESTAMP,'dd/mm/yyyy hh:mm')
  END IF;
  -- Note (comment)
  IF "pTransactionType" = 'N' THEN
    INSERT INTO "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes","whoentered" ) VALUES
      ( "CEKey","LineID","dStaffID","dEmploymentID",CURRENT DATE,CURRENT TIME,'Add comment:'+"ShiftRef","CommentClass","pNote","dWhoID" ) ;
    SELECT '0:~success#Comment made at '+"dateformat"(CURRENT TIMESTAMP,'dd/mm/yyyy hh:mm')+': '+"pNote"
  END IF
END /* DOC
2016-10-06 PC set decline for cascaded shift / vacancy, and create confirm / decline / Note entry into the contact event table
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
This routine requires three contact event types which must contain the following text:
Confirm Interest
Decline Interest
Client Comment
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
select * from NetAgencyCurrentRequirementConfirmInterest('test.agency','VTESTTESTTESTTESTTEST','C','TestNote');
expect retval=0:~success#Confirm*;
select top 1 staffid,vacancyid,employmentid,classcode,notes from contactevent order by whenentered desc,contacteventid desc;
expect staffid=test,vacancyid=TESTTESTTESTTESTTEST,employmentid=TEST,classcode=CI,notes=TestNote;
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCurrentRequirementConfirmInterest" IS 
{CREATE PROCEDURE pears."NetAgencyCurrentRequirementConfirmInterest"( IN "pWebUserID" CHAR(20),IN "pLineID" CHAR(22),IN "pTransactionType" CHAR(1), IN pNote CHAR(255),IN pDeclinedReason CHAR(20) DEFAULT NULL)
RESULT( "retval" CHAR(250) ) 
BEGIN
// IQXWeb
    DECLARE CEKey CHAR(20);
    DECLARE dStaffID CHAR(20);
    DECLARE dEmploymentID CHAR(20);
    DECLARE ConfirmContactClass CHAR(2);
    DECLARE DeclineContactClass CHAR(2);
    DECLARE CommentClass CHAR(2);
    DECLARE SecondaryAgencyID CHAR(20);
    DECLARE LineID CHAR(20);
    DECLARE ShiftRef CHAR(20);
    DECLARE dWhoID CHAR(20);
    DECLARE DeclinedReason CHAR(255);
    SET CEKey = "uniquekey"("pLineID");
    IF isnull(pTransactionType,'') NOT IN ('C','D','N') THEN
        SELECT '99:~Invalid transaction type';
    END IF;
    IF LEFT(pLineID,1)='V' THEN 
        SET LineID = RIGHT(pLineID,20);
        SELECT "refcode" INTO ShiftRef FROM "vacancy" WHERE "vacancyID" = "LineID"; 
    END IF;
    IF LEFT(pLineID,1)='P' THEN 
        SET LineID = (SELECT vacancyid FROM tempshiftplan WHERE tempshiftplanid=RIGHT(pLineID,20));
        SELECT "ShiftSerialNumber" INTO ShiftRef FROM tempshiftplan WHERE tempshiftplanid=RIGHT(pLineID,20); 
    END IF;
    SELECT top 1 employment."employmentid" INTO dEmploymentID FROM "iqxnetuserlink" KEY JOIN employment WHERE "iqxnetuserid" = "pWebUserID" ORDER BY isnull(employment.leavedate,CURRENT DATE) DESC;
    SELECT e.personid INTO dWhoID FROM employment e WHERE e.employmentid = dEmploymentID;
    SET dStaffID = (SELECT "staffid" FROM "vacancy" WHERE "vacancyid" = "LineID");
    SET ConfirmContactClass = (SELECT top 1 classcode FROM contactclass WHERE classdescrip='Confirm Interest' ORDER BY sortorder);
    SET DeclineContactClass = (SELECT top 1 classcode FROM contactclass WHERE classdescrip='Decline Interest' ORDER BY sortorder);
    SET CommentClass = (SELECT top 1 classcode FROM contactclass WHERE classdescrip='Client Comment' ORDER BY sortorder);
    SET SecondaryAgencyID = (SELECT "CompanyID" FROM "Employment" WHERE "EmploymentID" = dEmploymentID);
  -- Confirm
  IF "pTransactionType" = 'C' THEN
    INSERT INTO "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes",whoentered ) VALUES
      (CEKey,"LineID",dStaffID,dEmploymentID,CURRENT DATE,CURRENT TIME,'Confirm Interest:'+ShiftRef,ConfirmContactClass,"pNote",dWhoID ) ;
    SELECT '0:~success#Confirm Interest at '+"dateformat"(CURRENT TIMESTAMP,'dd/mm/yyyy hh:mm')
  END IF;
  -- Decline
  IF "pTransactionType" = 'D' THEN
    -- Mark Shift or Vacancy Cascade record as declined
    IF LEFT(pLineID,1)='P' THEN
      SET DeclinedReason = (SELECT top 1 reason FROM rejectionreason WHERE rejectreasonid = pDeclinedReason ORDER BY sortorder);
      UPDATE "CascadedShift" SET "WhenDeclined" = CURRENT TIMESTAMP,"DeclinedNote" = "pNote",Details=DeclinedReason WHERE "TempShiftPlanID" = "LineID" AND "SecondaryAgencyID" = SecondaryAgencyID
    END IF;
    IF LEFT(pLineID,1)='V' THEN
      UPDATE "CascadedVacancy" SET "WhenDeclined" = CURRENT TIMESTAMP,Details=pNote WHERE "VacancyID" = "LineID" AND "SecondaryAgencyID" = SecondaryAgencyID
    END IF;
    INSERT INTO "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes",whoentered ) VALUES
      ( "CEKey","LineID",dStaffID,dEmploymentID,CURRENT DATE,CURRENT TIME,'Decline Interest:'+ShiftRef+' '+DeclinedReason,DeclineContactClass,"pNote",dWhoID ) ;
    SELECT '0:~success#Decline Interest at '+"dateformat"(CURRENT TIMESTAMP,'dd/mm/yyyy hh:mm')
  END IF;
 -- Note (comment)
  IF "pTransactionType" = 'N' THEN
      INSERT INTO "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes",whoentered ) VALUES
        ( "CEKey","LineID",dStaffID,dEmploymentID,CURRENT DATE,CURRENT TIME,'Add comment:'+ShiftRef,CommentClass,"pNote",dWhoID ) ;
    SELECT '0:~success#Comment made at '+"dateformat"(CURRENT TIMESTAMP,'dd/mm/yyyy hh:mm')+': '+"pNote"
  END IF
END
 
/* DOC
2016-10-06 PC set decline for cascaded shift / vacancy, and create confirm / decline / Note entry into the contact event table
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
This routine requires three contact event types which must contain the following text:
    Confirm Interest
    Decline Interest
    Client Comment
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
select * from NetAgencyCurrentRequirementConfirmInterest('test.agency','VTESTTESTTESTTESTTEST','C','TestNote');
expect retval=0:~success#Confirm*;
select top 1 staffid,vacancyid,employmentid,classcode,notes from contactevent order by whenentered desc,contacteventid desc;
expect staffid=test,vacancyid=TESTTESTTESTTESTTEST,employmentid=TEST,classcode=CI,notes=TestNote;
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
 
*/
}