The script flags records as deleted which will then be picked up by the next deleted records clearance cron
Read more: espocrm/espocrm#1359
-
-
Save tonybyng/0ec001a14bdf4c31b79c6e53f80bcce1 to your computer and use it in GitHub Desktop.
Cleanup Emails in EspoCRM
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?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="[email protected]"; // 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