<?php
///////////////////////////////////////////////////////////////////////////////////////////////////
#  Author:   Anil Kumar
#  Version:	 1.0
#  Date:     25-July-2013
#  Purpose:  WMDSED - DB Handler
#  Requires: Requires PHP5, PDO
#  Usage Example:
#                     	require_once("wmdsed-api.php");
#                     	$pdo = new wmDomainDatabase();
#						...
///////////////////////////////////////////////////////////////////////////////////////////////////

class wmDomainDatabase
{
	public $db_connect_handle = null;
	public $last_error = null;
	public $db_table_prefix = null;
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function __construct($table_prefix="")
	{ 
		$this->db_table_prefix = trim(strtolower($table_prefix));
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getDomainTableName()
	{
		return $this->db_table_prefix . 'domains';
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getCategoryTableName()
	{
		return $this->db_table_prefix . 'catlist';
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getCategoryIDToDomainIDTableName()
	{
		return $this->db_table_prefix . 'catconn';
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getCustomFieldsTableName()
	{
		return $this->db_table_prefix . 'customfields';
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	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());
		}
		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 setError($msg)
	{
		$this->last_error[] = $msg;
	}
	
	public function clearErrors()
	{
		unset($this->last_error);
		$this->last_error= null;
	}
	
	public function getErrors()
	{
		return $this->last_error;
	}
	public function getLastError()
	{
		return $this->getError();
	}
	
	public function getError($index = -1)
	{
		if($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))
		{
			if(is_array($this->last_error))
			{
				foreach($this->last_error as $error)
					echo $error . "<br>";
			}
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public static function MySQLDateOffset($dt,$year_offset='',$month_offset='',$day_offset='') 
	{ 
		 return ($dt=='0000-00-00') ? '' : 
			date ("Y-m-d", mktime(0,0,0,substr($dt,5,2)+$month_offset,substr($dt,8,2)+ 
			$day_offset,substr($dt,0,4)+$year_offset)); 
	} 
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getCategoryIDFromName($category)
	{
		$categorytablename = $this->getCategoryTableName();
		try
		{
			$sql = "select CategoryID from " . $categorytablename . " where CategoryName = '$category'";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
				return $rows[0]['CategoryID'];
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getDomainID($domain)
	{
		$tablename = $this->getDomainTableName();
		try
		{
			$sql = "select sid from " . $tablename . " where domain = '$domain'";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
				return $rows[0]['sid'];
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getAllColumnNames($tablename="")
	{
		if($tablename == "")
			$tablename = $this->getDomainTableName();
			
		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 addTextColumn($name, $charlength, $default='NULL')
	{
		$columns[] = $name;
		$columns[] = "varchar(" . $charlength . ")";
		$columns[] = "DEFAULT " . $default;
		return $this->addCustomColumn($columns);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function addLongTextColumn($name)
	{
		$columns[] = $name;
		$columns[] = "longtext";
		$columns[] = "DEFAULT NULL";
		return $this->addCustomColumn($columns);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function addDateColumn($name)
	{
		$columns[] = $name;
		$columns[] = "DATE";
		$columns[] = "DEFAULT NULL";
		return $this->addCustomColumn($columns);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function addDateTimeColumn($name)
	{
		$columns[] = $name;
		$columns[] = "DATETIME";
		$columns[] = "DEFAULT NULL";
		return $this->addCustomColumn($columns);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function addTimestampColumn($name)
	{
		$columns[] = $name;
		$columns[] = "TIMESTAMP";
		$columns[] = "DEFAULT '0000-00-00 00:00:00'";
		return $this->addCustomColumn($columns);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function addBigIntColumn($name, $ilen)
	{
		$columns[] = $name;
		$columns[] = "bigint(" . $ilen . ")";
		$columns[] = "DEFAULT NULL";
		return $this->addCustomColumn($columns);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function addFloatColumn($name, $precis)
	{
		$columns[] = $name;
		$columns[] = "float(" . $precis . ")";
		$columns[] = "DEFAULT NULL";
		return $this->addCustomColumn($columns);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function AddColumnToMainTable($columns)
	{
		$tablename = $this->getDomainTableName();
			
		$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("Column(s) (" . implode(",", $columns) . ") already present in table.");
		}
		return false;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function addCustomColumn($columns)
	{
		$customTable = $this->getCustomFieldsTableName();
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT id FROM " . $customTable . " WHERE name = '$columns[0]'");
			$stmt->execute(array());
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
			{
				$stmt = $this->db_connect_handle->prepare("INSERT INTO " . $customTable . " (name,ftype,fdefault) VALUES(:name,:ftype,:fdefault)");
				$stmt->execute(array(':name' => $columns[0], ':ftype' => $columns[1], ':fdefault'=>$columns[2]));
				$tid = $this->db_connect_handle->lastInsertId('id');
				return $this->AddColumnToMainTable($columns);
			}
			$this->setError("Column '$columns[0]' already present.");
			return false;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getDomains($likeString="%")
	{
		$sql = "Domain LIKE '" . $likeString . "'";
		return $this->getDomainsFromWhereSQL($sql);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getDomainsFromWhereSQL($whereSQL)
	{
		$tablename = $this->getDomainTableName();
		$sql = "SELECT domain FROM " . $tablename . " WHERE " . $whereSQL;
		return $this->getDomainsFromSQL($sql);
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getDomainsFromSQL($sql)
	{
		try
		{
			$columns = array();
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			
			foreach($rows as $record)
				$columns[] = $record['domain'];
			return $columns;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getDomainData($domain, $columns)
	{
		$tablename = $this->getDomainTableName();
		$excolumns = $this->getAllColumnNames($tablename);
		$micolumns = array();
		
		try
		{
			$commaList = "";
			if(is_array($columns))
			{
				foreach($columns as $column)
				{
					if(in_array($column, $excolumns))
					{
						if($commaList != "")
							$commaList .= ",";
						$commaList .= $column;
					}
					else
						$micolumns[] = $column;
				}
			}
			else
			{
				if(in_array($columns, $excolumns))
					$commaList = $columns;
				else
				{
					$cdata[$columns] = "Missing Column!";
					return $cdata;
				}
			}
				
			$sql = "SELECT " . $commaList . " FROM " . $tablename . " WHERE Domain = '" . $domain . "'";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
			{
				if(is_array($columns))
				{
					foreach($micolumns as $mic)
						$rows[0][$mic] = "!";
					return $rows[0];
				}
				else
					return $rows[0][$columns];
			}
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function setDomainData($domain, $column, $value)
	{
		$tablename = $this->getDomainTableName();
		try
		{
			$sql = "UPDATE " . $tablename . " SET " . $column . "=? WHERE domain=?";
			$q = $this->db_connect_handle->prepare($sql);
			$q->execute(array($value,$domain));
			return true;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function setDomainDataArray($domain, $columnArray, $valueArray)
	{
		$tablename = $this->getDomainTableName();
		try
		{
			$columns = "";
			foreach($columnArray as $column)
				$columns .= "$column = ?,";
			$columns = trim($columns, ", ");
			
			$valueArray[] = $domain;
			
			$sql = "UPDATE " . $tablename . " SET " . $columns . " WHERE domain=?";
			$q = $this->db_connect_handle->prepare($sql);
			$q->execute($valueArray);
			return true;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getDomainsToLookup($lookuptimefield, $maxdomains, $maxdays)
	{
		$tablename = $this->getDomainTableName();
		try
		{
			$sql = "SELECT DISTINCT sid,domain,$lookuptimefield from $tablename WHERE $lookuptimefield IS NULL OR $lookuptimefield = '0000-00-00' LIMIT 0, $maxdomains";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			$found = count($rows);
			if($found)
				return $rows;
			else
			{
				$comparedate = $this->MySQLDateOffset(date("Y-m-d"),0,0,-$maxdays);
				$sql = "SELECT DISTINCT sid,domain,$lookuptimefield from $tablename WHERE $lookuptimefield <= '$comparedate' LIMIT 0, $maxdomains";
				$stmt = $this->db_connect_handle->prepare($sql);
				$stmt->execute();
				$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
				return $rows;
			}
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
}

///////////////////////////////////////////////////////////////////////////////////////////////////

?>

