/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->leftJoin('modMediaSource', 'Source');
$c->leftJoin($this->classKey, 'Thumb', "`{$this->classKey}`.`id` = `Thumb`.`parent`");
$c->groupby($this->classKey . '.id');
$c->select('`Source`.`name` as `source_name`');
$c->select('`Thumb`.`url` as `thumbnail`');
$c->where(array('resource_id' => $this->getProperty('resource_id')));
$parent = $this->getProperty('parent');
if ($parent !== false) {
$c->where(array('parent' => $parent));
}
$query = trim($this->getProperty('query'));
if (!empty($query)) {
$c->where(array('file:LIKE' => "%{$query}%", 'OR:name:LIKE' => "%{$query}%", 'OR:alt:LIKE' => "%{$query}%", 'OR:description:LIKE' => "%{$query}%", 'OR:add:LIKE' => "%{$query}%"));
}
$tags = array_map('trim', explode(',', $this->getProperty('tags')));
if (!empty($tags[0])) {
$tags = implode("','", $tags);
$c->innerJoin('msResourceFileTag', 'Tag', "`{$this->classKey}`.`id` = `Tag`.`file_id` AND `Tag`.`tag` IN ('" . $tags . "')");
$c->groupby($this->classKey . '.id');
$c->prepare();
$this->modx->log(1, $c->toSQL());
}
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->leftJoin($this->classKey, 'Children');
$c->where(array($this->classKey . '.parent' => $this->getProperty('id')));
$c->groupby($this->classKey . '.text');
return parent::prepareQueryBeforeCount($c);
}
/** {@inheritDoc} */
public function prepareQueryBeforeCount(xPDOQuery $c)
{
if ($this->getProperty('combo')) {
$c->limit(0);
$c->groupby('level');
}
return $c;
}
/**
* Group query by given field
*/
public function addGrouping()
{
if (!empty($this->config['groupby'])) {
$time = microtime(true);
$groupby = $this->config['groupby'];
$this->query->groupby($groupby);
$this->addTime('Grouped by <b>' . $groupby . '</b>', microtime(true) - $time);
}
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->where(array('referrer_id' => $this->getProperty('referrer_id')));
$c->groupby($this->classKey . '.id');
$c->select($this->modx->getSelectColumns($this->classKey, $this->classKey));
$c->innerJoin('modUser', 'User', $this->classKey . '.id = User.id');
$c->select('User.username');
$c->innerJoin('modUserProfile', 'UserProfile', $this->classKey . '.id = UserProfile.internalKey');
$c->select('UserProfile.fullname');
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->leftJoin('modMediaSource', 'Source');
$c->leftJoin($this->classKey, 'Thumb', "`{$this->classKey}`.`id` = `Thumb`.`parent`");
$c->groupby($this->classKey . '.id');
$c->select('`Source`.`name` as `source_name`');
$c->select('`Thumb`.`url` as `thumbnail`');
$c->where(array('resource_id' => $this->getProperty('resource_id')));
$parent = $this->getProperty('parent');
if ($parent !== false) {
$c->where(array('parent' => $parent));
}
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->leftJoin('modTemplate', 'Template');
$c->leftJoin('sxSubscriber', 'Subscribers');
$c->select($this->modx->getSelectColumns($this->classKey, $this->classKey));
$c->select($this->modx->getSelectColumns('modTemplate', 'Template', '', array('templatename')));
$c->select('COUNT(`Subscribers`.`id`) as `subscribers`');
$c->groupby($this->classKey . '.id');
if ($query = $this->getProperty('query')) {
$c->where(array('name:LIKE' => "%{$query}%", 'OR:description:LIKE' => "%{$query}%"));
}
if ($this->getProperty('combo')) {
$c->where(array('active' => 1));
}
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->groupby('glData.id');
$c->leftJoin('glCountry', 'glCountry', 'glCountry.id = glData.identifier');
$c->leftJoin('glRegion', 'glRegion', 'glRegion.id = glData.identifier');
$c->leftJoin('glCity', 'glCity', 'glCity.id = glData.identifier');
$c->select($this->modx->getSelectColumns('glData', 'glData'));
$c->select(array('name' => 'glCountry.name_ru', 'name1' => 'glRegion.name_ru', 'name2' => 'glCity.name_ru', 'active' => 'glCountry.active', 'active1' => 'glRegion.active', 'active2' => 'glCity.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;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
/* Get all comments by section */
if ($section = (int) $this->getProperty('section')) {
if ($section = $this->modx->getObject('modResource', $section)) {
$parents = $this->modx->getChildIds($section->get('id'), 1, array('context' => $section->get('context_key')));
if (empty($parents)) {
$parents = array('0');
}
$c->where(array('Thread.resource:IN' => $parents));
}
} elseif ($threads = $this->getProperty('threads')) {
if (!is_array($threads)) {
$threads = explode(',', $threads);
}
if (!empty($threads)) {
$c->where(array('TicketComment.thread:IN' => $threads));
}
} elseif ($parents = $this->getProperty('parents')) {
if (!is_array($parents)) {
$parents = explode(',', $parents);
}
if (!empty($parents)) {
$c->where(array('Thread.resource:IN' => $parents));
}
} else {
//$c->where(array('Thread.resource:!=' => 0));
}
if ($query = $this->getProperty('query', null)) {
$query = trim($query);
if (is_numeric($query)) {
$c->where(array('TicketComment.id:=' => $query, 'OR:TicketComment.parent:=' => $query));
} else {
$c->where(array('TicketComment.text:LIKE' => '%' . $query . '%', 'OR:TicketComment.name:LIKE' => '%' . $query . '%', 'OR:TicketComment.email:LIKE' => '%' . $query . '%'));
}
}
$c->leftJoin('TicketThread', 'Thread');
$c->leftJoin('modUser', 'User');
$c->leftJoin('modUserProfile', 'UserProfile');
$c->leftJoin('modResource', 'Resource', 'Thread.resource = Resource.id');
$c->select($this->modx->getSelectColumns('TicketComment', 'TicketComment'));
$c->select(array('Thread.resource', 'Thread.properties', 'thread_name' => 'Thread.name', 'User.username', 'UserProfile.fullname', 'Resource.pagetitle', 'Resource.context_key'));
$c->groupby('TicketComment.id');
return $c;
}
/** {@inheritDoc} */
public function prepareQueryBeforeCount(xPDOQuery $c)
{
if (!$this->getProperty('combo')) {
$c->groupby('MlmSystemClient.id');
$c->leftJoin('modUser', 'modUser', 'modUser.id = MlmSystemClient.id');
$c->leftJoin('modUserProfile', 'modUserProfile', 'modUserProfile.internalKey = MlmSystemClient.id');
$c->leftJoin('modUser', 'modUserParent', 'modUserParent.id = MlmSystemClient.parent');
$c->leftJoin('modUserProfile', 'modUserProfileParent', 'modUserProfileParent.internalKey = MlmSystemClient.parent');
$c->leftJoin('MlmSystemStatus', 'MlmSystemStatus', 'MlmSystemStatus.id = MlmSystemClient.status');
$c->leftJoin('MlmSystemPath', 'MlmSystemPath', 'MlmSystemPath.id = MlmSystemClient.id');
$c->leftJoin('MlmSystemClient', 'MlmSystemClientParent', 'MlmSystemClientParent.parent = MlmSystemClient.id');
$c->select($this->modx->getSelectColumns('MlmSystemClient', 'MlmSystemClient'));
$c->select($this->modx->getSelectColumns('modUserProfile', 'modUserProfile', 'profile_', array('id', 'internalKey'), true));
$c->select(array('username' => 'modUser.username', 'fullname' => 'modUserProfile.fullname', 'email' => 'modUserProfile.email', 'parent_username' => 'modUserParent.username', 'parent_fullname' => 'modUserProfileParent.fullname', 'parent_email' => 'modUserProfileParent.email', 'status_name' => 'MlmSystemStatus.name', 'status_color' => 'MlmSystemStatus.color', 'level' => 'MlmSystemPath.level', 'children' => 'COUNT(MlmSystemClientParent.parent)'));
} else {
$c->leftJoin('modUser', 'modUser', 'modUser.id = MlmSystemClient.id');
$c->leftJoin('modUserProfile', 'modUserProfile', 'modUserProfile.internalKey = MlmSystemClient.id');
$c->select($this->modx->getSelectColumns('MlmSystemClient', 'MlmSystemClient'));
$c->select($this->modx->getSelectColumns('modUserProfile', 'modUserProfile', 'profile_', array('id', 'internalKey'), true));
$c->select(array('username' => 'modUser.username', 'fullname' => 'modUserProfile.fullname'));
$c->where(array('status:NOT IN' => array(4)));
$client = $this->getProperty('client', 0);
if (!empty($client)) {
$c->where(array('id:!=' => $client));
}
}
$status = $this->getProperty('status');
if (!empty($status)) {
$c->where(array('status' => $status));
}
$level = $this->getProperty('level');
if (!empty($level)) {
$c->where(array('MlmSystemPath.level' => $level));
}
$leader = $this->getProperty('leader');
if ($leader != '') {
$c->where(array('leader' => $leader));
}
// query
if ($query = $this->getProperty('query')) {
$c->where(array('modUser.username:LIKE' => '%' . $query . '%', 'OR:modUserProfile.fullname:LIKE' => '%' . $query . '%', 'OR:modUserProfile.email:LIKE' => '%' . $query . '%'));
}
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->groupby($this->classKey . '.id');
$c->select($this->modx->getSelectColumns($this->classKey, $this->classKey));
$c->innerJoin('modUser', 'User', $this->classKey . '.id = User.id');
$c->select('User.username');
$c->innerJoin('modUserProfile', 'UserProfile', $this->classKey . '.id = UserProfile.internalKey');
$c->select('UserProfile.fullname');
$c->leftJoin('modUser', 'Referrer', $this->classKey . '.referrer_id = Referrer.id');
$c->select('Referrer.username as referrer_username');
$c->leftJoin('modUserProfile', 'ReferrerProfile', $this->classKey . '.referrer_id = ReferrerProfile.internalKey');
$c->select('ReferrerProfile.fullname as referrer_fullname');
$c->leftJoin($this->classKey, 'Referrals', $this->classKey . '.id = Referrals.referrer_id');
$c->select('COUNT(Referrals.id) as referrals');
if ($query = $this->getProperty('query')) {
$c->where(array('User.username:LIKE' => "%{$query}%", 'OR:UserProfile.fullname:LIKE' => "%{$query}%"));
}
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->leftJoin('Ticket', 'Ticket');
$c->select($this->modx->getSelectColumns('TicketThread', 'TicketThread'));
$c->select(array('Ticket.pagetitle'));
if (!$this->getProperty('combo')) {
$c->leftJoin('TicketComment', 'Comments');
$c->select(array('comments' => 'COUNT(Comments.id)'));
$c->groupby('TicketThread.id');
}
if ($query = $this->getProperty('query', null)) {
$query = trim($query);
if (is_numeric($query)) {
$c->where(array('TicketThread.id:=' => $query, 'OR:TicketThread.resource:=' => $query));
} else {
$c->where(array('Ticket.pagetitle:LIKE' => "%{$query}%", 'OR:TicketThread.name:LIKE' => "%{$query}%"));
}
}
return $c;
}
protected function prepareGroupCondition(xPDOQuery $c)
{
$c->groupby('sm_price');
$c->groupby('sm_trade_price');
return $c;
}
public function prepareQueryAfterCount(xPDOQuery $c)
{
$c->groupby($this->defaultSortField, $this->defaultSortDirection);
return $c;
}
/** {@inheritDoc} */
public function prepareQueryAfterCount(xPDOQuery $c)
{
$c->groupby($this->classKey . '.id');
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->groupby('form');
return $c;
}
public function prepareQueryAfterCount(xPDOQuery $c)
{
$c->select(array('modAccessPermission.id', 'modAccessPermission.name', 'modAccessPermission.description', 'Template.lexicon'));
$c->groupby('modAccessPermission.name');
return $c;
}
