<?php
require_once '../../../jq-config.php';
require_once ABSPATH . "php/jqGridPdo.php";
$country = $_GET['q'];
if ($country) {
try {
$conn = new PDO(DB_DSN, DB_USER, DB_PASSWORD);
$SQL = "SELECT DISTINCT(City) id, City value FROM customers WHERE Country='" . $country . "' ORDER BY City";
$collation = jqGridDB::query($conn, "SET NAMES utf8");
$city = jqGridDB::query($conn, $SQL);
$result = jqGridDB::fetch_object($city, true, $conn);
echo json_encode($result);
} catch (Exception $e) {
echo $e->getMessage();
}
}
<?php
require_once '../../../jq-config.php';
require_once ABSPATH . "php/jqUtils.php";
require_once ABSPATH . "php/jqGridPdo.php";
require_once ABSPATH . "php/jqChart.php";
ini_set("display_errors", "1");
$conn = new PDO(DB_DSN, DB_USER, DB_PASSWORD);
// Tell the db that we use utf-8
jqGridDB::query($conn, "SET NAMES utf8");
$chart = new jqChart($conn);
$chart->setChartOptions(array("defaultSeriesType" => "line"))->setTitle(array('text' => 'Freight by Month 1997', "x" => -20))->setyAxis(array("title" => array("text" => "Freight")))->setTooltip(array("formatter" => "function(){return '<b>'+ this.series.name +'</b><br/>'+this.x +': '+ this.y;}"))->addSeries('Blauer See Delikatessen', "SELECT MONTH(OrderDate), SUM(Freight) FROM orders WHERE CustomerID =? AND OrderDate BETWEEN '1997-01-01' AND '1997-12-31' GROUP BY MONTH(OrderDate)", array('BERGS'))->addSeries('White Clover Markets', "SELECT MONTH(OrderDate), SUM(Freight) FROM orders WHERE CustomerID =? AND OrderDate BETWEEN '1997-01-01' AND '1997-12-31' GROUP BY MONTH(OrderDate)", array('WHITC'));
echo $chart->renderChart('', true, 700, 350);
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;
}
/**
* 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 getEvents($start, $end)
{
if (!empty($this->user_id) && !empty($this->table)) {
$sql = "SELECT ";
$i = 0;
foreach ($this->dbmap as $k => $v) {
$sql .= $i == 0 ? $k . ' AS ' . $v : ', ' . $k . ' AS ' . $v;
$i++;
}
if (strlen($this->searchwhere) > 0) {
$sql .= ' FROM ' . $this->table . ' WHERE ' . $this->searchwhere . ' AND (user_id = ?) ORDER BY start DESC';
$params = $this->searchdata;
$params[] = (int) $this->user_id;
} else {
$sql .= ' FROM ' . $this->table . ' WHERE user_id = ? AND start >= ? AND start <= ? ORDER BY start';
$params = array((int) $this->user_id, (int) $start, (int) $end);
}
$query = jqGridDB::prepare($this->db, $sql, $params);
$ret = jqGridDB::execute($query);
$ev = array();
while ($row = jqGridDB::fetch_assoc($query, $this->db)) {
$row[$this->dbmap['all_day']] = $row[$this->dbmap['all_day']] == 1 ? true : false;
$ev[] = $row;
}
jqGridDB::closeCursor($query);
return $ev;
} else {
return false;
}
}
/**
* 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;
}
/**
* 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;
}
}
}
// 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()));
}
}
}
include_once "../ip-config.php";
require_once '../jq-config.php';
// include the jqGrid Class
require_once ABSPATH . "php/jqGrid.php";
// include the driver class
require_once ABSPATH . "php/jqGridPdo.php";
// Connection to the server
$conn = new PDO(DB_DSN, DB_USER, DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");
$rowid = jqGridUtils::Strip($_REQUEST["rowid"]);
if (!$rowid) {
die("Missed parameters");
}
// Get details
$SQL = "SELECT notes FROM project, priority, status WHERE id =" . (int) $rowid;
$qres = jqGridDB::query($conn, $SQL);
$result = jqGridDB::fetch_assoc($qres, $conn);
$s = "<table width='60%' align='left'><tbody>";
$s .= "<tr><td width='15%'><b>Notes</b></td><td width='85%'>" . $result["notes"] . "</td></tr>";
$s .= "<tr><td width='15%'><b> </b></td><td width='85%'> </td></tr>";
$s .= "<tr><td width='15%'><b>Priority</b></td><td width='85%'>" . $result["priority"] . "</td></tr>";
$s .= "<tr><td width='15%'><b> </b></td><td width='85%'> </td></tr>";
$s .= "<tr><td width='15%'><b>Status</b></td><td width='85%'>" . $result["status"] . "</td></tr>";
$s .= "<tr><td width='15%'><b> </b></td><td width='85%'> </td></tr>";
$s .= "</tbody></table>";
echo $s;
jqGridDB::closeCursor($qres);
?>
public function getEvents($start, $end)
{
if (!empty($this->user_id) && !empty($this->table)) {
$sql = "SELECT ";
$i = 0;
foreach ($this->dbmap as $k => $v) {
$sql .= $i == 0 ? $k . ' AS ' . $v : ', ' . $k . ' AS ' . $v;
$i++;
}
$sql .= ' FROM ' . $this->table . ' WHERE ';
if ($this->wherecond && strlen($this->wherecond) > 0) {
$pos = stripos($this->wherecond, 'where');
if ($pos !== false) {
$this->wherecond = substr_replace($this->wherecond, "", $pos, 5);
}
$sql .= $this->wherecond . ' AND ';
}
$sqluser = "******";
if (is_array($this->user_id)) {
foreach ($this->user_id as $k => $v) {
if ($k != 0) {
$sqluser .= " OR user_id = ? ";
} else {
$sqluser .= " user_id = ? ";
}
}
} else {
$sqluser .= " user_id = ? ";
}
$sqluser .= ")";
if (strlen($this->searchwhere) > 0) {
$sql .= $this->searchwhere . ' AND ' . $sqluser . ' ORDER BY start DESC';
$params = $this->whereparams;
foreach ($this->searchdata as $k => $v) {
$params[] = $v;
}
//$params[]
if (is_array($this->user_id)) {
foreach ($this->user_id as $k => $v) {
$params[] = $v;
}
} else {
$params[] = $this->user_id;
}
} else {
$sql .= $sqluser . ' AND start >= ? AND start <= ? ORDER BY start';
$params = $this->whereparams;
//$params[]
if (is_array($this->user_id)) {
foreach ($this->user_id as $k => $v) {
$params[] = $v;
}
} else {
$params[] = $this->user_id;
}
$params[] = (int) $start;
$params[] = (int) $end;
}
$query = jqGridDB::prepare($this->db, $sql, $params);
$ret = jqGridDB::execute($query, $params);
$ev = array();
while ($row = jqGridDB::fetch_assoc($query, $this->db)) {
$row[$this->dbmap['all_day']] = $row[$this->dbmap['all_day']] == 1 ? true : false;
$ev[] = $row;
}
jqGridDB::closeCursor($query);
return $ev;
} else {
return false;
}
}
public static function closeCursor($sql)
{
self::$acnt = 0;
return true;
}
