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 :-)
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 |
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
then I get 1143 rows returned
- See screenshot 1143rows.png
If I do
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
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
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
best wishes,
Danny
Concrete5 Team
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
Kind regards
But won't this leave behind a lot of order detail records in other tables?
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:
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.
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.
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?
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?
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.
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.
Correction: orders with status 0 are "abandoned" orders. Basically any time you start shopping it starts an order.
It worked liked a champ! Thanks for the code.
Very useful - thanks again Greg
Thank you so much for this super-easy and effective way to solve the problem!
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.
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.
Best wishes,
Danny
Concrete5 Core Team