<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: GET");
header("Access-Control-Max-Age: 3600");
header(
    "Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With"
);

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'];
 $admin_id = $_REQUEST['EmpID'];
 $cf_query_RowID = array();
 $json_cf_query_list = array();
 $json_cf_query_listPRM_MST = array();
 $json_cf_query_listAll =array();
 $json_cf_quert_list_sort = array();
 $dynamicQueries	 = [];
 $total_count_Prm_mst ='';
 $totalCounts = [];
 $totalWo = '';
 
if ($_SERVER["REQUEST_METHOD"] == "GET") {
    $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
		JOIN
			dsh_uac ON dsh_mst.site_cd = dsh_uac.site_cd AND dsh_mst.RowID = dsh_uac.dsh_RowID
		JOIN
			emp_mst ON dsh_mst.site_cd = emp_mst.site_cd AND dsh_uac.emp_RowID = emp_mst.RowID
		JOIN
			cf_query ON dsh_mst.site_cd = cf_query.site_cd AND dsh_mst.dsh_mst_query = cf_query.cf_query_descs
		WHERE
			dsh_mst.dsh_mst_disable_flag = '0'
			AND dsh_uac.dsh_uac_flag = '1'
			AND emp_mst.site_cd = '".$Site_cd."'
			AND emp_mst.emp_mst_empl_id = '".$admin_id."'";
	
//AND emp_mst.emp_mst_empl_id = '".$admin_id."' ORDER BY dsh_mst.RowID ASC
    $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);
	
	// Second Query if RowID !='' 
	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   
				WHERE cf_query_list.site_cd = '".$Site_cd."'
				AND cf_query_list.mst_RowID = '".$RowID."'
				AND cf_query_list.cf_query_list_type = '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'];
				$json_cf_query_listAll[$mstRowID][] = $row;
				// Exclude records where mst_RowID is 2668
				if ($mstRowID !== '6098') {
					$json_cf_query_list[$mstRowID][] = $row;	
				}else if($mstRowID === '6098'){
					$json_cf_query_listPRM_MST[$mstRowID][] = $row;
				}
			}		
			sqlsrv_free_stmt($stmt_cf_label);
		
		// Get Sorting Data 
			$sql2 = "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   
				WHERE		cf_query_list.site_cd = '".$Site_cd."'
				AND			cf_query_list.mst_RowID = '".$RowID."'
				AND			cf_query_list.cf_query_list_type = 'S'";

			$stmt_cf_label2 = sqlsrv_query($conn, $sql2);

			if (!$stmt_cf_label2) {
				$error_message = "Error selecting table (cf_query_list)";
				returnError($error_message);
				die(print_r(sqlsrv_errors(), true));
			}
			while ($row2 = sqlsrv_fetch_array($stmt_cf_label2, SQLSRV_FETCH_ASSOC)) {
					
				$mstRowID2 = $row2['mst_RowID'];
				$json_cf_quert_list_sort[$mstRowID2][] = $row2;
			}
			sqlsrv_free_stmt($stmt_cf_label2);
		}
		// Get Totla number 
	
	}
	
	foreach ($json_cf_query_list 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'
					]
				)
			) {
				
				
				$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())";
				} 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 .= "'$admin_id'";
			}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'] . " ";
			}
			// Check for the presence of a closing bracket
		}
		
		$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."')";
		$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_listAll as $mst_RowID => $conditions) {
		
		foreach ($conditions as $condition) {
			
				if ($condition['cf_query_list_table'] == 'wko_mst' || $condition['cf_query_list_table'] == 'wko_det') {
					
				} elseif ($condition['cf_query_list_table'] == 'prm_mst') {
			
				$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) {
					// 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'];

				sqlsrv_free_stmt($stmt4);
			
			} else {
            
			}
		}
	}
	//echo "The loop ran $loopCounter times.";
	//$totalCounts["14317"] = $totalWo;
	$totalCounts["6098"] =  $total_count_Prm_mst;
	
	if (!empty($json_dsh_mst)) {
			$responseData = returnData($json_dsh_mst,$cf_query_RowID,$json_cf_query_list,$json_cf_quert_list_sort,$total_count_Prm_mst,$totalCounts,$json_cf_query_listPRM_MST);
			echo json_encode($responseData);
		} else {
			sqlsrv_rollback($conn);
			$error_message = "Transaction rolled back.<br />";
			returnError($error_message);
	}
	sqlsrv_close($conn);
	
}


function returnData($json_dsh_mst,$cf_query_RowID,$json_cf_query_list,$json_cf_quert_list_sort,$total_count_Prm_mst,$totalCounts,$json_cf_query_listPRM_MST)
{
	$returnData = [
        "status" => "SUCCESS",
        "message" => "Successfully",
		"data" => $json_dsh_mst,
		"id_" => $cf_query_RowID,
		"RowDataFltBy" => $json_cf_query_list,
		"RowDataSortBy" => $json_cf_quert_list_sort,
		"PRM_MST" => $total_count_Prm_mst,
		"DashbrdCount" => $totalCounts,
		"RowDataSortPrm_mst" => $json_cf_query_listPRM_MST,
    ];

    return $returnData;
}

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

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

    echo json_encode($returnData);
}

?>