pears.NetClientVacancies

CREATE PROCEDURE "pears"."NetClientVacancies"( IN "pWebUserID" CHAR(20),IN "pStartDate" DATE DEFAULT NULL,IN "pEndDate" DATE DEFAULT NULL ) 
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) ) 
-- modified 20110815 to include shift date and time as well as plan date and time ref ASA
-- modified 20110823 to use temporary table for lookup to improve speed
BEGIN
  DECLARE @DateRangeStart DATE;
  DECLARE @DateRangeEnd DATE;
  DECLARE @companycount INTEGER;
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NOT NULL,
    "CompanyStartDate" DATE NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID","CompanyStartDate" ) 
    SELECT DISTINCT "companyid","IQXNetCompanyStartDate"("companyid") FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
  SET @DateRangeStart = "isnull"("pStartDate","dateadd"("month",-3,CURRENT DATE));
  SET @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,CURRENT DATE));
  SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID");
  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","tempshift" AS "t") KEY JOIN("Company","Person") 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" 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
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacancies" IS 
{CREATE PROCEDURE pears."NetClientVacancies"( IN "pWebUserID" CHAR(20),IN "pStartDate" DATE DEFAULT NULL,IN "pEndDate" DATE DEFAULT NULL ) 
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) ) 
-- modified 20110815 to include shift date and time as well as plan date and time ref ASA
-- modified 20110823 to use temporary table for lookup to improve speed
BEGIN
  DECLARE @DateRangeStart DATE;
  DECLARE @DateRangeEnd DATE;
  DECLARE @companycount INTEGER;
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NOT NULL,
    "CompanyStartDate" DATE NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID","CompanyStartDate" ) 
    SELECT DISTINCT "companyid","IQXNetCompanyStartDate"("companyid") FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
  SET @DateRangeStart = "isnull"("pStartDate","dateadd"("month",-3,CURRENT DATE));
  SET @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,CURRENT DATE));
  SET @companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID");
  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","tempshift" AS "t") KEY JOIN("Company","Person") 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" 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
END
}