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

$site_cd = $_REQUEST['site_cd'];

$wrk_sts_typ_cd =$_REQUEST['wrk_sts_typ_cd']; 
$wko_mst_assetno= $_REQUEST['wko_mst_assetno'];


if($wrk_sts_typ_cd  == "Open"){
	$sts_typ_cd ="AND (wrk_sts_typ_cd NOT IN ('CLOSE', 'COMPLETE', 'FORCE-CLOSE', 'CANCEL'))";
}else if($wrk_sts_typ_cd  == "Complete"){
	$sts_typ_cd ="AND (wrk_sts_typ_cd IN ('COMPLETE'))";
}else if($wrk_sts_typ_cd  == "Close"){
	$sts_typ_cd ="AND (wrk_sts_typ_cd IN ('CLOSE', 'FORCE-CLOSE'))";
}else{
	$sts_typ_cd ="AND (wrk_sts_typ_cd LIKE '%%')";
}
 
$sql_3="Select 
					wko_mst.site_cd,   
					wko_det_work_grp			= COALESCE(wko_det_work_grp,''),
					wko_mst.wko_mst_wo_no,     			
					wko_mst_originator 			= COALESCE(CASE WHEN emp_mst1.emp_mst_name IS NULL THEN wko_mst_originator WHEN emp_mst1.emp_mst_name IS NOT NULL THEN wko_mst_originator+':'+emp_mst1.emp_mst_name END,'') ,          			
					wko_mst_phone				= COALESCE(wko_mst_phone,'') ,     
					wko_mst_asset_level			= wko_mst_asset_level+':'+ast_lvl_desc,     	
					wko_mst_assetno 			= COALESCE(wko_mst_assetno,''), 
					wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''),
					wko_mst_flt_code			= wko_mst_flt_code+':'+wrk_flt_desc,      		
					wko_mst_status 				= wko_mst_status +':'+ wrk_sts_desc,      		
					wko_mst.wko_mst_due_date,     			
					wko_mst_descs				= COALESCE(wko_mst_descs,''), 
					wko_mst_ast_cod				= COALESCE(wko_mst_ast_cod,''),				
					wko_mst.wko_mst_org_date,     		
					wko_mst_chg_costcenter		= COALESCE(wko_mst_chg_costcenter+':'+cf_cost_center.descs,''),  
					wko_mst_work_area 			= COALESCE(wko_mst_work_area +':'+mst_war_desc,''),            	
					wko_mst_asset_location 		= COALESCE(wko_mst_asset_location+':'+ast_loc_desc,''),		
					wko_mst_asset_group_code 	= COALESCE(wko_mst_asset_group_code+':'+ ast_grp_desc,''),
					wko_mst_orig_priority 		= COALESCE(wko_mst_orig_priority+':'+ wrk_pri_2.wrk_pri_desc+' - '+ cast(wrk_pri_2.wrk_pri_due_date_count as varchar),''),			
					wko_mst_plan_priority 		= COALESCE(wko_mst_plan_priority+':'+ wrk_pri.wrk_pri_desc+' - '+ cast(wrk_pri_2.wrk_pri_due_date_count as varchar),''), 
					wko_mst.audit_user,   
					wko_mst.audit_date,     			
					wko_mst.wko_mst_create_by,  
					wko_mst.wko_mst_create_date,     		   		
					wko_mst.RowID, 
					wrk_sts_typ_cd				= COALESCE(wrk_sts_typ_cd,''),
					wko_mst_type				= COALESCE(wko_mst_type,''),
					wko_det_work_type 			= COALESCE(wko_det_work_type+ ' : ' + wrk_typ.wrk_typ_desc,''),
					wko_det_laccount			= COALESCE(wko_det_laccount+':'+ cf_account_1.descs,''),
					wko_det_caccount 			= COALESCE(wko_det_caccount+':'+ cf_account_2.descs ,''),
					wko_det_maccount			= COALESCE(wko_det_maccount+':'+ cf_account_3.descs,''),
					wko_det_assign_to 			= COALESCE(wko_det_assign_to+':'+emp_mst2.emp_mst_name,''),
					wko_det_varchar9  			= COALESCE(wko_det_varchar9,''),
					wko_det_varchar10  			= COALESCE(wko_det_varchar10,''),
					wko_det_datetime5  			= COALESCE(wko_det_datetime5 ,''),
					ast_mst_asset_shortdesc		= COALESCE(ast_mst_asset_shortdesc,''),					
					assigndate					= COALESCE(wko_ls7.audit_date,'')							
									
									
					FROM 	wko_mst (NOLOCK)     			
			
							INNER JOIN wko_det (NOLOCK)
							ON		wko_mst.site_cd = wko_det.site_cd
							AND		wko_mst.rowid = wko_det.mst_rowid
							
							INNER 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		ast_mst (NOLOCK)
							ON			wko_mst.site_cd = ast_mst.site_cd
							AND			wko_mst.wko_mst_assetno = ast_mst.ast_mst_asset_no
							
							LEFT 
							OUTER 
							JOIN		emp_mst emp_mst1(NOLOCK)						
							ON			wko_mst.site_cd = emp_mst1.site_cd
							AND			wko_mst.wko_mst_originator = emp_mst1.emp_mst_empl_id
							
							
							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
							
							
							LEFT 
							OUTER 
							JOIN		ast_grp (NOLOCK)
							ON			wko_mst.site_cd = ast_grp.site_cd
							AND			wko_mst.wko_mst_asset_group_code = ast_grp.ast_grp_grp_cd
							
							LEFT 
							OUTER 
							JOIN		ast_loc (NOLOCK)
							ON			wko_mst.site_cd = ast_loc.site_cd
							AND			wko_mst.wko_mst_asset_location = ast_loc.ast_loc_ast_loc


							LEFT 
							OUTER 
							JOIN		ast_lvl (NOLOCK)
							ON			wko_mst.site_cd = ast_lvl.site_cd
							AND			wko_mst.wko_mst_asset_level = ast_lvl.ast_lvl_ast_lvl
							
							LEFT 
							OUTER 
							JOIN		mst_war (NOLOCK)
							ON			wko_mst.site_cd = mst_war.site_cd
							AND			wko_mst.wko_mst_work_area = mst_war.mst_war_work_area

							
							LEFT 
							OUTER 
							JOIN		wrk_pri (NOLOCK)
							ON			wko_mst.site_cd = wrk_pri.site_cd
							AND			wko_mst.wko_mst_plan_priority = wrk_pri.wrk_pri_pri_cd

							LEFT 
							OUTER 
							JOIN		wrk_pri wrk_pri_2(NOLOCK)
							ON			wko_mst.site_cd = wrk_pri_2.site_cd
							AND			wko_mst.wko_mst_orig_priority = wrk_pri_2.wrk_pri_pri_cd

							LEFT 
							OUTER 
							JOIN		cf_cost_center (NOLOCK)
							ON			wko_mst.site_cd = cf_cost_center.site_cd
							AND			wko_mst.wko_mst_chg_costcenter = cf_cost_center.costcenter


							LEFT 
							OUTER 
							JOIN		wrk_flt (NOLOCK)
							ON			wko_mst.site_cd = wrk_flt.site_cd
							AND			wko_mst.wko_mst_flt_code = wrk_flt.wrk_flt_fault_cd
							
							
							LEFT 
							OUTER 
							JOIN		wrk_typ (NOLOCK)
							ON			wko_mst.site_cd = wrk_typ.site_cd
							AND			wko_det.wko_det_work_type = wrk_typ.wrk_typ_typ_cd
							
						
							LEFT 
							OUTER 
							JOIN		cf_account cf_account_1(NOLOCK)
							ON			wko_mst.site_cd = cf_account_1.site_cd
							AND			wko_det.wko_det_laccount = cf_account_1.account
							
							LEFT 
							OUTER 
							JOIN		cf_account cf_account_2(NOLOCK)
							ON			wko_mst.site_cd = cf_account_2.site_cd
							AND			wko_det.wko_det_caccount = cf_account_2.account
							
							LEFT 
							OUTER 
							JOIN		cf_account cf_account_3(NOLOCK)
							ON			wko_mst.site_cd = cf_account_3.site_cd
							AND			wko_det.wko_det_maccount = cf_account_3.account
							
							
							LEFT 
							OUTER 
							JOIN		wko_ls7(NOLOCK)
							ON			wko_mst.site_cd = wko_ls7.site_cd
							AND			wko_mst.rowid = wko_ls7.mst_rowid
							AND			wko_det.site_cd = wko_ls7.site_cd
							AND			wko_det_assign_to = wko_ls7.wko_ls7_emp_id
							AND			wko_ls7.RowID = (SELECT MAX(rowid) FROM wko_ls7 
												 WHERE wko_ls7.site_cd = wko_mst.site_cd
												 AND   wko_ls7.mst_rowid = wko_mst.rowid
												 AND   wko_ls7.site_cd = wko_det.site_cd
												 AND   wko_ls7.wko_ls7_emp_id = wko_det.wko_det_assign_to)

					WHERE		( wko_mst.site_cd = '".$site_cd."' )
							AND	( wko_mst.wko_mst_assetno = '".$wko_mst_assetno."')	
							".$sts_typ_cd."
							AND (wko_mst_type IN ('C', 'P'))
							
							
			UNION ALL
			
			Select 
					wko_mst.site_cd,   
					wko_det_work_grp			= COALESCE(wko_det_work_grp,''),
					wko_mst.wko_mst_wo_no,     			
					wko_mst_originator 			= COALESCE(CASE WHEN emp_mst1.emp_mst_name IS NULL THEN wko_mst_originator WHEN emp_mst1.emp_mst_name IS NOT NULL THEN wko_mst_originator+':'+emp_mst1.emp_mst_name END,'') ,          			
					wko_mst_phone				= COALESCE(wko_mst_phone,'') ,     
					wko_mst_asset_level			= wko_mst_asset_level+':'+ast_lvl_desc,     	
					wko_mst_assetno 			= COALESCE(wko_mst_assetno,''), 
					wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''),
					wko_mst_flt_code			= wko_mst_flt_code+':'+wrk_flt_desc,      		
					wko_mst_status 				= wko_mst_status +':'+ wrk_sts_desc,      		
					wko_mst.wko_mst_due_date,     			
					wko_mst_descs				= COALESCE(wko_mst_descs,''), 
					wko_mst_ast_cod				= COALESCE(wko_mst_ast_cod,''),				
					wko_mst.wko_mst_org_date,     		
					wko_mst_chg_costcenter		= COALESCE(wko_mst_chg_costcenter+':'+cf_cost_center.descs,''),  
					wko_mst_work_area 			= COALESCE(wko_mst_work_area +':'+mst_war_desc,''),            	
					wko_mst_asset_location 		= COALESCE(wko_mst_asset_location+':'+ast_loc_desc,''),		
					wko_mst_asset_group_code 	= COALESCE(wko_mst_asset_group_code+':'+ ast_grp_desc,''),
					wko_mst_orig_priority 		= COALESCE(wko_mst_orig_priority+':'+ wrk_pri_2.wrk_pri_desc+' - '+ cast(wrk_pri_2.wrk_pri_due_date_count as varchar),''),			
					wko_mst_plan_priority 		= COALESCE(wko_mst_plan_priority+':'+ wrk_pri.wrk_pri_desc+' - '+ cast(wrk_pri_2.wrk_pri_due_date_count as varchar),''), 
					wko_mst.audit_user,   
					wko_mst.audit_date,     			
					wko_mst.wko_mst_create_by,  
					wko_mst.wko_mst_create_date,     		   		
					wko_mst.RowID, 
					wrk_sts_typ_cd				= COALESCE(wrk_sts_typ_cd,''),
					wko_mst_type				= COALESCE(wko_mst_type,''),
					wko_det_work_type 			= COALESCE(wko_det_work_type+ ' : ' + wrk_typ.wrk_typ_desc,''),
					wko_det_laccount			= COALESCE(wko_det_laccount+':'+ cf_account_1.descs,''),
					wko_det_caccount 			= COALESCE(wko_det_caccount+':'+ cf_account_2.descs ,''),
					wko_det_maccount			= COALESCE(wko_det_maccount+':'+ cf_account_3.descs,''),
					wko_det_assign_to 			= COALESCE(wko_det_assign_to+':'+emp_mst2.emp_mst_name,''),
					wko_det_varchar9  			= COALESCE(wko_det_varchar9,''),
					wko_det_varchar10  			= COALESCE(wko_det_varchar10,''),
					wko_det_datetime5  			= COALESCE(wko_det_datetime5 ,''),
					ast_mst_asset_shortdesc		= COALESCE(ast_mst_asset_shortdesc,''),					
					assigndate					= COALESCE(wko_ls7.audit_date,'')			
									
					FROM 	wko_mst (NOLOCK)     			
			
							INNER JOIN wko_det (NOLOCK)
							ON		wko_mst.site_cd = wko_det.site_cd
							AND		wko_mst.rowid = wko_det.mst_rowid
							
							INNER JOIN	wrk_sts (NOLOCK)
							ON			wko_mst.site_cd = wrk_sts.site_cd
							AND			wko_mst.wko_mst_status = wrk_sts.wrk_sts_status
							
							INNER JOIN 	prm_ast (NOLOCK)
							ON			wko_mst.site_cd = prm_ast.site_cd
							AND			wko_mst.wko_mst_wo_no = prm_ast.prm_ast_wo_no
							AND			wko_mst.wko_mst_pm_grp = prm_ast.prm_ast_grp_cd
									
							LEFT 
							OUTER 
							JOIN		ast_mst (NOLOCK)
							ON			wko_mst.site_cd = ast_mst.site_cd
							AND			wko_mst.wko_mst_assetno = ast_mst.ast_mst_asset_no
							
							LEFT 
							OUTER 
							JOIN		emp_mst emp_mst1(NOLOCK)						
							ON			wko_mst.site_cd = emp_mst1.site_cd
							AND			wko_mst.wko_mst_originator = emp_mst1.emp_mst_empl_id
							
							
							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
							
							
							LEFT 
							OUTER 
							JOIN		ast_grp (NOLOCK)
							ON			wko_mst.site_cd = ast_grp.site_cd
							AND			wko_mst.wko_mst_asset_group_code = ast_grp.ast_grp_grp_cd
							
							LEFT 
							OUTER 
							JOIN		ast_loc (NOLOCK)
							ON			wko_mst.site_cd = ast_loc.site_cd
							AND			wko_mst.wko_mst_asset_location = ast_loc.ast_loc_ast_loc


							LEFT 
							OUTER 
							JOIN		ast_lvl (NOLOCK)
							ON			wko_mst.site_cd = ast_lvl.site_cd
							AND			wko_mst.wko_mst_asset_level = ast_lvl.ast_lvl_ast_lvl
							
							LEFT 
							OUTER 
							JOIN		mst_war (NOLOCK)
							ON			wko_mst.site_cd = mst_war.site_cd
							AND			wko_mst.wko_mst_work_area = mst_war.mst_war_work_area

							
							LEFT 
							OUTER 
							JOIN		wrk_pri (NOLOCK)
							ON			wko_mst.site_cd = wrk_pri.site_cd
							AND			wko_mst.wko_mst_plan_priority = wrk_pri.wrk_pri_pri_cd

							LEFT 
							OUTER 
							JOIN		wrk_pri wrk_pri_2(NOLOCK)
							ON			wko_mst.site_cd = wrk_pri_2.site_cd
							AND			wko_mst.wko_mst_orig_priority = wrk_pri_2.wrk_pri_pri_cd

							LEFT 
							OUTER 
							JOIN		cf_cost_center (NOLOCK)
							ON			wko_mst.site_cd = cf_cost_center.site_cd
							AND			wko_mst.wko_mst_chg_costcenter = cf_cost_center.costcenter


							LEFT 
							OUTER 
							JOIN		wrk_flt (NOLOCK)
							ON			wko_mst.site_cd = wrk_flt.site_cd
							AND			wko_mst.wko_mst_flt_code = wrk_flt.wrk_flt_fault_cd
							
							
							LEFT 
							OUTER 
							JOIN		wrk_typ (NOLOCK)
							ON			wko_mst.site_cd = wrk_typ.site_cd
							AND			wko_det.wko_det_work_type = wrk_typ.wrk_typ_typ_cd
						
							LEFT 
							OUTER 
							JOIN		cf_account cf_account_1(NOLOCK)
							ON			wko_mst.site_cd = cf_account_1.site_cd
							AND			wko_det.wko_det_laccount = cf_account_1.account
							
							LEFT 
							OUTER 
							JOIN		cf_account cf_account_2(NOLOCK)
							ON			wko_mst.site_cd = cf_account_2.site_cd
							AND			wko_det.wko_det_caccount = cf_account_2.account
							
							LEFT 
							OUTER 
							JOIN		cf_account cf_account_3(NOLOCK)
							ON			wko_mst.site_cd = cf_account_3.site_cd
							AND			wko_det.wko_det_maccount = cf_account_3.account
							
							
							LEFT 
							OUTER 
							JOIN		wko_ls7(NOLOCK)
							ON			wko_mst.site_cd = wko_ls7.site_cd
							AND			wko_mst.rowid = wko_ls7.mst_rowid
							AND			wko_det.site_cd = wko_ls7.site_cd
							AND			wko_det_assign_to = wko_ls7.wko_ls7_emp_id
							AND			wko_ls7.RowID = (SELECT MAX(rowid) FROM wko_ls7 
												 WHERE wko_ls7.site_cd = wko_mst.site_cd
												 AND   wko_ls7.mst_rowid = wko_mst.rowid
												 AND   wko_ls7.site_cd = wko_det.site_cd
												 AND   wko_ls7.wko_ls7_emp_id = wko_det.wko_det_assign_to)

					WHERE		( wko_mst.site_cd = '".$site_cd."' ) 
							AND (prm_ast.prm_ast_asset_no = '".$wko_mst_assetno."')
							".$sts_typ_cd."
							AND (wko_mst_type IN ('G'))
							
					ORDER BY wko_mst_create_date DESC";
	 

			//echo  $sql;

			$stmt_3 = sqlsrv_query( $conn, $sql_3);

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

			$json= array();

			do {
				 while ($row = sqlsrv_fetch_array($stmt_3, SQLSRV_FETCH_ASSOC)) {		
				 
					$json[] = $row;		
				
				 }
			} while ( sqlsrv_next_result($stmt_3) );
	 
	 
	 $sql_3_cnt="SELECT Opens = COALESCE(SUM(Opens),'' ), Complete = COALESCE(SUM(Complete),'' ), Closes = COALESCE(SUM(Closes),'' ) From (
			SELECT 	Opens = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'OPEN' THEN 1 
													WHEN 'READY-TO-BE-DONE' THEN 1 ELSE 0 END),
					Complete = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'COMPLETE' THEN 1 ELSE 0 END),
					Closes = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'CLOSE' THEN 1 WHEN 'FORCE-CLOSE' THEN 1 ELSE 0 END)
							
			FROM 	wko_mst (NOLOCK)  
			
					INNER JOIN wko_det (NOLOCK)
					ON		wko_mst.site_cd = wko_det.site_cd
					AND		wko_mst.rowid = wko_det.mst_rowid
					
					INNER 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		ast_mst (NOLOCK)
					ON			wko_mst.site_cd = ast_mst.site_cd
					AND			wko_mst.wko_mst_assetno = ast_mst.ast_mst_asset_no
											
					
				WHERE 			( wko_mst.site_cd = '".$site_cd."' )
							AND	( wko_mst.wko_mst_assetno = '".$wko_mst_assetno."')							
							AND (wko_mst_type IN ('C', 'P'))
							
							
				UNION ALL
				
				SELECT 	Opens = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'OPEN' THEN 1 WHEN 'READY-TO-BE-DONE' THEN 1 ELSE 0 END),
						Complete = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'COMPLETE' THEN 1 ELSE 0 END),
						Closes = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'CLOSE' THEN 1 WHEN 'FORCE-CLOSE' THEN 1 ELSE 0 END)
							
			FROM 	wko_mst (NOLOCK)  
			
					INNER JOIN wko_det (NOLOCK)
					ON		wko_mst.site_cd = wko_det.site_cd
					AND		wko_mst.rowid = wko_det.mst_rowid
					
					INNER JOIN	wrk_sts (NOLOCK)
					ON			wko_mst.site_cd = wrk_sts.site_cd
					AND			wko_mst.wko_mst_status = wrk_sts.wrk_sts_status	
					
					INNER JOIN 	prm_ast (NOLOCK)
					ON			wko_mst.site_cd = prm_ast.site_cd
					AND			wko_mst.wko_mst_wo_no = prm_ast.prm_ast_wo_no
					AND			wko_mst.wko_mst_pm_grp = prm_ast.prm_ast_grp_cd

					LEFT 
					OUTER 
					JOIN		ast_mst (NOLOCK)
					ON			wko_mst.site_cd = ast_mst.site_cd
					AND			wko_mst.wko_mst_assetno = ast_mst.ast_mst_asset_no
											
					
				WHERE 			( wko_mst.site_cd = '".$site_cd."' )							
							AND (prm_ast.prm_ast_asset_no = '".$wko_mst_assetno."')						
							AND (wko_mst_type IN ('G'))) as X";

				$stmt_3_cnt = sqlsrv_query( $conn, $sql_3_cnt);

				if( !$stmt_3_cnt ) {
					 $error_message = "Error selecting table (wko_mst) All Type";
					 returnError($error_message);
					 die( print_r( sqlsrv_errors(), true));
					 
				}
				do {
					 while ($row = sqlsrv_fetch_array($stmt_3_cnt, SQLSRV_FETCH_ASSOC)) {		
						$Opens = $row['Opens'];
						$Complete = $row['Complete'];
						$Closes = $row['Closes'];
					
					 }
				} while ( sqlsrv_next_result($stmt_3_cnt) );
				
				sqlsrv_free_stmt( $stmt_3);
				sqlsrv_free_stmt( $stmt_3_cnt);
				sqlsrv_close( $conn);
				
if ($valid) {

	returnData($json,$Opens,$Complete,$Closes);	
	
}


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


 
?>