pears.NetAgencyAdditionalComments

CREATE PROCEDURE "pears"."NetAgencyAdditionalComments"( IN "pWebUserID" CHAR(20),IN "pLineID" CHAR(22) ) 
RESULT( "ActionTime" CHAR(20),"SentTo" CHAR(50),"Note" long VARCHAR,"shiftref" CHAR(10),"EventType" CHAR(30),"ContactDate" DATE,"ContactTime" TIME ) 
// IQXWeb
BEGIN
  DECLARE "VacID" CHAR(20);
  DECLARE "TempShiftID" CHAR(20);
  DECLARE "CompID" CHAR(20);
  DECLARE "TempShiftRef" CHAR(20);
  DECLARE "VacRef" CHAR(20);
  IF "left"("pLineId",1) = 'V' THEN
    SET "VacID" = "right"("pLineID",20)
  END IF;
  IF "left"("pLineId",1) = 'P' THEN
    SET "TempShiftID" = "right"("pLineID",20)
  END IF;
  SET "CompID" = (SELECT top 1 "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" ORDER BY "employment"."employmentid" ASC);
  IF "VacID" IS NULL THEN
    SET "VacID" = (SELECT "VacancyID" FROM "TempShiftPlan" WHERE "tempshiftplanid" = "TempShiftID")
  END IF;
  SELECT DISTINCT
    CAST("dateformat"("ce"."contactdate",'dd/mm/yyyy') AS CHAR)+' '+CAST("dateformat"("ce"."contacttime",'hh:nn') AS CHAR) AS "ActionTime",
    "s"."name" AS "SentTo",
    "ce"."notes" AS "Note",
    "substring"("ce"."description","charindex"(':',"ce"."description")+1) AS "ShiftRef",
    "cc"."classdescrip",
    "ce"."contactdate" AS "contactdate",
    "ce"."contacttime" AS "contacttime"
    FROM "vacancy" AS "va"
      ,"tempshiftplan" AS "tsp"
      RIGHT OUTER JOIN "vacancy" AS "va"
      KEY JOIN "contactevent" AS "ce"
      KEY JOIN "staff" AS "s"
      ,"contactevent" AS "ce"
      KEY JOIN "employment" AS "e"
      ,"contactevent" AS "ce" KEY JOIN "contactclass" AS "cc"
    WHERE(("ce"."classcode" IN( 'CD','CI' ) AND "va"."vacancyid" = "VacID")
    AND "e"."companyid" = "CompID")
    ORDER BY "contactdate" DESC,"contacttime" DESC
END /* DOC
28-08-2013 Provide additional Comments for the detail line of Current Requirements.
This routine requires contact events with classcode of CD and CI
25-11-2016 PC Doc and Test
17-04-2018 PC modify test
2019-03-04 PC IW-873 add top 1 and order by on subqueries
 
Tests:
Lineid starting with V with no contact event
Lineid starting with V with contact event
Lineid starting with P with contact event
 
*/
/* TEST
NetTestSetup('');
delete from contactevent where contacteventid='TEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
insert into vacancy (vacancyid,departmentid,employmentid,status,position) values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2');
insert into contactclass (classcode,classdescrip) on existing update defaults off values ('CI','Confirm Interest');
insert into contactclass (classcode,classdescrip) on existing update defaults off values ('CD','Decline Interest');
insert into contactclass (classcode,classdescrip) on existing update defaults off values ('~~','Test');
insert into contactevent (contacteventid,staffid,contactdate,description,classcode) on existing update defaults off values ('TEST','TEST',now(*),'Test','~~');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
update contactevent set classcode='CI',vacancyid='TESTTESTTESTTESTTEST',employmentid='TEST' where contacteventid='TEST';
select shiftref,SentTo,replace(EventType,' ','!') as A from NetAgencyAdditionalComments('test.agency','VTESTTESTTESTTESTTEST') ;
expect shiftref=Test,SentTo=TestStaff,A=Confirm!Interest;
insert into tempshiftplan (tempshiftplanid,vacancyid,description) values ('TESTTESTTESTTESTTEST','TESTTESTTESTTESTTEST','Test2');
select shiftref,SentTo,replace(EventType,' ','!') as B from NetAgencyAdditionalComments('test.agency','PTESTTESTTESTTESTTEST') ;
expect shiftref=Test,SentTo=TestStaff,B=Confirm!Interest;
update contactevent set classcode='~~',vacancyid=null,employmentid=null where contacteventid='TEST';
update tempshiftplan set vacancyid='TEST' where tempshiftplanid='TEST';
delete from contactevent where contacteventid='TEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyAdditionalComments" IS 
{CREATE PROCEDURE pears."NetAgencyAdditionalComments"( IN "pWebUserID" CHAR(20),IN "pLineID" CHAR(22)) 
RESULT( "ActionTime" CHAR(20),"SentTo" CHAR(50),"Note" long VARCHAR,shiftref CHAR(10),EventType CHAR(30),ContactDate DATE,ContactTime TIME ) 
// IQXWeb
BEGIN
  DECLARE "VacID" CHAR(20);
  DECLARE "TempShiftID" CHAR(20);
  DECLARE "CompID" CHAR(20);
  DECLARE "TempShiftRef" CHAR(20);
  DECLARE "VacRef" CHAR(20);
  IF LEFT(pLineId,1)='V' THEN
    SET VacID=RIGHT(pLineID,20);
  END IF;
  IF LEFT(pLineId,1)='P' THEN
    SET TempShiftID=RIGHT(pLineID,20);
  END IF;
  SET "CompID" = (SELECT top 1 "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" ORDER BY employment.employmentid);
  IF VacID IS NULL THEN
    SET "VacID" = (SELECT VacancyID FROM "TempShiftPlan" WHERE "tempshiftplanid" = "TempShiftID");
  END IF;
  SELECT DISTINCT
    CAST("dateformat"("ce"."contactdate",'dd/mm/yyyy') AS CHAR)+' '+CAST("dateformat"("ce"."contacttime",'hh:nn') AS CHAR) AS "ActionTime",
    "s"."name" AS "SentTo",
    "ce"."notes" AS "Note",
    "substring"("ce"."description","charindex"(':',"ce"."description")+1) AS "ShiftRef",
     cc.classdescrip,
    "ce"."contactdate" AS "contactdate",
    "ce"."contacttime" AS "contacttime"
    FROM "vacancy" AS "va"
      ,"tempshiftplan" AS "tsp"
      RIGHT OUTER JOIN "vacancy" AS "va"
      KEY JOIN "contactevent" AS "ce"
      KEY JOIN "staff" AS "s"
      ,"contactevent" AS "ce"
      KEY JOIN "employment" AS "e",
    contactevent ce KEY JOIN contactclass cc
    WHERE(("ce"."classcode" IN ('CD','CI') AND "va"."vacancyid" = "VacID")
    AND "e"."companyid" = "CompID")
    ORDER BY "contactdate" DESC,"contacttime" DESC
END
 
/* DOC
28-08-2013 Provide additional Comments for the detail line of Current Requirements.
            This routine requires contact events with classcode of CD and CI
25-11-2016 PC Doc and Test
17-04-2018 PC modify test
2019-03-04 PC IW-873 add top 1 and order by on subqueries
 
Tests:
    Lineid starting with V with no contact event
    Lineid starting with V with contact event
    Lineid starting with P with contact event
 
*/
 
/* TEST
NetTestSetup('');
delete from contactevent where contacteventid='TEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
insert into vacancy (vacancyid,departmentid,employmentid,status,position) values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2');
insert into contactclass (classcode,classdescrip) on existing update defaults off values ('CI','Confirm Interest');
insert into contactclass (classcode,classdescrip) on existing update defaults off values ('CD','Decline Interest');
insert into contactclass (classcode,classdescrip) on existing update defaults off values ('~~','Test');
insert into contactevent (contacteventid,staffid,contactdate,description,classcode) on existing update defaults off values ('TEST','TEST',now(*),'Test','~~');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
update contactevent set classcode='CI',vacancyid='TESTTESTTESTTESTTEST',employmentid='TEST' where contacteventid='TEST';
select shiftref,SentTo,replace(EventType,' ','!') as A from NetAgencyAdditionalComments('test.agency','VTESTTESTTESTTESTTEST') ;
expect shiftref=Test,SentTo=TestStaff,A=Confirm!Interest;
insert into tempshiftplan (tempshiftplanid,vacancyid,description) values ('TESTTESTTESTTESTTEST','TESTTESTTESTTESTTEST','Test2');
select shiftref,SentTo,replace(EventType,' ','!') as B from NetAgencyAdditionalComments('test.agency','PTESTTESTTESTTESTTEST') ;
expect shiftref=Test,SentTo=TestStaff,B=Confirm!Interest;
update contactevent set classcode='~~',vacancyid=null,employmentid=null where contacteventid='TEST';
update tempshiftplan set vacancyid='TEST' where tempshiftplanid='TEST';
delete from contactevent where contacteventid='TEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
*/
}