<?php
// get these values from your DB.

require_once('config.php');

$error_message;
$valid = true;

$site_cd = $_REQUEST['site_cd'];
$requester = $_REQUEST['requester'];
//$mr_approval = $_REQUEST['mr_approval'];
$mr_approval = "0";

if ($requester=="ME"){	

$sql= "SELECT mtr_mst.site_cd,   
			mtr_mst.mtr_mst_mtr_no,			 
			mtr_mst.mtr_mst_costcenter, 
			mtr_mst_assetno  =coalesce(mtr_mst_assetno, ''),
			mtr_mst_wo_no =coalesce(mtr_mst_wo_no, ''),
			mtr_mst.mtr_mst_mr_status, 			
			mtr_mst.mtr_mst_account,   
			mtr_mst.mtr_mst_org_date,    
			mtr_mst.mtr_mst_req_date,
			mtr_mst.mtr_mst_requester,			
			Requester_Name = coalesce(  (	SELECT 	emp_mst_2.emp_mst_name
										FROM		emp_mst (NOLOCK) emp_mst_2
										WHERE	emp_mst_2.site_cd = mtr_mst.site_cd
										AND		emp_mst_2.emp_mst_empl_id = mtr_mst.mtr_mst_requester ), ''),
		mtr_mst.RowID
FROM 	mtr_mst (NOLOCK)
WHERE ( mtr_mst.mtr_mst_status like '%A%' And  (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 ) 
 not like '%FULL%' )  AND  ( site_cd = '".$site_cd."' ) AND mtr_mst.mtr_mst_requester = '".$_REQUEST['mtr_mst_requester']."'  ORDER BY mtr_mst_mtr_no ASC";
	
}else{

// $sql= "SELECT mtr_mst.site_cd,   
			// mtr_mst.mtr_mst_mtr_no,			 
			// mtr_mst.mtr_mst_costcenter,   
			// mtr_mst.mtr_mst_account,  
			// mtr_mst_assetno  =coalesce(mtr_mst_assetno, ''),
			// mtr_mst_wo_no =coalesce(mtr_mst_wo_no, ''),
			// mtr_mst.mtr_mst_mr_status,			
			// mtr_mst.mtr_mst_org_date,    
			// mtr_mst.mtr_mst_req_date,
			// mtr_mst.mtr_mst_requester,			
			// Requester_Name = coalesce(  (	SELECT 	emp_mst_2.emp_mst_name
										// FROM		emp_mst (NOLOCK) emp_mst_2
										// WHERE	emp_mst_2.site_cd = mtr_mst.site_cd
										// AND		emp_mst_2.emp_mst_empl_id = mtr_mst.mtr_mst_requester ), ''),
										
		// mtr_mst.RowID
// FROM 	mtr_mst (NOLOCK)
// WHERE ( mtr_mst.mtr_mst_status like '%A%' And  (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 ) 
 // not like '%FULL%' )  AND  ( site_cd = '".$site_cd."' )   ORDER BY mtr_mst_mtr_no ASC";


if ($mr_approval == "1"){
	$append = " ( mtr_mst.site_cd = '".$site_cd."')
								 AND mtr_mst_status = 'A' AND ( RowID IN ( SELECT mst_RowID FROM mtr_ls1 WHERE mtr_mst.site_cd = mtr_ls1.site_cd AND mtr_ls1_bo_qty > 0  )) 
								 AND mtr_mst_wo_no IS NOT NULL 	
								 UNION ALL 
								SELECT mtr_mst.site_cd,   
										mtr_mst.mtr_mst_mtr_no,			 
										mtr_mst.mtr_mst_costcenter,   
										mtr_mst.mtr_mst_account,  
										mtr_mst_assetno  =coalesce(mtr_mst_assetno, ''),
										mtr_mst_wo_no =coalesce(mtr_mst_wo_no, ''),
										mtr_mst.mtr_mst_mr_status,			
										mtr_mst.mtr_mst_org_date,    
										mtr_mst.mtr_mst_req_date,
										mtr_mst.mtr_mst_requester,			
										Requester_Name = coalesce(  (	SELECT 	emp_mst_2.emp_mst_name
																		FROM		emp_mst (NOLOCK) emp_mst_2
																		WHERE	emp_mst_2.site_cd = mtr_mst.site_cd
																		AND		emp_mst_2.emp_mst_empl_id = mtr_mst.mtr_mst_requester ), ''),
										mtr_mst.RowID
								 FROM 	mtr_mst  
								 WHERE ( mtr_mst.site_cd = '".$site_cd."')
								 AND mtr_mst_status = 'A' AND ( RowID IN ( SELECT mst_RowID FROM mtr_ls1 WHERE mtr_mst.site_cd = mtr_ls1.site_cd AND mtr_ls1_bo_qty > 0  )) 
								 AND mtr_mst_wo_no IS NULL ";
}else{
	$append = " ( mtr_mst.site_cd = '".$site_cd."')
								 AND mtr_mst_status IN ('W', 'A', 'N', 'L') AND ( RowID IN ( SELECT mst_RowID FROM mtr_ls1 WHERE mtr_mst.site_cd = mtr_ls1.site_cd AND mtr_ls1_bo_qty > 0  )) 
								 AND mtr_mst_wo_no IS NOT NULL  								
								 UNION ALL  
								 SELECT mtr_mst.site_cd,   
										mtr_mst.mtr_mst_mtr_no,			 
										mtr_mst.mtr_mst_costcenter,   
										mtr_mst.mtr_mst_account,  
										mtr_mst_assetno  =coalesce(mtr_mst_assetno, ''),
										mtr_mst_wo_no =coalesce(mtr_mst_wo_no, ''),
										mtr_mst.mtr_mst_mr_status,			
										mtr_mst.mtr_mst_org_date,    
										mtr_mst.mtr_mst_req_date,
										mtr_mst.mtr_mst_requester,			
										Requester_Name = coalesce(  (	SELECT 	emp_mst_2.emp_mst_name
																		FROM		emp_mst (NOLOCK) emp_mst_2
																		WHERE	emp_mst_2.site_cd = mtr_mst.site_cd
																		AND		emp_mst_2.emp_mst_empl_id = mtr_mst.mtr_mst_requester ), ''),
										mtr_mst.RowID
								 FROM 	mtr_mst 
								 WHERE ( mtr_mst.site_cd = '".$site_cd."')
								 AND mtr_mst_status IN ('W', 'A', 'N', 'L') AND ( RowID IN ( SELECT mst_RowID FROM mtr_ls1 WHERE mtr_mst.site_cd = mtr_ls1.site_cd AND mtr_ls1_bo_qty > 0  )) 
								 AND mtr_mst_wo_no IS NULL ";

}	

$sql= "SELECT mtr_mst.site_cd,   
			mtr_mst.mtr_mst_mtr_no,			 
			mtr_mst.mtr_mst_costcenter,   
			mtr_mst.mtr_mst_account,  
			mtr_mst_assetno  =coalesce(mtr_mst_assetno, ''),
			mtr_mst_wo_no =coalesce(mtr_mst_wo_no, ''),
			mtr_mst.mtr_mst_mr_status,			
			mtr_mst.mtr_mst_org_date,    
			mtr_mst.mtr_mst_req_date,
			mtr_mst.mtr_mst_requester,			
			Requester_Name = coalesce(  (	SELECT 	emp_mst_2.emp_mst_name
										FROM		emp_mst (NOLOCK) emp_mst_2
										WHERE	emp_mst_2.site_cd = mtr_mst.site_cd
										AND		emp_mst_2.emp_mst_empl_id = mtr_mst.mtr_mst_requester ), ''),
										
		mtr_mst.RowID
FROM 	mtr_mst (NOLOCK)
WHERE ".$append;
 
}

file_put_contents("data.txt", $sql);
		
$stmt = sqlsrv_query( $conn, $sql);

if( !$stmt ) {
     $error_message = "Error selecting table (mtr_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 ($valid) {
	sqlsrv_free_stmt( $stmt);
	sqlsrv_close( $conn);	
	
	returnData($json);	
}

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

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

 
?>