<?php
/**
 * TOracleCommandBuilder class file.
 *
 * @author Marcos Nobre <marconobre[at]gmail[dot]com>
 * @link http://www.pradosoft.com/
 * @copyright Copyright &copy; 2005-2007 PradoSoft
 * @license http://www.pradosoft.com/license/
 * @version $Id$
 * @package System.Data.Common
 */

Prado::using('System.Data.Common.TDbCommandBuilder');

/**
 * TOracleCommandBuilder provides specifics methods to create limit/offset query commands
 * for Oracle database.
 *
 * @author Marcos Nobre <marconobre[at]gmail[dot]com>
 * @version $Id$
 * @package System.Data.Common
 * @since 3.1
 */
class TOracleCommandBuilder extends TDbCommandBuilder
{

	/**
	 * Overrides parent implementation. Only column of type text or character (and its variants)
	 * accepts the LIKE criteria.
	 * @param array list of column id for potential search condition.
	 * @param string string of keywords
	 * @return string SQL search condition matching on a set of columns.
	 */
	public function getSearchExpression($fields, $keywords)
	{
		$columns = array();
		foreach($fields as $field)
		{
			if($this->isSearchableColumn($this->getTableInfo()->getColumn($field)))
				$columns[] = $field;
		}
		return parent::getSearchExpression($columns, $keywords);
	}
	/**
	 *
	 * @return boolean true if column can be used for LIKE searching.
	 */
	protected function isSearchableColumn($column)
	{
		$type = strtolower($column->getDbType());
		return $type === 'character varying' || $type === 'varchar2' ||
				$type === 'character' || $type === 'char' || $type === 'text';
	}

	/**
	 * Overrides parent implementation to use PostgreSQL's ILIKE instead of LIKE (case-sensitive).
	 * @param string column name.
	 * @param array keywords
	 * @return string search condition for all words in one column.
	 */
	 /*
	 *
	 *	how Oracle don't implements ILIKE, this method won't be overrided
	 *
	protected function getSearchCondition($column, $words)
	{
		$conditions=array();
		foreach($words as $word)
			$conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%');
		return '('.implode(' AND ', $conditions).')';
	}
	*/


	/**
	 * Overrides parent implementation to use Oracle way of get paginated RecordSet instead of using LIMIT sql clause.
	 * @param string SQL query string.
	 * @param integer maximum number of rows, -1 to ignore limit.
	 * @param integer row offset, -1 to ignore offset.
	 * @return string SQL with limit and offset in Oracle way.
	 */
	public function applyLimitOffset($sql, $limit=-1, $offset=-1)
	{
		if( (int)$limit <= 0 && (int)$offset <= 0 )
			return $sql;
	
		$pradoNUMLIN = 'pradoNUMLIN';
		$fieldsALIAS = 'xyz';

		$nfimDaSQL	 = strlen($sql);
		$nfimDoWhere = ( strpos($sql,'ORDER') !== false ? strpos($sql,'ORDER') : $nfimDaSQL );
		$niniDoSelect= strpos($sql,'SELECT')+6;
		$nfimDoSelect= ( strpos($sql,'FROM') !== false ? strpos($sql,'FROM') : $nfimDaSQL );

		$sORDERBY	 = '';
		if( stripos($sql,'ORDER') !== false ) {
			$p = stripos($sql,'ORDER');
			$sORDERBY = substr( $sql, $p+8, 10000 );
		}

		$fields		 	= substr( $sql, 0, $nfimDoSelect );
		$fields		 	= trim( substr( $fields, $niniDoSelect ) );
		$aliasedFields	= ', ';

		if( trim($fields) == '*' ) {
			$aliasedFields	= ", {$fieldsALIAS}.{$fields}";
			$fields			= '';
			$arr 			= $this->getTableInfo()->getColumns();
			foreach( $arr as $field )
			{
				$fields .= strtolower( $field->getColumnName() ).', ';
			}
			$fields = str_replace( '"', '', $fields );
			$fields = trim($fields);
			$fields = substr( $fields, 0, strlen($fields)-1 );
		} else {
			if( strpos( $fields, ',' ) !== false )
			{
				foreach( $arr as $field )
				{
					$field = strtolower( $field );
					$existAS = str_ireplace( ' as ', '-as-', $field );
					if( strpos( $existAS, '-as-' ) === false )
						$aliasedFields .= "{$fieldsALIAS}.".trim($field).", ";
					else
						$aliasedFields .= "{$field}, ";
				}
				$aliasedFields = trim($aliasedFields);
				$aliasedFields = substr( $aliasedFields, 0, strlen($aliasedFields)-1 );
			}
		}

		/* ************************
		$newSql = " SELECT $fields FROM ".
				  "(					".
				  "		SELECT rownum as {$pradoNUMLIN} {$aliasedFields} FROM ".
				  " ($sql) {$fieldsALIAS} WHERE rownum <= {$limit} ".
				  ") WHERE {$pradoNUMLIN} >= {$offset} ";

		************************* */
		$toReg = $offset + $limit;
		$fullTableName = $this->getTableInfo()->getTableFullName();
		if( empty($sORDERBY) )
		{
			$newSql = " SELECT $fields FROM " .
					  "(					" .
					  "		SELECT ROW_NUMBER() OVER ( ORDER BY ROWNUM ) as {$pradoNUMLIN} {$aliasedFields} " .
					  "		FROM {$fullTableName} {$fieldsALIAS}" .
					  ") nn					" .
					  " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} " ;
		} else {
			$newSql = " SELECT $fields FROM " .
					  "(					" .
					  "		SELECT ROW_NUMBER() OVER ( ORDER BY {$sORDERBY} ) as {$pradoNUMLIN} {$aliasedFields} " .
					  "		FROM {$fullTableName} {$fieldsALIAS}" .
					  ") nn					" .
					  " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} " ;
		}
		return $newSql;
	}


}

?>