<?php 
include "database/database.php";
include "database/data.php";
//ဝယ်အား Total Buy

$sql = "UPDATE ".$supplier." SET total_buy='0',debt='0',pay_now='0',payable='0',pay_after='0'"; //update Brower ID
$insertResult = $connection->query($sql);  

$sql = "SELECT supplier, SUM(total) AS total_buy FROM ".$product_refill." GROUP BY supplier";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0){
    while ($row = $result->fetch_assoc()) {
        $total_buy = $row['total_buy'];
        $supplier_data = $row['supplier'];
        $sql = "UPDATE ".$supplier." SET total_buy='$total_buy' WHERE name='$supplier_data'"; //update Brower ID
        $insertResult = $connection->query($sql);     
    }
}

//ပေးရန်အကြွေး debt
$sql = "SELECT supplier, SUM(total) AS total_debt FROM ".$product_refill." where payment='debt' GROUP BY supplier";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0){
    while ($row = $result->fetch_assoc()) {
        $total_debt = $row['total_debt'];
        $supplier_data = $row['supplier'];
        $sql = "UPDATE ".$supplier." SET debt='$total_debt' WHERE name='$supplier_data'"; //update Brower ID
        $insertResult = $connection->query($sql);     
    }
}

//ငွေချေပြီး pay_now
$sql = "SELECT supplier, SUM(total) AS total_pay FROM ".$product_refill." where payment='pay' GROUP BY supplier";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0){
    while ($row = $result->fetch_assoc()) {
        $total_pay = $row['total_pay'];
        $supplier_data = $row['supplier'];
        $sql = "UPDATE ".$supplier." SET pay_now='$total_pay' WHERE name='$supplier_data'"; //update Brower ID
        $insertResult = $connection->query($sql);     
    }
}

//ငွေချေပြီး pay_after
$sql = "SELECT shop_name, SUM(pay_amount) AS total_after_pay FROM ".$pay_after." GROUP BY shop_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0){
    while ($row = $result->fetch_assoc()) {
        $total_after_pay = $row['total_after_pay'];
        $shop_name = $row['shop_name'];
        $sql = "UPDATE ".$supplier." SET pay_after='$total_after_pay' WHERE name='$shop_name'"; //update Brower ID
        $insertResult = $connection->query($sql);     
    }
}

$sql = "UPDATE ".$supplier." SET payable=debt-pay_after"; //update Brower ID
        $insertResult = $connection->query($sql);  
?>

