public function __construct() { $this->previousDay = DateHelper::formatDBDate(date('Y-m-d') . " - 1 day"); $this->today = DateHelper::formatDBDate(date('Y-m-d')); $this->access = UsersHelper::getRole($this->person_id); $this->users = $this->getUsers($this->person_id, $this->access); }
/** * Method to store custom field cf data associated with items * @param int $id : The id of the item we wish to store associated data * @param mixed $cf_data : The data to be stored * @return void * */ public static function storeCustomCf($id, $cf_data, $type) { //Get DBO $db = \Cobalt\Container::fetch('db'); $query = $db->getQuery(true); //date generation $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); //Loop through $cf_data array to update/insert for ($i = 0; $i < count($cf_data); $i++) { //assign the data $row = $cf_data[$i]; $query->clear(); $query->select("COUNT(*)")->from("#__" . $type . "_custom_cf")->where($type . "_id=" . $id . " AND custom_field_id=" . $row['custom_field_id']); $db->setQuery($query); $count = $db->loadResult(); if ($count > 0) { //mysql query $query->clear(); $query->update('#__' . $type . '_custom_cf'); $query->set($type . "_id=" . $id . ",custom_field_id=" . $row['custom_field_id'] . ",value='" . $row['custom_field_value'] . "'" . ",modified='{$date}'"); $query->where($type . "_id={$id} AND custom_field_id=" . $row['custom_field_id']); $db->setQuery($query); $db->query(); } else { $query->clear(); $query->insert('#__' . $type . '_custom_cf'); $query->set($type . "_id=" . $id . ",custom_field_id=" . $row['custom_field_id'] . ",value='" . $row['custom_field_value'] . "'" . ",modified='{$date}'"); $db->setQuery($query); $db->query(); } } }
public function execute() { //post data $data = $this->getInput()->getArray(); //set date parameters $start_date = DateHelper::formatDBDate(date("Y-m-d 00:00:00", $data['start'])); $end_date = DateHelper::formatDBDate(date("Y-m-d 00:00:00", $data['end'])); //load model $model = new EventModel(); //set model parameters $model->set('start_date', "{$start_date}"); $model->set('end_date', "{$end_date}"); $model->set('loc', "calendar"); //get events $events = $model->getEvents(); echo json_encode($events); }
/** * Method to store a record * @param $_POST data * @return boolean True on success */ public function store() { $app = \Cobalt\Container::fetch('app'); //Load Tables $row = new GoalTable(); $oldRow = new GoalTable(); $data = $app->input->getArray(); //date generation $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); if (!array_key_exists('id', $data)) { $data['created'] = $date; $status = "created"; } else { $row->load($data['id']); $oldRow->load($data['id']); $status = "updated"; } //assign checkboxes if (array_key_exists('leaderboard', $data)) { $data['leaderboard'] = 1; } else { $data['leaderboard'] = 0; } //assign owner id $data['owner_id'] = UsersHelper::getUserId(); // Bind the form fields to the table if (!$row->bind($data)) { $this->setError($this->db->getErrorMsg()); return false; } // Make sure the record is valid if (!$row->check()) { $this->setError($this->db->getErrorMsg()); return false; } // Store the web link table to the database if (!$row->store()) { $this->setError($this->db->getErrorMsg()); return false; } ActivityHelper::saveActivity($oldRow, $row, 'goal', $status); return true; }
/** * Method to store a record * * @return boolean True on success */ public function store() { $app = \Cobalt\Container::fetch('app'); //Load Tables $row = new ConversationTable(); $oldRow = new ConversationTable(); $data = $app->input->getRequest('post'); //date generation $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); if (!array_key_exists('id', $data)) { $data['created'] = $date; $status = "created"; } else { $row->load($data['id']); $oldRow->load($data['id']); $status = "updated"; } $data['modified'] = $date; $data['author'] = UsersHelper::getUserId(); // Bind the form fields to the table if (!$row->bind($data)) { $this->setError($this->db->getErrorMsg()); return false; } // Make sure the record is valid if (!$row->check()) { $this->setError($this->db->getErrorMsg()); return false; } // Store the web link table to the database if (!$row->store()) { $this->setError($this->db->getErrorMsg()); return false; } $id = array_key_exists('id', $data) ? $data['id'] : $this->db->insertId(); ActivityHelper::saveActivity($oldRow, $row, 'conversation', $status); return $id; }
/** * Run items through template system */ public function createTemplate() { $template_id = $this->app->input->get('template_id'); $association_id = $this->app->input->get('association_id'); $association_type = $this->app->input->get('association_type'); $template = $this->getTemplate($template_id); $current_date = date("Y-m-d 00:00:00"); if (count($template) > 0) { $event_model = new Event(); foreach ($template as $event) { unset($event['id']); $event['association_id'] = $association_id; $event['association_type'] = $association_type; $event['type'] = "task"; $event['due_date'] = DateHelper::formatDBDate(date("Y-m-d", strtotime($current_date . " +" . $event['day'] . " days")), false); $event['due_date_hour'] = "00:00:00"; if (!$event_model->store($event)) { return false; } } } return true; }
/** * Save user avatars * @return [type] [description] */ public function saveAvatar() { //this is the name of the field in the html form, filedata is the default name for swfupload //so we will leave it as that $fieldName = 'avatar'; //any errors the server registered on uploading $fileError = $_FILES[$fieldName]['error']; if ($fileError > 0) { switch ($fileError) { case 1: echo TextHelper::_('FILE TO LARGE THAN PHP INI ALLOWS'); return false; case 2: echo TextHelper::_('FILE TO LARGE THAN HTML FORM ALLOWS'); return false; case 3: echo TextHelper::_('ERROR PARTIAL UPLOAD'); return false; case 4: echo TextHelper::_('ERROR NO FILE'); return false; } } //check the file extension is ok $fileName = $_FILES[$fieldName]['name']; $fileTemp = $_FILES[$fieldName]['tmp_name']; $uploadedFileNameParts = explode('.', $fileName); $uploadedFileExtension = array_pop($uploadedFileNameParts); $validFileExts = explode(',', 'jpeg,jpg,png,gif,bmp'); //assume the extension is false until we know its ok $extOk = false; //go through every ok extension, if the ok extension matches the file extension (case insensitive) //then the file extension is ok foreach ($validFileExts as $key => $value) { if (preg_match("/{$value}/i", $uploadedFileExtension)) { $extOk = true; } } if ($extOk == false) { echo TextHelper::_('INVALID EXTENSION'); return false; } //data generation $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); $hashFilename = md5($fileName . $date) . "." . $uploadedFileExtension; //lose any special characters in the filename //$fileName = preg_replace("[^A-Za-z0-9.]", "-", $fileName); //always use constants when making file paths, to avoid the possibilty of remote file inclusion $uploadPath = JPATH_SITE . '/src/Cobalt/media/avatars/' . $hashFilename; if (!File::upload($fileTemp, $uploadPath)) { echo TextHelper::_('ERROR MOVING FILE'); return false; } $image = new Image(); $image->loadFile($uploadPath); $image->resize(50, 50, false); $image->toFile($uploadPath); $data = array('id' => $this->state->get('item_id'), 'avatar' => $hashFilename); $item_type = $this->state->get('item_type'); $this->deleteOldAvatar($data['id'], $item_type); switch ($item_type) { case "people": $model_name = "people"; break; case "companies": $model_name = "company"; break; } $modelClass = "Cobalt\\Model\\" . ucwords($model_name); $model = new $modelClass($this->db); $model->store($data); return JUri::base() . 'src/Cobalt/media/avatars/' . $hashFilename; }
/** * Method to store a record * * @return boolean True on success */ public function store($data = null) { $app = \Cobalt\Container::fetch('app'); //Load Tables $row = new NoteTable(); $oldRow = new NoteTable(); if ($data == null) { $data = $app->input->getArray(array('note' => 'string', 'deal_id' => 'int', 'person_id' => 'int', 'name' => 'string', 'category_id' => 'int', 'company_id' => 'int', 'note_id' => 'int', 'event_id' => 'int')); } if (array_key_exists('note_id', $data)) { $data['id'] = $data['note_id']; } if (array_key_exists('is_email', $data)) { $model = new Mail(); $email = $model->getEmail($data['email_id']); $data['note'] = $email; } /** check for and automatically associate and create primary contacts or people **/ if (array_key_exists('person_name', $data) && $data['person_name'] != "") { $peopleModel = new People(); $existingPerson = $peopleModel->checkPersonName($data['person_name']); if ($existingPerson == "") { $pdata = array(); $name = explode(" ", $data['person_name']); $pdata['first_name'] = array_key_exists(0, $name) ? $name[0] : ""; $pdata['last_name'] = array_key_exists(1, $name) ? $name[1] : ""; $data['person_id'] = $peopleModel->store($pdata); } else { $data['person_id'] = $existingPerson; } } /** check for and automatically associate and create deals **/ if (array_key_exists('deal_name', $data) && $data['deal_name'] != "" && (!array_key_exists('deal_id', $data) || empty($data['deal_id']) || $data['deal_id'] == 0)) { $dealModel = new Deal(); $existingDeal = $dealModel->checkDealName($data['deal_name']); if ($existingDeal == "") { $pdata = array(); $pdata['name'] = $data['deal_name']; $data['deal_id'] = $dealModel->store($pdata); } else { $data['deal_id'] = $existingDeal; } } //date generation $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); if (!array_key_exists('id', $data)) { $data['created'] = $date; $status = "created"; } else { $row->load($data['id']); $oldRow->load($data['id']); $status = "updated"; } $data['modified'] = $date; $data['owner_id'] = UsersHelper::getUserId(); // Bind the form fields to the table if (!$row->bind($data)) { $this->setError($this->db->getErrorMsg()); return false; } //$app->triggerEvent('onBeforeNoteSave', array(&$row)); // Make sure the record is valid if (!$row->check()) { $this->setError($this->db->getErrorMsg()); return false; } // Store the web link table to the database if (!$row->store()) { $this->setError($this->db->getErrorMsg()); return false; } if (array_key_exists('id', $data) && intval($data['id'])) { $id = $data['id']; } else { $id = $this->db->insertId(); } ActivityHelper::saveActivity($oldRow, $row, 'note', $status); //Store email attachments if (array_key_exists('is_email', $data)) { $model = new CobaltModelMail(); $model->storeAttachments($data['email_id'], $data['person_id']); } //$app->triggerEvent('onAfterNoteSave', array(&$row)); return $id; }
/** * Get yearly commission data for user * @param int $id to search for * @return mixed $results */ public function getYearlyCommissionData($id) { //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 = $this->db->getQuery(true)->select("d.owner_id,d.modified,SUM(d.amount) AS y")->from("#__deals AS d")->where("d.stage_id IN (" . implode(',', $won_stage_ids) . ")")->where("d.modified >= '{$start_date}'")->where("d.modified < '{$end_date}'")->where("d.modified IS NOT NULL")->where("d.owner_id={$id}")->group("d.owner_id")->where("d.published>0"); $results[] = $this->db->setQuery($query)->loadAssoc(); } //clean data for commission rate foreach ($results as $key => $result) { $commission_rate = UsersHelper::getCommissionRate($result['owner_id']); $results[$key]['y'] = (int) $result['y'] * ($commission_rate / 100); } return $results; }
/** * 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; }
/** * Store contacts to CF tables * @param [type] $deal_id [description] * @param [type] $contact_id [description] * @return [type] [description] */ public function storeContact($deal_id, $contact_id) { $query = $this->db->getQuery(true)->select("COUNT(*)")->from("#__people_cf")->where("association_id=" . $deal_id)->where("association_type='deal'")->where("person_id=" . $contact_id); $contacts = $this->db->setQuery($query)->loadResult(); if ($contacts == 0) { $created = DateHelper::formatDBDate(date('Y-m-d H:i:s')); $data = array($deal_id . ",'deal'," . $contact_id . ",'" . $created . "'"); $query->clear()->insert('#__people_cf')->columns('association_id, association_type, person_id, created')->values($data); $this->db->setQuery($query)->execute(); } }
/** * Method to store a record * * @return boolean True on success */ public function store($data = null) { if (!$data) { $data = $this->app->input->post->getArray(); } //Load Table $row = new UserTable(); if (isset($data['id']) && $data['id']) { $row->load($data['id']); } if (isset($data['fullscreen'])) { $data['fullscreen'] = !$row->fullscreen; } if (isset($data['password']) && $data['password']) { $data['password'] = UsersHelper::hashPassword($data['password']); } //date generation $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); $data['modified'] = $date; // Bind the form fields to the table if (!$row->bind($data)) { $this->setError($this->db->getErrorMsg()); return false; } // Make sure the record is valid if (!$row->check()) { $this->setError($this->db->getErrorMsg()); return false; } // Store the web link table to the database if (!$row->store()) { $this->setError($this->db->getErrorMsg()); return false; } //update users email address if (array_key_exists('email', $data)) { $this->updateEmail($row->id, $data['email']); } if (isset($data['team_name']) && $data['team_name']) { $teamModel = new Teams(); $teamModel->createTeam($row->id, $data['team_name']); } $this->app->refreshUser(); return $row->id; }
/** * 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; }
/** * 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; }
/** * Method to store a record * * @return boolean True on success */ public function store($data = null) { if ($data) { $data = (array) $data; $_FILES = array(); $_FILES['document'] = $data; $_FILES['tmp_name'] = $data['attachment']; $fileName = $data['value']; $fileTemp = $data['attachment']; $association_id = $data['association_id']; $association_type = $data['association_type']; $uploadedFileExtension = substr(strrchr($fileName, '.'), 1); $data['is_attachment'] = 1; $data['email'] = 1; } else { $association_id = $_POST['association_id']; $association_type = $_POST['association_type']; //this is the name of the field in the html form, filedata is the default name for swfupload //so we will leave it as that $fieldName = 'document'; //any errors the server registered on uploading $fileError = $_FILES[$fieldName]['error']; if ($fileError > 0) { switch ($fileError) { case 1: echo TextHelper::_('FILE TO LARGE THAN PHP INI ALLOWS'); return; case 2: echo TextHelper::_('FILE TO LARGE THAN HTML FORM ALLOWS'); return; case 3: echo TextHelper::_('ERROR PARTIAL UPLOAD'); return; case 4: echo TextHelper::_('ERROR NO FILE'); return; } } //check the file extension is ok $fileName = $_FILES[$fieldName]['name']; $fileTemp = $_FILES[$fieldName]['tmp_name']; } $uploadedFileNameParts = explode('.', $fileName); $uploadedFileExtension = array_pop($uploadedFileNameParts); $validFileExts = explode(',', 'jpeg,jpg,png,gif,pdf,doc,docx,odt,rtf,ppt,xls,txt'); //assume the extension is false until we know its ok $extOk = false; //go through every ok extension, if the ok extension matches the file extension (case insensitive) //then the file extension is ok foreach ($validFileExts as $key => $value) { if (preg_match("/{$value}/i", $uploadedFileExtension)) { $extOk = true; } } if ($extOk == false) { echo TextHelper::_('INVALID EXTENSION'); return; } //data generation $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); $hashFilename = md5($fileName . $date) . "." . $uploadedFileExtension; //lose any special characters in the filename $fileName = preg_replace("[^A-Za-z0-9.]", "-", $fileName); //always use constants when making file paths, to avoid the possibilty of remote file inclusion $uploadPath = JPATH_SITE . '//documents/' . $hashFilename; if ($data['is_attachment']) { if (!File::write($uploadPath, $fileTemp)) { echo TextHelper::_('ERROR MOVING FILE'); return; } } else { if (!File::upload($fileTemp, $uploadPath)) { echo TextHelper::_('ERROR MOVING FILE'); return; } } $fileSize = filesize($uploadPath); //update the database $newData = array('name' => $fileName, 'filename' => $hashFilename, 'association_id' => $association_id, 'association_type' => $association_type, 'filetype' => $uploadedFileExtension, 'size' => $fileSize / 1024, 'created' => $date); if (array_key_exists('email', $data) && $data['email']) { $newData['email'] = 1; } //Load Tables $row = new DocumentTable(); $oldRow = new DocumentTable(); //date generation $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); if (!array_key_exists('id', $newData)) { $newData['created'] = $date; $status = "created"; } else { $row->load($data['id']); $oldRow->load($data['id']); $status = "updated"; } $is_image = is_array(getimagesize($uploadPath)) ? true : false; $newData['modified'] = $date; $newData['owner_id'] = UsersHelper::getUserId(); $newData['is_image'] = $is_image; // Bind the form fields to the table if (!$row->bind($newData)) { $this->setError($this->db->getErrorMsg()); return false; } $app = \Cobalt\Container::fetch('app'); //$app->triggerEvent('onBeforeDocumentSave', array(&$row)); // Make sure the record is valid if (!$row->check()) { $this->setError($this->db->getErrorMsg()); return false; } // Store the web link table to the database if (!$row->store()) { $this->setError($this->db->getErrorMsg()); return false; } $id = array_key_exists('id', $data) ? $data['id'] : $this->db->insertId(); ActivityHelper::saveActivity($oldRow, $row, 'document', $status); //$app->triggerEvent('onAfterDocumentSave', array(&$row)); return $id; }
/** * 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; }
public function execute() { //get post data $data = $this->getInput()->getArray(array('association_id' => 'int', 'association_type' => 'string', 'person_id' => 'int')); //get db Object $db = $this->container->fetch('db'); $query = $db->getQuery(true); $table = $this->getInput()->getCmd('table'); $loc = $this->getInput()->getCmd('loc'); unset($data['table']); unset($data['loc']); if (empty($data['person_id'])) { unset($data['person_id']); } //write to tables if there is no association already in cf tables $query->select('* FROM #__' . $table . '_cf'); //loop to see if we have matches in database $overrides = array('tmpl'); foreach ($data as $key => $value) { if (!in_array($key, $overrides)) { $query->where($key . " = '" . $value . "'"); } } $db->setQuery($query); $results = $db->loadAssocList(); $response = new \stdClass(); //determine if we found any results if (count($results) == 0) { $query->insert('#__' . $table . '_cf'); //timestamp $data['created'] = date('Y-m-d H:i:s'); $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); //loop through data to get query string foreach ($data as $key => $value) { if (!in_array($key, $overrides)) { // determine key and key values $query->set($key . " = '" . $value . "'"); } } $db->setQuery($query)->execute(); //if return data requested if ($table == 'people') { //determine which page we want are wanting to send information back to if ($loc == 'deal') { $model = new PeopleModel(); $return = $model->getPerson($data['person_id']); $response->alert = new \stdClass(); $response->alert->message = \JText::_('DEAL_CONTACT_ADDED_SUCCESS'); $response->alert->type = 'success'; $response->item = $return; $response->reload = 2000; } if ($loc == 'person') { $model = new DealModel(); $return = $model->getDeals(array_key_exists('deal_id', $data) ? $data['deal_id'] : ""); $return = $return[0]; $response->alert = new \stdClass(); $response->alert->message = $return; $response->alert->type = 'success'; } } } else { $response->alert = new \stdClass(); $response->alert->message = \JText::_('DEAL_CONTACT_ERROR_FAILURE_ADD_PERSON'); $response->alert->type = 'error'; } //return json data echo json_encode($response); }
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; }
public function store() { //Load Tables $app = \Cobalt\Container::fetch('app'); $row = new BrandingTable(); $data = $app->input->getRequest('post'); //date generation $date = DateHelper::formatDBDate(date('Y-m-d H:i:s')); $data['modified'] = $date; $this->changeDefault($data['id']); $fieldName = 'site_logo'; //any errors the server registered on uploading $fileError = $_FILES[$fieldName]['error']; if ($fileError > 0) { unset($data['site_logo']); } else { //check the file extension is ok $fileName = $_FILES[$fieldName]['name']; $fileTemp = $_FILES[$fieldName]['tmp_name']; $uploadedFileNameParts = explode('.', $fileName); $uploadedFileExtension = array_pop($uploadedFileNameParts); $validFileExts = explode(',', 'jpeg,jpg,png,gif,pdf,doc,docx,odt,rtf,ppt,xls,txt'); //assume the extension is false until we know its ok $extOk = false; //go through every ok extension, if the ok extension matches the file extension (case insensitive) //then the file extension is ok foreach ($validFileExts as $key => $value) { if (preg_match("/{$value}/i", $uploadedFileExtension)) { $extOk = true; } } if ($extOk == false) { echo TextHelper::_('INVALID EXTENSION'); return; } //data generation $hashFilename = md5($fileName . $date) . "." . $uploadedFileExtension; //lose any special characters in the filename //$fileName = preg_replace("[^A-Za-z0-9.]", "-", $fileName); //always use constants when making file paths, to avoid the possibilty of remote file inclusion $uploadPath = JPATH_SITE . '/src/Cobalt/media/logos/' . $hashFilename; if (!File::upload($fileTemp, $uploadPath)) { echo TextHelper::_('ERROR MOVING FILE'); return; } $fileSize = filesize($uploadPath); $this->updateSiteLogo($hashFilename); unset($data['site_logo']); } // Bind the form fields to the table if (!$row->bind($data)) { $this->setError($this->db->getErrorMsg()); return false; } // Make sure the record is valid if (!$row->check()) { $this->setError($this->db->getErrorMsg()); return false; } // Store the web link table to the database if (!$row->store()) { $this->setError($this->db->getErrorMsg()); return false; } return true; }