pears.NetClientShifts

CREATE PROCEDURE "pears"."NetClientShifts"( 
  IN "pWebUserID" CHAR(20),
  IN "pSlice" INTEGER DEFAULT 0,
  IN "pSliceSize" INTEGER DEFAULT 100,
  IN "pIncCancelled" SMALLINT DEFAULT 0,
  IN "pStartDate" CHAR(26) DEFAULT NULL,
  IN "pEndDate" CHAR(26) DEFAULT NULL,
  IN "SearchString" CHAR(100) DEFAULT NULL ) 
RESULT( 
  "TempShiftPlanID" CHAR(20),
  "StartDate" CHAR(20),
  "Position" CHAR(50),
  "Description" CHAR(50),
  "CompanyID" CHAR(20),
  "CompanyName" CHAR(60),
  "ContactName" CHAR(60),
  "Consultant" CHAR(60),
  "OurRef" CHAR(50),
  "ContractRef" CHAR(20),
  "ShiftRef" CHAR(20),
  "FilledBy" CHAR(250),
  "StartTime" CHAR(15),
  "FinishTime" CHAR(15),
  "FinishDate" CHAR(30),
  "LongDescription" long VARCHAR,
  "PersonID" CHAR(20),
  "TextStatus" CHAR(100),
  "CreatedBy" CHAR(255),
  "CancelState" CHAR(50),
  "ClientNote" CHAR(50),
  "VacancyNotes" long VARCHAR,
  "TextStatusFull" CHAR(100),
  "SiteDetails" CHAR(300) ) 
BEGIN
  DECLARE "DateRangeStart" DATE = "isnull"("IQXNetStringToDate"("pStartDate"),"dateadd"("week",-1,CURRENT DATE));
  DECLARE "DateRangeEnd" DATE = "isnull"("IQXNetStringToDate"("pEndDate"),"dateadd"("year",1,CURRENT DATE));
  DECLARE "SendVNotesToWeb" CHAR(10) = "isnull"("WPKMaintainGetSwitchValue"('SENDVNOTESTOWEB','WEBSITE','l'),'');
  DECLARE "SendVOtherNotesToWeb" CHAR(10) = "isnull"("WPKMaintainGetSwitchValue"('SENDVOTHERNOTESTOWEB','WEBSITE','l'),'');
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NOT NULL,
    "CompanyStartDate" DATE NULL,
    ) NOT transactional;
  IF "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 THEN
    RETURN
  END IF;
  INSERT INTO "IDs"( "ID","CompanyStartDate" ) 
    SELECT DISTINCT "companyid","IQXNetCompanyStartDate"("companyid") FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
  SELECT top "pSliceSize" START at("pSliceSize"*"pSlice"+1)
    "p"."TempShiftPlanID",
    "v"."Position",
    "p"."Description",
    "Company"."CompanyID",
    "Company"."Name" AS "CompanyName",
    "Pe"."Name" AS "ContactName",
    "staff"."name" AS "Consultant",
    "isnull"("s"."referencecode","p"."referencecode") AS "OurRef",
    "v"."ContractRef",
    CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "ShiftRef",
    "string"("pt"."name",(IF "pt"."status" = 'A' THEN ' (S)' endif)) AS "FilledBy",
    "dateformat"("isnull"("s"."timefrom","p"."TimeFrom"),'hh:mm') AS "StartTime",
    "dateformat"("isnull"("s"."timeto","p"."TimeTo"),'hh:mm') AS "FinishTime",
    "isnull"("s"."shiftdate","p"."ShiftDate","v"."startdate") AS "StartDate",
    (IF "FinishTime" < "StartTime" THEN "dateadd"("dd",1,"StartDate") ELSE "StartDate" endif) AS "FinishDate",
    (IF "SendVOtherNotesToWeb" <> 'Y' THEN ''
    ELSE IF "isnull"("d"."showvacextranotes",0) = 1 THEN CAST("v"."extranotes" AS long VARCHAR)
      ELSE CAST("v"."othernotes" AS long VARCHAR)
      endif
    endif) AS "LongDescription",
    "s"."personid" AS "PersonID",
    "NetTempShiftStatusString"("s"."state") AS "TextStatus",
    ('Ordered by: '+"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm')) AS "CreatedBy",
    "string"("dateformat"("s"."WhenCancelled",'dd/mm/yyyy hh:nn:ss'),' by ',"isnull"((SELECT "name" FROM "staff" WHERE "staff"."staffid" = "s"."WhoCancelled"),'(not recorded)')) AS "CancelState",
    "p"."ClientNote",
    (IF "SendVNotesToWeb" = 'Y' THEN "v"."notes" ELSE NULL endif) AS "VacancyNotes",
    "NetTempShiftStatusString"("s"."state","s"."tempconfirmed","s"."clientconfirmed","cr"."name") AS "TextStatusFull",
    "isnull"(
    "string"(
    "v"."sitename",
    (IF "isnull"("v"."addr1",'') <> '' THEN
      (IF "v"."Sitename" <> '' THEN
        ', '
      endif)
      +"v"."addr1"
    endif),
    (IF "isnull"("v"."addr2",'') <> '' THEN
      ', '+"v"."addr2"
    endif),
    (IF "isnull"("v"."addr3",'') <> '' THEN
      ', '+"v"."addr3"
    endif),
    (IF "isnull"("v"."Town",'') <> '' THEN
      ', '+"v"."town"
    endif),
    (IF "isnull"("v"."postcode",'') <> '' THEN
      ', '+"v"."postcode"
    endif)),
    "GetCompanyAddressOnLine"("e"."companyid")) AS "SiteDetails"
    FROM "TempShiftplan" AS "p"
      KEY LEFT OUTER JOIN "tempshift" AS "s"
      ,"TempShiftplan" AS "p"
      KEY JOIN "vacancy" AS "v"
      KEY JOIN "tempdesk" AS "td"
      KEY JOIN "Employment" AS "e"
      KEY JOIN("Company","Person" AS "Pe")
      JOIN "IDs" ON "e"."CompanyID" = "IDs"."ID"
      ,"vacancy" AS "v"
      KEY JOIN "Department" AS "d"
      ,"vacancy" AS "v"
      KEY LEFT OUTER JOIN "Staff"
      ,"tempshift" AS "s"
      KEY LEFT OUTER JOIN "person" AS "pt"
      ,"tempshift" AS "s"
      KEY LEFT OUTER JOIN "tempshiftcancelreason" AS "cr"
    WHERE "StartDate" BETWEEN "DateRangeStart" AND "DateRangeEnd"
    AND "td"."desktype" = 'S'
    AND "StartDate" >= "IDs"."CompanyStartDate"
    AND("pIncCancelled" = 0 AND NOT("isnull"("s"."State",'') = 'C' AND "isnull"("s"."cRefill",0) = 0)
    OR "pIncCancelled" = 1)
    ORDER BY "StartDate" DESC,"p"."TempShiftPlanID" DESC
END /* DOC
2020-07-01 ET Created based on NetClientRequirmentList modified to only include shifts
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientShifts" IS 
{CREATE PROCEDURE pears."NetClientShifts"(
  IN pWebUserID CHAR(20),
  IN pSlice INTEGER DEFAULT 0,
  IN pSliceSize INTEGER DEFAULT 100,
  IN pIncCancelled SMALLINT DEFAULT 0,
  IN pStartDate CHAR(26) DEFAULT NULL,
  IN pEndDate CHAR(26) DEFAULT NULL,
  IN SearchString CHAR(100) DEFAULT NULL
)
RESULT(
  TempShiftPlanID CHAR(20),
  StartDate CHAR(20),
  POSITION CHAR(50),
  Description CHAR(50),
  CompanyID CHAR(20),
  CompanyName CHAR(60),
  ContactName CHAR(60),
  Consultant CHAR(60),
  OurRef CHAR(50),
  ContractRef CHAR(20),
  ShiftRef CHAR(20),
  FilledBy CHAR(250),
  StartTime CHAR(15),
  FinishTime CHAR(15),
  FinishDate CHAR(30),
  LongDescription long VARCHAR,
  PersonID CHAR(20),
  TextStatus CHAR(100),
  CreatedBy CHAR(255),
  CancelState CHAR(50),
  ClientNote CHAR(50),
  VacancyNotes long VARCHAR,
  TextStatusFull CHAR(100),
  SiteDetails CHAR(300)
)
 
 
BEGIN
  DECLARE DateRangeStart DATE = isnull(IQXNetStringToDate(pStartDate),dateadd(week,-1,CURRENT DATE));
  DECLARE DateRangeEnd DATE = isnull(IQXNetStringToDate(pEndDate),dateadd(YEAR,1,CURRENT DATE));
 
  DECLARE SendVNotesToWeb CHAR(10) = isnull(WPKMaintainGetSwitchValue('SENDVNOTESTOWEB','WEBSITE','l'),'');
  DECLARE SendVOtherNotesToWeb CHAR(10) = isnull(WPKMaintainGetSwitchValue('SENDVOTHERNOTESTOWEB','WEBSITE','l'),'');
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NOT NULL,
    CompanyStartDate DATE NULL,
    ) NOT TRANSACTIONAL;
  IF IQXNetHasPermission(pWebUserID,'ORDERJOBS') = 0 THEN
    RETURN
  END IF;
 
  INSERT INTO IDs( ID,CompanyStartDate ) 
    SELECT DISTINCT companyid,IQXNetCompanyStartDate(companyid) FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID;
 
  SELECT TOP pSliceSize START AT (pSliceSize*pSlice+1)
    p.TempShiftPlanID,
    v.Position,
    p.Description,
    Company.CompanyID,
    Company.Name AS CompanyName,
    Pe.Name AS ContactName,
    staff.name AS Consultant,
    isnull(s.referencecode,p.referencecode) AS OurRef,
    v.ContractRef,
    CAST(p.ShiftSerialNumber AS CHAR(20)) AS ShiftRef,
    string(pt.name,(IF pt.status = 'A' THEN ' (S)' END IF)) AS FilledBy,
    dateformat(isnull(s.timefrom,p.TimeFrom),'hh:mm') AS StartTime,
    dateformat(isnull(s.timeto,p.TimeTo),'hh:mm') AS FinishTime,
    isnull(s.shiftdate,p.ShiftDate,v.startdate) AS StartDate,
    (IF FinishTime < StartTime THEN dateadd(dd,1,StartDate) ELSE StartDate END IF) AS FinishDate,
    (IF SendVOtherNotesToWeb <> 'Y' THEN ''
      ELSE IF isnull(d.showvacextranotes,0) = 1 THEN CAST(v.extranotes AS long VARCHAR)
      ELSE CAST(v.othernotes AS long VARCHAR)
      END IF
    END IF) AS LongDescription,
    s.personid AS PersonID,
    NetTempShiftStatusString(s.state) AS TextStatus,
    ('Ordered by: '+p.orderedby+' at: '+dateformat(p.whenentered,'dd/mm/yyyy hh:mm')) AS CreatedBy,
    string(dateformat(s.WhenCancelled,'dd/mm/yyyy hh:nn:ss'),' by ',isnull((SELECT name FROM staff WHERE staff.staffid = s.WhoCancelled),'(not recorded)')) AS CancelState,
    p.ClientNote,
    (IF SendVNotesToWeb = 'Y' THEN v.notes ELSE NULL END IF) AS VacancyNotes,
    NetTempShiftStatusString(s.state,s.tempconfirmed,s.clientconfirmed,cr.name) AS TextStatusFull,
    isnull(
      string(
        v.sitename,
       (IF isnull(v.addr1,'') <> '' THEN
         (IF v.Sitename <> '' THEN
           ', '
         END IF)
         +v.addr1
        END IF),
        (IF isnull(v.addr2,'') <> '' THEN
          ', '+v.addr2
        END IF),
        (IF isnull(v.addr3,'') <> '' THEN
        ', '+v.addr3
        END IF),
        (IF isnull(v.Town,'') <> '' THEN
          ', '+v.town
        END IF),
        (IF isnull(v.postcode,'') <> '' THEN
          ', '+v.postcode
        END IF)
      ),
      GetCompanyAddressOnLine(e.companyid)
    ) AS SiteDetails
  FROM
    TempShiftplan AS p
      KEY LEFT OUTER JOIN tempshift AS s,
    TempShiftplan AS p
      KEY JOIN vacancy AS v
      KEY JOIN tempdesk AS td
      KEY JOIN Employment AS e
      KEY JOIN(Company,Person AS Pe)
      JOIN IDs ON e.CompanyID = IDs.ID,
    vacancy AS v
      KEY JOIN Department AS d,
    vacancy AS v
      KEY LEFT OUTER JOIN Staff,
    tempshift AS s
      KEY LEFT OUTER JOIN person AS pt,
    tempshift AS s
      KEY LEFT OUTER JOIN tempshiftcancelreason AS cr
  WHERE
    StartDate BETWEEN DateRangeStart AND DateRangeEnd
    AND td.desktype = 'S'
    AND StartDate >= IDs.CompanyStartDate
    AND (
      pIncCancelled = 0 AND NOT (isnull(s.State,'') = 'C' AND isnull(s.cRefill,0) = 0)
    OR
      pIncCancelled = 1 
    )
  ORDER BY StartDate DESC, p.TempShiftPlanID DESC
END
 
/* DOC
  2020-07-01 ET Created based on NetClientRequirmentList modified to only include shifts
*/
}