/** * 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); }