<?php
	Class PHPRC_SQL_FLAGS
	{
		Const FLAG_NVARCHAR_NULL = "#RAISE_FLAG:NULL;";
		Const FLAG_BIT_NULL = -1;
		Const FLAG_TINYINT_NULL = 0;
		Const FLAG_INT_NULL = -2147483647;
		Const FLAG_BIGINT_NULL = -9223372036854700000;
		Const FLAG_DECIMAL_NULL = -9999999999999990.00;
		Const FLAG_DATETIME_NULL = "1000-01-01 01:00:00.0000000";
		Const FLAG_DIVIDER = "#RAISE_FLAG:DIVIDER;";
		
		/**
		 * Set DBNull value into $DATETIME variable if Null.
		 * @param DateTime $DATETIME_VALUE
		 * @return DateTime
		 */
		Public Static Function REF_FLAG_DATETIME_NULL_REPLACE($DATETIME_VALUE)
		{
			If(isset($DATETIME_VALUE) === False)
			{
				Return PHPVC_DATETIME::VAR_TO_DATETIME(FLAG_DATETIME_NULL);
			}
			Else
			{
				Return $DATETIME_VALUE;
			}
		}
	}

	class PHPRC_SQLI
	{
		
		/**
		 * Returns SQLiConnection. (Do not pass any parameter if you want to use the SQL constants in PHPSettings file)
		 * @param String $DATA_SOURCE <br />[Optional]DataSource to connect e.g. LocalHost.<br />
		 * @param String $DATABASE_NAME<br />[Optional]Name of database to load.<br />
		 * @param String $DATABASE_USER<br />[Optional]SQL database authentication username.<br />
		 * @param String $DATABASE_PASSWORD<br />[Optional]SQL database authentication password.<br />
		 * @return none
		 */
		Public Static Function REF_CONNECT($DATA_SOURCE = "", $DATABASE_NAME = "", $DATABASE_USER = "", $DATABASE_PASSWORD = "")
		{
			Try 
			{
				if
				(
						PHPVC_STR::VAR_IS_BLANK($DATA_SOURCE) === TRUE &&
						PHPVC_STR::VAR_IS_BLANK($DATABASE_NAME) === TRUE &&
						PHPVC_STR::VAR_IS_BLANK($DATABASE_USER) === TRUE &&
						PHPVC_STR::VAR_IS_BLANK($DATABASE_PASSWORD) === TRUE
						)
				{
					$DATA_SOURCE = PHPSET::$SQL_DATA_SOURCE;
					$DATABASE_NAME = PHPSET::$SQL_DATABASE_NAME;
					$DATABASE_USER = PHPSET::$SQL_DATABASE_USER;
					$DATABASE_PASSWORD = PHPSET::$SQL_DATABASE_PASSWORD;
				}
				
				Return Mysqli_Connect($DATA_SOURCE, $DATABASE_NAME, $DATABASE_USER, $DATABASE_PASSWORD);
			}
			Catch(Exception $ERR)
			{
				PHPVC_LOG::VAR_REPORT_ERROR($ERR, 0);
				Throw $ERR;
			}
		}
		
		/**
		 * Begins SQLi Transaction
		 * @param SQLiConnection $SQLI_CONNECTION <br />The open sqli connection.<br />
		 * @return none
		 */
		Public Static Function REF_TRANSACTION_BEGIN($SQLI_CONNECTION)
		{
			// Validate Parameters
			// --------------------------------------
			if($SQLI_CONNECTION == NULL)
			{
				Throw PHPVC_ERROR::VAR_RAISERROR(new Exception(PHPVC_ERROR::VAR_NULL_REFERENCE_EXCEPTION('SQLI_CONNECTION', FALSE)));
			}
			// --------------------------------------
			
			
			// Actual Function
			// --------------------------------------
			try 
			{
				Mysqli_Begin_Transaction($SQLI_CONNECTION);
			}
			catch(Exception $ERR)
			{
				PHPVC_LOG::VAR_REPORT_ERROR($ERR, 0);
				Throw $ERR;
			}
			// --------------------------------------
		}
			
		
		/**
		 * Commits the requests made within the SQLi Transaction
		 * @param SQLiConnection $SQLI_CONNECTION <br />The open sqli connection with transaction.<br />
		 * @return none
		 */
		Public Static Function REF_SQL_TRANSACTION_COMMIT($SQLI_CONNECTION)
		{
			// Validate Parameters
			// --------------------------------------
			if($SQLI_CONNECTION == NULL)
			{
				Throw PHPVC_ERROR::VAR_RAISERROR(new Exception(PHPVC_ERROR::VAR_NULL_REFERENCE_EXCEPTION('SQLI_CONNECTION', FALSE)));
			}
			// --------------------------------------
			
			
			// Actual Function
			// --------------------------------------
			try
			{
				Mysqli_Commit($SQLI_CONNECTION);
			}
			catch(Exception $ERR)
			{
				PHPVC_LOG::VAR_REPORT_ERROR($ERR, 0);
				Throw $ERR;
			}
			// --------------------------------------
		}
		
		
		/**
		 * Rollbacks the requests made within the SQLi Transaction
		 * @param SQLiConnection $SQLI_CONNECTION <br />The open sqli connection with transaction.<br />
		 * @return none
		 */
		public static function REF_SQL_TRANSACTION_ROLLBACK($SQLI_CONNECTION)
		{
			
			// Validate Parameters
			// --------------------------------------
			if($SQLI_CONNECTION == NULL)
			{
				Throw PHPVC_ERROR::VAR_RAISERROR(new Exception(PHPVC_ERROR::VAR_NULL_REFERENCE_EXCEPTION('SQLI_CONNECTION', FALSE)));
			}
			// --------------------------------------
				
				
			// Actual Function
			// --------------------------------------
			try
			{
				Mysqli_Rollback($SQLI_CONNECTION);
			}
			catch(Exception $ERR)
			{
				PHPVC_LOG::VAR_REPORT_ERROR($ERR, 0);
				Throw $ERR;
			}
			// --------------------------------------
		}
	}
	
	Class PHPRC_SQLI_PARAMS
	{
		/**
		 * Creates SQLi Statement (similar to SQLCommand)
		 * @param SQLiConnection $SQLI_CONNECTION <br />Open SQLi Connection<br />
		 * @param String $QUERY_TEXT<br />A query string. e.g. INSERT INTO REGISTRY(name, value) VALUES(:name, :value).<br />
		 * @return none
		 */
		Public Static Function REF_PREPARE($SQLI_CONNECTION, $QUERY_TEXT)
		{
			// Validate Parameters
			// --------------------------------------
			if($SQLI_CONNECTION == NULL)
			{
				Throw PHPVC_ERROR::VAR_RAISERROR(new Exception(PHPVC_ERROR::VAR_NULL_REFERENCE_EXCEPTION('SQLI_CONNECTION', FALSE)));
			}
			PHPVC_AV::VAR_VALIDATE_NULL($QUERY_TEXT, 'QUERY_TEXT');
			// --------------------------------------
				
				
			// Actual Function
			// --------------------------------------
			Try
			{
				
				// Validate Parameter Set
				$PARAMETER_SET = PHPVC_STR::VAR_BETWEEN($QUERY_TEXT, '(', ')');
				$PARAMETER_BLOCK = "";
				WHILE(PHPVC_STR::VAR_CONTAINS($PARAMETER_SET, ":"))
				{
					
					$PARAMETER_BLOCK = PHPVC_STR::VAR_BETWEEN($PARAMETER_SET, ":", ",");
					$PARAMETER_SET = PHPVC_STR::VAR_REPLACE($PARAMETER_SET, ":" . $PARAMETER_BLOCK . ",", "");
					
					// Check if parameter if valid
					if(PHPVC_STR::VAR_CONTAINS($PARAMETER_BLOCK, " "))
					{
						THROW NEW Exception("Invalid parameter format : [" . $PARAMETER_BLOCK . "]");
					}
				}
				
				// Bind parameter set with SQLI
				$SQLI_STATEMENT = Mysqli_Stmt_Init($SQLI_CONNECTION);
				Mysqli_Stmt_Prepare($SQLI_STATEMENT, $QUERY);
				Return $SQLI_STATEMENT;
			}
			Catch(Exception $ERR)
			{
				PHPVC_LOG::VAR_REPORT_ERROR($ERR, 0);
				Throw $ERR;
			}
			// --------------------------------------
		}
		
		/**
		 * Creates SQLi Statement (similar to SQLCommand)
		 * @param SQLiStatement $SQLI_STATEMENT <br />Prepared SQLi Statement.<br />
		 * @param String $PARAM_NAME<br />Parameter name. e.g. :name.<br />
		 * @param String $PARAM_VALUE<br />Parameter value to add (bind).<br />
		 * @return none
		 */
		Public Static Function REF_ADD($SQLI_STATEMENT, $PARAM_NAME, $PARAM_VALUE)
		{
			try 
			{
				Mysqli_Stmt_Bind_Param($SQLI_STATEMENT, "s", $PARAM_NAME, $PARAM_VALUE);
			}
			Catch(Exception $ERR)
			{
				
			}
		}
		
	}
	

		//===================================================================================================
		//				SQL Sessions
		//===================================================================================================

		//--------------------------------------------------------------------------
		//		SQL 02 Part 01 - Open Session (Single threaded process management)
		//--------------------------------------------------------------------------
		function REF_BEGIN_SESSION($SESSION_TITLE, $NUMERIC_PARAM, $STRING_PARAM, $DB_CONNECTION, $DB_TABLE = NULL)
		{
			$DB_TABLE = REF_DEFAULT_SESSION_TABLE($DB_TABLE);
			
			//Destroy old requests
			REF_CLEAN_SESSION($DB_CONNECTION, $DB_TABLE);
			
			// Catch if multiple requests is made
			$SESSION_CODE = VAR_RANDOM_STRING(100);	// Generate unique code (100 chr) for request
			mysqli_query($DB_CONNECTION, "INSERT INTO " . $DB_TABLE . "(`SESSION_TITLE`, `NUMERIC_PARAM`, `STRING_PARAM`, `SESSION_CODE`, `REQUEST_DATE_VAL`, `REQUEST_TIME_VAL`) values('" . VAR_SQLSAFE($SESSION_TITLE) . "', $NUMERIC_PARAM, '" . VAR_SQLSAFE($STRING_PARAM) . "', '" . VAR_SQLSAFE($SESSION_CODE) . "', " . VAR_GET_LOCAL_DATE_VAL() . ", " . VAR_GET_LOCAL_TIME_VAL() . ")");
			
			// Session available
			if(REF_IS_SESSION_AVAILABLE($SESSION_TITLE, $NUMERIC_PARAM, $STRING_PARAM, $SESSION_CODE, $DB_CONNECTION, $DB_TABLE) === true)
			{
				return $SESSION_CODE;
			}
			else
			{
				return false;
			}
		}

		//--------------------------------------------------------------------------
		//		SQL 02 Part 02 - Check Session
		//--------------------------------------------------------------------------
		function REF_IS_SESSION_AVAILABLE($SESSION_TITLE, $NUMERIC_PARAM, $STRING_PARAM, $SESSION_CODE, $DB_CONNECTION, $DB_TABLE = NULL)
		{
			$DB_TABLE = REF_DEFAULT_SESSION_TABLE($DB_TABLE);
			$DBQuery = mysqli_query($DB_CONNECTION, "SELECT * FROM " . $DB_TABLE . " WHERE `SESSION_TITLE` = '" . VAR_SQLSAFE($SESSION_TITLE) . "' AND `NUMERIC_PARAM` = $NUMERIC_PARAM AND `STRING_PARAM` = '" . VAR_SQLSAFE($STRING_PARAM) . "' AND `SESSION_CODE` = '" . VAR_SQLSAFE($SESSION_CODE) . "' ORDER BY `REQUEST_ID` ASC");
			while($DBFetch = mysqli_fetch_array($DBQuery))
			{	
				// Original request;
				if(VAR_SQLFETCH($DBFetch['SESSION_CODE']) == $SESSION_CODE)
				{
					return true;
				}
				// Multi-requests; delete request
				else
				{
					REF_DESTROY_SESSION($SESSION_TITLE, $NUMERIC_PARAM, $STRING_PARAM, $SESSION_CODE, $DB_CONNECTION, $DB_TABLE);
					return false;
				}
			}
		}

		//--------------------------------------------------------------------------
		//		SQL 02 Part 03 - Destroy Session
		//--------------------------------------------------------------------------
		function REF_DESTROY_SESSION($SESSION_TITLE, $NUMERIC_PARAM, $STRING_PARAM, $SESSION_CODE, $DB_CONNECTION, $DB_TABLE = NULL)
		{
			$DB_TABLE = REF_DEFAULT_SESSION_TABLE($DB_TABLE);
			mysqli_query($DB_CONNECTION, "DELETE FROM " . $DB_TABLE . " WHERE `SESSION_TITLE` = '" . VAR_SQLSAFE($SESSION_TITLE) . "' AND `NUMERIC_PARAM` = $NUMERIC_PARAM AND `STRING_PARAM` = '" . VAR_SQLSAFE($STRING_PARAM) . "' AND `SESSION_CODE` = '" . VAR_SQLSAFE($SESSION_CODE) . "'");
		}

		//--------------------------------------------------------------------------
		//		SQL 02 Part 04 - Clean expired Session (GC)
		//--------------------------------------------------------------------------
		function REF_CLEAN_SESSION($DB_CONNECTION, $DB_TABLE = NULL)
		{
			// Retrieve target table
			$DB_TABLE = REF_DEFAULT_SESSION_TABLE($DB_TABLE);
			
			$CURRENT_DATE_VAL = VAR_GET_LOCAL_DATE_VAL();
			$CURRENT_TIME_VAL = VAR_GET_LOCAL_TIME_VAL();
			$REQUEST_ID = 0;
			
			// Search and clean old data
			//-----------------------------------------------------------------------
			$DBQuery = mysqli_query($DB_CONNECTION, "SELECT * FROM " . $DB_TABLE);
			while($DBFetch = mysqli_fetch_array($DBQuery))
			{
				$REQUEST_ID = $DBFetch['REQUEST_ID'];
				
				// Delete advance game session
				if($CURRENT_DATE_VAL == $DBFetch['REQUEST_DATE_VAL'] && ($DBFetch['REQUEST_TIME_VAL'] + REF_SESSION_TIMEOUT()) < $CURRENT_TIME_VAL)
				{
					mysqli_query($DB_CONNECTION,"DELETE FROM " . $DB_TABLE . " WHERE `REQUEST_ID` = $REQUEST_ID"); 
				}
				else if($CURRENT_DATE_VAL < $DBFetch['REQUEST_DATE_VAL'] && $CURRENT_TIME_VAL > REF_SESSION_TIMEOUT())
				{
					mysqli_query($DB_CONNECTION,"DELETE FROM " . $DB_TABLE . "` WHERE `REQUEST_ID` = $REQUEST_ID");
				}
			}
			//-----------------------------------------------------------------------
		}

		//--------------------------------------------------------------------------
		//		SQL 02 Part 05.A - DEFAULT DATABASE
		//--------------------------------------------------------------------------
		function REF_DEFAULT_SESSION_TABLE($DB_TABLE)
		{
			if($DB_TABLE == NULL)
			{
				return 'sys_session_request';
			}
			else
			{
				return $DB_TABLE;
			}
		}

		//--------------------------------------------------------------------------
		//		SQL 02 Part 05.B - Timeout (2x of execution time)
		//--------------------------------------------------------------------------
		function REF_SESSION_TIMEOUT()
		{
			return ini_get('max_execution_time') * 2; 
		}


		//===================================================================================================
		//				SQL Commands
		//===================================================================================================

		//--------------------------------------------------------------------------
		//		SQL 03 - Check if Query is empty
		//--------------------------------------------------------------------------
		function REF_QUERY_BLANK($DB_QUERY)
		{
			if($DB_QUERY === false)
			{
				return true;
			}
			
			if(mysqli_num_rows($DB_QUERY) > 0)
			{
				return false;
			}
			else
			{
				return true;
			}
		}

		//--------------------------------------------------------------------------
		//		SQL 03 - Get Scope Identity
		//--------------------------------------------------------------------------
		function REF_QUERY_INSERT_ID($DB_QUERY)
		{
			return mysqli_insert_id($DB_QUERY);
		}


		//--------------------------------------------------------------------------
		//		SQL 04 - CLOSE QUERY
		//--------------------------------------------------------------------------
		function REF_SQL_QUERY_CLOSE($DB_QUERY, $DB_CONNECTION)
		{
			@mysqli_free_result($DB_QUERY);
			@mysqli_next_result($DB_CONNECTION);
		}

		// CHECKS IF THE SQL CONNECTION IS VALID
		function REF_SQL_IS_OPEN($DB_CONNECTION)
		{
			if ($DB_CONNECTION->connect_errno) 
			{
				return false;
			}
			else if($DB_CONNECTION == null)
			{
				return false;
			}
			else
			{
				return true;
			}
		}

?>