/**
*
* Construct the select used in the grid. The select element can be used in the
* editing modules, in formatter or in search module
* @param string $colname (requiered) valid colname in colmodel
* @param mixed $data can be array (with pair key value) or string which is
* the SQL command which is executed to obtain the values. The command should contain a
* minimun two fields. The first is the key and the second is the value whch will
* be displayed in the select
* @param boolean $formatter deternines that the select should be used in the
* formatter of type select. Default is true
* @param boolean $editing determines if the select should be used in editing
* modules. Deafult is true
* @param boolean $seraching determines if the select should be present in
* the search module. Deafult is true.
* @param array $defvals Set the default value if none is selected. Typically this
* is usefull in serch modules. Can be something like arrar(""=>"All");
* @return boolean
*/
public function setSelect($colname, $data, $formatter = true, $editing = true, $seraching = true, $defvals = array(), $sep = ":", $delim = ";")
{
$s1 = "";
//array();
//new stdClass();
$prop = array();
//$oper = $this->GridParams["oper"];
$goper = $this->oper ? $this->oper : 'nooper';
if ($goper == 'nooper' || $goper == $this->GridParams["excel"]) {
$runme = true;
} else {
$runme = !in_array($goper, array_values($this->GridParams));
}
if (!$this->runSetCommands && !$runme) {
return false;
}
if (count($this->colModel) > 0 && $runme) {
if (is_string($data)) {
$aset = jqGridDB::query($this->pdo, $data);
if ($aset) {
$i = 0;
$s = '';
while ($row = jqGridDB::fetch_num($aset)) {
if ($i == 0) {
$s1 .= $row[0] . $sep . $row[1];
} else {
$s1 .= $delim . $row[0] . $sep . $row[1];
}
$i++;
}
}
jqGridDB::closeCursor($aset);
} else {
if (is_array($data)) {
$i = 0;
foreach ($data as $k => $v) {
if ($i == 0) {
$s1 .= $k . $sep . $v;
} else {
$s1 .= $delim . $k . $sep . $v;
}
$i++;
}
//$s1 = $data;
}
}
if ($editing) {
$prop = array_merge($prop, array('edittype' => 'select', 'editoptions' => array('value' => $s1, 'separator' => $sep, 'delimiter' => $delim)));
}
if ($formatter) {
$prop = array_merge($prop, array('formatter' => 'select', 'editoptions' => array('value' => $s1, 'separator' => $sep, 'delimiter' => $delim)));
}
if ($seraching) {
if (is_array($defvals) && count($defvals) > 0) {
//$s1 = $defvals+$s1;
foreach ($defvals as $k => $v) {
$s1 = $k . $sep . $v . $delim . $s1;
}
}
$prop = array_merge($prop, array("stype" => "select", "searchoptions" => array("value" => $s1, 'separator' => $sep, 'delimiter' => $delim)));
}
if (count($prop) > 0) {
$this->setColProperty($colname, $prop);
}
return true;
}
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;
}
public function setSelect($colname, $data, $formatter = true, $editing = true, $seraching = true, $defvals = array())
{
$s1 = array();
$prop = array();
$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 (!$this->runSetCommands && !$runme) {
return false;
}
if (count($this->colModel) > 0 && $runme) {
if (is_string($data)) {
$aset = jqGridDB::query($this->pdo, $data);
if ($aset) {
while ($row = jqGridDB::fetch_num($aset)) {
$s1[$row[0]] = $row[1];
}
jqGridDB::closeCursor($aset);
}
} else {
if (is_array($data)) {
$s1 = $data;
}
}
if ($editing) {
$prop = array_merge($prop, array('edittype' => 'select', 'editoptions' => array('value' => $s1)));
}
if ($formatter) {
$prop = array_merge($prop, array('formatter' => 'select', 'editoptions' => array('value' => $s1)));
}
if ($seraching) {
if (is_array($defvals) && count($defvals) > 0) {
$s1 = $defvals + $s1;
}
$prop = array_merge($prop, array("stype" => "select", "searchoptions" => array("value" => $s1)));
}
if (count($prop) > 0) {
$this->setColProperty($colname, $prop);
}
return true;
}
return false;
}
/**
* 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;
}
