192 lines
8.2 KiB
PHP
192 lines
8.2 KiB
PHP
|
<?php
|
||
|
|
include('includes/config/config.php');
|
||
|
|
include('includes/functions.php');
|
||
|
|
//$conn = mysqli_connect("localhost","root","","phpsamples");
|
||
|
|
require_once('excel/vendor/php-excel-reader/excel_reader2.php');
|
||
|
|
require_once('excel/vendor/SpreadsheetReader.php');
|
||
|
|
header('Content-type: application/json');
|
||
|
|
error_reporting ( E_ALL & ~ E_NOTICE & ~ E_DEPRECATED );
|
||
|
|
|
||
|
|
$response_array=array();
|
||
|
|
$response_mssages="";
|
||
|
|
|
||
|
|
begin();
|
||
|
|
$allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
|
||
|
|
|
||
|
|
if(in_array($_FILES["file"]["type"],$allowedFileType)){
|
||
|
|
|
||
|
|
$targetPath = 'excel/uploads/'.$_FILES['file']['name'];
|
||
|
|
move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
|
||
|
|
|
||
|
|
$Reader = new SpreadsheetReader($targetPath);
|
||
|
|
|
||
|
|
$sheetCount = count($Reader->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.="<br>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<br> ";
|
||
|
|
$response_mssages1.=$update_counter." "."records updated<br>";
|
||
|
|
$response_mssages1.=$fail_counter." "."records skipped<br> ";
|
||
|
|
$response_array['responseText']="<br>".$response_mssages1.$response_mssages;
|
||
|
|
echo json_encode($response_array);
|
||
|
|
?>
|