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

// Step 1: Fetch all product_names from sell_data table


$b_id = '5012dda47785271947c14dd0d47950701731213137';


$sql = "SELECT * FROM ".$cargo." WHERE browser_id='$b_id'";
$result = $connection->query($sql);
if ($result->num_rows > 0) {
    $row = $result->fetch_assoc(); // Fetch the row here
    $cargo_data = $row["cargo"];

    $query = "UPDATE ".$cargo_data." set package_remain='0',unit_remain='0',pieces_remain='0',package_sell='0',unit_sell='0',pieces_sell='0',package_damage='0',unit_damage='0',pieces_damage='0',package_change='0',unit_change='0',pieces_change='0'";
    if (!$connection->query($query)) {
        die("ERROR: Query failed. " . $connection->error);
    }


//From Trans
$sql = "SELECT SUM(qty) AS total_pieces_qty,product_name FROM ".$cargo_data."_trans WHERE type = 'ဗူး/ခု' GROUP BY product_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $pieces_sell = $row['total_pieces_qty'];
        $product_name = $row['product_name'];
        $query = "UPDATE ".$cargo_data." set pieces_sell='$pieces_sell' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

$sql = "SELECT SUM(qty) AS total_unit_qty,product_name FROM ".$cargo_data."_trans WHERE type = 'ကဒ်' GROUP BY product_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $unit_sell = $row['total_unit_qty'];
        $product_name = $row['product_name'];
        $query = "UPDATE ".$cargo_data." set unit_sell='$unit_sell' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

$sql = "SELECT SUM(qty) AS total_package_qty,product_name FROM ".$cargo_data."_trans WHERE type = 'ဖာ' GROUP BY product_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $package_sell = $row['total_package_qty'];
        $product_name = $row['product_name'];
        $query = "UPDATE ".$cargo_data." set package_sell='$package_sell' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

//From Proudct_damage
$sql = "SELECT SUM(qty) AS total_package_qty,product_name FROM ".$cargo_data."_damage WHERE type = 'ဖာ' GROUP BY product_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $package_damage = $row['total_package_qty'];
        $product_name = $row['product_name'];
        $query = "UPDATE ".$cargo_data." set package_damage='$package_damage' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

$sql = "SELECT SUM(qty) AS total_unit_qty,product_name FROM ".$cargo_data."_damage WHERE type = 'ကဒ်' GROUP BY product_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $unit_damage = $row['total_unit_qty'];
        $product_name = $row['product_name'];
        $query = "UPDATE ".$cargo_data." set unit_damage='$unit_damage' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

$sql = "SELECT SUM(qty) AS total_pieces_qty,product_name FROM ".$cargo_data."_damage WHERE type = 'ဗူး/ခု' GROUP BY product_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $pieces_sell = $row['total_pieces_qty'];
        $product_name = $row['product_name'];
        $query = "UPDATE ".$cargo_data." set pieces_damage='$pieces_sell' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

//Remain from Refill
$sql = "SELECT SUM(qty) AS total_pieces_qty,product_name FROM ".$cargo_data."_refill WHERE type = 'ဗူး/ခု' GROUP BY product_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $pieces_sell = $row['total_pieces_qty'];
        $product_name = $row['product_name'];
        
        $query = "UPDATE ".$cargo_data." set pieces_remain='$pieces_sell' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

$sql = "SELECT SUM(qty) AS total_unit_qty,product_name FROM ".$cargo_data."_refill WHERE type = 'ကဒ်' GROUP BY product_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $unit_sell = $row['total_unit_qty'];
        $product_name = $row['product_name'];
        $query = "UPDATE ".$cargo_data." set unit_remain='$unit_sell' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

$sql = "SELECT SUM(qty) AS total_package_qty,product_name FROM ".$cargo_data."_refill WHERE type = 'ဖာ' GROUP BY product_name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $package_sell = $row['total_package_qty'];
        $product_name = $row['product_name'];
        $query = "UPDATE ".$cargo_data." set package_remain='$package_sell' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

//from product_change
$sql = "SELECT SUM(unit) AS total_change_unit,name FROM ".$cargo_data."_change GROUP BY name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_change_unit = $row['total_change_unit'];
        $product_name = $row['name'];
        
        $query = "UPDATE ".$cargo_data." set unit_change='$total_change_unit' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}

$sql = "SELECT SUM(pieces) AS total_change_pieces,name FROM ".$cargo_data."_change GROUP BY name";
$result = $connection->query($sql);
if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $total_change_pieces = $row['total_change_pieces'];
        $product_name = $row['name'];
        $query = "UPDATE ".$cargo_data." set pieces_change='$total_change_pieces' WHERE product_name='$product_name'";
        if (!$connection->query($query)) {
            die("ERROR: Query failed. " . $connection->error);
        }
    }
}



$query = "UPDATE ".$cargo_data." set 
                package_exist=package_remain - (package_sell + package_damage + unit_change),
                unit_exist= unit_remain - (unit_sell + unit_damage) + (unit_change * unit) -  pieces_change,
                pieces_exist= pieces_remain - (pieces_sell + pieces_damage) + (pieces_change * pieces)";
if (!$connection->query($query)) {
    die("ERROR: Query failed. " . $connection->error);
}

                }


                ?>


