Synchronize Online Database

[saubz]

Hello. I have had my osCommerce store up and running now for around 5 months and everything is going well so far. The problem I knew I was going to run into was there is too much inventory to keep track of by hand.

 

I have a physical store that has its own Point of Sale system and product database. The physical store's database is constantly updated with every sale from the store.

 

What I want to know is: how I can get my Point of Sale database to synchronize with my osCommerce database? They do not need to be continuously updated. I would only need them to update once per day.

 

My Point of Sale system can output a .txt file that has all the online products, their product number, sizes, and the quantities.

 

Is there ANY way to extract the quantity numbers from this .txt file and compare/update the osCommerce SQL database with these numbers?

 

Any help would be great. Thank You.

[kgt]

I actually just helped someone with a similar situation. Take a look at these two threads and see if it gets you anywhere:

 

http://www.oscommerce.com/forums/index.php?showtopic=180961&hl=

http://www.oscommerce.com/forums/index.php?act=ST&f=11&t=183914

[saubz]

Your thread helped me out a lot. Thank you. But...

 

I have a contribution installed called Attributes Sets. Its a contribution that allows each product to have sizes associated with them and individual quantities for each size. The sizes and quantities are located in a seperate table in the online database.

 

Do you or anybody else on here know how this could be accomplished?

 

Thanks

[kgt]

The key is to know how all your database records are related. There's no magic answer, so don't expect one. You'll need to tailor a solution to fix your problem unless someone has the exact same situation (unlikely).

 

You'll need to match your attributes table with the sizes and quantities to the products table (they're probably related by products_id). Then you need to know which field in your text file uniquely identifies which product in the products table.

[saubz]

 

Ok, I hope this helps. And I hope somebody can help me.

 

I have a store database and I can create a CSV or TXT file with the values I need for my online database. The values from my store database I need are SKU, COLUMN, and ON HAND.

 

The value I need to change on my online database is called products_stock_quantity. This is the same value located in the ON HAND field in my store database. So I think that part will be easy.

 

This is the more challenging part.

 

I need to translate the SKU to products_id. The SKU is the same value found in the products_model field in the products table in the online database. And the products_id can be found by correlating it to the products_model.

 

I also need to translate COLUMN to products_stock_attributes somehow. The value of COLUMN is going to be the same as the FIRST THREE characters in the products_options_values_name field in the products_options_values table. The corresponding products_options_values_id is needed to identify the proper entry in the products_stock table.

 

Also, the products_stock_attributes field in products_stock table is defined by two numbers seperated by a dash (e.g. 3-24). The last value, after the dash, is the products_options_values_id.

 

So, in order to change the correct stock level online, products_id must be matched with the appropriate products_stock_attributes and then change the products_stock_quantity.

 

Thank You for any help available

[saubz]

Actually, the contribution that is making this more difficult is QTPro along with Attributes Sets.

 

If anybody has had a similar issue or would like to offer some help, I would really appreciate it.

 

Thank You

[saubz]

KGT was a great help for me on this subject.

 

The SQL script he gave me to update my online datatbase was as follows:

 

create temporary table if not exists products_stock_temp select products_stock_id, products_id, substring( products_stock_attributes, 1 + locate( '-', products_stock_attributes ) ) as products_options_values_id from products_stock;

create temporary table if not exists products_stock_temp2 select pst.products_stock_id, on_hand from csv c left join products p on p.products_model=c.sku inner join products_stock_temp pst on pst.products_id=p.products_id inner join products_options_values pov on left( pov.products_options_values_name, 3 )=c.`column` and pst.products_options_values_id=pov.products_options_values_id;

update products_stock ps, products_stock_temp2 pst2 set ps.products_stock_quantity = pst2.on_hand where pst2.products_stock_id = ps.products_stock_id;

drop table products_stock_temp;

drop table products_stock_temp2;

 

This works with my particular two databases. It might not work exactly with others, but this code has helped me, and it will hopefully help others with the Attribute Sets contribution who want to update their online quantities with another database.