<?php
/*
	1. Install Xampp 8.2.xx
	2. Download SQL Server DLLs & configure MSSQL in xampp 
		- download php driver (https://learn.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server?view=sql-server-ver16)
		- in php.ini add in extension section
			extension=php_pdo_sqlsrv_82_ts_x64.dll
			extension=php_sqlsrv_82_ts_x64.dll  
		- copy above file from php driver and past in xampp/php/ext folder
	3. Configure desired Ports
		- Open httpd.conf file & change
		- change Listen 80 To Listen <port>
		- Servername localhost:80 To Servername localhost:<port>
		- Open  httpd-ssl.conf file & change
		- Listen 443 To To Listen <portSSL>
	4. PHP.ini changes
		- max_input_vars = 7000
		- max multi-body... => 10000
		- max_file_upload => 100
		- max_filesize => 40M
		- max_exec time => 120
		- post_max_size = 120M
		- Extension gd enable
		- zlib.output_compression = On
	5. Install Github & Add Project
	6. SETUP SSL
	7. Configure dbClassMSSQL for 
		- define variables
		- SQL connectivity
	8. Install Crystal Report 8.5 from zip
		- Add serial number from text
		- copy contents of C:\Windows\Crystal to SysWOW64 & System32
	9. Configure CONFIG file in the reports folder of the project.
	10. Install 7zip
	11. Extension gd enable in php.ini for Barcode generation
	12. httpd.conf:
		- LoadModule deflate_module modules/mod_deflate.so
		- LoadModule filter_module modules/mod_filter.so
		- The foll lines in the end
			SetOutputFilter DEFLATE 
			<Directory "V:/xampp/htdocs/tex"> 
				<IfModule mod_deflate.c>
					AddOutputFilterByType DEFLATE text/html
				</IfModule>
			</Directory> 

*/
define ('SITE_ROOT', ""); //
define ('CODE_PATH', "V:\\xampp\\htdocs\\tex\\"); //
define ('REPORT_PATH', "reports\\"); //
define ('URL_ROOT', "https://ai.mindforgeerp.com/"); //
define ('PDF_PATH', "reports/pdf/"); //
define ('TELEGRAM_TOKEN', '8179051405:AAFlGQ_o3gH9g4XDgaMt1_aNcQ2Zv0cIFW8');

error_reporting(E_ERROR | E_PARSE);	

header("X-Frame-Options: SAMEORIGIN");
header('X-XSS-Protection: 1; mode=block');
header('X-Content-Type-Options: nosniff');
header_remove("X-Powered-By");

//header("Access-Control-Allow-Origin: https://capapi.kreonsolutions.in/");
header('Access-Control-Allow-Credentials: true');
header('Access-Control-Max-Age: 1200');    // cache for 20mins
// header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST');
// header("Access-Control-Allow-Headers: X-Requested-With");        
// header('Access-Control-Allow-Headers: Origin, Content-Type, X-Auth-Token , Authorization');


// Set session lifetime (e.g., 1 hour = 3600 seconds)
ini_set('session.gc_maxlifetime', 7200);
// Optional: Set session cookie lifetime to match
ini_set('session.cookie_lifetime', 7200);

$k_debug = 0;

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

if($_SESSION['Category'] == 2){
	ini_set('session.gc_maxlifetime', 14400);
	ini_set('session.cookie_lifetime', 14400);
}
// echo ini_get("session.gc_maxlifetime");php_value session.gc_maxlifetime 3600



class db {
	// single instance of self shared among all instances
	private static $instance = null;
	private static $instanceClient = null;
	private $conn = null;
	private $env = 1; 	//0 = Localhost; 1 = Live Server
	private $checkTableFields = 0; 	//Enabling this would activate check in Insert & Update queries for CreatedAt, CreatedBy, UpdatedAt, UpdatedBy

	private $user = "root";
	private $pass = "";
	private $dbName = "klabs_kframe";
	private $dbHost = "localhost";

	private $user1 = "sa";
	private $pass1 = "d`5&M7C4W;xR>p+L'=D:s2";
	private $dbName1 = "Main";
	private $dbHost1 = "localhost,4545";
	public $enableLog = 0;
	
	//This method must be static, and must return an instance of the object if the object does not already exist.
	public static function getInstanceMaster() {
		if (!self::$instance instanceof self) {	
			self::$instance = new self(0);	//0 added for MASTER 
		}
		return self::$instance;
	}
	//This method must be static, and must return an instance of the object if the object does not already exist.
	public static function getInstance() {
		if (!self::$instanceClient instanceof self) {	
			self::$instanceClient = new self(1);	//1 added for Client 
		}
		return self::$instanceClient;
	}
	// The clone and wakeup methods prevents external instantiation of copies of the Singleton class, thus eliminating the possibility of duplicate objects.
	public function __clone() {
		trigger_error('Clone is not allowed.', E_USER_ERROR);
	}
	
	public function __wakeup() {
		trigger_error('De-serializing is not allowed.', E_USER_ERROR);
	}
	
	function __destruct() {
		if (ob_get_contents()) ob_end_flush();
        //ob_end_flush();
		//print "Destroying " . __CLASS__ . "\n";
		//parent::__destruct();
    }
	
	public function __construct($dbType) {
		// echo "Database" .  $_SESSION['dbName'] . "<br />uid" . $_SESSION['dbCompanyName'];
		//$dbType => 0 for Master, 1 for Client
		if($dbType == 0){
			if($this->env == 1){
				$connectionInfo = array("Database" => $this->dbName1,"uid" => $this->user1,"pwd" => $this->pass1,"ConnectionPooling" => "1","MultipleActiveResultSets"=>'0');	
				$this->conn = sqlsrv_connect($this->dbHost1, $connectionInfo);
				// parent::__construct($this->dbHost1, $this->user1, $this->pass1, $this->dbName1);
			}else{	
				$connectionInfo = array("Database" => $this->dbName,"uid" => $this->user,"pwd" => $this->pass,"ConnectionPooling" => "1","MultipleActiveResultSets"=>'0');	
				$this->conn = sqlsrv_connect($this->dbHost,$connectionInfo);
				// parent::__construct($this->dbHost, $this->user, $this->pass, $this->dbName);
			}
		}

		if($dbType == 1){
			if(isset($_SESSION['ConnectionError'])){
				$_SESSION['ConnectionError']="";
			}
			$connectionInfo = array("Database" => $_SESSION['dbName'],"uid" => $_SESSION['dbUser'],"pwd" => $_SESSION['dbPass'],"ConnectionPooling" => "1","MultipleActiveResultSets"=>'0');	
			$this->conn = sqlsrv_connect($_SESSION['dbHost'],$connectionInfo);
			// parent::__construct($_SESSION['dbHost'], $_SESSION['dbUser'], $_SESSION['dbPass'], $_SESSION['dbName']);
			// parent::set_charset('utf8');
		}
		// print_r($this->conn);
		if ($this->conn === false) {
			$error_array = sqlsrv_errors();
			$_SESSION['ConnectionError'] = "error";
			// exit('Connect Error (' . $error_array[0]['message'] . ') ');
			
			echo $url = $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI']; // Get the current page URL
			if (strpos($url, 'company.php') !== false) {
				//DO NOT REDIRECT
			} else {
				echo '<script>window.location.replace("company.php");</script>';
				exit;
			}
		}
		// parent::set_charset('utf-8');
	}
	
	public function query_valid($query){
		if($this->query($query)){
			return true;
		}
	} 
	
	public function get_result($query) {
		$result = $this->query($query);
		if ($result->num_rows > 0){
			//$row = $result->fetch_assoc();
			return $result;
		}else{
			return null;
		}
	}

	public function db_sp_select($sp, $set, $val) {
		$output = array();
		$output['error'] = "1";
		$params = array();
		$options = array("Scrollable" => SQLSRV_CURSOR_FORWARD);
		$output['error_statement'] = "";
	  
		// if (!(strlen($sp) > 0 && sizeof($set) > 0 && sizeof($set) == sizeof($val))) {
		//   $output['error_statement'] = "Invalid Arguments";
		//   return $output;
		// }
		
		$query = "EXEC " . trim($sp) . " ";
		
		if ((strlen($sp) > 0 && sizeof($set) > 0 && sizeof($set) == sizeof($val))) {
			for ($i = 0; $i < sizeof($set); $i++) {
				$tempVal = trim($val[$i]);

				if($tempVal[0] == '\'' && $tempVal[strlen($tempVal) - 1 == '\'']){
					$tempVal = substr($tempVal, 1, -1);
					$tempVal = $this->real_escape_string($tempVal);
					$val[$i] = "'".$tempVal."'";

				}

				$query .= $set[$i] . "=" . $val[$i];
				
				if ($i != sizeof($set) - 1) {
					$query .= ",";
				}
			}
		}
	

		date_default_timezone_set('Asia/Calcutta'); 
		$devLog  = PHP_EOL."-------------------------".PHP_EOL."User: ".$_SESSION['email'].' - '.$_SERVER['REMOTE_ADDR'] . ' - ' . date("F j, Y, g:i a")." => Data: ".json_encode($_REQUEST).PHP_EOL. ( "https://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]" ).PHP_EOL.$query;
		// echo $query;
		// exit();
		// $query = "EXEC stye_code_2177 @whereCondition=' 1=1 AND (departmentid= 0 or 0= 0 ) and (subdepartmentid= 0 or 0= 0 ) AND department LIKE ''%local%'' AND subdepartment LIKE ''%retail%'' ORDER BY operationid asc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY',@isFilter=1,@username='shri'";
		if ($result = sqlsrv_query($this->conn, $query, $params, $options)) {
			  $output['error'] = "0";
			  $output['result_set'] = array(); // Changed to store results in an array of arrays
			  do {
				$result_set = array(); // Create an array for each result set
				while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
					// if (!empty($row)) {
						$result_set[] = $row;
					// }
				}
				if(sizeof($result_set) > 0){
					$output['result_set'][] = $result_set; // Add the result set to the output array
				}
				// Check for more result sets
				$more_results = sqlsrv_next_result($result);
			} while ($more_results);

		  	sqlsrv_free_stmt($result);
		} else {
		  $error_array = sqlsrv_errors();
		  $output['error_statement'] = "Error: " . $error_array[0]['message'];
		  $devLog .= PHP_EOL . $output['error_statement'];
		//   print_r(sqlsrv_errors());
		}
		file_put_contents('./logs/'.$_SESSION['email'].'.log', $devLog, FILE_APPEND);
		$this->addToSqlProfiler($query);
		return $output;
	}

	public function addToSqlProfiler($query){
		$userCategory = isset ($_SESSION['Category']) ? $_SESSION['Category'] : 0;
		if($userCategory == 2){
			$Username = $_SESSION['email'];
			$Filename = './logs/dev_'.$Username . '.devlog' ;
			if(file_exists($Filename)){
				file_put_contents($Filename, PHP_EOL . "<br />-------------------------<br />" . PHP_EOL . $query, FILE_APPEND);
			}
		}
	}

	//SELECT Fn
	public function db_select($query, $limit=0, $offset=0) { ///returns "result_set, error, error_statement, num_rows
		if($limit > 0){
			// Use regex to safely inject TOP after SELECT
			$query = preg_replace('/^\s*SELECT\s/i', "SELECT TOP $limit ", $query, 1);
		}
		
		date_default_timezone_set('Asia/Calcutta'); 
		$devLog  = PHP_EOL."-------------------------".PHP_EOL."User: ".$_SESSION['email'].' - '.$_SERVER['REMOTE_ADDR'] . ' - ' . date("F j, Y, g:i a")." => Data: ".json_encode($_REQUEST).PHP_EOL. ( "https://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]" ).PHP_EOL.$query;
		$output = array();
		$output['result_set'] = array();
		$output['error'] = "1";
		$params = array();
		$options =  array( "Scrollable" => SQLSRV_CURSOR_FORWARD);	//CHANGED THIS BY MITESH, AS IT INCREASED THE SPEED OF QUERY USED IN REPORT
		// $options = array();
		// echo $query;				
		// $this->set_charset("utf-8");
		$output['error_statement'] = "";
		if ($result = sqlsrv_query($this->conn,$query)) {
			$output['error'] = "0";
			//$output['num_rows'] = sqlsrv_num_rows($result);		//DOESNT WORK WHEN USED SQLSRV_CURSOR_FORWARD
			while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){
				$output['result_set'][] = $row;
			}
			$output['num_rows'] = sizeof($output['result_set']);	//ADDED AS num_rows doesnt work
			sqlsrv_free_stmt($result);
		} else {
			$error_array = sqlsrv_errors();
			$output['error_statement'] =  "Error: " . $error_array[0]['message'];
			$devLog .= PHP_EOL . $output['error_statement'];
			// echo "<br /><br />Error: " . $query . print_r(sqlsrv_errors());  // will check again
		}
		// print_r($output); exit();
		file_put_contents('./logs/'.$_SESSION['email'].'.log', $devLog, FILE_APPEND);
		$this->addToSqlProfiler($query);
		return $output;
	}
	//UPDATE Fn
	public function db_update($query, $deleteFormID = 0) { ///SAME for DELETE & UPDATE
		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		$params = array();
    	$options =  array('Scrollable' => SQLSRV_CURSOR_FORWARD);
		$deleteFormID = $deleteFormID > 0 ? $deleteFormID : $_POST['FormID'];


		if(strpos($_SERVER['REQUEST_URI'],"/db.php") !== false){
			$this->enableLog = 1;
		}


		
		// if(isset($_POST['FormID'])){			
		// 	if($_POST['FormID'] == 3667)
		// 		echo "hi";
		// 		exit();
		// 	}
		// }
		
		// SELECT TO GET OLD DATA
		// COMPARE OLD DATA WITH NEW FOR CHANGES
		// LOG NEW DATA IN LOGS TABLE

		//log eventry record
		if($this->enableLog == 1){
			//If DELETE query
			if (strpos(strtoupper($query), 'DELETE') !== false) {
				$extractQuery = extractTableAndCondition($query,'DELETE');
			}else{ // If UPDATE query
				// Extract table name and WHERE condition from $query
				$extractQuery = extractTableAndCondition($query,'UPDATE');
			}

			// Output the extractQuery
			if ($extractQuery) {
				$tableName = $extractQuery['table'];
				$whereCondition = $extractQuery['where'];

				if (strpos(strtoupper($query), 'UPDATE') !== false) {

					//extract whereCondition & get only id from this
					$entryId = explode("=",$whereCondition);

					// SELECT TO GET OLD DATA
					$query1  = "SELECT * from $tableName where $whereCondition";
					$query1Result = db::getInstance()->db_select($query1);

					//Define variable for oldData
					$oldData = $query1Result['result_set'][0];

					//TO GET NEW DATA
					$newData = $extractQuery['new_data'];

					// Unset 'UpdatedAt' and 'UpdatedBy' from $oldData
					unset($oldData['UpdatedAt']);
					unset($oldData['UpdatedBy']);

					// 2. Compare Old Data with New Data
					$changes = []; // Array to store changes

					foreach ($newData as $key => $new_value) {
						// Check if the key exists in old_data
						if(is_array($oldData)){
							if (array_key_exists($key, $oldData)) {
								
								// Check if the value in oldData is a DateTime object
								if ($oldData[$key] instanceof DateTime) {
									$old_value = $oldData[$key]->format('Y-m-d'); // Format DateTime to string
								} else {
									$old_value = $oldData[$key]; // Keep the value as it is if it's not a DateTime object
								}

								// Check if the new value is a DateTime object
								if ($new_value instanceof DateTime) {
									$new_value = $new_value->format('Y-m-d'); // Format DateTime to string
								}

								// Now compare the values
								if ($old_value != $new_value) {
									$changes[] = [
										'field' => $key,
										'old_value' => $old_value,
										'new_value' => $new_value
									];
								}
							}
						}
					}

					// print_r($changes);
					// echo "<pre></pre>";
					// exit();

					// 3. Log Changes in Logs Table if there are changes
					if (!empty($changes)) {
						// Insert logs for changes
						foreach ($changes as $change) {
							$field = $change['field'];
							$old_value = $change['old_value'];
							$new_value = $change['new_value'];

							// LOG NEW DATA IN LOGS TABLE
							$log_sql = "INSERT INTO logs (field, Mode, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy) 
									VALUES ('$field', 'EDIT', '$old_value', '$new_value', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $entryId[1], $entryId[1], '{$_POST['FormID']}', '{$_SESSION['user_id']}')";
							$Result = db::getInstance()->db_select($log_sql);
						}

					} 
				}else{
					// Check if the string contains an assignment (field = value) with an "AND" condition
					if (strpos($whereCondition, 'AND') !== false) {
						// Regular expression to match any field and its associated value (e.g., entryid = 1, bid = 5, etc.)
						preg_match('/\b\w+\s*=\s*\d+/', $whereCondition, $matches);

						// Print the matched part (e.g., entryid = 1)
						if (isset($matches[0])) {
							$entryId = explode("=",$matches[0]);  // Output: entryid = 1 (or any other field=value)
						}
					}else{
						//extract whereCondition & get only id from this
						$entryId = explode("=",$whereCondition);
					}

					// SELECT TO GET OLD DATA
					$query1  = "SELECT id from $tableName where $whereCondition";
					$query1Result = db::getInstance()->db_select($query1);

					for($j=0; $j<count($query1Result['result_set']); $j++){
						$query2  = "SELECT * from $tableName where id= '".$query1Result['result_set'][$j]['id']."'";
						$query2Result = db::getInstance()->db_select($query2);

						//Define variable for oldData
						$oldData = $query2Result['result_set'];
				
						$json_oldData = json_encode($oldData);

						if($json_oldData != 'null'){
							// LOG NEW DATA IN LOGS TABLE
							$log_sql = "INSERT INTO logs (Mode, field, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy) 
									VALUES ('DELETE', '', '$json_oldData', '', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $entryId[1], '{$query1Result['result_set'][$j]['id']}', '{$deleteFormID}', '{$_SESSION['user_id']}')";			
							$Result = db::getInstance()->db_select($log_sql);
						}
					}	
				}	
			}
		}


		date_default_timezone_set('Asia/Calcutta'); 
		$devLog  = PHP_EOL."-------------------------".PHP_EOL."User: ".$_SESSION['email'].' - '.$_SERVER['REMOTE_ADDR'] . ' - ' . date("F j, Y, g:i a")." => Data: ".json_encode($_REQUEST).PHP_EOL. ( "https://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]" ).PHP_EOL.$query;
		if ($result = sqlsrv_query($this->conn,$query,$params,$options)) {
			$output['error'] = "0";
			$output['num_rows'] = sqlsrv_rows_affected($result);
		} else {
			$error_array = sqlsrv_errors();
			$output['error_statement'] =  "Error: " . $error_array[0]['message'];
			$devLog .= PHP_EOL . $output['error_statement'];
		}
		file_put_contents('./logs/'.$_SESSION['email'].'.log', $devLog, FILE_APPEND);
		$this->addToSqlProfiler($query);
		return $output;
	}
	//INSERT Fn
	public function db_insert($table, $set, $val) { ///returns should have at least 1 set & set==val
		
		if(strpos($_SERVER['REQUEST_URI'],"/db.php") !== false){
			$this->enableLog = 1;
		}

		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		if(!(strlen($table) > 0 && sizeof($set) > 0 && sizeof($set) == sizeof($val))){
			$output['error_statement'] = "Invalid Arguments";
			return $output;
		}
		// if($checkTableFields){
		// 	//Check if the columns exists in the table

		// 	//if not exists, ALTER TABLE
			
		// 	$s1 = "INSERT INTO ".$table." (CreatedBy, CreatedAt"; 
		// 	$s2 = " $_SESSION['user_id'] , GETDATE() " ;
		// }else{
		// 	$s1 = "INSERT INTO ".$table." ("; 
		// 	$s2 = " ";
		// }

		$s1 = "INSERT INTO ".$table." ("; 
		$s2 = " ";

		$separator = "";
		for($i = 0; $i < sizeof($set); $i++){
			$s1 .= $separator . $set[$i];

			//Updated by pornima on 5-11-2024
			if(strpos($val[$i], 'SqlQueryOnInsert') !== false){
				$s2 .= $separator . str_replace('SqlQueryOnInsert', '', $val[$i]);
		
			}else if(strpos($set[$i], 'uniqueid') !== false){
				$s2 .= $separator . "" . $val[$i];
			}else{
				$s2 .= $separator . "'" . $this->real_escape_string($val[$i]) . "'";
			}
			// $s2 .= $separator . "'" . mysqli_real_escape_string($this, $val[$i]) . "'";
			$separator = ",";
		}
		$sql = $s1 . " ) Values( " . $s2 . ")";
		
		// echo "<br>".$sql;
		// exit();
		$sql = $sql . " SELECT SCOPE_IDENTITY() as id";
		date_default_timezone_set('Asia/Calcutta'); 
		$devLog  = PHP_EOL."-------------------------".PHP_EOL."User: ".$_SESSION['email'].' - '.$_SERVER['REMOTE_ADDR'] . ' - ' . date("F j, Y, g:i a")." => Data: ".json_encode($_REQUEST).PHP_EOL. ( "https://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]" ).PHP_EOL.$sql;
		if ($result = sqlsrv_query($this->conn, $sql)) {
			sqlsrv_next_result($result);
			$row = sqlsrv_fetch_array($result);
			$output['error'] = "0";
			$output['last_id'] = $row["id"]; //returns 0 if PK not set/AI

			//Check log is enabled
			if($this->enableLog == 1){

				if($_POST['editID']){
					$editid = $_POST['editID'];
				}else{
					$editid = $output['last_id'];
				}
				// LOG NEW DATA IN LOGS TABLE
				$log_sql = "INSERT INTO logs (Mode, field, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy) 
				VALUES ('ADD', '', '', '', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $editid, {$output['last_id']}, '{$_POST['FormID']}', '{$_SESSION['user_id']}')";
				// exit();
				$Result = db::getInstance()->db_select($log_sql);
			}

		} else {
			$output['error'] = "1";
			$error_array = sqlsrv_errors();
			$output['error_statement'] =  "Error: " . $error_array[0]['message'];
			$devLog .= PHP_EOL . $output['error_statement'];
		}
		file_put_contents('./logs/'.$_SESSION['email'].'.log', $devLog, FILE_APPEND);
		$this->addToSqlProfiler($sql);
		return $output;
	}

	public function real_escape_string($value){	
		//COMMENTED BY MITESH ON 14/10/24 NEED OTHER SOLUTIONS ASAP**********************************************************************
		// $value = preg_replace('/[^A-Za-z0-9\-\_\?\=\.\&\s\(\)\:+]/', '', $value);
		// $value = str_replace ("'","''",$value);
		$value = preg_replace_callback("/(?<!')'(?!')/", function($matches) {
			return "''";
		}, $value);
		return $value;
	}	
	//INSERT Fn with Query
	public function db_insertQuery($query) {  //returns should have at least 1 set 
		// if($_POST['FormID'] == 2989){
		// 	ech
		// }
		// if(isset($_POST['FormID'])){
		// 	if($_POST['FormID'] == 3667){
		// 		$this->enableLog = 1;
		// 		echo "<br><br>Query ".$query;
		if(strpos($_SERVER['REQUEST_URI'],"/db.php") !== false){
			$this->enableLog = 1;
		}
		// 	}
		// }
		$sql = $query;
		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		if(!(strlen($query) > 0)){
			$output['error_statement'] = "Invalid Arguments";
			return $output;
		}
		
		date_default_timezone_set('Asia/Calcutta'); 
		$devLog  = PHP_EOL."-------------------------".PHP_EOL."User: ".$_SESSION['email'].' - '.$_SERVER['REMOTE_ADDR'] . ' - ' . date("F j, Y, g:i a")." => Data: ".json_encode($_REQUEST).PHP_EOL. ( "https://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]" ).PHP_EOL.$query;
		
		//$s1 = "INSERT INTO ".$table." ("; 
		//$s2 = " ";
		//$separator = "";
		//for($i = 0; $i < sizeof($set); $i++){
		//	$s1 .= $separator . $set[$i];
		//	$s2 .= $separator . "'" . mysqli_real_escape_string($this, $val[$i]) . "'";
		//	$separator = ",";
		//}
		//$sql = $s1 . " ) Values( " . $s2 . ")";

		//Check log is enabled
		if($this->enableLog == 1){
			// Check if the query contains INSERT or UPDATE (case-insensitive)
			if(stripos($sql, 'UPDATE') !== false){
				// Find the position of the "SELECT SCOPE_IDENTITY()" part in the query
				$selectPos = strpos($sql, "SELECT SCOPE_IDENTITY()");

				// If "SELECT SCOPE_IDENTITY()" is found, remove it and everything after
				if ($selectPos !== false) {
					$sql = substr($query, 0, $selectPos);  // Keep everything before "SELECT SCOPE_IDENTITY()"
				}

				// Extract table name and WHERE condition from $query
				$extractQuery = extractTableAndCondition($sql,'UPDATE');

				// Output the extractQuery
				if ($extractQuery) {
					$tableName = $extractQuery['table'];
					$whereCondition = $extractQuery['where'];

					// SELECT TO GET OLD DATA
					$query1  = "SELECT * from $tableName where $whereCondition";
					$query1Result = db::getInstance()->db_select($query1);

					// Query to get the primary key column name from the database
					$query3 = "SELECT * FROM kmaingrid WHERE TableName = '".$tableName."'";
					$query3Result = db::getInstanceMAster()->db_select($query3);

					//extract whereCondition & get only id from this
					$entryId = explode("=",$whereCondition);
					$editid = $query1Result['result_set'][0][$query3Result['result_set'][0]['TablePrimaryKey']];
					$gridIdValue = $query3Result['result_set'][0]['GridId'];
					if (strpos(strtoupper($sql), 'UPDATE') !== false) {
						//Define variable for oldData
						$oldData = $query1Result['result_set'][0];

						//TO GET NEW DATA
						$newData = $extractQuery['new_data'];

						// Unset 'UpdatedAt' and 'UpdatedBy' from $oldData
						unset($oldData['UpdatedAt']);
						unset($oldData['UpdatedBy']);

						// 2. Compare Old Data with New Data
						$changes = []; // Array to store changes

						foreach ($newData as $key => $new_value) {
							// Check if the key exists in old_data
							if(is_array($oldData)){
								if (array_key_exists($key, $oldData)) {
									
									// Check if the value in oldData is a DateTime object
									if ($oldData[$key] instanceof DateTime) {
										$old_value = $oldData[$key]->format('Y-m-d'); // Format DateTime to string
									} else {
										$old_value = $oldData[$key]; // Keep the value as it is if it's not a DateTime object
									}

									// Check if the new value is a DateTime object
									if ($new_value instanceof DateTime) {
										$new_value = $new_value->format('Y-m-d'); // Format DateTime to string
									}

									// Now compare the values
									if ($old_value != $new_value) {
										$changes[] = [
											'field' => $key,
											'old_value' => $old_value,
											'new_value' => $new_value
										];
									}
								}
							}
						}

						// 3. Log Changes in Logs Table if there are changes
						if (!empty($changes)) {
							// Insert logs for changes
							foreach ($changes as $change) {
								$field = $change['field'];
								$old_value = $change['old_value'];
								$new_value = $change['new_value'];

								// LOG NEW DATA IN LOGS TABLE
								$log_sql = "INSERT INTO logs (field, Mode, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy, GridID) 
										VALUES ('$field', 'GRID EDIT', '$old_value', '$new_value', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $editid, $entryId[1], '{$_POST['FormID']}', '{$_SESSION['user_id']}',$gridIdValue)";
								
								$Result = db::getInstance()->db_select($log_sql);
							}

						} 
					
					}
				}
			}
		}
		$sql = $sql . " SELECT SCOPE_IDENTITY() as id";
		if ($result = sqlsrv_query($this->conn, $sql)) {
			sqlsrv_next_result($result);
			$row = sqlsrv_fetch_array($result);
			$output['error'] = "0";
			
			// $rowsAffected = sqlsrv_rows_affected($result); //this code added for getting affected row because all inserted id can't get. So on affectedrow count we can get row inserted id
			// $rowsAffected = $rowsAffected;
			$output['last_id'] = $row['id']; //returns 0 if PK not set/AI

			// sqlsrv_free_stmt($result);

			//Check log is enabled
			if($this->enableLog == 1){
				// Check if the query contains INSERT or UPDATE (case-insensitive)
				if(stripos($sql, 'INSERT') !== false){
					// Find the position of the "SELECT SCOPE_IDENTITY()" part in the query
					$selectPos = strpos($sql, "SELECT SCOPE_IDENTITY()");

					// If "SELECT SCOPE_IDENTITY()" is found, remove it and everything after
					if ($selectPos !== false) {
						$sql = substr($query, 0, $selectPos);  // Keep everything before "SELECT SCOPE_IDENTITY()"
					}

					//get count of insert query
					// Regular expression to count occurrences of a row (based on the `VALUES` keyword)
					preg_match_all('/\(\d+,.*?\)/', $sql, $matches);

					// Output the count of rows
					$rowsAffected = count($matches[0]);

					// Use a regular expression to extract the table name after 'INSERT INTO'
					preg_match('/INSERT INTO\s+([a-zA-Z0-9_]+)/i', $sql, $matches);

					// Extracted table name will be in $matches[1]
					$tableName = $matches[1];

					// Query to get the primary key column name from the database
					$query3 = "SELECT * FROM kmaingrid WHERE TableName = '".$tableName."'";
					$query3Result = db::getInstanceMAster()->db_select($query3);

					//Not getting inserted id so using select query getting this
					$query4 = "SELECT TOP $rowsAffected id FROM $tableName WHERE CreatedBy= '{$_SESSION['user_id']}' OR UpdatedBy= '{$_SESSION['user_id']}' ORDER BY id DESC ";
					$query4Result = db::getInstance()->db_select($query4);

					// Check if query result is correct
					if (isset($query3Result['result_set'][0]['TablePrimaryKey'])) {
						$matches = [];

						// Use regular expression to match the first value in the VALUES clause
						preg_match('/VALUES\s*\((.*)\)/s', $sql, $matches);
						if (isset($matches[1])) {
							// $matches[1] contains the values part of the query
							$values = $matches[1];

							// Split values by commas, but don't split inside quotes
							$pattern = '/\s*,\s*(?=(?:[^"]*"[^"]*")*[^"]*$)/';
							$valuesArray = preg_split($pattern, $values);

							// Clean up quotes and whitespace around the values
							$cleanValuesArray = array_map(function($value) {
								return trim($value, " \t\n\r\0\x0B'\"");
							}, $valuesArray);

							// Extract the first value
							$editid = $cleanValuesArray[0];
							echo $editid;
						}
				
						for($i=count($query4Result['result_set'])-1; $i >= 0; $i--){
							
							$entryIdValue = $query4Result['result_set'][$i]['id'];
							$gridIdValue = $query3Result['result_set'][0]['GridId'];
							// LOG NEW DATA IN LOGS TABLE
							$log_sql = "INSERT INTO logs (Mode, field, oldValue, newValue, CompanyId, DivisionId, YearCode, EditId, EntryId ,FormID, CreatedBy, GridID) 
							VALUES ('GRID ADD', '', '', '', '{$_SESSION['dbCompany']}', '{$_SESSION['dbDivision']}', '{$_SESSION['dbYear']}', $editid, $entryIdValue, '{$_POST['FormID']}', '{$_SESSION['user_id']}',$gridIdValue)";
							$Result = db::getInstance()->db_select($log_sql);
						}
		
					}

				}
			}
			
		} else {
			$output['error'] = "1";
			$error_array = sqlsrv_errors();
			$output['error_statement'] =  "Error: " . $error_array[0]['message'];
			$devLog .= PHP_EOL . $output['error_statement'];
		}
		file_put_contents('./logs/'.$_SESSION['email'].'.log', $devLog, FILE_APPEND);
		$this->addToSqlProfiler($query);
		return $output;
	}	

	public function db_create_table($query) { 
		date_default_timezone_set('Asia/Calcutta'); 
		$devLog  = PHP_EOL."-------------------------".PHP_EOL."User: ".$_SESSION['email'].' - '.$_SERVER['REMOTE_ADDR'] . ' - ' . date("F j, Y, g:i a")." => Query: ".PHP_EOL.$query;
		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		
		// Attempt to execute the create table query
		if ($result = sqlsrv_query($this->conn, $query)) {
			$output['error'] = "0";  // Success
			$output['message'] = "Table created successfully.";
		} else {
			// Capture the error details
			$error_array = sqlsrv_errors();
			$output['error_statement'] = "Error: " . $error_array[0]['message'];
			$devLog .= PHP_EOL . $output['error_statement'];
		}
	
		// Log the details
		file_put_contents('./logs/'.$_SESSION['email'].'.log', $devLog, FILE_APPEND);
		$this->addToSqlProfiler($query);
		return $output;
	}	
}
?>