<? include "top.php"; ?>
<!DOCTYPE html>
<html>
   <head>
      <?php include('include/head.php'); 
         $stdt=$_POST['stdt'];
            $enddt=$_POST['enddt'];
            $customer=$_POST['customer'];
            $area_id=$_POST['area_id'];
            $getarea=f(q("SELECT * FROM tbl_area WHERE arid='$area_id'"));
            $areaname=$getarea['arname'];
         ?>
      <title>Area Wise Report for <?=$areaname?> from <?=$stdt?> to <?=$enddt?></title>
      <link rel="stylesheet" type="text/css" href="src/plugins/datatables/media/css/jquery.dataTables.css">
      <link rel="stylesheet" type="text/css" href="src/plugins/datatables/media/css/dataTables.bootstrap4.css">
      <link rel="stylesheet" type="text/css" href="src/plugins/datatables/media/css/responsive.dataTables.css">
      <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/sweetalert2@9"></script>
      <style type="text/css">
         th{
         font-size: 12px !important;
         background-color: #bad5e4 !important;
         }
         td{
         font-size: 10px !important;
         }
         table.report-table {
            width: 100%;
            border-collapse: collapse;
         }
         table.report-table, table.report-table th, table.report-table td {
            border: 1px solid #000000;
         }
         thead > tr > th{
            background-color: #bad5e4;
            font-weight: bold;
         }
         .total-row {
             font-weight: bold;
             background-color: #e6f7ff;
         }
         .export-btn {
             margin: 10px;
             padding: 5px 15px;
             background: #28a745;
             color: white;
             border: none;
             border-radius: 4px;
             cursor: pointer;
         }
         body {
             background-color: white; 
             padding: 3%;
         }
      </style>
   </head>
   <body>
      <br>
      <center>
         <h4>Area Wise Report for <?=$areaname?> from <?=$stdt?> to <?=$enddt?></h4>
      </center>
      <br>
      <button class="export-btn" onclick="exportTableToExcel()">Export to Excel</button>
      <table id="reportTable" class="stripe report-table">
         <thead>
            <tr>
               <th class="table-plus datatable-nosort">Sl. No</th>
               <th>Customer Id</th>
               <th>Customer Name</th>
               <th>Phone</th>
               <th>MSO No</th>
               <th>Address</th>
               <th>Start Date</th>
               <th>End Date</th>
               <th>Bill No</th>
               <th>Current Amt</th>
               <th>CGST</th>
               <th>SGST</th>
               <th>Bill Amt(incl Prev Due)</th>
               <th>Paid Amt</th>
               <th>Due Amt</th>
               <th>Pay Date</th>
            </tr>
         </thead>
         <tbody>
            <?
            // Initialize total variables
            $total_withoutgst = 0;
            $total_cgst = 0;
            $total_sgst = 0;
            $total_billamt = 0;
            $total_paidamt = 0;
            $total_dueamt = 0;
            
            $sql=mysqli_query($con,"SELECT * FROM `adduser` WHERE is_cable='1' AND area_id='$area_id'");
            $c = 0;
            while($row=mysqli_fetch_array($sql)){
                $c++;
                $query=mysqli_query($con,"SELECT * FROM `cbill` WHERE bl_cid=$row[id] AND bl_stdt BETWEEN '$stdt' AND '$enddt'");
                while($arrc=mysqli_fetch_array($query)){
                    // Calculate GST values
                    $noprevdue = $arrc['bl_amt'];   
                    $gstamt = floatval($noprevdue) * (18/118); 
                    $cgst=round(($gstamt)/2,2);
                    $sgst=round(($gstamt)/2,2);
                    $withoutgst=round($noprevdue-($cgst+$sgst),2);
                    $billamt=floatval($arrc['bl_amt'])+floatval($arrc['bl_prevdue']);
                    
                    // Add to totals
                    $total_withoutgst += $withoutgst;
                    $total_cgst += $cgst;
                    $total_sgst += $sgst;
                    $total_billamt += $billamt;
                    $total_paidamt += $arrc['bl_payamt'];
                    $total_dueamt += $arrc['bl_due'];
            ?>
            <tr>
               <td class="table-plus"><?=$c?></td>
               <td><?=$row['custid']?></td>
               <td><?=$row['name']?></td>
               <td><?=$row['phone']?></td>
               <td><?=$row['msoacno']?></td>
               <td><?=$row['address']?></td>
               <td><?=$arrc['bl_stdt']?></td>
               <td><?=$arrc['bl_expiry']?></td>
               <td><?=$arrc['bl_no']?></td>
               <td><?=number_format($withoutgst, 2)?></td>
               <td><?=number_format($cgst, 2)?></td>
               <td><?=number_format($sgst, 2)?></td>
               <td><?=number_format($billamt, 2)?></td>
               <td><?=number_format($arrc['bl_payamt'], 2)?></td>
               <td><?=number_format($arrc['bl_due'], 2)?></td>
               <td><?=$arrc['bl_paydt']?></td>
            </tr>
            <?}}?>	
            <!-- Total Row -->
            <tr class="total-row">
               <td colspan="9"><strong>Total</strong></td>
               <td><strong><?=number_format($total_withoutgst, 2)?></strong></td>
               <td><strong><?=number_format($total_cgst, 2)?></strong></td>
               <td><strong><?=number_format($total_sgst, 2)?></strong></td>
               <td><strong><?=number_format($total_billamt, 2)?></strong></td>
               <td><strong><?=number_format($total_paidamt, 2)?></strong></td>
               <td><strong><?=number_format($total_dueamt, 2)?></strong></td>
               <td></td>
            </tr>
         </tbody>
      </table>
      <?php include('include/script.php'); ?>
      <script src="src/plugins/datatables/media/js/jquery.dataTables.min.js"></script>
      <script src="src/plugins/datatables/media/js/dataTables.bootstrap4.js"></script>
      <script src="src/plugins/datatables/media/js/dataTables.responsive.js"></script>
      <script src="src/plugins/datatables/media/js/responsive.bootstrap4.js"></script>
      
      <script>
         $(document).ready(function(){
             $('#reportTable').DataTable({
                 scrollCollapse: true,
                 autoWidth: false,
                 responsive: true,
                 paging: false,
                 lengthChange: false,
                 searching: false,
                 columnDefs: [{
                     targets: "datatable-nosort",
                     orderable: false,
                 }]
             });
         });
         
         function exportTableToExcel() {
             // Create a temporary table with inline styles for Excel export
             var table = document.getElementById('reportTable');
             var clone = table.cloneNode(true);
             
             // Add inline styles for Excel
             clone.classList.add('report-table');
             var rows = clone.getElementsByTagName('tr');
             for (var i = 0; i < rows.length; i++) {
                 var cells = rows[i].getElementsByTagName('td');
                 for (var j = 0; j < cells.length; j++) {
                     cells[j].style.border = '1px solid #000000';
                     cells[j].style.padding = '5px';
                 }
                 var headers = rows[i].getElementsByTagName('th');
                 for (var k = 0; k < headers.length; k++) {
                     headers[k].style.border = '1px solid #000000';
                     headers[k].style.padding = '5px';
                     headers[k].style.backgroundColor = '#bad5e4';
                     headers[k].style.fontWeight = 'bold';
                 }
             }
             
             // Add style for total row
             var totalRow = clone.querySelector('.total-row');
             if (totalRow) {
                 totalRow.style.backgroundColor = '#e6f7ff';
                 totalRow.style.fontWeight = 'bold';
             }
             
             var html = clone.outerHTML;
             var url = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(html);
             var link = document.createElement("a");
             link.setAttribute("href", url);
             link.setAttribute("download", "area_wise_report.xls");
             document.body.appendChild(link);
             link.click();
             document.body.removeChild(link);
         }
      </script>
   </body>
</html>