Question Status

Suggested Answer
dionhall85 asked a question on 23 Feb 2015 10:17 AM

Im Looking for a module that will sync our Inventory from RMS with our Online Store which uses WooCommerce.  I just want to be able to sync Inventory levels.  I have looked at POS-Sync. which showed promise but it never worked and they never responded to e-mails or help tickets.  Any help would be great!!! Thanks!

Reply
dionhall85 responded on 4 Mar 2015 12:17 PM

Any help on this?

Reply
Suggested Answer
LanceM responded on 17 Mar 2015 4:05 AM

I don't use WooCommerce so I'm not familiar with the database structure.  However, I'll explain how I synchronize inventory between RMS and my online store so hopefully you can take it from there.

You can either sync data by connecting directly to the mysql database as a linked server or you can export the inventory data into a file that you FTP to your server and then use to update mysql.

You'll need to have database fields in each database which can be used to link items.  item.itemlookupcode in RMS is the most logical and hopefully there is a field in WooCommerce in which you can add itemlookupcodes.

Linked server method:

1. create linked server:  

www.ideaexcursion.com/.../howto-setup-sql-server-linked-server-to-mysql

www.packtpub.com/.../mysql-linked-server-sql-server-2008

2. use a query like this in SQL Server Management Studio:

UPDATE w

SET w.TempeQty = i.Quantity

FROM OPENQUERY([WEBSHOP], 'SELECT itemlookupcode, quantity_field FROM mysql_database.inventory_table') w

INNER JOIN [RMSdatabase].[dbo].[Item] i WITH (NOLOCK) ON w.itemlookupcode = i.itemlookupcode

WHERE w.TempeQty <> i.Quantity;

3. once you confirm that it works you can create a .bat file to run the query via OSQL or sqlcmd and then set it up as a scheduled task. Here is an example of the contents of the .bat file:

osql -S 172.168.1.102\SQLExpress -dRMSdatabase -Usa -Ppassword  < file_containing_sql_query.sql

Data export and FTP method:

1. create a .bat file with a bcp command and FTP command. For example:

bcp "SELECT itemlookupcode, quantity FROM RMSdatabase" queryout c:\inventory_for_mysql.txt -c -k -t, -S 172.168.1.102\SQLExpress -dRMSdatabase -Usa -Ppassword

ncftpput -C -u ftp_user -p ftp_password domain_name c:\inventory_for_mysql.txt c:\inventory_for_mysql.txt

I use ncftp (http://www.ncftp.com/ncftp/) but you can use the ftp command built into Windows.  If you have a lot of items and the file is big you can compress it using a 7zip command in the .bat file.  Here is an example of what I use:

cd "C:\program files\7-zip"

7z a -tzip "c:\inventory_for_mysql.zip" "c:\inventory_for_mysql.txt"

2. create a new table in mysql that serves as a temporary place to load the inventory data before it's used to update the WooCommerce inventory table

3. Create a shell script to import the data.  If using a Linux server here is an example:

#!/bin/sh

mysql temp_database -u mysql_username -ppassword < /path/to/sql/script/inventory_data_import.sql

contents of inventory_data_import.sql:

DELETE FROM temp_database.inventory_table;

LOAD DATA LOCAL INFILE '/path/to/file/inventory_for_mysql.txt' REPLACE INTO TABLE temp_database.inventory_table FIELDS TERMINATED BY '||' LINES TERMINATED BY '\r\n';

Create a cron job for the shell script.

Hopefully this will guide you in the right direction.

Lance

Reply
Suggested Answer
LanceM responded on 17 Mar 2015 4:05 AM

I don't use WooCommerce so I'm not familiar with the database structure.  However, I'll explain how I synchronize inventory between RMS and my online store so hopefully you can take it from there.

You can either sync data by connecting directly to the mysql database as a linked server or you can export the inventory data into a file that you FTP to your server and then use to update mysql.

You'll need to have database fields in each database which can be used to link items.  item.itemlookupcode in RMS is the most logical and hopefully there is a field in WooCommerce in which you can add itemlookupcodes.

Linked server method:

1. create linked server:  

www.ideaexcursion.com/.../howto-setup-sql-server-linked-server-to-mysql

www.packtpub.com/.../mysql-linked-server-sql-server-2008

2. use a query like this in SQL Server Management Studio:

UPDATE w

SET w.TempeQty = i.Quantity

FROM OPENQUERY([WEBSHOP], 'SELECT itemlookupcode, quantity_field FROM mysql_database.inventory_table') w

INNER JOIN [RMSdatabase].[dbo].[Item] i WITH (NOLOCK) ON w.itemlookupcode = i.itemlookupcode

WHERE w.TempeQty <> i.Quantity;

3. once you confirm that it works you can create a .bat file to run the query via OSQL or sqlcmd and then set it up as a scheduled task. Here is an example of the contents of the .bat file:

osql -S 172.168.1.102\SQLExpress -dRMSdatabase -Usa -Ppassword  < file_containing_sql_query.sql

Data export and FTP method:

1. create a .bat file with a bcp command and FTP command. For example:

bcp "SELECT itemlookupcode, quantity FROM RMSdatabase" queryout c:\inventory_for_mysql.txt -c -k -t, -S 172.168.1.102\SQLExpress -dRMSdatabase -Usa -Ppassword

ncftpput -C -u ftp_user -p ftp_password domain_name c:\inventory_for_mysql.txt c:\inventory_for_mysql.txt

I use ncftp (http://www.ncftp.com/ncftp/) but you can use the ftp command built into Windows.  If you have a lot of items and the file is big you can compress it using a 7zip command in the .bat file.  Here is an example of what I use:

cd "C:\program files\7-zip"

7z a -tzip "c:\inventory_for_mysql.zip" "c:\inventory_for_mysql.txt"

2. create a new table in mysql that serves as a temporary place to load the inventory data before it's used to update the WooCommerce inventory table

3. Create a shell script to import the data.  If using a Linux server here is an example:

#!/bin/sh

mysql temp_database -u mysql_username -ppassword < /path/to/sql/script/inventory_data_import.sql

contents of inventory_data_import.sql:

DELETE FROM temp_database.inventory_table;

LOAD DATA LOCAL INFILE '/path/to/file/inventory_for_mysql.txt' REPLACE INTO TABLE temp_database.inventory_table FIELDS TERMINATED BY '||' LINES TERMINATED BY '\r\n';

Create a cron job for the shell script.

Hopefully this will guide you in the right direction.

Lance

Reply