Location: PHPKode > projects > FreeKore > freekore.v0.2/app/PLUGINS/FkDataTable/FkDataTable.class.php
<?php
/****************************************************************************************
 **  fkControl : FkDataTable
 **  Version   : 0.0.1
 **  Author    : Mmendoza000 (Sep-2010)
 **  Licence   : GPL
 **
 **  Descripcion: Control Grid  para FreeKore
 **  proyecto    fecha      por         descripcion
 **  ----------  ---------  ----------- ----------------
 **  00000001    22/09/10   mmendoza    Creado.
 *****************************************************************************************/

class FkDataTable
{

	private $sLimit = ""; # Paging
	private $sOrder = ""; # Orderinf
	private $sWhere	= ""; # Where
	private $sQuery = ""; # Query
	private $sql_query    = ""; # sql

	private $sql_fields = '';
	private $sql_table = '';
	private $sql_where = '';
	private $sql_order = '';
	private $arr_col_db = array();	// Array de columnas de la tabla
	private $special_cols = array(); // Para columnas especiales
	private $arr_columns = array();	// Array final de colunmas

	private $Action='';
	private $CurrentPage='';

	public function __construct(){


		if(@$_GET['ajax']=='1'){
			$Act = 'show-json';
			fk_no_display_header();
		}else{
			$Act = 'show-table';
		}
		$this->Action = $Act;


	}

	public function Render($GetCurrentPage){

		$this->CurrentPage = $GetCurrentPage;

		$result = '';

		switch ($this->Action) {
			case 'show-json':
				// Show Json data
				$result = $this-> generateJSON();
					
				break;
			case 'show-table':
				$result = $this-> showTable();
				break;
		}

		echo $result;

	} // render()
	private function generateJSON(){
		// no mostrar errores como warnings, ya que afecta el resultado y marca error en {json}
		ini_set('display_errors', 0);
			
		// MySQL connection
		$db = new	db();
		$db->connect();
		// Get Columns
		$this->get_columns();
		//Limit
		$this->setLimit();

		//Ordering
		$this->setOrder();

		//Filtering
		$this->setFilter();

		$this->sQuery = "SELECT SQL_CALC_FOUND_ROWS ".$this->sql_fields."
			             FROM   ".$this->sql_table." ".$this->sWhere." ".$this->sOrder." ".$this->sLimit." ;";
			
		$db->query( $this->sQuery );

		$out_ini='';
		$out_regs='';
		$out_fin='';
		#--------------------------------------
		# REGISTROS
		#--------------------------------------

		while ( $aRow = $db->next() )
		{
			$out_regs .= "[";
			foreach($this->arr_columns as $k=>$col){

				if(isset($col['type']) && @$col['type']=='special'){
					$out_regs .= '"'.addslashes($this->procesa_columnas_esp($col,$aRow)).'",';
				}else{
					$out_regs .= '"'.addslashes($aRow[$col]).'",';
				}

			}
			$out_regs = substr_replace( $out_regs, "", -1 );

			$out_regs .= "],";
		}
		$out_regs = substr_replace( $out_regs, "", -1 );


		#--------------------------------------
		# TOTALES
		#--------------------------------------

		$this->sQuery = "SELECT FOUND_ROWS()";

		$db->query( $this->sQuery );
		$aResultFilterTotal = $db->next();
		$iFilteredTotal = $aResultFilterTotal[0];

		$this->sQuery = "
			SELECT COUNT(*)
			FROM   ".$this->sql_table."
		";

		$db->query( $this->sQuery );
		$aResultTotal = $db->next();
		$iTotal = $aResultTotal[0];



		$out_ini .= '{';
		$out_ini .= '"sEcho": '.intval(@$_GET['sEcho']).', ';
		$out_ini .= '"iTotalRecords": '.$iTotal.', ';
		$out_ini .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
		$out_ini .= '"aaData": [ ';

		#--------------------------------------
		# Cerrar cadena output
		#--------------------------------------
		$out_fin .= '] }';


		#--------------------------------------
		# FORTAMEAR output
		#--------------------------------------

		$sOutput = $out_ini . $out_regs . $out_fin;

		return $sOutput;

	}

	private function showTable(){
		// Obtener columnas
		$this->get_columns();
		 
		//pa($this-> arr_columns);
		
				 
		?>
<table cellpadding="0" cellspacing="0" border="0" class="display"
	id="videosGrid">
	<thead>
		<tr>
		<?php 
		foreach($this->arr_columns as $k=>$col){
			if(isset($col['type']) && @$col['type']=='special'){
				?><th></th><?php 
			}else{
				?><th><?php echo $col;?></th><?php
			}
		}
		
		?>
		
		</tr>
	</thead>
	<tbody>
		<tr>
			<td colspan="4" class="dataTables_empty">Loading data from server</td>
		</tr>
	</tbody>
</table>
<script language="javascript" type="text/javascript">
$(document).ready(function() {	
	
	

	$('#videosGrid').dataTable({
		    "bJQueryUI": true,
			"bProcessing": true,
			"bServerSide": true,
			"sAjaxSource": HTTP+"<?php echo $this->CurrentPage;?>?",
			"sPaginationType": "full_numbers",
			"aaSorting": [[ 0, "desc" ]],
			"iDisplayLength": 10,
			"fnServerData": function ( sSource, aoData, fnCallback ) {
				/* Add some extra data to the sender */
				aoData.push( { "name": "ajax", "value": "1" } );
				$.getJSON( sSource, aoData, function (json) { 
					/* Do whatever additional processing you want on the callback, then tell DataTables */
					fnCallback(json);
				} );
			}
		} );
   
} );
</script>
		<?php
		 
		 
	}
	# sql source
	public function sql_source($sql){

		$this->sql_query = $sql;
		$this->set_query_estructure();



	} // sqlSource

	private function set_query_estructure(){

		$sql =  trim( strtolower($this->sql_query));

		$sql_fields_table = explode('from',$sql);
		$sql_fields_1 = trim($sql_fields_table[0]);
		$sql_fields = trim($sql_fields_1,'select');
		$sql_fields = trim($sql_fields);
		$sql_table = trim($sql_fields_table[1]);
		$sql_table_where = explode("where",$sql_table);

		$sql_table = $sql_table_where[0];

		//prev where
		$sql_prev_where = ( isset($sql_table_where[1]) ? $sql_table_where[1] : '' );

		$sql_order = '';
		$sql_where = '';

		if(trim($sql_prev_where)!=''){
			$sql_order_1 = explode("order by",$sql_prev_where);

			if(isset($sql_order_1[1])){
				$sql_order = 'order by '.$sql_order_1[1] ;
				$sql_where = 'where '.$sql_order_1[0] ;
			}

		}

		if(trim( $sql_where ) == ''){
			$sql_where = ( isset($sql_table_where[1]) ? 'where '.$sql_table_where[1] : '' );
		}


		// FIELDS
		$this->sql_fields = $sql_fields;

		// TABLE
		$this->sql_table = $sql_table;

		//WHERE
		$this->sql_where = $sql_where;

		//ORDER
		$this->sql_order = $sql_order;


	} // set_query_estructure(){
	#SET LIMIT
	private function setLimit(){
		if ( isset( $_GET['iDisplayStart'] ) )
		{
			$this->sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
			mysql_real_escape_string( $_GET['iDisplayLength'] );
		}
	}

	#SET ORDER
	private function setOrder(){

		if ( isset( $_GET['iSortCol_0'] ) )
		{
			// PETICION DE ORDER
			$this->sOrder = "order by ";
			for ( $i=0 ; $i<mysql_real_escape_string( $_GET['iSortingCols'] ) ; $i++ )
			{
				$this->sOrder .= $this->fnColumnToField(mysql_real_escape_string( $_GET['iSortCol_'.$i] ))."
					".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
			}
			$this->sOrder = substr_replace( $this->sOrder, "", -2 );
		}else{
			//  ORDER	por default
			if($this->sql_order != '' ){
				$this->sOrder = $this->sql_order;
			}
		}
	}

	#Filtering
	private function setFilter(){

		if(trim($this->sql_where) != ''){
			$this->sWhere = $this->sql_where.' ';
			$W_A = ' and ';
		}else{
			$this->sWhere = '';
			$W_A = ' where ';
		}


		if ( @$_GET['sSearch'] != "" )
		{
			$this->sWhere .= $W_A." ( ".$this->getSearchWhere()." )";
		}


	}
	// GENERAR WHERE DE CONSULTA CON SEARCH
	private function getSearchWhere(){
			
		$whereStr = '';
		if(count($this->arr_col_db)>0){
			foreach($this->arr_col_db as $k=>$v){
				$whereStr .= $v." LIKE '%".mysql_real_escape_string( trim($_GET['sSearch']) )."%' OR ";
			}
			$whereStr = substr_replace( $whereStr, "", -3 );
		}

		return $whereStr;

	}

	private function fnColumnToField( $i ){

		return $this->arr_col_db[$i];

	}
	private function procesa_columnas_esp($col,$rec){
		$i = 0;
			
		foreach($rec as $k => $v){
			$col['val'] = str_replace('{'.$i.'}',$v, $col['val']);
			$i ++;
		}

		$rs = $col['val'];
		return $rs;

	} // End procesa_columnas_esp

	private function get_columns(){

		$flds1 = str_replace(' ','',$this->sql_fields);
		$columns = explode(',',$flds1);
		$this->arr_col_db = $columns;

		$tot_nr = count($columns); // Columnas normales
		$tot_sp = count($this->special_cols); // Columnas especiales

		for($i=0;$i<$tot_sp;$i++){
			$indx = $tot_nr + ($i);
			$columns[$indx]['type']='special';
			$columns[$indx]['val']=$this->special_cols[$i];
		}

		$this->arr_columns = $columns;


	} // End get_columns

	public function addCol($htmlVal){

		$this->special_cols[] = $htmlVal;

	} // End addCol

}

?>
Return current item: FreeKore