====== pears.NetClientVacancies ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
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
}