<?php
// get these values from your DB.
require_once('config.php');
require_once('f_get_emp_ls5.php');
require_once('f_resize_imagepath.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");


$json_return = array();
$json_img_link = array();


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'];
$wko_det_work_grp			= $data['wko_det_work_grp'];
$wko_mst_pm_grp				= $data['wko_mst_pm_grp'];

$type						= $data['type'];
$emp_det_work_grp			= $data['emp_det_work_grp'];
$emp_id						= $data['emp_id'];
$Dashoard_type				= $data['Dashoard_type'];
$Folder						= $data['Folder'];
$dvc_id						= $data['dvc_id'];
$MobileURL					= $data['MobileURL'];
$LOGINID 					= $data['LOGINID'];
$searchTerm 				= $data['searchTerm'];



$sql_emp_mst="SELECT
emp_det_cm_due_day= COALESCE(emp_det_cm_due_day,3),
emp_det_pm_due_day= COALESCE(emp_det_pm_due_day,3),
emp_mst_login_id
		from emp_mst (NOLOCK)
		INNER
		JOIN	emp_det (NOLOCK)
		ON		emp_mst.site_cd = emp_det.site_cd
		AND		emp_mst.rowid = emp_det.mst_RowID
		WHERE	emp_mst.site_cd = '".$site_cd."'
				AND emp_mst.emp_mst_empl_id = '".$emp_id."'";

$stmt_emp_mst = sqlsrv_query( $conn, $sql_emp_mst);

if( !$stmt_emp_mst ) {
	 $error_message = "Error selecting current week report (wko_mst)";
	 returnError($error_message);
	 die( print_r( sqlsrv_errors(), true));

}

do 	{
	 while ($row = sqlsrv_fetch_array($stmt_emp_mst, SQLSRV_FETCH_ASSOC)) {
	 $emp_det_cm_due_day= $row['emp_det_cm_due_day'];
	 $emp_det_pm_due_day= $row['emp_det_pm_due_day'];
	 $emp_mst_login_id= $row['emp_mst_login_id'];

	}
} while ( sqlsrv_next_result($stmt_emp_mst) );
sqlsrv_free_stmt( $stmt_emp_mst);

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. "%' ";
}else{

	$where_wko_mst_assetno = "";

}

if (!empty($wko_mst_pm_grp)){
	$where_wko_mst_pm_grp = " AND wko_mst.wko_mst_pm_grp = '".$wko_mst_pm_grp. "' ";
}else{
	$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 = '".$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 = '".$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 = '".$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 = '".$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','ACKNOWLEDGEMENT', 'FORCE-CLOSE', 'CANCEL'))";
}else if($wrk_sts_typ_cd  == "Complete"){
	$sts_typ_cd ="AND (wrk_sts_typ_cd IN ('COMPLETE','ACKNOWLEDGEMENT'))";
}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','REWORK'))";

	}else if($type == "notcomplete"){
		$sts_typ_cd ="AND (wrk_sts_typ_cd IN ('OPEN','REWORK'))";

	}else{
		$sts_typ_cd ="";
	}



}

if (!empty($wkr_mst_wr_no)){
	$where_wkr_mst_wr_no = " AND wko_det.wko_det_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_wr_no = '".$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 = '".$wko_mst_chg_costcenter. "' ";
}else{
	$where_wko_mst_chg_costcenter = "";
}

if (!empty($wko_det_work_grp)){
	$where_wko_det_work_grp = " AND wko_det.wko_det_work_grp = '".$wko_det_work_grp."' ";
}else{
	$where_wko_det_work_grp = "";
}

$where_work_group = get_emp_ls5($site_cd, $emp_id, 'wko_det_work_grp');


$uploaddir = "C:\\wamp64\\www\\".$Folder."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\";

	if (!is_dir($uploaddir)) {
		mkdir($uploaddir, 0777, true);
		mkdir($uploaddir."\\thumbnail\\", 0777, true);
	}

$MobileURLFullSize = $MobileURL . "/mobile_image_temp/temp_".$LOGINID."_".$dvc_id."/";
$MobileURLTnSize = $MobileURL . "/mobile_image_temp/temp_".$LOGINID."_".$dvc_id."/thumbnail/";

$exportdir = "C:\\wamp64\\www\\". $Folder ."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\";
$exportdirtn = "C:\\wamp64\\www\\". $Folder ."\\mobile_image_temp\\temp_".$LOGINID."_".$dvc_id."\\thumbnail\\";

if($wko_mst_type == "P"){



	$sql_1="Select
			wko_mst.site_cd,
			wko_mst.RowID ,
			wko_mst_wo_no				= COALESCE(wko_mst_wo_no,''),
			wko_mst_status 				= COALESCE(wrk_sts_desc,''),
			wko_mst_org_date			= COALESCE(wko_mst_org_date,''),
			wko_mst_descs				= COALESCE(wko_mst_descs,''),
			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 emp_mst1.emp_mst_name END,'') ,
			wko_mst_assetno 			= COALESCE(wko_mst_assetno,''),
			ast_mst_asset_shortdesc		= COALESCE(ast_mst_asset_shortdesc,''),
			wko_mst_work_area = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(mst_war_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_lvl_desc)), ''),
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), '')
			),

			wko_mst_asset_location = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), '')
			),
			wko_mst_asset_level			= COALESCE(ast_lvl_desc,'') ,
			wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''),
			wko_mst_type				= COALESCE(wko_mst_type,''),
			prm_grp_desc 				= '',
			wko_det_work_grp			= COALESCE(wrk_grp_desc,''),
			wko_det_assign_to 			= COALESCE(emp_mst2.emp_mst_name,''),
			assigndate					= COALESCE(wko_ls7.audit_date,''),
			wko_mst_create_date 		= COALESCE(wko_mst.wko_mst_create_date,''),
			wrk_sts_typ_cd				= COALESCE(wrk_sts_typ_cd,''),
			wko_mst_due_date			= COALESCE(wko_mst_due_date,''),
			attachment = ( Select TOP 1 wko_ref.attachment
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_name = ( Select TOP 1 wko_ref.file_name
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_RowID = ( Select TOP 1 wko_ref.RowID
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							)


			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_det.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_det.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_det.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_det.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."' )
			AND 	( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
								ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
								mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
								ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
								ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
								wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
								wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
								emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
								wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
							)".
					$where_wko_mst_pm_grp.
					$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_det_work_grp.
					$where_wko_mst_wo_no."


	UNION ALL

	Select
			wko_mst.site_cd,
			wko_mst.RowID ,
			wko_mst_wo_no				= COALESCE(wko_mst_wo_no,''),
			wko_mst_status 				= COALESCE(wrk_sts_desc,''),
			wko_mst_org_date			= COALESCE(wko_mst_org_date,''),
			wko_mst_descs				= COALESCE(wko_mst_descs,''),
			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 emp_mst1.emp_mst_name END,'') ,
			wko_mst_assetno 			= COALESCE(wko_mst_assetno,''),
			ast_mst_asset_shortdesc		= COALESCE(ast_mst_asset_shortdesc,''),
			wko_mst_work_area = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(mst_war_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_lvl_desc)), ''),
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), '')
			),

			wko_mst_asset_location = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), '')
			),
			wko_mst_asset_level			= COALESCE(ast_lvl_desc,'') ,
			wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''),
			wko_mst_type				= COALESCE(wko_mst_type,''),
			prm_grp_desc 				= COALESCE(prm_grp_desc,''),
			wko_det_work_grp			= COALESCE(wrk_grp_desc,''),
			wko_det_assign_to 			= COALESCE(emp_mst2.emp_mst_name,''),
			assigndate					= COALESCE(wko_ls7.audit_date,''),
			wko_mst_create_date 		= COALESCE(wko_mst.wko_mst_create_date,''),
			wrk_sts_typ_cd				= COALESCE(wrk_sts_typ_cd,''),
			wko_mst_due_date			= COALESCE(wko_mst_due_date,''),
			attachment = ( Select TOP 1 wko_ref.attachment
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_name = ( Select TOP 1 wko_ref.file_name
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_RowID = ( Select TOP 1 wko_ref.RowID
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							)

			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_det.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_det.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_det.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_det.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		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		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."' )
			AND 	( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
								ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
								mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
								ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
								ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
								wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
								wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
								emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
								wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
							)".
					$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_det_work_grp.
					$where_wko_mst_wo_no."  ORDER BY assigndate asc  , wko_mst_create_date asc";


		//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)) {

				$site_cd = $row['site_cd'];
				$RowID = $row['RowID'];
				$wko_mst_wo_no = $row['wko_mst_wo_no'];
				$wko_mst_status = $row['wko_mst_status'];
				$wko_mst_org_date = $row['wko_mst_org_date'];
				$wko_mst_descs = $row['wko_mst_descs'];
				$wko_mst_originator = $row['wko_mst_originator'];
				$wko_mst_assetno = $row['wko_mst_assetno'];
				$ast_mst_asset_shortdesc = $row['ast_mst_asset_shortdesc'];
				$wko_mst_work_area = $row['wko_mst_work_area'];
				$wko_mst_asset_location = $row['wko_mst_asset_location'];
				$wko_mst_asset_level = $row['wko_mst_asset_level'];
				$wko_mst_pm_grp = $row['wko_mst_pm_grp'];
				$prm_grp_desc = $row['prm_grp_desc'];
				$wko_mst_type = $row['wko_mst_type'];
				$wko_det_work_grp = $row['wko_det_work_grp'];
				$wko_det_assign_to = $row['wko_det_assign_to'];
				$assigndate = $row['assigndate'];
				$wko_mst_create_date = $row['wko_mst_create_date'];
				$wrk_sts_typ_cd = $row['wrk_sts_typ_cd'];
				$wko_mst_due_date = $row['wko_mst_due_date'];

				$file_name = $row['file_name'];

				$attachment;
				$fileName;

				if(empty($file_name)){

						$attachment = Null;
						$fileName =Null;

				}else{

					if (strpos($file_name, '.pdf') !== false) {

						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, '.mp4') !== false) {

						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, 'docx') == true){

							$attachment = Null;
							$fileName =Null;

					}else{

						$Photo = $row['attachment'];
						$file_Name = $row['file_name'];
						$file_RowID = $row['file_RowID'];
						
						$file_Name = generateUniqueName($file_Name,$file_RowID);
						
						$exportFullPathFileName = $exportdir . $file_Name;
						$exportFullPathTNFileName = $exportdirtn . $file_Name;

						$publicFullPathFileName = $MobileURLFullSize . $file_Name;
						$publicFullPathTNFileName = $MobileURLTnSize . $file_Name;

						if (!file_exists($exportFullPathTNFileName)) {
							// If the thumbnail does not exist, resize and write the thumbnail
							resizeImageImagick($Photo, $exportFullPathTNFileName);
						}


						$attachment = $publicFullPathTNFileName;
						$fileName =$file_Name;

					}


				}


				$json_img_link['site_cd'] = $site_cd;
				$json_img_link['RowID'] = $RowID;
				$json_img_link['wko_mst_wo_no'] = $wko_mst_wo_no;
				$json_img_link['wko_mst_status'] = $wko_mst_status;
				$json_img_link['wko_mst_org_date'] = $wko_mst_org_date;
				$json_img_link['wko_mst_descs'] = $wko_mst_descs;
				$json_img_link['wko_mst_originator'] = $wko_mst_originator;
				$json_img_link['wko_mst_assetno'] = $wko_mst_assetno;
				$json_img_link['ast_mst_asset_shortdesc'] = $ast_mst_asset_shortdesc;
				$json_img_link['wko_mst_work_area'] = $wko_mst_work_area;
				$json_img_link['wko_mst_asset_location'] = $wko_mst_asset_location;
				$json_img_link['wko_mst_asset_level'] = $wko_mst_asset_level;
				$json_img_link['wko_mst_pm_grp'] = $wko_mst_pm_grp;
				$json_img_link['prm_grp_desc'] = $prm_grp_desc;
				$json_img_link['wko_mst_type'] = $wko_mst_type;
				$json_img_link['wko_det_work_grp'] = $wko_det_work_grp;
				$json_img_link['wko_det_assign_to'] = $wko_det_assign_to;
				$json_img_link['assigndate'] = $assigndate;
				$json_img_link['wko_mst_create_date'] = $wko_mst_create_date;
				$json_img_link['wrk_sts_typ_cd'] = $wrk_sts_typ_cd;
				$json_img_link['wko_mst_due_date'] = $wko_mst_due_date;
				$json_img_link['attachment'] = $attachment;
				$json_img_link['fileName'] = $fileName;
				array_push($json_return, $json_img_link);
			}

		} 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 WHEN 'REWORK' THEN 1 ELSE 0 END),
						Complete 	= SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'COMPLETE' THEN 1 WHEN 'ACKNOWLEDGEMENT' 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
				
				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		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		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		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


			WHERE 		( wko_mst.site_cd = '".$site_cd."' )
			AND 	( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
						ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
						wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
						wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
						wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
						mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
						wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
						ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
						ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
						wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
						wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
						emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
						wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
						wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
					)".
						$where_wko_mst_pm_grp.
						$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_wko_det_work_grp.
						$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 WHEN 'REWORK' THEN 1 ELSE 0 END),
					Complete = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'COMPLETE' THEN 1 WHEN 'ACKNOWLEDGEMENT' 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
				
				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		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		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		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


			WHERE		( wko_mst.site_cd = '".$site_cd."' )
			AND 		( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
							ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
							wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
							wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
							wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
							mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
							wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
							ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
							ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
							wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
							wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
							emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
							wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
							wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
						)".
						$where_wko_mst_pm_grp.
						$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_wko_det_work_grp.
						$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_mst.RowID ,
			wko_mst_wo_no				= COALESCE(wko_mst_wo_no,''),
			wko_mst_status 				= COALESCE(wrk_sts_desc,''),
			wko_mst_org_date			= COALESCE(wko_mst_org_date,''),
			wko_mst_descs				= COALESCE(wko_mst_descs,''),
			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 emp_mst1.emp_mst_name END,'') ,
			wko_mst_assetno 			= COALESCE(wko_mst_assetno,''),
			ast_mst_asset_shortdesc		= COALESCE(ast_mst_asset_shortdesc,''),
			wko_mst_work_area = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(mst_war_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_lvl_desc)), ''),
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), '')
			),

			wko_mst_asset_location = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), '')
			),
			wko_mst_asset_level			= COALESCE(ast_lvl_desc,'') ,
			wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''),
			wko_mst_type				= COALESCE(wko_mst_type,''),
			prm_grp_desc 				= '',
			wko_det_work_grp			= COALESCE(wrk_grp_desc,''),
			wko_det_assign_to 			= COALESCE(emp_mst2.emp_mst_name,''),
			assigndate					= COALESCE(wko_ls7.audit_date,''),
			wko_mst_create_date 		= COALESCE(wko_mst.wko_mst_create_date,''),
			wrk_sts_typ_cd				= COALESCE(wrk_sts_typ_cd,''),
			wko_mst_due_date			= COALESCE(wko_mst_due_date,''),
			attachment = ( Select TOP 1 wko_ref.attachment
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_name = ( Select TOP 1 wko_ref.file_name
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_RowID = ( Select TOP 1 wko_ref.RowID
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							)

			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_det.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_det.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_det.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_det.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."' )
			AND 	( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
								ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
								mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
								ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
								ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
								wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
								wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
								emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
								wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
							)".
						$where_wko_mst_pm_grp.
						$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_det_work_grp.
						$where_wko_mst_wo_no."  ORDER BY assigndate asc  , wko_mst_create_date asc";


		//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)) {


				$site_cd = $row['site_cd'];
				$RowID = $row['RowID'];
				$wko_mst_wo_no = $row['wko_mst_wo_no'];
				$wko_mst_status = $row['wko_mst_status'];
				$wko_mst_org_date = $row['wko_mst_org_date'];
				$wko_mst_descs = $row['wko_mst_descs'];
				$wko_mst_originator = $row['wko_mst_originator'];
				$wko_mst_assetno = $row['wko_mst_assetno'];
				$ast_mst_asset_shortdesc = $row['ast_mst_asset_shortdesc'];
				$wko_mst_work_area = $row['wko_mst_work_area'];
				$wko_mst_asset_location = $row['wko_mst_asset_location'];
				$wko_mst_asset_level = $row['wko_mst_asset_level'];
				$wko_mst_pm_grp = $row['wko_mst_pm_grp'];
				$prm_grp_desc = $row['prm_grp_desc'];
				$wko_mst_type = $row['wko_mst_type'];
				$wko_det_work_grp = $row['wko_det_work_grp'];
				$wko_det_assign_to = $row['wko_det_assign_to'];
				$assigndate = $row['assigndate'];
				$wko_mst_create_date = $row['wko_mst_create_date'];
				$wrk_sts_typ_cd = $row['wrk_sts_typ_cd'];
				$wko_mst_due_date = $row['wko_mst_due_date'];

				$file_name = $row['file_name'];

				$attachment;
				$fileName;

				if(empty($file_name)){

						$attachment = Null;
						$fileName =Null;

				}else{

					if (strpos($file_name, '.pdf') !== false) {

						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, '.mp4') !== false) {

						$attachment = Null;
						$fileName =Null;
					}else if (strpos($file_name, 'docx') == true){

							$attachment = Null;
							$fileName =Null;

					}else{

						$Photo = $row['attachment'];
						$file_Name = $row['file_name'];
						$file_RowID = $row['file_RowID'];
						
						$file_Name = generateUniqueName($file_Name,$file_RowID);
						
						$exportFullPathFileName = $exportdir . $file_Name;
						$exportFullPathTNFileName = $exportdirtn . $file_Name;

						$publicFullPathFileName = $MobileURLFullSize . $file_Name;
						$publicFullPathTNFileName = $MobileURLTnSize . $file_Name;

						if (!file_exists($exportFullPathTNFileName)) {
							// If the thumbnail does not exist, resize and write the thumbnail
							resizeImageImagick($Photo, $exportFullPathTNFileName);
						}

						$attachment = $publicFullPathTNFileName;
						$fileName =$file_Name;

					}


				}


				$json_img_link['site_cd'] = $site_cd;
				$json_img_link['RowID'] = $RowID;
				$json_img_link['wko_mst_wo_no'] = $wko_mst_wo_no;
				$json_img_link['wko_mst_status'] = $wko_mst_status;
				$json_img_link['wko_mst_org_date'] = $wko_mst_org_date;
				$json_img_link['wko_mst_descs'] = $wko_mst_descs;
				$json_img_link['wko_mst_originator'] = $wko_mst_originator;
				$json_img_link['wko_mst_assetno'] = $wko_mst_assetno;
				$json_img_link['ast_mst_asset_shortdesc'] = $ast_mst_asset_shortdesc;
				$json_img_link['wko_mst_work_area'] = $wko_mst_work_area;
				$json_img_link['wko_mst_asset_location'] = $wko_mst_asset_location;
				$json_img_link['wko_mst_asset_level'] = $wko_mst_asset_level;
				$json_img_link['wko_mst_pm_grp'] = $wko_mst_pm_grp;
				$json_img_link['prm_grp_desc'] = $prm_grp_desc;
				$json_img_link['wko_mst_type'] = $wko_mst_type;
				$json_img_link['wko_det_work_grp'] = $wko_det_work_grp;
				$json_img_link['wko_det_assign_to'] = $wko_det_assign_to;
				$json_img_link['assigndate'] = $assigndate;
				$json_img_link['wko_mst_create_date'] = $wko_mst_create_date;
				$json_img_link['wrk_sts_typ_cd'] = $wrk_sts_typ_cd;
				$json_img_link['wko_mst_due_date'] = $wko_mst_due_date;
				$json_img_link['attachment'] = $attachment;
				$json_img_link['fileName'] = $fileName;
				array_push($json_return, $json_img_link);


			}

		} while ( sqlsrv_next_result($stmt_2) );

		$sql_2_cnt="SELECT 	Opens = ISNULL(SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'OPEN' THEN 1 WHEN 'READY-TO-BE-DONE' THEN 1 WHEN 'REWORK' THEN 1 ELSE 0 END),0),
				Complete = ISNULL(SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'COMPLETE' THEN 1 WHEN 'ACKNOWLEDGEMENT' THEN 1 ELSE 0 END),0),
				Closes = ISNULL(SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'CLOSE' THEN 1 WHEN 'FORCE-CLOSE' THEN 1 ELSE 0 END),0)

			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		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		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		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		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


			WHERE		( wko_mst.site_cd = '".$site_cd."' )
			AND 	( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
								ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
								mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
								ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
								ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
								wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
								wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
								emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
								wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
							)".
						$where_wko_mst_pm_grp.
						$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_wko_det_work_grp.
						$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_mst.RowID ,
			wko_mst_wo_no				= COALESCE(wko_mst_wo_no,''),
			wko_mst_status 				= COALESCE(wrk_sts_desc,''),
			wko_mst_org_date			= COALESCE(wko_mst_org_date,''),
			wko_mst_descs				= COALESCE(wko_mst_descs,''),
			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 emp_mst1.emp_mst_name END,'') ,
			wko_mst_assetno 			= COALESCE(wko_mst_assetno,''),
			ast_mst_asset_shortdesc		= COALESCE(ast_mst_asset_shortdesc,''),
			wko_mst_work_area = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(mst_war_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_lvl_desc)), ''),
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), '')
			),

			wko_mst_asset_location = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), '')
			),
			wko_mst_asset_level			= COALESCE(ast_lvl_desc,'') ,
			wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''),
			wko_mst_type				= COALESCE(wko_mst_type,''),
			prm_grp_desc 				= '',
			wko_det_work_grp			= COALESCE(wrk_grp_desc,''),
			wko_det_assign_to 			= COALESCE(emp_mst2.emp_mst_name,''),
			assigndate					= COALESCE(wko_ls7.audit_date,''),
			wko_mst_create_date 		= COALESCE(wko_mst.wko_mst_create_date,''),
			wrk_sts_typ_cd				= COALESCE(wrk_sts_typ_cd,''),
			wko_mst_due_date			= COALESCE(wko_mst_due_date,''),
			attachment = ( Select TOP 1 wko_ref.attachment
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_name = ( Select TOP 1 wko_ref.file_name
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_RowID = ( Select TOP 1 wko_ref.RowID
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							)

					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_det.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_det.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_det.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_det.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."' )
					AND 	( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
								ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
								mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
								ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
								ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
								wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
								wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
								emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
								wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
							)".
							$where_wko_mst_pm_grp.
							$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_det_work_grp.
							$where_wko_mst_wo_no."  ";

			If (!empty($asset_shortdesc)){
				$sql_3 = $sql_3."ORDER BY assigndate asc  , wko_mst_create_date asc";
			}else{
				$sql_3 = $sql_3."UNION ALL

			Select

					wko_mst.site_cd,
			wko_mst.RowID ,
			wko_mst_wo_no				= COALESCE(wko_mst_wo_no,''),
			wko_mst_status 				= COALESCE(wrk_sts_desc,''),
			wko_mst_org_date			= COALESCE(wko_mst_org_date,''),
			wko_mst_descs				= COALESCE(wko_mst_descs,''),
			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 emp_mst1.emp_mst_name END,'') ,
			wko_mst_assetno 			= COALESCE(wko_mst_assetno,''),
			ast_mst_asset_shortdesc		= COALESCE(ast_mst_asset_shortdesc,''),
			wko_mst_work_area = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(mst_war_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), ''),
				NULLIF(LTRIM(RTRIM(ast_lvl_desc)), ''),
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), '')
			),

			wko_mst_asset_location = 
			CONCAT_WS(' / ',
				NULLIF(LTRIM(RTRIM(wko_mst_loc_name)), ''),
				NULLIF(LTRIM(RTRIM(ast_loc_desc)), '')
			),
			wko_mst_asset_level			= COALESCE(ast_lvl_desc,'') ,
			wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''),
			wko_mst_type				= COALESCE(wko_mst_type,''),
			prm_grp_desc 				= COALESCE(prm_grp_desc,''),
			wko_det_work_grp			= COALESCE(wrk_grp_desc,''),
			wko_det_assign_to 			= COALESCE(emp_mst2.emp_mst_name,''),
			assigndate					= COALESCE(wko_ls7.audit_date,''),
			wko_mst_create_date 		= COALESCE(wko_mst.wko_mst_create_date,''),
			wrk_sts_typ_cd				= COALESCE(wrk_sts_typ_cd,''),
			wko_mst_due_date			= COALESCE(wko_mst_due_date,''),
			attachment = ( Select TOP 1 wko_ref.attachment
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_name = ( Select TOP 1 wko_ref.file_name
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							),
			file_RowID = ( Select TOP 1 wko_ref.RowID
								From wko_ref (NOLOCK)
								WHERE		wko_ref.type  IN ('P','A')
								and			COALESCE(wko_ref.column2,'') <> 'SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'RESPONSE_SIGN'
								and			COALESCE(wko_ref.column2,'') <> 'Checklist'
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp4')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.mp3')
								AND 		(file_name IS NULL OR file_name NOT LIKE '%.pdf')
								And 		DataLength(wko_ref.attachment) > 0
								And			DataLength(wko_ref.attachment) is not null
								and			wko_ref.file_name not like '%xlsx%'
								and			wko_ref.file_name not like '%xls%'
								And wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid
								order by wko_ref.type DESC
							)

					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		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		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_det.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_det.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_det.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_det.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."' )
					AND 	( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
								ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
								mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
								ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
								ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
								wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
								wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
								emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
								wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
							)".
							$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_det_work_grp.
							$where_wko_mst_wo_no."  ORDER BY assigndate asc  , wko_mst_create_date asc";
			}




			//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)) {

					$site_cd = $row['site_cd'];
					$RowID = $row['RowID'];
					$wko_mst_wo_no = $row['wko_mst_wo_no'];
					$wko_mst_status = $row['wko_mst_status'];
					$wko_mst_org_date = $row['wko_mst_org_date'];
					$wko_mst_descs = $row['wko_mst_descs'];
					$wko_mst_originator = $row['wko_mst_originator'];
					$wko_mst_assetno = $row['wko_mst_assetno'];
					$ast_mst_asset_shortdesc = $row['ast_mst_asset_shortdesc'];
					$wko_mst_work_area = $row['wko_mst_work_area'];
					$wko_mst_asset_location = $row['wko_mst_asset_location'];
					$wko_mst_asset_level = $row['wko_mst_asset_level'];
					$wko_mst_pm_grp = $row['wko_mst_pm_grp'];
					$prm_grp_desc = $row['prm_grp_desc'];
					$wko_mst_type = $row['wko_mst_type'];
					$wko_det_work_grp = $row['wko_det_work_grp'];
					$wko_det_assign_to = $row['wko_det_assign_to'];
					$assigndate = $row['assigndate'];
					$wko_mst_create_date = $row['wko_mst_create_date'];
					$wrk_sts_typ_cd = $row['wrk_sts_typ_cd'];
					$wko_mst_due_date = $row['wko_mst_due_date'];

					$file_name = $row['file_name'];

					$attachment;
					$fileName;

					if(empty($file_name)){

						$attachment = Null;
						$fileName =Null;

					}else{

						if (strpos($file_name, '.pdf') !== false) {

							$attachment = Null;
							$fileName =Null;
						}else if (strpos($file_name, '.mp4') !== false) {

							$attachment = Null;
							$fileName =Null;
						}else if (strpos($file_name, 'docx') == true){

							$attachment = Null;
							$fileName =Null;

						}else{

							$Photo = $row['attachment'];
							$file_Name = $row['file_name'];
							$file_RowID = $row['file_RowID'];
							
							$file_Name = generateUniqueName($file_Name,$file_RowID);

							$exportFullPathFileName = $exportdir . $file_Name;
							$exportFullPathTNFileName = $exportdirtn . $file_Name;

							$publicFullPathFileName = $MobileURLFullSize . $file_Name;
							$publicFullPathTNFileName = $MobileURLTnSize . $file_Name;

							if (!file_exists($exportFullPathTNFileName)) {
							// If the thumbnail does not exist, resize and write the thumbnail
								resizeImageImagick($Photo, $exportFullPathTNFileName);
							}

							$attachment = $publicFullPathTNFileName;
							$fileName =$file_Name;

						}


					}

					$json_img_link['site_cd'] = $site_cd;
					$json_img_link['RowID'] = $RowID;
					$json_img_link['wko_mst_wo_no'] = $wko_mst_wo_no;
					$json_img_link['wko_mst_status'] = $wko_mst_status;
					$json_img_link['wko_mst_org_date'] = $wko_mst_org_date;
					$json_img_link['wko_mst_descs'] = $wko_mst_descs;
					$json_img_link['wko_mst_originator'] = $wko_mst_originator;
					$json_img_link['wko_mst_assetno'] = $wko_mst_assetno;
					$json_img_link['ast_mst_asset_shortdesc'] = $ast_mst_asset_shortdesc;
					$json_img_link['wko_mst_work_area'] = $wko_mst_work_area;
					$json_img_link['wko_mst_asset_location'] = $wko_mst_asset_location;
					$json_img_link['wko_mst_asset_level'] = $wko_mst_asset_level;
					$json_img_link['wko_mst_pm_grp'] = $wko_mst_pm_grp;
					$json_img_link['prm_grp_desc'] = $prm_grp_desc;
					$json_img_link['wko_mst_type'] = $wko_mst_type;
					$json_img_link['wko_det_work_grp'] = $wko_det_work_grp;
					$json_img_link['wko_det_assign_to'] = $wko_det_assign_to;
					$json_img_link['assigndate'] = $assigndate;
					$json_img_link['wko_mst_create_date'] = $wko_mst_create_date;
					$json_img_link['wrk_sts_typ_cd'] = $wrk_sts_typ_cd;
					$json_img_link['wko_mst_due_date'] = $wko_mst_due_date;
					$json_img_link['attachment'] = $attachment;
					$json_img_link['fileName'] = $fileName;
					array_push($json_return, $json_img_link);


				}
			} 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 WHEN 'REWORK' THEN 1 ELSE 0 END),
					Complete = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'COMPLETE' THEN 1 WHEN 'ACKNOWLEDGEMENT' 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
					
					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		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		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		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


				WHERE		( wko_mst.site_cd = '".$site_cd."' )
				AND 	( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
								ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
								mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
								ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
								ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
								wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
								wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
								emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
								wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
							)".
							$where_wko_mst_pm_grp.
							$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_wko_det_work_grp.
							$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 WHEN 'REWORK' THEN 1 ELSE 0 END),
						Complete = SUM(CASE wrk_sts.wrk_sts_typ_cd WHEN 'COMPLETE' THEN 1 WHEN 'ACKNOWLEDGEMENT' 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
					
					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		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		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		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


				WHERE		( wko_mst.site_cd = '".$site_cd."' )
				AND 	( 	wko_mst.wko_mst_assetno LIKE '%".$searchTerm. "%' OR	
								ast_mst.ast_mst_asset_shortdesc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_wo_no LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_descs LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_work_area	 LIKE '%".$searchTerm. "%' OR 
								mst_war.mst_war_desc LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_location LIKE '%".$searchTerm. "%' OR
								ast_loc.ast_loc_desc LIKE '%".$searchTerm. "%' OR 
								ast_lvl.ast_lvl_desc  LIKE '%".$searchTerm. "%' OR 
								wko_det.wko_det_work_grp  LIKE '%".$searchTerm. "%' OR 
								wrk_grp.wrk_grp_desc  LIKE '%".$searchTerm. "%' OR 
								emp_mst1.emp_mst_name  LIKE '%".$searchTerm. "%' OR 
								wko_mst.wko_mst_asset_level LIKE '%".$searchTerm. "%' OR
								wko_mst.wko_mst_pm_grp LIKE '%".$searchTerm. "%' 
							)".
							$where_wko_mst_pm_grp.
							$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_wko_det_work_grp.
							$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);
			
	 }
 }

}


function generateUniqueName($file_name,$image_count) {

    // Get current date in Ymd format (YearMonthDay);
	//$date = date("YmdHis");
	//$random_id = rand(100000, 999999);
	// Concatenate date and image count with file extension
    $unique_name = "CMMS_IMAGE_" . $image_count . ".jpeg";
    return $unique_name;
}

if ($valid) {



	returnData($json_return,$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();

}



?>