<?php
// get these values from your DB.
require_once('config.php');
$error_message;
$valid = true;


/* Begin the transaction. */
if ( sqlsrv_begin_transaction( $conn ) === false ) {
     die( print_r( sqlsrv_errors(), true ));
}


$json = file_get_contents('php://input');

// Converts it into a PHP object
$data = json_decode($json, true);

$site_cd = $data['site_cd'];
$EmpID = $data['EmpID'];



$approval_limit = $data['approval_limit'];
$status = $data['status'];
$remark = $data['remark'];
$mtr_app_rowID = $data['mtr_app_rowID'];
$mtr_mst_rowID = $data['mtr_mst_rowID'];


$header_loop = $data['loop'];
$type = $data['type'];
$count = $data['count'];
$LOGINID = $data['LOGINID'];
$header_audit_date = $data['header_audit_date'];
$Mr_tot_cost = $data['Mr_tot_cost'];
$mtr_mst_mtr_no = $data['mtr_mst_mtr_no'];
$mst_costcenter = $data['mst_costcenter'];
$il_level = $data['cur_app_level'];//il_level
$ll_level = $data['level'];//ll_level
$total_level = $data['app_level'];



$sql_mtr_mst ="Select audit_date from mtr_mst (NOLOCK)  Where site_cd = ? and  mtr_mst_mtr_no = ?";
			
	$params_mtr_mst = array($site_cd,$mtr_mst_mtr_no);

	$stmt_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);

	if( !$stmt_mtr_mst ) {
		
		 $error_message = "Error selecting mtr_mst";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true));
		 
	}	

	do {
		
		while ($row = sqlsrv_fetch_array($stmt_mtr_mst, SQLSRV_FETCH_ASSOC)) {		
		
			$audit_date = $row['audit_date']->format('Y-m-d H:i:s');						
			//echo $audit_date;
		}
		
	} while ( sqlsrv_next_result($stmt_mtr_mst) );
	  sqlsrv_free_stmt( $stmt_mtr_mst);
			  
if($header_audit_date <> $audit_date){
	 //echo $header_audit_date;
	 //echo $audit_date;
				 
	sqlsrv_rollback( $conn );
	$error_message = "The MR audit date had been changed. The record already updated by another user. Please cancel and retrieve the record again.";
	returnError($error_message);
				
}else{
	
	
	if($header_loop == "1"){
		
		if($type == "my_level"){
			
			if($count == "1"){
				
				$sql_mtr_app = "UPDATE mtr_app 
								SET mtr_app_date = getdate(), 
									mtr_app_status = ?, 
									mtr_app_desc = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate()
								WHERE site_cd=? 
								and RowID = ?";
					
				
					$params_mtr_app = array($status,$remark,$LOGINID,$LOGINID,$site_cd,$mtr_app_rowID);
					$stmt_mtr_app = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	
						
					if( !$stmt_mtr_app ) {
						$error_message = "Error update table (wko_sts)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_mtr_app);
				
			}
			
			
		}else if($type == "my_limit"){
			
			if($count == "0"){
				
				$sql_insert_mtr_app = "	INSERT mtr_app 
										(	site_cd ,					mst_RowID ,					mtr_app_level ,		mtr_app_cost_center , 
											mtr_app_empl_id ,			mtr_app_mr_limit ,			mtr_app_date ,		mtr_app_status , 
											mtr_app_desc,				mtr_app_approval,			audit_user ,	 	audit_date,
											column1,					column2,					column3,			column4,		column5,	mbl_audit_user,mbl_audit_date) 
								VALUES 
										(	?,							?,							?,					?,
											?,							?,							GetDate(),			?,
											?,							NUll,						?,					GetDate(),
											NUll,						NUll,						NUll,				NUll,			NUll,		?,					GetDate())";

						$params_mtr_app = array($site_cd,$mtr_mst_rowID,$il_level+1,'***',$EmpID,$approval_limit,$status,$remark,$LOGINID,$LOGINID);
						$stmt_mtr_app = sqlsrv_query( $conn, $sql_insert_mtr_app,$params_mtr_app);	
						if( !$stmt_mtr_app ) {
						$error_message = "Error (INSERT Table mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
							
						}
						sqlsrv_free_stmt($stmt_mtr_app);
				
			}
				
			
		}
		
		
		
		
		if(($il_level + 1 == $total_level) and ($approval_limit >= $Mr_tot_cost) ){
				//echo 'Last Level in the Approval Route';
				
				//echo '1';
				
					$sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'A', 
									mtr_mst_cur_app_level = ?, 
									mtr_mst_approver = Null,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate()									
									WHERE site_cd=? 
									and RowID = ?";
					
					
					$params_mtr_mst = array($il_level +1 ,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
					$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);	
							
					if( !$stmt_update_mtr_mst ) {
						$error_message = "Error update table (mtr_mst)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_mst);
		
		
				
				
		} else if(($il_level + 1 > $total_level) and ($approval_limit >= $Mr_tot_cost)){
				//echo 'Additional Level from Global Limit';
				
				
					$sql_mtr_mst = "UPDATE mtr_mst SET 
										mtr_mst_status = 'A', 
										mtr_mst_app_level = ?, 
										mtr_mst_cur_app_level=?,
										mtr_mst_approver = Null,
										audit_user=? ,
										audit_date=getdate(),
										mbl_audit_user=?,		
										mbl_audit_date=getdate()										
										WHERE site_cd=? 
										and RowID = ?";
						
						
						$params_mtr_mst = array($il_level +1 ,$il_level +1,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
						$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);	
								
						if( !$stmt_update_mtr_mst ) {
							$error_message = "Error update table (mtr_mst)";
							returnError($error_message);
							die( print_r( sqlsrv_errors(), true));
						}sqlsrv_free_stmt( $stmt_update_mtr_mst);
					
					
				
		}else if(($il_level + 1 == $total_level) and ($approval_limit < $Mr_tot_cost)){
						//echo 'Approval Route Final Level but Limit Not enough';
				
				
				
						$sql_emp_mst ="SELECT	TOP 1 emp_mst_empl_id
									FROM	emp_mst (NOLOCK), emp_det  (NOLOCK)
									WHERE	emp_mst.site_cd = emp_det.site_cd
									AND		emp_mst.RowID = emp_det.mst_RowID
									AND		emp_mst.site_cd = ?
									AND		emp_det.emp_det_mr_approver = '1'
									AND		emp_det_mr_limit >= ?
								ORDER BY	emp_det_mr_limit Asc,emp_mst_empl_id Asc ;";
					
					$params_emp_mst = array($site_cd,$Mr_tot_cost);

					$stmt_emp_mst = sqlsrv_query( $conn,$sql_emp_mst,$params_emp_mst);

					if( !$stmt_emp_mst ) {
						
						 $error_message = "Error selecting mtr_mst";
						 returnError($error_message);
						 die( print_r( sqlsrv_errors(), true));
						 
					}	

					do {
						
						while ($row = sqlsrv_fetch_array($stmt_emp_mst, SQLSRV_FETCH_ASSOC)) {		
						
							$emp_mst_empl_id = $row['emp_mst_empl_id'];						
						
						}
						
					} while ( sqlsrv_next_result($stmt_emp_mst) );
					  sqlsrv_free_stmt( $stmt_emp_mst);  
					  
					  
					  $sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'L', 
									mtr_mst_app_level = ?, 									
									mtr_mst_approver = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate()
									WHERE site_cd=? 
									and RowID = ?";
					
					
					$params_mtr_mst = array($il_level +1 ,$emp_mst_empl_id,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
					$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);		
							
					if( !$stmt_update_mtr_mst ) {
						$error_message = "Error update table (mtr_mst)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_mst);
					
					
					
				
		}else if(($il_level + 1 < $total_level)){
				//echo 'Total Level 3.. The current Approval Level is at Level 2';
				
				
				$sql_mtr_mst = "UPDATE mtr_app SET 
									mtr_app_date = GetDate() ,
									mtr_app_status = 'Y',
									mtr_app_desc = ?,	
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate()									
									WHERE site_cd=? 
									and mst_rowid = ?
									and mtr_app_level = ?
									and mtr_app_status = 'P'";
					
					
					$params_mtr_mst = array('** Approved ***',$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID,$il_level +1);
					$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);		
							
					if( !$stmt_update_mtr_mst ) {
						$error_message = "Error update table (mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_mst);
					
					
				
				
				$sql_mtr_app ="SELECT	ll_empl_cnt = Count(mtr_app_empl_id), ls_approver = Min(mtr_app_empl_id)
									FROM	mtr_app (NOLOCK)
									WHERE	site_cd = ?
									AND		mst_rowid = ?
									AND		mtr_app_level= ?";
					
					$params_mtr_app = array($site_cd,$mtr_mst_rowID,$il_level +2);

					$stmt_select_mtr_mst = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	

					if( !$stmt_select_mtr_mst ) {
						
						 $error_message = "Error selecting mtr_mst";
						 returnError($error_message);
						 die( print_r( sqlsrv_errors(), true));
						 
					}	

					do {
						
						while ($row = sqlsrv_fetch_array($stmt_select_mtr_mst, SQLSRV_FETCH_ASSOC)) {		
						
							$ll_empl_cnt = $row['ll_empl_cnt'];		
							$ls_approver = $row['ls_approver'];

									//echo $ll_empl_cnt ;		
									//echo $ls_approver ;											
						
						}
						
					} while ( sqlsrv_next_result($stmt_select_mtr_mst) );
					  sqlsrv_free_stmt( $stmt_select_mtr_mst);  
					
					if($ll_empl_cnt == 1){
						
						$sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'N', 
									mtr_mst_cur_app_level = ?,
									mtr_mst_approver = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate()
									WHERE site_cd=? 
									and RowID = ?";
					
					
							$params_mtr_mst = array($il_level +1,$ls_approver,$LOGINID,$site_cd,$mtr_mst_rowID);
							//echo '<pre>'; print_r(array_merge_recursive($params_mtr_mst)); echo '</pre>'	;		
							$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);		
									
							if( !$stmt_update_mtr_mst ) {
								$error_message = "Error update table (mtr_mst =1)";
								returnError($error_message);
								die( print_r( sqlsrv_errors(), true));
							}sqlsrv_free_stmt( $stmt_update_mtr_mst);
						
					}else if($ll_empl_cnt > 1){
						
						
						$sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'N', 
									mtr_mst_cur_app_level = ?,
									mtr_mst_approver = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate()													
									WHERE site_cd=? 
									and RowID = ?";
					
					
							$params_mtr_mst = array($il_level +1,$ls_approver.'...',$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
							$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);	
									
							if( !$stmt_update_mtr_mst ) {
								$error_message = "Error update table (mtr_mst >1)";
								returnError($error_message);
								die( print_r( sqlsrv_errors(), true));
							}sqlsrv_free_stmt( $stmt_update_mtr_mst);
					
					
						
					}else if($ll_empl_cnt == 0){
						
						
						$sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'N' ,
									mtr_mst_cur_app_level = ?,
									mtr_mst_approver = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate()									
									WHERE site_cd=? 
									and RowID = ?";
					
					
							$params_mtr_mst = array($il_level +1,Null,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
							$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);	
									
							if( !$stmt_update_mtr_mst ) {
								$error_message = "Error update table (mtr_mst ==0)";
								returnError($error_message);
								die( print_r( sqlsrv_errors(), true));
							}sqlsrv_free_stmt( $stmt_update_mtr_mst);
					
						
					}
					
					
					
		}
		
		
		
		
	}else{
		//open loop (0)
		
		
		if($type == "my_level"){
			
			if($count == "1"){
				
				$sql_mtr_app = "UPDATE mtr_app SET mtr_app_date = getdate(), mtr_app_status = ?, mtr_app_desc = ?,audit_user=? ,audit_date=getdate() WHERE site_cd=? and RowID = ?";
					
				
					$params_mtr_app = array($status,$remark,$LOGINID,$site_cd,$mtr_app_rowID);
					$stmt_mtr_app = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	
						
					if( !$stmt_mtr_app ) {
						$error_message = "Error update table (wko_sts)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_mtr_app);
				
			}
			
		}else if($type == "my_limit"){
			
			if($count == "0"){
				
				$sql_insert_mtr_app = "	INSERT mtr_app 
										(	site_cd ,					mst_RowID ,					mtr_app_level ,		mtr_app_cost_center , 
											mtr_app_empl_id ,			mtr_app_mr_limit ,			mtr_app_date ,		mtr_app_status , 
											mtr_app_desc,				mtr_app_approval,			audit_user ,	 	audit_date,
											column1,					column2,					column3,			column4,		column5,	mbl_audit_user,mbl_audit_date ) 
								VALUES 
										(	?,							?,							?,					?,
											?,							?,							GetDate(),			?,
											?,							NUll,						?,					GetDate(),
											NUll,						NUll,						NUll,				NUll,			NUll,		?,					GetDate())";

						$params_mtr_app = array($site_cd,$mtr_mst_rowID,$ll_level+1,'***',$EmpID,$approval_limit,$status,$remark,$LOGINID,$LOGINID);
						$stmt_mtr_app = sqlsrv_query( $conn, $sql_insert_mtr_app,$params_mtr_app);	
						if( !$stmt_mtr_app ) {
						$error_message = "Error (INSERT Table mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
							
						}
						sqlsrv_free_stmt($stmt_mtr_app);
				
			}
			
			
		}else if($type == "all_level"){
			
			if($count == "1"){
				
				$sql_mtr_app = "UPDATE 	mtr_app 
								SET 	mtr_app_date = getdate(), 
										mtr_app_status = ?, 
										mtr_app_desc = ?,
										audit_user=? ,
										audit_date=getdate(),
										mbl_audit_user=?,		
										mbl_audit_date=getdate()										
								WHERE 	site_cd=? 
								and 	RowID = ?";
					
				
					$params_mtr_app = array($status,$remark,$LOGINID,$LOGINID,$site_cd,$mtr_app_rowID);
					$stmt_mtr_app = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	
						
					if( !$stmt_mtr_app ) {
						$error_message = "Error update table (wko_sts)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_mtr_app);
				
			}
			
			
			
			
		}
		
		
		
		if(($ll_level == $total_level) and ($approval_limit >= $Mr_tot_cost) ){
				//echo 'Last Level in the Approval Route';
				
				//echo '1';
				
					$sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'A', 
									mtr_mst_cur_app_level = ?, 
									mtr_mst_approver = Null,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate()										
									WHERE site_cd=? 
									and RowID = ?";
					
					
					$params_mtr_mst = array($ll_level ,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
					$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);	
							
					if( !$stmt_update_mtr_mst ) {
						$error_message = "Error update table (mtr_mst)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_mst);
					
					
					$sql_mtr_ls1 = "UPDATE mtr_ls1 SET 
									mtr_ls1_status = 'A',									
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and mst_RowID = ?";
					
					
					$params_mtr_ls1 = array($LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
					$stmt_update_mtr_ls1 = sqlsrv_query( $conn, $sql_mtr_ls1,$params_mtr_ls1);	
							
					if( !$stmt_update_mtr_ls1 ) {
						$error_message = "Error update table (mtr_ls1)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_ls1);
					
					
					$sql_mtr_app = "UPDATE mtr_app SET 
									mtr_app_date = GetDate(),
									mtr_app_status = 'Y',
									mtr_app_desc = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									AND mst_rowid = ?
									AND	mtr_app_level <= ?
									AND	mtr_app_status = 'P'";
					
					
					$params_mtr_app = array('** Approved ***',$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID,$ll_level);
					$stmt_update_mtr_app = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	
							
					if( !$stmt_update_mtr_app ) {
						$error_message = "Error update table (mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_app);
					
					
					$sql_mtr_det = "UPDATE mtr_det SET 
									mtr_det_approver = ?,
									mtr_det_appr_date = getdate(),									
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and mst_RowID = ?";
					
					
					$params_mtr_det = array($EmpID,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
					$stmt_update_mtr_det = sqlsrv_query( $conn, $sql_mtr_det,$params_mtr_det);	
							
					if( !$stmt_update_mtr_det ) {
						$error_message = "Error update table (mtr_det)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_det);
		
		
				
				
		} else if(($ll_level > $total_level) and ($approval_limit >= $Mr_tot_cost)){
				//echo 'Additional Level from Global Limit';
				//echo '2';
				
					$sql_mtr_mst = "UPDATE mtr_mst SET 
										mtr_mst_status = 'A', 
										mtr_mst_app_level = ?, 
										mtr_mst_cur_app_level=?,
										mtr_mst_approver = Null,
										audit_user=? ,
										audit_date=getdate(),
										mbl_audit_user=?,		
										mbl_audit_date=getdate() 
										WHERE site_cd=? 
										and RowID = ?";
						
						
						$params_mtr_mst = array($ll_level ,$ll_level ,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
						$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);	
								
						if( !$stmt_update_mtr_mst ) {
							$error_message = "Error update table (mtr_mst)";
							returnError($error_message);
							die( print_r( sqlsrv_errors(), true));
						}sqlsrv_free_stmt( $stmt_update_mtr_mst);
						
						
						$sql_mtr_ls1 = "UPDATE mtr_ls1 SET 
										mtr_ls1_status = 'A',										
										audit_user=? ,
										audit_date=getdate(),
										mbl_audit_user=?,		
										mbl_audit_date=getdate() 
										WHERE site_cd=? 
										and mst_RowID = ?";						
						
						$params_mtr_ls1 = array($LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
						$stmt_update_mtr_ls1 = sqlsrv_query( $conn, $sql_mtr_ls1,$params_mtr_ls1);	
								
						if( !$stmt_update_mtr_ls1 ) {
							$error_message = "Error update table (mtr_ls1)";
							returnError($error_message);
							die( print_r( sqlsrv_errors(), true));
						}sqlsrv_free_stmt( $stmt_update_mtr_ls1);
					
						
						
						$sql_mtr_app = "UPDATE mtr_app SET 
									mtr_app_date = GetDate(),
									mtr_app_status = 'Y',
									mtr_app_desc = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									AND mst_rowid = ?
									AND	mtr_app_level <= ?
									AND	mtr_app_status = 'P'";
					
					
					$params_mtr_app = array('** Approved ***',$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID,$ll_level);
					$stmt_update_mtr_app = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	
							
					if( !$stmt_update_mtr_app ) {
						$error_message = "Error update table (mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_app);
					
					
					$sql_mtr_det = "UPDATE mtr_det SET 
									mtr_det_approver = ?,
									mtr_det_appr_date = getdate(),									
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and mst_RowID = ?";
					
					
					$params_mtr_det = array($EmpID,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
					$stmt_update_mtr_det = sqlsrv_query( $conn, $sql_mtr_det,$params_mtr_det);	
							
					if( !$stmt_update_mtr_det ) {
						$error_message = "Error update table (mtr_det)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_det);
					
					
					
				
		}else if(($ll_level == $total_level) and ($approval_limit < $Mr_tot_cost)){
				//echo 'Approval Route Final Level but Limit Not enough';
				//echo '3';
				
				
						$sql_emp_mst ="SELECT	TOP 1 emp_mst_empl_id
									FROM	emp_mst (NOLOCK), emp_det  (NOLOCK)
									WHERE	emp_mst.site_cd = emp_det.site_cd
									AND		emp_mst.RowID = emp_det.mst_RowID
									AND		emp_mst.site_cd = ?
									AND		emp_det.emp_det_mr_approver = '1'
									AND		emp_det_mr_limit >= ?
								ORDER BY	emp_det_mr_limit Asc,emp_mst_empl_id Asc ;";
					
					$params_emp_mst = array($site_cd,$Mr_tot_cost);

					$stmt_emp_mst = sqlsrv_query( $conn,$sql_emp_mst,$params_emp_mst);

					if( !$stmt_emp_mst ) {
						
						 $error_message = "Error selecting mtr_mst";
						 returnError($error_message);
						 die( print_r( sqlsrv_errors(), true));
						 
					}	

					do {
						
						while ($row = sqlsrv_fetch_array($stmt_emp_mst, SQLSRV_FETCH_ASSOC)) {		
						
							$emp_mst_empl_id = $row['emp_mst_empl_id'];						
						
						}
						
					} while ( sqlsrv_next_result($stmt_emp_mst) );
					  sqlsrv_free_stmt( $stmt_emp_mst);  
					  
					  
					  $sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'L', 
									mtr_mst_app_level = ?, 									
									mtr_mst_approver = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and RowID = ?";
					
					
					$params_mtr_mst = array($ll_level,$emp_mst_empl_id,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
					$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);		
							
					if( !$stmt_update_mtr_mst ) {
						$error_message = "Error update table (mtr_mst)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_mst);
					
					
					
					$sql_mtr_ls1 = "UPDATE mtr_ls1 SET 
										mtr_ls1_status = 'L',										
										audit_user=? ,
										audit_date=getdate(),
										mbl_audit_user=?,		
										mbl_audit_date=getdate() 
										WHERE site_cd=? 
										and mst_RowID = ?";						
						
						$params_mtr_ls1 = array($LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
						$stmt_update_mtr_ls1 = sqlsrv_query( $conn, $sql_mtr_ls1,$params_mtr_ls1);	
								
						if( !$stmt_update_mtr_ls1 ) {
							$error_message = "Error update table (mtr_ls1)";
							returnError($error_message);
							die( print_r( sqlsrv_errors(), true));
						}sqlsrv_free_stmt( $stmt_update_mtr_ls1);
					
						
						
						$sql_mtr_app = "UPDATE mtr_app SET 
									mtr_app_date = GetDate(),
									mtr_app_status = 'Y',
									mtr_app_desc = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									AND mst_rowid = ?
									AND	mtr_app_level <= ?
									AND	mtr_app_status = 'P'";
					
					
					$params_mtr_app = array('** Approved ***',$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID,$ll_level);
					$stmt_update_mtr_app = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	
							
					if( !$stmt_update_mtr_app ) {
						$error_message = "Error update table (mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_app);
					
					
					
				
		}else if(($ll_level < $total_level) and ($approval_limit < $Mr_tot_cost) ){
				//echo 'Total Level 3.. The current Approval Level is at Level 2';
				//echo '4';
				
				$sql_mtr_mst = "UPDATE mtr_app SET 
									mtr_app_date = GetDate() ,
									mtr_app_status = 'Y',
									mtr_app_desc = ?,	
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and mst_rowid = ?
									and mtr_app_level <= ?
									and mtr_app_status = 'P'";
					
					
					$params_mtr_mst = array('** Approved ***',$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID,$ll_level);
					$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);		
							
					if( !$stmt_update_mtr_mst ) {
						$error_message = "Error update table (mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_mst);
					
					
				
				
				$sql_mtr_app ="SELECT	ll_empl_cnt = Count(mtr_app_empl_id), ls_approver = Min(mtr_app_empl_id)
									FROM	mtr_app (NOLOCK)
									WHERE	site_cd = ?
									AND		mst_rowid = ?
									AND		mtr_app_level= ?";
					
					$params_mtr_app = array($site_cd,$mtr_mst_rowID,$ll_level +1);

					$stmt_select_mtr_mst = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	

					if( !$stmt_select_mtr_mst ) {
						
						 $error_message = "Error selecting mtr_mst";
						 returnError($error_message);
						 die( print_r( sqlsrv_errors(), true));
						 
					}	

					do {
						
						while ($row = sqlsrv_fetch_array($stmt_select_mtr_mst, SQLSRV_FETCH_ASSOC)) {		
						
							$ll_empl_cnt = $row['ll_empl_cnt'];		
							$ls_approver = $row['ls_approver'];

									//echo $ll_empl_cnt ;		
									//echo $ls_approver ;											
						
						}
						
					} while ( sqlsrv_next_result($stmt_select_mtr_mst) );
					  sqlsrv_free_stmt( $stmt_select_mtr_mst);  
					
					if($ll_empl_cnt == 1){
						
						$sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'N', 
									mtr_mst_cur_app_level = ?,
									mtr_mst_approver = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and RowID = ?";
					
					
							$params_mtr_mst = array($ll_level,$ls_approver,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
							//echo '<pre>'; print_r(array_merge_recursive($params_mtr_mst)); echo '</pre>'	;		
							$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);		
									
							if( !$stmt_update_mtr_mst ) {
								$error_message = "Error update table (mtr_mst =1)";
								returnError($error_message);
								die( print_r( sqlsrv_errors(), true));
							}sqlsrv_free_stmt( $stmt_update_mtr_mst);
							
							
							
						$sql_mtr_ls1 = "UPDATE mtr_ls1 SET 
										mtr_ls1_status = 'N',										
										audit_user=? ,
										audit_date=getdate(),
										mbl_audit_user=?,		
										mbl_audit_date=getdate() 
										WHERE site_cd=? 
										and mst_RowID = ?";						
						
						$params_mtr_ls1 = array($LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
						$stmt_update_mtr_ls1 = sqlsrv_query( $conn, $sql_mtr_ls1,$params_mtr_ls1);	
								
						if( !$stmt_update_mtr_ls1 ) {
							$error_message = "Error update table (mtr_ls1)";
							returnError($error_message);
							die( print_r( sqlsrv_errors(), true));
						}sqlsrv_free_stmt( $stmt_update_mtr_ls1);
						
						
						
					}else if($ll_empl_cnt > 1){
						
						
						$sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'N', 
									mtr_mst_cur_app_level = ?,
									mtr_mst_approver = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and RowID = ?";
					
					
							$params_mtr_mst = array($ll_level ,$ls_approver.'...',$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
							$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);	
									
							if( !$stmt_update_mtr_mst ) {
								$error_message = "Error update table (mtr_mst >1)";
								returnError($error_message);
								die( print_r( sqlsrv_errors(), true));
							}sqlsrv_free_stmt( $stmt_update_mtr_mst);
							
							
							
					
						$sql_mtr_ls1 = "UPDATE mtr_ls1 SET 
										mtr_ls1_status = 'N',										
										audit_user=? ,
										audit_date=getdate(),
										mbl_audit_user=?,		
										mbl_audit_date=getdate() 
										WHERE site_cd=? 
										and mst_RowID = ?";						
						
						$params_mtr_ls1 = array($LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
						$stmt_update_mtr_ls1 = sqlsrv_query( $conn, $sql_mtr_ls1,$params_mtr_ls1);	
								
						if( !$stmt_update_mtr_ls1 ) {
							$error_message = "Error update table (mtr_ls1)";
							returnError($error_message);
							die( print_r( sqlsrv_errors(), true));
						}sqlsrv_free_stmt( $stmt_update_mtr_ls1);
						
					}else if($ll_empl_cnt == 0){
						
						
						$sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'N' ,
									mtr_mst_cur_app_level = ?,
									mtr_mst_approver = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and RowID = ?";
					
					
							$params_mtr_mst = array($ll_level ,Null,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
							$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);	
									
							if( !$stmt_update_mtr_mst ) {
								$error_message = "Error update table (mtr_mst ==0)";
								returnError($error_message);
								die( print_r( sqlsrv_errors(), true));
							}sqlsrv_free_stmt( $stmt_update_mtr_mst);
							
							
							
						$sql_mtr_ls1 = "UPDATE mtr_ls1 SET 
										mtr_ls1_status = 'N',										
										audit_user=? ,
										audit_date=getdate(),
										mbl_audit_user=?,		
										mbl_audit_date=getdate() 
										WHERE site_cd=? 
										and mst_RowID = ?";						
						
						$params_mtr_ls1 = array($LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
						$stmt_update_mtr_ls1 = sqlsrv_query( $conn, $sql_mtr_ls1,$params_mtr_ls1);	
								
						if( !$stmt_update_mtr_ls1 ) {
							$error_message = "Error update table (mtr_ls1)";
							returnError($error_message);
							die( print_r( sqlsrv_errors(), true));
						}sqlsrv_free_stmt( $stmt_update_mtr_ls1);
						
					}
					
					
					
		}else if(($ll_level < $total_level) and ($approval_limit >= $Mr_tot_cost) ){
			
			//echo'5';
						$sql_mtr_mst = "UPDATE mtr_mst SET 
									mtr_mst_status = 'A' ,
									mtr_mst_cur_app_level = ?,
									mtr_mst_approver = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and RowID = ?";
					
					
							$params_mtr_mst = array($ll_level ,Null,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
							$stmt_update_mtr_mst = sqlsrv_query( $conn, $sql_mtr_mst,$params_mtr_mst);	
									
							if( !$stmt_update_mtr_mst ) {
								$error_message = "Error update table (mtr_mst ==0)";
								returnError($error_message);
								die( print_r( sqlsrv_errors(), true));
							}sqlsrv_free_stmt( $stmt_update_mtr_mst);
							
							
							
						$sql_mtr_ls1 = "UPDATE mtr_ls1 SET 
										mtr_ls1_status = 'A',										
										audit_user=? ,
										audit_date=getdate(),
										mbl_audit_user=?,		
										mbl_audit_date=getdate() 
										WHERE site_cd=? 
										and mst_RowID = ?";						
						
						$params_mtr_ls1 = array($LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
						$stmt_update_mtr_ls1 = sqlsrv_query( $conn, $sql_mtr_ls1,$params_mtr_ls1);	
								
						if( !$stmt_update_mtr_ls1 ) {
							$error_message = "Error update table (mtr_ls1)";
							returnError($error_message);
							die( print_r( sqlsrv_errors(), true));
						}sqlsrv_free_stmt( $stmt_update_mtr_ls1);
					
						
						
					$sql_mtr_app = "UPDATE mtr_app SET 
									mtr_app_date = GetDate(),
									mtr_app_status = 'Y',
									mtr_app_desc = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									AND mst_rowid = ?
									AND	mtr_app_level <= ?
									AND	mtr_app_status = 'P'";
					
					
					$params_mtr_app = array('** Approved ***',$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID,$ll_level);
					$stmt_update_mtr_app = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	
							
					if( !$stmt_update_mtr_app ) {
						$error_message = "Error update table (mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_app);						
					
					
					
					$sql_mtr_app = "UPDATE mtr_app SET 
									mtr_app_date = GetDate(),
									mtr_app_status = 'Y',
									mtr_app_desc = ?,
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									AND mst_rowid = ?
									AND	mtr_app_level > ?
									AND	mtr_app_status = 'P'";
					
					
					$params_mtr_app = array('** Approved ***',$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID,$ll_level);
					$stmt_update_mtr_app2 = sqlsrv_query( $conn, $sql_mtr_app,$params_mtr_app);	
							
					if( !$stmt_update_mtr_app2 ) {
						$error_message = "Error update table (mtr_app)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_app2);
					
					
					$sql_mtr_det = "UPDATE mtr_det SET 
									mtr_det_approver = ?,
									mtr_det_appr_date = getdate(),									
									audit_user=? ,
									audit_date=getdate(),
									mbl_audit_user=?,		
									mbl_audit_date=getdate() 
									WHERE site_cd=? 
									and mst_RowID = ?";
					
					
					$params_mtr_det = array($EmpID,$LOGINID,$LOGINID,$site_cd,$mtr_mst_rowID);
					$stmt_update_mtr_det = sqlsrv_query( $conn, $sql_mtr_det,$params_mtr_det);	
							
					if( !$stmt_update_mtr_det ) {
						$error_message = "Error update table (mtr_det)";
						returnError($error_message);
						die( print_r( sqlsrv_errors(), true));
					}sqlsrv_free_stmt( $stmt_update_mtr_det);
			
		}
		
		
		
	}
	
}
	



if($stmt_mtr_mst){
	
	if($header_audit_date <> $audit_date){
				 
				
			
	}else{
		
		if($header_loop == "1"){
			//il_level
			if(($il_level + 1 == $total_level) and ($approval_limit >= $Mr_tot_cost) ){
									
				if($stmt_update_mtr_mst){
					 sqlsrv_commit( $conn );
					 sqlsrv_close( $conn);	
					 returnData();
			
				}else{
					sqlsrv_rollback( $conn );
					$error_message = "Transaction rolled back.<br />";
					returnError($error_message);
				}
				
			}else if(($il_level + 1 > $total_level) and ($approval_limit >= $Mr_tot_cost)){
				
				if($stmt_update_mtr_mst){
					 sqlsrv_commit( $conn );
					 sqlsrv_close( $conn);	
					 returnData();
					
				}else{
					sqlsrv_rollback( $conn );
					$error_message = "Transaction rolled back.<br />";
					returnError($error_message);
				}
				
			}else if(($il_level + 1 == $total_level) and ($approval_limit < $Mr_tot_cost)){
				
				if($stmt_emp_mst && $stmt_update_mtr_mst){
					 sqlsrv_commit( $conn );
					 sqlsrv_close( $conn);	
					 returnData();
					
				}else{
					sqlsrv_rollback( $conn );
					$error_message = "Transaction rolled back.<br />";
					returnError($error_message);
				}
				
			}else if(($il_level + 1 < $total_level)){
				
				if($stmt_update_mtr_mst && $stmt_select_mtr_mst){
					
					if($ll_empl_cnt == 1){
						if($stmt_update_mtr_mst){
							 sqlsrv_commit( $conn );
							 sqlsrv_close( $conn);	
							 returnData();
					
						}else{
							sqlsrv_rollback( $conn );
							$error_message = "Transaction rolled back.<br />";
							returnError($error_message);
						}
					}else if($ll_empl_cnt > 1){
						if($stmt_update_mtr_mst){
							 sqlsrv_commit( $conn );
							 sqlsrv_close( $conn);	
							 returnData();
					
						}else{
							sqlsrv_rollback( $conn );
							$error_message = "Transaction rolled back.<br />";
							returnError($error_message);
						}
					}else if($ll_empl_cnt == 0){
						if($stmt_update_mtr_mst){
							 sqlsrv_commit( $conn );
							 sqlsrv_close( $conn);	
							 returnData();
					
						}else{
							sqlsrv_rollback( $conn );
							$error_message = "Transaction rolled back.<br />";
							returnError($error_message);
						}
					}
					
				}else{
					sqlsrv_rollback( $conn );
					$error_message = "Transaction rolled back.<br />";
					returnError($error_message);
				}
			}
			
			
		}else{
			if(($ll_level == $total_level) and ($approval_limit >= $Mr_tot_cost) ){
								
				if($stmt_update_mtr_mst && $stmt_update_mtr_ls1 && $stmt_update_mtr_app && $stmt_update_mtr_det){
					
						 sqlsrv_commit( $conn );
						 sqlsrv_close( $conn);	
						 returnData();
				}else{
						 sqlsrv_rollback( $conn );
						 $error_message = "Transaction rolled back.<br />";
						 returnError($error_message);
				}
				
			}else if(($ll_level > $total_level) and ($approval_limit >= $Mr_tot_cost)){
				
				if($stmt_update_mtr_mst && $stmt_update_mtr_ls1 && $stmt_update_mtr_app && $stmt_update_mtr_det){
					
						 sqlsrv_commit( $conn );
						 sqlsrv_close( $conn);	
						 returnData();
				}else{
						 sqlsrv_rollback( $conn );
						 $error_message = "Transaction rolled back.<br />";
						 returnError($error_message);
				}
				
			}else if(($ll_level == $total_level) and ($approval_limit < $Mr_tot_cost)){
				
				if($stmt_emp_mst && $stmt_update_mtr_mst && $stmt_update_mtr_ls1 && $stmt_update_mtr_app){
					
						 sqlsrv_commit( $conn );
						 sqlsrv_close( $conn);	
						 returnData();
				}else{
						 sqlsrv_rollback( $conn );
						 $error_message = "Transaction rolled back.<br />";
						 returnError($error_message);
				}
				
			}else if(($ll_level < $total_level) and ($approval_limit < $Mr_tot_cost) ){
				
				if($stmt_update_mtr_mst && $stmt_select_mtr_mst){
					
					if($ll_empl_cnt == 1){
							if($stmt_update_mtr_mst){
								 sqlsrv_commit( $conn );
								 sqlsrv_close( $conn);	
								 returnData();
						
							}else{
								sqlsrv_rollback( $conn );
								$error_message = "Transaction rolled back.<br />";
								returnError($error_message);
							}
						}else if($ll_empl_cnt > 1){
							if($stmt_update_mtr_mst){
								 sqlsrv_commit( $conn );
								 sqlsrv_close( $conn);	
								 returnData();
						
							}else{
								sqlsrv_rollback( $conn );
								$error_message = "Transaction rolled back.<br />";
								returnError($error_message);
							}
						}else if($ll_empl_cnt == 0){
							if($stmt_update_mtr_mst){
								 sqlsrv_commit( $conn );
								 sqlsrv_close( $conn);	
								 returnData();
						
							}else{
								sqlsrv_rollback( $conn );
								$error_message = "Transaction rolled back.<br />";
								returnError($error_message);
							}
						}
					
					
				}else{
						 sqlsrv_rollback( $conn );
						 $error_message = "Transaction rolled back.<br />";
						 returnError($error_message);
				}
				
			}else if(($ll_level < $total_level) and ($approval_limit >= $Mr_tot_cost) ){
				
				if($stmt_update_mtr_mst && $stmt_update_mtr_ls1 && $stmt_update_mtr_app && $stmt_update_mtr_app2 && $stmt_update_mtr_det){
					
						 sqlsrv_commit( $conn );
						 sqlsrv_close( $conn);	
						 returnData();
				}else{
						 sqlsrv_rollback( $conn );
						 $error_message = "Transaction rolled back.<br />";
						 returnError($error_message);
				}
			}
		}		
		
		
}
	
}else{
	sqlsrv_rollback( $conn );
	$error_message = "Transaction rolled back.<br />";
	returnError($error_message);
	
}

function returnData(){
	$returnData = array(
		'status' => 'SUCCESS',
		'message' => 'The material request successfully approved.');
	echo json_encode($returnData);
}

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


 
?>