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


$query = "UPDATE ".$banking." SET sell='0',customer_pay='0',pay_after='0',pay_now='0',expense='0'";
if ($connection->query($query)) {
    //echo "Updated $banking with $total_sell sales.<br>";
} else {
    //echo "Failed to update $banking: " . $connection->error . "<br>";
}


//From total sell
$sql = "SELECT SUM(total / rate) AS total_sell, s.banking, b.currency 
        FROM ".$sell_data." s
        JOIN ".$banking." b ON s.banking = b.type
        WHERE buy_type = 'လက်ငင်း' 
        GROUP BY s.banking, b.currency";

$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_sell = round($row['total_sell'], 0);
        $banking_data = $row['banking'];
        $currency = $row['currency'];
        

        $query = "UPDATE ".$banking." SET sell='$total_sell' WHERE type = '$banking_data'";
        if ($connection->query($query)) {
            //echo "Updated $banking with $total_sell sales.<br>";
        } else {
            //echo "Failed to update $banking: " . $connection->error . "<br>";
        }
    }
} else {
    // echo "No sales data found.<br>";
}

//From cusotmer payment
$sql = "SELECT SUM(amount) AS total_customer_pay, s.banking, b.currency 
        FROM ".$customer_payment." s
        JOIN ".$banking." b ON s.banking = b.type
        GROUP BY s.banking, b.currency";

$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_customer_pay = round($row['total_customer_pay'], 0);
        $banking_data = $row['banking'];
        $currency = $row['currency'];
        

        $query = "UPDATE ".$banking." SET customer_pay='$total_customer_pay' WHERE type = '$banking_data'";
        if ($connection->query($query)) {
            // echo "Updated success!";
        } else {
            // echo "Failed to update <br>";
        }
    }
} else {
    // echo "No sales data found.<br>";
}



// From banking Refill Database
$item_types_with_refill = [];
$sql = "SELECT sum(amount) as total_refill, type FROM ".$banking_refill." GROUP BY type";
$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_refill = $row['total_refill'];
        $banking_data = $row['type'];

        // Update the category with the total sell
        $query = "UPDATE ".$banking." SET cash_in='$total_refill' WHERE type = '$banking_data'";
        if ($connection->query($query)) {
            //echo "Updated $banking with $total_sell sales.<br>";
        } else {
            //echo "Failed to update $banking: " . $connection->error . "<br>";
        }

        // Add the item type to the array of item types with sales
        $item_types_with_refill[] = $banking_data;
    }
} else {
    //echo "No sales data found.<br>";
}

// Set categories with no sales to zero
if (!empty($item_types_with_refill)) {
    $query = "UPDATE ".$banking." SET cash_in='0' WHERE type NOT IN ('" . implode("','", $item_types_with_refill) . "')";
    if ($connection->query($query)) {
        //echo "Updated categories with no sales to zero.<br>";
    } else {
        //echo "Failed to update categories with no sales: " . $connection->error . "<br>";
    }
} else {
    // If there were no sales, set all categories to zero
    $query = "UPDATE ".$banking." SET cash_in='0'";
    if ($connection->query($query)) {
        //echo "Set all categories to zero as there were no sales.<br>";
    } else {
        //echo "Failed to set all categories to zero: " . $connection->error . "<br>";
    }
}


// From expense Database
$sql = "SELECT SUM(total) AS total_expense, s.banking, b.currency 
        FROM ".$expense." s
        JOIN ".$banking." b ON s.banking = b.type
        GROUP BY s.banking, b.currency";

$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_expense = round($row['total_expense'], 0);
        $banking_data = $row['banking'];
        $currency = $row['currency'];
        

        $query = "UPDATE ".$banking." SET expense='$total_expense' WHERE type = '$banking_data'";
        if ($connection->query($query)) {
            // echo "Updated success!";
        } else {
            // echo "Failed to update <br>";
        }
    }
} else {
    // echo "No sales data found.<br>";
}


// From product_refill Database
$sql = "SELECT SUM(total) AS total_refill_pay, s.banking, b.currency 
        FROM ".$product_refill." s
        JOIN ".$banking." b ON s.banking = b.type
        GROUP BY s.banking, b.currency";

$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_refill_pay = round($row['total_refill_pay'], 0);
        $banking_data = $row['banking'];
        $currency = $row['currency'];


        $query = "UPDATE ".$banking." SET pay_now='$total_refill_pay' WHERE type = '$banking_data'";
        if ($connection->query($query)) {
            // echo "Updated success!";
        } else {
            // echo "Failed to update <br>";
        }
    }
} else {
    // echo "No sales data found.<br>";
}


// From pay_after Database
$sql = "SELECT SUM(pay_amount) AS total_pay_amount, s.payment, b.currency 
        FROM ".$pay_after." s
        JOIN ".$banking." b ON s.payment = b.type
        GROUP BY s.payment, b.currency";

$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_pay_amount = round($row['total_pay_amount'], 0);
        $banking_data = $row['payment'];
        $currency = $row['currency'];
        

        $query = "UPDATE ".$banking." SET pay_after='$total_pay_amount' WHERE type = '$banking_data'";
        if ($connection->query($query)) {
            // echo "Updated success!<br>";
        } else {
            // echo "Failed to update <br>";
        }
    }
} else {
    // echo "No sales data found.<br>";
}


//calculate banking remain
$query = "UPDATE " . $banking . " SET remain = (cash_in + sell + customer_pay) - (pay_after + pay_now + expense)";

if ($connection->query($query) === TRUE) {
    // echo "Record updated successfully";
} else {
    // echo "Error updating record: " . $connection->error;
}


?>
