<?php
define ('SITE_ROOT', ""); //added on 18 01 2021

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

// echo "=> " . print_r($_SERVER); exit();
//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');

$k_debug = 0;

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

function makeDir($path){
     return is_dir($path) || mkdir($path);
}

/*function extractAttribute($att_list, $att_name){
	//$mediaFields[$mf]["folder"] = "img/"; 
	//if($k_debug) echo "<br />OTHER Attributes: " . $att_list;
	$temp = explode("|", $att_list); //Other Attributes added on 18 01 2021 
	for($t = 0; $t < sizeof($temp); $t++){
		if(strrpos($temp[$t], $att_name) !== FALSE){ //Match found
			$str = substr($temp[$t], strlen($att_name . '="'), strlen($temp[$t]) - 12);
			if(strlen($str) > 0) return $str;
			break;
		}
	}
	return null;
}*/
function extractAttribute($att_list, $att_name){
    // Use regex to find the attribute in the format 'att_name="value"'
	// echo $att_list;
    if (preg_match('/' . preg_quote($att_name) . '="([^"]*)"/', $att_list, $matches)) {
        return $matches[1];
    }
    return null;
}

function extractParameters($params){	//FOR ADDING PARAMETERS IN kmainforms. To be used on list view
	/*
		ycode=SESSION(yearcode)
		//ycode=VARIABLE(yearcode)
		ycode=DEFAULT(2324)
		ycode=FIELD(dbfieldname) //POST value copy
	*/
	//echo "=========================>>>>>>>>>>" . $params;
	// where division = 1 AND (ycode = '2324' OR ycode = '2425') AND Company = 1
	// where division=DEFAULT(1) AND (ycode=SESSION(ycode) OR ycode = '2425') AND Company = 1

	$finalParams = "";
	$temp = explode("|", $params);
	
	for($t = 0; $t < sizeof($temp); $t++){
		if (preg_match('/=(.*?)\(/', $temp[$t], $match) == 1) {
			$function = trim($match[1]);
			if (preg_match('/\((.*?)\)/', $temp[$t], $val) == 1) {
				$value=$val[1];
			}
			$var = substr($temp[$t], 0, strpos($temp[$t], "="));

			if($function == "SESSION"){
				return $_SESSION[$value];
			}
			if($function == "DEFAULT"){
				return $value;
			}
			if($function == "FIELD"){
				return $value;
			}
		}else{
			// echo "<br />" . $temp[$t];
		}
	}
}
function getSPParams($var){
	$spname = preg_replace("/\([^)]+\)/","",$var);
	$sp['name'] = $spname;
	
	$params = substr($var , strpos($var, '(')+1, strpos($var,')') - 1);
	$spParams = str_replace(')','',$params);

	$dataArray = explode(',',$spParams);
	$set = [];
	$val = [];
	for($j=0; $j<count($dataArray); $j++){
	    $element = explode("=",$dataArray[$j]);
	    array_push($set,$element[0]);
	    array_push($val,$element[1]);
	}
	$sp['params'] = $set;
	$sp['values'] = $val;
	
	//$var = Grid_Populate_1162_fld1(@PRODID=sabid,@tmp=qualityID)
	// $sp['name'] = 'Grid_Populate_1162_fld1';
	// $sp['params'] = array('@PRODID','@tmp' );
	// $sp['values'] = array('sabid','qualityID');
	return $sp;
}


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 = "Erp@123";
	//private $dbName1 = "erp";
	//private $dbHost1 = "116.73.25.184,9998";

	private $user1 = "sa";
	private $pass1 = "Erp@123";
	private $dbName1 = "Main";
	// private $dbHost1 = "103.29.116.147,4545";	//THIS IS FOR REMOTE CONNECTION NOT SQL
	private $dbHost1 = "localhost,4545";	
	

	// db connection config vars 
	// private $user1 = "kreoninprimary_krdemoadmin";
	// private $pass1 = "Cjo%Oq&%!zea";
	// private $dbName1 = "kreoninprimary_krdemo";
	// private $dbHost1 = "localhost";
	// private $dbDebug = 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']="";
				// unset($_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');
		}
		
		if ($this->conn === false) {
			$error_array = sqlsrv_errors();
			$_SESSION['ConnectionError'] = "error";
			// exit('Connect Error (' . $error_array[0]['message'] . ') ');
			echo '<script>window.location.replace("company.php");</script>';
		}
		
		// 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);
		return $output;
	}

	//SELECT Fn
	public function db_select($query) { ///returns "result_set, error, error_statement, num_rows
		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);
		return $output;
	}
	//UPDATE Fn
	public function db_update($query) { ///SAME for DELETE & UPDATE
		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		$params = array();
    	$options =  array('Scrollable' => SQLSRV_CURSOR_FORWARD);

		//extract tablename between UPDATE & SET
		//Concat updatedat & updatedby before WHERE
		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);
		return $output;
	}
	//INSERT Fn
	public function db_insert($table, $set, $val) { ///returns should have at least 1 set & set==val
	
		$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 $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
		} 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);
		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 
		$sql = $query;
		$output = array();
		$output['error'] = "1";
		$output['error_statement'] = "";
		if(!(strlen($query) > 0)){
			$output['error_statement'] = "Invalid Arguments";
			return $output;
		}
		$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.$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 . ")";
		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
		} 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);
		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);
		return $output;
	}
	
}
/*
// Path to your log file $logFile = './logs/' . $_SESSION['email'] . '.log'; // Read the existing contents of the file $existingContent = file_get_contents($logFile); // Prepend the new log data $newContent = $devLog . $existingContent; // Write the new content back to the file file_put_contents($logFile, $newContent);
*/
?>