<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

session_start();
include("dbConfig.php");

if (isset($_SESSION['username']) && isset($_SESSION['name'])) {
    $username = $_SESSION['username'];
    $name = $_SESSION['name'];
} else {
    echo '<META http-equiv="refresh" content="0;logout.php">';
}

if (isset($_POST['datefrom'])) {
    $from = $_POST['datefrom'];
    $to = $_POST['dateto'];
    mysqli_query($db, "SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");

    if ($from == $to) {
        $db->query("SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");

        // Query 1: IPD Advance with patient name
     $query1 = mysqli_query($db, "SELECT a.*, p.Name 
                              FROM advance a 
                              LEFT JOIN patientdetails p ON a.PatId = p.PatId 
                              WHERE a.AdvanceDate='$from' 
                              AND a.is_deleted != 0
                              ORDER BY a.BillNo");


       

       $query2 = mysqli_query($db, "SELECT f.*,
                                        f.FinalBillNo, 
                                        f.PatientId, 
                                        f.DischargeDate, 
                                        f.PayableAmount AS Amount, 
                                        f.PaymentMode, 
                                        p.Name, 
                                        'Discharge' AS BillType
                                    FROM finalbill f
                                    LEFT JOIN patientdetails p ON f.PatientId = p.PatId
                                    WHERE f.DischargeDate BETWEEN '$from' AND '$to'
                                    AND f.is_deleted != 0
                                    ORDER BY FinalBillNo
                                ");



        // Query 3: Inbill with patient name
        $query3 = mysqli_query($db, "SELECT i.*, p.Name 
                                    FROM inbill i 
                                    LEFT JOIN patientdetails p ON i.PatientId = p.PatId 
                                    WHERE i.BDate='$from' AND i.is_deleted != 0 
                                    ORDER BY i.BillNo");

        // Query 4: OPD bill with patient name
      $query4 = mysqli_query($db, "SELECT b.*, o.Name 
                                    FROM bill b 
                                    LEFT JOIN opdpatientregister o ON b.PatientId = o.PatientId 
                                    WHERE b.BDate = '$from'  
                                    AND b.is_deleted != 0  
                                    ORDER BY b.BillNo");


        // Query 5: Examination with patient name
        $query5 = mysqli_query($db, "SELECT e.*, p.Name,
                                           (SELECT SUM(Amount) 
                                            FROM examination 
                                            WHERE BillNo = e.BillNo) AS TotalAmount
                                    FROM examination e
                                    LEFT JOIN patientdetails p ON e.PatId = p.PatId
                                    WHERE e.BDate = '$from'
                                    AND e.is_deleted != 0 
                                    GROUP BY e.BillNo
                                    ORDER BY e.BillNo");


      $query6 = mysqli_query($db, "SELECT 
                                    b.BillNo AS FinalBillNo, 
                                    b.PatientId, 
                                    b.BDate AS DischargeDate, 
                                    b.PayableAmount AS Amount, 
                                    b.PaymentMode, 
                                    p.Name, 
                                    'Consolidate' AS BillType
                                FROM bill b
                                LEFT JOIN patientdetails p ON b.PatientId = p.PatId
                                WHERE b.BDate= '$from'
                                AND b.PatientType = 'IPD'
                                AND b.is_deleted != 0  
                                AND b.PatientId NOT IN (
                                    SELECT f.PatientId 
                                    FROM finalbill f
                                    WHERE f.DischargeDate= '$from'
                                    AND f.is_deleted != 0 
                                )
                                ORDER BY FinalBillNo
                            ");

    } else {
        // For date range condition
        // Query 1: IPD Advance with patient name
      $query1 = mysqli_query($db, "SELECT a.*, p.Name 
                              FROM advance a 
                              LEFT JOIN patientdetails p ON a.PatId = p.PatId 
                              WHERE a.AdvanceDate >= '$from' AND a.AdvanceDate <= '$to' 
                              AND a.is_deleted != 0
                              ORDER BY a.BillNo");

        // Query 2: OPD Bills section
      $query2 = mysqli_query($db, "SELECT f.*,
                                        f.FinalBillNo, 
                                        f.PatientId, 
                                        f.DischargeDate, 
                                        f.PayableAmount AS Amount, 
                                        f.PaymentMode, 
                                        p.Name, 
                                        'Discharge' AS BillType
                                    FROM finalbill f
                                    LEFT JOIN patientdetails p ON f.PatientId = p.PatId
                                    WHERE f.DischargeDate >= '$from' 
                                    AND f.DischargeDate <= '$to'
                                    AND f.is_deleted != 0  
                                    ORDER BY f.FinalBillNo
                                ");



        // Query 3: Inbill with patient name
        $query3 = mysqli_query($db, "SELECT i.*, p.Name 
                                    FROM inbill i 
                                    LEFT JOIN patientdetails p ON i.PatientId = p.PatId 
                                    WHERE i.BDate >= '$from' AND i.BDate <= '$to'  AND i.is_deleted != 0
                                    ORDER BY i.BillNo");

        // Query 4: OPD bill with patient name
      $query4 = mysqli_query($db, "SELECT b.*, o.Name 
                                    FROM bill b 
                                    LEFT JOIN opdpatientregister o ON b.PatientId = o.PatientId 
                                    WHERE b.BDate >= '$from' AND b.BDate <= '$to'
                                    AND b.is_deleted != 0  
                                    ORDER BY b.BillNo");


        // Query 5: Examination with patient name
        $query5 = mysqli_query($db, "SELECT e.*, p.Name,
                                           (SELECT SUM(Amount) 
                                            FROM examination 
                                            WHERE BillNo = e.BillNo) AS TotalAmount
                                    FROM examination e
                                    LEFT JOIN patientdetails p ON e.PatId = p.PatId
                                    WHERE e.BDate >= '$from' AND e.BDate <= '$to'
                                    AND e.is_deleted != 0 
                                    GROUP BY e.BillNo
                                    ORDER BY e.BillNo");


       $query6 = mysqli_query($db, "SELECT 
                                        b.BillNo AS FinalBillNo, 
                                        b.PatientId, 
                                        b.BDate AS DischargeDate, 
                                        b.PayableAmount AS Amount, 
                                        b.PaymentMode, 
                                        p.Name, 
                                        'Consolidate' AS BillType
                                    FROM bill b
                                    LEFT JOIN patientdetails p ON b.PatientId = p.PatId
                                    WHERE b.BDate >= '$from' AND b.BDate <= '$to'
                                    AND b.PatientType = 'IPD'
                                    AND b.is_deleted != 0  -- Exclude deleted records in the bill table
                                    AND b.PatientId NOT IN (
                                        SELECT f.PatientId 
                                        FROM finalbill f
                                        WHERE f.DischargeDate >= '$from' AND f.DischargeDate <= '$to'
                                        AND f.is_deleted != 0 
                                    )
                                    ORDER BY FinalBillNo
                                ");

    }


    if (!$query1) {
        die("Error in query1: " . mysqli_error($db));
    }
    if (!$query2) {
        die("Error in query2: " . mysqli_error($db));
    }
    if (!$query3) {
        die("Error in query3: " . mysqli_error($db));
    }
    if (!$query4) {
        die("Error in query4: " . mysqli_error($db));
    }
    if (!$query5) {
        die("Error in query5: " . mysqli_error($db));
    }
}

?>
<!DOCTYPE html>
<html>

<head>
    <title>Daily Transactions</title>
    <meta charset="UTF-8">
    <link href="assets/img/favicon.png" rel="icon">
    <link href="assets/img/apple-touch-icon.png" rel="apple-touch-icon">
    <script src='https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js'></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js'></script>
    <link href='https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.0.3/css/font-awesome.css'>
    <link href='https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css'>
    <link href="https://fonts.googleapis.com/css?family=Open+Sans:300,300i,400,400i,700,700i|Raleway:300,400,500,700,800|Montserrat:300,400,700" rel="stylesheet">
    <link href="assets/vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <link href="assets/css/style.css" rel="stylesheet">
    <style type="text/css">
        .nav-menu a {
            padding: 3px 3px;
        }

        .hide {
            display: none;
        }

        h4 {
            padding-top: 5px;
            padding-bottom: 0px;
            color: #1abfbf;
            font-weight: bold;
        }

        hr {
            background-color: #1abfbf;
        }

        .footer {
            position: fixed;
            left: 0;
            bottom: 0;
            width: 100%;
            background-color: #efe8e8;
            color: black;
            text-align: center;
            padding: 4px;
            font-size: 12px;
        }

        .footer a {
            font-weight: bold;
            color: #0c0c67;
        }
    </style>
    <style>
        /* Styles for the summary section */
        .summary-table {
            border-collapse: collapse;
            width: 100%;
            margin-top: 20px;
        }

        .summary-table th,
        .summary-table td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }

        .summary-table th {
            background-color: #f2f2f2;
            font-weight: bold;
        }

        .summary-row {
            background-color: #e7f3fe;
            box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
        }

        .summary-title {
            font-weight: bold;
            color: #004085;
            /* Dark blue */
        }

        .summary-value {
            font-weight: bold;
            color: #155724;
            /* Dark green */
        }
    </style>
</head>

<body>
    <div style="text-align: center">
        <img src="assets/img/logo.png" width="100px" height="100px">
    </div>

    <header id="header">
        <div class="container">
            <nav id="nav-menu-container">
                <ul class="nav-menu">
                    <li class="menu-active">
                        <a class="nav-link" style="padding:2;font-size:14px" href="index.php">Home</a>
                    </li>
                    <li class="menu-has-children">
                        <a class="nav-link" style="padding:2;font-size:14px" href="#">IPD Patients</a>
                        <ul>
                            <li><a href="AddPatient.php">IPD Patient Registration</a></li>
                            <li><a href="AddAdvance.php">IPD add Advance</a></li>
                            <li><a href="UpdateWard.php">Update Ward</a></li>
                            <li><a href="IPDPatients.php">View IPD Patients</a></li>

                        </ul>
                    </li>
                    <li class="menu-has-children">
                        <a class="nav-link" style="padding:2;font-size:14px" href="#">OPD Patients</a>
                        <ul>
                            <li><a href="OPDRegistration.php">OPD Patient Registration</a></li>
                            <li><a href="OPDPatients.php">View OPD Patients</a></li>
                        </ul>
                    </li>
                    <li class="menu-has-children">
                        <a class="nav-link" style="padding:2;font-size:14px" href="#">Staff</a>
                        <ul>
                            <li><a href="AddStaff.php">Add Staff</a></li>
                            <li><a href="StaffList.php">Staff List</a></li>
                        </ul>
                    </li>
                    <li class="menu-has-children">
                        <a class="nav-link" style="padding:2;font-size:14px" href="#">Billing</a>
                        <ul>
                            <li><a href="billingId.php">Generate Consolidate Bill</a></li>
                            <li><a href="InvestigationId.php">Generate Investigation Bill</a></li>
                            <li><a href="FinaleBillId.php">Generate Discharge Bill</a></li>
                            <li><a href="SearchBill.php">Search Bill</a></li>
                        </ul>
                    </li>
                    <li class="menu-has-children">
                        <a class="nav-link" style="padding:2;font-size:14px" href="#">Particular</a>
                        <ul>
                            <li><a href="AddParticulars.php">Add Particulars</a></li>
                            <li><a href="ViewParticulars.php">View Particulars</a></li>
                        </ul>
                    </li>
                    <li class="menu-has-children">
                        <a class="nav-link" style="padding:2;font-size:14px" href="#">Report</a>
                        <ul>
                            <li><a href="UploadReport.php">Upload Patient Report</a></li>
                            <li><a href="DailyTrans.php">Transactions</a></li>
                            <li><a href="userTrans.php">User Transactions</a></li>
                            <li><a href="ViewReport.php">View Patient Reports</a></li>
                        </ul>
                    </li>
                    <li class="menu-has-children">
                        <a class="nav-link" style="padding:2;font-size:14px" href="#" style="font-size:15px"><?php echo $name ?></a>
                        <ul>
                            <li><a href="logout.php">Logout</a></li>
                            <li><a href="ResetPassword.php">Reset Password</a></li>
                        </ul>
                    </li>
                </ul>
            </nav><!-- #nav-menu-container -->
        </div>
    </header>

    <section>
        <div class="row">
            <div class="col-md-1"></div>
            <div class="col-md-10">
                <div class="shadow p-3 mb-5 bg-light rounded">
                    <h2 style="text-align: center;font-weight: bold;color:#fff;background-color: #279480;padding:10px;">List of Bills</h2><br>

                    <?php
                    $fromDate = $_POST['datefrom'] ?? '';
                    $toDate = $_POST['dateto'] ?? '';
                    ?>
                    <script>
                        var fromDate = "<?php echo $fromDate; ?>";
                        var toDate = "<?php echo $toDate; ?>";
                    </script>
                    <button type="button" class="btn btn-primary" onclick="exportTableToCSV('tblexportData', fromDate, toDate)">Export To CSV</button><br><br>
                    <button type="button" class="btn btn-danger" onclick="history.back()">Back</button><br><br>

                    <table border="1" id="tblexportData" class="table table-hover">
                        <thead>
                            <th>Sl.No.</th>
                            <th>Patient Id</th>
                            <th>Name</th>
                            <th>Bill Type</th>
                            <th>Mode Of Payment</th>
                            <th>Bill No</th>
                            <th>Bill Date/Time</th>
                            <th>Payable amount</th>

                        </thead>

                        <tbody>
                            <?php
                            $i = 1;
                            $overallTotal = 0;
                            $total_cash = 0;
                            $total_card = 0;
                            $total_upi = 0;
                            $total_other = 0;
                            $ipd_total = 0;
                            $opd_total = 0;

                            // function calculatePaymentModeTotals($row, &$total_cash, &$total_card, &$total_upi, &$total_other)
                            // {
                            //     $paymentMode = $row['PaymentMode'] ?? '';
                            //     $amount = 0;

                            //     // Determine the amount based on available fields
                            //     if (isset($row['PaymentMode']) && $row['PaymentMode'] == "Insurance") {
                            //         $amount = floatval($row['Amount']);
                            //     } else if (isset($row['PayableAmount'])) {
                            //         $amount = floatval($row['PayableAmount']);
                            //     } else if (isset($row['TotalAmount'])) {
                            //         $amount = floatval($row['TotalAmount']);
                            //     } else if (isset($row['Amount'])) {
                            //         $amount = floatval($row['Amount']);
                            //     }

                            //     if ($amount <= 0) return;

                            //     switch ($paymentMode) {
                            //         case 'Cash':
                            //             $total_cash += $amount;
                            //             break;
                            //         case 'Card':
                            //             $total_card += $amount;
                            //             break;
                            //         case 'UPI':
                            //             $total_upi += $amount;
                            //             break;
                            //         case 'Cash and Card':
                            //             // If specific amounts are provided, use them
                            //             if (isset($row['cashamount']) && isset($row['cardamount'])) {
                            //                 $total_cash += floatval($row['cashamount']);
                            //                 $total_card += floatval($row['cardamount']);
                            //             } else {
                            //                 // If no specific split is provided, divide amount equally
                            //                 $total_cash += $amount / 2;
                            //                 $total_card += $amount / 2;
                            //             }
                            //             break;
                            //         case 'Cash and UPI':
                            //             // If specific amounts are provided, use them
                            //             if (isset($row['cash']) && isset($row['upi'])) {
                            //                 $total_cash += floatval($row['cash']);
                            //                 $total_upi += floatval($row['upi']);
                            //             } else {
                            //                 // If no specific split is provided, divide amount equally
                            //                 $total_cash += $amount / 2;
                            //                 $total_upi += $amount / 2;
                            //             }
                            //             break;
                            //         case 'Insurance':
                            //         case 'ABARK':
                            //             $total_other += $amount;
                            //             break;
                            //         default:
                            //             // Handle any unspecified payment modes as Other
                            //             $total_other += $amount;
                            //             break;
                            //     }
                            // }
                            
                            function calculatePaymentModeTotals($row, &$total_cash, &$total_card, &$total_upi, &$total_other) {
    $paymentMode = $row['PaymentMode'] ?? '';
    $amount = 0;

    // Determine the amount based on available fields
    if (isset($row['PaymentMode']) && $row['PaymentMode'] == "Insurance") {
        $amount = floatval($row['Amount']);
    } else if (isset($row['PayableAmount'])) {
        $amount = floatval($row['PayableAmount']);
    } else if (isset($row['TotalAmount'])) {
        $amount = floatval($row['TotalAmount']);
    } else if (isset($row['Amount'])) {
        $amount = floatval($row['Amount']);
    }

    if ($amount <= 0) return;

    switch ($paymentMode) {
        case 'Cash':
            $total_cash += $amount;
            break;
        case 'Card':
            $total_card += $amount;
            break;
        case 'UPI':
            $total_upi += $amount;
            break;
        case 'Cash and Card':
            // Use specific amounts if available
            if (isset($row['cashamount']) && isset($row['cardamount'])) {
                $cash_amount = floatval($row['cashamount']);
                $card_amount = floatval($row['cardamount']);
                // Validate that the sum matches the total
                if (abs(($cash_amount + $card_amount) - $amount) < 0.01) {
                    $total_cash += $cash_amount;
                    $total_card += $card_amount;
                } else {
                    // If amounts don't match, split equally
                    $total_cash += $amount / 2;
                    $total_card += $amount / 2;
                }
            } else {
                $total_cash += $amount / 2;
                $total_card += $amount / 2;
            }
            break;
        case 'Cash and UPI':
            // Use specific amounts if available
            if (isset($row['cash']) && isset($row['upi'])) {
                $cash_amount = floatval($row['cash']);
                $upi_amount = floatval($row['upi']);
                // Validate that the sum matches the total
                if (abs(($cash_amount + $upi_amount) - $amount) < 0.01) {
                    $total_cash += $cash_amount;
                    $total_upi += $upi_amount;
                } else {
                    // If amounts don't match, split equally
                    $total_cash += $amount / 2;
                    $total_upi += $amount / 2;
                }
            } else {
                $total_cash += $amount / 2;
                $total_upi += $amount / 2;
            }
            break;
        case 'Insurance':
        case 'ABARK':
            $total_other += $amount;
            break;
        default:
            $total_other += $amount;
            break;
    }
}

                            // IPD Advance Section
                            if ($query1 && mysqli_num_rows($query1) != 0) {
                                echo "<tr><td colspan='8' style='text-align:center;'><b>IPD Advance</b></td></tr>";
                                $sectionTotal = 0;
                                while ($row = $query1->fetch_assoc()) {
                                    calculatePaymentModeTotals($row, $total_cash, $total_card, $total_upi, $total_other);
                                    $sectionTotal += $row['Amount'];
                                    echo "<tr><td>$i</td>";
                                    echo "<td>" . $row['PatId'] . "</td>";
                                    echo "<td>" . ($row['Name'] ?? 'N/A') . "</td>";
                                    echo "<td>Advance</td>";
                                    echo "<td>" . $row['PaymentMode'] . "</td>";
                                    $billNo = $row['BillNo'];
                                    $patid = $row['PatId'];
                                    echo "<td><a href=\"ViewTransBill1.php?Billno=$billNo&PatId=$patid&type=Advance\" target=\"_blank\">$billNo</a></td>";
                                    echo "<td>" . $row['AdvanceDate'] . "</td>";
                                    echo "<td>" . $row['Amount'] . "</td>";
                                    $i++;
                                }
                                echo "<tr><td colspan='7' style='text-align:right;'><b>Total for IPD Advance:</b></td><td><b>" . number_format($sectionTotal, 2) . "</b></td></tr>";
                                $overallTotal += $sectionTotal;
                                $ipd_total += $sectionTotal;
                            }

                            //IPD Consolidate bills
                            if ($query6 && mysqli_num_rows($query6) != 0) {
                                echo "<tr><td colspan='8' style='text-align:center;'><b>IPD Consolidate Bills</b></td></tr>";
                                $sectionTotal = 0;

                                while ($row = $query6->fetch_assoc()) {
                                    // Calculate payment mode totals
                                    calculatePaymentModeTotals($row, $total_cash, $total_card, $total_upi, $total_other);

                                    $final = $row['Amount'];

                                    echo "<tr>";
                                    echo "<td>{$i}</td>";
                                    echo "<td>" . $row['PatientId'] . "</td>";
                                    echo "<td>" . ($row['Name'] ?? 'N/A') . "</td>";
                                    echo "<td>" . $row['BillType'] . "</td>";
                                    echo "<td>" . $row['PaymentMode'] . "</td>";
                                    echo "<td><a href=\"PrintBill.php?BillNo=" . $row['FinalBillNo'] . "&patid=" . $row['PatientId'] . "\" target=\"_blank\">" . $row['FinalBillNo'] . "</a></td>";
                                    echo "<td>" . $row['DischargeDate'] . "</td>";
                                    echo "<td>" . number_format($final, 2) . "</td>";
                                    echo "</tr>";

                                    $sectionTotal += $final;
                                    $i++;
                                }

                                echo "<tr><td colspan='7' style='text-align:right;'><b>Total for IPD Consolidate Bills:</b></td><td><b>" . number_format($sectionTotal, 2) . "</b></td></tr>";
                                $overallTotal += $sectionTotal;
                                $ipd_total += $sectionTotal;
                            }

                            // IPD Discharge Bills Section
                            // if ($query2 && mysqli_num_rows($query2) != 0) {
                            //     echo "<tr><td colspan='8' style='text-align:center;'><b>IPD Discharge Bills</b></td></tr>";
                            //     $sectionTotal = 0;

                            //     while ($row = $query2->fetch_assoc()) {
                            //         // Calculate payment mode totals using the existing function
                            //         calculatePaymentModeTotals($row, $total_cash, $total_card, $total_upi, $total_other);

                            //         $final = $row['Amount'];
                            //         $insuranceClaim = $row['insuranceAmt'] ?? 0;
                            //         $discountAmount = $row['DiscountAmount'] ?? 0;
                                    
                            //         // If PaymentMode is Insurance, add the insuranceClaim to final
                            //         if ($row['PaymentMode'] === 'Insurance') {
                            //             $final += $insuranceClaim;
                            //         }
                                    
                            //         echo "<tr>";
                            //         echo "<td>{$i}</td>";
                            //         echo "<td>" . $row['PatientId'] . "</td>";
                            //         echo "<td>" . ($row['Name'] ?? 'N/A') . "</td>";
                            //         echo "<td>" . $row['BillType'] . "</td>";
                            //         echo "<td>" . $row['PaymentMode'] . "</td>";
                            //         echo "<td><a href=\"disprint.php?Billno=" . $row['FinalBillNo'] . "\" target=\"_blank\">" . $row['FinalBillNo'] . "</a></td>";
                            //         echo "<td>" . $row['DischargeDate'] . "</td>";
                            //         echo "<td>" . number_format($final, 2) . "</td>";
                            //         echo "</tr>";

                            //         // Display Insurance Claim and Discount if applicable
                            //         if ($row['PaymentMode'] === 'Insurance' || $discountAmount > 0) {
                            //             echo "<tr>";
                            //             if ($insuranceClaim > 0) {
                            //                 echo "<td colspan='4' style='text-align:right;'><b>Insurance Claim : </b>" . number_format($insuranceClaim, 2) . "</td>";
                            //             }
                            //             if ($discountAmount > 0) {
                            //                 echo "<td colspan='4' style='text-align:left;'><b>Discount : </b>" . number_format($discountAmount, 2) . "</td>";
                            //             }
                            //             echo "</tr>";
                            //         }

                            //         $sectionTotal += $final;
                            //         $i++;
                            //     }

                            //     echo "<tr><td colspan='7' style='text-align:right;'><b>Total for IPD Bills:</b></td><td><b>" . number_format($sectionTotal, 2) . "</b></td></tr>";
                            //     $overallTotal += $sectionTotal;
                            //     $ipd_total += $sectionTotal;
                            // }

                            // IPD Discharge Bills Section
                            if ($query2 && mysqli_num_rows($query2) != 0) {
                                echo "<tr><td colspan='8' style='text-align:center;'><b>IPD Discharge Bills</b></td></tr>";
                                $sectionTotal = 0;

                                while ($row = $query2->fetch_assoc()) {
                                    // Calculate payment mode totals using the existing function
                                    calculatePaymentModeTotals($row, $total_cash, $total_card, $total_upi, $total_other);

                                    $final = $row['Amount'];
                                    $insuranceClaim = $row['insuranceAmt'] ?? 0;
                                    $discountAmount = $row['DiscountAmount'] ?? 0;

                                    // If PaymentMode is Insurance, add the insuranceClaim to final
                                    // if ($row['PaymentMode'] === 'Insurance') {
                                    //     $final += $insuranceClaim;
                                    // }

                                    echo "<tr>";
                                    echo "<td>{$i}</td>";
                                    echo "<td>" . $row['PatientId'] . "</td>";
                                    echo "<td>" . ($row['Name'] ?? 'N/A') . "</td>";
                                    echo "<td>" . $row['BillType'] . "</td>";
                                    echo "<td>" . $row['PaymentMode'] . "</td>";
                                    echo "<td><a href=\"disprint.php?Billno=" . $row['FinalBillNo'] . "\" target=\"_blank\">" . $row['FinalBillNo'] . "</a></td>";
                                    echo "<td>" . $row['DischargeDate'] . "</td>";
                                    echo "<td>" . number_format($final, 2) . "</td>";
                                    echo "</tr>";

                                    // Display Insurance Claim, Discount and Final Payment Mode if applicable
                                    if (($row['PaymentMode'] === 'Insurance' && (!empty($row['Finalpaymentmode']) || $insuranceClaim > 0)) || $discountAmount > 0) {
                                        echo "<tr>";

                                        // First cell for Insurance Claim (left side)
                                        echo "<td colspan='4' style='text-align:right;'>";
                                        if ($insuranceClaim > 0) {
                                            echo "<b>Insurance Claim : </b>" . number_format($insuranceClaim, 2);
                                        }
                                        // Add discount info
                                        if ($discountAmount > 0) {
                                            echo "<br><b>Discount : </b>" . number_format($discountAmount, 2) . "<br>";
                                        }
                                        echo "</td>";

                                        // Second cell for Discount and Final Payment Mode (right side)
                                        echo "<td colspan='4' style='text-align:left;'>";



                                        // Add Final Payment Mode details if available
                                        if (!empty($row['Finalpaymentmode'])) {
                                            echo "<b>Final Payment Mode : </b>" . $row['Finalpaymentmode'];

                                            // Add specific details based on the payment mode
                                            if ($row['Finalpaymentmode'] == 'Cash') {
                                                echo "<br><b>Amount : </b>" . number_format($row['PayableAmount'], 2);
                                            } elseif ($row['Finalpaymentmode'] == 'Card') {
                                                echo "<br><b>Bank : </b>" . $row['Finalbank'];
                                                if (!empty($row['Finalcardno'])) {
                                                    echo "<br><b>Card : </b>" . substr($row['Finalcardno'], -4);
                                                }
                                                echo "<br><b>Amount : </b>" . number_format($row['PayableAmount'], 2);
                                            } elseif ($row['Finalpaymentmode'] == 'UPI') {
                                                echo "<br><b>UPI ID : </b>" . $row['Finalupiid'];
                                                echo "<br><b>Amount : </b>" . number_format($row['Finalupi'], 2);
                                            } elseif ($row['Finalpaymentmode'] == 'Cash and Card') {
                                                echo "<br><b>Bank : </b>" . $row['Finalbank'];
                                                if (!empty($row['Finalcardno'])) {
                                                    echo "<br><b>Card No : </b>" . substr($row['Finalcardno'], -4);
                                                }
                                                echo "<br><b>Cash : </b>" . number_format($row['Finalcashamount'], 2);
                                                echo "<br><b>Card : </b>" . number_format($row['Finalcardamount'], 2);
                                            } elseif ($row['Finalpaymentmode'] == 'Cash and UPI') {
                                                echo "<br><b>Cash : </b>" . number_format($row['Finalcash'], 2);
                                                echo "<br><b>UPI : </b>" . number_format($row['Finalupi'], 2);
                                                echo "<br><b>UPI ID : </b>" . $row['Finalupiid'];
                                            }
                                        }

                                        echo "</td>";
                                        echo "</tr>";
                                    }

                                    $sectionTotal += $final;
                                    $i++;
                                }

                                echo "<tr><td colspan='7' style='text-align:right;'><b>Total for IPD Bills:</b></td><td><b>" . number_format($sectionTotal, 2) . "</b></td></tr>";
                                $overallTotal += $sectionTotal;
                                $ipd_total += $sectionTotal;
                            }

                            // INBILLS Section
                            if ($query3 && mysqli_num_rows($query3) != 0) {
                                echo "<tr><td colspan='8' style='text-align:center;'><b>INBILLS</b></td></tr>";
                                $sectionTotal = 0;
                                while ($row = $query3->fetch_assoc()) {
                                    calculatePaymentModeTotals($row, $total_cash, $total_card, $total_upi, $total_other);
                                    $sectionTotal += $row['PayableAmount'];
                                    echo "<tr><td>$i</td>";
                                    echo "<td>" . $row['PatientId'] . "</td>";
                                    echo "<td>" . ($row['Name'] ?? 'N/A') . "</td>";
                                    echo "<td>Investigation Charges</td>";
                                    echo "<td>" . $row['PaymentMode'] . "</td>";
                                    echo "<td>" . $row['BillNo'] . "</td>";
                                    echo "<td>" . $row['BDate'] . "</td>";
                                    echo "<td>" . $row['PayableAmount'] . "</td>";
                                    $i++;
                                }
                                echo "<tr><td colspan='7' style='text-align:right;'><b>Total for INBILLS:</b></td><td><b>" . number_format($sectionTotal, 2) . "</b></td></tr>";
                                $overallTotal += $sectionTotal;
                            }

                            // OPD Bills Section
                            if ($query4 && mysqli_num_rows($query4) != 0) {
                                echo "<tr><td colspan='8' style='text-align:center;'><b>OPD Bills</b></td></tr>";
                                $sectionTotal = 0;
                                while ($row = $query4->fetch_assoc()) {
                                    if ($row['PatientType'] == "OPD") {
                                        calculatePaymentModeTotals($row, $total_cash, $total_card, $total_upi, $total_other);
                                        $sectionTotal += $row['PayableAmount'];
                                        echo "<tr><td>$i</td>";
                                        echo "<td>" . $row['PatientId'] . "</td>";
                                        echo "<td>" . ($row['Name'] ?? 'N/A') . "</td>";
                                        echo "<td>" . $row['PatientType'] . "</td>";
                                        echo "<td>" . $row['PaymentMode'] . "</td>";
                                        echo "<td><a href=\"ViewTransBill.php?Billno=" . $row['BillNo'] . "&type=" . $row['PatientType'] . "\" target=\"_blank\">" . $row['BillNo'] . "</a></td>";
                                        echo "<td>" . $row['BDate'] . "</td>";
                                        echo "<td>" . $row['PayableAmount'] . "</td>";
                                        $i++;
                                    }
                                }
                                echo "<tr><td colspan='7' style='text-align:right;'><b>Total for OPD Bills:</b></td><td><b>" . number_format($sectionTotal, 2) . "</b></td></tr>";
                                $overallTotal += $sectionTotal;
                                $opd_total += $sectionTotal;
                            }

                            // Examination Bills Section
                            if ($query5 && mysqli_num_rows($query5) != 0) {
                                echo "<tr><td colspan='8' style='text-align:center;'><b>Examination Bills</b></td></tr>";
                                $sectionTotal = 0;
                                while ($row = $query5->fetch_assoc()) {
                                    calculatePaymentModeTotals($row, $total_cash, $total_card, $total_upi, $total_other);
                                    $sectionTotal += $row['TotalAmount'];
                                    echo "<tr><td>$i</td>";
                                    echo "<td>" . $row['PatId'] . "</td>";
                                    echo "<td>" . ($row['Name'] ?? 'N/A') . "</td>";
                                    echo "<td>Examination</td>";
                                    echo "<td>" . ($row['PaymentMode'] ?? 'N/A') . "</td>";
                                    echo "<td>        <a href=\"ViewExaminationBill.php?Billno=" . $row['BillNo'] . "\" target=\"_blank\">" . $row['BillNo'] . "</a> </td>";
                                    
                                    echo "<td>" . $row['BDate'] . "</td>";
                                    echo "<td>" . round($row['TotalAmount']) . "</td>";
                                    $i++;
                                }
                                echo "<tr><td colspan='7' style='text-align:right;'><b>Total for Examination Bills:</b></td><td><b>" . number_format($sectionTotal, 2) . "</b></td></tr>";
                                $overallTotal += $sectionTotal;
                                // $ipd_total += $sectionTotal;
                            }

                            echo "<tr><td colspan='7' style='text-align:right;'><b>Overall Total:</b></td><td><b>" . number_format($overallTotal, 2) . "</b></td></tr>";

                             echo "<tr><td colspan='7'>IPD Total</td><td>" . number_format($ipd_total, 2) . "</td></tr>";
                            echo "<tr><td colspan='7'>OPD Total</td><td>" . number_format($opd_total, 2) . "</td></tr>";


                           $query_sections = mysqli_query($db, "SELECT BillNo, optionlab, SUM(Amount) AS TotalAmount 
                                      FROM examination 
                                      WHERE BDate >= '$from' AND BDate <= '$to' 
                                      AND is_deleted != 0
                                      GROUP BY BillNo, optionlab");


                            $cardiology_total = 0;
                            $radiology_total = 0;
                            $lab_total = 0;

                            while ($row = $query_sections->fetch_assoc()) {
                                switch ($row['optionlab']) {
                                    case 'Cardiology':
                                        $cardiology_total += $row['TotalAmount'];
                                        break;
                                    case 'Radiology':
                                        $radiology_total += $row['TotalAmount'];
                                        break;
                                    case 'Lab Test':
                                        $lab_total += $row['TotalAmount'];
                                        break;
                                }
                            }
                          
                            // Display all totals
                            echo "<tr><td colspan='7'>Cardiology Total</td><td>" . number_format($cardiology_total, 2) . "</td></tr>";
                            echo "<tr><td colspan='7'>Radiology Total</td><td>" . number_format($radiology_total, 2) . "</td></tr>";
                            echo "<tr><td colspan='7'>Lab Test Total</td><td>" . number_format($lab_total, 2) . "</td></tr>";

                            echo "<tr><td colspan='7'><b>Summary of Payment Modes</b></td></tr>";
                            echo "<tr><td colspan='7'>Cash Total</td><td>" . number_format($total_cash, 2) . "</td></tr>";
                            echo "<tr><td colspan='7'>Card Total</td><td>" . number_format($total_card, 2) . "</td></tr>";
                            echo "<tr><td colspan='7'>UPI Total</td><td>" . number_format($total_upi, 2) . "</td></tr>";
                            mysqli_data_seek($query2, 0); // Reset pointer to the beginning of the result set
                            while ($row = $query2->fetch_assoc()) {
                                if ($row['PaymentMode'] === 'Insurance') {
                                    $insuranceClaim = $row['insuranceAmt'] ?? 0;
                                    $total_other += $insuranceClaim;
                                }
                            }
                            echo "<tr><td colspan='7'>Other Total</td><td>" . number_format($total_other, 2) . "</td></tr>";

                            $payment_mode_grand_total = $total_cash + $total_card + $total_upi + $total_other;
                            echo "<tr><td colspan='7' style='text-align:right;'><b>Payment Mode Grand Total</b></td><td><b>" . number_format($payment_mode_grand_total, 2) . "</b></td></tr>";
                            ?>
                        </tbody>

                    </table>


                    <br>
                    <!-- <button type="button" class="btn btn-danger" onclick="history.back()">Back</button><br><br> -->
                </div>
            </div>
            <div class="col-md-1"></div>
        </div>
    </section>

    <script>
        function exportTableToCSV(tableID, fromDate, toDate) {
            var table = document.getElementById(tableID);
            var rows = table.querySelectorAll('tr');
            var csvContent = [];

            rows.forEach((row, index) => {
                var cols = row.querySelectorAll('td, th');
                var csvRow = [];
                cols.forEach(col => {
                    var cellText = col.innerText.trim();

                    cellText = cellText.replace(/\n/g, " ");

                    cellText = `"${cellText.replace(/"/g, '""')}"`;


                    if (row.parentElement.tagName === 'THEAD' || index === 0) {
                        cellText = `"**${cellText}**"`;
                    }
                    csvRow.push(cellText);
                });
                csvContent.push(csvRow.join(","));
            });


            var filename = `${fromDate}_${toDate}_dailytransactions.csv`;


            var csvFile = new Blob([csvContent.join("\n")], {
                type: 'text/csv'
            });
            var downloadLink = document.createElement("a");
            downloadLink.href = URL.createObjectURL(csvFile);
            downloadLink.download = filename;


            document.body.appendChild(downloadLink);
            downloadLink.click();
            document.body.removeChild(downloadLink);
        }
    </script>

    <div class="footer">
        &copy; Copyright 2024 All Rights Reserved <strong>Vidyaranya Hospital PVt LTD.</strong>. | Designed by <a href="http://www.vatsinsoft.in/">Vatsin Soft</a>
    </div>

    <script src='https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js'></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js'></script>
    <script src="assets/vendor/jquery/jquery.min.js"></script>
    <script src="assets/vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
    <script src="assets/js/main.js"></script>
</body>

</html>