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

$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."'";

if (preg_match('/^([A-Za-z]+)/', $wkr_mst_wr_no, $matches)) {
	$prefix = $matches[1]; 
	if ($prefix == 'PJR' || $prefix == 'PR') {
		
		$where_wr_app ="AND emp_det.emp_det_invoice_approver = '1'";
		
	}else{
		
		$where_wr_app ="AND emp_det.emp_det_foreman = '1'";
	}
		
}

	$sql_wrk_mst="Select wkr_mst_work_group  From wkr_mst where site_cd = '".$site_cd."' And wkr_mst_wr_no = '".$wkr_mst_wr_no."'" ;		
	$stmt_wrk_mst = sqlsrv_query( $conn, $sql_wrk_mst);
	if( !$stmt_wrk_mst ) {
		 $error_message = "Error selecting table (wkr_mst)";
		 returnError($error_message);
		 die( print_r( sqlsrv_errors(), true)); 
	}
	do{
		
		while ($row = sqlsrv_fetch_array($stmt_wrk_mst, SQLSRV_FETCH_ASSOC)) {
			$wkr_mst_work_group = $row['wkr_mst_work_group'];
		}
		
	}while ( sqlsrv_next_result($stmt_wrk_mst) );
	

$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_date3 >= dateadd(day, datediff(day, 0, getdate()),0)
								AND 			wko_ls7_date3 < 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'

AND (
        emp_det.emp_det_work_grp = '".$wkr_mst_work_group."'
        OR emp_det.emp_det_ira_idno = '".$wkr_mst_work_group."'
        OR emp_det.emp_det_work_area = '".$wkr_mst_work_group."'
        OR emp_det.emp_det_liability_acc = '".$wkr_mst_work_group."'
      )

AND		emp_mst.site_cd = '".$site_cd."'".
$where_wr_app."";

$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);	

 
?>