====== pears.NetCandidateDiaryAdd ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
create procedure "pears"."NetCandidateDiaryAdd"( in "pWebUserID" char(20) default null,in "pshifttype" char(10) default null,in "pdatefrom" char(50) default null,in "pdateto" char(50) default null,
in "ptimefrom" char(50) default null,in "ptimeto" char(50) default null,in "IDCode" char(20) default null,in "pState" char(2) default null,in "pPersonID" char(20) default null )
result( "pResult" char(250) )
// IQXWeb
begin
declare "dfrom" date;
declare "dto" date;
declare "tfrom" time;
declare "tto" time;
set "pState" = "trim"("isnull"("left"("pState",1),''));
if "pState" = 'C' then //Cancel
update "tempshift" set "state" = "pState","whencancelled" = current timestamp,"whocancelled" = "userstaffid","crefill" = 1,"clientconfirmed" = 0,"tempconfirmed" = 1,"cancelreason" = 'T'
where "tempShiftID" = "IDCode" and "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") and "vacancyid" is not null;
call "IQXNetRequestAction"("pWebUserID",'CandidateReject','SHIFT',"IDCode");
select '0:~Success';
return
end if;
if "pState" = 'F' then //conFirm
update "tempshift" set "tempconfirmed" = 1 where "tempshiftid" = "IDCode" and "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") and "vacancyid" is not null;
call "IQXNetRequestAction"("pWebUserID",'CandidateConfirm','SHIFT',"IDCode");
select '0:~Success';
return
end if;
if "pState" = 'D' then //delete
delete from "tempshift" where "tempshiftid" = "IDCode" and "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID") and "vacancyid" is null and "state" in( 'H','A','U' ) ;
call "IQXNetRequestAction"("pWebUserID",'CandidateDelete','SHIFT',"IDCode");
if("isnull"("WPKMaintainGetSwitchValue"('AVAILCHECKCONTEVENT','','L'),'') <> '') then
call "NetCreateAvailabilityContactEvent"("pWebUserID",'Candidate removed availability on the candidate portal.')
end if;
select '0:~Success';
return
end if;
set "pshifttype" = "trim"("isnull"("pshifttype",''));
if "substring"("pshifttype",2,1) = 'W' then -- Whole days
set "ptimefrom" = '';
set "ptimeto" = ''
end if;
set "pshifttype" = "left"("pshifttype",1);
set "dfrom" = "iqxnetstringtodate"("pdatefrom");
set "dto" = "iqxnetstringtodate"("pdateto");
set "tfrom" = "iqxnetstringtotime"("ptimefrom");
set "tto" = "iqxnetstringtotime"("ptimeto");
if "IDCode" <> '' and "pState" = '' then
update "tempshift" set "shiftdate" = "dfrom","timefrom" = "tfrom","timeto" = "tto" where "tempShiftID" = "IDCode" and "vacancyid" is null and "personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID");
select '0:~Success';
return
end if;
set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
if "pPersonID" is null then
select '99:~Unique candidate link required';
return
end if;
if "pshifttype" not in( 'H','A','U' ) then //Holiday/Available/Unavailable
select '101:~invalid availability type';
return
end if;
if("trim"("isnull"("pdatefrom",'')) <> '' and "dfrom" is null)
or("trim"("isnull"("pdateto",'')) <> '' and "dto" is null)
or("trim"("isnull"("ptimefrom",'')) <> '' and "tfrom" is null)
or("trim"("isnull"("ptimeto",'')) <> '' and "tto" is null) then
select '102:~Invalid date or time';
return
end if;
if "dfrom" is null then
select '103:~Invalid date';
return
end if;
if "dto" < "dfrom" then
select '104:~Finish day appears to be before Start day';
return
end if;
set "dto" = "isnull"("dto","dfrom");
if "tfrom" is null then
set "tto" = null
end if;
if "tto" is null then
set "tfrom" = null
end if;
while "dfrom" <= "dto" loop
insert into "tempshift"( "tempshiftid","personid","shiftdate","timefrom","timeto","state" ) values( "uniquekey"("dfrom"+"tfrom"),"pPersonID","dfrom","tfrom","tto","pshifttype" ) ;
set "dfrom" = "dfrom"+1
end loop;
select '0:~Success'
end /* DOC
2016-12-01 PC Doc and test
2018-01-23 PC modify primary key seed to avoid duplicating on several events on same day but different time
2018-05-17 MHS Fix and simplify tests. Allowed fromtime>totime for overnight availability etc.
2018-07-24 PC include improved error handler
2018-10-10 MHS IW-456 multi-cand fix. For shift updates and deletes it only affects them if they belong to the cand. For inserts it insists on unique or explicit pPersonID.
pdatefrom dd/mm/yyyy : Shift date - use when adding or editing holiday, availability or unavailability.
pdateto dd/mm/yyyy : End of date range - use when adding holiday, availability or unavailability.
ptimefrom and
ptimeto hh:mm : use when editing or adding holiday, availability or unavailability. Blank for whole days.
pshifttype: Use when adding A - availability, H - holiday or U - unavailability.
IDCode: The tempShiftID - use when editing or deleting existing holiday, availability or unavailability,
or confirming or cancelling a shift. Blank otherwise.
pState: C for cancel shift. F for confirm shift. D for delete holiday, availability or unavailability. Blank otherwise.
Inputs:
pWebUserID
pshifttype
pdatefrom
pdateto
ptimefrom
ptimeto
IDCode
pState
Outputs:
pResult
*/
/* TEST
call NetTestSetup('');
delete from tempshift where personid='TEST';
select * from NetCandidateDiaryAdd(null,null,null,null,null,null,null,null);
expect 99*;
select * from NetCandidateDiaryAdd('test.candidate',null,null,null,null,null,null,null);
expect 101*;
select * from NetCandidateDiaryAdd('test.candidate','H',null,null,null,null,null,null);
expect 103*;
select * from NetCandidateDiaryAdd('test.candidate','',null,null,null,null,null,'F');
expect 0*;
select * from NetCandidateDiaryAdd('test.candidate','',null,null,null,null,null,'C');
expect 0*;
select * from NetCandidateDiaryAdd('test.candidate','',null,null,null,null,null,'D');
expect 0*;
select * from NetCandidateDiaryAdd('test.candidate','H',dateformat(now(*),'dd/mm/yyyy'),dateformat(now(*),'dd/mm/yyyy'),dateformat(now(*),'hh:mm:ss'),dateformat(dateadd(hh,1,now(*)),'hh:mm:ss'),null,null);
expect 0*;
select * from tempshift where personid='TEST';
expect State=H;
expect EOF;
*/
go
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDiaryAdd" IS
{create PROCEDURE pears."NetCandidateDiaryAdd"(in pWebUserID char(20) default null,in pshifttype char(10) default null,in pdatefrom char(50) default null,in pdateto char(50) default null,in ptimefrom char(50) default
null,in ptimeto char(50) default null,in IDCode char(20) default null,in pState char(2) default null, in pPersonID char(20) default null)
result(pResult char(250))
// IQXWeb
begin
declare dfrom date;
declare dto date;
declare tfrom time;
declare tto time;
set pState=trim(isnull("left"(pState,1),''));
if pState = 'C' then //Cancel
update tempshift set state = pState,whencancelled = current timestamp,whocancelled = userstaffid,crefill = 1,clientconfirmed = 0,tempconfirmed = 1,cancelreason = 'T'
where tempShiftID = IDCode and "personid" in (select personid from iqxnetuserlink where iqxnetuserid=pWebUserID) and vacancyid is not null;
call IQXNetRequestAction(pWebUserID,'CandidateReject','SHIFT',IDCode);
select '0:~Success';
return
end if;
if pState = 'F' then //conFirm
update tempshift set tempconfirmed = 1 where tempshiftid = IDCode and "personid" in (select personid from iqxnetuserlink where iqxnetuserid=pWebUserID) and vacancyid is not null;
call IQXNetRequestAction(pWebUserID,'CandidateConfirm','SHIFT',IDCode);
select '0:~Success';
return
end if;
if pState = 'D' then //delete
delete from tempshift where tempshiftid = IDCode and "personid" in (select personid from iqxnetuserlink where iqxnetuserid=pWebUserID) and vacancyid is null and state in( 'H','A','U') ;
call IQXNetRequestAction(pWebUserID,'CandidateDelete','SHIFT',IDCode);
if(isnull(WPKMaintainGetSwitchValue('AVAILCHECKCONTEVENT', '', 'L'), '') <> '') then
CALL NetCreateAvailabilityContactEvent(pWebUserID, 'Candidate removed availability on the candidate portal.');
end if;
select '0:~Success';
return
end if;
set pshifttype=trim(isnull(pshifttype,''));
if substring(pshifttype,2,1) = 'W' then -- Whole days
set ptimefrom='';
set ptimeto=''
end if;
set pshifttype="left"(pshifttype,1);
set dfrom=iqxnetstringtodate(pdatefrom);
set dto=iqxnetstringtodate(pdateto);
set tfrom=iqxnetstringtotime(ptimefrom);
set tto=iqxnetstringtotime(ptimeto);
if IDCode <> '' and pState = '' then
update tempshift set shiftdate = dfrom,timefrom = tfrom,timeto = tto where tempShiftID = IDCode and vacancyid is null and "personid" in (select personid from iqxnetuserlink where iqxnetuserid=pWebUserID);
select '0:~Success';
return
end if;
set pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
if pPersonID is null then
select '99:~Unique candidate link required';
return;
end if;
if pshifttype not in( 'H','A','U') then //Holiday/Available/Unavailable
select '101:~invalid availability type';
return
end if;
if(trim(isnull(pdatefrom,'')) <> '' and dfrom is null)
or(trim(isnull(pdateto,'')) <> '' and dto is null)
or(trim(isnull(ptimefrom,'')) <> '' and tfrom is null)
or(trim(isnull(ptimeto,'')) <> '' and tto is null) then
select '102:~Invalid date or time';
return
end if;
if dfrom is null then
select '103:~Invalid date';
return
end if;
if dto < dfrom then
select '104:~Finish day appears to be before Start day';
return;
end if;
set dto=isnull(dto,dfrom);
if tfrom is null then
set tto=null
end if;
if tto is null then
set tfrom=null
end if;
while dfrom <= dto loop
insert into tempshift( tempshiftid,personid,shiftdate,timefrom,timeto,state) values( uniquekey(dfrom+tfrom),pPersonID,dfrom,tfrom,tto,pshifttype) ;
set dfrom=dfrom+1
end loop;
select '0:~Success'
end
/* DOC
2016-12-01 PC Doc and test
2018-01-23 PC modify primary key seed to avoid duplicating on several events on same day but different time
2018-05-17 MHS Fix and simplify tests. Allowed fromtime>totime for overnight availability etc.
2018-07-24 PC include improved error handler
2018-10-10 MHS IW-456 multi-cand fix. For shift updates and deletes it only affects them if they belong to the cand. For inserts it insists on unique or explicit pPersonID.
pdatefrom dd/mm/yyyy : Shift date - use when adding or editing holiday, availability or unavailability.
pdateto dd/mm/yyyy : End of date range - use when adding holiday, availability or unavailability.
ptimefrom and
ptimeto hh:mm : use when editing or adding holiday, availability or unavailability. Blank for whole days.
pshifttype: Use when adding A - availability, H - holiday or U - unavailability.
IDCode: The tempShiftID - use when editing or deleting existing holiday, availability or unavailability,
or confirming or cancelling a shift. Blank otherwise.
pState: C for cancel shift. F for confirm shift. D for delete holiday, availability or unavailability. Blank otherwise.
Inputs:
pWebUserID
pshifttype
pdatefrom
pdateto
ptimefrom
ptimeto
IDCode
pState
Outputs:
pResult
*/
/* TEST
call NetTestSetup('');
delete from tempshift where personid='TEST';
select * from NetCandidateDiaryAdd(null,null,null,null,null,null,null,null);
expect 99*;
select * from NetCandidateDiaryAdd('test.candidate',null,null,null,null,null,null,null);
expect 101*;
select * from NetCandidateDiaryAdd('test.candidate','H',null,null,null,null,null,null);
expect 103*;
select * from NetCandidateDiaryAdd('test.candidate','',null,null,null,null,null,'F');
expect 0*;
select * from NetCandidateDiaryAdd('test.candidate','',null,null,null,null,null,'C');
expect 0*;
select * from NetCandidateDiaryAdd('test.candidate','',null,null,null,null,null,'D');
expect 0*;
select * from NetCandidateDiaryAdd('test.candidate','H',dateformat(now(*),'dd/mm/yyyy'),dateformat(now(*),'dd/mm/yyyy'),dateformat(now(*),'hh:mm:ss'),dateformat(dateadd(hh,1,now(*)),'hh:mm:ss'),null,null);
expect 0*;
select * from tempshift where personid='TEST';
expect State=H;
expect EOF;
*/
}