Skip to content

Instantly share code, notes, and snippets.

@arkadiyasuratov
Created July 26, 2019 08:41
Show Gist options
  • Save arkadiyasuratov/3a9c4d10f778690b5f0b33e1ba94f699 to your computer and use it in GitHub Desktop.
Save arkadiyasuratov/3a9c4d10f778690b5f0b33e1ba94f699 to your computer and use it in GitHub Desktop.
Cleanup Emails in EspoCRM

MAKE A FULL BACKUP OF MYSQL DATABASE AND FTP

The script removes records from MySQL tables and related attachments.

Additional information

Read more: espocrm/espocrm#1359

<?php
// custom/Espo/Custom/Jobs/CleanupEmails.php
namespace Espo\Custom\Jobs;
class CleanupEmails extends \Espo\Core\Jobs\Base
{
public function run()
{
$this->cleanupEmails();
}
protected function cleanupEmails()
{
$pdo = $this->getEntityManager()->getPDO();
// $dateBefore = date('Y-m-d H:i:s', time() - 3600 * 24 * 20);
// $sql = "SELECT * FROM email WHERE deleted = 1 AND created_at < ".$pdo->quote($dateBefore);
$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 status != 'Sent' AND entity_email_address.email_address_id IS NULL
";
$sth = $pdo->prepare($sql);
$sth->execute();
while ($row = $sth->fetch(\PDO::FETCH_ASSOC)) {
$id = $row['id'];
// Remove related attachments
$attachments = $this->getEntityManager()->getRepository('Attachment')->where(array(
'parentId' => $id,
'parentType' => 'Email'
))->find();
foreach ($attachments as $attachment) {
$this->getEntityManager()->removeEntity($attachment);
}
// Delete email record
$sqlDel = "DELETE FROM email WHERE id = ".$pdo->quote($id);
$pdo->query($sqlDel);
// Delete email address from 'email_user' table
$sqlDel = "DELETE FROM email_user WHERE email_id = ".$pdo->quote($id);
$pdo->query($sqlDel);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment