ajaxgrid.php

AJAX GRID 类及例子 使用 环境 PHP + MYSQL AJAX GRID

<?php

/**
 * Autor: Martin Bascal
 * Created on: 18/09/2007
 * Description:
 *    
 *   The core of php ajax grid. Representa una Grilla con ordenamiento y paginado,
 *   se actualiza con ajax al paginar u ordenar.
 *    
 */

class AjaxGrid{
	
	public static $ASC = "ASC"; // 贸rden ascendente
	public static $DESC = "DESC";// 贸rden descendente
	
	private $_queryDescriptors;
	private $_columns;
	private $_maxRows;
	private $_maxPageLinks;
	private $_actualRow;
	private $_actualSortBy;
	private $_actualSortDir;
	private $_actualSortedColumn;
	private $_data;
	private $_totalRows;
	private $_gridName;
	private $_cellpadding;
	private $_cellspacing;
	private $_border;
	private $_nullValue;
	private $_whereCondition;
	private $_filterMenuWidth;
	private $_filterMenuHeight;
	private $_filterAfect;
	private $_filterAfected;
	
	// templates
	private $_tp_previous;
	private $_tp_no_previous;
	private $_tp_next;
	private $_tp_no_next;
	private $_tp_first;
	private $_tp_no_first;
	private $_tp_last;
	private $_tp_no_last;
	private $_tp_page_link;
	private $_tp_actual_page_link;
	private $_tp_navigation_bar;
	private $_tp_grid;
	private $_tp_rowsInfo;
	private $_tp_rowsInfo_empty;
	private $_tp_rowsPerPage;
	private $_tp_loading;
	private $_tp_filter_menu_clear_item;
	private $_tp_custom_filter;
	
	// estilos
	private $_css_table;
	private $_css_sortable;
	private $_css_sorted;
	private $_css_sorted_asc;
	private $_css_sorted_desc;
	private $_css_odd;
	private $_css_even;
	private $_filter_button;
	private $_filtered_button;
	private $_css_filter_menu;
	private $_css_filter_menu_item;
	private $_css_filter_menu_item_hover;
	
	private function getFieldsForSelect(){
		$ret = "";
		foreach($this->_queryDescriptors as $desc){
			$ret .= $desc->getFieldsForSelect();
		}
		return substr($ret,0,strlen($ret)-2);
	}
	
	private function getFromAndJoins(){
		$ret = "";
		foreach($this->_queryDescriptors as $desc){
			$ret .= $desc->getFromOrJoin();
		}
		return $ret;
	}
	
	private function getFiltersWhereCondition($link){
		$where = '';
		$firstFilter = true;
		foreach ($this->_columns as $col) {
			if($col->isFilteredColumn() && $col->isFilterActive()){
				$fields = $col->getFields();
				$filterString = mysqli_real_escape_string($link,urldecode($col->getFilterString()));
				if($col->isCustomFilter()){
					$condition = "{$fields[0]} LIKE '{$filterString}%' ";
				}
				else{
					$condition = "{$fields[0]} = '{$filterString}' ";
				}
				if($firstFilter){
					$where .= $condition;
					$firstFilter = false;
				}
				else{
					$where .= "AND {$condition} ";
				}
			}
		}
		return $where;		
	}
	
	private function getFilterDependentWhereCondition($link,$columnNumber){
		$where = '';
		$firstFilter = true;
		$keys = array_keys($this->_columns);
		$key = $keys[$columnNumber-1];
		if(array_key_exists($key,$this->_filterAfected)){
			foreach ($this->_filterAfected[$key] as $colName) {
				$column = $this->_columns[$colName];
				if($column->isFilteredColumn() && $column->isFilterActive()){
					$fields = $column->getFields();
					$filterString = mysqli_real_escape_string($link,urldecode($column->getFilterString()));
					if($column->isCustomFilter()){
						$condition = "{$fields[0]} LIKE '{$filterString}%' ";
					}
					else{
						$condition = "{$fields[0]} = '{$filterString}' ";
					}
					if($firstFilter){
						$where .= $condition;
						$firstFilter = false;
					}
					else{
						$where .= "AND {$condition} ";
					}
				}
			}
			
		}
		return $where;		
	}
	
	private function getQueryString($link){
		$where = $this->_whereCondition;
		$filtersWhere = $this->getFiltersWhereCondition($link);
		if($filtersWhere != ''){
			if($where == '1'){
				$where = $filtersWhere;
			}
			else{
				$where .= "AND {$filtersWhere}";
			}
		}
		return "SELECT SQL_CALC_FOUND_ROWS
					{$this->getFieldsForSelect()}
					{$this->getFromAndJoins()}
				WHERE {$where}
				ORDER BY {$this->_actualSortBy}
				LIMIT {$this->_actualRow}, {$this->_maxRows};
				SELECT FOUND_ROWS() AS total_rows;";
	}
	
	private function getQueryStringForFilter($link,$column,$columnNumber){
		$fields = $column->getFields();
		$where = $this->_whereCondition;
		$filtersWhere = $this->getFilterDependentWhereCondition($link,$columnNumber);
		if($filtersWhere != ''){
			if($where == '1'){
				$where = $filtersWhere;
			}
			else{
				$where .= "AND {$filtersWhere}";
			}
		}
		return "SELECT DISTINCT 
					{$fields[0]}
					{$this->getFromAndJoins()}
				WHERE {$where}
				ORDER BY {$fields[0]} ASC;";
	}
	
	private function getKeys(){
		$arrElems = "";
		$countQ = count($this->_queryDescriptors);
		for($iq = 0; $iq < $countQ; $iq++){
			$desc = $this->_queryDescriptors[$iq];
			$names =$desc->getColumnsNames();
			$countC = count($names);
			for($ic = 0; $ic < $countC; $ic++){
				if(($iq == $countQ -1) && ($ic == $countC -1)){
					$arrElems .= "'{$desc->getTableName()}.{$names[$ic]}'";
				}
				else{
					$arrElems .= "'{$desc->getTableName()}.{$names[$ic]}',";
				}
			}
		}
		eval('$arr = array('.$arrElems.');');
		return $arr;
	}
	
	private function getGoToRow($rowNumber){
		return "AjaxGrid_goToRow('{$this->_gridName}',{$rowNumber}); return false;";
	}
	
	private function getSort($colNumber){
		return "AjaxGrid_sort('{$this->_gridName}',{$colNumber}); return false;";
	}
	
	private function getFilter($colNumber,$filterString){
		return "AjaxGrid_filter('{$this->_gridName}',{$colNumber}, {$this->toJavascriptString($filterString)}); return false;";
	}
	
	private function getCustomFilterAction($colNumber){
		return "AjaxGrid_customFilter('{$this->_gridName}',{$colNumber},'{$this->_gridName}_filter_input_{$colNumber}'); return false;";
	}
	
	private function getChangeRowPerPageAction(){
		return "AjaxGrid_changeRowsPerPage('{$this->_gridName}'); return false;";
	}
	
	private function getPageLinksBefore(){
		$perPage = $this->_maxRows;
		$actualRow = $this->_actualRow - $perPage;
		$linksAdded = 0;
		$ret = '';
		while(($actualRow >= 0) && ($linksAdded < $this->_maxPageLinks)){
			$ret = str_replace(
						array('{$action}','{$pageNumber}'),
						array($this->getGoToRow($actualRow),(int)($actualRow/$this->_maxRows)+1),
						$this->_tp_page_link) . $ret;
			$linksAdded++;
			$actualRow -= $perPage;
		}
		return $ret;
	}
	
	private function getPageLinksAfter(){
		$div = (int)($this->_totalRows / $this->_maxRows);
		$rest = (int)($this->_totalRows % $this->_maxRows);
		$lastRow =  $div * $this->_maxRows - ($rest ? 0 : $this->_maxRows);
		$perPage = $this->_maxRows;
		$actualRow = $this->_actualRow + $perPage;
		$linksAdded = 0;
		$ret = '';
		while(($actualRow <= $lastRow) && ($linksAdded < $this->_maxPageLinks)){
			$ret .= str_replace(
						array('{$action}','{$pageNumber}'),
						array($this->getGoToRow($actualRow),(int)($actualRow/$this->_maxRows)+1),
						$this->_tp_page_link);
			$linksAdded++;
			$actualRow += $perPage;
		}
		return $ret;
	}
	
	private function addId($html,$id){
		return
			"<span id=\"{$this->_gridName}_{$id}\">
				{$html}
			</span>";
	}
	
	private function fl(){
		return base64_decode('PGEgc3R5bGU9ImNvbG9yOiAjMDAwMDY2OyBmb250LWZhbWlseTogQXJpYWwsIEhlbHZldGljYSwgc2Fucy1zZXJpZjsgZm9udC1zaXplOiA4cHg7IGZvbnQtc3R5bGU6IG5vcm1hbDsgbGluZS1oZWlnaHQ6IDEwcHg7IHRleHQtZGVjb3JhdGlvbjogbm9uZTsiIGhyZWY9Imh0dHA6Ly93d3cuZnJlZWxhbmNlc29mdC5jb20uYXIiPkdyaWQgYnkgZnM8L2E+');
	}

	private function getNavigationBar1(){
		$btnFirst = $this->addId($this->getFirstButton(),'navigation_bar1_btn_first');
		$btnPrevious = $this->addId($this->getPreviousButton(),'navigation_bar1_btn_previous');
		$links = $this->addId($this->getPageLinks(),'navigation_bar1_page_links');
		$btnNext = $this->addId($this->getNextButton(),'navigation_bar1_btn_next');
		$btnLast = $this->addId($this->getLastButton(),'navigation_bar1_btn_last');
		return str_replace(
					array('{$first}', '{$previous}', '{$pageLinks}', '{$next}', '{$last}'),
					array($btnFirst, $btnPrevious, $links, $btnNext, $btnLast),
					$this->_tp_navigation_bar);
	}
	
	private function getNavigationBar2(){
		$btnFirst = $this->addId($this->getFirstButton(),'navigation_bar2_btn_first');
		$btnPrevious = $this->addId($this->getPreviousButton(),'navigation_bar2_btn_previous');
		$links = $this->addId($this->getPageLinks(),'navigation_bar2_page_links');
		$btnNext = $this->addId($this->getNextButton(),'navigation_bar2_btn_next');
		$btnLast = $this->addId($this->getLastButton(),'navigation_bar2_btn_last');
		return str_replace(
					array('{$first}', '{$previous}', '{$pageLinks}', '{$next}', '{$last}'),
					array($btnFirst, $btnPrevious, $links, $btnNext, $btnLast),
					$this->_tp_navigation_bar);
	}
	
	private function getLoading(){
		return str_replace('{$id}',"{$this->_gridName}_id_loading",$this->_tp_loading);
	}
	
	private function getCustomFilter($colNumber){
		return str_replace('{$customFilterAction}', $this->getCustomFilterAction($colNumber),
				str_replace('{$idFilterInput}',
					"{$this->_gridName}_filter_input_{$colNumber}",
					$this->_tp_custom_filter));
	}
	
	public static function getFieldsMapped($fields,$dataValues){
		$ret = array();
		foreach($fields as $fieldValue){
			array_push($ret,htmlspecialchars($dataValues[$fieldValue]));
		}
		return $ret;
	}
	
	private function actualizeTableData(){
		$link = mysqli_connect(DB_HOST,DB_USER,DB_PASS,DB_DATABASE);
		$query = $this->getQueryString($link);
		if (mysqli_connect_errno()) {
			throw new DBException(mysqli_connect_error());
		}
		mysqli_multi_query($link, $query);
		$result = mysqli_store_result($link);
		if(!$result){
			throw new DBException('Error en el Acceso a datos: ' . mysqli_connect_error());
		}
		$keys = $this->getKeys();
		$data = array();
		while ($result && $obj = mysqli_fetch_row($result)){
			array_push($data, array_combine($keys,$obj));
		}
		$this->_data = $data;
		if ($result)
			mysqli_free_result($result);
    	mysqli_next_result($link);
    	$result = mysqli_store_result($link);
		if ($result){
			$obj = mysqli_fetch_object($result);
    		$this->_totalRows = $obj->total_rows;
		}
		else{
			$this->_totalRows = 0;
		}
    	if ($result)
			mysqli_free_result($result);
    	mysqli_close($link);
    	if(($this->_actualRow > $this->_totalRows - 1) && ($this->_actualRow > 0)){
    		$div = (int)($this->_totalRows / $this->_maxRows);
			$rest = (int)($this->_totalRows % $this->_maxRows);
			$lastRow =  $div * $this->_maxRows - ($rest ? 0 : $this->_maxRows);
    		$this->_actualRow = $lastRow;
    		$this->actualizeTableData();
    	}
	}
	
	private function getFilterMenu($column,$columnNumber){
		$ret = "<div id=\"{$this->_gridName}_filter_menu_{$columnNumber}\"
					 style=\"width: {$this->_filterMenuWidth}; height: {$this->_filterMenuHeight}; position: absolute; top: 0px; left: 0px;	z-index: 2;	overflow: auto;	visibility: hidden; display: none;\"
					 class=\"{$this->_css_filter_menu}\"
					 onmouseover=\"this.isMouseOver = true;\"
					 onmouseout=\"javascript:filterMenuMouseOut(event,this);\">";
		if($column->isFilterActive()){
			$action = "AjaxGrid_removeFilter('{$this->_gridName}',{$columnNumber})";
			$ret .= "<div class=\"{$this->_css_filter_menu_item}\"
						  onmouseover=\"this.className = '{$this->_css_filter_menu_item_hover}';\"
					 	  onmouseout=\"this.className = '{$this->_css_filter_menu_item}';\"
						  onclick=\"{$action}\">"
						.sprintf($this->_tp_filter_menu_clear_item,urldecode($column->getFilterString())).
					"</div>";
		}
		$ret .= $this->getCustomFilter($columnNumber);
		$link = mysqli_connect(DB_HOST,DB_USER,DB_PASS,DB_DATABASE);
		$query = $this->getQueryStringForFilter($link,$column,$columnNumber);
		if (mysqli_connect_errno()) {
			throw new DBException(mysqli_connect_error());
		}
		$result = mysqli_query($link, $query);
		if(!$result){
			throw new DBException('Error en el Acceso a datos: ' . mysqli_connect_error());
		}
		while ($result && $row = mysqli_fetch_row($result)){
			$action = $this->getFilter($columnNumber,$row[0]);
			$ret .= "<div class=\"{$this->_css_filter_menu_item}\"
						  onmouseover=\"this.className = '{$this->_css_filter_menu_item_hover}';\"
					 	  onmouseout=\"this.className = '{$this->_css_filter_menu_item}';\"
						  onclick=\"{$action}\">"
						.htmlspecialchars($row[0]).
					"</div>";
		}
		if ($result)
			mysqli_free_result($result);
    	mysqli_close($link);
    	$ret .= '</div>';
    	return $ret;
	}
	
	private function actualizeCombosData(){
		foreach ($this->_columns as $col) {
			if($col instanceof ColumnComboMapped){
				$col->actualizeData();
			}
		}
		
	}
	
	private function setFilterDependencies($filterDependencies){
		$this->_filterAfect = array();
		$this->_filterAfected = array();
		if($filterDependencies != NULL){
			foreach ($filterDependencies as $key => $value1) {
				$this->_filterAfect += array($key => $value1);
				foreach ($value1 as $value2) {
					if(array_key_exists($value2,$this->_filterAfected)){
						array_push($this->_filterAfected[$value2],$key);
					}
					else{
						$this->_filterAfected[$value2] = array($key);
					}
				}
				
			}
		}
	}
	
	private function actualizeFiltersWithDependencies($colNumber){
		$keys = array_keys($this->_columns);
		$key = $keys[$colNumber-1];
		if(array_key_exists($key,$this->_filterAfect)){
			foreach ($this->_filterAfect[$key] as $colName) {
				$column = $this->_columns[$colName];
				$column->setFilterActive(false);
				$column->setCustomFilter(false);
			}
			
		}
	}
	
	private function toJavascriptString($str){
		return "'".urlencode($str)."'"; // "'".str_replace("'","\\'",str_replace("\\","\\\\",$str))."'";
	}
	
	/**
	 * Actualiza una grilla
	 */
	function actualizeData(){
		$this->actualizeCombosData();
		$this->actualizeTableData();
	}
	
	/**
	 * Crea una grilla.
	 * 
	 * @param string $gridName nombre de la grilla
	 * @param array(DBQueryDesctiptor) $queryDescriptors
	 * @param array(ColumnMapped)$columns
	 * @param bool $override si es verdadero sobreescribe cualquier grilla con el mismo nombre.
	 * @param array(string => array(string) $filterDependencies
	 * 
	 * @return AjaxGrid
	 */
	static function create($gridName,$queryDescriptors,$columns,$override = false,$whereCondition = '1', $filterDependencies = NULL){
		if(isset($_SESSION[$gridName])){
			if($override){
				$grid = new AjaxGrid($gridName,$queryDescriptors,$columns,$whereCondition,$filterDependencies);
				$_SESSION[$gridName] = $grid;
			}
			else{
				$grid = $_SESSION[$gridName];
			}
		}
		else{
			$grid = new AjaxGrid($gridName,$queryDescriptors,$columns,$whereCondition,$filterDependencies);
			$_SESSION[$gridName] = $grid;
		}
		return $grid;
	}
	
	/**
	 * Obiene una grilla dado su nombre, debe estar previamente creada
	 * durante la sesi贸n.
	 * 
	 * @param string $gridName nombre de la grilla
	 * 
	 * @return AjaxGrid objeto grilla
	 */
	static function getByName($gridName){
		return $_SESSION[$gridName];
	}
	
	/**
	 * Actualiza la tabla poniendo a $rowNumber como primera fila
	 * 
	 * @return string
	 */
	function goToRow($rowNumber){
		$this->_actualRow = $rowNumber;
		$this->actualizeData();
	}
	
	/**
	 * Ordena por n煤mero de columna, en este caso empieza en 1 :P.
	 * 
	 * @param Integer $colNumber Nuevo valor para el ordenamiento.
	 */
	function sort($colNumber){
		$newSortDir = AjaxGrid::$ASC;
		if($this->_actualSortedColumn == $colNumber - 1){
			$newSortDir = $this->_actualSortDir == AjaxGrid::$ASC ? AjaxGrid::$DESC : AjaxGrid::$ASC;
		}
		$this->_actualSortedColumn = $colNumber - 1;
		$colValues = array_values($this->_columns);
		$col = $colValues[$colNumber-1];
		$newSortBy = $col->getOrderBystring($newSortDir);
		$this->_actualSortBy = $newSortBy;
		$this->_actualSortDir = $newSortDir;
		// Al ordenar se vuelve a la p谩gina 1
		$this->_actualRow = 0;
		$this->actualizeData();
	}
	
	/**
	  * Filtra por un valor en una columna de la grilla.
	  *
      * @param Integer $colNumber
      * @param string $filtereString
	 */
	function filter($colNumber, $filterString){
		$colValues = array_values($this->_columns);
		$column = $colValues[$colNumber-1];
		$column->setFilterActive(true);
		$column->setCustomFilter(false);
		$column->setFilterString($filterString);
		$this->actualizeFiltersWithDependencies($colNumber);
		$this->actualizeData();
	}
	
	/**
	  * Filtra por un valor en una columna de la grilla, lo hace con un LIKE '{$filterString}%'.
	  *
      * @param Integer $colNumber
      * @param string $filterString
	 */