Location: PHPKode > projects > SENAYAN Library Automation > senayan3-stable5/simbio2/simbio_DB/datagrid/simbio_dbgrid.inc.php
<?php
/**
 * simbio_datagrid class
 * SQL datagrid creator
 *
 * Copyright (C) 2007,2008  Arie Nugraha (hide@address.com)
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 */

class simbio_datagrid extends simbio_table
{
    /**
     * Private properties
     */
    private $grid_real_q = false;

    /**
     * Protected properties
     */
    protected $grid_result_fields = array();
    protected $grid_result_rows = array();
    protected $sql_table = '';
    protected $sql_column = '';
    protected $sql_criteria = '';
    protected $sql_order = '';
    protected $primary_keys = array();
    protected $no_sort_column = array();
    protected $modified_content = array();
    protected $editable = false;

    /**
     * Public properties
     */
    public $debug = false;
    public $num_rows = 0;
    public $chbox_form_URL = '';
    public $alt_row_color = '#FFFFFF';
    public $alt_row_color_2 = '#CCCCCC';
    public $icon_edit = '';
    public $table_name = 'datagrid';
    public $column_width = array();
    public $sort_column = array();
    public $sql_group_by = '';
    public $select_flag = '';
    public $chbox_property = array('itemID', 'DELETE');
    public $edit_property = array('itemID', 'EDIT');
    public $chbox_action_button = false;
    public $chbox_confirm_msg = false;
    public $current_page = 1;
    # are we using AJAX or not
    public $using_AJAX = true;

    /**
     * Method to create datagrid
     *
     * @param   object  $obj_db
     * @param   string  $str_db_table
     * @param   integer $int_num2show
     * @param   boolean $bool_editable
     * @return  string
     */
    public function createDataGrid($obj_db, $str_db_table = '', $int_num2show = 30, $bool_editable = false)
    {
        // check database connection
        if (!$obj_db OR $obj_db->error) {
            $_error = '<div style="padding: 5px; margin: 3px; border: 1px dotted #FF0000; color: #FF0000;">';
            $_error .= 'ERROR : Cant create datagrid, database connection error!';
            $_error .= '</div>';
            return $_error;
        }

        // set editable flag
        $this->editable = $bool_editable;

        if (!$this->chbox_confirm_msg) {
            if(defined('lang_sys_common_confirm_delete_selected')) {
                $this->chbox_confirm_msg = lang_sys_common_confirm_delete_selected;
            } else {
                $this->chbox_confirm_msg = 'Are You Sure To Delete Selected Data?';
            }
        }

        if (!$this->chbox_action_button) {
            if(defined('lang_sys_common_button_delete_selected')) {
                $this->chbox_action_button = lang_sys_common_button_delete_selected;
            } else {
                $this->chbox_action_button = 'Delete Selected Data';
            }
        }

        $this->sql_table = $str_db_table;
        $this->highlight_row = true;

        if (empty($this->sql_table)) {
            die('simbio_datagrid : Table not specified yet');
        }

        // get page number from http get var
        if (isset($_GET['page']) AND $_GET['page'] > 1) {
            $this->current_page = (integer) $_GET['page'];
        }

        // count the row offset
        if ($this->current_page <= 1) {
            $_offset = 0;
        } else {
            $_offset = ($this->current_page*$int_num2show) - $int_num2show;
        }

        // change the record sorting if there fld var in URL
        $_dir = 'ASC';
        $_next_dir = 'DESC';
        $_sort_dir_info = 'ascendingly';
        if (isset($_GET['fld']) AND !empty($_GET['fld'])) {
            $this->sql_order = 'ORDER BY `'.urldecode($_GET['fld']).'` ';
        }
        // record order direction
        if (isset($_GET['dir']) AND !empty($_GET['dir'])) {
            $_dir = trim($_GET['dir']);
            if ($_dir == 'DESC') {
                $_next_dir = 'ASC';
            } else {
                $_next_dir = 'DESC';
                $_sort_dir_info = 'descendingly';
            }
            // append sort direction
            $this->sql_order .= $_dir;
        }

        // check group by
        if ($this->sql_group_by) {
            $this->sql_group_by = ' GROUP BY '.$this->sql_group_by;
        }

        // sql string
        $_sql_str = 'SELECT SQL_CALC_FOUND_ROWS '.$this->select_flag.' '.$this->sql_column.
            ' FROM '.$this->sql_table.' '.$this->sql_criteria.
            ' '.$this->sql_group_by.' '.$this->sql_order." LIMIT $int_num2show OFFSET $_offset";

        // for debugging purpose only
        // return $_sql_str;

        // real query
        $this->grid_real_q = $obj_db->query($_sql_str);
        // if the query error
        if (!$this->grid_real_q OR $obj_db->error) {
            $_error = '<div style="padding: 5px; margin: 3px; border: 1px dotted #FF0000; color: #FF0000;">';
            $_error .= 'ERROR<br />';
            $_error .= 'MySQL Server said : '.$obj_db->error.'';
            if ($this->debug) {
                $_error .= '<br />With SQL Query : '.$_sql_str.'';
            }
            $_error .= '</div>';
            return $_error;
        }
        // check if there any rows returned
        if ($this->grid_real_q->num_rows < 1) {
            return $this->printTable();
        }

        // fetch total number of data
        $_num_query = $obj_db->query("SELECT FOUND_ROWS()");
        $_data = $_num_query->fetch_row();
        $this->num_rows = $_data[0];

        // check the query string and rebuild with urlencoded value
        $_url_query_str = '';
        if (isset($_SERVER['QUERY_STRING']) AND !empty($_SERVER['QUERY_STRING'])) {
            parse_str($_SERVER['QUERY_STRING'], $arr_query_var);
            // rebuild query str without "fld" and "dir" var
            foreach ($arr_query_var as $varname => $varvalue) {
                $varvalue = urlencode($varvalue);
                if ($varname != 'fld' AND $varname != 'dir') {
                    $_url_query_str .= $varname.'='.$varvalue.'&';
                }
            }
        }
        // make all field name link for sorting
        $this->grid_result_fields = array();
        // adding record order links to field name header
        foreach ($this->grid_real_q->fetch_fields() as $_fld) {
            // check if the column is not listed in no_sort_column array properties
            if (!in_array($_fld->name, $this->no_sort_column) AND isset($this->sort_column[$_fld->name])) {
                $_order_by = 'fld='.urlencode($this->sort_column[$_fld->name]).'&dir='.$_next_dir;
                if ($this->using_AJAX) {
                    $this->grid_result_fields[] = '<a onmouseover="window.status = \'\'; return true;" '
                        .'href="javascript: setContent(\'mainContent\', \''.$_SERVER['PHP_SELF'].'?'.$_url_query_str.$_order_by.'\', \'post\');" title="order list by '.$_fld->name.' '.$_sort_dir_info.'">'.$_fld->name.'</a>';
                } else {
                    $this->grid_result_fields[] = '<a href="'.$_SERVER['PHP_SELF'].'?'.$_url_query_str.$_order_by.'" title="order list by '.$_fld->name.' '.$_sort_dir_info.'">'.$_fld->name.'</a>';
                }
            } else {
                $this->grid_result_fields[] = $_fld->name;
            }
        }

        // table header
        // if the table is editable
        if ($this->editable) {
            // chbox and edit property checking
            if ($this->chbox_property AND $this->edit_property) {
                $_edit_header_fields = array($this->chbox_property[1], $this->edit_property[1]);
            } else if ($this->chbox_property AND !$this->edit_property) {
                $_edit_header_fields = array($this->chbox_property[1]);
            } else {
                $_edit_header_fields = array($this->edit_property[1]);
            }
            // concat arrays
            unset($this->grid_result_fields[0]);
            $_header_fields = array_merge($_edit_header_fields, $this->grid_result_fields);
        } else {
            $_header_fields = $this->grid_result_fields;
        }

        // get fields array and set the table header
        $this->setHeader($_header_fields);

        // field count
        $_field_cnt = count($_header_fields);

        $_row = 1;
        // records
        while ($_data = $this->grid_real_q->fetch_row()) {
            $this->grid_result_rows[$_row] = $_data;
            $_row_class = ($_row%2 == 0)?'alterCell':'alterCell2';

            // if the table is editable
            // make delete checkbox and edit button
            if ($this->editable) {
                // reset edit_fields array
                $_edit_fields = array();
                // check if checkbox array is included
                if ($this->chbox_property) {
                    $_del_chbox = '<input type="checkbox" name="'.$this->chbox_property[0].'[]" value="'.$this->grid_result_rows[$_row][0].'" id="cbRow'.$_row.'" onclick="cbHighlightRow(this, \'row'.$_row.'\', event)" />';
                    $_edit_fields[] = $_del_chbox;
                }
                // check if edit link array is included
                if ($this->edit_property) {
                    // check for edit icon
                    if ($this->icon_edit) {
                        if ($this->using_AJAX) {
                            $_edit_link = '<a href="#" '
                                .'onclick="setContent(\'mainContent\', \''.$_SERVER['PHP_SELF'].'?'.$_url_query_str.'\', \'post\', \''.$this->edit_property[0].'='.$this->grid_result_rows[$_row][0].'&detail=true\', true);" title="Click for detail or edit this Record">'
                                .'<img src="'.$this->icon_edit.'" border="0" alt="Edit" /></a>';
                        } else {
                            $_edit_link = '<a href="#" onclick="showDetailForm(\'notAJAXhiddenForm\', \''.$_SERVER['PHP_SELF'].'?'.$_url_query_str.'\', \''.$this->grid_result_rows[$_row][0].'\')" title="Click for detail or edit this Record">'
                                .'<img src="'.$this->icon_edit.'" border="0" alt="Edit" /></a>';
                        }
                    } else {
                        if ($this->using_AJAX) {
                            $_edit_link = '<a onmouseover="window.status = \'\'; return true;" '
                                .'href="javascript: setContent(\'mainContent\', \''.$_SERVER['PHP_SELF'].'?'.$this->edit_property[0].'='.$this->grid_result_rows[$_row][0].$_url_query_str.'\', \'post\', \'\', true);" title="Click for detail or edit this Record">'
                                .'Edit</a>';
                        } else {
                            $_edit_link = '<a href="#" onclick="showDetailForm(\'notAJAXhiddenForm\', \''.$_SERVER['PHP_SELF'].'?'.$_url_query_str.'\', \''.$this->grid_result_rows[$_row][0].'\')" title="Click for detail or edit this Record">'
                                .'Edit</a>';
                        }
                    }
                    $_edit_fields[] = $_edit_link;
                }
                // unset the first element (ID field)
                unset($this->grid_result_rows[$_row][0]);
                $this->grid_result_rows[$_row] = array_merge($_edit_fields, $this->grid_result_rows[$_row]);
            }

            // modified content
            foreach ($this->modified_content as $_field_num => $_new_content) {
                // change the value of modified column
                if (isset($this->grid_result_rows[$_row][$_field_num])) {
                    // run callback function php script if the string is embraced by "callback{*}"
                    if (preg_match('@^callback\{.+\}@i', $_new_content)) {
                        // strip the "callback{" and "}" string to empty string
                        $_callback_func = str_replace(array('callback{', '}'), '', $_new_content);
                        if (function_exists($_callback_func)) {
                            // call the function
                            $this->grid_result_rows[$_row][$_field_num] = @$_callback_func($obj_db, $this->grid_result_rows[$_row]);
                        } else { $this->grid_result_rows[$_row][$_field_num] = $_callback_func; }
                    } else {
                        // replace the "{column_value}" marker with real column value
                        $this->grid_result_rows[$_row][$_field_num] = str_replace('{column_value}', $this->grid_result_rows[$_row][$_field_num], trim($_new_content));
                    }
                }
            }

            // editable field style and column width modification
            for ($f = 0; $f < $_field_cnt; $f++) {
                if (($this->chbox_property AND $this->edit_property) AND ($f < 2) AND $this->editable) {
                    $this->setCellAttr($_row, $f, 'align="center" valign="top" class="'.$_row_class.'" style="width: 5%;"');
                } else {
                    // checking for special field width value set by column_width property array
                    $_row_attr = 'valign="top"';
                    if ($this->editable) {
                        if (($this->chbox_property AND $this->edit_property) AND isset($this->column_width[$f-2])) {
                            $_row_attr .= ' style="width: '.$this->column_width[$f-2].';"';
                        } else if ( (($this->chbox_property AND !$this->edit_property) OR (!$this->chbox_property AND $this->edit_property)) AND isset($this->column_width[$f-1])) {
                            $_row_attr .= ' style="width: '.$this->column_width[$f-1].';"';
                        }
                    } else {
                        if (isset($this->column_width[$f])) {
                            $_row_attr .= ' style="width: '.$this->column_width[$f].';"';
                        }
                    }
                    $this->setCellAttr($_row, $f, $_row_attr.' class="'.$_row_class.'"');
                }
            }
            $_row++;
        }

        // free resultset memory
        $this->grid_real_q->free_result();

        // return the formatted output
        return $this->makeOutput($int_num2show);
    }


    /**
     * Method to format an output of datagrid
     *
     * @param   integer $int_num2show
     * @return  string
     */
    protected function makeOutput($int_num2show = 30)
    {
        // data loop
        foreach ($this->grid_result_rows as $_data) {
            // append array to table
            $this->appendTableRow($_data);
        }

        // init buffer return var
        $_buffer = '';

        // create paging
        if ($this->num_rows > $int_num2show) {
            if ($this->using_AJAX) {
                $_paging = simbio_paging_ajax::paging($this->num_rows, $int_num2show, 5);
            } else {
                $_paging = simbio_paging::paging($this->num_rows, $int_num2show, 5);
            }
        } else {
            $_paging =  null;
        }
        // setting form target
        if ($this->using_AJAX) {
            $_target = 'submitExec';
            // below is for debugging purpose only
            // $_iframe = '<iframe name="submitExec" style="visibility: visible; width: 100%; height: 300px;"></iframe>'."\n";
            $_iframe = '<iframe name="submitExec" style="visibility: hidden; width: 100%; height: 0;"></iframe>'."\n";
        } else {
            $_target = '_self';
            $_iframe = '<form name="notAJAXhiddenForm" id="notAJAXhiddenForm" action="" method="post" style="display: inline;"><input type="hidden" name="itemID" value="" /><input type="hidden" name="detail" value="true" /></form>';
        }
        // if editable
        if ($this->editable) {
            $_buffer .= '<form action="'.$this->chbox_form_URL.'" name="'.$this->table_name.'" id="'.$this->table_name.'" target="'.$_target.'" method="post" style="display: inline;">'."\n";

            if (defined('lang_sys_common_form_checkbox_all')) {
                $_check_all = lang_sys_common_form_checkbox_all;
            }

            if (defined('lang_sys_common_form_uncheckbox_all')) {
                $_uncheck_all = lang_sys_common_form_uncheckbox_all;
            }

            // action buttons group
            $_button_grp = '<table cellspacing="0" cellpadding="5" style="background-color: #dcdcdc; width: 100%;"><tr>';
            // if checkbox is include then show button
            if ($this->chbox_property) {
                $_button_grp .= '<td><input type="button" onclick="chboxFormSubmit(\''.$this->table_name.'\', \''.$this->chbox_confirm_msg.'\')" value="'.$this->chbox_action_button.'" class="button" /> '
                    .'<input type="button" onclick="checkAll(\''.$this->table_name.'\', false)" value="'.$_check_all.'" class="button" /> '
                    .'<input type="button" onclick="checkAll(\''.$this->table_name.'\', true)" value="'.$_uncheck_all.'" class="button" /> '
                    .'</td>';
            }

            // paging
            if ($_paging) {
                $_button_grp .= '<td align="right">';
                $_button_grp .= $_paging."\n";
                $_button_grp .= '</td>';
            }
            if ($_paging AND $this->chbox_property) {
                $_button_grp .= '</tr></table>'."\n";
            }

            // table grid
            $_buffer .= $_button_grp;
            $_buffer .= $this->printTable();
            $_buffer .= $_button_grp;
            $_buffer .= '<input type="hidden" name="itemAction" value="true" />';
            $_buffer .= '<input type="hidden" name="lastQueryStr" value="'.$_SERVER['QUERY_STRING'].'" />'."\n";
            $_buffer .= '</form>'."\n";
            $_buffer .= $_iframe;
        } else {
            // paging
            $_button_grp = '';
            if ($_paging) {
                $_button_grp .= '<table cellspacing="0" cellpadding="5" class="notprinted" style="background-color: #dcdcdc; width: 100%;">';
                $_button_grp .= '<tr><td align="right">';
                $_button_grp .= $_paging."\n";
                $_button_grp .= '</td></tr></table>';
            }

            $_buffer .= $_button_grp;
            $_buffer .= $this->printTable();
            $_buffer .= $_button_grp;
        }

        return $_buffer;
    }


    /**
     * Method to set datagrid fields
     *
     * @param   string  $sql_field
     * @return  void
     */
    public function setSQLColumn()
    {
        $_args_num = func_num_args();
        if ($_args_num < 1) {
            $this->sql_column = '*';
        } else if ($_args_num == 1) {
            $this->sql_column = func_get_arg(0);
        } else {
            // get all function arguments
            $columns = func_get_args();
            // iterate all arguments
            foreach ($columns as $_field) {
                $_column_alias = '';
                $_real_column = '';
                if (preg_match('/\sAS\s/i', $_field)) {
                    $_field_n_alias = explode(' AS ', $_field);
                    $_real_column = $_field_n_alias[0];
                    $_column_alias = str_replace("'", '', $_field_n_alias[1]);
                } else {
                    $_real_column = $_field;
                    $_column_alias = $_field;
                }
                // store to class properties
                $this->sql_column .= $_field.', ';
                // $this->sort_column[trim($_column_alias)] = trim($_real_column);
                $this->sort_column[trim($_column_alias)] = trim($_column_alias);
            }

            // remove the last comma
            $this->sql_column = substr_replace($this->sql_column, ' ', -2);
        }
        // for debugging purpose only
        // var_dump($this->sort_column); die();
    }


    /**
     * Method to set SQL criteria (WHERE definition) of datagrid
     *
     * @param   string  $str_where_clause
     * @return  void
     */
    public function setSQLCriteria($str_where_clause)
    {
        if (!$str_where_clause) {
            // do nothing
        } else {
            // remove WHERE word if exist
            $str_where_clause = preg_replace("/^WHERE\s/i", '', $str_where_clause);
            $this->sql_criteria = 'WHERE '.$str_where_clause;
        }
    }


    /**
     * Method to set ordering of datagrid
     *
     * @param   string  $str_order_column
     */
    public function setSQLorder($str_order_column)
    {
        if (!$str_order_column) {
            // do nothing
        } else {
            // remove WHERE word if exist
            $this->sql_order = 'ORDER BY '.$str_order_column;
        }
    }


    /**
     * Method to disable sorting link of certain fields in datagrid
     *
     * @param   integer $field_number
     * @return  void
     */
    public function disableSort()
    {
        if (func_num_args() > 0) {
            $this->no_sort_column = func_get_args();
        }
    }


    /**
     * Method to modify column content of field in datagrid
     *
     * @param   integer $int_column_no
     * @param   string  $str_new_value
     * @return  void
     */
    public function modifyColumnContent($int_column_no, $str_new_value)
    {
        $this->modified_content[$int_column_no] = $str_new_value;
    }
}

?>
Return current item: SENAYAN Library Automation