<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

$Server = "XXXXXX";
$User = "XXXXXX";
$Pass = "XXXXXX";
$DB = "XXXXXX";

//connection to the database
$dbhandle = mssql_connect($Server, $User, $Pass)
  or die("Couldn't connect to SQL Server on $Server. Error: " . mssql_get_last_message());

//select a database to work with
$selected = mssql_select_db($DB, $dbhandle)
  or die("Couldn't connect to SQL Server on $Server. Error: " . mssql_get_last_message());

//declare the SQL statement that will query the database
$query  = "select I.ID, I.Code Code, H.OnHand / coalesce(StU.UnitSize, 1) OnHand n";
$query .= "from Products I n";
$query .= "left join ProductStockLocations St on (St.ProductID = I.ID) n";
$query .= "left join ProductPOSLocations L on (L.ProductID = I.ID) n";
$query .= "left join ProductUnits StU on (StU.ID = St.StockUnitID) n";
$query .= "left join ProductStockOnHandItems H on (H.ProductID = I.ID and H.StockLocationID = St.StockLocationID) n";
$query .= "where I.ID > 0 n";
$query .= "and St.StockLocationID = 1 n";
$query .= "and L.POSLocationID = 1 n";
$query .= "and L.SoldAtLocation = 1 n";


// execute the SQL query and return a result set
// mssql_query() actually returns a resource
// that you must iterate with (e.g.) mssql_fetch_array()
$mssqlResult = mssql_query($query);

if($mssqlResult === FALSE) {
    die(mysql_error()); // TODO: better error handling
}

// connect to the MySQL database
mysql_connect("XXXXXX", "XXXXXX", "XXXXXX") or die(mysql_error());
mysql_select_db("XXXXXX") or die(mysql_error());


while ( $mssqlRow = mssql_fetch_array($mssqlResult) ) {
    $mssqlCode = $mssqlRow['Code'];
    $mssqlOnHand = $mssqlRow['OnHand'];
    mysql_query(
        "UPDATE product_option_relation SET stock = $mssqlOnHand WHERE sku = '$mssqlCode'"
       // extra quotes may be required around $mssqlCode depending on the column type
    );
    mysql_query(
        "UPDATE product SET quantity = $mssqlOnHand WHERE sku = '$mssqlCode' AND has_option = '0' ");
    }

//close the connection
mssql_close($dbhandle);

// Update product total with the total quantities of the product options
$result = mysql_query("UPDATE product a INNER JOIN (SELECT product_id, SUM(stock) AS summedqty FROM product_option_relation GROUP BY product_id) b ON b.product_id = a.product_ID SET a.quantity = summedqty")
or die(mysql_error());
?>

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');

$Server = "XXXXXX";
$User = "XXXXXX";
$Pass = "XXXXXX";
$DB = "XXXXXX";

//Connection to the database
$connectionInfo = array( "Database"=>$DB, "UID"=>$User, "PWD"=>$Pass);
@$conn = sqlsrv_connect( $Server, $connectionInfo);

if( $conn === false) {
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}


//declare the SQL statement that will query the database
$query  = "select I.ID, I.Code Code, H.OnHand / coalesce(StU.UnitSize, 1) OnHand n";
$query .= "from Products I n";
$query .= "left join ProductStockLocations St on (St.ProductID = I.ID) n";
$query .= "left join ProductPOSLocations L on (L.ProductID = I.ID) n";
$query .= "left join ProductUnits StU on (StU.ID = St.StockUnitID) n";
$query .= "left join ProductStockOnHandItems H on (H.ProductID = I.ID and H.StockLocationID = St.StockLocationID) n";
$query .= "where I.ID > 0 n";
$query .= "and St.StockLocationID = 1 n";
$query .= "and L.POSLocationID = 1 n";
$query .= "and L.SoldAtLocation = 1 n";


// execute the SQL query and return a result set
// sqlsrv_query() actually returns a resource
// that you must iterate with (e.g.) sqlsrv_fetch_array()
@$mssqlResult = sqlsrv_query( $conn, $sql);


if( $mssqlResult === false) {
    echo "No result resource after MSSQL query.<br />";
    die( print_r( sqlsrv_errors(), true) );
}


// connect to the MySQL database
@$db = mysqli_connect("XXXXXX", "XXXXXX", "XXXXXX", "XXXXXX");


if (mysqli_connect_error()) {
    echo "Error: Unable to connect to MySQL.<br />";
    exit;
}


while( $mssqlRow = sqlsrv_fetch_array( $mssqlResult, SQLSRV_FETCH_ASSOC) ) {

    $mssqlCode = $mssqlRow['Code'];
    $mssqlOnHand = $mssqlRow['OnHand'];

    $mysqlQuery = "UPDATE product_option_relation SET stock = ".$mssqlOnHand." WHERE sku = '".$mssqlCode."'";

    mysqli_query($db, $mysqlQuery);

    $mysqlQuery = "UPDATE product SET quantity = ".$mssqlOnHand." WHERE sku = '".$mssqlCode."' AND has_option = '0' ";

    mysqli_query($db, $mysqlQuery);
}

//close the MSSRV connections
sqlsrv_free_stmt($mssqlResult);
sqlsrv_close($conn);


// Update product total with the total quantities of the product options
$mysqlQuery = "UPDATE product a INNER JOIN (SELECT product_id, SUM(stock) AS summedqty FROM product_option_relation GROUP BY product_id) b ON b.product_id = a.product_ID SET a.quantity = summedqty";
@$result = mysqli_query($db, $mysqlQuery);
if(!$result){
    echo "No result resource after MySQL query.<br />";
}

//Close MySQL connection
//mysqli_free_result($result);
mysqli_close($db);
?>