pears.DBCleanupOrphanRecentObjects

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."DBCleanupOrphanRecentObjects" IS 
{CREATE FUNCTION DBCleanupOrphanRecentObjects 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( IN countordo CHAR(1))
RETURNS INTEGER
BEGIN
  DECLARE @RC INTEGER;
  SET @RC = 0;
  IF countordo = 'C' THEN
    SELECT COUNT(*) INTO @RC FROM RecentObjects WHERE 
     (
    (NOT EXISTS(SELECT * FROM staff WHERE staffid = RecentObjects.staffid)) OR 
     ((objecttype = 'P') AND NOT EXISTS(SELECT * FROM person WHERE personid = RecentObjects.objectid)) OR 
     ((objecttype = 'C') AND NOT EXISTS(SELECT * FROM company WHERE companyid = RecentObjects.objectid)) OR 
     ((objecttype = 'V') AND NOT EXISTS(SELECT * FROM vacancy WHERE vacancyid = RecentObjects.objectid)) OR 
     ((objecttype = 'L') AND NOT EXISTS(SELECT * FROM placement WHERE placementid = RecentObjects.objectid)) OR 
     ((objecttype = 'N') AND NOT EXISTS(SELECT * FROM contactevent WHERE contacteventid = RecentObjects.objectid)) OR 
     ((objecttype = 'R') AND NOT EXISTS(SELECT * FROM progress WHERE progressid = RecentObjects.objectid)) OR 
     ((objecttype = 'E') AND NOT EXISTS(SELECT * FROM employment WHERE employmentid = RecentObjects.objectid)) 
     );
    RETURN @RC
  END IF;
  DELETE FROM RecentObjects WHERE
    NOT EXISTS(SELECT * FROM staff WHERE staffid = RecentObjects.staffid);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM RecentObjects WHERE(objecttype = 'P') AND
    NOT EXISTS(SELECT * FROM person WHERE personid = RecentObjects.objectid);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM RecentObjects WHERE(objecttype = 'C') AND
    NOT EXISTS(SELECT * FROM company WHERE companyid = RecentObjects.objectid);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM RecentObjects WHERE(objecttype = 'V') AND
    NOT EXISTS(SELECT * FROM vacancy WHERE vacancyid = RecentObjects.objectid);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM RecentObjects WHERE(objecttype = 'L') AND
    NOT EXISTS(SELECT * FROM placement WHERE placementid = RecentObjects.objectid);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM RecentObjects WHERE(objecttype = 'N') AND
    NOT EXISTS(SELECT * FROM contactevent WHERE contacteventid = RecentObjects.objectid);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM RecentObjects WHERE(objecttype = 'R') AND
    NOT EXISTS(SELECT * FROM progress WHERE progressid = RecentObjects.objectid);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM RecentObjects WHERE(objecttype = 'E') AND
    NOT EXISTS(SELECT * FROM employment WHERE employmentid = RecentObjects.objectid);
  SET @RC = @RC + (SELECT @@rowcount);
  CALL AuditLog('CLEANUP',NULL,string(@RC,' Unattached Recent Objects deleted '),NULL,NULL);
  RETURN @RC;
  /* ignore type T */
END
}