238 lines
7.1 KiB
PHP
238 lines
7.1 KiB
PHP
|
<?
|
||
|
|
//include ('pdf_header_reverse.php');
|
||
|
|
include('includes/config/config.php');
|
||
|
|
include('includes/functions.php');
|
||
|
|
|
||
|
|
header("Content-type:application/octet-stream");
|
||
|
|
|
||
|
|
header("Content-type: application/x-msdownload");
|
||
|
|
header("Content-Disposition: attachment; filename=consumable_report_excel.xls");
|
||
|
|
header("Pragma: no-cache");
|
||
|
|
header("Expires: 0");
|
||
|
|
|
||
|
|
?>
|
||
|
|
<?php
|
||
|
|
$from_date1 = date('Y-m-d', strtotime($_POST['startDate']));
|
||
|
|
$from_date2 = date('Y-m-d', strtotime($_POST['endDate']));
|
||
|
|
$from_date = date('d-m-Y', strtotime($from_date1));
|
||
|
|
$ohc = $_SESSION['current_ohcttype'];
|
||
|
|
|
||
|
|
?>
|
||
|
|
|
||
|
|
|
||
|
|
<link href="includes/css-js/admin.css" rel="stylesheet" type="text/css" />
|
||
|
|
<style>
|
||
|
|
@page {
|
||
|
|
margin: 15px;
|
||
|
|
}
|
||
|
|
|
||
|
|
.btn {
|
||
|
|
background-color: #4CAF50;
|
||
|
|
border-radius: 5%;
|
||
|
|
/* Green */
|
||
|
|
border: none;
|
||
|
|
color: white;
|
||
|
|
padding: 5px 8px;
|
||
|
|
text-align: center;
|
||
|
|
text-decoration: none;
|
||
|
|
display: inline-block;
|
||
|
|
font-size: 12px;
|
||
|
|
margin: 4px 2px;
|
||
|
|
cursor: pointer;
|
||
|
|
}
|
||
|
|
|
||
|
|
@media print {
|
||
|
|
#printPageButton {
|
||
|
|
display: none;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
</style>
|
||
|
|
|
||
|
|
<body>
|
||
|
|
<?php include('excel_ohc_header.php'); ?>
|
||
|
|
<table width="100%">
|
||
|
|
<tr>
|
||
|
|
|
||
|
|
<td align="center" style="font-size: 15px"><strong>Consumables Report</strong></td>
|
||
|
|
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" style="font-size: 12px"><strong>For Date: <? echo date("d-M-Y", strtotime($from_date)); ?></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 width="100%" border="1" cellspacing="0">
|
||
|
|
<tr>
|
||
|
|
<th width="1%" align="center">Sr</th>
|
||
|
|
<th align="center" width="5%">Items</th>
|
||
|
|
<th align="center" width="2%">Fresh</th>
|
||
|
|
<th align="center" width="2%">Previous</th>
|
||
|
|
|
||
|
|
<th width="2%" align="center">Total</th>
|
||
|
|
<th align="center" width="40%">Consumption Details</th>
|
||
|
|
<th align="center" width="2%">Total Consumed</th>
|
||
|
|
<th align="center" width="2%">Balance</th>
|
||
|
|
|
||
|
|
|
||
|
|
</tr>
|
||
|
|
<?
|
||
|
|
|
||
|
|
//echo $to_date;
|
||
|
|
|
||
|
|
|
||
|
|
// $emp_id=$_POST['empName'];
|
||
|
|
error_reporting(E_ERROR | E_PARSE);
|
||
|
|
$count = 0;
|
||
|
|
//echo $emp_id;
|
||
|
|
$data = "";
|
||
|
|
|
||
|
|
$ZeroAllowed = "N";
|
||
|
|
error_log("key val before" . $ZeroAllowed);
|
||
|
|
$ZeroAllowedKey = getConfigKey("ZERO_QUANTITY_ALLOWED");
|
||
|
|
error_log("key val " . $ZeroAllowedKey);
|
||
|
|
if ($ZeroAllowedKey == "TRUE") {
|
||
|
|
$ZeroAllowed = "Y";
|
||
|
|
}
|
||
|
|
error_log("key val after " . $ZeroAllowed);
|
||
|
|
|
||
|
|
$sql = "select cat_id from tbl_categories where cat_code in ('SMSC') order by cat_name asc";
|
||
|
|
|
||
|
|
error_log("all cat:" . $sql);
|
||
|
|
|
||
|
|
$result = mysqli_query($conn,$sql);
|
||
|
|
|
||
|
|
$med_cat = array();
|
||
|
|
|
||
|
|
function consume_details($item_id, $a, $fromdate1, $fromdate2 )
|
||
|
|
{
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
$sql_consume = "select a.appointment_id,a.ticket_no ,b.issued_qty as total_issued from employee_appointment a left join opd_consumables b on a.appointment_id = b.consume_id where b.medicine = '$item_id' and date_format(date(a.appointment_date),'%Y-%m-%d') between '$fromdate1' and '$fromdate2' ";
|
||
|
|
|
||
|
|
error_log("sql_consume:" . $sql_consume);
|
||
|
|
|
||
|
|
$result_consume = mysqli_query($GLOBALS['conn'],$sql_consume);
|
||
|
|
|
||
|
|
$consume_detail = '';
|
||
|
|
$issued_qty_total = 0;
|
||
|
|
|
||
|
|
while ($row_consume = mysqli_fetch_array($result_consume)) {
|
||
|
|
$consume_detail .= 'ID-' . $row_consume['ticket_no'] . ':' . $row_consume['total_issued'] . ' : ';
|
||
|
|
|
||
|
|
$issued_qty_total += $row_consume['total_issued'];
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
if ($a == 1) {
|
||
|
|
return $consume_detail;
|
||
|
|
} else {
|
||
|
|
return $issued_qty_total;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
function getBalance($item_id, $rec_date1, $rec_date2, $ohc)
|
||
|
|
{
|
||
|
|
$sql_existing_today_balance = "select stock_qty from item_stock_dispensary_daily_balance where item_id = '$item_id' and record_date between '$rec_date1' and '$rec_date2' and ohc_location_id = '$ohc'";
|
||
|
|
|
||
|
|
error_log("getBalance:" . $sql_existing_today_balance);
|
||
|
|
|
||
|
|
$result_balance = mysqli_query($GLOBALS['conn'],$sql_existing_today_balance);
|
||
|
|
$row_balance = mysqli_fetch_array($result_balance);
|
||
|
|
|
||
|
|
|
||
|
|
return $row_balance['stock_qty'];
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
function balance($total, $total_consumed, $id, $d)
|
||
|
|
{
|
||
|
|
|
||
|
|
|
||
|
|
$b = $total - $total_consumed;
|
||
|
|
|
||
|
|
|
||
|
|
return $b;
|
||
|
|
}
|
||
|
|
|
||
|
|
while ($row_m = mysqli_fetch_array($result)) {
|
||
|
|
|
||
|
|
array_push($med_cat, $row_m['cat_id']);
|
||
|
|
}
|
||
|
|
|
||
|
|
foreach ($med_cat as $i => $value) {
|
||
|
|
|
||
|
|
$newCat = true;
|
||
|
|
|
||
|
|
$query = "select i.item_id, i.item_name,rsi1.received_sum from tbl_items i left join (select item_id, sum(received_qty) received_sum from received_issue_items rsi where rsi.ohc_location_id='$ohc' and received_id in (select received_id from received_master where received_date between '$from_date1' and '$from_date2' ) group by item_id) rsi1 on i.item_id=rsi1.item_id where i.cat='$value' and i.status = '1' order by item_form_id";
|
||
|
|
|
||
|
|
error_log("query:" . $query);
|
||
|
|
|
||
|
|
$result = mysqli_query($conn,$query);
|
||
|
|
|
||
|
|
$total = mysqli_num_rows($result);
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
if ($total != 0) { ?>
|
||
|
|
<!--
|
||
|
|
<tr rowspan="2">
|
||
|
|
<th colspan='8'>
|
||
|
|
<center><?= getTableFieldValue("tbl_categories", "cat_name", "cat_id", $value) ?></center>
|
||
|
|
</th>
|
||
|
|
</tr>
|
||
|
|
<tr></tr> -->
|
||
|
|
|
||
|
|
<?
|
||
|
|
|
||
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
||
|
|
|
||
|
|
$total_qty = getBalance($row['item_id'], $from_date1, $from_date2, $ohc) + $row['received_sum'];
|
||
|
|
$total_consumed = consume_details($row['item_id'], 0, $from_date1, $from_date2);
|
||
|
|
if ($total_consumed == '0' && $ZeroAllowed == "N") {
|
||
|
|
} else {
|
||
|
|
|
||
|
|
|
||
|
|
?>
|
||
|
|
<?php if ($newCat) {
|
||
|
|
$newCat = false; ?>
|
||
|
|
<tr rowspan="2">
|
||
|
|
<th colspan='8'>
|
||
|
|
<center><?= getTableFieldValue("tbl_categories", "cat_name", "cat_id", $value) ?></center>
|
||
|
|
</th>
|
||
|
|
</tr>
|
||
|
|
<?php } ?>
|
||
|
|
<tr>
|
||
|
|
<td align="left"><?= ++$count ?></td>
|
||
|
|
<td align="left"><?= getItemWithFormName($row['item_id']) ?></td>
|
||
|
|
<? if ($row['received_sum'] == 0 || $row['received_sum'] == '') { ?>
|
||
|
|
<td align="left">0.00</td>
|
||
|
|
<? } else { ?>
|
||
|
|
<td align="left"><?= $row['received_sum'] ?></td>
|
||
|
|
<? } ?>
|
||
|
|
|
||
|
|
<td align="left"><?= getBalance($row['item_id'], $from_date1, $from_date2, $ohc) ?></td>
|
||
|
|
|
||
|
|
|
||
|
|
<td align="left"><?= ceil($total_qty) ?></td>
|
||
|
|
|
||
|
|
<td align="left" width="50%"><?= consume_details($row['item_id'], 1, $from_date1, $from_date2) ?></td>
|
||
|
|
<td align="left"><?= consume_details($row['item_id'], 0, $from_date1, $from_date2) ?></td>
|
||
|
|
<? if ($row['received_sum'] == 0 || $row['received_sum'] == '') { ?>
|
||
|
|
<td align="left"><?= ceil(balance(getBalance($row['item_id'], $from_date1, $from_date2, $ohc), consume_details($row['item_id'], 0, $from_date1, $from_date2))) ?></td>
|
||
|
|
<? } else { ?>
|
||
|
|
<td align="left"><?= ceil(balance($total_qty, consume_details($row['item_id'], 0, $from_date1, $from_date2))) ?></td>
|
||
|
|
<? } ?>
|
||
|
|
</tr>
|
||
|
|
|
||
|
|
<?php }
|
||
|
|
}
|
||
|
|
}
|
||
|
|
} ?>
|
||
|
|
</table>
|
||
|
|
</body>
|