/**
* @return JDatabaseQuery
*/
function getListQuery()
{
// Set the archive ordering
$params = $this->state->params;
$articleOrderby = $params->get('orderby_sec', 'rdate');
$articleOrderDate = $params->get('order_date');
// No category ordering
$categoryOrderby = '';
$secondary = TZ_PortfolioHelperQuery::orderbySecondary($articleOrderby, $articleOrderDate) . ', ';
$primary = TZ_PortfolioHelperQuery::orderbyPrimary($categoryOrderby);
$orderby = $primary . ' ' . $secondary . ' a.created DESC ';
$this->setState('list.ordering', $orderby);
$this->setState('list.direction', '');
// Create a new query object.
$query = parent::getListQuery();
// Add routing for archive
//sqlsrv changes
$case_when = ' CASE WHEN ';
$case_when .= $query->charLength('a.alias');
$case_when .= ' THEN ';
$a_id = $query->castAsChar('a.id');
$case_when .= $query->concatenate(array($a_id, 'a.alias'), ':');
$case_when .= ' ELSE ';
$case_when .= $a_id . ' END as slug';
$query->select($case_when);
$case_when = ' CASE WHEN ';
$case_when .= $query->charLength('c.alias');
$case_when .= ' THEN ';
$c_id = $query->castAsChar('c.id');
$case_when .= $query->concatenate(array($c_id, 'c.alias'), ':');
$case_when .= ' ELSE ';
$case_when .= $c_id . ' END as catslug';
$query->select($case_when);
// Filter on month, year
// First, get the date field
$queryDate = TZ_PortfolioHelperQuery::getQueryDate($articleOrderDate);
if ($month = $this->getState('filter.month')) {
$query->where('MONTH(' . $queryDate . ') = ' . $month);
}
if ($year = $this->getState('filter.year')) {
$query->where('YEAR(' . $queryDate . ') = ' . $year);
}
//echo nl2br(str_replace('#__','jos_',$query));
return $query;
}
