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; }