<?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_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'];


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 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_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_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 like '%".$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_work_group			= COALESCE(wkr_mst_work_group + ' : ' + wrk_grp.wrk_grp_desc,''),					
				wkr_mst_org_date			= COALESCE(wkr_mst_org_date,''),
				wkr_mst_taken_by   			= COALESCE(wkr_mst_taken_by,''),
				wkr_mst_originator 			= COALESCE(wkr_mst_originator + ' : ' + emp_mst1.emp_mst_name,'' ), 	
				wkr_mst_phone 				= COALESCE(wkr_mst_phone,''),					
				wkr_mst_orig_priority 		= COALESCE(wkr_mst_orig_priority + ' : ' + wrk_pri.wrk_pri_desc + ' - ' + cast(wrk_pri.wrk_pri_due_date_count as varchar), '') , 
				wkr_mst_due_date			= COALESCE(wkr_mst_due_date,''),	
				wkr_mst_fault_code			= COALESCE(wkr_mst_fault_code + ' : ' + wrk_flt_desc,''),    
				wkr_mst_wr_descs			= COALESCE(wkr_mst_wr_descs,''),	
				wkr_mst_assetno 			= COALESCE(wkr_mst_assetno,''),
				ast_mst_asset_shortdesc     = COALESCE(LEFT(CONCAT_WS(' / ',NULLIF(NULLIF(LTRIM(RTRIM(ast_mst_asset_shortdesc)), ''), '-'),NULLIF(NULLIF(LTRIM(RTRIM(wkr_mst_org_locn)), ''), '-')),80),''),
				wkr_mst_wr_status			= COALESCE(wkr_mst_wr_status,''),	
				wkr_mst_wo_status			= COALESCE(wko_mst.wko_mst_status + ' : ' + wrk_sts.wrk_sts_desc,''),
				wkr_mst_work_type 			= COALESCE(wkr_mst_work_type + ' : ' + wrk_typ.wrk_typ_desc,''),
				wkr_mst_work_class			= COALESCE(wkr_mst_work_class + ' : ' + wrk_cls.wrk_cls_desc,''),
				wkr_mst_chg_costcenter		= COALESCE(wkr_mst_chg_costcenter + ' : ' + cf_cost_center.descs,''), 
				wkr_mst_approved   			= COALESCE(wkr_mst_approved,''),	
				wkr_mst_location  			= COALESCE(wkr_mst_location + ' : ' + ast_lvl_desc,''),
				wkr_mst_assetdesc			= COALESCE(wkr_mst_assetdesc,''),
				wkr_mst_projectid  			= COALESCE(wkr_mst_projectid,''),		
				wkr_mst_work_area  			= COALESCE(wkr_mst_work_area + ' : ' + mst_war_desc,''),	
				wkr_mst_planner   			= COALESCE(wkr_mst_planner,''),
				wkr_mst_assetlocn  			= COALESCE(wkr_mst_assetlocn + ' : ' + ast_loc_desc,''),
				wkr_mst_requestor			= COALESCE(wkr_mst_requestor,''),
				wkr_mst_requestor_phone 	= COALESCE(wkr_mst_requestor_phone,''),   
				wkr_mst_org_locn    		= COALESCE(wkr_mst_org_locn,'') ,	
				wkr_mst_dept     			= COALESCE(wkr_mst_dept,''),		
				wkr_mst_capital_project    	= COALESCE(wkr_mst_capital_project,''), 		
				wkr_mst_temp_asset			= COALESCE(wkr_mst_temp_asset,''),
				wkr_mst_email_notification 	= COALESCE(wkr_mst_email_notification,''),
				wkr_det_wo  				= COALESCE(wkr_det_wo,''),	
				wkr_det_approver     		= COALESCE(wkr_det_approver + ' : ' + emp_mst2.emp_mst_name,''),  
				wkr_det_appr_date			= COALESCE(wkr_det_appr_date,''),
				wkr_det_reject_desc			= COALESCE(wkr_det_reject_desc,''),
				wkr_det_reject_by   		= COALESCE(wkr_det_reject_by + ' : ' + emp_mst3.emp_mst_name,''),	
				wkr_det_reject_date   		= COALESCE(wkr_det_reject_date,''),
				wkr_mst.audit_user,     			
				wkr_mst.audit_date,				
				wkr_mst.wkr_mst_create_by,   		
				wkr_mst.wkr_mst_create_date ,		
				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		emp_mst emp_mst2(NOLOCK)						
			ON			wkr_det.site_cd = emp_mst2.site_cd
			AND			wkr_det.wkr_det_approver = emp_mst2.emp_mst_empl_id

			LEFT 
			OUTER 
			JOIN		emp_mst emp_mst3(NOLOCK)						
			ON			wkr_det.site_cd = emp_mst3.site_cd
			AND			wkr_det.wkr_det_reject_by = emp_mst3.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_mst (NOLOCK)
			ON			wkr_det.site_cd = wko_mst.site_cd
			AND			wkr_det.wkr_det_wo = wko_mst.wko_mst_wo_no
			
			LEFT 
			OUTER 
			JOIN		wrk_sts (NOLOCK)
			ON			wko_mst.site_cd = wrk_sts.site_cd
			AND			wko_mst.wko_mst_status = wrk_sts.wrk_sts_status
			
			LEFT 
			OUTER 
			JOIN		wrk_typ (NOLOCK)
			ON			wkr_mst.site_cd = wrk_typ.site_cd
			AND			wkr_mst.wkr_mst_work_type = wrk_typ.wrk_typ_typ_cd
			
			LEFT 
			OUTER 
			JOIN		wrk_cls (NOLOCK)
			ON			wkr_mst.site_cd = wrk_cls.site_cd
			AND			wkr_mst.wkr_mst_work_class = wrk_cls.wrk_cls_cls_cd
			
			
					
					
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_org_date.
					$where_createby.
					$where_wr_status.
					//$where_work_group.
					$where_wkr_mst_wr_no;



		$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_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();	
}


 
?>