<?php
// get these values from your DB.
require_once('config.php');

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");



if ($_SERVER["REQUEST_METHOD"] == "POST")
{
	
	$json = file_get_contents('php://input');
	$data = json_decode($json, true);

	$site_cd = $data['site_cd'];
	$wko_mst_wo_no=$data['wko_mst_wo_no'];
	
	if (!empty($wko_mst_wo_no)){	
		$where_wko_mst_wo_no = " AND wko_mst.wko_mst_wo_no = '".$wko_mst_wo_no."'";	
	}else{	
		$where_wko_mst_wo_no = "";	
	}



$sql_3="Select 
				wko_mst.site_cd, 
				wko_det_work_grp			= COALESCE(wko_det_work_grp + ' : ' + wrk_grp_desc,''),						
				wko_mst_wo_no				= COALESCE(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			= COALESCE(wko_mst_asset_level + ' : ' + ast_lvl_desc,'') ,     	
				wko_mst_assetno 			= COALESCE(wko_mst_assetno,''), 
				wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''), 
				prm_grp_desc				= COALESCE(prm_grp_desc,''),
				wko_mst_flt_code			= COALESCE( wko_mst_flt_code + ' : ' + wrk_flt_desc,''),      		
				wko_mst_status 				= COALESCE(wko_mst_status + ' : ' + wrk_sts_desc,''),      		
				wko_mst_due_date			,     			
				wko_mst_descs				= COALESCE(wko_mst_descs,''), 
				wko_mst_ast_cod				= COALESCE(wko_mst_ast_cod,''),				
				wko_mst_org_date			= COALESCE(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_loc_name,				
				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.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(
					LEFT(
						CASE 
							WHEN wko_mst.wko_mst_type = 'G' 
							THEN prm_grp_desc 
							ELSE ast_mst_asset_shortdesc + ' / ' + wko_mst_loc_name 
						END,
					80),
				''),
				assigndate					= COALESCE(wko_ls7.audit_date,''),
				wko_mst_asset_status		= COALESCE(ast_sts.ast_sts_status+' : '+ast_sts.ast_sts_desc,''),
				
				wko_det_tech_resp_id 		= CASE WHEN wko_det_tech_resp_id IS NOT NULL Then '#12B76A' Else '#D0D5DD' End, 
				wko_det_safety 				= CASE WHEN wko_det_safety IS NOT NULL Then '#12B76A' Else '#D0D5DD' End, 
				wko_det_ack_name 			= CASE WHEN wko_det_ack_name IS NOT NULL Then '#12B76A' Else '#D0D5DD' End, 
				wko_det_COMPLETE  			= CASE WHEN wrk_sts_typ_cd IN ( 'COMPLETE','CLOSE', 'ACKNOWLEDGEMENT') Then '#12B76A' Else '#D0D5DD' End,
				wko_det_CLOSE  				= CASE WHEN wrk_sts_typ_cd = 'CLOSE' Then '#12B76A' Else '#D0D5DD' End,
				ctc_cnt 					= (Select Count(*) from wko_ls4 (NOLOCK) where wko_ls4.site_cd = wko_mst.site_cd and wko_ls4.mst_RowID = wko_mst.rowid),
				sp_cnt 						= (Select Count(*) from wko_ls3 (NOLOCK) where wko_ls3.site_cd = wko_mst.site_cd and wko_ls3.mst_RowID = wko_mst.rowid),
				mr_cnt 						= (Select Count(*) from wko_ls2 (NOLOCK) where wko_ls2.site_cd = wko_mst.site_cd and wko_ls2.mst_RowID = wko_mst.rowid),
				cl_cnt 						= (Select CAST(COALESCE(SUM(CASE WHEN wko_isp_stp_datatype = 'Z' and wko_isp_dropdown1 is not null Then 1
												WHEN wko_isp_stp_datatype = 'T' and wko_isp_varchar1 is not null Then 1
												WHEN wko_isp_stp_datatype = 'C' and wko_isp_checkbox1 = '1' Then 1
												WHEN wko_isp_stp_datatype = 'D' and wko_isp_datetime1 is not null Then 1
												WHEN wko_isp_stp_datatype = 'N' and wko_isp_numeric1 is not null Then 1 End), '0') as varchar) + '/' +
												CAST(COALESCE(SUM(CAST(wko_isp_varchar3 as numeric)), '0') as varchar)
												From wko_isp (NOLOCK) where mst_rowid = wko_mst.rowid and wko_isp.site_cd = wko_mst.site_cd and wko_isp_varchar3  = '1'),
												
				tc_cnt  					= (Select Count(*) from wko_ls8 (NOLOCK) where wko_ls8.site_cd = wko_mst.site_cd and wko_ls8.mst_RowID = wko_mst.rowid),
				swo_cnt 					= (Select Count(*) from wko_det B (NOLOCK) where B.site_cd = wko_mst.site_cd and B.wko_det_parent_wo = wko_mst.wko_mst_wo_no),
				dwn_cnt 					= (select count(*) from ast_dwntime (NOLOCK) where ast_dwntime_down_wo = wko_mst.wko_mst_wo_no and ast_dwntime.site_cd = wko_mst.site_cd),
				wko_ref_cnt					= (Select Count(*) from wko_ref (NOLOCK) where wko_ref.site_cd = wko_mst.site_cd and wko_ref.mst_RowID = wko_mst.rowid),
				chart_cnt 					= (Select Count(*) from wko_ls11 (NOLOCK) where wko_ls11.site_cd = wko_mst.site_cd and wko_ls11.mst_RowID = wko_mst.rowid),
				sub_wo_cnt 					= (Select Count(*) from wko_det (NOLOCK) where wko_det.site_cd = wko_mst.site_cd and wko_det.wko_det_parent_wo = wko_mst.wko_mst_wo_no),
				pur_mst_cnt 					= '0',
				
				wko_det_cause_code			= COALESCE(wko_det_cause_code + ' : ' + wrk_ccd.wrk_ccd_desc,''),
				wko_det_act_code			= COALESCE(wko_det_act_code + ' : ' + wrk_act.wrk_act_desc,''),
				wko_det_work_type			= COALESCE(wko_det_work_type + ' : ' + wrk_typ.wrk_typ_desc,''),
				wko_det_work_class			= COALESCE(wko_det_work_class + ' : ' + wrk_cls.wrk_cls_desc,''),
				wko_det_planner				= COALESCE(wko_det_planner + ' : ' + emp_mst1.emp_mst_name,''),
				wko_det_corr_action			= COALESCE(wko_det_corr_action,''),
				wko_det_note1				= COALESCE(wko_det_note1,''),
				wko_det_varchar1			= COALESCE(wko_det_varchar1,''),
				wko_det_varchar2			= COALESCE(wko_det_varchar2,''),
				wko_det_varchar3			= COALESCE(wko_det_varchar3,''),
				wko_det_sched_date			= COALESCE(wko_det_sched_date,''),
				wko_det_cmpl_date			= COALESCE(wko_det_cmpl_date,''),
		
				
				cf_wko_mst_originator 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_originator'),
				cf_wko_mst_phone			= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_phone'),
		
				cf_wko_mst_assetno 			= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_assetno'),
				cf_wko_mst_pm_grp 			= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_pm_grp'),
				cf_wko_mst_asset_group_code = (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_asset_group_code'),
				cf_wko_mst_asset_location 	= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_asset_location'),
				cf_wko_mst_asset_level 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_asset_level'),
				cf_wko_mst_chg_costcenter 	= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_chg_costcenter'),
				
				cf_wko_mst_wo_no 			= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_wo_no'),
				cf_wko_mst_orig_priority 	= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_orig_priority'),
				cf_wko_mst_plan_priority 	= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_plan_priority'),
				cf_wko_mst_org_date 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_org_date'),
				cf_wko_mst_due_date 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_due_date'),
				cf_wko_mst_org_time 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_org_time'),
				cf_wko_mst_due_time 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_due_time'),
				cf_wko_det_work_type 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_work_type'),
				cf_wko_mst_flt_code 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_flt_code'),
				cf_wko_mst_status 			= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_status'),
				cf_wko_mst_descs 			= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_mst_descs'),
				cf_wko_det_assign_to 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_assign_to'),
				cf_wko_det_work_grp 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_work_grp'),
				
				cf_wko_det_lab_cost 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_lab_cost'),
				cf_wko_det_con_cost 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_con_cost'),
				cf_wko_det_mtl_cost 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_mtl_cost'),
				
				cf_wko_det_cause_code 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_cause_code'),
				cf_wko_det_act_code 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_act_code'),
				cf_wko_det_corr_action 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_corr_action'),
				cf_wko_det_work_class 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_work_class'),
				cf_wko_det_sched_date 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_sched_date'),
				cf_wko_det_cmpl_date 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_cmpl_date'),
				cf_wko_det_planner 			= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='wko_det_planner'),
				
				cf_ast_mst_asset_no 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_no'),
				cf_ast_mst_asset_shortdesc 	= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_shortdesc'),
				cf_ast_mst_asset_type 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_type'),
				cf_ast_mst_asset_grpcode 	= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_grpcode'),
				cf_ast_mst_asset_code 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_code'),
				cf_ast_mst_work_area 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_work_area'),
				cf_ast_mst_asset_locn		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_asset_locn'),
				cf_ast_mst_ast_lvl 			= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_mst_ast_lvl'),
				cf_ast_det_from_date 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_det_from_date'),
				cf_ast_det_to_date 			= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_det_to_date'),
				cf_ast_det_serial_no 		= (select customize_header from cf_label (NOLOCK) where language_cd = 'DEFAULT' and column_name ='ast_det_serial')
				
								
					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		ast_sts (NOLOCK)
							ON			wko_mst.site_cd = ast_sts.site_cd
							AND			wko_mst.wko_mst_asset_status = ast_sts.ast_sts_status
							
							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		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		wrk_ccd (NOLOCK)
							ON			wko_det.site_cd = wrk_ccd.site_cd
							AND			wko_det.wko_det_cause_code = wrk_ccd.wrk_ccd_cause_cd

							LEFT 
							OUTER 
							JOIN		wrk_act (NOLOCK)
							ON			wko_det.site_cd = wrk_act.site_cd
							AND			wko_det.wko_det_act_code = wrk_act.wrk_act_action_cd


							LEFT 
							OUTER 
							JOIN		wrk_typ (NOLOCK)
							ON			wko_det.site_cd = wrk_typ.site_cd
							AND			wko_det.wko_det_work_type = wrk_typ.wrk_typ_typ_cd


							LEFT 
							OUTER 
							JOIN		wrk_cls (NOLOCK)
							ON			wko_det.site_cd = wrk_cls.site_cd
							AND			wko_det.wko_det_work_class = wrk_cls.wrk_cls_cls_cd

							
							LEFT 
							OUTER 
							JOIN		wrk_grp (NOLOCK)
							ON			wko_det.site_cd = wrk_grp.site_cd
							AND			wko_det.wko_det_work_grp = wrk_grp.wrk_grp_grp_cd
							
							LEFT 
							OUTER 
							JOIN		prm_grp (NOLOCK)
							ON			wko_mst.site_cd = prm_grp.site_cd
							AND			wko_mst.wko_mst_pm_grp = prm_grp.prm_grp_grp_cd
							
							
							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 (NOLOCK)
												 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."' )".
							$where_wko_mst_wo_no."  ";
			
					//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) );
			 
}

if ($stmt_3) {

	sqlsrv_free_stmt( $stmt_3);
	sqlsrv_close( $conn);


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


 
?>