pears.NetCandidateDiaryAddMultipleEvents

CREATE PROCEDURE "pears"."NetCandidateDiaryAddMultipleEvents"( IN "pWebUserID" CHAR(20) DEFAULT NULL,IN "pParamString" long VARCHAR DEFAULT NULL,IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "ShiftType" CHAR(10);
  DECLARE "DateOfShift" CHAR(10);
  DECLARE "ShiftTimeFrom" CHAR(10);
  DECLARE "ShiftTimeTo" CHAR(10);
  DECLARE "IDCode" CHAR(20);
  DECLARE "ShiftAction" CHAR(5);
  DECLARE "NoteText" CHAR(255);
  DECLARE "OriginalNoteText" CHAR(255);
  DECLARE "RowCounter" INTEGER;
  DECLARE "FieldIndex" INTEGER;
  DECLARE "ParamString" long VARCHAR;
  DECLARE "CancelReasonID" CHAR(1);
  DECLARE "availID" CHAR(2);
  DECLARE "unAvailID" CHAR(2);
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    SELECT '99:~Unique candidate link required';
    RETURN
  END IF;
  IF "pParamString" IS NULL THEN
    SELECT '100:~Parameters missing';
    RETURN
  END IF;
  SET "ParamString" = "pParamString";
  SET "RowCounter" = "length"("ParamString");
  SET "CancelReasonID" = 'T';
  while "RowCounter" > 1 loop
    SET "ShiftType" = NULL;
    SET "DateOfShift" = NULL;
    SET "ShiftTimeFrom" = NULL;
    SET "ShiftTimeTo" = NULL;
    SET "IDCode" = NULL;
    SET "ShiftAction" = NULL;
    SET "NoteText" = NULL;
    SET "FieldIndex" = "charindex"('|',"ParamString");
    SET "ShiftType" = "left"("ParamString","FieldIndex"-1);
    SET "ParamString" = "right"("ParamString","length"("ParamString")-"FieldIndex");
    SET "FieldIndex" = "charindex"('|',"ParamString");
    SET "DateOfShift" = "left"("ParamString","FieldIndex"-1);
    SET "ParamString" = "right"("ParamString","length"("ParamString")-"FieldIndex");
    SET "FieldIndex" = "charindex"('|',"ParamString");
    SET "ShiftTimeFrom" = "left"("ParamString","FieldIndex"-1);
    SET "ParamString" = "right"("ParamString","length"("ParamString")-"FieldIndex");
    SET "FieldIndex" = "charindex"('|',"ParamString");
    SET "ShiftTimeTo" = "left"("ParamString","FieldIndex"-1);
    SET "ParamString" = "right"("ParamString","length"("ParamString")-"FieldIndex");
    SET "FieldIndex" = "charindex"('|',"ParamString");
    SET "IDCode" = "left"("ParamString","FieldIndex"-1);
    SET "ParamString" = "right"("ParamString","length"("ParamString")-"FieldIndex");
    SET "FieldIndex" = "charindex"('|',"ParamString");
    SET "ShiftAction" = "left"("ParamString","FieldIndex"-1);
    SET "ParamString" = "right"("ParamString","length"("ParamString")-"FieldIndex");
    SET "FieldIndex" = "charindex"('|',"ParamString");
    SET "NoteText" = "left"("ParamString","FieldIndex"-1);
    SET "ParamString" = "right"("ParamString","length"("ParamString")-"FieldIndex");
    IF "isnull"("DateOfShift",'') <> '' THEN
      IF "DateOfShift" < "today"() THEN
        SELECT '101:~Date of shift is in the past';
        RETURN
      END IF;
      IF "DateOfShift" > "dateadd"("mm",6,"today"()) THEN
        SELECT '102:~Unable to add events more than six months in the future';
        RETURN
      END IF END IF;
    IF "ShiftAction" = '' THEN
      IF "isnull"("DateOfShift",'') = '' THEN
        SELECT '103:~Invalid date';
        RETURN
      END IF;
      IF "substring"("ShiftType",2,1) <> 'W' AND("isnull"("ShiftTimeFrom",'') = '' OR "isnull"("ShiftTimeTo",'') = '') THEN
        SELECT '104:~Invalid time';
        RETURN
      END IF;
      IF "substring"("ShiftType",2,1) = 'W' THEN -- Whole days
        SET "ShiftTimeFrom" = NULL;
        SET "ShiftTimeTo" = NULL
      END IF;
      SET "ShiftType" = "left"("ShiftType",1);
      IF "ShiftType" NOT IN( 'H','A','U','' ) THEN //Holiday/Available/Unavailable/Other editing
        SELECT '105:~invalid availability type';
        RETURN
      END IF;
      IF "ShiftType" = 'A' THEN // Remove any overlapping Holiday OR Unavailability
        DELETE FROM "tempshift" WHERE "personid" = "pPersonID" AND "shiftdate" BETWEEN "date"("DateOfShift")-1 AND "date"("DateOfShift")+1 AND "NetShiftDateTime"("shiftdate","timefrom","timeto",'F') <= "NetShiftDateTime"("DateOfShift","ShiftTimeFrom","ShiftTimeTo",'T') AND "NetShiftDateTime"("shiftdate","timefrom","timeto",'T') >= "NetShiftDateTime"("DateOfShift","ShiftTimeFrom","ShiftTimeTo",'F') AND "state" IN( 'H','U' ) 
      END IF;
      SET "availID" = NULL;
      SET "unAvailID" = NULL;
      IF "ShiftType" = 'A' THEN
        SET "availID" = "nullif"("trim"("IDCode"),'')
      END IF;
      IF "ShiftType" = 'U' THEN
        SET "unAvailID" = "nullif"("trim"("IDCode"),'')
      END IF END IF;
    SET "OriginalNoteText" = (SELECT "Note" FROM "tempshift" WHERE "tempshiftid" = "IDCode");
    IF "OriginalNoteText" IS NOT NULL THEN
      SET "OriginalNoteText" = "string"("char"(13),"OriginalNoteText")
    END IF;
    IF "ShiftAction" = 'C' THEN //Cancel
      IF "isnull"("NoteText",'') <> '' THEN
        SET "NoteText" = "string"('cancelled by candidate ',"dateformat"("now"(),'dd/mm/yyyy'),' ',"NoteText","OriginalNoteText")
      END IF;
      SET "CancelReasonID" = "isnull"((SELECT "right"("description","length"("description")-"charindex"(':',"description")) FROM "IQXNetSwitch" WHERE "iqxnetswitchid" = 'DEFAULTSHIFTCANCEL'),"CancelReasonID");
      UPDATE "tempshift" SET "state" = "ShiftAction","whencancelled" = CURRENT TIMESTAMP,"whocancelled" = "pPersonID","crefill" = 1,"clientconfirmed" = 0,"tempconfirmed" = 1,"cancelreason" = "CancelReasonID","Note" = "NoteText" WHERE "tempShiftID" = "IDCode" AND "personid" = "pPersonID" AND "vacancyid" IS NOT NULL;
      CALL "IQXNetRequestAction"("pWebUserID",'CandidateReject','SHIFT',"IDCode")
    END IF;
    IF "ShiftAction" = 'F' THEN //conFirm
      UPDATE "tempshift" SET "tempconfirmed" = 1 WHERE "tempshiftid" = "IDCode" AND "personid" = "pPersonID" AND "vacancyid" IS NOT NULL;
      CALL "IQXNetRequestAction"("pWebUserID",'CandidateConfirm','SHIFT',"IDCode")
    END IF;
    IF "ShiftAction" = 'D' THEN //DELETE
      DELETE FROM "tempshift" WHERE "tempshiftid" = "IDCode" AND "personid" = "pPersonID" AND "vacancyid" IS NULL AND "state" IN( 'H','A','U' ) ;
      CALL "IQXNetRequestAction"("pWebUserID",'CandidateDelete','SHIFT',"IDCode")
    END IF;
    IF "ShiftAction" = '' THEN
      INSERT INTO "tempshift"( "tempshiftid","personid","shiftdate","timefrom","timeto","state","Note","AvailTemplateID","UnavailableReason" ) VALUES( "uniquekey"("DateOfShift"+"ShiftTimeFrom"),"pPersonID","DateOfShift","ShiftTimeFrom","ShiftTimeTo","ShiftType","NoteText","availID","unAvailID" ) 
    END IF;
    SET "RowCounter" = "length"("ParamString")
  END loop;
  IF "ShiftAction" = '' THEN
    IF("isnull"("WPKMaintainGetSwitchValue"('AVAILCHECKCONTEVENT','','L'),'') <> '') THEN
      CALL "NetCreateAvailabilityContactEvent"("pWebUserID",'Candidate added availability on the candidate portal.')
    END IF END IF;
  SELECT '0:~Success'
END /* DOC
2018-01-26 PC accept a single string of multiple events, parse it into individual events, and insert,cancel,delete them in tempshift table
2018-04-30 ET removed error trapping for finish time is before end time, as this is not an error if the event spans multiple days
2018-05-16 MHS When entering availability, remove any overlapping holiday or unavailability. Add the UnavailableReason and AvailTemplateID where applicable. Add new tests and leave all working.
2018-06-18 PC modify test routines
2018-07-23 PC IW-603 limit date of event to between today and six months in the future
2018-07-24 PC include improved error handler
2018-10-10 MHS IW-456 multi-cand fix
2020-03-16 GJ Added auto contact even creation when adding availability.
2020-04-23 GJ Fixed an issue where the loop would bomb out.
 
input string params:
pShiftType  H=Holiday, A=Available, U=Unavailable
pDateFrom
pTimeFrom
pTimeTo
pIDCode     TempShiftID, in conjunction with pAction
pAction     C=Cancel (shift), F=Confirm, D=Delete (non shift), M=Modify (times)
pNote       free text
 
Tests:
null values
pWebUserID valid, string null
pWebUserID valid, pShiftType invalid
pWebUserID valid, date null
pWebUserID valid, times invalid
pWebUserID valid, finish and start times reversed
pWebUserID valid, insert parameters valid
pWebUserID valid, insert for whole day
pWebUserID valid, confirm
pWebUserID valid, cancel
pWebUserID valid, delete
pWebUserID valid, two inserts
*/
/* TEST
delete from tempshift where personid='TEST' or tempshiftid='test';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
select pResult from NetCandidateDiaryAddMultipleEvents(null,null);
expect pResult=99*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate',null);
expect pResult=100*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','Z|2018-01-01|10:00|15:00|||notehere|');
expect pResult=101*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','H|||||||');
expect pResult=103*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','H|2018-01-01||||||');
expect pResult=101*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','H|'+dateformat(today(*),'yyyy-mm-dd')+'|10:00|15:00|||notehere|');
expect pResult=0*;
select top 1 (if shiftdate=today(*) then 1 else 0 endif) as D,timefrom,timeto,state,note,isnull(cancelreason,'!') as A,tempconfirmed,isnull(whocancelled,'!') as B,isnull(unavailablereason,'!') as C from tempshift order by whenentered desc;
expect D=1,timefrom=10:00*,timeto=15:00*,state=H,note=notehere,A=!,tempconfirmed=0,B=!,C=!;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','HW|'+dateformat(today(*),'yyyy-mm-dd')+'|||||notehere|');
expect pResult=0*;
select top 1 (if shiftdate=today(*) then 1 else 0 endif) as F, isnull(timefrom,'00:01') as A, isnull(timeto,'00:02')as B, state, note, isnull(cancelreason,'!') as C, tempconfirmed, isnull(whocancelled,'!') as D, isnull(unavailablereason,'!') as E from tempshift order by whenentered desc;
expect F=1, A=00:01*, B=00:02*, state=H, note=notehere, C=!, tempconfirmed=0, D=!, E=!;
update tempshift set vacancyid='TEST' where tempshiftid=(select top 1 tempshiftid from tempshift order by whenentered desc);
update tempshift set tempshiftid='testshift' where tempshiftid=(select top 1 tempshiftid from tempshift order by whenentered desc);
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','||||testshift|F|notehere|');
expect pResult=0*;
select top 1 (if shiftdate=today(*) then 1 else 0 endif) as F, isnull(timefrom,'00:01') as A, isnull(timeto,'00:03')as B, state, note, isnull(cancelreason,'!') as C, tempconfirmed, isnull(whocancelled,'!') as D, isnull(unavailablereason,'!') as E from tempshift order by whenentered desc;
expect F=1, A=00:01*, B=00:03*, state=H, note=notehere, C=!, tempconfirmed=1, D=!, E=!;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','||||testshift|C|notehere|');
expect pResult=0*;
select top 1 (if shiftdate=today(*) then 1 else 0 endif) as F,isnull(timefrom,'00:01') as A, isnull(timeto,'00:04')as B, state, note, isnull(cancelreason,'!') as C, tempconfirmed, isnull(whocancelled,'!') as D, isnull(unavailablereason,'!') as E from tempshift order by whenentered desc;
expect F=1, A=00:01*, B=00:04*, state=C, note=cancelled*,  tempconfirmed=1, D=TEST, E=!;
update tempshift set vacancyid=null,state='H' where tempshiftid='testshift';
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','||||testshift|D|notehere|');
expect pResult=0*;
select top 1 shiftdate, isnull(timefrom,'00:01') as A, isnull(timeto,'00:05')as B, state, note, isnull(cancelreason,'!') as C, tempconfirmed, isnull(whocancelled,'!') as D, isnull(unavailablereason,'!') as E from tempshift where tempshiftid='testshift' order by whenentered desc;
expect EOF;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','A|'+dateformat(today(*),'yyyy-mm-dd')+'|10:00|15:00|||notehereA|U|'+dateformat(today(*),'yyyy-mm-dd')+'|15:00|18:00|||notehereB|');
expect pResult=0*;
select top 2 (if shiftdate=today(*) then 1 else 0 endif) as F, timefrom, timeto, state, note, isnull(cancelreason,'!') as A, tempconfirmed, isnull(whocancelled,'!') as B, isnull(unavailablereason,'!') as C from tempshift order by whenentered desc;
expect F=1, timefrom=15:00*, timeto=18:00*, state=U, note=notehereB, A=!, tempconfirmed=0, B=!, C=!;
expect F=1, timefrom=10:00*, timeto=15:00*, state=A, note=notehereA, A=!, tempconfirmed=0, B=!, C=!;
insert into tempshift (tempshiftid,state,personid,shiftdate) values ('testaholiday','H','TEST','2003-12-25');
select * from tempshift where tempshiftid='testaholiday';
expect personid=TEST;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','A|2003-12-24|22:00|06:00|||notehere|');
expect pResult=101*;
select * from tempshift where tempshiftid='testaholiday';
expect TempShiftID=testaholiday;
delete from tempshift where personid='TEST' or tempshiftid='test';
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDiaryAddMultipleEvents" IS 
{CREATE PROCEDURE pears."NetCandidateDiaryAddMultipleEvents"(IN pWebUserID CHAR(20) DEFAULT NULL,IN pParamString long VARCHAR DEFAULT NULL, pPersonID CHAR(20) DEFAULT NULL)
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
    DECLARE ShiftType CHAR(10);
    DECLARE DateOfShift CHAR(10);
    DECLARE ShiftTimeFrom CHAR(10);
    DECLARE ShiftTimeTo CHAR(10);
    DECLARE IDCode CHAR(20);
    DECLARE ShiftAction CHAR(5);
    DECLARE NoteText CHAR(255);
    DECLARE OriginalNoteText CHAR(255);
    DECLARE RowCounter INTEGER;
    DECLARE FieldIndex INTEGER;
    DECLARE ParamString long VARCHAR;
    DECLARE CancelReasonID CHAR(1);
    DECLARE availID CHAR(2);
    DECLARE unAvailID CHAR(2);
 
    SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
    IF pPersonID IS NULL THEN
      SELECT '99:~Unique candidate link required';
      RETURN;
    END IF;
 
    IF pParamString IS NULL THEN
        SELECT '100:~Parameters missing';
        RETURN
    END IF;
 
    SET ParamString=pParamString;
    SET RowCounter=LENGTH(ParamString);
    SET CancelReasonID = 'T';
 
    while RowCounter > 1 loop
        SET ShiftType=NULL;
        SET DateOfShift=NULL;
        SET ShiftTimeFrom=NULL;
        SET ShiftTimeTo=NULL;
        SET IDCode=NULL;
        SET ShiftAction=NULL;
        SET NoteText=NULL;
 
        SET FieldIndex=charindex('|',ParamString);
        SET ShiftType=LEFT(ParamString,FieldIndex-1);
        SET ParamString=RIGHT(ParamString,LENGTH(ParamString)-FieldIndex);
 
        SET FieldIndex=charindex('|',ParamString);
        SET DateOfShift=LEFT(ParamString,FieldIndex-1);
        SET ParamString=RIGHT(ParamString,LENGTH(ParamString)-FieldIndex);
 
        SET FieldIndex=charindex('|',ParamString);
        SET ShiftTimeFrom=LEFT(ParamString,FieldIndex-1);
        SET ParamString=RIGHT(ParamString,LENGTH(ParamString)-FieldIndex);
 
        SET FieldIndex=charindex('|',ParamString);
        SET ShiftTimeTo=LEFT(ParamString,FieldIndex-1);
        SET ParamString=RIGHT(ParamString,LENGTH(ParamString)-FieldIndex);
 
        SET FieldIndex=charindex('|',ParamString);
        SET IDCode=LEFT(ParamString,FieldIndex-1);
        SET ParamString=RIGHT(ParamString,LENGTH(ParamString)-FieldIndex);
 
        SET FieldIndex=charindex('|',ParamString);
        SET ShiftAction=LEFT(ParamString,FieldIndex-1);
        SET ParamString=RIGHT(ParamString,LENGTH(ParamString)-FieldIndex);
 
        SET FieldIndex=charindex('|',ParamString);
        SET NoteText=LEFT(ParamString,FieldIndex-1);
        SET ParamString=RIGHT(ParamString,LENGTH(ParamString)-FieldIndex);
 
        IF isnull(DateOfShift,'') !='' THEN
            IF DateOfShift < today(*) THEN
                SELECT '101:~Date of shift is in the past';
                RETURN
            END IF;
 
            IF DateOfShift > dateadd(mm,6,today(*)) THEN
                SELECT '102:~Unable to add events more than six months in the future';
                RETURN
            END IF;
        END IF;
        IF ShiftAction = '' THEN
            IF isnull(DateOfShift,'') ='' THEN
                SELECT '103:~Invalid date';
                RETURN
            END IF;
            IF SUBSTRING(ShiftType,2,1) != 'W'  AND (isnull(ShiftTimeFrom,'')='' OR isnull(ShiftTimeTo,'')='') THEN
                SELECT '104:~Invalid time';
                RETURN
            END IF;
 
            IF SUBSTRING(ShiftType,2,1) = 'W' THEN -- Whole days
                SET ShiftTimeFrom=NULL;
                SET ShiftTimeTo=NULL
            END IF;
            SET ShiftType="left"(ShiftType,1);
 
            IF ShiftType NOT IN( 'H','A','U','') THEN //Holiday/Available/Unavailable/Other editing
                SELECT '105:~invalid availability type';
                RETURN
            END IF;
 
            IF ShiftType='A' THEN  // Remove any overlapping Holiday OR Unavailability
               DELETE FROM tempshift WHERE personid=pPersonID AND shiftdate BETWEEN DATE(DateOfShift)-1 AND DATE(DateOfShift)+1 AND NetShiftDateTime(shiftdate,timefrom,timeto,'F') <= NetShiftDateTime(DateOfShift,ShiftTimeFrom,ShiftTimeTo,'T') AND NetShiftDateTime(shiftdate,timefrom,timeto,'T') >= NetShiftDateTime(DateOfShift,ShiftTimeFrom,ShiftTimeTo,'F') AND state IN ('H','U');  
            END IF;
 
            SET availID=NULL;
            SET unAvailID=NULL;
            IF ShiftType='A' THEN
              SET availID=NULLIF(TRIM(IDCode),'')
            END IF;
            IF ShiftType='U' THEN
              SET unAvailID=NULLIF(TRIM(IDCode),'')
            END IF;
 
        END IF;
 
        SET OriginalNoteText=(SELECT Note FROM tempshift WHERE tempshiftid=IDCode);
        IF OriginalNoteText IS NOT NULL THEN
            SET OriginalNoteText=string(CHAR(13),OriginalNoteText);
        END IF;
 
        IF ShiftAction = 'C' THEN //Cancel
            IF isnull(NoteText,'') != '' THEN
                SET NoteText=string('cancelled by candidate ',dateformat(now(*),'dd/mm/yyyy'),' ',NoteText,OriginalNoteText);
            END IF;
            SET CancelReasonID=isnull((SELECT RIGHT(description,LENGTH(description)-charindex(':',description)) FROM IQXNetSwitch WHERE iqxnetswitchid='DEFAULTSHIFTCANCEL'),CancelReasonID);
            UPDATE tempshift SET state = ShiftAction,whencancelled = CURRENT TIMESTAMP,whocancelled = pPersonID,crefill = 1,clientconfirmed = 0,tempconfirmed = 1,cancelreason = CancelReasonID, Note=NoteText WHERE tempShiftID = IDCode AND personid = pPersonID AND vacancyid IS NOT NULL;
            CALL IQXNetRequestAction(pWebUserID,'CandidateReject','SHIFT',IDCode);
        END IF;
        IF ShiftAction = 'F' THEN //conFirm
            UPDATE tempshift SET tempconfirmed = 1 WHERE tempshiftid = IDCode AND personid = pPersonID AND vacancyid IS NOT NULL;
            CALL IQXNetRequestAction(pWebUserID,'CandidateConfirm','SHIFT',IDCode);
        END IF;
        IF ShiftAction = 'D' THEN //DELETE
            DELETE FROM tempshift WHERE tempshiftid = IDCode AND personid = pPersonID AND vacancyid IS NULL AND state IN( 'H','A','U') ;
            CALL IQXNetRequestAction(pWebUserID,'CandidateDelete','SHIFT',IDCode);
        END IF;
        IF ShiftAction = '' THEN
            INSERT INTO tempshift( tempshiftid,personid,shiftdate,timefrom,timeto,state,Note,AvailTemplateID,UnavailableReason) VALUES( uniquekey(DateOfShift+ShiftTimeFrom),pPersonID,DateOfShift,ShiftTimeFrom,ShiftTimeTo,ShiftType,NoteText,availID,unAvailID);
        END IF;
 
        SET RowCounter=LENGTH(ParamString);
    END loop;
        IF ShiftAction = '' THEN
            IF(isnull(WPKMaintainGetSwitchValue('AVAILCHECKCONTEVENT', '', 'L'), '') <> '') THEN
                CALL NetCreateAvailabilityContactEvent(pWebUserID, 'Candidate added availability on the candidate portal.'); 
            END IF;
        END IF;
    SELECT '0:~Success'
END
 
/* DOC
2018-01-26 PC accept a single string of multiple events, parse it into individual events, and insert,cancel,delete them in tempshift table
2018-04-30 ET removed error trapping for finish time is before end time, as this is not an error if the event spans multiple days
2018-05-16 MHS When entering availability, remove any overlapping holiday or unavailability. Add the UnavailableReason and AvailTemplateID where applicable. Add new tests and leave all working.
2018-06-18 PC modify test routines
2018-07-23 PC IW-603 limit date of event to between today and six months in the future
2018-07-24 PC include improved error handler
2018-10-10 MHS IW-456 multi-cand fix
2020-03-16 GJ Added auto contact even creation when adding availability.
2020-04-23 GJ Fixed an issue where the loop would bomb out.
 
input string params:
    pShiftType  H=Holiday, A=Available, U=Unavailable
    pDateFrom
    pTimeFrom
    pTimeTo
    pIDCode     TempShiftID, in conjunction with pAction
    pAction     C=Cancel (shift), F=Confirm, D=Delete (non shift), M=Modify (times)
    pNote       free text
 
Tests:
    null values
    pWebUserID valid, string null
    pWebUserID valid, pShiftType invalid
    pWebUserID valid, date null
    pWebUserID valid, times invalid
    pWebUserID valid, finish and start times reversed
    pWebUserID valid, insert parameters valid
    pWebUserID valid, insert for whole day
    pWebUserID valid, confirm
    pWebUserID valid, cancel
    pWebUserID valid, delete
    pWebUserID valid, two inserts
*/
 
/* TEST
delete from tempshift where personid='TEST' or tempshiftid='test';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
select pResult from NetCandidateDiaryAddMultipleEvents(null,null);
expect pResult=99*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate',null);
expect pResult=100*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','Z|2018-01-01|10:00|15:00|||notehere|');
expect pResult=101*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','H|||||||');
expect pResult=103*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','H|2018-01-01||||||');
expect pResult=101*;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','H|'+dateformat(today(*),'yyyy-mm-dd')+'|10:00|15:00|||notehere|');
expect pResult=0*;
select top 1 (if shiftdate=today(*) then 1 else 0 endif) as D,timefrom,timeto,state,note,isnull(cancelreason,'!') as A,tempconfirmed,isnull(whocancelled,'!') as B,isnull(unavailablereason,'!') as C from tempshift order by whenentered desc;
expect D=1,timefrom=10:00*,timeto=15:00*,state=H,note=notehere,A=!,tempconfirmed=0,B=!,C=!;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','HW|'+dateformat(today(*),'yyyy-mm-dd')+'|||||notehere|');
expect pResult=0*;
select top 1 (if shiftdate=today(*) then 1 else 0 endif) as F, isnull(timefrom,'00:01') as A, isnull(timeto,'00:02')as B, state, note, isnull(cancelreason,'!') as C, tempconfirmed, isnull(whocancelled,'!') as D, isnull(unavailablereason,'!') as E from tempshift order by whenentered desc;
expect F=1, A=00:01*, B=00:02*, state=H, note=notehere, C=!, tempconfirmed=0, D=!, E=!;
update tempshift set vacancyid='TEST' where tempshiftid=(select top 1 tempshiftid from tempshift order by whenentered desc);
update tempshift set tempshiftid='testshift' where tempshiftid=(select top 1 tempshiftid from tempshift order by whenentered desc);
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','||||testshift|F|notehere|');
expect pResult=0*;
select top 1 (if shiftdate=today(*) then 1 else 0 endif) as F, isnull(timefrom,'00:01') as A, isnull(timeto,'00:03')as B, state, note, isnull(cancelreason,'!') as C, tempconfirmed, isnull(whocancelled,'!') as D, isnull(unavailablereason,'!') as E from tempshift order by whenentered desc;
expect F=1, A=00:01*, B=00:03*, state=H, note=notehere, C=!, tempconfirmed=1, D=!, E=!;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','||||testshift|C|notehere|');
expect pResult=0*;
select top 1 (if shiftdate=today(*) then 1 else 0 endif) as F,isnull(timefrom,'00:01') as A, isnull(timeto,'00:04')as B, state, note, isnull(cancelreason,'!') as C, tempconfirmed, isnull(whocancelled,'!') as D, isnull(unavailablereason,'!') as E from tempshift order by whenentered desc;
expect F=1, A=00:01*, B=00:04*, state=C, note=cancelled*,  tempconfirmed=1, D=TEST, E=!;
update tempshift set vacancyid=null,state='H' where tempshiftid='testshift';
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','||||testshift|D|notehere|');
expect pResult=0*;
select top 1 shiftdate, isnull(timefrom,'00:01') as A, isnull(timeto,'00:05')as B, state, note, isnull(cancelreason,'!') as C, tempconfirmed, isnull(whocancelled,'!') as D, isnull(unavailablereason,'!') as E from tempshift where tempshiftid='testshift' order by whenentered desc;
expect EOF;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','A|'+dateformat(today(*),'yyyy-mm-dd')+'|10:00|15:00|||notehereA|U|'+dateformat(today(*),'yyyy-mm-dd')+'|15:00|18:00|||notehereB|');
expect pResult=0*;
select top 2 (if shiftdate=today(*) then 1 else 0 endif) as F, timefrom, timeto, state, note, isnull(cancelreason,'!') as A, tempconfirmed, isnull(whocancelled,'!') as B, isnull(unavailablereason,'!') as C from tempshift order by whenentered desc;
expect F=1, timefrom=15:00*, timeto=18:00*, state=U, note=notehereB, A=!, tempconfirmed=0, B=!, C=!;
expect F=1, timefrom=10:00*, timeto=15:00*, state=A, note=notehereA, A=!, tempconfirmed=0, B=!, C=!;
insert into tempshift (tempshiftid,state,personid,shiftdate) values ('testaholiday','H','TEST','2003-12-25');
select * from tempshift where tempshiftid='testaholiday';
expect personid=TEST;
select pResult from NetCandidateDiaryAddMultipleEvents('test.candidate','A|2003-12-24|22:00|06:00|||notehere|');
expect pResult=101*;
select * from tempshift where tempshiftid='testaholiday';
expect TempShiftID=testaholiday;
delete from tempshift where personid='TEST' or tempshiftid='test';
 
*/
}