<?php
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">';
}
?>

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

          <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

include('dbConfig.php');

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

    // Initialize totals
    $total = 0;
    $ipdTotal = 0;
    $opdTotal = 0;
    $paymentModeTotals = [
        'Card' => 0,
        'Cash' => 0,
        'UPI' => 0,
        'Other' => 0
    ];

    if ($from == $to) {
        $query1 = mysqli_query($db, "SELECT a.*, p.Name as PatientName FROM advance a INNER JOIN patientdetails p ON a.PatId = p.PatId WHERE AdvanceDate = '$from' AND a.Amount > 0");
        $query2 = mysqli_query($db, "SELECT f.*, p.Name as PatientName FROM finalbill f INNER JOIN opdpatientregister p ON f.PatientId = p.PatientId WHERE DischargeDate = '$from' AND f.PayableAmount > 0");
        $query3 = mysqli_query($db, "SELECT i.*, p.Name as PatientName FROM inbill i INNER JOIN patientdetails p ON i.PatientId = p.PatId WHERE BDate = '$from' AND i.PayableAmount > 0");
        $query4 = mysqli_query($db, "SELECT b.*, p.Name as PatientName FROM bill b INNER JOIN opdpatientregister p ON b.PatientId = p.PatientId WHERE BDate = '$from' AND b.PayableAmount > 0");
    } else {
        $query1 = mysqli_query($db, "SELECT a.*, p.Name as PatientName FROM advance a INNER JOIN patientdetails p ON a.PatId = p.PatId WHERE AdvanceDate BETWEEN '$from' AND '$to' AND a.Amount > 0");
        $query2 = mysqli_query($db, "SELECT f.*, p.Name as PatientName FROM finalbill f INNER JOIN opdpatientregister p ON f.PatientId = p.PatientId WHERE DischargeDate BETWEEN '$from' AND '$to' AND f.PayableAmount > 0");
        $query3 = mysqli_query($db, "SELECT i.*, p.Name as PatientName FROM inbill i INNER JOIN patientdetails p ON i.PatientId = p.PatId WHERE BDate BETWEEN '$from' AND '$to' AND i.PayableAmount > 0");
        $query4 = mysqli_query($db, "SELECT b.*, p.Name as PatientName FROM bill b INNER JOIN opdpatientregister p ON b.PatientId = p.PatientId WHERE BDate BETWEEN '$from' AND '$to' AND b.PayableAmount > 0");
    }

    if (mysqli_num_rows($query1) != 0 || mysqli_num_rows($query2) != 0 || mysqli_num_rows($query3) != 0 || mysqli_num_rows($query4) != 0) {
        echo '<button type="button" class="btn btn-primary" onclick="exportToExcel(\'tblexportData\')">Export To Excel File</button><br><br>';
        echo '<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>
                </tr>
            </thead>
            <tbody>';

        $i = 1;

        // Function to calculate totals based on payment mode and department
        function calculateTotals($amount, $paymentMode, &$paymentModeTotals, &$ipdTotal, &$opdTotal, $isIpd)
        {
            global $total;
            $total += $amount;
            $paymentModeTotals[$paymentMode] += $amount;
            if ($isIpd) {
                $ipdTotal += $amount;
            } else {
                $opdTotal += $amount;
            }
        }

        // Display and calculate totals for each query
        while ($row = $query1->fetch_assoc()) {
            $payAmount = $row['Amount'];
            calculateTotals($payAmount, $row['PaymentMode'], $paymentModeTotals, $ipdTotal, $opdTotal, false); // Assume Advance is OPD
            echo "<tr><td>$i</td>
                <td>{$row['PatId']}</td>
                <td>{$row['PatientName']}</td>
                <td>Advance</td>
                <td>{$row['PaymentMode']}</td>
                <td><a href=\"ViewTransBill1.php?Billno={$row['BillNo']}&PatId={$row['PatId']}&type=Advance\" target=\"_blank\">{$row['BillNo']}</a></td>
                <td>{$row['AdvanceDate']}</td>
                <td>{$payAmount}</td>
                </tr>";
            $i++;
        }

        while ($row = $query2->fetch_assoc()) {
            $payAmount = ($row['PaymentMode'] == "Insurance") ? $row['TotalAmount'] : $row['PayableAmount'];
            calculateTotals($payAmount, $row['PaymentMode'], $paymentModeTotals, $ipdTotal, $opdTotal, true); // Assume Final Bill is IPD
            echo "<tr><td>$i</td>
                <td>{$row['PatientId']}</td>
                <td>{$row['PatientName']}</td>
                <td>Discharge</td>
                <td>{$row['PaymentMode']}</td>
                <td>{$row['FinalBillNo']}</td>
                <td>{$row['DischargeDate']}, {$row['DischargeTime']}</td>
                <td>{$payAmount}</td>
                </tr>";
            $i++;
        }

        while ($row = $query3->fetch_assoc()) {
            $payAmount = $row['PayableAmount'];
            calculateTotals($payAmount, $row['PaymentMode'], $paymentModeTotals, $ipdTotal, $opdTotal, false); // Assume InBill is OPD
            echo "<tr><td>$i</td>
                <td>{$row['PatientId']}</td>
                <td>{$row['PatientName']}</td>
                <td>Investigation Charges</td>
                <td>{$row['PaymentMode']}</td>
                <td>{$row['BillNo']}</td>
                <td>{$row['BDate']}</td>
                <td>{$payAmount}</td>
                </tr>";
            $i++;
        }

        while ($row = $query4->fetch_assoc()) {
            $payAmount = $row['PayableAmount'];
            calculateTotals($payAmount, $row['PaymentMode'], $paymentModeTotals, $ipdTotal, $opdTotal, true); // Assume Bill is IPD
            echo "<tr><td>$i</td>
                <td>{$row['PatientId']}</td>
                <td>{$row['PatientName']}</td>
                <td>{$row['PatientType']}</td>
                <td>{$row['PaymentMode']}</td>
                <td><a href=\"ViewTransBill.php?Billno={$row['BillNo']}&type={$row['PatientType']}\" target=\"_blank\">{$row['BillNo']}</a></td>
                <td>{$row['BDate']}</td>
                <td>{$payAmount}</td>
                </tr>";
            $i++;
        }

        echo "<tr>
                <td colspan='6'></td>
                <td>Total Transaction</td>
                <td>$total</td>
            </tr>";

        // Display totals by payment mode
        foreach ($paymentModeTotals as $mode => $amount) {
            echo "<tr>
                    <td colspan='6'></td>
                    <td>Total $mode</td>
                    <td>$amount</td>
                </tr>";
        }

        // Display totals by department
        echo "<tr>
                <td colspan='6'></td>
                <td>Total IPD</td>
                <td>$ipdTotal</td>
            </tr>";
        echo "<tr>
                <td colspan='6'></td>
                <td>Total OPD</td>
                <td>$opdTotal</td>
            </tr>";

        echo '</tbody></table>';
    } else {
        echo "<h3>No Transactions for this Date</h3>";
    }
}
?>

          ?>

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