/**
*
* @param int $days_threshold days threshold
* @param int $ca_threshold turnover threshold
* @param int $limit
* @return type
*/
public function getCustomerGeo($days_threshold = 300, $ca_threshold = 1000, $min_accuracy = 6, $limit = 1000)
{
$akilia2db = $this->configuration['synchronizer']['db_akilia2'];
$select = new Select();
$bcg = new \Zend\Db\Sql\TableIdentifier('base_customer_geo', $akilia2db);
$bc = new \Zend\Db\Sql\TableIdentifier('base_customer', $akilia2db);
$bs = new \Zend\Db\Sql\TableIdentifier('base_state', $akilia2db);
$bco = new \Zend\Db\Sql\TableIdentifier('base_country', $akilia2db);
$so = new \Zend\Db\Sql\TableIdentifier('sal_order', $akilia2db);
$sol = new \Zend\Db\Sql\TableIdentifier('sal_order_line', $akilia2db);
$select->from(["bc" => $bc], [])->join(['bcg' => $bcg], "bc.id = bcg.customer_id", [], Select::JOIN_LEFT)->join(['bs' => $bs], "bs.id = bc.state_id", [], Select::JOIN_LEFT)->join(['bco' => $bco], "bco.id = bc.country_id", [], Select::JOIN_LEFT)->join(['so' => $so], "bc.id = so.customer_id", [], Select::JOIN_INNER)->join(['sol' => $sol], "so.id = sol.order_id", [], Select::JOIN_INNER)->where('bc.flag_archived <> 1');
$columns = ['customer_id' => new Expression('bc.id'), 'name' => new Expression('bc.name'), 'street' => new Expression('bc.street'), 'street_2' => new Expression('bc.street_2'), 'street_number' => new Expression('bc.street_number'), 'state_reference' => new Expression('bs.reference'), 'state_name' => new Expression('bs.name'), 'zipcode' => new Expression('bc.zipcode'), 'city' => new Expression('bc.city'), 'country' => new Expression('bco.name'), 'accuracy' => new Expression('bcg.accuracy'), 'latitude' => new Expression('bcg.latitude'), 'longitude' => new Expression('bcg.longitude')];
$select->columns(array_merge($columns, ['total_net' => new Expression('sum(sol.price_total_net)')]), true);
$select->group($columns);
$select->having("sum(sol.price_total_net) > {$ca_threshold}");
$select->where(function (Where $where) use($min_accuracy) {
//$where->greaterThan('so.date_order', '2012-12-31');
$where->notLike('bc.name', '%FINISHED%');
$where->nest->lessThan('accuracy', $min_accuracy)->or->isNull('accuracy')->unnest;
});
$select->where(new Expression("(TO_DAYS(NOW()) - TO_DAYS(so.date_order)) < {$days_threshold}"));
if ($limit > 0) {
$select->limit($limit);
}
$store = $this->getStore($select);
$data = $store->getData()->toArray();
return $data;
}
public function getList($where = array(), $order = null, $offset = null, $limit = null)
{
if (empty($where['ProductFilterOption.productCategoryFilterOptionID'])) {
$select = new Select();
$select->from(array('b' => 'Product'));
$select->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'));
$select->where($where);
$select->offset($offset);
$select->limit($limit);
$select->order($order);
} else {
$select = $this->getSelect();
$select->columns(array())->join(array('b' => 'Product'), 'ProductFilterOption.productID = b.productID')->join(array('c' => 'ProductCategory'), 'b.productCategoryID = c.productCategoryID', array('categoryName'))->where($where)->offset($offset)->limit($limit)->group(array('ProductFilterOption.productID'))->having('count(ProductFilterOption.productID) > ' . (count($where['ProductFilterOption.productCategoryFilterOptionID']) - 1));
$select->order($order);
}
$paginator = $this->paginate($select);
$paginator->setCurrentPageNumber(ceil($offset / $limit) + 1);
//$paginator->setItemCountPerPage(1);
$products = $paginator->getCurrentItems()->getArrayCopy();
$pages = $paginator->getPages();
$productsCount = $paginator->getTotalItemCount();
foreach ($products as $k => $v) {
$products[$k]['leftTime'] = Utility::getLeftTime(time(), $v['endTime']);
}
return array('products' => $products, 'productsCount' => $productsCount, 'pages' => $pages);
}
function fetchAll($beginning, $limit)
{
$select = new Select($this->tableGateway->getTable());
$select->limit($limit)->offset($beginning);
$rowSet = $this->tableGateway->selectWith($select);
return $rowSet;
}
public function fetchPostsByFeeds($feeds, $offset = null, $limit = null)
{
$select = new Select(self::$_tableName);
if (!is_null($offset)) {
$select->offset($offset);
}
if (!is_null($limit)) {
$select->limit($limit);
}
$select->join('directus_social_feeds', 'directus_social_feeds.id = directus_social_posts.feed', ['feed_type' => 'type'])->order('directus_social_posts.datetime DESC');
$select->where->equalTo('directus_social_posts.status', 1)->equalTo('directus_social_feeds.status', 1);
$FeedWhere = new Where();
$SocialCache = new SocialCache();
foreach ($feeds as $feed) {
// Run scrape if due
$SocialCache->scrapeFeedIfDue($feed['name'], $feed['type']);
$FeedWhere->or->nest->equalTo('directus_social_feeds.name', $feed['name'])->equalTo('directus_social_feeds.type', $feed['type'])->unnest;
$select->where($FeedWhere);
}
$socialPosts = $this->selectWith($select);
$socialPosts = $socialPosts->toArray();
// Unserialize cached feed entry API-responses
foreach ($socialPosts as &$post) {
$post['data'] = json_decode($post['data'], true);
}
return $socialPosts;
}
protected function _applyLimit()
{
if ($this->_limit !== false) {
$this->_select->limit($this->_limit);
}
return $this;
}
public function getFeedByHandleAndType($handle, $type)
{
$select = new Select($this->getTable());
$select->limit(1);
$select->where->equalTo('name', $handle)->equalTo('type', $type);
$rowset = $this->selectWith($select);
return $rowset->current();
}
/**
* (non-PHPdoc)
*
* @see \MwAdmin\Service\DataTableProviderInterface::getDataTableSelect()
*/
public function getDataTableSelect($page = 1, $perPage = 10)
{
$table = $this->getDataTableGateway();
$sql = new Select($table->getTable());
$sql->order($this->getDefaultSort());
$sql->limit($perPage, $page * $perPage - $perPage);
return $sql;
}
public function getListingById($id = 1)
{
$select = new Select();
$select->from($this->tableName);
$where = new Where();
$where->equalTo('listings_id', $id);
$select->where($where);
$select->limit(1);
return $this->selectWith($select)->current();
}
public function fetchByUserAndId($user_id, $id)
{
$select = new Select($this->table);
$select->limit(1);
$select->where->equalTo('id', $id)->equalTo('user', $user_id);
$bookmarks = $this->selectWith($select)->current();
if ($bookmarks) {
$bookmarks = $bookmarks->toArray();
}
return $bookmarks;
}
public function fetchByCollectionAndName($collection, $name)
{
$select = new Select($this->table);
$select->limit(1);
$select->where->equalTo('collection', $collection)->equalTo('name', $name);
$rowset = $this->selectWith($select);
$result = $rowset->current();
if (false === $result) {
throw new \Exception('Required `directus_setting` with collection `' . $collection . '` and name `' . $name . '` not found.');
}
return $result;
}
public function getlistnews($limit)
{
$select = new Select('catelog');
$select->order('id DESC');
$select->limit($limit);
$resultSet = $this->tableGateway->selectWith($select);
$array = array();
foreach ($resultSet as $value) {
array_push($array, $value);
}
return $array;
}
/**
* Return all values from core_config_data
*
* @param string $locale Locale
* @param integer $limit Limit
*
* @return array
*/
public function getValues($locale = null, $limit = null)
{
$select = new Select();
$select->from('core_translate')->columns(array('src_id' => 'id', 'source'))->join('core_translate_locale', 'core_translate.id = core_translate_locale.core_translate_id', array('dst_id' => 'id', 'destination', 'locale'), Select::JOIN_INNER);
if (!empty($locale)) {
$select->where->equalTo('core_translate_locale.locale', $locale);
}
if (!empty($limit)) {
$select->limit($limit);
}
$select->order('core_translate.source ASC');
return $this->fetchAll($select);
}
public function fetchOneById($id)
{
$select = new Select(self::$_tableName);
$select->limit(1);
$select->where->equalTo('id', $id);
$row = $this->selectWith($select)->current();
if (!$row) {
return false;
}
$row = $row->toArray();
// The adapter's `params` column is JSON serialized.
$row['params'] = $this->jsonDecodeIfPossible($row['params']);
return $row;
}
public function getLastPhotoByAlbumId($id)
{
$id = (int) $id;
$select = new Select('photo');
//CHANGE TABLE_NAME as per needs
$select->where('album_id = ' . $id);
$select->order('id DESC');
$select->limit(1);
$rowset = $this->tableGateway->selectWith($select);
//Will get array of rows.
$row = $rowset->current();
if (!$row) {
return [];
}
return $row;
}
/**
* @param Zend\Db\Sql\Select $select
* @return Zend\Db\ResultSet
*/
public function performSelect(Select $select, $order, $paginated = false, $limit = null)
{
if ($order) {
$select->order($order);
}
if ($limit) {
$select->limit($limit);
}
if ($paginated) {
$adapter = new DbSelect($select, $this->tableGateway->getAdapter(), $this->resultSetPrototype);
$paginator = new Paginator($adapter);
return $paginator;
} else {
return $this->tableGateway->selectWith($select);
}
}
public function fetchAll($columns = null, $where = null, $limit = null, $offset = null)
{
$select = new Select();
$select->from($this->getTable());
if ($columns) {
$select->columns($columns);
}
if ($where) {
$select->where($where);
}
if ($limit) {
$select->limit((int) $limit);
}
if ($offset) {
$select->offset((int) $offset);
}
return $this->selectWith($select);
}
public function getPostsByDate($threadId, $pageNo = 1)
{
$selectPost = new Select();
$selectPost->from('post');
$selectPost->group('user_id');
$selectPost->columns(['user_id', 'postcount' => new Expression('COUNT(user_id)')]);
$predicate = new Predicate(null, Predicate::OP_AND);
$predicate->equalTo('thread_id', $threadId);
$select = new Select();
$select->from($this->tableName);
$select->order('date_added ASC');
$select->join(['po' => $selectPost], 'post.user_id = po.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
$select->join('user', 'user.user_id = post.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
$select->limit(20);
$select->offset(($pageNo - 1) * 20);
$select->columns(['id', 'date_added', 'content', 'last_updated', 'username' => new Expression('user.username'), 'user_title' => new Expression('user.title'), 'user_avatar' => new Expression('user.avatar'), 'user_signature' => new Expression('user.post_signature'), 'user_joined' => new Expression('user.date_joined'), 'user_postcount' => new Expression('po.postcount')], false);
$select->where($predicate);
$result = $this->select($select);
return $result->toArray();
}
public function getThreadsByDate($threadId, $pageNo = 1)
{
$selectPost = new Select();
$selectPost->join('user', 'post.user_id = user.user_id', [], $selectPost::JOIN_LEFT . ' ' . $selectPost::JOIN_OUTER);
$selectPost->from('post')->group('thread_id');
$selectPost->columns(['thread_id', 'count' => new Expression('COUNT(thread_id)'), 'last_post' => new Expression('MAX(date_added)'), 'poster' => new Expression('username')]);
$predicate = new Predicate(null, Predicate::OP_AND);
$predicate->equalTo('forum_id', $threadId)->equalTo('deleted', '0');
$select = new Select();
$select->from($this->tableName);
$select->order('date_updated DESC');
$select->limit(20);
$select->join('user', 'thread.user_id = user.user_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
$select->join(['po' => $selectPost], 'thread.id = po.thread_id', [], $select::JOIN_LEFT . ' ' . $select::JOIN_OUTER);
$select->columns(['id', 'title', 'date_added', 'forum_id', 'view_count', 'sticky', 'post_count' => new Expression('count'), 'creator' => new Expression('username'), 'last_poster' => new Expression('poster'), 'last_post_date' => new Expression('last_post')]);
$select->offset(($pageNo - 1) * 20);
$select->where($predicate);
$result = $this->select($select);
return $result->toArray();
}
public function fetchAllByGroup($groupId)
{
$select = new Select($this->getTable());
$select->limit(1);
$select->where->equalTo('group_id', $groupId);
$row = $this->selectWith($select)->toArray();
if (!$row) {
return [];
}
$row = $row[0];
if (array_key_exists('nav_override', $row)) {
if (!empty($row['nav_override'])) {
$row['nav_override'] = @json_decode($row['nav_override']);
if (json_last_error() !== JSON_ERROR_NONE) {
$row['nav_override'] = false;
}
} else {
$row['nav_override'] = NULL;
}
}
return $row;
}
/**
* @param Select $select
* @param $count
* @param $offset
* @return Select
*/
public function setLimit(Select $select, $count, $offset)
{
if ($count === null) {
return $select;
}
return $select->limit($count)->offset($offset);
}
public function applyParamsToTableEntriesSelect(array $params, Select $select, array $schema, $hasActiveColumn = false)
{
$tableName = $this->getTable();
if (isset($params['group_by'])) {
$select->group($tableName . '.' . $params['group_by']);
} else {
$select->group($tableName . '.' . $this->primaryKeyFieldName);
}
//If this is a relational order, than it is an array.
if (is_array($params['orderBy'])) {
$select->join(array('jsort' => $params['orderBy']['junction_table']), 'jsort.' . $params['orderBy']['jkeyRight'] . ' = ' . $tableName . '.' . $this->primaryKeyFieldName, array(), $select::JOIN_LEFT);
$select->join(array('rsort' => $params['orderBy']['related_table']), 'rsort.id = jsort.' . $params['orderBy']['jkeyLeft'], array(), $select::JOIN_LEFT);
$select->order('rsort.title', $params['orderDirection']);
} else {
$select->order(implode(' ', array($params['orderBy'], $params['orderDirection'])));
}
if (isset($params['perPage']) && isset($params['currentPage'])) {
$select->limit($params['perPage'])->offset($params['currentPage'] * $params['perPage']);
}
// Are we sorting on a relationship?
foreach ($schema as $column) {
if ($column['column_name'] != $params['orderBy']) {
continue;
}
// Must have defined table_related
if (!isset($column['relationship']) || !is_array($column['relationship']) || !isset($column['relationship']['table_related'])) {
break;
}
// Must have defined visible_column
if (!isset($column['options']) || !is_array($column['options']) || !isset($column['options']['visible_column'])) {
break;
}
$relatedTable = $column['relationship']['table_related'];
$visibleColumn = $column['options']['visible_column'];
$keyLeft = $params['table_name'] . "." . $params['orderBy'];
// @todo it's wrong to assume PKs are "id" but this is currently endemic to directus6
$keyRight = $relatedTable . ".id";
$joinedSortColumn = $relatedTable . "." . $visibleColumn;
$select->reset(Select::ORDER)->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT)->order("{$joinedSortColumn} " . $params['orderDirection']);
break;
}
// Note: be sure to explicitly check for null, because the value may be
// '0' or 0, which is meaningful.
if (null !== $params[STATUS_COLUMN_NAME] && $hasActiveColumn) {
$haystack = is_array($params[STATUS_COLUMN_NAME]) ? $params[STATUS_COLUMN_NAME] : explode(",", $params[STATUS_COLUMN_NAME]);
if (!isset($params['table_name']) || empty($params['table_name'])) {
$tableName = $this->getTable();
} else {
$tableName = $params['table_name'];
}
$select->where->in($tableName . '.' . STATUS_COLUMN_NAME, $haystack);
}
// Select only ids from the ids if provided
if (array_key_exists('ids', $params)) {
$entriesIds = array_filter(explode(',', $params['ids']), 'is_numeric');
if (count($entriesIds) > 0) {
$select->where->in($this->getTable() . '.' . $this->primaryKeyFieldName, $entriesIds);
}
}
// Where
$select->where->nest->expression('-1 = ?', $params[$this->primaryKeyFieldName])->or->equalTo($tableName . '.' . $this->primaryKeyFieldName, $params[$this->primaryKeyFieldName])->unnest;
// very very rudimentary ability to supply where conditions to fetch...
// at the moment, only 'equalTo' and 'between' are supported... also, the 'total' key returned
// in the json does not reflect these filters...
// -MG
if (array_key_exists('where', $params)) {
$outer = $select->where->nest;
foreach ($params['where'] as $whereCond) {
$type = $whereCond['type'];
$column = $whereCond['column'];
if ($type == 'equalTo') {
$val = $whereCond['val'];
if (is_array($val)) {
$where = $select->where->nest;
foreach ($val as $currentval) {
$where->equalTo($column, $currentval);
if ($currentval != end($val)) {
$where->or;
}
}
$where->unnest;
} else {
$outer->equalTo($column, $val);
}
} else {
if ($type == 'between') {
$val1 = $whereCond['val1'];
$val2 = $whereCond['val2'];
$outer->between($column, $val1, $val2);
}
}
}
$outer->unnest;
}
//@TODO: Make this better
if (isset($params['adv_where'])) {
$select->where($params['adv_where']);
}
if (isset($params['adv_search']) && !empty($params['adv_search'])) {
$i = 0;
foreach ($params['adv_search'] as $search_col) {
$target = array();
foreach ($schema as $col) {
if ($col['id'] == $search_col['id']) {
$target = $col;
break;
}
}
if (empty($target)) {
continue;
}
// TODO: fix this, it must be refactored
if (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOMANY") {
$relatedTable = $target['relationship']['table_related'];
$relatedAliasName = $relatedTable . "_" . $i;
if ($target['relationship']['type'] == "MANYTOMANY") {
$junctionTable = $target['relationship']['junction_table'];
$jkl = $target['relationship']['junction_key_left'];
$jkr = $target['relationship']['junction_key_right'];
$keyleft = $params['table_name'] . ".id";
$keyRight = $junctionTable . '.' . $jkl;
$jkeyleft = $junctionTable . '.' . $jkr;
$jkeyright = $relatedAliasName . ".id";
$select->join($junctionTable, "{$keyleft} = {$keyRight}", array(), Select::JOIN_INNER);
} else {
$select->join(array($relatedAliasName => $relatedTable), $tableName . '.' . $target['column_name'] . " = " . $relatedAliasName . ".id", array(), Select::JOIN_INNER);
}
$relatedTableMetadata = TableSchema::getSchemaArray($relatedTable);
if ($search_col['type'] == "like") {
$select->join(array($relatedAliasName => $relatedTable), "{$jkeyleft} = {$jkeyright}", array(), Select::JOIN_INNER);
$search_col['value'] = "%" . $search_col['value'] . "%";
if (isset($target['options']['filter_column'])) {
$targetCol = $target['options']['filter_column'];
} else {
$targetCol = $target['options']['visible_column'];
}
foreach ($relatedTableMetadata as $col) {
if ($col['id'] == $targetCol) {
if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
$where = $select->where->nest;
$columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
$columnName = $relatedAliasName . "." . $columnName;
$like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", $search_col['value']);
$where->addPredicate($like, Predicate\Predicate::OP_OR);
$where->unnest;
}
}
}
} else {
$select->where($jkeyleft . ' = ' . $this->adapter->platform->quoteValue($search_col['value']));
}
} elseif (isset($target['relationship']) && $target['relationship']['type'] == "MANYTOONE") {
$relatedTable = $target['relationship']['table_related'];
$keyLeft = $this->getTable() . "." . $target['relationship']['junction_key_left'];
$keyRight = $relatedTable . ".id";
$filterColumn = $target['options']['filter_column'];
$joinedFilterColumn = $relatedTable . "." . $filterColumn;
// do not let join this table twice
// TODO: do a extra checking in case it's being used twice
// and none for sorting
if ($target['column_name'] != $params['orderBy']) {
$select->join($relatedTable, "{$keyLeft} = {$keyRight}", array(), Select::JOIN_LEFT);
}
if ($search_col['type'] == 'like') {
$searchLike = '%' . $search_col['value'] . '%';
$spec = function (Where $where) use($joinedFilterColumn, $searchLike) {
$where->like($joinedFilterColumn, $searchLike);
};
$select->where($spec);
} else {
$select->where($search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value']));
}
} else {
if ($target['type'] == "DATETIME" && strpos($search_col['value'], " ") == false) {
$select->where('date(' . $tableName . '.' . $search_col['id'] . ") = " . $this->adapter->platform->quoteValue($search_col['value']));
} else {
if ($search_col['type'] == "like") {
$select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue("%" . $search_col['value'] . "%"));
} else {
$select->where($tableName . '.' . $search_col['id'] . " " . $search_col['type'] . " " . $this->adapter->platform->quoteValue($search_col['value']));
}
}
}
$i++;
}
} else {
if (isset($params['search']) && !empty($params['search'])) {
$params['search'] = "%" . $params['search'] . "%";
$where = $select->where->nest;
foreach ($schema as $col) {
if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
$columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
$like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", strtolower($params['search']));
$where->addPredicate($like, Predicate\Predicate::OP_OR);
}
}
$where->unnest;
}
}
return $select;
}
/**
* 根据columns主要信息
*/
function getSelectInfo($where = array(), $order = array(), $limit = '', $offset = '')
{
$select = new Select();
$select->from($this->getTable())->columns($this->selectColumns);
empty($where) ?: $select->where($where);
empty($limit) ?: $select->limit((int) $limit);
empty($offset) ?: $select->offset((int) $offset);
empty($order) ? $select->order(array($this->_primary => 'desc')) : $select->order($order);
return $this->selectWith($select)->toArray();
}
/**
*
* @param Select $select
* @param Options $options
* @return Select
*/
protected function assignOptions(Select $select, Options $options)
{
if ($options->hasLimit()) {
$select->limit($options->getLimit());
if ($options->hasOffset()) {
$select->offset($options->getOffset());
}
$select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
}
return $select;
}
public function selectOne(Select $select, ResultSet\ResultSetInterface $resultSet = null)
{
$select->limit(1);
//var_dump($select->getSqlString()); die();
return $this->select($select, $resultSet)->current();
}
public function fetchByUserAndTable($user_id, $table)
{
$select = new Select($this->table);
$select->limit(1);
$select->where->equalTo('table_name', $table)->equalTo('user', $user_id);
$preferences = $this->selectWith($select)->current();
if (!$preferences) {
return $this->constructPreferences($user_id, $table);
}
if ($preferences) {
$preferences = $preferences->toArray();
}
return $preferences;
}
/**
* @param int $limit
* @return $this
*/
public function limit($limit)
{
$this->select->limit($limit);
return $this;
}
public function applyParamsToTableEntriesSelect(array $params, Select $select, array $schema, $hasActiveColumn = false)
{
$select->group('id')->order(implode(' ', array($params['orderBy'], $params['orderDirection'])));
if (isset($params['perPage']) && isset($params['currentPage'])) {
$select->limit($params['perPage'])->offset($params['currentPage'] * $params['perPage']);
}
// Note: be sure to explicitly check for null, because the value may be
// '0' or 0, which is meaningful.
if (null !== $params[STATUS_COLUMN_NAME] && $hasActiveColumn) {
$haystack = is_array($params[STATUS_COLUMN_NAME]) ? $params[STATUS_COLUMN_NAME] : explode(",", $params[STATUS_COLUMN_NAME]);
$select->where->in(STATUS_COLUMN_NAME, $haystack);
}
// Select only ids from the ids if provided
if (array_key_exists('ids', $params)) {
$entriesIds = array_filter(explode(',', $params['ids']), 'is_numeric');
if (count($entriesIds) > 0) {
$select->where->in($this->getTable() . '.' . $this->primaryKeyFieldName, $entriesIds);
}
}
// Where
$select->where->nest->expression('-1 = ?', $params[$this->primaryKeyFieldName])->or->equalTo($this->primaryKeyFieldName, $params[$this->primaryKeyFieldName])->unnest;
if (isset($params['adv_search']) && !empty($params['adv_search'])) {
$select->where($params['adv_search']);
} else {
if (isset($params['search']) && !empty($params['search'])) {
$params['search'] = "%" . $params['search'] . "%";
$where = $select->where->nest;
foreach ($schema as $col) {
if ($col['type'] == 'VARCHAR' || $col['type'] == 'INT') {
$columnName = $this->adapter->platform->quoteIdentifier($col['column_name']);
$like = new Predicate\Expression("LOWER({$columnName}) LIKE ?", strtolower($params['search']));
$where->addPredicate($like, Predicate\Predicate::OP_OR);
}
}
$where->unnest;
}
}
return $select;
}
/**
* Returns the option to a LIMIT SQL statement, because it varies accross DB systems
*
* @access public
* @param int Number of the first row to return data from
* @param int Number of rows to return
* @return string SQL string to pass to a LIMIT statement
*/
function serendipity_db_limit($offset, $limit)
{
global $serendipity;
$sql = new Sql($serendipity['dbConn']);
$select = new Select();
$select->limit($limit);
$select->offset($offset);
return str_replace("LIMIT", "", $sql->getSqlStringForSqlObject($select));
# TODO: Find a way to not hardcode the LIMIT here
}
/**
* @testdox unit test: Test reset() resets internal stat of Select object, based on input
* @covers Zend\Db\Sql\Select::reset
*/
public function testReset()
{
$select = new Select();
// table
$select->from('foo');
$this->assertEquals('foo', $select->getRawState(Select::TABLE));
$select->reset(Select::TABLE);
$this->assertNull($select->getRawState(Select::TABLE));
// columns
$select->columns(array('foo'));
$this->assertEquals(array('foo'), $select->getRawState(Select::COLUMNS));
$select->reset(Select::COLUMNS);
$this->assertEmpty($select->getRawState(Select::COLUMNS));
// joins
$select->join('foo', 'id = boo');
$this->assertEquals(array(array('name' => 'foo', 'on' => 'id = boo', 'columns' => array('*'), 'type' => 'inner')), $select->getRawState(Select::JOINS));
$select->reset(Select::JOINS);
$this->assertEmpty($select->getRawState(Select::JOINS));
// where
$select->where('foo = bar');
$where1 = $select->getRawState(Select::WHERE);
$this->assertEquals(1, $where1->count());
$select->reset(Select::WHERE);
$where2 = $select->getRawState(Select::WHERE);
$this->assertEquals(0, $where2->count());
$this->assertNotSame($where1, $where2);
// group
$select->group(array('foo'));
$this->assertEquals(array('foo'), $select->getRawState(Select::GROUP));
$select->reset(Select::GROUP);
$this->assertEmpty($select->getRawState(Select::GROUP));
// having
$select->having('foo = bar');
$having1 = $select->getRawState(Select::HAVING);
$this->assertEquals(1, $having1->count());
$select->reset(Select::HAVING);
$having2 = $select->getRawState(Select::HAVING);
$this->assertEquals(0, $having2->count());
$this->assertNotSame($having1, $having2);
// limit
$select->limit(5);
$this->assertEquals(5, $select->getRawState(Select::LIMIT));
$select->reset(Select::LIMIT);
$this->assertNull($select->getRawState(Select::LIMIT));
// offset
$select->offset(10);
$this->assertEquals(10, $select->getRawState(Select::OFFSET));
$select->reset(Select::OFFSET);
$this->assertNull($select->getRawState(Select::OFFSET));
// order
$select->order('foo asc');
$this->assertEquals(array('foo asc'), $select->getRawState(Select::ORDER));
$select->reset(Select::ORDER);
$this->assertNull($select->getRawState(Select::ORDER));
}
/**
* {@inheritdoc}
*/
public function limit($limit)
{
parent::limit((int) $limit);
return $this;
}