pears.NetCandidateCalendar

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
}