<?php
###############################################################################
# cppdo.php
# Watch My Domains SED v3
# (c) Softnik Technologies. All rights reserved.
###############################################################################

class cpPDO
{
	public $db_connect_handle = null;
	public $last_error = null;
	public $db_type = "mysql";
	
	###########################################################################
	
	public function openMySQL($dbhost, $dbname, $dbuser, $dbpassword)
	{
		$this->clearErrors();
		$this->db_connect_handle = null;
		try 
		{
			$this->db_connect_handle = new PDO('mysql:host=' . $dbhost . ';dbname=' . $dbname, $dbuser, $dbpassword, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			$this->db_connect_handle = null;
			return false;
		}
		if($this->db_connect_handle)
		{
			$this->db_connect_handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->db_connect_handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
		}
		return $this->db_connect_handle;
	}
	
	###########################################################################
	
	public function setDBType($dbtype)
	{
		$this->db_type = strtolower($dbtype);
	}
	
	###########################################################################
	
	public function setError($msg)
	{
		$this->last_error[] = $msg;
	}
	
	###########################################################################
	
	public function clearErrors()
	{
		unset($this->last_error);
	}
	
	###########################################################################
	
	public function getErrors()
	{
		if(isset($this->last_error))
			return $this->last_error;
		else
			return null;
	}
	
	###########################################################################
	
	public function getLastError()
	{
		return $this->getError();
	}
	
	###########################################################################
	
	public function getError($index = -1)
	{
		if(isset($this->last_error))
		{
			$count = count($this->last_error);
			if($index < 0)
				$index = $count-1;
			if($index >= 0 && $index < $count)
				return $this->last_error[$index];
		}
		return "";
	}
	
	###########################################################################
	
	public function showLog()
	{
		if(isset($this->last_error))
		{
			$count = count($this->last_error);
			if($count)
			{
				foreach($this->last_error as $error)
					echo $error . "<br>";
			}
		}
	}
	
	###########################################################################
	
	public function getLog()
	{
		$elog = "";
		if(isset($this->last_error))
		{
			$count = count($this->last_error);
			if($count)
			{
				foreach($this->last_error as $error)
					$elog .= $error . "\n";
			}
		}
		return $elog;
	}
	
	###########################################################################
	
	public function getAllColumnNames($tablename)
	{
		try
		{
			$columns = array();
			$rs = $this->db_connect_handle->query('SELECT * FROM ' . $tablename . ' LIMIT 0');
			for ($i = 0; $i < $rs->columnCount(); $i++) 
			{
				$col = $rs->getColumnMeta($i);
				$columns[] = $col['name'];
			}
			return $columns;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function addMissingColumns($tablename, $columns)
	{
		$excolumns = $this->getAllColumnNames($tablename);
		if($excolumns !== false)
		{
			$count = count($columns)/3;
			$missing = 0;
			$sql = "ALTER TABLE `" . $tablename . "` ADD (";
			for($i = 0; $i < $count; $i++)
			{
				if(!in_array($columns[$i*3], $excolumns))
				{
					$sql .= "`" . $columns[$i*3] . "` ";
					$sql .= $columns[$i*3+1] . " ";
					$sql .= $columns[$i*3+2] . ",";
					$missing++;
				}
			}
			if($missing)
			{
				$sql = rtrim($sql, ", ");
				$sql .= ")";
				try 
				{
					$this->db_connect_handle->exec($sql);
					return true;
				} 
				catch (PDOException $e) 
				{
					$this->setError($e->getMessage());
					return false;
				}
			}
			else
				$this->setError("No columns are missing");
		}
		return false;
	}
	
	###########################################################################
	
	public function createTable($tablename, $columns, $keys, $autoinc=0, $engine="MyISAM")
	{
		if($columns == null)
		{
			$this->setError("Attempting to create table '" . $tablename . "' without any columns.");
			return false;
		}
		try
		{
			$this->db_connect_handle->query('select 1 from ' . $tablename);
			$this->setError("Table " . $tablename . " Exists");
			return false;
		} 
		catch (PDOException $e) 
		{
			$count = count($columns)/3;
			$sql = "CREATE TABLE " . $tablename . "(";
			for($i = 0; $i < $count; $i++)
			{
				$sql .= $columns[$i*3] . " ";
				$sql .= $columns[$i*3+1] . " ";
				$sql .= $columns[$i*3+2] . ",";
			}
			$sql .= $keys;
			$sql = rtrim($sql, ", ");
			$sql .= ") ENGINE=$engine  DEFAULT CHARSET=utf8 ";
			if($autoinc > 0)
				$sql .= "AUTO_INCREMENT=" . $autoinc;
			$sql = rtrim($sql);			
			try 
			{
				$this->db_connect_handle->exec($sql);
				return true;
			} 
			catch (PDOException $e) 
			{
				$this->setError($e->getMessage());
				return false;
			}
		}
	}
	
	###########################################################################
	
	public function renameTable($oldtablename, $newtablename)
	{
		try
		{
			$this->db_connect_handle->query('select 1 from ' . $oldtablename);
			try
			{
				$this->db_connect_handle->query('select 1 from ' . $newtablename);
				$this->setError("Table " . $newtablename . " Exists. Unable to rename.");
				return false;
			}
			catch (PDOException $e) 
			{
				$sql = "RENAME TABLE `" . $oldtablename . "` TO `" . $newtablename . "`";
				try 
				{
					$this->db_connect_handle->exec($sql);
					return true;
				} 
				catch (PDOException $e) 
				{
					$this->setError($e->getMessage());
					return false;
				}
			}
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	# will return false on database error. check $dbError value too.
	
	public function isEntryPresent($tablename, $fieldname, $value, &$dbError)
	{
		$dbError = false;
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $tablename . " WHERE " . $fieldname . "=?");
			$stmt->execute(array($value));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
				return false;
			else
				return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function getSingleEntry($tablename, $fieldname, $value, $entryField="id")
	{
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT " . $entryField . " FROM " . $tablename . " WHERE " . $fieldname . "=?");
			$stmt->execute(array($value));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
				return false;
			else if(isset($rows[0][$entryField]))
			{
				return $rows[0][$entryField];
			}
			return false;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	# to be used in the derived class (required)
	
	public function getColumns($tablename)
	{
		return null;
	}
    
    ###########################################################################
	
	public function getDefaultColumnNames($tablename)
	{
        $names = array();
		$columns = $this->getColumns($tablename);
        $count = count($columns)/3;
        for($i = 0; $i < $count; $i++)
            $names[] = $columns[$i*3];
        return $names;
	}
	
	###########################################################################
	# to be used in the derived class (optional)
	
	public function getDefaultKeys($tablename)
	{
		return "PRIMARY KEY (id)";
	}
	
	###########################################################################
	# to be used in the derived class (optional)
	
	public function getDefaultAutoIncrement($tablename)
	{
		return 1;
	}
	
	###########################################################################
	
	public function initTable($tablename, $engine="MyISAM")
	{
		return $this->createTable($tablename, $this->getColumns($tablename), $this->getDefaultKeys($tablename), $this->getDefaultAutoIncrement($tablename), $engine);
	}
	
	###########################################################################
	
	public function repairTable($tablename)
	{
		return $this->addMissingColumns($tablename, $this->getColumns($tablename));
	}
	
	###########################################################################
	
	public function getFieldType($table, $field) 
	{
		try 
		{
			$sql = "SHOW COLUMNS FROM `$table` WHERE Field = ?";
			$q = $this->db_connect_handle->prepare($sql);
			$q->execute(array($field));
			$rows = $q->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($rows) && isset($rows[0]))
				return $rows[0]['Type'];
		} 
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
		return "";
	}
	
	###########################################################################
	# Cleanup the fielddata so that it matches the fieldtype.
	
	public function cleanupFieldData($domaintable, $centry, $key)
	{
		$centry = trim($centry,chr(0xC2).chr(0xA0)); 
		$ft = $this->getFieldType($domaintable, $key);
		if($ft !== false)
		{
			if(stristr($ft, "datetime"))
			{
				if(strlen($centry) < 12)
					$centry = null;
				else
				{
					$dateval = strtotime($centry);
					$centry = date("Y-m-d H:i:s", $dateval);
				}
			}
			else if(stristr($ft, "date"))
			{
				if(strlen($centry) < 6)
					$centry = null;
				else
				{
					$dateval = strtotime($centry);
					$centry = date("Y-m-d", $dateval);
				}
			}
			else if(stristr($ft, "varchar"))
			{
				if(preg_match('!!u', $centry) === false)
					$centry = utf8_encode($centry);
				if(strlen($centry) > 255)
					$centry = substr($centry, 0, 254);
			}
			else if(stristr($ft, "bigint") !== false)
			{
				if($centry == '')
					$centry = null;
			}
			else if(stristr($ft, "tinyint") !== false)
			{
				if($centry == '')
					$centry = null;
			}
		}
		return $centry;
	}			
	
	###########################################################################
	
	public function getAllIDs($tablename, $idcol = "id")
	{
		return $this->getTableData($tablename, $idcol);
	}
	
	###########################################################################
	# The returned array structure will be different if the number of columns  
	# are more than 1 (FETCH_ASSOC vs FETCH_COLUMN)
	
	public function getTableData($tablename, $columns, $clause="", $dataarray=null)
	{
		$data = array();
		if($dataarray != null)
			$data = $dataarray;
		$ca = explode(",", $columns);
		$fmode = PDO::FETCH_COLUMN;
		if($columns == "*" || count($ca) > 1)
			$fmode = PDO::FETCH_ASSOC;
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT $columns FROM $tablename " . $clause);
			$stmt->execute($data);
			$rows = $stmt->fetchAll($fmode);
			if(is_array($rows))
				return $rows;
			return false;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function getCount($tablename, $where="", $dataarray=null)
	{
		$data = array();
		if($dataarray != null)
			$data = $dataarray;
		try
		{
			$sql = "SELECT COUNT(*) AS count FROM $tablename";
			if($where != "")			
				$sql .= " WHERE $where";  
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute($data);
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
				return $rows[0]['count'];
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function deleteFromTable($tablename, $where, $dataarray=null)
	{
		$data = array();
		if($dataarray !== null)
			$data = $dataarray;
		try
		{
			$sql = "delete from $tablename";
			if($where != "")			
				$sql .= " WHERE $where";  
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute($data);
			return true;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function insertIntoTable($tablename, $data, $insertidname="")
	{
		$valid_columns = $this->getAllColumnNames($tablename);
		if($valid_columns !== false)
		{
			$columnArray = array();
			$valueArray = array();
			foreach($data as $key=>$value)
			{
				if(in_array($key, $valid_columns) !== false)
				{
					$columnArray[] = $key;
					$valueArray[":$key"] = $value;
				}
			}
			try
			{
				$columns = "";
				$valuevars = "";
				foreach($columnArray as $column)
				{
					$columns .= "$column,";
					$valuevars  .= ":$column,";
				}
				$columns = trim($columns, ", ");
				$valuevars = trim($valuevars, ", ");
				$sql = "INSERT INTO $tablename  ($columns) VALUES ($valuevars)";
				$q = $this->db_connect_handle->prepare($sql);
				$q->execute($valueArray);
				if($insertidname != "")
				{
					$iid = $this->db_connect_handle->lastInsertId($insertidname);
					return $iid;
				}
				else
					return true;
			}
			catch (PDOException $e) 
			{
				$this->setError($e->getMessage());
				debugEcho($e->getMessage());
			}
		}
		return false;
	}
	
	###########################################################################
	
	public function updateTable($tablename, $setinfo, $thedata)
	{
		try
		{
			$sql = "UPDATE " . $tablename . " SET " . $setinfo;
			$q = $this->db_connect_handle->prepare($sql);
			$q->execute($thedata);
			return true;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			debugEcho($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function getCurrentTime()
	{
		try
		{
			$stmt = $this->db_connect_handle->prepare("select NOW() as CT");
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
				return false;
			else if(isset($rows[0]['CT']))
			{
				return $rows[0]['CT'];
			}
			return false;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	function testIfTablesExist($table_array)
	{
		$notfound = array();
		foreach($table_array as $table)
		{
			$sql = "SELECT 1 FROM $table LIMIT 1";
			try
			{
				$stmt = $this->db_connect_handle->prepare($sql);
				$stmt->execute();
			}
			catch (PDOException $e) 
			{
				$notfound[] = $table;
			}
		}
		return $notfound;
	}
}

###############################################################################
?>
