====== pears.NetClientAddShiftsMultiWeek ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
create procedure "pears"."NetClientAddShiftsMultiWeek"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pstartdate" char(25),
in "pExtraWeeks" char(20) default null,in "pTheirRef" char(50) default null,in "pOrderReason" char(50) default null,in "pMonday" char(20),
in "pMondayFrom" char(25) default null,in "pMondayTo" char(25) default null,in "pMondayBrk" char(20) default null,in "pMondayQty" char(20) default null,in "pMondayNote" char(50) default null,in "pTuesday" char(20),
in "pTuesdayFrom" char(25) default null,in "pTuesdayTo" char(25) default null,in "pTuesdayBrk" char(20) default null,in "pTuesdayQty" char(20) default null,in "pTuesdayNote" char(50) default null,in "pWednesday" char(20),
in "pWednesdayFrom" char(25) default null,in "pWednesdayTo" char(25) default null,in "pWednesdayBrk" char(20) default null,in "pWednesdayQty" char(20) default null,in "pWednesdayNote" char(50) default null,in "pThursday" char(20),
in "pThursdayFrom" char(25) default null,in "pThursdayTo" char(25) default null,in "pThursdayBrk" char(20) default null,in "pThursdayQty" char(20) default null,in "pThursdayNote" char(50) default null,in "pFriday" char(20),
in "pFridayFrom" char(25) default null,in "pFridayTo" char(25) default null,in "pFridayBrk" char(20) default null,in "pFridayQty" char(20) default null,in "pFridayNote" char(50) default null,in "pSaturday" char(20),
in "pSaturdayFrom" char(25) default null,in "pSaturdayTo" char(25) default null,in "pSaturdayBrk" char(20) default null,in "pSaturdayQty" char(20) default null,in "pSaturdayNote" char(50) default null,in "pSunday" char(20),
in "pSundayFrom" char(25) default null,in "pSundayTo" char(25) default null,in "pSundayBrk" char(20) default null,in "pSundayQty" char(20) default null,in "pSundayNote" char(50) default null )
result( "pResult" char(250) )
begin
declare "swebusername" char(50);
declare "ddate" date;
declare "dstart" date;
declare "dend" date;
declare "idow" integer;
declare "CanAuthorise" smallint;
declare "thelog" long varchar;
declare local temporary table "IDs"(
"ID" char(20) null,
) not transactional;
insert into "IDs"( "ID" )
select distinct "e"."companyid" from "iqxnetuserlink" as "l" key join "employment" as "e" where "l"."iqxnetuserid" = "pWebUserID";
if not "pvacancyid" = any(select "v"."vacancyid" from "vacancy" as "v" key join "employment" as "e" join "IDs" on "e"."companyid" = "IDs"."ID") then
select '99:~Permission denied';
return
end if;
if "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 then
select '99:~Permission denied';
return
end if;
set "swebusername" = (select "name" from "iqxnetuser" where "iqxnetuserid" = "pwebuserid");
set "dstart" = "iqxnetstringtodate"("pstartdate");
if "dstart" is null then
select '500:~Invalid start date';
return
end if;
set "CanAuthorise" = "IQXNetHasPermission"("pWebUserID",'AUTHORISEJOBS');
set "ddate" = "dstart";
set "dend" = "dateadd"("day",6+(7*"isnull"("iqxnetstringtointeger"("pextraweeks"),0)),"dstart");
while "ddate" <= "dend" loop
set "idow" = "dow"("ddate");
case "idow"
when 1 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "psunday","ptimefrom" = "psundayfrom","ptimeto" = "psundayto","pbreak" = "psundaybrk","pnote" = "psundaynote","pqty" = "psundayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 2 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "pmonday","ptimefrom" = "pmondayfrom","ptimeto" = "pmondayto","pbreak" = "pmondaybrk","pnote" = "pmondaynote","pqty" = "pmondayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 3 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "ptuesday","ptimefrom" = "ptuesdayfrom","ptimeto" = "ptuesdayto","pbreak" = "ptuesdaybrk","pnote" = "ptuesdaynote","pqty" = "ptuesdayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 4 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "pwednesday","ptimefrom" = "pwednesdayfrom","ptimeto" = "pwednesdayto","pbreak" = "pwednesdaybrk","pnote" = "pwednesdaynote","pqty" = "pwednesdayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 5 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "pthursday","ptimefrom" = "pthursdayfrom","ptimeto" = "pthursdayto","pbreak" = "pthursdaybrk","pnote" = "pthursdaynote","pqty" = "pthursdayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 6 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "pfriday","ptimefrom" = "pfridayfrom","ptimeto" = "pfridayto","pbreak" = "pfridaybrk","pnote" = "pfridaynote","pqty" = "pfridayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 7 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "psaturday","ptimefrom" = "psaturdayfrom","ptimeto" = "psaturdayto","pbreak" = "psaturdaybrk","pnote" = "psaturdaynote","pqty" = "psaturdayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
end case;
set "ddate" = "dateadd"("day",1,"ddate")
end loop;
if "CanAuthorise" = 1 then
call "IQXNetPopup"("pWebUserID",null,null,"pvacancyid",null,null,'ADDSHIFTS','New shift requirement(s) added',"thelog")
end if;
select '0:~Success'
end
go
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientAddShiftsMultiWeek" IS
{create procedure pears."NetClientAddShiftsMultiWeek"( in "pWebUserID" char(20),in "pvacancyid" char(20),in "pstartdate" char(25),
in "pExtraWeeks" char(20) default null,in "pTheirRef" char(50) default null,in "pOrderReason" char(50) default null,in "pMonday" char(20),
in "pMondayFrom" char(25) default null,in "pMondayTo" char(25) default null,in "pMondayBrk" char(20) default null,in "pMondayQty" char(20) default null,in "pMondayNote" char(50) default null,in "pTuesday" char(20),
in "pTuesdayFrom" char(25) default null,in "pTuesdayTo" char(25) default null,in "pTuesdayBrk" char(20) default null,in "pTuesdayQty" char(20) default null,in "pTuesdayNote" char(50) default null,in "pWednesday" char(20),
in "pWednesdayFrom" char(25) default null,in "pWednesdayTo" char(25) default null,in "pWednesdayBrk" char(20) default null,in "pWednesdayQty" char(20) default null,in "pWednesdayNote" char(50) default null,in "pThursday" char(20),
in "pThursdayFrom" char(25) default null,in "pThursdayTo" char(25) default null,in "pThursdayBrk" char(20) default null,in "pThursdayQty" char(20) default null,in "pThursdayNote" char(50) default null,in "pFriday" char(20),
in "pFridayFrom" char(25) default null,in "pFridayTo" char(25) default null,in "pFridayBrk" char(20) default null,in "pFridayQty" char(20) default null,in "pFridayNote" char(50) default null,in "pSaturday" char(20),
in "pSaturdayFrom" char(25) default null,in "pSaturdayTo" char(25) default null,in "pSaturdayBrk" char(20) default null,in "pSaturdayQty" char(20) default null,in "pSaturdayNote" char(50) default null,in "pSunday" char(20),
in "pSundayFrom" char(25) default null,in "pSundayTo" char(25) default null,in "pSundayBrk" char(20) default null,in "pSundayQty" char(20) default null,in "pSundayNote" char(50) default null )
result( "pResult" char(250) )
begin
declare "swebusername" char(50);
declare "ddate" date;
declare "dstart" date;
declare "dend" date;
declare "idow" integer;
declare "CanAuthorise" smallint;
declare "thelog" long varchar;
declare local temporary table "IDs"(
"ID" char(20) null,
) not transactional;
insert into "IDs"( "ID" )
select distinct "e"."companyid" from "iqxnetuserlink" as "l" key join "employment" as "e" where "l"."iqxnetuserid" = "pWebUserID";
if not "pvacancyid" = any(select "v"."vacancyid" from "vacancy" as "v" key join "employment" as "e" join "IDs" on "e"."companyid" = "IDs"."ID") then
select '99:~Permission denied';
return
end if;
if "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 then
select '99:~Permission denied';
return
end if;
set "swebusername" = (select "name" from "iqxnetuser" where "iqxnetuserid" = "pwebuserid");
set "dstart" = "iqxnetstringtodate"("pstartdate");
if "dstart" is null then
select '500:~Invalid start date';
return
end if;
set "CanAuthorise" = "IQXNetHasPermission"("pWebUserID",'AUTHORISEJOBS');
set "ddate" = "dstart";
set "dend" = "dateadd"("day",6+(7*"isnull"("iqxnetstringtointeger"("pextraweeks"),0)),"dstart");
while "ddate" <= "dend" loop
set "idow" = "dow"("ddate");
case "idow"
when 1 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "psunday","ptimefrom" = "psundayfrom","ptimeto" = "psundayto","pbreak" = "psundaybrk","pnote" = "psundaynote","pqty" = "psundayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 2 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "pmonday","ptimefrom" = "pmondayfrom","ptimeto" = "pmondayto","pbreak" = "pmondaybrk","pnote" = "pmondaynote","pqty" = "pmondayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 3 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "ptuesday","ptimefrom" = "ptuesdayfrom","ptimeto" = "ptuesdayto","pbreak" = "ptuesdaybrk","pnote" = "ptuesdaynote","pqty" = "ptuesdayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 4 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "pwednesday","ptimefrom" = "pwednesdayfrom","ptimeto" = "pwednesdayto","pbreak" = "pwednesdaybrk","pnote" = "pwednesdaynote","pqty" = "pwednesdayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 5 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "pthursday","ptimefrom" = "pthursdayfrom","ptimeto" = "pthursdayto","pbreak" = "pthursdaybrk","pnote" = "pthursdaynote","pqty" = "pthursdayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 6 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "pfriday","ptimefrom" = "pfridayfrom","ptimeto" = "pfridayto","pbreak" = "pfridaybrk","pnote" = "pfridaynote","pqty" = "pfridayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
when 7 then call "NetClientAddShiftsOneDay"("pshifttemplateid" = "psaturday","ptimefrom" = "psaturdayfrom","ptimeto" = "psaturdayto","pbreak" = "psaturdaybrk","pnote" = "psaturdaynote","pqty" = "psaturdayqty","pwebuserid" = "pwebuserid","pvacancyid" = "pvacancyid","pcanauthorise" = "canauthorise","pdate" = "ddate","ptheirref" = "ptheirref","porderreason" = "porderreason","pwebusername" = "swebusername","plog" = "thelog")
end case;
set "ddate" = "dateadd"("day",1,"ddate")
end loop;
if "CanAuthorise" = 1 then
call "IQXNetPopup"("pWebUserID",null,null,"pvacancyid",null,null,'ADDSHIFTS','New shift requirement(s) added',"thelog")
end if;
select '0:~Success'
end
}