pears.NetOwnerCandidateAppointments

CREATE PROCEDURE "pears"."NetOwnerCandidateAppointments"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pFromDate" CHAR(10) DEFAULT NULL,IN "pToDate" CHAR(10) DEFAULT NULL ) 
RESULT( "CandidateName" CHAR(60),"DiaryDescription" CHAR(255),"DiaryNotes" long VARCHAR,"DiaryFrom" CHAR(25),"DiaryTo" CHAR(25) ) 
BEGIN
  // IQXWeb
  DECLARE "pStartDate" CHAR(10);
  DECLARE "pFinishDate" CHAR(10);
  SET "pStartDate" = "isnull"("IQXNetStringToDate"("pFromDate"),"pFromDate",CURRENT date-31);
  SET "pFinishDate" = "isnull"("IQXNetStringToDate"("pToDate"),"pToDate",CURRENT DATE+31);
  IF NOT EXISTS(SELECT "iqxnetuserid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID" AND "IQXNetUserClassID" = 'OWNER') THEN
    RETURN
  END IF;
  IF NOT EXISTS(SELECT "personid" FROM "person" WHERE "personid" = "pPersonID") THEN
    RETURN
  END IF;
  SELECT "p"."name",
    "d"."description" AS "DiaryDescription",
    "d"."notes" AS "DiaryNotes",
    "d"."diaryFrom",
    "d"."DiaryTo"
    FROM "diary" AS "d"
      KEY JOIN "person" AS "p"
    WHERE "p"."personid" = "pPersonID"
    AND "DurationType" <> 'R'
    AND "diaryTo" >= "pStartDate"
    AND "diaryFrom" <= "pFinishDate"
END /* DOC
2018-10-23 PC IW-693 Consultant portal. Able to see candidates diary when logged in as a consultant
*/
/* TEST
set userstaffid='test';
insert into diary (diaryid,staffid,personid,diaryfrom,diaryto,description,notes,durationtype,duration,whenentered,whoentered) on existing update values('TEST1','TEST','TEST',current date,dateadd(dd,1,current date),'DanTester','BookedTest','H',30,current date,'TEST');
insert into diary (diaryid,staffid,personid,diaryfrom,diaryto,description,notes,durationtype,duration,whenentered,whoentered) on existing update values('TEST2','TEST','TEST',current date,dateadd(dd,1,current date),'DanTester','BookedTest','R',0,current date,'TEST');
select * from NetOwnerCandidateAppointments('test.client','test',current date+3,null);
expect EOF;
select * from NetOwnerCandidateAppointments('test.owner','test',null,null);
expect CandidateName=test,DiaryDescription=DanTester,DiaryNotes=BookedTest;
select * from NetOwnerCandidateAppointments('test.owner','test',current date+3,null);
expect EOF;
select * from NetOwnerCandidateAppointments('test.owner','test',null,current date-3);
expect EOF;
select * from NetOwnerCandidateAppointments('test.owner','test',current date-3,current date+3);
expect CandidateName=test,DiaryDescription=DanTester,DiaryNotes=BookedTest;
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerCandidateAppointments" IS 
{CREATE PROCEDURE pears."NetOwnerCandidateAppointments"(IN pWebUserID CHAR(20),IN pPersonID CHAR(20), IN pFromDate CHAR(10) DEFAULT NULL, pToDate CHAR(10) DEFAULT NULL)
RESULT(CandidateName CHAR(60),DiaryDescription CHAR(255),DiaryNotes long VARCHAR,DiaryFrom CHAR(25),DiaryTo CHAR(25))
BEGIN
// IQXWeb
 
DECLARE pStartDate CHAR(10);
DECLARE pFinishDate CHAR(10);
 
SET pStartDate=isnull(IQXNetStringToDate(pFromDate),pFromDate,CURRENT date-31);
SET pFinishDate=isnull(IQXNetStringToDate(pToDate),pToDate,CURRENT DATE+31);
 
IF NOT EXISTS (SELECT iqxnetuserid FROM iqxnetuser WHERE iqxnetuserid=pWebUserID AND IQXNetUserClassID='OWNER') THEN
    RETURN;
END IF;
IF NOT EXISTS (SELECT personid FROM person WHERE personid=pPersonID) THEN
    RETURN;
END IF;
 
SELECT 
    p.name,
    d.description AS DiaryDescription,
    d.notes AS DiaryNotes,
    d.diaryFrom,
    d.DiaryTo
FROM
    diary d
    KEY JOIN person p 
WHERE
    p.personid=pPersonID
    AND DurationType != 'R'
    AND diaryTo >= pStartDate
    AND diaryFrom <= pFinishDate
 
END
 
/* DOC
2018-10-23 PC IW-693 Consultant portal. Able to see candidates diary when logged in as a consultant
*/
 
/* TEST
set userstaffid='test';
insert into diary (diaryid,staffid,personid,diaryfrom,diaryto,description,notes,durationtype,duration,whenentered,whoentered) on existing update values('TEST1','TEST','TEST',current date,dateadd(dd,1,current date),'DanTester','BookedTest','H',30,current date,'TEST');
insert into diary (diaryid,staffid,personid,diaryfrom,diaryto,description,notes,durationtype,duration,whenentered,whoentered) on existing update values('TEST2','TEST','TEST',current date,dateadd(dd,1,current date),'DanTester','BookedTest','R',0,current date,'TEST');
select * from NetOwnerCandidateAppointments('test.client','test',current date+3,null);
expect EOF;
select * from NetOwnerCandidateAppointments('test.owner','test',null,null);
expect CandidateName=test,DiaryDescription=DanTester,DiaryNotes=BookedTest;
select * from NetOwnerCandidateAppointments('test.owner','test',current date+3,null);
expect EOF;
select * from NetOwnerCandidateAppointments('test.owner','test',null,current date-3);
expect EOF;
select * from NetOwnerCandidateAppointments('test.owner','test',current date-3,current date+3);
expect CandidateName=test,DiaryDescription=DanTester,DiaryNotes=BookedTest;
 
*/
}