/**
* Prepare result ordering.
*
* @param \JDatabaseQuery $query
* @param array $options
*/
protected function prepareOrder(&$query, $options)
{
// Filter by state.
if (array_key_exists('order', $options)) {
// Prepare direction of ordering.
$direction = !array_key_exists('order_dir', $options) ? 'DESC' : $options['order_dir'];
if (!in_array($direction, $this->allowedDirections, true)) {
$direction = 'DESC';
}
switch ($options['order']) {
case Constants::ORDER_BY_LOCATION_NAME:
// Order by location name.
$query->order('l.name ' . $direction);
break;
case Constants::ORDER_BY_NUMBER_OF_PROJECTS:
// Order by location name.
$query->order('project_number ' . $direction);
break;
default:
// Order by project title.
$query->order('a.title ' . $direction);
break;
}
}
}
/**
* Set order by field and direction.
*
* This function can be used more than once to chain order by.
*
* @param string $by
* @param int $direction
* @param string $alias
*
* @return $this
*/
public function order($by, $direction = 1, $alias = 'a')
{
$direction = $direction > 0 ? 'ASC' : 'DESC';
$by = $alias . '.' . $this->db->quoteName($by);
$this->query->order("{$by} {$direction}");
return $this;
}
/**
* Tests the JDatabaseQuery::union method.
*
* @return void
*
* @covers JDatabaseQuery::union
* @since 12.1
*/
public function testUnionClear()
{
$this->_instance->union = null;
$this->_instance->order = null;
$this->_instance->order('bar');
$this->_instance->union('SELECT name FROM foo');
$this->assertThat($this->_instance->order, $this->equalTo(null), 'Tests that ORDER BY is cleared with union.');
}
/**
* Tests the JDatabaseQuery::union method.
*
* @return void
*
* @since 12.1
*/
public function testUnionClear()
{
TestReflection::setValue($this->_instance, 'union', null);
TestReflection::setValue($this->_instance, 'order', null);
$this->_instance->order('bar');
$this->_instance->union('SELECT name FROM foo');
$this->assertThat(TestReflection::getValue($this->_instance, 'order'), $this->equalTo(null), 'Tests that ORDER BY is cleared with union.');
}
/**
* Tests the JDatabaseQuery::order method.
*
* @return void
*
* @since 11.3
*/
public function testOrder()
{
$this->assertThat($this->_instance->order('foo'), $this->identicalTo($this->_instance), 'Tests chaining.');
$this->assertThat(trim(TestReflection::getValue($this->_instance, 'order')), $this->equalTo('ORDER BY foo'), 'Tests rendered value.');
// Add another column.
$this->_instance->order('bar');
$this->assertThat(trim(TestReflection::getValue($this->_instance, 'order')), $this->equalTo('ORDER BY foo,bar'), 'Tests rendered value after second use.');
$this->_instance->order(array('goo', 'car'));
$this->assertThat(trim(TestReflection::getValue($this->_instance, 'order')), $this->equalTo('ORDER BY foo,bar,goo,car'), 'Tests array input.');
}
/**
* @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);
}
}
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;
}
/**
* @param array $filters
* @param array $sort_filters
*/
public function process(array $filters, array $sort_filters = array(), $showUnpublished = false)
{
$this->showUnpublished = $showUnpublished;
$this->setAccessWhere();
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->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');
}
foreach ($this->sort_order as $sort) {
$this->query->order($sort);
}
}
/**
* 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;
}
/**
* Prepare result ordering.
*
* @param \JDatabaseQuery $query
* @param array $options
*/
protected function prepareOrder(&$query, $options)
{
// Filter by state.
if (isset($options["order"])) {
// Prepare direction of ordering.
$direction = !isset($options["order_dir"]) ? "DESC" : $options["order_dir"];
if (!in_array($direction, $this->allowedDirections)) {
$direction = "DESC";
}
switch ($options["order"]) {
case Constants::ORDER_BY_LOCATION_NAME:
// Order by location name.
$query->order("l.name " . $direction);
break;
case Constants::ORDER_BY_NUMBER_OF_PROJECTS:
// Order by location name.
$query->order("project_number " . $direction);
break;
default:
// Order by project title.
$query->order("a.title " . $direction);
break;
}
}
}
/**
* Get options order by
*
* @param string $view View mode '' or 'filter'
* @param JDatabaseQuery|bool $query Set to false to return a string
*
* @return string order by statement
*/
protected function getOrderBy($view = '', $query = false)
{
$params = $this->getParams();
$orderBy = $params->get('notes_order_element');
if ($orderBy == '') {
return $query ? $query : '';
} else {
$order = FabrikString::safeQuoteName($params->get('join_db_name') . '.' . $orderBy) . ' ' . $params->get('notes_order_dir', 'ASC');
if ($query) {
$query->order($order);
return $query;
}
return " ORDER BY " . $order;
}
}
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;
}
/**
* Process ordering query.
*
* @param \JDatabaseQuery $query The query object.
* @param string $ordering The ordering string.
* @param string $direction ASC or DESC.
*
* @return void
*/
protected function processOrdering(\JDatabaseQuery $query, $ordering = null, $direction = null)
{
$ordering = $ordering ?: $this->get('list.ordering');
// If no ordering set, ignore this function.
if (!$ordering) {
return;
}
$direction = $direction ?: $this->get('list.direction', 'ASC');
$ordering = explode(',', $ordering);
// Add quote
foreach ($ordering as $key => &$value) {
// Remove extra spaces
$value = preg_replace('/\\s+/', ' ', trim($value));
$value = StringHelper::explode(' ', $value);
if (!$this->filterField($value[0])) {
unset($ordering[$key]);
continue;
}
$value[0] = $this->mapField($value[0]);
// Ignore expression
if (!empty($value[0]) && $value[0][strlen($value[0]) - 1] != ')') {
$value[0] = $query->quoteName($value[0]);
}
$value = implode(' ', $value);
}
$ordering = implode(', ', $ordering);
if (!$ordering) {
return;
}
$query->order($ordering . ' ' . $direction);
}
/**
* Triggered after building the query. If we're in the front-end we force the sorting to be always by ordering,
* ascending
*
* @param \JDatabaseQuery $query
* @param bool $overrideLimits
*/
protected function onAfterBuildQuery(\JDatabaseQuery &$query, $overrideLimits = false)
{
if ($this->container->platform->isFrontend()) {
$query->order($this->getDbo()->qn('ordering') . ' ASC');
}
}
private function add_product_rows_query_order(JDatabaseQuery $query)
{
$sort_data = $this->get_sort_data();
$sort_by = '';
$sort_order = $sort_data['sort_order'] == "asc" || $sort_data['sort_order'] == "desc" || $sort_data['sort_order'] == "" ? strtoupper($sort_data['sort_order']) : "ASC";
switch ($sort_data['sort_by']) {
case 'ordering':
$sort_by = 'T_PRODUCTS.ordering';
break;
case 'name':
$sort_by = 'T_PRODUCTS.name';
break;
case 'manufacturer':
$sort_by = 'T_MANUFACTURERS.name';
break;
case 'price':
$sort_by = 'T_PRODUCTS.price';
break;
case 'reviews_count':
$sort_by = 'T_FEEDBACK.reviews_count';
break;
case 'rating':
$sort_by = 'T_RATINGS.rating';
break;
}
if ($sort_by != '') {
$query->order($sort_by . ' ' . $sort_order);
}
$query->order('T_PRODUCTS.ordering ' . $sort_order);
return $query;
}
protected function _load($id)
{
$db = JFactory::getDbo();
$app = JFactory::getApplication();
$user = JFactory::getUser();
$extension = $this->_extension;
// Record that this $id has been checked
$this->_checkedCategories[$id] = true;
$query = new JDatabaseQuery();
// right join with c for category
$query->select('c.*');
$query->select('CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as slug');
$query->from('#__categories as c');
$query->where('(c.extension=' . $db->Quote($extension) . ' OR c.extension=' . $db->Quote('system') . ')');
if ($this->_options['access']) {
$query->where('c.access IN (' . implode(',', $user->getAuthorisedViewLevels()) . ')');
}
if ($this->_options['published'] == 1) {
$query->where('c.published = 1');
}
$query->order('c.lft');
// s for selected id
if ($id != 'root') {
// Get the selected category
$query->leftJoin('#__categories AS s ON (s.lft <= c.lft AND s.rgt >= c.rgt) OR (s.lft > c.lft AND s.rgt < c.rgt)');
$query->where('s.id=' . (int) $id);
}
$subQuery = ' (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ' . 'ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = ' . $db->quote($extension) . ' AND parent.published != 1 GROUP BY cat.id) ';
$query->leftJoin($subQuery . 'AS badcats ON badcats.id = c.id');
$query->where('badcats.id is null');
// i for item
if (isset($this->_options['countItems']) && $this->_options['countItems'] == 1) {
if ($this->_options['published'] == 1) {
$query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id AND i.' . $this->_statefield . ' = 1');
} else {
$query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id');
}
$query->select('COUNT(i.' . $db->nameQuote($this->_key) . ') AS numitems');
}
// Group by
$query->group('c.id');
// Filter by language
if ($app->isSite() && $app->getLanguageFilter()) {
$query->where('(' . ($id != 'root' ? 'c.id=s.id OR ' : '') . 'c.language in (' . $db->Quote(JFactory::getLanguage()->getTag()) . ',' . $db->Quote('*') . '))');
}
// Get the results
$db->setQuery($query);
$results = $db->loadObjectList('id');
$childrenLoaded = false;
if (count($results)) {
// foreach categories
foreach ($results as $result) {
// Deal with root category
if ($result->id == 1) {
$result->id = 'root';
}
// Deal with parent_id
if ($result->parent_id == 1) {
$result->parent_id = 'root';
}
// Create the node
if (!isset($this->_nodes[$result->id])) {
// Create the JCategoryNode and add to _nodes
$this->_nodes[$result->id] = new JCategoryNode($result, $this);
// If this is not root, and if the current nodes parent is in the list or the current node parent is 0
if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) {
// Compute relationship between node and its parent - set the parent in the _nodes field
$this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]);
}
// if the node's parent id is not in the _nodes list and the node is not root (doesn't have parent_id == 0),
// then remove this nodes from the list
if (!(isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) {
unset($this->_nodes[$result->id]);
continue;
}
if ($result->id == $id || $childrenLoaded) {
$this->_nodes[$result->id]->setAllLoaded();
$childrenLoaded = true;
}
} else {
if ($result->id == $id || $childrenLoaded) {
// Create the JCategoryNode
$this->_nodes[$result->id] = new JCategoryNode($result, $this);
if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id)) {
// Compute relationship between node and its parent
$this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]);
}
if (!isset($this->_nodes[$result->parent_id])) {
unset($this->_nodes[$result->id]);
continue;
}
if ($result->id == $id || $childrenLoaded) {
$this->_nodes[$result->id]->setAllLoaded();
$childrenLoaded = true;
}
}
}
}
} else {
$this->_nodes[$id] = null;
}
}
/**
* Get options order by
*
* @param string $view View mode '' or 'filter'
* @param bool|JDatabaseQuery $query Set to false to return a string
*
* @return string order by statement
*/
protected function getOrderBy($view = '', $query = false)
{
if ($view == 'filter') {
$params = $this->getParams();
$joinKey = $this->getJoinValueColumn();
$joinLabel = $this->getJoinLabelColumn();
$order = '';
switch ($params->get('filter_groupby', 'text')) {
case 'text':
$order = $joinLabel . 'ASC ';
break;
case 'value':
$order = $joinKey . 'ASC ';
break;
case '-1':
default:
// Check if the 'Joins where and/or order by statement' has an order by
$joinWhere = $params->get('database_join_where_sql');
if (JString::stristr($joinWhere, 'ORDER BY')) {
$joinWhere = str_replace('order by', 'ORDER BY', $joinWhere);
$joinWhere = explode('ORDER BY', $joinWhere);
if (count($joinWhere) > 1) {
$order = $joinWhere[count($joinWhere) - 1];
}
}
break;
}
if (!$query) {
return $order === '' ? '' : ' ORDER BY ' . $order;
} else {
if ($order !== '') {
$query->order($order);
}
return $query;
}
} else {
if (!empty($this->orderBy)) {
if (!$query) {
return $this->orderBy;
} else {
$order = JString::str_ireplace('ORDER BY', '', $this->orderBy);
$query->order($order);
return $query;
}
} else {
if (!$query) {
return "ORDER BY text ASC ";
} else {
$query->order('text ASC');
return $query;
}
}
}
}
