<?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'];

// Function to calculate payment totals
function calculatePaymentTotals($paymentMode, $amount, &$total_cash, &$total_card, &$total_upi, &$total_other) {
    switch ($paymentMode) {
        case 'Cash':
            $total_cash += $amount;
            break;
        case 'Card':
            $total_card += $amount;
            break;
        case 'UPI':
            $total_upi += $amount;
            break;
        default:
            $total_other += $amount; // Any other payment mode
            break;
    }
}

// Initialize query variables
$query1 = $query2 = $query3 = $query4 = null;

// Initialize total variables
$total_cash = $total_card = $total_upi = $total_other = 0;
$total_ipd = 0; // Total for IPD
$total_opd = 0; // Total for OPD

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 {
        // Fetch records based on the date range with JOIN to fetch patient names
        $query1 = mysqli_query($db, "SELECT a.*, p.Name AS PatientName 
                                      FROM advance a 
                                      LEFT JOIN patientdetails p ON a.PatId = p.PatId
                                      WHERE a.AdvanceDate >= '$from' AND a.AdvanceDate <= '$to'");

        $query2 = mysqli_query($db, "SELECT f.*, p.Name AS PatientName 
                                      FROM finalbill f 
                                      LEFT JOIN patientdetails p ON f.PatientId = p.PatId
                                      WHERE f.DischargeDate >= '$from' AND f.DischargeDate <= '$to'");

        $query3 = mysqli_query($db, "SELECT i.*, o.Name AS PatientName 
                                      FROM inbill i 
                                      LEFT JOIN opdpatientregister o ON i.PatientId = o.PatientId
                                      WHERE i.BDate >= '$from' AND i.BDate <= '$to'");

        $query4 = mysqli_query($db, "SELECT b.*, o.Name AS PatientName 
                                      FROM bill b 
                                      LEFT JOIN opdpatientregister o ON b.PatientId = o.PatientId
                                      WHERE b.BDate >= '$from' AND b.BDate <= '$to'");

        $query5 = mysqli_query($db, "SELECT bp.SlNo, bp.BillNo, bp.PatId, bp.Particulars, bp.Quantity, bp.UnitPrice, bp.Amount, bp.created_at, p.Name AS PatientName 
                                      FROM billparticulars bp
                                      LEFT JOIN patientdetails p ON bp.PatId = p.PatId
                                      WHERE bp.created_at >= '$from' AND bp.created_at <= '$to'");

        // New query to fetch total amounts for each examination type
        $examinationQuery = mysqli_query($db, "SELECT optionlab, SUM(Amount) AS TotalAmount 
                                               FROM examination 
                                               WHERE DATE(created_at) BETWEEN '$from' AND '$to' 
                                               GROUP BY optionlab");

        // New query to fetch complete examination details
        $examinationDetailsQuery = mysqli_query($db, "SELECT SlNo, BillNo, PatId, Particulars, Quantity, UnitPrice, Amount, optionlab, consultant, InsuranceName, InsuranceNo, bank, cardno, upi, cash, upiid, created_at, created_by, modified_at, modified_by 
                                                      FROM examination 
                                                      WHERE DATE(created_at) BETWEEN '$from' AND '$to'");

        if (!$query1 || !$query2 || !$query3 || !$query4 || !$query5 || !$examinationQuery || !$examinationDetailsQuery) {
            echo "Error executing queries: " . mysqli_error($db);
            exit();
        }

        // Initialize totals for examination categories
        $cardiology_total = 0;
        $radiology_total = 0;
        $laboratory_total = 0;

        // 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;
            }
        }
    }
}
?>


<script>
function exportToExcel(tableID) {
    // Export table to Excel
    let table = document.getElementById(tableID);
    let wb = XLSX.utils.table_to_book(table, {sheet: "Sheet 1"});
    XLSX.writeFile(wb, "Data.xlsx");
}
</script>


<!DOCTYPE html>
<html>
<head>
  <title>
    
  </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'>
  <!-- Google Fonts -->
  <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='https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css'>
<link href='https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.0.3/css/font-awesome.css'>

  <!-- Vendor CSS Files -->
  <link href="assets/vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
  <link href="assets/vendor/ionicons/css/ionicons.min.css" rel="stylesheet">
  <link href="assets/vendor/animate.css/animate.min.css" rel="stylesheet">
  <link href="assets/vendor/font-awesome/css/font-awesome.min.css" rel="stylesheet">
  <link href="assets/vendor/venobox/venobox.css" rel="stylesheet">
  <link href="assets/vendor/owl.carousel/assets/owl.carousel.min.css" rel="stylesheet">
  <link href="assets/vendor/boxicons/css/boxicons.min.css" rel="stylesheet">
  <script src="https://code.jquery.com/jquery.min.js"></script>
  <script src="jquery.dependent.fields.js"></script>
  <!-- Template Main CSS File -->
  <link href="assets/css/style.css" rel="stylesheet">
  <script src="https://code.jquery.com/jquery.min.js"></script>
<script src="jquery.dependent.fields.js"></script>
<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>
</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><!-- End 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>
  <!--<h1 style="color:red">Under Construction</h1>-->
  <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 (isset($query1, $query2, $query3, $query4, $query5, $examinationDetailsQuery) && 
          (mysqli_num_rows($query1) != 0 || mysqli_num_rows($query2) != 0 || 
           mysqli_num_rows($query3) != 0 || mysqli_num_rows($query4) != 0 || 
           mysqli_num_rows($query5) != 0 || mysqli_num_rows($examinationDetailsQuery) != 0)) : ?>

    <button type="button" class="btn btn-primary" onclick="exportToExcel('tblexportData')">Export To Excel File</button><br><br>

    <table border="1" id="tblexportData" class="table table-hover">
        <thead>
            <tr>
                <th>Sl.No.</th>
                <th>Patient Id</th>
                <th>Patient Name</th> <!-- New column for Patient Name -->
                <th>Bill Type</th>
                <th>Mode Of Payment</th>
                <th>Bill No</th>
                <th>Bill Date/Time</th>
                <th>Payable amount</th>
            </tr>
        </thead>
        <tbody>
        <?php
        $i = 1;
        $total = 0;

        // Fetch IPD records (Advance and Final Bill)
        if (mysqli_num_rows($query1) != 0 || mysqli_num_rows($query2) != 0) {
            while ($row = $query1->fetch_assoc()) { // Advance
                $total += $row['Amount'];
                $billNo = $row['BillNo'];
                $patid = $row['PatId'];
                $paymentMode = $row['PaymentMode'];
                $total_ipd += $row['Amount']; // Add to IPD total
                calculatePaymentTotals($paymentMode, $row['Amount'], $total_cash, $total_card, $total_upi, $total_other);
                ?>
                <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['PatId']; ?></td>
                    <td><?php echo $row['PatientName']; ?></td> <!-- Display Patient Name -->
                    <td>Advance</td>
                    <td><?php echo $row['PaymentMode']; ?></td>
                    <td><a href="ViewTransBill1.php?Billno=<?php echo $billNo; ?>&PatId=<?php echo $patid; ?>&type=Advance" target="_blank"><?php echo $billNo; ?></a></td>
                    <td><?php echo $row['AdvanceDate']; ?></td>
                    <td><?php echo $row['Amount']; ?></td>
                </tr>
                <?php
                $i++;
            }

            while ($row = $query2->fetch_assoc()) { // Discharge
                $final = ($row['PaymentMode'] == "Insurance") ? $row['TotalAmount'] : $row['PayableAmount'];
                $total += $final;
                $total_ipd += $final; // Add to IPD total
                calculatePaymentTotals($row['PaymentMode'], $final, $total_cash, $total_card, $total_upi, $total_other);
                ?>
                <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['PatientId']; ?></td>
                    <td><?php echo $row['PatientName']; ?></td> <!-- Display Patient Name -->
                    <td>Discharge</td>
                    <td><?php echo $row['PaymentMode']; ?></td>
                    <td><?php echo $row['FinalBillNo']; ?></td>
                    <td><?php echo $row['DischargeDate']; ?></td>
                    <td><?php echo $final; ?></td>
                </tr>
                <?php
                $i++;
            }
        }

        // Fetch OPD records (OPD Bills)
        if (mysqli_num_rows($query3) != 0 || mysqli_num_rows($query4) != 0) {
            while ($row = $query3->fetch_assoc()) { // OPD Bills
                $total += $row['PayableAmount'];
                $total_opd += $row['PayableAmount']; // Add to OPD total
                calculatePaymentTotals($row['PaymentMode'], $row['PayableAmount'], $total_cash, $total_card, $total_upi, $total_other);
                ?>
                <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['PatientId']; ?></td>
                    <td><?php echo $row['PatientName']; ?></td> <!-- Display Patient Name -->
                    <td>OPD </td>
                    <td><?php echo $row['PaymentMode']; ?></td>
                    <td><?php echo $row['BillNo']; ?></td>
                    <td><?php echo $row['BDate']; ?></td>
                    <td><?php echo $row['PayableAmount']; ?></td>
                </tr>
                <?php
                $i++;
            }

            while ($row = $query4->fetch_assoc()) { // OPD Bills
                $total += $row['PayableAmount'];
                $total_opd += $row['PayableAmount']; // Add to OPD total
                calculatePaymentTotals($row['PaymentMode'], $row['PayableAmount'], $total_cash, $total_card, $total_upi, $total_other);
                ?>
                <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['PatientId']; ?></td>
                    <td><?php echo $row['PatientName']; ?></td> <!-- Display Patient Name -->
                    <td>OPD </td>
                    <td><?php echo $row['PaymentMode']; ?></td>
                    <td><?php echo $row['BillNo']; ?></td>
                    <td><?php echo $row['BDate']; ?></td>
                    <td><?php echo $row['PayableAmount']; ?></td>
                </tr>
                <?php
                $i++;
            }
        }

        // Fetch examination details and add to totals
        while ($row = $examinationDetailsQuery->fetch_assoc()) {
            $total += $row['Amount'];
            if ($row['optionlab'] == 'IPD') {
                $total_ipd += $row['Amount']; // Add to IPD total
            } elseif ($row['optionlab'] == 'OPD') {
                $total_opd += $row['Amount']; // Add to OPD total
            }
            calculatePaymentTotals($row['PaymentMode'], $row['Amount'], $total_cash, $total_card, $total_upi, $total_other);
            ?>
            <tr>
                <td><?php echo $i; ?></td>
                <td><?php echo $row['PatId']; ?></td>
                <td><?php echo $row['PatientName']; ?></td> <!-- Display Patient Name -->
                <td><?php echo $row['Particulars']; ?></td>
                <td><?php echo $row['PaymentMode']; ?></td>
                <td><?php echo $row['BillNo']; ?></td>
                <td><?php echo $row['created_at']; ?></td>
                <td><?php echo $row['Amount']; ?></td>
            </tr>
            <?php
            $i++;
        }
        ?>
    
        <!---->
       <tr>
    <td colspan="7"><strong>Total Transaction</strong></td>
    <td colspan="1"><strong><?php echo $total; ?></strong></td>
</tr>
        </tbody>
    </table>
    <!---->
    <div class="row"  id="tblexportData" >
        <div class="col-md-6">
            <div class="card text-white bg-success mb-3">
                <div class="card-header">IPD Total</div>
                <div class="card-body">
                    <h5 class="card-title"><?php echo $total_ipd; ?></h5>
                </div>
            </div>
        </div>
        <div class="col-md-6">
            <div class="card text-white bg-success mb-3">
                <div class="card-header">OPD Total</div>
                <div class="card-body">
                    <h5 class="card-title"><?php echo $total_opd; ?></h5>
                </div>
            </div>
        </div>
    </div>
    <!---->
<div class="row"  id="tblexportData" >
        <div class="col-md-3">
            <div class="card text-white bg-success mb-3">
                <div class="card-header">Cash Total</div>
                <div class="card-body">
                    <h5 class="card-title"><?php echo $total_cash; ?></h5>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card text-white bg-success mb-3">
                <div class="card-header">Card Total</div>
                <div class="card-body">
                    <h5 class="card-title"><?php echo $total_card; ?></h5>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card text-white bg-success mb-3">
                <div class="card-header">UPI Total</div>
                <div class="card-body">
                    <h5 class="card-title"><?php echo $total_upi; ?></h5>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card text-white bg-success mb-3">
                <div class="card-header">Other Total</div>
                <div class="card-body">
                    <h5 class="card-title"><?php echo $total_other; ?></h5>
                </div>
            </div>
        </div>
</div>
     <!---->
    <div class="row"  id="tblexportData" >
        <div class="col-md-4">
            <div class="card text-white bg-success mb-3">
                <div class="card-header">Radiology Total</div>
                <div class="card-body">
                    <h5 class="card-title"><?php echo $cardiology_total; ?></h5>
                </div> 
            </div>
        </div>
        <div class="col-md-4">
            <div class="card text-white bg-success mb-3">
                <div class="card-header">Cardiology Total</div>
                <div class="card-body">
                    <h5 class="card-title"><?php echo $radiology_total; ?></h5>
                </div>
            </div>
        </div>
         <div class="col-md-4">
            <div class="card text-white bg-success mb-3">
                <div class="card-header">Laboratary Total</div>
                <div class="card-body">
                    <h5 class="card-title"><?php echo $laboratory_total; ?></h5>
                </div>
            </div>
        </div>
    </div>
    <!---->  
   
<?php else: ?>
    <h3>No records found for the selected date range.</h3>
<?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 type="text/javascript">

function exportToExcel(tableID, filename = ''){
    var downloadurl;
    var dataFileType = 'application/vnd.ms-excel';
    var tableSelect = document.getElementById(tableID);
    var tableHTMLData = tableSelect.outerHTML.replace(/ /g, '%20');
    
    // Specify file name
    filename = filename?filename+'.xls':'Transactions.xls';
    
    // Create download link element
    downloadurl = document.createElement("a");
    
    document.body.appendChild(downloadurl);
    
    if(navigator.msSaveOrOpenBlob){
        var blob = new Blob(['\ufeff', tableHTMLData], {
            type: dataFileType
        });
        navigator.msSaveOrOpenBlob( blob, filename);
    }else{
        // Create a link to the file
        downloadurl.href = 'data:' + dataFileType + ', ' + tableHTMLData;
    
        // Setting the file name
        downloadurl.download = filename;
        
        //triggering the function
        downloadurl.click();
    }
}
 

</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/vendor/jquery.easing/jquery.easing.min.js"></script>
  <script src="assets/vendor/php-email-form/vali.js"></script>
  <script src="assets/vendor/wow/wow.min.js"></script>
  <script src="assets/vendor/venobox/venobox.min.js"></script>
  <script src="assets/vendor/owl.carousel/owl.carousel.min.js"></script>
  <script src="assets/vendor/jquery-sticky/jquery.sticky.js"></script>
  <script src="assets/vendor/superfish/superfish.min.js"></script>
  <script src="assets/vendor/hoverIntent/hoverIntent.js"></script>
  <script src="assets/vendor/isotope-layout/isotope.pkgd.min.js"></script>
  <script src="assets/js/main.js"></script>
  <script>
       
    </script>
</body>
</html>



