pears.NetMessage

CREATE PROCEDURE "pears"."NetMessage"( IN "pWebUserID" CHAR(20),IN "ptype" CHAR(20),IN "ptitle" CHAR(100),IN "pdescription" long VARCHAR,
  IN "pvacancyid" CHAR(20) DEFAULT NULL,IN "pplacementid" CHAR(20) DEFAULT NULL,IN "pprogressid" CHAR(20) DEFAULT NULL,
  IN "pprovtimesheetid" CHAR(20) DEFAULT NULL,IN "ptimesheetid" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "persid" CHAR(20);
  DECLARE "empid" CHAR(20);
  IF(SELECT "Value" FROM "IQXNetSwitch" WHERE "IQXNetSwitchID" = 'POPUPDOCUPDISABLE') = 1 THEN
    SELECT '99:~Permission denied';
    RETURN
  END IF;
  SET "persid" = NULL;
  SET "empid" = (SELECT FIRST "employmentid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid");
  IF "empid" IS NULL THEN
    SET "persid" = (SELECT FIRST "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid");
    IF "persid" IS NULL THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF END IF;
  SET "pprovtimesheetid" = "nullif"("trim"("pprovtimesheetid"),'');
  SET "ptimesheetid" = "nullif"("trim"("ptimesheetid"),'');
  IF "pprovtimesheetid" IS NOT NULL THEN
    SET "pvacancyid" = (SELECT FIRST "vacancyid" FROM "tempprovtimesheet" WHERE "tempprovtimesheetid" = "pprovtimesheetid")
  END IF;
  IF "ptimesheetid" IS NOT NULL THEN
    SET "pplacementid" = (SELECT FIRST "placementid" FROM "temptimesheet" WHERE "temptimesheetid" = "ptimesheetid");
    SET "pvacancyid" = (SELECT FIRST "vacancyid" FROM "placement" WHERE "placementid" = "pplacementid")
  END IF;
  IF "trim"("isnull"("ptype",'')) = '' THEN
    SELECT '101:~Invalid message type';
    RETURN
  END IF;
  IF "trim"("isnull"("ptitle",'')) = '' THEN
    SELECT '102:~Incomplete message';
    RETURN
  END IF;
  CALL "IQXNetPopup"("pWebUserID","persid","empid","pvacancyid","pplacementid","pprogressid","ptype","ptitle","pdescription");
  SELECT '0:~Success'
END /* DOC
2016-08-30 PC test and doc
2018-08-27 PC improve error handler
2019-11-22 GJ Added option to disable popup for document upload (IW-1040)
 
tests:
invalid person attached to pWebUserID
invalid message type
incomplete message
valid message attached to vacancy
valid message attached to placement
valid message attached to progress
valid message attached to prov timesheet
valid message attached to timesheet
 
*/
/* TEST
call NetTestSetup('');
 
select * from NetMessage('test.zz',null,null,null,null,null,null,null);
expect pResult=99*;
select * from NetMessage('test.client','zz',null,null,null,null,null,null);
expect pResult=102*;
select * from NetMessage('test.client','','TestMessage','TestDescription',null,null,null,null);
expect pResult=101*;
select * from NetMessage('test.client','TEST','TestMessage','TestDescription','TEST',null,null,null);
expect pResult=0*;
select top 1 * from diary where staffid='TEST' order by whenentered desc;
expect vacancyid=TEST,employmentid=TEST,description=TestMessage;
delete from diary where staffid='test';
select * from NetMessage('test.client','TEST','TestMessage2','TestDescription2',null,'TEST',null,null);
expect pResult=0*;
select top 1 * from diary where staffid='TEST' order by whenentered desc;
expect vacancyid=TEST,employmentid=TEST,description=TestMessage2;
 
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetMessage" IS 
{CREATE PROCEDURE pears."NetMessage"(IN pWebUserID CHAR(20),IN ptype CHAR(20),IN ptitle CHAR(100),IN pdescription long VARCHAR,IN pvacancyid CHAR(20) DEFAULT
NULL,IN pplacementid CHAR(20) DEFAULT NULL,IN pprogressid CHAR(20) DEFAULT NULL,IN pprovtimesheetid CHAR(20) DEFAULT
NULL,IN ptimesheetid CHAR(20) DEFAULT NULL)
RESULT(pResult CHAR(250))
// IQXWeb
BEGIN
  DECLARE persid CHAR(20);
  DECLARE empid CHAR(20);
 
  IF (SELECT "Value" FROM IQXNetSwitch WHERE IQXNetSwitchID = 'POPUPDOCUPDISABLE') = 1 THEN
    SELECT '99:~Permission denied';
    RETURN;
  END IF;
 
  SET persid=NULL;
  SET empid=(SELECT FIRST employmentid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid);
  IF empid IS NULL THEN
    SET persid=(SELECT FIRST personid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid);
    IF persid IS NULL THEN
      SELECT '99:~Permission denied';
      RETURN
    END IF
  END IF;
  SET pprovtimesheetid=NULLIF(TRIM(pprovtimesheetid),'');
  SET ptimesheetid=NULLIF(TRIM(ptimesheetid),'');
  IF pprovtimesheetid IS NOT NULL THEN
    SET pvacancyid=(SELECT FIRST vacancyid FROM tempprovtimesheet WHERE tempprovtimesheetid = pprovtimesheetid)
  END IF;
  IF ptimesheetid IS NOT NULL THEN
    SET pplacementid=(SELECT FIRST placementid FROM temptimesheet WHERE temptimesheetid = ptimesheetid);
    SET pvacancyid=(SELECT FIRST vacancyid FROM placement WHERE placementid = pplacementid)
  END IF;
  IF TRIM(isnull(ptype,'')) = '' THEN
    SELECT '101:~Invalid message type';
    RETURN
  END IF;
  IF TRIM(isnull(ptitle,'')) = '' THEN
    SELECT '102:~Incomplete message';
    RETURN
  END IF;
  CALL IQXNetPopup(pWebUserID,persid,empid,pvacancyid,pplacementid,pprogressid,ptype,ptitle,pdescription);
  SELECT '0:~Success'
END
 
/* DOC
2016-08-30 PC test and doc
2018-08-27 PC improve error handler
2019-11-22 GJ Added option to disable popup for document upload (IW-1040)
 
tests:
    invalid person attached to pWebUserID
    invalid message type
    incomplete message
    valid message attached to vacancy
    valid message attached to placement
    valid message attached to progress
    valid message attached to prov timesheet
    valid message attached to timesheet
 
*/
 
/* TEST
call NetTestSetup('');
 
select * from NetMessage('test.zz',null,null,null,null,null,null,null);
expect pResult=99*;
select * from NetMessage('test.client','zz',null,null,null,null,null,null);
expect pResult=102*;
select * from NetMessage('test.client','','TestMessage','TestDescription',null,null,null,null);
expect pResult=101*;
select * from NetMessage('test.client','TEST','TestMessage','TestDescription','TEST',null,null,null);
expect pResult=0*;
select top 1 * from diary where staffid='TEST' order by whenentered desc;
expect vacancyid=TEST,employmentid=TEST,description=TestMessage;
delete from diary where staffid='test';
select * from NetMessage('test.client','TEST','TestMessage2','TestDescription2',null,'TEST',null,null);
expect pResult=0*;
select top 1 * from diary where staffid='TEST' order by whenentered desc;
expect vacancyid=TEST,employmentid=TEST,description=TestMessage2;
 
 
*/
}