====== pears.NetCandidateDiaryAddMultipleEvents ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
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';
*/
}