pears.NetOwnerCandidateDiary

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';
*/
}