#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    2
    Rep Power
    0

    Sync EPOS Database with Mysql database Problem


    Hi,

    I have an EPOS till software which I use in my business premises and have the option to sync it with my online shops database remotely. The idea being if I sell an item in my bricks and mortar shop it updates the product totals on my on-line shop and vice versa.

    The problem I have is the EPOS software needs to sync with the product price, product id and product quantity but not all this information is in one table.

    The product id and quantity are in the "ps_stock_available" table and the product id, quantity and price are in the a"ps_stock" table.

    The problem is that it's the quantity field in "ps_stock_available" that actually updates the quantities but this table does not contain the product price.

    Is there some way to enable my EPOS database and on-line database sync properly given that the information required is in 2 different tables.

    Thank you.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    try using a view that contains the necessary join query

    (except that this only syncs in one direction, from mysql out)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    try using a view that contains the necessary join query

    (except that this only syncs in one direction, from mysql out)

    Just read the help file again for the EPOS software (EPOS Excel) and the info below explains it a lot better. I hope this helps with solving the problem.

    EEOnline Web Store Synchronisation Module

    The EEOnline module is one of the "bolt-on" options available with the EPOS-Excel system. It enables you to synchronise stock level and price data between your in-store EPOS system and up to two remote web store databases.

    How does the system work?

    To explain how the system works further lets assume that you have a high street store selling computer hardware. You also sell most of your in-store products online using an online store web site. Although in effect you have two separate shops, all the stock that you sell is stored in a common location (more than likely in the store room at your high street store). So what happens if someone walks into your store to buy stock item 1001 which in this case we'll assume is a PC motherboard...

    You complete the transaction on your in-store EPOS system and the stock level is adjusted accordingly. But what about the stock level that's showing on your online store? Well the only way to reflect the new stock level would be to manually enter it using the web store admin interface. That's not too bad if you've only got a few amendments to make during the day but if you're selling larger quantities, stock level adjustments between the two systems can become a real pain.

    EEOnline is a method of automating this process. The following flow diagram shows how the EEOnline module deals with a simple shop sale.1 x stock item 1001 (PC Motherboard) sold in store.
    V
    EEOnline checks the latest stock level for item 1001 on the web store database (found to be 5 units)
    V
    Web stock level is assumed to be the most recent and is used to set the new stock level.
    V
    New stock level = web level - quantity sold in store. (5 - 1 = 4 units).
    V
    New level is now stored on BOTH in-store database and web database.


    It's important to note that ONLY stock items that appear on BOTH your web store database AND your EPOS-Excel database will be synchronised. The module will not attempt to update any online stock records that do not appear on your EPOS-Excel database.



    What happens when I sell items via my online store?

    So you may be wondering how this system works in reverse. What happens when someone makes a web purchase? Well the key to this is that the EEOnline system will always check the latest web store levels before showing you a local stock level. For example lets assume that at the start of the day your in-store database and web store database were both showing 10 units of stock item 1001. However at 10AM you sell two of these motherboards via your online store. If EEOnline is enabled, whenever you perform a stock record lookup using EPOS-Excel, the EEOnline module will download the latest stock level from your web store and show this value as the current stock level. It will also save this value locally to your EPOS-Excel database so that everything is synchronised.

    So in the above example, the EEOnline module will return a value of 8 items. This will then be shown to the till user as the current stock level.

IMN logo majestic logo threadwatch logo seochat tools logo