212 lines
8.1 KiB
PHP
212 lines
8.1 KiB
PHP
<?php
|
|
// include('pdf_header.php');
|
|
include('includes/config/config.php');
|
|
include('includes/functions.php');
|
|
header('Content-Type: application/force-download');
|
|
header('Content-disposition: attachment; filename=clinical_recovery_report_excel.xls');
|
|
error_reporting(E_ERROR | E_PARSE);
|
|
$start = $_POST['startexpDate'];
|
|
$end = $_POST['endexpDate'];
|
|
$pat_cat = $_POST['emp_type'];
|
|
$charge = $_POST['chargeable'];
|
|
|
|
?>
|
|
<link rel="preconnect" href="https://fonts.googleapis.com">
|
|
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
|
|
<link href="https://fonts.googleapis.com/css2?family=Mulish:wght@300;400;500;600;700;800&display=swap" rel="stylesheet">
|
|
<style>
|
|
* {
|
|
font-family: 'Mulish', sans-serif;
|
|
}
|
|
|
|
#main-table {
|
|
width: 100%;
|
|
border-collapse: collapse;
|
|
border: 1px solid black;
|
|
font-size: 11px;
|
|
}
|
|
|
|
#main-table td {
|
|
padding: 5px;
|
|
border: 1px solid black;
|
|
font-size: 11px;
|
|
}
|
|
|
|
#main-table tr:nth-child(even) {
|
|
background-color: lightgoldenrodyellow;
|
|
color: black;
|
|
}
|
|
|
|
#main-table thead tr {
|
|
background-color: lightcoral;
|
|
}
|
|
</style>
|
|
|
|
|
|
<body>
|
|
<?php include('excel_ohc_header.php'); ?>
|
|
<table width="100%">
|
|
<tr>
|
|
|
|
<?php if ($charge == '0') { ?>
|
|
<td style="font-size: 15px;"><strong style="margin-left: 600px">Non Clinical Recovery Report</strong></td>
|
|
|
|
<?php
|
|
} else {
|
|
?>
|
|
<td style="font-size: 15px;"><strong style="margin-left: 600px">Clinical Recovery Report</strong></td>
|
|
<?php
|
|
}
|
|
?>
|
|
|
|
|
|
</tr>
|
|
<tr>
|
|
<td align="left" style="font-size: 12px"><strong>For Duration: <?php echo $start; ?> -
|
|
<?php echo $end; ?></strong></td>
|
|
<td align="right" style="font-size: 15px"><button align="center" id="printPageButton" class="btn btn-success" onClick="window.print();">Print</button></td>
|
|
</tr>
|
|
|
|
</table>
|
|
<br>
|
|
<table id="main-table" border="1" width="100%" cellspacing="0">
|
|
|
|
<strong>
|
|
<tr style="background-color:cadetblue; height: 40px;">
|
|
<th align="center">Sr No</th>
|
|
<th align="center">Employee Name</th>
|
|
<?php if ($pat_cat == '3') { ?>
|
|
<th align="center">Employer Contractor</th>
|
|
<?php } ?>
|
|
<th align="center">Emp Code</th>
|
|
<th align="center">Medicines</th>
|
|
<th align="center">P-Lab</th>
|
|
<th align="center">X Ray</th>
|
|
<th align="center">Checkup-Cost</th>
|
|
<th align="center">Total</th>
|
|
</tr>
|
|
</strong>
|
|
|
|
<?php
|
|
$ohc_id = $_SESSION['current_ohcttype'];
|
|
$sql = "select * from patient_master where patient_cat_id= '" . $pat_cat . "'";
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
$emp_code = "";
|
|
$emp_name = "";
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$data = array();
|
|
array_push($data, $row['id']);
|
|
|
|
$emp_code = $row['emp_code'];
|
|
$emp_name = $row['patient_name'];
|
|
$employer_contractor = getFieldFromTable('employer_contractor_name', 'employer_contractor', 'id', $row['employer_contractor_id']);
|
|
|
|
$dependent = "select * from patient_master where primary_patient_id='" . $row['id'] . "'";
|
|
$dep_result = mysqli_query($conn, $dependent);
|
|
while ($row_dep = mysqli_fetch_assoc($dep_result)) {
|
|
array_push($data, $row_dep['id']);
|
|
}
|
|
|
|
$ids = join(",", $data);
|
|
error_log("data " . $ids);
|
|
|
|
$opd_cost = 0;
|
|
$lab_cost = 0;
|
|
$xray_cost = 0;
|
|
|
|
$xray = "select sum(bd.total_amount) as lab_cost,IsChargeable from bill_master b left join bill_details bd on bd.bill_id=b.bill_id where ohc_type_id='" . $ohc_id . "' and patient_id in ($ids) and date(bill_date) between STR_TO_DATE('" . $start . "', '%d-%m-%Y') AND STR_TO_DATE('" . $end . "', '%d-%m-%Y') and bd.product_list = (select product_id from product_service_master where product_name='X RAY') and IsChargeable='" . $charge . "'";
|
|
$result_xray = mysqli_query($conn, $xray);
|
|
error_log("xray lab query bill " . $xray);
|
|
|
|
if ($row_xray = mysqli_fetch_assoc($result_xray)) {
|
|
$xray_cost = $row_xray['lab_cost'];
|
|
} else {
|
|
$xray_cost = 0;
|
|
}
|
|
|
|
|
|
$plab = "select sum(bd.total_amount) as lab_cost,IsChargeable from bill_master b left join bill_details bd on bd.bill_id=b.bill_id where ohc_type_id='" . $ohc_id . "' and patient_id in ($ids) and date(bill_date) between STR_TO_DATE('" . $start . "', '%d-%m-%Y') AND STR_TO_DATE('" . $end . "', '%d-%m-%Y') and bd.product_list != (select product_id from product_service_master where product_name='X RAY') and IsChargeable='" . $charge . "'";
|
|
$result_lab = mysqli_query($conn, $plab);
|
|
error_log("p lab query bill " . $plab);
|
|
|
|
|
|
if ($row_lab = mysqli_fetch_assoc($result_lab)) {
|
|
$lab_cost = $row_lab['lab_cost'];
|
|
} else {
|
|
$lab_cost = 0;
|
|
}
|
|
|
|
$cost_arr = array();
|
|
$type_arr = array();
|
|
$final_total = 0;
|
|
|
|
if ($charge != '0') {
|
|
$checkup = "select * from checkup_type where type_state!='Yes' ";
|
|
|
|
$res = mysqli_query($conn, $checkup);
|
|
|
|
while ($rowc = mysqli_fetch_assoc($res)) {
|
|
|
|
array_push($type_arr, $rowc['checkup_type_id']);
|
|
|
|
$check_cost = $rowc['cost'];
|
|
|
|
$final = "select (count(*) * $check_cost) as total_cost from checkup_form c left join checkup_type t on c.checkup_type_id=t.checkup_type_id where t.checkup_type_id = '" . $rowc['checkup_type_id'] . "' and c.ohc_type_id='" . $ohc_id . "' and c.emp_id in ($ids) and date(checkup_date) between STR_TO_DATE('" . $start . "', '%d-%m-%Y') AND STR_TO_DATE('" . $end . "', '%d-%m-%Y') ";
|
|
|
|
error_log("medical cost query " . $final);
|
|
|
|
$resultc = mysqli_query($conn, $final);
|
|
$rowsc = mysqli_fetch_assoc($resultc);
|
|
|
|
$cost_arr[$rowc['checkup_type_id']] = $rowsc['total_cost'];
|
|
|
|
$final_total += $rowsc['total_cost'];
|
|
}
|
|
|
|
error_log("final medical cost val " . print_r($cost_arr, true));
|
|
}
|
|
|
|
$opd = "select sum(t.issued_item_cost) as opd_cost,IsChargeable from employee_appointment em left join treatment_cost_master t on t.appointment_id = em.appointment_id where t.ohc_type_id='" . $ohc_id . "' and date(em.appointment_date) between STR_TO_DATE('" . $start . "', '%d-%m-%Y') AND STR_TO_DATE('" . $end . "', '%d-%m-%Y') and emp_id in ($ids) and IsChargeable='" . $charge . "'";
|
|
|
|
$result_opd = mysqli_query($conn, $opd);
|
|
error_log("opd cost query " . $opd);
|
|
|
|
|
|
if ($row_opd = mysqli_fetch_assoc($result_opd)) {
|
|
$opd_cost = $row_opd['opd_cost'];
|
|
} else {
|
|
$opd_cost = 0;
|
|
}
|
|
|
|
|
|
if ($opd_cost != 0 || $lab_cost != 0 || $xray_cost != 0 || $final_total != 0) {
|
|
$total_cost = $opd_cost + $lab_cost + $xray_cost + $final_total;
|
|
?>
|
|
<tr>
|
|
<td align="center"><?= ++$count ?></td>
|
|
<td align="center"><?= $emp_name ?></td>
|
|
<?php if ($pat_cat == '3') { ?>
|
|
<th align="center"><?= $employer_contractor ?></th>
|
|
<?php } ?>
|
|
<td align="center"><?= $emp_code ?></td>
|
|
|
|
|
|
<td align="center"><?= $opd_cost ?></td>
|
|
|
|
|
|
<td align="center"><?= $lab_cost ?></td>
|
|
|
|
<td align="center"><?= $xray_cost ?></td>
|
|
|
|
<td align="center"><?= $final_total ?></td>
|
|
|
|
<td align="center"><?= $total_cost ?></td>
|
|
</tr>
|
|
|
|
<?php
|
|
}
|
|
}
|
|
?>
|
|
</table>
|
|
</body>
|