Purge unused email addresses from Cerb 6.x versions

cerb670_address_purge.php

<?php
require(getcwd() . '/framework.config.php');
require(DEVBLOCKS_PATH . 'Devblocks.class.php');

// Verify we're running in CLI mode only

if(0 != strcasecmp(php_sapi_name(),'cli'))
	die("This script should only be run from the command line.\n");

$is_dryrun = true;

// Accept an argument for $is_dryrun

if(isset($argv[1]))
	$is_dryrun = ($argv[1] == '--delete') ? false : true;

$db = DevblocksPlatform::getDatabaseService();

// Create a table with all the existing address IDs that don't have a contact record or org
$db->Execute("create temporary table _tmp_address_ids select id from address where contact_person_id = 0 and contact_org_id = 0");
$db->Execute("alter table _tmp_address_ids add primary key (id)");

// Ignore addresses used as reply-to
$db->Execute("delete from _tmp_address_ids where id in (select address_id from address_outgoing)");

// Ignore worker addresses
$db->Execute("delete from _tmp_address_ids where id in (select address.id from address_to_worker inner join address on (address_to_worker.address=address.email))");

// Ignore attachments on addresses
$db->Execute("delete from _tmp_address_ids where id in (select context_id from attachment_link where context = 'cerberusweb.contexts.address')");

// Ignore addresses with comments
$db->Execute("delete from _tmp_address_ids where id in (select context_id from comment where context = 'cerberusweb.contexts.address')");

// Ignore addresses that own comments
//$db->Execute("delete from _tmp_address_ids where id in (select owner_context_id from comment where owner_context = 'cerberusweb.contexts.address')");

// Ignore addresses with context links or watchers
$db->Execute("delete from _tmp_address_ids where id in (select from_context_id from context_link where from_context = 'cerberusweb.contexts.address')");
$db->Execute("delete from _tmp_address_ids where id in (select to_context_id from context_link where to_context = 'cerberusweb.contexts.address')");

// Ignore addresses with scheduled behaviors
$db->Execute("delete from _tmp_address_ids where id in (select context_id from context_scheduled_behavior where context = 'cerberusweb.contexts.address')");

// Ignore addresses w/ opps
$db->Execute("delete from _tmp_address_ids where id in (select primary_email_id from crm_opportunity)");

// Ignore addresses w/ custom field values set
$db->Execute("delete from _tmp_address_ids where id in (select context_id from custom_field_clobvalue where context = 'cerberusweb.contexts.address')");
$db->Execute("delete from _tmp_address_ids where id in (select context_id from custom_field_numbervalue where context = 'cerberusweb.contexts.address')");
$db->Execute("delete from _tmp_address_ids where id in (select context_id from custom_field_stringvalue where context = 'cerberusweb.contexts.address')");

// Ignore addresses that own fieldsets
$db->Execute("delete from _tmp_address_ids where id in (select owner_context_id from custom_fieldset where owner_context = 'cerberusweb.contexts.address')");

// Ignore addresses that gave feedback
$db->Execute("delete from _tmp_address_ids where id in (select quote_address_id from feedback_entry)");

// Ignore addresses that own html templates
$db->Execute("delete from _tmp_address_ids where id in (select owner_context_id from mail_html_template where owner_context = 'cerberusweb.contexts.address')");

// Ignore addresses that sent messages
$db->Execute("delete _tmp_address_ids from _tmp_address_ids inner join message on (message.address_id=_tmp_address_ids.id)");

// Ignore addresses that are notification targets
$db->Execute("delete from _tmp_address_ids where id in (select context_id from notification where context = 'cerberusweb.contexts.address')");

// Ignore addresses that are requesters
$db->Execute("delete _tmp_address_ids from _tmp_address_ids inner join requester on (_tmp_address_ids.id=requester.address_id)");

// Ignore addresses that own snippets
$db->Execute("delete from _tmp_address_ids where id in (select owner_context_id from snippet where owner_context = 'cerberusweb.contexts.address')");

// Ignore addresses that are shared in the Support Center
$db->Execute("delete from _tmp_address_ids where id in (select share_address_id from supportcenter_address_share)");
$db->Execute("delete from _tmp_address_ids where id in (select with_address_id from supportcenter_address_share)");

// Ignore addresses involved with tickets
$db->Execute("delete from _tmp_address_ids where id in (select first_wrote_address_id from ticket)");
$db->Execute("delete from _tmp_address_ids where id in (select last_wrote_address_id from ticket)");

// Ignore addresses that own VAs
$db->Execute("delete from _tmp_address_ids where id in (select owner_context_id from virtual_attendant where owner_context = 'cerberusweb.contexts.address')");

// Ignore worker addresses
$db->Execute("delete from _tmp_address_ids where id in (select address.id from address inner join worker on (worker.email=address.email))");

// Finish count
$count_tmp = $db->GetOne("select count(id) from _tmp_address_ids");

if($count_tmp) {
	if($is_dryrun) {
		echo sprintf("%d unused email addresses would be purged.\n", $count_tmp);
		
	} else {
        $db->Execute("delete from address where id in (select id from _tmp_address_ids)");
		echo sprintf("Purged %d unused email addresses.\n", $count_tmp);
	}
	
} else {
	echo sprintf("No unused email addresses were found.\n", $count_tmp);
}

// Drop the temporary table
$db->Execute("drop table _tmp_address_ids");