<?php include "../../root.class.php";
$html = new html();
$db = new db_safeguard();
$html->add_styles_page();
// $html->check_user_type("ADMIN");
session_start();

$bins_trans_res = $db->query("bins_trans", "SELECT * FROM bins_trans WHERE silo_id <> 0");
$bins_trans = $bins_trans_res->fetch_assoc();

if ($bins_trans_res->num_rows >= 1) {
    while ($bins_info = $bins_trans_res->fetch_assoc()) {

        $variety_res = $db->query("variety", "SELECT * FROM variety WHERE record_id ={$bins_info['variety_id']}");
        $variety_info = $variety_res->fetch_assoc();

        $drying_bin_res = $db->query("drying_bins", "SELECT * FROM drying_bins WHERE record_id ={$bins_info['bin_id']}");
        $drying_bin = $drying_bin_res->fetch_assoc();

        $silo_res = $db->query("silos", "SELECT * FROM silos WHERE record_id ={$bins_info['silo_id']}");
        $silo = $silo_res->fetch_assoc();

        $table_data = $table_data . "<tr>
            <td>BA {$bins_info['batch_id']}</td>
            <td>{$variety_info['name']}</td>
            <td>{$bins_info['amount']}</td>
            <td>{$drying_bin['name']}</td>
            <td>{$bins_info['date']}</td>
            <td>{$silo['name']}</td>
            </tr>
        ";
    }

} else {
    $table_data = "<tr>
        <td> NO RESULTS</td>
    </tr>";
}

$silo_data = $db->query("bins_trans", 'SELECT * FROM bins_trans WHERE silo_id <> 0');

//calculate the total amount of each stilo


$total_data = "";

$total = 0;

$silo_names_res = $db->query("bins_trans", "SELECT DISTINCT silo_id FROM bins_trans WHERE silo_id <> 0");
// echo "SELECT DISTINCT silo_id FROM bins_trans<br>";

while ($silo_ids = $silo_names_res->fetch_assoc()) {

    // Get total amount for this silo_id
    $silo_id = $silo_ids['silo_id'];
    $silo_amount_res = $db->query("bins_trans", "SELECT SUM(amount) AS total FROM bins_trans WHERE silo_id = {$silo_id}");
    $silo_amount_row = $silo_amount_res->fetch_assoc();
    $silo_total = $silo_amount_row['total'];

    // echo "SELECT SUM(amount) AS total FROM bins_trans WHERE silo_id = {$silo_id}<br>";

    // Get silo name
    $silo_name_res = $db->query("silos", "SELECT * FROM silos WHERE record_id = {$silo_id}");
    $silo_name = $silo_name_res->fetch_assoc();

    $variety_res = $db->query("variety", "SELECT * FROM variety WHERE record_id = {$silo_name['variety_id']}");
    $variety = $variety_res->fetch_assoc();

    $all_silos_res = $db->query("silos", "SELECT * FROM silos");
    $all_silos = $all_silos_res->fetch_assoc();

    $total += $silo_total;

    // Add to output
    $total_data .= "<tr>
        <td>{$silo_name['name']}</td>
        <td>{$variety['name']}</td>
        <td>{$silo_total}</td>
        </tr>
    ";
}

//get all silos

$silo_res = $db->query("silos", "SELECT * FROM silos");

while ($silo = $silo_res->fetch_assoc()) {
    if ($silo_res->num_rows >= 1) {

        $variety_res = $db->query("variety", "SELECT * FROM variety WHERE record_id ={$silo['variety_id']}");
        $variety_info = $variety_res->fetch_assoc();

        $get_drying_bins_res = $db->query("bins_trans", "SELECT * FROM bins_trans WHERE silo_id != 0");
        

        if ($get_drying_bins = $get_drying_bins_res->fetch_assoc()) {
            //calculate the total amount of each stilo

            $total = 0;

            $silo_id = $get_drying_bins['silo_id'];
            $silo_amount_res = $db->query("bins_trans", "SELECT SUM(amount) AS total FROM bins_trans GROUP BY silo_id");
            $silo_amount_row = $silo_amount_res->fetch_assoc();
            // echo $silo_amount_row['total'];
            $silo_total = $silo_amount_row['total'];

            $current_silo_data = $current_silo_data . "
            <tr>
                <td>{$silo['name']}</td>
                <td>{$variety_info['name']}</td>
                <td>{$silo_amount_row['total']}</td>
                <td>{$silo['capacity']}</td>
            </tr>
        ";
        }
    } else {
        $current_silo_data = "<tr>
            <td></td><td> NO RESULTS</td><td></td><td></td>
        </tr>";
    }
}

?>
<style>
    body {
        background-image: url('');
        /* background-color: white; */
        background-repeat: repeat-y;
    }

    table {
        width: 95%;
        border-collapse: collapse;
    }

    th,
    td {
        padding-left: 10px;
        border: 3px solid black;
        text-align: left;
        background-color: white;
    }
</style>
<br>

<div class="form_down">
    <h1>SILOS</h1>

    <table style='width:100%; text-align:center;'>

        <tr>
            <td>SILO</td>
            <td>VARIETY</td>
            <td>TOTAL</td>
        </tr>

        <?php echo $total_data; ?>

    </table>

    <br>
    <hr><br>
    <table style='width:100%; text-align:center;'>
        <tr>
            <td>
                BATCH:
            </td>
            <td>
                VARIETY:
            </td>
            <td>
                AMOUNT:
            </td>
            <td>
                DRYING BIN:
            </td>
            <td>
                DATE:
            </td>
            <td>
                SILO:
            </td>
        </tr>

        <?php echo $table_data; ?>
    </table>
    <br>
</div>

<div class="form_down">
    <table>
        <tr>
            <th>SILO NAME:</th>
            <th>VARIETY</th>
            <th>CURRENT AMOUNT</th>
            <th>CAPACITY</th>
        </tr>
        <?php echo $current_silo_data; ?>
    </table>
</div>