Page 5 of 5 FirstFirst ... 345
Results 41 to 47 of 47

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

  1. #41
    Junior Member
    Join Date
    Sep 2011
    Location
    Midrand
    Posts
    22
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by bjsteyn View Post
    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.

  2. Thanks given for this post:

    bjsteyn (17-Jul-12)

  3. #42
    Silver Member bjsteyn's Avatar
    Join Date
    Jul 2010
    Location
    east lndon
    Posts
    231
    Thanks
    4
    Thanked 10 Times in 7 Posts
    Quote Originally Posted by Salad_Dressing View Post
    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

  4. #43
    Full Member
    Join Date
    Apr 2012
    Location
    KZN
    Posts
    36
    Thanks
    4
    Thanked 4 Times in 4 Posts
    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).

  5. Thanks given for this post:

    bjsteyn (19-Jul-12)

  6. #44
    Silver Member bjsteyn's Avatar
    Join Date
    Jul 2010
    Location
    east lndon
    Posts
    231
    Thanks
    4
    Thanked 10 Times in 7 Posts
    Quote Originally Posted by kleva View Post
    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

  7. #45
    Full Member
    Join Date
    Apr 2012
    Location
    KZN
    Posts
    36
    Thanks
    4
    Thanked 4 Times in 4 Posts
    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).

    www.kleva.co.za

  8. #46
    Junior Member
    Join Date
    Sep 2011
    Location
    Midrand
    Posts
    22
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by bjsteyn View Post
    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
    Sorry for the delay in answering. My recent schedule has been pressured by me coaching softball and also studying for softball umpire exam.

    Here's the PHP code snippet triggered on the remote server by Chron job (Linux machine). The token is a session token for security, and the prodCODES is a delimited text string of product codes I want prices and stock counts for. The PHP code on the Pastel server will explode the string into an array and then traverse the codes while querying the ODBC database. Note the function do_post_request() which allows large text strings to be posted without problems (I did modify the do_post_request() function very slightly, so follow Wez Furlong's link in the code to reference the original version).

    PHP Code:
    $paramsA = array(
        
    'p1' => $token,
        
    'p2' => $prodCODES
    );

    $paramsA http_build_query($paramsA);

    $responseA do_post_request("http://00.000.000.000/ODBC_get_Pastel_prices.php"$paramsA); //use server's fixed IP address here (you must take all security precautions necessary)

    function do_post_request($url$data$optional_headers null)
    {
        
    //=== function by Wez Furlong ============================================
        //=== http://wezfurlong.org/blog/2006/nov/http-post-from-php-without-curl/
        
        
    if (!isset($php_errormsg))
        {
            
    $php_errormsg '';
        }
        
        
    $params = array('http' => array(
                  
    'method' => 'POST',
                  
    'content' => $data
                
    ));
                
        if (
    $optional_headers !== null)
        {
            
    $params['http']['header'] = $optional_headers;
        }
        
        
    $ctx stream_context_create($params);
        
    $fp = @fopen($url'rb'false$ctx);
        
        if (!
    $fp)
        {
            echo 
    "<BR>could not open file >>> $url";
        }
        
        
    $response = @stream_get_contents($fp);
        
        if (
    $response === false)
        {
            echo 
    "<BR>no content response for file >>> $url";
        }
        
        return 
    $response;

    Here's the PHP code on the Pastel server (Windows machine) which does the actual query (I'm assuming you have a fairly recent PHP version and that ODBC functions are included). The results are then put into a delimited string and returned with an echo. This code does assume that an ODBC driver connected to a database is already setup on the Pastel server (the same machine executing this PHP code) and that you know that DSN (set up via Pervasive's Data Source Administrator found under Tools in the Pervasive Control Center). Also note that this code accesses Pastel price and stock codes. You may need to access something else.

    PHP Code:
    <?php

    ini_set
    ('display_errors''on');
    ini_set('max_execution_time',1800); //1800 = 30 minutes
    error_reporting(E_ALL);

    if ((isset(
    $_POST['p1'])) && (isset($_POST['p2'])))
    {
        
    $token $_POST['p1'];
        
    $prodCODES $_POST['p2'];

        
    $x 0;
        
    $priceSTR "";

        
    $prodCODE_array explode("^"$prodCODES); //explode list into array
        
    $x count($prodCODE_array);
        
        if (
    $x 0//more than 0 products code
        
    {
            
    //=== connect to the Pastel database ===
            
    $connect_string "DRIVER={Pervasive ODBC Client Interface};".
            
    "SERVERNAME=127.0.0.1;".
            
    "SERVERDSN=Your_DSN;"//Your_DSN is set up via Pervasive's Data Source Administrator found under Tools in the Pervasive Control Center

            
    $tbl "MultiStoreTrn";
            
    $fld "StoreCode,ItemCode,SellIncl01,SellIncl02,SellIncl04,SellIncl05,SellExcl01,SellExcl02,SellExcl04,SellExcl05,OpeningQty,QtyBuyThis01,QtyBuyThis02,QtyBuyThis03,QtyBuyThis04,QtyBuyThis05,QtyBuyThis06,QtyBuyThis07,QtyBuyThis08,QtyBuyThis09,QtyBuyThis10,QtyBuyThis11,QtyBuyThis12,QtyBuyThis13,QtyAdjustThis01,QtyAdjustThis02,QtyAdjustThis03,QtyAdjustThis04,QtyAdjustThis05,QtyAdjustThis06,QtyAdjustThis07,QtyAdjustThis08,QtyAdjustThis09,QtyAdjustThis10,QtyAdjustThis11,QtyAdjustThis12,QtyAdjustThis13,QtySellThis01,QtySellThis02,QtySellThis03,QtySellThis04,QtySellThis05,QtySellThis06,QtySellThis07,QtySellThis08,QtySellThis09,QtySellThis10,QtySellThis11,QtySellThis12,QtySellThis13,QtyBuyLast,QtyAdjustLast,QtySellLast";
            
            
    $conn = @odbc_connect($connect_string''''); //odbc_connect() Returns an ODBC connection id or 0 (FALSE) on error.
            
            
    if ($conn//connection OK
            
    {
                
    //echo "<BR>connection ID = $conn"; //DEBUG!
                
                //=== traverse product array and look for corresponding matches on Pastel database ===
                //$x = 20; //force small dataset for testing
                            
                
    for ($a 0$a <= $x 1$a++)
                {
                    
    $pCode $prodCODE_array[$a];

                    
    $sql "SELECT $fld FROM $tbl WHERE StoreCode = 'XYZ' AND ItemCode = '$pCode'"//MPW = Miracle Park Warehouse
                    
                    
    $rs odbc_exec($conn,$sql);
                    
    //$numfields = odbc_num_fields($rs);
                    //$numrows = odbc_num_rows($rs);
                    
                    
    if (!$rs) exit("Error in SQL");
                    
                    
    $counter 0;
                    
                    while (
    odbc_fetch_row($rs))
                    {
                        
    $counter += 1;

                        
    $pA1 odbc_result($rs"SellIncl01");
                        
    $pB1 odbc_result($rs"SellIncl02");
                        
    $pC1 odbc_result($rs"SellIncl04");
                        
    $pD1 odbc_result($rs"SellIncl05");
                        
    $pA2 odbc_result($rs"SellExcl01");
                        
    $pB2 odbc_result($rs"SellExcl02");
                        
    $pCode2 odbc_result($rs"SellExcl04");
                        
    $pD2 odbc_result($rs"SellExcl05");
                        
                        
    $qO = (int)odbc_result($rs"OpeningQty");
                        
    $storeC odbc_result($rs"StoreCode");
                        
                        
    $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");
                        
    $qB13 = (int)odbc_result($rs"QtyBuyThis13");
                        
    $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");
                        
    $qA13 = (int)odbc_result($rs"QtyAdjustThis13");
                        
    $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");
                        
    $qS13 = (int)odbc_result($rs"QtySellThis13");
                        
    $qSL = (int)odbc_result($rs"QtySellLast");
                        
                        if (
    $counter == 0)
                        {
                            
    $priceSTR .= "$pCode;error^";
                        }
                        else
                        {
                            
    $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;
                            
                            
    //formula = OpeningQty + QtyBuyLast + QtyBuyThis01 + QtyBuyThis02 + QtyBuyThis03 + QtyBuyThis04 + QtyAdjustLast + QtyAdjustThis01 + QtyAdjustThis02 + QtyAdjustThis03 + QtyAdjustThis04 - QtySellLast - QtySellThis01 - QtySellThis02 - QtySellThis03 - QtySellThis04

                            
    $priceSTR .= "$pCode;$pA1;$pB1;$pC1;$pD1;$pA2;$pB2;$pCode2;$pD2;$qOH^"//build price string for this product
                        
    }
                    }

                } 
    //end for
                
                
    odbc_close($conn);
            }
            else 
    //connection FAIL
            
    {
                exit(
    "<BR>Connection Failed: " $conn."!");
            }
            
            
    $priceSTR substr($priceSTR0, -1); //strip last (excessive) "^" character
            
            
    echo "$priceSTR"//this sends query results back to remote web server
        
    }
        else
        {
            echo 
    "<BR>Zero product codes received to look up.";
        }
    }
    ?>
    Lastly (I'm not showing PHP code for this) the data string is received by the remote web server, exploded using the relevant delimiters, and then the database is updated with the price & stock data using the MySQL UPDATE statement.

    Please note that my examples above are simplified and stripped of any identifying information. You have to develop your own code with adequate Internet security measures.

    Let me know if you need any help in setting up the Pervasive drivers on the Pastel server.

  9. #47
    Silver Member bjsteyn's Avatar
    Join Date
    Jul 2010
    Location
    east lndon
    Posts
    231
    Thanks
    4
    Thanked 10 Times in 7 Posts
    Hey Salad Dressing, awesome. Will try out the code a bit later and let you know how it goes. (THANX ALOT)

    Just working on my new website www.pcdeals.co.za . Should launch within a week.

    Click image for larger version. 

Name:	pcdealscoza.jpg 
Views:	244 
Size:	35.6 KB 
ID:	2757

Page 5 of 5 FirstFirst ... 345

Similar Threads

  1. Replies: 3
    Last Post: 19-Jul-18, 11:49 AM
  2. [Question] Pastel Partner v 9.3.4
    By Martinco in forum Accounting Forum
    Replies: 6
    Last Post: 07-Jan-16, 05:17 PM
  3. Magento website developers - idea on price?
    By Pap_sak in forum Business Online Forum
    Replies: 8
    Last Post: 18-Nov-11, 04:48 PM
  4. [Question] Pastel Partner
    By Goop in forum General Business Forum
    Replies: 5
    Last Post: 15-Aug-10, 06:58 PM
  5. [Question] Help with Pastel Payroll Partner
    By celly007 in forum Tax Forum
    Replies: 3
    Last Post: 17-Sep-09, 10:25 AM

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •