394 lines
14 KiB
PHP
394 lines
14 KiB
PHP
<?
|
|
//include ('pdf_header_reverse.php');
|
|
include('includes/config/config.php');
|
|
include('includes/functions.php');
|
|
error_reporting(0);
|
|
?>
|
|
<?php
|
|
|
|
$year = $_POST['years'];
|
|
$year2 = $year + 1;
|
|
error_log("year:" . $year . " " . $year2);
|
|
$ohc = $_SESSION['current_ohcttype'];
|
|
|
|
//echo $hygiene_abnormal1;
|
|
// $to_date = $_POST['endDate'];
|
|
// $to_date = date('Y-m-d', strtotime($to_date));
|
|
|
|
$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);
|
|
?>
|
|
<style type="text/css">
|
|
body,
|
|
div,
|
|
table,
|
|
thead,
|
|
tbody,
|
|
tfoot,
|
|
tr,
|
|
th,
|
|
td,
|
|
p {
|
|
font-family: "Calibri";
|
|
font-size: x-small
|
|
}
|
|
|
|
a.comment-indicator:hover+comment {
|
|
background: #ffd;
|
|
position: absolute;
|
|
display: block;
|
|
border: 1px solid black;
|
|
padding: 0.5em;
|
|
}
|
|
|
|
a.comment-indicator {
|
|
background: red;
|
|
display: inline-block;
|
|
border: 1px solid black;
|
|
width: 0.5em;
|
|
height: 0.5em;
|
|
}
|
|
|
|
comment {
|
|
display: none;
|
|
}
|
|
|
|
.btn {
|
|
cursor: pointer;
|
|
}
|
|
</style>
|
|
|
|
<body>
|
|
<?php include('pdf_ohc_header.php'); ?>
|
|
|
|
<table width="100%">
|
|
<tr>
|
|
|
|
<td align="center" style="font-size: 15px"><strong>Yearly Medicine Expense Report</strong></td>
|
|
|
|
</tr>
|
|
<tr>
|
|
<td align="left" style="font-size: 12px"><strong>For Year : <?= $year ?>-<?= $year2 ?></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">
|
|
<colgroup width="36"></colgroup>
|
|
<colgroup width="142"></colgroup>
|
|
<colgroup width="66"></colgroup>
|
|
<colgroup width="43"></colgroup>
|
|
<colgroup width="44"></colgroup>
|
|
<colgroup span="5" width="39"></colgroup>
|
|
<colgroup width="47"></colgroup>
|
|
<colgroup span="4" width="39"></colgroup>
|
|
<colgroup width="66"></colgroup>
|
|
<colgroup span="7" width="39"></colgroup>
|
|
<colgroup width="46"></colgroup>
|
|
<colgroup width="42"></colgroup>
|
|
<colgroup width="44"></colgroup>
|
|
<colgroup span="2" width="39"></colgroup>
|
|
<colgroup width="62"></colgroup>
|
|
<colgroup span="2" width="64"></colgroup>
|
|
<tr>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" colspan=16 height="48" align="center" valign=middle bgcolor="#DEEBF7"><b>
|
|
<font size=4 color="#000000">MONTHLY WISE MEDICINE RECEIVED BY OHC
|
|
</font>
|
|
</b></td>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" colspan=15 align="center" valign=middle bgcolor="#BDD7EE"><b>
|
|
<font size=4 color="#000000">MONTHLY WISE EXPENCES OF MEDICINE </font>
|
|
</b></td>
|
|
<td align="left" valign=bottom>
|
|
<font color="#000000"><br></font>
|
|
</td>
|
|
</tr>
|
|
<tr>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" colspan=16 height="34" align="center" valign=middle bgcolor="#DEEBF7"><b>
|
|
<font size=4 color="#000000"> RECEIVED</font>
|
|
</b></td>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" colspan=15 align="center" valign=middle bgcolor="#BDD7EE"><b>
|
|
<font size=4 color="#000000"> ISSUED</font>
|
|
</b></td>
|
|
<td align="left" valign=bottom>
|
|
<font color="#000000"><br></font>
|
|
</td>
|
|
</tr>
|
|
<tr>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" colspan=16 height="30" align="center" valign=middle bgcolor="#DEEBF7"><b>
|
|
<font size=4>YEAR: <?= $year ?>-<?= $year2 ?></font>
|
|
</b></td>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" colspan=15 align="center" valign=middle bgcolor="#BDD7EE"><b>
|
|
<font size=4 color="#000000">YEAR: <?= $year ?>-<?= $year2 ?></font>
|
|
</b></td>
|
|
<td align="left" valign=bottom>
|
|
<font color="#000000"><br></font>
|
|
</td>
|
|
</tr>
|
|
<tr>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" height="50" align="center" valign=middle bgcolor="#92D050"><b>SL.NO</b></td>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center" valign=middle bgcolor="#92D050"><b>ITEMS</b></td>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center" valign=middle bgcolor="#92D050"><b>PREVIOUS STOCK</b></td>
|
|
<?php for ($i = 4; $i <= 12; $i++) {
|
|
$month_name = date("F", mktime(0, 0, 0, $i, 10)); ?>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center" valign=middle bgcolor="#92D050"><b><?= $month_name ?></b></td>
|
|
<? } ?>
|
|
|
|
<?php for ($i = 1; $i <= 3; $i++) {
|
|
$month_name = date("F", mktime(0, 0, 0, $i, 10)); ?>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center" valign=middle bgcolor="#92D050"><b><?= $month_name ?></b></td>
|
|
<? } ?>
|
|
<td style="border-top: 1px solid #000000; border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center" valign=middle bgcolor="#FFFF00"><b>
|
|
<font size=4>Total<br>Rec.</font>
|
|
</b></td>
|
|
<?php for ($i = 4; $i <= 12; $i++) {
|
|
$month_name = date("F", mktime(0, 0, 0, $i, 10)); ?>
|
|
|
|
<td style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center" valign=middle bgcolor="#F4B183"><b><?= $month_name ?></b></td>
|
|
<? } ?>
|
|
|
|
<?php for ($i = 1; $i <= 3; $i++) {
|
|
$month_name = date("F", mktime(0, 0, 0, $i, 10)); ?>
|
|
|
|
<td style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center" valign=middle bgcolor="#F4B183"><b><?= $month_name ?></b></td>
|
|
<? } ?>
|
|
<td style="border-bottom: 1px solid #000000; border-left: 1px solid #000000; border-right: 1px solid #000000" align="center" valign=middle bgcolor="#C55A11"><b>
|
|
<font size=3>Total<br>Issue</font>
|
|
</b></td>
|
|
<td align="center" valign=middle bgcolor="red"><b>
|
|
<font size=4 color="#000000">Expired<br>Stock</font>
|
|
</b></td>
|
|
<td align="center" valign=middle bgcolor="#00B050"><b>
|
|
<font size=4 color="#000000">Current<br>Stock</font>
|
|
</b></td>
|
|
<td align="left" valign=bottom>
|
|
<font color="#000000"><br></font>
|
|
</td>
|
|
</tr>
|
|
<?
|
|
|
|
|
|
error_reporting(E_ERROR | E_PARSE);
|
|
$count = 0;
|
|
//echo $emp_id;
|
|
$data = "";
|
|
$sql = "select cat_id from tbl_categories order by sequence asc";
|
|
|
|
error_log("SSSSSSS$$$$$$$$$" . $sql);
|
|
|
|
$result = mysqli_query($conn,$sql);
|
|
|
|
$med_cat = array();
|
|
|
|
function expired_item_qty($item_id, $y, $m)
|
|
{
|
|
|
|
$expired_item_sql = "select * from cleanup_item_stock where item_id = '" . $item_id . "' and year(cleanup_date) = '$y' and month(cleanup_date) = '$m' ";
|
|
|
|
$result_expired_item_sql = mysqli_query($GLOBALS['conn'],$expired_item_sql);
|
|
|
|
$expired_qty = "";
|
|
while ($row_expired_item_sql = mysqli_fetch_assoc($result_expired_item_sql)) {
|
|
$expired_qty += $row_expired_item_sql['cleanup_qty'];
|
|
}
|
|
|
|
if ($expired_qty == 0) {
|
|
return 0;
|
|
} else {
|
|
return $expired_qty;
|
|
}
|
|
}
|
|
|
|
function previous_year_balance($item_id, $y)
|
|
{
|
|
$prev_year_balance_sql = "select stock_qty from item_stock_daily_balance where item_id='" . $item_id . "' and month(record_date) = '3' and year(record_date) = '$y' and day(record_date) = '31' ";
|
|
|
|
error_log('prev_year_balance_sql:' . $prev_year_balance_sql);
|
|
|
|
$result_prev_year_balance = mysqli_query($GLOBALS['conn'],$prev_year_balance_sql);
|
|
$previous_balance = "";
|
|
|
|
while ($row_prev_balance = mysqli_fetch_assoc($result_prev_year_balance)) {
|
|
|
|
$previous_balance += $row_prev_balance['stock_qty'];
|
|
}
|
|
|
|
if ($previous_balance == 0) {
|
|
return 0;
|
|
} else {
|
|
return $previous_balance;
|
|
}
|
|
}
|
|
|
|
function procurement_details($item_id, $y, $m)
|
|
{
|
|
$procurement_qty_total = 0;
|
|
$sql_procurement = "select pt.qty from procurement_items pt right join procurement p on pt.procurement_id = p.procurement_id where pt.item_id = '$item_id' and year(p.procurement_date )='$y' and month(p.procurement_date )='$m' ";
|
|
|
|
error_log("procurement:" . $sql_procurement);
|
|
|
|
$result_procurement = mysqli_query($GLOBALS['conn'],$sql_procurement);
|
|
|
|
while ($row_procurement = mysqli_fetch_assoc($result_procurement)) {
|
|
|
|
$procurement_qty_total += $row_procurement['qty'];
|
|
}
|
|
|
|
$sql_direct_in = "select pt.received_qty from store_received_return_items pt right join store_received_return_master p on pt.received_id = p.received_id where pt.item_id = '$item_id' and month(p.received_date ) = '$m' and year(p.received_date )='$y' ";
|
|
|
|
|
|
// error_log("procurement:" . $sql_procurement);
|
|
|
|
$result_direct_in = mysqli_query($GLOBALS['conn'],$sql_direct_in);
|
|
|
|
while ($row_direct_in = mysqli_fetch_assoc($result_direct_in)) {
|
|
|
|
$procurement_qty_total += $row_direct_in['received_qty'];
|
|
}
|
|
|
|
return $procurement_qty_total;
|
|
}
|
|
|
|
function total($prev_balance, $new_received)
|
|
{
|
|
$total = $prev_balance + $new_received;
|
|
|
|
return $total;
|
|
}
|
|
|
|
function issued_details($item_id, $m, $y)
|
|
{
|
|
$sql_consume = "select st.issue_qty from stock_issue_items st left join stock_issue s on st.stock_issue_id = s.stock_issue_id where item_id = '$item_id' and month(s.issue_date ) = '$m' and year(s.issue_date )='$y' ";
|
|
|
|
error_log("issued_stock" . $sql_consume);
|
|
|
|
$result_consume = mysqli_query($GLOBALS['conn'],$sql_consume);
|
|
|
|
$issued_qty_total = 0;
|
|
|
|
while ($row_consume = mysqli_fetch_array($result_consume)) {
|
|
$issued_qty_total += $row_consume['issue_qty'];
|
|
}
|
|
return $issued_qty_total;
|
|
}
|
|
|
|
function balance($total, $issue_qty_total, $expired_qty)
|
|
{
|
|
$balance = ceil($total - $issue_qty_total - $expired_qty);
|
|
|
|
return $balance;
|
|
}
|
|
|
|
|
|
|
|
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 tbl_items.item_id, tbl_items.item_name, item_stock.stock_qty from tbl_items left join (select item_stock.item_id,sum(item_stock.stock_qty) as stock_qty from item_stock group by item_id) item_stock on item_stock.item_id=tbl_items.item_id where tbl_items.cat='$value' and tbl_items.status= '1' ";
|
|
|
|
error_log("###########QQQQQQQQ" . $query);
|
|
|
|
$result = mysqli_query($conn,$query);
|
|
|
|
$total = mysqli_num_rows($result);
|
|
|
|
if ($total != 0) { ?>
|
|
|
|
<!-- <tr rowspan="2">
|
|
<th colspan='31' bgcolor="black;">
|
|
<center><?= getTableFieldValue("tbl_categories", "cat_name", "cat_id", $value) ?></center>
|
|
</th>
|
|
</tr> -->
|
|
<?
|
|
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$total_procurement = 0.00;
|
|
$total_expired_item_qty = 0.00;
|
|
$issued_total_per_year = 0.00;
|
|
|
|
for ($j = 4; $j <= 12; $j++) {
|
|
$total_procurement += procurement_details($row['item_id'], $year, $j);
|
|
$total_expired_item_qty += expired_item_qty($row['item_id'], $year, $j);
|
|
$issued_total_per_year += issued_details($row['item_id'], $j, $year);
|
|
}
|
|
|
|
|
|
for ($j = 1; $j <= 3; $j++) {
|
|
$total_procurement += procurement_details($row['item_id'], $year2, $j);
|
|
$total_expired_item_qty += expired_item_qty($row['item_id'], $year2, $j);
|
|
$issued_total_per_year += issued_details($row['item_id'], $j, $year2);
|
|
}
|
|
|
|
if ($total_procurement == 0.00 && $total_expired_item_qty == 0.00 && $issued_total_per_year == 0.00 && $ZeroAllowed == "N") {
|
|
} else {
|
|
?>
|
|
<?php if ($newCat) {
|
|
$newCat = false; ?>
|
|
<tr rowspan="2">
|
|
<th colspan='31' bgcolor="black;">
|
|
<center><?= getTableFieldValue("tbl_categories", "cat_name", "cat_id", $value) ?></center>
|
|
</th>
|
|
</tr>
|
|
<?php } ?>
|
|
<tr>
|
|
<td align="left"><?= ++$count ?></td>
|
|
<td align="left"><?= $row['item_name'] ?></td>
|
|
|
|
<td align="left"><?= previous_year_balance($row['item_id'], $year) ?></td>
|
|
|
|
|
|
<!-- <td align="left"><?= $total_procurement ?></td> -->
|
|
<? for ($j = 4; $j <= 12; $j++) { ?>
|
|
<td align="left"><?= ceil(procurement_details($row['item_id'], $year, $j)) ?></td>
|
|
<? } ?>
|
|
|
|
<? for ($j = 1; $j <= 3; $j++) { ?>
|
|
<td align="left"><?= ceil(procurement_details($row['item_id'], $year2, $j)) ?></td>
|
|
<? } ?>
|
|
|
|
|
|
<td align="left" bgcolor="#FFFF00"> <?= total(previous_year_balance($row['item_id'], $year), $total_procurement) ?></td>
|
|
|
|
<? for ($j = 4; $j <= 12; $j++) { ?>
|
|
<td align="left"><?= ceil(issued_details($row['item_id'], $j, $year)) ?></td>
|
|
<? } ?>
|
|
|
|
<? for ($j = 1; $j <= 3; $j++) { ?>
|
|
<td align="left"><?= ceil(issued_details($row['item_id'], $j, $year2)) ?></td>
|
|
<? } ?>
|
|
|
|
|
|
<td align="left" bgcolor="#C55A11"><?= $issued_total_per_year ?></td>
|
|
<td align="left"> <?= $total_expired_item_qty ?></td>
|
|
|
|
<td align="left"> <?= ceil(balance(total(previous_year_balance($row['item_id'], $year), $total_procurement), $issued_total_per_year, $total_expired_item_qty)) ?></td>
|
|
</tr>
|
|
|
|
<?php }
|
|
}
|
|
}
|
|
} ?>
|
|
</table>
|
|
|
|
</body>
|
|
|
|
|
|
<script>
|
|
$(document).ready(function() {
|
|
window.print();
|
|
|
|
});
|
|
</script>
|