Skip to content

Instantly share code, notes, and snippets.

@tonybyng
Forked from arkadiyasuratov/CleanupEmails.php
Last active July 27, 2019 10:37
Show Gist options
  • Save tonybyng/0ec001a14bdf4c31b79c6e53f80bcce1 to your computer and use it in GitHub Desktop.
Save tonybyng/0ec001a14bdf4c31b79c6e53f80bcce1 to your computer and use it in GitHub Desktop.
Cleanup Emails in EspoCRM

MAKE A FULL BACKUP OF MYSQL DATABASE AND SOURCE CODE

The script flags records as deleted which will then be picked up by the next deleted records clearance cron

Additional information

Read more: espocrm/espocrm#1359

<?php
/*
Job: CleanUpEmails
System: Espo CRM
Author: Tony Byng
Based from: Arkadywtf github gist: https://gist.github.com/arkadywtf/3a9c4d10f778690b5f0b33e1ba94f699
Following this issue: https://github.com/espocrm/espocrm/issues/1359
Description:
This will look for email records that do not have a parent
and the email wasn't sent from the system
and its before the selected date period and its not an "is_system" email
Legal Notice: This code is provided as-is, with no warranty, express or implied on fitness for any
purpose. You should only run it in safe mode until you have identified that the code
works for you and you should confirm that safe mode does what you want it to do before
running any code at all that you have downloaded from the internet.
You are responsible for your own backups, of both source code
and database and if you don't take enough backups to be able to return your system to
a stable state if something goes wrong, then it's on your own head. YOU HAVE BEEN WARNED
On the up side, if it helps. More power to open source!
*/
namespace Espo\Custom\Jobs;
use Espo\Core\Jobs\Base;
use \PDO;
class CleanUpEmails extends Base
{
public function run()
{
$safeMode = true; // switch safemode off to actually run the code when you set up as a scheduled job
$daysToKeep = 1;
$emailNotification="you@yourdomain.com"; // get a notification by email in safemode rather than on screen
// or leave empty to get result on screen if running via CLI
$this->cleanUpEmails($safeMode, $daysToKeep,$emailNotification);
// You can either call cleanUpEmails in safemode until you are happy the code is working for you
// and then simply change to live mode when you set up a cron
// or get a cron set up to call the job in safe mode on a Monday morning
// looking at a period of 7 days and then run it Monday afternoon in live mode giving you say 8 hours
// to spot any emails that should really have been associated with an account but its a new email address
// that the system doesn't recognise. Or possibly change the query to give you a week between deletion and
// notification
}
protected function cleanUpEmails($safeMode = true, $keepDays = 7,$emailNotification="")
{
/* @var PDO $pdo */
$pdo = $this->getEntityManager()->getPDO();
$dateBefore = date('Y-m-d', strtotime(" -" . $keepDays . " days"));
$notification = "";
if ($safeMode) {
$notification .= "SafeMode is ON - no deletion will take place. Looking for emails before $dateBefore\n\n";
}
// this does the original email based filtering
/* $sql = "
SELECT email.id, email.from_email_address_id
FROM email LEFT OUTER JOIN entity_email_address
ON (email.from_email_address_id=entity_email_address.email_address_id)
WHERE email.status != 'Sent' AND entity_email_address.email_address_id IS NULL
AND email.created_at < :date and email.deleted=0 and is_system=0
";*/
// we are now opting for a more generic "if it doesn't have a parent" version instead
$sql = "SELECT email.id FROM email WHERE email.status != 'Sent' AND parent_id is null and parent_type is null
AND email.created_at < :date and email.deleted=0 and is_system=0 ";
$sth = $pdo->prepare($sql);
$sth->execute([":date" => $dateBefore]);
$selectedToDelete = $sth->rowCount();
while ($row = $sth->fetch(PDO::FETCH_OBJ)) {
$id = $row->id;
$emailEntity = $this->getEntityManager()->getRepository('Email')->get($id);
if ($emailEntity) {
if (!$safeMode) {
// Delete email record. Note this just flags as deleted, there is a separate EspoCRM
// job that deletes the email and any relationships
$this->getEntityManager()->removeEntity($emailEntity);
} else {
$notification.=$emailEntity->get("name") . " :: On " . $emailEntity->get("createdAt") .
" :: Sent From: " . $emailEntity->get("fromString") . "\n";
}
}
}
if ($safeMode) {
$notification .= $selectedToDelete . " emails would be flagged as deleted and cleared in the ".
" next trash pickup if safe mode was not on\n\nFinished\n\n";
if ($emailNotification)
mail($emailNotification,"EspoCRM Email Removal Notification",
$notification,"From: ".$emailNotification);
else
echo $notification; // if not emailing, then echo to screen
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment