<?php 
include "database/database.php";
include "database/data.php";

$sql = "UPDATE ".$customer." SET total_buy='0',debt='0',pay='0',balance='0',remain_debt='0'"; //update Brower ID
$insertResult = $connection->query($sql);  

$count_result = mysqli_query($connection, "select count(*) as total from ".$product."");
$count = mysqli_fetch_assoc($count_result)['total'];
mysqli_free_result($count_result);

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

$sql = "SELECT customer, SUM(total/rate) AS debt FROM ".$sell_data." WHERE buy_type = 'အကြွေး' GROUP BY customer";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $debt = $row['debt'];
        $customer_data = $row['customer'];
        $sql = "UPDATE ".$customer." SET debt='$debt' WHERE name='$customer_data'"; //update Brower ID
        $insertResult = $connection->query($sql);       
    }
}


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

$sql = "UPDATE ".$customer." SET remain_debt = debt - pay;"; //update Brower ID
$insertResult = $connection->query($sql);

?>

