293 lines
10 KiB
PHP
293 lines
10 KiB
PHP
|
<?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=monthly_store_stock_report_excel.xls");
|
||
|
|
header("Pragma: no-cache");
|
||
|
|
header("Expires: 0");
|
||
|
|
|
||
|
|
?>
|
||
|
|
|
||
|
|
|
||
|
|
<?php
|
||
|
|
$startDatestock = $_REQUEST['startDatestock'];
|
||
|
|
|
||
|
|
$endDatestock = $_REQUEST['endDatestock'];
|
||
|
|
$ohc = $_SESSION['current_ohcttype'];
|
||
|
|
//echo $from_date;
|
||
|
|
|
||
|
|
?>
|
||
|
|
<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" colspan='2' style="font-size: 15px"><strong>Combine Stock OHC store And Dispensary Report</strong></td>
|
||
|
|
</tr>
|
||
|
|
<tr>
|
||
|
|
<td align="left" style="font-size: 12px"><strong>For Date: <?php echo $startDatestock; ?>,<?php echo $endDatestock; ?></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="2%" align="center">Sr</th>
|
||
|
|
<th align="center" width="5%">Items</th>
|
||
|
|
<th align="center" width="8%">Expiry date</th>
|
||
|
|
<th align="center" width="5%">Previous Balance</th>
|
||
|
|
<th width="5%" align="center">New received</th>
|
||
|
|
<th align="center" width="5%">Total Store Balance</th>
|
||
|
|
<th align="center" width="5%">Store Issued</th>
|
||
|
|
<th align="center" width="5%">Expired Qty</th>
|
||
|
|
<th align="center" width="5%">Dispensary Balance</th>
|
||
|
|
<th align="center" width="5%">Remaining Balance</th>
|
||
|
|
|
||
|
|
|
||
|
|
</tr>
|
||
|
|
<?php
|
||
|
|
|
||
|
|
//echo $to_date;
|
||
|
|
|
||
|
|
|
||
|
|
// $emp_id=$_POST['empName'];
|
||
|
|
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 getDisBalance($item_id, $startDatestock, $endDatestock)
|
||
|
|
{
|
||
|
|
$PreviousendDate = date('Y-m-d', strtotime($startDatestock . ' - 1 day'));
|
||
|
|
|
||
|
|
$dis_balance_sql = "select stock_qty from item_stock_dispensary_daily_balance where stock_qty>=0 and item_id='" . $item_id . "' and date_format(date(record_date),'%Y-%m-%d') = STR_TO_DATE('" . $PreviousendDate . "', '%Y-%m-%d') ";
|
||
|
|
|
||
|
|
error_log('$dis_balance_sql:' . $dis_balance_sql);
|
||
|
|
|
||
|
|
$result_dis_balance = mysqli_query($GLOBALS['conn'], $dis_balance_sql);
|
||
|
|
$disp_balance = "";
|
||
|
|
|
||
|
|
while ($row_disp_balance = mysqli_fetch_assoc($result_dis_balance)) {
|
||
|
|
|
||
|
|
$disp_balance += $row_disp_balance['stock_qty'];
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($disp_balance == 0) {
|
||
|
|
return 0;
|
||
|
|
} else {
|
||
|
|
return $disp_balance;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
function getExpiry($item_id)
|
||
|
|
{
|
||
|
|
$sql_expiry = "SELECT DISTINCT(expiry_date) as expiry,item_batch_no FROM item_stock WHERE item_id='" . $item_id . "' AND stock_qty>0 ";
|
||
|
|
$result_expiry = mysqli_query($GLOBALS['conn'], $sql_expiry);
|
||
|
|
$expiry_with_batch = "";
|
||
|
|
while ($row_expiry = mysqli_fetch_assoc($result_expiry)) {
|
||
|
|
$dateStrVal = strtotime($row_expiry['expiry']);
|
||
|
|
if (empty($dateStrVal)) {
|
||
|
|
$expired_date = 'NA';
|
||
|
|
} else {
|
||
|
|
$expired_date = date_format(date_create($row_expiry['expiry']), "M-Y ");
|
||
|
|
}
|
||
|
|
$expiry_with_batch .= $expired_date . " | ";
|
||
|
|
}
|
||
|
|
return $expiry_with_batch;
|
||
|
|
}
|
||
|
|
|
||
|
|
function expired_item_qty($item_id, $startDatestock, $endDatestock)
|
||
|
|
{
|
||
|
|
|
||
|
|
$expired_item_sql = "select * from cleanup_item_stock where item_id = '" . $item_id . "' and date_format(date(cleanup_date),'%Y-%m-%d') between STR_TO_DATE('" . $startDatestock . "', '%d-%m-%Y') AND STR_TO_DATE('" . $endDatestock . "', '%d-%m-%Y') ";
|
||
|
|
|
||
|
|
$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_balance($item_id, $startDatestock, $endDatestock)
|
||
|
|
{
|
||
|
|
$PreviousDate = date('Y-m-d', strtotime($startDatestock . ' - 1 day'));
|
||
|
|
|
||
|
|
$prev_balance_sql = "select stock_qty from item_stock_daily_balance where stock_qty>=0 and item_id='" . $item_id . "' and date_format(date(record_date),'%Y-%m-%d') = STR_TO_DATE('" . $PreviousDate . "', '%Y-%m-%d') ";
|
||
|
|
|
||
|
|
error_log('$prev_balance_sql:' . $prev_balance_sql);
|
||
|
|
|
||
|
|
$result_prev_balance = mysqli_query($GLOBALS['conn'], $prev_balance_sql);
|
||
|
|
$previous_balance = "";
|
||
|
|
|
||
|
|
while ($row_prev_balance = mysqli_fetch_assoc($result_prev_balance)) {
|
||
|
|
|
||
|
|
$previous_balance += $row_prev_balance['stock_qty'];
|
||
|
|
}
|
||
|
|
|
||
|
|
if ($previous_balance == 0) {
|
||
|
|
return 0;
|
||
|
|
} else {
|
||
|
|
return $previous_balance;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
function procurement_details($item_id, $startDatestock, $endDatestock)
|
||
|
|
{
|
||
|
|
$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 date_format(procurement_date,'%Y-%m-%d') between STR_TO_DATE('" . $startDatestock . "', '%d-%m-%Y') AND STR_TO_DATE('" . $endDatestock . "', '%d-%m-%Y')";
|
||
|
|
|
||
|
|
|
||
|
|
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 date_format(received_date),'%Y-%m-%d') between STR_TO_DATE('" . $startDatestock . "', '%d-%m-%Y') AND STR_TO_DATE('" . $endDatestock . "', '%d-%m-%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, $startDatestock, $endDatestock)
|
||
|
|
{
|
||
|
|
$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 date_format(date(issue_date),'%Y-%m-%d') between STR_TO_DATE('" . $startDatestock . "', '%d-%m-%Y') AND STR_TO_DATE('" . $endDatestock . "', '%d-%m-%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, $expired_qty)
|
||
|
|
{
|
||
|
|
$balance = ceil($total - $issue_qty - $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) {
|
||
|
|
|
||
|
|
|
||
|
|
$query = "select i.item_id, i.item_name, isd1.sum_total,isd1.expiry_date from tbl_items i left join (select isd.item_id,sum(isd.stock_qty) as sum_total,isd.expiry_date from item_stock isd group by item_id) isd1 on isd1.item_id=i.item_id where i.cat='$value' and i.status= '1'";
|
||
|
|
|
||
|
|
error_log("###########QQQQQQQQ" . $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>
|
||
|
|
|
||
|
|
<?php
|
||
|
|
|
||
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
||
|
|
|
||
|
|
|
||
|
|
$count++;
|
||
|
|
|
||
|
|
?>
|
||
|
|
<tr>
|
||
|
|
<td align="left"><?= $count ?></td>
|
||
|
|
<td align="left"><?= $row['item_name'] ?></td>
|
||
|
|
<td align="left"><?= getExpiry($row['item_id']) ?></td>
|
||
|
|
<td align="left"><?= previous_balance($row['item_id'], $startDatestock, $endDatestock) ?></td>
|
||
|
|
<td align="left"><?= procurement_details($row['item_id'], $startDatestock, $endDatestock) ?></td>
|
||
|
|
<td align="left">
|
||
|
|
<?= total(previous_balance($row['item_id'], $startDatestock, $endDatestock), procurement_details($row['item_id'], $startDatestock, $endDatestock)) ?>
|
||
|
|
</td>
|
||
|
|
<td align="left"><?= issued_details($row['item_id'], $startDatestock, $endDatestock) ?></td>
|
||
|
|
<td align="left"><?= expired_item_qty($row['item_id'], $startDatestock, $endDatestock) ?></td>
|
||
|
|
<td align="left"><?= getDisBalance($row['item_id'], $startDatestock, $endDatestock) ?></td>
|
||
|
|
<td align="left">
|
||
|
|
<?= balance(total(previous_balance($row['item_id'], $startDatestock, $endDatestock), procurement_details($row['item_id'], $startDatestock, $endDatestock)), issued_details($row['item_id'], $startDatestock, $endDatestock), expired_item_qty($row['item_id'], $startDatestock, $endDatestock)) + getDisBalance($row['item_id'], $startDatestock, $endDatestock) ?>
|
||
|
|
</td>
|
||
|
|
</tr>
|
||
|
|
|
||
|
|
<?php }
|
||
|
|
}
|
||
|
|
} ?>
|
||
|
|
</table>
|
||
|
|
</body>
|