

<!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>
  <?php
// Include your database connection file
include("dbConfig.php");

// Check if the form was submitted
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $consultant_id = $_POST['consultant'];
    $from_date = $_POST['from_date'];
    $to_date = $_POST['to_date'];

    // Ensure connection to the database
    if (!$db) {
        die("Connection failed: " . mysqli_connect_error());
    }

    // Prepare the query to fetch data from IPD, OPD, and Bill details based on consultant and date range
    $query = "
        SELECT pd.SlNo AS PatientDetailSlNo, 
               pd.PatId AS PatientDetailPatId, 
               pd.Name AS PatientDetailName, 
               pd.AdmitDate AS IPDAdmitDate, 
               opd.SlNo AS OPDSlNo, 
               opd.PatientId AS OPDPatientId, 
               opd.Name AS OPDName, 
               opd.admitDate AS OPDAdmitDate, 
               bp.SlNo AS BillSlNo, 
               bp.BillNo, 
               bp.PatId AS BillPatId, 
               bp.Particulars, 
               bp.Quantity, 
               bp.UnitPrice, 
               bp.Amount, 
               bp.consultant AS BillConsultant, 
               bp.created_at AS BillCreatedDate
        FROM patientdetails pd
        LEFT JOIN opdpatientregister opd ON pd.PatId = opd.PatientId
        LEFT JOIN billparticulars bp ON opd.PatientId = bp.PatId
        WHERE pd.Consultant = ? 
        AND ((pd.AdmitDate BETWEEN ? AND ?) OR (opd.admitDate BETWEEN ? AND ?))
    ";

    // Use prepared statements to avoid SQL injection
    $stmt = $db->prepare($query);
    $stmt->bind_param('issss', $consultant_id, $from_date, $to_date, $from_date, $to_date);
    $stmt->execute();
    $result = $stmt->get_result();

    // Initialize total amount
    $totalAmount = 0;
    $resultRows = [];

    // Fetch all rows
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $resultRows[] = $row;
            $totalAmount += $row['Amount'];  // Calculate total amount from the bill
        }
    } else {
        echo "<p>No transactions found for the selected doctor and date range.</p>";
    }

    // Display the result in a table if data is found
    if (!empty($resultRows)) : ?>
        <h2 style="text-align: center;">Transactions for the Selected Doctor from <?php echo $from_date; ?> to <?php echo $to_date; ?></h2>
        <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>Patient ID</th>
                    <th>Patient Name</th>
                    <th>IPD Admit Date</th>
                    <th>OPD Admit Date</th>
                    <th>Bill No</th>
                    <th>Particulars</th>
                    <th>Quantity</th>
                    <th>Unit Price</th>
                    <th>Amount</th>
                    <th>Transaction Date</th>
                </tr>
            </thead>
            <tbody>
                <?php
                foreach ($resultRows as $row) {
                    echo "<tr>";
                    echo "<td>{$row['PatientDetailPatId']}</td>";
                    echo "<td>{$row['PatientDetailName']}</td>";
                    echo "<td>{$row['IPDAdmitDate']}</td>";
                    echo "<td>{$row['OPDAdmitDate']}</td>";
                    echo "<td>{$row['BillNo']}</td>";
                    echo "<td>{$row['Particulars']}</td>";
                    echo "<td>{$row['Quantity']}</td>";
                    echo "<td>{$row['UnitPrice']}</td>";
                    echo "<td>{$row['Amount']}</td>";
                    echo "<td>{$row['BillCreatedDate']}</td>";
                    echo "</tr>";
                }
                ?>
                <tr>
                    <td colspan="8" style="text-align: right;">Total Amount</td>
                    <td colspan="2"><?php echo number_format($totalAmount, 2); ?></td>
                </tr>
            </tbody>
        </table>
    <?php endif;
}
?>


<script>
function exportToExcel(tableID) {
    let table = document.getElementById(tableID);
    let html = table.outerHTML.replace(/ /g, '%20');
    let downloadLink = document.createElement("a");
    document.body.appendChild(downloadLink);
    downloadLink.href = 'data:application/vnd.ms-excel,' + html;
    downloadLink.download = 'consultant_transactions.xls';
    downloadLink.click();
    document.body.removeChild(downloadLink);
}
</script>




<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>
</body>
</html>



