Deleting/archiving Old Orders Without Affecting Stock

Store Builder Questions
1

Hi,

what should I do if I want get rid of old orders, which I dont need any more...?

When I simply delete them, all items from that orders get back to stock, which is unwanted. If my goal is to lower the database size on one side and simply delete old orders on other side, Is there any kind of "archiving" or deleting orders without affecting stock?

thanks for ideas

2

In the fn_delete_order function (app/functions/fn.cart.php) replace the following line of code

fn_change_order_status($order_id, STATUS_INCOMPLETED_ORDER, ‘’, fn_get_notification_rules(array(), false)); // incomplete to increase inventory
with
//fn_change_order_status($order_id, STATUS_INCOMPLETED_ORDER, ‘’, fn_get_notification_rules(array(), false)); // incomplete to increase inventory
P.S. Make database backup just in case

3

There should be a warning notice about this, or better an option to delete or not!

We usually delete orders >5 years old as no longer required for au tax purposes.
Inventory should not change when deleting orders, if an order is cancelled, we just set status to cancelled.

Thanks for the fix.

4

In the fn_delete_order function (app/functions/fn.cart.php) replace the following line of code

fn_change_order_status($order_id, STATUS_INCOMPLETED_ORDER, '', fn_get_notification_rules(array(), false)); // incomplete to increase inventory

with

//fn_change_order_status($order_id, STATUS_INCOMPLETED_ORDER, '', fn_get_notification_rules(array(), false)); // incomplete to increase inventory

P.S. Make database backup hust in case

thanks, look like this works

5

Just curious how big your store is? Generally, deleting old orders is not needed to maintain performance.

Be sure to change that code back after you complete your current deletion effort so that inventory will continue to be handled properly.

6

Just curious how big your store is? Generally, deleting old orders is not needed to maintain performance.

Be sure to change that code back after you complete your current deletion effort so that inventory will continue to be handled properly.

Actually, I must admit that I am really referring to our main website that is still using csc v2.

Currently in the process of creating the replacement on v4 platform.

Does v4 csc handle the database differently , speeding up the loading of orders list?

With v2, it takes 62seconds to load All 5-6 years of orders, so we delete orders more 5years old.
We do set up filters to speed this up, like last 30 days etc, but sometimes we need to check a customers previous order, while on phone to them, so we need it as fast as possible.. another reason for deleting old orders.

v4 will be faster on the new vps server, but its also pointless keeping the old orders in the database,

So to adjust inventory automatically when deleting 1000's orders is a disaster for inventory control.

7

How do you load 5 years of orders in the admin UI? Don't you have the number of items per page set to something <= 100?

It shouldn't take that long under V2. A search for a specific order_id should be very quick. If you are looking it up by email, it can take a bit longer since the email field isn't indexed (though it could easily be if that's something you do frequently).

8

I have about 20000 orders and about 5-10000 is old enough I would never need them. On other side size of order_data database is biggest in eshop 160-200MB and I have some hosting limitations for whole eshop. All the time Im balancing on 80-90% of its kapacity.

Anyway this deleting should be handled via special eshop feature as deleting can (I belive) affect more eshop features (statistics, customer order history, reward points, etc.)

9

Hi tony,

Using the Search/Filter is faster, and we set up filters for each financial year, and a 30 day filter, and a few others, Generally speed is not an issue, its just on a few occasions where we want to load "all" that it becomes an issue.

Note that setting the "items per page" to <=100 has no effect on speed what so ever.

10

Sorry, I'm still confused by what you mean when you say "load all".

Note that the browser can be a huge constraint on large data sets that have select elements and/or complex html elements. It simply takes time to render and that might have nothing to do with your database. However, if you are frequently looking up orders by email address, then you might want to add an index for 'email' to the cscart_orders table. In V4 it doesn't appear to be indexed. It should be of type BTREE and NOT unique. This should also help when searching for orders by partial email address too since the whole orders table won't have to be scanned, only the index.

Timestamp is already indexed so you should see reasonable speed for anything related to order date.

The indexes of the cscart_orders table seems to be the same between V2 and V4.

11

Ive got 30,000 orders and my database is 1.5 gig, loads pretty much instantly in the back end when searching orders.

I dont usually have it set to show more than 50 per time remoteone, but loads instantly, sounds strange to me.

12

For the csc v2 store, the slow order loading is mostly an issue if loading all orders (per-page <100 has no affect).

By "load all" I mean retrieve all orders from the database, as in "View Orders" or the "All" filter.

We have filters set up to search by financial year, so its quite fast to load at the beginning, but at EOFY around june, it gets slow to load. For day to day operation we have a filter that shows current orders only, those that require shipping or other attention. These filters load the orders list quite quickly.

It was a lot slower than it is now, changing to a faster mySQL server sped things up considerably.

Its not a browser issue.

Anyway, Im leaving this topic now as I am actually referring to our v2 store. The v4 store is on a different server.

13

Why don't you search on order nr, email or customers name?

14

And how storing forever orders work with this

http://forum.cs-cart.com/topic/52025-gdpr-compliance-in-cs-cart-and-multi-vendor/?fromsearch=1

why there is no proper tool or a method to archive old order? Some of my customers are already dead I know this for a fact (children continue business) :)

Collecting and storing data in EU has its legislation, If I remember correct, this data should not be stored longer then 3 or 5 years.

15

In AU, we need to keep tax records for 5 financial years , so every 01-July we will delete 12 months of orders to save data space and speed orders retrieval from the database. We always keep .sql copy anyway.

We don't want 12 months of inventory added back in the process. Conversely, unlike some EU countries, we do not need to keep copies of cancelled orders/invoices, thus for recent orders that we cancel, we actually DO want the inventory increased as we delete the cancelled order(s). I dont think its a problem if we remember to set the orders to "cancelled" before deleteing them.

So, some cart admins will want the inventory added back and others will not.

Thus there seems to be a need for a Admin General>Settings for "Update inventory on order deletion: Yes,No, Ask"

A popup window asking if we want to update inventory as we confirm order deletion is needed?

16

I believe when you change the order status to cancelled that inventory is adjusted then so it woudn't be an issue when you delete the orders.

You could extend your my_changes addon to create a function that will delete the order data without modifying inventory. There are several tables where you would simply delete the related rows but not use the busiiness logic in the fn_delete_order() function.

Give a shout and I'll cut you a good deal. You can then just execute a single url once a year and it will leave only orders in the last 5 years from the date it is run or it could be specified to be something like "last Jan 1 - 5 years"/

17

Hi, I tried this yesterday, and it worked just fine on my test store, but on live, it was still deducting inventory and notifying customers that products were back in stock.

I suspect its a caching issue, in the case of a 'backend' php file such as fn_cart.php does it help to clear out the var/cache directory?

Thanks, Scott.

In the fn_delete_order function (app/functions/fn.cart.php) replace the following line of code

fn_change_order_status($order_id, STATUS_INCOMPLETED_ORDER, '', fn_get_notification_rules(array(), false)); // incomplete to increase inventory
with
//fn_change_order_status($order_id, STATUS_INCOMPLETED_ORDER, '', fn_get_notification_rules(array(), false)); // incomplete to increase inventory
P.S. Make database backup just in case

18

I suspect its a caching issue, in the case of a 'backend' php file such as fn_cart.php does it help to clear out the var/cache directory?

Hm... It should work, but try to clear cache just in case

19

Thank you, I had to upload the fn.cart.php file with your line commented out, and then do a graceful restart of Litespeed.

I'd advise anyone trying this to edit the fn.cart.php file and then delete one order first. Choose an order with minimal products and note the qtys you currently have. Its handy to have a product with the current qty (that matches a product in your order) in another browser tab.

Then delete the single order, refresh your product page and make sure none of the qtys increased by 1. If thats the case, you can then go through and delete multiple orders at a time.

20

Thank you for the message. We also suggest to make full database backup before testing