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

$json = file_get_contents('php://input');

// Converts it into a PHP object
$data = json_decode($json, true);

$site_cd 				= $data['site_cd'];
$wkr_mst_assetno		= $data['wkr_mst_assetno'];
$wkr_mst_wr_descs		= $data['wkr_mst_wr_descs'];
$wkr_mst_originator		= $data['wkr_mst_originator'];
$wkr_mst_work_area		= $data['wkr_mst_work_area'];
$wko_mst_asset_location	= $data['wko_mst_asset_location'];
$wko_mst_asset_level	= $data['wko_mst_asset_level'];
$wkr_mst_chg_costcenter	= $data['wkr_mst_chg_costcenter'];
$wkr_mst_wr_no			= $data['wkr_mst_wr_no'];
$wkr_mst_org_date		= $data['wkr_mst_org_date'];
$wkr_mst_due_date		= $data['wkr_mst_due_date'];
$wkr_mst_wr_status 		= $data['wkr_mst_wr_status'];
$wkr_mst_work_group 	= $data['emp_det_work_grp'];
$emp_id					= $data['emp_id'];
$sortOrder 				= $data['sortOrder'];

$page 					= $data['page'];
$pageSize 	= 20;
$offset = ($page - 1) * $pageSize;

if (!empty($wkr_mst_assetno)){
	
	$where_wkr_mst_assetno = "  AND wkr_mst.wkr_mst_assetno like '%".$wkr_mst_assetno."%'";
	
}else{
	
	$where_wkr_mst_assetno = "";
	
}

if (!empty($wkr_mst_wr_descs)){
	
	$where_wkr_mst_wr_descs = "  AND wkr_mst.wkr_mst_wr_descs like '%".$wkr_mst_wr_descs."%'";
	
}else{
	
	$where_wkr_mst_wr_descs = "";
	
}
if (!empty($wkr_mst_work_group)){
	
	$where_wkr_mst_work_group = "  AND wkr_mst.wkr_mst_work_group like '%".$wkr_mst_work_group."%'";
	
}else{
	
	$where_wkr_mst_work_group = "";
	
}
if (!empty($wkr_mst_originator)){
	
	$where_wkr_mst_originator = " AND wkr_mst.wkr_mst_originator like '%".$wkr_mst_originator."%'";
	
}else{
	
	$where_wkr_mst_originator = "";
	
}

if (!empty($wkr_mst_work_area)){
	
	$where_wkr_mst_work_area = "  AND wkr_mst.wkr_mst_work_area like '%".$wkr_mst_work_area."%'";
	
}else{
	
	$where_wkr_mst_work_area = "";
	
}

if (!empty($wko_mst_asset_location)){
	
	$where_wko_mst_asset_location = "  AND	wkr_mst.wkr_mst_assetlocn like '%".$wko_mst_asset_location."%'";
	
}else{
	
	$where_wko_mst_asset_location = "";
	
}

if (!empty($wko_mst_asset_level)){
	
	$where_wko_mst_asset_level = "  AND	wkr_mst.wkr_mst_location like '%".$wko_mst_asset_level."%'";
	
}else{
	
	$where_wko_mst_asset_level = "";
	
}

if (!empty($wkr_mst_chg_costcenter)){
	
	$where_wkr_mst_chg_costcenter = "  AND wkr_mst.wkr_mst_chg_costcenter like '%".$wkr_mst_chg_costcenter."%'";
	
}else{
	
	$where_wkr_mst_chg_costcenter = "";
	
}

if (!empty($wkr_mst_wr_no)){
	
	$where_wkr_mst_wr_no = "  AND wkr_mst.wkr_mst_wr_no like '%".$wkr_mst_wr_no."%'";
	
}else{
	
	$where_wkr_mst_wr_no = "";
	
}

if (!empty($wkr_mst_org_date)){
	
	$where_org_date = "AND CONVERT(DATETIME, CONVERT(VARCHAR, wkr_mst.wkr_mst_org_date , 101)) >= CONVERT(DATETIME, CONVERT(VARCHAR, '".$wkr_mst_org_date."', 101)) 
						AND CONVERT(DATETIME, CONVERT(VARCHAR, wkr_mst.wkr_mst_org_date , 101)) < CONVERT(DATETIME, CONVERT(VARCHAR, '".$wkr_mst_due_date."', 101))  + 1";
	
}else{
	
	$where_org_date = "AND CONVERT(DATETIME, CONVERT(VARCHAR, '1900-01-01', 101)) < Getdate()";
	
}

if($wkr_mst_wr_status  == "Waiting"){
	$where_wr_status ="AND (wkr_mst_wr_status LIKE ('%W%') OR wkr_mst.wkr_mst_wr_status IS NULL)";
}else if($wkr_mst_wr_status  == "Approve"){
	$where_wr_status ="AND (wkr_mst_wr_status LIKE ('A') OR wkr_mst.wkr_mst_wr_status IS NULL)";
}else if($wkr_mst_wr_status  == "Disapprove"){
	$where_wr_status ="AND (wkr_mst_wr_status LIKE ('D') OR wkr_mst.wkr_mst_wr_status IS NULL)";
}else{
	$where_wr_status ="";
}
 
 
 //$where_work_group = get_emp_ls5($site_cd, $emp_id, 'wkr_mst_work_group');

$sql ="Select   wkr_mst.site_cd,				
				wkr_mst_wr_no				= COALESCE(wkr_mst_wr_no,''),
				wkr_mst_wr_status			= COALESCE(wkr_mst_wr_status,''),
				wkr_mst_org_date			= COALESCE(wkr_mst_org_date,''),		
				wkr_mst_originator 			= COALESCE(emp_mst1.emp_mst_name,'' ), 
				wkr_mst_assetno 			= COALESCE(wkr_mst_assetno,''),
				ast_mst_asset_shortdesc    	= COALESCE(ast_mst_asset_shortdesc,''),				
				wkr_mst_work_area = 
				CONCAT_WS(' / ',
					NULLIF(LTRIM(RTRIM(mst_war_desc)), ''),
					NULLIF(LTRIM(RTRIM(ast_loc_desc)), ''),
					NULLIF(LTRIM(RTRIM(ast_lvl_desc)), ''),
					NULLIF(LTRIM(RTRIM(wkr_mst_org_locn)), '')
				),

				wkr_mst_assetlocn = 
				CONCAT_WS(' / ',
					NULLIF(LTRIM(RTRIM(wkr_mst_org_locn)), ''),
					NULLIF(LTRIM(RTRIM(ast_loc_desc)), '')
				),
				wkr_mst_ast_lvl  			= COALESCE(ast_lvl_desc,''),				
				wkr_mst_work_group			= COALESCE(wrk_grp.wrk_grp_desc,''),	
				wkr_mst_due_date			= COALESCE(wkr_mst_due_date,''),
				wkr_mst_wr_descs			= COALESCE(wkr_mst_wr_descs,''),	
				wkr_det_approver     		= COALESCE(wkr_det_approver,''),  
				wkr_det_appr_date			= COALESCE(wkr_det_appr_date,''),
				wko_det_assign_to     		= COALESCE(emp_mst2.emp_mst_name,''),  
				wkr_mst.RowID  						
		FROM 	wkr_mst (NOLOCK)  	 

				INNER JOIN wkr_det (NOLOCK)
				ON		wkr_mst.site_cd = wkr_det.site_cd
				AND		wkr_mst.rowid = wkr_det.mst_rowid    

				LEFT 
				OUTER 
				JOIN		emp_mst emp_mst1(NOLOCK)						
				ON			wkr_mst.site_cd = emp_mst1.site_cd
				AND			wkr_mst.wkr_mst_originator = emp_mst1.emp_mst_empl_id
				
				LEFT 
				OUTER 
				JOIN		wrk_flt (NOLOCK)
				ON			wkr_mst.site_cd = wrk_flt.site_cd
				AND			wkr_mst.wkr_mst_fault_code = wrk_flt.wrk_flt_fault_cd
				
				LEFT 
				OUTER 
				JOIN		ast_mst (NOLOCK)
				ON			wkr_mst.site_cd = ast_mst.site_cd
				AND			wkr_mst.wkr_mst_assetno = ast_mst.ast_mst_asset_no
				
				LEFT 
				OUTER 
				JOIN		wrk_pri (NOLOCK)
				ON			wkr_mst.site_cd = wrk_pri.site_cd
				AND			wkr_mst.wkr_mst_orig_priority = wrk_pri.wrk_pri_pri_cd
				
				LEFT 
				OUTER 
				JOIN		cf_cost_center (NOLOCK)
				ON			wkr_mst.site_cd = cf_cost_center.site_cd
				AND			wkr_mst.wkr_mst_chg_costcenter = cf_cost_center.costcenter
				
				LEFT 
				OUTER 
				JOIN		mst_war (NOLOCK)
				ON			wkr_mst.site_cd = mst_war.site_cd
				AND			wkr_mst.wkr_mst_work_area = mst_war.mst_war_work_area
				
				LEFT 
				OUTER 
				JOIN		ast_loc (NOLOCK)
				ON			wkr_mst.site_cd = ast_loc.site_cd
				AND			wkr_mst.wkr_mst_assetlocn = ast_loc.ast_loc_ast_loc
				
				
				LEFT 
				OUTER 
				JOIN		ast_lvl (NOLOCK)
				ON			wkr_mst.site_cd = ast_lvl.site_cd
				AND			wkr_mst.wkr_mst_location = ast_lvl.ast_lvl_ast_lvl
				
				
				
				LEFT 
				OUTER 
				JOIN		wrk_grp (NOLOCK)
				ON			wkr_mst.site_cd = wrk_grp.site_cd
				AND			wkr_mst.wkr_mst_work_group = wrk_grp.wrk_grp_grp_cd
				
				LEFT 
				OUTER 
				JOIN		wko_det (NOLOCK)
				ON			wkr_mst.site_cd = wko_det.site_cd
				AND			wkr_mst.wkr_mst_wr_no = wko_det.wko_det_wr_no
				
				LEFT 
				OUTER 
				JOIN		emp_mst emp_mst2(NOLOCK)						
				ON			wko_det.site_cd = emp_mst2.site_cd
				AND			wko_det.wko_det_assign_to = emp_mst2.emp_mst_empl_id
			
					
					
			WHERE	( wkr_mst.site_cd = '".$site_cd."' ) ".
					$where_wkr_mst_assetno.					
					$where_wkr_mst_originator.			
					$where_wkr_mst_work_area.
					$where_wko_mst_asset_location.									
					$where_wko_mst_asset_level.
					$where_wkr_mst_wr_descs.
					$where_wkr_mst_chg_costcenter.
					$where_org_date.
					$where_wr_status.
					//$where_work_group.
					$where_wkr_mst_work_group.
					$where_wkr_mst_wr_no." order by wkr_mst_wr_no ".$sortOrder." OFFSET ".$offset." ROWS FETCH NEXT ".$pageSize." ROWS ONLY";



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

		if( !$stmt_1 ) {
			
			 $error_message = "Error selecting table Preventive Type";
			 returnError($error_message);
			 die( print_r( sqlsrv_errors(), true));
			 
		}

		$json= array();

		do {
			
			while ($row = sqlsrv_fetch_array($stmt_1, SQLSRV_FETCH_ASSOC)) {		
			
			$json[] = $row;		
			
			}
			
		} while ( sqlsrv_next_result($stmt_1) );
 
 
	$sql_1_cnt="SELECT	waiting = COALESCE(SUM(waiting),'' ), 
						approve = COALESCE(SUM(approve),'' ),
						disapprove = COALESCE(SUM(disapprove),'' ) 
						From 
						(SELECT waiting = SUM(CASE wkr_mst.wkr_mst_wr_status WHEN 'W' THEN 1 ELSE 0 END),
						approve = SUM(CASE wkr_mst.wkr_mst_wr_status WHEN 'A' THEN 1 ELSE 0 END),
						disapprove = SUM(CASE wkr_mst.wkr_mst_wr_status WHEN 'D' THEN 1 ELSE 0 END)
										
						FROM 		wkr_mst (NOLOCK) 

						INNER JOIN wkr_det (NOLOCK)
						ON		wkr_mst.site_cd = wkr_det.site_cd
						AND		wkr_mst.rowid = wkr_det.mst_rowid    

						LEFT 
						OUTER 
						JOIN		emp_mst emp_mst1(NOLOCK)						
						ON			wkr_mst.site_cd = emp_mst1.site_cd
						AND			wkr_mst.wkr_mst_originator = emp_mst1.emp_mst_empl_id
						
						LEFT 
						OUTER 
						JOIN		wrk_flt (NOLOCK)
						ON			wkr_mst.site_cd = wrk_flt.site_cd
						AND			wkr_mst.wkr_mst_fault_code = wrk_flt.wrk_flt_fault_cd
						
						LEFT 
						OUTER 
						JOIN		ast_mst (NOLOCK)
						ON			wkr_mst.site_cd = ast_mst.site_cd
						AND			wkr_mst.wkr_mst_assetno = ast_mst.ast_mst_asset_no 
					
				WHERE 	( wkr_mst.site_cd = '".$site_cd."' ) ".
						$where_wkr_mst_assetno.									
						$where_wkr_mst_originator.			
						$where_wkr_mst_work_area.
						$where_wko_mst_asset_location.									
						$where_wko_mst_asset_level.
						$where_wkr_mst_wr_descs.
						$where_wkr_mst_chg_costcenter.
						$where_org_date.	
						//$where_work_group.
						$where_wkr_mst_work_group.									
						$where_wkr_mst_wr_no.")	as x";

			$stmt_1_cnt = sqlsrv_query( $conn, $sql_1_cnt);

			if( !$stmt_1_cnt ) {
				 $error_message = "Error selecting table count Preventive";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
				 
			}
			do {
				 while ($row = sqlsrv_fetch_array($stmt_1_cnt, SQLSRV_FETCH_ASSOC)) {		
					$waiting = $row['waiting'];
					$approve = $row['approve'];
					$disapprove = $row['disapprove'];
				
				 }
			} while ( sqlsrv_next_result($stmt_1_cnt) );
	 


if ($valid) {

	returnData($json,$waiting,$approve,$disapprove);	
	
}


function returnData($json,$waiting,$approve,$disapprove){
	$json1 = json_encode($json);
	//echo$json;
	 
	if(empty(json_decode($json1,1))) {
		 $returnData = array(
		'status' => 'SUCCESS',
		'message' => 'No records found',
		'waiting'=>$waiting,
		'approve'=>$approve,
		'disapprove'=>$disapprove,
		'data' => $json);
	}else{
		$returnData = array(
		'status' => 'SUCCESS',
		'message' => 'Successfully',
		'waiting'=>$waiting,
		'approve'=>$approve,
		'disapprove'=>$disapprove,
		'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);
		
}


 
?>