MySQL MSSQL stock sync update script

[Artx]

If someone`s interested, I`we made product stock update script from ERP, that uses MSSQL database. It updates product stock count in prestashop, including attributes, by comparing product reference numbers in both databases. Can be sheduled by cron to automaticly sync stock.

 

I`m using it to sync prestashop with SAGE OfficeLine.

[jaro61]

Hello Artx

 

I have interest for yor script. Please send me detail to get the script

 

JAro

[TorbenЯune]

Hi Artx,

 

I am trying to do the same type of integration between Openbravo POS and Prestashop 1.5.3.

Wolud be very interested in looking into your code

[jlsanta]

Is it posible, the integration between openbravo and prestashop?

[TorbenЯune]

It is possible. I am using Openbravo POS 2.30.2 and Prestashop 1.5.3.

Both runs in single shop mode and PS runs simple stock mode, and both runs MySQL.

I can synchonize the stock level from Openbravo to Prestashop ( and only that). Items must have the same reference number in both systems. The idea is to let Openbravo be the "stock keeper", i.e. you pull items from openbravo stock when you pack orders for Prestashop. Whe the days work is done, you sync openbravo back to prestashop. I admit it is not 100% and that items can get out of stock during the day, but it is bette than no sync at all.

Items with negative stock count is set to 0.

It is written in PHP, and must run on an Apache web server. The databases can be on different servers - only requirement is that you have network access to them.

Let me konw if you are interested, and I can send you the source.

Note! You are on you own with this one. I can not offer any support what so ever.

 

[srebella]

If someone`s interested, I`we made product stock update script from ERP, that uses MSSQL database. It updates product stock count in prestashop, including attributes, by comparing product reference numbers in both databases. Can be sheduled by cron to automaticly sync stock.

 

I`m using it to sync prestashop with SAGE OfficeLine.

 

Hello i am interested in looking your code, i am developing similar task.

Edited July 4, 2013 by srebella (see edit history)

[nisheednair]

I am interested in stock sync update script. Could you please share it to me. My email id is

[email protected]

[lcerce]

It is possible. I am using Openbravo POS 2.30.2 and Prestashop 1.5.3.

Both runs in single shop mode and PS runs simple stock mode, and both runs MySQL.

I can synchonize the stock level from Openbravo to Prestashop ( and only that). Items must have the same reference number in both systems. The idea is to let Openbravo be the "stock keeper", i.e. you pull items from openbravo stock when you pack orders for Prestashop. Whe the days work is done, you sync openbravo back to prestashop. I admit it is not 100% and that items can get out of stock during the day, but it is bette than no sync at all.

Items with negative stock count is set to 0.

It is written in PHP, and must run on an Apache web server. The databases can be on different servers - only requirement is that you have network access to them.

Let me konw if you are interested, and I can send you the source.

Note! You are on you own with this one. I can not offer any support what so ever.

 

Hello, I am searching for a way, how to sync stock from excel file. It is possible with your code? Could you please share it to me. My email is [email protected].

 

Thank you in advance!

[Kenedy]

Dropped you PM

[TorbenЯune]

 

 

Hello, I am searching for a way, how to sync stock from excel file. It is possible with your code? Could you please share it to me. My email is [email protected].

 

Thank you in advance!

 

Stock sync from excel is supported in Prestashop. Save your excel data in CSV and import them.

[vekia]

it is worth to say that you can do it under the:

advanced parameters > CSV import

tab in back office

[mauroagr]

Dear,

Some know script in php to sync erp or other software.

I have one specific erp and need developing the sincronization, then is more fast get some running and update it to my situation.

Thanks

Mauro

[Lombok Design S.L.]

I'm interested in something that syncs Prestashop 1.5.6 with Eurowin ERP software. I need something to sync both stocks, it doesn't matter if i have to duplicate products (in both platforms, Prestashop and Eurowin) but i need almost a real-time sync.

 

Could you send me more information about the usage to [email protected]? Thanks for your work! 

[timdiam]

Hi there,

 

I'm developing something very similar, could you please send me a link to your script?  Very interested to take a look at it.

 

Thank you!!

 

-Tim

[VkV]

"If someone`s interested, I`we made product stock update script from ERP, that uses MSSQL database. It updates product stock count in prestashop, including attributes, by comparing product reference numbers in both databases. Can be sheduled by cron to automaticly sync stock.

I`m using it to sync prestashop with SAGE OfficeLine."

 

Hi, i'm also interested in the script mysql mssql stock sync, my email is [email protected], thank you in advance.

Edited July 2, 2014 by VkV (see edit history)

[TorbenЯune]

OK, the code for OpenBravo to Prestashop stock sync comes here.

 

The PHP code below run under these conditions:

 

Openbravo POS must be version 2.30.2 

Prestashop must be version 1.5.3.

Both the OpenBravo and the PrestaShop must run i Single Shop Mode.

Both OpenBravo and PrestaShop must run on MySQL.

All items must have the same reference number in both systems.

The code must run on an Apache server, and the script must have execute rights.

 

How to do:

Save the code in a file called stock.php and put it somewhere where you can access it. 

You need two graphics: logo.png and rdot.png. The logo.png can be any graphics file. The rdot.png is a small red dot that is used for indicating sync errors.

 

Put the two png files in the same location as the stock.php file. 

 

Now edit this block of the stock.php code:

 

$user_name = "UsernameForAccessToTheOpenBravoSQLServer";

$password = "PasswordToTheOpenBravoSQLServer";

$database = "NameOfTheOpenBravoDatabase";

$server = 'IPAddressOfTheOpenBravoSQLServer'; 

 

$destination_user_name = "UsernameForThePrestaShopSQLServer";

$destination_password = "PasswordForThePerstaShopSQLServer";

$destination_database = "NameOfThePresatShopDatabase";

$destination_server = 'IPAddressOfThePrestaShopSQLServer';

 

and enter the correct values for the OpenBravo and the PrestaShop SQL servers respectively.

 

When you call stock.php ther are two parameters you can pass to it. One is a simple password, that is mandatory. In the code here, it is set to 1234 but you can change it to whatever (change the password in line 6 of the code).

 

You call it like:

 

stock.php?pwx=1234

 

The other parameter is a repeat parameter. You might omit it,  and if you do the script is only executer once. You calle it like:

 

stock.php?pwx=1234&refresh=60

 

which means that your browser will repeat the script every 60 seconds, as long as your browser is open.

 

This is about it. Hope you get it to work.

I can't offer you any assistance of help on this, so you are on your own from here. 

 

------ PHP code start ------

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<?PHP

$pwx=$_GET["pwx"];

if ($pwx=='1234') {

$refresh_secs=$_GET["refresh"];

$rfsh = '<meta http-equiv="refresh" content="'.$refresh_secs.';url=stock.php?refresh=' .$refresh_secs . '" />';

if (strlen($_GET["refresh"])>0) {

print $rfsh;

}

?>

<title>OpenBravo PrestaShop Stock Sync</title>

 

<meta http-equiv="Content-Type" content="text/html; " />

<style type="text/css">

body,td,th {

font-family: Tahoma, Geneva, sans-serif;

font-size: 10px;

}

body {

background-color: #CCC;

margin-left: 10px;

margin-top: 10px;

margin-right: 10px;

margin-bottom: 10px;

}

 

 

</style>

</head>

<body>

<div align="center"><img src="logo.png"></div>

<H1 align="center" style="color:gray">Stock Synchronization</H1>

<H3 align="center" style="color:gray">Openbravo POS -> Prestashop</H3>

<hr>

<?PHP

 

$test = 0;

if (strlen($_GET["test"])>0) {

$test = $_GET["test"];

}

 

$user_name = "UsernameForAccessToTheOpenBravoSQLServer";

$password = "PasswordToTheOpenBravoSQLServer";

$database = "NameOfTheOpenBravoDatabase";

$server = 'IPAddressOfTheOpenBravoSQLServer'; 

 

$destination_user_name = "UsernameForThePrestaShopSQLServer";

$destination_password = "PasswordForThePerstaShopSQLServer";

$destination_database = "NameOfThePresatShopDatabase";

$destination_server = 'IPAddressOfThePrestaShopSQLServer';

 

$antal_vare=0;

 

If ($test) {

print '<H2>Diagnostics on</H2>';

print '<h3>Openbravo POS source parameters:</h3><h4>Username: ' . $user_name . '<br>Database: ' . $database  . '<br>Server:   ' . $server . '</h4>';

print '<h3>Prestashop destination parameters:</h3><h4>Username: ' . $destination_user_name . '<br>Database: ' . $destination_database  . '<br>Server:   ' . $destination_server . '</h4>';

print '<h3>Execution parameters:</h3>';

print '<h4>Refresh time: ' . $refresh_secs . ' sek.</h4>';

print '<hr>';

}

 

$db_handle = mysql_connect($server, $user_name, $password);

$db_found = mysql_select_db($database, $db_handle);

 

/* $destination_db_handle = mysql_connect($destination_server, $destination_user_name, $destination_password);

$destination_db_found = mysql_select_db($destination_database, $destination_db_handle);

print 'Destunation query result:' . mysql_error($destination_db_handle) . "<br>";

*/

 

if ($db_found) {

 

$SQL = "SELECT PRODUCTS.NAME AS NAME, PRODUCTS.REFERENCE AS REF, STOCKCURRENT.UNITS AS STYKTAL

FROM STOCKCURRENT INNER JOIN PRODUCTS ON STOCKCURRENT.PRODUCT = PRODUCTS.ID ORDER BY STOCKCURRENT.UNITS DESC;";

$result = mysql_query($SQL,$db_handle);

 

if (!$result) {

    print 'Invalid query: ' . mysql_error($db_handle) . "<br>";

}

 

if (mysql_num_rows($result)==0) {

    print '<H1 align="center">Fejl i synkronisering.</H1><BR>';

print '<H3 align="center">Inge informationer overført</H3><BR><BR>';

}

 

?>

<table border="1" cellpadding="2" cellspacing="0">

  <tr>

    <th>#</th>

    <th>ON STOCK</th>

    <th>PRODUCT NAME</th>

    <th>REF#</th>

<?PHP

if ($test) {

print '<th>PRESTASHOP SQL</th>';

}

?>

    <th>UPDATED</th>

 

</tr>

<?PHP

 

while ( $db_field = mysql_fetch_assoc($result) ) {

$stk=$db_field['STYKTAL'];

if ($stk<0) {

$stk=0;

}

 

print '<tr><th><div align="right">' . $antal_vare . '</div></ht><th><div align="right">' . $stk . '</div></ht><th><div align="left">' . $db_field['NAME'] . '</div></ht><th><div align="right">' . $db_field['REF'] .'</div></ht>';

$destination_sql='UPDATE ps_stock_available SET quantity=' . $stk . ' WHERE id_product=' .  $db_field['REF'] . ';';

if ($test) {

print '<th>' . $destination_sql. '</th>';

}

 

print '<th><img src="rdot.png"></th>';

print '</tr>';

$antal_vare=$antal_vare+1;

}

mysql_close($db_handle);

?>

</table>

<?PHP

}

else {

print "Error! Database not found";

}

print '<hr>In total: ' . $antal_vare . ' updated stock levels<br>';

}

else {

print 'Illegal password - Execution STOP!';

}

?>

<hr>

</body>

</html>

---------- PHP code end -------

[SysTest]

If someone`s interested, I`we made product stock update script from ERP, that uses MSSQL database. It updates product stock count in prestashop, including attributes, by comparing product reference numbers in both databases. Can be sheduled by cron to automaticly sync stock.

 

I`m using it to sync prestashop with SAGE OfficeLine.

Hi there,

 

I'm interested in you code. I need to get the clients, categories, shippers and products from a MSSQL 2008R2 to feed Prestashop's mySQL. Then, keep both databases synchronized.

[gadnis]

On 2013-01-13 at 3:35 PM, Artx said:

If someone`s interested, I`we made product stock update script from ERP, that uses MSSQL database. It updates product stock count in prestashop, including attributes, by comparing product reference numbers in both databases. Can be sheduled by cron to automaticly sync stock.

 

I`m using it to sync prestashop with SAGE OfficeLine.

I'd like to have it

[cmauro]

Can you share code by email? 

 

Thx

[selectshop.at]

Gentlemen, this thread is very old. In 2013 we used PS 1.3/1.4. The script will not work anymore. Besides now webservice is integrated in Prestashop and you can use this for to sync with openerp. Furthermore there is a module available for to sync with odoo/ex openerp.

For other applications, and this was only restricted possible in PS 1.3/1.4 you have now the possibility to export your data from back-office.

@cmauro What exactly do you want to export, for which purposes ?

[cmauro]

First of all, thanks for your quick reply. 

I have an application installed on a PC in my store that uses MSSQL Database (Express Edition) and I want to synchro with Prestashop 1.7.3.1

The aim is to maintain the same stock in both sides. If a user buy online the stock must reduce in mysql and mssql, and if I sale a product in the store it must be reflected in mssql and mysql. 

I don't know how to use webservices, if you have documentation that a I can read, just tell me, or If you know any solution to reach the aim.

Thanks in advance

[selectshop.at]

1) First of all you need to create a linked server using OBDC driver. There are several tutorials over the net you can use for that.

2) For to use the webservice api on Prestashop you should create your own scripts. The webservice works via XML exchange.

Tutorials and samples of scripts you will find here:

Tutorials: http://doc.prestashop.com/display/PS17/Webservice?src=search

http://doc.prestashop.com/display/PS16/Webservice+Parameters?src=search

http://doc.prestashop.com/display/PS15/Webservice+one-page+documentation?src=search

Samples: https://github.com/PrestaShop/PrestaShop-webservice-lib/tree/master/examples