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

require_once("cppdo.php");
require_once("gridpdo.php");
require_once("tables/domain.php");

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

define("LOOKUP_DOMAIN_WHOIS", 		"1");
define("LOOKUP_IP_ADDRESS",			"2");
define("LOOKUP_HTTP_HOMEPAGE",		"4");
define("LOOKUP_US_TM",				"8");
define("LOOKUP_ALEXA_DATA",			"16");
define("LOOKUP_GOOGLE_PR",			"32");
define("LOOKUP_GOOGLE_LINKS",		"64");
define("LOOKUP_MX_RECORDS",			"128");
define("LOOKUP_PING_RESPONSE",		"256");
define("LOOKUP_BING_LINKS",			"512");
define("LOOKUP_YAHOO_LINKS",		"1024");
define("LOOKUP_SSL_CERTS",			"2048");
define("LOOKUP_IP_WHOIS",			"4096");
define("LOOKUP_SEC_DOMAIN_WHOIS",	"8192");
define("LOOKUP_SUB_DOMAINS",		"16384");
define("LOOKUP_GOOGLE_INDEX",		"32768");
define("LOOKUP_BING_INDEX",			"65536");
define("LOOKUP_YAHOO_INDEX",		"131072");

define("LOOKUP_MOZ_DATA",			"262144");
define("LOOKUP_ESTIBOT_DATA",		"524288");
define("LOOKUP_SIMILARWEB_DATA",	"1048576");

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

define("AUDIT_ADD_DOMAINS",			1);
define("AUDIT_DELETE_DOMAINS",		2);
define("AUDIT_LOGGED_IN",			3);
define("AUDIT_LOGGED_OUT",			4);
define("AUDIT_SESSION_TIMEOUT",		5);
define("AUDIT_ADDED_USER",			6);
define("AUDIT_DELETED_USER",		7);
define("AUDIT_ADDED_CATEGORY",		8);
define("AUDIT_DELETED_CATEGORY",	9);
define("AUDIT_EDITED_DOMAIN",		10);
define("AUDIT_ADDED_AUTOQUERY",		11);
define("AUDIT_DELETED_AUTOQUERY",	12);
define("AUDIT_EDITED_AUTOQUERY",	13);
define("AUDIT_CSV_DOWNLOAD",		14);
define("AUDIT_SSL_CSV_DOWNLOAD",	15);
define("AUDIT_DNS_CSV_DOWNLOAD",	16);
define("AUDIT_DNS_ROOT_RECORD_CHANGE",	17);
define("AUDIT_EMAIL_ATTEMPT_FAILED",200);

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

define("LUQSTAT_WAITING",			0);
define("LUQSTAT_IN_PROGRESS",		1);
define("LUQSTAT_COMPLETED",			2);
define("LUQSTAT_FAILED",			3);

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

define("MAX_AUDIT_LOG_DOMAINS",		50);

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

class wmDomainDatabase extends gridPDO
{	
	public $domain_table 			= null;
	public $subdomain_table   		= null;
	public $category_table 			= null;
	public $autoqueries_table		= null;
	public $catconn_table  			= null;
	public $custom_fields_table 	= null;
	public $settings_table 			= null;
	public $displaycolumns_table	= null;
	public $lookup_queue_table      = null;
	public $tlds_table      		= null;
	public $whoisserver_table      	= null;
	public $last_lookups_table		= null;
	public $installed_servers_table	= null;
	public $registrar_aliases_table	= null;
	public $data_history_table		= null;
	public $audit_log_table			= null;
	public $second_level_tld_table	= null;
	public $registrar_api_table		= null;
	
	public $ms_global_settings		= null;
	
	static $glb_domain_database		= false;
	static $last_init_error			= false;
	
	###########################################################################
	
	public function __construct($db_type="mysql", $table_prefix="")
	{ 
		$this->setDBType($db_type);
		
		$this->domain_table 			= trim(strtolower($table_prefix . "domains"));
		$this->subdomain_table 			= trim(strtolower($table_prefix . "subdomains"));
		$this->category_table 			= trim(strtolower($table_prefix . "catlist"));
		$this->catconn_table 			= trim(strtolower($table_prefix . "catconn"));
		$this->custom_fields_table 		= trim(strtolower($table_prefix . "customfields"));
		$this->settings_table 			= trim(strtolower($table_prefix . "settings"));
		$this->displaycolumns_table	 	= trim(strtolower($table_prefix . "tablecolumns"));
		$this->autoqueries_table		= trim(strtolower($table_prefix . "queries"));
		$this->lookup_queue_table		= trim(strtolower($table_prefix . "luq"));
		$this->tlds_table				= trim(strtolower($table_prefix . "tlds"));
		$this->whoisserver_table		= trim(strtolower($table_prefix . "whoisservers"));
		$this->last_lookups_table		= trim(strtolower($table_prefix . "last_lookups"));
		$this->installed_servers_table	= trim(strtolower($table_prefix . "installed_servers"));
		$this->registrar_aliases_table	= trim(strtolower($table_prefix . "registrar_aliases"));
		$this->data_history_table		= trim(strtolower($table_prefix . "data_history"));
		$this->audit_log_table			= trim(strtolower($table_prefix . "audit_log"));
		$this->second_level_tld_table	= trim(strtolower($table_prefix . "second_level_tlds"));
		$this->registrar_api_table		= trim(strtolower($table_prefix . "registrar_api"));
	}
	
	###########################################################################
	
	public function getTableList()
	{
		$tablelist = array();
		$tablelist[] = $this->domain_table;
		$tablelist[] = $this->subdomain_table;
		$tablelist[] = $this->category_table;
		$tablelist[] = $this->catconn_table;
		$tablelist[] = $this->custom_fields_table;
		$tablelist[] = $this->settings_table;
		$tablelist[] = $this->displaycolumns_table;
		$tablelist[] = $this->autoqueries_table;
		$tablelist[] = $this->lookup_queue_table;
		$tablelist[] = $this->tlds_table;
		$tablelist[] = $this->whoisserver_table;
		$tablelist[] = $this->last_lookups_table;
		$tablelist[] = $this->registrar_aliases_table;
		$tablelist[] = $this->data_history_table;
		$tablelist[] = $this->installed_servers_table;
		$tablelist[] = $this->audit_log_table;
		$tablelist[] = $this->second_level_tld_table;
		$tablelist[] = $this->registrar_api_table;
		
		return $tablelist;
	}
	
	###########################################################################
	
	public function getColumns($tablename)
	{ 		
		if($tablename == $this->domain_table)
			return get_domain_domain_table_columns(); 
		else if($tablename == $this->settings_table)
			return get_domain_settings_columns();
		else if($tablename == $this->displaycolumns_table)
			return get_domain_display_columns();
		else if($tablename == $this->category_table)
			return get_domain_categorytable_columns();
		else if($tablename == $this->catconn_table)
			return get_domain_catconntable_columns();
		else if($tablename == $this->autoqueries_table)
			return get_domain_autoquerytable_columns();
		else if($tablename == $this->lookup_queue_table)
			return get_domain_luq_columns();
		else if($tablename == $this->tlds_table)
			return get_domain_tlds_colums();
		else if($tablename == $this->whoisserver_table)
			return get_domain_whoisserver_columns();
		else if($tablename == $this->last_lookups_table)
			return get_domain_last_lookups_table_columns();
		else if($tablename == $this->subdomain_table)
			return get_domain_subdomain_table_columns();
		else if($tablename == $this->custom_fields_table)
			return get_domain_custom_fields_table_columns();
		else if($tablename == $this->registrar_aliases_table)
			return get_domain_registrar_alias_table_columns();
		else if($tablename == $this->data_history_table)
			return get_domain_data_history_table_columns();
		else if($tablename == $this->installed_servers_table)
			return get_domain_installed_servers_table_columns();
		else if($tablename == $this->audit_log_table)
			return get_domain_auditlog_columns();
		else if($tablename == $this->second_level_tld_table)
			return get_second_level_tld_columns();
		else if($tablename == $this->registrar_api_table)
			return get_registrar_api_columns();
	}
	
	###########################################################################
	
	public function getDefaultKeys($tablename)
	{
		if(strtolower($tablename) == $this->domain_table)
			return "PRIMARY KEY (sid),UNIQUE KEY domain (domain),INDEX(registry_expiry),INDEX(registrar_expiry),INDEX(status),INDEX(availability),INDEX(edited),INDEX(lookedup)";
		else if(strtolower($tablename) == $this->settings_table)
			return "PRIMARY KEY (id),UNIQUE KEY name (name),INDEX(value)";
		else if(strtolower($tablename) == $this->displaycolumns_table)
			return "PRIMARY KEY (id),UNIQUE KEY name (name)";
		else if(strtolower($tablename) == $this->last_lookups_table)
			return "PRIMARY KEY (id)";
		else if(strtolower($tablename) == $this->subdomain_table)
			return "PRIMARY KEY (hid),INDEX(subdomain),INDEX(ssl_valid_to),INDEX(edited),INDEX(sid)";
		else if(strtolower($tablename) == $this->catconn_table)
			return "PRIMARY KEY (id),INDEX(CategoryID),INDEX(sid)";
		else if(strtolower($tablename) == $this->category_table)
			return "PRIMARY KEY (CategoryID)";
		else if(strtolower($tablename) == $this->custom_fields_table)
			return "PRIMARY KEY (id)";
		else if(strtolower($tablename) == $this->autoqueries_table)
			return "PRIMARY KEY (id)";
		else if(strtolower($tablename) == $this->lookup_queue_table)
			return "PRIMARY KEY (id),INDEX(sid),INDEX(created_on),INDEX(type)";
		else if(strtolower($tablename) == $this->whoisserver_table)
			return "PRIMARY KEY (server)";
		else if(strtolower($tablename) == $this->data_history_table)
			return "PRIMARY KEY (id)";
		else if(strtolower($tablename) == $this->installed_servers_table)
			return "PRIMARY KEY (id)";
		else if(strtolower($tablename) == $this->audit_log_table)
			return "PRIMARY KEY (id), INDEX(action)";
		else if(strtolower($tablename) == $this->tlds_table)
			return "PRIMARY KEY (tld), INDEX(server)";
		else if(strtolower($tablename) == $this->second_level_tld_table)
			return "PRIMARY KEY (id), INDEX(tld)";
		else if(strtolower($tablename) == $this->registrar_api_table)
			return "PRIMARY KEY (id), INDEX(registrar)";
	}
	
	###########################################################################
	
	public function getDomainTableName()
	{
		return $this->domain_table;
	}
	
	###########################################################################
	
	public function getCategoryTableName()
	{
		return $this->category_table;
	}
	
	###########################################################################
	
	public function getAutoQueryTableName()
	{
		return $this->autoqueries_table;
	}
	
	###########################################################################
	
	public function getCategoryIDToDomainIDTableName()
	{
		return $this->catconn_table;
	}
	
	###########################################################################
	
	public function getCustomFieldsTableName()
	{
		return $this->custom_fields_table;
	}
	
	###########################################################################
	
	public function getLookupQueueTableName()
	{
		return $this->lookup_queue_table;
	}
	
	###########################################################################
	
	public function getTldsTableName()
	{
		return $this->tlds_table;
	}
	
	###########################################################################
	
	public function getWhoisserverTableName()
	{
		return $this->whoisserver_table;
	}
	
	###########################################################################
	
	public function getSubdomainTableName()
	{
		return $this->subdomain_table;
	}
	
	###########################################################################
	
	public function getRegistrarAliasTableName()
	{
		return $this->registrar_aliases_table;
	}
	
	###########################################################################
	
	public function getInstalledServersTableName()
	{
		return $this->installed_servers_table;
	}
	
	###########################################################################
	
	public function getLastLookupTimeTableName()
	{
		return $this->last_lookups_table;
	}
	
	###########################################################################
	
	public function getDataHistoryTableName()
	{
		return $this->data_history_table;
	}
	
	###########################################################################
	
	public function getAuditLogTableName()
	{
		return $this->audit_log_table;
	}
	
	###########################################################################
	
	public function getSecondLevelTLDTableName()
	{
		return $this->second_level_tld_table;
	}
	
	###########################################################################
	
	public function getRegistrarAPITableName()
	{
		return $this->registrar_api_table;
	}
	
	###########################################################################
	
	public function initTables($engine="MyISAM")
	{
		$tablelist = $this->getTableList();
		foreach($tablelist as $table)
		{
			if($this->initTable($table, $engine) !== false)
				$this->initTableData($table);
			$this->repairTable($table);
			if($table == $this->getCategoryIDToDomainIDTableName())
				$this->makeDummyCategoryConnectionEntry();
		}
	}
	
	###########################################################################
	
	public function makeDummyCategoryConnectionEntry()
	{
		# Add a dummy entry so that custom / auto queries always work.
		# If this table is empty, some SQL queries will not work because
		# the engine will see an 'impossible condition'. So we add a
		# harmless entry. This is not required even if one domain is
		# categorized."
		$count = $this->getCount($this->getCategoryIDToDomainIDTableName());
		if($count !== false && $count == 0)
		{
			$sql = "(CategoryID, sid) VALUES (1,9999999999)";
			$this->InsertData($this->getCategoryIDToDomainIDTableName(), $sql);
		}
	}
	
	###########################################################################
	
	public function initTableData($table)
	{
		if($table == $this->getCategoryTableName())
		{
			$this->InsertData($table, "(`CategoryID`, `CategoryName`) VALUES ('1', 'All Domains')");
			return $this->InsertData($table, getCategoryDefaultSQL());
		}
		else if($table == $this->getAutoQueryTableName())
			return $this->InsertData($table, getAutoqueryDefaultSQL());
		else if($table == $this->getWhoisserverTableName())
			return $this->InsertData($table, getWhoisServerTableDefaultSQL());
		else if($table == $this->getTldsTableName())
			return $this->InsertData($table, getTldTableDefaultSQL());
		return true;
	}
	
	###########################################################################
	
	public function InsertData($table, $sql)
	{
		try
		{
			$sql = "INSERT INTO $table $sql";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute();
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
		return true;
	}
	
	###########################################################################
	
	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 = ?";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute(array(trim($category)));
			$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'];
			else
				return 0;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function addDomainToCategory($sid, $cid)
	{
		$cid = intval($cid);
		$sid = intval($sid);
		$domtable = $this->getDomainTableName();
		$catconnTable = $this->getCategoryIDToDomainIDTableName();
		try
		{
			$sql = "select count(id) as count from " . $catconnTable . " where sid = $sid AND CategoryID = $cid";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
			{
				if($rows[0]['count'] == 0)
				{
					$sql = "insert into $catconnTable  (sid, CategoryID) values (:sid, :cid)";
					$stmt = $this->db_connect_handle->prepare($sql);
					$stmt->execute(array('sid'=>$sid, 'cid'=>$cid));
					return $this->db_connect_handle->lastInsertId('id');
				}
			}
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
		return 0;
	}
	
	###########################################################################
	
	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');
			}
			$status = $this->addColumnToMainTable($columns);
			return true;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function getCustomFieldInfo()
	{	
		$fields = array();
		$types = array();
		$widths = array();
		
		$cfdata = $this->getTableData($this->getCustomFieldsTableName(), "*");
		if($cfdata != false)
		{
			foreach($cfdata as $crow) 
			{
				$fieldname = strtolower(trim($crow['name']));
				$fieldname = str_replace(" ","_", $fieldname);
				$fields[] = $fieldname;
				$ftype = trim($crow['ftype']);
				$length = "";
				if(stristr($ftype, "varchar") !== false)
				{
					$types[] = "varchar";
					$length = substr($ftype, 8, strlen($ftype)-9);
				}
				else if(stristr($ftype, "bigint") !== false)
				{
					$types[] = "bigint";
					$length = substr($ftype, 7, strlen($ftype)-8);
				}
				else if(stristr($ftype, "text") !== false)
					$types[] = "text";
				else if(stristr($ftype, "datetime") !== false)
					$types[] = "datetime";
				else if(stristr($ftype, "date") !== false)
					$types[] = "date";
				else
					$types[] = "varchar";
				$widths[] = intval($length);
			}
		}
		return array('fields'=>$fields, 'types'=> $types, 'widths'=>$widths);
	}
	
	###########################################################################
	
	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;
		}
	}
	
	###########################################################################
	# $columns - comma separated list of columns
	# $query - Eg. "registry_expiry <= [TODAY+$expirydays] ORDER BY registry_expiry asc"
	# $required - array of columns that should be always present (will be added if not).
	
	public function getDomainDataFromQuery($columns, $query, $required=array())
	{
		$colarray = explode(",", strtolower($columns));
		foreach($required as $entry)
		{
			if(!in_array($entry, $colarray))
				array_unshift($colarray, $entry);
		}

		$validcolumns = $this->getAllColumnNames($this->getDomainTableName());
		$select = "";
		$fields = array();
		$labels = array();
		foreach($colarray as $c)
		{
			if(in_array($c, $validcolumns))
			{
				$select .= " $c,";
				$fields[] = $c;
				if($this->getColumnLabelAndWidthForDomain($c, $label, $width) !== false)
					$labels[] = $label;
				else
					$labels[] = getLabelFromFieldName($c);
			}
		}
		$select = trim($select, ",");

		$where = "WHERE " . $query;
		$where = str_replace("[TODAY]", $this->wrapQuotes(date("Y-m-d")), $where);
		$where = $this->parseCustomQuery($where);

		$tdata = $this->getTableData($this->getDomainTableName(), $select, $where);
		if($tdata !== false)
			return array('data'=>$tdata, 'columns'=>$labels, 'fields'=>$fields);
		else
			return false;
	}
	
	###########################################################################
	
	public function insertDaysToExpireIntoArray($tdata)
	{
		if($tdata !== false)
		{
			foreach($tdata as &$row)
			{
				$expdays = 99999;
				$domainage = 99999;
				foreach($row as $key=>$value)
				{
					if(($key == 'registry_expiry' || $key == 'registrar_expiry' || $key == 'ssl_valid_to') && isADate($value))
					{
						$days = get_date_difference(strtotime($value));
						$row[$key . "_days"] = $days;	
						$expdays = min($expdays, $days);
					}
					else if(($key == 'created_on') && isADate($value))
					{
						$days = get_date_difference(strtotime($value)) * -1;
						$row[$key . "_days"] = $days;	
						$domainage = min($domainage, $days);
					}
				}
				if($expdays != 99999)
					$row['expiry_days'] = $expdays;
				if($domainage != 99999)
					$row['domain_age'] = $domainage;
			}
		}
		
		return $tdata;
	}
	
	###########################################################################
	# $columns - comma separated list of columns
	# $query - Eg. "ssl_valid_to <= [TODAY+$expirydays] ORDER BY ssl_valid_to asc"
	# $required - array of columns that should be always present (will be added if not).
	
	public function getSubdomainDataFromQuery($columns, $query, $required=array())
	{
		$colarray = explode(",", strtolower($columns));
		foreach($required as $entry)
		{
			if(!in_array($entry, $colarray))
				array_unshift($colarray, $entry);
		}

		$validcolumns = $this->getAllColumnNames($this->getSubdomainTableName());
		$select = "";
		$fields = array();
		$labels = array();
		foreach($colarray as $c)
		{
			if(in_array($c, $validcolumns))
			{
				$select .= " $c,";
				$fields[] = $c;
				if($this->getColumnLabelAndWidthForSubdomain($c, $label, $width) !== false)
					$labels[] = $label;
				else
					$labels[] = getLabelFromFieldName($c);
			}
		}
		$select = trim($select, ",");

		$where = "WHERE " . $query;
		$where = str_replace("[TODAY]", $this->wrapQuotes(date("Y-m-d")), $where);
		$where = $this->parseCustomQuery($where);

		$tdata = $this->getTableData($this->getSubdomainTableName(), $select, $where);
		if($tdata !== false)
			return array('data'=>$tdata, 'columns'=>$labels, 'fields'=>$fields);
		else
			return false;
	}
	
	#########################################################################################################
	# Create a table containing the domain information.
	# $rowclassfunc is a call back function for wrapping the row class. You can use this to color code a
	# row based on expiry date etc.

	static function convertToTable($domaininfo, $sortcolumn, $tableclass="", $rowclassfunc=null, $sortorder=SORT_ASC)
	{
		$tdata = $domaininfo['data'];
		$labels = $domaininfo['columns'];
		$fields = $domaininfo['fields'];
		
		$tclass = "";
		if($tableclass != "")
			$tclass = " class=\"$tableclass\"";
		$tablehtml = "\n<table" . "$tclass>\n";
		
		$tablehtml .= "<tr>";
		foreach($labels as $label)
			$tablehtml .= "<th>$label</th>";
		$tablehtml .= "</tr>\n";
		
		$tdata = array_sort($tdata, $sortcolumn, $sortorder);
		foreach($tdata as $row)
		{
			$rowhtml = "";
			foreach($fields as $column)
			{
				$entry = "";
				if(isset($row[$column]))
					$entry = $row[$column];
				$rowhtml .= "<td class=\"$column\">$entry</td>";
			}
			if($rowclassfunc != null)
				$rowhtml = $rowclassfunc($rowhtml, $row);
			else
				$rowhtml = "<tr>$rowhtml</tr>\n";
			$tablehtml .= $rowhtml;
		}
		$tablehtml .= "</table>";
		return $tablehtml;
	}
	
	###########################################################################
	
	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 setDomainDataFromArray($domain, $domainData)
	{
		$tablename = $this->getDomainTableName();
        $valid_columns = $this->getAllColumnNames($tablename);
		
		// Convert to unicode name if in punycode.
		if(substr($domain, 0, 4) == "xn--")
			$domain = idn_reconvert($domain);
        
        $columnArray = array();
        $valueArray = array();
        foreach($domainData as $key=>$value)
        {
            if(in_array($key, $valid_columns) !== false)
            {
                $columnArray[] = $key;
                $valueArray[] = $value;
            }
        }
		try
		{
			$columns = "";
			if(count($columnArray))
			{
				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 resetDomainData($domain, $columns)
	{
		$tablename = $this->getDomainTableName();
        $sql = 'update ' . $tablename . ' set ';
        foreach($columns as $cname)
            $sql .= "$cname = NULL,";
        $sql = trim($sql, ",");
        $sql .= " where domain = '" . $domain . "'";
        
        try
		{
            $q = $this->db_connect_handle->prepare($sql);
            $q->execute();
            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;
		}
	}
	
	###########################################################################
	
	public function getPreferenceLocalName($key)
	{
		return $this->getPreferenceLocalPrefix($key) . "_" . $key;
	}
	
	###########################################################################
	
	public function getPreferenceLocalPrefix($key)
	{
		if($this->ms_global_settings == null)
			$this->ms_global_settings = $this->getPreference("multi_server_global_settings");

		global $install_server_id;
		if(isset($install_server_id) && $key != "multi_server_global_settings" && $this->ms_global_settings == false)
		{
			$install_id = strtolower(trim($install_server_id));
			if($install_id != "")
			{
				if($install_id == "auto")
					$install_id = strtolower(php_uname('n'));
			}
			if($install_id != "")
				return $install_id;
		}
		return "master";
	}
	
	###########################################################################

	static function getInstallServerID()
	{
		global $install_server_id;
		if(isset($install_server_id))
		{
			$install_id = strtolower(trim($install_server_id));
			if($install_id != "")
			{
				if($install_id == "auto")
					$install_id = strtolower(php_uname('n'));
			}
			if($install_id != "")
				return $install_id;
		}
		return "master";
	}
	
	###########################################################################
	# The keys will not match the actual preference names (the keys will have
	# the server id as a prefix, master_ui_theme instead of ui_theme, etc.)
	
	public function getAllPreferences()
	{
		$prefdata = array();
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $this->settings_table);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($rows))
			{
				foreach($rows as $row)
					$prefdata[$row['name']] = $row['value'];
			}
			return $prefdata;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function getPreference($name)
	{
		if($name == "multi_server_global_settings")
			$field = "master_" . $name;
		else
			$field = $this->getPreferenceLocalName($name);
		return $this->_getPreference($field);
	}
	
	###########################################################################
	
	public function _getPreference($field)
	{
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $this->settings_table . " WHERE name=?");
			$stmt->execute(array($field));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
			{
				return $rows[0]['value'];
			}
			return false;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function setPreference($name, $value)
	{
		if($name == "multi_server_global_settings")
			$field = "master_" . $name;
		else
			$field = $this->getPreferenceLocalName($name);
		return $this->_setPreference($field, $value);
	}
	
	###########################################################################
	
	public function _setPreference($field, $value)
	{
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $this->settings_table . " WHERE name=?");
			$stmt->execute(array($field));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
			{
				$stmt = $this->db_connect_handle->prepare("INSERT INTO " . $this->settings_table . " (name,value) VALUES(:name,:value)");
				$stmt->execute(
				array(
				':name' => $field, ':value' => $value
				));
				return true;
			}
			else
			{
				$stmt = $this->db_connect_handle->prepare("UPDATE " . $this->settings_table . " SET value=? WHERE name=?");
				$stmt->execute(array($value,$field));
				return true;
			}
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function setPreferenceNumber($name, $value)
	{
		return $this->setPreference($name, strval($value));
	}
	
	###########################################################################
	
	public function getPreferenceNumber($name)
	{
		$value = $this->getPreference($name);
		if($value !== false)
		{
			if(ctype_digit($value))
				return intval($value);
		}
		return false;
	}
	
	###########################################################################
	
	function getDefaultWidthForDomainColumn($field)
	{
		$width = 100;
		if($field == "domain") 
			$width = 170;
		else if($field == "edited") 
			$width = 40;
		return $width;
	}
	
	###########################################################################
	
	function getDefaultWidthForSubdomainColumn($field)
	{
		$width = 100;
		if($field == "domain") 
			$width = 170;
		else if($field == "edited" || $field == "ttl" ) 
			$width = 40;
		else if($field == "record_type" || $field == "auto_added") 
			$width = 60;
		return $width;
	}
	
	###########################################################################

	function setColumnLabelAndWidthForDomain($field, $label, $width)
	{
		$status = $this->_setPreference($this->getInstallServerID() . "-fw-".$field, $width);
		if($status == true)
			$status = $this->_setPreference($this->getInstallServerID() . "-fl-".$field, $label);
		return $status;
	}

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

	# Get Column Label and Width from DB

	function getColumnLabelAndWidthForDomain($field, &$label, &$width)
	{
		$wd = $this->_getPreference($this->getInstallServerID() . "-fw-".$field);
		if($wd !== false)
			$width = $wd;
		$lb = $this->_getPreference($this->getInstallServerID() . "-fl-".$field);
		if($lb !== false)
			$label = $lb;
		if($lb !== false && $wd !== false)
			return true;
		return false;
	}
	
	###########################################################################

	function setColumnLabelAndWidthForSubdomain($field, $label, $width)
	{
		$status = $this->_setPreference($this->getInstallServerID() . "-fw-s-".$field, $width);
		if($status == true)
			$status = $this->_setPreference($this->getInstallServerID() . "-fl-s-".$field, $label);
		return $status;
	}

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

	# Get Column Label and Width from DB

	function getColumnLabelAndWidthForSubdomain($field, &$label, &$width)
	{
		$wd = $this->_getPreference($this->getInstallServerID() . "-fw-s-".$field);
		if($wd !== false)
			$width = $wd;
		$lb = $this->_getPreference($this->getInstallServerID() . "-fl-s-".$field);
		if($lb !== false)
			$label = $lb;
		if($lb !== false && $wd !== false)
			return true;
		return false;
	}
	
	###############################################################################

	static function getFieldNameFromLabel($label)
	{
		$field = strtolower(trim($label));
		$field = str_replace(" ","_", $field);
		return $field;
	}
	
	###########################################################################
	
	public function getDisplayColumns()
	{
		$prefdata = array();
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $this->displaycolumns_table);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($rows))
			{
				foreach($rows as $row)
					$prefdata[$row['name']] = $row['entry'];
			}
			return $prefdata;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function getDisplayColumnsForGroup($dgroup)
	{
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $this->displaycolumns_table . " WHERE name=?");
			$stmt->execute(array($dgroup));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($rows))
				return $rows[0]['entry'];
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
		return false;
	}
	
	###########################################################################
	
	public function setDisplayColumns($group, $columns)
	{
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $this->displaycolumns_table . " WHERE name=?");
			$stmt->execute(array($group));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
			{
				$stmt = $this->db_connect_handle->prepare("INSERT INTO " . $this->displaycolumns_table . " (name,entry) VALUES(:name,:entry)");
				$stmt->execute(
				array(
				':name' => $group, ':entry' => $columns
				));
				return true;
			}
			else
			{
				$stmt = $this->db_connect_handle->prepare("UPDATE " . $this->displaycolumns_table . " SET entry=? WHERE name=?");
				$stmt->execute(array($columns,$group));
				return true;
			}
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function getCustomColumns()
	{
		$columnnames = array();
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $this->custom_fields_table);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($rows))
			{
				foreach($rows as $row)
					$columnnames[] = $row['name'];
			}
			return $columnnames;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	
	###########################################################################

	static function wrapQuotes($str)
	{
		return "'" . $str . "'";
	}
	
	###########################################################################
	
	public function getAllQueryIDs()
	{
		return $this->getAllIDs($this->getAutoQueryTableName());
	}
	
	###########################################################################
	
	public function getAllCategoryIDs()
	{
		return $this->getAllIDs($this->getCategoryTableName(), "CategoryID");
	}
	
	###########################################################################
	# Cleanup the query.
	# TODO: This requires more work.
	# Remove any ORDER BY
	# Set the FROM clause
	# Use three of the tables in FROM except when there is a LEFT, 
	# INNER or OUTER JOIN

	public function getAutoQuery($aqid, $usecattable=false)
	{
		$query = $this->getSingleEntry($this->getAutoQueryTableName(), "id", $aqid, "query");
		if($query !== false)
		{
			$opos = mb_stripos($query, " order by ");
			if($opos !== false)
				$query = substr($query, 0, $opos);
			if($usecattable)
			{
				$posw = mb_stripos($query, " where ");
				$posl = mb_stripos($query, " left ");
				if($posw !== false && $posl !== false)
					$pos = $posw < $posl ? $posw : $posl;
				else
					$pos = $posw !== false ? $posw : $posl;
				
				if($posl !== false)
					$qfrom = "from [domains] a";
				else
					$qfrom = "from [domains] a, [catconn] b, [catlist] c ";
				$append = ($pos !== false) ? substr($query, $pos) : "";
				$query = $qfrom . $append;
			}
			
			# wrap the conditional clause in ()
			if(stripos($query, " where ") !== false)
			{
				$query = str_ireplace(" where ", " where (", $query);
				$query .= ")";
			}
			
			return $this->expandQuery($query, $usecattable);
		}
		return "";
	}
	
	###########################################################################
	
	public function expandQuery($query)
	{
		$query = str_replace("[domaintable]", $this->getDomainTableName() . " a", $query);
		$query = str_replace("[domains]", $this->getDomainTableName(), $query);
		$query = str_replace("[catconn]", $this->getCategoryIDToDomainIDTableName(), $query);
		$query = str_replace("[catlist]", $this->getCategoryTableName(), $query);
		$query = str_replace("[TODAY]", $this->wrapQuotes(date("Y-m-d")), $query);
		$query = $this->parseCustomQuery($query);
		return $query;
	}
	
	###########################################################################
	// Look for multiple [TODAY+XX] or [TODAY-XX] and replace it with appropriate date strings (wrapped 
	// in single quotes.
	static function parseCustomQuery($query)
	{
		$pos = stripos($query, "[TODAY");
		while($pos !== false)
		{
			$qleft  = trim(substr($query, 0, $pos));
			$qright = trim(substr($query, $pos+6, strlen($query)-$pos-6));
			$epos   = stripos($qright, "]");
			
			if($epos !== false)
			{
				$qdays  = trim(substr($qright, 0, $epos));
				$qright = trim(substr($qright, $epos+1, strlen($qright)-$epos-1));
				
				$sign = substr($qdays, 0, 1);
				$days = trim(substr($qdays, 1, strlen($qdays)-1));
				$days = $sign == "-" ? (-$days) : $days;
				$qdate = wmDomainDatabase::wrapQuotes(wmDomainDatabase::MySQLDateOffset(date("Y-m-d"),0,0,$days));
				
				$query = $qleft . " " . $qdate . " " . $qright;
			}
			else
				break;
				
			$pos = stripos($query, "[TODAY");
		}
		return $query;
	}
	
	###########################################################################
	
	static public function appendAllowedCategoriesSQLAndSearchFilter($vcids, $searchFilter, $SQL)
	{
		if(count($vcids) > 0)
		{
			$sqlAdd = "";
			foreach($vcids as $c)
			{
				if($c == 1)
					continue;
				if($sqlAdd != "")
					$sqlAdd .= " OR ";
				$sqlAdd .= "(b.CategoryID = $c AND b.sid = a.sid)";
			}
			if(stristr($SQL, " where "))
				$SQL .= " AND ($sqlAdd)";
			else
				$SQL .= " WHERE ($sqlAdd)";
		}
		if($searchFilter != "")
		{
			$searchFilter = trim($searchFilter);
			if(stristr($SQL, "WHERE ") !== false)
			{
				// Replace the starting "WHERE" with "AND"
				$ipos = mb_stripos($searchFilter, 'WHERE ');
				if($ipos !== false && $ipos == 0)
					$searchFilter = "AND " . mb_substr($searchFilter, 6);
			}
			$SQL .= " " . $searchFilter;
		}
		return $SQL;
	}
	
	###########################################################################
	
	public function getCustomQueryClause($cqdata, $vcids)
	{
		$clause = "";
		if(isset($cqdata['qcids']))
		{
			$qcids = $cqdata['qcids'];
			foreach($qcids as $qcid)
			{
				if($qcid > 1)
				{
					if(count($vcids))
					{
						# If not in valid category id list, ignore.
						if(in_array($qcid, $vcids) === false)
							continue;
					}
					if($clause !== "")
						$clause .= " OR ";
					$clause .= "(b.CategoryID = $qcid AND b.sid = a.sid)";
				}
			}
		}
		if(isset($cqdata['qs']))
		{
			$qs = $cqdata['qs'];
			if($clause !== "")
				$clause = "($clause) AND ";
			$clause .= "(a.domain LIKE '$qs')";
		}
		if(isset($cqdata['q']))
		{
			$cq = $cqdata['q'];
			$sanityarray = array('drop ', 'insert ', 'select ', 'from ', 'delete ', 'update ', 'alter ', 'show ', 'create ', 'use ');
			foreach($sanityarray as $sanity)
			{
				if(stristr($cq, $sanity) !== false)
				{
					$cq = "1=0";
					break;
				}
			}
			if($cq != "")
			{
				if($clause !== "")
					$clause = "($clause) AND ";
				$clause .= "($cq)";
			}
		}
		return $clause;
	}
	
	###########################################################################
	# $vcids - Valid category ids. The Categories the current user can access.
	# $aqid - Auto Query ID.

	public function jqGridDomainCount($cid, $aqid, $cqdata, $vcids, $responce)
	{		
		$domtable = $this->getDomainTableName();
		$cattable = $this->getCategoryIDToDomainIDTableName();
		$dbError = false;
		try
		{
			$usecat = false;
			$sqlCount = "SELECT COUNT(DISTINCT a.sid) AS count FROM  " . $domtable . " a";
			if(count($vcids) || $cid > 1 || $cqdata != null)
			{
				$sqlCount .= ", $cattable b ";
				$usecat = true;
			}
			
			if($cid > 1)
				$sqlCount .= "WHERE (b.CategoryID = $cid AND b.sid = a.sid)";
			else if($aqid > 0)
			{
				$query = $this->getAutoQuery($aqid, $usecat);
				if($query != "")
					$sqlCount = "SELECT COUNT(DISTINCT a.sid) AS count " . $query;
			}
			else if($cqdata != null)
			{
				$ewhere = $this->getCustomQueryClause($cqdata, $vcids);
				if($ewhere != "")
					$sqlCount .= " WHERE " . $this->expandQuery($ewhere);
			}
			
			$sqlFilter = $this->jqGridSearchData("a.");
			$sqlCount = $this->appendAllowedCategoriesSQLAndSearchFilter($vcids, $sqlFilter, $sqlCount);
			$this->getGridStats($sqlCount, $responce);
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			debugEcho($e->getMessage());
			return false;
		}
	}
	
	###########################################################################

	public function jqGridDomainData($cid, $aqid, $cqdata, $columns, $vcids, $responce)
	{
		$valid_columns = $this->getAllColumnNames($this->getDomainTableName());
		$sidx = gridPDO::getIndexedRequestVariable('sidx', "sid", $valid_columns); 
		$sord = gridPDO::getIndexedRequestVariable('sord', 'asc', array('asc', 'desc')); 
		
		$domtable = $this->getDomainTableName();
		$cattable = $this->getCategoryIDToDomainIDTableName();
		$dbError = false;
		try
		{
			$usecat = false;
			$sqlcolumns = str_replace("sid", "a.sid", $columns);
			$cc = explode(",", $sqlcolumns);
			foreach($cc as &$c)
			{
				if(strpos($c, "a.") === false)
					$c = "`" . $c . "`";
			}
			$cc[] = "a.$sidx";
			$sqlcolumns = implode(",", $cc);
			$SQL = "SELECT DISTINCT a.sid, $sqlcolumns FROM " . $domtable . " a";
			if(count($vcids) || $cid > 1 || $cqdata != null)
			{
				$SQL .= ", $cattable b ";
				$usecat = true;
			}
			if($cid > 1)
				$SQL .= " WHERE (b.CategoryID = $cid AND b.sid = a.sid)";
			else if($aqid > 0)
			{
				$query = $this->getAutoQuery($aqid, $usecat);
				if($query != "")
					$SQL = "SELECT DISTINCT a.sid, $sqlcolumns " . $query;
			}
			else if($cqdata != null)
			{
				$ewhere = $this->getCustomQueryClause($cqdata, $vcids);
				if($ewhere != "")
					$SQL .= " WHERE " . $this->expandQuery($ewhere);
			}
			
			$sqlFilter = $this->jqGridSearchData("a.");
			$SQL = $this->appendAllowedCategoriesSQLAndSearchFilter($vcids, $sqlFilter, $SQL);
			$SQL .= " ORDER BY a.$sidx $sord";
			if($responce->records > 0)
				$SQL .= " LIMIT $responce->start , $responce->limit";
			$stmt = $this->db_connect_handle->prepare($SQL);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			$i = 0;
			$colnames = explode(",", $columns);
			foreach($rows as $row)
			{
				$responce->rows[$i]['id']=$row['sid'];
				foreach($colnames as $col)
					$responce->rows[$i][$col] = $row[$col];
				$i++;
			}
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			debugEcho($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function getCustomQuerySQL($cqdata)
	{
		$SQL = "FROM " . $this->getDomainTableName() . " a, " . $this->getCategoryIDToDomainIDTableName() . " b";
		$ewhere = $this->getCustomQueryClause($cqdata, array());
		if($ewhere != "")
			$SQL .= " WHERE " . $ewhere; //$this->expandQuery($ewhere);
		return $SQL;
	}
	
	###########################################################################
	
	public function addSubdomain($sid, $sd)
	{
		$subdomain = $this->cleanupDomainName(trim($sd));
		if(subdomain != "" && $sid > 0)
		{
			$count = $this->getCount($this->getSubdomainTableName(), "sid = ? AND subdomain = ?", array($sid, $subdomain));
			if($count === false || $count == 0)
				return $this->insertIntoTable($this->getSubdomainTableName(), array('sid'=>$sid, 'subdomain'=>$subdomain, 'added_on'=>date("Y-m-d H:i:s")));
		}
		return false;
	}
	
	###########################################################################

	public function addDefaultSubdomains($sid)
	{
		$added = 0;
		$default_sub_domains = getConfigData('default_sub_domains', null);
		$sddata = trim($default_sub_domains);
		if($sddata != "")
		{
			$subdomains = explode(",", $sddata);
			foreach($subdomains as $sd)
				if($this->addSubdomain($sid, $sd) !== false)
					$added++;
		}
		return $added;
	}
	
	###########################################################################
	
	public function setSubdomainDataFromArray($hid, $subDomainData)
	{
		$tablename = $this->getSubdomainTableName();
        $valid_columns = $this->getAllColumnNames($tablename);
        
        $columnArray = array();
        $valueArray = array();
        foreach($subDomainData as $key=>$value)
        {
            if(in_array($key, $valid_columns) !== false)
            {
                $columnArray[] = $key;
                $valueArray[] = $value;
            }
        }
		try
		{
			$columns = "";
			foreach($columnArray as $column)
				$columns .= "$column = ?,";
			$columns = trim($columns, ", ");
			$valueArray[] = $hid;
			$sql = "UPDATE " . $tablename . " SET " . $columns . " WHERE hid=?";
			$q = $this->db_connect_handle->prepare($sql);
			$q->execute($valueArray);
			return true;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function editSubdomain($hid)
	{
		try
		{
			$sdomaintable = $this->getSubdomainTableName();
			$subdomain = $this->getSingleEntry($sdomaintable, "hid", $hid, "subdomain");
			if($subdomain !== false)
			{
				$allcolumns = $this->getAllColumnNames($sdomaintable);
				$dataarray = array();
				$update_manual_edit = false;
				foreach($_REQUEST as $key=>$value)
				{
					$kl = strtolower($key);
					if(in_array($kl, $allcolumns))
					{
						if($kl != "subdomain" && $kl != "hid" && $kl != "manual_edited_at"  && $kl != "edited")
						{
							$dataarray[$kl] = $this->cleanupFieldData($sdomaintable, $value, $kl);
							if($kl != "notes_a" && $kl != "notes_b" && $kl != "notes_c" && $kl != "notes_d")
								$update_manual_edit = true;
						}
					}
				}
				if($update_manual_edit)
				{
					$dataarray['manual_edited_at'] = date("Y-m-d H:i:s");
					$dataarray['edited'] = 1;
				}
				$previous = $this->getTableData($sdomaintable, implode(",", array_keys($dataarray)), "WHERE hid=?", array($hid));
				$edited = $this->setSubdomainDataFromArray($hid, $dataarray);
				if($edited !== false && count($dataarray))
				{
					$output = implode(', ', array_map(
						function ($v, $k) { return sprintf("%s='%s'", $k, $v); },
						$dataarray,
						array_keys($dataarray)
					));
					if($previous !== false && count($previous))
					{
						$previous = $previous[0];
						$output = $this->compareArraysWithKeys($dataarray, $previous);
					}
					else
						$output = "($output)";
					$sid = $this->getSingleEntry($sdomaintable, "hid", $hid, "sid");
					$domain = $this->getSingleEntry($this->getDomainTableName(), "sid", $sid, "domain");
					$this->logAuditEntry(AUDIT_EDITED_DOMAIN, "Domain {$subdomain}.{$domain} edited $output");
				}
				return $edited;
			}
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###############################################################################

	function compareArraysWithKeys($array01, $array02)
	{
		$output = "\n";
		foreach($array01 as $key=>$val)
		{
			$found = false;
			$oldval = "";
			if(isset($array01[$key]))
			{
				if($array02[$key] === $val)
					$found = true;
				$oldval = $array02[$key];
			}
			if(!$found)
				$output .= "$key from '$oldval' to '$val'\n";
		}
		return rtrim($output);
	}
	
	###########################################################################
	
	public function deleteSubdomains($hidlist)
	{
		try
		{
			$ids = explode(",", $hidlist);
			foreach($ids as &$id)
				$id = $this->db_connect_handle->quote($id);
			$this->db_connect_handle->exec("DELETE FROM " . $this->getSubdomainTableName() . " WHERE hid IN (" . implode(', ', $ids) . ')');
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	# $vcids - Valid category ids. The Categories the current user can access.
	# $aqid - Auto Query ID.

	public function jqGridSubdomainCount($cid, $aqid, $cqdata, $vcids, $responce)
	{		
		$domtable = $this->getSubdomainTableName();
		$cattable = $this->getCategoryIDToDomainIDTableName();
		$dbError = false;
		try
		{
			$usecat = false;
			$sqlCount = "SELECT COUNT(DISTINCT a.hid) AS count FROM  " . $domtable . " a";
			if(count($vcids) || $cid > 1 || $cqdata != null)
			{
				$sqlCount .= ", $cattable b ";
				$usecat = true;
			}
			
			if($cid > 1)
				$sqlCount .= "WHERE (b.CategoryID = $cid AND b.sid = a.sid)";
			else if($aqid > 0)
			{
				$query = $this->getAutoQuery($aqid, $usecat);
				if($query != "")
					$sqlCount .= " WHERE (a.sid IN (SELECT DISTINCT a.sid $query))";
			}
			else if($cqdata != null)
			{
				$ewhere = $this->getCustomQueryClause($cqdata, $vcids);
				$domtable = $this->getDomainTableName();
				$domsql = "SELECT DISTINCT a.sid FROM $domtable a";
				if($ewhere != "")
					$domsql .= " WHERE " . $this->expandQuery($ewhere);
				$sqlCount .= " WHERE (a.sid IN ($domsql))";
			}
			if(isset($_REQUEST['noroot']))
			{
				if(stripos($sqlCount, " where ") === false)
					$sqlCount .= " WHERE";
				else
					$sqlCount .= " AND";
				$sqlCount .= " (a.record_type='CNAME' OR a.record_type='A' OR a.ssl_issued_by is not null OR a.subdomain LIKE '%__%')";
			}
			
			$sqlFilter = $this->jqGridSubdomainSearchData("a.");
			$sqlCount = $this->appendAllowedCategoriesSQLAndSearchFilter($vcids, $sqlFilter, $sqlCount);
			$this->getGridStats($sqlCount, $responce);
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			debugEcho($e->getMessage());
			return false;
		}
	}
	
	###########################################################################

	public function jqGridSubdomainData($cid, $aqid, $cqdata, $columns, $vcids, $responce)
	{
		$valid_columns = $this->getAllColumnNames($this->getSubdomainTableName());
		$sidx = gridPDO::getIndexedRequestVariable('sidx', "sid", $valid_columns); 
		$sord = gridPDO::getIndexedRequestVariable('sord', 'asc', array('asc', 'desc')); 
		
		$sdomtable = $this->getSubdomainTableName();
		$cattable = $this->getCategoryIDToDomainIDTableName();
		$dbError = false;
		try
		{
			$usecat = false;
			$sqlcolumns = str_replace("hid", "a.hid", $columns);
			$sqlcolumns = str_replace(",domain", ",a.sid", $sqlcolumns);
			$sqlcolumns = str_replace(",hostname", ",a.sid", $sqlcolumns);
			$sqlcolumns = str_replace(",sid", ",a.sid", $sqlcolumns);
			$SQL = "SELECT DISTINCT a.hid, $sqlcolumns FROM " . $sdomtable . " a";
			if(count($vcids) || $cid > 1 || $cqdata != null)
			{
				$SQL .= ", $cattable b ";
				$usecat = true;
			}
			if($cid > 1)
				$SQL .= " WHERE (b.CategoryID = $cid AND b.sid = a.sid)";
			else if($aqid > 0)
			{
				$query = $this->getAutoQuery($aqid, $usecat);
				if($query != "")
					$SQL .= " WHERE (a.sid IN (SELECT DISTINCT a.sid $query))";
			}
			else if($cqdata != null)
			{
				$ewhere = $this->getCustomQueryClause($cqdata, $vcids);
				$domtable = $this->getDomainTableName();
				$domsql = "SELECT DISTINCT a.sid FROM $domtable a";
				if($ewhere != "")
					$domsql .= " WHERE " . $this->expandQuery($ewhere);
				$SQL .= " WHERE (a.sid IN ($domsql))";
			}
			if(isset($_REQUEST['noroot']))
			{
				if(stripos($SQL, " where ") === false)
					$SQL .= " WHERE";
				else
					$SQL .= " AND";
				$SQL .= " (a.record_type='CNAME' OR a.record_type='A' OR a.ssl_issued_by is not null OR a.subdomain LIKE '%__%')";
			}
			
			$sqlFilter = $this->jqGridSubdomainSearchData("a.");
			$SQL = $this->appendAllowedCategoriesSQLAndSearchFilter($vcids, $sqlFilter, $SQL);
			$SQL .= " ORDER BY a.$sidx $sord";
			
			if($responce->records > 0)
				$SQL .= " LIMIT $responce->start , $responce->limit";
			$stmt = $this->db_connect_handle->prepare($SQL);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			$i = 0;
			$colnames = explode(",", $columns);
			foreach($rows as $row)
			{
				$responce->rows[$i]['id']=$row['hid'];
				foreach($colnames as $col)
				{
					$rcol = $col;
					if($rcol == "domain")
						$rcol = 'sid';
					else if($rcol == "hostname")
						$rcol = 'sid';
					$responce->rows[$i][$col] = $row[$rcol];
				}
				$i++;
			}
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			debugEcho($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	# set $tablename = a. or b. etc for use when data is from two tables
	# $sql = select .. FROM table1 a, table2 b
	
	public function jqGridSubdomainSearchData($tablename="")
	{
		$ui_search_status	= "false";
		$ui_search_field  	= "";
		$ui_search_string 	= "";
		$ui_search_operator = "";
		$ui_search_filter	= "";
	
		if(isset($_REQUEST['_search']))
			$ui_search_status = $_REQUEST['_search']; 
		if(isset($_REQUEST['searchField']))
			$ui_search_field = $_REQUEST['searchField']; 
		if(isset($_REQUEST['searchString']))
			$ui_search_string = $_REQUEST['searchString']; 
		if(isset($_REQUEST['searchOper']))
			$ui_search_operator = $_REQUEST['searchOper']; 
	
		if(isset($_REQUEST['filters']))
		{
			$ui_search_filter = $_REQUEST['filters'];
			$ui_search_filter = str_replace("\\", "", $ui_search_filter);
		}

		$domtable = $this->getDomainTableName();
		$qWhere = '';
		if($ui_search_status == 'true')
		{
			$qWhere = 'WHERE';
			if($ui_search_filter != "")
			{
				$firstElem = true;
				$searchData = json_decode($ui_search_filter);
				foreach ($searchData->rules as $rule) 
				{
					if (!$firstElem) 
					{
						if (in_array($searchData->groupOp, array('AND', 'OR'))) 
							$qWhere .= ' '.$searchData->groupOp.' ';
					}
					else 
						$firstElem = false;	 
					if($rule->field == 'domain')
						$qWhere .= " $tablename" . "sid IN (SELECT sid FROM $domtable WHERE " . $rule->field . " " . $this->fnGetSQLSearchString($rule->op, $rule->data, $rule->field) . ")";
					else
						$qWhere .= " $tablename" . $rule->field . " " . $this->fnGetSQLSearchString($rule->op, $rule->data, $rule->field);
				}
				if($qWhere == 'WHERE' && $ui_search_string != "")
					$qWhere .= " $tablename" . $ui_search_field . ' ' . $this->fnGetSQLSearchString($ui_search_operator, $ui_search_string, $ui_search_field);
			}
			else			
			{
				$qWhere .= " " . $ui_search_field . ' ' . $this->fnGetSQLSearchString($ui_search_operator, $ui_search_string, $ui_search_field);
			}
		}
		
		if($qWhere == '')
			return '';
		else
			return ' ' . $qWhere;
	}
	
	###########################################################################
	
	public function deleteCategories($cidlist)
	{
		try
		{
			$catnames = array();
			$ids = explode(",", $cidlist);
			foreach($ids as &$id)
			{
				$id = intval($id);
				if($id == 1)  # Don't Delete 'All Domains' category.
					return false;
				$categoryname = $this->getSingleEntry($this->getCategoryTableName(), "CategoryID", $id, "CategoryName");
				$catnames[] = $categoryname;
				$id = $this->db_connect_handle->quote($id);	
			}
			$this->db_connect_handle->exec("DELETE FROM " . $this->getCategoryTableName() . " WHERE CategoryID IN (" . implode(', ', $ids) . ')');
			$this->db_connect_handle->exec("DELETE FROM " . $this->getCategoryIDToDomainIDTableName() . " WHERE CategoryID IN (" . implode(', ', $ids) . ')');
			if(count($catnames))
				$this->logAuditEntry(AUDIT_DELETED_CATEGORY, "Deleted " . count($catnames) . " categories (" . implode(", ", $catnames) . ")");
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function deleteDomains($didlist)
	{
		try
		{
			$domainlist = array();
			$ids = explode(",", $didlist);
			foreach($ids as &$id)
			{
				if(count($domainlist) < MAX_AUDIT_LOG_DOMAINS)
				{
					$domain = $this->getSingleEntry($this->getDomainTableName(), "sid", $id, "domain");
					$domainlist[] = "$domain";
				}
				$id = $this->db_connect_handle->quote($id);
			}
			# Delete Main List
			$this->db_connect_handle->exec("DELETE FROM " . $this->getDomainTableName() . " WHERE sid IN (" . implode(', ', $ids) . ')');
			# Delete from Category to Domain List
			$this->db_connect_handle->exec("DELETE FROM " . $this->getCategoryIDToDomainIDTableName() . " WHERE sid IN (" . implode(', ', $ids) . ')');
			# Delete from Lookup Queue
			$this->db_connect_handle->exec("DELETE FROM " . $this->getLookupQueueTableName() . " WHERE sid IN (" . implode(', ', $ids) . ')');
			# Delete from Subdomains
			$this->db_connect_handle->exec("DELETE FROM " . $this->getSubdomainTableName() . " WHERE sid IN (" . implode(', ', $ids) . ')');
			$deleted = count($ids);
			if($deleted)
			{
				if($deleted > MAX_AUDIT_LOG_DOMAINS)
					$domainlist[] = "...";
				$this->logAuditEntry(AUDIT_DELETE_DOMAINS, "Deleted $deleted domains (" . implode(", ", $domainlist) . ")");
			}
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function deleteAllDomains()
	{
		try
		{
			# Delete Main List
			$this->db_connect_handle->exec("DELETE FROM " . $this->getDomainTableName());
			# Delete from Category to Domain List
			$this->db_connect_handle->exec("DELETE FROM " . $this->getCategoryIDToDomainIDTableName());
			# Delete from Lookup Queue
			$this->db_connect_handle->exec("DELETE FROM " . $this->getLookupQueueTableName());
			# Delete Subdomains
			$this->db_connect_handle->exec("DELETE FROM " . $this->getSubdomainTableName());
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function resetSubdomainManualEditInfo($hid)
	{
		return $this->updateTable($this->getSubdomainTableName(), "manual_edited_at = NULL, edited = NULL where hid=?", array($hid));
	}
	
	###########################################################################
	
	public function resetDomainManualEditInfoForSid($sid)
	{
		return $this->updateTable($this->getDomainTableName(), "manual_edited_at = NULL, edited = NULL where sid=?", array($sid));
	}
	
	###########################################################################
	
	public function resetDomainManualEditInfoForDomain($domain)
	{
		return $this->updateTable($this->getDomainTableName(), "manual_edited_at = NULL, edited = NULL where domain=?", array($domain));
	}
	
	###########################################################################
	
	public function editDomain($sid)
	{
		try
		{
			$domaintable = $this->getDomainTableName();
			$domain = $this->getSingleEntry($domaintable, "sid", $sid, "domain");
			if($domain !== false)
			{
				$customcolumns = $this->getCustomColumns();
				$allcolumns = $this->getAllColumnNames($domaintable);
				$dataarray = array();
				$update_manual_edit = false;
				foreach($_REQUEST as $key=>$value)
				{
					$kl = strtolower($key);
					if(in_array($kl, $allcolumns))
					{
						if($kl != "domain" && $kl != "sid" && $kl != 'manual_edited_at' && $kl != 'edited')
						{
							// Fix the last_update value to include date+time
							if($kl == "last_update" && strpos($value, ":") === false)
								$value .= " 00:00:00";
							$dataarray[$kl] = $this->cleanupFieldData($domaintable, $value, $kl);
							if($kl != "notes_a" && $kl != "notes_b" && $kl != "notes_c" && $kl != "notes_d" && !in_array($kl, $customcolumns))
								$update_manual_edit = true;
						}
					}
				}
				if($update_manual_edit)
				{
					$dataarray['manual_edited_at'] = date("Y-m-d H:i:s");
					$dataarray['edited'] = 1;
				}
				$previous = $this->getTableData($domaintable, implode(",", array_keys($dataarray)), "WHERE sid=?", array($sid));
				$edited = $this->setDomainDataFromArray($domain, $dataarray);
				if($edited !== false && count($dataarray))
				{
					$output = implode(', ', array_map(
						function ($v, $k) { return sprintf("%s='%s'", $k, $v); },
						$dataarray,
						array_keys($dataarray)
					));
					if($previous !== false && count($previous))
					{
						$previous = $previous[0];
						$output = $this->compareArraysWithKeys($dataarray, $previous);
					}
					else
						$output = "($output)";
					$this->logAuditEntry(AUDIT_EDITED_DOMAIN, "Domain $domain edited $output");
				}
				return $edited;
			}
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function emptyCategory($cid)
	{
		$dberror = false;
		try
		{
			$sql = "DELETE FROM " . $this->getCategoryIDToDomainIDTableName() . " WHERE CategoryID=?";
			$stmt = $this->db_connect_handle->prepare($sql);
			if($stmt->execute(array($cid)))
				return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###########################################################################
	
	public function getCategoryIdsForDomain($sid)
	{
		try
		{
			$cids = array();
			$sql = "select CategoryID from " . $this->getCategoryIDToDomainIDTableName() . " WHERE sid=?";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute(array($sid));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($rows))
			{
				foreach($rows as $row)
					$cids[] = $row['CategoryID'];
			}
			return $cids;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function getAllCategoryIDsBySortOrder()
	{
		try
		{
			$cids = array();
			$sql = "select CategoryID from " . $this->getCategoryTableName() . " ORDER BY SortOrder DESC";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($rows))
			{
				foreach($rows as $row)
					$cids[] = $row['CategoryID'];
			}
			return $cids;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################
	
	public function setCategorySortOrder($cid, $sortorder)
	{
		$tablename = $this->getCategoryTableName();
		try
		{
			$sql = "UPDATE " . $tablename . " SET SortOrder=? WHERE CategoryID=?";
			$q = $this->db_connect_handle->prepare($sql);
			$q->execute(array($sortorder,$cid));
			return true;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###############################################################################
	
	public function getCategoryGridCounts($cids, $responce)
	{
		$idcolumn = "CategoryID";
		$table = $this->getCategoryTableName();
		
		$dbError = false;
		try
		{
			$sqlCount = "SELECT COUNT(CategoryID) AS count FROM  " . $table . $this->jqGridSearchData();
			$sqlAdd = "";
			foreach($cids as $cid)
			{
				$cid = intval($cid);
				if($sqlAdd != "")
					$sqlAdd .= " OR ";
				$sqlAdd .= " $idcolumn = $cid";
			}
			$sqlAdd = trim($sqlAdd);
			if($sqlAdd != "")
			{
				if(stristr($sqlCount, " where "))
					$sqlCount .= " AND ($sqlAdd)";
				else
					$sqlCount .= " WHERE ($sqlAdd)";
			}
			
			$this->getGridStats($sqlCount, $responce);
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###########################################################################

	public function getCategoryGridData($cids, $responce)
	{
		$idcolumn = "CategoryID";
		$table = $this->getCategoryTableName();
		$valid_columns = $this->getAllColumnNames($table);
		$sidx = gridPDO::getIndexedRequestVariable('sidx', $idcolumn, $valid_columns); 
		$sord = gridPDO::getIndexedRequestVariable('sord', 'asc', array('asc', 'desc')); 
		$columns = "CategoryID,CategoryName,SortOrder";
		$dbError = false;
		try
		{
			$SQL = "SELECT DISTINCT $idcolumn, $columns FROM " . $table . $this->jqGridSearchData();
			$sqlAdd = "";
			foreach($cids as $cid)
			{
				$cid = intval($cid);
				if($sqlAdd != "")
					$sqlAdd .= " OR ";
				$sqlAdd .= " $idcolumn = $cid";
			}
			$sqlAdd = trim($sqlAdd);
			if($sqlAdd != "")
			{
				if(stristr($SQL, " where "))
					$SQL .= " AND ($sqlAdd)";
				else
					$SQL .= " WHERE ($sqlAdd)";
			}
			$SQL .= " ORDER BY $sidx $sord";

			if($responce->records > 0)
				$SQL .= " LIMIT $responce->start , $responce->limit";
			$stmt = $this->db_connect_handle->prepare($SQL);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			$i = 0;
			$colnames = explode(",", $columns);
			foreach($rows as $row)
			{
				$responce->rows[$i]['id']=$row[$idcolumn];
				foreach($colnames as $col)
					$responce->rows[$i][$col] = $row[$col];
				$i++;
			}
			return true;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###############################################################################
	
	public function getWhoisData($sid)
	{
		$output = array('registry_whois'=>'', 'registrar_whois'=>'', 'ip_whois'=>'');
		$tablename = $this->getDomainTableName();
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT registry_whois,registrar_whois,ip_whois FROM " . $tablename . " WHERE  sid=?");
			$stmt->execute(array($sid));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
			{
				if(isset($rows[0]['registry_whois']))
					$output['registry_whois'] = $rows[0]['registry_whois'];
				if(isset($rows[0]['registrar_whois']))
					$output['registrar_whois'] = $rows[0]['registrar_whois'];
				if(isset($rows[0]['ip_whois']))
					$output['ip_whois'] = $rows[0]['ip_whois'];
			}
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
		return $output;
	}
	
	###############################################################################
	
	public function getDomainTableData($sid)
	{
		$dominfo = array();
		$domainname = "";
		$tablename = $this->getDomainTableName();
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $tablename . " WHERE  sid=?");
			$stmt->execute(array($sid));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
			{
				# Ignore columns that are no longer valid or not required.
				$ignore_list = array('domain', 'r_h_disp', 'homepage_html',
									'google_links','google_pr', 'yahoo_links', 'bing_links', 
									'google_pr_checked_at', 'google_sitepop_checked_at',
									'yahoo_sitepop_checked_at', 'bing_sitepop_checked_at', 
									'bing_index_count', 'bing_index_checked_at',
									'yahoo_index_count', 'yahoo_index_checked_at', 
									'se_link_count', 'us_tm_checked_at', 'us_tm_records');
				foreach($rows[0] as $what=>$info)
				{
					if($info === NULL)
						$info = '';
					if($what == "domain")
						$domainname = $info;
					if(in_array($what, $ignore_list) === false)
						$dominfo[$what] = $info;
				}
			}
			if($domainname != "" && isDataHistoryEnabled("whois")) {
				$dominfo['whois_history'] = "";
				$history = $this->getTableData($this->data_history_table, "id,lookedup_at,ftype", "WHERE domain=? AND (ftype = ? OR ftype = ?) ORDER BY lookedup_at DESC LIMIT 10", array($domainname, intval(LOOKUP_DOMAIN_WHOIS), intval(LOOKUP_SEC_DOMAIN_WHOIS)));
				if($history !== false) {
					foreach($history as $h) {
						$bclass = 'btn-danger';
						$btitle = "registrar whois";
						if($h['ftype'] == 1) {
							$bclass = 'btn-primary';
							$btitle = "registry whois";
						}
						$dominfo['whois_history'] .= "<a href=\"#\" id=\"wh_" . $h['id'] . "\" title=\"$btitle\" class=\"btn btn-xs history-popup {$bclass}\" style=\"margin-bottom: 8px;\">" . $h['lookedup_at'] . "</a>&nbsp;";
					}
				}
			}
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
		return $dominfo;
	}
	
	###############################################################################
	
	static function cleanupDomainName($domain)
	{
		if(function_exists("idn_convert"))
		{
			$ascii_domain = idn_convert($domain);
			$url = idn_reconvert($ascii_domain);
			# idn_convert returns domain in lowercase.
		}
		else
		{
			echo "Fatal Error: IDN Library not included";
			exit;
		}
		
		if ( substr($url, 0, 11) == 'http://www.')  { $url = substr($url, 11); }
		if ( substr($url, 0, 12) == 'https://www.') { $url = substr($url, 12); }
		if ( substr($url, 0, 7) == 'http://')  { $url = substr($url, 7); }
		if ( substr($url, 0, 8) == 'https://') { $url = substr($url, 8); }
		if ( strpos($url, '/') !== false) 
		{
			$ex = explode('/', $url);
			$url = $ex[0];
		}
		return trim(strip_tags($url));
	}
	
	###############################################################################
	
	public function addDomain($domain, $catid, &$sid)
	{
		$added = 0;
		$sid = 0;
		$domain = $this->cleanupDomainName(trim($domain));
		if($domain == "" || substr_count($domain, ".") == 0 || substr_count($domain, " ") > 0)
			return false;
		$ascii_domain = strtolower(idn_convert($domain));
		try
		{
			$timenow = date("Y-m-d H:i:s");
			$domtable = $this->getDomainTableName();
			$sid = $this->getDomainID($domain);
			if($sid !== false && $sid == 0)
			{
				$sqlAdd = "insert into $domtable (domain, added_on, ascii_domain) values (:domain, :timenow, :asciiname)";
				$stmt = $this->db_connect_handle->prepare($sqlAdd);
				$stmt->execute(array(':domain' => $domain, ':timenow'=>$timenow, ':asciiname'=>$ascii_domain));
				$sid = $this->db_connect_handle->lastInsertId('id');
				$added++;
			}
			else if($sid === false)
				return false;
			
			if($catid > 1 && $sid > 0)
				if($this->addDomainToCategory($sid, $catid) === false)
					return false;
			
			return $added;
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
			return false;
		}
		return false;
	}
	
	###############################################################################
	
	public function addDomains($domaindata, $catid)
	{
		$this->clearErrors();
		$domtable = $this->getDomainTableName();
		$split_tokens = " \n\r\t,|'\"()";
		$added = 0;
		$domainlist = array();
		if($domaindata != "")
		{
			$domain_names = array();
			$domain = strtok($domaindata, $split_tokens);
			while ($domain !== false) 
			{
				$status = $this->addDomain($domain, $catid, $sid);
				if($status !== false && $sid > 0 && $status > 0)
				{
					$this->doDefaultDomainLookups($sid);
					$added++;
					if(count($domainlist) < MAX_AUDIT_LOG_DOMAINS)
						$domainlist[] = $domain;
				}
				$domain = strtok($split_tokens);
			}
		}
		if($added)
		{
			if($added > MAX_AUDIT_LOG_DOMAINS)
				$domainlist[] = "...";
			$this->logAuditEntry(AUDIT_ADD_DOMAINS, "Added $added domains (" . implode(", ", $domainlist) . ")");
		}
		return $this->getLastError() == "" ? $added : false;
	}
	
	###############################################################################

	static public function getLastLookupTimeField($lookup_type)
	{
		switch($lookup_type)
		{
			case LOOKUP_DOMAIN_WHOIS:
				return "lookedup";
			case LOOKUP_IP_ADDRESS:
				return "ip_checked_at";
			case LOOKUP_HTTP_HOMEPAGE:
				return "home_page_checked_at";
			case LOOKUP_GOOGLE_PR:
				return "google_pr_checked_at";
			case LOOKUP_PING_RESPONSE:
				return 'ping_checked_at';
			case LOOKUP_MX_RECORDS:
				return 'mx_checked_at';
			case LOOKUP_YAHOO_LINKS:
				return 'yahoo_sitepop_checked_at';
			case LOOKUP_GOOGLE_LINKS:
				return 'google_sitepop_checked_at';
			case LOOKUP_ALEXA_DATA:
				return 'alexa_checked_at';
			case LOOKUP_SSL_CERTS:
				return 'ssl_checked_at';
			case LOOKUP_IP_WHOIS:
				return 'ip_whois_at';
			case LOOKUP_SUB_DOMAINS:
				return 'dns_checked_at';
			case LOOKUP_GOOGLE_INDEX:
				return "google_index_checked_at";
			default:
				return "";
		}
	}

	###############################################################################
	// Add Lookup
	// See if a lookup is required.
	// See if the lookup entry already exists.
	// If No, add

	public function addToLookupQueue($sid, $lookup_type, $min_gap=1440)
	{	
		$last_lookup_time_field = $this->getLastLookupTimeField($lookup_type);
		if($last_lookup_time_field != "")
		{
			$lookup_required = true;
			$next_lookup = $this->getSingleEntry($this->getDomainTableName(), "sid", $sid, $last_lookup_time_field);
			if($next_lookup !== false)
			{
				$next_lookup = strtotime($next_lookup) + $min_gap*60; 
				$current_time = time();
				if($next_lookup > $current_time)
					$lookup_required = false;
			}
			if($lookup_required)
				return $this->addLookup($sid, $lookup_type);
		}
		return false;
	}
	
	###############################################################################
	
	public function addToRegistrarWhoisLookupQueue($sid, $server, $mingap=1440)
	{
		$luqtable = $this->getLookupQueueTableName();
		$lookup_type = LOOKUP_SEC_DOMAIN_WHOIS;
		$count = $this->getCount($luqtable, "sid=? AND type=?", array($sid,$lookup_type));
		if($count !== false && $count == 0)
		{
			try
			{
				$wait = LUQSTAT_WAITING;
				$timenow = date("Y-m-d H:i:s");
				$asql  = "insert into $luqtable (sid, type, status, created_on, server) values (:sid, $lookup_type, $wait, :timenow, :server);";
				$dstmt = $this->db_connect_handle->prepare($asql);
				$dstmt->execute(array(':sid'=>$sid,':timenow'=>$timenow,':server'=>$server));
				return true;
			}
			catch (PDOException $e) 
			{
				$this->setError($e->getMessage());
			}	
		}	
		return false;
	}

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

	public function addLookup($sid, $lut)
	{
		$luqtable = $this->getLookupQueueTableName();
		if($lut == LOOKUP_DOMAIN_WHOIS)
			$sql = "SELECT COUNT(*) AS count FROM $luqtable WHERE sid=? AND (type=? OR type=8192)";    
		else
			$sql = "SELECT COUNT(*) AS count FROM $luqtable WHERE sid=? AND type=?"; 
		try
		{
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute(array($sid,$lut));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows))
			{
				$count = $rows[0]['count'];
				if(!$count)
				{
					$wait = LUQSTAT_WAITING;
					$timenow = date("Y-m-d H:i:s");
					$sql  = "insert into $luqtable (sid, type, status, created_on) values (:sid, :lut, :wait, :timenow);";
					$stmt = $this->db_connect_handle->prepare($sql);
					$stmt->execute(array('sid'=>$sid, 'lut'=>$lut, 'wait'=>$wait, 'timenow'=>$timenow));
					return true;
				}
			}
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}	
		return false;		
	}
	
	###############################################################################
	
	public function doDefaultDomainLookups($sid)
	{
		$this->addDefaultSubdomains($sid);
		$lookups = getConfigData('default_lookup_types', "dw,ip");
		$luts = explode(",", $lookups);
		foreach($luts as $luw)
		{
			$luw = strtolower(trim($luw));
			if($luw == "alexa")
				$this->addToLookupQueue($sid, LOOKUP_ALEXA_DATA);
			else if($luw == "dw")
				$this->addToLookupQueue($sid, LOOKUP_DOMAIN_WHOIS);
			//else if($luw == "sdw") # Secondary domain whois is automatic
			//	$this->addToLookupQueue($sid, LOOKUP_SEC_DOMAIN_WHOIS);
			else if($luw == "gpr")
				$this->addToLookupQueue($sid, LOOKUP_GOOGLE_PR);
			else if($luw == "ip")
				$this->addToLookupQueue($sid, LOOKUP_IP_ADDRESS);
			else if($luw == "mx")
				$this->addToLookupQueue($sid, LOOKUP_MX_RECORDS);
			else if($luw == "ipw")
				$this->addToLookupQueue($sid, LOOKUP_IP_WHOIS);
			else if($luw == "http")
				$this->addToLookupQueue($sid, LOOKUP_HTTP_HOMEPAGE);
			else if($luw == "ping")
				$this->addToLookupQueue($sid, LOOKUP_PING_RESPONSE);
			else if($luw == "ssl")
				$this->addToLookupQueue($sid, LOOKUP_SSL_CERTS);
			else if($luw == "dns")
				$this->addToLookupQueue($sid, LOOKUP_SUB_DOMAINS);
			else if($luw == "gi")
				$this->addToLookupQueue($sid, LOOKUP_GOOGLE_INDEX);
		}
	}
	
	###############################################################################

	function addDomainToLookupQueue($sid, $what, $ri)
	{
		$lucount = 0;
		if($what & LOOKUP_DOMAIN_WHOIS)
			if($this->addToLookupQueue($sid, LOOKUP_DOMAIN_WHOIS, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_IP_ADDRESS)
			if($this->addToLookupQueue($sid, LOOKUP_IP_ADDRESS, $ri)  !== false)
				$lucount++;
		if($what & LOOKUP_GOOGLE_PR)
			if($this->addToLookupQueue($sid, LOOKUP_GOOGLE_PR, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_ALEXA_DATA)
			if($this->addToLookupQueue($sid, LOOKUP_ALEXA_DATA, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_MX_RECORDS)
			if($this->addToLookupQueue($sid, LOOKUP_MX_RECORDS, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_HTTP_HOMEPAGE)
			if($this->addToLookupQueue($sid, LOOKUP_HTTP_HOMEPAGE, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_PING_RESPONSE)
			if($this->addToLookupQueue($sid, LOOKUP_PING_RESPONSE, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_GOOGLE_LINKS)
			if($this->addToLookupQueue($sid, LOOKUP_GOOGLE_LINKS, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_YAHOO_LINKS)
			if($this->addToLookupQueue($sid, LOOKUP_YAHOO_LINKS, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_SSL_CERTS)
			if($this->addToLookupQueue($sid, LOOKUP_SSL_CERTS, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_IP_WHOIS)
			if($this->addToLookupQueue($sid, LOOKUP_IP_WHOIS, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_SUB_DOMAINS)
			if($this->addToLookupQueue($sid, LOOKUP_SUB_DOMAINS, $ri) !== false)
				$lucount++;
		if($what & LOOKUP_GOOGLE_INDEX)
			if($this->addToLookupQueue($sid, LOOKUP_GOOGLE_INDEX, $ri) !== false)
				$lucount++;
		return $lucount;
	}
	
	###############################################################################
	# Log the lookup time-stamp into DB

	public function logQueueLookupTimestamp()
	{
		$servertable = $this->getInstalledServersTableName();
		$server_name = $this->getInstallServerID();
		$conntime = strval(time());
		
		try
		{
			$sql = "SELECT COUNT(*) AS count FROM $servertable WHERE name=?";  
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute(array($server_name));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
			{
				if($rows[0]['count'] == 0)
				{
					$sql  = "insert into $servertable (name, last_lookup_at, last_connect_at) values (:server_name,:lookuptime,:conntime);";
					$stmt = $this->db_connect_handle->prepare($sql);
					$stmt->execute(array(':server_name'=>$server_name, ':lookuptime'=>$conntime,':conntime'=>$conntime));
					return true;
				}
				if($rows[0]['count'] == 1)
				{
					$sql = "update $servertable set last_lookup_at=?, last_connect_at=? where name=?";
					$stmt = $this->db_connect_handle->prepare($sql);
					$stmt->execute(array($conntime, $conntime, $server_name));
					return true;
				}
			}
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###############################################################################
	
	public function getLastQueueProcessedTime()
	{
		$servertable = $this->getInstalledServersTableName();
		$server_name = $this->getInstallServerID();
		
		try
		{
			$sql = "SELECT last_connect_at FROM $servertable WHERE name=?"; 
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute(array($server_name));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
				return $rows[0]['last_connect_at'];
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###############################################################################
	
	# Log a bunch of stuff about the client server to the database
	#
	public function logQueueProcessingTimestamp()
	{
		global $install_server_id;
		$servertable = $this->getInstalledServersTableName();
		
		$server_ip = "";
		if(isset($_SERVER['SERVER_ADDR']))
			$server_ip = $_SERVER['SERVER_ADDR'];
		else
			$server_ip = $this->getPreference("server_ip_address");
		$server_name = $this->getInstallServerID();
		$host_name = strtolower(php_uname('n'));
		$cnf_name = "";
		$conntime = strval(time());
		if(isset($install_server_id))
			$cnf_name = strtolower(trim($install_server_id));
		$verindex = get_build_index();			
		$base_url = (is_cli() ? ($this->getPreference('root_url')) : get_root_url());

		try
		{
			$count = $this->getCount($servertable, "name=?", array($server_name));
			if ($count != false && $count == 1) 
			{
				$sinfo = "last_connect_at=?, ver_index=?, address=?, hostname=?, cnfname=?, baseurl=? where name=?";
				$updata = array($conntime,$verindex, $server_ip, $host_name, $cnf_name, $base_url, $server_name);
				return $this->updateTable($servertable, $sinfo, $updata);
			}
			else
			{
				$qindex = $this->findQueueIndex($server_name);
				$data = array('address'=>$server_ip, 'name'=>$server_name, 'hostname'=>$host_name, 'cnfname'=>$cnf_name, 'baseurl'=>$base_url, 'last_connect_at'=>$conntime, 'ver_index'=>$verindex, 'queue_index'=>$qindex);
				return $this->insertIntoTable($servertable, $data);
			}
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	/////////////////////////////////////////////////////////////////////////////////////////////////////////

	public function findQueueIndex($name)
	{
		if(strtolower($name) == "master" || $name == "" || strtolower($name) == "default")
			return 0;
			
		$qdarray[] = 0;
		$server_array = $this->getTableData($this->getInstalledServersTableName(), "*", "order by last_connect_at desc");
		if($server_array !== false)
		{
			foreach($server_array as $row)
			{
				if(isset($row['queue_index']))
				{
					$qindex = $row['queue_index'];
					if(!in_array($qindex, $qdarray))
						$qdarray[] = $qindex;
				}
			}
		}
		$sanity_check = 0;
		$qdx = 1;
		while(in_array($qdx, $qdarray))
		{
			$qdx++;
			$sanity_check++;
			if($sanity_check > 10000)
				break;
		}
		return $qdx;
	}
    
    ###############################################################################
    
    public function getFromLookupQueue($max_count, $start_from=0, $prefer="")
    {
        $luqtable = $this->getLookupQueueTableName();
        $sql = "select * from $luqtable order by created_on asc";
        if($prefer != "")
        {
            $where = "";
            $luts = explode(",", $prefer);
            foreach($luts as $luw)
            {
                $luw = strtolower(trim($luw));
                if($luw == "alexa")
                    $where .= " | type=" . intval(LOOKUP_ALEXA_DATA);
                else if($luw == "dw")
                    $where .= " | type=" . intval(LOOKUP_DOMAIN_WHOIS);
                else if($luw == "sdw")
                    $where .= " | type=" . intval(LOOKUP_SEC_DOMAIN_WHOIS);
                else if($luw == "gpr")
                    $where .= " | type=" . intval(LOOKUP_GOOGLE_PR);
                else if($luw == "ip")
                    $where .= " | type=" . intval(LOOKUP_IP_ADDRESS);
                else if($luw == "mx")
                    $where .= " | type=" . intval(LOOKUP_MX_RECORDS);
                else if($luw == "ipw")
                    $where .= " | type=" . intval(LOOKUP_IP_WHOIS);
                else if($luw == "http")
                    $where .= " | type=" . intval(LOOKUP_HTTP_HOMEPAGE);
                else if($luw == "ping")
                    $where .= " | type=" . intval(LOOKUP_PING_RESPONSE);
                else if($luw == "ssl")
                    $where .= " | type=" . intval(LOOKUP_SSL_CERTS);
                else if($luw == "gi")
                    $where .= " | type=" . intval(LOOKUP_GOOGLE_INDEX);
                else if($luw == "dns")
                    $where .= " | type=" . intval(LOOKUP_SUB_DOMAINS);
            }
            $where = trim($where, "| ");
            $where = str_replace("|", "OR", $where);
            if($where != "")
                $sql = "select * from $luqtable WHERE ($where) order by created_on asc";
        }
        if($start_from != 0 && $start_from > 0 && $start_from < 150000)
        {
            $endat = $start_from + $max_count + 1;
            $limit = " LIMIT $start_from, $endat";
            $sql .= $limit;
        }
        
        $lookup_array = array();
        try
        {
            $stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if($rows !== false && is_array($rows))
			{
				foreach($rows as $row)
				{
					$lookup_array[] = $row;
					$lookup_type   = $row['type'];
					$sid           = $row['sid'];
					$this->deleteFromTable($luqtable, "sid=? AND type=?", array($sid, $lookup_type));
					if(count($lookup_array) == $max_count)
						break;
				}
            }
        }
        catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
        return $lookup_array;
    }
	
	###############################################################################

	function getQueueIndex()
	{
		$name = $this->getInstallServerID();
		if(strtolower($name) == "master" || $name == "" || strtolower($name) == "default")
			return 0;
		$servertable = $this->getInstalledServersTableName();
		$qindex = $this->getSingleEntry($servertable, "name", $name, 'queue_index');
		return $qindex !== false ? $qindex : 0;
	}
	
	###############################################################################
	
	public function getTLDs($orderby="ORDER BY LENGTH(tld) DESC")
	{
		$tlds = $this->getTableData($this->getTldsTableName(), "tld", $orderby);
		if($tlds !== false)
		{
			$list = array();
			foreach($tlds as $tld)
				$list[] = $tld['tld'];
			return $list;
		}
		return false;
	}
	
	###############################################################################
	
	public function getTLDsInfo($orderby="ORDER BY LENGTH(tld) DESC")
	{
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT tld,server FROM " . $this->getTldsTableName() . " " . $orderby);
			$stmt->execute();
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(is_array($rows))
				return $rows;
			return false;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
		return false;
	}
	
	/////////////////////////////////////////////////////////////////////////////////////////////////////////

	function setWhoisServerForTLD($tld, $server)
	{
		$tldtable = $this->getTldsTableName();
		$count = $this->getCount($tldtable, "tld=?", array($tld));
		if($count === false)
			$count = 0;
		if($count)
			return $this->updateTable($tldtable, "server=? where tld=?", array($server, $tld));
		else
			return $this->insertIntoTable($tldtable, array('tld'=>$tld, 'server'=>$server));
	}
	
	###############################################################################
	
	public function getWhoisServerInfo($server)
	{
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM " . $this->getWhoisserverTableName() . " WHERE server=?");
			$stmt->execute(array($server));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if($rows !== false && isset($rows[0]))
				return $rows[0];
			return false;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
		return false;
	}
	
	###############################################################################
	
	public function setWhoisServerInfo($server, $serverdata)
	{
		$tablename = $this->getWhoisserverTableName();
        $valid_columns = $this->getAllColumnNames($tablename);
		
		$present = $this->getCount($tablename, "server = ?", array($server));
		if($present === false || $present == 0)
		{
			$this->insertIntoTable($tablename, array('server'=>$server)); 
		}
        
        $columnArray = array();
        $valueArray = array();
        foreach($serverdata as $key=>$value)
        {
            if(in_array($key, $valid_columns) !== false)
            {
				if($key == "last_connect_at" && $value == "null")
				{
					$this->resetLastConnectTimeForExternalServer($server);
					continue;
				}
				if($value == '')
				{
					// Ignore Integer and date fields with empty input.
					if($key == 'conninterval' || $key == 'port' || $key == 'maxconnections' || $key == 'stop_lookups' || $key == 'last_connect_at')
						continue;
				}
                $columnArray[] = $key;
                $valueArray[] = $value;
            }
        }
		try
		{
			$columns = "";
			foreach($columnArray as $column)
				$columns .= "$column = ?,";
			$columns = trim($columns, ", ");
			$valueArray[] = $server;
			$sql = "UPDATE " . $tablename . " SET " . $columns . " WHERE server=?";
			$q = $this->db_connect_handle->prepare($sql);
			$q->execute($valueArray);
			return true;
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
	}
	
	###############################################################################
	
	public function getConnectionIntervalForWhoisServer($wserver)
	{
		$ci = 10;
		try
		{
			$stmt = $this->db_connect_handle->prepare("SELECT conninterval FROM " . $this->getWhoisserverTableName() . " WHERE server=?");
			$stmt->execute(array($wserver));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if($rows !== false && isset($rows[0]) && $rows[0]['conninterval'] !== null)
				$ci = $rows[0]['conninterval'];
			else
			{
				if(strtolower($wserver) == "whois.crsnic.net")
					$ci = 0;
				else if(strtolower($wserver) == "whois.ausregistry.net.au")
					$ci = 183;
				else if(strtolower($wserver) == "whois.nic.xyz")
					$ci = 16;
				else
					$ci = 10;
			}
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
		}
		return $ci;
	}
	
	###############################################################################
	
	public function setLastConnectTimeForExternalServerToNow($server)
	{
		return $this->setLastConnectTimeForExternalServer($server, date("Y-m-d H:i:s"));
	}
	
	###############################################################################
	
	public function resetLastConnectTimeForExternalServer($server)
	{
		return $this->setLastConnectTimeForExternalServer($server, null);
	}
	
	###############################################################################
	
	public function setLastConnectTimeForExternalServer($server, $ct)
	{
		$dbError = false;
		try
		{
			$isname = $this->getInstallServerID();
			$table = $this->getLastLookupTimeTableName();
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM $table WHERE server=? AND name=?");
			$stmt->execute(array($server, $isname));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
			{
				$stmt = $this->db_connect_handle->prepare( "insert into $table (name, server, last_connect_at) values (:lusid, :server, :ct)");
				return $stmt->execute(array($isname,$server, $ct));
			}
			else
			{
				$stmt = $this->db_connect_handle->prepare( "update $table set last_connect_at=? where server=? and name=?");
				return $stmt->execute(array($ct, $server, $isname));
			}
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###############################################################################
	
	public function getLastConnectTimeForExternalServer($server)
	{
		$dbError = false;
		try
		{
			$isname = $this->getInstallServerID();
			$table = $this->getLastLookupTimeTableName();
			$sql = "select last_connect_at from $table where server = ? AND name= ?";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute(array($server, $isname));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
				return $rows[0]['last_connect_at'];
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###############################################################################
	
	public function setBlockedFromTimestampForExternalServerToNow($server)
	{
		$this->setBlockedFromTimestampForExternalServer($server, date("Y-m-d H:i:s"));
	}
	
	###############################################################################
	
	public function resetBlockedFromTimestampForExternalServer($server)
	{
		$this->setBlockedFromTimestampForExternalServer($server, null);
	}
	
	###############################################################################
	
	public function setBlockedFromTimestampForExternalServer($server, $ct)
	{
		$dbError = false;
		try
		{
			$server .= "-block";
			$isname = $this->getInstallServerID();
			$table = $this->getLastLookupTimeTableName();
			$stmt = $this->db_connect_handle->prepare("SELECT * FROM $table WHERE server=? AND name=?");
			$stmt->execute(array($server, $isname));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 0)
			{
				$stmt = $this->db_connect_handle->prepare( "insert into $table (name, server, last_connect_at) values (:lusid, :server, :ct)");
				return $stmt->execute(array($isname,$server, $ct));
			}
			else
			{
				$stmt = $this->db_connect_handle->prepare( "update $table set last_connect_at=? where server=? and name=?");
				return $stmt->execute(array($ct, $server, $isname));
			}
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###############################################################################
	
	# Get the "BlockedFrom" timestamp
	public function getBlockedFromTimestampForExternalServer($server)
	{
		$dbError = false;
		try
		{
			$server .= "-block";
			$isname = $this->getInstallServerID();
			$table = $this->getLastLookupTimeTableName();
			$sql = "select last_connect_at from $table where server = ? AND name= ?";
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute(array($server, $isname));
			$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
			if(count($rows) == 1)
				return $rows[0]['last_connect_at'];
		}
		catch (PDOException $e) 
		{
			$dberror = true;
			$this->setError($e->getMessage());
		}
		return false;
	}
	
	###############################################################################
	
	public function isOkToConnectoToExternalServer($server)
	{
		$banned_at = $this->getBlockedFromTimestampForExternalServer($server);
		if($banned_at !== false)
		{
			if($banned_at != "" && $banned_at != null)
			{
				$next_connect = strtotime($banned_at) + 24*3600 + 10;
				if($next_connect > time())
					return false;
			}
		}
			
		$last_connect_at = $this->getLastConnectTimeForExternalServer($server);
		if($last_connect_at !== false && $last_connect_at != null && $last_connect_at != "")
		{
			$conninterval = $this->getConnectionIntervalForWhoisServer($server);
			$next_connect = strtotime($last_connect_at) + $conninterval;
			if($next_connect > time())
				return false;
		}
		return true;
	}

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

	public function updateDataHistory($domain, $ftype, $fval)
	{
		$domain = trim($domain,chr(0xC2).chr(0xA0)); 
		$conntime = date("Y-m-d H:i:s");
		if($ftype == intval(LOOKUP_DOMAIN_WHOIS) || $ftype == intval(LOOKUP_SEC_DOMAIN_WHOIS))
			return $this->insertIntoTable($this->getDataHistoryTableName(), array('domain'=>$domain, 'ftype'=>intval($ftype), 'tvalue'=>$fval, 'fvalue'=>0, 'lookedup_at'=>$conntime));
		else
			return $this->insertIntoTable($this->getDataHistoryTableName(), array('domain'=>$domain, 'ftype'=>intval($ftype), 'fvalue'=>intval($fval), 'lookedup_at'=>$conntime));
	}
	
	###############################################################################
	
	public function setRowHighlights($days)
	{
		$mysqldate = $this->MySQLDateOffset(date("Y-m-d"),0,0,$days);
		$sixmonths = $this->MySQLDateOffset(date("Y-m-d"),0,0,-180) . " 0:0:0";
		$today = date("Y-m-d");
		$domaintable = $this->getDomainTableName();
		$queries = array( 
		"UPDATE $domaintable SET r_h_disp = 0 WHERE registry_expiry IS NOT NULL OR registrar_expiry IS NOT NULL",
		"UPDATE $domaintable SET r_h_disp = 14 WHERE registry_expiry IS NULL AND registrar_expiry IS NULL",
		"UPDATE $domaintable SET r_h_disp = 10 WHERE edited = 1",
		"UPDATE $domaintable SET r_h_disp = 11 WHERE status = 'Setup Error'",
		"UPDATE $domaintable SET r_h_disp = 12 WHERE availability = 'Available'",
		"UPDATE $domaintable SET r_h_disp = 13 WHERE lookedup IS NOT NULL AND lookedup < '$sixmonths'",
		"STATUSQUERY",
		"UPDATE $domaintable SET r_h_disp = 17 WHERE (registrar_expiry IS NOT NULL AND registrar_expiry <=  '$today') OR (registry_expiry IS NOT NULL  AND registry_expiry <= '$today')",
		"UPDATE $domaintable SET r_h_disp = 16 WHERE registrar_expiry IS NOT NULL AND registry_expiry IS NOT NULL AND registrar_expiry <  '$mysqldate' AND registry_expiry >=  '$mysqldate'",
		"UPDATE $domaintable SET r_h_disp = 15 WHERE registry_expiry IS NOT NULL AND registry_expiry <  '$mysqldate'"
		);
		
		foreach($queries as $query)
		{
			try
			{
				if($query == "STATUSQUERY")
				{
					$query = "";
					$sarray_s = trim(getConfigData('highlight_status_codes', null));
					if($sarray_s != "")
					{
						$sarray = explode(",", $sarray_s);
						$s1 = "";
						foreach($sarray as $s)
						{
							$s = trim($s);
							if($s != "")
							{
								if($s1 != "")
									$s1 .= " OR";
								$s1 .= " status LIKE '%$s%'";
							}
						}
						$s1 = trim($s1);
						if($s1 != "")
							$query = "UPDATE $domaintable SET r_h_disp = 18 WHERE ($s1)";
					}
				}
				if($query != "")
				{
					$stmt = $this->db_connect_handle->prepare($query);
					$stmt->execute(array());
				}
			}
			catch (PDOException $e) 
			{
				$this->setError($e->getMessage());
				return false;
			}
		}
		return true;
	}
	
	###############################################################################
	
	public function setSubdomainRowHighlights($days)
	{
		$mysqldate = $this->MySQLDateOffset(date("Y-m-d"),0,0,$days);
		$sixmonths = $this->MySQLDateOffset(date("Y-m-d"),0,0,-180) . " 0:0:0";
		$today = date("Y-m-d");
		$domaintable = $this->getSubdomainTableName();
		$queries = array( 
		"UPDATE $domaintable SET r_h_disp = 0",
		"UPDATE $domaintable SET r_h_disp = 10 WHERE edited = 1",
		"UPDATE $domaintable SET r_h_disp = 17 WHERE (ssl_valid_to IS NOT NULL AND ssl_valid_to <=  '$mysqldate')"
		);
		
		foreach($queries as $query)
		{
			try
			{
				$stmt = $this->db_connect_handle->prepare($query);
				$stmt->execute(array());
			}
			catch (PDOException $e) 
			{
				$this->setError($e->getMessage());
				return false;
			}
		}
		return true;
	}
	
	###############################################################################
	
	public function getInstalledServerStatus()
	{
		$table = $this->getInstalledServersTableName();
		return $this->getTableData($table, "*", "ORDER BY last_connect_at DESC");
	}
	
	###############################################################################
	
	public function resetInstalledServerTable()
	{
		$table = $this->getInstalledServersTableName();
		$sql = "truncate table $table";
		try
		{
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute(array());
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
		return true;
	}
	
	###############################################################################
	
	public function logAuditEntry($action, $description="")
	{
		$ui = getUserinfoFromSession();
		if(!isset($ui['uid']))
			$uid = 0;
		else
			$uid = $ui['uid'];
		$remote = "";
		if(isset($_SERVER['REMOTE_ADDR']))
			$remote = $_SERVER['REMOTE_ADDR'];
			
		if($ui !== false)
		{
			$lastconn = date("Y-m-d H:i:s");
			return $this->insertIntoTable($this->getAuditLogTableName(), array('action'=>$action, 'description'=>$description, 'timeat'=>$lastconn, 'uid'=>$uid, 'remote'=>$remote));
		}
		return false;
	}
	
	###############################################################################
	
	public function getAllDisplayColumns()
	{
		$displaycolumns = array();
		$dcols = $this->getTableData($this->displaycolumns_table, "entry");
		if($dcols !== false)
		{
			foreach($dcols as $entry)
			{
				$dc = explode(";", $entry);
				$displaycolumns = array_merge($displaycolumns, $dc);
			}
		}
		return array_unique($displaycolumns);
	}
	
	###############################################################################
	
	public function getNoGridDomainColumnNames()
	{
		return array('r_h_disp', 'registrar_whois', 'registry_whois', 'sid', 'homepage_html', /*'ascii_domain',*/
		'write_protect', 'disable_alerts', 'yahoo_index_count', 'yahoo_links', 'bing_index_count', 
		'bing_links', 'yahoo_index_checked_at', 'yahoo_sitepop_checked_at', 'se_link_count',
		'bing_index_checked_at', 'bing_sitepop_checked_at', 'google_sitepop_checked_at', 
		'google_links', 'us_tm_records', 'us_tm_checked_at', 'google_pr');
	}
	
	###############################################################################
	
	public function updateSecondLevelTLDs($tlds=array())
	{
		if(!count($tlds))
			$tlds = $this->getPublicTLDList();
		$table = $this->getSecondLevelTLDTableName();
		$sql = "truncate table $table";
		try
		{
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute(array());
			$question_marks = array();
			for($i = 0; $i < count($tlds); $i++)
				$question_marks[] = "(?)";
			$sql = "INSERT into $table (tld) values " . implode(",", $question_marks);
			$stmt = $this->db_connect_handle->prepare($sql);
			$stmt->execute($tlds);
		}
		catch (PDOException $e) 
		{
			$this->setError($e->getMessage());
			return false;
		}
		return $tlds;
	}
	
	###############################################################################

	static function getPublicTLDList($onlyseclevel=false)
	{
		$tlds = array();
		$url = "https://publicsuffix.org/list/public_suffix_list.dat";
		$result = @file_get_contents($url);
		if($result !== false)
		{
			$parts = explode("\n", $result);
			foreach($parts as $part)
			{
				$part = trim($part, "\r\n\t ");
				if($part == "")
					continue;
				else if($part[0] == '!')
					continue;
				else if(mb_strpos($part, "===END ICANN DOMAINS===") !== false)
					break;
				else if(strlen($part) >= 2)
				{
					if($part[0] == '/' && $part[1] == '/')
						continue;
					else if($part[0] == '*' && $part[1] == '.')
						$part = mb_substr($part, 2);
				}
				if($onlyseclevel)
				{
					if(mb_strpos($part, ".") === false)
						continue;
				}
				$tlds[] = $part;
			}
		}
		return array_unique($tlds);
	}
}

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