So I run a little B&M;shop in addition to my magento. I need a way for the inventory to sync, because I hate overselling. I have tried everything I can think of. I bought eCC, and QB Pro and QB POS in order to get a syncing solution. It doesn’t work for me, I can only sync when orders are all processed, and when I have a lot of transactions (example 3 online, 3 in the local shop) I can’t sync those until all 3 online orders are processed, so it sits and waits, online thinks I have 3 left, but I’m actually sold out, and blah blah blah. It’s just not a solution. I don’t need QB per se`, I’m fine to use magento as my sole inventory manager, and I would process all orders through magento, but it takes so long. So if I have 3-4 customers in the shop, I can’t afford to take 6-10 minutes per customer to process . So all I need is something that will let me make a sale from a local cache of my database, print a receipt, then have the one time interaction with the online database where it decrements the qty. That’s it. That’s all I need it to do. I can setup a kiosk type POS and knock myself out. I can take a laptop to trade shows, whatever I want, and we’re in good shape.
So I don’t know enough to do this on my own, and if you can help at all, pick a task and help me out please, or just explain to me how to do it and I’ll build it. I’ve broken it down as follows.
I need to add a UPC attribute to the products.
- I need to add this to the normal product view so I can search by it in the admin section, I’ll just scan into the field and search
I need to be able to pull down the product ID, Title, SKU, UPC (attribute from above), price (and/or special price if there) to a local cache
- My problem here is where do I find this info on my magento database
- Also how do I determine what my last ID was so I can just run a “Update products” button and it will pull down the delta instead of everything
I need to create the program (I can do this part) to make a sale.
I need to be able to print a receipt to my receipt printer with the very simple Title or SKU, price, - subtotal, tax, total
- How can I format this in PHP so it will print?
After each sale I need to update the magento database with a query like “UPDATE qty= -1 WHERE ID=** “ if I can just do a decrement, that’s what I’m looking for.
- How do I format this query
Then I can run the credit card using my local setup, or ideally figure out a way to process that via quickbooks POS or something.
- Any suggestions on how to run the card if I was doing something standalone like this? Can I add an order to Magento db?
If you can help with any of these, please let me know. I plan on opening this up to everyone ASAP, because it’s ridiculous that we are still struggling with this and the solutions as is aren’t cutting it. Or if this is completely impossible, explain why please and save us the dissapointment
I am interested in what you find. I am going to try to impliment OpenBravo later this summer.
Here are the two tables you need to update (from what I can see)
update cataloginventory_stock_item set qty = 2 where item_id = 1
update cataloginventory_stock_status set qty = 2 where product_id = 1
Of course you would have to change the status if the qty was 0, I only tried this just now as a test and it worked on the backend, the inventory went down. You probably want to write a function in a controller to take care of any negatives. You would also have to get the existing QTY before you ran the update, since you need to know what to set the qty to.
i.e. select the qty the subtract what you have against the tables to set your new qty, if you are not using negative numbers in your Magento database then you would want to just set it to 0 and reset your status. Please keep in touch as I am slated to start in a month or so.
May be by looking at what tables are involved in deleting orders can give an idea on how to create an order, take a look at this post by Brent. http://www.magentocommerce.com/boards/viewthread/185392/#top
I’m talking about nixing the existing POS system and running all my orders through Magento, but in real time (at the end of a sale, I’d like to place the order row in magento and decrement the inventory) My in store sales are 15% and online is the remainder of my business, 15% is just enough to shaft my nightly inventory sync approach and I have to come up with something that lets it all happen in real time. Do you have any ideas to solve the same problem without maybe reinventing the wheel?
Feel free to grab me offline, my gmail / gchat is drbillnye
Hi Al & Brent, I’m interested in this and very much like to follow the discussion even though I have not anything useful to contribute. I would appreciate it if you would continue to use this board or report back any useful information here.
My initial thought on using Magento as POS is that it’ll be easier to place the order from the frontend, you can enabled the existing payment method <b>Check/Money Order</b> for his. But this is cumbersome as you’ll need to add items to cart and go through the checkout process. So what we need is a barcode reader attached to Magento and automate the checkout process.
Or we can use the existing POS and have it interface to Magento, with an API, or web services, to check / decrement the stock. Now we would have to solve the conflict problem when both online and onsite sales are happening. If priority is given to the online sales, when the item is in the cart, and when the POS query the same item, Magento would return stock qty - 1.
Sure Kiatng, so Brent is talking me out of building from scratch I think. Really the issue is for a typical order I have to go -
Create order button
Add products to order
enter in search criteria for product 1 - hit search
choose product 1 - add products to order
repeat till all products are added
assume generic customer so all the addy field and such is filled out
So with 5-6 people standing in a line waiting to check out, this feels like it takes a really really long time. So I\’m trying to figure out a way to cut out some of that load time. A lite version would be great, or a better way to search for products quickly without the search load or the add load time.
Brent suggested working to streamline an order process much as you described. I would use a generic \"THE STORE\” customer name and info, and what would suffice for me (as suggested by brent) is something like this quickorder feature http://www.midwestsupplies.com/QuickOrder-form where it would sit static and let me punch in SKUs or Barcode\’s or what have you.
Then, since I don\’t want people using the cash or check option during checkout (which they often will do) use this -
to make the payment received option only available on the backend.
Honestly this would work great if we can make it go. I can print barcode\’s to read SKU format, so the quick order approach would work - play around with it, I\’ll do the same
Check out http://www.magentocommerce.com/magento-connect/Rico+Neitzel/extension/764/paymentfilter-for-products-and-customer-groups/reviews. It can hide unwanted payment method from your customers except you.
For the quick form solution, are you able to grab the UPC/SKU from the barcode reader into the form? What barcode reader do you use and do you need to do anything with the driver?
The barcode reader + quick form method is really a cool combination for a really basic MagePOS ! I can imagine we can have several checkout counters installed too. A little more customization and it may be made into a full fledge POS. This would be great for online and onsite stores and when mCommerce comes into play where the two have to be seamlessly integrated (http://www.nytimes.com/2010/02/27/business/27shop.html).