<?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, 'emp_ls5_work_group');
	
//$sql= "select emp_mst.emp_mst_empl_id,emp_mst.emp_mst_name from emp_mst where site_cd ='".$site_cd."'";

$sql= " select Distinct Selected = '0',
			Seq = ( CASE WHEN emp_det.emp_det_work_grp IS NULL THEN 0 ELSE 1 END ),
			emp_det.emp_det_work_grp,
			emp_mst.emp_mst_empl_id,  
			emp_mst.emp_mst_title,  
			emp_mst.emp_mst_name,         
			emp_det.emp_det_email_id,
			emp_det.emp_det_ira_idno,
			WOASSIGN = ( 	SELECT 		COALESCE(COUNT(DISTINCT wko_mst_wo_no), 0) 
								FROM			wko_mst (NOLOCK),
												wko_det (NOLOCK),
												wrk_sts (NOLOCK)
								WHERE		wko_mst.site_cd = wko_det.site_cd
								AND			wko_mst.RowID = wko_det.mst_RowID
								AND			wko_mst.site_cd = wrk_sts.site_cd
								AND 			wko_mst.wko_mst_status = wrk_sts.wrk_sts_status
								AND			wrk_sts.wrk_sts_typ_cd  NOT IN ('COMPLETE', 'CANCEL', 'FORCE-CLOSE', 'CLOSE')
								AND			wko_det.wko_det_assign_to = emp_mst.emp_mst_empl_id ),
			TotalWO_Today = ( 	SELECT 		COALESCE(COUNT(DISTINCT wko_mst_wo_no), 0) 
								FROM			wko_mst (NOLOCK),
												wko_det (NOLOCK),
												wrk_sts (NOLOCK),
												wko_ls7 (NOLOCK)
								WHERE		wko_mst.site_cd = wko_det.site_cd
								AND			wko_mst.RowID = wko_det.mst_RowID
								AND			wko_mst.site_cd = wrk_sts.site_cd
								AND 			wko_mst.wko_mst_status = wrk_sts.wrk_sts_status
								AND			wrk_sts.wrk_sts_typ_cd  NOT IN ('COMPLETE', 'CANCEL', 'FORCE-CLOSE', 'CLOSE')
								AND 			wko_mst.site_cd = wko_ls7.site_cd
								AND			wko_mst.RowID = wko_ls7.mst_RowID
								AND 			wko_ls7_wo_org_date >= dateadd(day, datediff(day, 0, getdate()),0)
								AND 			wko_ls7_wo_org_date < dateadd(day, datediff(day, 0, getdate()),1)
								AND			wko_det.wko_det_assign_to = emp_mst.emp_mst_empl_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
INNER JOIN emp_sts (NOLOCK) ON emp_mst.site_cd = emp_sts.site_cd AND emp_mst.emp_mst_status = emp_sts.emp_sts_status
LEFT JOIN emp_ls5 (NOLOCK) ON emp_mst.site_cd = emp_ls5.site_cd AND emp_mst.RowID = emp_ls5.mst_RowID
WHERE 	emp_sts.emp_sts_typ_cd = 'ACTIVE'
AND 	emp_det.emp_det_foreman = '1'
".$where_work_group."
AND		emp_mst.site_cd = '".$site_cd."'";

$stmt = sqlsrv_query( $conn, $sql);



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

function returnData($json){
	//echo $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' => $json);
	
	echo json_encode($returnData);
		
}

	sqlsrv_free_stmt( $stmt);

	sqlsrv_close( $conn);	

 
?>