<?php
session_start();

// Include your database connection script
require 'connection.php'; 

// Check if user is logged in and has permissions
if (isset($_SESSION['userId']) && ($_SESSION['userId'] == 52 || $_SESSION['userId'] == 2532)) {
    
    // Check if accid is set in the URL
    if (isset($_GET['accid'])) {
        $accid = $_GET['accid'];
        $from = $_GET['from'];
        $to = $_GET['to'];

        // Query to fetch account details
        $qry = "SELECT * FROM accreg WHERE accid = '$accid'";
        $run = mysqli_query($connect, $qry);
        $row = mysqli_fetch_array($run);
        $name = $row['accname'];

        // Query to find opening balance
        $qry1 = "SELECT SUM(debit) - SUM(credit) AS cashOp FROM ledger WHERE accid = '$accid' AND dated < '$from'";
        $run_qry1 = mysqli_query($connect, $qry1);
        $row1 = mysqli_fetch_array($run_qry1);
        $cashOp = $row1['cashOp'];

        // Query to find closing balance and details
        $qry2 = "SELECT SUM(debit) - SUM(credit) AS close, MIN(dated) AS firstdate, MAX(dated) AS lastdate FROM ledger WHERE accid = '$accid' AND dated <= '$to'";
        $run_qry2 = mysqli_query($connect, $qry2);
        $row2 = mysqli_fetch_array($run_qry2);
        $closingB = $row2['close'];
        $fdate = date_format(date_create($row2['firstdate']), "d-m-Y");
        $ldate = date_format(date_create($row2['lastdate']), "d-m-Y");

        // Initial values
        $debitT = 0;
        $qtytotal = 0;
        ?>

        <!-- HTML Content Starts Here -->
        <!DOCTYPE html>
        <html lang="en">
        <head>
            <meta charset="UTF-8">
            <meta name="viewport" content="width=device-width, initial-scale=1.0">
            <title>Account Ledger</title>
            <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
            <style>
                /* Custom table styling */
                .table {
                    width: 100%;
                    max-width: 100%;
                    margin-bottom: 1rem;
                    background-color: #fff;
                    border-collapse: collapse;
                    box-shadow: 0 1px 4px rgba(0, 0, 0, 0.1);
                }
                .table th, .table td {
                    padding: 0.75rem;
                    vertical-align: top;
                    border-top: 1px solid #dee2e6;
                }
                .table th {
                    background-color: #f8f9fa;
                }
                .table-striped tbody tr:nth-of-type(odd) {
                    background-color: #ccd6db;
                }
                .table-hover tbody tr:hover {
                    background-color: rgba(0, 0, 0, 0.075);
                }

    @media print {
            #closebtn, #printbtn {
                display: none;
            }
            }
                
            </style>
        </head>
        <body>

        <div class="container">
            <button class="btn btn-warning btn-sm" id="closebtn" onclick="window.close()">
                <i class="glyphicon glyphicon-off"></i> Close
            </button>
            <button class="btn btn-primary btn-sm" id="printbtn" onclick="window.print()">
                <i class="glyphicon glyphicon-print"></i> Print
            </button>
            <br><br>

            <h1>Account Ledger</h1>
            <h4>Account Name: <?php echo $name; ?></h4>

            <table class="table table-striped table-bordered table-hover">
                <thead>
                    <tr>
                        <th>Dated</th>
                        <th>Qty</th>
                        <th>Rate</th>
                        <th>Bwp Rate</th>
                        <th>Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    // Fetch transaction details
                    $qry = "SELECT SUM(qty) AS qty, crate, dated FROM broilertransfer WHERE accid='$accid' AND dated>='$from' AND dated<='$to' GROUP BY crate, dated ORDER BY dated, trid ASC";
                    $run_qry = mysqli_query($connect, $qry);

                    // Loop through the results and display in the table
                    while ($row = mysqli_fetch_array($run_qry)) {
                        $qtytotal += $row['qty'];
                        $crate = $row['crate'];
                        $lgrdated = $row['dated'];
                        $qty = $row['qty'];
                        $balok = $crate * $qty;
                        $debitT += $balok;

                        // Fetch Bwp Rate from another table
                        $qrybrldt = "SELECT bwp FROM brllist WHERE dated='$lgrdated'";
                        $run_qrybrldt = mysqli_query($connect, $qrybrldt);
                        $rowbrldt = mysqli_fetch_array($run_qrybrldt);
                        $brldt = ($run_qrybrldt->num_rows > 0) ? $rowbrldt['bwp'] : '';

                        ?>
                        <tr>
                            <td><?php echo date_format(date_create($row['dated']), "d-m-Y"); ?></td>
                            <td><?php echo $row['qty']; ?></td>
                            <td><?php echo $row['crate']; ?></td>
                            <td><?php echo $brldt; ?></td>
                            <td><?php echo $balok; ?></td>
                        </tr>
                        <?php
                    }
                    ?>
                    <tr>
                        <td></td>
                        <td><strong><?php echo $qtytotal; ?></strong></td>
                        <td colspan="2"></td>
                        <td><strong><?php echo $debitT; ?></strong></td>
                    </tr>
                </tbody>
            </table>
        </div>

        </body>
        </html>

        <?php
    } else {
        header('location: index.php'); // Redirect if accid is not set
    }
} else {
    header('location: index.php'); // Redirect if user is not logged in or doesn't have correct permissions
}
?>
