Sales Order – Removing All Old Sales and Customer Data

sales-order

We have a Magento v1.4.0.1 store and, yes, we know it is a very old version and would like to upgrade but the contractor that implemented this modified core code. It is tightly coupled with our ExpressionEngine CMS and has been fairly stable for about 3 yrs.

We are going to be implementing a new CMS this year so we may be able to either implement a new version of Magento or another cart but in the mean time, in order to speed up our site, we'd like to be able to archive and then delete old sales/order and customer data.
We'd like to remove all quote/order/payment info from years before 2014 and customer data, including addresses, from customers that have not placed orders since 2014.

Our code authenticates with our ERP through a web service and creates a new Magento customer when they do not exist so we are not worried about customer history because we have their order history in our ERP.

Can this be done using SQL? What queries would I run?

Best Answer

This script will remove the orders - the invoices and shipments associated with the deleted orders will be removed as well. Put this in your Magento root folder and run by navigating to the script - yourdomain.com/whatYouNamedThisScript.php

<?php
/**
 * Remove Orders
 *
 */
require 'app/Mage.php';
Mage::app('admin')->setUseSessionInUrl(false);

$salesModel = Mage::getModel("sales/order");
$salesCollection = $salesModel->getCollection();
$test_order_ids = array();
$i = 0;
/**
 * Use this version of the loop to delete ALL orders.
 */
foreach ($salesCollection as $order) {
    $orderId = $order->getIncrementId();
    array_push($test_order_ids, $orderId);
}
/**
 *  Use this version of loop to target range of orders.
 *  $i is just a counter, use it to restrict the removal to a certain total number of orders.
 *  Change the if statement in this loop to target the selected order numbers if need be.
 */
 foreach($salesCollection as $order) {
    if ($i < 100){
        $orderId = $order->getIncrementId();
        if((int)$orderId < 200){
            array_push($test_order_ids, $orderId);
        }
    }
    else{
        break;
    }
    $i++;
}
*/
foreach ($test_order_ids as $id) {
    try {
        Mage::getModel('sales/order')->loadByIncrementId($id)->delete();
        echo "order #" . $id . " is removed" . PHP_EOL;
    } catch (Exception $e) {
        echo "order #" . $id . " could not be remvoved: " . $e->getMessage() . PHP_EOL;
    }
}
echo "complete.";

If you modify the if statement in the second version of the foreach loop (currently commented out) you should be able to target any range of order IDs.

Hope that helps!

EDIT - Remove Customers

Feel free to adjust the cutoff time. This will remove all customers whose most recent order is older than the cutoff.

RemoveCustomerByOrderDate.php (place this in your magento root, navigate to it with a browser to run)

 <?php

require_once 'app/Mage.php';
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

getCustomers();

function getCustomers()
{
    //Set your cutoff date here - use / as seperator for m/d/y/ format, use - for d-m-y format.
    $cutOffDate = strtotime('1/1/2013');

    $collection = Mage::getModel('customer/customer')->getCollection()
        ->addAttributeToSelect('last_order_date');

    foreach ($collection as $customer) {
        $orderCollection = Mage::getModel('sales/order')->getCollection()
            ->addFilter('customer_id', $customer->getId())
            ->setOrder('created_at', Varien_Data_Collection_Db::SORT_ORDER_DESC)
        ;
        $newestOrder = $orderCollection->getFirstItem();

        if (strtotime($newestOrder->getCreatedAt()) < $cutOffDate) {
            $customer->delete();
            echo 'Customer '.$customer->getId().' has been deleted.<br/>';
        }
    }
    echo 'Customer Deletion Completed.';
}
Related Topic