pears.NetClientCreatePopup

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