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

[Salad_Dressing]

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).

$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

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($priceSTR, 0, -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.

[bjsteyn]

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.