Example #1
0
 /**
  * Get Yearly Commission
  * @param $access_type we wish to filter by 'member','team','company'
  * @param $access_id the id of the $access_type we wish to filter by
  * @return mixed $results
  */
 public function getYearlyCommission($access_type = null, $access_id = null)
 {
     //get member data
     if ($access_type == 'member') {
         return $this->getYearlyCommissionData($access_id);
     } else {
         //array to assign our data
         $members = array();
         $results = array();
         //get team data
         if ($access_type == 'team') {
             //get team members
             $team_members = UsersHelper::getTeamUsers($access_id);
             foreach ($team_members as $key => $member) {
                 $members[] = $this->getYearlyCommissionData($member['id']);
             }
         }
         //get company data
         if ($access_type == 'company') {
             //get company users
             $company_members = UsersHelper::getCompanyUsers();
             foreach ($company_members as $key => $member) {
                 $members[] = $this->getYearlyCommissionData($member['id']);
             }
         }
         //combine data
         foreach ($members as $key => $member) {
             foreach ($member as $date_key => $data) {
                 if (array_key_exists($date_key, $results)) {
                     $results[$date_key]['y'] += $data['y'];
                 } else {
                     $results[$date_key]['y'] = $data['y'];
                 }
             }
         }
         return $results;
     }
 }
Example #2
0
 public function _buildQuery()
 {
     $app = \Cobalt\Container::fetch('app');
     $loc = $app->input->getCmd('loc', $this->loc);
     $association = null;
     $user = null;
     if ($this->db->name == 'mysqli') {
         $this->db->setQuery("SET SQL_BIG_SELECTS=1")->execute();
     }
     $query = $this->db->getQuery(true);
     $db = $this->db;
     $query->select("e.*," . "a.*," . "ci.name AS category_name," . "c.name as company_name, c.id as company_id," . "d.name as deal_name,d.id as deal_id," . "p.first_name as person_first_name, p.last_name as person_last_name,p.id as person_id," . "assignee.color AS assignee_color," . 'assignee.first_name AS assignee_first_name,assignee.last_name AS assignee_last_name,' . "owner.first_name as owner_first_name, owner.last_name as owner_last_name " . "FROM #__events AS e");
     $query->leftJoin("#__events_categories AS ci ON ci.id = e.category_id");
     $query->leftJoin("#__events_cf AS a ON e.id = a.event_id");
     $query->leftJoin("#__companies AS c ON a.association_type = 'company' AND a.association_id = c.id AND c.published>0");
     $query->leftJoin("#__deals AS d ON a.association_type = 'deal' AND a.association_id = d.id AND d.published>0");
     $query->leftJoin("#__people AS p ON a.association_type = 'person' AND a.association_id = p.id AND p.published>0");
     $query->leftJoin('#__users AS assignee ON assignee.id = e.assignee_id');
     $query->leftJoin('#__users AS owner ON owner.id = e.owner_id');
     //gather info
     $user_role = UsersHelper::getRole();
     $user_id = UsersHelper::getUserId();
     $team_id = UsersHelper::getTeamId();
     //filter based on user role
     if ($user_role != 'exec' && $this->view != "print") {
         //manager filter
         if ($user_role == 'manager') {
             $query->where('(assignee.team_id = ' . $team_id . ' OR owner.team_id = ' . $team_id . ")");
         } else {
             //basic user filter
             $query->where("(e.assignee_id = " . $user_id . " OR e.owner_id =" . $user_id . ")");
         }
     }
     //search for certain user events
     if ($user && $this->view != "print") {
         if ($user == $user_id) {
             $query->where("(e.assignee_id=" . $user_id . ' OR e.owner_id=' . $user_id . ')');
         } elseif ($user != 'all') {
             $query->where(array("e.assignee_id=" . $user));
         }
     }
     if (!$association) {
         $association = $app->input->get('association_id') ? $app->input->get('association_id') : $app->input->get('id');
     }
     $association_type = $app->input->get('association_type') ? $app->input->get('association_type') : $app->input->get('layout');
     $association_types = array("company", "deal", "person");
     if ($association) {
         $association_type = $association_type ? $association_type : $loc;
         if ($association_type == "company") {
             if (is_array($association)) {
                 $query->where("(p.company_id=" . $association . " OR d.company_id=" . $association . " OR ( a.association_type=" . $db->quote("company") . " AND a.association_id IN(" . implode(",", $association) . ") ))");
             } else {
                 $query->where("(p.company_id=" . $association . " OR d.company_id=" . $association . " OR ( a.association_type=" . $db->quote("company") . " AND a.association_id=" . $association . " ))");
             }
         } else {
             if (is_array($association)) {
                 $query->where("a.association_id IN(" . implode(",", $association) . ")");
             } else {
                 $query->where("a.association_id=" . $association);
             }
             $query->where("a.association_type=" . $db->quote($association_type));
         }
     } elseif ($association_type && in_array($association_type, $association_types)) {
         $query->where("a.association_type=" . $db->Quote($association_type));
     } else {
         /** hide events associated with archived deals **/
         $query->where("(d.archived=0 OR d.archived IS NULL)");
     }
     if ($this->_id != null) {
         if (is_array($this->_id)) {
             $query->where("e.id IN (" . implode(',', $this->_id) . ")");
         } else {
             $query->where("e.id={$this->_id}");
         }
     }
     if ($this->current_events) {
         $now = DateHelper::formatDBDate(date('Y-m-d'));
         $query->where('e.due_date != "0000-00-00 00:00:00" AND e.due_date >="' . $now . '"');
     }
     /** Filter by status **/
     $status_filter = $this->getState('Event.' . $this->view . '_' . $this->layout . '_status');
     if ($status_filter != null && $this->view != "print") {
         $query->where("e.completed={$status_filter}");
     } else {
         if ($this->completed != null) {
             if ($this->completed == 'true') {
                 $query->where("e.completed=1");
             } elseif ($this->completed != 'false') {
                 $query->where("e.completed=" . $this->completed);
             }
         } else {
             $query->where("e.completed=0");
         }
     }
     /** Filter by type **/
     $type_filter = $this->getState('Event.' . $this->view . '_' . $this->layout . '_type');
     if ($type_filter != null && $type_filter != "all" && $this->view != "print") {
         $query->where("e.type='{$type_filter}'");
     }
     /** Filter by category **/
     $category_filter = $this->getState('Event.' . $this->view . '_' . $this->layout . '_category');
     if ($category_filter != null && $category_filter != "any" && $this->view != "print") {
         $query->where("e.category_id={$category_filter}");
     }
     /** Filter by due date **/
     $due_date_filter = $this->getState('Event.' . $this->view . '_' . $this->layout . '_due_date');
     if ($due_date_filter != null && $due_date_filter != "any" && $this->view != "print") {
         $date = DateHelper::formatDBDate(date('Y-m-d 00:00:00'));
         switch ($due_date_filter) {
             case "today":
                 $tomorrow = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() + 1 * 24 * 60 * 60));
                 $query->where("((e.due_date >= '{$date}' AND e.due_date < '{$tomorrow}') OR (e.start_time >= '{$date}' AND e.start_time < '{$tomorrow}'))");
                 break;
             case "tomorrow":
                 $tomorrow = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() + 1 * 24 * 60 * 60));
                 $day_after_tomorrow = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() + 2 * 24 * 60 * 60));
                 $query->where("((e.due_date >= '{$tomorrow}' AND e.due_date < '{$day_after_tomorrow}') OR (e.start_time >= '{$tomorrow}' AND e.start_time < '{$day_after_tomorrow}'))");
                 break;
             case "this_week":
                 $date_info = getDate(strtotime($date));
                 $today = $date_info['wday'];
                 $days_to_remove = -1 + $today;
                 $days_to_add = 5 - $today;
                 $beginning_of_week = DateHelper::formatDBDate(date('Y-m-d 00:00:00', strtotime($date . " - {$days_to_remove} days")));
                 $end_of_week = DateHelper::formatDBDate(date('Y-m-d 00:00:00', strtotime($date . " + {$days_to_add} days")));
                 $query->where("((e.due_date >= '{$beginning_of_week}' AND e.due_date < '{$end_of_week}') OR (e.start_time >= '{$beginning_of_week}' AND e.start_time < '{$end_of_week}'))");
                 break;
             case "past_due":
                 $query->where("((e.due_date < '{$date}' AND e.due_date != '0000-00-00 00:00:00') OR (e.start_time < '{$date}' AND e.start_time != '0000-00-00 00:00:00'))");
                 break;
             case "not_past_due":
                 $query->where("((e.due_date >= '{$date}' AND e.due_date != '0000-00-00 00:00:00') OR (e.start_time >= '{$date}' AND e.start_time != '0000-00-00 00:00:00'))");
                 break;
         }
     }
     /** Filter by assignee id **/
     $assignee_id_filter = $this->getState('Event.' . $this->view . '_' . $this->layout . '_assignee_id');
     $assignee_filter_type = $this->getState('Event.' . $this->view . '_' . $this->layout . '_assignee_filter_type');
     if ($loc != "calendar" && $assignee_id_filter != null && $assignee_id_filter != 'all' && $this->view != "print") {
         if ($assignee_filter_type == "team") {
             $team_members = UsersHelper::getTeamUsers($assignee_id_filter, TRUE);
             $query->where("e.assignee_id IN(" . implode(',', $team_members) . ")");
         } else {
             $query->where("e.assignee_id={$assignee_id_filter}");
         }
     }
     /** Filter by association type **/
     $association_type_filter = $this->getState('Event.' . $this->view . '_' . $this->layout . '_association_type');
     if ($association_type_filter != null && $association_type_filter != "any" && !$association && $assignee_id_filter != 'all' && $this->view != "print") {
         $query->where("a.association_type='" . $association_type_filter . "'");
     }
     $query->where("e.published=" . $this->published);
     if ($this->start_date) {
         $query->where("(e.due_date >= '" . $this->start_date . "' OR e.start_time >= '" . $this->start_date . "' OR e.repeats != 'none' )");
     }
     if ($this->end_date) {
         $query->where("(e.due_date < '" . $this->end_date . "' OR e.end_time < '" . $this->end_date . "' OR e.repeats != 'none' )");
     }
     if ($this->deal_id > 0) {
         $query->where("(a.association_id=" . $this->deal_id . " AND a.association_type='deal')");
     }
     $this->filter_order = $this->getState('Event.' . $this->view . '_' . $this->layout . '_filter_order');
     $this->filter_order_Dir = $this->getState('Event.' . $this->view . '_' . $this->layout . '_filter_order_Dir');
     $query->order($this->filter_order . ' ' . $this->filter_order_Dir);
     /** ------------------------------------------
      * Set query limits and load results
      */
     if ($this->getState("Event." . $this->view . '_' . $this->layout . '_limit') != 0) {
         $query .= " LIMIT " . $this->getState("Event." . $this->view . '_' . $this->layout . '_limit') . " OFFSET " . $this->getState("Event." . $this->view . '_' . $this->layout . '_limitstart');
     }
     return $query;
 }
Example #3
0
 /**
  * Get lead sources from database where we have associated won deals and grab amount from deals
  * @param $access_type to filter by 'member','team','company'
  * @param $access_id id of $access_type to filter by
  * @return mixed $results
  */
 public function getLeadSources($access_type = null, $access_id = null)
 {
     //get won stage id so we know what stage to filter by for the deals
     $won_stage_ids = DealHelper::getWonStages();
     $query = $this->db->getQuery(true);
     //gen query
     $query->select("s.name,SUM(d.amount) as y");
     $query->from("#__people AS p");
     //left join people that have won deals
     $query->leftJoin("#__sources AS s ON s.id = p.source_id");
     $query->leftJoin("#__people_cf AS dpcf ON dpcf.person_id = p.id AND dpcf.association_type='deal'");
     $query->leftJoin("#__deals AS d ON d.id = dpcf.association_id AND d.published>0");
     //filter where we have associated source ids and the person is a lead contact
     $query->where("p.source_id <> 0");
     $query->where("p.type='lead'");
     //filter by won deals
     $won_stage_ids[] = 0;
     $query->where("d.stage_id IN(" . implode(',', $won_stage_ids) . ")");
     $query->where("p.published=" . $this->published);
     //filter by access type
     if ($access_type != 'company') {
         //team sorting
         if ($access_type == 'team') {
             //get team members
             $team_members = UsersHelper::getTeamUsers($access_id);
             $query .= " AND d.owner_id IN (";
             //loop to make string
             $query .= "0,";
             foreach ($team_members as $key => $member) {
                 $query .= "'" . $member['id'] . "',";
             }
             $query = substr($query, 0, -1);
             $query .= ") ";
         }
         //member sorting
         if ($access_type == 'member') {
             $query .= " AND d.owner_id={$access_id} ";
         }
     }
     //group by source ids
     $query .= " GROUP BY s.id";
     $results = $this->db->setQuery($query)->loadAssocList();
     if (count($results) > 0) {
         foreach ($results as $key => $source) {
             $results[$key]['y'] = (int) $source['y'];
             $results[$key]['data'] = array((int) $source['y']);
         }
     }
     return $results;
 }
Example #4
0
 /**
  * Get data for custom reports
  * @param  int   $id custom id data to retrieve
  * @return mixed $results
  */
 public function getCustomReportData($id = null)
 {
     //get db
     $db = JFactory::getDBO();
     $query = $db->getQuery(true);
     $app = \Cobalt\Container::fetch('app');
     //get the custom report so we know what data to filter and select
     $custom_report = $this->getCustomReports($id);
     $custom_report = $custom_report[0];
     $custom_report_fields = unserialize($custom_report['fields']);
     //gen query
     //construct query string
     $queryString = 'd.*,SUM(d.amount) AS filtered_total,';
     $queryString .= 'c.name as company_name,';
     $queryString .= 'stat.name as status_name,';
     $queryString .= 'source.name as source_name,';
     $queryString .= 'stage.name as stage_name,stage.percent,';
     $queryString .= 'user.first_name, user.last_name,';
     $queryString .= 'p.first_name as primary_contact_first_name,p.last_name as primary_contact_last_name,';
     $queryString .= "p.email as primary_contact_email,p.phone as primary_contact_phone,";
     $queryString .= "pc.name as primary_contact_company_name";
     //select
     $query->select($queryString);
     $query->from("#__deals AS d");
     //left join
     $query->leftJoin('#__companies AS c ON c.id = d.company_id AND c.published>0');
     $query->leftJoin('#__deal_status AS stat ON stat.id = d.status_id');
     $query->leftJoin('#__sources AS source ON source.id = d.source_id');
     $query->leftJoin('#__stages AS stage on stage.id = d.stage_id');
     $query->leftJoin('#__users AS user ON user.id = d.owner_id');
     $query->leftJoin("#__people AS p ON p.id = d.primary_contact_id AND p.published>0");
     $query->leftJoin("#__companies AS pc ON pc.id = p.company_id AND pc.published>0");
     //group results
     $query->group("d.id");
     //filter data with user state requests
     $layout = str_replace("_filter", "", $app->input->get('layout'));
     $view = $app->input->get('view');
     if ($view == "print") {
         $layout = "custom_report";
         $id = $app->input->get('custom_report');
     }
     $filter_order = $this->getState('Report.' . $id . '_' . $layout . '_filter_order');
     $filter_order_Dir = $this->getState('Report.' . $id . '_' . $layout . '_filter_order_Dir');
     $filter_order = strstr($filter_order, "custom_") ? str_replace("d.", "", $filter_order) : $filter_order;
     $query->order($filter_order . ' ' . $filter_order_Dir);
     //assign defaults
     $close = null;
     $modified = null;
     $created = null;
     $status = null;
     $source = null;
     $stage = null;
     //filter by deal names
     $deal_filter = $this->getState('Report.' . $id . '_' . $layout . '_name');
     if ($deal_filter != null) {
         $query->where("d.name LIKE '%" . $deal_filter . "%'");
     }
     //owner
     $owner_filter = $this->getState('Report.' . $id . '_' . $layout . '_owner_id');
     if ($owner_filter != null and $owner_filter != 'all') {
         $owner_type = $this->getState('Report.' . $id . '_' . $layout . '_owner_type');
         if ($owner_type == 'member') {
             $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 = "";
             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('Report.' . $id . '_' . $layout . '_amount');
     if ($amount_filter != null and $amount_filter != 'all') {
         if ($amount_filter == 'small') {
             $query->where("d.amount <= 50");
         }
         if ($amount_filter == 'medium') {
             $query->where("d.amount > 50 AND d.amount <= 400");
         }
         if ($amount_filter == 'large') {
             $query->where("d.amount > 400");
         }
     }
     //source
     $source_filter = $this->getState('Report.' . $id . '_' . $layout . '_source_id');
     if ($source_filter != null and $source_filter != 'all') {
         $source = $source_filter;
     }
     //stage
     $stage_filter = $this->getState('Report.' . $id . '_' . $layout . '_stage_id');
     if ($stage_filter != null and $stage_filter != 'all') {
         $stage = $stage_filter;
     }
     //status
     $status_filter = $this->getState('Report.' . $id . '_' . $layout . '_status_id');
     if ($status_filter != null and $status_filter != 'all') {
         $status = $status_filter;
     }
     //expected close
     $expected_close_filter = $this->getState('Report.' . $id . '_' . $layout . '_expected_close');
     if ($expected_close_filter != null and $expected_close_filter != 'all') {
         $close = $expected_close_filter;
     }
     //modified
     $modified_filter = $this->getState('Report.' . $id . '_' . $layout . '_modified');
     if ($modified_filter != null and $modified_filter != 'all') {
         $modified = $modified_filter;
     }
     //created
     $created_filter = $this->getState('Report.' . $id . '_' . $layout . '_created');
     if ($created_filter != null and $created_filter != 'all') {
         $created = $created_filter;
     }
     //filter by primary contact name
     $primary_contact_name = $this->getState('Report.' . $id . '_' . $layout . '_primary_contact_name');
     if ($primary_contact_name != null) {
         $query->where("(p.first_name LIKE '%" . $primary_contact_name . "%' OR p.last_name LIKE '%" . $primary_contact_name . "%')");
     }
     //filter by primary contact email
     $primary_contact_email = $this->getState('Report.' . $id . '_' . $layout . '_primary_contact_email');
     if ($primary_contact_email != null) {
         $query->where("p.email LIKE '%" . $primary_contact_email . "%'");
     }
     //filter by primary contact phone
     $primary_contact_phone = $this->getState('Report.' . $id . '_' . $layout . '_primary_contact_phone');
     if ($primary_contact_phone != null) {
         $query->where("p.phone LIKE '%" . $primary_contact_phone . "%'");
     }
     //get current date to use for all date filtering
     $date = DateHelper::formatDBDate(date('Y-m-d 00:00:00'));
     /** --------------------------------------------
      * 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(DateHelper::formatDBDate(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') {
         //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 . "'");
         }
     }
     /** ---------------------------------------------------------------------------------------------------------------
      * Field for custom field user states
      */
     //Get custom filters
     $custom_fields = DealHelper::getUserCustomFields();
     //If the user has defined any custom fields we will left join the associated data here
     if (count($custom_fields) > 0) {
         foreach ($custom_fields as $row) {
             //Join different data based on type
             switch ($row['type']) {
                 //If the type is forecast we want to calculate the amount
                 case "forecast":
                     $query->select("( d.amount * ( d.probability / 100 )) as custom_" . $row['id']);
                     break;
                     //Else join the associated value from the database
                 //Else join the associated value from the database
                 default:
                     $query->select("custom_" . $row['id'] . ".value as custom_" . $row['id']);
                     $query->leftJoin("#__deal_custom_cf as custom_" . $row['id'] . " on " . "custom_" . $row['id'] . ".deal_id = d.id AND " . "custom_" . $row['id'] . ".custom_field_id = " . $row['id']);
                     break;
             }
             //If the user has any associated user state requests set in the model we set the filters here
             $custom_field_filter = $this->getState('Report.' . $id . '_' . $layout . '_' . $row['id']);
             if ($custom_field_filter != null and $custom_field_filter != 'all') {
                 switch ($row['type']) {
                     case "forecast":
                         $query->where("( d.amount * ( d.probability / 100 )) LIKE '%" . $custom_field_filter . "%'");
                         break;
                     case "date":
                         if ($custom_field_filter == "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("custom_" . $row['id'] . ".value >= '{$this_week}'");
                             $query->where("custom_" . $row['id'] . ".value < '{$next_week}'");
                         }
                         if ($custom_field_filter == "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("custom_" . $row['id'] . ".value >= '{$next_week}'");
                             $query->where("custom_" . $row['id'] . ".value < '{$week_after_next}'");
                         }
                         if ($custom_field_filter == "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("custom_" . $row['id'] . ".value >= '{$this_month}'");
                             $query->where("custom_" . $row['id'] . ".value < '{$next_month}'");
                         }
                         if ($custom_field_filter == "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("custom_" . $row['id'] . ".value >= '{$next_month}'");
                             $query->where("custom_" . $row['id'] . ".value < '{$next_next_month}'");
                         }
                         break;
                     default:
                         $query->where("custom_" . $row['id'] . ".value LIKE '%" . $custom_field_filter . "%'");
                         break;
                 }
             }
         }
     }
     //filter based on member access roles
     $user_id = UsersHelper::getUserId();
     $member_role = UsersHelper::getRole();
     $team_id = UsersHelper::getTeamId();
     if ($member_role != 'exec') {
         if ($member_role == 'manager') {
             $query->where("user.team_id={$team_id}");
         } else {
             $query->where("(d.owner_id={$user_id})");
         }
     }
     $query->where("d.published=" . $this->published);
     $query->where("d.archived=0");
     //return results
     $db->setQuery($query);
     $results = $db->loadAssocList();
     return $results;
 }
Example #5
0
 public function getNotes($object_id = NULL, $type = NULL, $display = true)
 {
     $app = \Cobalt\Container::fetch('app');
     //grab db
     $db = JFactory::getDBO();
     //initialize query
     $query = $db->getQuery(true);
     //gen query string
     $query->select("n.*,cat.name as category_name,comp.name as company_name,\n                        comp.id as company_id,deal.name as deal_name,deal.id as deal_id,\n                        person.id as person_id,person.first_name as person_first_name,\n                        person.last_name as person_last_name, owner.first_name as owner_first_name,\n                        event.name as event_name, event.id as event_id,\n                        owner.last_name as owner_last_name, author.email");
     $query->from("#__notes as n");
     $query->leftJoin("#__notes_categories AS cat ON cat.id = n.category_id");
     $query->leftJoin("#__companies AS comp ON comp.id = n.company_id AND comp.published>0");
     $query->leftJoin("#__events AS event ON event.id = n.event_id AND event.published>0");
     $query->leftJoin("#__deals AS deal ON deal.id = n.deal_id AND deal.published>0");
     $query->leftJoin("#__people AS person on n.person_id = person.id AND person.published>0");
     $query->leftJoin("#__users AS owner ON n.owner_id = owner.id");
     $query->leftJoin("#__users AS author ON author.id = owner.id");
     $company_filter = $this->getState('Note.company_name');
     if ($company_filter != null) {
         $query->where("comp.name LIKE '%" . $company_filter . "%'");
     }
     //deal
     $deal_filter = $this->getState('Note.deal_name');
     if ($deal_filter != null) {
         $query->where("deal.name LIKE '%" . $deal_filter . "%'");
     }
     //person
     $person_filter = $this->getState('Note.person_name');
     if ($person_filter != null) {
     }
     if ($object_id) {
         switch ($type) {
             case 'person':
             case 'people':
                 $query->where('n.person_id =' . $object_id);
                 break;
             case 'company':
                 $query->where('(n.company_id =' . $object_id . ' OR deal.company_id = ' . $object_id . ' OR person.company_id = ' . $object_id . ")");
                 break;
             case 'deal':
                 $query->where('n.deal_id=' . $object_id);
                 break;
             case "event":
                 $query->where("n.event_id={$object_id}");
                 break;
         }
     }
     //owner
     $owner_filter = $this->getState('Note.owner_id');
     if ($owner_filter != null && $owner_filter != "all") {
         $owner_type = $this->getState('Note.owner_type');
         switch ($owner_type) {
             case "team":
                 $team_member_ids = UsersHelper::getTeamUsers($owner_filter, TRUE);
                 $query->where("n.owner_id IN (" . implode(',', $team_member_ids) . ")");
                 break;
             case "member":
                 $query->where("n.owner_id=" . $owner_filter);
                 break;
         }
     }
     //created
     $created_filter = $this->getState('Note.created');
     if ($company_filter != null) {
     }
     //category
     $category_filter = $this->getState('Note.category_id');
     if ($category_filter != null) {
         $query->where("n.category_id=" . $category_filter);
     }
     if ($this->_id) {
         if (is_array($this->_id)) {
             $query->where("n.id IN (" . implode(',', $this->_id) . ")");
         } else {
             $query->where("n.id={$this->_id}");
         }
     }
     /** ---------------------------------------------------------------
      * Filter data using member role permissions
      */
     $member_id = UsersHelper::getUserId();
     $member_role = UsersHelper::getRole();
     $team_id = UsersHelper::getTeamId();
     if ($this->public_notes != true) {
         if ($member_role != 'exec') {
             //manager filter
             if ($member_role == 'manager') {
                 $query->where('owner.team_id = ' . $team_id);
             } else {
                 //basic user filter
                 $query->where(array('n.owner_id = ' . $member_id));
             }
         }
     }
     $query->where("n.published=" . $this->published);
     $query->order("n.modified DESC");
     //load results
     $db->setQuery($query);
     $results = $db->loadAssocList();
     //clean results
     if (count($results) > 0) {
         foreach ($results as $key => $note) {
             $results[$key]['created_formatted'] = DateHelper::formatDate($note['created']);
             $results[$key]['owner_avatar'] = CobaltHelper::getGravatar($note['email']);
         }
     }
     //$app->triggerEvent('onNoteLoad', array(&$results));
     if (!$display) {
         //return results
         return $results;
     } else {
         $notesView = ViewHelper::getView('note', 'default', 'phtml', array('notes' => $results));
         return $notesView;
     }
 }
Example #6
0
 /**
  * Get Yearly Revenue
  * @param $access_type we wish to filter by 'member','team','company'
  * @param $access_id the id of the $access_type we wish to filter by
  * @return mixed $results
  */
 public function getYearlyRevenue($access_type = null, $access_id = null)
 {
     //get db
     $db = JFactory::getDBO();
     $query = $db->getQuery(true);
     //get current year and months to loop through
     $current_year = DateHelper::formatDBDate(date('Y-01-01 00:00:00'));
     $month_names = DateHelper::getMonthNames();
     $months = DateHelper::getMonthDates();
     //get stage id to filter deals by
     $won_stage_ids = DealHelper::getWonStages();
     //gen query
     $results = array();
     foreach ($months as $month) {
         $start_date = $month['date'];
         $end_date = DateHelper::formatDBDate(date('Y-m-d 00:00:00', strtotime("{$start_date} + 1 months")));
         //flush the query
         $query = $db->getQuery(true);
         //generate query string
         $query->select("d.modified,SUM(d.amount) AS y");
         $query->from("#__deals AS d");
         $query->where("d.stage_id IN (" . implode(',', $won_stage_ids) . ")");
         $query->where("d.modified >= '{$start_date}'");
         $query->where("d.modified < '{$end_date}'");
         $query->where("d.modified IS NOT NULL");
         //sort by published deals
         $query->where("d.published>0");
         //filter by access type
         if ($access_type != 'company') {
             //team sorting
             if ($access_type == 'team') {
                 //get team members
                 $team_members = UsersHelper::getTeamUsers($access_id);
                 $query .= " AND d.owner_id IN (";
                 //loop to make string
                 foreach ($team_members as $key => $member) {
                     $query .= "'" . $member['id'] . "',";
                 }
                 $query = substr($query, 0, -1);
                 $query .= ") ";
             }
             //member filter
             if ($access_type == 'member') {
                 $query->where("d.owner_id={$access_id}");
             }
         }
         //get results and assign to month
         $db->setQuery($query);
         $totals = $db->loadAssoc();
         if (!$totals) {
             $totals = array('y' => 0);
         }
         $totals['y'] = (int) $totals['y'];
         $results[] = $totals;
     }
     //return
     return $results;
 }
Example #7
0
 /**
  * Build our query
  */
 public function _buildQuery()
 {
     /** Large SQL Selections **/
     $db = JFactory::getDBO();
     $query = $db->getQuery(true);
     if ($this->db->name == 'mysqli') {
         $this->db->setQuery("SET SQL_BIG_SELECTS=1")->execute();
     }
     $view = $this->app->input->get('view');
     $layout = $this->app->input->get('layout');
     //retrieve person id
     if (!$this->_id) {
         //get filters
         $type = $this->app->input->get('type') ? $this->app->input->get('type') : $this->type;
         $user = $this->app->input->get('user');
         $stage = $this->app->input->get('stage');
         $tag = $this->app->input->get('tag');
         $status = $this->app->input->get('status');
         $team = $this->app->input->get('team_id');
         //get session data
         $session = JFactory::getSession();
         //set user session data
         if ($tag != null) {
             $session->set('people_tag_filter', $tag);
         } else {
             $sess_tag = $session->get('people_tag_filter');
             $tag = $sess_tag;
         }
     }
     //TODO specific user id, access roles
     $db = JFactory::getDBO();
     //generate query
     $query = $db->getQuery(true);
     $export = $this->app->input->get('export');
     if ($export) {
         $query->select('p.first_name,p.last_name,p.position,p.phone,p.email,p.home_address_1,p.home_address_2,' . 'p.home_city,p.home_state,p.home_zip,p.home_country,p.fax,p.website,p.facebook_url,p.twitter_user,' . 'p.linkedin_url,p.created,p.tags,p.type,p.info,p.modified,p.work_address_1,p.work_address_2,' . 'p.work_city,p.work_state,p.work_zip,p.work_country,p.assignment_note,p.mobile_phone,p.home_email,' . 'p.other_email,p.home_phone,c.name as company_name, CONCAT(u2.first_name,NULL,u2.last_name) AS assignee_name,' . 'u.first_name AS owner_first_name,' . 'u.last_name AS owner_last_name, stat.name as status_name,' . 'source.name as source_name');
         $query->from('#__people AS p');
         $query->leftJoin('#__companies AS c ON c.id = p.company_id');
         $query->leftJoin('#__people_status AS stat ON stat.id = p.status_id');
         $query->leftJoin('#__sources AS source ON source.id = p.source_id');
         $query->leftJoin("#__users AS u ON u.id = p.owner_id");
         $query->leftJoin("#__users AS u2 ON u2.id = p.assignee_id");
     } else {
         $query->select('p.*,c.name as company_name, CONCAT(u2.first_name,NULL,u2.last_name) AS assignee_name,u.first_name AS owner_first_name,
                     u.last_name AS owner_last_name, stat.name as status_name,stat.color as status_color,
                     source.name as source_name,event.id as event_id,
                     event.name as event_name, event.type as event_type,
                     event.due_date as event_due_date,event.description as event_description');
         $query->from('#__people AS p');
         $query->leftJoin('#__companies AS c ON c.id = p.company_id');
         $query->leftJoin('#__people_status AS stat ON stat.id = p.status_id');
         $query->leftJoin('#__sources AS source ON source.id = p.source_id');
         $query->leftJoin("#__users AS u ON u.id = p.owner_id");
         $query->leftJoin("#__users AS u2 ON u2.id = p.assignee_id");
         //join tasks
         $query->leftJoin("#__events_cf as event_person_cf on event_person_cf.association_id = p.id AND event_person_cf.association_type ='person'");
         $query->leftJoin("#__events as event on event.id = event_person_cf.event_id");
     }
     // group ids
     $query->group("p.id");
     /** ---------------------------------------------------------------
      * Filter data using member role permissions
      */
     $member_id = UsersHelper::getUserId();
     $member_role = UsersHelper::getRole();
     $team_id = UsersHelper::getTeamId();
     $owner_filter = $this->state->get('People.owner_id_filter');
     $owner_filter_team = $this->state->get('People.owner_id_filter', $team_id);
     $owner_filter_member = $this->state->get('People.owner_id_filter', $member_id);
     $owner_type_filter = $this->state->get('People.owner_type_filter');
     if ($owner_filter && $owner_filter == "all") {
         if ($member_role != 'exec') {
             if ($member_role == 'manager') {
                 $query->where("(u.team_id={$owner_filter_team} OR u2.team_id={$owner_filter_team})");
             } else {
                 $query->where("(p.owner_id={$owner_filter_member} OR p.assignee_id={$owner_filter_member})");
             }
         }
     } elseif ($owner_type_filter == 'team') {
         $query->where("(u.team_id={$owner_filter_team} OR u2.team_id={$owner_filter_team})");
     } elseif ($owner_type_filter == 'member') {
         $query->where("(p.owner_id={$owner_filter_member} OR p.assignee_id={$owner_filter_member})");
     } else {
         if (!isset($owner_filter)) {
             if ($this->_id) {
                 if ($member_role == "basic") {
                     $query->where("(p.owner_id={$member_id} OR p.assignee_id={$member_id})");
                 }
                 if ($member_role == "manager") {
                     $team_members = UsersHelper::getTeamUsers($team_id, TRUE);
                     $team_members = array_merge($team_members, array(0 => $member_id));
                     $query->where("(p.owner_id IN(" . implode(',', $team_members) . ") OR p.assignee_id IN(" . implode(',', $team_members) . "))");
                 }
             } else {
                 $query->where("(p.owner_id={$member_id} OR p.assignee_id={$member_id})");
             }
         }
     }
     //searching for specific person
     if ($this->_id) {
         if (is_array($this->_id)) {
             $query->where("p.id IN (" . implode(',', $this->_id) . ")");
         } else {
             $query->where("p.id={$this->_id}");
         }
     }
     if (!$this->_id) {
         if (!$export) {
             //filter data
             $item_filter = $this->state->get('People.item_filter', $this->app->input->getString('item'));
             if ($item_filter && $item_filter != 'all') {
                 switch ($item_filter) {
                     case 'leads':
                         $query->where("p.type='lead'");
                         break;
                     case 'not_leads':
                         $query->where("p.type='contact'");
                         break;
                 }
             }
             //search with status
             $status_filter = $this->state->get('People.item_filter', $status);
             if ($status_filter && $status_filter != 'any') {
                 $query->where('p.status_id=' . $status_filter);
             }
             //search by tags
             if ($tag) {
             }
             //get current date
             $date = DateHelper::formatDBDate(date('Y-m-d 00:00:00'));
             $stage_filter = $this->state->get('People.stage_filter', $stage);
             //filter for type
             if ($stage != null && $stage != 'all') {
                 //filter for deals//tasks due today
                 if ($stage == '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}'");
                 }
                 //filter for deals//tasks due tomorrow
                 if ($stage == "tomorrow") {
                     $tomorrow = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() + 1 * 24 * 60 * 60));
                     $query->where("event.due_date='" . $tomorrow . "'");
                 }
                 //filter for people updated in the last 30 days
                 if ($stage == "past_thirty") {
                     $last_thirty_days = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() - 30 * 24 * 60 * 60));
                     $query->where("p.modified >'{$last_thirty_days}'");
                 }
                 //filter for recently added people
                 if ($stage == "recently_added") {
                     $last_five_days = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() - 5 * 24 * 60 * 60));
                     $query->where("p.modified >'{$last_five_days}'");
                 }
                 //filter for last imported people
                 if ($stage == "last_import") {
                 }
             } else {
                 //get latest task entry
                 if ($this->recent) {
                     $query->where("( 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=p.id AND e2.published>0) )");
                 }
             }
         }
         /** company filter **/
         if ($this->company_id) {
             $query->where("p.company_id=" . $this->company_id);
         }
         if ($this->event_id) {
             $query->where("event.id=" . $this->event_id);
         }
         /** person name filter **/
         $person_filter = $this->getState('People.person_name');
         if ($person_filter != null) {
             $query->where("( p.first_name LIKE '%" . $person_filter . "%' OR p.last_name LIKE '%" . $person_filter . "%' OR CONCAT(p.first_name,' ',p.last_name) LIKE '%" . $person_filter . "%')");
         }
     }
     $query->where("p.published=" . $this->published);
     //return query string
     return $query;
 }
Example #8
0
 /**
  * Build our db query object
  */
 public function _buildQuery()
 {
     $app = \Cobalt\Container::fetch('app');
     if ($this->db->name == 'mysqli') {
         $this->db->setQuery("SET SQL_BIG_SELECTS=1")->execute();
     }
     $user = $this->_user;
     $team = $this->_team;
     $id = $this->_id;
     $type = $this->_type;
     $view = $app->input->get('view');
     if (!$id) {
         $session = $app->getSession();
         //determine whether we are searching for a team or user
         if ($user) {
             $session->set('company_team_filter', null);
         }
         if ($team) {
             $session->set('company_user_filter', null);
         }
         //set user session data
         if ($type != null) {
             $session->set('company_type_filter', $type);
         } else {
             $sess_type = $session->get('company_type_filter');
             $type = $sess_type;
         }
         if ($user != null) {
             $session->set('company_user_filter', $user);
         } else {
             $sess_user = $session->get('company_user_filter');
             $user = $sess_user;
         }
         if ($team != null) {
             $session->set('company_team_filter', $team);
         } else {
             $sess_team = $session->get('company_team_filter');
             $team = $sess_team;
         }
     }
     //generate query for base companies
     $query = $this->db->getQuery(true);
     $export = $app->input->get('export');
     if ($export) {
         $select_string = 'c.name,c.description,c.address_1,c.address_2,c.address_city,';
         $select_string .= 'c.address_state,c.address_zip,c.address_country,c.website,c.created,c.modified';
         $query->select($select_string)->from("#__companies as c")->leftJoin("#__users AS u on u.id = c.owner_id");
     } else {
         $query->select('c.*')->from("#__companies as c")->leftJoin("#__users AS u on u.id = c.owner_id");
     }
     if (!$id) {
         //get current date
         $date = DateHelper::formatDBDate(date('Y-m-d 00:00:00'));
         $type = $this->getState('Company.item_filter', $type);
         //filter for type
         if ($type != null && $type != "all") {
             //filter for companies with tasks due today
             if ($type == 'today') {
                 $query->leftJoin("#__events_cf as event_company_cf on event_company_cf.association_id = c.id AND event_company_cf.association_type='company'");
                 $query->join('INNER', "#__events as event on event.id = event_company_cf.event_id");
                 $query->where("event.due_date='{$date}'");
                 $query->where("event.published>0");
             }
             //filter for companies and deals//tasks due tomorrow
             if ($type == "tomorrow") {
                 $tomorrow = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() + 1 * 24 * 60 * 60));
                 $query->leftJoin("#__events_cf as event_company_cf on event_company_cf.association_id = c.id AND event_company_cf.association_type='company'");
                 $query->join('INNER', "#__events as event on event.id = event_company_cf.event_id");
                 $query->where("event.due_date='{$tomorrow}'");
                 $query->where("event.published>0");
             }
             //filter for companies updated in the last 30 days
             if ($type == "updated_thirty") {
                 $last_thirty_days = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() - 30 * 24 * 60 * 60));
                 $query->where("c.modified >'{$last_thirty_days}'");
             }
             //filter for past companies// last contacted 30 days ago or longer
             if ($type == "past") {
                 $last_thirty_days = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() - 30 * 24 * 60 * 60));
                 $query->where("c.modified <'{$last_thirty_days}'");
             }
             //filter for recent companies
             if ($type == "recent") {
                 $last_thirty_days = DateHelper::formatDBDate(date('Y-m-d 00:00:00', time() - 30 * 24 * 60 * 60));
                 $query->where("c.modified >'{$last_thirty_days}'");
             }
             $query->group("c.id");
         }
         /** company name filter **/
         $company_name = $this->getState('Company.' . $view . '_name');
         if ($company_name != null) {
             $query->where("( c.name LIKE '%" . $company_name . "%' )");
         }
     }
     //search for specific companies
     if ($id != null) {
         if (is_array($id)) {
             $query->where("c.id IN (" . implode(',', $id) . ")");
         } else {
             $query->where("c.id={$id}");
         }
     }
     //filter based on member access roles
     $user_id = UsersHelper::getUserId();
     $member_role = UsersHelper::getRole();
     $team_id = UsersHelper::getTeamId();
     //filter based on specified user
     if ($user && $user != 'all') {
         $query->where("c.owner_id = " . $user);
     }
     //filter based on team
     if ($team) {
         $team_members = UsersHelper::getTeamUsers($team, true);
         $query->where("c.owner_id IN (" . implode(',', $team_members) . ")");
     }
     //set user state requests
     $query->order($this->getState('Company.filter_order') . ' ' . $this->getState('Company.filter_order_Dir'))->where("c.published=" . $this->published);
     return $query;
 }
Example #9
0
 /**
  * Get Goal Information
  * @param $goal mixed array of goal information
  * @return $results int information relating to goal
  */
 public function goalInfo($goal)
 {
     //get DBO
     $db = JFactory::getDBO();
     $query = $db->getQuery(true);
     //win_cash
     if ($goal['goal_type'] == 'win_cash') {
         $query->select("SUM(amount)");
         $query->from("#__deals");
         //filter based on company
         if ($goal['assigned_type'] != 'company') {
             //filter by member
             if ($goal['assigned_type'] == 'member') {
                 $query->where("owner_id=" . $goal['assigned_id']);
             }
             //filter by team
             if ($goal['assigned_type'] == 'team') {
                 //get team members
                 $team_members = UsersHelper::getTeamUsers($goal['assigned_id']);
                 //filter by results having team ids
                 $query .= " WHERE owner_id IN(";
                 for ($i = 0; $i < count($team_members); $i++) {
                     $member = $team_members[$i];
                     $query .= "" . $member['id'] . ",";
                 }
                 $query = substr($query, 0, -1);
                 $query .= ")";
             }
             $query .= " AND stage_id IN (" . implode(',', $this->won_stage_ids) . ")";
         } else {
             $query->where("stage_id IN (" . implode(',', $this->won_stage_ids) . ")");
         }
         //filter by start and end date
         $query .= " AND modified >= '" . $goal['start_date'] . "'";
         $query .= " AND modified <= '" . $goal['end_date'] . "'";
     }
     //win_deals
     if ($goal['goal_type'] == 'win_deals') {
         $query->select("COUNT(id)");
         $query->from("#__deals");
         //filter based on company
         if ($goal['assigned_type'] != 'company') {
             //filter based on member
             if ($goal['assigned_type'] == 'member') {
                 $query->where("owner_id=" . $goal['assigned_id']);
             }
             //filter based on team
             if ($goal['assigned_type'] == 'team') {
                 //get team members
                 $team_members = UsersHelper::getTeamUsers($goal['assigned_id']);
                 //filter by results having team ids
                 $query .= " WHERE owner_id IN(";
                 for ($i = 0; $i < count($team_members); $i++) {
                     $member = $team_members[$i];
                     $query .= "" . $member['id'] . ",";
                 }
                 $query = substr($query, 0, -1);
                 $query .= ")";
             }
             $query .= " AND stage_id IN (" . implode(',', $this->won_stage_ids) . ")";
         } else {
             $query->where("stage_id IN (" . implode(',', $this->won_stage_ids) . ")");
         }
         //filter by start and end date
         $query .= " AND modified >= '" . $goal['start_date'] . "'";
         $query .= " AND modified <= '" . $goal['end_date'] . "'";
     }
     //move_deals
     if ($goal['goal_type'] == 'move_deals') {
         $query->select("COUNT(id)");
         $query->from("#__deals");
         //filter based on company
         if ($goal['assigned_type'] != 'company') {
             //filter based on member
             if ($goal['assigned_type'] == 'member') {
                 $query->where("owner_id=" . $goal['assigned_id']);
             }
             //filter based on team
             if ($goal['assigned_type'] == 'team') {
                 //get team members
                 $team_members = UsersHelper::getTeamUsers($goal['assigned_id']);
                 //filter by results having team ids
                 $query .= " WHERE owner_id IN(";
                 for ($i = 0; $i < count($team_members); $i++) {
                     $member = $team_members[$i];
                     $query .= "" . $member['id'] . ",";
                 }
                 $query = substr($query, 0, -1);
                 $query .= ")";
             }
             $query .= " AND stage_id=" . $goal['stage_id'];
         } else {
             $query->where("stage_id=" . $goal['stage_id']);
         }
         //filter by start and end date
         $query .= " AND modified >= '" . $goal['start_date'] . "'";
         $query .= " AND modified <= '" . $goal['end_date'] . "'";
     }
     //complete_tasks
     if ($goal['goal_type'] == 'complete_tasks') {
         $query->select("COUNT(id)");
         $query->from("#__events");
         //filter based on company
         if ($goal['assigned_type'] != 'company') {
             //filter based on member
             if ($goal['assigned_type'] == 'member') {
                 $query->where("assignee_id=" . $goal['assigned_id']);
             }
             //filter based on team
             if ($goal['assigned_type'] == 'team') {
                 //get team members
                 $team_members = UsersHelper::getTeamUsers($goal['assigned_id']);
                 //filter by results having team ids
                 $query .= " WHERE assignee_id IN(";
                 for ($i = 0; $i < count($team_members); $i++) {
                     $member = $team_members[$i];
                     $query .= "" . $member['id'] . ",";
                 }
                 $query = substr($query, 0, -1);
                 $query .= ")";
             }
             $query .= " AND category_id=" . $goal['category_id'];
             $query .= " AND completed=1";
         } else {
             $query->where("category_id=" . $goal['category_id']);
             $query->where("completed=1");
         }
         //filter by start and end date
         $query .= " AND modified >= '" . $goal['start_date'] . "'";
         $query .= " AND modified <= '" . $goal['end_date'] . "'";
     }
     //write_notes
     if ($goal['goal_type'] == 'write_notes') {
         $query->select("COUNT(id)");
         $query->from("#__notes");
         //filter by company
         if ($goal['assigned_type'] != 'company') {
             //filter based on member
             if ($goal['assigned_type'] == 'member') {
                 $query->where("owner_id=" . $goal['assigned_id']);
             }
             //filter based on team
             if ($goal['assigned_type'] == 'team') {
                 //get team members
                 $team_members = UsersHelper::getTeamUsers($goal['assigned_id']);
                 //filter by results having team ids
                 $query .= " WHERE owner_id IN(";
                 for ($i = 0; $i < count($team_members); $i++) {
                     $member = $team_members[$i];
                     $query .= "" . $member['id'] . ",";
                 }
                 $query = substr($query, 0, -1);
                 $query .= ")";
             }
             $query .= " AND category_id=" . $goal['category_id'];
         } else {
             $query->where("category_id=" . $goal['category_id']);
         }
         //filter by start and end date
         $query .= " AND created >= '" . $goal['start_date'] . "'";
         $query .= " AND created <= '" . $goal['end_date'] . "'";
     }
     //create_deals
     if ($goal['goal_type'] == 'create_deals') {
         $query->select("COUNT(id)");
         $query->from("#__deals");
         //filter by company
         if ($goal['assigned_type'] != 'company') {
             //filter based on member
             if ($goal['assigned_type'] == 'member') {
                 $query->where("owner_id=" . $goal['assigned_id']);
             }
             //filter by team
             if ($goal['assigned_type'] == 'team') {
                 //get team members
                 $team_members = UsersHelper::getTeamUsers($goal['assigned_id']);
                 //filter by results having team ids
                 $query .= " WHERE owner_id IN(";
                 for ($i = 0; $i < count($team_members); $i++) {
                     $member = $team_members[$i];
                     $query .= "" . $member['id'] . ",";
                 }
                 $query = substr($query, 0, -1);
                 $query .= ")";
             }
         }
         //filter by start and end date
         $query .= " AND created >= '" . $goal['start_date'] . "'";
         $query .= " AND created <= '" . $goal['end_date'] . "'";
     }
     $query .= "AND published=" . $this->published;
     //return info
     $db->setQuery($query);
     $results = $db->loadResult();
     return $db->loadResult();
 }