Cleaning Magento sales related information
June 22nd, 2012

The other day one of my customers came to me asking to create a duplicate of one of his web-sites running Magento 1.5.1.
After everything has been moved, among the other things that my customer wanted to be done was an obvious request to dump all sales related information from new web-site’s database, like orders, invoices, shipments, credit notes, customers, transaction, etc.

“Googling” didn’t help much as mostly there were solutions for only a part of the problem.

So here’s my guide on how to get rid of everything that pertains to sales.

First things are first.
Let’s get rid of all existing orders…
Create a file named for example orders_cleanup.php in the root of your Magento installation.
Open it in a editor of your choice and paste the following code:

<?php
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Varien_Profiler::enable();
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('default');
Mage::register('isSecureArea', 1);
//until here you gained access to the Magento models. The rest is custom code
 
$sales_orders = Mage::getModel('sales/order')->getCollection()->getData(); //get collection of orders

$max_i = isset($_GET['i']) ? $_GET['i'] : count($sales_orders); //set the max number of orders to process from the query string parameter `i`. if not set all the orders will be deleted

$i = 0; //initialize order counter;

foreach($sales_orders as $sales_order){// loop through orders
	$i++; //increment the orders counter
    $orderId = 0; //initialize orderId variable
    $orderId = $sales_order['entity_id']; // set orderId variable to the order entity_is from DB
	$order = Mage::getModel('sales/order')->load($orderId); //load order by order id NOT the increment_id
	echo nl2br('------------------ Iteration #'.$i.' ----------------'.PHP_EOL);
	echo nl2br('Processing order: '.$orderId.PHP_EOL); 
	$invoices = $order->getInvoiceCollection(); //get the collection of invoices for given order
	$creditnotes = $order->getCreditmemosCollection(); //get the collection of credit memos for given order
	$shipments = $order->getShipmentsCollection(); //get the collection of shipments for given order
	echo nl2br('Invoices found :'.count($invoices).PHP_EOL);
	$j = 0; //initialize deleted items counter
	$k = 0; //initialize failed items counter
	foreach ($invoices as $invoice){//loop through invoices collection
		try{
			$invoice->delete(); //try to delete invoice
			$j++; //increment deleted items counter
		}catch(Exception $e){
                        echo nl2br("Invoice could not be remvoved: ".$e->getMessage().PHP_EOL); //log the exception
			$k++; //increment failed items counter
		}
	}
	if (count($invoices)) //if there were any invoices to process
		echo nl2br('Invoices deleted: '.$j.'; Invoiced skipped: '.$k.PHP_EOL); //log processing results
 	echo nl2br('Creditnotes found: '.count($creditnotes).PHP_EOL);
 	$j = 0; $k = 0; //resetting deleted and failed items counters
	foreach ($creditnotes as $creditnote){//loop through credit notes collection
		try{
			$creditnote->delete(); //try to delete credit note
			$j++; //increment deleted items counter
		}catch(Exception $e){
                        echo nl2br("Credit note could not be remvoved: ".$e->getMessage().PHP_EOL); //log the exception
			$k++; //increment failed items counter
		}
	}
	if (count($creditnotes)) //if there were any credit notes to process
		echo nl2br('Creditnotes deleted: '.$j.'; Creditnotes skipped: '.$k.PHP_EOL); //log processing results
	echo nl2br('Shipments found :'.count($shipments).PHP_EOL);
 	$j = 0; $k = 0; //resetting deleted and failed items counters
	foreach ($shipments as $shipment){//loop through credit notes collection
		try{
			$shipment->delete(); //try to delete shipment
			$j++; //increment deleted items counter
		}catch(Exception $e){
                        echo nl2br("Shipment could not be remvoved: ".$e->getMessage().PHP_EOL); //log the exception
			$k++; //increment failed items counter
		}
	}
	if (count($shipments)) //if there were any shipments to process
		echo nl2br('Shipments deleted: '.$j.'; Shipments skipped: '.$k.PHP_EOL); //log processing results
	
   try{
		$order->delete(); //finally let's try deleting orders
        echo nl2br("Order #".$orderId." is removed".PHP_EOL); // log success
    }catch(Exception $e){
        echo nl2br("Order #".$orderId." could not be remvoved: ".$e->getMessage().PHP_EOL); //log exception if any
    }

	if ( $i >= $max_i ) break; //break looping if max number of orders to process reached
}
printf('Batch of '.ngettext('%d order', '%d orders', $max_i).' has been completed.', $max_i); //log batch completion message
?>

Now save the file and update if needed on remote server.
To run this script simply open the browser and navigate to http://your_magento_site.com/orders_cleanup.php?i=5 to delete first 5 orders from the DB.
If you need to delete more than that adjust value of `i` query string variable you’re passing.
In case you’d like to go ahead and remove everything at once remove query string. The URL should look like this http://your_magento_site.com/orders_cleanup.php.
Examine the onscreen log to mage sure everything went well.
By running this script you deleted only orders and their respect invoices, shipments and credit notes.
But there are still plenty of information available in both database and you Magento admin panel about the sales activity.
To remove the remainder you’ll have to execute the following SQL script against your database.

truncate table `sales_flat_creditmemo_comment`;
truncate table `sales_flat_creditmemo_grid`;
truncate table `sales_flat_creditmemo_item`;
truncate table `sales_flat_invoice_comment`;
truncate table `sales_flat_invoice_grid`;
truncate table `sales_flat_invoice_item`;
truncate table `sales_flat_shipment_comment`;
truncate table `sales_flat_shipment_grid`;
truncate table `sales_flat_shipment_item`;
truncate table `sales_flat_shipment_track`;
truncate table `sales_flat_shipment_item`;
truncate table `sales_flat_shipment_item`;
truncate table `sales_flat_shipment_item`;
truncate table `sales_flat_shipment_item`;
truncate table `sales_bestsellers_aggregated_daily`;
truncate table `sales_bestsellers_aggregated_monthly`;
truncate table `sales_bestsellers_aggregated_yearly`;
truncate table `sales_flat_order_address`;
truncate table `sales_flat_order_grid`;
delete from `sales_flat_order_item`;
truncate table `sales_flat_order_payment`;
truncate table `sales_flat_order_status_history`;
truncate table `sales_flat_quote`;
truncate table `sales_flat_quote_address`;
truncate table `sales_flat_quote_address_item`;
truncate table `sales_flat_quote_item`;
truncate table `sales_flat_quote_item_option`;
truncate table `sales_flat_quote_payment`;
truncate table `sales_flat_quote_payment`;
truncate table `sales_invoiced_aggregated`;
truncate table `sales_invoiced_aggregated_order`;
truncate table `sales_order_aggregated_created`;
truncate table `sales_payment_transaction`;
truncate table `sales_refunded_aggregated`;
truncate table `sales_refunded_aggregated_order`;
truncate table `sales_shipping_aggregated`;
truncate table `sales_shipping_aggregated_order`;
TRUNCATE TABLE `customer_address_entity`;
TRUNCATE TABLE `customer_address_entity_datetime`;
TRUNCATE TABLE `customer_address_entity_decimal`;
TRUNCATE TABLE `customer_address_entity_int`;
TRUNCATE TABLE `customer_address_entity_text`;
TRUNCATE TABLE `customer_address_entity_varchar`;
TRUNCATE TABLE `customer_entity`;
TRUNCATE TABLE `customer_entity_datetime`;
TRUNCATE TABLE `customer_entity_decimal`;
TRUNCATE TABLE `customer_entity_int`;
TRUNCATE TABLE `customer_entity_text`;
TRUNCATE TABLE `customer_entity_varchar`;
DELETE FROM `aw_rma_entity`;
DELETE FROM `aw_rma_entity_comments`;

Please, note that some tables don’t get truncated but rather their records are being deleted. That is because those tables have constraints on them restricting these tables from being truncated.

That’s all.
Your database now should be clean of any sales related information.

Tags: , , , , , , ,
No Comments
Leave a Reply
You must be logged in to post a comment.