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