The script removes records from MySQL tables and related attachments.
Read more: espocrm/espocrm#1359
The script removes records from MySQL tables and related attachments.
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); | |
} | |
} | |
} |