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

$site_cd = $_REQUEST['site_cd'];
$type = $_REQUEST['type'];
$EmpID = $_REQUEST['EmpID'];
$emp_det_mr_limit = $_REQUEST['emp_det_mr_limit'];
$loop = $_REQUEST['loop']; 	


if($type == "my_level")
{
	
	if($loop == "0"){
		
		$ls_site_filter = "  AND ( mtr_mst_status NOT IN ('A', 'D', 'C')) AND ( mtr_mst_release_for_app = '1' ) ";
		
		$ls_pr_filter = 	" AND mtr_mst_costcenter IN 
					 ( 
							SELECT	cf_cost_center.costcenter  
							FROM	cf_cost_center,emp_ls3,mtr_app								
							WHERE	cf_cost_center.site_cd = emp_ls3.site_cd 
							AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter 
							AND		emp_ls3.emp_ls3_approver_flag = '1' 
							AND		cf_cost_center.site_cd = mtr_mst.site_cd 
							AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter 						
							AND		mtr_app.site_cd = mtr_mst.site_cd 
							AND		mtr_app.mst_rowid = mtr_mst.rowid 
							AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter 
							AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id 								
							AND		emp_ls3.emp_ls3_empl_id = '".$EmpID ."') 
							AND 	( mtr_mst_cur_app_level + 1 )  =  ( SELECT	emp_ls3.emp_ls3_level 
							FROM	cf_cost_center,emp_ls3, mtr_app 		
							WHERE	cf_cost_center.site_cd = emp_ls3.site_cd 
							AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter 
							AND		emp_ls3.emp_ls3_approver_flag = '1' 
							AND		cf_cost_center.site_cd = mtr_mst.site_cd 
							AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter 						
							AND		mtr_app.site_cd = mtr_mst.site_cd 
							AND		mtr_app.mst_rowid = mtr_mst.rowid 
							AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter 
							AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id 								
							AND		emp_ls3.emp_ls3_empl_id = '".$EmpID."')
							AND 	mtr_mst_app_level > 0 ";	
	}else{
		
		$ls_site_filter = "  AND ( mtr_mst_status NOT IN ('A', 'D', 'C')) AND ( mtr_mst_release_for_app = '1' ) ";
		
			$ls_pr_filter = 	" AND  mtr_mst_costcenter + Convert(Varchar(3), mtr_mst_cur_app_level + 1 ) IN 
								 (  
								 SELECT	cf_cost_center.costcenter + Convert(Varchar(3), emp_ls3.emp_ls3_level ) 
								 FROM		cf_cost_center, emp_ls3, mtr_app 								
								 WHERE	cf_cost_center.site_cd = emp_ls3.site_cd 
								 AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter 
								 AND		emp_ls3.emp_ls3_approver_flag = '1' 
								 AND		cf_cost_center.site_cd = mtr_mst.site_cd 
								 AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter 
								 AND		mtr_app.site_cd = mtr_mst.site_cd  
								 AND		mtr_app.mst_rowid = mtr_mst.rowid  
								 AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter 
								 AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id 
								 AND		emp_ls3.emp_ls3_empl_id = '".$EmpID."') 
								 AND mtr_mst_app_level > 0 ";
		
	}
	
	
	
	
	
	
}else if ($type == "my_limit")
{
	
	if($loop == "0"){
		$ls_site_filter = "  AND ( mtr_mst_release_for_app = '1' ) ";
		
			
		$ls_pr_filter = 	"AND (( mtr_mst_status = 'W' And mtr_mst_app_level = 0) OR 
							 ( mtr_mst_status = 'L' And mtr_mst_cur_app_level = mtr_mst_app_level) OR  
							 ( mtr_mst_status = 'N' And mtr_mst_cur_app_level < mtr_mst_app_level) OR 		
							 ( mtr_mst_status = 'W' And mtr_mst_cur_app_level < mtr_mst_app_level) ) 										
							 AND	( mtr_mst_tot_cost <= '".$emp_det_mr_limit ."')";
	}else{
		$ls_site_filter = " AND ( mtr_mst_release_for_app = '1' ) ";
		
		$ls_pr_filter = 	" AND (( mtr_mst_status = 'W' And mtr_mst_app_level = 0) OR 
										( mtr_mst_status = 'L' And mtr_mst_cur_app_level  = mtr_mst_app_level)) 
								 AND	( mtr_mst_tot_cost <=  '".$emp_det_mr_limit ."')";
	}
	
}
else if ($type == "all_level")
{
	
	$ls_site_filter = " AND ( mtr_mst_status NOT IN ('A', 'D', 'C')) AND ( mtr_mst_release_for_app = '1' ) ";
		
	$ls_pr_filter = 	" AND mtr_mst_costcenter IN 
						 ( 
						 SELECT	cf_cost_center.costcenter 
						 FROM		cf_cost_center, emp_ls3,mtr_app 	
						 WHERE	cf_cost_center.site_cd = emp_ls3.site_cd 
						 AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter 
						 AND		emp_ls3.emp_ls3_approver_flag = '1' 
						 AND		cf_cost_center.site_cd = mtr_mst.site_cd 
						 AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter 
						 AND		mtr_app.site_cd = mtr_mst.site_cd  
						 AND		mtr_app.mst_rowid = mtr_mst.rowid  
						 AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter 
						 AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id 									
						 AND		emp_ls3.emp_ls3_empl_id = '".$EmpID."') 
						 AND 		( mtr_mst_cur_app_level + 1 )  <=  ( 
						 SELECT		emp_ls3.emp_ls3_level 
						 FROM		cf_cost_center, emp_ls3, mtr_app 	
						 WHERE		cf_cost_center.site_cd = emp_ls3.site_cd 
						 AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter 
						 AND		emp_ls3.emp_ls3_approver_flag = '1' 
						 AND		cf_cost_center.site_cd = mtr_mst.site_cd 
						 AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter 
						 AND		mtr_app.site_cd = mtr_mst.site_cd   
						 AND		mtr_app.mst_rowid = mtr_mst.rowid  
						 AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter 
						 AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id 								
						 AND		emp_ls3.emp_ls3_empl_id = '".$EmpID."') 
						 AND mtr_mst_app_level > 0 ";								
	
			
			
			
	
}


		
	$sql ="	SELECT 	mtr_mst.site_cd,
			mtr_mst.mtr_mst_mtr_no,
			mtr_mst.mtr_mst_assetno,
			mtr_mst.mtr_mst_costcenter, 
			mtr_mst.mtr_mst_account, 
			mtr_mst.mtr_mst_org_date, 
			mtr_mst.mtr_mst_req_date,
			mtr_mst.mtr_mst_cmpl_date,
			mtr_mst.mtr_mst_wo_no,  
			mtr_mst.mtr_mst_requester,  
			requestername = coalesce((select emp_mst_name  from emp_mst where 	mtr_mst.site_cd = emp_mst.site_cd and emp_mst_empl_id = mtr_mst.mtr_mst_requester),mtr_mst.mtr_mst_requester),   			
			mtr_mst.mtr_mst_entered_by,    
			mtr_mst.mtr_mst_status,     	
			mtr_mst.mtr_mst_email_notification, 
			mtr_mst.mtr_mst_note,     	
			mtr_mst.mtr_mst_tot_cost,  
			Received_Status =  (SELECT CASE  WHEN Sum(mtr_ls1_req_qty) = Sum(mtr_ls1_rcv_qty + COALESCE(mtr_ls1_clear_qty, 0)) 
								THEN 'Fully Issued'  WHEN Sum(mtr_ls1_req_qty) > Sum(mtr_ls1_rcv_qty + COALESCE(mtr_ls1_clear_qty, 0)) 
								AND Sum(mtr_ls1_rcv_qty + COALESCE(mtr_ls1_clear_qty, 0)) > 0 
								THEN 'Partial Issued'  WHEN Sum(mtr_ls1_rcv_qty + COALESCE(mtr_ls1_clear_qty, 0)) = 0
								THEN 'Not Issue' END  	FROM mtr_ls1  WHERE mtr_mst.rowid = mtr_ls1.mst_rowid  GROUP BY mst_rowid ) ,
			mtr_mst.mtr_mst_cur_app_level,
			mtr_mst.mtr_mst_app_level,
			mtr_mst.mtr_mst_approver,
			mtr_mst.mtr_mst_release_for_app,
			mtr_mst.mtr_mst_email_requestor,
			mtr_mst_mr_status =COALESCE(mtr_mst_mr_status+ ' : ' + mtr_sts_description,''), 
			Approval_Status = CONVERT(VARCHAR(3), mtr_mst_cur_app_level) + ' / ' + CONVERT(VARCHAR(3), mtr_mst_app_level ),
			mtr_mst.audit_user,     	
			mtr_mst.audit_date,     	
			mtr_mst.mtr_mst_create_by,     
			mtr_mst.mtr_mst_create_date, 
			mtr_mst.column1,     	
			mtr_mst.column2,     	
			mtr_mst.column3,     	
			mtr_mst.column4,     	
			mtr_mst.column5,     
			mtr_mst.RowID  
			FROM 	mtr_mst (NOLOCK) 
			
			LEFT 
			OUTER 
			JOIN		mtr_sts (NOLOCK)
			ON			mtr_mst.site_cd = mtr_sts.site_cd
			AND			mtr_mst.mtr_mst_mr_status = mtr_sts.mtr_sts_status
			
			WHERE	( mtr_mst.site_cd = '".$site_cd."' )".
			$ls_site_filter.
			$ls_pr_filter."";



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

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

		$json= array();

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


if($loop == "0"){
	
		$sql_count= "SELECT	Label = 'MyLevel',
							MyLevelCount = COUNT(mtr_mst_mtr_no)
					FROM	mtr_mst (NOLOCK)
					WHERE	( mtr_mst.site_cd = '".$site_cd."' ) 
					AND		( mtr_mst_status NOT IN ('A', 'D', 'C')) 
					AND		( mtr_mst_release_for_app = '1' )
					AND		mtr_mst_costcenter IN
					(
					 SELECT		cf_cost_center.costcenter
					 FROM		cf_cost_center (NOLOCK),
								emp_ls3 (NOLOCK),
								mtr_app (NOLOCK)							
					 WHERE		cf_cost_center.site_cd = emp_ls3.site_cd
					 AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter
					 AND		emp_ls3.emp_ls3_approver_flag = '1'
					 AND		cf_cost_center.site_cd = mtr_mst.site_cd
					 AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter
					 AND		mtr_app.site_cd = mtr_mst.site_cd
					 AND		mtr_app.mst_rowid = mtr_mst.rowid
					 AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter
					 AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id
					 AND		emp_ls3.emp_ls3_empl_id = '".$EmpID."' ) 
					 AND		( mtr_mst_cur_app_level + 1 )  =
					 ( 
					 SELECT		emp_ls3.emp_ls3_level
					 FROM		cf_cost_center (NOLOCK),
								emp_ls3 (NOLOCK),
								mtr_app (NOLOCK)
					 WHERE		cf_cost_center.site_cd = emp_ls3.site_cd
					 AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter 
					 AND		emp_ls3.emp_ls3_approver_flag = '1' 
					 AND		cf_cost_center.site_cd = mtr_mst.site_cd
					 AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter
					 AND		mtr_app.site_cd = mtr_mst.site_cd
					 AND		mtr_app.mst_rowid = mtr_mst.rowid
					 AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter
					 AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id
					 AND		emp_ls3.emp_ls3_empl_id = '".$EmpID."' )
					 AND		mtr_mst_app_level > 0



					UNION ALL

					SELECT	Label = 'MyLimit',
							MyLimitCount = COUNT(mtr_mst_mtr_no)
					FROM	mtr_mst (NOLOCK)		
					WHERE	( site_cd = '".$site_cd."' ) 
					AND		( mtr_mst_release_for_app = '1' )
					AND		(( mtr_mst_status = 'W' And mtr_mst_app_level = 0) OR
							( mtr_mst_status = 'L' And mtr_mst_cur_app_level = mtr_mst_app_level) OR
							( mtr_mst_status = 'N' And mtr_mst_cur_app_level < mtr_mst_app_level) OR
							( mtr_mst_status = 'W' And mtr_mst_cur_app_level < mtr_mst_app_level) )	
					AND		( mtr_mst_tot_cost <= '".$emp_det_mr_limit ."' )



					UNION ALL

					SELECT	Label = 'AnyLabel',
							AnyLabelCount = COUNT(mtr_mst_mtr_no)
					FROM	mtr_mst (NOLOCK)		
					WHERE	( site_cd = '".$site_cd."' ) 
					AND		( mtr_mst_status NOT IN ('A', 'D', 'C')) AND ( mtr_mst_release_for_app = '1' )
					AND		mtr_mst_costcenter IN 
					( 
					 SELECT		cf_cost_center.costcenter
					 FROM		cf_cost_center (NOLOCK),
								emp_ls3 (NOLOCK),
								mtr_app (NOLOCK)
					 WHERE		cf_cost_center.site_cd = emp_ls3.site_cd
					 AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter
					 AND		emp_ls3.emp_ls3_approver_flag = '1'
					 AND		cf_cost_center.site_cd = mtr_mst.site_cd
					 AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter
					 AND		mtr_app.site_cd = mtr_mst.site_cd
					 AND		mtr_app.mst_rowid = mtr_mst.rowid
					 AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter
					 AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id
					 AND		emp_ls3.emp_ls3_empl_id = '".$EmpID."'
					 ) 
					AND ( mtr_mst_cur_app_level + 1 )  <= 
					( 
					 SELECT		emp_ls3.emp_ls3_level
					 FROM		cf_cost_center (NOLOCK),
								emp_ls3 (NOLOCK),
								mtr_app (NOLOCK)
					 WHERE		cf_cost_center.site_cd = emp_ls3.site_cd
					 AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter
					 AND		emp_ls3.emp_ls3_approver_flag = '1'
					 AND		cf_cost_center.site_cd = mtr_mst.site_cd
					 AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter
					 AND		mtr_app.site_cd = mtr_mst.site_cd
					 AND		mtr_app.mst_rowid = mtr_mst.rowid
					 AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter
					 AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id
					 AND		emp_ls3.emp_ls3_empl_id = '".$EmpID."'
					  ) AND mtr_mst_app_level > 0";
					  
					  
					  
		$stmt_count = sqlsrv_query( $conn, $sql_count);

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

			$json_count= array();
		do {
			
			while ($row = sqlsrv_fetch_array($stmt_count, SQLSRV_FETCH_ASSOC)) {		
			
			$json_count[]= $row;
			
			}
			
		} while ( sqlsrv_next_result($stmt_count) );			  
		
	}else{
		
		
		$sql_count= "SELECT	Label = 'MyLevel',
							MyLevelCount = COUNT(mtr_mst_mtr_no)
					FROM	mtr_mst (NOLOCK)
					WHERE	( mtr_mst.site_cd = '".$site_cd."' ) 
					AND		( mtr_mst_status NOT IN ('A', 'D', 'C')) 
					AND		( mtr_mst_release_for_app = '1' )
					AND		mtr_mst_costcenter + Convert(Varchar(3), mtr_mst_cur_app_level + 1 ) IN
					 ( 
					 SELECT		cf_cost_center.costcenter + Convert(Varchar(3), emp_ls3.emp_ls3_level )
					 FROM		cf_cost_center (NOLOCK),
								emp_ls3 (NOLOCK),
								mtr_app (NOLOCK)								
					 WHERE		cf_cost_center.site_cd = emp_ls3.site_cd
					 AND		cf_cost_center.costcenter = emp_ls3.emp_ls3_costcenter
					 AND		emp_ls3.emp_ls3_approver_flag = '1'
					 AND		cf_cost_center.site_cd = mtr_mst.site_cd
					 AND		cf_cost_center.costcenter = mtr_mst.mtr_mst_costcenter
					 AND		mtr_app.site_cd = mtr_mst.site_cd
					 AND		mtr_app.mst_rowid = mtr_mst.rowid
					 AND		mtr_app.mtr_app_cost_center = cf_cost_center.costcenter
					 AND		emp_ls3.emp_ls3_empl_id = mtr_app.mtr_app_empl_id
					 AND		emp_ls3.emp_ls3_empl_id = '".$EmpID."'
					  ) AND mtr_mst_app_level > 0 
					
					UNION ALL

					SELECT	Label = 'MyLimit',
							MyLimitCount = COUNT(mtr_mst_mtr_no)
					FROM	mtr_mst (NOLOCK)		
					WHERE	( site_cd = '".$site_cd."' ) 
					AND		( mtr_mst_release_for_app = '1' )
					AND 	(( mtr_mst_status = 'W' And mtr_mst_app_level = 0) OR
							( mtr_mst_status = 'L' And mtr_mst_cur_app_level  = mtr_mst_app_level)) 
					AND		( mtr_mst_tot_cost <= '".$emp_det_mr_limit ."' )";
					  
					  
					  
		$stmt_count = sqlsrv_query( $conn, $sql_count);

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

			$json_count= array();
		do {
			
			while ($row = sqlsrv_fetch_array($stmt_count, SQLSRV_FETCH_ASSOC)) {		
			
			$json_count[]= $row;
			
			}
			
		} while ( sqlsrv_next_result($stmt_count) );	
		
	}






if ($valid) {

	returnData($json,$json_count);	
	
}


function returnData($json,$json_count){
	$json1 = json_encode($json);
	//echo$json;
	 
	if(empty(json_decode($json1,1))) {
		 $returnData = array(
		'status' => 'SUCCESS',
		'message' => 'No Records found',		
		'count' => $json_count,
		'data' => $json);
	}else{
		$returnData = array(
		'status' => 'SUCCESS',
		'message' => 'Successfully',
		'count' => $json_count,		
		'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);
		
}


 
?>