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

// Check if session variables are set
if (!isset($_SESSION['username']) || !isset($_SESSION['name'])) {
    echo '<META http-equiv="refresh" content="0;logout.php">';
    exit();
}

$username = $_SESSION['username'];
$name = $_SESSION['name'];

// Initialize query variable and totals
$query = null;
$total_cash = $total_card = $total_upi = $total_other = 0;
$total_ipd = $total_opd = 0;
$cardiology_total = $radiology_total = $laboratory_total = 0;

if (isset($_POST['datefrom']) && isset($_POST['dateto'])) {
    $from = $_POST['datefrom'];
    $to = $_POST['dateto'];

    if ($from > $to) {
        echo "<h3>Invalid date range. 'From' date cannot be greater than 'To' date.</h3>";
    } else {
        $query = mysqli_query($db, "
            SELECT 
                PatientId,
                PatientName,
                BillType,
                PaymentMode,
                BillNo,
                BillDate,
                PayableAmount
            FROM (
                SELECT 
                    a.PatId AS PatientId,
                    p.Name AS PatientName,
                    'Advance' AS BillType,
                    a.PaymentMode,
                    a.BillNo,
                    a.AdvanceDate AS BillDate,
                    a.Amount AS PayableAmount
                FROM advance a 
                LEFT JOIN patientdetails p ON a.PatId = p.PatId
                WHERE a.AdvanceDate BETWEEN '$from' AND '$to'
                AND a.Amount > 0
            
                UNION ALL
            
                SELECT 
                    f.PatientId,
                    p.Name AS PatientName,
                    'Discharge' AS BillType,
                    f.PaymentMode,
                    f.FinalBillNo AS BillNo,
                    f.DischargeDate AS BillDate,
                    CASE WHEN f.PaymentMode = 'Insurance' THEN f.TotalAmount ELSE f.PayableAmount END AS PayableAmount
                FROM finalbill f
                LEFT JOIN patientdetails p ON f.PatientId = p.PatId
                WHERE f.DischargeDate BETWEEN '$from' AND '$to'
                AND (CASE WHEN f.PaymentMode = 'Insurance' THEN f.TotalAmount ELSE f.PayableAmount END) > 0
            
                UNION ALL
            
                SELECT 
                    i.PatientId,
                    o.Name AS PatientName,
                    'Investigation' AS BillType,
                    i.PaymentMode,
                    i.BillNo,
                    i.BDate AS BillDate,
                    i.PayableAmount
                FROM inbill i
                LEFT JOIN opdpatientregister o ON i.PatientId = o.PatientId
                WHERE i.BDate BETWEEN '$from' AND '$to'
                AND i.PayableAmount > 0
            
                UNION ALL
            
                SELECT 
                    b.PatientId,
                    o.Name AS PatientName,
                    b.PatientType AS BillType,
                    b.PaymentMode,
                    b.BillNo,
                    b.BDate AS BillDate,
                    b.PayableAmount
                FROM bill b
                LEFT JOIN opdpatientregister o ON b.PatientId = o.PatientId
                WHERE b.BDate BETWEEN '$from' AND '$to' 
                AND b.PatientType = 'OPD'
                AND b.PayableAmount > 0
            ) AS combined_data
            ORDER BY PatientId, BillDate, BillType
        ");

        // Examination totals query
        $examinationQuery = mysqli_query($db, "
            SELECT optionlab, SUM(Amount) AS TotalAmount 
            FROM examination 
            WHERE DATE(created_at) BETWEEN '$from' AND '$to' 
            GROUP BY optionlab
        ");

        if (!$query || !$examinationQuery) {
            echo "Error executing query: " . mysqli_error($db);
            exit();
        }

        // Process the examination totals
        while ($row = $examinationQuery->fetch_assoc()) {
            switch ($row['optionlab']) {
                case 'Cardiology':
                    $cardiology_total = $row['TotalAmount'];
                    break;
                case 'Radiology':
                    $radiology_total = $row['TotalAmount'];
                    break;
                case 'Lab Test':
                    $laboratory_total = $row['TotalAmount'];
                    break;
            }
        }
    }
}

function calculatePaymentTotals($mode, $amount, &$total_cash, &$total_card, &$total_upi, &$total_other)
{
    switch ($mode) {
        case 'Cash':
            $total_cash += $amount;
            break;
        case 'Card':
            $total_card += $amount;
            break;
        case 'UPI':
            $total_upi += $amount;
            break;
        default:
            $total_other += $amount;
            break;
    }
}
?>

<!-- Example HTML to display examination totals -->

     



<!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"><? 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>
                    <form method="POST" action="">
                        <h5>Enter Dates</h5>
                        <div class="row">
                            <div class="form-group col">
                                <label for="datefrom">From</label>
                                <input class="form-control" type="date" name="datefrom" id="datefrom" required>
                            </div>
                            <div class="form-group col">
                                <label for="dateto">To</label>
                                <input class="form-control" type="date" name="dateto" id="dateto" required>
                            </div>
                            <div class="col">
                                <button type="submit" class="btn btn-primary" id="viewbtn">View</button>
                            </div>
                        </div>
                    </form>

<?php if ($query && mysqli_num_rows($query) > 0) : ?>
    <!--<button type="button" class="btn btn-primary" onclick="exportToExcel('tblexportData')">Export To Excel File</button><br><br>-->
    <button type="button" class="btn btn-primary" onclick="exportTableToCSV('tblexportData')">Export To CSV File</button>

    <table border="1" id="tblexportData" class="table table-hover">
        <thead>
            <tr>
                <th>Sl.No.</th>
                <th>Patient Id</th>
                <th>Patient Name</th>
                <th>Bill Type</th>
                <th>Mode Of Payment</th>
                <th>Bill No</th>
                <th>Bill Date</th>
                <th>Payable Amount</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $i = 1;
            $total = 0;
            $current_patient = '';
            $current_date = '';

            while ($row = mysqli_fetch_assoc($query)) {
                $patient_id = $row['PatientId'];
                $bill_date = $row['BillDate'];

                if ($patient_id != $current_patient || $bill_date != $current_date) {
                    $total += $row['PayableAmount'];
                    calculatePaymentTotals($row['PaymentMode'], $row['PayableAmount'], $total_cash, $total_card, $total_upi, $total_other);

                    if ($row['BillType'] == 'OPD') {
                        $total_opd += $row['PayableAmount'];
                    } else {
                        $total_ipd += $row['PayableAmount'];
                    }
            ?>
                    <tr>
                        <td><?php echo $i; ?></td>
                        <td><?php echo $patient_id; ?></td>
                        <td><?php echo $row['PatientName']; ?></td>
                        <td><?php echo $row['BillType']; ?></td>
                        <td><?php echo $row['PaymentMode']; ?></td>
                        <td>
                            <a href="<?php echo ($row['BillType'] == 'OPD') ? "ViewTransBill.php?Billno={$row['BillNo']}&type={$row['BillType']}" : (($row['BillType'] == 'Advance') ? "ViewTransBill1.php?Billno={$row['BillNo']}&PatId={$row['PatientId']}&type=Advance" : '#'); ?>" target="_blank"><?php echo $row['BillNo']; ?></a>
                        </td>
                        <td><?php echo date('d-m-Y', strtotime($bill_date)); ?></td>

                        <!--<td><?php echo $bill_date; ?></td>-->
                        <td><?php echo $row['PayableAmount']; ?></td>
                    </tr>
            <?php
                    $i++;
                    $current_patient = $patient_id;
                    $current_date = $bill_date;
                }
            }
            ?>
            <tr>
                <td colspan="7">Total Transaction</td>
                <td><?php echo $total; ?></td>
            </tr>
        </tbody>
        <tfoot>
                        <tr>
                <th colspan="8" class="summary-title">Distribution Of total Amounts IN IPD or OPD</th>
            </tr>
            <tr class="summary-row">
                <td colspan="7">IPD Total</td>
                <td class="summary-value"><?php echo $total_ipd; ?></td>
            </tr>
            <tr class="summary-row">
                <td colspan="7">OPD Total</td>
                <td class="summary-value"><?php echo $total_opd; ?></td>
            </tr>
            
             <tr>
                <th colspan="8" class="summary-title">Summary</th>
            </tr>
            
            <tr class="summary-row">
                <td colspan="7">Cash Total</td>
                <td class="summary-value"><?php echo $total_cash; ?></td>
            </tr>
            <tr class="summary-row">
                <td colspan="7">Card Total</td>
                <td class="summary-value"><?php echo $total_card; ?></td>
            </tr>
            <tr class="summary-row">
                <td colspan="7">UPI Total</td>
                <td class="summary-value"><?php echo $total_upi; ?></td>
            </tr>
            <tr class="summary-row">
                <td colspan="7">Other Total</td>
                <td class="summary-value"><?php echo $total_other; ?></td>
            </tr>
              <tr>
                <th colspan="8" class="summary-title">Examinations Total</th>
            </tr>
            <tr class="summary-row">
                <td colspan="7">Cardiology Total</td>
                <td class="summary-value"><?php echo $cardiology_total; ?></td>
            </tr>
            <tr class="summary-row">
                <td colspan="7">Radiology Total</td>
                <td class="summary-value"><?php echo $radiology_total; ?></td>
            </tr>
            <tr class="summary-row">
                <td colspan="7">Lab Test Total</td>
                <td class="summary-value"><?php echo $laboratory_total; ?></td>
            </tr>
        </tfoot>
    </table>
<?php else : ?>
    <p>No data available for the selected date range.</p>
<?php endif; ?>



                    <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, filename = 'data.csv') {
        var table = document.getElementById(tableID);
        var rows = table.querySelectorAll('tr');
        var csvContent = [];

        rows.forEach(row => {
            var cols = row.querySelectorAll('td, th');
            var csvRow = [];
            cols.forEach(col => csvRow.push(col.innerText));
            csvContent.push(csvRow.join(","));
        });

        // Create a downloadable CSV file
        var csvFile = new Blob([csvContent.join("\n")], { type: 'text/csv' });
        var downloadLink = document.createElement("a");
        downloadLink.href = URL.createObjectURL(csvFile);
        downloadLink.download = filename;
        downloadLink.click();
    }
</script>
<button type="button" class="btn btn-primary" onclick="exportTableToCSV('tblexportData')">Export To CSV File</button>



    <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>