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


$site_cd = $_REQUEST['site_cd'];
$emp_id = $_REQUEST['emp_id'];

//Version
$sql="	SELECT	Opens = COALESCE(SUM(Opens),'' ),Due_in = COALESCE(SUM(Due_in),'' ),Past = COALESCE(SUM(Past),'' )
	
		From 	( SELECT 	Opens = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'OPEN' THEN 1 WHEN 'READY-TO-BE-DONE' THEN 1 ELSE 0 END),
							Due_in = SUM(CASE WHEN Datediff(day, getdate(), wko_mst_due_date) <= 3 
										AND Datediff(day, getdate(), wko_mst_due_date) >= 0 
										AND wrk_sts.wrk_sts_typ_cd = 'OPEN' Then 1 ELSE 0 END),
							Past = SUM(CASE WHEN Datediff(day, getdate(), wko_mst_due_date) < 0 
										AND wrk_sts.wrk_sts_typ_cd = 'OPEN' Then 1 ELSE 0 END)
					
							
					FROM 	wko_mst (NOLOCK)  
			
					INNER JOIN wko_det (NOLOCK)
					ON		wko_mst.site_cd = wko_det.site_cd
					AND		wko_mst.rowid = wko_det.mst_rowid
					AND 	wko_mst_type  IN ('C', 'P') 
					
					INNER JOIN	wrk_sts (NOLOCK)
					ON			wko_mst.site_cd = wrk_sts.site_cd
					AND			wko_mst.wko_mst_status = wrk_sts.wrk_sts_status	

					LEFT 
					OUTER 
					JOIN		ast_mst (NOLOCK)
					ON			wko_mst.site_cd = ast_mst.site_cd
					AND			wko_mst.wko_mst_assetno = ast_mst.ast_mst_asset_no
											
					
				WHERE		( wko_mst.site_cd = '".$site_cd."' ) 
				AND wko_det.wko_det_assign_to LIKE '%".$emp_id."%' 

				AND CONVERT(DATETIME, CONVERT(VARCHAR, '1900-01-01', 101)) < Getdate()   UNION ALL
				
				SELECT 	Opens = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'OPEN' THEN 1 WHEN 'READY-TO-BE-DONE' THEN 1 ELSE 0 END),
						Due_in = SUM(CASE WHEN Datediff(day, getdate(), wko_mst_due_date) <= 3 
										AND Datediff(day, getdate(), wko_mst_due_date) >= 0 
										AND wrk_sts.wrk_sts_typ_cd = 'OPEN' Then 1 ELSE 0 END),
						Past = SUM(CASE WHEN Datediff(day, getdate(), wko_mst_due_date) < 0 
										AND wrk_sts.wrk_sts_typ_cd = 'OPEN' Then 1 ELSE 0 END)		
					
						
							
				FROM 	wko_mst (NOLOCK)  
			
					INNER JOIN wko_det (NOLOCK)
					ON		wko_mst.site_cd = wko_det.site_cd
					AND		wko_mst.rowid = wko_det.mst_rowid
					AND 	wko_mst_type  IN ('G')
					
					INNER JOIN	wrk_sts (NOLOCK)
					ON			wko_mst.site_cd = wrk_sts.site_cd
					AND			wko_mst.wko_mst_status = wrk_sts.wrk_sts_status	

					LEFT 
					OUTER 
					JOIN		prm_grp (NOLOCK)
					ON			wko_mst.site_cd = prm_grp.site_cd
					AND			wko_mst.wko_mst_pm_grp = prm_grp.prm_grp_grp_cd
											
					
				WHERE		( wko_mst.site_cd = '".$site_cd."' ) 
				AND wko_det.wko_det_assign_to LIKE '%".$emp_id."%'  
				AND CONVERT(DATETIME, CONVERT(VARCHAR, '1900-01-01', 101)) < Getdate()  ) as X ";
		
$stmt = sqlsrv_query( $conn, $sql);

if( !$stmt ) {
     $error_message = "Error selecting current week report (wko_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) {

	
	//echo $sql;
	returnData($json);	
	sqlsrv_free_stmt( $stmt);
	sqlsrv_close( $conn);	
	
}

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

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

 
?>