<?php
/*
 * JsonSQL
 * By: Trent Richardson [http://trentrichardson.com]
 * Version 0.1
 * Last Modified: 1/1/2008
 * 
 * Copyright 2008 Trent Richardson
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

/**
 * PHP port of JsonSQL
 * 
 * @author Harry Roberts
 */
class PhpSql
{
	protected $fields;
	protected $from;
	protected $where = 'true';
	protected $orderby = array();
	protected $order = 'asc';
	protected $limit = array();
	protected $result = array();
	public $data = array();
	
	public static function query( $sql, array $data )
	{
		$sql = new self( $sql, $data );
		return $sql->result;
	}
	
	public function __construct( $sql, array $data )
	{
		assert( ! empty($sql) );
		
		$rs = preg_match('/^(select)\s+([a-z0-9_\,\.\s\*]+)\s+from\s+([a-z0-9_\.]+)(?: where\s+\((.+)\))?\s*(?:order\sby\s+([a-z0-9_\,]+))?\s*(asc|desc|ascnum|descnum)?\s*(?:limit\s+([0-9_\,]+))?/i', $sql, $returnfields);
		if( $rs == FALSE )
		{
			throw new Exception( "Unable to match SQL statement" );
		}
		
	 	$this->fields = explode(',',str_replace(' ','',$returnfields[2]));
		$this->from = str_replace(' ', '', $returnfields[3]);		
		$this->where = ( ! isset($returnfields[4]) ) ? "true" : $returnfields[4];
		$this->orderby = ( ! isset($returnfields[5]) ) ? array() : explode(',',str_replace(' ', '', $returnfields[5]));
		$this->order = ( ! isset($returnfields[6]) ) ? 'asc' : $returnfields[6];
		$this->limit = ( ! isset($returnfields[7]) ) ? array() : explode(',',str_replace(' ', '', $returnfields[7]));
		
		$this->result = array();
		$this->data = $data; 
		
		$this->returnFilter( );
		$this->returnOrderBy( );
		$this->returnLimit( );
	}
	
	protected function returnFilter( )
	{
		if( empty($this->where) )
		{
			$this->where = 'true';
		}
				
		foreach( $this->data AS $__ROWKEY => $__ROWDATA )
		{
			extract( $__ROWDATA, EXTR_OVERWRITE );
			
			// Ewww - horible port directly from JavaScript!
			eval( '$__ROWSTATUS = (' . $this->where . ');' );
			if( $__ROWSTATUS )
			{
				$this->result[] = $this->returnFields($__ROWDATA);
			}
		}
	}
	
	protected function returnFields( array $scope )
	{
		if( ! count($this->fields) OR $this->fields[0] == '*' )
		{
			return $scope;
		}
		
		$returnobj = array();
		
		foreach( $this->fields AS $field_name )
		{
			$returnobj[$field_name] = $scope[$field_name];
		}
		
		return $returnobj;
	}
	
	protected function sortCallback( $a, $b )
	{
		switch( strtolower($this->order) )
		{
		case 'desc':
			return $a[$this->orderby[0]] < $b[$this->orderby[0]] ? 1 : -1;
			
		case 'descnum':
			return $a[$this->orderby[0]] - $b[$this->orderby[0]];
			
		case 'ascnum':
			return $b[$this->orderby[0]] - $a[$this->orderby[0]];
				
		case 'asc':
		default:
			return $a[$this->orderby[0]] > $b[$this->orderby[0]] ? 1 : -1;
		}
	}
	
	protected function returnOrderBy( )
	{
		usort( $this->result, array(&$this,'sortCallback') );
	}
	
	protected function returnLimit( )
	{
		switch( count($this->limit) )
		{
		case 1:
			$this->result = array_slice($this->result, 0, $this->limit[0], TRUE); 
			break;
			
		case 2:
			$this->result = array_slice($this->result, $this->limit[0] - 1, $this->limit[1], TRUE); 
			break;
		}
	}
}

$testdata = array(
	array('username' => 'hello123', 'id' => 1),
	
	array('username' => 'harry', 'id' => 5),
	array('username' => 'test', 'id' => 6),
	array('username' => 'blah', 'id' => 7),
	array('username' => 'whatever', 'id' => 8),
	
	array('username' => 'hello123', 'id' => 20),
);

print_r( PhpSql::query( 'SELECT * FROM data WHERE ($id > 5) ORDER BY id DESC LIMIT 5', $testdata ) );