public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('galAlbumItem', 'AlbumItems', array('galItem.id = AlbumItems.item', 'AlbumItems.album' => $this->getProperty('album')));
$c->innerJoin('galAlbum', 'Album', array('Album.id = AlbumItems.album'));
$c->leftJoin('galTag', 'Tags');
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('modUserGroupRole', 'UserGroupRole');
$c->innerJoin('modUserGroup', 'UserGroup');
$c->innerJoin('modUser', 'User', array('User.id' => 'modUserGroupMember.member', 'User.id' => $this->getProperty('user')));
$c->where(array('modUserGroupMember.member' => $this->getProperty('user')));
return $c;
}
/**
* @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->innerJoin('modUser', 'User');
$c->innerJoin('modUserProfile', 'UserProfile');
$c->select($this->modx->getSelectColumns($this->classKey, $this->classKey));
$c->select(array('username' => 'User.username', 'fullname' => 'UserProfile.fullname', 'active' => 'User.active', 'blocked' => 'UserProfile.blocked'));
if ($query = $this->getProperty('query', null)) {
$c->where(array('User.username:LIKE' => "%{$query}%", 'OR:UserProfile.fullname:LIKE' => "%{$query}%"));
}
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('modUserGroupMember', 'UserGroupMembers');
$c->innerJoin('modUserGroup', 'UserGroup', 'UserGroupMembers.user_group = UserGroup.id');
$c->leftJoin('modUserGroupRole', 'UserGroupRole', 'UserGroupMembers.role = UserGroupRole.id');
$userGroup = $this->getProperty('usergroup', 0);
$c->where(array('UserGroupMembers.user_group' => $userGroup));
$username = $this->getProperty('username', '');
if (!empty($username)) {
$c->where(array('modUser.username:LIKE' => '%' . $username . '%'));
}
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('modAccessPolicyTemplate', 'Template');
$group = $this->getProperty('group');
if (!empty($group)) {
$group = is_array($group) ? $group : explode(',', $group);
$c->innerJoin('modAccessPolicyTemplateGroup', 'TemplateGroup', 'TemplateGroup.id = Template.template_group');
$c->where(array('TemplateGroup.name:IN' => $group));
}
$query = $this->getProperty('query', '');
if (!empty($query)) {
$c->where(array('modAccessPolicy.name:LIKE' => '%' . $query . '%', 'OR:modAccessPolicy.description:LIKE' => '%' . $query . '%'));
}
return $c;
}
/**
* @return mixed
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->select($this->modx->getSelectColumns('modManagerLog', 'modManagerLog', '', array('action'), true));
// $c->select($this->modx->getSelectColumns('modManagerLog','modManagerLog'));
$c->select(array('User.username', 'Template.templatename', 'Chunk.name as chunkname', 'Snippet.name as snippetname', 'Plugin.name as pluginname', 'TV.name as tvname'));
$c->innerJoin('modUser', 'User');
$c->leftJoin('modTemplate', 'Template', '`modManagerLog`.item = `Template`.`id` AND `modManagerLog`.`classKey` = "modTemplate"');
$c->leftJoin('modChunk', 'Chunk', '`modManagerLog`.item = `Chunk`.`id` AND `modManagerLog`.`classKey` = "modChunk"');
$c->leftJoin('modSnippet', 'Snippet', '`modManagerLog`.item = `Snippet`.`id` AND `modManagerLog`.`classKey` = "modSnippet"');
$c->leftJoin('modPlugin', 'Plugin', '`modManagerLog`.item = `Plugin`.`id` AND `modManagerLog`.`classKey` = "modPlugin"');
$c->leftJoin('modTemplateVar', 'TV', '`modManagerLog`.item = `TV`.`id` AND `modManagerLog`.`classKey` = "modTemplateVar"');
$query = trim($this->getProperty('query'));
if ($query) {
$c->where('(Template.templatename LIKE "%' . $query . '%" OR Chunk.name LIKE "%' . $query . '%" OR Snippet.name LIKE "%' . $query . '%" OR Plugin.name LIKE "%' . $query . '%" OR TV.name LIKE "%' . $query . '%")');
} else {
$c->where('(modManagerLog.action LIKE "template_%" OR modManagerLog.action LIKE "chunk_%" OR modManagerLog.action LIKE "snippet_%" OR modManagerLog.action LIKE "plugin_%" OR modManagerLog.action LIKE "tv_%")');
}
$user = intval($this->getProperty('user'));
if ($user) {
$c->andCondition(array('modManagerLog.user' => $user));
}
$dateStart = trim($this->getProperty('datestart'));
if ($dateStart) {
$dateStart = date('Y-m-d', strtotime($dateStart));
$c->andCondition(array('modManagerLog.occurred:>=' => $dateStart));
}
$dateEnd = trim($this->getProperty('dateend'));
if ($dateEnd) {
$dateEnd = date('Y-m-d 23:59:59', strtotime($dateEnd));
$c->andCondition(array('modManagerLog.occurred:<=' => $dateEnd));
}
return $c;
}
/**
* @param xPDOQuery $c
*
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('sxNewsletter', 'sxNewsletter', 'sxNewsletter.id = sxQueue.newsletter_id');
$c->select($this->modx->getSelectColumns('sxQueue', 'sxQueue'));
$c->select('sxNewsletter.name as newsletter');
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->select(array('qsbSetUserGroup.*', 'UserGroup.name'));
$c->innerJoin('modUserGroup', 'UserGroup');
$c->where(array('set' => $this->getProperty('id')));
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));
}
$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->innerJoin('modAccessPolicyTemplateGroup', 'TemplateGroup');
$query = $this->getProperty('query', '');
if (!empty($query)) {
$c->where(array('modAccessPolicyTemplate.name:LIKE' => '%' . $query . '%', 'OR:modAccessPolicyTemplate.description: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;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('modUser', 'Sender');
$c->where(array('recipient' => $this->modx->user->get('id')));
$search = $this->getProperty('search', '');
if (!empty($search)) {
$c->andCondition(array('subject:LIKE' => '%' . $search . '%', 'OR:message:LIKE' => '%' . $search . '%'), null, 2);
}
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('msOrder', 'msOrder', '`msOrderProduct`.`order_id` = `msOrder`.`id`');
$c->leftJoin('msProduct', 'msProduct', '`msOrderProduct`.`product_id` = `msProduct`.`id`');
$c->leftJoin('msProductData', 'msProductData', '`msOrderProduct`.`product_id` = `msProductData`.`id`');
$c->where(array('order_id' => $this->getProperty('order_id'), 'msOrder.user_id' => $this->modx->user->id));
$c->select($this->modx->getSelectColumns('msOrderProduct', 'msOrderProduct'));
$c->select($this->modx->getSelectColumns('msProduct', 'msProduct', 'product_'));
$c->select($this->modx->getSelectColumns('msProductData', 'msProductData', 'product_', array('id'), true));
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();
if (!empty($props['tagId'])) {
$c->where(array('tag_id' => $props['tagId']));
}
if (!empty($props['tvId'])) {
$c->where(array('tmplvar_id' => $props['tvId']));
}
$c->innerJoin('smarttagResource', 'smarttagResource', 'smarttagResource.id = smarttagTagresources.resource_id');
$c->select(array('smarttagTagresources.*', 'smarttagResource.pagetitle'));
return $c;
}
/**
* @param xPDOQuery $c
* @return \xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('disCategory', 'Category');
$c->leftJoin('disBoard', 'Parent');
$c->select($this->modx->getSelectColumns('disBoard', 'disBoard', '', array('id', 'name')));
$c->select($this->modx->getSelectColumns('disCategory', 'Category', 'category_', array('id', 'name')));
$c->select($this->modx->getSelectColumns('disBoard', 'Parent', 'parent_', array('id', 'name')));
$query = $this->getProperty('query');
if (!empty($query)) {
$c->where(array('disBoard.name:LIKE' => "%{$query}%"));
}
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('modAction', 'Action');
$c->leftJoin('modTemplate', 'Template');
$profile = $this->getProperty('profile');
if (!empty($profile)) {
$c->where(array('profile' => $profile));
}
$search = $this->getProperty('search');
if (!empty($search)) {
$c->where(array('modFormCustomizationSet.description:LIKE' => '%' . $search . '%', 'OR:Template.templatename:LIKE' => '%' . $search . '%', 'OR:modFormCustomizationSet.constraint_field:LIKE' => '%' . $search . '%'), null, 2);
}
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('msOrder', 'msOrder', '`msOrderProduct`.`order_id` = `msOrder`.`id`');
$c->leftJoin('msProduct', 'msProduct', '`msOrderProduct`.`product_id` = `msProduct`.`id`');
$c->leftJoin('msProductData', 'msProductData', '`msOrderProduct`.`product_id` = `msProductData`.`id`');
$c->where(array('order_id' => $this->getProperty('order_id')));
$c->select($this->modx->getSelectColumns('msOrderProduct', 'msOrderProduct'));
$c->select($this->modx->getSelectColumns('msProduct', 'msProduct', 'product_'));
$c->select($this->modx->getSelectColumns('msProductData', 'msProductData', 'product_', array('id'), true));
if ($query = $this->getProperty('query', null)) {
$c->where(array('msProduct.pagetitle:LIKE' => '%' . $query . '%', 'OR:msProduct.description:LIKE' => '%' . $query . '%', 'OR:msProduct.introtext:LIKE' => '%' . $query . '%', 'OR:msProductData.article:LIKE' => '%' . $query . '%', 'OR:msProductData.vendor:LIKE' => '%' . $query . '%', 'OR:msProductData.made_in:LIKE' => '%' . $query . '%'));
}
return $c;
}
public function prepareQueryAfterCount(xPDOQuery $c)
{
$query = $this->getProperty('query');
if (!empty($query)) {
$c->where(array('modDashboard.name:LIKE' => '%' . $query . '%'));
$c->orCondition(array('modDashboard.description:LIKE' => '%' . $query . '%'));
}
$userGroup = $this->getProperty('usergroup', false);
if (!empty($userGroup)) {
$c->innerJoin('modUserGroup', 'UserGroups');
$c->where(array('UserGroups.id' => $userGroup));
}
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->leftJoin('modUserProfile', 'Profile');
$query = $this->getProperty('query', '');
if (!empty($query)) {
$c->where(array('modUser.username:LIKE' => '%' . $query . '%'));
$c->orCondition(array('Profile.fullname:LIKE' => '%' . $query . '%'));
$c->orCondition(array('Profile.email:LIKE' => '%' . $query . '%'));
}
$userGroup = $this->getProperty('usergroup', 0);
if (!empty($userGroup)) {
$c->innerJoin('modUserGroupMember', 'UserGroupMembers');
$c->where(array('UserGroupMembers.user_group' => $userGroup));
}
return $c;
}
/**
* Filter on status and add task data
*
* @param xPDOQuery $c
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('sTask', 'Task');
$c->select($this->modx->getSelectColumns($this->classKey, $this->classKey));
$c->select($this->modx->getSelectColumns('sTask', 'Task', 'task_'));
$c->where($this->additionalWhere);
$query = $this->getProperty('query');
if (!empty($query)) {
$c->andCondition(array('Task.reference:LIKE' => '%' . $query . '%'));
}
$namespace = $this->getProperty('namespace');
if (!empty($namespace)) {
$c->andCondition(array('Task.namespace' => $namespace));
}
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->leftJoin('modUserProfile', 'Profile');
$query = $this->getProperty('query', '');
if (!empty($query)) {
$c->where(array('modUser.username:LIKE' => '%' . $query . '%', 'OR:Profile.fullname:LIKE' => '%' . $query . '%', 'OR:Profile.email:LIKE' => '%' . $query . '%'));
}
$userGroup = $this->getProperty('usergroup', 0);
if (!empty($userGroup)) {
if ($userGroup === 'anonymous') {
$c->join('modUserGroupMember', 'UserGroupMembers', 'LEFT OUTER JOIN');
$c->where(array('UserGroupMembers.user_group' => NULL));
} else {
$c->distinct();
$c->innerJoin('modUserGroupMember', 'UserGroupMembers');
$c->where(array('UserGroupMembers.user_group' => $userGroup));
}
}
return $c;
}
/**
* Can be used to adjust the query prior to the COUNT statement
*
* @param xPDOQuery $c
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$categoryId = (int) $this->getProperty('category_id');
if (!empty($categoryId)) {
$c->innerJoin('vnewsNewslettersHasCategories', 'vnewsNewslettersHasCategories');
$c->leftJoin('vnewsCategories', 'vnewsCategories', 'vnewsCategories.id = vnewsNewslettersHasCategories.category_id');
$c->where(array('vnewsCategories.id' => $categoryId));
} elseif ($categoryId === 0) {
$criteria = $this->modx->newQuery('vnewsNewslettersHasCategories');
$criteria->distinct();
$newsHasCats = $this->modx->getCollection('vnewsNewslettersHasCategories', $criteria);
$newsHasCatsArray = array();
if ($newsHasCats) {
foreach ($newsHasCats as $item) {
$newsHasCatsArray[] = $item->get('newsletter_id');
}
}
$c->where(array('id:NOT IN' => $newsHasCatsArray));
}
$c->where(array('parent_id' => 0));
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$ugns = $this->modx->user->getUserGroupNames();
$userGroupNames = '';
foreach ($ugns as $ugn) {
$userGroupNames .= '"' . $ugn . '",';
}
$userGroupNames = rtrim($userGroupNames, ',');
$c->leftJoin('modUser', 'Author');
$c->leftJoin('modResource', 'Resource');
$c->innerJoin('quipThread', 'Thread');
$c->where(array('quipComment.deleted' => $this->getProperty('deleted', false), 'quipComment.approved' => false));
/* handle moderator permissions */
$c->andCondition(array('(
Thread.moderated = 0
OR Thread.moderator_group IN (' . $userGroupNames . ')
OR "' . $this->modx->user->get('username') . '" IN (Thread.moderators)
)'));
$thread = $this->getProperty('thread');
if (!empty($thread)) {
$c->where(array('quipComment.thread' => $thread));
}
return $c;
}
/** {@inheritDoc} */
public function prepareQueryBeforeCount(xPDOQuery $c)
{
if ($master = $this->getProperty('master')) {
$c->orCondition(array('master' => $master, 'slave' => $master));
}
$c->innerJoin('msLink', 'msLink', 'msProductLink.link=msLink.id');
$c->leftJoin('msProduct', 'Master', 'Master.id=msProductLink.master');
$c->leftJoin('msProduct', 'Slave', 'Slave.id=msProductLink.slave');
$c->select($this->modx->getSelectColumns('msProductLink', 'msProductLink'));
$c->select($this->modx->getSelectColumns('msLink', 'msLink', '', array('id'), true));
$c->select('`Master`.`pagetitle` as `master_pagetitle`, `Slave`.`pagetitle` as `slave_pagetitle`');
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('ShopmodxProduct', 'Product');
return parent::prepareQueryBeforeCount($c);
}
/**
* Filter by system event
*
* @param xPDOQuery $c
* @return xPDOQuery
*/
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('modPluginEvent', 'modPluginEvent', array('modPluginEvent.pluginid = modPlugin.id', 'modPluginEvent.event' => $this->getProperty('event')));
return $c;
}
public function prepareQueryBeforeCount(xPDOQuery $c)
{
$c->innerJoin('modUser', 'EditedBy');
$c->where(array('deleted' => 0, 'editedon:!=' => null));
return $c;
}