====== Housekeeping ======

---- plugin ----

description: A plugin to delete old records from the phplist database.
author     : Duncan Cameron
type       : 
lastupdate : 2017-09-17
compatible : phplist 3
depends    : 
conflicts  : 
similar    : 
tags       : 

sourcerepo : https://github.com/bramley/phplist-plugin-housekeeping
donationurl: 
----

====== Purpose ======
This plugin deletes old and unused records from various tables, either single tables or a group of related tables.
The plugin provides a page that can be invoked through menu System > Housekeeping or as a cron job.

==== Messages ====

Rows will be deleted from the ''message'' table that have a status of 'sent' and whose sent date is earlier than the current date less the configuration threshold value.
Rows will also be deleted from all tables related to the ''message'' table, these are

  * ''linktrack_ml''
  * ''linktrack_uml_click''
  * ''listmessage''
  * ''message_attachment''
  * ''message_data''
  * ''usermessage''
  * ''user_message_bounce''
  * ''user_message_forward''
  * ''user_message_view''

==== Event log ====

Rows will be deleted from the ''eventlog'' table whose date field is earlier than the current date less the configuration threshold value.

==== Bounces ====

Rows will be deleted from the ''bounce'' table whose date field is earlier than the current date less the configuration threshold value. Related rows will be deleted from the ''user_message_bounce'' table.

Rows will also be deleted from the ''bounce'' table that relate to subscribers who are marked as blacklisted.

Rows will also be deleted from the ''user_message_bounce'' table whose related record in the ''bounce'' table no longer exists..

Rows will be deleted from the ''bounceregex_bounce'' table that refer to bounces which no longer exist.

==== Linktrack forward ====

Rows will be deleted from the ''linktrack_forward'' table that are no longer used (do not have a reference in either the ''linktrack_ml'' or ''linktrack_uml_click'' tables).

This can happen after deleting campaigns,and a row in ''linktrack_forward'' is no longer used by any campaigns.

==== User history ====

Deletes rows from the ''user_history'' table that are older than the threshold, but taken from each subscriber's most recent ''user_history'' record. This ensures that the user history for a subscriber will never be totally deleted.

For example, if the threshold is 6 months and the most recent ''user_history'' record for a subscriber was 2 months ago, then the records for that subscriber older than 8 months will be deleted.
====== Installation ======

See the README file on the GitHub page https://github.com/bramley/phplist-plugin-housekeeping for how to install.
===== Translations =====

The text displayed by the plugin can be translated into other languages. See the file ''plugins/HousekeepingPlugin/lan/translations_en.php'' for the English version that can be used as the basis for another language. Each line in the file contains the English text then the equivalent text in the target language.

To create a new language file, copy ''translations_en.php'' to ''translations_xx.php'', where xx is the language code, then change the texts for your target language. You can share your new language file by submitting it for inclusion in the plugin. Please create a topic in the user forum or an issue on GitHub.


====== Usage ======

===== Configuration =====

The plugin provdes a section on the Settings page where you can configure which tables should be processed.
{{:plugin:housekeeping.png?nolink&500|}}

The threshold values must be specified as a number of days, weeks, months, quarters or years, singular or plural are accepted. For example

  * 1 year
  * 10 weeks
  * 1 month
  * 60 days

===== Cron job =====

The housekeeping can be automated by running a cron job periodically, say each week or month. The cron command should be similar to this but adjusted for the command to run php and the installation directory of phplist:

<code bash>php /home/xxx/public_html/lists/admin/index.php -p process -m HousekeepingPlugin -c /home/xxx/public_html/lists/config/config.php </code>

You can also run the command as a remote page if your cron job is limited to fetching web pages and cannot run linux commands. 
The page can be fetched using a linux command such as wget or curl.

<code>wget -O housekeeping.log 'https://mysite.com/lists/admin/?page=process&pi=HousekeepingPlugin&secret=xxxxxxxx' </code>

<code>curl 'https://mysite.com/lists/admin/?page=process&pi=HousekeepingPlugin&secret=xxxxxxxx'</code>

where xxxxxxxx is the “Secret for remote processing” value from the phplist Settings page.