pears.DBCleanupOrphanTagValue

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."DBCleanupOrphanTagValue" IS 
{CREATE FUNCTION DBCleanupOrphanTagValue 
 
/* 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 tagvalue WHERE 
     (
     (((taglocation = 'P') OR (taglocation LIKE 'A%')) AND NOT EXISTS(SELECT * FROM person WHERE personid = tagvalue.id)) OR 
     ((taglocation = 'E') AND NOT EXISTS(SELECT * FROM employment WHERE employmentid = tagvalue.id)) OR 
     ((taglocation = 'C') AND NOT EXISTS(SELECT * FROM company WHERE companyid = tagvalue.id)) OR 
     ((taglocation LIKE 'V%') AND NOT EXISTS(SELECT * FROM vacancy WHERE vacancyid = tagvalue.id)) OR 
     ((taglocation LIKE 'L%') AND NOT EXISTS(SELECT * FROM placement WHERE placementid = tagvalue.id)) OR 
     ((taglocation = 'U') AND NOT EXISTS(SELECT * FROM staff WHERE staffid = tagvalue.id)) OR 
     ((taglocation = 'N') AND NOT EXISTS(SELECT * FROM contactevent WHERE contacteventid = tagvalue.id)) 
     );
    RETURN @RC
  END IF;
  DELETE FROM tagvalue WHERE((taglocation = 'P') OR(taglocation LIKE 'A%')) AND
    NOT EXISTS(SELECT * FROM person WHERE personid = tagvalue.id);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM tagvalue WHERE(taglocation = 'C') AND
    NOT EXISTS(SELECT * FROM company WHERE companyid = tagvalue.id);
  DELETE FROM tagvalue WHERE(taglocation = 'E') AND
    NOT EXISTS(SELECT * FROM employment WHERE employmentid = tagvalue.id);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM tagvalue WHERE(taglocation LIKE 'V%') AND
    NOT EXISTS(SELECT * FROM vacancy WHERE vacancyid = tagvalue.id);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM tagvalue WHERE(taglocation LIKE 'L%') AND
    NOT EXISTS(SELECT * FROM placement WHERE placementid = tagvalue.id);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM tagvalue WHERE(taglocation = 'U') AND
    NOT EXISTS(SELECT * FROM staff WHERE staffid = tagvalue.id);
  SET @RC = @RC + (SELECT @@rowcount);
  DELETE FROM tagvalue WHERE(taglocation = 'N') AND
    NOT EXISTS(SELECT * FROM contactevent WHERE contacteventid = tagvalue.id);
  SET @RC = @RC + (SELECT @@rowcount);
  CALL AuditLog('CLEANUP',NULL,string(@RC,' Unattached Question Answers deleted '),NULL,NULL);
  RETURN @RC;
END
}