sheets());
$insert_counter=0;
$update_counter=0;
$fail_counter=0;
for($i=0;$i<$sheetCount;$i++)
{
$Reader->ChangeSheet($i);
$j=0;
foreach ($Reader as $Row)
{
if($j==0 || $j==1){
$j++;
continue;
}
$j++;
// echo "".$Row[0];
$procurement_refno= "";
if(isset($Row[0])) {
$procurement_refno= mysqli_real_escape_string($conn,$Row[0]);
}
$procurement_date= "";
if(isset($Row[1])) {
$procurement_date= mysqli_real_escape_string($conn,$Row[1]);
}
$item_name= "";
if(isset($Row[2])) {
$item_name= mysqli_real_escape_string($conn,$Row[2]);
if($item_name==NULL){
$response_mssages.="
ITEM NAME Missing at row: ".+$j;
$fail_counter++;
continue;
}
}
$stock_qty= "";
if(isset($Row[3])) {
$stock_qty= mysqli_real_escape_string($conn,$Row[3]);
}
$net_value= "";
if(isset($Row[4])) {
$net_value= mysqli_real_escape_string($conn,$Row[4]);
}
$item_batch_no= "";
if(isset($Row[5])) {
$item_batch_no= mysqli_real_escape_string($conn,$Row[5]);
}
$expiry_date= "";
if(isset($Row[6])) {
$expiry_date= mysqli_real_escape_string($conn,$Row[6]);
}
$remarks= "";
if(isset($Row[7])) {
$remarks= mysqli_real_escape_string($conn,$Row[7]);
}
if ( !empty($procurement_refno)) {
$procurement_id=getTableFieldValue('procurement','procurement_id','procurement_refno',"'".$procurement_refno."'");
$initquery="";
$endquery="";
if($procurement_id!="" && $procurement_id!=null){
$initquery = "update procurement set ";
$endquery=" where procurement_id = '".$procurement_id."' ";
$update_counter++;
}else{
$initquery = "insert into procurement set ";
$endquery="";
$insert_counter++;
}
$query_procurement= $initquery." procurement_date = STR_TO_DATE('".$procurement_date."','%d/%m/%Y'), procurement_refno='".$procurement_refno."',remarks='".$remarks."'". $endquery;
// echo $query_procurement;
if (!$result = @mysqli_query($conn,$query_procurement)) {
echo $query_procurement;
$response_array['responseText']=mysqli_error($conn);
rollback();
exit(mysqli_error($conn));
}
// $result = mysqli_query($conn, $query_patient);
if ($result) {
$response_array['status'] = 'success';
//$message = "Excel Data Imported Into the Database";
} else {
$response_array['status'] = 'error';
//$message = "Problem In Importing Excel Data";
}
}
$procurement_id=getTableFieldValue('procurement','procurement_id','procurement_refno',"'".$procurement_refno."'");
$item_id=getTableFieldValue('tbl_items','item_id','item_name',"'".$item_name."'");
// echo $item_id;
$query_del = " delete from procurement_items where procurement_id = '".$procurement_id."' ";
//echo $query_del;
$result_del = @mysqli_query($conn,$query_del);
$query_procurement_items="";
if($item_id!=null && $item_id!="" && $item_id!=0){
$query_procurement_items= " insert into procurement_items set procurement_id='".$procurement_id."',item_id='".$item_id."', qty='".$stock_qty."',net_value='".$net_value."',batch='". $item_batch_no."',expiry=STR_TO_DATE('".$expiry_date."','%d/%m/%Y'),remarks='".$remarks."' ";
// echo $query_procurement_items;
// $result_query_procurement_items= @mysqli_query($conn,$query_procurement_items);
if (!@mysqli_query($conn,$query_procurement_items)) {
rollback();
header('HTTP/1.1 500 Internal Server Error');
echo json_encode(die('failed!' . mysqli_error($conn)));
}
}
if(!empty($procurement_id)){
$current_stock_level_procurement_items_query="select item_id,qty,batch,net_value from procurement_items where procurement_id='".$procurement_id."' ";
// echo $current_stock_level_procurement_items_query;
$results_procuerment_items=mysqli_query($conn,$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']);
$stock_qty= getTableFieldValue('item_stock','stock_qty','item_id',$rows_procurement_items['item_id'],'item_batch_no',"'".$rows_procurement_items['batch']."'");
//echo"qtyyyyyyyyyyyyyy" .$stock_qty;
$current_stock_level_items_qty=$stock_qty+$rows_procurement_items['qty'];
//echo current_stock_level_items_qty;
$current_stock_query="update item_stock set stock_qty='".$current_stock_level_items_qty."' where item_id='".$rows_procurement_items['item_id']."' and item_batch_no='".$rows_procurement_items['batch']."' ";
//$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;
//$result_procurement_items_query= @mysqli_query($conn,$current_stock_query);
if (!@mysqli_query($conn,$current_stock_query)) {
rollback();
header('HTTP/1.1 500 Internal Server Error');
echo json_encode(die('failed!' . mysqli_error($conn)));
}
}
}
}
}
}
commit();
$response_mssages1=$insert_counter." "."records inserted
";
$response_mssages1.=$update_counter." "."records updated
";
$response_mssages1.=$fail_counter." "."records skipped
";
$response_array['responseText']="
".$response_mssages1.$response_mssages;
echo json_encode($response_array);
?>