/**
* @param bool $paginated
* @param string $filter
* @param string $orderBy
* @param string $order
* @return \Zend\Db\ResultSet\ResultSet|Paginator
*/
public function fetchAll($paginated = false, $filter = '', $orderBy = 'voucherNo', $order = 'ASC')
{
$view = 'vw_account_receivable';
if ($paginated) {
$select = new Select($view);
$select->order($orderBy . ' ' . $order);
$where = new Where();
$where->literal("concat_ws(' ',description, voucherNo, Type, amount, voucherDate, currencyCode) LIKE ?", '%' . $filter . '%')->and->equalTo('depositBy', $this->staffId);
$select->where($where);
return $this->paginateWith($select);
}
$tableGateway = new TableGateway($view, $this->adapter);
return $tableGateway->select(array('depositBy' => $this->staffId));
}
/**
* @param $filter
* @param $orderBy
* @param $order
* @param string $source
* @return Paginator
* @throws \Exception
*/
public function paginate($filter, $orderBy, $order, $source = '')
{
try {
$metadata = new Metadata($this->adapter);
if (empty($source)) {
$source = $this->table;
}
$columns = $metadata->getColumnNames($source);
$select = new Select($source);
$select->order($orderBy . ' ' . $order);
if (!empty($filter)) {
$query = "CONCAT_WS(' '," . implode(',', $columns) . ') LIKE ?';
$where = new Where();
$where->literal($query, '%' . $filter . '%');
$select->where($where);
}
$paginatorAdapter = new DbSelect($select, $this->adapter);
return new Paginator($paginatorAdapter);
} catch (\Exception $ex) {
throw $ex;
}
}
/**
* @param DatagridFilter $filter
* @throws \Exception
*/
public function applyFilter(DatagridFilter $filter)
{
$select = $this->getSelect();
$adapter = $this->getSql()->getAdapter();
$qi = function ($name) use($adapter) {
return $adapter->getPlatform()->quoteIdentifier($name);
};
$col = $filter->getColumn();
if (!$col instanceof Column\Select) {
throw new \Exception('This column cannot be filtered: ' . $col->getUniqueId());
}
$colString = $col->getSelectPart1();
if ($col->getSelectPart2() != '') {
$colString .= '.' . $col->getSelectPart2();
}
if ($col instanceof Column\Select && $col->hasFilterSelectExpression()) {
$colString = sprintf($col->getFilterSelectExpression(), $colString);
}
$values = $filter->getValues();
$wheres = [];
foreach ($values as $value) {
$where = new Where();
switch ($filter->getOperator()) {
case DatagridFilter::LIKE:
$wheres[] = $where->like($colString, '%' . $value . '%');
break;
case DatagridFilter::LIKE_LEFT:
$wheres[] = $where->like($colString, '%' . $value);
break;
case DatagridFilter::LIKE_RIGHT:
$wheres[] = $where->like($colString, $value . '%');
break;
case DatagridFilter::NOT_LIKE:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value . '%']);
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value]);
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', [$value . '%']);
break;
case DatagridFilter::EQUAL:
$wheres[] = $where->equalTo($colString, $value);
break;
case DatagridFilter::NOT_EQUAL:
$wheres[] = $where->notEqualTo($colString, $value);
break;
case DatagridFilter::GREATER_EQUAL:
$wheres[] = $where->greaterThanOrEqualTo($colString, $value);
break;
case DatagridFilter::GREATER:
$wheres[] = $where->greaterThan($colString, $value);
break;
case DatagridFilter::LESS_EQUAL:
$wheres[] = $where->lessThanOrEqualTo($colString, $value);
break;
case DatagridFilter::LESS:
$wheres[] = $where->lessThan($colString, $value);
break;
case DatagridFilter::BETWEEN:
$wheres[] = $where->between($colString, $values[0], $values[1]);
break 2;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
if (!empty($wheres)) {
$set = new PredicateSet($wheres, PredicateSet::OP_OR);
$select->where->andPredicate($set);
}
}
public function init($params, $table, $whereSql = '')
{
$result['draw'] = (int) $params['draw'];
$columns = $this->_extractColumns($params);
$sql = new Sql($this->adapterDb);
$where = new Where();
$select = $sql->select()->from($table)->columns(array('total' => new Expression('COUNT(*)')));
if ($whereSql) {
$where->literal($whereSql);
$select->where($where);
}
$statement = $sql->prepareStatementForSqlObject($select);
$resultsSql = $statement->execute();
$result['recordsTotal'] = (int) $resultsSql->current()['total'];
$select = $sql->select()->from($table)->columns($columns);
$globalSearch = [];
if ($search = $params['search']['value']) {
foreach ($columns as $col) {
$globalSearch[] = "`{$col}` LIKE '%{$search}%'";
}
}
if (!empty($globalSearch)) {
$globalSearchSql = implode(' OR ', $globalSearch);
$where->literal("({$globalSearchSql})");
}
$columnSearch = [];
foreach ($params['columns'] as $index => $column) {
if ($column['search']['value']) {
if ($params['search']['regex'] == 'false') {
$columnSearch[] = "`{$columns[$index]}` LIKE '%{$column['search']['value']}%'";
} else {
$columnSearch[] = "`{$columns[$index]}` = '{$column['search']['value']}'";
}
}
}
if (!empty($columnSearch)) {
$columnSearchSql = implode(' AND ', $columnSearch);
$where->literal("({$columnSearchSql})");
}
if ($whereSql) {
$where->literal($whereSql);
}
$select->where($where);
if (isset($params['order'])) {
$arrayOrder = [];
foreach ($params['order'] as $order) {
$arrayOrder[$columns[$order['column']]] = $order['dir'];
}
$select->order($arrayOrder);
}
// die($select->getSqlString());
$statement = $sql->prepareStatementForSqlObject($select);
$resultsSql = $statement->execute();
$resultSet = new ResultSet();
$resultSet->initialize($resultsSql);
$result['recordsFiltered'] = (int) $resultSet->count();
$dbSelect = new DbSelect($select, $this->adapterDb);
$paginator = new Paginator($dbSelect);
$paginator->setItemCountPerPage($params['length']);
$paginator->setCurrentPageNumber($params['start'] / $params['length'] + 1);
$array = [];
foreach ($paginator as $index => $data) {
foreach ($data as $value) {
$array[$index][] = $value;
}
}
$result['data'] = $array;
$this->data = $result;
return $this;
}
/**
* @param DatagridFilter $filter
* @throws \InvalidArgumentException
*/
public function applyFilter(DatagridFilter $filter)
{
$select = $this->getSelect();
$adapter = $this->getSql()->getAdapter();
$qi = function ($name) use($adapter) {
return $adapter->getPlatform()->quoteIdentifier($name);
};
$column = $filter->getColumn();
$colString = $column->getSelectPart1();
if ($column->getSelectPart2() != '') {
$colString .= '.' . $column->getSelectPart2();
}
if ($column instanceof Column\Select && $column->hasFilterSelectExpression()) {
$colString = sprintf($column->getFilterSelectExpression(), $colString);
}
$values = $filter->getValues();
$filterSelectOptions = $column->getFilterSelectOptions();
$wheres = array();
if ($filter->getColumn()->getType() instanceof Column\Type\DateTime && $filter->getColumn()->getType()->isDaterangePickerEnabled() === true) {
$where = new Where();
$wheres[] = $where->between($colString, $values[0], $values[1]);
if (count($wheres) > 0) {
$set = new PredicateSet($wheres, PredicateSet::OP_AND);
$select->where->andPredicate($set);
}
} else {
foreach ($values as $value) {
$where = new Where();
switch ($filter->getOperator()) {
case DatagridFilter::LIKE:
$wheres[] = $where->like($colString, '%' . $value . '%');
break;
case DatagridFilter::LIKE_LEFT:
$wheres[] = $where->like($colString, '%' . $value);
break;
case DatagridFilter::LIKE_RIGHT:
$wheres[] = $where->like($colString, $value . '%');
break;
case DatagridFilter::NOT_LIKE:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value . '%'));
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value));
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array($value . '%'));
break;
case DatagridFilter::EQUAL:
$wheres[] = $where->equalTo($colString, $value);
break;
case DatagridFilter::NOT_EQUAL:
$wheres[] = $where->notEqualTo($colString, $value);
break;
case DatagridFilter::GREATER_EQUAL:
$wheres[] = $where->greaterThanOrEqualTo($colString, $value);
break;
case DatagridFilter::GREATER:
$wheres[] = $where->greaterThan($colString, $value);
break;
case DatagridFilter::LESS_EQUAL:
$wheres[] = $where->lessThanOrEqualTo($colString, $value);
break;
case DatagridFilter::LESS:
$wheres[] = $where->lessThan($colString, $value);
break;
case DatagridFilter::BETWEEN:
$wheres[] = $where->between($colString, $values[0], $values[1]);
break;
case DatagridFilter::IN:
$wheres[] = $where->in($colString, (array) $value);
break;
case DatagridFilter::NOT_IN:
$wheres[] = $where->notin($colString, (array) $value);
break;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
if (count($wheres) > 0) {
$set = new PredicateSet($wheres, PredicateSet::OP_OR);
$select->where->andPredicate($set);
}
}
}