/**
* Returns query object to retrieve list of recently viewed records by
* module.
*
* @param SugarBean $seed Instance of current bean.
* @param array $options Prepared options.
* @return SugarQuery query to execute.
*/
protected function getRecentlyViewedQueryObject($seed, $options)
{
$currentUser = $this->getUserBean();
$query = new SugarQuery();
$query->from($seed);
// FIXME: FRM-226, logic for these needs to be moved to SugarQuery
// Since tracker relationships don't actually exist, we're gonna have to add a direct join
$query->joinRaw(sprintf(" JOIN tracker ON tracker.item_id=%s.id AND tracker.module_name='%s' AND tracker.user_id='%s' ", $query->from->getTableName(), $query->from->module_name, $currentUser->id), array('alias' => 'tracker'));
// we need to set the linkName to hack around tracker not having real relationships
/* TODO think about how to fix this so we can be less restrictive to raw joins that don't have a relationship */
$query->join['tracker']->linkName = 'tracker';
$query->select(array('id', array('tracker.module_name', 'module_name')));
if (!empty($options['date'])) {
$td = new SugarDateTime();
$td->modify($options['date']);
$query->where()->queryAnd()->gte('tracker.date_modified', $td->asDb());
}
foreach ($query->select()->select as $v) {
$query->groupBy($v->table . '.' . $v->field);
}
$query->select()->fieldRaw('MAX(tracker.date_modified)', 'last_viewed_date');
return $query;
}
protected static function addTrackerFilter(SugarQuery $q, SugarQuery_Builder_Where $where, $interval)
{
global $db;
$td = new SugarDateTime();
$td->modify($interval);
$min_date = $td->asDb();
// Have to do a subselect because MAX() and GROUP BY don't get along with
// databases other than MySQL
$q->joinRaw(" INNER JOIN ( SELECT t.item_id item_id, MAX(t.date_modified) track_max " . " FROM tracker t " . " WHERE t.module_name = '" . $db->quote($q->from->module_name) . "' " . " AND t.user_id = '" . $db->quote($GLOBALS['current_user']->id) . "' " . " AND t.date_modified >= " . $db->convert("'" . $min_date . "'", 'datetime') . " " . " GROUP BY t.item_id " . " ) tracker ON tracker.item_id = " . $q->from->getTableName() . ".id ", array('alias' => 'tracker'));
// Now, if they want tracker records, so let's order it by the tracker date_modified
$q->order_by = array();
$q->orderByRaw('tracker.track_max', 'DESC');
$q->distinct(false);
}
