public function getData($table = '') { if ($table != '') { $this->table = $table; } //set necessary variables $table_parent = Utils::setVar("table_parent", ''); $id_parent = Utils::setVar("id_parent", ''); $this->sql = ''; //these should be mapped in from the controller - not set here $sort_dir = Utils::setVar("sort_dir", "DESC"); $offset = Utils::setVar("offset", "0"); $limit = Utils::setVar("limit", "100"); $search = Utils::setVar("search"); $mode = Utils::setVar("mode", "main"); //get table description data $this->tableMeta = AdaptorMysql::query("SHOW COLUMNS FROM {$this->table}", MYSQL_BOTH); $this->key = AdaptorMysql::getPrimaryKey($this->table); $sort_col = Utils::setVar("sort_col", $this->key); //check for config info here $q_col = false; //get configuration data for form $q_c = array(); //get all the base config $tA = Utils::checkArray(_ControllerFront::$config['tables'], array('table_name' => $this->table), true); if (is_array($tA)) { $q_c = $tA; } if ($q_sql = $this->db->query("SELECT * FROM " . BLACKBIRD_TABLE_PREFIX . "tables WHERE table_name = '{$this->table}' ORDER BY table_name,display_mode")) { $q_c = array_merge($q_c, $q_sql); } if (!$q_col) { if ($mode == 'main') { $q_col = Utils::checkArray($q_c, array('table_name' => $this->table, 'display_mode' => 'main')); if (!$q_col) { $q_col = Utils::checkArray($q_c, array('table_name' => $this->table, 'display_mode' => '')); } } if ($mode == 'related') { $q_col = Utils::checkArray($q_c, array('table_name' => $this->table, 'display_mode' => 'related')); if (!$q_col) { $q_col = Utils::checkArray($q_c, array('table_name' => $this->table, 'display_mode' => '')); } } } //column description information $fields = array(); if ($q_col['cols_default'] == "") { $select_cols = '*'; } else { $select_cols = $q_col['cols_default']; $fields = explode(",", $select_cols); } if ($select_cols == "*") { $fields = array(); for ($i = 0; $i < count($this->tableMeta); $i++) { $row = $this->tableMeta[$i]; $fields[] = $row[0]; } } //filters and WHERE $filterA = array(); $this->filtersA = array(); $whereA = array(); $where = 'WHERE '; $where .= $this->sql; $filterWhere = ''; if ($table == BLACKBIRD_TABLE_PREFIX . 'history') { $filterWhere = $this->sql; $label = '_History_'; } else { if ($table_parent != '') { $relation = AdaptorMysql::queryRow("SELECT * FROM " . BLACKBIRD_TABLE_PREFIX . "relations WHERE table_parent = '{$table_parent}' AND table_child = '{$table}'"); $q_parent = AdaptorMysql::queryRow("SELECT * FROM {$table_parent} WHERE id = {$id_parent}"); $sql_val = $q_parent[$relation['column_parent']]; $whereA[] = "{$relation['column_child']} = '{$sql_val}'"; $filterWhere = "{$relation['column_child']} = '{$sql_val}'"; //from build in a page if (isset($this->config['sql_where'])) { $whereA[] = $this->config['sql_where']; $filterWhere .= ' AND ' . $this->config['sql_where']; } //from build in remote if (isset($_REQUEST['sql_where'])) { $whereA[] = stripslashes($_REQUEST['sql_where']); $filterWhere .= ' AND ' . stripslashes($_REQUEST['sql_where']); } $label = $relation['label']; } else { $label = $table; } } if ($search != "") { $q = $this->db->query("SHOW COLUMNS FROM {$this->table}", MYSQL_BOTH); $search_fields = array(); for ($i = 0; $i < count($q); $i++) { $row = $q[$i]; $search_fields[] = $row[0]; } //Generate search $mySearch = "'%" . mysql_real_escape_string(stripslashes(trim($search))) . "%'"; $rSearch = AdaptorMysql::generateSearch($search_fields, $mySearch); } $q_filters = AdaptorMysql::query("SELECT column_name FROM " . BLACKBIRD_TABLE_PREFIX . "cols WHERE (table_name = '*' OR table_name = '{$table}') AND filter != ''"); if ($q_filters) { //loop through and find intersections foreach ($q_filters as $filter) { $col = $filter['column_name']; if (in_array($col, $fields)) { $filterA[] = $col; $_filter = array(); if (isset($_REQUEST['filter_' . $col])) { if ($_REQUEST['filter_' . $col] != '') { $t = $_REQUEST['filter_' . $col]; $whereA[] = "{$col} = '{$t}'"; //$this->filtersA[] = array('col'=>$col,'value'=>$t); $_filter['col'] = $col; $_filter['value'] = $t; } } //query up option data $filterWhere != '' ? $w = 'WHERE ' . $filterWhere : ($w = ''); $optionA = array(); $field = $col; if ($q_select = AdaptorMysql::query("SELECT DISTINCT `{$field}` FROM `{$table}` {$w} ORDER BY `{$field}`")) { foreach ($q_select as $row) { $sel = ''; if (isset($_REQUEST['filter_' . $field])) { if ($_REQUEST['filter_' . $field] == $row[$field]) { $sel = 'selected="selected"'; } } $tv = _ControllerFront::formatCol($field, $row[$field], $table); $q_c = AdaptorMysql::query("SELECT * FROM " . BLACKBIRD_TABLE_PREFIX . "cols WHERE column_name = '{$field}'"); if ($q_c) { $q_col = Utils::checkArray($q_c, array('table_name' => $table)); if (!$q_col) { $q_col = Utils::checkArray($q_c, array('table_name' => '*')); } if ($q_col) { if ($q_col['filter'] != '') { $tA = _ControllerFront::parseConfig($q_col['filter']); if (isset($tA['filter_length'])) { if (strlen(strip_tags($tv)) > $tA['filter_length']) { $tv = substr(strip_tags($tv), 0, $tA['filter_length']) . '...'; } } } } } $optionA[] = array('value' => $row[$field], 'label' => $tv, 'selected' => $sel); } } //sort it $optionA = Utils::arraySort($optionA, 'label'); $_filter['options'] = $optionA; $this->filtersA[$field] = $_filter; } } } if (count($whereA) > 0) { if ($where != 'WHERE ') { $where .= ' AND '; } $where .= join($whereA, ' AND '); } else { if ($where == 'WHERE ') { $where = ''; } } if ($search == '') { $query_data = AdaptorMysql::query("SELECT {$select_cols} FROM `{$table}` {$where} ORDER BY `{$sort_col}` {$sort_dir} LIMIT {$limit} OFFSET {$offset}"); if ($query_data) { $rT = count($query_data); } else { $rT = 0; } $q2 = AdaptorMysql::query("SELECT * FROM {$table} {$where}"); if ($q2) { $rows_total = count($q2); } else { $rows_total = 0; } } else { if ($where == '') { $where = 'WHERE '; } if ($where != 'WHERE ' && $rSearch != '') { $where .= ' AND ('; $rSearch = $rSearch . ')'; } $query_data = AdaptorMysql::query("SELECT {$select_cols} FROM `{$table}` {$where} {$rSearch} ORDER BY `{$sort_col}` LIMIT {$limit} OFFSET {$offset}"); $rT = count($query_data); $q2 = AdaptorMysql::query("SELECT * FROM `{$table}` {$where} {$rSearch}"); if ($q2) { $rows_total = count($q2); } else { $rows_total = 0; } } $this->recordSet = array(); //build recordSet if ($query_data) { foreach ($query_data as $row) { $tA = array(); for ($j = 0; $j < count($fields); $j++) { $data = _ControllerFront::formatCol($fields[$j], $row[$fields[$j]], $table); $tA[$fields[$j]] = array('col' => $fields[$j], 'value' => $data); } //convert to the key $this->recordSet[$row[$this->key]] = _ControllerFront::injectData($tA, $table, 'body'); } } //headerData $tA = array(); for ($j = 0; $j < count($fields); $j++) { isset($row[$j]) ? $value = $row[$j] : ($value = ''); $data = _ControllerFront::formatCol($fields[$j], $value, $table); $tA[$fields[$j]] = array('col' => $fields[$j], 'value' => $data); } $this->headerData = _ControllerFront::injectData($tA, $table, 'head'); $delete_allowed = false; //if($this->cms->session->privs("delete",$table)){ // $delete_allowed = true; //} return array('headerData' => $this->headerData, 'rowData' => $this->recordSet, 'sort_col' => $sort_col, 'sort_dir' => $sort_dir, 'table' => $this->table, 'rows_total' => $rows_total, 'limit' => $limit, 'offset' => $offset, 'mode' => $mode, 'filtersA' => $this->filtersA, 'filterA' => $filterA, 'search' => $search); }
public function Process() { $this->layout_view = null; //server side validation $this->_name_space = $_POST['name_space'] . '_'; $this->mode = $_POST['mode']; $this->table = $_POST['table']; $this->query_action = $_POST['query_action']; //check permissions if (!_ControllerFront::$session->getPermissions($this->query_action, $this->table)) { return; } $this->channel = $_POST['channel']; $this->key = AdaptorMysql::getPrimaryKey($this->table); if ($this->query_action == 'update') { $this->id = $_POST[$this->_name_space . $this->key]; } else { $this->id = ''; } $this->db = AdaptorMysql::getInstance(); $q_cols = $this->db->query("SHOW COLUMNS FROM {$this->table}", MYSQL_BOTH); $row_data = array(); //set up error handler here $this->errorData = array(); foreach ($q_cols as $col) { $col_type = strtolower($col['Type']); $col_ready = false; $q_c = array(); //get all the base config $tA = Utils::checkArray(_ControllerFront::$config['cols'], array('column_name' => $col['Field']), true); if (is_array($tA)) { $q_c = $tA; } //get anything from the blackbird_cols if ($q_sql = $this->db->query("SELECT * FROM " . BLACKBIRD_TABLE_PREFIX . "cols WHERE column_name = '{$col['Field']}' AND process_module != '' ORDER BY table_name,process_mode")) { $q_c = array_merge($q_c, $q_sql); } // Check process mode $q_col = Utils::checkArray($q_c, array('table_name' => $this->table, 'process_mode' => $this->query_action)); if (!$q_col) { $q_col = Utils::checkArray($q_c, array('table_name' => $this->table, 'process_mode' => '')); } if (!$q_col) { $q_col = Utils::checkArray($q_c, array('table_name' => '*', 'process_mode' => $this->query_action)); if (!$q_col) { $q_col = Utils::checkArray($q_c, array('table_name' => '*', 'process_mode' => '')); } } $module = $q_col ? $q_col['process_module'] : ''; switch (true) { case $module == 'plugin' || $module == 'file': $options = array(); $options['mode'] = $this->query_action; $options['name_space'] = $this->_name_space; $options['db'] = AdaptorMysql::getInstance(); if ($this->query_action == "update") { $options['id'] = $this->id; } if ($this->query_action == "insert") { $options['id'] = $this->db->getInsertId($this->table); } $options['col_name'] = $col['Field']; $options['table'] = $this->table; if (isset($_REQUEST[$this->_name_space . $col['Field']])) { $value = $_REQUEST[$this->_name_space . $col['Field']]; } else { $value = ''; } if (strlen($q_col['process_config']) > 1) { $options = array_merge($options, _ControllerFront::parseConfig($q_col['process_config'])); } if ($module == 'plugin') { $t = _ControllerFront::pluginColumnProcess($this->_name_space . $col['Field'], $value, $options); if (isset($t['error'])) { $this->errorData[] = array('field' => $col['Field'], 'error' => $t['error']); } else { if (is_array($t)) { $row_data[] = $t; } } } if ($module == 'file') { $name = $this->_name_space . $col['Field']; $upload = true; if (isset($options['file_validator']) && is_uploaded_file($_FILES[$name]['tmp_name'])) { $t = Utils::validateFile($_FILES[$name], $options['file_validator']); if ($t === true) { } else { if (is_array($t)) { $r = '<ul>'; foreach ($t as $row) { $r .= '<li>' . $row . '</li>'; } $r .= '</ul>'; $this->errorData[] = array('field' => $col['Field'], 'error' => $r); $upload = false; } } } //if so.. do upload if ($upload === true) { if ($value = Utils::uploadFile($name, $value, $options)) { $row_data[] = array('field' => $options['col_name'], 'value' => $value); if (isset($options['thumbnails'])) { foreach ($options['thumbnails'] as $thumb) { $src = WEB_ROOT . 'files/' . $options['table'] . '/' . $options['col_name'] . '/' . $value; $targ = WEB_ROOT . 'files/' . $options['table'] . '/' . $thumb['output_directory'] . '/image_' . $options['id'] . '.jpg'; Utils::createThumb($src, $targ, $thumb['height'], $thumb['width'], array('quality' => $thumb['quality'], 'mode' => $thumb['mode'])); } } } elseif (isset($_POST[$name . '_delete']) && $_POST[$name . '_delete']) { $row_data[] = array('field' => $options['col_name'], 'value' => ''); } } } $col_ready = true; break; case $module == 'position': //if we are a position column $where = ''; if (strlen($q_col['process_config']) > 1) { $config = _ControllerFront::parseConfig($q_col['process_config']); } else { if (isset($config)) { unset($config); } } $value = $_REQUEST[$this->_name_space . $col['Field']]; $nullable = AdaptorMysql::isNullable($this->table, $config['col_constraint']); if ($this->query_action == "update") { //check for constraints from config if (isset($config['col_constraint'])) { //try to find in row_data $foundrow = false; foreach ($row_data as $temprow) { if ($temprow['field'] == $config['col_constraint']) { $foundrow = true; if ($nullable && Utils::isNull($_REQUEST[$this->_name_space . $config['col_constraint']])) { $_v = "IS NULL"; } else { $_v = " = '" . $temprow['value'] . "'"; } $where = "WHERE `{$config['col_constraint']}` " . $_v; } } if (!$foundrow) { //check for the $_REQUEST if ($nullable && Utils::isNull($_REQUEST[$this->_name_space . $config['col_constraint']])) { $_v = "IS NULL"; } else { $_v = " = '" . $_REQUEST[$this->_name_space . $config['col_constraint']] . "'"; } $where = "WHERE `{$config['col_constraint']}` " . $_v; } } _ControllerFront::sortPosition($this->table, "SELECT id FROM `{$this->table}` {$where} ORDER BY `{$col['Field']}`", $this->id, $value, $col['Field']); } if ($this->query_action == "insert") { //check for constraints from config if (isset($config)) { //is null if ($nullable && Utils::isNull($_REQUEST[$this->_name_space . $config['col_constraint']])) { $_v = "IS NULL"; } else { $_v = " = '" . $_REQUEST[$this->_name_space . $config['col_constraint']] . "'"; } $where = "WHERE `{$config['col_constraint']}` " . $_v . " "; } $sql = "SELECT max(`{$col['Field']}`) AS position FROM `{$this->table}` {$where}"; trigger_error($sql); $q_pos = $this->db->queryRow($sql); $row_data[] = array("field" => $col['Field'], "value" => $q_pos['position'] + 1); } $col_ready = true; break; case $module == 'slug': function checkSlug($slug, $options) { if ($slug != '*' && ($q = $options['db']->query("\n\t\t\t\t\t\t\tSELECT " . $options['col_name'] . "\n\t\t\t\t\t\t\tFROM " . $options['table'] . "\n\t\t\t\t\t\t\tWHERE id != '" . $options['id'] . "'\n\t\t\t\t\t\t\t\tAND " . $options['col_name'] . " = '" . $slug . "'" . $options['where'] . "\n\t\t\t\t\t\t"))) { if (is_numeric($i = substr($slug, strrpos($slug, '_') + 1))) { $slug = substr($slug, 0, strrpos($slug, '_') + 1) . ($i + 1); } else { $slug .= '_1'; } return checkSlug($slug, $options); } else { return $slug; } } if (strlen($q_col['process_config']) > 1) { $config = _ControllerFront::parseConfig($q_col['process_config']); } else { if (isset($config)) { unset($config); } } $value = $_REQUEST[$this->_name_space . $col['Field']]; if ($this->query_action == 'insert') { $this->id = mysql_insert_id(); //$q_pos = $this->db->queryRow("SELECT max($col[Field]) FROM `$this->table` $where"); //$this->id = $q_pos[0] + 1; } //check for constraints from config $where = ""; if (isset($config['col_constraint'])) { //try to find in row_data foreach ($row_data as $temprow) { if ($temprow['field'] == $config['col_constraint']) { $where = " AND `" . $temprow['field'] . "` = '" . $temprow['value'] . "' "; } } } $value = checkSlug($value, array('col_name' => $col['Field'], 'table' => $this->table, 'id' => $this->id, 'where' => $where, 'db' => $this->db)); /*if ($value != '*' && $q = $this->db->query(" SELECT ".$col['Field']." FROM ".$this->table." WHERE id != '".$this->id."' AND ".$col['Field']." = '".$value."'".$where." ")) { if (is_numeric($i = substr($value,strrpos($value,'_')+1))) $value = substr($value,0,strrpos($value,'_')+1).($i+1); else $value = $value.'_1'; }*/ $row_data[] = array("field" => $col['Field'], "value" => $value); $col_ready = true; break; case $module == 'timestamp': $row_data[] = array("field" => $col['Field'], "value" => $col['Field'] == 'created' && $_REQUEST[$this->_name_space . $col['Field']] ? $_REQUEST[$this->_name_space . $col['Field']] : Utils::now()); $col_ready = true; break; case $col_type == "datetime" || $col_type == "timestamp": //check null if (isset($_REQUEST[$this->_name_space . $col['Field'] . '_isnull'])) { $row_data[] = array('field' => $col['Field'], 'value' => null); } else { $row_data[] = array("field" => $col['Field'], "value" => Utils::assembleDateTime($col['Field'], $this->_name_space)); } $col_ready = true; break; case $col_type == "date": // if (isset($_REQUEST[$this->_name_space . $col['Field'] . '_isnull'])) { $row_data[] = array('field' => $col['Field'], 'value' => null); } else { $row_data[] = array("field" => $col['Field'], "value" => Utils::assembleDate($col['Field'], $this->_name_space)); } $col_ready = true; break; case $col_type == "time": $row_data[] = array("field" => $col['Field'], "value" => Utils::assembleTime($col['Field'], $this->_name_space)); $col_ready = true; break; default: //if we are a generic column if (isset($_REQUEST[$this->_name_space . $col['Field']])) { $row_data[] = array("field" => $col['Field'], "value" => $_REQUEST[$this->_name_space . $col['Field']]); } break; } } $q_table = $this->db->queryRow("SELECT * FROM " . BLACKBIRD_TABLE_PREFIX . "tables WHERE table_name = '{$this->table}'"); if (strlen($q_table['process_module']) > 3) { //$this->cms->pluginTableProcess($this->table,$this->id,$this->query_action); } else { if (count($this->errorData) == 0) { //check nullable foreach ($row_data as $key => $row) { if (Utils::isNull($row['value'])) { if (AdaptorMysql::isNullable($this->table, $row['field'])) { $row_data[$key]['value'] = null; } } } if ($this->query_action == "insert") { $sql = $this->db->insert($this->table, $row_data); $this->id = mysql_insert_id(); } if ($this->query_action == "update") { $key = AdaptorMysql::getPrimaryKey($this->table); $sql = $this->db->update($this->table, $row_data, $key, $this->id); } $row_data = array(); $row_data[] = array('field' => 'table_name', 'value' => $this->table); $row_data[] = array('field' => 'record_id', 'value' => $this->id); $row_data[] = array('field' => 'action', 'value' => $this->query_action); $row_data[] = array('field' => 'user_id', 'value' => _ControllerFront::$session->u_id); $row_data[] = array('field' => 'sql', 'value' => $sql); $row_data[] = array('field' => 'session_id', 'value' => session_id()); $this->db->insert(BLACKBIRD_TABLE_PREFIX . 'history', $row_data); $this->view(array('data' => array('mode' => $this->mode, 'query_action' => $this->query_action, 'channel' => $this->channel, 'name_space' => $_POST['name_space'], 'table' => $this->table, 'id' => $this->id))); } else { //$GLOBALS['errors'] = $this->errorData; $this->view(array('view' => '/_errors/remote', 'data' => array('mode' => $this->mode, 'query_action' => $this->query_action, 'channel' => $this->channel, 'name_space' => $_POST['name_space'], 'table' => $this->table, 'id' => $this->id, 'errors' => $this->errorData))); } } //if we have warnings, feed them back }