/**
* Adds Count Items for Category Manager.
*
* @param JDatabaseQuery $query The query object of com_categories
*
* @return JDatabaseQuery
*
* @since 3.4
*/
public static function countItems($query)
{
// Join articles to categories and count published items
$query->select('COUNT(DISTINCT cp.id) AS count_published');
$query->join('LEFT', '#__newsfeeds AS cp ON cp.catid = a.id AND cp.published = 1');
// Count unpublished items
$query->select('COUNT(DISTINCT cu.id) AS count_unpublished');
$query->join('LEFT', '#__newsfeeds AS cu ON cu.catid = a.id AND cu.published = 0');
// Count archived items
$query->select('COUNT(DISTINCT ca.id) AS count_archived');
$query->join('LEFT', '#__newsfeeds AS ca ON ca.catid = a.id AND ca.published = 2');
// Count trashed items
$query->select('COUNT(DISTINCT ct.id) AS count_trashed');
$query->join('LEFT', '#__newsfeeds AS ct ON ct.catid = a.id AND ct.published = -2');
return $query;
}
/**
* Tests the JDatabaseQuery::join method.
*
* @return void
*
* @covers JDatabaseQuery::join
* @since 11.3
*/
public function testJoin()
{
$this->assertThat($this->_instance->join('INNER', 'foo ON foo.id = bar.id'), $this->identicalTo($this->_instance), 'Tests chaining.');
$this->assertThat(trim($this->_instance->join[0]), $this->equalTo('INNER JOIN foo ON foo.id = bar.id'), 'Tests that first join renders correctly.');
$this->_instance->join('OUTER', 'goo ON goo.id = car.id');
$this->assertThat(trim($this->_instance->join[1]), $this->equalTo('OUTER JOIN goo ON goo.id = car.id'), 'Tests that second join renders correctly.');
}
/**
* @param array $filters
* @param array $sort_filters
*/
public function process(array $filters, array $sort_filters = array(), $showUnpublished = false)
{
$this->showUnpublished = $showUnpublished;
$this->setAccessWhere();
$this->setDisplayedWhere();
foreach ($filters as $filtertype => $fitlerdata) {
if (!method_exists($this, $filtertype)) {
//throw new RokSprocket_Exception(rc__('Unknown Filter %s', $filtertype));
} else {
$this->{$filtertype}($fitlerdata);
}
}
foreach ($sort_filters as $sort_filtertype => $sort_fitlerdata) {
$sort_function = 'sort_' . $sort_filtertype;
if (!method_exists($this, 'sort_' . $sort_filtertype)) {
//throw new RokSprocket_Exception(rc__('Unknown Filter %s', $filtertype));
} else {
$this->{$sort_function}($sort_fitlerdata);
}
}
if (!empty($this->access_where)) {
$access_where = sprintf('(%s)', implode(' AND ', $this->access_where));
$article_where_parts[] = $access_where;
$filter_where_parts[] = $access_where;
}
if (!empty($this->displayed_where)) {
$displayed_where = sprintf('(%s)', implode(' AND ', $this->displayed_where));
$article_where_parts[] = $displayed_where;
$filter_where_parts[] = $displayed_where;
}
if (!empty($this->article_where)) {
$article_where_parts[] = implode(' AND ', $this->article_where);
$this->query->where(sprintf('(%s)', implode(' AND ', $article_where_parts)), 'OR');
}
if (!empty($this->filter_where)) {
$filter_where_parts[] = implode(' AND ', $this->filter_where);
$this->query->where(sprintf('(%s)', implode(' AND ', $filter_where_parts)), 'OR');
}
if (empty($this->article_where) && empty($this->filter_where)) {
$this->query->where('0=1');
}
if ($this->manualSort) {
$this->query->join('LEFT OUTER', sprintf('(select rsi.provider_id, rsi.order from #__roksprocket_items as rsi where module_id = %d) rsi on a.id = rsi.provider_id', $this->moduleId));
array_unshift($this->sort_order, 'rsi.order');
if ($this->manualAppend == 'after') {
array_unshift($this->sort_order, 'IF(ISNULL(rsi.order),1,0)');
}
}
foreach ($this->sort_order as $sort) {
$this->query->order($sort);
}
}
/**
* add filter to query
*
* @param JDatabaseQuery $query query object
*
* @return JDatabaseQuery
*/
public function addFilter(JDatabaseQuery $query)
{
if (!$this->filterField) {
return $query;
}
$db = JFactory::getDbo();
//since joomla 3.0 filter_value can be '' too not only filterNullValue
if (isset($this->filter_value) && strlen($this->filter_value) > 0 && $this->filter_value != $this->filterNullValue) {
$query->join('INNER', '#__rwf_fields AS rwff ON rwff.id = c.field_id');
$query->where("rwff.form_id = " . $db->escape($this->filter_value, true));
}
return $query;
}
static function &getModules()
{
static $modules;
if (!is_null($modules))
return $modules;
$mainframe = JFactory::getApplication();
$user =& JFactory::getUser();
$db =& JFactory::getDBO();
$aid = $user->get('aid', 0);
$groups = implode(',', $user->getAuthorisedViewLevels());
$query = new JDatabaseQuery;
$query->select('id, title, module, position, content, showtitle, params, mm.menuid');
$query->from('#__modules AS m');
$query->join('LEFT','#__modules_menu AS mm ON mm.moduleid = m.id');
$query->where('m.published = 1');
if (!$user->authorise('core.admin',1)) {
$query->where('m.access IN (' . $groups . ')');
}
$query->where('m.client_id = ' . (int)$mainframe->getClientId());
$query->order('position, ordering');
$db->setQuery($query);
$modules = $db->loadObjectList('id');
if ($db->getErrorNum())
{
$modules = array();
}
foreach ($modules as $key => $mod)
{
$module =& $modules[$key];
$file = $module->module;
$custom = substr($file, 0, 4) == 'mod_' ? 0 : 1;
$module->user = $custom;
$module->name = $custom ? $module->title : substr( $file, 4 );
$module->style = null;
$module->position = strtolower($module->position);
}
return $modules;
}
/**
* Load published modules
*
* @return array
*/
protected static function &_load()
{
static $clean;
if (isset($clean)) {
return $clean;
}
$Itemid = JRequest::getInt('Itemid');
$app = JFactory::getApplication();
$user = JFactory::getUser();
$groups = implode(',', $user->getAuthorisedViewLevels());
$lang = JFactory::getLanguage()->getTag();
$clientId = (int) $app->getClientId();
$cache = JFactory::getCache('com_modules', '');
$cacheid = md5(serialize(array($Itemid, $groups, $clientId, $lang)));
if (!($clean = $cache->get($cacheid))) {
$db = JFactory::getDbo();
$query = new JDatabaseQuery();
$query->select('id, title, module, position, content, showtitle, params, mm.menuid');
$query->from('#__modules AS m');
$query->join('LEFT', '#__modules_menu AS mm ON mm.moduleid = m.id');
$query->where('m.published = 1');
$date = JFactory::getDate();
$now = $date->toMySQL();
$nullDate = $db->getNullDate();
$query->where('(m.publish_up = ' . $db->Quote($nullDate) . ' OR m.publish_up <= ' . $db->Quote($now) . ')');
$query->where('(m.publish_down = ' . $db->Quote($nullDate) . ' OR m.publish_down >= ' . $db->Quote($now) . ')');
$query->where('m.access IN (' . $groups . ')');
$query->where('m.client_id = ' . $clientId);
$query->where('(mm.menuid = ' . (int) $Itemid . ' OR mm.menuid <= 0)');
// Filter by language
if ($app->isSite() && $app->getLanguageFilter()) {
$query->where('m.language IN (' . $db->Quote($lang) . ',' . $db->Quote('*') . ')');
}
$query->order('position, ordering');
// Set the query
$db->setQuery($query);
$modules = $db->loadObjectList();
$clean = array();
if ($db->getErrorNum()) {
JError::raiseWarning(500, JText::sprintf('JLIB_APPLICATION_ERROR_MODULE_LOAD', $db->getErrorMsg()));
return $clean;
}
// Apply negative selections and eliminate duplicates
$negId = $Itemid ? -(int) $Itemid : false;
$dupes = array();
for ($i = 0, $n = count($modules); $i < $n; $i++) {
$module =& $modules[$i];
// The module is excluded if there is an explicit prohibition, or if
// the Itemid is missing or zero and the module is in exclude mode.
$negHit = $negId === (int) $module->menuid || !$negId && (int) $module->menuid < 0;
if (isset($dupes[$module->id])) {
// If this item has been excluded, keep the duplicate flag set,
// but remove any item from the cleaned array.
if ($negHit) {
unset($clean[$module->id]);
}
continue;
}
$dupes[$module->id] = true;
// Only accept modules without explicit exclusions.
if (!$negHit) {
//determine if this is a custom module
$file = $module->module;
$custom = substr($file, 0, 4) == 'mod_' ? 0 : 1;
$module->user = $custom;
// Custom module name is given by the title field, otherwise strip off "com_"
$module->name = $custom ? $module->title : substr($file, 4);
$module->style = null;
$module->position = strtolower($module->position);
$clean[$module->id] = $module;
}
}
unset($dupes);
// Return to simple indexing that matches the query order.
$clean = array_values($clean);
$cache->store($clean, $cacheid);
}
return $clean;
}
protected function modifyQuery(JDatabaseQuery &$query)
{
$query->where("NOT deleted");
if (isset($this->leader)) {
if ($this->leader instanceof Leader) {
$query->where("leaderid = " . $this->leader->id);
} else {
if (is_int($this->leader)) {
$query->where("leaderid = " . $this->leader);
} else {
if (ctype_digit($this->leader)) {
$query->where("leaderid = " . (int) $this->leader);
}
}
}
}
if (isset($this->walkProgramme)) {
if ($this->walkProgramme instanceof WalkProgramme) {
$wpID = $this->walkProgramme->id;
} else {
$wpID = (int) $this->walkProgramme;
}
$query->join('INNER', 'walkprogrammewalklinks ON WalkProgrammeWalkID = walkprogrammewalks.SequenceID');
$query->where("walkprogrammewalklinks.ProgrammeID = " . $wpID);
}
if (isset($this->startTimeMax)) {
$query->where("meettime <= '" . strftime("%H:%M", $this->startTimeMax) . "'");
}
}
/**
* Get the part of the sql query that creates the joins
* used when building the table's data
*
* @param bool|JDatabaseQuery $query JQuery object or false
*
* @return JDatabaseQuery|string string or join query - join sql
*/
public function buildQueryJoin($query = false)
{
$db = FabrikWorker::getDbo();
$ref = $query ? '1' : '0';
if (isset($this->joinsSQL[$ref])) {
return $this->joinsSQL[$ref];
}
$statements = array();
$table = $this->getTable();
$selectedTables[] = $table->db_table_name;
$return = array();
$joins = $this->get('includeCddInJoin', true) === false ? $this->getJoinsNoCdd() : $this->getJoins();
foreach ($joins as $join) {
// Used to bypass user joins if the table connect isnt the Joomla connection
if ((int) $join->canUse === 0) {
continue;
}
if ($join->join_type == '') {
$join->join_type = 'LEFT';
}
$sql = JString::strtoupper($join->join_type) . ' JOIN ' . $db->qn($join->table_join);
$k = FabrikString::safeColName($join->keytable . '.' . $join->table_key);
// Check we only get the field name
$join->table_join_key = explode('.', $join->table_join_key);
$join->table_join_key = array_pop($join->table_join_key);
if ($join->table_join_alias == '') {
$on = FabrikString::safeColName($join->table_join . '.' . $join->table_join_key);
$sql .= ' ON ' . $on . ' = ' . $k;
} else {
$on = FabrikString::safeColName($join->table_join_alias . '.' . $join->table_join_key);
$sql .= ' AS ' . FabrikString::safeColName($join->table_join_alias) . ' ON ' . $on . ' = ' . $k . "\n";
}
/*
* @FIXME - need to work out where the COLLATE needs to go. Was at the end of builQuery, but that creates
* an invalid query if we had any grouping. As it only applies to joined tables, tried putting it here,
* but still get a query error. Needs to be fixed, but I have to commit to get some other changes done.
*/
/*
$params = $this->getParams();
if ($params->get('force_collate', '') !== '')
{
$sql .= ' COLLATE ' . $params->get('force_collate', '') . ' ';
}
*/
/* Try to order join statements to ensure that you are selecting from tables that have
* already been included (either via a previous join statement or the table select statement)
*/
if (in_array($join->keytable, $selectedTables)) {
$return[] = $sql;
$selectedTables[] = $join->table_join;
} else {
// Didn't find anything so defer it till later
/* $statements[$join->keytable] = $sql;
* $$$rob - sometimes the keytable is the same for 2 deferred joins
* in this case the first join is incorrectly overwritten in the $statements array
* keying on join->id should solve this
*/
$statements[$join->id] = array($join->keytable, $sql);
}
// Go through the deferred join statements and see if their table has now been selected
foreach ($statements as $joinId => $ar) {
$t = $ar[0];
$s = $ar[1];
if (in_array($t, $selectedTables)) {
if (!in_array($s, $return)) {
// $$$rob test to avoid duplicate join queries
$return[] = $s;
unset($statements[$t]);
}
}
}
}
// $$$rob test for bug #376
foreach ($statements as $joinId => $ar) {
$s = $ar[1];
if (!in_array($s, $return)) {
$return[] = $s;
}
}
if ($query !== false) {
foreach ($return as $r) {
$words = explode(' ', trim($r));
$type = array_shift($words);
$statement = str_replace('JOIN', '', implode(' ', $words));
$query->join($type, $statement);
}
return $query;
} else {
$return = implode(' ', $return);
$this->joinsSQL[$ref] = $return;
}
return $query == false ? $return : $query;
}
function getModule($id = 0, $name = '')
{
$Itemid = $this->Itemid;
$app = JFactory::getApplication();
$user = JFactory::getUser();
$groups = implode(',', $user->authorisedLevels());
$db = JFactory::getDbo();
$query = new JDatabaseQuery();
$query->select('id, title, module, position, content, showtitle, params, mm.menuid');
$query->from('#__modules AS m');
$query->join('LEFT', '#__modules_menu AS mm ON mm.moduleid = m.id');
$query->where('m.published = 1');
$query->where('m.id = ' . $id);
$date = JFactory::getDate();
$now = $date->toSql();
$nullDate = $db->getNullDate();
$query->where('(m.publish_up = ' . $db->Quote($nullDate) . ' OR m.publish_up <= ' . $db->Quote($now) . ')');
$query->where('(m.publish_down = ' . $db->Quote($nullDate) . ' OR m.publish_down >= ' . $db->Quote($now) . ')');
$clientid = (int) $app->getClientId();
if (!$user->authorise('core.admin', 1)) {
$query->where('m.access IN (' . $groups . ')');
}
$query->where('m.client_id = ' . $clientid);
if (isset($Itemid)) {
$query->where('(mm.menuid = ' . (int) $Itemid . ' OR mm.menuid <= 0)');
}
$query->order('position, ordering');
// Filter by language
if ($app->isSite() && $app->getLanguageFilter()) {
$query->where('m.language in (' . $db->Quote(JFactory::getLanguage()->getTag()) . ',' . $db->Quote('*') . ')');
}
// Set the query
$db->setQuery($query);
$cache = JFactory::getCache('com_modules', 'callback');
$cacheid = md5(serialize(array($Itemid, $groups, $clientid, JFactory::getLanguage()->getTag(), $id)));
$module = $cache->get(array($db, 'loadObject'), null, $cacheid, false);
if (!$module) {
return null;
}
$negId = $Itemid ? -(int) $Itemid : false;
// The module is excluded if there is an explicit prohibition, or if
// the Itemid is missing or zero and the module is in exclude mode.
$negHit = $negId === (int) $module->menuid || !$negId && (int) $module->menuid < 0;
// Only accept modules without explicit exclusions.
if (!$negHit) {
//determine if this is a custom module
$file = $module->module;
$custom = substr($file, 0, 4) == 'mod_' ? 0 : 1;
$module->user = $custom;
// Custom module name is given by the title field, otherwise strip off "com_"
$module->name = $custom ? $module->title : substr($file, 4);
$module->style = null;
$module->position = strtolower($module->position);
$clean[$module->id] = $module;
}
return $module;
}
/**
* registerQueryTables
*
* @param DatabaseQuery $query
*
* @return DatabaseQuery
*/
public function registerQueryTables(DatabaseQuery $query)
{
foreach ($this->tables as $alias => $table) {
if ($table['join'] == 'FROM') {
$query->from($query->quoteName($table['name']) . ' AS ' . $query->quoteName($alias));
} else {
$query->join($table['join'], $query->quoteName($table['name']) . ' AS ' . $query->quoteName($alias) . ' ' . $table['condition']);
}
}
return $query;
}
/**
* Apply currently set filters to the database query
* @param JDatabaseQuery $query
*/
protected function applyFilters(JDatabaseQuery &$query)
{
if ($this->useEventsTable) {
$query->from("events");
$query->leftJoin($this->table . " USING (" . $this->idField . ")");
} else {
$query->from($this->table);
}
$query->where(array($this->startDateField . " >= '" . Event::timeToDate($this->startDate) . "'", $this->startDateField . " <= '" . Event::timeToDate($this->endDate) . "'"));
if (!$this->showUnpublished) {
$query->where($this->readyToPublishField);
}
// Filter by attendees
if (!empty($this->filterAttendedBy)) {
$query->join("INNER", "eventattendance ON eventattendance.eventtype=" . $this->eventTypeConst . " AND eventattendance.eventid=" . $this->table . "." . $this->idField);
$query->where("eventattendance.user IN (" . implode(",", $this->filterAttendedBy) . ")");
}
// This allows subclasses to modify the query. Normally they'll add extra WHERE clauses.
$this->modifyQuery($query);
}
