database:procedures:pears_netclientvacanciespaging



pears.NetClientVacanciesPaging

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE PROCEDURE "pears"."NetClientVacanciesPaging"( IN "pWebUserID" CHAR(20),IN "pStartDate" CHAR(20) DEFAULT NULL,IN "pEndDate" CHAR(20) DEFAULT NULL,IN "pPageNo" INTEGER DEFAULT 1,IN "SearchString" CHAR(100) DEFAULT NULL,IN "SampleSize" INTEGER DEFAULT 25 ) 
RESULT( "RowID" CHAR(30),"StartDate" DATE,"StartTime" TIME,"FinishTime" TIME,"FinishDate" DATE,"IsShift" SMALLINT,"VacancyID" CHAR(20),"TempShiftPlanID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Status" CHAR(40),"Department" CHAR(30),"Consultant" CHAR(60),"OurRef" CHAR(20),"TheirRef" CHAR(50),"ContractRef" CHAR(20),"CascadeDateTime" TIMESTAMP,"CascadeLevel" SMALLINT,"WhenEntered" TIMESTAMP,"FilledBy" CHAR(250),"Filled" SMALLINT,"Unconfirmed" SMALLINT,"LongDescription" long VARCHAR,"CompanyAddress" CHAR(250),"OutStartDate" CHAR(20),"OutEndDate" CHAR(20),"RowCount" INTEGER ) 
-- 20130204 based on NetClientVacancies, modified for paging
BEGIN
  DECLARE @DateRangeStart DATE;
  DECLARE @DateRangeEnd DATE;
  DECLARE @companycount INTEGER;
  DECLARE "pStart" DATE;
  DECLARE "pEnd" DATE;
  DECLARE "RangeStart" INTEGER;
  DECLARE "RangeEnd" INTEGER;
  DECLARE "SearchStr" CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NOT NULL,
    "CompanyStartDate" DATE NULL,
    ) NOT transactional;
  DECLARE LOCAL TEMPORARY TABLE "DSet"(
    "RowID" CHAR(30) NULL,
    "StartDate" DATE NULL,
    "StartTime" TIME NULL,
    "FinishTime" TIME NULL,
    "FinishDate" DATE NULL,
    "IsShift" SMALLINT NULL,
    "VacancyID" CHAR(20) NULL,
    "TempShiftPlanID" CHAR(20) NULL,
    "Position" CHAR(50) NULL,
    "ShiftDescription" CHAR(50) NULL,
    "CompanyName" CHAR(60) NULL,
    "ContactName" CHAR(30) NULL,
    "Status" CHAR(40) NULL,
    "Department" CHAR(30) NULL,
    "Consultant" CHAR(60) NULL,
    "OurRef" CHAR(20) NULL,
    "TheirRef" CHAR(50) NULL,
    "ContractRef" CHAR(20) NULL,
    "CascadeDateTime" TIMESTAMP NULL,
    "CascadeLevel" SMALLINT NULL,
    "WhenEntered" TIMESTAMP NULL,
    "FilledBy" CHAR(250) NULL,
    "Filled" SMALLINT NULL,
    "Unconfirmed" SMALLINT NULL,
    "LongDescription" long VARCHAR NULL,
    "CompanyAddress" CHAR(250) NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID","CompanyStartDate" ) 
    SELECT DISTINCT "companyid","IQXNetCompanyStartDate"("companyid") FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
  SET "pStart" = "iqxnetstringtodate"("pStartDate");
  SET "pEnd" = "iqxnetstringtodate"("pEndDate");
  IF "SampleSize" = 1 THEN
    SET @DateRangeStart = "dateadd"("year",-10,CURRENT DATE);
    SET "SearchString" = ''
  ELSE
    SET @DateRangeStart = "isnull"("pStart","dateadd"("month",-3,CURRENT DATE))
  END IF;
  SET @DateRangeEnd = "isnull"("pEnd","dateadd"("year",1,CURRENT DATE));
  SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID");
  SET "RangeStart" = (("pPageNo"-1)*"SampleSize");
  IF "RangeStart" > 1 THEN SET "RangeStart" = "RangeStart"+1
  END IF;
  SET "RangeEnd" = ("pPageNo")*"SampleSize";
  SET "SearchStr" = "trim"("replace"("SearchString",' ','%'));
  INSERT INTO "DSet" SELECT 'V'+"v"."VacancyID" AS "RowID",
      "v"."StartDate",
      CAST(NULL AS TIME) AS "StartTime",
      CAST(NULL AS TIME) AS "FinishTime",
      "v"."FinishDate",
      0 AS "IsShift",
      "v"."VacancyID",
      CAST(NULL AS CHAR(20)) AS "TempShiftPlanID",
      "v"."Position" AS "Position",
      CAST(NULL AS CHAR(50)) AS "ShiftDescription",
      "Company"."Name" AS "CompanyName",
      "Person"."Name" AS "ContactName",
      (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status",
      (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"."TheirRef",
      "v"."ContractRef",
      "v"."CascadeDateTime",
      "v"."CascadeLevel",
      "v"."WhenEntered",
      (SELECT "list"("person"."name") FROM "vacancy" KEY JOIN "placement" KEY JOIN "employment" KEY JOIN "person" WHERE "vacancy"."vacancyid" = "v"."vacancyid") AS "FilledBy",
      "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled",
      (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed",
      "v"."OtherNotes" AS "LongDescription",
      (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid")
      ELSE ''
      endif) AS "CompanyAddress"
      FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
        ,"Company" JOIN "IDs" ON "Company"."CompanyID" = "IDs"."ID"
      WHERE "v"."StartDate" <= @DateRangeEnd AND "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart AND "v"."temp" = 1
      AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
      AND "v"."StartDate" >= "IDs"."CompanyStartDate"
      AND "v"."status" = 'C' UNION ALL
    SELECT 'P'+"p"."TempShiftPlanID" AS "RowID",
      "isnull"("ts"."shiftdate","p"."ShiftDate") AS "StartDate",
      "isnull"("ts"."timefrom","p"."TimeFrom") AS "StartTime",
      "isnull"("ts"."timeto","p"."TimeTo") AS "FinishTime",
      (IF "StartTime" < "FinishTime" THEN "StartDate" ELSE "StartDate"+1 endif) AS "FinishDate",
      1 AS "IsShift",
      "v"."VacancyID",
      "p"."TempShiftPlanID",
      "v"."Position",
      "p"."Description" AS "ShiftDescription",
      "Company"."Name" AS "CompanyName",
      "Pe"."Name" AS "ContactName",
      (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status",
      (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department",
      (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant",
      CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "OurRef",
      "p"."ReferenceCode" AS "TheirRef",
      "v"."ContractRef",
      "p"."CascadeDateTime",
      "p"."CascadeLevel",
      "p"."WhenEntered",
      (SELECT "name" FROM "person" WHERE "personid" = "ts"."personid") AS "FilledBy",
      "ifnull"("FilledBy",0,1) AS "Filled",
      "isnull"((SELECT FIRST 1 FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" = '~'),0) AS "UnConfirmed",
      CAST(NULL AS long VARCHAR) AS "LongDescription",
      (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid")
      ELSE ''
      endif) AS "CompanyAddress"
      FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person" AS "pe") JOIN "IDs" ON "Company"."CompanyID" = "IDs"."ID"
        ,"TempShiftPlan" AS "p" LEFT OUTER JOIN "tempshift" AS "ts" ON "ts"."tempshiftplanid" = "p"."tempshiftplanid" AND("ts"."state" <> 'C' OR "ts"."cancelreason" = '~')
      WHERE "p"."ShiftDate" BETWEEN @DateRangeStart AND @DateRangeEnd
      AND "StartDate" >= "IDs"."CompanyStartDate"
      AND NOT EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "isnull"("crefill",0) = 0 AND "isnull"("cancelreason",'') <> '~')
      ORDER BY 2 ASC,3 ASC;
  WITH
  "RowData" AS(SELECT "row_number"() OVER(ORDER BY "theirRef" DESC) AS "RowNumber",
    "RowID","StartDate","StartTime","FinishTime","FinishDate","IsShift","VacancyID","TempShiftPlanID","Position","ShiftDescription","CompanyName","ContactName","Status","Department","Consultant","OurRef","TheirRef","ContractRef","CascadeDateTime","CascadeLevel","WhenEntered","FilledBy","Filled","Unconfirmed","LongDescription","CompanyAddress" FROM "DSet"
    WHERE("position" LIKE '%'+"SearchStr"+'%' OR "CompanyName" LIKE '%'+"SearchStr"+'%' OR "Department" LIKE '%'+"SearchStr"+'%' OR "FilledBy" LIKE '%'+"SearchStr"+'%' OR "Consultant" LIKE '%'+"SearchStr"+'%' OR "OurRef" LIKE '%'+"SearchStr"+'%' OR "TheirRef" LIKE '%'+"SearchStr"+'%')
    ORDER BY "RowNumber" DESC)
  -- output full dataset without RowNumber filtered on RowNumber
  SELECT "RowID","StartDate","StartTime","FinishTime","FinishDate","IsShift","VacancyID","TempShiftPlanID","Position","ShiftDescription","CompanyName","ContactName","Status","Department","Consultant","OurRef","TheirRef","ContractRef","CascadeDateTime","CascadeLevel","WhenEntered","FilledBy","Filled","Unconfirmed","LongDescription","CompanyAddress","pStartDate","pEndDate",@@ROWCOUNT
    FROM "RowData"
    WHERE("RowNumber" BETWEEN "RangeStart" AND "RangeEnd")
    ORDER BY "RowNumber" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacanciesPaging" IS 
{CREATE PROCEDURE pears."NetClientVacanciesPaging"( IN "pWebUserID" CHAR(20),IN "pStartDate" CHAR(20) DEFAULT NULL,IN "pEndDate" CHAR(20) DEFAULT NULL,IN "pPageNo" INTEGER DEFAULT 1,IN "SearchString" CHAR(100) DEFAULT NULL,IN "SampleSize" INTEGER DEFAULT 25 ) 
RESULT( "RowID" CHAR(30),"StartDate" DATE,"StartTime" TIME,"FinishTime" TIME,"FinishDate" DATE,"IsShift" SMALLINT,"VacancyID" CHAR(20),"TempShiftPlanID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Status" CHAR(40),"Department" CHAR(30),"Consultant" CHAR(60),"OurRef" CHAR(20),"TheirRef" CHAR(50),"ContractRef" CHAR(20),"CascadeDateTime" TIMESTAMP,"CascadeLevel" SMALLINT,"WhenEntered" TIMESTAMP,"FilledBy" CHAR(250),"Filled" SMALLINT,"Unconfirmed" SMALLINT,"LongDescription" long VARCHAR,"CompanyAddress" CHAR(250),"OutStartDate" CHAR(20),"OutEndDate" CHAR(20),"RowCount" INTEGER ) 
-- 20130204 based on NetClientVacancies, modified for paging
BEGIN
  DECLARE @DateRangeStart DATE;
  DECLARE @DateRangeEnd DATE;
  DECLARE @companycount INTEGER;
  DECLARE "pStart" DATE;
  DECLARE "pEnd" DATE;
  DECLARE "RangeStart" INTEGER;
  DECLARE "RangeEnd" INTEGER;
  DECLARE "SearchStr" CHAR(100);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NOT NULL,
    "CompanyStartDate" DATE NULL,
    ) NOT transactional;
  DECLARE LOCAL TEMPORARY TABLE "DSet"(
    "RowID" CHAR(30) NULL,
    "StartDate" DATE NULL,
    "StartTime" TIME NULL,
    "FinishTime" TIME NULL,
    "FinishDate" DATE NULL,
    "IsShift" SMALLINT NULL,
    "VacancyID" CHAR(20) NULL,
    "TempShiftPlanID" CHAR(20) NULL,
    "Position" CHAR(50) NULL,
    "ShiftDescription" CHAR(50) NULL,
    "CompanyName" CHAR(60) NULL,
    "ContactName" CHAR(30) NULL,
    "Status" CHAR(40) NULL,
    "Department" CHAR(30) NULL,
    "Consultant" CHAR(60) NULL,
    "OurRef" CHAR(20) NULL,
    "TheirRef" CHAR(50) NULL,
    "ContractRef" CHAR(20) NULL,
    "CascadeDateTime" TIMESTAMP NULL,
    "CascadeLevel" SMALLINT NULL,
    "WhenEntered" TIMESTAMP NULL,
    "FilledBy" CHAR(250) NULL,
    "Filled" SMALLINT NULL,
    "Unconfirmed" SMALLINT NULL,
    "LongDescription" long VARCHAR NULL,
    "CompanyAddress" CHAR(250) NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID","CompanyStartDate" ) 
    SELECT DISTINCT "companyid","IQXNetCompanyStartDate"("companyid") FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
  SET "pStart" = "iqxnetstringtodate"("pStartDate");
  SET "pEnd" = "iqxnetstringtodate"("pEndDate");
  IF "SampleSize" = 1 THEN
    SET @DateRangeStart = "dateadd"("year",-10,CURRENT DATE);
    SET "SearchString" = ''
  ELSE
    SET @DateRangeStart = "isnull"("pStart","dateadd"("month",-3,CURRENT DATE))
  END IF;
  SET @DateRangeEnd = "isnull"("pEnd","dateadd"("year",1,CURRENT DATE));
  SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID");
  SET "RangeStart" = (("pPageNo"-1)*"SampleSize");
  IF "RangeStart" > 1 THEN SET "RangeStart" = "RangeStart"+1
  END IF;
  SET "RangeEnd" = ("pPageNo")*"SampleSize";
  SET "SearchStr" = "trim"("replace"("SearchString",' ','%'));
  INSERT INTO "DSet" SELECT 'V'+"v"."VacancyID" AS "RowID",
      "v"."StartDate",
      CAST(NULL AS TIME) AS "StartTime",
      CAST(NULL AS TIME) AS "FinishTime",
      "v"."FinishDate",
      0 AS "IsShift",
      "v"."VacancyID",
      CAST(NULL AS CHAR(20)) AS "TempShiftPlanID",
      "v"."Position" AS "Position",
      CAST(NULL AS CHAR(50)) AS "ShiftDescription",
      "Company"."Name" AS "CompanyName",
      "Person"."Name" AS "ContactName",
      (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status",
      (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"."TheirRef",
      "v"."ContractRef",
      "v"."CascadeDateTime",
      "v"."CascadeLevel",
      "v"."WhenEntered",
      (SELECT "list"("person"."name") FROM "vacancy" KEY JOIN "placement" KEY JOIN "employment" KEY JOIN "person" WHERE "vacancy"."vacancyid" = "v"."vacancyid") AS "FilledBy",
      "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled",
      (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed",
      "v"."OtherNotes" AS "LongDescription",
      (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid")
      ELSE ''
      endif) AS "CompanyAddress"
      FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
        ,"Company" JOIN "IDs" ON "Company"."CompanyID" = "IDs"."ID"
      WHERE "v"."StartDate" <= @DateRangeEnd AND "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart AND "v"."temp" = 1
      AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
      AND "v"."StartDate" >= "IDs"."CompanyStartDate"
      AND "v"."status" = 'C' UNION ALL
    SELECT 'P'+"p"."TempShiftPlanID" AS "RowID",
      "isnull"("ts"."shiftdate","p"."ShiftDate") AS "StartDate",
      "isnull"("ts"."timefrom","p"."TimeFrom") AS "StartTime",
      "isnull"("ts"."timeto","p"."TimeTo") AS "FinishTime",
      (IF "StartTime" < "FinishTime" THEN "StartDate" ELSE "StartDate"+1 endif) AS "FinishDate",
      1 AS "IsShift",
      "v"."VacancyID",
      "p"."TempShiftPlanID",
      "v"."Position",
      "p"."Description" AS "ShiftDescription",
      "Company"."Name" AS "CompanyName",
      "Pe"."Name" AS "ContactName",
      (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status",
      (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department",
      (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant",
      CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "OurRef",
      "p"."ReferenceCode" AS "TheirRef",
      "v"."ContractRef",
      "p"."CascadeDateTime",
      "p"."CascadeLevel",
      "p"."WhenEntered",
      (SELECT "name" FROM "person" WHERE "personid" = "ts"."personid") AS "FilledBy",
      "ifnull"("FilledBy",0,1) AS "Filled",
      "isnull"((SELECT FIRST 1 FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" = '~'),0) AS "UnConfirmed",
      CAST(NULL AS long VARCHAR) AS "LongDescription",
      (IF @companycount > 1 THEN "GetCompanyAddressOnLine"("company"."companyid")
      ELSE ''
      endif) AS "CompanyAddress"
      FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person" AS "pe") JOIN "IDs" ON "Company"."CompanyID" = "IDs"."ID"
        ,"TempShiftPlan" AS "p" LEFT OUTER JOIN "tempshift" AS "ts" ON "ts"."tempshiftplanid" = "p"."tempshiftplanid" AND("ts"."state" <> 'C' OR "ts"."cancelreason" = '~')
      WHERE "p"."ShiftDate" BETWEEN @DateRangeStart AND @DateRangeEnd
      AND "StartDate" >= "IDs"."CompanyStartDate"
      AND NOT EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "isnull"("crefill",0) = 0 AND "isnull"("cancelreason",'') <> '~')
      ORDER BY 2 ASC,3 ASC;
  WITH
  "RowData" AS(SELECT "row_number"() OVER(ORDER BY "theirRef" DESC) AS "RowNumber",
    "RowID","StartDate","StartTime","FinishTime","FinishDate","IsShift","VacancyID","TempShiftPlanID","Position","ShiftDescription","CompanyName","ContactName","Status","Department","Consultant","OurRef","TheirRef","ContractRef","CascadeDateTime","CascadeLevel","WhenEntered","FilledBy","Filled","Unconfirmed","LongDescription","CompanyAddress" FROM "DSet"
    WHERE("position" LIKE '%'+"SearchStr"+'%' OR "CompanyName" LIKE '%'+"SearchStr"+'%' OR "Department" LIKE '%'+"SearchStr"+'%' OR "FilledBy" LIKE '%'+"SearchStr"+'%' OR "Consultant" LIKE '%'+"SearchStr"+'%' OR "OurRef" LIKE '%'+"SearchStr"+'%' OR "TheirRef" LIKE '%'+"SearchStr"+'%')
    ORDER BY "RowNumber" DESC)
  -- output full dataset without RowNumber filtered on RowNumber
  SELECT "RowID","StartDate","StartTime","FinishTime","FinishDate","IsShift","VacancyID","TempShiftPlanID","Position","ShiftDescription","CompanyName","ContactName","Status","Department","Consultant","OurRef","TheirRef","ContractRef","CascadeDateTime","CascadeLevel","WhenEntered","FilledBy","Filled","Unconfirmed","LongDescription","CompanyAddress","pStartDate","pEndDate",@@ROWCOUNT
    FROM "RowData"
    WHERE("RowNumber" BETWEEN "RangeStart" AND "RangeEnd")
    ORDER BY "RowNumber" ASC
END
}
  • database/procedures/pears_netclientvacanciespaging.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1