====== pears.NetOwnerRequirementShifts ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
create procedure "pears"."NetOwnerRequirementShifts"( in "pWebUserID" char(20),in "pSlice" integer default 1,in "pSliceSize" integer default 10,in "pStartDate" char(26) default null,in "pEndDate" char(26) default null,in "pStatus" char(1) default null,in "pTempdeskID" char(20) default null )
result( "RowID" char(20),"StartDate" char(20),"CombinedTime" char(20),"IsShift" smallint,"Position" char(50),"ShiftDescription" char(50),"CompanyID" char(20),"CompanyName" char(60),"ContactID" char(20),"ContactName" char(60),"DetailStatus" char(30),"ListStatus" char(30),"Department" char(50),"Consultant" char(60),"OurRef" char(20),"ContractRef" char(20),"ShiftRef" char(20),"WhenEntered" timestamp,"FilledBy" char(250),"StartTime" char(15),"FinishTime" char(15),"FinishDate" char(30),"LongDescription" long varchar,"CompanyAddress" char(250),"PersonID" char(20),"TextStatus" char(100),"PONumber" char(50),"CreatedBy" char(255),"ConfirmStatus" char(250) )
begin
// IQXWeb
declare "DateRangeStart" date;
declare "DateRangeEnd" date;
declare "x" integer;
declare "y" integer;
declare "DefaultTempdeskID" char(20);
set "DateRangeStart" = "IQXNetStringToDate"("pStartDate");
set "DateRangeEnd" = "IQXNetStringToDate"("pEndDate");
set "DateRangeStart" = "isnull"("DateRangeStart","dateadd"("week",-1,current date));
set "DateRangeEnd" = "isnull"("DateRangeEnd","dateadd"("week",1,current date));
set "x" = "pSliceSize";
set "y" = "x"*"pSlice"+1;
if not exists(select * from "staff" key join "iqxnetuser" where "iqxnetuserid" = "pWebUserID" and "IQXNetUserClassID" = 'OWNER') then
return
end if;
set "DefaulttempdeskID" = "isnull"("pTempdeskID",(select top 1 "tempdeskid" from "staff" key join "iqxnetuser" where "iqxnetuserid" = "pWebUserID" and "IQXNetUserClassID" = 'OWNER' order by "tempdeskid" asc));
select top "x" start at "y"
"p"."TempShiftPlanID" as "RowID",
"p"."shiftdate" as "StartDate",
cast("dateformat"("StartTime",'hh:nn') as char)+' - '+cast("dateformat"("FinishTime",'hh:nn') as char) as "CombinedTime",
1 as "IsShift",
"v"."Position" as "Position",
"p"."Description" as "ShiftDescription",
"Company"."CompanyID",
"Company"."Name" as "CompanyName",
"Pe"."PersonID" as "ContactID",
"Pe"."Name" as "ContactName",
(select(if "Status"."final" = 1 or "ConfirmStatus" is null then "Status"."Name" else "ConfirmStatus" endif) from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "DetailStatus",
(select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "ListStatus",
(select "Name" from "Department" where "Department"."DepartmentID" = "v"."DepartmentID") as "Department",
(select "Name" from "Staff" where "Staff"."StaffID" = "v"."StaffID") as "Consultant",
"v"."RefCode" as "OurRef",
"v"."ContractRef",
cast("p"."ShiftSerialNumber" as char(20)) as "ShiftRef",
"p"."WhenEntered",
(select "name" from "person" where "personid" = "tes"."personid")+(if(select "status" from "person" where "personid" = "tes"."personid") = 'A' then ' (S)' endif) as "FilledBy",
"dateformat"("isnull"("tes"."timefrom","p"."TimeFrom"),'hh:mm') as "StartTime",
"dateformat"("isnull"("tes"."timeto","p"."TimeTo"),'hh:mm') as "FinishTime",
(if "FinishTime" < "StartTime" then "dateadd"("dd",1,"StartDate") else "StartDate" endif) as "FinishDate",
cast(null as long varchar) as "LongDescription",
(if 1 > 1 then "GetCompanyAddressOnLine"("company"."companyid")
else ''
endif) as "CompanyAddress",
"tes"."personid" as "PersonID",
(if(select "count"() from "contactevent" where "classcode" = 'SS' and "description" = "ShiftRef") = 1 then 'In Progress'
else(case "tes"."state" when 'B' then 'Confirmed' when 'W' then 'Worked' when 'P' then 'Pending' when 'C' then(select "cr"."name" from "TempShiftCancelReason" as "cr" where "cr"."TempShiftCancelReasonid" = "tes"."cancelreason")+' '+"tes"."note"+' at '+"dateformat"("tes"."whencancelled",'dd/mm/yyyy hh:nn')
else 'Pending'
end)
endif) as "statusText",
"p"."ReferenceCode" as "PONumber",
('Ordered by: '+"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm:ss')) as "CreatedBy",
if "isnull"("tes"."clientconfirmed",0) = 1 then 'Client confirmed'
else if "isnull"("tes"."tempconfirmed",0) = 1 then 'Candidate confirmed'
else null
endif
endif as "ConfirmStatus"
from "TempShiftPlan" as "p"
key join "Vacancy" as "v"
key join "Employment" as "e"
key join("Company","Person" as "Pe")
,"TempShiftPlan" as "p"
key left outer join "tempshift" as "tes"
where "StartDate" between "DateRangeStart" and "DateRangeEnd"
and "v"."status" = "isnull"("pStatus","v"."status")
and "v"."tempdeskid" = "DefaulttempdeskID"
and "isnull"("company"."divisionid",'') = any(select "DivisionID" from "DashboardStaffDivisions"())
order by "StartDate" desc
end /* DOC
2018-10-08 PC IW-700 Derived from NetOwnerRequirementList
2018-10-11 PC enforce sort order
2018-11-27 IW-758 prioritise selection by tempdesk
*/
/* TEST
update tempshiftplan set shiftdate=current date where tempshiftplanid='test';
update tempdesk set desktype='S' where tempdeskid in ('test','test2');
update staff set tempdeskid='test' where staffid='test';
update vacancy set status='C';
insert into deptmaintenance (departmentid,staffid) on existing update defaults off values ('~~','TEST');
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,null,null);
expect RowID=TEST,Position=test;
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,null,'x99');
expect EOF;
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,null,'Test');
expect RowID=TEST,Position=test;
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,null,'Test2');
expect EOF;
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,'C',null);
expect RowID=TEST,Position=test;
select * from NetOwnerRequirementShifts('test.client',0,100,null,null,'Z',null);
expect EOF;
*/
go
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementShifts" IS
{create PROCEDURE pears."NetOwnerRequirementShifts"( in "pWebUserID" char(20),in "pSlice" integer default 1,in pSliceSize integer default 10,in "pStartDate" char(26) default null,in "pEndDate" char(26) default null, in "pStatus" char(1) default null, in pTempdeskID char(20) default null)
result( "RowID" char(20),"StartDate" char(20),"CombinedTime" char(20),"IsShift" smallint,"Position" char(50),"ShiftDescription" char(50),CompanyID char(20),CompanyName char(60),ContactID char(20),ContactName char(60),DetailStatus char(30),ListStatus char(30),Department char(50),"Consultant" char(60),"OurRef" char(20),"ContractRef" char(20),"ShiftRef" char(20),"WhenEntered" timestamp,"FilledBy" char(250),StartTime char(15),FinishTime char(15),FinishDate char(30),"LongDescription" long varchar,"CompanyAddress" char(250),"PersonID" char(20),"TextStatus" char(100),"PONumber" char(50),CreatedBy char(255),ConfirmStatus char(250))
BEGIN
// IQXWeb
declare DateRangeStart date;
declare DateRangeEnd date;
declare x integer;
declare y integer;
declare DefaultTempdeskID char(20);
set DateRangeStart=IQXNetStringToDate(pStartDate);
set DateRangeEnd=IQXNetStringToDate(pEndDate);
set DateRangeStart = "isnull"(DateRangeStart,"dateadd"("week",-1,current date));
set DateRangeEnd = "isnull"(DateRangeEnd,"dateadd"("Week",1,current date));
set x=pSliceSize;
set y=x*pSlice+1;
if not exists(select * from staff key join iqxnetuser where iqxnetuserid=pWebUserID and IQXNetUserClassID ='OWNER') then
return ;
end if;
set DefaulttempdeskID=isnull(pTempdeskID,(select top 1 tempdeskid from staff key join iqxnetuser where iqxnetuserid=pWebUserID and IQXNetUserClassID ='OWNER' order by tempdeskid asc));
select top x start at y
"p"."TempShiftPlanID" as "RowID",
"p"."shiftdate" as "StartDate",
cast("dateformat"("StartTime",'hh:nn') as char)+' - '+cast("dateformat"("FinishTime",'hh:nn') as char) as "CombinedTime",
1 as "IsShift",
"v"."Position" as "Position",
"p"."Description" as "ShiftDescription",
"Company"."CompanyID",
"Company"."Name" as "CompanyName",
"Pe".PersonID as ContactID,
"Pe"."Name" as "ContactName",
(select (if Status.final=1 or ConfirmStatus is null then Status."Name" else ConfirmStatus endif) from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as DetailStatus,
(select "Name" from "Status" where "Status"."Status" = "v"."Status" and "Status"."Type" = 'V') as "ListStatus",
(select "Name" from "Department" where "Department"."DepartmentID" = "v"."DepartmentID") as "Department",
(select "Name" from "Staff" where "Staff"."StaffID" = "v"."StaffID") as "Consultant",
"v"."RefCode" as "OurRef",
"v"."ContractRef",
cast("p"."ShiftSerialNumber" as char(20)) as "ShiftRef",
"p"."WhenEntered",
(select "name" from "person" where "personid" = "tes"."personid")+(if(select "status" from "person" where "personid" = "tes"."personid") = 'A' then ' (S)' endif) as "FilledBy",
dateformat("isnull"("tes"."timefrom","p"."TimeFrom"),'hh:mm') as "StartTime",
dateformat("isnull"("tes"."timeto","p"."TimeTo"),'hh:mm') as "FinishTime",
(if "FinishTime" < "StartTime" then "dateadd"("dd",1,"StartDate") else "StartDate" endif) as "FinishDate",
cast(null as long varchar) as "LongDescription",
(if 1 > 1 then "GetCompanyAddressOnLine"("company"."companyid")
else ''
endif) as "CompanyAddress",
"tes"."personid" as "PersonID",
(if(select "count"() from "contactevent" where "classcode" = 'SS' and "description" = "ShiftRef") = 1 then 'In Progress'
else(case "tes"."state" when 'B' then 'Confirmed' when 'W' then 'Worked' when 'P' then 'Pending' when 'C' then(select "cr"."name" from "TempShiftCancelReason" as "cr" where "cr"."TempShiftCancelReasonid" = "tes"."cancelreason")+' '+"tes"."note"+' at '+"dateformat"("tes"."whencancelled",'dd/mm/yyyy hh:nn')
else 'Pending'
end)
endif) as "statusText",
"p"."ReferenceCode" as "PONumber",
('Ordered by: '+"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm:ss')) as "CreatedBy",
if isnull(tes.clientconfirmed,0)=1 then 'Client confirmed' else
if isnull(tes.tempconfirmed,0)=1 then 'Candidate confirmed' else
null
endif
endif as "ConfirmStatus"
from
"TempShiftPlan" as "p"
key join "Vacancy" as "v"
key join "Employment" as "e"
key join ("Company","Person" as "Pe"),
"TempShiftPlan" as "p"
key left outer join "tempshift" as "tes"
where
"StartDate" between DateRangeStart and DateRangeEnd
and v.status = isnull(pStatus,v.status)
and v.tempdeskid=DefaulttempdeskID
and isnull(company.divisionid,'') in (select DivisionID from DashboardStaffDivisions() )
order by
StartDate desc;
END
/* DOC
2018-10-08 PC IW-700 Derived from NetOwnerRequirementList
2018-10-11 PC enforce sort order
2018-11-27 IW-758 prioritise selection by tempdesk
*/
/* TEST
update tempshiftplan set shiftdate=current date where tempshiftplanid='test';
update tempdesk set desktype='S' where tempdeskid in ('test','test2');
update staff set tempdeskid='test' where staffid='test';
update vacancy set status='C';
insert into deptmaintenance (departmentid,staffid) on existing update defaults off values ('~~','TEST');
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,null,null);
expect RowID=TEST,Position=test;
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,null,'x99');
expect EOF;
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,null,'Test');
expect RowID=TEST,Position=test;
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,null,'Test2');
expect EOF;
select * from NetOwnerRequirementShifts('test.owner',0,100,null,null,'C',null);
expect RowID=TEST,Position=test;
select * from NetOwnerRequirementShifts('test.client',0,100,null,null,'Z',null);
expect EOF;
*/
}