256 lines
11 KiB
PHP
256 lines
11 KiB
PHP
|
<?php
|
||
|
|
error_reporting(E_ERROR | E_PARSE);
|
||
|
|
include('includes/config/config.php');
|
||
|
|
include('includes/functions.php');
|
||
|
|
include("log_entry.php");
|
||
|
|
$noOfRows = $_POST['count_items'];
|
||
|
|
// echo $noOfRows;
|
||
|
|
$received_id = $_POST['received_id'];
|
||
|
|
// $procurement_refno = $_POST['ref_no'];
|
||
|
|
// echo $procurement_refno;
|
||
|
|
//$remarks = $_POST ['remarks'];
|
||
|
|
$received_date = $_POST['received_date'];
|
||
|
|
// echo $procurement_id;
|
||
|
|
begin();
|
||
|
|
$initquery = "";
|
||
|
|
$data = array();
|
||
|
|
$user_id = $_SESSION['user_id'];
|
||
|
|
$ohc_type = $_SESSION['current_ohcttype'];
|
||
|
|
|
||
|
|
$endquery = "";
|
||
|
|
$isuue_ref_no = "";
|
||
|
|
if (!empty($received_id)) {
|
||
|
|
// update case
|
||
|
|
$initquery = "update received_master set ";
|
||
|
|
$endquery = " where received_id = '" . $received_id . "' ";
|
||
|
|
$data['$received_id'] = $received_id;
|
||
|
|
} else {
|
||
|
|
// insert case
|
||
|
|
$month = date('n');
|
||
|
|
$year = date('Y');
|
||
|
|
|
||
|
|
if ($month > 3) {
|
||
|
|
$year = $year;
|
||
|
|
} else {
|
||
|
|
$year = $year - 1;
|
||
|
|
}
|
||
|
|
$ohc_type_code = getTableFieldValue('ohc_type', 'ohc_code', 'ohc_type_id', $ohc_type);
|
||
|
|
$query_received_ref_no = "select max( CAST(substring(received_ref_no,locate('-',received_ref_no)+1,length(received_ref_no)-(5+locate('-',received_ref_no))) as UNSIGNED) ) from received_master where received_ref_no like '%$year%' and ohc_location_id=$ohc_type ";
|
||
|
|
//echo $query_received_ref_no;
|
||
|
|
$result_received_ref_no = @mysqli_query($conn, $query_received_ref_no);
|
||
|
|
$row_received_ref_no = mysqli_fetch_row($result_received_ref_no);
|
||
|
|
$received_ref_no = 'REV' . $ohc_type_code . '-' . ($row_received_ref_no[0] + 1) . '-' . $year;
|
||
|
|
/*
|
||
|
|
* if(mysqli_num_rows($result_issue_ref_no) > 0){
|
||
|
|
* $row_isuue_ref_no = mysqli_fetch_row($result_issue_ref_no);
|
||
|
|
*
|
||
|
|
*
|
||
|
|
* $isuue_ref_no = 'ISS-'.($isuue_ref_no[0]+1).'-'.$year;
|
||
|
|
*
|
||
|
|
*
|
||
|
|
* }else{
|
||
|
|
* $isuue_ref_no = 'ISS-1-'.$year;
|
||
|
|
*
|
||
|
|
* }
|
||
|
|
*/
|
||
|
|
$initquery = "INSERT INTO received_master set received_ref_no='$received_ref_no' ,";
|
||
|
|
}
|
||
|
|
if ($_POST['req_id'] != null && $_POST['req_id'] != "") {
|
||
|
|
$query = $initquery . " remarks='" . $remarks . "',issue_id='" . $_POST['issue_id'] . "',req_id='" . $_POST['req_id'] . "',ohc_location_id='" . $_SESSION['current_ohcttype'] . "',received_date = now() ,modified_by = '" . $_SESSION['user_id'] . "' $endquery ";
|
||
|
|
} else {
|
||
|
|
$query = $initquery . " remarks='" . $remarks . "',issue_id='" . $_POST['issue_id'] . "',ohc_location_id='" . $_SESSION['current_ohcttype'] . "',received_date = now() ,modified_by = '" . $_SESSION['user_id'] . "' $endquery ";
|
||
|
|
}
|
||
|
|
// echo $query;
|
||
|
|
|
||
|
|
if (!$result = @mysqli_query($conn, $query)) {
|
||
|
|
error_log("Failure while saving dispensary stock in. Error:" . mysqli_error($conn) . " Failing Query:" . $query);
|
||
|
|
rollback();
|
||
|
|
exit(mysqli_error($conn));
|
||
|
|
} else {
|
||
|
|
if (!empty($received_ref_no)) {
|
||
|
|
|
||
|
|
// insert case when procuremnt id is not available but needed for saving into the child table
|
||
|
|
$query = "select received_id from received_master where received_ref_no= '$received_ref_no' ";
|
||
|
|
//echo $query;
|
||
|
|
if (!$result = @mysqli_query($conn, $query)) {
|
||
|
|
error_log("Failed to find received_id:" . mysqli_error($conn) . " Failing Query:" . $query);
|
||
|
|
|
||
|
|
//rollback();
|
||
|
|
//exit ( mysqli_error($conn) );
|
||
|
|
} else {
|
||
|
|
if (mysqli_num_rows($result) > 0) {
|
||
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
||
|
|
$data = $row;
|
||
|
|
$received_id = $row['received_id'];
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!empty($received_id)) {
|
||
|
|
|
||
|
|
/*
|
||
|
|
* $current_stock_level_procurement_items_query="select item_id,qty from requisition_items where req_id='".req_id."' ";
|
||
|
|
* $results_procuerment_items=mysqli_query($conn,$current_stock_level_procurement_items_query);
|
||
|
|
* echo $current_stock_level_procurement_items_query;
|
||
|
|
* while($rows_procurement_items=mysqli_fetch_assoc($results_procuerment_items)){
|
||
|
|
* $current_stock_level_items_qty= getTableFieldValue('tbl_items','current_stock_level','item_id',$rows_procurement_items['item_id']);
|
||
|
|
* $current_stock_level_items_qty=$current_stock_level_items_qty-$rows_procurement_items['qty'];
|
||
|
|
* echo current_stock_level_items_qty;
|
||
|
|
* $current_stock_query="update tbl_items set current_stock_level='".$current_stock_level_items_qty."' where item_id='".$rows_procurement_items['item_id']."' ";
|
||
|
|
* echo $current_stock_query;
|
||
|
|
* mysqli_query($conn,$current_stock_query);
|
||
|
|
*
|
||
|
|
* }
|
||
|
|
*/
|
||
|
|
}
|
||
|
|
resetDispensaryItemStockForItemInward($_SESSION['current_ohcttype'], $received_id);
|
||
|
|
// clear existing entries into the child table
|
||
|
|
$query_del = " delete from received_issue_items where received_id = '" . $received_id . "' ";
|
||
|
|
// echo $query_del;
|
||
|
|
$result_del = @mysqli_query($conn, $query_del);
|
||
|
|
error_log("Approve Dispensary In no of rows:" . $noOfRows);
|
||
|
|
for ($i = 0; $i < $noOfRows; $i++) {
|
||
|
|
$toCheckIsPackingItem = getTableFieldValue('tbl_items', 'is_packaging_item', 'item_id', ${"item_id$i"});
|
||
|
|
if ($toCheckIsPackingItem == 'C') {
|
||
|
|
$packing_item_value = 'Y';
|
||
|
|
updateDispensaryStockForItemInward($_SESSION['current_ohcttype'], ${"dispensary_item$i"}, ${"dispensary_item_qty$i"});
|
||
|
|
} else {
|
||
|
|
$packing_item_value = 'N';
|
||
|
|
updateDispensaryStockForItemInward($_SESSION['current_ohcttype'], ${"item_id$i"}, ${"dispensary_item_qty$i"}); //keeping it same as no difference here with packing item
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
// echo $i;
|
||
|
|
|
||
|
|
/*$current_stock__query="select stock_qty,expiry_date from item_stock where item_id=${"item_id$i"} and item_batch_no='".${"item_batch_no$i"}."' ";
|
||
|
|
$results_stock__query=mysqli_query($conn,$current_stock__query);
|
||
|
|
$row_stocks = mysqli_fetch_row ( $results_stock__query );
|
||
|
|
//$stock_qty = getTableFieldValue ( 'item_stock', 'stock_qty', 'item_id', ${"item_id$i"}, 'item_batch_no',$item_batch_no));
|
||
|
|
$stock_qty = floatval ( $row_stocks[0] ) - floatval ( ${"issue_qty$i"} );
|
||
|
|
|
||
|
|
// echo $current_stock_level;
|
||
|
|
$current_stock_query = "update item_stock set stock_qty='" . $stock_qty . "' where item_id='${"item_id$i"}' and item_batch_no='${"item_batch_no$i"}' ";
|
||
|
|
// echo $current_stock_query;
|
||
|
|
mysqli_query($conn,$current_stock_query );*/
|
||
|
|
|
||
|
|
/*$current_stock__query_dispensary="select stock_qty from item_stock_dispensary where item_id=${"item_id$i"} and item_batch_no='".${"item_batch_no$i"}."' and ohc_location_id='".$_POST['ohc_location_id']."' ";
|
||
|
|
$results_stock__query_dispensary=mysqli_query($conn,$current_stock__query_dispensary);
|
||
|
|
//echo $current_stock__query_dispensary;
|
||
|
|
$row_stocks_dispensary = mysqli_fetch_row ( $results_stock__query_dispensary );
|
||
|
|
//$stock_qty = getTableFieldValue ( 'item_stock', 'stock_qty', 'item_id', ${"item_id$i"}, 'item_batch_no',$item_batch_no));
|
||
|
|
$stock_qty_dispensary = floatval ( $row_stocks_dispensary[0] ) +floatval ( ${"received_qty$i"} );
|
||
|
|
//echo floatval ( ${"received_qty$i"} );
|
||
|
|
// echo mysqli_num_rows ( $results_stock__query_dispensary) ;
|
||
|
|
|
||
|
|
if(mysqli_num_rows ( $results_stock__query_dispensary )>0){
|
||
|
|
$current_stock_query_dispensary = "update item_stock_dispensary set stock_qty='" . $stock_qty_dispensary . "' where item_id='${"item_id$i"}' and item_batch_no='${"item_batch_no$i"}' and ohc_location_id='".$_POST['ohc_location_id']."' ";
|
||
|
|
// echo $current_stock_query_dispensary;
|
||
|
|
mysqli_query($conn,$current_stock_query_dispensary );
|
||
|
|
}else{
|
||
|
|
$current_stock_query_dispensary = "insert into item_stock_dispensary set expiry_date=STR_TO_DATE('".${"expiry_date$i"}."','%Y-%m-%d'), stock_qty='" . $stock_qty_dispensary . "',ohc_location_id='".$_POST['ohc_location_id']."' , item_id='${"item_id$i"}', item_batch_no='${"item_batch_no$i"}' ";
|
||
|
|
// echo $current_stock_query_dispensary;
|
||
|
|
mysqli_query($conn,$current_stock_query_dispensary );
|
||
|
|
}*/
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
/*
|
||
|
|
* $current_stock_level= getTableFieldValue('tbl_items','current_stock_level','item_id',${"item_id$i"});
|
||
|
|
* $current_stock_level=$current_stock_level+${"item_qty$i"};
|
||
|
|
* //echo $current_stock_level;
|
||
|
|
* $current_stock_query="update tbl_items set current_stock_level='".$current_stock_level."' where item_id='${"item_id$i"}' ";
|
||
|
|
* echo $current_stock_query;
|
||
|
|
* mysqli_query($conn,$current_stock_query);
|
||
|
|
*/
|
||
|
|
|
||
|
|
// $batch_ref_no=${"item_id$i"}.'_'.${"batch$i"};
|
||
|
|
$query1 = "insert into received_issue_items set received_id='$received_id', ohc_location_id='" . $_SESSION['current_ohcttype'] . "', issue_id='" . $_POST['issue_id'] . "', stock_issue_item_id='" . ${"item_id$i"} . "',received_qty='" . ${"dispensary_item_qty$i"} . "',item_batch_no='" . ${"item_batch_no$i"} . "', item_id ='" . ${"item_id$i"} . "',issue_qty = '" . ${"dispensary_item_qty$i"} . "',dis_item_qty='" . ${"dispensary_item_qty$i"} . "',dispensary_item='" . ${"dispensary_item$i"} . "',is_packaging_item='" . $packing_item_value . "' ";
|
||
|
|
error_log("received_issue_items query:" . $query1);
|
||
|
|
if (!$result1 = @mysqli_query($conn, $query1)) {
|
||
|
|
error_log("Failed to update received_issue_items:" . mysqli_error($conn) . " Failing Query:" . $query1);
|
||
|
|
rollback();
|
||
|
|
exit(mysqli_error($conn));
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
//to update item price list
|
||
|
|
|
||
|
|
$rate_sql = "select * from item_cost where item_id='" . ${"item_id$i"} . "'";
|
||
|
|
|
||
|
|
$rate_result = mysqli_query($conn, $rate_sql);
|
||
|
|
|
||
|
|
$num_count = mysqli_num_rows($rate_result);
|
||
|
|
|
||
|
|
$row_rate = mysqli_fetch_assoc($rate_result);
|
||
|
|
|
||
|
|
$new_applicable_date = date("Y/m/d");
|
||
|
|
|
||
|
|
// getting new cost from latest procurement start
|
||
|
|
$cost_per_unit = 0;
|
||
|
|
$total_qty = 0;
|
||
|
|
$total_mrp = 0;
|
||
|
|
$total_cost = 0;
|
||
|
|
|
||
|
|
$rate_sql_proc = "select * from procurement p left join procurement_items pt on p.procurement_id=pt.procurement_id where pt.item_id= '" . ${"item_id$i"} . "' and pt.ohc_type_id='" . $_SESSION['current_ohcttype'] . "' order by pt.procurement_item_id desc limit 1";
|
||
|
|
|
||
|
|
error_log("query to get latest item cost " . $rate_sql_proc);
|
||
|
|
|
||
|
|
$rate_result_proc = mysqli_query($conn, $rate_sql_proc);
|
||
|
|
|
||
|
|
$rate_row_proc = mysqli_fetch_assoc($rate_result_proc);
|
||
|
|
|
||
|
|
$total_qty = $rate_row_proc['qty'];
|
||
|
|
$total_mrp = $rate_row_proc['net_value'];
|
||
|
|
|
||
|
|
error_log("total qty " . $total_qty . " total mrp " . $total_mrp);
|
||
|
|
|
||
|
|
if ($total_mrp != null && $total_qty != null && $total_mrp != '' && $total_qty != '') {
|
||
|
|
$cost_per_unit = round($total_mrp / $total_qty, 2);
|
||
|
|
} else {
|
||
|
|
$cost_per_unit = 0;
|
||
|
|
}
|
||
|
|
|
||
|
|
error_log("cost per unit " . $cost_per_unit);
|
||
|
|
|
||
|
|
|
||
|
|
//getting new cost from latest procurement end
|
||
|
|
|
||
|
|
if ($num_count > 0) {
|
||
|
|
|
||
|
|
$old_cost = $row_rate['item_rate_latest'];
|
||
|
|
|
||
|
|
$new_cost = $cost_per_unit;
|
||
|
|
|
||
|
|
|
||
|
|
$rate_update_sql = "update item_cost set item_rate_latest='" . $new_cost . "',item_rate_old='" . $old_cost . "',applicable_date='" . $new_applicable_date . "',ohc_type_id='" . $_SESSION['current_ohcttype'] . "' where item_id='" . ${"item_id$i"} . "' ";
|
||
|
|
|
||
|
|
error_log("rate update query " . $rate_update_sql);
|
||
|
|
|
||
|
|
if (!$result_update_sql = mysqli_query($conn, $rate_update_sql)) {
|
||
|
|
error_log("error in update item " . mysqli_error($conn));
|
||
|
|
}
|
||
|
|
} else {
|
||
|
|
|
||
|
|
$new_cost = $cost_per_unit;
|
||
|
|
|
||
|
|
$insert_item_rate = "insert into item_cost set item_id='" . ${"item_id$i"} . "', item_rate_latest='" . $new_cost . "',item_rate_old='" . $new_cost . "',applicable_date='" . $new_applicable_date . "',ohc_type_id='" . $_SESSION['current_ohcttype'] . "'";
|
||
|
|
|
||
|
|
error_log("rate insert query " . $insert_item_rate);
|
||
|
|
|
||
|
|
if (!$result_item_sql = mysqli_query($conn, $insert_item_rate)) {
|
||
|
|
error_log("error in insert item" . mysqli_error($conn));
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// $item_stock_query="select current_stock_level from tbl_items where item_id='".${"item_id$i"}."'";
|
||
|
|
}
|
||
|
|
}
|
||
|
|
commit();
|
||
|
|
|
||
|
|
if ($data == null) {
|
||
|
|
$data['status'] = 200;
|
||
|
|
$data['message'] = "Data not found!";
|
||
|
|
}
|
||
|
|
echo json_encode($data);
|