====== pears.NetOwnerCandidateAppointments ======
<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"."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;

*/
}
</code>