/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$id = $this->getProperty('id');
if (!empty($id) and $this->getProperty('combo')) {
$q = $this->modx->newQuery($this->objectType);
$q->where(array('id!=' => $id));
$q->select('id');
$q->limit(11);
$q->prepare();
$q->stmt->execute();
$ids = $q->stmt->fetchAll(PDO::FETCH_COLUMN, 0);
$ids = array_merge_recursive(array($id), $ids);
$c->where(array("{$this->objectType}.id:IN" => $ids));
}
$active = $this->getProperty('active');
if ($active != '') {
$c->where("{$this->objectType}.active={$active}");
}
$query = trim($this->getProperty('query'));
if ($query) {
$c->where(array("{$this->objectType}.name_ru:LIKE" => "%{$query}%", "OR:{$this->objectType}.name_en:LIKE" => "%{$query}%"));
}
$c->sortby('active', 'DESC');
$c->sortby('name_ru', 'ASC');
return $c;
}
/**
* Can be used to adjust the query prior to the COUNT statement
*
* @param xPDOQuery $c
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$props = $this->getProperties();
$c->distinct();
// from combobox
if (isset($props['valuesqry'])) {
if ($props['valuesqry'] === 'true') {
if (isset($props['query'])) {
if (!empty($props['query'])) {
$query = urldecode($props['query']);
$queries = @explode('||', $query);
$i = 0;
$where = '';
foreach ($queries as $query) {
$where .= ($i !== 0 ? ' OR ' : '') . "`smarttagTags`.`tag` = '{$query}'";
$i++;
}
$c->where("({$where})");
}
}
} else {
if (isset($props['query']) && !empty($props['query'])) {
$c->where(array('tag:LIKE' => "{$props['query']}%", 'OR:tag:LIKE' => "% {$props['query']}%"));
}
}
} else {
if (isset($props['query']) && !empty($props['query'])) {
$c->where(array('tag:LIKE' => "%{$props['query']}%"));
}
}
$tvId = isset($props['tvId']) && is_numeric($props['tvId']) ? intval($props['tvId']) : '';
// for tagcloud
if (strtolower($props['sort']) === 'count') {
$c->select(array('smarttagTags.id', 'smarttagTags.tag', 'count' => "(SELECT COUNT(*) FROM {$this->modx->getTableName('smarttagTagresources')} AS Tagresources " . 'WHERE (Tagresources.tag_id = smarttagTags.id ' . (!empty($tvId) ? 'AND Tagresources.tmplvar_id=' . $tvId : '') . '))'));
$c->sortby('count', 'desc');
$c->sortby('tag', 'asc');
}
if (!empty($tvId)) {
$tv = $this->modx->getObject('modTemplateVar', $tvId);
if ($tv) {
$params = $tv->get('input_properties');
if ($params['globaltags'] != 'true') {
$c->leftJoin('smarttagTagresources', 'Tagresources', 'Tagresources.tag_id = smarttagTags.id');
$c->where(array('Tagresources.tmplvar_id' => $tvId));
}
}
}
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$context = array_map('trim', explode(',', $this->getProperty('context', $this->modx->context->key)));
$c->where(array('class_key' => 'TicketsSection', 'published' => 1, 'deleted' => 0, 'context_key:IN' => $context));
$sortby = $this->getProperty('sortby');
$sortdir = $this->getProperty('sortdir');
if ($sortby && $sortdir) {
$c->sortby($sortby, $sortdir);
}
if (!empty($_REQUEST['tid']) && ($tmp = $this->modx->getObject('Ticket', (int) $_REQUEST['tid']))) {
$this->current_category = $tmp->get('parent');
}
if ($parents = $this->getProperty('parents')) {
$depth = $this->getProperty('depth', 0);
$parents = array_map('trim', explode(',', $parents));
foreach ($parents as $pid) {
$parents = array_merge($parents, $this->modx->getChildIds($pid, $depth));
}
if (!empty($parents) && !empty($this->current_category)) {
$c->where(array('parent:IN' => $parents, 'OR:id:=' => $this->current_category));
} else {
$c->where(array('parent:IN' => $parents));
}
}
if ($resources = $this->getProperty('resources')) {
$resources = array_map('trim', explode(',', $resources));
$c->where(array('id:IN' => $resources));
}
return $c;
}
public function prepareQueryAfterCount(xPDOQuery $c)
{
if ($this->getProperty('sort') == 'category') {
$c->sortby('parent', $this->getProperty('dir', 'ASC'));
}
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
if (!$this->getProperty('combo')) {
} else {
}
$active = $this->getProperty('active');
if ($active != '') {
$c->where(array('active' => $active));
}
$query = trim($this->getProperty('query'));
if ($query) {
$c->where(array('name_ru:LIKE' => "%{$query}%", 'OR:name_en:LIKE' => "%{$query}%"));
}
$c->sortby('active', 'DESC');
$c->sortby('name_ru', 'ASC');
return $c;
}
/**
* Add sort to query
*/
public function addSort()
{
$time = microtime(true);
$tmp = $this->config['sortby'];
if (empty($tmp) || strtolower($tmp) == 'resources' || strtolower($tmp) == 'ids') {
$resources = $this->config['class'] . '.' . $this->pk . ':IN';
if (!empty($this->config['where'][$resources])) {
$tmp = array('FIELD(`' . $this->config['class'] . '`.`' . $this->pk . '`,\'' . implode('\',\'', $this->config['where'][$resources]) . '\')' => '');
} else {
$tmp = array($this->config['class'] . '.' . $this->pk => !empty($this->config['sortdir']) ? $this->config['sortdir'] : 'ASC');
}
} else {
$tmp = is_string($tmp) && ($tmp[0] == '{' || $tmp[0] == '[') ? $this->modx->fromJSON($this->config['sortby']) : array($this->config['sortby'] => $this->config['sortdir']);
}
if (!empty($this->config['sortbyTV']) && !array_key_exists($this->config['sortbyTV'], $tmp)) {
$tmp2[$this->config['sortbyTV']] = !empty($this->config['sortdirTV']) ? $this->config['sortdirTV'] : (!empty($this->config['sortdir']) ? $this->config['sortdir'] : 'ASC');
$tmp = array_merge($tmp2, $tmp);
if (!empty($this->config['sortbyTVType'])) {
$tv = strtolower($this->config['sortbyTV']);
if (array_key_exists($tv, $this->config['tvsJoin'])) {
if (!empty($this->config['tvsJoin'][$tv]['tv'])) {
$this->config['tvsJoin'][$tv]['tv']['type'] = $this->config['sortbyTVType'];
}
}
}
}
$fields = $this->modx->getFields($this->config['class']);
$sorts = $this->replaceTVCondition($tmp);
if (is_array($sorts)) {
while (list($sortby, $sortdir) = each($sorts)) {
if (preg_match_all('/TV(.*?)[`|.]/', $sortby, $matches)) {
foreach ($matches[1] as $tv) {
if (array_key_exists($tv, $this->config['tvsJoin'])) {
$params = $this->config['tvsJoin'][$tv]['tv'];
switch ($params['type']) {
case 'number':
case 'decimal':
$sortby = preg_replace('/(TV' . $tv . '\\.value|`TV' . $tv . '`\\.`value`)/', 'CAST($1 AS DECIMAL(13,3))', $sortby);
break;
case 'integer':
$sortby = preg_replace('/(TV' . $tv . '\\.value|`TV' . $tv . '`\\.`value`)/', 'CAST($1 AS SIGNED INTEGER)', $sortby);
break;
case 'date':
case 'datetime':
$sortby = preg_replace('/(TV' . $tv . '\\.value|`TV' . $tv . '`\\.`value`)/', 'CAST($1 AS DATETIME)', $sortby);
break;
}
}
}
} elseif (array_key_exists($sortby, $fields)) {
$sortby = $this->config['class'] . '.' . $sortby;
}
$this->query->sortby($sortby, $sortdir);
$this->addTime('Sorted by <b>' . $sortby . '</b>, <b>' . $sortdir . '</b>', microtime(true) - $time);
$time = microtime(true);
}
}
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$query = $this->getProperty('query');
if (!empty($query)) {
$c->where(array('name:LIKE' => '%' . $query . '%', 'OR:alias:LIKE' => '%' . $query . '%'));
}
$c->sortby('menuindex', 'ASC');
return $c;
}
public function prepareQueryAfterCount(xPDOQuery $c)
{
$c->select($this->modx->getSelectColumns('modUser', 'modUser'));
$c->select(array('usergroup' => 'UserGroup.id', 'usergroup_name' => 'UserGroup.name', 'role' => 'UserGroupRole.id', 'role_name' => 'UserGroupRole.name', 'authority' => 'UserGroupRole.authority'));
if ($this->getProperty('sort') !== 'authority') {
$c->sortby('authority', 'ASC');
}
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$exclude = $this->getProperty('exclude', '');
if (!empty($exclude)) {
$c->where(array('id:NOT IN' => is_array($exclude) ? $exclude : explode(',', $exclude)));
}
$parent = $this->getProperty('parent', '');
if (!empty($parent)) {
$c->where(array('parent' => $parent));
}
$query = $this->getProperty('query', '');
if (!empty($query)) {
$c->where(array('name:LIKE' => '%' . $query . '%', 'OR:description:LIKE' => '%' . $query . '%'));
}
$c->sortby('parent', 'asc');
$c->sortby('id', 'asc');
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$query = $this->getProperty('query');
if (!empty($query)) {
$c->where(array('pagetitle:LIKE' => '%' . $query . '%'));
}
$c->where(array('deleted' => false, 'published' => true));
$c->sortby('pagetitle', 'ASC');
return $c;
}
protected function getCount(xPDOQuery &$c)
{
$query = clone $c;
$query = $this->prepareCountQuery($query);
if (!($this->total = $this->countTotal($this->classKey, $query))) {
return false;
}
# $sortKey = 'pagetitle';
if ($sortKey = $this->getProperty('sort')) {
$dir = $this->getProperty('dir');
$c->sortby($sortKey, $dir);
$query->sortby($sortKey, $dir);
} else {
$sortKey = 'show_in_tree';
$dir = 'DESC';
$c->sortby($sortKey, $dir);
$query->sortby($sortKey, $dir);
$sortKey = 'isfolder';
$dir = 'DESC';
$c->sortby($sortKey, $dir);
$query->sortby($sortKey, $dir);
}
$limit = intval($this->getProperty('limit'));
$start = intval($this->getProperty('start'));
if ($limit > 0) {
$query->limit($limit, $start);
}
$query = $this->prepareUniqObjectsQuery($query);
if ($query->prepare() && $query->stmt->execute() && ($rows = $row = $query->stmt->fetchAll(PDO::FETCH_ASSOC))) {
$IDs = array();
foreach ($rows as $row) {
$IDs[] = $row['id'];
}
if ($this->flushWhere && isset($c->query['where'])) {
$c->query['where'] = array();
}
$c->where(array("{$this->classKey}.id:IN" => $IDs));
} else {
return false;
}
return $c;
}
/**
* @return mixed
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$rid = trim($this->getProperty('resource'));
$c->setClassAlias('Permissions');
$c->leftJoin('modUserProfile', 'User', 'Permissions.principal = User.internalKey AND Permissions.principal_type = "usr"');
$c->leftJoin('modUserGroup', 'Group', 'Permissions.principal = Group.id AND Permissions.principal_type = "grp"');
$c->select('Permissions.*, User.fullname, Group.name as groupname');
$c->where(array('Permissions.rid' => $rid));
$c->sortby('Permissions.weight', 'ASC');
//$c->sortby('Permissions.priority', 'DESC');
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
if (!$this->getProperty('combo')) {
} else {
}
$c->leftJoin('glRegion', 'glRegion', 'glRegion.id = glCity.region_id');
$c->select($this->modx->getSelectColumns('glCity', 'glCity'));
$c->select(array('region_name_ru' => 'glRegion.name_ru'));
$active = $this->getProperty('active');
if ($active != '') {
$c->where(array('glCity.active' => $active));
}
$regionId = $this->getProperty('region_id');
if ($regionId != '') {
$c->where(array('glCity.region_id' => $regionId));
}
$query = trim($this->getProperty('query'));
if ($query) {
$c->where(array('glCity.name_ru:LIKE' => "%{$query}%", 'glCity.OR:name_en:LIKE' => "%{$query}%"));
}
$c->sortby('glCity.active', 'DESC');
$c->sortby('glCity.name_ru', 'ASC');
return $c;
}
protected function getCount(xPDOQuery &$c)
{
if (!($sortKey = $this->getProperty('sort'))) {
$sortClassKey = $this->getSortClassKey();
$sortKey = $this->modx->getSelectColumns($sortClassKey, $this->getProperty('sortAlias', $sortClassKey), '', array($this->getProperty('sort')));
}
$query = clone $c;
$query = $this->prepareCountQuery($query);
if (!($this->total = $this->countTotal($this->classKey, $query))) {
return false;
}
if ($sortKey) {
$c->sortby($sortKey, $this->getProperty('dir'));
$query->sortby($sortKey, $this->getProperty('dir'));
}
$limit = intval($this->getProperty('limit'));
$start = intval($this->getProperty('start'));
if ($limit > 0) {
$query->limit($limit, $start);
}
$query = $this->prepareUniqObjectsQuery($query);
if ($query->prepare() && $query->stmt->execute() && ($rows = $row = $query->stmt->fetchAll(PDO::FETCH_ASSOC))) {
$IDs = array();
foreach ($rows as $row) {
$IDs[] = $row['id'];
}
// print $query->toSQL();
// print_r($IDs);
// exit;
if ($this->flushWhere && isset($c->query['where'])) {
$c->query['where'] = array();
}
$c->where(array("{$this->classKey}.id:IN" => $IDs));
} else {
return false;
}
return $c;
}
public function prepareQueryAfterCount(xPDOQuery $c)
{
$c->sortby($this->modx->getSelectColumns('modAction', 'modAction', '', array('namespace')), 'ASC');
return $c;
}
protected function permanentSort(xPDOQuery $c, $gridSort, $sortOptions)
{
$sorts = explode(',', $sortOptions);
foreach ($sorts as $sort) {
$sort = explode('=', $sort);
if (isset($sort[1])) {
if ($sort[0] != '*' && strtolower($sort[0]) != strtolower($gridSort)) {
continue;
}
}
$options = isset($sort[1]) ? $sort[1] : $sort[0];
$options = explode(':', $options);
if (empty($options[0])) {
continue;
}
$options['field'] = $options[0];
$options['dir'] = empty($options[1]) ? $this->getProperty('dir') : $options[1];
$options['type'] = empty($options[2]) ? null : $options[2];
if (empty($options['type'])) {
$c->sortby('`' . $options['field'] . '`', $options['dir']);
} else {
$c->sortby('CAST(`' . $options['field'] . '` as ' . $options['type'] . ')', $options['dir']);
}
}
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
protected function _sortResources(xPDOQuery $c)
{
$parent_id = $this->getProperty('parent', 0);
$sort = $this->classKey . '.menuindex';
$dir = 'ASC';
if ($parent_id && ($parent = $this->modx->getObject($this->classKey, $parent_id))) {
$class_key = $parent->get('class_key');
if ($class_key == 'TicketsSection') {
$sort = $this->classKey . '.createdon';
$dir = 'DESC';
}
}
$c->sortby($sort, $dir);
return $c;
}
