automatic price & stock updates from Pastel Partner to MySQL website?

[Salad_Dressing]

Thanks again Neville! The correction hit the mark!

I have run tests against what Pastel reports is the Stock on Hand and it all matches except for unposted orders.

The formula below calculates Stock on Hand which does not cater for unposted orders. This means that if the order filling part of your operation does not run frequent updates, then the online stock may become incorrect and possibly cause frustration and anger with Internet customers (as well as with those having to deal with the customers).

One of the accounting ladies mentioned that Stock Available (instead of Stock on Hand) may take unposted orders into consideration and provide a more accurate stock count although (a) I'm not sure about this and (b) I don't know how to calculate Stock Available. Any best practice insight / advice / info on this?

For those interested, here's the working formula for Stock on Hand as implemented in PHP:

$qO = (int)odbc_result($rs, "OpeningQty");

$qB01 = (int)odbc_result($rs, "QtyBuyThis01");
$qB02 = (int)odbc_result($rs, "QtyBuyThis02");
$qB03 = (int)odbc_result($rs, "QtyBuyThis03");
$qB04 = (int)odbc_result($rs, "QtyBuyThis04");
$qB05 = (int)odbc_result($rs, "QtyBuyThis05");
$qB06 = (int)odbc_result($rs, "QtyBuyThis06");
$qB07 = (int)odbc_result($rs, "QtyBuyThis07");
$qB08 = (int)odbc_result($rs, "QtyBuyThis08");
$qB09 = (int)odbc_result($rs, "QtyBuyThis09");
$qB10 = (int)odbc_result($rs, "QtyBuyThis10");
$qB11 = (int)odbc_result($rs, "QtyBuyThis11");
$qB12 = (int)odbc_result($rs, "QtyBuyThis12");
$qBL = (int)odbc_result($rs, "QtyBuyLast");

$qA01 = (int)odbc_result($rs, "QtyAdjustThis01");
$qA02 = (int)odbc_result($rs, "QtyAdjustThis02");
$qA03 = (int)odbc_result($rs, "QtyAdjustThis03");
$qA04 = (int)odbc_result($rs, "QtyAdjustThis04");
$qA05 = (int)odbc_result($rs, "QtyAdjustThis05");
$qA06 = (int)odbc_result($rs, "QtyAdjustThis06");
$qA07 = (int)odbc_result($rs, "QtyAdjustThis07");
$qA08 = (int)odbc_result($rs, "QtyAdjustThis08");
$qA09 = (int)odbc_result($rs, "QtyAdjustThis09");
$qA10 = (int)odbc_result($rs, "QtyAdjustThis10");
$qA11 = (int)odbc_result($rs, "QtyAdjustThis11");
$qA12 = (int)odbc_result($rs, "QtyAdjustThis12");
$qAL = (int)odbc_result($rs, "QtyAdjustLast");

$qS01 = (int)odbc_result($rs, "QtySellThis01");
$qS02 = (int)odbc_result($rs, "QtySellThis02");
$qS03 = (int)odbc_result($rs, "QtySellThis03");
$qS04 = (int)odbc_result($rs, "QtySellThis04");
$qS05 = (int)odbc_result($rs, "QtySellThis05");
$qS06 = (int)odbc_result($rs, "QtySellThis06");
$qS07 = (int)odbc_result($rs, "QtySellThis07");
$qS08 = (int)odbc_result($rs, "QtySellThis08");
$qS09 = (int)odbc_result($rs, "QtySellThis09");
$qS10 = (int)odbc_result($rs, "QtySellThis10");
$qS11 = (int)odbc_result($rs, "QtySellThis11");
$qS12 = (int)odbc_result($rs, "QtySellThis12");
$qSL = (int)odbc_result($rs, "QtySellLast");

$qOH = $qO + $qBL+$qB01+$qB02+$qB03+$qB04+$qB05+$qB06+$qB07+$qB08+$qB09+$qB10+$qB11+$qB12 + $qAL+$qA01+$qA02+$qA03+$qA04+$qA05+$qA06+$qA07+$qA08+$qA09+$qA10+$qA11+$qA12 - $qSL-$qS01-$qS02-$qS03-$qS04-$qS05-$qS06-$qS07-$qS08-$qS09-$qS10-$qS11-$qS12; 

[Neville Bailey]

One of the accounting ladies mentioned that Stock Available (instead of Stock on Hand) may take unposted orders into consideration and provide a more accurate stock count although (a) I'm not sure about this and (b) I don't know how to calculate Stock Available. Any best practice insight / advice / info on this?

Quantities of Purchase Orders and Sales Orders outstanding can be accessed in the HistoryLines data table. You can filter for Purchase Orders and Sales Orders on the DocumentType field using the criteria of 106 and 102 respectively.

Perhaps you can then join the MultiStoreTrn and HistoryLines tables using the ItemCode field as the link, and then modify your formula accordingly? The only complication is that, in the HistoryLines table, a particular ItemCode could appear in multiple records.

[Salad_Dressing]

Quantities of Purchase Orders and Sales Orders outstanding can be accessed in the HistoryLines data table. You can filter for Purchase Orders and Sales Orders on the DocumentType field using the criteria of 106 and 102 respectively.

Perhaps you can then join the MultiStoreTrn and HistoryLines tables using the ItemCode field as the link, and then modify your formula accordingly? The only complication is that, in the HistoryLines table, a particular ItemCode could appear in multiple records.

Thank you, Neville!

I'm honestly not brave enough for this right now. I'll keep it in mind should it be required in the future.

I'm pleased to have a working solution for Stock on Hand. The unposted orders issue forces operational efficiency in the warehouse - a far greater value than more accurate PHP code.

Now to incorporate the live price and stock code into the website front-end...

[bjsteyn]

Hi Salad_Dressing, how do you find the IP address that you are remote connecting to, or do you need to use something like LogMeIn?

[Salad_Dressing]

Hi Salad_Dressing, how do you find the IP address that you are remote connecting to, or do you need to use something like LogMeIn?

Hi bjsteyn, I'm assuming you are wanting to connect via the Internet and not on a LAN.

Let's also call your Pastel machine the "server".

You need to know if your ISP (Internet Service Provider) provides you with a fixed IP address. Your server needs a fixed IP address for your scripts to correctly and consistently access your server.

From the server, using a browser, you can use this free Open Port Check Tool to not only see your external facing IP address but also to check the open ports required for remote access. There are also other methods you can use to determine router outward facing IP address by Googling "find ip address router."

Note that if your server is connected to the Internet via a Router, then accessing the outward facing IP of the router still does not grant access to your server.

Let me know what your setup is (network, hardware, OS, ) and I'll see if I can be of assistance.

[bjsteyn]

Hi @Salad_Dressing, my problem is the following. I have written a sales system and am wanting to integrate it with Pastel and add Web features to it. For Pastel i am looking at the moment to just do an export. I want to my system to send sales data and target data to a mysql database that is on a web server. The sales manager can then use it for setting targets and checking progress on sales vs budget, as-well as drilling down on data. Reps can use it to check there progress and daily sales figures as-well. Latest customer pricing / login information will also be sent to web server and customers can place there orders online, check on order delivery status, as-well as check on outstanding invoices, promotions etc.

I am thinking of running a timer on my ms access system that runs a php script to send and fetch data from the web server. Alternatively, I would also like it if I could run a cron task on the web server to remote access the ms access database on LAN Server via ODBC and then fetch and send data to the ms access database.

Any thoughts on this will be appreciated.

Thanx

[Salad_Dressing]

Hi @Salad_Dressing, my problem is the following. I have written a sales system and am wanting to integrate it with Pastel and add Web features to it. For Pastel i am looking at the moment to just do an export. I want to my system to send sales data and target data to a mysql database that is on a web server. The sales manager can then use it for setting targets and checking progress on sales vs budget, as-well as drilling down on data. Reps can use it to check there progress and daily sales figures as-well. Latest customer pricing / login information will also be sent to web server and customers can place there orders online, check on order delivery status, as-well as check on outstanding invoices, promotions etc.

I am thinking of running a timer on my ms access system that runs a php script to send and fetch data from the web server. Alternatively, I would also like it if I could run a cron task on the web server to remote access the ms access database on LAN Server via ODBC and then fetch and send data to the ms access database.

Any thoughts on this will be appreciated.

Thanx

A fairly easy setup is to install XAMPP on your Pastel server, thereby turning it into a web server with PHP. These PHP scripts are then used to locally access your Pastel (or other) database using the installed ODBC drivers.

In the meanwhile your remote web server also runs PHP but does not need to have ODBC drivers installed since it sends a request to the Pastel server (which does a local database query = very fast) and then receives the data again, parsing it and updating the web server database.

All that is required is a cron job on the remote web server to trigger the PHP script and for the Pastel server to be online.

[bjsteyn]

Hi Salad_Dressing, I use WAMP (same thing) . I have used WAMP before to run PHP Scripts locally on my ms access database via ODBC. It is just the remote connection to the ODBC database that I nead a clear picture on.

If it is as fast as you say, i believe you :-) , then I could just make my website write and query data directly, and no need for an MySQL database on the Web Server. Wamp/Xampp has then no influence of the functioning of the remote connection or is it needed to make odbc available to remote web connections. I still need an IP then to remote connect. How do i go about setting up the IP. And if you can just explain to me what XAMPP has to do with the remote connection.

Thanx
BJ

[Salad_Dressing]

Hi Salad_Dressing, I use WAMP (same thing) . I have used WAMP before to run PHP Scripts locally on my ms access database via ODBC. It is just the remote connection to the ODBC database that I nead a clear picture on.

If it is as fast as you say, i believe you :-) , then I could just make my website write and query data directly, and no need for an MySQL database on the Web Server. Wamp/Xampp has then no influence of the functioning of the remote connection or is it needed to make odbc available to remote web connections. I still need an IP then to remote connect. How do i go about setting up the IP. And if you can just explain to me what XAMPP has to do with the remote connection.

Thanx
BJ

Yes, you can query your Pastel server without having a MySQL database on a remote web server. I use both because the Pastel server only runs 08h00 to 17h00 weekdays while the web server is 24/7. So I duplicate the essential data onto the web server's database and then benefit from excellent up time. The other benefit is that if the Pastel server is down for whatever reason, then the web server is still representing the company to the customers out there. A further reason is that the partially duplicated data is actually an offsite backup of some of the most essential business data.

I personally have zero interest in trying to physically keep a server running, maintained and redundant, not in South Africa, not in any first-world country for that matter. Web and cloud servers provide an essential service in this regard.

WAMP / XAMPP has got nothing to do with the ODBC driver set up. PHP does have ODBC commands built in which work if you already have the ODBC drivers installed and the database properly exposed. Once set up, it is a simple matter of doing an SQL query and then whatever else in PHP to parse and get the data out.

Yes, you need a static IP for remote connections. You need to contact your ISP for this.

What OS is your Pastel machine running?

[bjsteyn]

Use Windows 7. I ( But should work on Win Xp as-well )

So you use wamp/xamp on the local server to send data from your odbc database to your MySQL database on the Web Server and also to fetch data
from the Web Server and insert it on the local server odbc database.

Do you use a specific program to simulate cron tasks, as windows task scheduler can't be configured to run eg. every few minutes?

I agree an online duplicate database, looks like is the better option.

Thanx BJ