pears.DataManagementErase

CREATE FUNCTION "pears"."DataManagementErase"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN @PersonID CHAR(20),IN "DeleteAll" CHAR(1) DEFAULT 'N',IN "Anonymise" CHAR(1) DEFAULT 'N',
  IN "RemoveContactEvents" CHAR(1) DEFAULT 'N',IN "ContactEventDate" DATE DEFAULT CURRENT DATE,
  IN "RemoveDocs" CHAR(1) DEFAULT 'N',IN "RemoveComplianceDocs" CHAR(1) DEFAULT 'N',
  IN "RemovePhoto" CHAR(1) DEFAULT 'N',IN "RemoveCV" CHAR(1) DEFAULT 'N',IN "RemoveProgress" CHAR(1) DEFAULT 'N',
  IN "RemoveAvail" CHAR(1) DEFAULT 'N',IN "RemovePhone" CHAR(1) DEFAULT 'N',IN "RemoveIQXWeb" CHAR(1) DEFAULT 'N' ) 
RETURNS long VARCHAR
deterministic
BEGIN
  DECLARE "Mess" long VARCHAR;
  SET "Mess" = '';
  /* all char(1) parameters are Y or N */
  /* null for date parameters means all */
  /* this is also a data tidying tool */
  IF NOT EXISTS(SELECT * FROM "Person" WHERE "PersonID" = @PersonID) THEN
    RETURN 'Person not found'
  END IF;
  IF "DeleteAll" = 'Y' THEN
    IF EXISTS(SELECT * FROM "Temptimesheet" WHERE "PersonID" = @PersonID) THEN
      SET "Mess" = 'Timesheets exist '
    END IF;
    IF EXISTS(SELECT * FROM "Tempshift" WHERE "PersonID" = @PersonID AND "state" NOT IN( 'A','H','U' ) ) THEN
      SET "Mess" = "Mess"+'Shifts exist '
    END IF;
    IF EXISTS(SELECT * FROM "Placement" KEY JOIN "Employment" WHERE "Employment"."PersonID" = @PersonID) THEN
      SET "Mess" = "Mess"+'Placements exist '
    END IF;
    IF "Mess" > '' THEN
      RETURN "Mess"
    END IF;
    SET "Mess" = "DataManagementEraseCustom"(@PersonID);
    IF "Mess" > '' THEN
      RETURN "Mess"
    END IF;
    BEGIN atomic
      DELETE FROM "AccordEmployee" WHERE "PersonID" = @PersonID;
      DELETE FROM "Employment" WHERE "PersonID" = @PersonID;
      DELETE FROM "ExternalEmployment" WHERE "PersonID" = @PersonID;
      DELETE FROM "Pay_Employee" WHERE "PersonID" = @PersonID;
      DELETE FROM "Pay_Employment" WHERE "PersonID" = @PersonID;
      DELETE FROM "Phone" WHERE "Who" = 'P' AND "WhoID" = @PersonID;
      DELETE FROM "DataManagementLog" WHERE "PersonID" = @PersonID;
      DELETE FROM "ReferenceRequest" WHERE "PersonID" = @PersonID;
      DELETE FROM "AuditTrail" WHERE "RecordID" = @PersonID AND "AuditType" = 'PERSON';
      DELETE FROM "AuditTrail" WHERE "RecordID" = @PersonID AND "AuditType" = 'DOCUMENT';
      DELETE FROM "AWRWeeklyDetail" WHERE "PersonID" = @PersonID;
      DELETE FROM "EBTimesheet" WHERE "PersonID" = @PersonID;
      DELETE FROM "MasterRosterShift" WHERE "DefaultPersonID" = @PersonID;
      DELETE FROM "TempProvTimesheetLine" FROM "TempProvTimesheet" KEY JOIN "TempProvTimesheetline" WHERE "TempProvTimesheet"."PersonID" = @PersonID;
      DELETE FROM "TempProvTimesheetShift" FROM "TempProvTimesheet" KEY JOIN "TempProvTimesheetShift" WHERE "TempProvTimesheet"."PersonID" = @PersonID;
      DELETE FROM "TempProvTimesheet" WHERE "TempProvTimesheet"."PersonID" = @PersonID;
      DELETE FROM "Tempshift" WHERE "PersonID" = @PersonID;
      DELETE FROM "ContactEvent" WHERE "Personid" = @PersonID AND "ContactDate" < "isnull"("ContactEventDate",'2050-01-01');
      DELETE FROM "Person" WHERE "PersonID" = @PersonID;
      DELETE FROM "IQXNetUser" WHERE "IQXNetUserID" = any(SELECT "IQXNetUserID" FROM "IQXNetUserlink" WHERE "personid" = @PersonID);
      DELETE FROM "IQXNetUser" WHERE "IQXNetUserID" = any(SELECT "IQXNetUserID" FROM "IQXNetUserlink" KEY JOIN "employment" WHERE "employment"."personid" = @PersonID);
      CALL "AuditLog"('DATAMANAGE',NULL,"string"('PersonID - ',@PersonID),'','Delete All')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "RemoveContactEvents" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "ContactEvent" WHERE "Personid" = @PersonID AND "ContactDate" < "isnull"("ContactEventDate",'2050-01-01');
      CALL "AuditLog"('PERSON',@PersonID,'Contact Events Deleted before',"string"("ContactEventDate"),'')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "Anonymise" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "AccordEmployee" WHERE "PersonID" = @PersonID;
      UPDATE "Person" SET "Surname" = 'Anonymised',"forenames" = 'Anonymised',"Name" = @PersonID,"Salutation" = 'Anonymised',"Keyname" = @PersonID,
        "Addr1" = 'Anonymised',"Addr2" = 'Anonymised',"Addr3" = 'Anonymised',"Town" = 'Anonymised',"County" = 'Anonymised',"Country" = 'Anonymised',
        "NI" = NULL,"PayrollEmailAddress" = 'Anonymised',"PayrollNumber" = NULL,"TitleForPayroll" = NULL,"Postcode" = 'Anonymised',"Status" = '.',"ExtraNotes" = NULL
        WHERE "PersonID" = @PersonID;
      UPDATE "BroadbeanCandidate" SET "Surname" = 'Anonymised',"forename" = 'Anonymised',"Name" = @PersonID,"Salutation" = 'Anonymised',
        "Address" = 'Anonymised',"Town" = 'Anonymised',"Street" = 'Anonymised',"Email" = 'Anonymised',
        "Phone" = NULL,"Postcode" = 'Anonymised'
        WHERE "PersonID" = @PersonID;
      UPDATE "Pay_Employment" SET "PayrollAddr1" = 'Anonymised',"PayrollAddr2" = 'Anonymised',"PayrollAddr3" = 'Anonymised',"PayrollAddr4" = 'Anonymised',
        "PayrollPostcode" = 'Anonymised',"PayrollFullAddr" = 'Anonymised' WHERE "PersonID" = @PersonID;
      DELETE FROM "Pay_Employee" WHERE "PersonID" = @PersonID;
      DELETE FROM "phone" WHERE "whoid" = @PersonID AND "who" = 'P';
      DELETE FROM "AuditTrail" WHERE "RecordID" = @PersonID AND "AuditType" = 'PERSON';
      DELETE FROM "AuditTrail" WHERE "RecordID" = @PersonID AND "AuditType" = 'DOCUMENT';
      DELETE FROM "IQXNetUser" WHERE "IQXNetUserID" = any(SELECT "IQXNetUserID" FROM "IQXNetUserlink" WHERE "personid" = @PersonID);
      DELETE FROM "IQXNetUser" WHERE "IQXNetUserID" = any(SELECT "IQXNetUserID" FROM "IQXNetUserlink" KEY JOIN "employment" WHERE "employment"."personid" = @PersonID);
      UPDATE "DataManagementLog" SET "name" = 'Anonymised' WHERE "PersonID" = @PersonID;
      CALL "AuditLog"('PERSON',@PersonID,'Anonymised','Anonymised','')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "RemovePhone" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "phone" WHERE "whoid" = @PersonID AND "who" = 'P';
      UPDATE "person" SET "payrollemailaddress" = NULL WHERE "personid" = @PersonID;
      CALL "AuditLog"('PERSON',@PersonID,'Remove Contact Details','Remove Contact Details','')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "RemoveCV" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "Blobstore" WHERE "id" = @PersonID AND "class" = 'V';
      UPDATE "person" SET "cvtext" = 'Anonymised' WHERE "PersonID" = @PersonID;
      CALL "AuditLog"('PERSON',@PersonID,'Remove CV','Remove CV','')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "RemovePhoto" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "Blobstore" WHERE "id" = @PersonID AND "class" = 'J';
      CALL "AuditLog"('PERSON',@PersonID,'Remove Photo','Remove Photo','')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "RemoveAvail" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "tempshift" WHERE "Personid" = @PersonID AND "state" IN( 'A','H','U' ) ;
      CALL "AuditLog"('PERSON',@PersonID,'Remove Availability','Remove Availability','')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "RemoveProgress" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "progress" WHERE "Personid" = @PersonID;
      CALL "AuditLog"('PERSON',@PersonID,'Remove Progress','Remove Progress','')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "RemoveDocs" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "Blobstore" WHERE "class" = 'O' AND "id" = any(SELECT "oledocumentid" FROM "oledocument" WHERE "ownerid" = @PersonID AND "isnull"((SELECT "iscompliance"
            FROM "documenttype" WHERE "documenttypeid" = "oledocument"."documenttypeid" AND "oledocument"."documenttypeid" IS NOT NULL),0) = 0);
      DELETE FROM "oledocument" WHERE "ownerid" = @PersonID AND "isnull"((SELECT "iscompliance"
          FROM "documenttype" WHERE "documenttypeid" = "oledocument"."documenttypeid" AND "oledocument"."documenttypeid" IS NOT NULL),0) = 0;
      CALL "AuditLog"('PERSON',@PersonID,'Remove Documents','Remove Documents','')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "RemoveIQXWeb" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "IQXNetUser" WHERE "IQXNetUserID" = any(SELECT "IQXNetUserID" FROM "IQXNetUserlink" WHERE "personid" = @PersonID);
      DELETE FROM "IQXNetUser" WHERE "IQXNetUserID" = any(SELECT "IQXNetUserID" FROM "IQXNetUserlink" KEY JOIN "employment" WHERE "employment"."personid" = @PersonID);
      CALL "AuditLog"('PERSON',@PersonID,'Remove IQXWeb','Remove IQXWeb','')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  IF "RemoveComplianceDocs" = 'Y' THEN
    BEGIN atomic
      DELETE FROM "Blobstore" WHERE "class" = 'O' AND "id" = any(SELECT "oledocumentid" FROM "oledocument" WHERE "ownerid" = @PersonID AND(SELECT "isnull"("iscompliance",0)
            FROM "documenttype" WHERE "documenttypeid" = "oledocument"."documenttypeid") = 1);
      DELETE FROM "oledocument" WHERE "ownerid" = @PersonID AND(SELECT "isnull"("iscompliance",0)
          FROM "documenttype" WHERE "documenttypeid" = "oledocument"."documenttypeid") = 1;
      CALL "AuditLog"('PERSON',@PersonID,'Remove Compliance Documents','Remove Compliance Documents','')
    exception
      WHEN others THEN RETURN "ERRORMSG"()
    END END IF;
  RETURN ''
END