Don't archive all mails / delete mails that aren't connected to other items

[klues]

We are currently testing EpsoCRM for our usecase and generally really like it - thanks for your work!

One question/feature request:
I've setup a personal mail account for my user and receiving the mails from this account in general works perfect. However:

If I delete some mails from the IMAP server using another mail program (Thunderbird), these deleted mails are still there in EpsoCRM. If I open any mail in EpsoCRM it has the state "archived".
This makes sense for mails that are connected to any contact/task/item in EpsoCRM, but not for other mails. I do not want to keep track of all mails I'll ever received in EpsoCRM, but only those which are relevant to the system in terms of any relations to contacts/tasks/items.

Suggestion for implementation:

• Fetch all mails from the account as currently
• a) If mails on the IMAP server are deleted and they are not connected to any item in EpsoCRM, also delete them in EpsoCRM.
• b) If easier: Add possibility of a cron job that scans all mails and deletes those which are older than a specific date and not connected to any item in the system.

[buzzard10]

We created some kind of script which can delete all e-mails which arent assigned to items from EspoCRM like contacts and etc

[klues]

Thanks for the answer! How did you create this script? Inside EpsoCRM or a shell script? How do you find out which mails aren't related to items from EpsoCRM and how do you delete them in the script?

[tonybyng]

@b1f17773 I too would be interested in a script that cleared any emails out from the mailbox that were not related to an espo record at all. Something maybe that cleared things older than a set number of days so that recent items could be manually assigned if they've come from a new contact at a company for example.

[buzzard10]

Thanks for reply @tonybyng and @klues .
We want to share our script online but there was a problem with script. Our script also delete sented mails. So before you use that script you should fix that thing.

[klues]

Yes, please share - it's better to start with something already existing and trying to fix the problems of it than to start without any clue what to do.

[arkadiyasuratov]

@tonybyng @klues here's the code

https://gist.github.com/arkadywtf/3a9c4d10f778690b5f0b33e1ba94f699

Email addresses for Account, Contact, Lead and User are stored in entity_email_address table. So basically this script is removing all emails not related to records in these tables. I have added also a condition not to remove Sent emails, but have not tested the script.

For anyone who will stumble to this post in the future:

Please do not run this code in production and make a full backup!

If this script works for you and you haven't found any bugs you can follow this tutorial for creating a custom scheduled cron job: https://www.espocrm.com/documentation/development/scheduled-job/

[arkadiyasuratov]

@tonybyng @klues forgot to mention. EspoCRM gives the ability to run specific job directly from console #1239

For example:

php command.php RunJob CleanupEmails

I highly recommend this approach, especially if you have lots of emails in database. If you hit memory limit on your server you can increase the limit with this command

php -d memory_limit=-1 RunJob CleanupEmails

[tonybyng]

@arkadywtf @klues I'm working on some changes on it over the weekend hopefully. So will upload when I'm done. One thing I wanted to check with you as I'm relatively new to EspoCRM development, do you think its safer to delete the email and email user links using the entity rather than via PDO ? I havent checked the EspoCRM code but I assume if you delete an email via the entity delete request, it would automatically delete any and all child records relating to that entity ? So once the correct records have been selected with the query where the email is not linked to an entity, its seems potentially safer to remove the attachments, users and email via an email entity delete, if it does indeed do that. Thoughts? @yurikuzn could you confirm ?

This will be a great boost to me because i'm busy adding in loads of email "ignore" rules but this way, any I miss can be removed automatically every few days.

Im also going to add a "safe mode" option so that it will tell you what its going to delete so you can confirm the emails look like the right ones to go, before running it and then setting it up as a scheduled job when you are happy that the code is selecting the right emails to go !

[yurikuzn]

If you use $entityManager->removeEntity($email) it will set deleted = 1. After some period of time cleanup job will delete it and delete relationships.

[tonybyng]

@yurikuzn Many thanks. Thats what I was hoping

[tonybyng]

@arkadywtf @klues

https://gist.github.com/tonybyng/0ec001a14bdf4c31b79c6e53f80bcce1

I changed the code to this (forking @arkadywtf 's gist) As per the original code its looking based on email address links but it could be tweaked to look for empty parent id's either as well as or instead. I may alter it further at some point but its just flagged 1500 emails as removed for me ;-) and doing it through the official entity removal means that its not likely to break anything.

Hope that helps.

Not sure if its the full answer as I do still have some emails hanging around that I thought should have gone but it was a good first attempt, and using the "safemode" feature means we can tweak settings and look at emails that would be deleted so we can confirm it wont be removing things we don't want

[yurikuzn]

I believe such a task can be also achieved by using our Workflows tool.