public static function getActivity()
{
$db = \Cobalt\Container::fetch('db');
$query = $db->getQuery(true);
$query->select('h.*, CONCAT(u.first_name," ", u.last_name) AS owner_name, c.name as company_name, CONCAT(p.first_name," ", p.last_name) AS person_name,
d.name as deal_name, e.name as event_name, note_cat.name as notes_category_name,
event_cat.name as events_category_name, old_event_cat.name as events_category_name_old, old_note_cat.name AS notes_category_name_old,
doc.name AS document_name,status.name AS deal_status_name_old, status2.name AS deal_status_name,deal_source.name AS deal_source_name_old,deal_source_2.name AS deal_source_name,
deal_stage.name AS deal_stage_name_old,deal_stage_2.name AS deal_stage_name,CONCAT(deal_owner.first_name," ",deal_owner.last_name) AS deal_owner_name_old,
CONCAT(deal_owner_2.first_name," ",deal_owner_2.last_name) AS deal_owner_name
');
$query->from('#__history AS h');
$query->leftJoin('#__users AS u ON u.id = h.user_id');
$query->leftJoin('#__companies AS c ON c.id = h.type_id AND h.type="company"');
$query->leftJoin('#__notes AS n ON n.id = h.type_id AND h.type="note"');
$query->leftJoin('#__deals AS d on d.id = h.type_id AND h.type="deal"');
$query->leftJoin('#__people AS p on p.id = h.type_id AND h.type="person"');
$query->leftJoin('#__goals AS g on g.id = h.type_id AND h.type="goal"');
$query->leftJoin('#__events AS e on e.id = h.type_id AND h.type="event"');
$query->leftJoin('#__reports AS r on r.id = h.type_id AND h.type="report"');
$query->leftJoin('#__documents AS doc ON doc.id = h.type_id AND h.type="document"');
$query->leftJoin('#__notes_categories as note_cat ON note_cat.id = h.new_value AND h.field="category_id" AND h.type="notes"');
$query->leftJoin('#__events_categories as event_cat ON event_cat.id = h.new_value AND h.field="category_id" AND h.type="events"');
$query->leftJoin('#__notes_categories as old_note_cat ON old_note_cat.id = h.old_value AND h.field="category_id" AND h.type="notes"');
$query->leftJoin('#__events_categories as old_event_cat ON old_event_cat.id = h.old_value AND h.field="category_id" AND h.type="events"');
$query->leftJoin("#__deal_status AS status ON status.id = h.old_value AND h.type='deal'");
$query->leftJoin("#__deal_status AS status2 ON status2.id = h.new_value AND h.type='deal'");
$query->leftJoin("#__sources AS deal_source ON deal_source.id = h.old_value AND h.type='deal'");
$query->leftJoin("#__sources AS deal_source_2 ON deal_source_2.id = h.new_value AND h.type='deal'");
$query->leftJoin("#__stages AS deal_stage ON deal_stage.id = h.old_value AND h.type='deal'");
$query->leftJoin("#__stages AS deal_stage_2 ON deal_stage_2.id = h.new_value AND h.type='deal'");
$query->leftJoin("#__users AS deal_owner ON deal_owner.id = h.old_value AND h.type='deal'");
$query->leftJoin("#__users AS deal_owner_2 ON deal_owner_2.id = h.new_value AND h.type='deal'");
$member_id = UsersHelper::getUserId();
$member_role = UsersHelper::getRole();
$team_id = UsersHelper::getTeamId();
if ($member_role != 'exec') {
//manager filter
if ($member_role == 'manager') {
$query->where('u.team_id = ' . $team_id);
} else {
//basic user filter
$query->where(array('h.user_id = ' . $member_id));
}
}
//TODO: Add assignees to the display (massive left join)
$query->where('h.field!="assignee_id" AND h.field!="repeats"');
$query->order('h.date DESC');
if (self::$limit != null) {
$query .= " LIMIT " . self::$limit;
} else {
$query .= " LIMIT 10";
}
$db->setQuery($query);
$activity = $db->loadObjectList();
return $activity;
}
