====== 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
}