Jump to content

  • You cannot start a new topic
  • You cannot reply to this topic

Deleting/archiving Old Orders Without Affecting Stock Rate Topic   - - - - -

 
  • markuson
  • Junior Member
  • Members
  • Join Date: 08-May 11
  • 30 posts

Posted 23 April 2018 - 08:51 AM #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



 
  • eComLabs
  • CS-Cart Expert
  • Authorized Reseller
  • Join Date: 27-Jan 14
  • 19373 posts

Posted 23 April 2018 - 01:16 PM #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

GET A FREE QUOTE | CS-Cart Add-ons | CS-Cart Licenses | CS-Cart Development | CS-Cart Design | Server Configuration | UniTheme and YOUPI
CS-Cart                USD 345     Multi-Vendor              USD 1250    CS-Cart RU                         24500 руб.
CS-Cart Ultimate  USD 775     CS-Cart + YOUPI      USD 545      CS-Cart RU + UniTheme    36000 руб.


 
  • remoteone
  • Member
  • Members
  • Join Date: 06-Oct 09
  • 727 posts

Posted 23 April 2018 - 02:12 PM #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.



 
  • markuson
  • Junior Member
  • Members
  • Join Date: 08-May 11
  • 30 posts

Posted 23 April 2018 - 04:28 PM #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



 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 11302 posts

Posted 23 April 2018 - 10:46 PM #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.


EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • remoteone
  • Member
  • Members
  • Join Date: 06-Oct 09
  • 727 posts

Posted 24 April 2018 - 01:24 AM #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.



 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 11302 posts

Posted 24 April 2018 - 01:55 AM #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).


EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • markuson
  • Junior Member
  • Members
  • Join Date: 08-May 11
  • 30 posts

Posted 24 April 2018 - 07:34 AM #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.) 



 
  • remoteone
  • Member
  • Members
  • Join Date: 06-Oct 09
  • 727 posts

Posted 24 April 2018 - 05:52 PM #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.



 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 11302 posts

Posted 24 April 2018 - 06:02 PM #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.


EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • johnbol1
  • Never Re
  • Members
  • Join Date: 23-Feb 10
  • 4551 posts

Posted 24 April 2018 - 06:17 PM #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.


Custom printed hi visibility clothing sale the UK's online hivis safety shop
v4.5.2


 
  • remoteone
  • Member
  • Members
  • Join Date: 06-Oct 09
  • 727 posts

Posted 25 April 2018 - 02:55 AM #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.



 
  • Flow
  • Super Duper and Amazingly Sexy Senior
  • Members
  • Join Date: 13-Oct 10
  • 2289 posts

Posted 25 April 2018 - 08:11 AM #13

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


When life hands you lemons, bring on the Tequila baby!


 
  • Darius
  • Douchebag
  • Members
  • Join Date: 20-Apr 08
  • 3268 posts

Posted 25 April 2018 - 09:40 AM #14

And how storing forever orders work with this

http://forum.cs-cart...r/?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.



 
  • remoteone
  • Member
  • Members
  • Join Date: 06-Oct 09
  • 727 posts

Posted 25 April 2018 - 03:11 PM #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?



 
  • tbirnseth
  • CS Cart Expert
  • Authorized Reseller
  • Join Date: 08-Nov 08
  • 11302 posts

Posted 25 April 2018 - 05:32 PM #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"/


EZ Merchant Solutions: Custom (USA based) B2B Development, Consulting, Development and Special Projects (get a quote here).
Commercial addons, payment methods and modifications to meet your business and operations needs.


 
  • Scott_C
  • Senior Member
  • Members
  • Join Date: 23-Jun 11
  • 452 posts

Posted 09 December 2018 - 03:34 AM #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

 



 
  • eComLabs
  • CS-Cart Expert
  • Authorized Reseller
  • Join Date: 27-Jan 14
  • 19373 posts

Posted 10 December 2018 - 06:13 AM #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


GET A FREE QUOTE | CS-Cart Add-ons | CS-Cart Licenses | CS-Cart Development | CS-Cart Design | Server Configuration | UniTheme and YOUPI
CS-Cart                USD 345     Multi-Vendor              USD 1250    CS-Cart RU                         24500 руб.
CS-Cart Ultimate  USD 775     CS-Cart + YOUPI      USD 545      CS-Cart RU + UniTheme    36000 руб.


 
  • Scott_C
  • Senior Member
  • Members
  • Join Date: 23-Jun 11
  • 452 posts

Posted 21 December 2018 - 02:09 PM #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.



 
  • eComLabs
  • CS-Cart Expert
  • Authorized Reseller
  • Join Date: 27-Jan 14
  • 19373 posts

Posted 21 December 2018 - 02:15 PM #20

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


GET A FREE QUOTE | CS-Cart Add-ons | CS-Cart Licenses | CS-Cart Development | CS-Cart Design | Server Configuration | UniTheme and YOUPI
CS-Cart                USD 345     Multi-Vendor              USD 1250    CS-Cart RU                         24500 руб.
CS-Cart Ultimate  USD 775     CS-Cart + YOUPI      USD 545      CS-Cart RU + UniTheme    36000 руб.