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

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');

//to debug the json values;
//file_put_contents("data.json", $json)
// Converts it into a PHP object
$data = json_decode($json, true);



$site_cd = $data['site_cd'];

$wrk_sts_typ_cd =$data['wrk_sts_typ_cd']; 

$wko_mst_assetno= $data['wko_mst_assetno'];
$wko_mst_descs= $data['wko_mst_descs'];
$wko_mst_originator= $data['wko_mst_originator'];
$wko_mst_status= $data['wko_mst_status'];
$wko_mst_work_area= $data['wko_mst_work_area'];
$wko_mst_type= $data['wko_mst_type'];
$wko_mst_asset_location= $data['wko_mst_asset_location'];
$wko_mst_asset_level= $data['wko_mst_asset_level'];
$wko_mst_org_date=$data['wko_mst_org_date'];
$wko_mst_due_date= $data['wko_mst_due_date'];
$asset_shortdesc=$data['asset_shortdesc'];
$wko_det_assign_to=$data['wko_det_assign_to'];
$wko_mst_wo_no=$data['wko_mst_wo_no'];
$wko_det_work_type=$data['wko_det_work_type'];

$wkr_mst_wr_no=$data['wkr_mst_wr_no'];
$wko_det_supv_id=$data['wko_det_supv_id'];
$wko_mst_chg_costcenter=$data['wko_mst_chg_costcenter'];

$type=$data['type'];
$emp_det_work_grp=$data['emp_det_work_grp'];
$emp_id=$data['emp_id'];
$Dashoard_type=$data['Dashoard_type'];

if($Dashoard_type == ""){

	$where_Dashoard_type = "";

}else if($Dashoard_type == "Due"){

	if($wko_mst_type == "P"){
		
		$where_Dashoard_type = " AND (Datediff(day, getdate(), wko_mst_due_date) <= ".$emp_det_pm_due_day." AND Datediff(day, getdate(), wko_mst_due_date) >= 0 AND wrk_sts.wrk_sts_typ_cd IN ('OPEN','REWORK')) ";
		
	}else if($wko_mst_type == "C"){
		
		$where_Dashoard_type = " AND (Datediff(day, getdate(), wko_mst_due_date) <= ".$emp_det_cm_due_day." AND Datediff(day, getdate(), wko_mst_due_date) >= 0 AND wrk_sts.wrk_sts_typ_cd IN ('OPEN','REWORK')) ";
		
	}else{
		$where_Dashoard_type = "";
	}

}else if($Dashoard_type == "Past"){

	$where_Dashoard_type = " AND (Datediff(day, getdate(), wko_mst_due_date) < 0 AND wrk_sts.wrk_sts_typ_cd IN ('OPEN','REWORK')) ";

}


if (!empty($wko_mst_wo_no)){	
	$where_wko_mst_wo_no = " AND wko_mst.wko_mst_wo_no LIKE '%".$wko_mst_wo_no. "%' ";	
}else{	
	$where_wko_mst_wo_no = "";	
}


if (!empty($wko_mst_assetno)){	
	$where_wko_mst_assetno = " AND wko_mst.wko_mst_assetno LIKE '%".$wko_mst_assetno. "%' ";
	$where_wko_mst_pm_grp = " AND wko_mst.wko_mst_pm_grp LIKE '%".$wko_mst_assetno. "%' ";
}else{	

	$where_wko_mst_assetno = "";
	$where_wko_mst_pm_grp = "";
	
}

if (!empty($wko_mst_descs)){	
	$where_wko_mst_descs = " AND wko_mst.wko_mst_descs LIKE '%".$wko_mst_descs. "%' ";
	$where_pm_grp_desc = " AND prm_grp.prm_grp_desc LIKE '%".$wko_mst_descs. "%' ";
}else{	
	$where_wko_mst_descs = "";	
	$where_pm_grp_desc = "";
}
if (!empty($wko_mst_originator)){	
	$where_wko_mst_originator = " AND wko_mst.wko_mst_originator LIKE '%".$wko_mst_originator. "%' ";
}else{	
	$where_wko_mst_originator = "";
}

if (!empty($wko_mst_status)){	
	$where_wko_mst_status = " AND wko_mst.wko_mst_status IN ('".$wko_mst_status. "') ";
}else{	
	$where_wko_mst_status = "";
}

if (!empty($wko_mst_work_area)){	
	$where_wko_mst_work_area = " AND wko_mst.wko_mst_work_area LIKE '%".$wko_mst_work_area. "%' ";
}else{	
	$where_wko_mst_work_area = "";
}

if (!empty($wko_mst_type)){	
	if ($wko_mst_type=='P'){
		$where_wko_mst_type_p = " AND wko_mst.wko_mst_type LIKE '%P%' ";
		$where_wko_mst_type_g = " AND wko_mst.wko_mst_type LIKE '%G%' ";
	}else{
		$where_wko_mst_type = " AND wko_mst.wko_mst_type LIKE '%".$wko_mst_type. "%' ";
	}
	
}else{	
	$where_wko_mst_type = "";
}

if (!empty($wko_mst_asset_location)){	
	$where_wko_mst_asset_location = " AND wko_mst.wko_mst_asset_location LIKE '%".$wko_mst_asset_location. "%' ";
}else{	
	$where_wko_mst_asset_location = "";
}

if (!empty($wko_mst_asset_level)){	
	$where_wko_mst_asset_level = " AND wko_mst.wko_mst_asset_level LIKE '%".$wko_mst_asset_level. "%' ";
}else{	
	$where_wko_mst_asset_level = "";
}

if (!empty($wko_det_assign_to)){	
	$where_wko_det_assign_to = " AND wko_det.wko_det_assign_to LIKE '%".$wko_det_assign_to. "%' ";
}else{	

	if($type == "assign"){
			
			$where_wko_det_assign_to = " AND ( wko_det.wko_det_assign_to is Null  or  wko_det.wko_det_assign_to = '' ) ";		
			
		}else {
			$where_wko_det_assign_to = "";
			}	
}

if (!empty($asset_shortdesc)){	
	$where_asset_shortdesc = " AND ast_mst.ast_mst_asset_shortdesc LIKE '%".$asset_shortdesc. "%' ";
}else{	
	$where_asset_shortdesc = "";
}

if (!empty($wko_mst_org_date)){
	$where_org_date = " AND CONVERT(DATETIME, CONVERT(VARCHAR, wko_mst.wko_mst_org_date , 101)) >= CONVERT(DATETIME, CONVERT(VARCHAR, '".$wko_mst_org_date."', 101)) 
						AND CONVERT(DATETIME, CONVERT(VARCHAR, wko_mst.wko_mst_org_date , 101)) < CONVERT(DATETIME, CONVERT(VARCHAR, '".$wko_mst_due_date."', 101))  + 1 ";
}else{
	$where_org_date = " AND CONVERT(DATETIME, CONVERT(VARCHAR, '1900-01-01', 101)) < Getdate() ";
}

if (!empty($wko_det_work_type)){	
	$where_wko_det_work_type = " AND wko_det.wko_det_work_type LIKE '%".$wko_det_work_type. "%' ";
}else{	
	$where_wko_det_work_type = "";
}


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{
	//echo $type;
	
	
	if($type == "assign"){
		$sts_typ_cd ="AND (wrk_sts_typ_cd IN ('OPEN'))";
		
	}else if($type == "notcomplete"){
		$sts_typ_cd ="AND (wrk_sts_typ_cd IN ('OPEN'))";
		
	}else{
		$sts_typ_cd ="";
	}
	
	
	
}

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 (!empty($wko_det_supv_id)){	
	$where_wko_det_supv_id = " AND wko_det.wko_det_supv_id LIKE '%".$wko_det_supv_id. "%' ";
}else{	
	$where_wko_det_supv_id = "";
}

if (!empty($wko_mst_chg_costcenter)){	
	$where_wko_mst_chg_costcenter = " AND wko_mst.wko_mst_chg_costcenter LIKE '%".$wko_mst_chg_costcenter. "%' ";
}else{	
	$where_wko_mst_chg_costcenter = "";
}

$where_work_group = get_emp_ls5($site_cd, $emp_id, 'wko_det_work_grp');
 
if($wko_mst_type == "P"){
	
	$sql_1="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,''), 
			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_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,''),
			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 '#5EFF33' Else '#05375a' End, 
			wko_det_safety = CASE WHEN wko_det_safety IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
			wko_det_ack_name = CASE WHEN wko_det_ack_name IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
			wko_det_COMPLETE  = CASE WHEN wrk_sts_typ_cd = 'COMPLETE' Then '#5EFF33' Else '#05375a' End,
					
			ctc_cnt = (Select Count(*) from wko_ls4 where wko_ls4.site_cd = wko_mst.site_cd and wko_ls4.mst_RowID = wko_mst.rowid),
			sp_cnt = (Select Count(*) from wko_ls3 where wko_ls3.site_cd = wko_mst.site_cd and wko_ls3.mst_RowID = wko_mst.rowid),
			mr_cnt = (Select Count(*) from wko_ls2 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 where mst_rowid = wko_mst.rowid and wko_isp_varchar3  = '1'),
			tc_cnt  = (Select Count(*) from wko_ls8 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 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 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 where wko_ref.site_cd = wko_mst.site_cd and wko_ref.mst_RowID = wko_mst.rowid) 			
							
			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
					AND wko_mst_type  IN ('P')
					
					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_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		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		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		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_assetno.
					$where_asset_shortdesc.
					$where_wko_mst_descs.
					$where_wko_mst_originator.
					$where_wko_mst_status.
					$where_wko_mst_work_area.
					$where_wko_mst_asset_location.
					$where_wko_mst_asset_level.
					$where_wko_det_assign_to.
					$where_wko_det_work_type.
					$where_org_date.
					$sts_typ_cd.
					$where_Dashoard_type.
					$where_work_group.
					$where_wko_mst_type_p.
					$where_wkr_mst_wr_no.
					$where_wko_det_supv_id.
					$where_wko_mst_chg_costcenter.
					$where_wko_mst_wo_no." 
					
	
	UNION ALL
	
	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(prm_grp_grp_cd,''), 
				wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''), 
				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_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(prm_grp_desc,''),
				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 '#5EFF33' Else '#05375a' End, 
				wko_det_safety = CASE WHEN wko_det_safety IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
				wko_det_ack_name = CASE WHEN wko_det_ack_name IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
				wko_det_COMPLETE  = CASE WHEN wrk_sts_typ_cd = 'COMPLETE' Then '#5EFF33' Else '#05375a' End,
					
				ctc_cnt = (Select Count(*) from wko_ls4 where wko_ls4.site_cd = wko_mst.site_cd and wko_ls4.mst_RowID = wko_mst.rowid),
				sp_cnt = (Select Count(*) from wko_ls3 where wko_ls3.site_cd = wko_mst.site_cd and wko_ls3.mst_RowID = wko_mst.rowid),
				mr_cnt = (Select Count(*) from wko_ls2 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 where mst_rowid = wko_mst.rowid and wko_isp_varchar3  = '1'),
				tc_cnt  = (Select Count(*) from wko_ls8 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 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 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 where wko_ref.site_cd = wko_mst.site_cd and wko_ref.mst_RowID = wko_mst.rowid) 				
							
			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
					AND 	wko_mst_type  IN ('G')
					
					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		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		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		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		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		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_pm_grp.
					$where_pm_grp_desc.
					$where_wko_mst_descs.
					$where_wko_mst_originator.
					$where_wko_mst_status.
					$where_wko_mst_work_area.
					$where_wko_mst_asset_location.
					$where_wko_mst_asset_level.
					$where_wko_det_assign_to.
					$where_wko_det_work_type.
					$where_org_date.
					$sts_typ_cd.
					$where_Dashoard_type.
					$where_work_group.
					$where_wko_mst_type_g.
					$where_wkr_mst_wr_no.
					$where_wko_det_supv_id.
					$where_wko_mst_chg_costcenter.
					$where_wko_mst_wo_no."  ORDER BY assigndate DESC, wko_mst_create_date DESC";
 

		//echo  $sql;

		$stmt_1 = sqlsrv_query( $conn, $sql_1);

		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 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
				AND 	wko_mst_type  IN ('P')
					
				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."' )".
						$where_wko_mst_assetno.
						$where_asset_shortdesc.
						$where_wko_mst_descs.
						$where_wko_mst_originator.
						$where_wko_mst_status.
						$where_wko_mst_work_area.
						$where_wko_mst_asset_location.
						$where_wko_mst_asset_level.
						$where_wko_det_assign_to.
						$where_wko_det_work_type.
						$where_org_date.
						$where_work_group.
						$where_wko_mst_type_p.
						$where_wkr_mst_wr_no.
						$where_wko_det_supv_id.
						$where_wko_mst_chg_costcenter.
						$where_Dashoard_type.
						$where_wko_mst_wo_no." 
						
			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
				AND 	wko_mst_type  IN ('G')
				
				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		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
										
				
			WHERE		( wko_mst.site_cd = '".$site_cd."' )".
						$where_wko_mst_pm_grp.
						$where_pm_grp_desc.
						$where_wko_mst_descs.
						$where_wko_mst_originator.
						$where_wko_mst_status.
						$where_wko_mst_work_area.
						$where_wko_mst_asset_location.
						$where_wko_mst_asset_level.
						$where_wko_det_assign_to.
						$where_wko_det_work_type.
						$where_org_date.
						$where_work_group.
						$where_wko_mst_type_g.
						$where_wkr_mst_wr_no.
						$where_wko_det_supv_id.
						$where_wko_mst_chg_costcenter.
						$where_Dashoard_type.
						$where_wko_mst_wo_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)) {		
					$Opens = $row['Opens'];
					$Complete = $row['Complete'];
					$Closes = $row['Closes'];
				
				 }
			} while ( sqlsrv_next_result($stmt_1_cnt) );
	 
			sqlsrv_free_stmt( $stmt_1);
			sqlsrv_free_stmt( $stmt_1_cnt);
			sqlsrv_close( $conn);	
	 
}else{
	
	If ($wko_mst_type == "C"){
		
		$sql_2="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,''), 
			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_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,''),
			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 '#5EFF33' Else '#05375a' End, 
			wko_det_safety = CASE WHEN wko_det_safety IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
			wko_det_ack_name = CASE WHEN wko_det_ack_name IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
			wko_det_COMPLETE  = CASE WHEN wrk_sts_typ_cd = 'COMPLETE' Then '#5EFF33' Else '#05375a' End,

					
			ctc_cnt = (Select Count(*) from wko_ls4 where wko_ls4.site_cd = wko_mst.site_cd and wko_ls4.mst_RowID = wko_mst.rowid),
			sp_cnt = (Select Count(*) from wko_ls3 where wko_ls3.site_cd = wko_mst.site_cd and wko_ls3.mst_RowID = wko_mst.rowid),
			mr_cnt = (Select Count(*) from wko_ls2 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 where mst_rowid = wko_mst.rowid and wko_isp_varchar3  = '1'),
			tc_cnt  = (Select Count(*) from wko_ls8 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 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 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 where wko_ref.site_cd = wko_mst.site_cd and wko_ref.mst_RowID = wko_mst.rowid) 			
							
			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
					AND 	wko_mst_type  IN ('C')
					
					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		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		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		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_assetno.
						$where_asset_shortdesc.
						$where_wko_mst_descs.
						$where_wko_mst_originator.
						$where_wko_mst_status.
						$where_wko_mst_work_area.
						$where_wko_mst_asset_location.
						$where_wko_mst_asset_level.
						$where_wko_det_assign_to.
						$where_wko_det_work_type.
						$where_org_date.
						$sts_typ_cd.
						$where_Dashoard_type.
						$where_work_group.
						$where_wko_mst_type.
						$where_wkr_mst_wr_no.
						$where_wko_det_supv_id.
						$where_wko_mst_chg_costcenter.
						$where_wko_mst_wo_no."  ORDER BY assigndate , wko_mst_create_date DESC";
 

		//echo  $sql;

		$stmt_2 = sqlsrv_query( $conn, $sql_2);

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

		$json= array();

		do {
			
			while ($row = sqlsrv_fetch_array($stmt_2, SQLSRV_FETCH_ASSOC)) {		
			
			$json[] = $row;		
			
			}
			
		} while ( sqlsrv_next_result($stmt_2) );
 
 
	$sql_2_cnt="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
				AND 	wko_mst_type  IN ('C')
					
				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."' )".
						$where_wko_mst_assetno.
						$where_asset_shortdesc.
						$where_wko_mst_descs.
						$where_wko_mst_originator.
						$where_wko_mst_status.
						$where_wko_mst_work_area.
						$where_wko_mst_asset_location.
						$where_wko_mst_asset_level.
						$where_wko_det_assign_to.
						$where_wko_det_work_type.
						$where_org_date.
						$where_work_group.
						$where_wko_mst_type.
						$where_wkr_mst_wr_no.
						$where_wko_det_supv_id.
						$where_wko_mst_chg_costcenter.
						$where_Dashoard_type.
						$where_wko_mst_wo_no."  ";

			$stmt_2_cnt = sqlsrv_query( $conn, $sql_2_cnt);

			if( !$stmt_2_cnt ) {
				 $error_message = "Error selecting table count Corrective Type";
				 returnError($error_message);
				 die( print_r( sqlsrv_errors(), true));
				 
			}
			do {
				 while ($row = sqlsrv_fetch_array($stmt_2_cnt, SQLSRV_FETCH_ASSOC)) {		
					$Opens = $row['Opens'];
					$Complete = $row['Complete'];
					$Closes = $row['Closes'];
				
				 }
			} while ( sqlsrv_next_result($stmt_2_cnt) );
			
			sqlsrv_free_stmt( $stmt_2);
			sqlsrv_free_stmt( $stmt_2_cnt);
			sqlsrv_close( $conn);	
		}else{

		$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,''), 
					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_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,''),
					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 '#5EFF33' Else '#05375a' End, 
					wko_det_safety = CASE WHEN wko_det_safety IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
					wko_det_ack_name = CASE WHEN wko_det_ack_name IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
					wko_det_COMPLETE  = CASE WHEN wrk_sts_typ_cd = 'COMPLETE' Then '#5EFF33' Else '#05375a' End,
					ctc_cnt = (Select Count(*) from wko_ls4 where wko_ls4.site_cd = wko_mst.site_cd and wko_ls4.mst_RowID = wko_mst.rowid),
					sp_cnt = (Select Count(*) from wko_ls3 where wko_ls3.site_cd = wko_mst.site_cd and wko_ls3.mst_RowID = wko_mst.rowid),
					mr_cnt = (Select Count(*) from wko_ls2 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 where mst_rowid = wko_mst.rowid and wko_isp_varchar3  = '1'),
					tc_cnt  = (Select Count(*) from wko_ls8 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 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 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 where wko_ref.site_cd = wko_mst.site_cd and wko_ref.mst_RowID = wko_mst.rowid) 					
									
					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
							AND 	wko_mst_type  IN ('C', 'P')
							
							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		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		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		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_assetno.
							$where_asset_shortdesc.
							$where_wko_mst_descs.
							$where_wko_mst_originator.
							$where_wko_mst_status.
							$where_wko_mst_work_area.
							$where_wko_mst_asset_location.
							$where_wko_mst_asset_level.
							$where_wko_det_assign_to.
							$where_wko_det_work_type.
							$where_org_date.
							$sts_typ_cd.
							$where_Dashoard_type.
							$where_work_group.
							$where_wko_mst_type.
							$where_wkr_mst_wr_no.
							$where_wko_det_supv_id.
							$where_wko_mst_chg_costcenter.
							$where_wko_mst_wo_no."  ";
			
			If (!empty($asset_shortdesc)){
				$sql_3 = $sql_3."ORDER BY wko_mst_create_date DESC";
			}else{
				$sql_3 = $sql_3."UNION ALL
			
			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(prm_grp_grp_cd,''), 
					wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''), 
					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_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(prm_grp_desc,''),
					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 '#5EFF33' Else '#05375a' End, 
					wko_det_safety = CASE WHEN wko_det_safety IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
					wko_det_ack_name = CASE WHEN wko_det_ack_name IS NOT NULL Then '#5EFF33' Else '#05375a' End, 
					wko_det_COMPLETE  = CASE WHEN wrk_sts_typ_cd = 'COMPLETE' Then '#5EFF33' Else '#05375a' End,
			
					ctc_cnt = (Select Count(*) from wko_ls4 where wko_ls4.site_cd = wko_mst.site_cd and wko_ls4.mst_RowID = wko_mst.rowid),
					sp_cnt = (Select Count(*) from wko_ls3 where wko_ls3.site_cd = wko_mst.site_cd and wko_ls3.mst_RowID = wko_mst.rowid),
					mr_cnt = (Select Count(*) from wko_ls2 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 where mst_rowid = wko_mst.rowid and wko_isp_varchar3  = '1'),
					tc_cnt  = (Select Count(*) from wko_ls8 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 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 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 where wko_ref.site_cd = wko_mst.site_cd and wko_ref.mst_RowID = wko_mst.rowid) 					
					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
							AND 	wko_mst_type  IN ('G')
							
							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		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		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		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		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		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_pm_grp.
							$where_pm_grp_desc.
							$where_wko_mst_descs.
							$where_wko_mst_originator.
							$where_wko_mst_status.
							$where_wko_mst_work_area.
							$where_wko_mst_asset_location.
							$where_wko_mst_asset_level.
							$where_wko_det_assign_to.
							$where_wko_det_work_type.
							$where_org_date.
							$sts_typ_cd.
							$where_Dashoard_type.
							$where_work_group.
							$where_wko_mst_type.
							$where_wkr_mst_wr_no.
							$where_wko_det_supv_id.
							$where_wko_mst_chg_costcenter.
							$where_wko_mst_wo_no."  ORDER BY assigndate DESC , 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
					AND 	wko_mst_type  IN ('C', 'P') 
					
					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."' )".
							$where_wko_mst_assetno.
							$where_asset_shortdesc.
							$where_wko_mst_descs.
							$where_wko_mst_originator.
							$where_wko_mst_status.
							$where_wko_mst_work_area.
							$where_wko_mst_asset_location.
							$where_wko_mst_asset_level.
							$where_wko_det_assign_to.
							$where_wko_det_work_type.
							$where_org_date.	
							$where_work_group.
							$where_wko_mst_type.
							$where_wkr_mst_wr_no.
							$where_wko_det_supv_id.
							$where_wko_mst_chg_costcenter.
							$where_Dashoard_type.							
							$where_wko_mst_wo_no." ";
							
			If (!empty($asset_shortdesc)){
				$sql_3_cnt = $sql_3_cnt.") as X";
			}else{
				$sql_3_cnt = $sql_3_cnt." 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
					AND 	wko_mst_type  IN ('G')
					
					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		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
											
					
				WHERE		( wko_mst.site_cd = '".$site_cd."' )".
							$where_wko_mst_pm_grp.
							$where_pm_grp_desc.
							$where_wko_mst_descs.
							$where_wko_mst_originator.
							$where_wko_mst_status.
							$where_wko_mst_work_area.
							$where_wko_mst_asset_location.
							$where_wko_mst_asset_level.
							$where_wko_det_assign_to.
							$where_wko_det_work_type.
							$where_org_date.
							$where_work_group.
							$where_wko_mst_type.
							$where_wkr_mst_wr_no.
							$where_wko_det_supv_id.
							$where_wko_mst_chg_costcenter.
							$where_Dashoard_type.
							$where_wko_mst_wo_no." ) 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);
	exit();
		
}


 
?>