<!DOCTYPE html>
<html lang="en" data-bs-theme="dark">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>နှစ်ချုပ်</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha2/dist/css/bootstrap.min.css">
    <script src="https://kit.fontawesome.com/ae360af17e.js" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
    <link rel="stylesheet" href="css/style.css">
    <link rel="icon" href="image/logo.png" type="image/png">

    <link rel="stylesheet" href="extensions/fixed-columns/bootstrap-table-fixed-columns.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
    <script src="extensions/fixed-columns/bootstrap-table-fixed-columns.js"></script>
</head>
<style>
        .image-container {
            width: 200px; /* Adjust this width as needed */
            height: 200px; /* Adjust this height as needed */
            overflow: hidden;
            position: relative;
            border: 1px solid #ccc; /* Add a border for the frame effect */
        }
        .image-container img {
            width: 100%;
            height: 100%;
            object-fit: contain; /* Ensure the entire image is visible */
        }
    </style>



<?php

session_start();
                                
if (isset($_SESSION['browser_id'])) {
    $browserId = $_SESSION['browser_id'];
} else {
    // Generate a new browser ID
    $browserId = generateUniqueBrowserId();

    // Store the browser ID in the session for future use
    $_SESSION['browser_id'] = $browserId;
}


include "database/database.php";
include "database/data.php";
include "login_check.php";
?>


    <style>
    .select-container {
        position: relative;
        display: inline-block;
    }
    .select-container select {
        appearance: none;
        -webkit-appearance: none;
        -moz-appearance: none;
        padding-right: 30px; /* Adjust this based on your icon size */
    }
    .select-container .fa-chevron-down {
        position: absolute;
        right: 10px;
        top: 50%;
        transform: translateY(-50%);
        pointer-events: none; /* Allows clicking through the icon to the select element */
    }
</style>


<body>
    <div class="wrapper">
        <?php 
        include "sidebar.php";
        ?>

        <div class="main">
            <nav class="navbar navbar-expand px-3 border-bottom">
                <button class="btn" id="sidebar-toggle" type="button">
                    <span class="navbar-toggler-icon"></span>
                </button>
                <div class="navbar-collapse navbar">
                    <ul class="navbar-nav">
                    <div class="mb-3" style="margin-right:30px">
                    <h4 style="font-size:25px">နှစ်ချုပ်</h4>
                    </div>
                        <li class="nav-item dropdown">
                            <a href="#" data-bs-toggle="dropdown" class="nav-icon pe-md-0">
                                <img src="image/logo.png" class="avatar img-fluid rounded" alt="">
                            </a>
                            <?php include "logo_profile.php";?>
                        </li>
                    </ul>
                </div>
            </nav>

            <main class="content px-3 py-2">
                <div class="container-fluid">

                <div class="mb-3" style="position: relative;">
                
                    
                </div>


                    <div class="row">
                  
                    <!-- Table Element -->
                    <div class="container-fluid">


                    <div class="container-fluid" style="margin-bottom: 20px;">
                        <form>
                        <div class="row">
                        <div class="col-sm-2 mb-2">
                            <div class="select-container">
                                <input type="date" class="form-control" id="DateInput" placeholder="date" onchange="filterTable()">
                            </div>
                        </div>

                        <div class="col-sm-2">
                        <div class="select-container">
                        <button type="button" class="btn btn-outline-success" onclick="refreshPage()">Show All</button>
                    </div>

                        </form>
                    </div>

                    <script>
                        function refreshPage() {
                            location.reload();
                        }
                    </script>

                    </div>

            
                        
                        <div class="scrollable-table" style="height:70vh;">
                        <table class="table table-bordered table-hover" style="color:white;font-size:18px;"  id="dataTable">
                        <thead style="background-color:#19242e" class="text-center align-middle">
                            <tr>
                            <tr>
                            <th rowspan="2" scope="col">date</th>
                            <th colspan="3" scope="col" style="color:#FFA62F">ကျပ်ငွေ</th>
                            <th colspan="3" scope="col" style="color:#42f560">ဘတ်ငွေ</th>
                            <th colspan="3" scope="col" style="color:#c7023a">ယွမ်ငွေ</th>
                            <th colspan="3" scope="col" style="color:#ffffff">ဒေါ်လာ</th>
                        </tr>
                        <tr>
                            <th scope="col" style="color:#FFA62F">ဝင်ငွေ</th>
                            <th scope="col" style="color:#FFA62F">ထွက်ငွေ</th>
                            <th scope="col" style="color:#FFA62F">လက်ကျန်</th>

                            <th scope="col" style="color:#42f560">ဝင်ငွေ</th>
                            <th scope="col" style="color:#42f560">ထွက်ငွေ</th>
                            <th scope="col" style="color:#42f560">လက်ကျန်</th>

                            <th scope="col" style="color:#c7023a">ဝင်ငွေ</th>
                            <th scope="col" style="color:#c7023a">ထွက်ငွေ</th>
                            <th scope="col" style="color:#c7023a">လက်ကျန်</th>

                            <th scope="col" style="color:#ffffff">ဝင်ငွေ</th>
                            <th scope="col" style="color:#ffffff">ထွက်ငွေ</th>
                            <th scope="col" style="color:#ffffff">လက်ကျန်</th>
                        </tr>
                        </tr>
                            </thead>
                                <tbody>
                                <?php
                                // Retrieve all unique dates from both tables
                                 $sql_dates = "
    SELECT DISTINCT year(date) as year FROM (
        SELECT date FROM $expense
        UNION ALL
        SELECT date FROM $sell_data
        UNION ALL
        SELECT date FROM $banking_refill
        UNION ALL
        SELECT date_1 FROM $product_refill
        UNION ALL
        SELECT date FROM $pay_after
        UNION ALL
        SELECT date FROM $customer_payment
    ) AS combined
    ORDER BY year DESC
";
$result_dates = mysqli_query($connection, $sql_dates);

$dates = [];
if ($result_dates) {
    while ($row_date = mysqli_fetch_assoc($result_dates)) {
        $dates[] = $row_date['year'];
    }
}



                                // Retrieve total expenses grouped by date
                                $sql_expense = "
                                            SELECT 
                                                e.banking, 
                                                year(e.date) AS year, 
                                                b.currency, 
                                                SUM(e.total) AS total_expense_kyat 
                                            FROM 
                                                $expense e
                                            JOIN 
                                                $banking b 
                                            ON 
                                                e.banking = b.type
                                            WHERE 
                                                b.currency = 'kyat'
                                            GROUP BY 
                                                e.banking, year(e.date), b.currency
                                        ";

                                $result_expense = mysqli_query($connection, $sql_expense);

                                $expenses_kyat = [];
                                if ($result_expense) {
                                    while ($row_expense = mysqli_fetch_assoc($result_expense)) {
                                        $expenses_kyat[$row_expense['year']] = $row_expense['total_expense_kyat'];
                                    }
                                }

                                // Retrieve total sales grouped by date
                                //$sql_sell = "SELECT date, SUM(total) AS total_sell FROM $sell_data where buy_type ='လက်ငင်း' GROUP BY year(date)";
                                $sql_sell = "
                                        SELECT 
                                            e.banking, 
                                            year(e.date) AS year, 
                                            b.currency, 
                                            SUM(e.total/e.rate) AS total_sell_kyat 
                                        FROM 
                                            $sell_data e
                                        JOIN 
                                            $banking b 
                                        ON 
                                            e.banking = b.type
                                        WHERE 
                                            b.currency = 'kyat' and buy_type ='လက်ငင်း'
                                        GROUP BY 
                                            e.banking, year(e.date), b.currency
                                    ";
                                $result_sell = mysqli_query($connection, $sql_sell);

                                $sales_kyat = [];
                                if ($result_sell) {
                                    while ($row_sell = mysqli_fetch_assoc($result_sell)) {
                                        $sales_kyat[$row_sell['year']] = $row_sell['total_sell_kyat'];
                                    }
                                }
                                
                                 $sql_customer_payment = "
                                            SELECT 
                                                e.banking, 
                                                YEAR(e.date) AS year,
                                                b.currency, 
                                                SUM(e.amount) AS total_customer_payment_kyat 
                                            FROM 
                                                $customer_payment e
                                            JOIN 
                                                $banking b 
                                            ON 
                                                e.banking = b.type
                                            WHERE 
                                                b.currency = 'kyat'
                                            GROUP BY 
                                                YEAR(e.date),e.banking, b.currency
                                        ";
                                        $result_customer_payment = mysqli_query($connection, $sql_customer_payment);
                                        
                                        $customer_payment_kyat = [];
                                        if ($result_customer_payment) {
                                            while ($row_customer_payment = mysqli_fetch_assoc($result_customer_payment)) {
                                                $customer_payment_kyat[$row_customer_payment['year']] = $row_customer_payment['total_customer_payment_kyat'];
                                            }
                                        }

                                // Retrieve total refill grouped by date
                                $sql_refill = "SELECT 
                                            e.type, 
                                            YEAR(e.date) AS year,
                                            b.currency, 
                                            SUM(e.amount) AS total_refill_kyat 
                                        FROM 
                                            $banking_refill e
                                        JOIN 
                                            $banking b 
                                        ON 
                                            e.type = b.type
                                        WHERE 
                                            b.currency = 'kyat'
                                        GROUP BY 
                                             YEAR(e.date)";

                                $result_refill = mysqli_query($connection, $sql_refill);

                                $refills_kyat = [];
                                if ($result_refill) {
                                    while ($row_refill = mysqli_fetch_assoc($result_refill)) {
                                        $refills_kyat[$row_refill['year']] = $row_refill['total_refill_kyat'];
                                    }
                                }

                                // Retrieve total pay_now refill grouped by date
                                //$sql_refill_pay_now = "SELECT date, SUM(total) AS total_refill_pay_now FROM `$product_refill` WHERE payment='pay' GROUP BY year(date)";
                                $sql_refill_pay_now = "
                                        SELECT 
                                            e.banking, 
                                            year(e.date_1) AS year, 
                                            b.currency, 
                                            SUM(e.total) AS total_refill_pay_now_kyat 
                                        FROM 
                                            $product_refill e
                                        JOIN 
                                            $banking b 
                                        ON 
                                            e.banking = b.type
                                        WHERE 
                                            b.currency = 'kyat' and payment ='pay'
                                        GROUP BY 
                                            e.banking, year(e.date_1), b.currency
                                    ";
                                $result_refill_pay_now = mysqli_query($connection, $sql_refill_pay_now);

                                $refills_pay_now_kyat  = [];
                                if ($result_refill_pay_now) {
                                    while ($row_refill = mysqli_fetch_assoc($result_refill_pay_now)) {
                                        $refills_pay_now_kyat[$row_refill['year']] = $row_refill['total_refill_pay_now_kyat'];
                                    }
                                }

                                // Fetch total pay_amount grouped by date
                                //$sql_pay_after = "SELECT date, SUM(pay_amount) AS total_pay_after FROM $pay_after GROUP BY year(date)";
                                $sql_pay_after = "
                                            SELECT 
                                                e.payment, 
                                                year(e.date) AS year, 
                                                b.currency, 
                                                SUM(e.pay_amount) AS total_pay_after_kyat 
                                            FROM 
                                                $pay_after e
                                            JOIN 
                                                $banking b 
                                            ON 
                                                e.payment = b.type
                                            WHERE 
                                                b.currency = 'kyat'
                                            GROUP BY 
                                                year(e.date), e.payment, b.currency
                                        ";
                                        $result_pay_after = mysqli_query($connection, $sql_pay_after);

                                        $pay_afters_kyat = [];
                                        if ($result_pay_after) {
                                            while ($row_pay_after = mysqli_fetch_assoc($result_pay_after)) {
                                                // Corrected the variable name here
                                                $pay_afters_kyat[$row_pay_after['year']] = $row_pay_after['total_pay_after_kyat'];
                                            }
                                        }

                                $total_remain_kyat = 0;

                                //Baht
                                $sql_expense = "
        SELECT 
            e.banking, 
            year(e.date) AS year, 
            b.currency, 
            SUM(e.total) AS total_expense_baht 
        FROM 
            $expense e
        JOIN 
            $banking b 
        ON 
            e.banking = b.type
        WHERE 
            b.currency = 'baht'
        GROUP BY 
            e.banking, year(e.date), b.currency
    ";
$result_expense = mysqli_query($connection, $sql_expense);

$expenses_baht = [];
if ($result_expense) {
    while ($row_expense = mysqli_fetch_assoc($result_expense)) {
        $expenses_baht[$row_expense['year']] = $row_expense['total_expense_baht'];
    }
}

// Retrieve total sales grouped by date
//$sql_sell = "SELECT date, SUM(total) AS total_sell FROM $sell_data where buy_type ='လက်ငင်း' GROUP BY year(date)";
$sql_sell = "
        SELECT 
            e.banking, 
            year(e.date) AS year, 
            b.currency, 
            SUM(e.total/e.rate) AS total_sell_baht 
        FROM 
            $sell_data e
        JOIN 
            $banking b 
        ON 
            e.banking = b.type
        WHERE 
            b.currency = 'baht' and buy_type ='လက်ငင်း'
        GROUP BY 
            e.banking, year(e.date), b.currency
    ";
$result_sell = mysqli_query($connection, $sql_sell);

$sales_baht = [];
if ($result_sell) {
    while ($row_sell = mysqli_fetch_assoc($result_sell)) {
        $sales_baht[$row_sell['year']] = $row_sell['total_sell_baht'];
    }
}

$sql_customer_payment = "
                                            SELECT 
                                                e.banking, 
                                                YEAR(e.date) AS year,
                                                b.currency, 
                                                SUM(e.amount) AS total_customer_payment_baht 
                                            FROM 
                                                $customer_payment e
                                            JOIN 
                                                $banking b 
                                            ON 
                                                e.banking = b.type
                                            WHERE 
                                                b.currency = 'baht'
                                            GROUP BY 
                                                YEAR(e.date),e.banking, b.currency
                                        ";
                                        $result_customer_payment = mysqli_query($connection, $sql_customer_payment);
                                        
                                        $customer_payment_baht = [];
                                        if ($result_customer_payment) {
                                            while ($row_customer_payment = mysqli_fetch_assoc($result_customer_payment)) {
                                                $customer_payment_baht[$row_customer_payment['year']] = $row_customer_payment['total_customer_payment_baht'];
                                            }
                                        }

// Retrieve total refill grouped by date
 $sql_refill = "SELECT 
                                            e.type, 
                                            YEAR(e.date) AS year,
                                            b.currency, 
                                            SUM(e.amount) AS total_refill_baht
                                        FROM 
                                            $banking_refill e
                                        JOIN 
                                            $banking b 
                                        ON 
                                            e.type = b.type
                                        WHERE 
                                            b.currency = 'baht'
                                        GROUP BY 
                                             YEAR(e.date)";

$result_refill = mysqli_query($connection, $sql_refill);

$refills_baht = [];
if ($result_refill) {
    while ($row_refill = mysqli_fetch_assoc($result_refill)) {
        $refills_baht[$row_refill['year']] = $row_refill['total_refill_baht'];
    }
}

// Retrieve total pay_now refill grouped by date
//$sql_refill_pay_now = "SELECT date, SUM(total) AS total_refill_pay_now FROM `$product_refill` WHERE payment='pay' GROUP BY year(date)";
$sql_refill_pay_now = "
        SELECT 
            e.banking, 
            year(e.date_1) AS year, 
            b.currency, 
            SUM(e.total) AS total_refill_pay_now_baht 
        FROM 
            $product_refill e
        JOIN 
            $banking b 
        ON 
            e.banking = b.type
        WHERE 
            b.currency = 'baht' and payment ='pay'
        GROUP BY 
            e.banking, year(e.date_1), b.currency
    ";
$result_refill_pay_now = mysqli_query($connection, $sql_refill_pay_now);

$refills_pay_now_baht  = [];
if ($result_refill_pay_now) {
    while ($row_refill = mysqli_fetch_assoc($result_refill_pay_now)) {
        $refills_pay_now_baht[$row_refill['year']] = $row_refill['total_refill_pay_now_baht'];
    }
}

// Fetch total pay_amount grouped by date
//$sql_pay_after = "SELECT date, SUM(pay_amount) AS total_pay_after FROM $pay_after GROUP BY year(date)";
$sql_pay_after = "
            SELECT 
                e.payment, 
                year(e.date) AS year, 
                b.currency, 
                SUM(e.pay_amount) AS total_pay_after_baht 
            FROM 
                $pay_after e
            JOIN 
                $banking b 
            ON 
                e.payment = b.type
            WHERE 
                b.currency = 'baht'
            GROUP BY 
                year(e.date), e.payment, b.currency
        ";
        $result_pay_after = mysqli_query($connection, $sql_pay_after);

        $pay_afters_baht = [];
        if ($result_pay_after) {
            while ($row_pay_after = mysqli_fetch_assoc($result_pay_after)) {
                // Corrected the variable name here
                $pay_afters_baht[$row_pay_after['year']] = $row_pay_after['total_pay_after_baht'];
            }
        }


$total_remain_baht = 0;

//Yuan
$sql_expense = "
        SELECT 
            e.banking, 
            year(e.date) AS year, 
            b.currency, 
            SUM(e.total) AS total_expense_yuan 
        FROM 
            $expense e
        JOIN 
            $banking b 
        ON 
            e.banking = b.type
        WHERE 
            b.currency = 'yuan'
        GROUP BY 
            e.banking, year(e.date), b.currency
    ";
$result_expense = mysqli_query($connection, $sql_expense);

$expenses_yuan = [];
if ($result_expense) {
    while ($row_expense = mysqli_fetch_assoc($result_expense)) {
        $expenses_yuan[$row_expense['year']] = $row_expense['total_expense_yuan'];
    }
}

// Retrieve total sales grouped by date
//$sql_sell = "SELECT date, SUM(total) AS total_sell FROM $sell_data where buy_type ='လက်ငင်း' GROUP BY year(date)";
$sql_sell = "
        SELECT 
            e.banking, 
            year(e.date) AS year, 
            b.currency, 
            SUM(e.total/e.rate) AS total_sell_yuan 
        FROM 
            $sell_data e
        JOIN 
            $banking b 
        ON 
            e.banking = b.type
        WHERE 
            b.currency = 'yuan' and buy_type ='လက်ငင်း'
        GROUP BY 
            e.banking, year(e.date), b.currency
    ";
$result_sell = mysqli_query($connection, $sql_sell);

$sales_yuan = [];
if ($result_sell) {
    while ($row_sell = mysqli_fetch_assoc($result_sell)) {
        $sales_yuan[$row_sell['year']] = $row_sell['total_sell_yuan'];
    }
}

$sql_customer_payment = "
                                            SELECT 
                                                e.banking, 
                                                YEAR(e.date) AS year,
                                                b.currency, 
                                                SUM(e.amount) AS total_customer_payment_yuan 
                                            FROM 
                                                $customer_payment e
                                            JOIN 
                                                $banking b 
                                            ON 
                                                e.banking = b.type
                                            WHERE 
                                                b.currency = 'yuan'
                                            GROUP BY 
                                                YEAR(e.date),e.banking, b.currency
                                        ";
                                        $result_customer_payment = mysqli_query($connection, $sql_customer_payment);
                                        
                                        $customer_payment_yuan = [];
                                        if ($result_customer_payment) {
                                            while ($row_customer_payment = mysqli_fetch_assoc($result_customer_payment)) {
                                                $customer_payment_yuan[$row_customer_payment['year']] = $row_customer_payment['total_customer_payment_yuan'];
                                            }
                                        }

// Retrieve total refill grouped by date
 $sql_refill = "SELECT 
                                            e.type, 
                                            YEAR(e.date) AS year,
                                            b.currency, 
                                            SUM(e.amount) AS total_refill_yuan 
                                        FROM 
                                            $banking_refill e
                                        JOIN 
                                            $banking b 
                                        ON 
                                            e.type = b.type
                                        WHERE 
                                            b.currency = 'yuan'
                                        GROUP BY 
                                             YEAR(e.date)";

$result_refill = mysqli_query($connection, $sql_refill);

$refills_yuan = [];
if ($result_refill) {
    while ($row_refill = mysqli_fetch_assoc($result_refill)) {
        $refills_yuan[$row_refill['year']] = $row_refill['total_refill_yuan'];
    }
}

// Retrieve total pay_now refill grouped by date
//$sql_refill_pay_now = "SELECT date, SUM(total) AS total_refill_pay_now FROM `$product_refill` WHERE payment='pay' GROUP BY year(date)";
$sql_refill_pay_now = "
        SELECT 
            e.banking, 
            year(e.date_1) AS year, 
            b.currency, 
            SUM(e.total) AS total_refill_pay_now_yuan 
        FROM 
            $product_refill e
        JOIN 
            $banking b 
        ON 
            e.banking = b.type
        WHERE 
            b.currency = 'yuan' and payment ='pay'
        GROUP BY 
            e.banking, year(e.date_1), b.currency
    ";
$result_refill_pay_now = mysqli_query($connection, $sql_refill_pay_now);

$refills_pay_now_yuan  = [];
if ($result_refill_pay_now) {
    while ($row_refill = mysqli_fetch_assoc($result_refill_pay_now)) {
        $refills_pay_now_yuan[$row_refill['year']] = $row_refill['total_refill_pay_now_yuan'];
    }
}

// Fetch total pay_amount grouped by date
//$sql_pay_after = "SELECT date, SUM(pay_amount) AS total_pay_after FROM $pay_after GROUP BY year(date)";
$sql_pay_after = "
            SELECT 
                e.payment, 
                year(e.date) AS year, 
                b.currency, 
                SUM(e.pay_amount) AS total_pay_after_yuan 
            FROM 
                $pay_after e
            JOIN 
                $banking b 
            ON 
                e.payment = b.type
            WHERE 
                b.currency = 'yuan'
            GROUP BY 
                year(e.date), e.payment, b.currency
        ";
        $result_pay_after = mysqli_query($connection, $sql_pay_after);

        $pay_afters_yuan = [];
        if ($result_pay_after) {
            while ($row_pay_after = mysqli_fetch_assoc($result_pay_after)) {
                // Corrected the variable name here
                $pay_afters_yuan[$row_pay_after['year']] = $row_pay_after['total_pay_after_yuan'];
            }
        }


$total_remain_yuan = 0;

//Dollar
$sql_expense = "
        SELECT 
            e.banking, 
            year(e.date) AS year, 
            b.currency, 
            SUM(e.total) AS total_expense_dollar 
        FROM 
            $expense e
        JOIN 
            $banking b 
        ON 
            e.banking = b.type
        WHERE 
            b.currency = 'dollar'
        GROUP BY 
            e.banking, year(e.date), b.currency
    ";
$result_expense = mysqli_query($connection, $sql_expense);

$expenses_dollar = [];
if ($result_expense) {
    while ($row_expense = mysqli_fetch_assoc($result_expense)) {
        $expenses_dollar[$row_expense['year']] = $row_expense['total_expense_dollar'];
    }
}

// Retrieve total sales grouped by date
//$sql_sell = "SELECT date, SUM(total) AS total_sell FROM $sell_data where buy_type ='လက်ငင်း' GROUP BY year(date)";
$sql_sell = "
        SELECT 
            e.banking, 
            year(e.date) AS year, 
            b.currency, 
            SUM(e.total/e.rate) AS total_sell_dollar 
        FROM 
            $sell_data e
        JOIN 
            $banking b 
        ON 
            e.banking = b.type
        WHERE 
            b.currency = 'dollar' and buy_type ='လက်ငင်း'
        GROUP BY 
            e.banking, year(e.date), b.currency
    ";
$result_sell = mysqli_query($connection, $sql_sell);

$sales_dollar = [];
if ($result_sell) {
    while ($row_sell = mysqli_fetch_assoc($result_sell)) {
        $sales_dollar[$row_sell['year']] = $row_sell['total_sell_dollar'];
    }
}

$sql_customer_payment = "
                                            SELECT 
                                                e.banking, 
                                                YEAR(e.date) AS year,
                                                b.currency, 
                                                SUM(e.amount) AS total_customer_payment_dollar 
                                            FROM 
                                                $customer_payment e
                                            JOIN 
                                                $banking b 
                                            ON 
                                                e.banking = b.type
                                            WHERE 
                                                b.currency = 'dollar'
                                            GROUP BY 
                                                YEAR(e.date),e.banking, b.currency
                                        ";
                                        $result_customer_payment = mysqli_query($connection, $sql_customer_payment);
                                        
                                        $customer_payment_dollar = [];
                                        if ($result_customer_payment) {
                                            while ($row_customer_payment = mysqli_fetch_assoc($result_customer_payment)) {
                                                $customer_payment_dollar[$row_customer_payment['year']] = $row_customer_payment['total_customer_payment_dollar'];
                                            }
                                        }

// Retrieve total refill grouped by date
 $sql_refill = "SELECT 
                                            e.type, 
                                            YEAR(e.date) AS year,
                                            b.currency, 
                                            SUM(e.amount) AS total_refill_dollar
                                        FROM 
                                            $banking_refill e
                                        JOIN 
                                            $banking b 
                                        ON 
                                            e.type = b.type
                                        WHERE 
                                            b.currency = 'dollar'
                                        GROUP BY 
                                             YEAR(e.date)";

$result_refill = mysqli_query($connection, $sql_refill);

$refills_dollar = [];
if ($result_refill) {
    while ($row_refill = mysqli_fetch_assoc($result_refill)) {
        $refills_dollar[$row_refill['year']] = $row_refill['total_refill_dollar'];
    }
}

// Retrieve total pay_now refill grouped by date
//$sql_refill_pay_now = "SELECT date, SUM(total) AS total_refill_pay_now FROM `$product_refill` WHERE payment='pay' GROUP BY year(date)";
$sql_refill_pay_now = "
        SELECT 
            e.banking, 
            year(e.date_1) AS year, 
            b.currency, 
            SUM(e.total) AS total_refill_pay_now_dollar 
        FROM 
            $product_refill e
        JOIN 
            $banking b 
        ON 
            e.banking = b.type
        WHERE 
            b.currency = 'dollar' and payment ='pay'
        GROUP BY 
            e.banking, year(e.date_1), b.currency
    ";
$result_refill_pay_now = mysqli_query($connection, $sql_refill_pay_now);

$refills_pay_now_dollar  = [];
if ($result_refill_pay_now) {
    while ($row_refill = mysqli_fetch_assoc($result_refill_pay_now)) {
        $refills_pay_now_dollar[$row_refill['year']] = $row_refill['total_refill_pay_now_dollar'];
    }
}

// Fetch total pay_amount grouped by date
//$sql_pay_after = "SELECT date, SUM(pay_amount) AS total_pay_after FROM $pay_after GROUP BY year(date)";
$sql_pay_after = "
            SELECT 
                e.payment, 
                year(e.date) AS year, 
                b.currency, 
                SUM(e.pay_amount) AS total_pay_after_dollar 
            FROM 
                $pay_after e
            JOIN 
                $banking b 
            ON 
                e.payment = b.type
            WHERE 
                b.currency = 'dollar'
            GROUP BY 
                year(e.date), e.payment, b.currency
        ";
        $result_pay_after = mysqli_query($connection, $sql_pay_after);

        $pay_afters_dollar = [];
        if ($result_pay_after) {
            while ($row_pay_after = mysqli_fetch_assoc($result_pay_after)) {
                // Corrected the variable name here
                $pay_afters_dollar[$row_pay_after['year']] = $row_pay_after['total_pay_after_dollar'];
            }
        }


$total_remain_dollar = 0;


                                // Display the results
                                foreach ($dates as $year) {
                                     $total_sell_kyat = isset($sales_kyat[$year]) ? $sales_kyat[$year] : 0;
                                    $total_expense_kyat = isset($expenses_kyat[$year]) ? $expenses_kyat[$year] : 0;
                                    $total_refill_kyat = isset($refills_kyat[$year]) ? $refills_kyat[$year] : 0;
                                    $total_refill_pay_now_kyat = isset($refills_pay_now_kyat[$year]) ? $refills_pay_now_kyat[$year] : 0;
                                    $total_pay_after_kyat = isset($pay_afters_kyat[$year]) ? $pay_afters_kyat[$year] : 0;
                                    $total_customer_payment_kyat = isset($customer_payment_kyat[$year]) ? $customer_payment_kyat[$year] : 0;
                                    
                                    $remain_kyat = ($total_refill_kyat + $total_sell_kyat + $total_customer_payment_kyat) - ( $total_expense_kyat + $total_refill_pay_now_kyat + $total_pay_after_kyat);

                                    $total_remain_kyat += $remain_kyat;

                                    //Baht
                                    $total_sell_baht = isset($sales_baht[$year]) ? $sales_baht[$year] : 0;
                                    $total_expense_baht = isset($expenses_baht[$year]) ? $expenses_baht[$year] : 0;
                                    $total_refill_baht = isset($refills_baht[$year]) ? $refills_baht[$year] : 0;
                                    $total_refill_pay_now_baht = isset($refills_pay_now_baht[$year]) ? $refills_pay_now_baht[$year] : 0;
                                    $total_pay_after_baht = isset($pay_afters_baht[$year]) ? $pay_afters_baht[$year] : 0;
                                    $total_customer_payment_baht = isset($customer_payment_baht[$year]) ? $customer_payment_baht[$year] : 0;

                                    $remain_baht = ($total_refill_baht + $total_sell_baht + $total_customer_payment_baht) - ( $total_expense_baht + $total_refill_pay_now_baht + $total_pay_after_baht);

                                    $total_remain_baht += $remain_baht;
                                    
                                    //Yuan
                                    $total_sell_yuan = isset($sales_yuan[$year]) ? $sales_yuan[$year] : 0;
                                    $total_expense_yuan = isset($expenses_yuan[$year]) ? $expenses_yuan[$year] : 0;
                                    $total_refill_yuan = isset($refills_yuan[$year]) ? $refills_yuan[$year] : 0;
                                    $total_refill_pay_now_yuan = isset($refills_pay_now_yuan[$year]) ? $refills_pay_now_yuan[$year] : 0;
                                    $total_pay_after_yuan = isset($pay_afters_yuan[$year]) ? $pay_afters_yuan[$year] : 0;
                                    $total_customer_payment_yuan = isset($customer_payment_yuan[$year]) ? $customer_payment_yuan[$year] : 0;
                                    
                                    $remain_yuan = ($total_refill_yuan + $total_sell_yuan + $total_customer_payment_yuan) - ( $total_expense_yuan + $total_refill_pay_now_yuan + $total_pay_after_yuan);

                                    $total_remain_yuan += $remain_yuan;

                                    //Dollar
                                    $total_sell_dollar = isset($sales_dollar[$year]) ? $sales_dollar[$year] : 0;
                                    $total_expense_dollar = isset($expenses_dollar[$year]) ? $expenses_dollar[$year] : 0;
                                    $total_refill_dollar = isset($refills_dollar[$year]) ? $refills_dollar[$year] : 0;
                                    $total_refill_pay_now_dollar = isset($refills_pay_now_dollar[$year]) ? $refills_pay_now_dollar[$year] : 0;
                                    $total_pay_after_dollar = isset($pay_afters_dollar[$year]) ? $pay_afters_dollar[$year] : 0;
                                    $total_customer_payment_dollar = isset($customer_payment_dollar[$year]) ? $customer_payment_dollar[$year] : 0;
                                    
                                    $remain_dollar = ($total_refill_dollar + $total_sell_dollar + $total_customer_payment_dollar) - ( $total_expense_dollar + $total_refill_pay_now_dollar + $total_pay_after_dollar);
                                    
                                
                                    $total_remain_dollar += $remain_dollar;

                                    echo "
                                        <tr>
                                            <td>".$year."</td>
                                            <td style='text-align:right;color:#FFA62F'>".number_format($total_sell_kyat + $total_refill_kyat + $total_customer_payment_kyat)."</td>
                                            <td style='text-align:right;color:#FFA62F'>".number_format($total_expense_kyat + $total_refill_pay_now_kyat + $total_pay_after_kyat)."</td>
                                            <td style='text-align:right;color:#FFA62F'><b>".number_format($remain_kyat)."</b></td>
                                            ";

                                            echo "
                                            <td style='text-align:right;color:#42f560'>".number_format($total_sell_baht + $total_refill_baht)."</td>
                                            <td style='text-align:right;color:#42f560'>".number_format($total_expense_baht + $total_refill_pay_now_baht + $total_pay_after_baht + $total_customer_payment_baht)."</td>
                                            <td style='text-align:right;color:#42f560'><b>".number_format($remain_baht)."</b></td>
                         
                                            ";

                                            echo "
                                            <td style='text-align:right;color:#c7023a'>".number_format($total_sell_yuan + $total_refill_yuan)."</td>
                                            <td style='text-align:right;color:#c7023a'>".number_format($total_expense_yuan + $total_refill_pay_now_yuan + $total_pay_after_yuan + $total_customer_payment_yuan)."</td>
                                            <td style='text-align:right;color:#c7023a'><b>".number_format($remain_yuan)."</b></td>
                                            ";

                                            echo "
                                            <td style='text-align:right;color:#ffffff'>".number_format($total_sell_dollar + $total_refill_dollar,2)."</td>
                                            <td style='text-align:right;color:#ffffff'>".number_format($total_expense_dollar + $total_refill_pay_now_dollar + $total_pay_after_dollar + + $total_customer_payment_dollar,2)."</td>
                                            <td style='text-align:right;color:#ffffff'><b>".number_format($remain_dollar,2)."</b></td>
                                        </tr>
                                            ";
                                }

                                

                                

                                

                                // Display the total remain
                                echo "
                                    <tr>
                                        <td ><strong>Total Remain:</strong></td>
                                        <td style='text-align:right;color:#FFA62F' colspan='3'><strong>".number_format($total_remain_kyat)."</strong></td>
                                        <td style='text-align:right;color:#42f560' colspan='3'><strong>".number_format($total_remain_baht)."</strong></td>
                                        <td style='text-align:right;color:#c7023a' colspan='3'><strong>".number_format($total_remain_yuan)."</strong></td>
                                        <td style='text-align:right;color:#ffffff' colspan='3'><strong>".number_format($total_remain_dollar,2)."</strong></td>
                                    </tr>";
                                ?>


     
                                </tbody>
                            </table>

                            <script>
                          function filterTable() {
        var dateInput = document.getElementById("DateInput").value;
        var table, tr, td, i, total = 0;

        table = document.getElementById("myTable");
        tr = table.getElementsByTagName("tr");

        for (i = 1; i < tr.length; i++) {
            td = tr[i].getElementsByTagName("td")[0]; // Date column

            if (td) {
                if (dateInput === "" || td.textContent === dateInput) {
                    tr[i].style.display = "";
                } else {
                    tr[i].style.display = "none";
                }
            }
        }
    }
                        </script>

                            <!-- Delete Data -->
                    </div>

                    <!-- Kyat Detail -->
                    <h3 style="margin-top:30px">အသေးစိတ်စာရင်း (ကျပ်)</h3>
                    <div class="scrollable-table" style="height:70vh;">
                            <table class="table table-bordered rounded table-hover" style="color:white;font-size:18px;"  id="myTable">
                            <thead style="background-color:#19242e">
                            <tr>
                            <th>date</th>
                            <th>သွင်းငွေ</th>
                            <th>ရောင်းရငွေ</th>
                            <th>ဖောက်သည်ငွေချေ</th>
                            <th>အသုံစရိတ်</th>
                            <th>ဝယ်ပြီးငွေချေ</th>
                            <th>နောက်ပိုင်းငွေချေ</th>
                            <th>လက်ကျန်</th>
                        </tr>
                            </thead>
                                <tbody>
                                <?php
// Retrieve all unique dates from both tables
$total_remain_kyat = 0;
$total_remain_baht = 0;
$total_remain_yuan = 0;
$total_remain_dollar = 0;
// Display the results
foreach ($dates as $year) {
            //Kyat
             $total_sell_kyat = isset($sales_kyat[$year]) ? $sales_kyat[$year] : 0;
            $total_expense_kyat = isset($expenses_kyat[$year]) ? $expenses_kyat[$year] : 0;
            $total_refill_kyat = isset($refills_kyat[$year]) ? $refills_kyat[$year] : 0;
            $total_refill_pay_now_kyat = isset($refills_pay_now_kyat[$year]) ? $refills_pay_now_kyat[$year] : 0;
            $total_pay_after_kyat = isset($pay_afters_kyat[$year]) ? $pay_afters_kyat[$year] : 0;
            $total_customer_payment_kyat = isset($customer_payment_kyat[$year]) ? $customer_payment_kyat[$year] : 0;
            
            $remain_kyat = ($total_refill_kyat + $total_sell_kyat + $total_customer_payment_kyat) - ( $total_expense_kyat + $total_refill_pay_now_kyat + $total_pay_after_kyat);

            $total_remain_kyat += $remain_kyat;

    echo "
        <tr>
            <td>".$year."</td>
            <td style='text-align:right'>".number_format($total_refill_kyat)."</td>
            <td style='text-align:right'>".number_format($total_sell_kyat)."</td>
            <td style='text-align:right'>".number_format($total_customer_payment_kyat)."</td>
            <td style='text-align:right'>".number_format($total_expense_kyat)."</td>
            <td style='text-align:right'>".number_format($total_refill_pay_now_kyat)."</td>
            <td style='text-align:right'>".number_format($total_pay_after_kyat)."</td>
            <td style='text-align:right'>".number_format($remain_kyat)."</td>
        </tr>";
}

// Display the total remain
echo "
    <tr>
        <td ><strong>Total Remain:</strong></td>
        <td style='text-align:right' colspan='7'><strong>".number_format($total_remain_kyat)."</strong></td>
    </tr>";
?>


     
                                </tbody>
                            </table>
                           
                        </div>

                    <!-- baht Detail -->
<h3 style="margin-top:30px">အသေးစိတ်စာရင်း (ဘတ်)</h3>
                    <div class="scrollable-table" style="height:70vh;">
                            <table class="table table-bordered rounded table-hover" style="color:white;font-size:18px;"  id="myTable">
                            <thead style="background-color:#19242e">
                            <tr>
                            <th>date</th>
                            <th>သွင်းငွေ</th>
                            <th>ရောင်းရငွေ</th>
                             <th>ဖောက်သည်ငွေချေ</th>
                            <th>အသုံစရိတ်</th>
                            <th>ဝယ်ပြီးငွေချေ</th>
                            <th>နောက်ပိုင်းငွေချေ</th>
                            <th>လက်ကျန်</th>
                        </tr>
                            </thead>
                                <tbody>
                                <?php
// Retrieve all unique dates from both tables

// Display the results
foreach ($dates as $year) {
            //baht
            $total_sell_baht = isset($sales_baht[$year]) ? $sales_baht[$year] : 0;
            $total_expense_baht = isset($expenses_baht[$year]) ? $expenses_baht[$year] : 0;
            $total_refill_baht = isset($refills_baht[$year]) ? $refills_baht[$year] : 0;
            $total_refill_pay_now_baht = isset($refills_pay_now_baht[$year]) ? $refills_pay_now_baht[$year] : 0;
            $total_pay_after_baht = isset($pay_afters_baht[$year]) ? $pay_afters_baht[$year] : 0;
            $total_customer_payment_baht = isset($customer_payment_baht[$year]) ? $customer_payment_baht[$year] : 0;

            $remain_baht = ($total_refill_baht + $total_sell_baht + $total_customer_payment_baht) - ( $total_expense_baht + $total_refill_pay_now_baht + $total_pay_after_baht);

            $total_remain_baht += $remain_baht;

    echo "
        <tr>
            <td>".$year."</td>
            <td style='text-align:right'>".number_format($total_refill_baht)."</td>
            <td style='text-align:right'>".number_format($total_sell_baht)."</td>
            <td style='text-align:right'>".number_format($total_customer_payment_baht)."</td>
            <td style='text-align:right'>".number_format($total_expense_baht)."</td>
            <td style='text-align:right'>".number_format($total_refill_pay_now_baht)."</td>
            <td style='text-align:right'>".number_format($total_pay_after_baht)."</td>
            <td style='text-align:right'>".number_format($remain_baht)."</td>
        </tr>";
}

// Display the total remain
echo "
    <tr>
        <td ><strong>Total Remain:</strong></td>
        <td style='text-align:right' colspan='7'><strong>".number_format($total_remain_baht)."</strong></td>
    </tr>";
?>


     
                                </tbody>
                            </table>
                           
                        </div>

<!-- yuan Detail -->
<h3 style="margin-top:30px">အသေးစိတ်စာရင်း (ယွမ်)</h3>
                    <div class="scrollable-table" style="height:70vh;">
                            <table class="table table-bordered rounded table-hover" style="color:white;font-size:18px;"  id="myTable">
                            <thead style="background-color:#19242e">
                            <tr>
                            <th>date</th>
                            <th>သွင်းငွေ</th>
                            <th>ရောင်းရငွေ</th>
                             <th>ဖောက်သည်ငွေချေ</th>
                            <th>အသုံစရိတ်</th>
                            <th>ဝယ်ပြီးငွေချေ</th>
                            <th>နောက်ပိုင်းငွေချေ</th>
                            <th>လက်ကျန်</th>
                        </tr>
                            </thead>
                                <tbody>
                                <?php
// Retrieve all unique dates from both tables

// Display the results
foreach ($dates as $year) {
            //yuan
            $total_sell_yuan = isset($sales_yuan[$year]) ? $sales_yuan[$year] : 0;
            $total_expense_yuan = isset($expenses_yuan[$year]) ? $expenses_yuan[$year] : 0;
            $total_refill_yuan = isset($refills_yuan[$year]) ? $refills_yuan[$year] : 0;
            $total_refill_pay_now_yuan = isset($refills_pay_now_yuan[$year]) ? $refills_pay_now_yuan[$year] : 0;
            $total_pay_after_yuan = isset($pay_afters_yuan[$year]) ? $pay_afters_yuan[$year] : 0;
            $total_customer_payment_yuan = isset($customer_payment_yuan[$year]) ? $customer_payment_yuan[$year] : 0;
            
            $remain_yuan = ($total_refill_yuan + $total_sell_yuan + $total_customer_payment_yuan) - ( $total_expense_yuan + $total_refill_pay_now_yuan + $total_pay_after_yuan);

            $total_remain_yuan += $remain_yuan;

    echo "
        <tr>
            <td>".$year."</td>
            <td style='text-align:right'>".number_format($total_refill_yuan)."</td>
            <td style='text-align:right'>".number_format($total_sell_yuan)."</td>
            <td style='text-align:right'>".number_format($total_customer_payment_yuan)."</td>
            <td style='text-align:right'>".number_format($total_expense_yuan)."</td>
            <td style='text-align:right'>".number_format($total_refill_pay_now_yuan)."</td>
            <td style='text-align:right'>".number_format($total_pay_after_yuan)."</td>
            <td style='text-align:right'>".number_format($remain_yuan)."</td>
        </tr>";
}

// Display the total remain
echo "
    <tr>
        <td ><strong>Total Remain:</strong></td>
        <td style='text-align:right' colspan='7'><strong>".number_format($total_remain_yuan)."</strong></td>
    </tr>";
?>


     
                                </tbody>
                            </table>
                           
                        </div>

<!-- dollar Detail -->
<h3 style="margin-top:30px">အသေးစိတ်စာရင်း (ဒေါ်လာ)</h3>
                    <div class="scrollable-table" style="height:70vh;">
                            <table class="table table-bordered rounded table-hover" style="color:white;font-size:18px;"  id="myTable">
                            <thead style="background-color:#19242e">
                            <tr>
                            <th>date</th>
                            <th>သွင်းငွေ</th>
                            <th>ရောင်းရငွေ</th>
                             <th>ဖောက်သည်ငွေချေ</th>
                            <th>အသုံစရိတ်</th>
                            <th>ဝယ်ပြီးငွေချေ</th>
                            <th>နောက်ပိုင်းငွေချေ</th>
                            <th>လက်ကျန်</th>
                        </tr>
                            </thead>
                                <tbody>
                                <?php
// Retrieve all unique dates from both tables

// Display the results
foreach ($dates as $year) {
            //dollar
            $total_sell_dollar = isset($sales_dollar[$year]) ? $sales_dollar[$year] : 0;
            $total_expense_dollar = isset($expenses_dollar[$year]) ? $expenses_dollar[$year] : 0;
            $total_refill_dollar = isset($refills_dollar[$year]) ? $refills_dollar[$year] : 0;
            $total_refill_pay_now_dollar = isset($refills_pay_now_dollar[$year]) ? $refills_pay_now_dollar[$year] : 0;
            $total_pay_after_dollar = isset($pay_afters_dollar[$year]) ? $pay_afters_dollar[$year] : 0;
            $total_customer_payment_dollar = isset($customer_payment_dollar[$year]) ? $customer_payment_dollar[$year] : 0;
            
            $remain_dollar = ($total_refill_dollar + $total_sell_dollar + $total_customer_payment_dollar) - ( $total_expense_dollar + $total_refill_pay_now_dollar + $total_pay_after_dollar);


    echo "
        <tr>
            <td>".$year."</td>
            <td style='text-align:right'>".number_format($total_refill_dollar,2)."</td>
            <td style='text-align:right'>".number_format($total_sell_dollar,2)."</td>
            <td style='text-align:right'>".number_format($total_customer_payment_dollar,2)."</td>
            <td style='text-align:right'>".number_format($total_expense_dollar,2)."</td>
            <td style='text-align:right'>".number_format($total_refill_pay_now_dollar,2)."</td>
            <td style='text-align:right'>".number_format($total_pay_after_dollar,2)."</td>
            <td style='text-align:right'>".number_format($remain_dollar,2)."</td>
        </tr>";
}

// Display the total remain
echo "
    <tr>
        <td ><strong>Total Remain:</strong></td>
        <td style='text-align:right' colspan='7'><strong>".number_format($total_remain_dollar,2)."</strong></td>
    </tr>";
?>


     
                                </tbody>
                            </table>
                           
                        </div>

             
                </div>
            </main>


            <a href="#" class="theme-toggle">
                <i class="fa-regular fa-moon"></i>
                <i class="fa-regular fa-sun"></i>
            </a>
            <footer class="footer">
                <div class="container-fluid">
                    <div class="row text-muted">
                        <div class="col-6 text-start">
                            <p class="mb-0">
                                <a href="#" class="text-muted">
                                    <?php 
                                        include "version.php";
                                    ?>
                                </a>
                            </p>
                        </div>
                        <div class="col-6 text-end">
                            <ul class="list-inline">
                                <li class="list-inline-item">
                                    <a href="#" class="text-muted">Contact</a>
                                </li>
                                <li class="list-inline-item">
                                    <a href="#" class="text-muted">About Us</a>
                                </li>
                                <li class="list-inline-item">
                                    <a href="#" class="text-muted">Terms</a>
                                </li>
                                <li class="list-inline-item">
                                    <a href="#" class="text-muted">Booking</a>
                                </li>
                            </ul>
                        </div>
                    </div>
                </div>
            </footer>
        </div>
    </div>
    

<?php 

function generateUniqueBrowserId()
{
    $randomString = bin2hex(random_bytes(16));
    $timestamp = time();

    return $randomString . $timestamp;
}

?>


</body>
<script src="js/script.js"></script>
    <link rel="stylesheet" href="extensions/sticky-header/bootstrap-table-sticky-header.css">
<link rel="stylesheet" href="extensions/fixed-columns/bootstrap-table-fixed-columns.css">
<script src="extensions/sticky-header/bootstrap-table-sticky-header.js"></script>
<script src="extensions/fixed-columns/bootstrap-table-fixed-columns.js"></script>

</html>
