Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 2
Real Time inventory sync for a POS
 
DrBillNye
Sr. Member
 
Avatar
Total Posts:  96
Joined:  2009-02-07
Boulder, CO
 

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 smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Dear Science Guy,

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 

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
DrBillNye
Sr. Member
 
Avatar
Total Posts:  96
Joined:  2009-02-07
Boulder, CO
 

Awesome, thanks Brent!  That’s exactly what I was having trouble pinning down so far as database config goes.  I’ll plunk around with it tonight and see how far I can get and post with my progress.

Thanks!
Al Doan
http://www.linkedin.com/in/alandoan

 
Magento Community Magento Community
Magento Community
Magento Community
 
DrBillNye
Sr. Member
 
Avatar
Total Posts:  96
Joined:  2009-02-07
Boulder, CO
 

Can anyone tell me what needs to be touched to correctly enter an order via mysql statement?

 
Magento Community Magento Community
Magento Community
Magento Community
 
kiatng
Enthusiast
 
Total Posts:  875
Joined:  2008-09-03
Kuala Lumpur, Malaysia
 

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

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

Dear Science Guy,

You ask the hard questions! I am not sure what you are trying to do? You want to create an entire order outside of Magento? Are you talking about Duplicating what your POS is already doing?

 
Magento Community Magento Community
Magento Community
Magento Community
 
DrBillNye
Sr. Member
 
Avatar
Total Posts:  96
Joined:  2009-02-07
Boulder, CO
 

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

Al Doan

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brent W Peterson
Moderator
 
Avatar
Total Posts:  3217
Joined:  2009-02-26
Minneapolis MN
 

You could take your orders through Admin. Is that what you are thinking or do you want to make something a bit “Lighter”? (I sent an Email to your gmail account)

 
Magento Community Magento Community
Magento Community
Magento Community
 
kiatng
Enthusiast
 
Total Posts:  875
Joined:  2008-09-03
Kuala Lumpur, Malaysia
 

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
DrBillNye
Sr. Member
 
Avatar
Total Posts:  96
Joined:  2009-02-07
Boulder, CO
 

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
load
Choose customer
load
Add products to order
quick load
enter in search criteria for product 1 - hit search
load
choose product 1 - add products to order
load
repeat till all products are added
assume generic customer so all the addy field and such is filled out
submit order
load

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 -
http://www.magentocommerce.com/boards/viewthread/38765/
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

Thanks for the help

 
Magento Community Magento Community
Magento Community
Magento Community
 
kiatng
Enthusiast
 
Total Posts:  875
Joined:  2008-09-03
Kuala Lumpur, Malaysia
 

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?

 
Magento Community Magento Community
Magento Community
Magento Community
 
DrBillNye
Sr. Member
 
Avatar
Total Posts:  96
Joined:  2009-02-07
Boulder, CO
 

Yeah, the bar code reader just reads in the data and puts it in any text field, so I just need the back end space, and then I can print SKUs as barcodes so I wouldn’t even need the extra field.

 
Magento Community Magento Community
Magento Community
Magento Community
 
DrBillNye
Sr. Member
 
Avatar
Total Posts:  96
Joined:  2009-02-07
Boulder, CO
 

http://devzone.pratthost.com/2008/11/03/magento-add-product-to-cart-by-sku-form/ this could be interesting.  I\’m going to play around tomorrow and see what it does

 
Magento Community Magento Community
Magento Community
Magento Community
 
kiatng
Enthusiast
 
Total Posts:  875
Joined:  2008-09-03
Kuala Lumpur, Malaysia
 

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).

 
Magento Community Magento Community
Magento Community
Magento Community
 
saho
Member
 
Avatar
Total Posts:  52
Joined:  2008-08-06
 

We use POSIM point of sale, they have a php module which brings down orders and uploads inventory.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ladle
Sr. Member
 
Avatar
Total Posts:  173
Joined:  2009-06-11
 
saho - 07 October 2010 08:25 AM

We use POSIM point of sale, they have a php module which brings down orders and uploads inventory.

How do you like that?  Looks pretty nice, did they have to custom build the Magento module, or is it out of the box?

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 2