====== pears.DiskSpaceWarning ======
<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"."DiskSpaceWarning"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in @DiskType char(10) ) 
begin
  declare @StaffList long varchar;
  declare @PropertyName char(20);
  declare @DescriptiveName char(20);
  if "varexists"('uniqueroll') = 0 then create variable "uniqueroll" integer
  end if;
  case @DiskType when 'Main' then
    set @PropertyName = 'File';
    set @DescriptiveName = 'Database File';
    set "uniqueroll" = 1 when 'Temp' then -- Ensures different disk type warnings are not given the same diaryid
    set @PropertyName = 'TempFileName';
    set @DescriptiveName = 'Temp File';
    set "uniqueroll" = 2 when 'Log' then
    set @PropertyName = 'LogName';
    set @DescriptiveName = 'Log File';
    set "uniqueroll" = 3
  end case;
  -- Cursor for connected users with Tech Support of Full Maint Rights
  for "StaffLoop" as "StaffCursor" no scroll cursor for
    select distinct "Staff"."StaffID" as @StaffID,"Staff"."UserID"
      from "WPKAssignedRole"
        join "WPKRole" on "WPKAssignedRole"."WPKRoleID" = "WPKRole"."WPKRoleID"
        join "Staff" on "WPKAssignedRole"."OwnerID" = "Staff"."StaffID"
      where("WPKAssignedRole"."Expires" is null or "expires" > current date)
      and "isnull"("Staff"."Defunct",0) <> 1
      and "WPKRole"."WPKRoleID" in( 'MAINTENANCE','TECHSUPPORT','SERVERALERTS' ) 
      and "Staff"."StaffID" = any(select "DatabaseLoginAttempts"."StaffID" from "DatabaseLoginAttempts"
        where "DatabaseLoginAttempts"."result" = 0 and "logofftime" is null and(select "connection_property"('Number',"DataBaseLoginAttempts"."ConnectionNumber")) is not null) for read only
  do -- Build up staff list for audit log
    set @StaffList = "string"("UserID",', ',@StaffList);
    -- Insert a pop-up
    insert into "diary"( "diaryid","staffid","diaryfrom","diaryto","description","notes","durationtype","duration","PopupMinutes","PopupTime" ) values
      ( "uniquekey"("string"(@DiskType,@StaffID)),@StaffID,current timestamp,current timestamp,
      "string"('URGENT - Low Disk Space on Database Server ',"left"("db_property"(@PropertyName),2),' drive'),
      "string"('Disk space on the database server ',"left"("db_property"(@PropertyName),2),' drive is running critically low. This may cause the server to crash with the possibility of data loss if not addressed immediately. Please advise your system administrator at once.',"char"(13),'The ',@DescriptiveName,' is located at ',"db_property"(@PropertyName),' on the database server.'),'R',
      0,0,"dateadd"("second",61,current timestamp) ) end for;
  -- Log the warning
  call "AuditLog"('DISKSPACE',null,"string"('Low Temp File Disk Space Warning sent to user(s): ',@StaffList),null,null)
end
go

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

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

(in @DiskType char(10))
begin
  declare @StaffList long varchar;
  declare @PropertyName char(20);
  declare @DescriptiveName char(20);
  if varexists('uniqueroll') = 0 then create variable uniqueroll integer
  end if;
  case @DiskType when 'Main' then
    set @PropertyName='File';
    set @DescriptiveName='Database File';
    set uniqueroll=1 when 'Temp' then -- Ensures different disk type warnings are not given the same diaryid
    set @PropertyName='TempFileName';
    set @DescriptiveName='Temp File';
    set uniqueroll=2 when 'Log' then
    set @PropertyName='LogName';
    set @DescriptiveName='Log File';
    set uniqueroll=3
  end case
  ; -- Cursor for connected users with Tech Support of Full Maint Rights
  for StaffLoop as StaffCursor no scroll cursor for
    select distinct Staff.StaffID as @StaffID,Staff.UserID from
      WPKAssignedRole join
      WPKRole on WPKAssignedRole.WPKRoleID = WPKRole.WPKRoleID join
      Staff on WPKAssignedRole.OwnerID = Staff.StaffID where
      (WPKAssignedRole.Expires is null or expires > current date) and
      isnull(Staff.Defunct,0) <> 1 and
      WPKRole.WPKRoleID in( 'MAINTENANCE','TECHSUPPORT','SERVERALERTS') and
      Staff.StaffID = any(select DatabaseLoginAttempts.StaffID from DatabaseLoginAttempts
            where DatabaseLoginAttempts.result = 0 and logofftime is null and(select connection_property('Number',DataBaseLoginAttempts.ConnectionNumber)) is not null) for read only do
    -- Build up staff list for audit log
    set @StaffList=string(UserID,', ',@StaffList);
    -- Insert a pop-up
    insert into diary( diaryid,staffid,diaryfrom,diaryto,description,notes,durationtype,duration,PopupMinutes,PopupTime) values( 
      uniquekey(string(@DiskType,@StaffID)),@StaffID,current timestamp,current timestamp,
      string('URGENT - Low Disk Space on Database Server ',"left"(db_property(@PropertyName),2),' drive'),
      string('Disk space on the database server ',"left"(db_property(@PropertyName),2),' drive is running critically low. This may cause the server to crash with the possibility of data loss if not addressed immediately. Please advise your system administrator at once.',"char"(13),'The ',@DescriptiveName,' is located at ',db_property(@PropertyName),' on the database server.'),'R',
      0,0,dateadd(second,61,current timestamp)) end for;
  -- Log the warning
  call AuditLog('DISKSPACE',null,string('Low Temp File Disk Space Warning sent to user(s): ',@StaffList),null,null)
end
}
</code>