<?php
###############################################################################
# Database.php
#
#
# @author Anil Kumar <akumar@codepunch.com>
# @link   https://codepunch.com
#
############################################################################### 

namespace 	CodePunch\DB;

use 		CodePunch\Base\Util as UTIL;
use			CodePunch\Base\Text as TEXT;
use 		CodePunch\Base\CPLogger;
use 		Exception;
use 		Doctrine\DBAL\Schema\SchemaException;
use 		Doctrine\DBAL\Driver\PDOException;

###############################################################################

class Database {
	public $connection_params 	= null;
	public $config				= null;
	public $connection			= null;
	public $tableprefix			= "";
	
	const	READONLY_TABLES		= 0;
	const	DROP_TABLES			= 1;
	const	REPAIR_TABLES		= 2;
	const   FRESH_TABLES		= 3;	// Will Drop and Recreate Tables
	const   STANDARD_INIT       = self::READONLY_TABLES;
	
	###########################################################################
	
	private function initConnectionParameters($connparams) {
		// Set compatible charsets
		if(!isset($connparams['charset'])) {
			if($connparams['driver'] == "sqlsrv")
				$connparams['charset'] = "utf-8";
			else if($connparams['driver'] == "pdo_mysql" || $connparams['driver'] == "mysqli")
				$connparams['charset'] = "utf8mb4";
			else if($connparams['driver'] == "oci8")
				$connparams['charset'] = "AL32UTF8";
			else if($connparams['driver'] != "sqlsrv")
				$connparams['charset'] = "utf8";
		}
		if($connparams['driver'] == "pdo_sqlsrv" || $connparams['driver'] == "sqlsrv") {
			$connparams['driverOptions'] = ['TrustServerCertificate'  => true];
		}
		return $connparams;
	}
	
	###########################################################################
	
	private function initPlatforms() 
	{
		// Set the Date format for Oracle
		$platform = $this->connection->getDatabasePlatform();
		$platformName = $platform->getName();
		// ::WARNING:: PLATFORM DEPENDENT CODE //
		if(strtolower($platformName) == "oracle") {
			try {
				$sql = "ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS' NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZH:TZM'";
				$stmt = $this->connection->prepare($sql);
				$stmt->execute();
				return true;
			}
			catch(\Exception $e) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error($e->getMessage());
				return false;
			}
		}
		return true;
	}
	
	###########################################################################
	
	public function getPlatformName()
	{
		$platform = $this->connection->getDatabasePlatform();
		return strtolower($platform->getName());
	}
	
	###########################################################################
	
	private function initColumnSettings($column) 
	{
		if(!isset($column['options']))
			 $column['options'] = array();
		foreach($column['options'] as $ok=>&$ov) {
			if(!is_array($ov)) {
				$lov = strtolower($ov);
				if($lov == "true" || $lov == "false")
					$ov = UTIL::str_to_bool($ov);
				if($lov == "null")
					$ov = NULL;
				if($lov == "empty")
					$ov = "";
			}
		}
		
		// ::WARNING:: PLATFORM DEPENDENT CODE //
		$platform = $this->connection->getDatabasePlatform();
		$platformName = strtolower($platform->getName());
		if($platformName == "mssql" || $platformName == "sqlite" || $platformName == "postgresql") {
			if(isset($column['options']['customSchemaOptions']['collation'])) {
				//$logger = new \CodePunch\Base\CPLogger();
				//$logger->debug("MSSQL/SQLite/PostgreSQL doesn't support custom collation");
				unset($column['options']['customSchemaOptions']['collation']);
			}
		}
		return $column;
	}
	
	###########################################################################
	
	public function getConnection() 
	{
		if($this->connection == null) {
			try {
				if($this->connection_params != null) {
					$this->config = new \Doctrine\DBAL\Configuration();
					$this->connection = \Doctrine\DBAL\DriverManager::getConnection($this->connection_params, $this->config);
					if($this->connection) {
						$this->connection->connect();
						if($this->connection->isConnected()) {
							$this->initPlatforms();
						}
					}
				}
				else {
					throw new Exception(TEXT::get("config_missing_connection_info"));
				}
			}
			catch(\PDOException $e) {
				throw $e;
			}
		}
		return $this->connection;
	}
	
	###########################################################################
	
	public function constructConnectionURL($params) 
	{
		if(UTIL::iskeys_set($params, array('dbname', 'user', 'password', 'host', 'driver'))) {
			if(strtolower($params['driver']) == "pdo_sqlite") {
				if(!UTIL::starts_with($params['dbname'], UTIL::get_install_folder_path()) && !UTIL::starts_with($params['dbname'], DIRECTORY_SEPARATOR))
					$params['dbname'] = UTIL::get_install_folder_path() . $params['dbname'];
				return "sqlite:///" . $params['dbname'];
			}
			else
				return $params['driver'] . "://" . $params['user'] . ":" . $params['password'] . "@" . $params['host'] . "/" . $params['dbname'];
		}
		else
			throw new Exception(TEXT::get("config_missing_connection_info"));
	}
	
	###########################################################################
	
	public function setConnectionURL($url) 
	{
		$this->connection_params = array(
			'url' => $url
		);
	}
	
	###########################################################################
	
	public function setConnectionParams($params) 
	{
		$params = $this->initConnectionParameters($params);
		if(isset($params['driver']) && isset($params['dbname'])) {
			if(strtolower($params['driver']) == "pdo_sqlite") {
				$url = $this->constructConnectionURL($params);
				$this->setConnectionURL($url);
				return;
			}
		}
		$this->connection_params = $params;
	}
	
	###########################################################################
	
	public function createTablesFromDefinitions($tabledatafolder, $action=Database::READONLY_TABLES)
	{
		if($tabledatafolder[strlen($tabledatafolder)-1] != DIRECTORY_SEPARATOR)
			$tabledatafolder .= DIRECTORY_SEPARATOR;
		$files = UTIL::find_all_matched_files($tabledatafolder, "*.xml");
		foreach($files as $file) {
			$tabledatafile = $tabledatafolder . $file;
			$table_data = array();
			$dbdata = file_get_contents($tabledatafile);
			if(!function_exists("simplexml_load_string"))
				throw new Exception(TEXT::get("config_missing_php_simplexml")); 
			$xml = simplexml_load_string($dbdata);
			if(isset($xml->table))
			{
				$t = json_decode(json_encode($xml), true);
				$schema = new \Doctrine\DBAL\Schema\Schema();
				if(isset($t['table'])) {
					$tables = $t['table'];
					if(isset($tables[0])) {
						foreach($tables as $tab) 
							$this->createTableFromDefinition($tab, $action);
					}
					else
						$this->createTableFromDefinition($tables, $action);
				}
			}
		}
	}
	
	###########################################################################
	
	public function createTableFromDefinition($tab, $action=Database::READONLY_TABLES) {
		if(isset($tab['name'])) {
			$tablename = $this->tableprefix . $tab['name'];
			if($action & Database::DROP_TABLES)
				$this->dropTable($tablename);
			if(isset($tab['column']) && ($action&Database::REPAIR_TABLES)) {
				$columns = $tab['column'];
				$initdata = isset($tab['init']) ? $tab['init'] : "";
				$keys = array();
				if(isset($tab['keys'])) {
					$keys = $tab['keys'];
				}
				$this->createTable($tablename, $columns, $initdata, $keys, $action);
			}
		}
	}
	
	###########################################################################
	
	public function createTable($tablename, $columninfo, $initdata, $keys=array())
	{
		if($columninfo != null && $this->connection != null)
		{
			$platform = $this->connection->getDatabasePlatform();
			$platformName = strtolower($platform->getName());
			$schema = new \Doctrine\DBAL\Schema\Schema();
			$schemaManager = $this->connection->getSchemaManager();
			if ($schemaManager->tablesExist(array($tablename)) !== true) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->info("Creating Table $tablename");
				if(UTIL::is_cli())
					UTIL::print("Creating Table $tablename");
				$thetable = $schema->createTable($tablename);
				foreach($columninfo as $column) {
					try {
						$column = $this->initColumnSettings($column);
						$thecolumn = $thetable->addColumn($column['name'], $column['type'], $column['options']);
						if(isset($column['options']['autoincrement'])) {
							$autoinc = UTIL::str_to_bool($column['options']['autoincrement']);
							if($autoinc && $platformName != "mysql" && $platformName != "sqlite" && $platformName != "postgresql") {
								try {
									$statement = $this->connection->prepare("DROP SEQUENCE {$tablename}_seq");
									$statement->execute();
								}
								catch(Exception $e) {
									$logger = new \CodePunch\Base\CPLogger();
									$logger->debug($e->getMessage());
								}
								$thecolumn->setAutoincrement($autoinc);
							}
						}
					}
					catch(\Exception $e) {
						// Critical Error, One or more tables were not created.
						$logger = new \CodePunch\Base\CPLogger();
						$logger->error($e->getMessage());
					}
				}

				$keyidx = 1;
				foreach($keys as $key=>$value) {
					try {
						if(strtolower($key) == "primary")
							$thetable->setPrimaryKey(array($value));
						else if(strtolower($key) == "unique") {
							if(!is_array($value))
								$value = array($value);
							foreach($value as $colname) {
								$colname = explode("|", $colname);
								$thetable->addUniqueIndex($colname, $tablename . "_{$keyidx}_uidx");
								$keyidx++;
							}
						}
						else if(strtolower($key) == "index") {
							if(!is_array($value))
								$value = array($value);
							foreach($value as $colname) {
								$colname = explode("|", $colname);
								$thetable->addIndex($colname, $tablename . "_{$keyidx}_idx");
								$keyidx++;
							}
						}
					}
					catch(Exception $e) {
						$logger = new \CodePunch\Base\CPLogger();
						if(stripos($e->getMessage(), "already has a") === false)
							$logger->error($e->getMessage());
					}
				}
				$platform = $this->connection->getDatabasePlatform();
				$queries = $schema->toSql($platform);
				foreach($queries as $q) {
					try {
						$statement = $this->connection->prepare($q);
						$statement->execute();
					}
					catch(Exception $e) {
						// Critical
						$logger = new \CodePunch\Base\CPLogger();
						$logger->error($e->getMessage());
						
						$platform = $this->connection->getDatabasePlatform();
						$platformName = strtolower($platform->getName());
						$logger->error("$platformName: $q");
					}
				}
				if($initdata != "")
				{
					// $initdata contains CSVs with each row separated by 
					// a || and the first row is the header
					$initdata = str_ireplace("SQL:", "", $initdata);
					$initdata = str_ireplace("\r\n", " ", $initdata);
					$initdata = trim(str_ireplace("\n", " ", $initdata));
					$allrows = explode("||", $initdata);
					$header = null;
					foreach($allrows as &$row) {
						$row = trim($row);
						if($header == null)
							$header = str_getcsv($row, ',', '"', '\\');
						else {
							$row = str_getcsv($row, ',', '"', '\\');
							if(count($row) == count($header)) {
								$index = 0;
								$as = array();
								foreach($header as $key) {
									$row[$index] = str_replace("\\r", "\r", $row[$index]);
									$row[$index] = str_replace("\\n", "\n", $row[$index]);
									$row[$index] = trim($row[$index], " ");
									if($row[$index] == "NULL")
										$row[$index] = NULL;
									$as[$key] = $row[$index];
									$index++;
								}
								$this->insertIntoTable($tablename, $as);
							}
							else {
								$logger = new \CodePunch\Base\CPLogger();
								$logger->error("Error CSV: " . implode(";", $row) . "; " . count($header) . " - ". count($row));
							}
						}
					}
				}
			}
			else {
				// Check if any columns are missing
				// The $keys are not passed because we will be adding the missing indexes
				// separately.
				$this->insertColumnsAndKeys($tablename, $columninfo, array());
			}
			
			// Check if any Indexes are missing
			if ($schemaManager->tablesExist(array($tablename))) {
				foreach($keys as $key=>$value)
					$this->createSQLIndex($tablename, $key, $value);
			}
			else {
				throw new Exception(sprintf(TEXT::get("db_table_creation_error_S"), $tablename)); 
			}
		}
	}
	
	###########################################################################
	
	public function updateInitDataFromFile($tabledatafolder, $tablestoupdate, $pivotcolumn="")
	{
		$updated = 0;
		if($tabledatafolder[strlen($tabledatafolder)-1] != DIRECTORY_SEPARATOR)
			$tabledatafolder .= DIRECTORY_SEPARATOR;
		$files = UTIL::find_all_matched_files($tabledatafolder, "*.xml");
		foreach($files as $file) {
			$tabledatafile = $tabledatafolder . $file;
			$table_data = array();
			$dbdata = file_get_contents($tabledatafile);
			$xml = simplexml_load_string($dbdata);
			if(isset($xml->table))
			{
				$t = json_decode(json_encode($xml), true);
				$schema = new \Doctrine\DBAL\Schema\Schema();
				if(isset($t['table'])) {
					$tables = $t['table'];
					if(isset($tables[0])) {
						foreach($tables as $tab) {
							if(isset($tab['name']) && isset($tab['init'])) {
								if(UTIL::in_array_casei($tab['name'], $tablestoupdate))
									$updated += $this->updateInitData($this->tableprefix . $tab['name'], $tab['init'], $pivotcolumn);
							}
						}
					}
					else if(isset($tables['name']) && isset($tables['init'])) {
						if(UTIL::in_array_casei($tables['name'], $tablestoupdate))
							$updated += $this->updateInitData($this->tableprefix . $tables['name'], $tables['init'], $pivotcolumn);
					}
				}
			}
		}
		return $updated;
	}
	
	###########################################################################
	# If a $pivotcolumn is specified and it is part of the initdata, the rows 
	# will be deleted before insertion
	# 
	
	public function updateInitData($tablename, $initdata, $pivotcolumn="")
	{
		$rowcount = 0;
		// $initdata contains CSVs with each row separated by 
		// a || and the first row is the header
		$initdata = str_ireplace("SQL:", "", $initdata);
		$initdata = str_ireplace("\r\n", " ", $initdata);
		$initdata = trim(str_ireplace("\n", " ", $initdata));
		$allrows = explode("||", $initdata);
		$header = null;
		foreach($allrows as &$row) {
			$row = trim($row);
			if($row == "")
				continue;
			if($header == null)
				$header = str_getcsv($row, ',', '"', '\\');
			else {
				$row = str_getcsv($row, ',', '"', '\\');
				if(count($row) == count($header)) {
					$index = 0;
					$as = array();
					foreach($header as $key) {
						$row[$index] = str_replace("\\r", "\r", $row[$index]);
						$row[$index] = str_replace("\\n", "\n", $row[$index]);
						$row[$index] = trim($row[$index], " ");
						if($row[$index] == "NULL")
							$row[$index] = NULL;
						$as[$key] = $row[$index];
						$index++;
					}
					try {
						if($pivotcolumn != "" && isset($as[$pivotcolumn])) {
							$this->deleteFromTable($tablename, "$pivotcolumn=?", array($as[$pivotcolumn]));
						}
						if($this->connection->insert($tablename, $as))
							$rowcount++;
					}
					catch(Exception $e) {
						#Ignore the constraint violation errors.
						//$logger = new \CodePunch\Base\CPLogger();
						//$logger->error($e->getMessage());
					}
				}
				else {
					$logger = new \CodePunch\Base\CPLogger();
					$logger->error("Error CSV: " . implode(";", $row) . "\n" . count($header) . " - ". count($row));
				}
			}
		}
		return $rowcount;
	}
	
	###########################################################################
	
	public function insertIntoTable($tablename, $rows) {
		try {
			//if($this->connection->insert($tablename, $rows) > 0) 
			//	return true;
			$queryBuilder = $this->connection->createQueryBuilder();
			$queryBuilder->insert($tablename);
			$index = 0;
			foreach($rows as $key=>$value) {
				$queryBuilder->setValue($key, '?');
				$queryBuilder->setParameter($index, $value);
				$index++;
			}
			return $queryBuilder->execute();
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function insertColumnsAndKeys($tablename, $columninfo, $keys)
	{
		$cnames = array_map("mb_strtoupper", $this->getAllColumnNames($tablename));
		$newcolumns = array();
		$platform = $this->connection->getDatabasePlatform();
		$platformname = strtolower($platform->getName());
		$missing = 0;
		
		// ::WARNING:: PLATFORM DEPENDENT CODE //
		// SQLite needs separate queries for each add column.
		// 
		$sqls = [];
		$sql = "ALTER TABLE " . $tablename;
		if($platformname == "mssql" || $platformname == "oracle")
			$sql .= " ADD";
		else if($platformname == "sqlite") {
			$sql = '';
		}
		foreach($columninfo as $newcolumn) {
			$newcolumn = $this->initColumnSettings($newcolumn); // Added March/8/2022
			$cname = $newcolumn['name'];
			if(!in_array(mb_strtoupper($cname), $cnames) && isset($newcolumn['type'])) {
				$missing++;
				$newcolumns[] = $cname;
				if($platformname == "mssql" || $platformname == "oracle")
					$sql .= " $cname ";
				else if($platformname == "sqlite")
					$sql .= " ALTER TABLE $tablename ADD $cname ";
				else
					$sql .= " ADD $cname ";
				
				if($newcolumn['type'] == "string") {
					$coltype = $platform->getVarcharTypeDeclarationSQL($newcolumn['options']);
					$sql .= $coltype;
				}
				else if($newcolumn['type'] == "integer") {
					$coltype = $platform->getIntegerTypeDeclarationSQL($newcolumn['options']);
					$sql .= $coltype;
				}
				else if($newcolumn['type'] == "boolean") {
					$coltype = $platform->getBooleanTypeDeclarationSQL($newcolumn['options']);
					$sql .= $coltype;
				}
				else if($newcolumn['type'] == "datetime") {
					$coltype = $platform->getDateTimeTypeDeclarationSQL($newcolumn['options']);
					$sql .= $coltype;
				}
				else if($newcolumn['type'] == "date") {
					$coltype = $platform->getDateTypeDeclarationSQL($newcolumn['options']);
					$sql .= $coltype;
				}
				else if($newcolumn['type'] == "text") {
					$coltype = $platform->getClobTypeDeclarationSQL($newcolumn['options']);
					$sql .= $coltype;
				}
				else if($newcolumn['type'] == "float") {
					$coltype = $platform->getFloatDeclarationSQL($newcolumn['options']);
					$sql .= $coltype;
				}
				
				// ::WARNING:: PLATFORM DEPENDENT CODE //
				$sql = rtrim($sql);
				if(isset($newcolumn['options']['default']) && strtolower($newcolumn['options']['default']) == "null")
					$newcolumn['options']['default'] = NULL;
				if(isset($newcolumn['options']['notnull']) && $newcolumn['options']['notnull'] == false)
					$sql .= " DEFAULT NULL";
				else if(isset($newcolumn['options']['default'])) {
					$newcolumn['options']['type'] = $newcolumn['type']; {
						if($platformname == "mssql") {
							if($newcolumn['type'] == "string") 
								$sql .= " NOT NULL CONSTRAINT {$cname}_default DEFAULT N" . $platform->quoteStringLiteral($newcolumn['options']['default']);
							else
								$sql .= " NOT NULL CONSTRAINT {$cname}_default " . $platform->getDefaultValueDeclarationSQL($newcolumn['options']);
						}
						else
							$sql .= " " . $platform->getDefaultValueDeclarationSQL($newcolumn['options']);
					}
				}
				if($platformname == "sqlite") {
					$sqls[] = $sql;
					$sql = '';
				}
				else
					$sql .= ",";
			}
		}
		if($platformname != "sqlite") {
			$sqls[] = rtrim($sql, ", ");
		}

		if($missing) {
			try {
				$logger = new \CodePunch\Base\CPLogger();
				$missing = implode(",", $newcolumns);
				foreach($sqls as $sql) {
					$statement = $this->connection->prepare($sql);
					$statement->execute();
				}
				$logger->info("Table $tablename modified, columns ($missing) added.");
				foreach($keys as $k=>$v) {
					if(UTIL::in_array_casei($v, $newcolumns)) {
						$this->createSQLIndex($tablename, $k, $v);
					}
				}
			}
			catch(Exception $e) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error($e->getMessage());
				return false;
			}
		}
		return true;
	}
	
	###########################################################################
	
	public function deleteIndex($tablename, $idxname)
	{
		$dbPlatform = $this->connection->getDatabasePlatform();
		$sql = $dbPlatform->getDropIndexSQL($idxname, $tablename);
		try {
			$statement = $this->connection->prepare($sql);
			$statement->execute();
			return true;
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}

	###########################################################################
	# separate column names by a | for creating a multi column index.
	# If the $column is an array, a different index will be created for each.

	public function createSQLIndex($tablename, $type, $column, &$idxname="")
	{
		if(is_array($column)) {
			$count = 0;
			foreach($column as $c) {
				if($this->createSQLIndex($tablename, $type, $c) === true)
					$count++;
			}
			return $count; // Return number of indexes created.
		}
		
		$logger = new \CodePunch\Base\CPLogger();
		if($idxname == "") {
			// If multi-column index the column names will be separated by |
			$cidx = strtolower(str_replace("|", "_", $column));
			$idxname = "INDEX";
			# Remove possible table-prefix from tablename.
			$otname = strpos($tablename, "_") !== false ? strtolower(substr($tablename, strpos($tablename, "_")+1)) : strtolower($tablename);
			$idxprefix = "{$otname}_{$cidx}";
			// Oracle has a 30 character limit (SQL-92 standard ?)
			if(strlen($idxprefix) > 24)
				$idxprefix = strtolower(UTIL::abbreviate($idxprefix, 4));
			if(strtolower($type) == "index")
				$idxname = "{$idxprefix}_idx";
			else if(strtolower($type) == "unique")
				$idxname = "{$idxprefix}_uidx";
		}
		$schemaManager = $this->connection->getSchemaManager();
		$indexes = $schemaManager->listTableIndexes($tablename);
		foreach($indexes as $idx) {
			if(strtolower($type) == "primary" && $idx->isPrimary()) {
				//$logger->debug("$tablename already has a primary key");
				return false;
			}
			$idxcolumns = implode("|", $idx->getColumns());
			if(!strcasecmp($idxcolumns, $column)) {
				if(strtolower($type) == "unique" && $idx->isUnique()) {
					//$logger->debug("$tablename already has a unique index for $column");
					return false;
				}
				else if(strtolower($type) == "index" && $idx->isSimpleIndex()) {
					//$logger->debug("$tablename already has a simple index for $column");
					return false;
				}
			}
			if(!strcasecmp($idxname, $idx->getName())) {
				//$logger->debug("$tablename already has a key named $idxname");
				return false;
			}
		}

		$scolumns = str_replace("|", ",", $column);
		$sql = "CREATE index $idxname ON $tablename ($scolumns)";
		if(strtolower($type) == "primary")
			$sql = "CREATE primary index ON $tablename ($scolumns)";
		else if(strtolower($type) != "index")
			$sql = "CREATE $type index $idxname ON $tablename ($scolumns)";
		try {
			$statement = $this->connection->prepare($sql);
			$statement->execute();
			return true;
		}
		catch(Exception $e) {
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function dropTable($tablename) 
	{
		if($this->connection != null) {
			try {
				$sql = "DROP table $tablename";
				$statement = $this->connection->prepare($sql);
				$statement->execute();
				return true;
			}
			catch(Exception $e) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error($e->getMessage());
			}
		}
		return false;
	}
	
	###########################################################################
	
	public function truncateTable($tablename) 
	{
		if($this->connection != null) {
			try {
				$dbPlatform = $this->connection->getDatabasePlatform();
				$q = $dbPlatform->getTruncateTableSql($tablename);
				$this->connection->executeUpdate($q);
				return true;
			}
			catch(Exception $e) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error($e->getMessage());
			}
		}
		return false;
	}
	
	###########################################################################
	// Checks if the table has a matching row, supports multi-column matches,
	// and (NE) !=. The $column and $value pair can be arrays. Use [!=] in
	// front of the $value for NE checks.
	
	public function hasRow($table, $column, $value) {
		try {
			$queryBuilder = $this->connection->createQueryBuilder();
			$queryBuilder->select($column)->from($table);
			if(is_array($column)) {
				$idx = 0;
				foreach($column as $c) {
					$ops = "=";
					if(isset($value[$idx])) {
						if(substr($value[$idx], 0, 4) == "[!=]") {
							$ops = "!=";
							$value[$idx] = substr($value[$idx], 4);
						}
						if(!$idx) 
							$queryBuilder->where("$c{$ops}?");
						else
							$queryBuilder->andWhere("$c{$ops}?");
						$queryBuilder->setParameter($idx, $value[$idx]);
					}
					else 
						break;
					$idx++;
				}
				$firstcolumn = $column[0];
			}
			else if(!is_array($column) && !is_array($value)){
				$ops = "=";
				if(substr($value, 0, 4) == "[!=]") {
					$ops = "!=";
					$value = substr($value, 4);
				}
				$queryBuilder->where("$column{$ops}?");
				$queryBuilder->setParameter(0, $value);
				$firstcolumn = $column;
			}
			$data = $queryBuilder->execute()->fetchAll();
			if(count($data))
				$data[0] = array_change_key_case($data[0], CASE_LOWER);
			if(isset($data[0][$firstcolumn]))
				return true;
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function insertOrUpdateTable($table, $idata, $pivotcolumn, $value)
	{
		if($this->hasRow($table, $pivotcolumn, $value)) 
			return $this->updateTable($table, $idata, "$pivotcolumn=?", array($value));
		else {
			if(!isset($idata[$pivotcolumn]))
				$idata[$pivotcolumn] = $value;
			return  $this->insertIntoTable($table, $idata) ? 1 : 0;
		}
	}
	
	###########################################################################
	
	public function findOneOf($from, $column, $value, $seek) {
		try {
			$queryBuilder = $this->connection->createQueryBuilder();
			$queryBuilder->select($seek,$column)->from($from);
			$queryBuilder->where("$column=?");
			$queryBuilder->setParameter(0, $value);
			$data = $queryBuilder->execute()->fetchAll();
			if($data) {
				$data = UTIL::array_flatten($data);
				$data = array_change_key_case($data, CASE_LOWER);
				if(isset($data[$seek])) 
					return $data[$seek];
			}
		}
		catch(Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	# Fetch a specified row after a SELECT. Optionally convert all keys to
	# lower case and replace nulls with empty string.
	
	public function fetchRow($rows, $index, $lowercasekeys=true, $cleanNulls=false) 
	{
		if($rows !== false && isset($rows[$index])) {
			$row = $rows[$index];
			if($lowercasekeys)
				$row = array_change_key_case($row, CASE_LOWER);
			if($cleanNulls) {
				foreach($row as $k=>&$v) {
					if($v == null)
						$v = "";
				}
			}
			return $row;
		}
		return false;
	}
	
	###########################################################################
	
	public function customExpandWhere($where)
	{
		$timenow = time();
		$pos = strpos($where, "[TODAY");
		while($pos !== false) {
			$tleft = substr($where, 0, $pos);
			$tright = substr($where, $pos+6);
			$pend = strpos($tright, "]");
			if($pend !== false) {
				$days = substr($tright, 0, $pend);
				$tright = substr($tright, $pend+1);
				$platform = $this->connection->getDatabasePlatform();
				if(UTIL::starts_with($days, "+")) {
					$days = intval(substr($days,1));
					$dc = "'" . date("Y-m-d", $timenow+($days*24*3600)) . "'";
					//$dc = $platform->getDateAddDaysExpression('CURRENT_TIMESTAMP', intval($days));
				}
				else if(UTIL::starts_with($days, "-")) {
					$days = intval(substr($days,1));
					$dc = "'" . date("Y-m-d", $timenow-($days*24*3600)) . "'";
					//$dc = $platform->getDateSubDaysExpression('CURRENT_TIMESTAMP', intval($days));
				}
				else {
					$dc = "'" . date("Y-m-d", $timenow) . "'";
					//$dc = $platform->getCurrentDateSQL();
				}
				$where = $tleft . $dc . $tright;
				$pos = strpos($where, "[TODAY");
			}
			else
				break;
		}
		$where = str_ireplace(" LT ", " < ", $where);
		$where = str_ireplace(" GT ", " > ", $where);
		$where = str_ireplace(" EQ ", " = ", $where);
		$where = str_ireplace(" LE ", " <= ", $where);
		$where = str_ireplace(" GE ", " >= ", $where);
		$where = str_ireplace(" NE ", " != ", $where);
		$where = str_ireplace(" BW ", " LIKE ", $where);
		$where = str_ireplace(" EW ", " LIKE ", $where);
		$where = str_ireplace(" CN ", " LIKE ", $where);
		
		return $where;
	}
	
	###########################################################################
	
	public function getRowCount($tablename, $where="", $params=array())
	{
		if($this->connection != null) {
			try {
				$queryBuilder = $this->connection->createQueryBuilder();
				$queryBuilder->select("COUNT(*) AS count")->from($tablename);
				if($where != "") {
					$where = $this->customExpandWhere($where);
					$queryBuilder->where($where);
				}
				$index = 0;
				foreach($params as $param) {
					if(!is_array($param))
						$queryBuilder->setParameter($index++, $param);
					else if(count($param) == 2)
						$queryBuilder->setParameter($index++, $param[0], $param[1]);
				}
				$rows = $queryBuilder->execute()->fetchAll();
				if(isset($rows[0])) {
					$row = array_change_key_case($rows[0], CASE_LOWER);
					return $row['count'];
				}
			}
			catch(Exception $e) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error($e->getMessage());
			}
		}
		return false;
	}
	
	###########################################################################
	
	public function getFromTable($select, $from, $where="", $params=array(), $orderby="", $sortorder="asc", $limit=null)
	{
		if($this->connection != null) {
			try {
				$queryBuilder = $this->connection->createQueryBuilder();
				$queryBuilder->select($select)->from($from);
				if($where != "")
					$queryBuilder->where($where);
				$index = 0;
				if(!is_array($params))
					$queryBuilder->setParameter($index, $params);
				else {
					foreach($params as $param) {
						if(!is_array($param)) {
							$queryBuilder->setParameter($index, $params[$index]);
							$index++;
						}
						else if(count($param) == 2) {
							$queryBuilder->setParameter($index, $params[$index][0], $params[$index][1]);
							$index++;
						}
					}
				}
				if($orderby != "")
					$queryBuilder->orderby($orderby, $sortorder);
				if($limit != null) {
					if(is_array($limit)) {
						if(count($limit) == 2) {
							$queryBuilder->setFirstResult($limit[0]);
							$queryBuilder->setMaxResults($limit[1]);
						}
						else if(count($limit) == 1) {
							$queryBuilder->setFirstResult(0);
							$queryBuilder->setMaxResults($limit[1]);
						}
					}
					else {
						$queryBuilder->setFirstResult(0);
						$queryBuilder->setMaxResults($limit);
					}
				}
				$data = $queryBuilder->execute()->fetchAll();
				return $data;
			}
			catch(Exception $e) {
				$logger = new \CodePunch\Base\CPLogger();
				$logger->error($e->getMessage());
			}
		}
		return false;
	}
	
	###########################################################################
	
	public function updateTable($table, $set, $where, $data) {
		try {
			// If $data is a string we assume that there is only 
			if(!is_array($data))
				$data = array($data);
			$queryBuilder = $this->connection->createQueryBuilder();
			$queryBuilder->update($table);
			$values = array();
			foreach($set as $k=>$v) {
				$queryBuilder->set($k, '?');
				$values[] = $v;
			}
			$queryBuilder->where($where);
			$index = 0;
			foreach($values as $v)
				$queryBuilder->setParameter($index++, $v);
			foreach($data as $v)
				$queryBuilder->setParameter($index++, $v);
			return $queryBuilder->execute();
		}
		catch (Exception $e) 
		{
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###############################################################################
	
	public function editTable($table, $data, $idname)
	{
		$id = UTIL::get_from_array($data['id'], false);
		if($id !== false) {
			unset($data['id']);
			if($this->hasRow($table, $idname, $id)) {
				return $this->updateTable($table, $data, "$idname=?", array($id));
			}
		}
		return false;
	}
	
	###############################################################################
	// Delete all specified rows from multiple tables
	// Use for deleting all entries from linked tables.
	// $ids is the comma separated input id list.
	// $tabledata is an array containing tablename + idname + unique column name 
	// in that table.
	// $entries is an array of deleted unique column names from the first table 
	// Used only to report the entries that were actually deleted.
	// If the id is in the $protected_ids list, it will not be deleted. 
	
	public function deleteRowsByIDFromTables($ids, $tabledata, $protected_ids)
	{
		$delcounts = array();
		$entries = array();
		$first = true;
		foreach($tabledata as $td) {
			$table = UTIL::get_from_array($td['table'], "");
			$stid = UTIL::get_from_array($td['id'], "");
			$uname = UTIL::get_from_array($td['uname'], "");
			if($stid != "" && $table != "") {
				$status = $this->deleteRowsByID($table, $ids, $stid, $protected_ids, $first ? $uname : "");
				$delcounts[$table] = $status['delcounts']; 
				if($first) 
					$entries = $status['entries'];
			}
			$first = false;
		}
		return array('delcounts'=>$delcounts, 'entries'=>$entries);
	}
	
	###########################################################################
	// If the id is in the $protected_ids list, it will not be deleted.
	
	public function deleteRowsByID($table, $ids, $idname, $protected_ids, $uname="")
	{
		$entries = array();
		$theids = explode(",", $ids);
		$count = 0;
		foreach($theids as $theid) {
			if(in_array($theid, $protected_ids))
				continue;
			if($uname != "")
				$name = $this->findOneOf($table, $idname, $theid, $uname);
			$dcount = $this->deleteFromTable($table, "$idname = ?", array($theid));
			$count += $dcount;
			if($dcount && $uname != "")
				$entries[] = $name;
		}
		return array('delcounts'=>$count, 'entries'=>$entries);
	}
	
	###########################################################################
	
	public function deleteFromTable($table, $where, $params)
	{
		try {
			$queryBuilder = $this->connection->createQueryBuilder();
			$queryBuilder->delete($table);
			$queryBuilder->where($where);
			$index = 0;
			foreach($params as $v) {
				if(!is_array($v))
					$queryBuilder->setParameter($index++, $v);
				else if(count($v) == 2)
					$queryBuilder->setParameter($index++, $v[0], $v[1]);	
			}
			return $queryBuilder->execute();
		}
		catch (Exception $e) 
		{
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function getAllTableNames() 
	{
		try {
			$tnames = array();
			$sm = $this->connection->getSchemaManager();
			return $sm->listTableNames();
			/*
			$tables = $sm->listTables();
			foreach ($tables as $table) {
				$tnames[] = $table->getName();
			}
			return $tnames;
			*/
		}
		catch(SchemaException $e) 
		{
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function getColumnType($tablename, $columnname) {
		$ci = $this->getColumnInfo($tablename, $columnname);
		if(isset($ci['type']))
			return strtolower($ci['type']);
		return "";
	}
	
	###########################################################################
	
	public function getColumnInfo($tablename, $columnname) {
		try {
			$cdata = array();
			$sm = $this->connection->getSchemaManager();
			$table = $sm->listTableDetails($tablename);
			$column = $table->getColumn($columnname);
			$coldata = array();
			$coldata['type'] = $column->getType()->getName();
			$coldata['length'] = $column->getLength();
			return $coldata;
		}
		catch(SchemaException $e) 
		{
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function getAllColumns($tablename) {
		try {
			$cdata = array();
			$sm = $this->connection->getSchemaManager();
			$table = $sm->listTableDetails($tablename);
			$ci = $table->getColumns();
			foreach($ci as $column) {
				$coldata = array();
				$coldata['name'] = $column->getName();
				$coldata['type'] = $column->getType()->getName();
				$coldata['length'] = $column->getLength();
				$cdata[] = $coldata;
			}
			return $cdata;
		}
		catch(SchemaException $e) 
		{
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function getAllColumnNames($tablename) {
		$cnames = array();
		$cdata = $this->getAllColumns($tablename);
		if($cdata !== false) {
			foreach($cdata as $c) 
				$cnames[] = $c['name'];
			return array_map('strtolower', $cnames);
		}
		return false;
	}
	
	###########################################################################
	
	public function getAllTables() {
		try {
			$tabledata = array();
			$sm = $this->connection->getSchemaManager();
			$tables = $sm->listTables();
			foreach ($tables as $table) {
				$ti = array();
				$ti['name'] = $table->getName();
				$columns = array();
				$idx = array();
				foreach ($table->getColumns() as $column) {
					$columns['name'] = $column->getName();
					$columns['type'] = $column->getType();
					$columns['length'] = $column->getLength();
				}
				$indexes = $sm->listTableIndexes($ti['name']);
				foreach ($indexes as $index) {
					$idx[] = $index->getName() . ': ' . ($index->isUnique() ? 'unique' : 'not unique');
				}
				$ti['indexes'] = $idx;
				$ti['columns'] = $columns;
				$tabledata[] = $ti;
			}
			return $tabledata;
		}
		catch(SchemaException $e) 
		{
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function getValidTables() {
		try {
			$tabledata = array();
			$sm = $this->connection->getSchemaManager();
			$tables = $sm->listTables();
			foreach ($tables as $table) {
				$ti = array();
				$ti['name'] = $table->getName();
				if(UTIL::starts_with(strtolower($ti['name']), strtolower($this->tableprefix))) {
					$columns = array();
					foreach ($table->getColumns() as $column) {
						$columns['name'] = $column->getName();
						$columns['type'] = $column->getType();
						$columns['length'] = $column->getLength();
					}
					$ti['columns'] = $columns;
					$tabledata[] = $ti;
				}
			}
			return $tabledata;
		}
		catch(SchemaException $e) 
		{
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function getNowExpression() {
		$platform = $this->connection->getDatabasePlatform();
		return $platform->getNowExpression();
	}
	
	###########################################################################
	
	public function getCurrentTime()
	{
		try {
			$queryBuilder = $this->connection->createQueryBuilder();
			$dbPlatform = $this->connection->getDatabasePlatform();
			$platformName = $dbPlatform->getName();
			$q = $dbPlatform->getCurrentTimestampSQL();
			$select = "$q as CT";
			if(strtolower($platformName) == "oracle")
				$select .= " From Dual";
			$queryBuilder->select($select);
			$rows = $queryBuilder->execute()->fetchAll();
			if(isset($rows[0])) {
				$row = array_change_key_case($rows[0], CASE_LOWER);
				return $row['ct'];
			}
			else
				return false;
		}
		catch (Exception $e) {
			$logger = new \CodePunch\Base\CPLogger();
			$logger->error($e->getMessage());
		}
	}
}
	
###############################################################################
