<?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'];
$searchTerm 		= $_REQUEST['searchTerm'];
$Folder				= $_REQUEST['Folder'];
$dvc_id				= $_REQUEST['dvc_id'];
$MobileURL			= $_REQUEST['MobileURL'];
$wkr_mst_wr_status 	= $_REQUEST['wkr_mst_wr_status'];

$page 				= $_REQUEST['page'];
$pageSize 			= 50;

$offset 			= ($page - 1) * $pageSize;


if($wkr_mst_wr_status  == "Waiting"){
	$where_wr_status ="AND (wkr_mst_wr_status LIKE ('%W%'))";
}else if($wkr_mst_wr_status  == "Approve"){
	$where_wr_status ="AND (wkr_mst_wr_status LIKE ('A'))";
}else if($wkr_mst_wr_status  == "Disapprove"){
	$where_wr_status ="AND (wkr_mst_wr_status LIKE ('D'))";
}else{
	$where_wr_status ="";
}

 

$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(wko_det.wko_det_assign_to,''),  
					
				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
			
					
					
		WHERE	( wkr_mst.site_cd = '".$site_cd."'  ) 
		AND 	( 	wkr_mst.wkr_mst_assetno LIKE '%".$searchTerm. "%' OR	
					ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
					wkr_mst.wkr_mst_wr_no LIKE '%".$searchTerm. "%' OR 
					wkr_mst.wkr_mst_wr_descs LIKE '%".$searchTerm. "%' OR 
					wkr_mst.wkr_mst_work_group LIKE '%".$searchTerm. "%' OR 
					wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
					wkr_mst.wkr_mst_work_area LIKE '%".$searchTerm. "%' OR 
					mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
					ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
					wkr_mst.wkr_mst_assetlocn LIKE '%".$searchTerm. "%' OR
					ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' 
				)".$where_wr_status."order by wkr_mst_wr_no OFFSET ".$offset." ROWS FETCH NEXT ".$pageSize." ROWS ONLY";


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

		if( !$stmt ) {
			
			 $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, SQLSRV_FETCH_ASSOC)) {		
			
				$json[] = $row;		
			
			}
			
		} while ( sqlsrv_next_result($stmt) );
 
 
if ($stmt) {

	returnData($json);	
	
}


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


 
?>