<?php
include "top.php";

$stdt = isset($_REQUEST['stdt']) ? $_REQUEST['stdt'] : '';
$endt = isset($_REQUEST['endt']) ? $_REQUEST['endt'] : '';
$bl_area = isset($_REQUEST['bl_area']) ? $_REQUEST['bl_area'] : '0';

$area_filter = ($bl_area != '0') ? " AND area_id='$bl_area'" : "";
$q = "SELECT * FROM adduser WHERE is_cable=1 AND pexpiry BETWEEN '$stdt' AND '$endt' $area_filter ORDER BY pexpiry ASC";
$getc = mysqli_query($con, $q);

if ($bl_area != '0') {
    $geta = f(q("SELECT arname FROM tbl_area WHERE arid='$bl_area'"));
    $aname = $geta['arname'];
} else {
    $aname = "All Areas";
}

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=enddate_report.xls");

?>
<table border="1">
    <thead>
        <tr>
            <th colspan="10" style="font-size:16px; font-weight:bold; height:30px;">End Date Wise Customer Report</th>
        </tr>
        <tr>
            <th colspan="10">Period: <?php echo date("d-m-Y", strtotime($stdt)); ?> to
                <?php echo date("d-m-Y", strtotime($endt)); ?> | Area: <?php echo $aname; ?>
            </th>
        </tr>
        <tr style="background-color: #f4f4f4;">
            <th>Sl</th>
            <th>Customer Name</th>
            <th>Phone</th>
            <th>Area</th>
            <th>VC Number</th>
            <th>A/C No</th>
            <th>Expiry Date</th>
            <th>STB Number</th>
            <th>Price</th>
            <th>Last Pay Dt & Amt</th>
            <th>Last Bill Due Amount</th>
        </tr>
    </thead>
    <tbody>
        <?php
        $c = 0;
        while ($arr = mysqli_fetch_array($getc)) {
            $c++;
            $get_last_bill = f(q("SELECT bl_due,bl_payamt FROM cbill WHERE bl_cid='{$arr['id']}' ORDER BY blid DESC LIMIT 1"));
            $last_bill_due_amt = !empty($get_last_bill) ? $get_last_bill['bl_due'] : '-';
            $last_bill_payamt = !empty($get_last_bill) ? $get_last_bill['bl_payamt'] : '-';
            ?>
            <tr>
                <td><?php echo $c; ?></td>
                <td><?php echo $arr['name']; ?></td>
                <td><?php echo $arr['phone']; ?></td>
                <td><?php echo $arr['area_name']; ?></td>
                <td><?php echo $arr['vc']; ?></td>
                <td><?php echo $arr['custid']; ?></td>
                <td><?php echo date("d-m-Y", strtotime($arr['pexpiry'])); ?></td>
                <td><?php echo $arr['stb']; ?></td>
                <td><?php echo $arr['price']; ?></td>
                <td><?php echo $arr['lpaydt']; ?><br> Rs
                    <?php echo $last_bill_payamt; ?>
                </td>
                <td>Rs <?php echo $last_bill_due_amt; ?></td>
            </tr>
        <?php } ?>
        <?php if ($c == 0) { ?>
            <tr>
                <td colspan="10" style="text-align:center;">No records found for the selected criteria.</td>
            </tr>
        <?php } ?>
    </tbody>
</table>