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

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

if (isset($_SESSION['username']) && isset($_SESSION['password'])) {
    $username = $_SESSION['username'];
    $pass = $_SESSION['password'];
} else {
    echo '<META http-equiv="refresh" content="0;logout.php">';
    exit();
}
?>
    <!DOCTYPE html>
    <html lang="en">
    <meta http-equiv="content-type" content="text/html;charset=UTF-8" />
    <head>
    <title>VH-Amdin</title>

    <link rel="icon" href="images/logo.png" type="image/x-icon" class="abc">
    
    <link rel="icon" type="image/icon" href="images/logo.png" />
    
    <link href="https://fonts.googleapis.com/css?family=Open+Sans:300,400,600,700,800" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css?family=Quicksand:500,700" rel="stylesheet">
    
    <link rel="stylesheet" type="text/css" href="../files/bower_components/bootstrap/css/bootstrap.min.css">
    
    <link rel="stylesheet" href="../files/assets/pages/waves/css/waves.min.css" type="text/css" media="all">
    
    <link rel="stylesheet" type="text/css" href="../files/assets/icon/feather/css/feather.css">
    
    <link rel="stylesheet" type="text/css" href="../files/assets/css/font-awesome-n.min.css">
    
    <link rel="stylesheet" href="../files/bower_components/chartist/css/chartist.css" type="text/css" media="all">
    
    <link rel="stylesheet" type="text/css" href="../files/assets/css/style.css">
    <link rel="stylesheet" type="text/css" href="../files/assets/css/widget.css">
    
    <style>
        .img-fluid {
    max-width: 66%;
    height: auto;
    margin-left: -47px;
}
.pcoded li.pcoded-hasmenu[dropdown-icon=style1]>a:after {
    content: none;
}
.pcoded[theme-layout=vertical] .pcoded-navbar .pcoded-item[item-border=true][item-border-style=solid] li>a {
    border-bottom-style: solid;
    height: 53px;
}
div#c1 {
    background-color: cadetblue;
}
div#c2 {
    background-color: slategrey;
}
div#c3 {
    background-color: salmon;
}
div#c4 {
    background-color: #a549a5;
}
{
    padding-left: 10px;
    font-size: 20px;
}
    </style>

    </head>
    <body>
    
    <div class="loader-bg">
    <div class="loader-bar"></div>
    </div>
    
    <div id="pcoded" class="pcoded">
    <div class="pcoded-overlay-box"></div>
    <div class="pcoded-container navbar-wrapper">
    
    <nav class="navbar header-navbar pcoded-header">
    <div class="navbar-wrapper">
    <div class="navbar-logo">
    <a href="index.php">
     <h5>Admin</h5>
    </a>
    <a class="mobile-menu" id="mobile-collapse" href="#!">
    <i class="feather icon-menu icon-toggle-right"></i>
    </a>
    <a class="mobile-options waves-effect waves-light">
    <i class="feather icon-more-horizontal"></i>
    </a>
    </div>
    <div class="navbar-container container-fluid">
    <ul class="nav-left">
    <li class="header-search">
    <div class="main-search morphsearch-search">
    <div class="input-group">

    <input type="text" class="form-control" placeholder="Enter Keyword">

    </div>
    </div>
    </li>
    
    </a>
    </li>
    </ul>
    <ul class="nav-right">
    <li class="header-notification">
    <div class="dropdown-primary dropdown">
   
    <ul class="show-notification notification-view dropdown-menu" data-dropdown-in="fadeIn" data-dropdown-out="fadeOut">
    
    <li>
   
    </li>
    <li>
    
    </li>
    <li>
   
    </li>
    </ul>
    </div>
    </li>
   
    <li class="user-profile header-notification">
    <div class="dropdown-primary dropdown">
    <div class="dropdown-toggle" data-toggle="dropdown">
    <img src="../files/assets/images/avatar-4.jpg" class="img-radius" alt="User-Profile-Image">
    <span>Admin</span>
    <i class="feather icon-chevron-down"></i>
    </div>
    <ul class="show-notification profile-notification dropdown-menu" data-dropdown-in="fadeIn" data-dropdown-out="fadeOut">
   
    <!--<li>-->
    <!--<a href="#">-->
    <!--<i class="feather icon-user"></i> Profile-->
    <!--</a>-->
    <!--</li>-->
    <li>
    <a href="logout.php">
    <i class="feather icon-log-out"></i> Logout
    </a>
    </li>
    </ul>
    </div>
    </li>
    </ul>
    </div>
    </div>
    </nav>
    
    
    
  
   
    <div class="pcoded-main-container">
    <div class="pcoded-wrapper">
    
    <nav class="pcoded-navbar">
    <div class="nav-list">
    <div class="pcoded-inner-navbar main-menu">
    <div class="pcoded-navigation-label">Navigation</div>
    <ul class="pcoded-item pcoded-left-item">
        
    <li class="pcoded-hasmenu">
        <a href="index.php" class="waves-effect waves-dark">
            <span class="pcoded-micon"><i class="feather icon-home"></i></span>
            <span class="pcoded-mtext">Dashboard</span>
        </a>
    </li>
    
    <li class="pcoded-hasmenu active pcoded-trigger">
        <a href="PatientDetails.php" class="waves-effect waves-dark">
            <span class="pcoded-micon"><i class="fa fa-registered"></i></span>
            <span class="pcoded-mtext">Patient Details</span>
        </a>
    </li>
        <li class="pcoded-hasmenu">
        <a href="StaffDetails.php" class="waves-effect waves-dark">
            <span class="pcoded-micon"><i class="fa fa-registered"></i></span>
            <span class="pcoded-mtext">Staff Details</span>
        </a>
    </li>
                   <li class="pcoded-hasmenu ">
        <a href="javascript:void(0)" class="waves-effect waves-dark">
            <span class="pcoded-micon"><i class="fa fa-users"></i></span>
            <span class="pcoded-mtext">Users</span>
        </a>
            <ul class="pcoded-submenu">
 <li class="">
        <a href="CreateUsers.php" class="waves-effect waves-dark">
           
            <span class="pcoded-mtext">Create Billing Users</span>
        </a>
    </li>
                <li class="">
                    <a href="ViewUsers.php" class="waves-effect waves-dark">
                        <span class="pcoded-mtext">View Billing Users</span>
                    </a>
                </li>
            </ul> 
    </li>
    </ul>
    </div>
    </div>
    </nav>

    <div class="pcoded-content">
            <div class="page-header card">
    <div class="row align-items-end">
    <div class="col-lg-8">
    <div class="page-header-title">
    <i class="feather icon-inbox bg-c-blue"></i>
    <div class="d-inline">
    <h5>Create Particulars</h5>
    </div>
    </div>
    </div>
    <div class="col-lg-4">
    <div class="page-header-breadcrumb">
    <ul class=" breadcrumb breadcrumb-title">
    <li class="breadcrumb-item">
    <a href="index.html"><i class="feather icon-home"></i></a>
    </li>
    <li class="breadcrumb-item"><a href="#!">Home</a>
    </li>
    <li class="breadcrumb-item"><a href="#!">Create Particulars</a>
    </li>
    
    
    
    
    </ul>
    </div>
    </div>
    </div>
    </div>

    <div class="pcoded-inner-content" style="margin:0% ;">
  <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


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

// Initialize total variables
$total_cash = $total_card = $total_upi = $total_other = 0;

// Handle the delete action
if (isset($_POST['delete'])) {
    $billNo = $_POST['billNo'];
    $patId = $_POST['patId'];

    // Prepare the DELETE queries for both tables
    $deleteAdvance = "DELETE FROM advance WHERE BillNo = '$billNo'";
    $deleteFinalBill = "DELETE FROM finalbill WHERE PatientId = '$patId'";

    // Execute delete queries
    if (mysqli_query($db, $deleteAdvance)) {
        echo "<h3>Record with BillNo $billNo deleted successfully from advance.</h3>";
    } else {
        echo "Error deleting from advance: " . mysqli_error($db);
    }

    if (mysqli_query($db, $deleteFinalBill)) {
        echo "<h3>Record with PatientId $patId deleted successfully from finalbill.</h3>";
    } else {
        echo "Error deleting from finalbill: " . mysqli_error($db);
    }
}

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 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'");

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

<?php
if (isset($query1, $query2, $query3, $query4) && 
    (mysqli_num_rows($query1) != 0 || mysqli_num_rows($query2) != 0 || mysqli_num_rows($query3) != 0 || mysqli_num_rows($query4) != 0)) : ?>

    <button type="button" class="btn btn-primary" onclick="exportToExcel('tblexportData')">Export To Excel File</button><br><br>
<div style="overflow-x: auto;">
    <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/Time</th>
                <th>Payable Amount</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
        <?php
        $i = 1;
        $total = 0;

        // Fetch records and display them
        if (mysqli_num_rows($query1) != 0 || mysqli_num_rows($query2) != 0) {
            while ($row = mysqli_fetch_assoc($query1)) { // Advance records
                $billNo = $row['BillNo'];
                $patid = $row['PatId'];
                $total += $row['Amount'];
                ?>
                <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['PatId']; ?></td>
                    <td><?php echo $row['PatientName']; ?></td>
                    <td>Advance</td>
                    <td><?php echo $row['PaymentMode']; ?></td>
                    <td><?php echo $billNo; ?></td>
                    <td><?php echo $row['AdvanceDate']; ?></td>
                    <td><?php echo $row['Amount']; ?></td>
                    <td>
                        <form method="POST" action="">
                            <input type="hidden" name="billNo" value="<?php echo $billNo; ?>">
                            <input type="hidden" name="patId" value="<?php echo $patid; ?>">
                            <button type="submit" name="delete" class="btn btn-danger">Delete</button>
                        </form>
                    </td>
                </tr>
                <?php
                $i++;
            }

            while ($row = mysqli_fetch_assoc($query2)) { // Discharge records
                $finalAmount = ($row['PaymentMode'] == "Insurance") ? $row['TotalAmount'] : $row['PayableAmount'];
                $total += $finalAmount;
                ?>
                <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['PatientId']; ?></td>
                    <td><?php echo $row['PatientName']; ?></td>
                    <td>Discharge</td>
                    <td><?php echo $row['PaymentMode']; ?></td>
                    <td><?php echo $row['FinalBillNo']; ?></td>
                    <td><?php echo $row['DischargeDate'] . ' ,' . $row['DischargeTime']; ?></td>
                    <td><?php echo $finalAmount; ?></td>
                    <td>
                        <form method="POST" action="">
                            <input type="hidden" name="billNo" value="<?php echo $row['FinalBillNo']; ?>">
                            <input type="hidden" name="patId" value="<?php echo $row['PatientId']; ?>">
                            <button type="submit" name="delete" class="btn btn-danger">Delete</button>
                        </form>
                    </td>
                </tr>
                <?php
                $i++;
            }
        }

        // Fetch OPD records (Investigation Charges and Bills)
        if (mysqli_num_rows($query3) != 0 || mysqli_num_rows($query4) != 0) {
            while ($row = mysqli_fetch_assoc($query3)) { // Investigation Charges
                $total += $row['PayableAmount'];
                ?>
                <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['PatientId']; ?></td>
                    <td><?php echo $row['PatientName']; ?></td>
                    <td>Investigation Charges</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>
                    <td>
                        <form method="POST" action="">
                            <input type="hidden" name="billNo" value="<?php echo $row['BillNo']; ?>">
                            <input type="hidden" name="patId" value="<?php echo $row['PatientId']; ?>">
                            <button type="submit" name="delete" class="btn btn-danger">Delete</button>
                        </form>
                    </td>
                </tr>
                <?php
                $i++;
            }

            while ($row = mysqli_fetch_assoc($query4)) { // Bills
                $total += $row['PayableAmount'];
                ?>
                <tr>
                    <td><?php echo $i; ?></td>
                    <td><?php echo $row['PatientId']; ?></td>
                    <td><?php echo $row['PatientName']; ?></td>
                    <td>Bill</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>
                    <td>
                        <form method="POST" action="">
                            <input type="hidden" name="billNo" value="<?php echo $row['BillNo']; ?>">
                            <input type="hidden" name="patId" value="<?php echo $row['PatientId']; ?>">
                            <button type="submit" name="delete" class="btn btn-danger">Delete</button>
                        </form>
                    </td>
                </tr>
                <?php
                $i++;
            }
        }
        ?>
        </tbody>
        <tfoot>
            <tr>
                <td colspan="8" style="text-align:right"><strong>Total:</strong></td>
                <td><?php echo $total; ?></td>
               
            </tr>
        </tfoot>
    </table>
    </div>
<?php else : ?>
    <h3>No records found for the selected date range.</h3>
<?php endif; ?>

<script>
function exportToExcel(tableID) {
    // Export logic here
}
</script>



  </div>
    </div>
    </div>



    
    <script type="a0935012553127c718007911-text/javascript" src="../files/bower_components/jquery/js/jquery.min.js"></script>
    <script type="a0935012553127c718007911-text/javascript" src="../files/bower_components/jquery-ui/js/jquery-ui.min.js"></script>
    <script type="a0935012553127c718007911-text/javascript" src="../files/bower_components/popper.js/js/popper.min.js"></script>
    <script type="a0935012553127c718007911-text/javascript" src="../files/bower_components/bootstrap/js/bootstrap.min.js"></script>

    <script src="../files/assets/pages/waves/js/waves.min.js" type="a0935012553127c718007911-text/javascript"></script>

    <script type="a0935012553127c718007911-text/javascript" src="../files/bower_components/jquery-slimscroll/js/jquery.slimscroll.js"></script>

    <script type="a0935012553127c718007911-text/javascript" src="../files/bower_components/modernizr/js/modernizr.js"></script>
    <script type="a0935012553127c718007911-text/javascript" src="../files/bower_components/modernizr/js/css-scrollbars.js"></script>

    <script src="../files/assets/pages/waves/js/waves.min.js" type="a0935012553127c718007911-text/javascript"></script>

    <script src="../files/bower_components/datatables.net/js/jquery.dataTables.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/bower_components/datatables.net-buttons/js/dataTables.buttons.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/assets/pages/data-table/js/jszip.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/assets/pages/data-table/js/pdfmake.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/assets/pages/data-table/js/vfs_fonts.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/bower_components/datatables.net-buttons/js/buttons.print.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/bower_components/datatables.net-buttons/js/buttons.html5.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/bower_components/datatables.net-bs4/js/dataTables.bootstrap4.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/bower_components/datatables.net-responsive/js/dataTables.responsive.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/bower_components/datatables.net-responsive-bs4/js/responsive.bootstrap4.min.js" type="a0935012553127c718007911-text/javascript"></script>

    <script src="../files/assets/pages/data-table/js/data-table-custom.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/assets/js/pcoded.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/assets/js/vertical/vertical-layout.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script src="../files/assets/js/jquery.mCustomScrollbar.concat.min.js" type="a0935012553127c718007911-text/javascript"></script>
    <script type="a0935012553127c718007911-text/javascript" src="../files/assets/js/script.js"></script>

    <script async src="https://www.googletagmanager.com/gtag/js?id=UA-23581568-13" type="a0935012553127c718007911-text/javascript"></script>
    <script type="a0935012553127c718007911-text/javascript">
      window.dataLayer = window.dataLayer || [];
      function gtag(){dataLayer.push(arguments);}
      gtag('js', new Date());

      gtag('config', 'UA-23581568-13');
    </script>
    <script src="rocket-loader.min.js" data-cf-settings="a0935012553127c718007911-|49" defer=""></script></body>

    </html>

