pears.NetOwnerRequirementContracts

CREATE PROCEDURE "pears"."NetOwnerRequirementContracts"( 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),"Position" CHAR(50),"CompanyID" CHAR(20),"CompanyName" CHAR(60),"ContactID" CHAR(20),"ContactName" CHAR(60),"DetailStatus" CHAR(30),"OurRef" CHAR(20),"ContractRef" CHAR(20),"ShiftRef" CHAR(20),"FinishDate" CHAR(30),"LongDescription" long VARCHAR,"CompanyAddress" CHAR(250),"TextStatus" CHAR(100),"PONumber" CHAR(50),"CreatedBy" CHAR(255),"DeskType" CHAR(20),"FilledBy" CHAR(100) ) 
BEGIN
  // IQXNet
  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"
    "v"."VacancyID" AS "RowID",
    "v"."startdate" AS "StartDate",
    "v"."Position" AS "Position",
    "Company"."CompanyID",
    "Company"."Name" AS "CompanyName",
    "Person"."PersonID",
    "Person"."Name" AS "ContactName",
    (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "DetailStatus",
    "v"."RefCode" AS "OurRef",
    "v"."ContractRef",
    "v"."refcode" AS "ShiftRef",
    "v"."finishdate" AS "FinishDate",
    "v"."OtherNotes" AS "LongDescription",
    "GetCompanyAddressOnLine"("e"."companyid") AS "CompanyAddress",
    (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "statusText",
    "v"."theirref" AS "PONumber",
    ('Consultant: '+(SELECT "name" FROM "staff" WHERE "staffid" = "v"."staffid")+' at: '+"dateformat"("v"."whenentered",'dd/mm/yyyy hh:mm:ss')) AS "CreatedBy",
    "isnull"("td"."desktype",'') AS "DeskType",
    (SELECT "list"("personname") FROM "NetOwnerRequirementShortListPlaced"("pWebUserID","v"."VacancyID",0)) AS "FilledBy"
    FROM "vacancy" AS "v"
      KEY JOIN "Employment" AS "e"
      KEY JOIN("Company","Person")
      ,"vacancy" AS "v"
      KEY JOIN "tempdesk" AS "td"
    WHERE("StartDate" <= "DateRangeEnd" AND("FinishDate" IS NULL OR "FinishDate" >= "DateRangeStart")
    AND "v"."status" NOT IN( 'D','[' ) 
    AND "v"."status" = "isnull"("pStatus","v"."status")
    AND "DeskType" <> 'S'
    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-11-28 IW-759 prioritise selection by tempdesk
2019-7-4 VHS/MHS fixed erroneous date criteria and removed dangerous tests
*/
/* TEST
select * from NetOwnerRequirementContracts('test.owner',0,100,null,null,null,'Z');
expect EOF;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementContracts" IS 
{CREATE PROCEDURE pears.NetOwnerRequirementContracts( 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),POSITION CHAR(50),CompanyID CHAR(20),CompanyName CHAR(60),ContactID CHAR(20),ContactName CHAR(60),DetailStatus CHAR(30),OurRef CHAR(20),ContractRef CHAR(20),ShiftRef CHAR(20),FinishDate CHAR(30),LongDescription long VARCHAR,CompanyAddress CHAR(250),TextStatus CHAR(100),PONumber CHAR(50),CreatedBy CHAR(255),DeskType CHAR(20),FilledBy CHAR(100) ) 
BEGIN
  // IQXNet
  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
    v.VacancyID AS RowID,
    v.startdate AS StartDate,
    v.Position AS POSITION,
    Company.CompanyID,
    Company.Name AS CompanyName,
    Person.PersonID,
    Person.Name AS ContactName,
    (SELECT Name FROM STATUS WHERE STATUS.Status = v.Status AND STATUS.Type = 'V') AS DetailStatus,
    v.RefCode AS OurRef,
    v.ContractRef,
    v.refcode AS ShiftRef,
    v.finishdate AS FinishDate,
    v.OtherNotes AS LongDescription,
    GetCompanyAddressOnLine(e.companyid) AS CompanyAddress,
    (SELECT Name FROM STATUS WHERE STATUS.Status = v.Status AND STATUS.Type = 'V') AS statusText,
    v.theirref AS PONumber,
    ('Consultant: '+(SELECT name FROM staff WHERE staffid = v.staffid)+' at: '+dateformat(v.whenentered,'dd/mm/yyyy hh:mm:ss')) AS CreatedBy,
    isnull(td.desktype,'') AS DeskType,
    (SELECT list(personname) FROM NetOwnerRequirementShortListPlaced(pWebUserID,v.VacancyID,0)) AS FilledBy
    FROM vacancy AS v
      KEY JOIN Employment AS e
      KEY JOIN(Company,Person)
      ,vacancy AS v
      KEY JOIN tempdesk AS td
    WHERE (StartDate <= DateRangeEnd AND ( FinishDate IS NULL OR FinishDate >= DateRangeStart)
    AND v.status NOT IN( 'D','[' ) 
    AND v.status = isnull(pStatus,v.status)
    AND DeskType <> 'S'
    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-11-28 IW-759 prioritise selection by tempdesk
2019-7-4 VHS/MHS fixed erroneous date criteria and removed dangerous tests
*/
/* TEST
select * from NetOwnerRequirementContracts('test.owner',0,100,null,null,null,'Z');
expect EOF;
*/
}