<?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'];
$pr_approval_limit = $_REQUEST['pr_approval_limit'];
$loop = $_REQUEST['loop']; 	


if($type == "my_level")
{
	
	if($loop == "0"){
		
		$ls_site_filter = "  AND (pur_mst_purq_approve NOT IN ('A', 'D', 'C')) AND (  pur_mst_release_for_app = '1' ) ";
		
		$ls_pr_filter = 	" AND pur_mst_chg_costcenter  IN 
					 ( 
							SELECT	cf_cost_center.costcenter  
							FROM	cf_cost_center,emp_ls2,pur_app								
							WHERE	cf_cost_center.site_cd = emp_ls2.site_cd 
							AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter 
							AND		emp_ls2.emp_ls2_approver = '1' 
							AND		cf_cost_center.site_cd = pur_mst.site_cd
							AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter 						
							AND		pur_app.site_cd = pur_mst.site_cd 
							AND		pur_app.mst_rowid = pur_mst.rowid 
							AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter
							AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id 								
							AND		emp_ls2.emp_ls2_empl_id = '".$EmpID ."') 
							AND 	( pur_mst_cur_app_level + 1 )  =  ( SELECT	emp_ls2.emp_ls2_level 
							FROM	cf_cost_center,emp_ls2, pur_app		
							WHERE	cf_cost_center.site_cd = emp_ls2.site_cd 
							AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter
							AND		emp_ls2.emp_ls2_approver = '1'
							AND		cf_cost_center.site_cd = pur_mst.site_cd 
							AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter 						
							AND		pur_app.site_cd = pur_mst.site_cd 
							AND		pur_app.mst_rowid = pur_mst.rowid 
							AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter
							AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id							
							AND		emp_ls2.emp_ls2_empl_id  = '".$EmpID."')
							AND 	pur_mst_app_level > 0 ";	
		
	}else{
		
		$ls_site_filter = "AND (pur_mst_purq_approve NOT IN ('A', 'D', 'C')) AND (  pur_mst_release_for_app = '1' ) ";
		
		$ls_pr_filter = 	" AND pur_mst_chg_costcenter + Convert(Varchar(3), pur_mst_cur_app_level + 1 )  IN 
					 ( 
							SELECT	cf_cost_center.costcenter + Convert(Varchar(3), emp_ls2.emp_ls2_level ) 
							FROM	cf_cost_center,emp_ls2,pur_app								
							WHERE	cf_cost_center.site_cd = emp_ls2.site_cd 
							AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter 
							AND		emp_ls2.emp_ls2_approver = '1' 
							AND		cf_cost_center.site_cd = pur_mst.site_cd
							AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter 						
							AND		pur_app.site_cd = pur_mst.site_cd 
							AND		pur_app.mst_rowid = pur_mst.rowid 
							AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter
							AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id 								
							AND		emp_ls2.emp_ls2_empl_id = '".$EmpID ."') 					
							AND 	pur_mst_app_level > 0 ";	
		
	}
	
	
	
	
	
	
}else if ($type == "my_limit")
{

	if($loop == "0"){
		
		$ls_site_filter = "AND ( pur_mst_release_for_app = '1' ) ";
		
		$ls_pr_filter = 	"AND ((pur_mst_purq_approve = 'W' And pur_mst_app_level = 0) OR 
							 ( pur_mst_purq_approve = 'L' And pur_mst_cur_app_level  = pur_mst_app_level)OR
							 ( pur_mst_purq_approve = 'N' And pur_mst_cur_app_level  < pur_mst_app_level)OR
							 ( pur_mst_purq_approve = 'W' And pur_mst_cur_app_level  < pur_mst_app_level))
							 AND ( pur_mst_tot_cost <= '".$pr_approval_limit."')";
							 
	}else{
		
		$ls_site_filter = "AND ( pur_mst_release_for_app = '1' ) ";
		
		$ls_pr_filter = 	"AND ((pur_mst_purq_approve = 'W' And pur_mst_app_level = 0) OR 
							 ( pur_mst_purq_approve = 'L' And pur_mst_cur_app_level  = pur_mst_app_level)
							 AND ( pur_mst_tot_cost <= '".$pr_approval_limit."'))";
	}
	
}
else if ($type == "all_level")
{
	
	$ls_site_filter = " AND ( pur_mst_purq_approve NOT IN  ('A', 'D', 'C')) AND (  pur_mst_release_for_app  = '1' ) ";
		
	$ls_pr_filter = 	" AND pur_mst_chg_costcenter IN  
						 ( 
						 SELECT		cf_cost_center.costcenter 
						 FROM		cf_cost_center, emp_ls2,pur_app 	
						 WHERE		cf_cost_center.site_cd = emp_ls2.site_cd 
						 AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter 
						 AND		emp_ls2.emp_ls2_approver = '1' 
						 AND		cf_cost_center.site_cd = pur_mst.site_cd 
						 AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter 
						 AND		pur_app.site_cd = pur_mst.site_cd  
						 AND		pur_app.mst_rowid = pur_mst.rowid  
						 AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter 
						 AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id 									
						 AND		emp_ls2.emp_ls2_empl_id	= '".$EmpID."') 
						 AND 		( pur_mst_cur_app_level + 1 )  <=  ( 
						 SELECT		emp_ls2.emp_ls2_level 
						 FROM		cf_cost_center, emp_ls2, pur_app 	
						 WHERE		cf_cost_center.site_cd = emp_ls2.site_cd 
						 AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter 
						 AND		emp_ls2.emp_ls2_approver = '1' 
						 AND		cf_cost_center.site_cd = pur_mst.site_cd 
						 AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter 
						 AND		pur_app.site_cd = pur_mst.site_cd   
						 AND		pur_app.mst_rowid = pur_mst.rowid  
						 AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter 
						 AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id 								
						 AND		emp_ls2.emp_ls2_empl_id  = '".$EmpID."') 
						 AND pur_mst_app_level > 0 ";		
						 
}


		
	$sql ="	SELECT 	pur_mst.site_cd,   
			pur_mst.pur_mst_porqnnum,   
			pur_mst_status				= COALESCE(pur_mst_status+ ' : ' + pur_sts_description,''),   
			pur_mst.pur_mst_rqn_date,   
			pur_mst_requested_by		= COALESCE(pur_mst.pur_mst_requested_by+ ' : ' + emp_mst_name,''),     
			pur_mst.pur_mst_dept,   
			pur_mst.pur_mst_req_date,    
			pur_mst.pur_mst_chg_costcenter,   
			pur_mst.pur_mst_chg_account,   
			pur_mst.pur_mst_crd_costcenter,   
			pur_mst.pur_mst_crd_account,   
			pur_mst.pur_mst_op_flag,   
			pur_mst.pur_mst_entered_by,
			pur_mst.pur_mst_tot_cost,
			pur_mst.pur_mst_purq_approve,   
			pur_mst.pur_mst_notes,   
			pur_mst.pur_mst_app_route_id,   
			pur_mst.pur_mst_projectid,    
			pur_mst.pur_mst_buyer,    
			pur_mst.pur_mst_app_level,    
			pur_mst.pur_mst_cur_app_level,
			pur_mst.pur_mst_approver,
			pur_mst.pur_mst_create_by,
			pur_mst.pur_mst_create_date,
			pur_mst.pur_mst_release_for_app,
			Approval_Status = CONVERT(VARCHAR(3), pur_mst_cur_app_level) + ' / ' + CONVERT(VARCHAR(3), pur_mst_app_level ),
			pur_mst.audit_user,   
			pur_mst.audit_date,   
			pur_mst.column1,   
			pur_mst.column2,   
			pur_mst.column3,   
			pur_mst.column4,   
			pur_mst.column5,   
			pur_mst.RowID  
			FROM 	pur_mst (NOLOCK) 
			
			LEFT 
			OUTER 
			JOIN		pur_sts (NOLOCK)
			ON			pur_mst.site_cd = pur_sts.site_cd
			AND			pur_mst.pur_mst_status = pur_sts.pur_sts_status
			
			LEFT 
			OUTER 
			JOIN		emp_mst (NOLOCK)
			ON			pur_mst.site_cd = emp_mst.site_cd
			AND			pur_mst.pur_mst_requested_by = emp_mst.emp_mst_empl_id
					
			WHERE	( pur_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(pur_mst_porqnnum)
					FROM	pur_mst (NOLOCK)
					WHERE	( pur_mst.site_cd = '".$site_cd."' ) 
					AND		( pur_mst_purq_approve NOT IN ('A', 'D', 'C')) 
					AND		( pur_mst_release_for_app = '1' )
					AND		pur_mst_chg_costcenter IN
					(
					 SELECT		cf_cost_center.costcenter
					 FROM		cf_cost_center (NOLOCK),
								emp_ls2 (NOLOCK),
								pur_app (NOLOCK)							
					 WHERE		cf_cost_center.site_cd = emp_ls2.site_cd
					 AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter
					 AND		emp_ls2.emp_ls2_approver = '1'
					 AND		cf_cost_center.site_cd = pur_mst.site_cd
					 AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter
					 AND		pur_app.site_cd = pur_mst.site_cd
					 AND		pur_app.mst_rowid = pur_mst.rowid
					 AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter
					 AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id
					 AND		emp_ls2.emp_ls2_empl_id = '".$EmpID."' ) 
					 AND		( pur_mst_cur_app_level + 1 )  =
					 ( 
					 SELECT		emp_ls2.emp_ls2_level
					 FROM		cf_cost_center (NOLOCK),
								emp_ls2 (NOLOCK),
								pur_app (NOLOCK)
					 WHERE		cf_cost_center.site_cd = emp_ls2.site_cd
					 AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter 
					 AND		emp_ls2.emp_ls2_approver = '1' 
					 AND		cf_cost_center.site_cd = pur_mst.site_cd
					 AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter
					 AND		pur_app.site_cd = pur_mst.site_cd
					 AND		pur_app.mst_rowid = pur_mst.rowid
					 AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter
					 AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id
					 AND		emp_ls2.emp_ls2_empl_id = '".$EmpID."' )
					 AND		pur_mst_app_level > 0



					UNION ALL

					SELECT	Label = 'MyLimit',
							MyLimitCount = COUNT(pur_mst_porqnnum)
					FROM	pur_mst (NOLOCK)		
					WHERE	( site_cd = '".$site_cd."' ) 
					AND		( pur_mst_release_for_app = '1' )
					AND		(( pur_mst_purq_approve = 'W' And pur_mst_app_level = 0) OR
							( pur_mst_purq_approve = 'L' And pur_mst_cur_app_level = pur_mst_app_level) OR
							( pur_mst_purq_approve = 'N' And pur_mst_cur_app_level < pur_mst_app_level) OR
							( pur_mst_purq_approve = 'W' And pur_mst_cur_app_level < pur_mst_app_level) )	
					AND		( pur_mst_tot_cost <= '".$pr_approval_limit ."' )



					UNION ALL

					SELECT	Label = 'AnyLabel',
							AnyLabelCount = COUNT(pur_mst_porqnnum)
					FROM	pur_mst (NOLOCK)		
					WHERE	( site_cd = '".$site_cd."' ) 
					AND		( pur_mst_purq_approve NOT IN ('A', 'D', 'C')) AND ( pur_mst_release_for_app = '1' )
					AND		pur_mst_chg_costcenter IN 
					( 
					 SELECT		cf_cost_center.costcenter
					 FROM		cf_cost_center (NOLOCK),
								emp_ls2 (NOLOCK),
								pur_app (NOLOCK)
					 WHERE		cf_cost_center.site_cd = emp_ls2.site_cd
					 AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter
					 AND		emp_ls2.emp_ls2_approver = '1'
					 AND		cf_cost_center.site_cd = pur_mst.site_cd
					 AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter
					 AND		pur_app.site_cd = pur_mst.site_cd
					 AND		pur_app.mst_rowid = pur_mst.rowid
					 AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter
					 AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id
					 AND		emp_ls2.emp_ls2_empl_id  = '".$EmpID."'
					 ) 
					AND ( pur_mst_cur_app_level + 1 )  <= 
					( 
					 SELECT		emp_ls2.emp_ls2_level
					 FROM		cf_cost_center (NOLOCK),
								emp_ls2 (NOLOCK),
								pur_app (NOLOCK)
					 WHERE		cf_cost_center.site_cd = emp_ls2.site_cd
					 AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter
					 AND		emp_ls2.emp_ls2_approver = '1'
					 AND		cf_cost_center.site_cd = pur_mst.site_cd
					 AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter
					 AND		pur_app.site_cd = pur_mst.site_cd
					 AND		pur_app.mst_rowid = pur_mst.rowid
					 AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter
					 AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id
					 AND		emp_ls2.emp_ls2_empl_id  = '".$EmpID."'
					  ) AND pur_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(pur_mst_porqnnum)
					FROM	pur_mst (NOLOCK)
					WHERE	( pur_mst.site_cd = '".$site_cd."' ) 
					AND		( pur_mst_status NOT IN ('A', 'D', 'C')) 
					AND		( pur_mst_release_for_app  = '1' )
					AND		pur_mst_chg_costcenter + Convert(Varchar(3), pur_mst_cur_app_level + 1 ) IN
					 ( 
					 SELECT		cf_cost_center.costcenter + Convert(Varchar(3), emp_ls2.emp_ls2_level )
					 FROM		cf_cost_center (NOLOCK),
								emp_ls2 (NOLOCK),
								pur_app (NOLOCK)								
					  WHERE		cf_cost_center.site_cd = emp_ls2.site_cd
					  AND		cf_cost_center.costcenter = emp_ls2.emp_ls2_costcenter
					  AND		emp_ls2.emp_ls2_approver = '1'
					  AND		cf_cost_center.site_cd = pur_mst.site_cd
					  AND		cf_cost_center.costcenter = pur_mst.pur_mst_chg_costcenter
					  AND		pur_app.site_cd = pur_mst.site_cd
					  AND		pur_app.mst_rowid = pur_mst.rowid
					  AND		pur_app.pur_app_cost_center = cf_cost_center.costcenter
					  AND		emp_ls2.emp_ls2_empl_id = pur_app.pur_app_empl_id
					  AND		emp_ls2.emp_ls2_empl_id = '".$EmpID."'
					  ) AND pur_mst_app_level > 0 
					
					UNION ALL

					SELECT	Label = 'MyLimit',
							MyLimitCount = COUNT(pur_mst_porqnnum)
					FROM	pur_mst(NOLOCK)		
					WHERE	( site_cd = '".$site_cd."' ) 
					AND		( pur_mst_release_for_app = '1' )
					AND 	(( pur_mst_purq_approve = 'W' And pur_mst_app_level = 0) OR
							( pur_mst_purq_approve = 'L' And pur_mst_cur_app_level   = pur_mst_app_level)) 
					AND		( pur_mst_tot_cost<= '".$pr_approval_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);
		
}


 
?>