====== pears.NetCandidateDiary ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetCandidateDiary"( in "pWebUserID" char(20) ) 
result( "DiaryID" char(50),"DiaryClass" char(50),"ActionType" char(50),"Description" char(100),"DiaryStatus" char(50),"DateFrom" date,"ShiftType" char(60),"DateTo" date,"TimeFrom" time,"TimeTo" time,"Address" char(250),"ContactName" char(100),"ContactNumber" char(100),"MapQuery" char(100) ) 
// IQXWeb
begin
  declare "pstart" date;
  set "pstart" = current date-365;
  while "dow"("pstart") <> 2 loop
    set "pstart" = "pstart"-1
  end loop;
  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","tempshiftunavailablereason"."name") as "Description",
    (if "s"."state" = 'P' then 'Provisional'
    else ''
    endif) as "DiaryStatus",
    "s"."shiftdate" as "DateFrom",
    "isnull"("ty"."Name","ur"."name") as "ShiftType",
    "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"("vacancy"."sitename",(if "vacancy"."sitename" is not null then ', ' endif),(if "vacancy"."addr1" is not null then "vacancy"."addr1" endif),(if "vacancy"."addr2" is not null then ', '+"vacancy"."addr2" endif),(if "vacancy"."addr3" is not null then ', '+"vacancy"."addr3" endif),(if "vacancy"."town" is not null then ', '+"vacancy"."town" endif),(if "vacancy"."postcode" is not null then ', '+"vacancy"."postcode" endif)),
    "getcompanyaddressonline"("c"."companyid"))) as "Address",
    "isnull"("vacancy"."sitecontact","cp"."name") as "ContactName",
    "isnull"("vacancy"."sitephonenumbers","getphone"('C','Telephone',"c"."companyid")) as "ContactPhone",
    if "trim"("isnull"("vacancy"."postcode","c"."postcode",'')) = '' then '' else "urlsafe"("string"("isnull"("vacancy"."postcode","c"."postcode"),'(',"isnull"("vacancy"."addr1","c"."addr1"),')')) endif as "MapQuery"
    from "tempshift" as "s" key join "person"
      ,"tempshift" as "s" key left outer join "tempshiftunavailablereason"
      ,"tempshift" as "s" key left outer join("vacancy" key join "employment" key join("company" as "c","person" as "cp"))
      ,"tempshift" as "s" left outer join "availabilitytemplate" as "ty" on "ty"."AvailTemplateid" = "s"."availtemplateid"
      ,"tempshift" as "s" left outer join "TempShiftUnavailableReason" as "ur" on "ur"."TempShiftUnavailableReasonID" = "s"."UnavailableReason"
    where "s"."personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pwebuserid")
    and "s"."shiftdate" >= "pstart"-1 and not("s"."state" = 'C' and "isnull"("s"."tempconfirmed",0) = 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",
    '' as "ShiftType",
    "isnull"("e"."leavedate",("dateadd"("year",1,current date))) as "DateTo",
    cast(null as time) as "TimeFrom",
    cast(null as time) 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","cp"."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"
    from "employment" as "e" key join "placement" as "pl" key join "vacancy" as "v" key join "employment" as "ce" key join("company" as "c","person" as "cp")
    where "e"."personid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pwebuserid")
    and("e"."leavedate" is null or "e"."leavedate" >= "pstart")
    and "isnull"("e"."concurrent",0) = 0
    order by "DateFrom" asc,"TimeFrom" asc
end /* DOC
2016-12-01 PC Doc and Test
2017-03-21 PC extend data sample from 30 days to 365 days
2017-09-26 PC split address line into address,contact and phone 
2017-10-23 PC increase size of ContactNumber parameter to allow for chained numbers 
2017-10-26 PC change person displayed to company contact
2017-10-27 PC link modified on line 56, ref. MHS
2017-12-12 PC change mapquery, contact address etc. to refer to vacancy then company
2018-02-06 PC modify the concurrent and finish dates
2018-05-01 PC,MS concurrent reinstated
2018-05-17 MHS fixed repeating employments (removed bogus join to tempshift). Sorted tests.
2018-05-23 PC IW-543 include ShiftType column
2018-11-09 PC DateTo modified as per request.
2018-11-23 PC DateTo modified as per request.

Inputs:
pWebUserID

Outputs:
DiaryID
DiaryClass
ActionType
Description
DiaryStatus
DateFrom
DateTo
TimeFrom
TimeTo
Address
MapQuery

*/
/* TEST
call NetTestSetup('');
delete from tempshift where personid='TEST';
update employment set concurrent=1 where personid='TEST';
select * from NetCandidateDiary('test.candidate');
expect EOF;
update employment set startdate=current date-50,leavedate=null,concurrent=0 where employmentid='TEST';
insert into placement (placementid,employmentid,vacancyid,staffid,departmentid,placedate) on existing update defaults off values ('TEST','TEST','TEST','TEST','~~',current date-50);
insert into tempshift (tempshiftid,vacancyid,personid,shiftdate,state) values ('TESTSHIFT1','TEST','TEST',current date,'B');
insert into tempshift (tempshiftid,vacancyid,personid,shiftdate,state) values ('TESTSHIFT2','TEST','TEST',current date+1,'P');
insert into tempshift (tempshiftid,personid,shiftdate,state) values ('TESTSHIFT3','TEST',current date+2,'H');
select DiaryID, DiaryClass, DiaryStatus+'z' as Stat from NetCandidateDiary('test.candidate');
expect DiaryID=Employment*, DiaryClass=Working, Stat=z;
expect DiaryID=Shift*, DiaryClass=Working, Stat=z;
expect DiaryID=Shift*, DiaryClass=Working, Stat=Provisionalz;
expect DiaryID=Shift*, DiaryClass=Holiday, Stat=z;
expect EOF;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDiary" IS 
{create PROCEDURE pears."NetCandidateDiary"(in pWebUserID char(20))
result(DiaryID char(50),DiaryClass char(50),ActionType char(50),Description char(100),DiaryStatus char(50),DateFrom date,ShiftType char(60),DateTo date,TimeFrom time,TimeTo time, Address char (250), ContactName char(100), ContactNumber char(100), MapQuery char(100))
// IQXWeb
begin
  declare pstart date;
  set pstart=current date-365;
  while dow(pstart) <> 2 loop
    set pstart=pstart-1
  end loop;
  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,tempshiftunavailablereason.name) as Description,
    (if s.state = 'P' then 'Provisional'
    else ''
    endif) as DiaryStatus,
    s.shiftdate as DateFrom,
    isnull(ty.Name,ur.name) as ShiftType,
    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(vacancy.sitename,(if vacancy.sitename is not null then ', ' endif),(if vacancy.addr1 is not null then vacancy.addr1 endif),(if vacancy.addr2 is not null then ', '+vacancy.addr2 endif),(if vacancy.addr3 is not null then ', '+vacancy.addr3 endif),(if vacancy.town is not null then ', '+vacancy.town endif),(if vacancy.postcode is not null then ', '+vacancy.postcode endif))
        ,getcompanyaddressonline(c.companyid))
    ) as Address,
    isnull(vacancy.sitecontact,cp.name) as ContactName,
    isnull(vacancy.sitephonenumbers,getphone('C','Telephone',c.companyid)) as ContactPhone,
    if trim(isnull(vacancy.postcode,c.postcode,''))='' then '' else urlsafe(string(isnull(vacancy.postcode,c.postcode),'(',isnull(vacancy.addr1,c.addr1),')')) endif as MapQuery
    from 
       tempshift s key join person, 
       tempshift s key left outer join tempshiftunavailablereason, 
       tempshift as s key left outer join (vacancy key join employment key join (company c, person cp)),
       tempshift as s left outer join availabilitytemplate ty on ty.AvailTemplateid=s.availtemplateid,
       tempshift as s left outer join TempShiftUnavailableReason ur on ur.TempShiftUnavailableReasonID=s.UnavailableReason
    where s.personid in (select personid from iqxnetuserlink where iqxnetuserid = pwebuserid)
    and s.shiftdate >= pstart-1 and not(s.state = 'C' and isnull(s.tempconfirmed,0) = 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,
    '' as ShiftType,
    isnull(e.leavedate,(dateadd(year,1,current date))) as DateTo,
    cast(null as time) as TimeFrom,
    cast(null as time) 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,cp.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
    from 
       employment e key join placement pl key join vacancy v key join employment ce key join (company c, person cp)
    where e.personid in (select personid from iqxnetuserlink where iqxnetuserid = pwebuserid)
    and(e.leavedate is null or e.leavedate >= pstart)
    and isnull(e.concurrent,0) = 0 
  order by
    DateFrom asc,TimeFrom asc
end

/* DOC
2016-12-01 PC Doc and Test
2017-03-21 PC extend data sample from 30 days to 365 days
2017-09-26 PC split address line into address,contact and phone 
2017-10-23 PC increase size of ContactNumber parameter to allow for chained numbers 
2017-10-26 PC change person displayed to company contact
2017-10-27 PC link modified on line 56, ref. MHS
2017-12-12 PC change mapquery, contact address etc. to refer to vacancy then company
2018-02-06 PC modify the concurrent and finish dates
2018-05-01 PC,MS concurrent reinstated
2018-05-17 MHS fixed repeating employments (removed bogus join to tempshift). Sorted tests.
2018-05-23 PC IW-543 include ShiftType column
2018-11-09 PC DateTo modified as per request.
2018-11-23 PC DateTo modified as per request.

Inputs:
    pWebUserID
 
Outputs:
    DiaryID
    DiaryClass
    ActionType
    Description
    DiaryStatus
    DateFrom
    DateTo
    TimeFrom
    TimeTo
    Address
    MapQuery

*/

/* TEST
call NetTestSetup('');
delete from tempshift where personid='TEST';
update employment set concurrent=1 where personid='TEST';
select * from NetCandidateDiary('test.candidate');
expect EOF;
update employment set startdate=current date-50,leavedate=null,concurrent=0 where employmentid='TEST';
insert into placement (placementid,employmentid,vacancyid,staffid,departmentid,placedate) on existing update defaults off values ('TEST','TEST','TEST','TEST','~~',current date-50);
insert into tempshift (tempshiftid,vacancyid,personid,shiftdate,state) values ('TESTSHIFT1','TEST','TEST',current date,'B');
insert into tempshift (tempshiftid,vacancyid,personid,shiftdate,state) values ('TESTSHIFT2','TEST','TEST',current date+1,'P');
insert into tempshift (tempshiftid,personid,shiftdate,state) values ('TESTSHIFT3','TEST',current date+2,'H');
select DiaryID, DiaryClass, DiaryStatus+'z' as Stat from NetCandidateDiary('test.candidate');
expect DiaryID=Employment*, DiaryClass=Working, Stat=z;
expect DiaryID=Shift*, DiaryClass=Working, Stat=z;
expect DiaryID=Shift*, DiaryClass=Working, Stat=Provisionalz;
expect DiaryID=Shift*, DiaryClass=Holiday, Stat=z;
expect EOF;
*/
}
</code>