<?php

require_once ('config.php');
require_once('f_resize_imagepath.php');
$error_message;
$valid = true;
$json_return = array();
$json_img_link = array();

/* Begin the transaction. */
if ( sqlsrv_begin_transaction( $conn ) === false ) {
     die( print_r( sqlsrv_errors(), true ));
}

 
$Site_cd 	= $_REQUEST['site_cd'];
$EmpID 		= $_REQUEST['EmpID'];
$emp_det_work_grp = $_REQUEST['emp_det_work_grp'];
$Folder		= $_REQUEST['Folder'];
$dvc_id		= $_REQUEST['dvc_id'];
$MobileURL	= $_REQUEST['MobileURL'];
$page 		= $_REQUEST['page'];
$pageSize 	= 30;

$offset = ($page - 1) * $pageSize;
$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\\";	


$jsonData = file_get_contents('php://input');
$data = json_decode($jsonData, true);

	

	if ($_SERVER["REQUEST_METHOD"] == "POST") {
		
		
		$sql = "Select 	emp_ls5_work_group
			From	emp_mst (NOLOCK), emp_ls5 (NOLOCK)
			Where 	emp_mst.site_cd = emp_ls5.site_cd
			And		emp_mst.rowid = emp_ls5.mst_rowid
			And		emp_mst.site_cd = '".$Site_cd."'
			And		emp_mst.emp_mst_empl_id = '".$EmpID."'";

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

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

	  $json_work_grp = array();
		while ($row = sqlsrv_fetch_array($stmt_work_group, SQLSRV_FETCH_ASSOC)) {
				
			$json_work_grp[] = $row;
				
		}
		
		sqlsrv_free_stmt($stmt_work_group);
		
		
		
		if (empty($json_work_grp)) {
			
				$where_work_group = "";	
				
		} else {
			
			
			$work_groups = array();
			foreach ($json_work_grp as $item) {
				$work_groups[] = $item['emp_ls5_work_group'];
			}
			
			$in_clause = "('" . implode("','", $work_groups) . "')";
			
			
			
			if(!empty($in_clause)){
				$where_work_group = "AND(wko_det_work_grp IN $in_clause )";	
			}else{
				$where_work_group = "";	
			}
		}
		
		
		
		$dynamicConditions = "";
		
		if(!empty($data['rows'])){
			
			foreach ($data['rows'] as $index => $condition) {
				
			
				// Check for the presence of an opening bracket
				if ($condition['cf_query_list_o_bracket'] !== null) {
					$dynamicConditions .= $condition['cf_query_list_o_bracket'];
				}
				
				$dynamicConditions .= "(";
				// $dynamicConditions .= $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " ";

				if (
					in_array(
						$condition['cf_query_list_column'],
						[
							'wko_mst_org_date',
							'wko_mst_due_date',
							'wko_det_cmpl_date',
							'wko_det_clo_date',
							'wko_det_wr_org_date',
							'wko_det_wr_due_date',
							'wko_det_sc_date',
							'wko_det_sched_date',
							'wko_det_exc_date',
							'wko_det_datetime2',
							'wko_det_datetime3',
							'wko_det_datetime4',
							'wko_det_datetime5',
							'wko_mst_create_date'
						]
					)
				) 
				
				{			

					$value = strtolower(trim($condition['cf_query_list_value']));

					if (strpos($value, 'today()') !== false) {
						// Replace "today()" with "GETDAY()" while keeping the offset
						$condition['cf_query_list_value'] = str_replace('today()', 'GETDATE()', $value);
					}

					if (strtoupper($condition['cf_query_list_operator']) == "IS NOT"){
						$dynamicConditions .= "" . $condition['cf_query_list_column'] . "  is not NULL";
					}else {
						//$dynamicConditions .= "(" . $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " " . $condition['cf_query_list_value'] . ")";
						$dynamicConditions .= "CONVERT(DATETIME, CONVERT(VARCHAR, " . $condition['cf_query_list_column'] . ", 101)) " . $condition['cf_query_list_operator'] . "CONVERT(DATETIME, CONVERT(VARCHAR, ".$condition['cf_query_list_value'] .", 101))";
					}				
			
					/* $dateValue = DateTime::createFromFormat('d/m/Y', $condition['cf_query_list_value']);
					
			
					if ($dateValue) {
						$dateValue = $dateValue->format('Y-m-d');
						$dynamicConditions .= "(CONVERT(DATETIME, CONVERT(VARCHAR, " . $condition['cf_query_list_column'] . ", 101)) " . $condition['cf_query_list_operator'] . " '" . $dateValue . "')";
					}elseif (
						$condition['cf_query_list_column'] == 'wko_mst_due_date' &&
						(strtolower($condition['cf_query_list_value']) == 'getdate()' || strtolower($condition['cf_query_list_value']) == 'today()')
					) {
						// Handle condition for comparing with GETDATE() or 'today()'
						$dynamicConditions .= "(" . $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " GETDATE())";
					} elseif (
						$condition['cf_query_list_column'] == 'wko_mst_org_date' &&
						(strtolower($condition['cf_query_list_value']) == 'getdate()' || strtolower($condition['cf_query_list_value']) == 'today()')
					) {
						// Handle condition for comparing with GETDATE() or 'today()'
						$dynamicConditions .= "(" . $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " GETDATE())";
					}elseif (strtoupper($condition['cf_query_list_operator']) == "IS NOT"){
						$dynamicConditions .= "" . $condition['cf_query_list_column'] . "  is not NULL";
					}else {
						$dynamicConditions .= "'INVALID_DATE_FORMAT'";
					} */
				}
				elseif ($condition['cf_query_list_value'] == 'gs_empl_id') {
						$dynamicConditions .= $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " ";		
						$dynamicConditions .= "'$EmpID'";
				}elseif ($condition['cf_query_list_value'] == 'gs_work_group') {
					if (strtoupper($condition['cf_query_list_operator']) == "LIKE") {
						if(empty($emp_det_work_grp) || $emp_det_work_grp === null){
							
							$dynamicConditions .= $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " ";	
							$dynamicConditions .= "'%".$condition['cf_query_list_value']."%'";
						}else{
							$dynamicConditions .= $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " ";	
							$dynamicConditions .= "'%" .$emp_det_work_grp . "%'";
						}
					}else{
						if(empty($emp_det_work_grp) || $emp_det_work_grp === null){
							$dynamicConditions .= $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " ";	
							$dynamicConditions .= "'".$condition['cf_query_list_value']."'";
						}else{
							$dynamicConditions .= $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " ";	
							$dynamicConditions .= "'" .$emp_det_work_grp . "'";
						}
					}
				}
				else {
					$dynamicConditions .= $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " ";
					if (strtoupper($condition['cf_query_list_operator']) == "LIKE" || strtoupper($condition['cf_query_list_operator']) == "NOT LIKE") {
						
						$dynamicConditions .= "'%" . $condition['cf_query_list_value'] . "%'";
						
					} elseif ($condition['cf_query_list_column'] == 'wko_mst_due_date' && strtolower($condition['cf_query_list_value']) == 'getdate()') {
						
						// Remove single quotes around 'getdate()'
						$dynamicConditions .= "GETDATE()";
						
					} elseif($condition['cf_query_list_column'] == 'wko_mst_due_date' && $condition['cf_query_list_operator'] == '<' && strtolower($condition['cf_query_list_value']) == 'today()'){
						
						$dynamicConditions .= "GETDATE()";
						
					}elseif (strtoupper($condition['cf_query_list_value']) == 'NULL') {
						// Handle NULL values without quotes
						$dynamicConditions .= "NULL";
						
					} else {
						
						$dynamicConditions .= "'" . $condition['cf_query_list_value'] . "'";
						
					}
				}

			$dynamicConditions .= ")";

			// Check for the presence of a closing bracket
			if ($condition['cf_query_list_c_bracket'] !== null) {
				$dynamicConditions .= $condition['cf_query_list_c_bracket'];
			}
			// if ($index > 0) {
				// $dynamicConditions .= " " . $condition['cf_query_list_logical'] . " ";
			// }
			if ($index < count($data['rows']) - 1) {
				 $dynamicConditions .= " " . $data['rows'][$index]['cf_query_list_logical'] . " ";
						
			} 
		}


			$query = "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,''),
			file_name					= COALESCE(wko_ref.file_name,'')					
					
					
			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 (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 " . $dynamicConditions;
			$query .= " AND (wko_mst.site_cd = '".$Site_cd."') " .$where_work_group. "ORDER BY wko_mst_create_date DESC OFFSET ".$offset." ROWS FETCH NEXT ".$pageSize." ROWS ONLY";
			$query = str_replace('"','',$query);
			
			$stmt = sqlsrv_query($conn, $query); 
			if (!$stmt) {
				$error_message = "Error selecting table (wko_det)";
				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;
						
						writeFile($exportFullPathFileName, $Photo);
						writeFile($exportFullPathTNFileName, $Photo);
						
						// try {
							
							////Resize and write the thumbnail
							// $maxWidth = 250;  // Maximum width of the thumbnail
							// $maxHeight = 250; // Maximum height of the thumbnail
							// $thumbnail = resizeImage($Photo, $maxWidth, $maxHeight);

							// $fileExtension = strtolower(pathinfo($file_Name, PATHINFO_EXTENSION));
							// switch ($fileExtension) {
								// case 'jpeg':
								// case 'jpg':
								// case 'jfif':
									// imagejpeg($thumbnail, $exportFullPathTNFileName, 75); // Quality: 0-100
									// break;
								// case 'png':
									// imagepng($thumbnail, $exportFullPathTNFileName, 6);  // Compression level: 0-9
									// break;
								// case 'gif':								
									// imagegif($thumbnail, $exportFullPathTNFileName);
									// break;
								// default:
									// throw new Exception("Unsupported image format: " . $fileExtension);
							// }
							// imagedestroy($thumbnail);
						// } catch (Exception $e) {
							////error_log($e->getMessage());
							////echo "Error processing image";
							// $error_message = "Error processing image";
							// returnError($error_message);
							
						// }

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


		}

	 
		
	   
	}

	returnData($json_return);

	

	function returnData($json_return)
	{
		$returnData = [
			"status" => "SUCCESS",
			"message" => "Successfully",
			"data"=>$json_return
			
		];

		

		echo json_encode($returnData);
	}

	function returnError($error_message)
	{
		$json = [];

		$returnData = [
			"status" => "ERROR",
			"message" => $error_message,
			"data" => $json,
		];

		echo json_encode($returnData);
		exit();
	}

?>
