====== pears.NetProvTimesheetShiftAdd ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetProvTimesheetShiftAdd"( in "pWebUserID" char(20),in "pTempProvTimesheetID" char(20),in "pshiftdate" char(250),in "ptimefrom" char(250),in "ptimeto" char(250),in "pbreakminutes" char(250) default '0',in "preferencecode" char(20) default null,in "ptempshifttypeid" char(2) default null ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "newshiftid" char(20);
  declare "userClass" char(20);
  declare "svacancyid" char(20);
  declare "spersonid" char(20);
  declare "splacementid" char(20);
  declare "xdate" date;
  declare "xtimefrom" time;
  declare "xtimeto" time;
  declare "checkstart" date;
  declare "checkend" date;
  declare local temporary table "IDs"(
    "ID" char(20) null,
    ) not transactional;
  set "xdate" = "iqxnetstringtodate"("pshiftdate");
  set "xtimefrom" = "iqxnetstringtotime"("ptimefrom");
  set "xtimeto" = "iqxnetstringtotime"("ptimeto");
  if "xdate" is null or "xtimefrom" is null or "xtimeto" is null then
    select '101:~Missing or invalid date or times';
    return
  end if;
  if "trim"("pbreakminutes") = '' then
    set "pbreakminutes" = '0'
  end if;
  set "preferencecode" = "nullif"("trim"("ucase"("preferencecode")),'');
  set "ptempshifttypeid" = "nullif"("trim"("ptempshifttypeid"),'');
  set "userClass" = (select first "iqxnetuserclassid" from "iqxnetuser" where "iqxnetuserid" = "pWebUserID");
  if "userClass" = 'OWNER' then
    insert into "IDs"( "ID" ) 
      select distinct "s"."divisionid" from "iqxnetuser" as "i" key join "staff" as "s" where "i"."iqxnetuserid" = "pwebuserid";
    if not "ptempprovtimesheetid" = any(select "t"."tempprovtimesheetid" from "tempprovtimesheet" as "t" key join "person" as "p" join "IDs" on "p"."divisionid" = "IDs"."ID") then
      select '99:~Permission denied';
      return
    end if
  else if not "ptempprovtimesheetid" = any(select "t"."tempprovtimesheetid"
        from "tempprovtimesheet" as "t" key join "person" key join "pay_employee" key join "company" as "agcomp" key join "employment" as "agemp" key join "iqxnetuserlink"
        where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" union
      select "t"."tempprovtimesheetid"
        from "tempprovtimesheet" as "t" key join "person" key join "iqxnetuserlink"
        where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" union
      select "t"."tempprovtimesheetid" from "tempprovtimesheet" as "t" key join "vacancy" key join "employment" as "vacemp" key join "company" key join "employment" key join "iqxnetuserlink"
        where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid") then
      select '99:~Permission denied';
      return
    end if end if;
  select "vacancyid","personid","placementid","weekmonthstartdate"("period","periodlength"),"weekmonthenddate"("period","periodlength") into "svacancyid","spersonid","splacementid","checkstart","checkend" from "tempprovtimesheet" where "tempprovtimesheetid" = "ptempprovtimesheetid";
  if "xdate" < "checkstart" or "xdate" > "checkend" then
    select '102:~Date must be between '+"dateformat"("checkstart",'dd/mm/yyyy')+' and '+"dateformat"("checkend",'dd/mm/yyyy');
    return
  end if;
  if "isnull"("IQXNetSwitchValue"("pwebuserid",'SHIFTDATELIMIT'),0) = 1 then
    select "p"."vacancyid","e"."personid","p"."placementid","e"."startdate","dateformat"("isnull"("e"."leavedate","dateadd"("year",1,"today"())),'yyyy-mm-dd') into "svacancyid","spersonid","splacementid","checkstart","checkend" from "tempprovtimesheet" key join "placement" as "p" key join "employment" as "e" where "tempprovtimesheetid" = "ptempprovtimesheetid";
    if "xdate" not between "checkstart" and "checkend" then
      select '103:~Date must be between '+"dateformat"("checkstart",'dd/mm/yyyy')+' and '+"dateformat"("checkend",'dd/mm/yyyy');
      return
    end if end if;
  set "newshiftid" = "uniquekey"('');
  insert into "tempshiftplan"( "TempShiftPlanID","VacancyID","ShiftDate","TimeFrom","TimeTo","BreakMinutes","Description","ReferenceCode",
    "TempShiftTypeID" ) values
    ( "newshiftid","sVacancyID","xdate","xtimefrom","xtimeto","iqxnetstringtointeger"("pbreakminutes"),'Self-booked',"preferencecode","ptempshifttypeid" ) ;
  insert into "tempshift"( "TempShiftID","VacancyID","PersonID","PlacementID","ShiftDate","TimeFrom","TimeTo","BreakMinutes","State","TempShiftPlanID","ReferenceCode","ClientConfirmed","TempConfirmed","TempShiftTypeID" ) values
    ( "newshiftid","sVacancyID","sPersonID","sPlacementID","xdate","xtimefrom","xtimeto","iqxnetstringtointeger"("pbreakminutes"),'B',"newshiftid","preferencecode",1,1,"ptempshifttypeid" ) ;
  select '0:'+"newshiftid"+'~Success'
end /* DOC
2017-04-03 MHS new proc with tests
2018-02-26 PC IW-381 check shift date between employment start and end dates
2018-07-26 PC include improved error handler
2018-09-05 ET changed back to old way of returning errors, updated error number so that system errors are < 100 and user errors >= 100
2018-10-11 PC IW-705 reverse IW-381

Tests:
invalid pWebUserID
valid pWebUserID, pTempProvTimesheetID, invalid dates and times
valid pWebUserID, invalid pTempProvTimesheetID as owner
valid pWebUserID, invalid pTempProvTimesheetID as client
valid pWebUserID, pTempProvTimesheetID, new shift as candidate

*/
/* TEST
call NetTestSetup('');
select * from NetProvTimesheetShiftAdd('TEST.CANDIDATE','TEST', '', '' ,'' ,'', 'ref');
expect 101*;
select * from NetProvTimesheetShiftAdd('TEST.OWNER','zzzzzzz', '25/12/2012', '09:00' ,'17:00' ,'', 'ref');
expect 99*;
select * from NetProvTimesheetShiftAdd('TEST.CLIENT','zzzzzzz', '25/12/2012', '09:00' ,'17:00' ,'', 'ref');
expect 99*;
select * from NetProvTimesheetShiftAdd('TEST.CANDIDATE','TEST', '25/12/2012', '09:00' ,'17:00' ,'53', 'testRefXYZ');
expect pResult=102:~Date*;
select shiftdate,timefrom,timeto,breakminutes from tempshiftplan where vacancyid='TEST' and referencecode='testRefXYZ';
expect EOF;
select shiftdate,timefrom,timeto,breakminutes,personid,placementid from tempshift where vacancyid='TEST' and referencecode='testRefXYZ';
expect EOF;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetShiftAdd" IS 
{create PROCEDURE pears."NetProvTimesheetShiftAdd"(in pWebUserID char(20),in pTempProvTimesheetID char(20), in pshiftdate char(250), in ptimefrom char(250) ,in ptimeto char(250) ,in pbreakminutes char(250) default '0', in preferencecode char(20) default null, in ptempshifttypeid char(2) default null)
result(pResult char(250))
// IQXWeb
begin
  declare newshiftid char(20);
  declare userClass char(20);
  declare svacancyid char(20);
  declare spersonid char(20);
  declare splacementid char(20);
  declare xdate date;
  declare xtimefrom time;
  declare xtimeto time;
  declare checkstart date;
  declare checkend date;
  declare local temporary table IDs(
    ID char(20) null,
    ) not transactional;
  set xdate=iqxnetstringtodate(pshiftdate);
  set xtimefrom=iqxnetstringtotime(ptimefrom);
  set xtimeto=iqxnetstringtotime(ptimeto);
  if xdate is null or xtimefrom is null or xtimeto is null then
    select '101:~Missing or invalid date or times';
    return;
  end if;
  if trim(pbreakminutes) = '' then
    set pbreakminutes='0'
  end if;
  set preferencecode=nullif(trim(ucase(preferencecode)),'');
  set ptempshifttypeid=nullif(trim(ptempshifttypeid),'');
  set userClass=(select first iqxnetuserclassid from iqxnetuser where iqxnetuserid = pWebUserID);
  if userClass = 'OWNER' then
    insert into IDs( ID) 
      select distinct s.divisionid from iqxnetuser as i key join staff as s where i.iqxnetuserid = pwebuserid;
    if not ptempprovtimesheetid = any(select t.tempprovtimesheetid from tempprovtimesheet as t key join person as p join IDs on p.divisionid = IDs.ID) then
        select '99:~Permission denied';
      return
    end if
  else
    if not ptempprovtimesheetid = any(select t.tempprovtimesheetid
        from tempprovtimesheet as t key join person key join pay_employee key join company as agcomp key join employment as agemp key join iqxnetuserlink
        where iqxnetuserlink.iqxnetuserid = pwebuserid union
      select t.tempprovtimesheetid
        from tempprovtimesheet as t key join person key join iqxnetuserlink
        where iqxnetuserlink.iqxnetuserid = pwebuserid union
      select t.tempprovtimesheetid from tempprovtimesheet as t key join vacancy key join employment as vacemp key join company key join employment key join iqxnetuserlink
        where iqxnetuserlink.iqxnetuserid = pwebuserid) then
        select '99:~Permission denied';
      return
    end if
  end if;
  select vacancyid,personid,placementid,weekmonthstartdate(period,periodlength),weekmonthenddate(period,periodlength) into svacancyid,spersonid,splacementid,checkstart,checkend from tempprovtimesheet where tempprovtimesheetid=ptempprovtimesheetid;
  if xdate<checkstart or xdate>checkend then
      select '102:~Date must be between '+dateformat(checkstart,'dd/mm/yyyy')+' and '+dateformat(checkend,'dd/mm/yyyy');
    return
  end if;
  if isnull(IQXNetSwitchValue(pwebuserid,'SHIFTDATELIMIT'),0) = 1 then
    select p.vacancyid,e.personid,p.placementid,e.startdate, dateformat(isnull(e.leavedate,dateadd(year,1,today(*))),'yyyy-mm-dd') into svacancyid,spersonid,splacementid,checkstart,checkend from tempprovtimesheet key join placement p key join  employment e where tempprovtimesheetid=ptempprovtimesheetid;
    if xdate not between checkstart and checkend then
      select '103:~Date must be between '+dateformat(checkstart,'dd/mm/yyyy')+' and '+dateformat(checkend,'dd/mm/yyyy');
      return
    end if;
  end if;
  set newshiftid=uniquekey('');
  insert into tempshiftplan( TempShiftPlanID,VacancyID,ShiftDate,TimeFrom,TimeTo,BreakMinutes,Description,ReferenceCode,
    TempShiftTypeID) values
    (newshiftid,sVacancyID,xdate,xtimefrom,xtimeto,iqxnetstringtointeger(pbreakminutes),'Self-booked',preferencecode,ptempshifttypeid);
  insert into tempshift( TempShiftID,VacancyID,PersonID,PlacementID,ShiftDate,TimeFrom,TimeTo,BreakMinutes,State,TempShiftPlanID,ReferenceCode,ClientConfirmed,TempConfirmed,TempShiftTypeID) values 
  (newshiftid,sVacancyID,sPersonID,sPlacementID,xdate,xtimefrom,xtimeto,iqxnetstringtointeger(pbreakminutes),'B',newshiftid,preferencecode,1,1,ptempshifttypeid); 
  select '0:'+newshiftid+'~Success'
end

/* DOC
2017-04-03 MHS new proc with tests
2018-02-26 PC IW-381 check shift date between employment start and end dates
2018-07-26 PC include improved error handler
2018-09-05 ET changed back to old way of returning errors, updated error number so that system errors are < 100 and user errors >= 100
2018-10-11 PC IW-705 reverse IW-381

Tests:
    invalid pWebUserID
    valid pWebUserID, pTempProvTimesheetID, invalid dates and times
    valid pWebUserID, invalid pTempProvTimesheetID as owner
    valid pWebUserID, invalid pTempProvTimesheetID as client
    valid pWebUserID, pTempProvTimesheetID, new shift as candidate

*/

/* TEST
call NetTestSetup('');
select * from NetProvTimesheetShiftAdd('TEST.CANDIDATE','TEST', '', '' ,'' ,'', 'ref');
expect 101*;
select * from NetProvTimesheetShiftAdd('TEST.OWNER','zzzzzzz', '25/12/2012', '09:00' ,'17:00' ,'', 'ref');
expect 99*;
select * from NetProvTimesheetShiftAdd('TEST.CLIENT','zzzzzzz', '25/12/2012', '09:00' ,'17:00' ,'', 'ref');
expect 99*;
select * from NetProvTimesheetShiftAdd('TEST.CANDIDATE','TEST', '25/12/2012', '09:00' ,'17:00' ,'53', 'testRefXYZ');
expect pResult=102:~Date*;
select shiftdate,timefrom,timeto,breakminutes from tempshiftplan where vacancyid='TEST' and referencecode='testRefXYZ';
expect EOF;
select shiftdate,timefrom,timeto,breakminutes,personid,placementid from tempshift where vacancyid='TEST' and referencecode='testRefXYZ';
expect EOF;
*/
}
</code>