pears.NetOwnerRequirementShifts [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetOwnerRequirementShifts ======
<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"."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;
*/
}
</code>