/**
* Paginate and return result
*
* @param Table $table
* @return array
*/
public function paginate(Table $table)
{
$ands = [];
foreach ($this->sqlAnds as $filter => $ors) {
$ands[] = '(' . join(') OR (', $ors) . ')';
}
$where = '';
if (strlen($this->initialWhere) > 0) {
$where = ' WHERE (' . $this->initialWhere . ')';
if (count($ands)) {
$where .= ' AND (' . join(') AND (', $ands) . ')';
}
} else {
if (count($ands)) {
$where = ' WHERE (' . join(') AND (', $ands) . ')';
}
}
$preparedParams = [];
foreach ($this->sqlParams as $name => $value) {
if ($value instanceof \DateTime) {
$preparedParams[$name] = $value->format('Y-m-d H:i:s');
} else {
$preparedParams[$name] = $value;
}
}
$db = $this->getPdo();
$mapper = $table->getMapper();
if (!$mapper) {
throw new \Exception("Data 'mapper' is required when using PDOAdapter");
}
$sql = "SELECT COUNT(*) AS count" . " FROM " . $this->initialFrom . " " . $where . " ";
$sth = $db->prepare($sql);
$sth->execute($preparedParams);
$result = $sth->fetchAll(\PDO::FETCH_ASSOC);
$count = $result[0]['count'];
$table->calculatePageParams($count);
if ($this->sqlOrderBy) {
$where .= ' ORDER BY ' . $this->sqlOrderBy . ' ';
}
if ($table->getPageSize() > 0) {
$where .= ' LIMIT ' . $table->getPageSize() . ' ';
$where .= ' OFFSET ' . $table->getPageSize() * ($table->getPageNumber() - 1) . ' ';
}
$sql = "SELECT " . $this->initialSelect . " " . " FROM " . $this->initialFrom . " " . $where . " ";
$sth = $db->prepare($sql);
$sth->execute($preparedParams);
$result = $sth->fetchAll(\PDO::FETCH_ASSOC);
$data = [];
foreach ($result as $row) {
foreach ($table->getColumns() as $columnId => $columnParams) {
$value = $row[$columnId];
if ($value === null) {
continue;
}
if ($columnParams['type'] == Table::TYPE_DATETIME) {
if (is_string($value)) {
if ($this->getDbTimezone()) {
$dt = new \DateTime($value, new \DateTimeZone($this->getDbTimezone()));
} else {
$dt = new \DateTime($value);
}
} else {
if (is_int($value)) {
$dt = new \DateTime('@' . $value);
} else {
$dt = $value;
}
}
if (date_default_timezone_get()) {
$dt->setTimezone(new \DateTimeZone(date_default_timezone_get()));
}
$row[$columnId] = $dt;
}
}
$data[] = $mapper ? $mapper($row) : $row;
}
return $data;
}
/**
* Paginate and return result
*
* @param Table $table
* @return array
*/
public function paginate(Table $table)
{
$table->calculatePageParams(count($this->data));
if ($table->getPageSize() > 0) {
$offset = $table->getPageSize() * ($table->getPageNumber() - 1);
$length = $table->getPageSize();
$data = array_slice($this->data, $offset, $length);
} else {
$data = $this->data;
}
$mapper = $table->getMapper();
$result = [];
foreach ($data as $row) {
foreach ($table->getColumns() as $columnId => $columnParams) {
$value = $row[$columnId];
if ($value === null) {
continue;
}
if ($columnParams['type'] == Table::TYPE_DATETIME) {
if (is_string($value)) {
if ($this->getDbTimezone()) {
$dt = new \DateTime($value, new \DateTimeZone($this->getDbTimezone()));
} else {
$dt = new \DateTime($value);
}
} else {
if (is_int($value)) {
$dt = new \DateTime('@' . $value);
} else {
$dt = $value;
}
}
if (date_default_timezone_get()) {
$dt->setTimezone(new \DateTimeZone(date_default_timezone_get()));
}
$row[$columnId] = $dt;
}
}
$result[] = $mapper ? $mapper($row) : $row;
}
return $result;
}
/**
* Sort data
*
* @param Table $table
*/
public function sort(Table $table)
{
$column = $table->getSortColumn();
$dir = $table->getSortDir();
if (!$column) {
return;
}
$sqlId = null;
foreach ($table->getColumns() as $id => $params) {
if ($id == $column) {
$sqlId = $params['sql_id'];
break;
}
}
if (!$sqlId) {
throw new \Exception("No 'sql_id' for column: {$column}");
}
$qb = $this->getQueryBuilder();
$qb->addOrderBy($sqlId, $dir);
}
/**
* Sort data
*
* @param Table $table
* @return GenericDBAdapter
*/
public function sort(Table $table)
{
$column = $table->getSortColumn();
$dir = $table->getSortDir();
if (!$column) {
return;
}
$sqlId = null;
foreach ($table->getColumns() as $id => $params) {
if ($id == $column) {
$sqlId = $params['sql_id'];
break;
}
}
if (!$sqlId) {
throw new \Exception("No 'sql_id' for column: {$column}");
}
$this->sqlOrderBy = $sqlId . " " . $dir;
return $this;
}
/**
* Sort data
*
* @param Table $table
*/
public function sort(Table $table)
{
$columns = $table->getColumns();
$column = $table->getSortColumn();
$dir = $table->getSortDir();
if (!$column) {
return;
}
$type = $columns[$column]['type'];
$cmp = function ($a, $b) use($column, $dir, $type) {
$a = $a[$column];
$b = $b[$column];
if ($a === null && $b !== null) {
return $dir == Table::DIR_ASC ? -1 : 1;
}
if ($a !== null && $b === null) {
return $dir == Table::DIR_ASC ? 1 : -1;
}
if ($a === null && $b === null) {
return 0;
}
switch ($type) {
case Table::TYPE_BOOLEAN:
$a = $a ? 1 : 0;
$b = $b ? 1 : 0;
case Table::TYPE_INTEGER:
case Table::TYPE_FLOAT:
case Table::TYPE_DATETIME:
if ($a == $b) {
return 0;
}
if ($dir == Table::DIR_ASC) {
return $a < $b ? -1 : 1;
}
return $a < $b ? 1 : -1;
case Table::TYPE_STRING:
if ($dir == Table::DIR_ASC) {
return strcmp($a, $b);
}
return strcmp($b, $a);
default:
throw new \Exception("Unknown field type: {$type}");
}
};
if (!uasort($this->data, $cmp)) {
throw new \Exception('PHP sort failed');
}
}
/**
* Sort data
*
* @param Table $table
*/
public function sort(Table $table)
{
$column = $table->getSortColumn();
$dir = $table->getSortDir();
if (!$column) {
return;
}
$field = null;
foreach ($table->getColumns() as $id => $params) {
if ($id == $column) {
$field = $params['field_name'];
break;
}
}
if (!$field) {
throw new \Exception("No 'field_name' for column: {$column}");
}
$qb = $this->getQueryBuilder();
$qb->sort($field, $dir);
}
