pears.NetDivisionPopup

CREATE PROCEDURE "pears"."NetDivisionPopup"( 
  IN "pWebUserID" CHAR(20),IN "pDivisionID" CHAR(20) DEFAULT NULL,IN "pStaffID" CHAR(20) DEFAULT NULL,IN "pPersonID" CHAR(20) DEFAULT NULL,IN "pEmploymentID" CHAR(20) DEFAULT NULL,IN "pVacancyID" CHAR(20) DEFAULT NULL,IN "ptitle" CHAR(100),IN "pdescription" long VARCHAR DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE LOCAL TEMPORARY TABLE "sids"(
    "sid" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "sids"( "sid" ) SELECT "staffid" FROM "staff" WHERE("divisionid" = "pDivisionID" OR "staffid" = "pStaffID");
  IF "pVacancyID" IS NOT NULL AND "pEmploymentID" IS NULL THEN
    SET "pEmploymentID" = (SELECT "employmentid" FROM "vacancy" WHERE "vacancyid" = "pVacancyID")
  END IF;
  INSERT INTO "diary"( "diaryid","staffid","personid","employmentid","vacancyid","diaryfrom","description","notes","durationtype","popupminutes" ) 
    SELECT "uniquekey"("sid"),"sid","pPersonID","pEmploymentID","pVacancyID",CURRENT TIMESTAMP,"ptitle","pdescription",'R',0
      FROM "sids";
  SELECT '0:~Success'
END /* DOC
2019-7-24 MHS created for IW-993
Can pass in pDivisionID and/or pStaffID along with any of pPersonID, pEmploymentID, pVacancyID
pTitle is required, pdescription is optional
*/
/* TEST
call NetTestSetup('');
delete from diary where staffid='TEST' and diaryfrom > current date;
select * from netdivisionpopup(pWebUserID='TEST',pStaffID='TEST',pVacancyID='TEST',pTitle='T1',pdescription='T2');
select * from diary where staffid='TEST' and diaryfrom > current date;
expect staffid=TEST, employmentid=TEST, notes=T2;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetDivisionPopup" IS 
{CREATE PROCEDURE pears.NetDivisionPopup
(IN pWebUserID CHAR(20),IN pDivisionID CHAR(20) DEFAULT NULL, IN pStaffID CHAR(20) DEFAULT NULL, IN pPersonID CHAR(20) DEFAULT NULL,IN pEmploymentID CHAR(20) DEFAULT NULL,IN pVacancyID CHAR(20) DEFAULT NULL,IN ptitle CHAR(100),IN pdescription long VARCHAR DEFAULT NULL)
RESULT(pResult CHAR(250))
BEGIN
  DECLARE LOCAL TEMPORARY TABLE sids(
    sid CHAR(20),
    ) NOT transactional;
  INSERT INTO sids (sid) SELECT staffid FROM staff WHERE divisionid=pDivisionID OR staffid=pStaffID;
  IF pVacancyID IS NOT NULL AND pEmploymentID IS NULL THEN
    SET pEmploymentID=(SELECT employmentid FROM vacancy WHERE vacancyid=pVacancyID)
  END IF;
  INSERT INTO diary( diaryid,staffid,personid,employmentid,vacancyid,diaryfrom,description,notes,durationtype,popupminutes) SELECT 
    uniquekey(sid),sid,pPersonID,pEmploymentID,pVacancyID,CURRENT TIMESTAMP,ptitle,pdescription,'R',0
  FROM sids;
  SELECT '0:~Success'
END
 
/* DOC
2019-7-24 MHS created for IW-993
Can pass in pDivisionID and/or pStaffID along with any of pPersonID, pEmploymentID, pVacancyID
pTitle is required, pdescription is optional
*/
 
/* TEST
call NetTestSetup('');
delete from diary where staffid='TEST' and diaryfrom > current date;
select * from netdivisionpopup(pWebUserID='TEST',pStaffID='TEST',pVacancyID='TEST',pTitle='T1',pdescription='T2');
select * from diary where staffid='TEST' and diaryfrom > current date;
expect staffid=TEST, employmentid=TEST, notes=T2;
*/
}