<?php
require_once('config.php');
require_once('f_update_budget.php');
$error_message;
$valid = true;

//Header
$site_cd;
$pkg_slip;
$rcv_emp;
$remark;
$audit_user;
$audit_date = date("Y-m-d h:i:s");
$supplier;
//Details
$Rowid;
$stock_no;
$stock_location;
$rcv_uom;
$rcv_itm_cost;
$rcv_qty;
$cost_center;
$account;

//Add Values
$itm_oh_qty;
$itm_lockout;
$latest_itm_cost;
$latest_issue_price;
$surcharge = 0;
$raw_cost;
$ext_cost;
$p_issue_qty;
$fifo_avg;
$wko_mst_rowid;
$ast_mst_rowid;
$current_datetime = date("Y-m-d h:i:s");
$rcv_cost;
$rcv_con_qty;
$rcv_con_itm_cost;
$fifo_avg_cost;
$serial_no;
$itm_mst_serialize_counter;
$dft_mst_itm_resv;
$rcv_uom;
$rcv_itm_cost;
$itm_loc_stock_cost_flag;
$itm_loc_inc_ttloh;
$loc_mst_stock_cost_flag = 0;
$li_grn_line = 0;


// Takes raw data from the request
$json = file_get_contents('php://input');
$result = json_decode($json);

foreach($result->Header as $Heade){
	
	$site_cd= $Heade->site_cd;
	$pkg_slip= $Heade->pkg_slip;
	$rcv_emp= $Heade->rcv_emp;
	$remark= $Heade->remark;	
	$audit_user= $Heade->audit_user;
	$audit_date= $Heade->audit_date;
	$supplier= $Heade->supplier;

	//Select document number			
	$sql = "Select		doc_no = trx_cnt_prefix + SUBSTRING(CONVERT(VARCHAR(7), trx_cnt_counter + 1000000), 2, 6)
			From 		trx_cnt WITH (UPDLOCK)
			Where 		site_cd = '".$site_cd."' And 		trx_cnt_module_cd = 'RCV'";

	$stmt = sqlsrv_query( $conn, $sql);				
					
	if( !$stmt ) {
		 $error_message = "Error select table (cnt_mst)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
	}

	do {
		 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
			$doc_no = $row['doc_no'];		
		 }
	} while ( sqlsrv_next_result($stmt) );	

	$sql = "UPDATE trx_cnt WITH (UPDLOCK) SET trx_cnt_counter = trx_cnt_counter + 1 WHERE site_cd = '".$site_cd."' AND trx_cnt_module_cd = 'RCV'";			
	$stmt = sqlsrv_query( $conn, $sql);				
					
	if( !$stmt ) {
		 $error_message = "Error update table (cnt_mst)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
	}

foreach($result->Details as $Detail){


	$itm_mst_rowid = $Detail->itm_mst_rowid;
	$stock_no = $Detail->stock_no;
	$stock_location = $Detail->stock_location;
	$rcv_uom = $Detail->rcv_uom;
	$rcv_itm_cost = $Detail->rcv_itm_cost;
	$rcv_qty = $Detail->rcv_qty;
	$cost_center = $Detail->itm_mst_costcenter;
	$account = $Detail->itm_mst_account;

//Select item stock location exist
$sql = "SELECT 	'count' = count(*)
		FROM 	loc_mst (NOLOCK)
		WHERE 	site_cd = '".$site_cd."'
		AND		loc_mst_stk_loc = '".$stock_location."'";

$stmt = sqlsrv_query( $conn, $sql);				

if( !$stmt ) {
     $error_message = "Error select table (itm_mst, itm_loc) - 1";
	 returnError($error_message);
     die( print_r( sqlsrv_errors(), true));
}

do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		$count_stk_loc = $row['count'];	
	
     }
} while ( sqlsrv_next_result($stmt) );	

if ($count_stk_loc == 0) {
	returnError("The stock location not found in the location master, Please select a valid stock location") ;
}

//check if item have location record in itm_loc
$sql = "SELECT 	'count' = count(*)
	FROM 	itm_mst (NOLOCK), 
			itm_loc (NOLOCK)
	WHERE 	itm_mst.RowID = itm_loc.mst_RowID
	And		itm_mst.site_cd = '".$site_cd."' 
	AND		itm_mst.itm_mst_stockno = '".$stock_no."'
	AND		itm_loc.itm_loc_stk_loc = '".$stock_location."'";

$stmt = sqlsrv_query( $conn, $sql);				

if( !$stmt ) {
     $error_message = "Error select table (itm_mst, itm_loc) - 2";
	 returnError($error_message);
     die( print_r( sqlsrv_errors(), true));
}

do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		$count_itm_loc = $row['count'];		
     }
} while ( sqlsrv_next_result($stmt) );


if ($count_itm_loc == 0) {
	//Select itm rowid
	//check if item have location record in itm_loc
	$sql = "SELECT 	itm_mst.RowID
			FROM 	itm_mst (NOLOCK)
			WHERE 	itm_mst.site_cd = '".$site_cd."'
			AND		itm_mst.itm_mst_stockno = '".$stock_no."'";

	$stmt = sqlsrv_query( $conn, $sql);				

	if( !$stmt ) {
		 $error_message = "Error select table (itm_mst)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
	}

	do {
		 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
			$itm_mst_rowid = $row['RowID'];
			
			
		 }
	} while ( sqlsrv_next_result($stmt) );	
	
	//Select stock location loc_mst_stock_cost_flag
	$sql = "SELECT 	loc_mst_stock_cost_flag
			FROM 	loc_mst (NOLOCK)
			WHERE 	loc_mst.site_cd = '".$site_cd."'
			AND		loc_mst.loc_mst_stk_loc = '".$stock_location."'";

	$stmt = sqlsrv_query( $conn, $sql);				

	if( !$stmt ) {
		 $error_message = "Error select table (loc_mst)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
	}

	do {
		 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
			$loc_mst_stock_cost_flag = $row['loc_mst_stock_cost_flag'];
		 }
	} while ( sqlsrv_next_result($stmt) );	
	
	//Insert itm_loc
	//Select stock location loc_mst_stock_cost_flag
	$sql = "INSERT itm_loc (	
			site_cd, 						itm_loc_stk_loc, 					mst_RowID, 				
			itm_loc_oh_qty, 				itm_loc_item_cost, 					itm_loc_create_date,  
			itm_loc_stock_cost_flag,		itm_loc_lockout4count, 				itm_loc_order_pt, 
			itm_loc_maximum, 				itm_loc_minimum, 					itm_loc_inc_ttloh, 
			itm_loc_prim_locn_flg, 			itm_loc_pr_due_in, 					itm_loc_due_in, 
			itm_loc_hard_resrv, 			itm_loc_short_qty,  				audit_user, 
			audit_date,						mbl_audit_user,						mbl_audit_date )
	VALUES (
			?, 								?, 									?, 				
			0, 								0, 									GETDATE(),  
			?, 								'0', 								0, 
			0,								0, 									'1', 
			'0',							0, 									0, 
			0, 								0, 									?, 
			GETDATE(),						?, 									GETDATE() )";

	$params_itm_loc = array( $site_cd,$stock_location,$itm_mst_rowid,$loc_mst_stock_cost_flag,$audit_user,$audit_user );
	$stmt = sqlsrv_query( $conn, $sql,$params_itm_loc);				

	if( !$stmt ) {
		 $error_message = "Error insert table (itm_loc)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
	}	
}


//Select item lockout status	
$sql = "SELECT 	'itm_lockout' = itm_loc_lockout4count 
		FROM 	itm_mst (NOLOCK), 
				itm_loc (NOLOCK)
		WHERE 	( itm_mst.RowID = itm_loc.mst_RowID )
		AND		( itm_mst.site_cd = '".$site_cd."' ) 
		AND		( itm_mst.itm_mst_stockno = '".$stock_no."' ) 
		AND		( itm_loc.itm_loc_stk_loc = '".$stock_location."' )";

$stmt = sqlsrv_query( $conn, $sql);				

if( !$stmt ) {
     $error_message = "Error select table (itm_mst, itm_loc) - 2";
	 returnError($error_message);
     die( print_r( sqlsrv_errors(), true));
}

do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		$itm_lockout = $row['itm_lockout'];		
     }
} while ( sqlsrv_next_result($stmt) );



if ($itm_lockout == '1') {
	returnError("Item lockout for stock count") ;
}	

$rcv_cost = $rcv_qty * $rcv_itm_cost;
$li_grn_line =$li_grn_line +1;

//Select Item detail from itm_mst and itm_det
$sql = "SELECT 	itm_mst.RowID,
				itm_mst_desc,
				itm_mst_ttl_oh,
				itm_det_std_cost,
				itm_det_item_cost,
				itm_det_avg_cost,         			
				itm_det_cr_code,			
				itm_mst_serialize_flg,
				itm_mst_auto_serialize_flg,
				itm_mst_serialize_counter,
				itm_det_issue_uom
		FROM 	itm_mst (NOLOCK),
				itm_det (NOLOCK)
		WHERE 	( itm_mst.RowID =itm_det.mst_RowID ) 
		AND 	( itm_mst.itm_mst_stockno = '".$stock_no."' ) 
		AND 	( itm_mst.site_cd ='".$site_cd."' )";

$stmt = sqlsrv_query( $conn, $sql);
				
if( !$stmt ) {
     $error_message = "Error select table (itm_mst , itm_det)";
	 returnError($error_message);
     die( print_r( sqlsrv_errors(), true));
}

do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		$itm_mst_rowid 				= $row['RowID'];
		$itm_mst_serialize_flg 		= $row['itm_mst_serialize_flg'];
		$itm_mst_auto_serialize_flg = $row['itm_mst_auto_serialize_flg'];
		$itm_mst_desc 				= $row['itm_mst_desc'];
		$itm_mst_ttl_oh 			= $row['itm_mst_ttl_oh'];
		$itm_det_issue_uom 			= $row['itm_det_issue_uom'];
		$itm_det_cr_code 			= $row['itm_det_cr_code'];
		$itm_det_item_cost 			= $row['itm_det_item_cost'];
		$itm_det_std_cost 			= $row['itm_det_std_cost'];
		$itm_det_avg_cost 			= $row['itm_det_avg_cost'];		
		$itm_mst_serialize_counter	= $row['itm_mst_serialize_counter'];		
     }
} while ( sqlsrv_next_result($stmt) );	


//Select Stock Cost Flag
 $sql = "SELECT	itm_loc_stock_cost_flag,
				itm_loc_inc_ttloh
		FROM	itm_loc (NOLOCK)
		WHERE	site_cd = '".$site_cd."'
		AND		mst_RowID = ".$itm_mst_rowid."
		AND		itm_loc_stk_loc = '".$stock_location."'";

$stmt = sqlsrv_query( $conn, $sql);

 if( !$stmt ) {
      $error_message = "Error Select table (itm_loc)";
	  returnError($error_message);
      die( print_r( sqlsrv_errors(), true));
 }

 do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		$itm_loc_stock_cost_flag 	= $row['itm_loc_stock_cost_flag'];
		$itm_loc_inc_ttloh 			= $row['itm_loc_inc_ttloh'];
     }
} while ( sqlsrv_next_result($stmt) );


If ($itm_loc_stock_cost_flag == "" OR IS_NULL($itm_loc_stock_cost_flag)){
	$itm_loc_stock_cost_flag = '1';
}

//Conversion Factor
if ($itm_det_issue_uom == "" OR is_null($itm_det_issue_uom)){
	$itm_det_issue_uom = $rcv_uom;
}


if ($rcv_uom <> $itm_det_issue_uom){
	//Select convertion factor
	 $sql = "SELECT	uom_con_factor
			FROM	uom_con (NOLOCK)
			WHERE	site_cd = '".$site_cd."'
			AND		uom_con_from_uom = ".$rcv_uom."
			AND		uom_con_to_uom = '".$itm_det_issue_uom."'";

	$stmt = sqlsrv_query( $conn, $sql);

	 if( !$stmt ) {
		  $error_message = "Error Select table (itm_loc)";
		  returnError($error_message);
		  die( print_r( sqlsrv_errors(), true));
	 }

	 do {
		 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
			$uom_con_factor = $row['uom_con_factor'];			
		}
	} while ( sqlsrv_next_result($stmt) );
	
	if (is_null($uom_con_factor) or $uom_con_factor <= 0 ){
		$uom_con_factor = 1;
	}
	
	$rcv_con_qty = $rcv_qty * $uom_con_factor;
	$rcv_con_itm_cost = $rcv_itm_cost / $uom_con_factor;
	
	
	
}else{
	$rcv_con_qty = $rcv_qty;
	$rcv_con_itm_cost = $rcv_itm_cost;
	
}

$itm_det_avg_cost = (( $itm_mst_ttl_oh * $itm_det_avg_cost ) + ( $rcv_cost )) / ( $itm_mst_ttl_oh + $rcv_con_qty );

$fifo_avg_cost = (( $itm_mst_ttl_oh * $itm_det_item_cost ) + ( $rcv_cost )) / ( $itm_mst_ttl_oh + $rcv_con_qty );


switch ($itm_det_cr_code) {

  case 'AVG':
    $latest_itm_cost = $itm_det_avg_cost;	
    break;
  case 'STD':
	$latest_itm_cost = $itm_det_std_cost;
    break;
  case 'LST':
    $latest_itm_cost = $rcv_con_itm_cost;
    break;
 case 'FIFO':
	$latest_itm_cost = $fifo_avg_cost;
	break;
 
  default:

}

$latest_issue_price = $latest_itm_cost * ( (100.00 + $surcharge) / 100 );

// Receive In Repair Location
switch ($itm_loc_stock_cost_flag) {
  case "1":
    switch ($itm_loc_inc_ttloh) {
	  case "1":
		//update itm_det
		 $sql = "UPDATE 	itm_det
				SET 		itm_det_ttl_oh = COALESCE(itm_det_ttl_oh, 0) + ".$rcv_con_qty.",
							itm_det_item_cost = ".$latest_itm_cost.",
							itm_det_issue_price = ".$latest_issue_price.",				
							itm_det_avg_cost = ".$itm_det_avg_cost.",
							itm_det_last_cost = ".$rcv_con_itm_cost.",
							itm_det_lastactdate = GetDate(),
							mbl_audit_user='".$audit_user."',		
							mbl_audit_date=getdate()
				WHERE		( itm_det.mst_RowID = ".$itm_mst_rowid." )";

		$stmt = sqlsrv_query( $conn, $sql);

		 if( !$stmt ) {
			  $error_message = "Error update table (itm_det 1)";
			  returnError($error_message);
			  die( print_r( sqlsrv_errors(), true));
		 }
		 
		 //update itm_mst
		 $sql = "UPDATE 	itm_mst
				SET 		itm_mst_ttl_oh = COALESCE(itm_mst_ttl_oh, 0) + ".$rcv_con_qty.",	
							itm_mst_issue_price = ".$latest_issue_price.",
							mbl_audit_user='".$audit_user."',		
							mbl_audit_date=getdate()
				WHERE		( itm_mst.RowID = ".$itm_mst_rowid." ) ";

		$stmt = sqlsrv_query( $conn, $sql);

		 if( !$stmt ) {
			  $error_message = "Error update table (itm_mst)";
			  returnError($error_message);
			  die( print_r( sqlsrv_errors(), true));
		 }

		break;
	  case "0":
		//update itm_det
		 $sql = "UPDATE 	itm_det
				SET 		itm_det_due_in = COALESCE(itm_det_due_in, 0) - ".$rcv_con_qty.",		
							itm_det_item_cost = ".$latest_itm_cost.",
							itm_det_issue_price = ".$latest_issue_price.",				
							itm_det_avg_cost = ".$itm_det_avg_cost.",
							itm_det_last_cost = ".$rcv_con_itm_cost.",
							itm_det_lastactdate = GetDate(),
							mbl_audit_user='".$audit_user."',		
							mbl_audit_date=getdate()
				WHERE		( itm_det.mst_RowID = ".$itm_mst_rowid." )";

		$stmt = sqlsrv_query( $conn, $sql);

		 if( !$stmt ) {
			  $error_message = "Error update table (itm_det 2)";
			  returnError($error_message);
			  die( print_r( sqlsrv_errors(), true));
		 }
		 
		 //update itm_mst
		 $sql = "UPDATE 	itm_mst
				SET 		itm_mst_issue_price = ".$latest_issue_price.",
							mbl_audit_user='".$audit_user."',		
							mbl_audit_date=getdate()
				WHERE		( itm_mst.RowID = ".$itm_mst_rowid." ) ";

		$stmt = sqlsrv_query( $conn, $sql);

		 if( !$stmt ) {
			  $error_message = "Error update table (itm_mst)";
			  returnError($error_message);
			  die( print_r( sqlsrv_errors(), true));
		 }		
		break;
	  default:
	}
    break;
  case "0":
	switch ($itm_loc_inc_ttloh) {
	  case "1":
		//update itm_det
		 $sql = "UPDATE 	itm_det
				SET 		itm_det_ttl_oh = COALESCE(itm_det_ttl_oh, 0) + ".$rcv_con_qty.",
							itm_det_ttl_repair = COALESCE(itm_det_ttl_repair, 0) + ".$rcv_con_qty.",			
							itm_det_lastactdate = GetDate(),
							mbl_audit_user='".$audit_user."',		
							mbl_audit_date=getdate()
				WHERE		( itm_det.mst_RowID = ".$itm_mst_rowid.")";

		$stmt = sqlsrv_query( $conn, $sql);

		 if( !$stmt ) {
			  $error_message = "Error update table (itm_det 3)";
			  returnError($error_message);
			  die( print_r( sqlsrv_errors(), true));
		 }
		 
		 //update itm_mst
		 $sql = "UPDATE 	itm_mst
				SET 		itm_mst_ttl_oh = COALESCE(itm_mst_ttl_oh, 0) + ".$rcv_con_qty.",
							mbl_audit_user='".$audit_user."',		
							mbl_audit_date=getdate()
				WHERE		( itm_mst.RowID = ".$itm_mst_rowid." ) ";

		$stmt = sqlsrv_query( $conn, $sql);

		 if( !$stmt ) {
			  $error_message = "Error update table (itm_mst)";
			  returnError($error_message);
			  die( print_r( sqlsrv_errors(), true));
		 }
		break;
	  case "0":
		//update itm_det
		 $sql = "UPDATE 	itm_det
				SET 		itm_det_due_in = COALESCE(itm_det_due_in, 0) - ".$rcv_con_qty.",		
							itm_det_ttl_repair = COALESCE(itm_det_ttl_repair, 0) + ".$rcv_con_qty.",			
							itm_det_lastactdate = GetDate(),
							mbl_audit_user='".$audit_user."',		
							mbl_audit_date=getdate()
				WHERE		( itm_det.mst_RowID = ".$itm_mst_rowid.")";

		$stmt = sqlsrv_query( $conn, $sql);

		 if( !$stmt ) {
			  $error_message = "Error update table (itm_det 4)";
			  returnError($error_message);
			  die( print_r( sqlsrv_errors(), true));
		 }
		break;
	  default:
	}
    break;
  default:
}

//update itm_loc
$sql = "UPDATE 	itm_loc
		SET 	itm_loc_oh_qty = COALESCE(itm_loc_oh_qty, 0) + ".$rcv_con_qty.",
				itm_loc_lastactdate = GetDate(),
				mbl_audit_user='".$audit_user."',		
				mbl_audit_date=getdate()
		WHERE	( itm_loc.mst_RowID = ".$itm_mst_rowid." ) 
		AND		( itm_loc_stk_loc = '".$stock_location."' )";

$stmt = sqlsrv_query( $conn, $sql);

if( !$stmt ) {
  $error_message = "Error update table (itm_loc 1)";
  returnError($error_message);
  die( print_r( sqlsrv_errors(), true));
}

//insert itm_trx
$sql_insert_itm_trx = "INSERT INTO itm_trx 
	( 	site_cd,						itm_trx_doc_no, 				itm_trx_trx_type, 				itm_trx_curr_date, 				itm_trx_trx_date,
		itm_trx_isu_empl_id,			itm_trx_rcv_empl_id, 			itm_trx_stockno, 				itm_trx_desc, 					itm_trx_partno,				
		itm_trx_com_code,				itm_trx_isu_qty, 				itm_trx_rcv_qty,				itm_trx_ord_qty, 				itm_trx_rtn_qty, 			
		itm_trx_bo_qty, 				itm_trx_chg_costcenter, 		itm_trx_chg_account, 			itm_trx_crd_costcenter,			itm_trx_crd_account,		
		itm_trx_assetno, 				itm_trx_wo, 					itm_trx_mtlrqnnum, 				itm_trx_item_cost, 				itm_trx_uom, 				
		itm_trx_cnv_qty,				itm_trx_pkguom, 				itm_trx_ext_cost, 				itm_trx_porqnnum, 				itm_trx_approver, 			
		itm_trx_supplier, 				itm_trx_pono, 					itm_trx_po_lineno,				itm_trx_ttl_oh, 				itm_trx_stk_locn, 			
		itm_trx_oh_qty, 				itm_trx_comments, 				itm_trx_to_stk_locn, 			itm_trx_mtlrqn_lineno, 			itm_trx_grn_no, 			
		itm_trx_grn_itm_no, 			itm_trx_posted, 				itm_trx_avg_cost, 				itm_trx_std_cost, 				itm_trx_last_cost, 			
		itm_trx_consigned, 				itm_trx_receive_doc_note,		itm_trx_inv_qty, 				itm_trx_inv_uom,				itm_trx_liability_acc, 		
		itm_trx_raw_cost, 				itm_trx_rcv_cost, 				itm_trx_rcv_date, 				itm_trx_pkg_slip_no, 			itm_trx_mt_special_idno,
		itm_trx_cost_cat_id, 			itm_trx_mtcs_ref_id, 			itm_trx_return_fee, 			itm_trx_mtrtnsup_ref_id,		itm_trx_mtrtnsup_lineno,	
		itm_trx_mtns_ref_id, 			itm_trx_inv_id, 				itm_trx_inv_lineno,				itm_trx_rev_inv, 				itm_trx_ret_supp, 			
		itm_trx_mkey, 					itm_trx_status,					itm_trx_projectid, 				itm_trx_login_id, 				itm_trx_cnx_qty, 			
     	itm_trx_fifo_avg_cost,      	itm_trx_remark,					audit_user,                   	audit_date,						mbl_audit_user,
		mbl_audit_date ) 
VALUES 
	( 	?,								?,								'MT41', 						GetDate(),						getdate(),					
		?,								?,								?,								?,								'', 								
		'',								0,								?,								0,								0,								
		0,								?,								?,								'',								'',								
		'',								'',								0,								?,								?,
		?,								'',								?,								'',								'',								
		'',								'',								NULL,							0,								?,				
		0,								'',								'',								0,								?,								
		?,								'',								?,								?,								?,								
		'',								0,								NULL,							'',								'',								
		0,								0,								0,								?,								'',								
		'',								0,								0,								0,								0,								
		0,								0,								0,								'',								'',								
		'',								'',								'',								?,								0,								
      	?,          					?,								?,       						GetDate(),						?,
		GetDate() )";


$params_itm_trx = array( $site_cd,$doc_no,$rcv_emp,$rcv_emp,$stock_no,$itm_mst_desc,$rcv_qty,$cost_center,$account,$rcv_itm_cost,$rcv_uom,$rcv_con_qty,$rcv_cost,$stock_location,$doc_no,$li_grn_line,$itm_det_avg_cost,$itm_det_std_cost,$rcv_con_itm_cost,$pkg_slip,$audit_user,$fifo_avg_cost,$remark,$audit_user,$audit_user );
$stmt_insert_itm_trx = sqlsrv_query( $conn, $sql_insert_itm_trx,$params_itm_trx);	


if( !$stmt_insert_itm_trx ) {
	 $error_message = "Error insert table (itm_trx)";
	 returnError($error_message);
	 die( print_r( sqlsrv_errors(), true));
}

//insert itm_mtr
$sql_insert_itm_mtr = "INSERT INTO itm_mtr
	( 	site_cd,							itm_mtr_doc_no,					itm_mtr_stockno,				itm_mtr_stk_locn,				itm_mtr_desc,
		itm_mtr_partno,						itm_mtr_rcv_date,				itm_mtr_rcv_qty,				itm_mtr_rcv_uom,				itm_mtr_item_cost,			
		itm_mtr_chg_costcenter,				itm_mtr_chg_account,			itm_mtr_pr_no,					itm_mtr_pr_lineno,				itm_mtr_po_no,				
		itm_mtr_po_lineno,					itm_mtr_supplier,				itm_mtr_pkg_slip_no,			itm_mtr_wo_no,					itm_mtr_activity_code,	
		audit_user,							audit_date,						mbl_audit_user,					mbl_audit_date  	)
VALUES 
	( 	?,									?,								?,								?,								?,	
      	'',                           		GETDATE(),                  	?,	          					?,	        					?,	
		?,									?,								'',								0,								'',
		NULL,								'',								?,								'',								'',
		?,									GetDate(),						?,								GetDate() ) ";


$params_itm_mtr = array( $site_cd,$doc_no,$stock_no,$stock_location,$itm_mst_desc,$rcv_qty,$rcv_uom,$rcv_itm_cost,$cost_center,$account,$pkg_slip,$audit_user,$audit_user );

$stmt_insert_itm_mtr = sqlsrv_query( $conn, $sql_insert_itm_mtr,$params_itm_mtr);				
				
if( !$stmt_insert_itm_mtr ) {
	 $error_message = "Error insert table (itm_mtr)";
	 returnError($error_message);
	 die( print_r( sqlsrv_errors(), true));
}
sqlsrv_free_stmt($stmt_insert_itm_mtr);


//insert itm_mtc
$sql_insert_itm_mtc = "INSERT INTO itm_mtc
						( 	site_cd,				itm_mtc_grn_no,					itm_mtc_grn_itm_no,				itm_mtc_rcv_date,				itm_mtc_rcv_empl_id, 		
							itm_mtc_stockno,		itm_mtc_stk_locn,				itm_mtc_uom,					itm_mtc_costcenter,				itm_mtc_account,				
							itm_mtc_pono,			itm_mtc_po_lineno,				itm_mtc_nonstkitm,				itm_mtc_rcv_qty,				itm_mtc_rcv_cost,				
							itm_mtc_isu_qty,		itm_mtc_inv_qty,				itm_mtc_inv_cost,				audit_user,						audit_date,		
							mbl_audit_user,			mbl_audit_date  	)
						VALUES 
					   (    ?,          			?,                  	 		?,                				GETDATE(),                 		?,                   
							?,  		 			?,  			 				?,  		 					?,  				 			?,  				
							'',						NULL,							'N',							?,  				 			?,  
							0,						0,								?,  							?,  							GetDate(),
							?,  					GetDate() 	)	";

$params_itm_mtc = array( $site_cd,$doc_no,$li_grn_line,$rcv_emp,$stock_no,$stock_location,$itm_det_issue_uom,$cost_center,$account,$rcv_con_qty,$rcv_con_itm_cost,$rcv_con_itm_cost,$audit_user,$audit_user);
$stmt_insert_itm_mtc = sqlsrv_query( $conn, $sql_insert_itm_mtc,$params_itm_mtc);				
				
if( !$stmt_insert_itm_mtc ) {
	 $error_message = "Error insert table (itm_mtc 1)";
	 returnError($error_message);
	 die( print_r( sqlsrv_errors(), true));
}
sqlsrv_free_stmt($stmt_insert_itm_mtc);



if ($itm_mst_serialize_flg == '1'){
	for ($ll_serial_cnt = 1; $ll_serial_cnt <= $rcv_con_qty; $ll_serial_cnt++){
		if ($itm_mst_auto_serialize_flg == '1'){
			$serial_no = $stock_no.strval($itm_mst_serialize_counter);
			//insert itm_ser
			$sql_insert_itm_ser = "INSERT INTO itm_ser
				( 	site_cd,					mst_RowID,					itm_ser_serial_no,			itm_ser_stk_loc,				itm_ser_create_date,
					itm_ser_warranty_date,		itm_ser_item_cost,			itm_ser_oh_qty,				itm_ser_supplier,				itm_ser_varchar1,
					audit_user,					audit_date,					itm_ser_repair_cost,		itm_ser_total_repair_cost,		itm_ser_total_repair,
					itm_ser_total_attach,		mbl_audit_user,				mbl_audit_date )
			VALUES
				(	 ?,  			 			?,  		 				?,  			 			?,  							GetDate(),
					NULL,						?,  						1,							?,  							'0',
					?,  						GetDate(),					0,							0,								0,
					0,							?,  						GetDate())";


			$params_itm_ser = array( $site_cd,$itm_mst_rowid,$serial_no,$stock_location,$rcv_con_itm_cost,$supplier,$audit_user,$audit_user);
			$stmt_insert_itm_ser = sqlsrv_query( $conn, $sql_insert_itm_ser,$params_itm_ser);				
							
			if( !$stmt_insert_itm_ser ) {
				 $error_message = "Error insert table (itm_ser)";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
			}
			
			sqlsrv_free_stmt($stmt_insert_itm_ser);
			
		}
		$itm_mst_serialize_counter = $itm_mst_serialize_counter + 1;
	}
	
	//update itm_mst
	$sql_update_itm_mst = "UPDATE 	itm_mst
							SET		itm_mst_serialize_counter = ".$itm_mst_serialize_counter.",
									mbl_audit_user='".$audit_user."',		
									mbl_audit_date=getdate()
							WHERE	itm_mst.RowID = ".$itm_mst_rowid;

	$stmt_update_itm_mst = sqlsrv_query( $conn, $sql_update_itm_mst);				
					
	if( !$stmt_update_itm_mst ) {
		 $error_message = "Error update table (itm_mst)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
	}
	
	sqlsrv_free_stmt($stmt_update_itm_mst);

}

$charge_valid = f_update_budget($site_cd, $current_datetime, $cost_center, $account, "CHARGE", $rcv_cost, $conn, $audit_user);

//echo 'supplier ='.$supplier;
if (strlen($supplier) > 0){
	//Count supplier if exist
	$sql_supplier_count = "SELECT 	'count' = COUNT(*)
							FROM	itm_sup (NOLOCK)
							WHERE	site_cd = '".$site_cd."'
							AND		mst_RowID = ".$itm_mst_rowid."
							AND		itm_sup_supplier = '".$supplier."'";

	$stmt_supplier_count = sqlsrv_query( $conn, $sql_supplier_count);				
			//echo 'supplier ='.$sql_supplier_count;		
	if( !$stmt_supplier_count ) {
		 $error_message = "Error update table (itm_mst)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
	}	
	
	do {
		 while ($row = sqlsrv_fetch_array($stmt_supplier_count, SQLSRV_FETCH_ASSOC)) {
			$supplier_count 	= $row['count'];
		}
	} while ( sqlsrv_next_result($stmt_supplier_count) );
	
	sqlsrv_free_stmt($stmt_supplier_count);
	
	if ($supplier_count == 0){
		//Select total count
		$sql_supplier_ttl_count = "SELECT 	'count' = COUNT(*) + 1
									FROM	itm_sup (NOLOCK)
									WHERE	site_cd = '".$site_cd."'
									AND		mst_RowID = ".$itm_mst_rowid;

		$stmt_supplier_ttl_count = sqlsrv_query( $conn, $sql_supplier_ttl_count);				
						
		if( !$stmt_supplier_ttl_count ) {
			 $error_message = "Error update table (itm_mst)";
			 returnError($error_message);
			 die( print_r( sqlsrv_errors(), true));
		}
		
		do {
			 while ($row = sqlsrv_fetch_array($stmt_supplier_ttl_count, SQLSRV_FETCH_ASSOC)) {
				$supplier_ttl_count 	= $row['count'];
			}
		} while ( sqlsrv_next_result($stmt_supplier_ttl_count) );
		
		sqlsrv_free_stmt($stmt_supplier_ttl_count);
		
		//Insert supplier
		$sql_insert_sup = "INSERT itm_sup (	
											site_cd,							mst_RowID,						itm_sup_curr_code,			itm_sup_last_itemcost,		itm_sup_supplier,
											itm_sup_last_rcvd_date,				itm_sup_supplier_partno,		itm_sup_avg_leadtime, 		itm_sup_min_orderqty,		itm_sup_order_uom,
											itm_sup_rcpts_ctr,					itm_sup_retail_price,			itm_sup_discount_pct,		itm_sup_partmfg,			itm_sup_mfgrank,
											itm_sup_plan_leadtime,				itm_sup_mfg_partno,				itm_sup_ord_qty,			itm_sup_rcv_qty,			itm_sup_late_qty,
											itm_sup_high_qty,					itm_sup_di,						itm_sup_ci,					audit_user,					audit_date,			
											mbl_audit_user,						mbl_audit_date	)
								VALUES (				
											?,									?,								NULL,						?,							?,
											getdate(),							NULL,							NULL,						0,							?,
											0,									?,								0,							NULL,						?,
											0,									NULL,							?,							?,							0,
											0,									0,								0,							?,							getdate(),	
											?,									getdate() )";

		$params_itm_sup = array( $site_cd,$itm_mst_rowid,$rcv_itm_cost,$supplier,$rcv_uom,$rcv_itm_cost,$supplier_ttl_count,$rcv_qty,$rcv_qty,$audit_user,$audit_user);
		$stmt_insert_sup = sqlsrv_query( $conn, $sql_insert_sup,$params_itm_sup);				
						
		if( !$stmt_insert_sup ) {
			 $error_message = "Error insert table (itm_sup)";
			 returnError($error_message);
			 die( print_r( sqlsrv_errors(), true));
		}
		
		sqlsrv_free_stmt($stmt_insert_sup);
		
	}else{
		if ($supplier_count == 1){
			//Update itm_sup
			$sql_supplier_ttl_count = "UPDATE 	itm_sup
										SET		itm_sup_last_itemcost = ".$rcv_itm_cost." ,
												itm_sup_retail_price = ".$rcv_itm_cost." ,
												itm_sup_last_rcvd_date = getdate(),
												itm_sup_order_uom = '".$rcv_uom."',
												itm_sup_ord_qty = ".$rcv_qty.",
												itm_sup_rcv_qty = ".$rcv_qty.",
												mbl_audit_user='".$audit_user."',		
												mbl_audit_date=getdate()
										WHERE 	( itm_sup.mst_RowID = ".$itm_mst_rowid." )
										AND		( itm_sup_supplier = '".$supplier."' )";

			$stmt_supplier_ttl_count = sqlsrv_query( $conn, $sql_supplier_ttl_count);				
							
			if( !$stmt_supplier_ttl_count ) {
				 $error_message = "Error update table (itm_sup)";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
			}
			
			sqlsrv_free_stmt($stmt_supplier_ttl_count);
			
		}
	}
}

//Select Stock Cost Flag
 $sql = "SELECT	dft_mst_po_auto_close,
				dft_mst_itm_resv,
				dft_mst_print_trx
		FROM	dft_mst (NOLOCK)
		WHERE	site_cd = '".$site_cd."'";

$stmt = sqlsrv_query( $conn, $sql);

 if( !$stmt ) {
      $error_message = "Error Select table (itm_loc)";
	  returnError($error_message);
      die( print_r( sqlsrv_errors(), true));
 }

 do {
     while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
		$dft_mst_po_auto_close 	= $row['dft_mst_po_auto_close'];
		$dft_mst_itm_resv 		= $row['dft_mst_itm_resv'];
		$dft_mst_print_trx 		= $row['dft_mst_print_trx'];
		$dft_mst_print_trx 		= $row['dft_mst_print_trx'];
     }
} while ( sqlsrv_next_result($stmt) );

}/* 2nd lopp */
}/* 1st lopp */

if ($valid) {	
	returnData('');	
}

sqlsrv_free_stmt( $stmt);
sqlsrv_close($conn);

function returnData($json){
	
	$returnData = array(
	'status' => 'SUCCESS',
	'message' => 'Miscellaneous received successfully',
	'data' => $json);
	
	echo json_encode($returnData);
}

function returnError($error_message){
	$json = array();
	
	$returnData = array(
	'status' => 'ERROR',
	'message' => $error_message,
	'data' => $json);	
	
	echo json_encode($returnData);
		
	exit();
}


?>