pears.NetClientVacancies2

CREATE PROCEDURE "pears"."NetClientVacancies2"( IN "pWebUserID" CHAR(20),IN "pStartDate" DATE DEFAULT NULL,IN "pEndDate" DATE DEFAULT NULL ) 
RESULT( "RowID__H" CHAR(30),"Position" CHAR(200),"Start_Date__D" DATE,"Start_Time" TIME,"Finish_Date__D" DATE,"Finish_Time" TIME,"VacancyID__H" CHAR(20),"TempShiftPlanID__H" CHAR(20),"Our_Ref" CHAR(20),"Their_Ref" CHAR(50),"Contract_Ref" CHAR(20),"Filled_By" CHAR(250),"IsShift__H" SMALLINT,"Filled__H" SMALLINT,"Unconfirmed__H" SMALLINT,"GroupID__H" SMALLINT ) 
BEGIN
  DECLARE @DateRangeStart DATE;
  DECLARE @DateRangeEnd DATE;
  SET @DateRangeStart = "isnull"("pStartDate","dateadd"("week",-1,CURRENT DATE));
  SET @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,CURRENT DATE));
  SELECT 'V'+"v"."VacancyID" AS "RowID",
    "v"."Position" AS "Position",
    "v"."StartDate",
    CAST(NULL AS TIME) AS "StartTime",
    "v"."FinishDate",
    CAST(NULL AS TIME) AS "FinishTime",
    "v"."VacancyID",
    CAST(NULL AS CHAR(20)) AS "TempShiftPlanID",
    "v"."RefCode" AS "OurRef",
    "v"."TheirRef",
    "v"."ContractRef",
    (SELECT "list"("person"."name") FROM "vacancy" KEY JOIN "placement" KEY JOIN "employment" KEY JOIN "person" WHERE "vacancy"."vacancyid" = "v"."vacancyid") AS "FilledBy",
    0 AS "IsShift",
    "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled",
    (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed",
    0 AS "GroupID" FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "v"."StartDate" <= @DateRangeEnd AND "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart AND "v"."temp" = 1
    AND "v"."Status" IN( 'C','F',']' ) AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
    AND "v"."StartDate" >= "IQXNetCompanyStartDate"("Company"."companyid") UNION ALL
  SELECT 'P'+"p"."TempShiftPlanID" AS "RowID",
    "string"("v"."Position",' ',"p"."Description") AS "Position",
    "p"."ShiftDate" AS "StartDate",
    "p"."TimeFrom" AS "StartTime",
    (IF "p"."TimeFrom" < "p"."TimeTo" THEN "p"."ShiftDate" ELSE "p"."ShiftDate"+1 endif) AS "FinishDate",
    "p"."TimeTo" AS "FinishTime",
    "v"."VacancyID",
    "p"."TempShiftPlanID",
    CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "OurRef",
    "p"."ReferenceCode" AS "TheirRef",
    "v"."ContractRef",
    (SELECT FIRST "person"."name" FROM "person" KEY JOIN "tempshift" WHERE "tempshift"."tempshiftplanid" = "p"."tempshiftplanid" AND "tempshift"."state" <> 'C') AS "FilledBy",
    1 AS "IsShift",
    "ifnull"("FilledBy",0,1) AS "Filled",
    "isnull"((SELECT FIRST 1 FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" = '~'),0) AS "UnConfirmed",0 AS "GroupID"
    FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "p"."ShiftDate" BETWEEN @DateRangeStart AND @DateRangeEnd
    AND NOT EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" <> '~' AND "isnull"("crefill",0) = 0)
    AND "StartDate" >= "IQXNetCompanyStartDate"("Company"."companyid")
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientVacancies2" IS 
{CREATE PROCEDURE pears."NetClientVacancies2"( IN "pWebUserID" CHAR(20),IN "pStartDate" DATE DEFAULT NULL,IN "pEndDate" DATE DEFAULT NULL ) 
RESULT( "RowID__H" CHAR(30),"Position" CHAR(200),"Start_Date__D" DATE,"Start_Time" TIME,"Finish_Date__D" DATE,"Finish_Time" TIME,"VacancyID__H" CHAR(20),"TempShiftPlanID__H" CHAR(20),"Our_Ref" CHAR(20),"Their_Ref" CHAR(50),"Contract_Ref" CHAR(20),"Filled_By" CHAR(250),"IsShift__H" SMALLINT,"Filled__H" SMALLINT,"Unconfirmed__H" SMALLINT,"GroupID__H" SMALLINT ) 
BEGIN
  DECLARE @DateRangeStart DATE;
  DECLARE @DateRangeEnd DATE;
  SET @DateRangeStart = "isnull"("pStartDate","dateadd"("week",-1,CURRENT DATE));
  SET @DateRangeEnd = "isnull"("pEndDate","dateadd"("year",1,CURRENT DATE));
  SELECT 'V'+"v"."VacancyID" AS "RowID",
    "v"."Position" AS "Position",
    "v"."StartDate",
    CAST(NULL AS TIME) AS "StartTime",
    "v"."FinishDate",
    CAST(NULL AS TIME) AS "FinishTime",
    "v"."VacancyID",
    CAST(NULL AS CHAR(20)) AS "TempShiftPlanID",
    "v"."RefCode" AS "OurRef",
    "v"."TheirRef",
    "v"."ContractRef",
    (SELECT "list"("person"."name") FROM "vacancy" KEY JOIN "placement" KEY JOIN "employment" KEY JOIN "person" WHERE "vacancy"."vacancyid" = "v"."vacancyid") AS "FilledBy",
    0 AS "IsShift",
    "ifnull"("nullif"("FilledBy",''),0,1) AS "Filled",
    (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed",
    0 AS "GroupID" FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "v"."StartDate" <= @DateRangeEnd AND "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart AND "v"."temp" = 1
    AND "v"."Status" IN( 'C','F',']' ) AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
    AND "v"."StartDate" >= "IQXNetCompanyStartDate"("Company"."companyid") UNION ALL
  SELECT 'P'+"p"."TempShiftPlanID" AS "RowID",
    "string"("v"."Position",' ',"p"."Description") AS "Position",
    "p"."ShiftDate" AS "StartDate",
    "p"."TimeFrom" AS "StartTime",
    (IF "p"."TimeFrom" < "p"."TimeTo" THEN "p"."ShiftDate" ELSE "p"."ShiftDate"+1 endif) AS "FinishDate",
    "p"."TimeTo" AS "FinishTime",
    "v"."VacancyID",
    "p"."TempShiftPlanID",
    CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "OurRef",
    "p"."ReferenceCode" AS "TheirRef",
    "v"."ContractRef",
    (SELECT FIRST "person"."name" FROM "person" KEY JOIN "tempshift" WHERE "tempshift"."tempshiftplanid" = "p"."tempshiftplanid" AND "tempshift"."state" <> 'C') AS "FilledBy",
    1 AS "IsShift",
    "ifnull"("FilledBy",0,1) AS "Filled",
    "isnull"((SELECT FIRST 1 FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" = '~'),0) AS "UnConfirmed",0 AS "GroupID"
    FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person")
    WHERE "Company"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "p"."ShiftDate" BETWEEN @DateRangeStart AND @DateRangeEnd
    AND NOT EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" <> '~' AND "isnull"("crefill",0) = 0)
    AND "StartDate" >= "IQXNetCompanyStartDate"("Company"."companyid")
END
}