pears.IQXNetPopup

CREATE PROCEDURE "pears"."IQXNetPopup"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20),IN "pEmploymentID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pPlacementID" CHAR(20),IN "pProgressID" CHAR(20),IN "ptype" CHAR(20),IN "ptitle" CHAR(100),IN "pdescription" long VARCHAR ) 
BEGIN
  DECLARE "qstaffid" CHAR(20);
  DECLARE "qpersonid" CHAR(20);
  DECLARE "qemploymentid" CHAR(20);
  DECLARE "qcompanyid" CHAR(20);
  DECLARE "qvacancyid" CHAR(20);
  DECLARE "qdivisionid" CHAR(20);
  DECLARE "qdepartmentid" CHAR(2);
  DECLARE "qlink" CHAR(100);
  DECLARE "cnt" INTEGER;
  SET "ptype" = "nullif"("trim"("ptype"),'');
  SET "ppersonid" = "nullif"("trim"("ppersonid"),'');
  SET "pemploymentid" = "nullif"("trim"("pemploymentid"),'');
  SET "pvacancyid" = "nullif"("trim"("pvacancyid"),'');
  SET "pplacementid" = "nullif"("trim"("pplacementid"),'');
  SET "pprogressid" = "nullif"("trim"("pprogressid"),'');
  IF "pPlacementID" IS NOT NULL THEN
    SELECT "l"."staffid","pe"."personid","e"."employmentid","e"."companyid","l"."vacancyid","isnull"("p"."divisionid","c"."divisionid"),"v"."departmentid","string"('placement/',"pPlacementID")
      INTO "qstaffid","qpersonid","qemploymentid","qcompanyid","qvacancyid","qdivisionid","qdepartmentid","qlink"
      FROM "placement" AS "l" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" AS "e" KEY JOIN "company" AS "c","placement" AS "l" KEY JOIN "employment" AS "pe" KEY JOIN "person" AS "p" WHERE "l"."placementid" = "pPlacementID"
  ELSE IF "pProgressID" IS NOT NULL THEN
      SELECT "r"."staffid","r"."personid","e"."employmentid","e"."companyid","r"."vacancyid","isnull"("p"."divisionid","c"."divisionid"),"v"."departmentid","string"('progress/',"pProgressID")
        INTO "qstaffid","qpersonid","qemploymentid","qcompanyid","qvacancyid","qdivisionid","qdepartmentid","qlink"
        FROM "progress" AS "r" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" AS "e" KEY JOIN "company" AS "c","progress" AS "r" KEY JOIN "person" AS "p" WHERE "r"."progressid" = "pProgressID"
    ELSE IF "pVacancyID" IS NOT NULL THEN
        SELECT "v"."staffid","e"."employmentid","e"."companyid","v"."vacancyid","c"."divisionid","v"."departmentid","string"('vacancy/',"pVacancyID")
          INTO "qstaffid","qemploymentid","qcompanyid","qvacancyid","qdivisionid","qdepartmentid","qlink"
          FROM "vacancy" AS "v" KEY JOIN "employment" AS "e" KEY JOIN "company" AS "c" WHERE "v"."vacancyid" = "pVacancyID"
      ELSE IF "pPersonID" IS NOT NULL THEN
          SELECT "staffid","divisionid","string"('person/',"pPersonID")
            INTO "qstaffid","qdivisionid","qlink"
            FROM "person" WHERE "personid" = "pPersonID"
        END IF END IF END IF END IF;
  SET "ppersonid" = "isnull"("ppersonid","qpersonid");
  SET "pemploymentid" = "isnull"("pemploymentid","qemploymentid");
  SET "pvacancyid" = "isnull"("pvacancyid","qvacancyid");
  IF "ppersonid" IS NULL AND "pemploymentid" IS NULL THEN
    SET "pemploymentid" = (SELECT FIRST "employmentid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid");
    IF "pemploymentid" IS NULL THEN
      SET "ppersonid" = (SELECT FIRST "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid");
      IF "ppersonid" IS NULL THEN
        RETURN
      END IF END IF END IF;
  IF "ptype" IS NOT NULL THEN
    CALL "RequestEmailStaffNotification"("ptype","string"("ptitle",'<br/>',"pdescription"),"qLink","qDepartmentID","qDivisionID")
  END IF;
  IF "qcompanyid" IS NOT NULL THEN
    SET "cnt" = 0;
    FOR "fetchfor" AS "fetchcursor" no scroll cursor FOR
      SELECT "staffid" AS "xstaffid","isnull"("trim"("MessageType"),'') AS "xtype" FROM "IQXNetMessageCompanyRecipient" WHERE "companyid" = "qcompanyid" AND("xtype" = '' OR "xtype" = "ptype") FOR READ ONLY do
      INSERT INTO "diary"( "diaryid","staffid","personid","employmentid","vacancyid","placementid","progressid","diaryfrom","description","notes","durationtype","popupminutes" ) VALUES
        ( "uniquekey"("xstaffid"),"xstaffid","pPersonID","pEmploymentID","pVacancyID","pPlacementID","pProgressID",CURRENT TIMESTAMP,"ptitle","pdescription",'R',0 ) ;
      SET "cnt" = "cnt"+1 END FOR;
    IF "cnt" > 0 THEN
      RETURN
    END IF END IF;
  IF "ptype" IS NOT NULL THEN
    SET "qstaffid" = "isnull"((SELECT FIRST "staffid" FROM "IQXNetMessageRecipient" WHERE "MessageType" = "ptype"),"qstaffid")
  END IF;
  IF "qstaffid" IS NULL THEN
    RETURN
  END IF;
  INSERT INTO "diary"( "diaryid","staffid","personid","employmentid","vacancyid","placementid","progressid","diaryfrom","description","notes","durationtype","popupminutes" ) VALUES
    ( "uniquekey"(''),"qstaffid","pPersonID","pEmploymentID","pVacancyID","pPlacementID","pProgressID",CURRENT TIMESTAMP,"ptitle","pdescription",'R',0 ) 
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."IQXNetPopup" IS 
{CREATE PROCEDURE IQXNetPopup 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( IN pWebUserID CHAR(20),IN pPersonID CHAR(20),IN pEmploymentID CHAR(20),IN pVacancyID CHAR(20),IN pPlacementID CHAR(20),IN pProgressID CHAR(20),IN ptype CHAR(20),IN ptitle CHAR(100),IN pdescription long VARCHAR ) 
BEGIN
  DECLARE qstaffid CHAR(20);
  DECLARE qpersonid CHAR(20);
  DECLARE qemploymentid CHAR(20);
  DECLARE qcompanyid CHAR(20);
  DECLARE qvacancyid CHAR(20);
  DECLARE qdivisionid CHAR(20);
  DECLARE qdepartmentid CHAR(2);
  DECLARE qlink CHAR(100);
  DECLARE cnt INTEGER;
  SET ptype = NULLIF(TRIM(ptype),'');
  SET ppersonid = NULLIF(TRIM(ppersonid),'');
  SET pemploymentid = NULLIF(TRIM(pemploymentid),'');
  SET pvacancyid = NULLIF(TRIM(pvacancyid),'');
  SET pplacementid = NULLIF(TRIM(pplacementid),'');
  SET pprogressid = NULLIF(TRIM(pprogressid),'');
  IF pPlacementID IS NOT NULL THEN
    SELECT l.staffid,pe.personid,e.employmentid,e.companyid,l.vacancyid,isnull(p.divisionid,c.divisionid),v.departmentid,string('placement/',pPlacementID)
      INTO qstaffid,qpersonid,qemploymentid,qcompanyid,qvacancyid,qdivisionid,qdepartmentid,qlink
      FROM placement AS l KEY JOIN vacancy AS v KEY JOIN employment AS e KEY JOIN company AS c,placement AS l KEY JOIN employment AS pe KEY JOIN person AS p WHERE l.placementid = pPlacementID
  ELSE IF pProgressID IS NOT NULL THEN
      SELECT r.staffid,r.personid,e.employmentid,e.companyid,r.vacancyid,isnull(p.divisionid,c.divisionid),v.departmentid,string('progress/',pProgressID)
        INTO qstaffid,qpersonid,qemploymentid,qcompanyid,qvacancyid,qdivisionid,qdepartmentid,qlink
        FROM progress AS r KEY JOIN vacancy AS v KEY JOIN employment AS e KEY JOIN company AS c,progress AS r KEY JOIN person AS p WHERE r.progressid = pProgressID
    ELSE IF pVacancyID IS NOT NULL THEN
        SELECT v.staffid,e.employmentid,e.companyid,v.vacancyid,c.divisionid,v.departmentid,string('vacancy/',pVacancyID)
          INTO qstaffid,qemploymentid,qcompanyid,qvacancyid,qdivisionid,qdepartmentid,qlink
          FROM vacancy AS v KEY JOIN employment AS e KEY JOIN company AS c WHERE v.vacancyid = pVacancyID
      ELSE IF pPersonID IS NOT NULL THEN
          SELECT staffid,divisionid,string('person/',pPersonID)
            INTO qstaffid,qdivisionid,qlink
            FROM person WHERE personid = pPersonID
        END IF
      END IF
    END IF END IF;
  SET ppersonid = isnull(ppersonid,qpersonid);
  SET pemploymentid = isnull(pemploymentid,qemploymentid);
  SET pvacancyid = isnull(pvacancyid,qvacancyid);
  IF ppersonid IS NULL AND pemploymentid IS NULL THEN
    SET pemploymentid = (SELECT FIRST employmentid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid);
    IF pemploymentid IS NULL THEN
      SET ppersonid = (SELECT FIRST personid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid);
      IF ppersonid IS NULL THEN
        RETURN
      END IF
    END IF END IF;
  IF ptype IS NOT NULL THEN
    CALL RequestEmailStaffNotification(ptype,string(ptitle,'<br/>',pdescription),qLink,qDepartmentID,qDivisionID)
  END IF;
  IF qcompanyid IS NOT NULL THEN
    SET cnt = 0;
    FOR fetchfor AS fetchcursor no scroll cursor FOR
      SELECT staffid AS xstaffid,isnull(TRIM(MessageType),'') AS xtype FROM IQXNetMessageCompanyRecipient WHERE companyid = qcompanyid AND(xtype = '' OR xtype = ptype) FOR READ ONLY do
      INSERT INTO diary( diaryid,staffid,personid,employmentid,vacancyid,placementid,progressid,diaryfrom,description,notes,durationtype,popupminutes ) VALUES
        ( uniquekey(xstaffid),xstaffid,pPersonID,pEmploymentID,pVacancyID,pPlacementID,pProgressID,CURRENT TIMESTAMP,ptitle,pdescription,'R',0 ) ;
      SET cnt = cnt+1 END FOR;
    IF cnt > 0 THEN
      RETURN
    END IF END IF;
  IF ptype IS NOT NULL THEN
    SET qstaffid = isnull((SELECT FIRST staffid FROM IQXNetMessageRecipient WHERE MessageType = ptype),qstaffid)
  END IF;
  IF qstaffid IS NULL THEN
    RETURN
  END IF;
  INSERT INTO diary( diaryid,staffid,personid,employmentid,vacancyid,placementid,progressid,diaryfrom,description,notes,durationtype,popupminutes ) VALUES
    ( uniquekey(''),qstaffid,pPersonID,pEmploymentID,pVacancyID,pPlacementID,pProgressID,CURRENT TIMESTAMP,ptitle,pdescription,'R',0 ) 
END
}