Post by nardus » Fri Sep 13, 2019 7:32 pm

How can I find and delete customers with no orders the last 5 years?

I'm using OpenCart 2.3.0.2

New member

Posts

Joined
Wed Dec 05, 2012 2:54 am

Post by uksitebuilder » Sun Sep 15, 2019 5:47 pm

You could do it with a script.

IMPORTANT - Backup your database before using this script

create a file in your favourite text editor with the following code and save it as whatever.php - Upload it to your store root and run it in a web browser

Code: Select all

<?php
set_time_limit(0);
require_once('config.php');
require_once(DIR_SYSTEM . 'startup.php');

// Registry
$registry = new Registry();

// Loader
$loader = new Loader($registry);
$registry->set('load', $loader);

// Config
$config = new Config();
$registry->set('config', $config);

// Database 
$db = new DB(DB_DRIVER, DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
$registry->set('db', $db);

// Create array of all customer IDs and array of customers to be deleted

$customers = array();
$customers_delete = array();

$query = $db->query("SELECT `customer_id` FROM `" . DB_PREFIX . "customer`");

foreach($query->rows as $c){
	$customers[] = $c['customer_id']; 
}


// Create array of customers who have ordered in last 5 years

$customers_5years = array();

$query = $db->query("SELECT DISTINCT `customer_id` FROM `" . DB_PREFIX . "order` WHERE date_added <= DATE_SUB(NOW(),INTERVAL 5 YEAR)");

foreach($query->rows as $c){
	$customers_5years[] = $c['customer_id']; 
}


// Work out which customers to delete

$customers_delete = array_diff($customers, $customers_5years);


foreach($customers_delete as $cid){
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "address` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_activity` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_online` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_search` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_wishlist` WHERE `customer_id` = " . $cid);
	$query = $db->query("UPDATE `" . DB_PREFIX . "order` SET `customer_id` = '0' WHERE `customer_id` = " . $cid);
}

ImageImageImageImageImage

For Friendly Professional Support - Click Here


User avatar
Guru Member

Posts

Joined
Thu Jun 09, 2011 11:37 pm
Location - United Kindgom

Post by thekrotek » Sun Sep 15, 2019 6:37 pm

uksitebuilder wrote:
Sun Sep 15, 2019 5:47 pm

Code: Select all

foreach($customers_delete as $cid){
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "address` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_activity` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_history` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_ip` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_online` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_reward` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_search` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_transaction` WHERE `customer_id` = " . $cid);
	$query = $db->query("DELETE FROM `" . DB_PREFIX . "customer_wishlist` WHERE `customer_id` = " . $cid);
	$query = $db->query("UPDATE `" . DB_PREFIX . "order` SET `customer_id` = '0' WHERE `customer_id` = " . $cid);
}
You don't need to request customers and then use array_diff(). One single query should be enough:

$query = $db->query("SELECT c.customer_id FROM `" . DB_PREFIX . "customer` AS c LEFT JOIN `" . DB_PREFIX . "order` AS o ON (c.customer_id = o.order_id AND o.date_added <= DATE_SUB(NOW(),INTERVAL 5 YEAR)) WHERE o.order_id IS NULL");

You also don't need foreach() clause, because you can do everything in a single run, if this:

= " . $cid);

Is replaced with this:

IN (" . implode(',', array_colum($query->rows, 'customer_id').")");

Here's even a single liner for one of the queries:

$db->query("DELETE FROM `" . DB_PREFIX . "address` WHERE `customer_id` IN (SELECT GROUP_CONCAT(c.customer_id) FROM `" . DB_PREFIX . "customer` AS c LEFT JOIN `" . DB_PREFIX . "order` AS o ON (c.customer_id = o.order_id AND o.date_added <= DATE_SUB(NOW(),INTERVAL 5 YEAR)) WHERE o.order_id IS NULL)");

Might require to run this before executing the main queries:

$this->db->query("SET SESSION group_concat_max_len = 1000000");

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am

Who is online

Users browsing this forum: Google [Bot] and 22 guests