Combine Stock OHC store And Dispensary Report
For Date: ,	Print

=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) { ?>

Sr	Items	Expiry date	Previous Balance	New received	Total Store Balance	Store Issued	Expired Qty	Dispensary Balance	Remaining Balance