<?php

include("dbConfig.php");
ini_set('display_errors', E_ALL);
ini_set('display_startup_errors', E_ALL);
error_reporting(E_ALL);
session_start();
if (isset($_SESSION['username']) && isset($_SESSION['name'])) {
  $username = $_SESSION['username'];
  $name = $_SESSION['name'];
} else {
  echo '<META http-equiv="refresh" content="0;logout.php">';
}

if (isset($_POST['PatientId']) || isset($_POST['contact'])) {
  $patientId = $_POST['PatientId'] ?? "";
  $contact = $_POST['contact'] ?? "";

  // If PatientId is provided
  if (!empty($patientId)) {
    // Check in OPD
    $query1 = mysqli_query($db, "SELECT * FROM opdpatientregister WHERE PatientId='$patientId'");
    // Check in IPD
    $query2 = mysqli_query($db, "SELECT * FROM patientdetails WHERE PatId='$patientId'");

    if (mysqli_num_rows($query1) > 0) {
      // If found in OPD, redirect to billing
      $url = "/Billing/NewBill.php?OPDpid=$patientId";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    } elseif (mysqli_num_rows($query2) > 0) {
      // If found in IPD, ask for OPD registration
      echo "<script>alert('You are an existing IPD patient. Please register on OPD to generate a bill.');</script>";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=/Billing/billingId.php\" />";
      exit;
    } else {
      // If PatientId is not found in either table
      echo "<script>alert('Incorrect Patient ID!');</script>";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=/Billing/billingId.php\" />";
    }
  }
  // If contact is provided
  elseif (!empty($contact)) {
    // Check in OPD first
    $query1 = mysqli_query($db, "SELECT * FROM opdpatientregister WHERE Contact='$contact'");

    if (mysqli_num_rows($query1) > 0) {
      // If multiple records are found, get the latest one (LIFO)
      $opdData = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM opdpatientregister WHERE Contact='$contact' ORDER BY AdmitDate DESC LIMIT 1"));
      $opdPatientId = $opdData['PatientId'];
      // print_r($opdPatientId);
      // exit;
      // Redirect to billing for OPD patient
      $url = "/Billing/NewBill.php?OPDpid=$opdPatientId";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
      exit;
    } else {
      // Check in IPD if not found in OPD
      $query2 = mysqli_query($db, "SELECT * FROM patientdetails WHERE Contact='$contact'");

      if (mysqli_num_rows($query2) > 0) {
        // If found in IPD, alert and ask for OPD registration
        echo "<script>alert('You are an existing IPD patient. Please register on OPD to generate a bill.');</script>";
        echo "<meta http-equiv=\"refresh\" content=\"0;url=/Billing/billingId.php\" />";
        exit;
      } else {
        // If contact is not found in either OPD or IPD
        echo "<script>alert('Incorrect contact!');</script>";
        echo "<meta http-equiv=\"refresh\" content=\"0;url=/Billing/billingId.php\" />";
      }
    }
  }
}


if ((isset($_POST['Incontact'])) || (isset($_POST['InPatientId']))) {


  $patientId = $_POST['InPatientId'];
  $contact = $_POST['Incontact'];
  if ($patientId != "") {
    $query1 = mysqli_query($db, "SELECT * FROM patientdetails WHERE PatId='$patientId'");
    if (mysqli_num_rows($query1) != 0) {
      $url = "/Billing/InBill.php?PatientID=$patientId";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    } else {
      echo "<script>alert('Incorrect Patient ID!')</script>";
      $url = "/Billing/InvestigationId.php";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    }
  } elseif ($contact != "") {

    $query1 = mysqli_query($db, "SELECT * FROM patientdetails WHERE Contact='$contact'");
    if (mysqli_num_rows($query1) != 0) {
      $url = "/Billing/InBill.php?Contact=$contact";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    } else {
      echo "<script>alert('Incorrect Phone Number or Entered number is not registered!')</script>";
      $url = "/Billing/InvestigationId.php";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    }
  }
}
/*Add advance amount*/
// if (isset($_POST['AdvanceAmount'])) {
//     $amount = $_POST['AdvanceAmount'];
//     $date = $_POST['AdDate'];
//     $time = $_POST['AdTime']; // Get the time input from the form
//     $mode = $_POST['PaymentMode'];
//     $InsuranceName = $_POST['InsuranceName'];
//     $InsuranceNo = $_POST['InsuranceNo'];
//     $upi = $_POST['upi'];
//     $cash = $_POST['cash'];
//     $bank = $_POST['bank'];
//     $cardno = $_POST['cardno'];
//     $preparedby=$_POST['preparedby'];

//     // Query to fetch patient details
//     $qu1 = mysqli_query($db, "SELECT * FROM patientdetails WHERE Contact='$_POST[Adcontact]' OR PatId='$_POST[AdPatientId]'");
    
//     if (mysqli_num_rows($qu1) != 0) {
//         while ($row = $qu1->fetch_assoc()) {
//             $patid = $row['PatId'];
//         }

//         // Insert advance details including AdTime
//         mysqli_query($db, "INSERT INTO advance(PatId, Amount, AdvanceDate, advanceTime, PaymentMode, InsuranceName, InsuranceNo, upi, cash, bank, cardno, PreparedBy) VALUES('$patid', '$amount', '$date', '$time', '$mode', '$InsuranceName', '$InsuranceNo', '$upi', '$cash', '$bank', '$cardno', '$preparedby')");

//         // Fetch the newly inserted record
//         $fetchid = mysqli_query($db, "SELECT * FROM advance WHERE PatId='$patid' AND Amount='$amount' AND AdvanceDate='$date' AND advanceTime='$time'");
//         while ($row = $fetchid->fetch_assoc()) {
//             $abillid = "VH/AB24/" . $row['SlNo'];
//         }

//         // Update the bill number
//         mysqli_query($db, "UPDATE advance SET BillNo='$abillid' WHERE PatId='$patid' AND Amount='$amount' AND AdvanceDate='$date' AND advanceTime='$time'");

//         echo "<script>alert('Advance Amount is added and Bill is generated!')</script>";
//         $url = "/Billing/AdvanceBill.php?PatId=$patid&amount=$amount&addate=$date&mode=$mode&preby=$preparedby&iname=$InsuranceName&ino=$InsuranceNo";
//         echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
//     } else {
//         echo "<script>alert('Incorrect Patient ID or Contact Number!')</script>";
//         $url = "/Billing/AddAdvance.php";
//         echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
//     }
// }

if (isset($_POST['AdvanceAmount'])) {
  $amount = $_POST['AdvanceAmount'];
  $date = $_POST['AdDate'];
  $time = $_POST['AdTime'];
  $mode = $_POST['PaymentMode'];
  $InsuranceName = $_POST['InsuranceName'];
  $InsuranceNo = $_POST['InsuranceNo'];
  $preparedby = $_POST['preparedby'];

  $bank = $_POST['bank'] ?? '';
  $cardno = $_POST['cardno'] ?? '';
  $bank_a = $_POST['bank-a'] ?? '';
  $cardno_a = $_POST['cardno-a'] ?? '';
  $cash_a = $_POST['cash-a'] ?? '';
  $card_a = $_POST['card-a'] ?? '';

  $final_bank = !empty($bank) ? $bank : (!empty($bank_a) ? $bank_a : null);
  $final_cardno = !empty($cardno) ? $cardno : (!empty($cardno_a) ? $cardno_a : null);

  $upi = $_POST['upi'];
  $cash = $_POST['cash'];
  $upiid = $_POST['upiid'];

  $qu1 = mysqli_query($db, "SELECT * FROM patientdetails WHERE Contact='$_POST[Adcontact]' OR PatId='$_POST[AdPatientId]'");
  
  if (mysqli_num_rows($qu1) != 0) {
    // Set SQL mode if needed
    mysqli_query($db, "SET SESSION sql_mode = ''");

    while ($row = $qu1->fetch_assoc()) {
      $patid = $row['PatId'];
    }

    // Insert the data
    mysqli_query($db, "INSERT INTO advance(PatId,Amount,AdvanceDate,advanceTime,PaymentMode,InsuranceName,InsuranceNo,upi, cash, bank, cardno, cashamount, cardamount, upiid, PreparedBy) 
    VALUES('$patid','$amount','$date','$time','$mode','$InsuranceName',
            '$InsuranceNo','$upi', '$cash', '$final_bank', '$final_cardno','$cash_a','$card_a', '$upiid','$preparedby')");

    // Fetch the last inserted SlNo
    $lastInserted = mysqli_query($db, "SELECT SlNo FROM advance WHERE PatId='$patid' AND Amount='$amount' AND AdvanceDate='$date' ORDER BY SlNo DESC LIMIT 1");

    if ($row = mysqli_fetch_assoc($lastInserted)) {
      $lastSlNo = $row['SlNo'];
      $abillid = "VH/AB24/" . $lastSlNo;
      mysqli_query($db, "UPDATE advance SET BillNo='$abillid' WHERE SlNo='$lastSlNo'");
    }

    echo "<script>alert('Advance Amount is added and Bill is generated!')</script>";
    $url = "/Billing/AdvanceBill.php?PatId=$patid&amount=$amount&addate=$date&mode=$mode&preby=$preparedby&iname=$InsuranceName&ino=$InsuranceNo&upi=$upi&cash=$cash";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  } else {
    echo "<script>alert('Incorrect Patient ID or Contact Number!')</script>";
    $url = "/Billing/AddAdvance.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  }
}



/* Update Ward*/
if (isset($_POST['UpdateWard'])) {
  $patid = $_POST['uppdatePatientId'];
  $con = $_POST['updatecontact'];
  $ward = $_POST['UpdateWard'];

  $query = mysqli_query($db, "SELECT * FROM patientdetails WHERE Contact='$con' OR PatId='$patid'");
  if (mysqli_num_rows($query) != 0) {
    mysqli_query($db, "UPDATE patientdetails SET WardType='$ward' WHERE Contact='$con' OR PatId='$patid'");
    echo "<script>alert('Patient Ward detail is Updated!')</script>";
    $url = "/Billing/index.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  } else {
    echo "<script>alert('Incorrect Phone Number or Entered number is not registered!')</script>";
    $url = "/Billing/index.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  }
}


/*OLD - OPD Registration*/
/*OPD Registration*/
// if(isset($_POST['opdname'])){
// $Pname=$_POST['opdname'];
// $fhname=$_POST['fhname'];
// $contact1=$_POST['contact1'];
// $email=$_POST['email'];
// $age=$_POST['age'];
// $gender=$_POST['Gender'];
// $Address=$_POST['Address'];
// $Address.=", ".$_POST['City'];
// $Address.=", ".$_POST['State'];
// $Address.=", ".$_POST['Country'];
// $Consultant=$_POST['Consultant'];
// $admitDate=$_POST['admitDate'];

// mysqli_query($db,"INSERT INTO opdpatientregister(Name,FHN,Contact,Email,Age,Gender,Address,Consultant,admitDate) VALUES('$Pname','$fhname','$contact1','$email','$age','$gender','$Address','$Consultant','$admitDate')");

// $patid=mysqli_query($db,"SELECT SlNo FROM opdpatientregister WHERE Contact='$contact1' AND Name='$Pname'");
// while($row=$patid->fetch_assoc()){
//   $patid1=$row['SlNo'];
// }
// $patId="VH24/OPD".$patid1;
// mysqli_query($db,"UPDATE opdpatientregister SET PatientId='$patId' where Contact='$contact1' AND Name='$Pname'");
// echo "<script>alert('OPD Patient is Registered!')</script>";
// $url="https://vidyaranyahospital.in/Billing/index.php";
// echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />"; 
// }

/*Add OPD Patient Registration*/
if (isset($_POST['opdname'])) {
  $title = $_POST['title'] ?? 'N/A';
  $Pname = $_POST['opdname'];
  $fhname = $_POST['fhname'];
  $contact1 = $_POST['contact1'];
  $email = $_POST['email'];
  $age = $_POST['age'];
  $gender = $_POST['Gender'];
  $Address = $_POST['Address'];
  $Address .= ", " . $_POST['City'];
  $Address .= ", " . $_POST['State'];
  $Address .= ", " . $_POST['Country'];
  $Consultant = $_POST['Consultant'];
  $admitDate = $_POST['AdmitDate'];
  $admitTime = $_POST['AdmitTime'];

  $lastSlNoQuery = mysqli_query($db, "SELECT MAX(SlNo) AS LastSlNo FROM opdpatientregister");
  $lastSlNo = $lastSlNoQuery->fetch_assoc()['LastSlNo'] ?? 0;
  $nextSlNo = $lastSlNo + 1;
  $patId = "VH24/OPD" . $nextSlNo;

  mysqli_query($db, "INSERT INTO opdpatientregister(title,Name,FHN,Contact,Email,Age,Gender,Address,Consultant,admitDate, admitTime, PatientId) VALUES('$title','$Pname','$fhname','$contact1','$email','$age','$gender','$Address','$Consultant','$admitDate','$admitTime','$patId')");

  echo "<script>alert('OPD Patient is Registered!')</script>";
  $url = "/Billing/OPDPatientsList.php";
  echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
}

/*Upload*/
if (isset($_POST['UploadPatientId'])) {
  $patid = $_POST['UploadPatientId'];
  $rname = $_POST['RName'];
  $datenow = date("Y-m-d");
  $fileName = $_FILES['report']['name'];
  $fileTmpName = $_FILES['report']['tmp_name'];
  $fileSize = $_FILES['report']['size'];
  $fileError = $_FILES['report']['error'];
  $fileType = $_FILES['report']['type'];

  $fileExt = explode('.', $fileName);
  $fileActualExt = strtolower(end($fileExt));
  $allowed = array('pdf', 'PDF');

  if (in_array($fileActualExt, $allowed)) {
    if ($fileError === 0) {
      if ($fileSize <= 3000000) {
        $PfileNameNew = uniqid('', true) . "." . $fileActualExt;


        $fileDestination = 'assets/PatientReports/' . $PfileNameNew;
        move_uploaded_file($fileTmpName, $fileDestination);

        $pidsearch = mysqli_query($db, "SELECT * FROM patientdetails WHERE PatId='$patid'");
        if (mysqli_num_rows($pidsearch) != 0) {
          mysqli_query($db, "INSERT INTO uploadreports(PatId,ReportName,Report,DateUploaded) VALUES('$patid','$rname','$PfileNameNew','$datenow')");

          echo "<script>alert('Report Uploaded!')</script>";
          $url = "/Billing/UploadReport.php";
          echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        } else {
          echo "<script>alert('Entered Patient ID does not exists!')</script>";
          $url = "/Billing/UploadReport.php";
          echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        }
      } else {
        echo "<script>alert('Your File Size is large!')</script>";
        $url = "/Billing/UploadReport.php";
        echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
      }
    } else {
      echo "<script>alert('Error Uploading File!')</script>";
      $url = "/Billing/UploadReport.php";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    }
  } else {
    echo "<script>alert('This File format does not support!Upload Only PDF!')</script>";
    $url = "/Billing/UploadReport.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  }
}
/*search Bills*/
if ((isset($_POST['searchcontact'])) || (isset($_POST['searchPatientId']))) {

    $patientId = $_POST['searchPatientId'];
    $contact = $_POST['searchcontact'];
    if ($patientId != "") {
        $patientQuery = mysqli_query($db, "SELECT * FROM patientdetails WHERE PatId='$patientId' AND `is_deleted` = 1 ");
        
        $opdQuery = mysqli_query($db, "SELECT * FROM opdpatientregister WHERE PatientId='$patientId' AND `is_deleted` = 1 ");
        
        $advanceQuery = mysqli_query($db, "SELECT * FROM advance WHERE PatId='$patientId' AND `is_deleted` = 1 ");

        if (mysqli_num_rows($patientQuery) != 0) {
            $url = "/Billing/BillList.php?PatientID=$patientId";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        } elseif (mysqli_num_rows($opdQuery) != 0) {
            $url = "/Billing/opdtransbill.php?PatientID=$patientId";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        } elseif (mysqli_num_rows($advanceQuery) != 0) {
            $url = "/Billing/BillList.php?PatientID=$patientId"; 
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        } else {
            echo "<script>alert('No records found for the given Patient ID!')</script>";
            $url = "/Billing/SearchBill.php";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        }
    }

   
    elseif ($contact != "") {
        // Query to fetch Patient details by contact number
        $patientContactQuery = mysqli_query($db, "SELECT * FROM patientdetails WHERE Contact='$contact'");
        
        // Query to fetch OPD details by contact number
        $opdContactQuery = mysqli_query($db, "SELECT * FROM opdpatientregister WHERE Contact='$contact' AND `is_deleted` = 1 ");
        
        // Query to fetch Advance details by contact number
        $advanceContactQuery = mysqli_query($db, "SELECT * FROM advance WHERE Contact='$contact' AND `is_deleted` = 1 "); // Adjust as necessary

        if (mysqli_num_rows($patientContactQuery) != 0) {
            while ($row = $patientContactQuery->fetch_assoc()) {
                $patientId = $row['PatId'];
            }
            // Redirect to Patient Details page
            $url = "/Billing/BillList.php?PatientID=$patientId";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        } elseif (mysqli_num_rows($opdContactQuery) != 0) {
            while ($row = $opdContactQuery->fetch_assoc()) {
                $patientId = $row['PatientId'];
            }
            // Redirect to OPD Bill List
            $url = "/Billing/opdtransbill.php?PatientID=$patientId";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        } elseif (mysqli_num_rows($advanceContactQuery) != 0) {
            while ($row = $advanceContactQuery->fetch_assoc()) {
                $patientId = $row['PatId'];
            }
            // Redirect to Advance Bill List
            $url = "/Billing/BillList.php?PatientID=$patientId"; // Change URL if necessary
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        } else {
            echo "<script>alert('Incorrect Phone Number or the entered number is not registered!')</script>";
            $url = "/Billing/SearchBill.php";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        }
    }
}




/*insurence search Bills*/

if ((isset($_POST['insurencesearchPatientId'])) || (isset($_POST['insurencesearchcontact']))) {


  $patientId = $_POST['insurencesearchPatientId'];
  $contact = $_POST['insurencesearchcontact'];
  if ($patientId != "") {
    $query = mysqli_query($db, "SELECT * FROM patientdetails WHERE PatId='$patientId'");
    if (mysqli_num_rows($query) != 0) {
      $url = "/Billing/insurenceBillList.php?PatientID=$patientId";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    } else {
      echo "<script>alert('Incorrect Patient ID!')</script>";
      $url = "/Billing/insurence_dummy_search.php";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    }
  } elseif ($contact != "") {

    $query = mysqli_query($db, "SELECT * FROM patientdetails WHERE Contact='$contact'");
    if (mysqli_num_rows($query) != 0) {
      while ($row = $query->fetch_assoc()) {
        $patientId = $row['PatId'];
      }
      $url = "/Billing/insurenceBillList.php?PatientID=$patientId";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    } else {
      echo "<script>alert('Incorrect Phone Number or Entered number is not registered!')</script>";
      $url = "/Billing/insurence_dummy_search.php";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    }
  }
}


/*Discharge Bill*/
if (isset($_POST['DischargeDate'])) {
  $patid = $_POST['FPatientId'];
  $searchpid = mysqli_query($db, "SELECT * FROM finalbill WHERE PatientId='$patid' AND `is_deleted` = 1 ");
  if (mysqli_num_rows($searchpid) == 0) {
    $bank = !empty($_POST['bank']) ? $_POST['bank'] : (!empty($_POST['bank-a']) ? $_POST['bank-a'] : '');
    $cardno = !empty($_POST['cardno']) ? $_POST['cardno'] : (!empty($_POST['cardno-a']) ? $_POST['cardno-a'] : '');
    $cash_a = isset($_POST['cash-a']) ? $_POST['cash-a'] : '';
    $card_a = isset($_POST['card-a']) ? $_POST['card-a'] : '';
    $advanceAmount = isset($_POST['FAdvanceAmount']) && $_POST['FAdvanceAmount'] !== '' ? $_POST['FAdvanceAmount'] : 0;
    $insuranceAmt = isset($_POST['insuranceAmt']) && $_POST['insuranceAmt'] !== '' ? $_POST['insuranceAmt'] : 0;

    // Ensure $totalAmount and $payableAmount are set
    $totalAmount = isset($_POST['TotalAmount']) && $_POST['TotalAmount'] !== '' ? $_POST['TotalAmount'] : 0;
    $payableAmount = isset($_POST['PayableAmount']) && $_POST['PayableAmount'] !== '' ? $_POST['PayableAmount'] : 0;

    // Generate a new FinalBillNo (e.g., max + 1 from the database)
    $result = mysqli_query($db, "SELECT MAX(SlNo) AS maxNo FROM finalbill");
    $row = mysqli_fetch_assoc($result);
    $nextSlNo = $row['maxNo'] + 1;
    $finalBillNo = "VH/DB24/$nextSlNo";

    //   mysqli_query($db, "INSERT INTO finalbill (PatientId,AdmitDate,AdmitTime,DischargeDate,DischargeTime,DiscountAmount,AdvanceAmount,TotalAmount,insuranceAmt,PayableAmount,PreparedBy,PaymentMode,InsuranceName,InsuranceNo,bank,cardno,upi,cash, cashamount, cardamount) VALUES
    // ('$_POST[FPatientId]','$_POST[AdmitDate]','$_POST[AdmitTime]','$_POST[DischargeDate]','$_POST[DischargeTime]','$_POST[DiscountAmount]','$advanceAmount','$_POST[TotalAmount]','$insuranceAmt','$_POST[PayableAmount]','$_POST[preparedby]','$_POST[paymentmode]','$_POST[InsuranceName]','$_POST[InsuranceNo]','$bank','$cardno','$_POST[upi]','$_POST[cash]','$cash_a','$card_a')");
    //   $fetchid = mysqli_query($db, "SELECT * FROM finalbill WHERE PatientId='$_POST[FPatientId]'");
    //   while ($row = $fetchid->fetch_assoc()) {
    //     $fbillid = "VH/DB24/" . $row['SlNo'];
    //   }
    //   mysqli_query($db, "UPDATE finalbill SET FinalBillNo='$fbillid' WHERE PatientId='$_POST[FPatientId]'");

    // Include FinalBillNo in the INSERT query
    $sql = "INSERT INTO finalbill (
                FinalBillNo, PatientId, AdmitDate, AdmitTime, DischargeDate, DischargeTime, 
                DiscountAmount, AdvanceAmount, TotalAmount, insuranceAmt, PayableAmount, 
                PreparedBy, PaymentMode, InsuranceName, InsuranceNo, bank, cardno, upi, 
                cash, cashamount, cardamount, DiscountDisp, upiid
            ) VALUES (
                '$finalBillNo', '{$_POST['FPatientId']}', '{$_POST['AdmitDate']}', 
                '{$_POST['AdmitTime']}', '{$_POST['DischargeDate']}', '{$_POST['DischargeTime']}', 
                '{$_POST['DiscountAmount']}', '$advanceAmount', '$totalAmount', '$insuranceAmt', 
                '$payableAmount', '{$_POST['preparedby']}', '{$_POST['paymentmode']}', 
                '{$_POST['InsuranceName']}', '{$_POST['InsuranceNo']}', '$bank', '$cardno', 
                '{$_POST['upi']}', '{$_POST['cash']}', '$cash_a', '$card_a', '0', '{$_POST['upiid']}'
            )";

    // mysqli_query($db, "UPDATE patientdetails SET DischargeDate='$_POST[DischargeDate]' WHERE PatId='$_POST[FPatientId]'");
    // $patid = $_POST['FPatientId'];
    // echo "<script>alert('Discharge Bill Genrated!')</script>";
    // $url = "/Billing/PrintDischargeBill.php?patientId=$patid";
    // echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";

    if (mysqli_query($db, $sql)) {
      mysqli_query($db, "UPDATE patientdetails SET DischargeDate='{$_POST['DischargeDate']}' WHERE PatId='{$_POST['FPatientId']}'");
      echo "<script>alert('Discharge Bill Generated!')</script>";
      $url = "/Billing/PrintDischargeBill.php?patientId={$_POST['FPatientId']}";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    } else {
      echo "<script>alert('Error: " . mysqli_error($db) . "')</script>";
    }
  } else {
    echo "<script>alert('Discharge Bill is already Genrated for this Patient!')</script>";
    $url = "/Billing/FinaleBillId.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  }
}

/*Discharge Insurance Bill*/
// if(isset($_POST['insuranceSubmit'])){
// $patid=$_POST['FPatientId'];
// $searchpid=mysqli_query($db,"SELECT * FROM FinalBill WHERE PatientId='$patid'");
// if(mysqli_num_rows($searchpid)==0){
//       mysqli_query($db,"INSERT INTO FinalBill (PatientId,AdmitDate,AdmitTime,DischargeDate,DischargeTime,DiscountAmount,AdvanceAmount,TotalAmount, insuranceAmt, PayableAmount,PreparedBy,PaymentMode,InsuranceName,InsuranceNo) VALUES
//   ('$_POST[FPatientId]','$_POST[AdmitDate]','$_POST[AdmitTime]','$_POST[DischargeDate]','$_POST[DischargeTime]','$_POST[DiscountAmount]','$_POST[FAdvanceAmount]','$_POST[TotalAmount]', '$_POST[insuranceAmt]','$_POST[PayableAmount]','$_POST[preparedby]','$_POST[paymentmode]','$_POST[InsuranceName]','$_POST[InsuranceNo]')");
// $fetchid=mysqli_query($db,"SELECT * FROM FinalBill WHERE PatientId='$_POST[FPatientId]'");
// while ($row=$fetchid->fetch_assoc()) {
//   $fbillid="VH/DB24/".$row['SlNo'];
// }
// mysqli_query($db,"UPDATE FinalBill SET FinalBillNo='$fbillid' WHERE PatientId='$_POST[FPatientId]'");
// mysqli_query($db,"UPDATE PatientDetails SET DischargeDate='$_POST[DischargeDate]' WHERE PatId='$_POST[FPatientId]'");
// $patid=$_POST['FPatientId'];
//   echo "<script>alert('Discharge Bill Genrated!')</script>";
//   $url="/Billing/PrintDischargeBill.php?patientId=$patid";
// echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
// }
// else{
//     echo "<script>alert('Discharge Bill is already Genrated for this Patient!')</script>";
//   $url="/Billing/FinaleBillId.php";
// echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />"; 
// }

// }



/*OLD - Add patient details*/
// if (isset($_POST['Pname'])) {
//   $title = $_POST['title'];
//   $Pname = $_POST['Pname'];
//   $fhname = $_POST['fhname'];
//   $contact1 = $_POST['contact1'];
//   $contact2 = $_POST['contact2'];
//   $email = $_POST['email'];
//   $dob = $_POST['dob'];
//   $age = $_POST['age'];
//   $gender = $_POST['Gender'];
//   $blood = $_POST['Blood'];
//   $wt = $_POST['Weight'];
//   $height = $_POST['Height'];
//   $Aadhaar = $_POST['Aadhaar'];
//   $Address = $_POST['Address'];
//   $Mark = $_POST['Mark'];
//   $Photo = $_POST['Photo'];
//   $Consultant = $_POST['Consultant'];
//   $PatientType = $_POST['PatientType'];
//   $CaseType = $_POST['CaseType'];
//   $AdmitDate = $_POST['AdmitDate'];
//   $AdmitTime = $_POST['AdmitTime'];
//   $AdvanceAmount = $_POST['AdAmount'];
//   $PaymentMode = $_POST['PaymentMode'];
//   $WardType = $_POST['WardType'];
//   $preparedby = $_POST['preparedby'];
//   $InsuranceName = $_POST['InsuranceName'];
//   $InsuranceNo = $_POST['InsuranceNo'];
//   $bank = $_POST['bank'];
//   $cardno = $_POST['cardno'];
//   $upi = $_POST['upi'];
//   $cash = $_POST['cash'];
//   $upiid = $_POST['upiid'];

//   // Get the last inserted ID
//   $lastId = mysqli_insert_id($db);

//   // Now generate the patient ID (patId)
//   $patId = "VH2024/" . ($lastId + 1);
//   $DiscountAmount = 0;
//   $DischargeDate = "Zero";
//   mysqli_query($db, "INSERT INTO patientdetails(title,PatId,Name,FHName,Contact,EmContact,Email,Dob,Age,Gender,BloodGroup,BodyWeight,Height,AadhaarNo,Address,Mark,Photo,Consultant,PatientType,AdmitDate,AdmitTime,CaseType,WardType,AdvanceAmount,PaymentMode,Discountdisp,DiscountAmount,InsuranceName,InsuranceNo,bank,cardno,upi,cash,upiid,PreparedBy,DischargeDate) VALUES('$title','$patId','$Pname','$fhname','$contact1','$contact2','$email','$dob','$age','$gender','$blood','$wt','$height','$Aadhaar','$Address','$Mark','$Photo','$Consultant','$PatientType','$AdmitDate','$AdmitTime','$CaseType','$WardType','$AdvanceAmount','$PaymentMode','','$DiscountAmount','$InsuranceName','$InsuranceNo','$bank','$cardno','$upi','$cash','$upiid','$preparedby','$DischargeDate')");
//   $patid = mysqli_query($db, "SELECT SlNo FROM patientdetails WHERE Contact='$contact1' AND Name='$Pname'");
//   while ($row = $patid->fetch_assoc()) {
//     $patid1 = $row['SlNo'];
//   }
//   $patId = "VH2024/" . $patid1;
//   mysqli_query($db, "UPDATE patientdetails SET PatId='$patId' where Contact='$contact1' AND Name='$Pname'");
//  mysqli_query($db, "INSERT INTO advance (PatId, Amount, AdvanceDate, advanceTime, PaymentMode, InsuranceName, InsuranceNo, upi, cash, bank, cardno, upiid, PreparedBy) 
//                   VALUES ('$patId', '$AdvanceAmount', NOW(), NOW(), '$PaymentMode', '$InsuranceName', '$InsuranceNo', '$upi', '$cash', '$bank', '$cardno', '$upiid', '$preparedby')");


// //   mysqli_query($db, "INSERT INTO advance(PatId,Amount,AdvanceDate,PaymentMode,InsuranceName,InsuranceNo,upi,cash,bank,cardno,upiid,PreparedBy) VALUES('$patId','$AdvanceAmount','$AdmitDate','$PaymentMode','$InsuranceName','$InsuranceNo','$upi','$cash','$bank','$cardno','$upiid','$preparedby')");

//   $fetchid = mysqli_query($db, "SELECT * FROM advance WHERE PatId='$patId' AND Amount='$AdvanceAmount' AND AdvanceDate='$AdmitDate'");
//   while ($row = $fetchid->fetch_assoc()) {
//     $abillid = "VH/AB24/" . $row['SlNo'];
//   }
//   mysqli_query($db, "UPDATE advance SET BillNo='$abillid' WHERE PatId='$patId' AND Amount='$AdvanceAmount' AND AdvanceDate='$AdmitDate'");

//   echo "<script>alert('Patient Data and Advance Amount is added!')</script>";
//   $url = "/Billing/AdvanceBill.php?PatId=$patId&amount=$AdvanceAmount&addate=$AdmitDate&mode=$PaymentMode&preby=$preparedby&iname=$InsuranceName&ino=$InsuranceNo";
//   echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
// }

/* Add IPD Patient Registration */
if (isset($_POST['Pname'])) {
  $title = $_POST['title'];
  $Pname = $_POST['Pname'];
  $fhname = $_POST['fhname'];
  $contact1 = $_POST['contact1'];
  $contact2 = $_POST['contact2'];
  $email = $_POST['email'];
  $dob = $_POST['dob'];
  $age = $_POST['age'];
  $gender = $_POST['Gender'];
  $blood = $_POST['Blood'];
  $wt = $_POST['Weight'];
  $height = $_POST['Height'];
  $Aadhaar = $_POST['Aadhaar'];
  $Address = $_POST['Address'];
  $Mark = $_POST['Mark'];
  $Photo = $_POST['Photo'];
  $Consultant = $_POST['Consultant'];
  $PatientType = $_POST['PatientType'];
  $CaseType = $_POST['CaseType'];
  $AdmitDate = $_POST['AdmitDate'];
  $AdmitTime = $_POST['AdmitTime'];
  $AdvanceAmount = $_POST['AdAmount'];
  $PaymentMode = $_POST['PaymentMode'];
  $WardType = $_POST['WardType'];
  $preparedby = $_POST['preparedby'];
  $InsuranceName = $_POST['InsuranceName'];
  $InsuranceNo = $_POST['InsuranceNo'];
  $bank = $_POST['bank'] ?? '';
  $cardno = $_POST['cardno'] ?? '';
  $bank_a = $_POST['bank-a'] ?? '';
  $cardno_a = $_POST['cardno-a'] ?? '';
  $cash_a = $_POST['cash-a'] ?? '';
  $card_a = $_POST['card-a'] ?? '';
  
  $final_bank = !empty($bank) ? $bank : (!empty($bank_a) ? $bank_a : '');
  $final_cardno = !empty($cardno) ? $cardno : (!empty($cardno_a) ? $cardno_a :  '');
  
  $upi = $_POST['upi'];
  $cash = $_POST['cash'];
  $upiid = $_POST['upiid'];

$ipdLastId = "SELECT SlNo FROM patientdetails ORDER BY SlNo DESC LIMIT 1";
  $result = mysqli_query($db, $ipdLastId);
  $row = mysqli_fetch_assoc($result);
  $lastId = $row['SlNo'];
  $patId = "VH2024/" . ($lastId + 1);

  // Default values for some fields
  $DiscountAmount = 0;
  $DischargeDate = "Zero";
  mysqli_query($db, "SET SESSION sql_mode = ''");

  // Insert the patient data
  $insertQuery = "INSERT INTO patientdetails(
      title, PatId, Name, FHName, Contact, EmContact, Email, Dob, Age, Gender, 
      BloodGroup, BodyWeight, Height, AadhaarNo, Address, Mark, Photo, Consultant, 
      PatientType, AdmitDate, AdmitTime, CaseType, WardType, AdvanceAmount, PaymentMode, 
      Discountdisp, DiscountAmount, InsuranceName, InsuranceNo, bank, cardno, upi, 
      cash, upiid, PreparedBy, DischargeDate
  ) VALUES (
      '$title', '$patId', '$Pname', '$fhname', '$contact1', '$contact2', '$email', 
      '$dob', '$age', '$gender', '$blood', '$wt', '$height', '$Aadhaar', '$Address', 
      '$Mark', '$Photo', '$Consultant', '$PatientType', '$AdmitDate', '$AdmitTime', 
      '$CaseType', '$WardType', '$AdvanceAmount', '$PaymentMode', '', '$DiscountAmount', 
      '$InsuranceName', '$InsuranceNo', '$bank', '$cardno', '$upi', '$cash', '$upiid', 
      '$preparedby', '$DischargeDate'
  )";

  if (mysqli_query($db, $insertQuery)) {
    // Check if advance amount is greater than zero
    if ($AdvanceAmount > 0) {
      // Insert advance data with additional fields
      $advanceQuery = "INSERT INTO advance (
              PatId, Amount, AdvanceDate, advanceTime, PaymentMode, InsuranceName, InsuranceNo, 
              upi, cash, bank, cardno, cashamount, cardamount, upiid, PreparedBy
          ) VALUES (
              '$patId', '$AdvanceAmount', '$AdmitDate', '$AdmitTime', '$PaymentMode', '$InsuranceName', 
              '$InsuranceNo', '$upi', '$cash', '$final_bank', '$final_cardno','$cash_a','$card_a', '$upiid', '$preparedby'
          )";

      if (mysqli_query($db, $advanceQuery)) {
        // Fetch the SlNo of the advance payment
        $fetchid = mysqli_query($db, "SELECT SlNo FROM advance WHERE PatId='$patId' AND Amount='$AdvanceAmount'");
        $row = mysqli_fetch_assoc($fetchid);
        $abillid = "VH/AB24/" . $row['SlNo'];

        // Update the advance payment with the BillNo
        mysqli_query($db, "UPDATE advance SET BillNo='$abillid' WHERE SlNo='" . $row['SlNo'] . "'");
      }
    }

    // Redirect based on advance amount
    echo "<script>alert('Patient Data and Advance Amount added successfully!')</script>";
    $url = ($AdvanceAmount > 0) ? "/Billing/AdvanceBill.php?PatId=$patId&amount=$AdvanceAmount&addate=$AdmitDate&mode=$PaymentMode&preby=$preparedby&iname=$InsuranceName&ino=$InsuranceNo&upi=$upi&cash=$cash" : "IPDPatients.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  } else {
    echo "<script>alert('Error inserting patient details!')</script>";
  }

}

/* Consolidate Billing, final opd bill*/
// if (isset($_POST['part'])) {
//   $part = $_POST['part'];
//   $qty = $_POST['qty'];
//   $size = $_POST['size'];
//   $Total = $_POST['Total'];
//   $Amount = $_POST['Amount'];
//   $consultant = $_POST['consultant'];
//   $optionlab = $_POST['optionlab'];
//   $price = $_POST['price'];
//   $patid = $_POST['patid'];
// //   $disdesp = $_POST['disdesp'];
// //   $disamount = $_POST['disamount'];
//   $billtype = $_POST['billtype'];
//   $bdate = $_POST['bdate'];
//   $btime = $_POST['btime'];
//   $payable = $_POST['payable'];
//   $patienttype = $_POST['patienttype'];
//   $preparedby = $_POST['preparedby'];
//   $InsuranceName = $_POST['InsuranceName'];
//   $InsuranceNo = $_POST['InsuranceNo'];
  
//   $bank = $_POST['bank'];
//   $cardno = $_POST['cardno'];
//   $upi = $_POST['upi'];
//   $cash = $_POST['cash'];
//   $upiid = $_POST['upiid'] ?? '';
//   $cashAmount = $_POST['cashamount'] ?? '';
//   $cardAmount = $_POST['cardamount'] ?? '';

//   $new = "old";
//   $newEntry = "New Entry";
//   if (isset($_POST['edited_bill'])) {

//     date_default_timezone_set('Asia/Kolkata');
//     $pa_id = $_POST['patid'];
//     $edited_by = $_POST['preparedby'];

//     $date = date("d-m-Y h:i:sa", time());

//     $to = "vidyaranyahospital.in";

//     $subject = "Edited Bill Particulars from VH Consolidate IPD/OPD Section";

//     for ($i = 0; $i < $size; $i++) {

//       $ParticularItems = "(Particulars,Quantity,UnitPrice,Amount,consultant,optionlab)('$part[$i]','$qty[$i]','$price[$i]','$Amount[$i]','$consultant[$i]','$optionlab[$i]')";
//     }
//     $txt = "patient id:  " . $pa_id . "\r\nedited by:  " . $edited_by . "\r\nsubject: " . $subject . "\r\nParticularItems: " . $ParticularItems . "\r\nDate&time:  " . $date . "\r\nType:  " . $newEntry;
//     $header = "from : noreplay@yourself.com" . "\r\n" . "CC: somebody@example.com";
//     // $mail = mail($to, $subject,$txt, $header);


//     $qu4 = "INSERT INTO bill(PatientId,TotAmount,PayableAmount,PaymentMode,BDate,BTime,PreparedBy,PatientType,InsuranceName,InsuranceNo,bank,cardno,upi,cash) VALUES('$patid','$Total','$payable','$billtype','$bdate','$btime','$preparedby','$patienttype','$InsuranceName','$InsuranceNo','$bank','$cardno','$upi','$cash')";
//     mysqli_query($db, $qu4);

//     $getbill = mysqli_query($db, "SELECT * FROM bill WHERE PatientId='$patid' AND BDate='$bdate' AND BTime='$btime'");
//     while ($row = $getbill->fetch_assoc()) {
//       $slno = $row['BillId'];
//     }
//     $billNo = "VH/CB/" . $slno;
//     mysqli_query($db, "UPDATE bill SET BillNo='$billNo' where BillId='$slno'");

//     for ($i = 0; $i < $size; $i++) {
//       mysqli_query($db, "INSERT INTO billparticulars(BillNo, PatId, Particulars, Quantity, UnitPrice, Amount, consultant, optionlab) 
//                       VALUES('$billNo', '$patid', '$part[$i]', '$qty[$i]', '$price[$i]', '$Amount[$i]', '$consultant[$i]', '$optionlab[$i]')");

//       mysqli_query($db, "INSERT INTO edited_billparticulars(BillNo, type, PatId, Particulars, Quantity, UnitPrice, Amount, consultant, optionlab, created_by) 
//                       VALUES('$billNo', '$new', '$patid', '$part[$i]', '$qty[$i]', '$price[$i]', '$Amount[$i]', '$consultant[$i]','$optionlab[$i]', '$preparedby')");
//     }



//     echo "<script>alert('Bill Generated and Saved')</script>";
//     $url = "/Billing/PrintBill.php?BillNo=$billNo&patid=$patid";
//     echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
//   } else {

//     $lastId = mysqli_query($db, "SELECT MAX(BillId) as lastBillId FROM bill");
//     $row = mysqli_fetch_assoc($lastId);
//     $lastId = $row['lastBillId'];
//     // Now generate the billNo
//     $billNo = "VH/CB/" . ($lastId + 1);

//     $qu4 = "INSERT INTO bill(BillNo,PatientId,TotAmount,PayableAmount,PaymentMode,BDate,BTime,PreparedBy,PatientType,InsuranceName,InsuranceNo,bank,cardno,upi,cash, upiid, cashamount, cardamount) VALUES('$billNo','$patid','$Total','$payable','$billtype','$bdate','$btime','$preparedby','$patienttype','$InsuranceName','$InsuranceNo','$bank','$cardno','$upi','$cash','$upiid','$cashAmount','$cardAmount')";
//     mysqli_query($db, $qu4);

//     $getbill = mysqli_query($db, "SELECT * FROM bill WHERE PatientId='$patid' AND BDate='$bdate' AND BTime='$btime'");
//     while ($row = $getbill->fetch_assoc()) {
//       $slno = $row['BillId'];
//     }
//     $billNo = "VH/CB/" . $slno;
//     mysqli_query($db, "UPDATE bill SET BillNo='$billNo' where BillId='$slno'");

//     $stmt = $db->prepare("INSERT INTO billparticulars (BillNo, PatId, Particulars, Quantity, UnitPrice, Amount, consultant, optionlab, created_at, created_by, modified_at, modified_by) 
//                       VALUES (?, ?, ?, ?, ?, ?, ?, ?, $bdate, '', $bdate, '')");

//     for ($i = 0; $i < $size; $i++) {
//       $stmt->bind_param("ssssssss", $billNo, $patid, $part[$i], $qty[$i], $price[$i], $Amount[$i], $consultant[$i], $optionlab[$i]);
//       $stmt->execute();
//     }
//     $stmt->close();


//     echo "<script>alert('Bill Generated and Saved')</script>";
//     $url = "/Billing/PrintBill.php?BillNo=$billNo&patid=$patid";
//     echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
//   }
// }

if (isset($_POST['part'])) {
  // Collect form data
  $part = $_POST['part'];
  $qty = $_POST['qty'];
  $size = $_POST['size'];
  $Total = $_POST['Total'];
  $Amount = $_POST['Amount'];
  $consultant = $_POST['consultant'];
  $optionlab = $_POST['optionlab'] ?? [];
  $price = $_POST['price'];
  $patid = $_POST['patid'];
  $billtype = $_POST['billtype'];
  $bdate = $_POST['bdate'];
  $btime = $_POST['btime'];
  $payable = $_POST['payable'];
  $patienttype = $_POST['patienttype'];
  $preparedby = $_POST['preparedby'];
  $InsuranceName = $_POST['InsuranceName'];
  $InsuranceNo = $_POST['InsuranceNo'];
  $bank = $_POST['bank'] ?? '';
  $cardno = $_POST['cardno'] ?? '';
  $bank_a = $_POST['bank-a'] ?? '';
  $cardno_a = $_POST['cardno-a'] ?? '';
  $cash_a = $_POST['cash-a'] ?? '';
  $card_a = $_POST['card-a'] ?? '';

  // Determine which value to use for bank and cardno
  $final_bank = !empty($bank) ? $bank : (!empty($bank_a) ? $bank_a : '');
  $final_cardno = !empty($cardno) ? $cardno : (!empty($cardno_a) ? $cardno_a : '');
  $upi = $_POST['upi'];
  $cash = $_POST['cash'];
  $upiid = $_POST['upiid'] ?? '';
  $cashAmount = $_POST['cashamount'] ?? '';
  $cardAmount = $_POST['cardamount'] ?? '';

  // Check if an edited bill
  if (isset($_POST['edited_bill'])) {
    // Insert into the `edited_billparticulars` table for changes
    $created_at = $modified_at = date('Y-m-d');
    foreach ($part as $i => $particular) {
      mysqli_query($db, "INSERT INTO edited_billparticulars (BillNo, type, PatId, Particulars, Quantity, UnitPrice, Amount, consultant, optionlab, created_by, created_at, modified_at, modified_by) 
              VALUES ('$billNo', 'Edited', '$patid', '$particular', '$qty[$i]', '$price[$i]', '$Amount[$i]', '$consultant[$i]', '$optionlab[$i]', '$preparedby', '$created_at','$modified_at','$preparedby')");
    }
  }

  // Prevent duplicate entries by checking the bill existence
  $existingBillQuery = "SELECT BillId FROM bill WHERE PatientId = '$patid' AND BDate = '$bdate' AND BTime = '$btime'";
  $existingBillResult = mysqli_query($db, $existingBillQuery);

  if (mysqli_num_rows($existingBillResult) > 0) {
    $row = mysqli_fetch_assoc($existingBillResult);
    $slno = $row['BillId'];
    $billNo = "VH/CB/" . $slno;

    // Update bill data if already exists
    mysqli_query($db, "UPDATE bill SET TotAmount='$Total', PayableAmount='$payable', PaymentMode='$billtype', PreparedBy='$preparedby', InsuranceName='$InsuranceName', InsuranceNo='$InsuranceNo', bank='$bank', cardno='$cardno', upi='$upi', cash='$cash' WHERE BillId='$slno'");
  } else {
    // Generate a new bill number
    $lastIdResult = mysqli_query($db, "SELECT MAX(BillId) as lastBillId FROM bill");
    $row = mysqli_fetch_assoc($lastIdResult);
    $lastId = $row['lastBillId'];
    $billNo = "VH/CB/" . ($lastId + 1);

    // Insert new bill data
    mysqli_query($db, "INSERT INTO bill (BillNo, PatientId, TotAmount, PayableAmount, PaymentMode, BDate, BTime, PreparedBy, PatientType, InsuranceName, InsuranceNo, bank, cardno, upi, cash, upiid, cashamount, cardamount) 
          VALUES ('$billNo', '$patid', '$Total', '$payable', '$billtype', '$bdate', '$btime', '$preparedby', '$patienttype', '$InsuranceName', '$InsuranceNo', '$final_bank', '$final_cardno', '$upi', '$cash', '$upiid', '$cash_a', '$card_a')");

    $slno = mysqli_insert_id($db);
  }

  // Insert bill particulars
  $stmt = $db->prepare("INSERT INTO billparticulars (BillNo, PatId, Particulars, Quantity, UnitPrice, Amount, consultant, optionlab, created_at, created_by, modified_at, modified_by) 
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

  foreach ($part as $i => $particular) {
    $created_at = $modified_at = date('Y-m-d');
    $stmt->bind_param("ssssssssssss", $billNo, $patid, $particular, $qty[$i], $price[$i], $Amount[$i], $consultant[$i], $optionlab[$i], $created_at, $preparedby, $modified_at, $preparedby);
    $stmt->execute();
  }
  $stmt->close();

  echo "<script>alert('Bill Generated and Saved');</script>";
  $url = "/Billing/PrintBill.php?BillNo=$billNo&patid=$patid";
  echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
}





/*Investigation Charges*/
if (isset($_POST['Inpart'])) {
  $part = $_POST['Inpart'];
  $qty = $_POST['qty'];
  $size = $_POST['size'];
  $Total = $_POST['Total'];
  $Amount = $_POST['Amount'];
  $consultant = $_POST['consultant'];
  $optionlab = $_POST['optionlab'];
  $price = $_POST['price'];
  $patid = $_POST['patid'];
  $disdesp = $_POST['disdesp'];
  $disamount = $_POST['disamount'];
  $billtype = $_POST['billtype'];
  $bdate = $_POST['bdate'];
  $btime = $_POST['btime'];
  $payable = $_POST['payable'];
  $patienttype = $_POST['patienttype'];
  $preparedby = $_POST['preparedby'];
  $InsuranceName = $_POST['InsuranceName'];
  $InsuranceNo = $_POST['InsuranceNo'];



  $qu4 = "INSERT INTO inbill(PatientId,TotAmount,PayableAmount,PaymentMode,BDate,BTime,PreparedBy,PatientType,InsuranceName,InsuranceNo) VALUES('$patid','$Total','$payable','$billtype','$bdate','$btime','$preparedby','$patienttype','$InsuranceName','$InsuranceNo')";
  mysqli_query($db, $qu4);

  $getbill = mysqli_query($db, "SELECT * FROM inbill WHERE PatientId='$patid' AND BDate='$bdate' AND BTime='$btime'");
  while ($row = $getbill->fetch_assoc()) {
    $slno = $row['BillId'];
  }
  $billNo = "VH/ICB/" . $slno;
  mysqli_query($db, "UPDATE inbill SET BillNo='$billNo' where BillId='$slno'");

  for ($i = 0; $i < $size; $i++) {
    mysqli_query($db, "INSERT INTO billparticulars(BillNo, PatId, Particulars, Quantity, UnitPrice, Amount, consultant, optionlab) 
                      VALUES('$billNo', '$patid', '$part[$i]', '$qty[$i]', '$price[$i]', '$Amount[$i]', '$consultant[$i]', '$optionlab[$i]')");
  }



  echo "<script>alert('Bill Generated and Saved')</script>";
  $url = "/Billing/PrintBill.php?BillNo=$billNo&patid=$patid";
  echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
}
/*Add particulars*/
if (isset($_POST['PDescription'])) {
  $desp = $_POST['PDescription'];
  $price = $_POST['PPrice'];
  $check = mysqli_query($db, "SELECT * FROM particulars WHERE Description='$desp'");
  if (mysqli_num_rows($check) == 0) {
    mysqli_query($db, "INSERT INTO particulars(Description,Price) VALUES('$desp','$price')");
    echo "<script>alert('Particulars Saved!')</script>";
    $url = "/Billing/AddParticulars.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  } else {
    echo "<script>alert('Particulars already Exists!')</script>";
    $url = "/Billing/AddParticulars.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  }
}
/*Edit particulars*/
if (isset($_POST['eDescription'])) {
  $desp = $_POST['eDescription'];
  $price = $_POST['ePrice'];
  $partid = $_POST['partid'];
  mysqli_query($db, "UPDATE particulars SET Description='$desp',Price='$price' WHERE PartId='$partid'");
  echo "<script>alert('Particulars Updated!')</script>";
  $url = "/Billing/ViewParticulars.php";
  echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
}
/* Add Staff*/
if (isset($_POST['StaffName'])) {
  $StaffName = $_POST['StaffName'];
  $Group = $_POST['Group'];
  $Spec = $_POST['Spec'];
  $FHN = $_POST['FHN'];
  $Contact = $_POST['Contact'];
  $EmContact = $_POST['EmContact'];
  $Email = $_POST['Email'];
  $dob = $_POST['dob'];
  $Gender = $_POST['Gender'];
  $Blood = $_POST['Blood'];
  $Education = $_POST['Education'];
  $Address = $_POST['Address'];
  $Photo = $_POST['Photo'];

  mysqli_query($db, "INSERT INTO staffdetails(Name,StaffGroup,Specification,FatherHusband,Contact,EmContact,Email,dob,Gender,Blood,Education,Address,Photo) VALUES('$StaffName','$Group','$Spec','$FHN','$Contact','$EmContact','$Email','$dob','$Gender','$Blood','$Education','$Address','$Photo')");
  $fetchid = mysqli_query($db, "SELECT * FROM staffdetails WHERE Name='$StaffName' AND Contact='$Contact'");
  while ($row = $fetchid->fetch_assoc()) {
    $slno = $row['SlNo'];
    $staffid = "VH/ST24/" . $slno;
  }
  mysqli_query($db, "UPDATE staffdetails SET StaffId='$staffid' WHERE Contact='$Contact'");
  echo "<script>alert('Staff Details Added!')</script>";
  $url = "/Billing/AddStaff.php";
  echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
}
/* Update Password*/
if (isset($_POST['Password'])) {
  $pass = $_POST['Password'];
  $cpass = $_POST['cPassword'];
  if ($pass == $cpass) {
    mysqli_query($db, "UPDATE billlogin SET Password='$pass' where Username='$username'");
    echo "<script>alert('Password Updated!')</script>";
    $url = "/Billing/index.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  } else {
    echo "<script>alert('Password Mismatch!')</script>";
    $url = "/Billing/ResetPassword.php";
    echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
  }
}
