Please help us to make AbanteCart Ideal Open Source Ecommerce Solution for everyone.

Support AbanteCart eCommerce

Author Topic: Permanently reduce price of all items in store  (Read 5836 times)

Offline calbashmc

  • Jr. Member
  • **
  • Posts: 52
  • Karma: +8/-0
    • View Profile
Permanently reduce price of all items in store
« on: October 28, 2013, 01:43:13 AM »
Is there a way I can reduce the selling price of everything in a category (or across all items) without having to go into each product item?

For example, if I want to permanently reduce everything I sell by 10%, and I have hundreds of items, it will take forever to go to each one and change the price.  I can't just use a discount code as I want the new price to be permanent and not just a special.

Offline junkyard

  • Full Member
  • ***
  • Posts: 127
  • Karma: +25/-0
    • View Profile
Re: Permanently reduce price of all items in store
« Reply #1 on: October 28, 2013, 03:12:29 PM »
hi, calbashmc!  Afaik, there's no such feature,  and the only relatively easy way could be to make a direct update in the database -- fast if you are familiar with MySQL,  but very dangerous if you are not..
You will have to make DB backup first, and then access DB from command line (or MyLittleAdmin, or otherwise). All product prices are stored in the table called  ab_products,  and the links to the categories - in the table ab_products_to_categories.   
    You can list your prices all products in category with something like this:
# select product_id, model, sku, price from ab_products where product_id in (select product_id from ab_products_to_categories where category_id = 100);
   And update (change) the prices for ALL products in this category with something like this:
#  update ab_products set price=price*0.9 where product_id in (select product_id from ab_products_to_categories where category_id = 100);
   where 100 is an example of Category ID that can be found (along with the cat names) in a table called ab_category_descriptions, you have to find it by
# select * from ab_category_descriptions;
 Hope it helps a bit, you get the idea - here are external source for the syntax and MySQL manuals for learning (choose your sql version):
http://dev.mysql.com/doc/refman/5.5/en/
http://dev.mysql.com/doc/refman/5.5/en/select.html
http://dev.mysql.com/doc/refman/5.5/en/update.html
     Nevertheless, in general it might be risky to modify SQL directly, as a small human error can lead to a serious inconsistency and a disaster (you might inadvertently update the whole table, or a wrong table, or forget updating another table which is related and this would affect how your shop operates...), so this is at your own risk..  :-\    Alas, in UI there's no such feature yet..
   Maybe developers have better way,  e.g.  it  could be a CLI utility (script) to be run with different options on the server command line,  for making complex and unusual operations by the admins (just an idea).

Offline calbashmc

  • Jr. Member
  • **
  • Posts: 52
  • Karma: +8/-0
    • View Profile
Re: Permanently reduce price of all items in store
« Reply #2 on: October 28, 2013, 07:02:15 PM »
Thanks for the suggestion junkyard.  I actually discovered another way to do this which is easier for me as my sql skills are limited.

In admin under System | Data | Import / Export I exported some line items (for a test sample).  I opened these in LibreOffice Calc (I use Ubuntu as my OS - LibreOffice is an open source MS Office alternative with LibrOffice Calc = MS Office Excel).  In Calc I create a new column next to the price column and use the formula =SUM(M2*0.9) (where M2 is the column where price is.  I then just replace the price colum with the values in this new column, delete the new column, save the file as csv, then import back into Abantecart from the admin menu.  It updates all pricing to 10% less.

Another way to do this would be to create a new column and then use the Cost column to add the desired percentage then replace the Price column with values in this new column. Just depends which way you want to go, reduce prices by a percentage, or do a recalc adding a percentage to the cost price.

In fact doing this makes it faster to add new products.  I can now add products and just put the cost price, then when I'm finished I do the export, do a bulk calculation to add my percentage as per above, then import.

I hope this helps others.  Or if anyone has a better way I'd love to hear it too.

Offline junkyard

  • Full Member
  • ***
  • Posts: 127
  • Karma: +25/-0
    • View Profile
Re: Permanently reduce price of all items in store
« Reply #3 on: October 29, 2013, 03:56:04 AM »
Great idea, calbashmc!  Really,  this is a much safer and easier way to do this!
We have completely forgot  :-[ that Import also allows us to do mass update of the data,  not just inserting the new products, as we are used to do (we also use LibreOffice Calc by the way).
     http://www.abantecart.com/ecommerce-documentation/59-admin-user-manual/system/data/55-importexport#Example%201:%20update
On that link ^ we read for the products' update the default action will be "update" during import, so one doesn't even have to add Action column for this (as you have proved it),  so the whole bulk update effort for prices can be limited to:
-  export 1 table :  Product > product_descriptions ,
- delete all  columns but 2  (for convenience sake):    products.product_id  &  products.price,
( you may want to keep other columns like products.model if you like-- their values will be re-updated on import with you CSV values so there will be no change unless you changed some in the csv, in addition to prices ).
- modify prices (or else), save it as csv and reimport as usual.
 Thanks for shedding the light on it, calbashmc!

Offline flyn

  • Full Member
  • ***
  • Posts: 142
  • Karma: +8/-0
    • View Profile
Re: Permanently reduce price of all items in store
« Reply #4 on: June 26, 2014, 05:53:37 PM »
I know this is old topic but if I read correctly if you do an export of existing products and then make some changes, even add a few new items, then import no duplicates are created?

That is fantastic as my last cart system was so dumb in that regard. So basically everything just gets updated and anything new is an addition.

This is great!

Offline Basara

  • Administrator
  • Hero Member
  • *****
  • Posts: 5797
  • Karma: +274/-2
    • View Profile
Re: Permanently reduce price of all items in store
« Reply #5 on: June 27, 2014, 01:22:57 AM »
I know this is old topic but if I read correctly if you do an export of existing products and then make some changes, even add a few new items, then import no duplicates are created?

Hello.

Basically You can choose update or create new. To insert new products using import tool you need to add new column named action in the exported file and remove id. See short import/export manual

 

Powered by SMFPacks Social Login Mod