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

require_once('config.php');
require_once('f_get_emp_ls5.php');
$error_message;
$valid = true;

$site_cd = $_REQUEST['site_cd'];
$requester = $_REQUEST['requester'];
$EmpID = $_REQUEST['Emp_ID'];

$where_work_group = get_emp_ls5($site_cd, $EmpID, 'mtr_mst_costcenter');

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']."' ".$where_work_group."  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."' ) ".$where_work_group."  ORDER BY mtr_mst_mtr_no ASC";
}
		
$stmt = sqlsrv_query( $conn, $sql);

if( !$stmt ) {
     $error_message = "Error selecting table (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 ($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();
}

 
?>