			<?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');

$emp_det_cm_due_day;
$emp_det_pm_due_day;

$sql_emp_mst="SELECT 
emp_det_cm_due_day= COALESCE(emp_det_cm_due_day,3),	
emp_det_pm_due_day= COALESCE(emp_det_pm_due_day,3),	
emp_mst_login_id 
		from emp_mst (NOLOCK)
		INNER 
		JOIN	emp_det (NOLOCK)
		ON		emp_mst.site_cd = emp_det.site_cd
		AND		emp_mst.rowid = emp_det.mst_RowID
		WHERE	emp_mst.site_cd = '".$site_cd."' 
				AND emp_mst.emp_mst_empl_id = '".$emp_id."'";
		
$stmt_emp_mst = sqlsrv_query( $conn, $sql_emp_mst);

if( !$stmt_emp_mst ) {
	 $error_message = "Error selecting current week report (wko_mst)";
	 returnError($error_message);
	 die( print_r( sqlsrv_errors(), true));
	 
}
				
do 	{
	 while ($row = sqlsrv_fetch_array($stmt_emp_mst, SQLSRV_FETCH_ASSOC)) {	
	 $emp_det_cm_due_day= $row['emp_det_cm_due_day'];
	 $emp_det_pm_due_day= $row['emp_det_pm_due_day'];
	 $emp_mst_login_id= $row['emp_mst_login_id'];
	
	}
} while ( sqlsrv_next_result($stmt_emp_mst) );
sqlsrv_free_stmt( $stmt_emp_mst);
				
				

$sql_cm="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 WHEN 'REWORK' THEN 1 ELSE 0 END),
							Due_in = SUM(CASE WHEN Datediff(day, getdate(), wko_mst_due_date) <= ".$emp_det_pm_due_day." AND Datediff(day, getdate(), wko_mst_due_date) >= 0 AND wrk_sts.wrk_sts_typ_cd IN ('OPEN','READY-TO-BE-DONE','REWORK') THEN 1 ELSE 0 END),
							Past = SUM(CASE WHEN Datediff(day, getdate(), wko_mst_due_date) < 0 AND wrk_sts.wrk_sts_typ_cd IN ('OPEN','READY-TO-BE-DONE','REWORK') 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_cm = sqlsrv_query( $conn, $sql_cm);

				if( !$stmt_cm ) {
					 $error_message = "Error selecting CM current week report";
					 returnError($error_message);
					 die( print_r( sqlsrv_errors(), true));
					 
				}
				$CO_json =array();
				do 	{
					 while ($row = sqlsrv_fetch_array($stmt_cm, SQLSRV_FETCH_ASSOC)) {	
					 $CO_json[]= $row;
					
					}
				} while ( sqlsrv_next_result($stmt_cm) );
				sqlsrv_free_stmt( $stmt_cm);
				

$sql_pm="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 WHEN 'REWORK' THEN 1 ELSE 0 END),
							Due_in = SUM(CASE WHEN Datediff(day, getdate(), wko_mst_due_date) <= ".$emp_det_pm_due_day." AND Datediff(day, getdate(), wko_mst_due_date) >= 0 AND wrk_sts.wrk_sts_typ_cd IN ('OPEN','READY-TO-BE-DONE','REWORK') THEN 1 ELSE 0 END),
							Past = SUM(CASE WHEN Datediff(day, getdate(), wko_mst_due_date) < 0 AND wrk_sts.wrk_sts_typ_cd IN ('OPEN','READY-TO-BE-DONE','REWORK') 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_pm = sqlsrv_query( $conn, $sql_pm);

				if( !$stmt_pm ) {
					 $error_message = "Error selecting PM current week report";
					 returnError($error_message);
					 die( print_r( sqlsrv_errors(), true));
					 
				}
				$PM_json =array();
				do 	{
					 while ($row = sqlsrv_fetch_array($stmt_pm, SQLSRV_FETCH_ASSOC)) {	
					 $PM_json[]= $row;
					
					}
				} while ( sqlsrv_next_result($stmt_pm) );
				sqlsrv_free_stmt($stmt_pm);
				
				
				
				
					$sql_cf_menu="Select cf_menu.object_name,exe_flag,edit_flag
							from cf_menu  (NOLOCK)
							INNER JOIN			cf_user_access (NOLOCK)
							ON					cf_user_access.menuid = cf_menu.rowid
							
							INNER JOIN			cf_module (NOLOCK)
							ON					cf_module.language_cd = cf_menu.language_cd
							AND					cf_module.cf_module_module_cd = cf_menu.program_category

							WHERE    			cf_user_access.site_cd = '".$site_cd."'
							AND      			cf_user_access.empl_id = '".$emp_mst_login_id."'		
							AND					object_name IN( 'm_wo_my_wo','m_ast_scan_Asset','m_wo_dashboard','m_wr_my_wr','m_wo_create_wo','m_wr_create_wr','w_wko_manager','w_wkr_manager')
							AND					cf_menu.language_cd = 'DEFAULT'
							AND					cf_menu.program_category not in('FEMS', 'BEMS')
							order by  cf_menu.object_name";
		
				$stmt_cf_menu = sqlsrv_query( $conn, $sql_cf_menu);

				if( !$stmt_cf_menu ) {
					 $error_message = "Error selecting (cf_menu)";
					 returnError($error_message);
					 die( print_r( sqlsrv_errors(), true));
					 
				}
				$CFMenu_json =array();
				do 	{
					 while ($row = sqlsrv_fetch_array($stmt_cf_menu, SQLSRV_FETCH_ASSOC)) {	
					 $CFMenu_json[]= $row;
					
					}
				} while ( sqlsrv_next_result($stmt_cf_menu) );
				sqlsrv_free_stmt($stmt_cf_menu);


if ($stmt_emp_mst && $stmt_cm && $stmt_pm && $stmt_cf_menu) {

	
	//echo $sql;
	returnData($CO_json,$PM_json,$CFMenu_json,$emp_det_cm_due_day,$emp_det_pm_due_day);	
	sqlsrv_close( $conn);	
	
}

function returnData($CO_json,$PM_json,$CFMenu_json,$emp_det_cm_due_day,$emp_det_pm_due_day){
	$returnData = array(
	'status' => 'SUCCESS',
	'message' => 'Successfully',
	'emp_det_cm_due_day'=>$emp_det_cm_due_day,
	'emp_det_pm_due_day'=>$emp_det_pm_due_day,
	'CO_json'=>$CO_json,
	'CFMenu_json'=>$CFMenu_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();	
		
}
	

 
?>