<?php

require_once('config.php');
$error_message;
$valid = true;

/* 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'];
 
  $json_cf_query_list = array();
  $cf_query_RowID = array();
  $json_cf_query_list_prm_mst = array();
  $json_cf_query_list_wko_mst =array();
  $json_cf_query_list_wkr_mst =array();
  $totalCounts = array();
  $json_work_grp = array();

 
if ($_SERVER["REQUEST_METHOD"] == "GET") {
	
	$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));
	}

 
	while ($row = sqlsrv_fetch_array($stmt_work_group, SQLSRV_FETCH_ASSOC)) {
			
		$json_work_grp[] = $row;
			
	}
	
	sqlsrv_free_stmt($stmt_work_group);
	
	$work_groups = array();
	foreach ($json_work_grp as $item) {
		$work_groups[] = $item['emp_ls5_work_group'];
	}
	
	$in_clause = "('" . implode("','", $work_groups) . "')";
	
	if(!empty($json_work_grp)){
		
		
		
		$where_work_group_wko_mst = "AND(wko_det_work_grp IN $in_clause )";	
		$where_work_group_wkr_mst = "AND(wkr_mst_work_group IN $in_clause )";	
	}else{
		$where_work_group_wko_mst = "";	
		$where_work_group_wkr_mst = "";	
	}

	
	

	
		
	$sql = "SELECT
			dsh_mst.site_cd,
			dsh_mst.dsh_mst_module_cd,
			dsh_mst.dsh_mst_title,
			dsh_mst.dsh_mst_desc,
			dsh_mst.dsh_mst_object_name,
			dsh_mst.dsh_mst_datastore,
			dsh_mst.dsh_mst_submodule,
			dsh_mst.dsh_mst_query,
			dsh_mst.dsh_mst_start_value,
			dsh_mst.dsh_mst_end_value,
			dsh_mst.dsh_mst_critical_value,
			dsh_mst.dsh_mst_alert_value,
			dsh_mst.dsh_mst_target_value,
			dsh_mst.dsh_mst_display_order,
			dsh_mst.dsh_mst_disable_flag,
			dsh_mst.audit_user,
			dsh_mst.audit_date,
			dsh_mst.column1,
			dsh_mst.column2,
			dsh_mst.column3,
			dsh_mst.column4,
			dsh_mst.column5,
			dsh_mst.RowID,
			cf_query.RowID as cf_query_RowID 
			
		FROM dsh_mst (NOLOCK)	
		
		LEFT 
		OUTER 
		JOIN		dsh_uac (NOLOCK)						
		ON			dsh_mst.site_cd = dsh_uac.site_cd
		AND			dsh_mst.RowID = dsh_uac.dsh_RowID

		LEFT 
		OUTER 
		JOIN		emp_mst (NOLOCK)						
		ON			dsh_mst.site_cd = emp_mst.site_cd
		AND			dsh_uac.emp_RowID = emp_mst.RowID

		LEFT 
		OUTER 
		JOIN		cf_query (NOLOCK)						
		ON			dsh_mst.site_cd = cf_query.site_cd
		AND			dsh_mst.dsh_mst_query = cf_query.cf_query_title
		AND			dsh_mst.dsh_mst_submodule = cf_query.cf_query_submodule
		
		WHERE
			dsh_mst.dsh_mst_disable_flag = '0'
			AND dsh_uac.dsh_uac_flag = '1'
			AND emp_mst.site_cd = '".$Site_cd."'
			and dsh_mst_submodule IN ('wko_mst','wko_det','wkr_mst')
			AND emp_mst.emp_mst_empl_id = '".$EmpID."' order by dsh_mst_display_order";

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

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

	$json_dsh_mst = array();
 
	while ($row = sqlsrv_fetch_array($stmt_dsh_mst, SQLSRV_FETCH_ASSOC)) {
			
		$json_dsh_mst[] = $row;
		$cf_query_RowID[] = $row['cf_query_RowID'];
			
	}
	
	sqlsrv_free_stmt($stmt_dsh_mst);
	
	if(!empty($cf_query_RowID)){
		
		foreach ($cf_query_RowID as $RowID) {
			
			$sql = "SELECT cf_query_list.site_cd,   
					cf_query_list.mst_RowID,   
					cf_query_list.cf_query_list_type,   
					cf_query_list.cf_query_list_table,   
					cf_query_list.cf_query_list_column,   
					cf_query_list.cf_query_list_data_type,   
					cf_query_list.cf_query_list_prompt,
					cf_query_list.cf_query_list_operator,   
					cf_query_list.cf_query_list_value,   
					cf_query_list.cf_query_list_logical,   
					cf_query_list.cf_query_list_order_by,   
					cf_query_list.cf_query_list_o_bracket,
					cf_query_list.cf_query_list_c_bracket,  
					cf_query_list.audit_user,   
					cf_query_list.audit_date,   
					cf_query_list.RowID 
				FROM cf_query_list (NOLOCK)
				WHERE cf_query_list.site_cd = '".$Site_cd."'
				AND cf_query_list.mst_RowID = '".$RowID."'
				AND cf_query_list.cf_query_list_type IN ('F')";
				
			$stmt_cf_label = sqlsrv_query($conn, $sql);
			
			if (!$stmt_cf_label) {
				$error_message = "Error selecting table (cf_query_list)";
				returnError($error_message);
				die(print_r(sqlsrv_errors(), true));
			}

			while ($row = sqlsrv_fetch_array($stmt_cf_label, SQLSRV_FETCH_ASSOC)) {
				$mstRowID = $row['mst_RowID'];
				$cf_query_list_table = $row['cf_query_list_table'];
				$json_cf_query_list[$mstRowID][] = $row;
				
				if($cf_query_list_table === 'prm_mst'){
					
					$json_cf_query_list_prm_mst[$mstRowID][] = $row;
					
				}else if($cf_query_list_table === 'wko_mst' || $cf_query_list_table === 'wko_det'){
					
					$json_cf_query_list_wko_mst[$mstRowID][] = $row;
					
				}else if($cf_query_list_table === 'wkr_mst'){
					
					$json_cf_query_list_wkr_mst[$mstRowID][] = $row;
					
				}
				
			}		
			sqlsrv_free_stmt($stmt_cf_label);
			
		}
		

		foreach ($json_cf_query_list_wko_mst as $mstRowID => $conditions) {
			
			

			
			
			$dynamicConditions ="";
			
			foreach ($conditions 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 .= "(";
				
				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 (strpos(strtolower($value), 'month') !== false) {
						// Replace "month()" with SQL expression to get current month
						$condition['cf_query_list_value'] = str_replace('month', 'DATEPART(MONTH, GETDATE())', $value);
					}

						
					if (strtoupper($condition['cf_query_list_operator']) == "IS NOT"){
						$dynamicConditions .= "" . $condition['cf_query_list_column'] . "  is not NULL";
					}else {
											
						if (strpos($value, 'month') !== false) {
							
							$dynamicConditions .= "MONTH(CONVERT(DATETIME, CONVERT(VARCHAR, " . $condition['cf_query_list_column'] . ", 101))) " . $condition['cf_query_list_operator'] . " ".$condition['cf_query_list_value'] ."";
						
						}else{
							
							$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))";
							
						}
					}
					
				} 
				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()') {
						$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 .= ")";
				if ($condition['cf_query_list_c_bracket'] !== null) {
					$dynamicConditions .= $condition['cf_query_list_c_bracket'];
				}

				if ($index < count($conditions) - 1) {
					$dynamicConditions .= " " . $conditions[$index]['cf_query_list_logical'] . " ";
				}
				
			}
			
			
			
			
			$query = "SELECT COUNT(*) as total_count FROM wko_mst (NOLOCK), wko_det (NOLOCK), wrk_sts (NOLOCK) WHERE 
			( " . $dynamicConditions; $query .= " ) 
			AND (wko_mst.site_cd = wko_det.site_cd) 
			AND (wko_mst.rowid = wko_det.mst_rowid) 
			AND (wko_mst.site_cd = wrk_sts.site_cd) 
			AND (wko_mst.wko_mst_status = wrk_sts.wrk_sts_status) 
			AND (wko_mst.site_cd = '".$Site_cd."')"
			.$where_work_group_wko_mst;
			$query = str_replace('"','',$query);

			
			$stmt = sqlsrv_query($conn, $query);
			if (!$stmt) {
				$error_message = "Error executing query";
				returnError($error_message);
				die(print_r(sqlsrv_errors(), true));
			}

			$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
			$totalCounts[$mstRowID] = $row['total_count'];

			sqlsrv_free_stmt($stmt); 
			
			
		}
		
		foreach ($json_cf_query_list_prm_mst as $mst_RowID => $conditions) {
			
			foreach ($conditions as $condition) {
				
				$query = "SELECT COUNT(*) AS total_count FROM prm_mst WITH (NOLOCK) ";
				$query .= "INNER JOIN prm_det WITH (NOLOCK) ON prm_mst.site_cd = prm_det.site_cd AND prm_mst.rowid = prm_det.mst_rowid ";
				$query .= "LEFT OUTER JOIN prm_grp WITH (NOLOCK) ON prm_mst.site_cd = prm_grp.site_cd AND prm_mst.prm_mst_pm_grp = prm_grp.prm_grp_grp_cd ";
				$query .= "LEFT OUTER JOIN ast_mst WITH (NOLOCK) ON prm_mst.site_cd = ast_mst.site_cd AND prm_mst.prm_mst_assetno = ast_mst.ast_mst_asset_no ";
				$whereClause = "WHERE ";
				
				foreach ($conditions as $condition) {

					$mst_RowID = $condition['mst_RowID'];
					
					// Check if the table is 'prm_mst'
					if ($condition['cf_query_list_table'] == 'prm_mst') {
						// Add the logical operator (AND/OR) if it's not the first condition
						if ($whereClause !== "WHERE ") {
							$whereClause .= $condition['cf_query_list_logical'] . ' ';
						}

						// Add the condition to the WHERE clause
						$whereClause .= "(" . $condition['cf_query_list_column'] . " " . $condition['cf_query_list_operator'] . " ";

						// If the value is a function (e.g., today()), leave it as is; otherwise, wrap in quotes
						$value = ($condition['cf_query_list_value'] == 'today()') ? "GETDATE()" : "'{$condition['cf_query_list_value']}'";

						 $whereClause .= $value . ")";
					}
				}
				
				$whereClause .= " AND (prm_mst.site_cd = '".$Site_cd."')";
				$query .= $whereClause;
				$stmt4 = sqlsrv_query($conn, $query);
				if (!$stmt4) {
					$error_message = "Error executing query";
					returnError($error_message);
					die(print_r(sqlsrv_errors(), true));
				}
				$row = sqlsrv_fetch_array($stmt4, SQLSRV_FETCH_ASSOC);
				
				$total_count_Prm_mst = $row['total_count'];
				$totalCounts[$mst_RowID] =  $total_count_Prm_mst;

				sqlsrv_free_stmt($stmt4);
				
				
			}
		}
		
		foreach ($json_cf_query_list_wkr_mst as $mstRowID => $conditions) {
			
			$dynamicConditions ="";
			
			foreach ($conditions 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 .= "(";
				
				if (
					in_array(
						$condition['cf_query_list_column'],
						[
							'wkr_mst_org_date',
							
							
						]
					)
				) {
					
					
					$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'] == 'wkr_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())";
					} 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'";
				}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()') {
						$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 .= ")";
				if ($condition['cf_query_list_c_bracket'] !== null) {
					$dynamicConditions .= $condition['cf_query_list_c_bracket'];
				}

				if ($index < count($conditions) - 1) {
					$dynamicConditions .= " " . $conditions[$index]['cf_query_list_logical'] . " ";
				}
				
			}
			
			$query = "SELECT COUNT(*) as total_count FROM wkr_mst (NOLOCK), wkr_det (NOLOCK) WHERE ( " . $dynamicConditions;
			$query .= " ) AND (wkr_mst.site_cd = wkr_det.site_cd) AND (wkr_mst.rowid = wkr_det.mst_rowid)  AND (wkr_mst.site_cd = '".$Site_cd."')".$where_work_group_wkr_mst;
			$query = str_replace('"','',$query);

			
			$stmt = sqlsrv_query($conn, $query);
			if (!$stmt) {
				$error_message = "Error executing query";
				returnError($error_message);
				die(print_r(sqlsrv_errors(), true));
			}

			$row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
			
			
			$total_count_wkr_mst = $row['total_count'];
			
			$totalCounts[$mstRowID] = $total_count_wkr_mst;

			sqlsrv_free_stmt($stmt); 
			
			
		}
		
		
		
		
		//echo '<pre>'; print_r($totalCounts); echo '</pre>';
		
	}
		
}

returnData($json_dsh_mst,$json_cf_query_list,$totalCounts);

function returnData($json_dsh_mst,$json_cf_query_list,$totalCounts)
	{
		$returnData = [
			"status" => "SUCCESS",
			"message" => "Successfully",
			"data"=>$json_dsh_mst,
			"RowDataFltBy" => $json_cf_query_list,
			"DashbrdCount" => $totalCounts,
			
		];

		

		echo json_encode($returnData);
	}

function returnError($error_message)
{
    $json = array();

    $returnData = array(
        'status' => 'ERROR',
        'message' => $error_message,
        'data' => $json
    );

    echo json_encode($returnData);
}

?>