/** * Construct the column model of the grid. The model can be passed as array * or can be constructed from sql. See _setSQL() to determine which SQL is * used. The method try to determine the primary key and if it is found is * set as key:true to the appropriate field. If the primary key can not be * determined set the first field as key:true in the colModel. * Return true on success. * @see _setSQL * @param array $model if set construct the model ignoring the SQL command * @param array $params if a sql command is used parametters passed to the SQL * @param array $labels if this parameter is set it set the labels in colModel. * The array should be associative which key value correspond to the name of * colModel * @return boolean */ public function setColModel(array $model = null, array $params = null, array $labels = null) { $goper = $this->oper ? $this->oper : 'nooper'; // excel, nooper, !(in_array....) if ($goper == 'nooper' || $goper == $this->GridParams["excel"] || $goper == "pdf" || $goper == "csv") { $runme = true; } else { $runme = !in_array($goper, array_values($this->GridParams)); } if ($runme) { if (is_array($model) && count($model) > 0) { $this->colModel = $model; return true; } $sql = null; $sqlId = $this->_setSQL(); if (!$sqlId) { return false; } $nof = $this->dbtype == 'sqlite' || $this->dbtype == 'db2' || $this->dbtype == 'array' || $this->dbtype == 'mongodb' ? 1 : 0; //$sql = $this->parseSql($sqlId, $params); $ret = $this->execute($sqlId, $params, $sql, true, $nof, 0); //$this->execute($sqlId, $params, $sql, $limit, $nrows, $offset) if ($ret) { if (is_array($labels) && count($labels) > 0) { $names = true; } else { $names = false; } $colcount = jqGridDB::columnCount($sql); for ($i = 0; $i < $colcount; $i++) { $meta = jqGridDB::getColumnMeta($i, $sql); if (strtolower($meta['name']) == 'jqgrid_row') { continue; } //Oracle, IBM DB2 if ($names && array_key_exists($meta['name'], $labels)) { $this->colModel[] = array('label' => $labels[$meta['name']], 'name' => $meta['name'], 'index' => $meta['name'], 'sorttype' => jqGridDB::MetaType($meta, $this->dbtype)); } else { $this->colModel[] = array('name' => $meta['name'], 'index' => $meta['name'], 'sorttype' => jqGridDB::MetaType($meta, $this->dbtype)); } } jqGridDB::closeCursor($sql); if ($this->primaryKey) { $pk = $this->primaryKey; } else { $pk = jqGridDB::getPrimaryKey($this->table, $this->pdo, $this->dbtype); $this->primaryKey = $pk; } if ($pk) { $this->setColProperty($pk, array("key" => true)); } else { $this->colModel[0] = array_merge($this->colModel[0], array("key" => true)); } } else { $this->errorMessage = jqGridDB::errorMessage($sql); if ($this->showError) { $this->sendErrorHeader(); } return $ret; } } if ($goper == $this->GridParams["excel"]) { // notify all other set methods not to be executed $this->runSetCommands = false; } else { if (!$runme) { $this->runSetCommands = false; } } return true; }
/** * Return a array representation of the sql query. Also return only the * the values of the first column * @param string $sql The sql query string * @param array $params array of parameters passed to the query * @param mixed $limit the number of records to retrieve. if false - all * @param number $offset how many record to skip. 0 - none * @return array of the values of the first column of the query */ protected function getSQLSerie($sql, $params = null, $limit = false, $offset = 0) { $retarr = array(); if ($this->dbtype != 'chartarray' && $this->conn) { try { if ($limit && $limit > 0) { $sql = jqGridDB::limit($sql, $this->dbtype, $limit, $offset); } $sersql = jqGridDB::prepare($this->conn, $sql, $params, true); jqGridDB::execute($sersql, $params); $xy = false; $ncols = jqGridDB::columnCount($sersql); if ($ncols > 1) { $xy = true; } for ($i = 0; $i < $ncols; $i++) { $field = jqGridDB::getColumnMeta($i, $sersql); $typearr[$i] = jqGridDB::MetaType($field, $this->dbtype); } while ($r = jqGridDB::fetch_num($sersql)) { $retarr[] = $xy ? array($this->convertVar($r[0], $typearr[0]), $this->convertVar($r[1], $typearr[1])) : $this->convertVar($r[0], $typearr[0]); } jqGridDB::closeCursor($sersql); } catch (Exception $e) { echo $e->getMessage(); return false; } } return $retarr; }
public function setColModel(array $model = null, array $params = null, array $labels = null) { $oper = $this->GridParams["oper"]; $goper = jqGridUtils::GetParam($oper, 'nooper'); if ($goper == 'nooper' || $goper == $this->GridParams["excel"]) { $runme = true; } else { $runme = !in_array($goper, array_values($this->GridParams)); } if ($runme) { if (is_array($model) && count($model) > 0) { $this->colModel = $model; return true; } $sql = null; $sqlId = $this->_setSQL(); if (!$sqlId) { return false; } $nof = $this->dbtype == 'sqlite' || $this->dbtype == 'db2' ? 1 : 0; $ret = $this->execute($sqlId, $params, $sql, true, $nof, 0); if ($ret === true) { if (is_array($labels) && count($labels) > 0) { $names = true; } else { $names = false; } $colcount = jqGridDB::columnCount($sql); for ($i = 0; $i < $colcount; $i++) { $meta = jqGridDB::getColumnMeta($i, $sql); if (strtolower($meta['name']) == 'jqgrid_row') { continue; } if ($names && array_key_exists($meta['name'], $labels)) { $this->colModel[] = array('label' => $labels[$meta['name']], 'name' => $meta['name'], 'index' => $meta['name'], 'editable' => true, 'sorttype' => jqGridDB::MetaType($meta, $this->dbtype)); } else { $this->colModel[] = array('name' => $meta['name'], 'index' => $meta['name'], 'editable' => true, 'sorttype' => jqGridDB::MetaType($meta, $this->dbtype)); } } jqGridDB::closeCursor($sql); if ($this->primaryKey) { $pk = $this->primaryKey; } else { $pk = jqGridDB::getPrimaryKey($this->table, $this->pdo, $this->dbtype); $this->primaryKey = $pk; } if ($pk) { $this->setColProperty($pk, array("key" => true)); } else { $this->colModel[0] = array_merge($this->colModel[0], array("key" => true)); } } else { return false; } } if ($goper == $this->GridParams["excel"]) { $this->runSetCommands = false; } else { if (!$runme) { $this->runSetCommands = false; } } return true; }
} } // var_dump($action); if ($action == 'genform') { if ($dbtype == 'mysql' || $dbtype == 'pgsql' || $dbtype == 'sqlite') { include '../php/jqGridPdo.php'; ini_set("display_errors", 1); $info = parse_connection_string($connstr); try { $conn = new PDO($dbtype . ':host=' . $info->host . ';dbname=' . $info->database, $info->user_id, $info->password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sqlstr = jqGridDB::limit($sqlstr, $dbtype, 1, 0); //var_dump($sqlstr); $stmt = jqGridDB::prepare($conn, $sqlstr, null); $ret = jqGridDB::execute($stmt); $metasql = array(); if ($ret) { $meta = array(); $colcount = jqGridDB::columnCount($stmt, null); for ($i = 0; $i < $colcount; $i++) { $meta = jqGridDB::getColumnMeta($i, $stmt); $metasql[] = array("name" => $meta['name'], "type" => jqGridDB::MetaType($meta, $dbtype), "len" => $meta['len']); } } jqGridDB::closeCursor($stmt); echo json_encode(array("msg" => "success", "rows" => $metasql)); } catch (Exception $e) { echo json_encode(array("msg" => $e->getMessage())); } } }