public function _buildQuery()
{
if ($this->db->name == 'mysqli') {
$this->db->setQuery("SET SQL_BIG_SELECTS=1")->execute();
}
//set defaults
$id = $this->_id;
$type = $this->_type;
$user = $this->_user;
$stage = $this->_stage;
$close = $this->_close;
$team = $this->_team;
$status = $this->_status;
$source = $this->_source;
$modified = $this->_modified;
$created = $this->_created;
$session = $this->_session;
$user_id = $this->_user_id;
$session = $this->app->getSession();
//determine which layout is requesting the information
$view = $this->_view;
$layout = $this->_layout;
//determine if we are sorting//searching for a team or user
if ($team) {
$session->set('deal_user_filter', null);
}
if ($user) {
$session->set('deal_team_filter', null);
}
/** -----------------------------
* Session data for the default deals page
*/
//set user session data
if ($view != "reports") {
if ($type != null) {
$session->set('deal_type_filter', $type);
} else {
$sess_type = $session->get('deal_type_filter');
$type = $sess_type;
}
if ($user != null) {
$session->set('deal_user_filter', $user);
} else {
$sess_user = $session->get('deal_user_filter');
$user = $sess_user;
}
if ($stage != null) {
$session->set('deal_stage_filter', $stage);
} else {
$sess_stage = $session->get('deal_stage_filter');
$stage = $sess_stage;
}
if ($close != null) {
$session->set('deal_close_filter', $close);
} else {
$sess_close = $session->get('deal_close_filter');
$close = $sess_close;
}
if ($team != null) {
$session->set('deal_team_filter', $team);
} else {
$sess_team = $session->get('deal_team_filter');
$team = $sess_team;
}
}
$query = $this->db->getQuery(true);
//construct query string
if ($this->export) {
$queryString = 'd.name,d.summary,d.probability,d.amount,d.actual_close,d.archived,';
$queryString .= 'd.modified,d.category,d.expected_close,d.created,SUM(d.amount) AS filtered_total,';
$queryString .= '( d.amount * ( d.probability / 100 )) AS forecast,';
$queryString .= 'c.name as company_name,';
$queryString .= '(CASE WHEN (stat.name IS NOT NULL) THEN stat.name ELSE NULL END) as status_name,';
$queryString .= 'source.name as source_name,';
$queryString .= 'stage.name as stage_name,stage.percent,';
$queryString .= 'p.first_name as primary_contact_first_name,p.last_name as primary_contact_last_name,';
$queryString .= "p.email,p.phone";
$queryString .= ' FROM #__deals AS d';
$query->select($queryString)->leftJoin('#__companies AS c ON c.id = d.company_id AND c.published>0')->leftJoin('#__deal_status AS stat ON stat.id = d.status_id')->leftJoin('#__sources AS source ON source.id = d.source_id')->leftJoin('#__stages AS stage on stage.id = d.stage_id')->leftJoin("#__people AS p ON p.id = d.primary_contact_id AND p.published>0")->leftJoin("#__shared AS shared ON shared.item_id=d.id AND shared.item_type='deal'");
} else {
$queryString = 'd.*,SUM(d.amount) AS filtered_total,';
$queryString .= '( d.amount * ( d.probability / 100 )) AS forecast,';
$queryString .= 'c.name as company_name,';
$queryString .= '(CASE WHEN (stat.name IS NOT NULL) THEN stat.name ELSE NULL END) as status_name,';
$queryString .= 'source.name as source_name,';
$queryString .= 'stage.name as stage_name,stage.percent,';
$queryString .= 'event_cf.*,';
$queryString .= 'event.id as event_id,event.name as event_name,event.due_date as event_due_date,event.type as event_type,';
$queryString .= 'user.first_name as owner_first_name, user.last_name as owner_last_name,';
$queryString .= 'p.first_name as primary_contact_first_name,p.last_name as primary_contact_last_name,';
$queryString .= "p.email,p.phone";
$queryString .= ' FROM #__deals AS d';
$query->select($queryString)->leftJoin('#__companies AS c ON c.id = d.company_id AND c.published>0')->leftJoin('#__deal_status AS stat ON stat.id = d.status_id')->leftJoin('#__sources AS source ON source.id = d.source_id')->leftJoin('#__stages AS stage on stage.id = d.stage_id')->leftJoin("#__events_cf AS event_cf ON event_cf.association_id = d.id AND event_cf.association_type ='deal' ")->leftJoin("#__events AS event ON event.id = event_cf.event_id AND event.due_date IS NULL or event.due_date=(SELECT MIN(e2.due_date) FROM #__events_cf e2cf " . "LEFT JOIN #__events as e2 on e2.id = e2cf.event_id " . "WHERE e2cf.association_id=d.id AND e2cf.association_type='deal') AND event.published>0")->leftJoin('#__users AS user ON user.id = d.owner_id')->leftJoin("#__people AS p ON p.id = d.primary_contact_id AND p.published>0")->leftJoin("#__shared AS shared ON shared.item_id=d.id AND shared.item_type='deal'");
}
if (!$id) {
//name
$deal_filter = $this->getState('Deal.' . $layout . '_name');
if ($deal_filter != null) {
$query->where("d.name LIKE '%" . $deal_filter . "%'");
}
//owner
$owner_filter = $this->getState('Deal.' . $layout . '_owner_id', $this->_user_id);
if ($owner_filter != 'all' && $owner_filter) {
$owner_type = $this->getState('Deal.' . $layout . '_owner_type');
if ($owner_type == 'member' or $owner_type == null) {
$query->where("d.owner_id=" . $owner_filter);
}
if ($owner_type == 'team') {
//get team members
$team_members = UsersHelper::getTeamUsers($owner_filter);
//filter by results having team ids
$ids = "0,";
for ($i = 0; $i < count($team_members); $i++) {
$member = $team_members[$i];
$ids .= $member['id'] . ",";
}
$ids = substr($ids, 0, -1);
$query->where("d.owner_id IN(" . $ids . ")");
}
}
//amount
$amount_filter = $this->getState('Deal.' . $layout . '_amount', '');
if ($amount_filter != null and $amount_filter != 'all') {
if ($amount_filter == 'small') {
$query->where("d.amount <= " . TextHelper::_('COBALT_SMALL_DEAL_AMOUNT'));
}
if ($amount_filter == 'medium') {
$query->where("d.amount > " . TextHelper::_('COBALT_SMALL_DEAL_AMOUNT') . " AND d.amount <= " . TextHelper::_('COBALT_MEDIUM_DEAL_AMOUNT'));
}
if ($amount_filter == 'large') {
$query->where("d.amount > " . TextHelper::_('COBALT_LARGE_DEAL_AMOUNT'));
}
}
//source
$source_filter = $this->getState('Deal.' . $layout . '_source_id');
if ($source_filter != null and $source_filter != 'all') {
$source = $source_filter;
}
//stage
$stage_filter = $this->getState('Deal.' . $layout . '_stage_id');
if ($stage_filter != null and $stage_filter != 'all') {
$stage = $stage_filter;
}
//status
$status_filter = $this->getState('Deal.' . $layout . '_status_id');
if ($status_filter != null and $status_filter != 'all') {
$status = $status_filter;
}
//expected close
$expected_close_filter = $this->getState('Deal.' . $layout . '_expected_close');
if ($expected_close_filter != null and $expected_close_filter != 'all') {
$close = $expected_close_filter;
}
//modified
$modified_filter = $this->getState('Deal.' . $layout . '_modified');
if ($modified_filter != null and $modified_filter != 'all') {
$modified = $modified_filter;
}
//created
$created_filter = $this->getState('Deal.' . $layout . '_created');
if ($created_filter != null and $created_filter != 'all') {
$created = $created_filter;
}
//get current date to use for all date filtering
$date = DateHelper::formatDBDate(date('Y-m-d 00:00:00'));
/** ------------------------------------------
* Here we filter for diferent types of deals
*/
$item_filter = $this->getState('Deal.' . $layout . '_item', '');
if ($item_filter) {
//filter for deals//tasks due today
if ($item_filter == 'due.today') {
$tomorrow = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() + 1 * 24 * 60 * 60));
$query->where("event.due_date > '{$date}' AND event.due_date < '{$tomorrow}'");
$query->where("event.published>0");
}
//filter for deals//tasks due tomorrow
if ($item_filter == "due.tomorrow") {
$tomorrow = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() + 1 * 24 * 60 * 60));
$query->where("event.due_date='" . $tomorrow . "'");
$query->where("event.published>0");
}
//filter for past deals
if ($item_filter == "due.past") {
$query->where("event.due_date < '{$date}'");
$query->where("event.published>0");
}
//filter for deals not updated in the last 30 days
if ($item_filter == "updated.not_thirty") {
$last_thirty_days = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() - 30 * 24 * 60 * 60));
$query->where("d.modified < '{$last_thirty_days}'");
}
//filter for deals updated in the last 30 days
if ($item_filter == "updated.thirty") {
$last_thirty_days = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() - 30 * 24 * 60 * 60));
$query->where("d.modified >'{$last_thirty_days}'");
}
//filter for most valuable deals
if ($item_filter == "amount.valuable") {
$query->where('d.amount > 0');
}
//filter for shared deals
if ($item_filter == "shared") {
$query->where("shared.item_id IS NOT NULL");
}
}
//filter for archived deals
if ($item_filter == "archived" && is_null($this->archived)) {
$query->where("d.archived=1");
} else {
$query->where("d.archived=0");
}
/** --------------------------------------------
* Search for closing deal filters
*/
if ($close != null && $close != "any") {
if ($close == "this_week") {
$this_week = DateHelper::formatDBDate(date('Y-m-d 00:00:00'));
$next_week = date('Y-m-d 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "+7 days"));
$query->where("d.expected_close >= '{$this_week}'");
$query->where("d.expected_close < '{$next_week}'");
}
if ($close == "next_week") {
$next_week = date('Y-m-d 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "+7 days"));
$week_after_next = date('Y-m-d 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "+14 days"));
$query->where("d.expected_close >= '{$next_week}'");
$query->where("d.expected_close < '{$week_after_next}'");
}
if ($close == "this_month") {
$this_month = DateHelper::formatDBDate(date('Y-m-0 00:00:00'));
$next_month = date('Y-m-0 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "+1 month"));
$query->where("d.expected_close >= '{$this_month}'");
$query->where("d.expected_close < '{$next_month}'");
}
if ($close == "next_month") {
$next_month = date("Y-m-0 00:00:00", strtotime(date("Y-m-d 00:00:00", strtotime($date)) . "+1 month"));
$next_next_month = date("Y-m-0 00:00:00", strtotime(date("Y-m-d 00:00:00", strtotime($date)) . "+2 months"));
$query->where("d.expected_close >= '{$next_month}'");
$query->where("d.expected_close < '{$next_next_month}'");
}
}
/** --------------------------------------------
* Search for modified deal filters
*/
if ($modified != null && $modified != "any") {
if ($modified == "this_week") {
$this_week = DateHelper::formatDBDate(date('Y-m-d 00:00:00'));
$last_week = date('Y-m-d 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "-7 days"));
$query->where("d.modified >= '{$last_week}'");
$query->where("d.modified < '{$this_week}'");
}
if ($modified == "last_week") {
$last_week = DateHelper::formatDBDate(date("Y-m-d", strtotime("-7 days")));
$week_before_last = DateHelper::formatDBDate(date("Y-m-d", strtotime("-14 days")));
$query->where("d.modified >= '{$week_before_last}'");
$query->where("d.modified < '{$last_week}'");
}
if ($modified == "this_month") {
$this_month = DateHelper::formatDBDate(date('Y-m-1 00:00:00'));
$next_month = date('Y-m-1 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "+1 month"));
$query->where("d.modified >= '{$this_month}'");
$query->where("d.modified < '{$next_month}'");
}
if ($modified == "last_month") {
$this_month = DateHelper::formatDBDate(date('Y-m-1 00:00:00'));
$last_month = date('Y-m-1 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "-1 month"));
$query->where("d.modified >= '{$last_month}'");
$query->where("d.modified < '{$this_month}'");
}
}
/** --------------------------------------------
* Search for created deal filters
*/
if ($created != null && $created != "any") {
if ($created == "this_week") {
$this_week = DateHelper::formatDBDate(date('Y-m-d 00:00:00'));
$last_week = date('Y-m-d 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "-7 days"));
$query->where("d.created >= '{$last_week}'");
$query->where("d.created < '{$this_week}'");
}
if ($created == "last_week") {
$last_week = DateHelper::formatDBDate(date("Y-m-d", strtotime("-7 days")));
$week_before_last = DateHelper::formatDBDate(date("Y-m-d", strtotime("-14 days")));
$query->where("d.created >= '{$week_before_last}'");
$query->where("d.created < '{$last_week}'");
}
if ($created == "this_month") {
$this_month = DateHelper::formatDBDate(date('Y-m-1 00:00:00'));
$next_month = date('Y-m-1 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "+1 month"));
$query->where("d.created >= '{$this_month}'");
$query->where("d.created < '{$next_month}'");
}
if ($created == "last_month") {
$this_month = DateHelper::formatDBDate(date('Y-m-1 00:00:00'));
$last_month = date('Y-m-1 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "-1 month"));
$query->where("d.created >= '{$last_month}'");
$query->where("d.created < '{$this_month}'");
}
if ($created == "today") {
$today = DateHelper::formatDBDate(date("Y-m-d 00:00:00"));
$tomorrow = date('Y-m-d 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "+1 day"));
$query->where("d.created >= '{$today}'");
$query->where("d.created < '{$tomorrow}'");
}
if ($created == "yesterday") {
$today = DateHelper::formatDBDate(date("Y-m-d 00:00:00"));
$yesterday = date('Y-m-d 00:00:00', strtotime(date("Y-m-d", strtotime($date)) . "-1 day"));
$query->where("d.created >= '{$yesterday}'");
$query->where("d.created < '{$today}'");
}
}
/** ------------------------------------------
* Search for status
*/
if ($status != null and $status != 'all') {
$query->where("d.status_id=" . $status);
}
/** -------------------------
* Search for sources
*/
if ($source != null and $source != 'all') {
$query->where('d.source_id=' . $source);
}
/** ----------------------------------------------------------------
* Filter for stage id associations
*/
if ($stage != null && $stage != 'all' and !$id) {
//if we want active deals we must retrieve the active stage ids to filter by
if ($stage == 'active') {
//get stage ids
$stage_ids = DealHelper::getActiveStages();
//filter by results having team ids
$stages = "";
for ($i = 0; $i < count($stage_ids); $i++) {
$stage = $stage_ids[$i];
$stages .= $stage['id'] . ",";
}
$stages = substr($stages, 0, -1);
$query->where("d.stage_id IN(" . $stages . ")");
} else {
//else filter by the stage id
$query->where("d.stage_id='" . $stage . "'");
}
}
/** --------------------------------------------------------
* Filter data for the sources page
*/
//source view
if ($view == "reports" && $layout == "source_report") {
//filter by active and closed stages
$active_and_closed_stages = DealHelper::getNonInactiveStages();
$query->where("d.stage_id IN(" . implode(',', $active_and_closed_stages) . ")");
//filter by deals that are associated to sources
$query->where("d.source_id <> 0");
}
$deal_filter = $this->getState('Deal.' . $view . '_name');
if ($deal_filter != null) {
$query->where("d.name LIKE '%" . $deal_filter . "%'");
}
/** --------------------
* Grab only recently accessed deals
*/
if ($this->recent) {
$past = DateHelper::formatDBDate(date('Y-m-d H:i:s') . " - 30 days");
$query->where('d.last_viewed > ' . $this->db->Quote($past));
}
/**---------------------
* Group by deal id
*/
$query->group('d.id');
/**
* Set our sorting direction if set via post
*/
//default deals view
//
if ($this->ordering) {
$query->order($this->ordering);
}
//reports view
if ($view == "reports") {
$order = $this->getState('Deal.' . $layout . '_filter_order');
$dir = $this->getState('Deal.' . $layout . '_filter_order_Dir');
$query->order($order . ' ' . $dir);
} else {
$orderString = $this->getState('Deal.filter_order', 'd.name') . " " . $this->getState('Deal.filter_order_Dir', 'asc');
$query->order($orderString);
}
}
/** ---------------------
* Filter by id
*/
if ($id) {
if (is_array($id)) {
$query->where("d.id IN (" . implode(',', $id) . ")");
} else {
$query->where("d.id = {$id}");
}
}
/** or team **/
if ($team) {
$query->where("user.team_id = {$team}");
}
/** ---------------------------------------------------------------
* Filter data using member role permissions
*/
$member_id = $this->_user_id;
$member_role = UsersHelper::getRole();
$team_id = UsersHelper::getTeamId();
if (isset($user) && $user == "all" || isset($owner_filter) && $owner_filter == "all") {
if ($member_role != 'exec') {
//manager filter
if ($member_role == 'manager') {
$query->where('( user.team_id = ' . $team_id . " OR shared.user_id=" . $member_id . " )");
} else {
//basic user filter
$query->where(array('(d.owner_id = ' . $member_id . " OR shared.user_id=" . $member_id . " )"));
}
}
} elseif ($team) {
$query->where("user.team_id={$team}");
} elseif ($user && $user != "all") {
$query->where("(d.owner_id=" . $this->_user_id . " OR shared.user_id=" . $this->_user_id . ")");
} else {
if (!isset($owner_filter)) {
$query->where("( d.owner_id=" . $this->_user_id . " OR shared.user_id=" . $this->_user_id . " )");
}
}
/** company **/
if ($this->company_id) {
$query->where("d.company_id=" . $this->company_id);
}
/** people **/
if ($this->person_id) {
$query->leftJoin("#__people_cf AS dpcf ON dpcf.association_id = d.id AND dpcf.association_type='deal'");
$query->where("dpcf.person_id=" . $this->person_id);
}
/** archived **/
if (!is_null($this->archived)) {
$query->where("d.archived=" . $this->archived);
}
/** published **/
$query->where("d.published=" . $this->published);
//set db query and load object list
return $query;
}
public function _display_sales_pipeline()
{
//get deals for reports
$dealModel = new DealModel();
$dealModel->set('archived', 0);
$dealModel->set('limit', 0);
$reports = $dealModel->getReportDeals();
// Initialise state variables.
$state = $dealModel->getState();
//info for dropdowns
$deal_amounts = DealHelper::getAmounts();
$deal_stages = DealHelper::getActiveStages(TRUE);
$deal_statuses = DealHelper::getStatuses();
$deal_close_dates = DealHelper::getClosing();
$modified_dates = DealHelper::getModified();
//list view
$sales_pipeline_header = ViewHelper::getView('reports', 'sales_pipeline_header', 'phtml', array('state' => $state, 'reports' => $reports));
$sales_pipeline_list = ViewHelper::getView('reports', 'sales_pipeline_filter', 'phtml', array('reports' => $reports));
$sales_pipeline_footer = ViewHelper::getView('reports', 'sales_pipeline_footer', 'phtml');
$sales_pipeline_header->deal_amounts = $deal_amounts;
$sales_pipeline_header->deal_stages = $deal_stages;
$sales_pipeline_header->deal_statuses = $deal_statuses;
$sales_pipeline_header->deal_close_dates = $deal_close_dates;
$sales_pipeline_header->modified_dates = $modified_dates;
$sales_pipeline_header->created_dates = DateHelper::getCreatedDates();
$sales_pipeline_header->team_names = DropdownHelper::getTeamNames();
$sales_pipeline_header->user_names = DropdownHelper::getUserNames();
$sales_pipeline_header->state = $state;
//assign refs to view
$this->sales_pipeline_header = $sales_pipeline_header;
$this->sales_pipeline_list = $sales_pipeline_list;
$this->sales_pipeline_footer = $sales_pipeline_footer;
$this->state = $state;
$this->reports = $reports;
}
