====== pears.NetClientCreatePopup ======
<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"."NetClientCreatePopup"( in "pWebUserID" char(20),in "pConsultantID" char(20) default null,in "pDocID" char(10),in "pComment" char(255) ) 
result( "pResult" char(255) ) 
begin
  // IQXNet
  declare "ptype" char(10);
  declare "ptitle" char(30);
  declare "pStaffID" char(20);
  declare "pEmploymentID" char(20);
  if "pComment" is null then
    select '102:~Please enter the details';
    return
  end if;
  set "pEmploymentID" = (select first "employmentid" from "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID" and "employmentid" is not null order by "employmentid" asc);
  if "pEmploymentID" is null then
    select '99:~Permission denied';
    return
  end if;
  set "ptype" = 'ACCOUNTS';
  set "ptitle" = 'Query from website'+if "isnull"("pDocID",'') <> '' then ' - '+"pDocID" endif;
  set "pStaffID" = "isnull"("pConsultantID",
    (select first "r"."staffid" from "IQXNetMessageCompanyRecipient" as "r" key join "company" key join "employment" key join "iqxnetuserlink" as "l" where "l"."iqxnetuserid" = "pWebUserID" and "isnull"("r"."MessageType","ptype") = "ptype" order by "r"."staffid" asc),
    (select first "staffid" from "IQXNetMessageRecipient" where "MessageType" = "ptype" order by "staffid" asc),
    (select first "company"."staffid" from "iqxnetuserlink" as "l" key join "employment" key join "company" where "l"."iqxnetuserid" = "pWebUserID" order by "company"."staffid" asc));
  if "pStaffID" is null then
    select '101:~Unable to route your query - please contact the agency';
    return
  end if;
  insert into "diary"( "diaryid","staffid","employmentid","diaryfrom","description","notes","durationtype","popupminutes" ) values
    ( "uniquekey"("pStaffID"),"pStaffID","pEmploymentID",current timestamp,"ptitle","pComment",'R',0 ) ;
  select '0:~success'
end /* DOC
2019-10-17 MHS re-design and re-write tests
*/
/* TEST
call NetTestSetup('');
insert into iqxnetmessagecompanyrecipient (recipientid,companyid,messagetype,staffid) on existing update values('TESTACCOUNTS','TEST','ACCOUNTS','TEST');
select * from NetClientCreatePopup(null,null,null,null);
expect 102*;
select * from NetClientCreatePopup(null,null,null,'xxx');
expect 99*;
select * from NetClientCreatePopup('test.candidate',null,null,'xxx');
expect 99*;
select * from NetClientCreatePopup('test.client',null,null,'xxx');
expect 0*;
select top 1 description,notes from diary where employmentid='TEST' and staffid='TEST' and whenentered>current date order by whenentered desc;
expect description=Query from website,notes=xxx;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientCreatePopup" IS 
{create PROCEDURE pears."NetClientCreatePopup"(in pWebUserID char(20),in pConsultantID char(20) default null, in pDocID char(10), in pComment char(255) )
RESULT( pResult char(255) )
BEGIN
// IQXNet
  declare "ptype" char(10);
  declare "ptitle" char(30);
  declare "pStaffID" char(20);
  declare "pEmploymentID" char(20);

  if pComment is null then
    select '102:~Please enter the details';
    return
  end if;

  set "pEmploymentID" = (select first employmentid from iqxnetuserlink where iqxnetuserid=pWebUserID and employmentid is not null order by employmentid);
  if pEmploymentID is null then
    select '99:~Permission denied';
    return
  end if;

  set "ptype" = 'ACCOUNTS';
  set "ptitle" = 'Query from website'+ if isnull(pDocID,'')!='' then ' - '+pDocID endif;
  set pStaffID=isnull(pConsultantID,
    (select first r.staffid from IQXNetMessageCompanyRecipient r key join company key join employment key join iqxnetuserlink l where l.iqxnetuserid=pWebUserID and isnull(r.MessageType,ptype)=ptype order by r.staffid),
    (select first staffid from IQXNetMessageRecipient where MessageType = ptype order by staffid),
    (select first company.staffid from iqxnetuserlink l key join employment key join company where l.iqxnetuserid=pWebUserID order by company.staffid));
   
  if pStaffID is null then
    select '101:~Unable to route your query - please contact the agency';
    return
  end if;
  insert into diary( diaryid,staffid,employmentid,diaryfrom,description,notes,durationtype,popupminutes) values( 
    uniquekey(pStaffID),pStaffID,pEmploymentID,current timestamp,ptitle,pComment,'R',0);
  select '0:~success'
END

/* DOC
2019-10-17 MHS re-design and re-write tests
*/

/* TEST
call NetTestSetup('');
insert into iqxnetmessagecompanyrecipient (recipientid,companyid,messagetype,staffid) on existing update values('TESTACCOUNTS','TEST','ACCOUNTS','TEST');
select * from NetClientCreatePopup(null,null,null,null);
expect 102*;
select * from NetClientCreatePopup(null,null,null,'xxx');
expect 99*;
select * from NetClientCreatePopup('test.candidate',null,null,'xxx');
expect 99*;
select * from NetClientCreatePopup('test.client',null,null,'xxx');
expect 0*;
select top 1 description,notes from diary where employmentid='TEST' and staffid='TEST' and whenentered>current date order by whenentered desc;
expect description=Query from website,notes=xxx;
*/
}
</code>