====== pears.NetProvTimesheetShiftSet ======
<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"."NetProvTimesheetShiftSet"( in "pWebUserID" char(20),in "ptempshiftid" char(250),in "pshiftdate" char(250) default 'NOT_SET',in "ptimefrom" char(250) default 'NOT_SET',in "ptimeto" char(250) default 'NOT_SET',in "pbreakminutes" char(250) default 'NOT_SET',in "preferencecode" char(20) default null,in "ptempshifttypeid" char(2) default null ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  declare "i" smallint;
  declare "isnewshift" smallint;
  declare "newshiftid" char(20);
  declare "userClass" char(20);
  declare "divid" char(20);
  declare "checkstart" date;
  declare "checkend" date;
  declare local temporary table "IDs"(
    "ID" char(20) null,
    ) not transactional;
  if "trim"("pbreakminutes") = '' then
    set "pbreakminutes" = '0'
  end if;
  set "preferencecode" = "ucase"("preferencecode");
  set "ptempshifttypeid" = "nullif"("trim"("ptempshifttypeid"),'');
  if "ptempshiftid" like 'Copy%' then
    set "isnewshift" = 1
  else
    set "isnewshift" = 0
  end if;
  set "i" = "charindex"('_',"ptempshiftid");
  if "i" > 0 then
    set "ptempshiftid" = "right"("ptempshiftid","length"("ptempshiftid")-"i")
  end if;
  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 "ptempshiftid" = any(select "s"."tempshiftid" from "tempshift" as "s" key join "person" as "p" join "IDs" on "p"."divisionid" = "IDs"."ID") then
      select '99:~Permission denied';
      return
    end if
  else if not "pTempShiftID" = any(
      select "t"."TempShiftID" from "tempshift" as "t" key join "person" key join "iqxnetuserlink" where "iqxnetuserid" = "pwebuserid" union
      select "t"."TempShiftID" from "tempshift" 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"."TempShiftID" from "tempshift" 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;
  if "isnewshift" = 1 then
    set "newshiftid" = "uniquekey"("ptempshiftid");
    if "trim"("preferencecode") = '' then
      set "preferencecode" = null
    end if;
    if "isnull"("IQXNetSwitchValue"("pwebuserid",'SHIFTDATELIMIT'),0) = 1 then
      select "e"."startdate","dateformat"("isnull"("e"."leavedate","dateadd"("year",1,"today"())),'yyyy-mm-dd') into "checkstart","checkend" from "tempshift" key join "placement" as "p" key join "employment" as "e" where "tempshiftid" = "ptempshiftid";
      if "iqxnetstringtodate"("pShiftDate") 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;
    insert into "tempshiftplan"( "TempShiftPlanID","VacancyID","ShiftDate","TimeFrom","TimeTo","BreakMinutes","Description","EssentialSkill","EssentialSkillGradeID","ReferenceCode",
      "EssentialSkillChoiceList","TempShiftTypeID","AnalysisCode","RecoveryHours" ) 
      select "newshiftid","VacancyID","isnull"("iqxnetstringtodate"("pshiftdate"),"shiftdate"),"isnull"("iqxnetstringtotime"("ptimefrom"),"timefrom"),
        "isnull"("iqxnetstringtotime"("ptimeto"),"timeto"),"isnull"("iqxnetstringtointeger"("pbreakminutes"),"breakminutes"),"Description","EssentialSkill","EssentialSkillGradeID","isnull"("preferencecode",'Self-booked'),
        "EssentialSkillChoiceList","isnull"("ptempshifttypeid","TempShiftTypeID"),"AnalysisCode","RecoveryHours" from "tempshiftplan"
        where "tempshiftplanid" = (select "tempshiftplanid" from "tempshift" where "tempshiftid" = "ptempshiftid");
    insert into "tempshift"( "TempShiftID","VacancyID","PersonID","PlacementID","ShiftDate","TimeFrom","TimeTo","BreakMinutes","State","TempShiftPlanID","EssentialSkillGradeID","ReferenceCode",
      "ClientConfirmed","TempConfirmed","TempShiftTypeID","AnalysisCode","RecoveryHours" ) 
      select "NewShiftID","VacancyID","PersonID","PlacementID","isnull"("iqxnetstringtodate"("pshiftdate"),"shiftdate"),"isnull"("iqxnetstringtotime"("ptimefrom"),"timefrom"),
        "isnull"("iqxnetstringtotime"("ptimeto"),"timeto"),"isnull"("iqxnetstringtointeger"("pbreakminutes"),"breakminutes"),'B',"NewShiftID","EssentialSkillGradeID","isnull"("preferencecode",'Self-booked'),
        1,1,"isnull"("ptempshifttypeid","TempShiftTypeID"),"AnalysisCode","RecoveryHours" from "tempshift"
        where "tempshiftid" = "ptempshiftid"
  else
    update "tempshift" set "shiftdate" = "isnull"("iqxnetstringtodate"("pshiftdate"),"shiftdate"),"timefrom" = "isnull"("iqxnetstringtotime"("ptimefrom"),"timefrom"),
      "timeto" = "isnull"("iqxnetstringtotime"("ptimeto"),"timeto"),"breakminutes" = "isnull"("iqxnetstringtointeger"("pbreakminutes"),"breakminutes"),
      "ReferenceCode" = "isnull"("preferencecode","ReferenceCode"),"TempShiftTypeID" = "isnull"("ptempshifttypeid","TempShiftTypeID")
      where "tempshiftid" = "ptempshiftid"
  end if;
  select '0:~Success'
end /* DOC
2016-12-07 PC doc and test
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, invalid pTempShiftID as owner
valid pWebUserID, invalid pTempShiftID as client
valid pWebUserID, pTempShiftID, new shift as client
valid pWebUserID, pTempShiftID, copy shift as client

*/
/* TEST
call NetTestSetup('');
update staff set divisionid='TEST' where staffid='TEST';
update person set divisionid='TEST' where personid='TEST';
update tempshift set personid='TEST' where tempshiftid='TEST';
select * from NetProvTimesheetShiftSet(null,null,null,null,null,null,null,null);
expect 99*;
select * from NetProvTimesheetShiftSet('test.owner',null,null,null,null,null,null,null);
expect 0*;
select * from NetProvTimesheetShiftSet('test.owner','Shift_TEST',null,null,null,null,null,null);
expect 0*;

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetShiftSet" IS 
{create PROCEDURE pears."NetProvTimesheetShiftSet"(in pWebUserID char(20),in ptempshiftid char(250),in pshiftdate char(250) default 'NOT_SET',in ptimefrom char(250) default 'NOT_SET',in ptimeto char(250) default 'NOT_SET',in pbreakminutes char(250) default 'NOT_SET', in preferencecode char(20) default null, in ptempshifttypeid char(2) default null)
result(pResult char(250))
// IQXWeb
begin
  declare i smallint;
  declare isnewshift smallint;
  declare newshiftid char(20);
  declare userClass char(20);
  declare divid char(20);
  declare checkstart date;
  declare checkend date;
  declare local temporary table IDs(
    ID char(20) null,
    ) not transactional;
  if trim(pbreakminutes) = '' then
    set pbreakminutes='0'
  end if;
  set preferencecode=ucase(preferencecode);
  set ptempshifttypeid=nullif(trim(ptempshifttypeid),'');
  if ptempshiftid like 'Copy%' then
    set isnewshift=1
  else
    set isnewshift=0
  end if;
  set i=charindex('_',ptempshiftid);
  if i > 0 then
    set ptempshiftid="right"(ptempshiftid,length(ptempshiftid)-i)
  end if;
  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 ptempshiftid = any(select s.tempshiftid from tempshift as s key join person as p join IDs on p.divisionid = IDs.ID) then
      select '99:~Permission denied';
      return
    end if
  else
    if not pTempShiftID = any(
      select t.TempShiftID from tempshift as t key join person key join iqxnetuserlink where iqxnetuserid = pwebuserid union
      select t.TempShiftID from tempshift 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.TempShiftID from tempshift 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;
  if isnewshift = 1 then
    set newshiftid=uniquekey(ptempshiftid);
    if trim(preferencecode)='' then
      set preferencecode=null
    end if;
    if isnull(IQXNetSwitchValue(pwebuserid,'SHIFTDATELIMIT'),0) = 1 then
        select e.startdate, dateformat(isnull(e.leavedate,dateadd(year,1,today(*))),'yyyy-mm-dd') into checkstart,checkend from tempshift key join placement p key join  employment e where tempshiftid=ptempshiftid;
        if iqxnetstringtodate(pShiftDate) 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;
    insert into tempshiftplan( TempShiftPlanID,VacancyID,ShiftDate,TimeFrom,TimeTo,BreakMinutes,Description,EssentialSkill,EssentialSkillGradeID,ReferenceCode,
      EssentialSkillChoiceList,TempShiftTypeID,AnalysisCode,RecoveryHours) 
      select newshiftid,VacancyID,isnull(iqxnetstringtodate(pshiftdate),shiftdate),isnull(iqxnetstringtotime(ptimefrom),timefrom),
        isnull(iqxnetstringtotime(ptimeto),timeto),isnull(iqxnetstringtointeger(pbreakminutes),breakminutes),Description,EssentialSkill,EssentialSkillGradeID,isnull(preferencecode,'Self-booked'),
        EssentialSkillChoiceList,isnull(ptempshifttypeid,TempShiftTypeID),AnalysisCode,RecoveryHours from tempshiftplan
        where tempshiftplanid = (select tempshiftplanid from tempshift where tempshiftid = ptempshiftid);
    insert into tempshift( TempShiftID,VacancyID,PersonID,PlacementID,ShiftDate,TimeFrom,TimeTo,BreakMinutes,State,TempShiftPlanID,EssentialSkillGradeID,ReferenceCode,
      ClientConfirmed,TempConfirmed,TempShiftTypeID,AnalysisCode,RecoveryHours) 
      select NewShiftID,VacancyID,PersonID,PlacementID,isnull(iqxnetstringtodate(pshiftdate),shiftdate),isnull(iqxnetstringtotime(ptimefrom),timefrom),
        isnull(iqxnetstringtotime(ptimeto),timeto),isnull(iqxnetstringtointeger(pbreakminutes),breakminutes),'B',NewShiftID,EssentialSkillGradeID,isnull(preferencecode,'Self-booked'),
        1,1,isnull(ptempshifttypeid,TempShiftTypeID),AnalysisCode,RecoveryHours from tempshift
        where tempshiftid = ptempshiftid
  else
    update tempshift set shiftdate = isnull(iqxnetstringtodate(pshiftdate),shiftdate),timefrom = isnull(iqxnetstringtotime(ptimefrom),timefrom),
      timeto = isnull(iqxnetstringtotime(ptimeto),timeto),breakminutes = isnull(iqxnetstringtointeger(pbreakminutes),breakminutes),
      ReferenceCode = isnull(preferencecode,ReferenceCode), TempShiftTypeID = isnull(ptempshifttypeid,TempShiftTypeID)
      where tempshiftid = ptempshiftid
  end if;
  select '0:~Success'
end

/* DOC
2016-12-07 PC doc and test
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, invalid pTempShiftID as owner
    valid pWebUserID, invalid pTempShiftID as client
    valid pWebUserID, pTempShiftID, new shift as client
    valid pWebUserID, pTempShiftID, copy shift as client

*/

/* TEST
call NetTestSetup('');
update staff set divisionid='TEST' where staffid='TEST';
update person set divisionid='TEST' where personid='TEST';
update tempshift set personid='TEST' where tempshiftid='TEST';
select * from NetProvTimesheetShiftSet(null,null,null,null,null,null,null,null);
expect 99*;
select * from NetProvTimesheetShiftSet('test.owner',null,null,null,null,null,null,null);
expect 0*;
select * from NetProvTimesheetShiftSet('test.owner','Shift_TEST',null,null,null,null,null,null);
expect 0*;

*/
}
</code>