Monthly Store Consumption Report
For Month: ,	Print

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 expiry_details($item_id, $m, $y,$ohc) { $expired_item_sql = "select * from cleanup_item_stock where item_id = '" . $item_id . "' and month(cleanup_date) = '$m' and year(expiry_date) = '$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, $m, $y) { if ($m == 3) { $prev_month_number = $m - 1; $prev_month = date("F", mktime(0, 0, 0, $prev_month_number, 10)); } else if ($m == 2) { $prev_month_number = $m - 1; $prev_month = date("F", mktime(0, 0, 0, $prev_month_number, 10)); } else if ($m == 1) { $prev_month_number = 12; $prev_month = date("F", mktime(0, 0, 0, $prev_month_number, 10)); } else { $monthname = date("F", mktime(0, 0, 0, $m, 10)); $prev_month_number = $m - 1; $prev_month = date("F", mktime(0, 0, 0, $prev_month_number, 10)); // $prev_month_number = date("m", strtotime($prev_month_number)); // $prev_month_number = date('m', strtotime($prev_month)); } error_log("month name=>" . $monthname . " prev month name=> " . $prev_month . " prev month number=>" . $prev_month_number); if ($prev_month_number == '12') { $Prev_year = $y - 1; } else { $Prev_year = $y; } error_log("previous month:" . $prev_month . " " . $prev_month_number); $Last_date = new DateTime($prev_month . "" . $y); $date = $Last_date->format('t'); error_log("last date:" . $date); $prev_balance_sql = "select stock_qty from item_stock_daily_balance where stock_qty>=0 and item_id='" . $item_id . "' and month(record_date) = '$prev_month_number' and year(record_date) = '$Prev_year' and day(record_date) = '$date'"; 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, $m, $y,$ohc) { $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 month(p.procurement_date ) = '$m' and year(p.procurement_date )='$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 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 consume_details($item_id, $m, $y,$ohc) { $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 cost_latest($item_id, $m, $y) { $sql_procurement = "select * from item_rate_change_history h left join item_rate r on h.item_id=r.item_id where h.id=(select max(id) from item_rate_change_history where item_id = '$item_id' and month(last_modified ) = '$m' and year(last_modified )='$y') "; // error_log("exp_cost:".$item_id . $sql_procurement); $result_procurement = mysqli_query($GLOBALS['conn'],$sql_procurement); $procurement_qty_total = 0; while ($row_procurement = mysqli_fetch_assoc($result_procurement)) { $procurement_qty_total += ($row_procurement['new_rate']/$row_procurement['unit']); } if($procurement_qty_total==0 || $procurement_qty_total=='' || $procurement_qty_total==null){ $sql_procurement_rate = "select * from item_rate where item_id = '$item_id' "; // error_log("exp_cost:".$item_id . $sql_procurement); $result_procurement_rate = mysqli_query($GLOBALS['conn'],$sql_procurement_rate); while ($row_procurement_rate = mysqli_fetch_assoc($result_procurement_rate)) { $procurement_qty_total += ($row_procurement_rate['item_rate']/$row_procurement_rate['unit']); } } return $procurement_qty_total; } function cost_old($item_id, $m, $y) { $sql_procurement = "select * from item_rate_change_history h left join item_rate r on h.item_id=r.item_id where h.id=(select max(id) from item_rate_change_history where item_id = '$item_id' and month(last_modified ) = '$m' and year(last_modified )='$y') "; // error_log("exp_cost:".$item_id . $sql_procurement); $result_procurement = mysqli_query($GLOBALS['conn'],$sql_procurement); $procurement_qty_total = 0; while ($row_procurement = mysqli_fetch_assoc($result_procurement)) { $procurement_qty_total += ($row_procurement['old_rate']/$row_procurement['unit']); } if($procurement_qty_total==0 || $procurement_qty_total=='' || $procurement_qty_total==null){ $sql_procurement_rate = "select * from item_rate where item_id = '$item_id' "; // error_log("exp_cost:".$item_id . $sql_procurement); $result_procurement_rate = mysqli_query($GLOBALS['conn'],$sql_procurement_rate); while ($row_procurement_rate = mysqli_fetch_assoc($result_procurement_rate)) { $procurement_qty_total += ($row_procurement_rate['item_rate']/$row_procurement_rate['unit']); } } return $procurement_qty_total; } function balance($total_balance, $total_consumed) { $b = $total_balance - $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, 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) { ?>

Sr	Items	Items Rate Per Unit	Previous Balance Qty	Previous Balance Amount	Procured Qty	Procured Amount	Total Qty	Total Amount	Consumption Qty	Consumption Amount	Expired Qty	Expired Amount	Balance Qty	Balance Amount
Grand Total Amount