<?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'];
$ast_mst_asset_shortdesc	= $data['ast_mst_asset_shortdesc'];
$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_dept				= $data['wkr_mst_dept'];
$wkr_mst_org_date			= $data['wkr_mst_org_date'];
$wkr_mst_due_date			= $data['wkr_mst_due_date'];
$createby					= $data['createby'];
$wkr_mst_wr_no				= $data['wkr_mst_wr_no'];
$wkr_mst_wr_status 			= $data['wkr_mst_wr_status'];
$emp_det_work_grp 			= $data['emp_det_work_grp'];
$emp_id						= $data['emp_id'];
$sortOrder 				= $data['sortOrder'];

$page 						= $data['page'];
$pageSize 					= 10;


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($ast_mst_asset_shortdesc)){
	
	$where_ast_mst_asset_shortdesc = "  AND	ast_mst_asset_shortdesc like '%".$ast_mst_asset_shortdesc."%'  ";
	
}else{
	
	$where_ast_mst_asset_shortdesc = "";
	
}


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_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 = '".$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 = '".$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 = '".$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 = '".$wkr_mst_chg_costcenter."'";
	
}else{
	
	$where_wkr_mst_chg_costcenter = "";
	
}

if (!empty($wkr_mst_dept)){
	
	$where_wkr_mst_dept = "  AND wkr_mst.wkr_mst_dept = '".$wkr_mst_dept."'";
	
}else{
	
	$where_wkr_mst_dept = "";
	
}

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 (!empty($createby)){
	
	$where_createby = "AND wkr_mst.wkr_mst_create_by = '".$createby."' ";
	
}else{
	
	$where_createby = "";
	
}


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($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 ="";
}


$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  			= COALESCE(mst_war_desc+' / '+ast_loc_desc+' / '+ast_lvl_desc,''),
				wkr_mst_assetlocn  			= COALESCE(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,''),
				wkr_mst_work_type			= COALESCE(wkr_mst_work_type,''),
				wkr_mst_work_class			= COALESCE(wkr_mst_work_class,''),	
				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."' )".
				$where_wkr_mst_assetno.
				$where_ast_mst_asset_shortdesc.
				$where_wkr_mst_wr_descs.
				$where_wkr_mst_originator.
				$where_wkr_mst_work_area.
				$where_wko_mst_asset_location.
				$where_wko_mst_asset_level.
				$where_wkr_mst_chg_costcenter.
				$where_wkr_mst_dept.
				$where_org_date.
				$where_createby.
				$where_wr_status.
				$where_work_group.
				$where_wkr_mst_wr_no."order by wkr_mst_wr_no ".$sortOrder."	  OFFSET ".($page  -1)*$pageSize." 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_ast_mst_asset_shortdesc.
							$where_wkr_mst_wr_descs.
							$where_wkr_mst_originator.
							$where_wkr_mst_work_area.
							$where_wko_mst_asset_location.
							$where_wko_mst_asset_level.
							$where_wkr_mst_chg_costcenter.
							$where_wkr_mst_dept.
							$where_org_date.
							$where_createby.
							$where_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);
	exit();	
}


 
?>