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


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

$site_cd 		= $_REQUEST['site_cd'];
$searchTerm 	= $_REQUEST['searchTerm'];
$Folder			= $_REQUEST['Folder'];
$dvc_id			= $_REQUEST['dvc_id'];
$MobileURL		= $_REQUEST['MobileURL'];
$wrk_sts_typ_cd = $_REQUEST['wrk_sts_typ_cd']; 
$emp_id 		= $_REQUEST['emp_id']; 

$page 			= $_REQUEST['page'];
$pageSize 		= 100;
$offset 		= ($page - 1) * $pageSize;


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

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

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

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

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

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

$sql="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 			= COALESCE(mst_war_desc+' / '+ast_loc_desc+' / '+ast_lvl_desc,''), 
			wko_mst_asset_location 		= COALESCE(ast_loc_desc,''),
			wko_mst_asset_level			= COALESCE(ast_lvl_desc,'') , 
			wko_mst_pm_grp 				= COALESCE(wko_mst_pm_grp,''), 
			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					= COALESCE(wko_ref.attachment,null),
			file_name					= COALESCE(wko_ref.file_name,null)	
								
	FROM 	wko_mst (NOLOCK)     			
		
		INNER 
		JOIN 	wko_det (NOLOCK)
		ON		wko_mst.site_cd = wko_det.site_cd
		AND		wko_mst.rowid = wko_det.mst_rowid
				
		INNER 
		JOIN	wrk_sts (NOLOCK)
		ON		wko_mst.site_cd = wrk_sts.site_cd
		AND		wko_mst.wko_mst_status = wrk_sts.wrk_sts_status
						
		LEFT 
		OUTER 
		JOIN	( Select *, ROW_NUMBER() OVER (PARTITION BY mst_rowid ORDER BY rowid DESC) AS rn
					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 		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%'
				) wko_ref 
		ON wko_mst.site_cd = wko_ref.site_cd AND wko_mst.rowid = wko_ref.mst_rowid AND rn = 1		
	
		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 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. "%' 
				)".$sts_typ_cd.$where_work_group."	order by wko_mst_wo_no OFFSET ".$offset." ROWS FETCH NEXT ".$pageSize." ROWS ONLY";
 

		//echo  $sql;

		$stmt = sqlsrv_query( $conn, $sql);

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

			$json= array();

		do {
			 while ($row = sqlsrv_fetch_array($stmt, 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'];
				$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'];

						$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['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) );
 
		sqlsrv_free_stmt( $stmt);
		sqlsrv_close( $conn);
 
 
if ($stmt) {

	//echo $sql;
	
	returnData($json_return);	
	
}


function returnData($json){
	 $json1 = json_encode($json);
	 //echo$json;
	 
if(empty(json_decode($json1,1))) {
     $returnData = array(
	'status' => 'Error',
	'message' => 'No Records found',
	'data' => $json);
	
	
}else{
	$returnData = array(
	'status' => 'SUCCESS',
	'message' => 'Successfully',
	'data' => $json);
}

	echo json_encode($returnData);
}

function returnError($error_message){
	$json = array();
	
	$returnData = array(
	'status' => 'ERROR',
	'message' => $error_message,
	'data' => $json);
	
	echo json_encode($returnData);
		
}
		

 
?>