<?php
/* File Name: suggest.class.php
 * 	dbLink Plugin.
 *      for phpnuke CMS and equivalents
 *        Select URL using title of contents from tables pages, stories, FAQ, Encyclopia, Download, WebLink,  Ephemerids
 * File Authors:
 * 		Gustavo G. Vilchez B. (ggvilchez@gmail.com)
 * */

//Cause browser to reload page every time
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT"); // Date in the past

// load error handling module
//require_once('error_handler.php');
// load configuration file
 $PHP_SELF=$HTTP_SERVER_VARS['PHP_SELF'];
require_once('config.php');
// require_once("../../../../includes/sql_layer.php");
// defines database connection data



// class supports server-side suggest & autocomplete functionality
class Suggest
{
  // database handler   private $mMysqli;
  var $msql;
  

 function getSuggestions($keyword)
   {

    //get DB
	if(defined('_JEXEC'))
	{
    	$dbi =  &  JDatabase::getInstance(array('driver'=>DB_DRIVER,'host'=>DB_HOST,'user'=>DB_USER,'password'=>DB_PASSWORD,
								'database'=>DB_DATABASE,'prefix'=>DB_PREFIX));
	}
	else
	{
		$dbi =	new database( DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE, DB_PREFIX, DB_OFFLINE );
	}
    // escape the keyword string     
    
    if (get_magic_quotes_gpc())  //Addded by AW
    $keyword  = stripslashes($keyword);
    $keyword = addcslashes(mysql_real_escape_string($keyword), "%_");

     
    //$patterns = array('/\s+/', '/"+/', '/%+/');
    //$replace = array('');
    //$keyword = preg_replace($patterns, $replace, $keyword);

        // build the SQL query that gets the matching functions from the database
	

    $tit ="title";
	$id  ="id";
	$link = "link";
	
    // execute the SQL query
   

  	$output  = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
  	$output .= '<response>';  
	
	$filter =  '=""';
	 
	if($keyword != '')
    		  $filter = ' LIKE "' . $keyword . '%"';
    
	
		
	// if the keyword is empty build a SQL query that will return no results
    
	$query =   '';
	
	
	if(defined('_JEXEC'))
	{
	
		$query = 
			'SELECT ' . $tit . ',' . $link . ',' . $id . '
			 FROM
			(SELECT c.'. $tit .' ,m.' . $link . ',m.id,c.created
			FROM #__content c
			JOIN
			(
			 SELECT  ' . $link . ',' . $id . ' 
			 FROM #__menu
			 WHERE ' . $link . ' like "index.php?option=com_content&view=article&id=%"
			 AND published = 1
			
			) as m on m.' . $link . ' = concat("index.php?option=com_content&view=article&id=",c.' . $id . ')
			WHERE  c.' . $tit . $filter . '
					
			UNION 
			
			SELECT c.'. $tit .' ,concat("index.php?option=com_content&amp;view=article&amp;id=",cast(c.' . $id . ' as char(11))) as link ,0 as id,c.created
			FROM #__content c
			LEFT JOIN
			(
			 SELECT  ' . $link . ',' . $id . ' 
			 FROM #__menu
			 WHERE ' . $link . ' like "index.php?option=com_content&view=article&id=%"
			 AND published = 1
			
			) as m on m.' . $link . ' = concat("index.php?option=com_content&view=article&id=",c.' . $id . ')
			WHERE  c.' . $tit . $filter . '
			AND m.' . $id  . ' is null
			AND c.sectionid = 0
			AND c.catid = 0
			AND c.state = 1
			
			UNION 
		
			SELECT i.' . $tit . ' as title,concat("index.php?option=com_content&amp;view=article&amp;id=",cast(i.' . $id . ' as char(11)))
			 as link, IFNULL(ms.' . $id . ' ,mc.' . $id . ' ) as id,i.created
			 FROM #__content AS i
			 JOIN #__sections AS s ON i.sectionid = s.' . $id . '
			 LEFT JOIN #__menu AS ms ON 
			 ms.' . $link . ' = concat("index.php?option=com_content&view=section&layout=blog&id=",s.' . $id . ')
			OR ms.' . $link . ' =  concat("index.php?option=com_content&view=section&id=",s.' . $id . ')
			 JOIN #__categories AS c ON i.catid = c.' . $id . '
			 LEFT JOIN #__menu AS mc ON
			 mc.' . $link . ' = concat("index.php?option=com_content&view=category&layout=blog&id=",c.' . $id . ')
			OR mc.' . $link . ' =  concat("index.php?option=com_content&view=category&id=",c.' . $id . ')
			 
			 WHERE (ms.id is not null or mc.id is not null)
			AND (ms.published = 1 or mc.published=1)
			 AND i.' . $tit . $filter . '
					 
			UNION   
			
			select i.' . $tit .',concat("index.php?option=com_content&amp;view=article&amp;id=",cast(i.' . $id . ' as char(11))) as link, 0 as id,i.created
			 FROM #__content i
			 LEFT join #__menu  m on m.link = concat("index.php?option=com_content&view=article&id=",i.' . $id . ')
			 JOIN #__sections AS s ON i.sectionid = s.' . $id .'
			 LEFT JOIN #__menu AS ms ON 
			 ms.' . $link . ' = concat("index.php?option=com_content&view=section&layout=blog&id=",s.' . $id . ')
				OR ms.' . $link . ' =  concat("index.php?option=com_content&view=section&id=",s.' . $id . ')
			 JOIN #__categories AS c ON i.catid = c.' . $id . '
			 LEFT JOIN #__menu AS mc ON
			 mc.' . $link . ' = concat("index.php?option=com_content&view=category&layout=blog&id=",c.' . $id . ')
			 OR mc.' . $link . ' =  concat("index.php?option=com_content&view=category&id=",c.' . $id . ')
			 
			 WHERE m.' . $id  . ' is null
			 AND ms.' . $id . ' is null 
			 AND mc.' . $id . ' is null
			 AND state = 1
			 AND i.' . $tit . $filter . '
			 
			UNION
			 
			SELECT IFNULL(s.' . $tit . ',c.' . $tit . '),m.link,m.id,"0000-00-00 00:00:00" as created
			 FROM #__menu AS m 
			 LEFT JOIN #__sections AS s ON
			 m.' . $link . ' = concat("index.php?option=com_content&view=section&layout=blog&id=",s.' . $id . ')
				OR m.' . $link . ' =  concat("index.php?option=com_content&view=section&id=",s.' . $id . ')
		 
			 LEFT JOIN #__categories AS c ON
			 m.' . $link . ' = concat("index.php?option=com_content&view=category&layout=blog&id=",c.' . $id . ')
			OR m.' . $link . ' =  concat("iindex.php?option=com_content&view=category&id=",c.' . $id . ')
					 
			 WHERE (s.id is not null or c.id is not null)
			AND m.published = 1
			AND IFNULL(s.' . $tit . ',c.' . $tit . ') ' . $filter . '
		 
			 UNION 
		 
		 
			 SELECT s.' . $tit . ',concat("index.php?option=com_content&amp;view=section&amp;id=",cast(s.' . $id . ' as char(11))) as link,0 as id,"0000-00-00 00:00:00" as created
			 FROM #__sections AS s 
			 LEFT JOIN #__menu AS m ON
			 m.' . $link . ' = concat("index.php?option=com_content&view=section&layout=blog&id=",s.' . $id . ')
			 OR m.' . $link . ' =  concat("index.php?option=com_content&view=section&id=",s.' . $id . ')
		 
				WHERE m.id is null
			   AND s.published = 1
			   AND s.' . $tit . $filter . '
		 
			   UNION
		 
			SELECT c.title,concat("index.php?option=com_content&amp;view=category&amp;id=",cast(c.' . $id . ' as char(11))) as link,0 as id,"0000-00-00 00:00:00" as created
			 FROM #__categories AS c 
			 LEFT JOIN #__menu AS m ON
			 m.' . $link . ' = concat("index.php?option=com_content&view=category&layout=blog&id=",c.' . $id . ')
			OR m.' . $link . ' =  concat("index.php?option=com_content&view=category&id=",c.' . $id . ')
					 
			WHERE m.id is null
				AND c.published = 1
			AND c.section REGEXP "^[0-9]+$"
			AND c.' . $tit . $filter . '
	
			 ORDER BY created desc) a
			 WHERE ' . $tit . $filter;
	}
	else
	{
	
		$query = 
	    'SELECT ' . $tit . ',' . $link . ',' . $id . '
		 FROM
		(SELECT c.'. $tit .' ,m.' . $link . ',m.id,c.created
		FROM #__content c
		JOIN
		(
		 SELECT componentid,' . $link . ',' . $id . ' 
		 FROM #__menu
		 WHERE type in ("content_item_link","content_typed")
		 AND published = 1
		
		) as m on m.' . $link . ' = concat("index.php?option=com_content&task=view&id=",c.' . $id . ')
		WHERE  c.' . $tit . $filter . '
		
		union
		
		SELECT c.'. $tit .' ,concat("index.php?option=com_content&amp;task=view&amp;id=",cast(c.' . $id . ' as char(11))) as link ,0 as id,c.created
		FROM #__content c
		LEFT JOIN
		(
		 SELECT  ' . $link . ',' . $id . ' 
		 FROM #__menu
		 WHERE type in ("content_item_link","content_typed")
		 AND published = 1
		
		) as m on m.' . $link . ' = concat("index.php?option=com_content&task=view&id=",c.' . $id . ')
		WHERE  m.' . $id  . ' is null
		AND c.sectionid = 0
		AND c.catid = 0
		AND	c.' . $tit . $filter . '
		
		union
		
		SELECT i.' . $tit . ' as title,concat("index.php?option=com_content&amp;task=view&amp;id=",cast(i.' . $id . ' as char(11)))
		 as link, IFNULL(ms.' . $id . ' ,mc.' . $id . ' ) as id,i.created
		 FROM #__content i
		 LEFT JOIN #__sections AS s ON i.sectionid = s.' . $id . '
		 LEFT JOIN #__menu AS ms ON ms.componentid = s.' . $id . '
		 LEFT JOIN #__categories AS c ON i.catid = c.' . $id . '
		 LEFT JOIN #__menu AS mc ON mc.componentid = c.' . $id . '
		 WHERE ( ms.type IN ( "content_section", "content_blog_section" ) OR mc.type IN ( "content_blog_category", "content_category" ) )
		 AND ms.published = 1 or mc.published=1
		 AND  i.' . $tit . $filter . '
		
		union
		
		select uc.' . $tit . ',concat("index.php?option=com_content&amp;task=view&amp;id=",cast(uc.' . $id . ' as char(11))) as link,um.' . $id . ',uc.created
		FROM #__content uc
		JOIN #__menu um on um.componentid = uc.' . $id . '
		WHERE type = "url"
		AND um.published = 1
		AND ' . $link . ' LIKE "index.php?%"
		AND  uc.' . $tit . $filter . '
		
		union
		
		select i.' . $tit .',concat("index.php?option=com_content&amp;task=view&amp;id=",cast(i.' . $id . ' as char(11))) as link, 0 as id,i.created
		from #__content i
		LEFT join #__menu  m on m.link = concat("index.php?option=com_content&task=view&id=",i.' . $id . ')
		LEFT join #__menu um on um.componentid = i.' . $id . '
		LEFT JOIN #__sections AS s ON i.sectionid = s.' . $id .'
		LEFT JOIN #__menu AS ms ON ms.componentid = s.' . $id . '
		AND ms.type IN ( "content_section", "content_blog_section" )
		LEFT JOIN #__categories AS c ON i.catid = c.' . $id . '
		LEFT JOIN #__menu AS mc ON mc.componentid = c.' . $id . '
		AND mc.type IN ( "content_blog_category", "content_category" )
		WHERE m.' . $id  . ' is null
		AND um.' . $id . ' is null
		AND ms.' . $id . ' is null and mc.id is null
		AND state = 1
		AND  i.' . $tit . $filter . '
		 
		union
		 
		SELECT IFNULL(s.' . $tit . ',c.' . $tit . '),m.link,m.id,"0000-00-00 00:00:00" as created
		FROM #__menu AS m 
		LEFT JOIN #__sections AS s ON m.componentid = s.' . $id . '
		LEFT JOIN #__categories AS c ON m.componentid = c.' . $id . '
	 	WHERE m.type IN ( "content_section", "content_blog_section" ,"content_blog_category", "content_category" ) 
     	AND m.published = 1
     	AND IFNULL(s.' . $tit . ',c.' . $tit . ') ' . $filter . '
	    
	    union
	    
	
	    SELECT s.' . $tit . ',concat("index.php?option=com_content&amp;view=section&amp;id=",cast(s.' . $id . ' as char(11))) as link,0 as id,"0000-00-00 00:00:00" as created
		FROM #__sections AS s 
		LEFT JOIN #__menu AS m ON m.componentid = s.' . $id . '
		AND m.type IN ( "content_section", "content_blog_section" )
    	WHERE m.id is null
    	AND s.published = 1
     	AND s.' . $tit . $filter . '
     
    	union
     
    	SELECT c.title,concat("index.php?option=com_content&amp;view=category&amp;id=",cast(c.' . $id . ' as char(11))) as link,0 as id,"0000-00-00 00:00:00" as created
		FROM #__categories AS c 
		LEFT JOIN #__menu AS m ON m.componentid = c.' . $id . '
		AND m.type IN ( "content_blog_category", "content_category" )
		WHERE m.id is null
     	AND c.published = 1
		AND c.section REGEXP "^[0-9]+$"
     	AND c.' . $tit . $filter . '
		ORDER BY created desc) a
		WHERE ' . $tit . $filter;
	
	}
    	
	// execute the SQL query
        
  	 //get DB intstance

     
		 $dbi->setQuery($query);
		 $rows = $dbi->loadAssocList();
	
		 
	    
   	 // if we have results, loop through them and add them to the output
   	 if($rows)
    	foreach ($rows as $row) 	
		{      
      		//$output .= '<name>' . '<![CDATA[' . htmlentities($row[$tit], ENT_QUOTES) . ']]>' . '</name>';
			$output .= '<name>' . '<![CDATA[' . $row[$tit] . ']]>' . '</name>';
			$output .= '<pid>' . $row[$id]  . '</pid>';
			$output .= '<link>' . '<![CDATA[' . $row[$link] .']]>' . '</link>';
		}
         		
		 // add debug information
		// $output .= '<query>' . 	 $query . '</query>';
		 $output .= '<error>' . 	$dbi->getErrorMsg() . '</error>';
	
	// add the final closing tag	 
    $output .= '</response>';  
   	 // return the results
    return $output;  
  }
//end class Suggest
}
?>
