134 lines
4.6 KiB
PHP
134 lines
4.6 KiB
PHP
|
|
<?php
|
|
header("Content-type:application/octet-stream");
|
|
|
|
header("Content-type: application/vnd.ms-excel");
|
|
header("Content-Disposition: attachment; filename=monthly_consumption_item_excel.xls");
|
|
header("Pragma: no-cache");
|
|
header("Expires: 0");
|
|
//include('pdf_header_reverse.php');
|
|
|
|
?>
|
|
<?php
|
|
include('includes/config/config.php');
|
|
include('includes/functions.php');
|
|
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
|
|
//include('pop_up_top.php');
|
|
?>
|
|
|
|
<body>
|
|
<table border="1">
|
|
<tr>
|
|
<?php
|
|
|
|
|
|
/* $year= date("Y");
|
|
$year = substr( $year, -2);*/
|
|
$startMonth1=$_REQUEST['startDate1'];
|
|
$endMonth1=$_REQUEST['endDate1'];
|
|
$startMonth1Array=explode("-",$startMonth1);
|
|
$endMonth1Array=explode("-",$endMonth1);
|
|
$startYear=$startMonth1Array[1];
|
|
$endYear= $endMonth1Array[1];
|
|
$year = substr( $startYear, -2);
|
|
?>
|
|
<th>Sr</th><th>Active ingredients</th><th>Brand Name</th><th>Expiry Date</th><th>Basic rate</th>
|
|
<th >Available in Stock</th>
|
|
<?php
|
|
$startMonth=intval($startMonth1Array[0]);
|
|
$endMonth=intval($endMonth1Array[0]);
|
|
while($startMonth<=$endMonth){
|
|
$monthName = date('F', mktime(0, 0, 0, $startMonth, 10));
|
|
$monthName= substr( $monthName, 0,3);
|
|
?><th>Recieved <?php echo $monthName?>-<?php echo $year?></th><th>Utilized <?php echo $monthName?>-<?php echo $year?></th><th>Bal @end <?php echo $monthName?>-<?php echo $year?></th> <?php
|
|
|
|
|
|
$startMonth=$startMonth+1;
|
|
}
|
|
|
|
?>
|
|
|
|
|
|
|
|
|
|
<?php
|
|
//$sql_stock="select distinct(a.item_id) from appointment_stock_history a inner join employee_appointment b on a.appointment_id=b.appointment_id where YEAR(appointment_date)='2019' ";
|
|
$sql_stock="select * from tbl_items ";
|
|
$results_stock=mysqli_query($conn,$sql_stock);
|
|
|
|
while($row_stock=mysqli_fetch_array($results_stock)){
|
|
//$issue_date=$row_stock[];
|
|
//$cat_id = getTableFieldValue('tbl_items','cat','item_id',$row_stock['item_id']);
|
|
$cat_id =$row_stock['cat'];
|
|
$current_stock_level_qty=$row_stock['current_stock_level'];
|
|
//$current_rate = getTableFieldValue('tbl_items','current_rate','item_id',$row_stock['item_id']);
|
|
$current_rate=$row_stock['current_rate'];
|
|
$form_name = getTableFieldValue('medicine_form','form_name','form_id',$cat_id);
|
|
// $item_desc = getTableFieldValue('tbl_items','item_name','item_id',$row_stock['item_id']);
|
|
$item_desc=$row_stock['item_name'];
|
|
$expiry_date=getTableFieldValue('procurement_items','max(expiry)','item_id',$row_stock['item_id']);
|
|
// $issue_qty=getTableFieldValue('appointment_stock_history','issued_qty','item_id',$row_stock['item_id']);
|
|
|
|
$date= date('Y-m-d');
|
|
?><tr><td><?php echo ++$count?></td><td></td><td><?php echo ($form_name.". ".$item_desc);?></td><td><?php
|
|
if($expiry_date<=$date)
|
|
{
|
|
?><font color="red"><?php echo $expiry_date;?></font><?php
|
|
}
|
|
else{
|
|
echo $expiry_date;
|
|
}
|
|
|
|
?></td><td><?php echo $current_rate?></td><td><?php echo $current_stock_level_qty?></td>
|
|
<?php
|
|
$startMonth=intval($startMonth1Array[0]);
|
|
$endMonth=intval($endMonth1Array[0]);
|
|
|
|
while($startMonth<=$endMonth){
|
|
//$monthName = date('F', mktime(0, 0, 0, $i, 10));
|
|
$sql_qty_sum="select sum(a.issued_qty) as total_sum from appointment_stock_history a where (a.item_id)='".$row_stock['item_id']."' and a.appointment_id in (select appointment_id from employee_appointment where MONTH(appointment_date)='".$startMonth."' AND YEAR(appointment_date)='".$startYear."' ) ";
|
|
|
|
$results_qty_sum=mysqli_query($conn,$sql_qty_sum);
|
|
$row_qty_sum=mysqli_fetch_array($results_qty_sum);
|
|
$sql_recieved_sum="select sum(qty) as total_sum from procurement_items where item_id='".$row_stock['item_id']."' and procurement_id in (select procurement_id from procurement where MONTH(procurement_date)='".$startMonth."' AND YEAR(procurement_date)='".$startYear."' ) ";
|
|
$results_recieved_sum=mysqli_query($conn,$sql_recieved_sum);
|
|
$row_recieved_sum=mysqli_fetch_array($results_recieved_sum);
|
|
$total_sum= $current_stock_level_qty+$row_recieved_sum['total_sum'];
|
|
$balance_qty=$total_sum-$row_qty_sum['total_sum'];
|
|
$current_stock_level_qty=$balance_qty;
|
|
|
|
//echo $sql_qty_sum;
|
|
?><td><?php
|
|
if($row_recieved_sum['total_sum']==null || $row_recieved_sum['total_sum']=='')
|
|
echo 0;
|
|
else
|
|
echo $row_recieved_sum['total_sum'];
|
|
|
|
?></td><td><?php
|
|
if($row_qty_sum['total_sum']==null || $row_qty_sum['total_sum']=='')
|
|
echo 0;
|
|
else
|
|
echo $row_qty_sum['total_sum'];
|
|
|
|
?></td><td><?php
|
|
if($balance_qty==null || $balance_qty=='')
|
|
echo 0;
|
|
else
|
|
echo $balance_qty;
|
|
|
|
|
|
?></td><?php
|
|
$startMonth=$startMonth+1;
|
|
}
|
|
|
|
?>
|
|
|
|
|
|
</tr><?php
|
|
}
|
|
|
|
?>
|
|
|
|
|
|
</table>
|
|
<?php //include('pdf_footer.php');?>
|