====== pears.NetCandidateCalendar ======
<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"."NetCandidateCalendar"( in "pWebUserID" char(20),in "pstart" double,in "pend" double ) 
result( "id" char(20),"title" char(100),"className" char(50),"allDay" char(5),"shiftStart" char(20),"shiftEnd" char(20),"description" char(250),"editable" char(5),"deletable" char(5),"confirmable" char(5),"unconfirmable" char(5) ) 
begin
  declare "t" char(5);
  declare "f" char(5);
  declare "dStart" date;
  declare "dEnd" date;
  set "t" = 'true';
  set "f" = 'false';
  set "dstart" = "dateadd"("second","pstart",'1970-01-01');
  set "dend" = "dateadd"("second","pend",'1970-01-01');
  select "s"."tempshiftid",
    (case "s"."state" when 'H' then 'Holiday' when 'U' then 'Unavailable' when 'A' then 'Available' when 'C' then 'Cancelled' when 'P' then 'Provisionally Working'
    else 'Working'
    end) as "title",(case "s"."state" when 'H' then 'calendarUnavailable' when 'U' then 'calendarUnavailable' when 'A' then 'calendarAvailable' when 'P' then 'calendarBooked' when 'C' then 'calendarCancelled' end) as "className",
    (if "s"."timefrom" is null or "s"."timeto" is null then "t" else "f" endif) as "allDay",
    (if "allDay" = "t" then "dateformat"("s"."shiftdate",'yyyy-mm-dd')
    else "dateformat"("s"."shiftdate"+"s"."timefrom",'yyyy-mm-ddThh:nn:ssZ')
    endif) as "shiftStart",(if "allDay" = "t" then "dateformat"("s"."shiftdate",'yyyy-mm-dd')
    else if "s"."timeto" >= "s"."timefrom" then "dateformat"("s"."shiftdate"+"s"."timeto",'yyyy-mm-ddThh:nn:ssZ')
      else "dateformat"("dateadd"("day",1,"s"."shiftdate"+"s"."timeto"),'yyyy-mm-ddThh:nn:ssZ')
      endif
    endif) as "shiftEnd","string"("vacancy"."position",' ',"company"."name") as "description",
    (if "s"."state" in( 'H','A','U' ) then "t" else "f" endif) as "editable",
    "editable" as "deleteable",
    (if "s"."state" = 'P' and "isnull"("tempconfirmed",0) = 0 then "t" else "f" endif) as "confirmable",
    (if "s"."state" = 'P' and "isnull"("tempconfirmed",0) = 1 then "t" else "f" endif) as "unconfirmable"
    from "tempshift" as "s" key join "person" key join "iqxnetuserlink"
      ,"tempshift" as "s" key left outer join "tempshiftunavailablereason"
      ,"tempshift" as "s" key left outer join("vacancy" key join "employment" key join "company")
    where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid"
    and "s"."shiftdate" >= "dstart"-1 and "s"."shiftdate" <= "dend" and not("s"."state" = 'C' and "isnull"("s"."tempconfirmed",0) = 1) union all
  select "e"."employmentid",'Working','',"t",
    "dateformat"("e"."startdate",'yyyy-mm-dd'),"dateformat"("isnull"("e"."leavedate","dend"),'yyyy-mm-dd'),
    "string"("e"."position",' ',"company"."name"),"f","f","f","f"
    from "employment" as "e" key join("person","company")
      ,"person" key join "iqxnetuserlink"
    where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid"
    and("e"."leavedate" is null or "e"."leavedate" >= "dstart") and "e"."startdate" <= "dend"
    and "isnull"("e"."concurrent",0) = 0
    order by "shiftstart" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateCalendar" IS 
{create procedure pears."NetCandidateCalendar"( in "pWebUserID" char(20),in "pstart" double,in "pend" double ) 
result( "id" char(20),"title" char(100),"className" char(50),"allDay" char(5),"shiftStart" char(20),"shiftEnd" char(20),"description" char(250),"editable" char(5),"deletable" char(5),"confirmable" char(5),"unconfirmable" char(5) ) 
begin
  declare "t" char(5);
  declare "f" char(5);
  declare "dStart" date;
  declare "dEnd" date;
  set "t" = 'true';
  set "f" = 'false';
  set "dstart" = "dateadd"("second","pstart",'1970-01-01');
  set "dend" = "dateadd"("second","pend",'1970-01-01');
  select "s"."tempshiftid",
    (case "s"."state" when 'H' then 'Holiday' when 'U' then 'Unavailable' when 'A' then 'Available' when 'C' then 'Cancelled' when 'P' then 'Provisionally Working'
    else 'Working'
    end) as "title",(case "s"."state" when 'H' then 'calendarUnavailable' when 'U' then 'calendarUnavailable' when 'A' then 'calendarAvailable' when 'P' then 'calendarBooked' when 'C' then 'calendarCancelled' end) as "className",
    (if "s"."timefrom" is null or "s"."timeto" is null then "t" else "f" endif) as "allDay",
    (if "allDay" = "t" then "dateformat"("s"."shiftdate",'yyyy-mm-dd')
    else "dateformat"("s"."shiftdate"+"s"."timefrom",'yyyy-mm-ddThh:nn:ssZ')
    endif) as "shiftStart",(if "allDay" = "t" then "dateformat"("s"."shiftdate",'yyyy-mm-dd')
    else if "s"."timeto" >= "s"."timefrom" then "dateformat"("s"."shiftdate"+"s"."timeto",'yyyy-mm-ddThh:nn:ssZ')
      else "dateformat"("dateadd"("day",1,"s"."shiftdate"+"s"."timeto"),'yyyy-mm-ddThh:nn:ssZ')
      endif
    endif) as "shiftEnd","string"("vacancy"."position",' ',"company"."name") as "description",
    (if "s"."state" in( 'H','A','U' ) then "t" else "f" endif) as "editable",
    "editable" as "deleteable",
    (if "s"."state" = 'P' and "isnull"("tempconfirmed",0) = 0 then "t" else "f" endif) as "confirmable",
    (if "s"."state" = 'P' and "isnull"("tempconfirmed",0) = 1 then "t" else "f" endif) as "unconfirmable"
    from "tempshift" as "s" key join "person" key join "iqxnetuserlink"
      ,"tempshift" as "s" key left outer join "tempshiftunavailablereason"
      ,"tempshift" as "s" key left outer join("vacancy" key join "employment" key join "company")
    where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid"
    and "s"."shiftdate" >= "dstart"-1 and "s"."shiftdate" <= "dend" and not("s"."state" = 'C' and "isnull"("s"."tempconfirmed",0) = 1) union all
  select "e"."employmentid",'Working','',"t",
    "dateformat"("e"."startdate",'yyyy-mm-dd'),"dateformat"("isnull"("e"."leavedate","dend"),'yyyy-mm-dd'),
    "string"("e"."position",' ',"company"."name"),"f","f","f","f"
    from "employment" as "e" key join("person","company")
      ,"person" key join "iqxnetuserlink"
    where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid"
    and("e"."leavedate" is null or "e"."leavedate" >= "dstart") and "e"."startdate" <= "dend"
    and "isnull"("e"."concurrent",0) = 0
    order by "shiftstart" asc
end
}
</code>