====== pears.EscalatePopups ======
<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"."EscalatePopups"()
/* Application Maintained Function / Procedure - DO NOT EDIT*/
begin
  declare "idum" smallint;
  if not exists(select * from "params" where "EscalatePopups" = 1) then
    return
  end if;
  begin
    create variable "uniqueroll" smallint
  exception
    when others then set "idum" = 0
  end;
  for "forlab" as "curs" insensitive cursor for
    select "diary"."diaryid" as "xxxdiaryid","diary"."staffid" as "xxxstaffid","staff"."userid" as "xxxuserid",
      "datediff"("minute",current timestamp,"diaryfrom") as "xxxpopmins","OriginalDiaryID" as "xxxOriginalDiaryID","WhoEntered" as "xxxWhoEntered" from "diary" key join "staff"
      where "diary"."popuptime" <= "dateadd"("minute",-5,current timestamp) and "canescalate" is null and "diary"."staffid" = any(select "origstaffid" from "popupescalation") for read only do
    if "xxxpopmins" between-32700 and 32700 then
      insert into "diary"( "diaryid","staffid","personid","vacancyid","employmentid","progressid","placementid","diaryfrom","diaryto",
        "description","notes","durationtype","duration","popupminutes","OriginalDiaryID","WhoEntered" ) (
        select "uniquekey"("string"("diaryid","TargetStaffID")),"targetstaffid","personid","vacancyid","employmentid","progressid","placementid","diaryfrom","diaryto",
          "description","string"('ESCALATED from ',"xxxuserid","char"(13),"char"(10),"notes"),
          "durationtype","duration","xxxpopmins",
          "isnull"("xxxOriginalDiaryID","xxxDiaryID"), //if this is the first escalation xxxOriginalDiaryID will be null
          "xxxWhoEntered"
          from "diary","popupescalation"
          where "diary"."diaryid" = "xxxdiaryid"
          and "popupescalation"."origstaffid" = "xxxstaffid"
          and not exists(select "DiaryID" from "Diary" where "StaffID" = "TargetStaffID" and "OriginalDiaryID" = "xxxOriginalDiaryID")); // Prevents multiple reminders for circular escalations 
      -- clear pop-up time on escalated pop-ups and indicate where escalated
      update "diary" set "popupminutes" = null,"notes" = "string"('ESCALATED to ',
        (select "list"("staff"."userid") from "staff" where "popupescalation"."targetstaffid" = "staff"."staffid"),
        "char"(13),"char"(10),"notes") from
        "diary","popupescalation"
        where "diaryid" = "xxxdiaryid"
        and "popupescalation"."origstaffid" = "xxxstaffid"
        and not exists(select "DiaryID" from "Diary" where "StaffID" = "TargetStaffID" and "OriginalDiaryID" = "xxxOriginalDiaryID")
    else
      update "diary" set "popupminutes" = null where "diaryid" = "xxxdiaryid"
    end if
  end for
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."EscalatePopups" IS 
{create procedure EscalatePopups 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

()
begin
  declare idum smallint;
  if not exists(select * from params where EscalatePopups = 1) then
    return
  end if;
  begin
    create variable uniqueroll smallint
  exception
    when others then set idum=0
  end;
  for forlab as curs insensitive cursor for
    select diary.diaryid as xxxdiaryid,diary.staffid as xxxstaffid,staff.userid as xxxuserid,
      datediff(minute,current timestamp,diaryfrom) as xxxpopmins,OriginalDiaryID as xxxOriginalDiaryID,WhoEntered as xxxWhoEntered from diary key join staff where
      diary.popuptime <= dateadd(minute,-5,current timestamp) and canescalate is null and diary.staffid = any(select origstaffid from popupescalation) for read only do
    if xxxpopmins between-32700 and 32700 then
      insert into diary( diaryid,staffid,personid,vacancyid,employmentid,progressid,placementid,diaryfrom,diaryto,
        description,notes,durationtype,duration,popupminutes,OriginalDiaryID,WhoEntered) (
        select uniquekey(string(diaryid,TargetStaffID)),targetstaffid,personid,vacancyid,employmentid,progressid,placementid,diaryfrom,diaryto,
          description,string('ESCALATED from ',xxxuserid,"char"(13),"char"(10),notes),
          durationtype,duration,xxxpopmins,
          isnull(xxxOriginalDiaryID,xxxDiaryID), //if this is the first escalation xxxOriginalDiaryID will be null
          xxxWhoEntered from
          diary,popupescalation where
          diary.diaryid = xxxdiaryid and
          popupescalation.origstaffid = xxxstaffid and
          not exists(select DiaryID from Diary where StaffID = TargetStaffID and OriginalDiaryID = xxxOriginalDiaryID)); // Prevents multiple reminders for circular escalations 
      -- clear pop-up time on escalated pop-ups and indicate where escalated
      update diary set popupminutes = null,notes = string('ESCALATED to ',
        (select list(staff.userid) from staff where popupescalation.targetstaffid = staff.staffid),
        "char"(13),"char"(10),notes) from
        diary,popupescalation where
        diaryid = xxxdiaryid and
        popupescalation.origstaffid = xxxstaffid and
        not exists(select DiaryID from Diary where StaffID = TargetStaffID and OriginalDiaryID = xxxOriginalDiaryID)
    else
      update diary set popupminutes = null where diaryid = xxxdiaryid
    end if end for
end
}
</code>