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

[Salad_Dressing]

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

I call a PHP script on the Pastel server from out of a PHP script running on the web server.

Before this call, the web server initiates a session and provides a token which gets sent to the Pastel server which later forwards the same token back to the web server along with the query results.

Before the web server writes the results into the MySQL database, it first verifies the token to ensure the results were requested by itself earlier.

Because I initiate the request from the web server, I can use a cron job to repeat the request and not worry about doing that on Windows.

[bjsteyn]

I call a PHP script on the Pastel server from out of a PHP script running on the web server.

Before this call, the web server initiates a session and provides a token which gets sent to the Pastel server which later forwards the same token back to the web server along with the query results.

Before the web server writes the results into the MySQL database, it first verifies the token to ensure the results were requested by itself earlier.

Because I initiate the request from the web server, I can use a cron job to repeat the request and not worry about doing that on Windows.

Cool, can you mabe, PRETTY PLEASE, show me some sample code of the php script on the web server and the one the local server that gets this done. Will save me time if i can see some sample code. Thanx again, Salad_Dressing.

Thanx
BJ

[kleva]

bjsteyn - If your ISP can't provide a permanent IP or want to charge too much per month for IP address, then most ADSL routers support a feature called dynamic DNS, which your webserver can use (eg: bjsteynpastel.dyndns.org). There would need to be a few tweaks depending on your router and your network (firewalls) to allow access for the query. I must however point out that security is a concern here as while performing the query your data/information may be being transmitted insecurely (fixed IP or no Fixed IP). I highly suggest you investigate some form of VPN connectivity between the servers (internal & external instead). I have some clients who run their complete operations off of dynamic DNS and can then host internally to avoid the security exposure (even if you only host the pages that need to show the data).

[bjsteyn]

bjsteyn - If your ISP can't provide a permanent IP or want to charge too much per month for IP address, then most ADSL routers support a feature called dynamic DNS, which your webserver can use (eg: bjsteynpastel.dyndns.org). There would need to be a few tweaks depending on your router and your network (firewalls) to allow access for the query. I must however point out that security is a concern here as while performing the query your data/information may be being transmitted insecurely (fixed IP or no Fixed IP). I highly suggest you investigate some form of VPN connectivity between the servers (internal & external instead). I have some clients who run their complete operations off of dynamic DNS and can then host internally to avoid the security exposure (even if you only host the pages that need to show the data).

Hi Kleva, Am working on a second version of sales system for distributors that I wrote and want to make it downloadable online. I want the web features to be easy to setup with the installation of the system. The user is not going to bother to setup a fixed IP, configuring a router etc.

So maby i should try and automate cron task from local server and then send and request data from the website server. I could maby run a vbscript at startup that runs a php script every few minutes via a timer. Then use a token method for keeping track of what has been sent and received. Every time there is changes to the database there is token created on either side (local or web) and once it has been updated on the other side, the token is marked as completed. The vbscript will use an ODBC connection to check for new tokens on the MS Access system and run a php script to check for new tokens on the web server.

VPN is probably i good idee if the system is specifically developed for a company, but as I want to license the software out it is not an option. But my knowledge on VPN's is to great, so if I am missing how I can use it in my scenario then please correct me.

Thanx
BJ

[kleva]

Ok, misundertood the purpose there then.

This could be a lot trickier to deal with as if you wanted to work "live" then all the VPN & connectivity tools have to built into your application - Support nightmare. From what it sounds like you are also already trying to deal with disparate systems (Windows, Linux, VB, php, ODBC, MS Access, MySQL) adding further complexity, maybe a rethink of process to try simplify?

I have configured in many similar scenarios and none of them is easy. No matter what there will always be a customisation required if there are so many systems involved or at least a very complicated installation process. You might be solving an immediate problem but creating a nightmare for yourself later.

Eg: If your clients are garenteed to all be windows based, then you should firstly reconsider your website hosting to be Windows based (IIS) and the database on the webserver can also be MSAccess to start and migrated through to MSSQL later? I don't recommend a MS Access based database (size and speed). Alternately, maybe changing the client side operation to a MS SQL base (even express) - Then your link can be direct and scheduled within SQL?

Maybe I am still misreading the intention and objective here. If you want, give me a call, I will provide some "free for first hour" info/advice (0835579832).

https://www.kleva.co.za