/**
* Process
*
* @param array $settings
* @return \Bluz\Grid\Data
*/
public function process(array $settings = [])
{
// process filters
$where = [];
if (!empty($settings['filters'])) {
foreach ($settings['filters'] as $column => $filters) {
foreach ($filters as $filter => $value) {
if ($filter == Grid\Grid::FILTER_LIKE) {
$value = '%' . $value . '%';
}
$where[] = $column . ' ' . $this->filters[$filter] . ' ' . Proxy\Db::quote($value);
}
}
}
// process orders
$orders = [];
if (!empty($settings['orders'])) {
// Obtain a list of columns
foreach ($settings['orders'] as $column => $order) {
$column = Proxy\Db::quoteIdentifier($column);
$orders[] = $column . ' ' . $order;
}
}
// process pages
$limit = ' LIMIT ' . ($settings['page'] - 1) * $settings['limit'] . ', ' . $settings['limit'];
// prepare query
$type = Proxy\Db::getOption('connect', 'type');
if (strtolower($type) == 'mysql') {
// MySQL
$dataSql = preg_replace('/SELECT\\s(.*?)\\sFROM/is', 'SELECT SQL_CALC_FOUND_ROWS $1 FROM', $this->source, 1);
$totalSql = 'SELECT FOUND_ROWS()';
} else {
// other
$dataSql = $this->source;
$totalSql = preg_replace('/SELECT\\s(.*?)\\sFROM/is', 'SELECT COUNT(*) FROM', $this->source, 1);
if (sizeof($where)) {
$totalSql .= ' WHERE ' . join(' AND ', $where);
}
}
if (sizeof($where)) {
$dataSql .= ' WHERE ' . join(' AND ', $where);
}
if (sizeof($orders)) {
$dataSql .= ' ORDER BY ' . join(', ', $orders);
}
$dataSql .= $limit;
// run queries
// use transaction to avoid errors
Proxy\Db::transaction(function () use(&$data, &$total, $dataSql, $totalSql) {
$data = Proxy\Db::fetchAll($dataSql);
$total = Proxy\Db::fetchOne($totalSql);
});
$gridData = new Grid\Data($data);
$gridData->setTotal($total);
return $gridData;
}
/**
* setFromQueryPart
*
* @param string $table
* @return $this
*/
protected function setFromQueryPart($table)
{
$table = Db::quoteIdentifier($table);
return $this->addQueryPart('from', ['table' => $table], false);
}
/**
* Set key-value pair
*
* Sets a new value for a column in a insert/update query
* $ub = new UpdateBuilder();
* $ub
* ->update('users')
* ->set('password', md5('password'))
* ->where('id = ?');
*
* @param string $key The column to set
* @param string $value The value, expression, placeholder, etc
* @return Insert|Update
*/
public function set($key, $value)
{
$this->setParameter(null, $value, \PDO::PARAM_STR);
$key = Db::quoteIdentifier($key);
return $this->addQueryPart('set', $key . ' = ?', true);
}
/**
* Deletes existing rows
*
* <code>
* Table::delete(['login' => 'Man'])
* </code>
*
* @param array $where An array of SQL WHERE clause(s)
* @return integer The number of rows deleted
* @throws Exception\DbException
*/
public static function delete(array $where)
{
if (!sizeof($where)) {
throw new DbException("Method `Table::delete()` can't delete all records in table,\n" . "please use `Db::query()` instead (of cause if you know what are you doing)");
}
$self = static::getInstance();
$where = $self->filterColumns($where);
if (!sizeof($where)) {
throw new DbException("Invalid field names of table `{$self->table}`. Please check use of `delete()` method");
}
$table = DbProxy::quoteIdentifier($self->table);
$sql = "DELETE FROM {$table}" . " WHERE " . join(' AND ', self::prepareStatement($where));
return DbProxy::query($sql, array_values($where));
}
