<?php
// get these values from your DB.
require_once('config.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'];
$service_type =$data['service_type'];



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


 if (!empty($service_type)){
	
	$where_service_type = "  AND wkr_mst.wkr_mst_service = '".$service_type."'";
	
}else{
	
	$where_service_type = "";
	
}


$sql ="Select  	wkr_mst_originator 			= COALESCE(wkr_mst_originator+':'+emp_mst1.emp_mst_name,'' ), 
				wkr_det_varchar4			= COALESCE(wkr_det.wkr_det_varchar4,''),
				wkr_det_varchar5			= COALESCE(wkr_det.wkr_det_varchar5,''),
				wkr_mst_phone 				= COALESCE(wkr_mst_phone,''),	
				wkr_det_varchar10			= COALESCE(wkr_det.wkr_det_varchar10,''),
				wkr_mst_work_class			= COALESCE(wkr_mst.wkr_mst_work_class,''),
				
				wkr_mst_service				= COALESCE(wkr_mst.wkr_mst_service,''),
				wkr_mst_wr_no				= COALESCE(wkr_mst_wr_no,''),	
				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_org_date			= COALESCE(wkr_mst_org_date,''), 	
				wkr_mst_due_date			= COALESCE(wkr_mst_due_date,''),			
				wkr_det_varchar7			= COALESCE(wkr_det.wkr_det_varchar7,''),
				wkr_det_varchar8			= COALESCE(wkr_det.wkr_det_varchar8,''),
				wkr_det_varchar9			= COALESCE(wkr_det.wkr_det_varchar9,''),
				wkr_mst_wr_descs			= COALESCE(wkr_mst_wr_descs,''),
				
				wkr_mst_assetno 			= COALESCE(wkr_mst_assetno,''),
				ast_mst_asset_shortdesc    	= COALESCE(ast_mst_asset_shortdesc,''),
				ast_det_varchar5			= COALESCE(ast_det_varchar5,''),	
				wkr_mst_chg_costcenter		= COALESCE(wkr_mst_chg_costcenter+':'+cf_cost_center.descs,''), 
				wkr_mst_work_area  			= COALESCE(wkr_mst_work_area +':'+mst_war_desc,''),		
				wkr_mst_projectid  			= COALESCE(wkr_mst_projectid,''),	
				wkr_mst_location  			= COALESCE(wkr_mst_location+':'+ast_lvl_desc,''),
				wkr_mst_assetlocn			= COALESCE(wkr_mst_assetlocn,''),
				wkr_det_note1				= COALESCE(wkr_det.wkr_det_note1,''),


				wkr_mst.site_cd,				
				
				wkr_mst_work_group			= COALESCE(wkr_mst_work_group+':'+wrk_grp.wrk_grp_desc,''),
				wkr_mst_taken_by   			= COALESCE(wkr_mst_taken_by,''),
				wkr_mst_fault_code			= COALESCE(wkr_mst_fault_code+':'+wrk_flt_desc,''), 
				wkr_mst_wr_status			= COALESCE(wkr_mst_wr_status,''),	
				wkr_mst_wo_status			= COALESCE(wkr_mst_wo_status,''),
				wkr_mst_work_type 			= COALESCE(wkr_mst_work_type,''),
				wkr_mst_work_class			= COALESCE(wkr_mst_work_class,''),				
				wkr_mst_approved   			= COALESCE(wkr_mst_approved,''),				
				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,''),  
				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,''),	
				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  	 

			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
			AND			wkr_mst.wkr_mst_service = wrk_pri.wrk_pri_service
			
			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		ast_det (NOLOCK)
			ON			wkr_mst.site_cd = ast_det.site_cd
			AND			wkr_mst.rowid = ast_det.mst_rowid
			
					
					
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_service_type.
					$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_service_type.									
									$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();	
}


 
?>