Clear Test Orders - Getting Ready for Live

Permalink Browser Info Environment
After setting up eCommerce module for the first time, I have a dozen or so orders in the orders history section.

What is the reccomended way to clear the test orders before the site goes live? I'm assuming deleting directly from the DB is not cool, so thought best to ask :-)

Type: Discussion
Status: In Progress
c5bruceandwalker
View Replies:
CC3381 replied on at Permalink Reply
CC3381
Go into the DB and locate the table named CoreCommerceOrders and delete the records you do not want to keep.

Best wishes,

Danny
Concrete5 Core Team
c5bruceandwalker replied on at Permalink Reply 3 Attachments
c5bruceandwalker
In the dashboard I see 11 orders (see attached screenshot dashboardorders.png )

Orders 000001 -> 000009 are test orders
Orders 000010 & 000011 are real orders

If I do
SELECT * FROM CoreCommerceOrders

then I get 1143 rows returned
- See screenshot 1143rows.png

If I do
SELECT * FROM CoreCommerceOrders WHERE oStatus >0

then i get 11 rows returned
- See screenshot 11rows.png

If i cross reference then I need to keep

Order 000010 = OrderID 1000
Order 000011 = OrderID 1054

Now for my question:

Why are there 1132 rows in CoreCommerceOrders with oStatus = zero ?


Kind regards
Paul

concrete5 Version 5.4.2.2
eCommerce 2.0.8
Zone Based Shipping 1.1
CC3381 replied on at Permalink Reply
CC3381
The extra rows are generated by every instance of the cart. They will have a 0 value unless the order is completed. There are safe to delete and should cause you no issues if you do.

best wishes,
Danny
Concrete5 Team
c5bruceandwalker replied on at Permalink Reply
c5bruceandwalker
Could those oStatus=zero rows be used as an indicator of the number of carts being started, but never making it to checkout? Would you say these were all human created carts?

Kind regards
waterfeller replied on at Permalink Reply
waterfeller
But won't this leave behind a lot of order detail records in other tables?
GregJoyce replied on at Permalink Reply 1 Attachment
GregJoyce
Yes just deleting the order table would leave some stuff hanging around. Unzip this file in the site root then visithttp://[your site]/index.php/tools/remove_orders/

This is the code:
<?php
Loader::model('order/list','core_commerce');
$orderList = new CoreCommerceOrderList();
$orders = $orderList->get(10);
while(count($orders)) {
   foreach($orders as $order) {
      $order->delete($order->getOrderID());
   }
   $orders = $orderList->get(10);
}


It will remove all orders. For obvious reasons, you will absolutely want to make sure you delete the file tools/remove_orders.php when you are done with it.
waterfeller replied on at Permalink Reply
waterfeller
I did run that and the order list and profile/order history now show up empty. However, I notice that there are still plenty of records left in the database:

CoreCommerceOrderAttributeValues 971 records
CoreCommerceOrderDownloadableFiles 2459 records
CoreCommerceOrderInvoiceNumbers 1291 records
CoreCommerceOrderProducts 401 records
CoreCommerceOrders 2709 records (All with oStatus=0)
CoreCommerceOrderSearchIndexAttributes 151 records

There may be other dead records lurking elsewhere.

How important is it to have a facility to purge old order records. Doesn't the size of the database affect performance? What are risks of leaving orphan records in the database?
GregJoyce replied on at Permalink Reply
GregJoyce
Orders with status 0 are orders that have not shipped yet. For an SQL table in general, this is not very many orders, things start to get dodgy when you are into the many-millions.

Also, the data in these orders is not considered in productList->get() so it is not impacting the system in terms of PHP performance. So you should be just fine if you are no longer seeing test orders in your dashboard.
GregJoyce replied on at Permalink Reply
GregJoyce
Correction: orders with status 0 are "abandoned" orders. Basically any time you start shopping it starts an order.
sogren replied on at Permalink Reply
sogren
It worked liked a champ! Thanks for the code.
jamesleech replied on at Permalink Reply
jamesleech
Very useful - thanks again Greg
littlestone replied on at Permalink Reply
littlestone
Thank you so much for this super-easy and effective way to solve the problem!
ianj replied on at Permalink Reply
ianj
I know this is an old post but I just thought I would add one small caveat to this tool, which I have just used to remove previous orders from a database. I removed around 9,000 order numbers so the system would re-start from Order No.1.

What I did not realise was that the table CoreCommerceOrderInvoiceNumbers was unaffected by the tools deletion of the order numbers.

New customers entering an order for the first time were finding the address details of a complete stranger pre-filled in their Billing Form as the system was pulling previous invoice details from the relationship between Order Numbers and Invoice Numbers. This was a potential confidentiality issue and cause my client some concerns. My quick get around was to add a value to the CoreCommerceOrders table starting at 10,000 and allowing it to auto-increment up from that number, thereby not pulling up data from previous completed invoices.

concrete5 Environment Information

Browser User-Agent String

Hide Post Content

This will replace the post content with the message: "Content has been removed by an Administrator"

Hide Content

Request Refund

You have not specified a license for this support ticket. You must have a valid license assigned to a support ticket to request a refund.