<?php if (!defined('VB_ENTRY')) die('Access denied.');
/*======================================================================*\
|| #################################################################### ||
|| # vBulletin 5.1.1 - Licence Number VBCUP51ZOY
|| # ---------------------------------------------------------------- # ||
|| # Copyright �2000-2014 vBulletin Solutions Inc. All Rights Reserved. ||
|| # This file may not be redistributed in whole or significant part. # ||
|| # ---------------- VBULLETIN IS NOT FREE SOFTWARE ---------------- # ||
|| # http://www.vbulletin.com | http://www.vbulletin.com/license.html # ||
|| #################################################################### ||
\*======================================================================*/

/**
*
* @package vBulletin
* @version $Revision: 28823 $
* @since $Date: 2008-12-16 17:43:04 +0000 (Tue, 16 Dec 2008) $
* @copyright vBulletin Solutions Inc.
*/
class vBAdmincp_dB_MYSQL_QueryDefs extends vB_dB_MYSQL_QueryDefs
{

	/**
	* This class is called by the new vB_dB_Assertor database class
	* It does the actual execution. See the vB_dB_Assertor class for more information
	*
	* Note that there is no install package. Therefore the ONLY thing that should be in this are queries unique to
	* the install/upgrade process. Especially there should be no table definitions unless they are vB3/4 tables not used
	* in vB5.
	*
	**/

	/*Properties====================================================================*/

	//type-specific

	protected $db_type = 'MYSQL';

	protected $table_data = array(
	);

	/** This is the definition for queries.
	 * **/
	protected $query_data = array(
		'updateThreadCounts' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node as node INNER JOIN
			(SELECT node.starter,
			SUM(CASE WHEN node.parentid = node.starter AND (node.showpublished > 0 AND node.showapproved > 0) THEN 1 ELSE 0 END) AS textcount,
			SUM(CASE WHEN node.parentid = node.starter AND (node.showpublished = 0 OR node.showapproved = 0) THEN 1 ELSE 0 END) AS textunpubcount,
			SUM(CASE WHEN node.nodeid != node.starter AND (node.showpublished > 0 AND node.showapproved > 0) THEN 1 ELSE 0 END) AS totalcount,
			SUM(CASE WHEN node.nodeid != node.starter AND (node.showpublished = 0 OR node.showapproved = 0) THEN 1 ELSE 0 END) AS totalunpubcount,
			MAX(node.publishdate) AS lastcontent
			FROM {TABLE_PREFIX}node AS node
			WHERE node.starter between {start} and {end} AND node.contenttypeid NOT IN ({nonTextTypes})
			GROUP BY node.starter) as counts
			ON node.nodeid = counts.starter
			SET node.textcount = counts.textcount, node.textunpubcount = counts.textunpubcount,
			node.totalcount = counts.totalcount, node.totalunpubcount = counts.totalunpubcount,
			node.lastcontent = counts.lastcontent'),
		'updateThreadLast' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS node INNER JOIN {TABLE_PREFIX}node AS lastcontent
			ON lastcontent.starter = node.nodeid AND lastcontent.publishdate = node.lastcontent
			SET node.lastcontentid = lastcontent.nodeid, node.lastcontentauthor = lastcontent.authorname, node.lastauthorid = lastcontent.userid
			WHERE node.starter between {start} and {end}  AND lastcontent.contenttypeid NOT IN ({nonTextTypes})'),
		'getMaxNodeid' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(nodeid) AS maxid FROM {TABLE_PREFIX}node'),
		'getMaxStarter' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(starter) AS maxstarter FROM {TABLE_PREFIX}node'),
		'getNextStarter' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT min(starter) AS next FROM {TABLE_PREFIX}node WHERE starter > {startat}'),
		'getNextChannels' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT nodeid FROM {TABLE_PREFIX}channel WHERE nodeid > {startat} LIMIT {blocksize}'),
		'getMaxChannel' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT max(nodeid) AS maxid FROM {TABLE_PREFIX}channel'),
		'updateChannelCounts' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS channel INNER JOIN
			( SELECT parentid,
			SUM(CASE WHEN showpublished AND showapproved THEN 1 else 0 END) AS textcount,
			SUM(CASE WHEN showpublished AND showapproved THEN 0 else 1 END) AS textunpubcount,
			SUM(totalcount) AS childcount,
			SUM(totalunpubcount) AS childunpub,
			MAX(COALESCE(lastcontent, publishdate, 0)) AS lastcontent
			FROM {TABLE_PREFIX}node
			WHERE parentid IN ({nodeids})
			GROUP BY parentid
			) AS starter ON starter.parentid = channel.nodeid
			set channel.textcount = starter.textcount, channel.textunpubcount = starter.textunpubcount,
			channel.totalcount = starter.childcount + starter.textcount,
			channel.totalunpubcount = starter.childunpub,
			channel.lastcontent = starter.lastcontent'),
		'updateChannelLast' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_UPDATE,
			'query_string' => 'UPDATE {TABLE_PREFIX}node AS channel INNER JOIN {TABLE_PREFIX}node AS starter
			ON starter.parentid = channel.nodeid AND starter.lastcontent = channel.lastcontent
			SET channel.lastcontentid = starter.lastcontentid, channel.lastcontentauthor = starter.lastcontentauthor,
			channel.lastauthorid = starter.lastauthorid
			WHERE channel.nodeid IN ({nodeids})'),
		'rows_affected' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' =>'SELECT ROW_COUNT() AS qty'),
		'getChannelTypes' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT ch.guid, child.nodeid FROM {TABLE_PREFIX}channel AS ch
			INNER JOIN {TABLE_PREFIX}closure AS cl ON cl.parent = ch.nodeid
			INNER JOIN {TABLE_PREFIX}channel AS child ON child.nodeid = cl.child
			WHERE ch.guid IN ({guids}) ORDER BY child.nodeid, cl.depth DESC'),
		'getContentTypes' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT DISTINCT contenttypeid FROM {TABLE_PREFIX}node ORDER BY contenttypeid'),
		'getMissingClosureParents' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT DISTINCT node.nodeid FROM {TABLE_PREFIX}node as node
			INNER JOIN {TABLE_PREFIX}closure AS clp ON clp.child = node.parentid
			LEFT JOIN {TABLE_PREFIX}closure AS cl ON cl.child = node.nodeid AND cl.parent = clp.parent
			WHERE node.nodeid >= {start} AND node.nodeid <= {end} AND cl.child IS NULL'),
		'getMissingClosureSelf' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT DISTINCT node.nodeid FROM {TABLE_PREFIX}node as node
			LEFT JOIN {TABLE_PREFIX}closure AS cl ON cl.child = node.nodeid AND cl.parent = node.nodeid
    	  WHERE node.nodeid >= {start} AND node.nodeid <= {end} AND cl.child IS NULL'),
		'insertMissingClosureSelf' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}closure (parent, child, depth)
			SELECT node.nodeid, node.nodeid, 0
			FROM {TABLE_PREFIX}node AS node LEFT JOIN {TABLE_PREFIX}closure AS cl ON cl.child = node.nodeid AND cl.depth = 0
			WHERE node.nodeid IN ({nodeid}) AND cl.child IS NULL'),
		'insertMissingClosureParent' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_INSERT,
			'query_string' => 'INSERT INTO {TABLE_PREFIX}closure (parent, child, depth)
			SELECT clp.parent, node.nodeid, clp.depth + 1
			FROM {TABLE_PREFIX}node AS node
			INNER JOIN {TABLE_PREFIX}closure AS clp ON clp.child = node.parentid
			LEFT JOIN {TABLE_PREFIX}closure AS cl ON cl.child = node.nodeid AND cl.parent = clp.parent
			WHERE node.nodeid IN ({nodeid}) AND cl.child IS NULL'),
		'getMaxId' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT MAX(nodeid) AS maxid FROM {TABLE_PREFIX}node'),
		'getNextNode' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT min(nodeid) AS nextid FROM {TABLE_PREFIX}node WHERE contenttypeid = {contenttypeid} AND nodeid > {start}'),
		'getCurrentSettings' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT setting.*, sg.adminperm AS groupperm FROM {TABLE_PREFIX}setting AS setting
			LEFT JOIN {TABLE_PREFIX}settinggroup AS sg ON sg.grouptitle = setting.grouptitle
			WHERE varname IN ({varname})'),
		'getCMSChannels' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' =>
				'SELECT node.nodeid, node.htmltitle, node.title, node.parentid, node.showpublished, node.textcount,
					node.displayorder, node.description, closure.depth
				FROM {TABLE_PREFIX}node AS node
				INNER JOIN {TABLE_PREFIX}closure AS closure
					ON closure.child = node.nodeid AND closure.parent = {articleChannelId}
				WHERE node.contenttypeid = {channelcontenttype}
				ORDER BY closure.depth ASC, CASE WHEN node.displayorder < 1 THEN 1 ELSE -1 END DESC, node.displayorder DESC, node.nodeid DESC'
		),
		'getUserActivation' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' => 'SELECT user.userid, user.usergroupid, username, email, activationid, languageid
			FROM {TABLE_PREFIX}user AS user
			LEFT JOIN {TABLE_PREFIX}useractivation AS useractivation ON (user.userid = useractivation.userid AND type = 0)
			WHERE user.userid IN ({userid})'
		),
		'getPagesForSitemap' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' =>
				"SELECT route.prefix, route.routeid, page.guid, page.pageid
				FROM {TABLE_PREFIX}routenew AS route
				INNER JOIN {TABLE_PREFIX}page AS page
					ON page.routeid = route.routeid
				WHERE route.class = 'vB5_Route_Page' AND (name IS NULL OR name NOT IN ({skipped_names}))
				ORDER BY page.pageid"
			),
		'getPagesForSitemapWithLimit' => array(vB_dB_Query::QUERYTYPE_KEY => vB_dB_Query::QUERY_SELECT,
			'query_string' =>
				"SELECT route.prefix, route.routeid, page.guid, page.pageid
				FROM {TABLE_PREFIX}routenew AS route
				INNER JOIN {TABLE_PREFIX}page AS page
					ON page.routeid = route.routeid
				WHERE route.class = 'vB5_Route_Page' AND (name IS NULL OR name NOT IN ({skipped_names}))
					AND page.pageid >= {startat}
				ORDER BY page.pageid
				LIMIT {perpage}"
			),
	);



	/**
	 * Gets the damaged nodeids
	 */
	public function getDamagedNodes($params, $db, $check_only = false)
	{
		if ($check_only)
		{
			return isset($params['start']) AND isset($params['end'])  AND isset($params['contenttypeid']) ;
		}
		else
		{
			$cleaner = vB::getCleaner();
			$params = $cleaner->cleanArray($params, array('start' => vB_Cleaner::TYPE_UINT, 'end' => vB_Cleaner::TYPE_UINT,
				'contenttypeid' => vB_Cleaner::TYPE_UINT));
			$contentLib = vB_Library_Content::getContentLib($params['contenttypeid']);
			$tables = $contentLib->fetchTableName();
			$sql = "SELECT DISTINCT node.nodeid FROM "  . TABLE_PREFIX . "node AS node \n";
			$where = array();
			foreach ($tables AS $table)
			{
				$sql .= "LEFT JOIN "  . TABLE_PREFIX . "$table AS $table ON $table.nodeid = node.nodeid\n";
				$where[] = "$table.nodeid IS NULL\n";
			}
			$sql .= "WHERE (" . implode (' OR ', $where) .")\n AND node.contenttypeid = " . $params['contenttypeid'] .
				" AND node.nodeid >= " . $params['start'] . " AND node.nodeid <=" .
				$params['end'] . "\n/**" . __FUNCTION__ . (defined('THIS_SCRIPT') ? '- ' . THIS_SCRIPT : '') . "**/";
			$resultclass = 'vB_dB_' . $this->db_type . '_result';
			$config = vB::getConfig();
			if (isset($config['Misc']['debug_sql']) AND $config['Misc']['debug_sql'])
			{
				echo "sql: $sql<br />\n";
			}
			$result = new $resultclass($db, $sql);

			return $result;
		}
	}



	/**
	 * Gets CMS Content nodes
	 */
	public function getFullFilteredCMSContentNodeids($params, $db, $check_only = false)
	{
		if ($check_only)
		{
			return (isset($params['channelids']) AND is_array($params['channelids']));
		}
		else
		{
			$cleaner = vB::getCleaner();
			$setParamKeys = array_keys($params);
			$params = $cleaner->cleanArray($params,
				array(
					'channelids' => vB_Cleaner::TYPE_ARRAY_INT,
					// filters
					'contenttypeid' => vB_Cleaner::TYPE_UINT,	// int, can't be zero (empty)
					'authorname' => vB_Cleaner::TYPE_STR,
					'tag' => vB_Cleaner::TYPE_UINT,	// tagid
					'showpublished' => vB_Cleaner::TYPE_UINT,	// int, can be zero
					'public_preview' => vB_Cleaner::TYPE_UINT,	// int, can be zero
					'publishdate' => vB_Cleaner::TYPE_NOCLEAN, // array('op' => str, 'value' => int)
					'viewcount' => vB_Cleaner::TYPE_NOCLEAN, // array('op' => str, 'value' => int), NOT NODE TABLE
					'textcount' => vB_Cleaner::TYPE_NOCLEAN, // array('op' => str, 'value' => int)
				)
			);
			// we don't want unpassed values to be inserted into the params array, so unset them here
			// Otherwise, (!empty($params[$key])) doesn't play nicely with values that can be zeros like showpublished & public_preview
			foreach ($params AS $key => $value)
			{
				if (!in_array($key, $setParamKeys))
				{
					unset($params[$key]);
				}
			}

			// array cleaning
			foreach (array('publishdate', 'views', 'comments') AS $key)
			{
				if (isset($params[$key]) AND is_array($params[$key]) AND isset($params[$key]['op']) AND isset($params[$key]['value']))
				{
					$params[$key] = array(
						'op' => $cleaner->clean($params[$key]['op'],  vB_Cleaner::TYPE_STR),
						'value' => $cleaner->clean($params[$key]['value'],  vB_Cleaner::TYPE_INT),
					);
				}
				else
				{
					unset($params[$key]);
				}
			}


			$filters = array();
			$opMapping = array('eq' => ' = ', 'gt' => ' > ', 'lt' => ' < ');
			// nodefields, equal-to filters
			foreach (array('contenttypeid', 'authorname', 'showpublished', 'public_preview') AS $key)
			{
				if (isset($params[$key]))
				{
					$filters['where'][] = "\tnode." . $key . " = '" . $params[$key] . "'\n";
				}
			}
			// nodefields, selective operation filters
			foreach (array('publishdate', 'textcount') AS $key)
			{
				if (!empty($params[$key]))
				{
					$filters['where'][] = "\tnode." . $key . $opMapping[$params[$key]['op']] . "'" . $params[$key]['value'] . "'\n";
				}
			}
			// tags, equal-to filters
			if (!empty($params['tag']))
			{
				// performance note:
				// there should only be a single row in tagnode with the nodeid & tagid combination,
				// so this *should* not result in duplicate rows, allowing us to not use GROUP BY
				$filters['join'][] = "INNER JOIN " . TABLE_PREFIX . "tagnode AS tagnode_filter \n" .
									"ON tagnode_filter.nodeid = node.nodeid AND tagnode_filter.tagid = '" . $params['tag'] . "'\n";
			}
			// viewcount, condition on nodeview table
			if (!empty($params['viewcount']))
			{
				//$filters['join'][] = "LEFT JOIN " . TABLE_PREFIX . "nodeview AS nodeview \n" .
				//					"ON nodeview.nodeid = node.nodeid \n";
				$filters['where'][] = "\t IFNULL(nodeview.count, 0)" . $opMapping[$params['viewcount']['op']] . "'" . $params['viewcount']['value'] . "'\n";
			}

			// glue together
			if (!empty($filters['where']))
			{
				$filters['where'] = "AND\t" . implode("AND\t", $filters['where']);
			}
			else
			{
				$filters['where'] = '';
			}
			if (!empty($filters['join']))
			{
				$filters['join'] = implode($filters['join']);
			}
			else
			{
				$filters['join'] = '';
			}

			// exclude channels, we want content only
			$channeltypeid = vB_Api::instanceInternal('ContentType')->fetchContentTypeIdFromClass('Channel');

			// build up SQL
			$sql = "SELECT node.nodeid, node.taglist, node.publishdate, \n" .
					"node.title, node.htmltitle, node.parentid, node.showpublished, node.public_preview, \n" .
					"node.authorname, node.userid, node.displayorder, node.textcount, \n" .
					"node.routeid, node.contenttypeid, IFNULL(nodeview.count, 0) AS viewcount \n" .
					"FROM "  . TABLE_PREFIX . "node AS node \n" .
					"LEFT JOIN " . TABLE_PREFIX . "nodeview AS nodeview \n" .
									"ON nodeview.nodeid = node.nodeid \n";
			$sql .= $filters['join'];


			$sql .= "WHERE node.parentid IN (" . implode(',', $params['channelids']) . ") AND node.contenttypeid <> " . $channeltypeid . " AND node.nodeid = node.starter \n" .
				$filters['where'];

			// The CASE in the ORDER BY code is necessary to force displayorder=0 to the end of the list.
			$sql .= "ORDER BY CASE WHEN  node.displayorder < 1 THEN 1 ELSE -1 END ASC, node.displayorder ASC \n" .
						""; //	"LIMIT " . ($params['perpage'] * ($params['page'] - 1)) . ", " . $params['perpage'] . "\n";

			$sql .= "\n/**" . __FUNCTION__ . (defined('THIS_SCRIPT') ? '- ' . THIS_SCRIPT : '') . "**/";

			$resultclass = 'vB_dB_' . $this->db_type . '_result';
			$config = vB::getConfig();

			if (isset($config['Misc']['debug_sql']) AND $config['Misc']['debug_sql'])
			{
				echo "sql: $sql<br />\n";
			}

			$result = new $resultclass($db, $sql);

			return $result;
		}
	}

	/*
	 *	Grab all starters visible for guests.
	 *	Unfortunately, I don't think the clauses returned by getNodePermTerms gives me a way to
	 *	grab channels as well... So at the moment, vB_SiteMap_Node->generate_sitemap() fetches the
	 *	channels separately
	*/
	public function getGuestVisibleNodes($params, $db, $check_only = false)
	{
		if ($check_only)
		{
			return (isset($params['startat']) AND isset($params['perpage']));
		}
		else
		{
			$cleaner = vB::getCleaner();
			$params = $cleaner->cleanArray(
				$params,
				array('startat' => vB_Cleaner::TYPE_UINT, 'perpage' => vB_Cleaner::TYPE_UINT)
			);
			$permflags = $this->getNodePermTerms(false, 0);

			// exclude channels, we want content only
			$channeltypeid = vB_Api::instanceInternal('ContentType')->fetchContentTypeIdFromClass('Channel');
			$pmid = vB_Api::instanceInternal('ContentType')->fetchContentTypeIdFromClass('PrivateMessage');

			// build up SQL
			$sql = "SELECT node.routeid, node.nodeid, node.lastcontent, node.parentid, node.urlident, \n" .
					"\troutenew.prefix, routenew.arguments \n" .
				"FROM " . TABLE_PREFIX . "node AS node \n" .
				"INNER JOIN " . TABLE_PREFIX . "node AS starter ON starter.nodeid = node.starter AND starter.nodeid = node.nodeid \n" .	// I forget, why is this join needed? It's probably due to getNodePermTerms, but let's double check.
				"INNER JOIN " . TABLE_PREFIX . "routenew AS routenew ON routenew.routeid = node.routeid \n" .
				"WHERE node.contenttypeid <> $pmid \n" .
				$permflags['where'] .
				"AND node.nodeid >= ".$params['startat'] . " LIMIT " .$params['perpage'] ." \n";

			$sql .= "\n/**" . __FUNCTION__ . (defined('THIS_SCRIPT') ? '- ' . THIS_SCRIPT : '') . "**/";

			$resultclass = 'vB_dB_' . $this->db_type . '_result';
			$config = vB::getConfig();

			if (isset($config['Misc']['debug_sql']) AND $config['Misc']['debug_sql'])
			{
				echo "sql: $sql<br />\n";
			}

			$result = new $resultclass($db, $sql);

			return $result;
		}
	}
}
