/** * 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; }
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; }
/** * This method is internally used to get the data. * @return array */ private function getACData() { $result = array(); if (strlen($this->SelectCommand) > 0) { $prmlen = substr_count($this->SelectCommand, "?"); if ($prmlen > 0) { $params = array(); if (strtolower($this->encoding) != 'utf-8') { $this->term = iconv("utf-8", $this->encoding . "//TRANSLIT", $this->term); } for ($i = 1; $i <= $prmlen; $i++) { switch ($this->searchType) { case 'startWith': array_push($params, $this->term . "%"); break; case 'contain': array_push($params, "%" . $this->term . "%"); break; case 'endWith': array_push($params, "%" . $this->term); break; default: array_push($params, $this->term); break; } } } else { $params = null; } if ($this->itemLength > 0 && !$this->loadAll) { $sqlCmd = jqGridDB::limit($this->SelectCommand, $this->dbtype, $this->itemLength, 0); } else { $sqlCmd = $this->SelectCommand; } $sql1 = jqGridDB::prepare($this->conn, $sqlCmd, $params, true); $ret = jqGridDB::execute($sql1, $params); $ncols = jqGridDB::columnCount($sql1); // Mysqli hack if ($this->dbtype == 'mysqli') { $fld = $sql1->field_count; //start the count from 1. First value has to be a reference to the stmt. because bind_param requires the link to $stmt as the first param. $count = 1; $fieldnames[0] =& $sql1; for ($i = 0; $i < $ncols; $i++) { $fieldnames[$i + 1] =& $res_arr[$i]; //load the fieldnames into an array. } call_user_func_array('mysqli_stmt_bind_result', $fieldnames); } while ($row = jqGridDB::fetch_num($sql1)) { if ($this->dbtype == 'mysqli') { $row = $res_arr; } if ($ncols == 1) { array_push($result, array("value" => $row[0], "label" => $row[0])); } else { if ($ncols == 2) { array_push($result, array("value" => $row[0], "label" => $row[1])); } else { if ($ncols >= 3) { array_push($result, array("value" => $row[0], "label" => $row[1], "id" => $row[2])); } } } } jqGridDB::closeCursor($sql1); } return $result; }
/** * 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; }
} } // 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())); } } }