====== pears.NetOwnerCandidateDiary ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
create procedure "pears"."NetOwnerCandidateDiary"( in "pWebUserID" char(20),in "pPersonID" char(20),in "pStartDate" char(10) default null,in "pEndDate" char(10) default null )
result( "DiaryID" char(50),"DiaryClass" char(50),"ActionType" char(50),"Description" char(100),"DiaryStatus" char(50),"DateFrom" date,"DateTo" date,"TimeFrom" time,"TimeTo" time,"Address" char(250),"ContactName" char(100),"ContactNumber" char(100),"MapQuery" char(100),"FilterColumn" tinyint )
// IQXWeb
begin
declare "SampleStartDate" date;
declare "SampleEndDate" date;
set "SampleStartDate" = "isnull"("pStartDate","dateadd"("month",-1,"today"()));
set "SampleEndDate" = "isnull"("pEndDate","dateadd"("month",1,"today"()));
if not exists(select * from "iqxnetuser" where "iqxnetuserid" = "pWebUserID" and "IQXNetUserClassID" = 'OWNER') then
return
end if;
if not exists(select * from "person" where "personid" = "pPersonID") then
return
end if;
select "string"('Shift_',"s"."tempshiftid") as "DiaryID",
(case "s"."state" when 'H' then 'Holiday' when 'U' then 'Unavailable' when 'A' then 'Available' when 'C' then 'Cancelled' else 'Working' end) as "DiaryClass",
(case "s"."state" when 'H' then 'deleteable_shift' when 'U' then 'deleteable_shift' when 'A' then 'deleteable_shift' when 'P' then(if "isnull"("s"."tempconfirmed",0) = 0 then 'confirmable_shift' endif) when 'C' then 'cancelled_shift' end) as "ActionType",
"isnull"("c"."name","tu"."name") as "Description",
(if "s"."state" = 'P' then 'Provisional' else '' endif) as "DiaryStatus",
"s"."shiftdate" as "DateFrom",
"isnull"((if "s"."timeto" <= "s"."timefrom" then "s"."shiftdate"+1 else "s"."shiftdate" endif),"s"."shiftdate") as "DateTo",
"s"."TimeFrom",
"s"."TimeTo",
"string"(
"isnull"(
"string"("v"."sitename",(if "v"."sitename" is not null then ', ' endif),(if "v"."addr1" is not null then "v"."addr1" endif),(if "v"."addr2" is not null then ', '+"v"."addr2" endif),(if "v"."addr3" is not null then ', '+"v"."addr3" endif),(if "v"."town" is not null then ', '+"v"."town" endif),(if "v"."postcode" is not null then ', '+"v"."postcode" endif)),
"getcompanyaddressonline"("c"."companyid"))) as "Address",
"isnull"("v"."sitecontact","p"."name") as "ContactName",
"isnull"("v"."sitephonenumbers","getphone"('C','Telephone',"c"."companyid")) as "ContactPhone",
if "trim"("isnull"("v"."postcode","c"."postcode",'')) = '' then '' else "urlsafe"("string"("isnull"("v"."postcode","c"."postcode"),'(',"isnull"("v"."addr1","c"."addr1"),')')) endif as "MapQuery",
(if "s"."state" = 'C' and "isnull"("s"."tempconfirmed",0) = 1 then 0 else 1 endif) as "FilterColumn"
from "tempshift" as "s"
key left outer join("tempshiftunavailablereason" as "tu",("vacancy" as "v"
key join "employment"
key join "company" as "c"
,"employment"
key join "person" as "p"))
,"tempshift" as "s"
key join "person"
where "person"."personid" = "pPersonID"
and "s"."shiftdate" between "SampleStartDate" and "SampleEndDate"
and "FilterColumn" = 1 union all
select "string"('Employment_',"e"."employmentid") as "DiaryID",
'Working' as "DiaryClass",'' as "ActionType",
"string"("c"."name",' - ',"isnull"("e"."position","v"."position")) as "Description",
'' as "DiaryStatus",
"e"."startdate" as "DateFrom",
"isnull"("e"."leavedate","dateadd"("year",1,current date)) as "DateTo",
"pl"."workstarttime" as "TimeFrom",
"dateadd"("mi",("pl"."WorkNormalHours"*60),"pl"."workstarttime") as "TimeTo",
"string"(
"isnull"(
"string"("v"."sitename",(if "v"."sitename" is not null then ', ' endif),(if "v"."addr1" is not null then "v"."addr1" endif),(if "v"."addr2" is not null then ', '+"v"."addr2" endif),(if "v"."addr3" is not null then ', '+"v"."addr3" endif),(if "v"."town" is not null then ', '+"v"."town" endif),(if "v"."postcode" is not null then ', '+"v"."postcode" endif)),
"getcompanyaddressonline"("c"."companyid"))) as "Address",
"isnull"("v"."sitecontact","p"."name") as "ContactName",
"isnull"("v"."sitephonenumbers","getphone"('C','Telephone',"c"."companyid")) as "ContactPhone",
if "trim"("isnull"("v"."postcode","c"."postcode",'')) = '' then '' else "urlsafe"("string"("isnull"("v"."postcode","c"."postcode"),'(',"isnull"("v"."addr1","c"."addr1"),')')) endif as "MapQuery",
(if "DateTo" >= "SampleStartDate" or "e"."startdate" <= "SampleEndDate" then 1 else 0 endif) as "FilterColumn"
from "employment" as "e"
key join "person" as "pe"
,"employment" as "e"
key join "placement" as "pl"
key join "vacancy" as "v"
key join "employment" as "em"
key join "company" as "c"
,"employment" as "em"
key join "person" as "p"
where "pe"."personid" = "pPersonID"
and "filtercolumn" = 1
end /* DOC
2018-02-12 PC allow consultants to view candidate diary
2018-10-09 PC IW-682 alter method of finding timefrom and timeto
2018-10-23 PC IW-693 Consultant portal. Able to see candidates diary when logged in as a consultant
2018-10-26 PC IW-693 optimised
2018-11-23 PC removed tempshiftplan
*/
/* TEST
update employment set startdate=current date,leavedate=current date where employmentid='test';
select * from NetOwnerCandidateDiary('test.owner','test',null,null);
expect DiaryID=Shift_TEST,DiaryClass=Working,TimeFrom=14:00*,TimeTo=15:00*;
expect DiaryID=Employment_TEST,DiaryClass=Working,TimeFrom=08:00*,TimeTo=13:00*;
select * from NetOwnerCandidateDiary('test.owner','test','2011-01-01','2011-01-01');
expect DiaryID=Employment_TEST,DiaryClass=Working,TimeFrom=08:00*,TimeTo=13:00*;
update employment set startdate=null,leavedate=null where employmentid='test';
*/
go
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerCandidateDiary" IS
{create PROCEDURE pears."NetOwnerCandidateDiary"(in pWebUserID char(20),in pPersonID char(20),in pStartDate char(10) default null,in pEndDate char(10) default null)
result(DiaryID char(50),DiaryClass char(50),ActionType char(50),Description char(100),DiaryStatus char(50),DateFrom date,DateTo date,TimeFrom time,TimeTo time, Address char (250), ContactName char(100), ContactNumber char(100), MapQuery char(100),FilterColumn tinyint)
// IQXWeb
BEGIN
declare SampleStartDate date;
declare SampleEndDate date;
set SampleStartDate=isnull(pStartDate,dateadd(month,-1,today(*)));
set SampleEndDate=isnull(pEndDate,dateadd(month,1,today(*)));
if not exists (select * from iqxnetuser where iqxnetuserid=pWebUserID and IQXNetUserClassID='OWNER') then
return;
end if;
if not exists (select * from person where personid=pPersonID) then
return;
end if;
SELECT
string('Shift_',s.tempshiftid) as DiaryID,
(case s.state when 'H' then 'Holiday' when 'U' then 'Unavailable' when 'A' then 'Available' when 'C' then 'Cancelled' else 'Working' end) as DiaryClass,
(case s.state when 'H' then 'deleteable_shift' when 'U' then 'deleteable_shift' when 'A' then 'deleteable_shift' when 'P' then (if isnull(s.tempconfirmed,0)=0 then 'confirmable_shift' endif) when 'C' then 'cancelled_shift' end) as ActionType,
isnull(c.name,tu.name) as Description,
(if s.state = 'P' then 'Provisional' else '' endif) as DiaryStatus,
s.shiftdate as DateFrom,
isnull((if s.timeto <= s.timefrom then s.shiftdate+1 else s.shiftdate endif),s.shiftdate) as DateTo,
s.TimeFrom,
s.TimeTo,
string(isnull(
string(v.sitename,(if v.sitename is not null then ', ' endif),(if v.addr1 is not null then v.addr1 endif),(if v.addr2 is not null then ', '+v.addr2 endif),(if v.addr3 is not null then ', '+v.addr3 endif),(if v.town is not null then ', '+v.town endif),(if v.postcode is not null then ', '+v.postcode endif))
,getcompanyaddressonline(c.companyid))) as Address,
isnull(v.sitecontact,p.name) as ContactName,
isnull(v.sitephonenumbers,getphone('C','Telephone',c.companyid)) as ContactPhone,
if trim(isnull(v.postcode,c.postcode,''))='' then '' else urlsafe(string(isnull(v.postcode,c.postcode),'(',isnull(v.addr1,c.addr1),')')) endif as MapQuery,
(if s.state = 'C' and isnull(s.tempconfirmed,0) = 1 then 0 else 1 endif) as FilterColumn
from
tempshift as s
key left outer join (tempshiftunavailablereason tu,(vacancy v
key join employment
key join company c,
employment
key join person p)),
tempshift as s
key join person
where
person.personid=pPersonID
and s.shiftdate between SampleStartDate and SampleEndDate
and FilterColumn=1
union all
select
string('Employment_',e.employmentid) as DiaryID,
'Working' as DiaryClass,'' as ActionType,
string(c.name,' - ',isnull(e.position,v.position)) as Description,
'' as DiaryStatus,
e.startdate as DateFrom,
isnull(e.leavedate,dateadd(year,1,current date)) as DateTo,
pl.workstarttime as TimeFrom,
dateadd(mi,(pl.WorkNormalHours*60),pl.workstarttime) as TimeTo,
string(isnull(
string(v.sitename,(if v.sitename is not null then ', ' endif),(if v.addr1 is not null then v.addr1 endif),(if v.addr2 is not null then ', '+v.addr2 endif),(if v.addr3 is not null then ', '+v.addr3 endif),(if v.town is not null then ', '+v.town endif),(if v.postcode is not null then ', '+v.postcode endif))
,getcompanyaddressonline(c.companyid))
) as Address,
isnull(v.sitecontact,p.name) as ContactName,
isnull(v.sitephonenumbers,getphone('C','Telephone',c.companyid)) as ContactPhone,
if trim(isnull(v.postcode,c.postcode,''))='' then '' else urlsafe(string(isnull(v.postcode,c.postcode),'(',isnull(v.addr1,c.addr1),')')) endif as MapQuery,
(if DateTo >= SampleStartDate or e.startdate <= SampleEndDate then 1 else 0 endif) as FilterColumn
from
employment e
key join person pe,
employment e
key join placement pl
key join vacancy v
key join employment em
key join company c,
employment em
key join person p
where
pe.personid=pPersonID
and filtercolumn=1
END
/* DOC
2018-02-12 PC allow consultants to view candidate diary
2018-10-09 PC IW-682 alter method of finding timefrom and timeto
2018-10-23 PC IW-693 Consultant portal. Able to see candidates diary when logged in as a consultant
2018-10-26 PC IW-693 optimised
2018-11-23 PC removed tempshiftplan
*/
/* TEST
update employment set startdate=current date,leavedate=current date where employmentid='test';
select * from NetOwnerCandidateDiary('test.owner','test',null,null);
expect DiaryID=Shift_TEST,DiaryClass=Working,TimeFrom=14:00*,TimeTo=15:00*;
expect DiaryID=Employment_TEST,DiaryClass=Working,TimeFrom=08:00*,TimeTo=13:00*;
select * from NetOwnerCandidateDiary('test.owner','test','2011-01-01','2011-01-01');
expect DiaryID=Employment_TEST,DiaryClass=Working,TimeFrom=08:00*,TimeTo=13:00*;
update employment set startdate=null,leavedate=null where employmentid='test';
*/
}