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

$where_work_group = get_emp_ls5($site_cd, $emp_id, 'wko_det_work_grp');

$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') 
							
							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."%'  
				" .$where_work_group." ) 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));
					 
				}
				$CO_json =array();
				do 	{
					 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
					 $CO_json[]= $row;
					
					}
				} while ( sqlsrv_next_result($stmt) );
				

$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 ('G','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		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."%'  
				". $where_work_group."  ) 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));
					 
				}
				$PM_json =array();
				do 	{
					 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {	
					 $PM_json[]= $row;
					
					}
				} while ( sqlsrv_next_result($stmt) );


if ($valid) {

	
	//echo $sql;
	returnData($CO_json,$PM_json);	
	sqlsrv_free_stmt( $stmt);
	sqlsrv_close( $conn);	
	
}

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

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

 
?>