pears.DiskSpaceWarning

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
}