<?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['contact'])) || (isset($_POST['PatientId']))) {


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

    $query1 = mysqli_query($db, "SELECT * FROM patientdetails WHERE Contact='$contact'");
    $query2 = mysqli_query($db, "SELECT * FROM opdpatientregister WHERE Contact='$contact'");
    if (mysqli_num_rows($query1) != 0) {
      $url = "/Billing/NewBill.php?Contact=$contact";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    } elseif (mysqli_num_rows($query2) != 0) {
      $url = "/Billing/NewBill.php?OPDcon=$contact";
      echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
    } else {
      echo ("Error description: " . $mysqli->error);
    }
  }
}
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'];
    $mode=$_POST['PaymentMode'];
    $InsuranceName=$_POST['InsuranceName'];
    $InsuranceNo=$_POST['InsuranceNo'];
    $preparedby=$_POST['preparedby'];
    $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'];
        }
        mysqli_query($db,"INSERT INTO advance(PatId,Amount,AdvanceDate,PaymentMode,InsuranceName,InsuranceNo,PreparedBy) VALUES('$patid','$amount','$date','$mode','$InsuranceName','$InsuranceNo','$preparedby')");
        $fetchid=mysqli_query($db,"SELECT * FROM advance WHERE PatId='$patid' AND Amount='$amount' AND AdvanceDate='$date'");
while ($row=$fetchid->fetch_assoc()) {
  $abillid="VH/AB20/".$row['SlNo'];
}
mysqli_query($db,"UPDATE advance SET BillNo='$abillid' WHERE PatId='$patid' AND Amount='$amount' AND AdvanceDate='$date'");
        
         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\" />";    
    }
}




/* 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\" />";
  }
}


/*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\" />"; 


}
/*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 Patient ID is provided
    if ($patientId != "") {
        // Query to fetch Patient details
        $patientQuery = mysqli_query($db, "SELECT * FROM patientdetails WHERE PatId='$patientId'");
        
        // Query to fetch OPD details
        $opdQuery = mysqli_query($db, "SELECT * FROM opdpatientregister WHERE PatientId='$patientId'");
        
        // Query to fetch Advance details
        $advanceQuery = mysqli_query($db, "SELECT * FROM advance WHERE PatId='$patientId'");

        if (mysqli_num_rows($patientQuery) != 0) {
            // Redirect to Patient Details page
            $url = "/Billing/BillList.php?PatientID=$patientId";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        } elseif (mysqli_num_rows($opdQuery) != 0) {
            // Redirect to OPD Bill List
            $url = "/Billing/opdtransbill.php?PatientID=$patientId";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        } elseif (mysqli_num_rows($advanceQuery) != 0) {
            // 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('No records found for the given Patient ID!')</script>";
            $url = "/Billing/SearchBill.php";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=$url\" />";
        }
    }

    // If contact number is provided
    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'");
        
        // Query to fetch Advance details by contact number
        $advanceContactQuery = mysqli_query($db, "SELECT * FROM advance WHERE Contact='$contact'"); // 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'");
  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,bank,cardno,upi,cash) 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]','$_POST[bank]','$_POST[cardno]','$_POST[upi]','$_POST[cash]')");
    $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\" />";
  }
}

/*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\" />"; 
// }

// }



/*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\" />";
}


/* 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'];

  $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) VALUES('$billNo','$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, created_by,modified_by) 
                      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\" />";
  }
}





/*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\" />";
  }
}
