';
return $_error;
}
// set editable flag
$this->editable = $bool_editable;
// set default confirm message
if (empty($this->chbox_confirm_msg)) $this->chbox_confirm_msg = __('Are You Sure Want to DELETE Selected Data?');
// enable or disable confirm message
$this->enable_chbox_confirm = config('enable_chbox_confirm', $this->enable_chbox_confirm);
$this->sql_table = $str_db_table;
$this->highlight_row = true;
// sanitize table ID
$this->table_ID = strtolower(str_replace(array(' ', '-', ','), '', $this->table_ID));
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
$_fld_sort = $this->table_ID.'fld';
$_dir = 'ASC';
$_next_dir = 'DESC';
$_sort_dir_info = __('ascendingly');
if (isset($_GET[$_fld_sort]) AND !empty($_GET[$_fld_sort])) {
$orderBy = str_replace(["`"], '', utility::filterData($_fld_sort, 'get', true, true, true));
$this->sql_order = 'ORDER BY `'.urldecode($orderBy).'` ';
// record order direction
if (isset($_GET['dir']) AND ($_dir = trim($_GET['dir']))) {
if ($_dir == 'DESC') {
$_dir = 'DESC';
$_next_dir = 'ASC';
} else {
$_dir = 'ASC';
$_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
$_start = function_exists('microtime')?microtime(true):time();
$this->grid_real_q = $obj_db->query($_sql_str);
$_end = function_exists('microtime')?microtime(true):time();
$this->query_time = round($_end-$_start, 5);
// if the query error
if (ENVIRONMENT === 'development' AND (!$this->grid_real_q OR $obj_db->error)) {
$_error = '
';
$_error .= 'ERROR ';
$_error .= 'MySQL Server said : '.$obj_db->error.'';
if ($this->debug) {
$_error .= ' With SQL Query : '.strip_tags($_sql_str).'';
}
$_error .= '
';
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) {
if (!is_scalar($varvalue)) {
continue;
}
$varvalue = urlencode($varvalue);
if ($varname != $this->table_ID.'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 = $this->table_ID.'fld='.urlencode($this->sort_column[$_fld->name]).'&dir='.$_next_dir;
$this->grid_result_fields[] = ''.$_fld->name.'';
} else {
$this->grid_result_fields[] = $_fld->name;
}
}
// table header and invisible fields shifting
// if the table is editable
if ($this->editable) {
// invisible fields shifting value
$_shift = 1;
// 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]);
$_shift = 2;
} 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]);
$this->grid_result_fields = array_merge($_edit_header_fields, $this->grid_result_fields);
// invisible field shifting
if ($this->invisible_fields) {
$_shifted_inv_fld = array();
foreach ($this->invisible_fields as $_inv_fld) {
$_shifted_inv_fld[] = $_inv_fld+$_shift;
}
$this->invisible_fields = $_shifted_inv_fld;
}
}
// field count
$_field_cnt = count($this->grid_result_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';
// 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], $_field_num);
} 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));
}
}
}
// 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 = '';
$_edit_fields[] = $_del_chbox;
}
// check if edit link array is included
if ($this->edit_property) {
$_edit_data = $this->edit_property[0].'='.$this->grid_result_rows[$_row][0].'&detail=true';
$_edit_link = ''.( $this->edit_link_text?$this->edit_link_text:' ' ).'';
$_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]);
}
// 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" style="width: 5%;"');
} else {
// checking for special field width value set by column_width property array
$_attr = 'valign="top"';
if ($this->editable) {
if (($this->chbox_property AND $this->edit_property) AND isset($this->column_width[$f-2])) {
$_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])) {
$_attr .= ' style="width: '.$this->column_width[$f-1].';"';
}
} else {
if (isset($this->column_width[$f])) {
$_attr .= ' style="width: '.$this->column_width[$f].';"';
}
}
$this->setCellAttr($_row, $f, $_attr);
}
}
$this->setCellAttr($_row, null, '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)
{
// remove invisible fields
$this->removeInvisibleField();
// get fields array and set the table header
$this->setHeader($this->grid_result_fields);
// data loop
foreach ($this->grid_result_rows as $_data) {
// append array to table
$this->appendTableRow($_data);
}
// init buffer return var
$_buffer = '';
// create paging
$_paging = null;
if ($this->num_rows > $int_num2show && !$this->disable_paging) {
$_paging = simbio_paging::paging($this->num_rows, $int_num2show, 5);
}
// iframe
$_iframe = '';
// setting form target
$_target = '_self';
if ($this->using_AJAX) {
$_target = 'submitExec';
$_iframe = ''."\n";
// below is for debugging purpose only
// $_iframe = ''."\n";
}
// if editable
if ($this->editable) {
if (ENVIRONMENT === 'development')
{
$_buffer .= '# Debug|';
$_buffer .= '';
$_buffer .= '';
}
if (class_exists('simbio_form_maker')) {
$form_maker = new simbio_form_maker($this->table_name, $this->chbox_form_URL, $str_form_method = 'post', false);
$form_maker->submit_target = $_target;
$form_maker->add_form_attributes= 'class="form-inline"';
$_buffer .= $form_maker->startForm();
} else {
$_buffer .= ''."\n";
if (ENVIRONMENT !== 'development') $_buffer .= $_iframe;
} else {
// paging
$_button_grp = '';
if ($_paging) {
$_button_grp .= '
'
.'
'.$_paging."\n".'
';
}
$_buffer .= $_button_grp.$this->printTable().$_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;
}
/**
* Method to remove invisible field
*
* @return void
*/
protected function removeInvisibleField()
{
if (!$this->invisible_fields OR !$this->grid_result_rows) return;
$_result_rows_buffer = array();
foreach ($this->grid_result_rows as $_data) {
foreach ($this->invisible_fields as $_inv_fld) {
unset($_data[$_inv_fld]);
// remove header field to
unset($this->grid_result_fields[$_inv_fld]);
}
$_result_rows_buffer[] = $_data;
}
$this->grid_result_rows = $_result_rows_buffer;
}
}