function getWebpages($project, $tag = '', $page = 1, $webpages_per_page = 10, $orderBy = 'title', $orderDir = 'ASC', $archived = false)
{
$orderDir = strtoupper($orderDir);
if ($orderDir != "ASC" && $orderDir != "DESC") {
$orderDir = "ASC";
}
if ($page < 0) {
$page = 1;
}
//$conditions = logged_user()->isMemberOfOwnerCompany() ? '' : ' `is_private` = 0';
if ($tag == '' || $tag == null) {
$tagstr = "1=1";
} else {
$tagstr = "(SELECT count(*) FROM `" . TABLE_PREFIX . "tags` WHERE `" . TABLE_PREFIX . "project_webpages`.`id` = `" . TABLE_PREFIX . "tags`.`rel_object_id` AND `" . TABLE_PREFIX . "tags`.`tag` = " . DB::escape($tag) . " AND `" . TABLE_PREFIX . "tags`.`rel_object_manager` = 'ProjectWebpages' ) > 0 ";
}
$permission_str = ' AND (' . permissions_sql_for_listings(ProjectWebpages::instance(), ACCESS_LEVEL_READ, logged_user()) . ')';
if ($project instanceof Project) {
$pids = $project->getAllSubWorkspacesCSV(true);
$project_str = " AND " . self::getWorkspaceString($pids);
} else {
$project_str = "";
}
if ($archived) {
$archived_cond = " AND `archived_by_id` <> 0";
} else {
$archived_cond = " AND `archived_by_id` = 0";
}
$conditions = $tagstr . $permission_str . $project_str . $archived_cond;
return ProjectWebpages::paginate(array("conditions" => $conditions, 'order' => DB::escapeField($orderBy) . " {$orderDir}"), config_option('files_per_page', 10), $page);
// paginate
}
function countUserInboxUnreadEmails()
{
$tp = TABLE_PREFIX;
$uid = logged_user()->getId();
$sql = "SELECT count(*) `c` FROM `{$tp}mail_contents` `a`, `{$tp}read_objects` `b` WHERE `b`.`rel_object_manager` = 'MailContents' AND `b`.`rel_object_id` = `a`.`id` AND `b`.`user_id` = '{$uid}' AND `b`.`is_read` = '1' AND `a`.`trashed_on` = '0000-00-00 00:00:00' AND `a`.`is_deleted` = 0 AND `a`.`archived_by_id` = 0 AND (`a`.`state` = '0' OR `a`.`state` = '5') AND " . permissions_sql_for_listings(MailContents::instance(), ACCESS_LEVEL_READ, logged_user(), null, '`a`');
$rows = DB::executeAll($sql);
$read = $rows[0]['c'];
$sql = "SELECT count(*) `c` FROM `{$tp}mail_contents` `a` WHERE `a`.`trashed_on` = '0000-00-00 00:00:00' AND `a`.`is_deleted` = 0 AND `a`.`archived_by_id` = 0 AND (`a`.`state` = '0' OR `a`.`state` = '5') AND " . permissions_sql_for_listings(MailContents::instance(), ACCESS_LEVEL_READ, logged_user(), null, '`a`');
$rows = DB::executeAll($sql);
$all = $rows[0]['c'];
return $all - $read;
}
/**
* Reaturn all calendar Events
*
* @param Project $project
* @return array
*/
static function getAllEventsByProject($project = null, $archived = false, $inc_sub = true, $user = null)
{
if ($project instanceof Project) {
if ($inc_sub) {
$pids = $project->getAllSubWorkspacesQuery(true);
} else {
$pids = $project->getId();
}
$wsstring = " AND " . self::getWorkspaceString($pids);
} else {
$wsstring = "";
}
if ($user instanceof User) {
$permissions = " AND " . permissions_sql_for_listings(self::instance(), ACCESS_LEVEL_READ, $user);
} else {
$permissions = "";
}
if ($archived) {
$archived_cond = " `archived_by_id` <> 0";
} else {
$archived_cond = " `archived_by_id` = 0";
}
$cond_str = $archived_cond . $wsstring . $permissions;
$result_events = self::findAll(array('conditions' => array($cond_str)));
// findAll
// Find invitations for events and logged user
ProjectEvents::addInvitations($result_events, $user instanceof User ? $user->getId() : 0);
return $result_events;
}
/**
* Enter description here...
* assumes manager has one field as PK
*
* @param DataManager $manager
* @param $access_level ACCESS_LEVEL_XX objects that defines which permission is being checked
* @param string $project_id string that will be compared to the project id while searching project_user table
* @param int $user_id user whose permissions are being checked
* @return unknown
*/
function permissions_sql_for_listings(DataManager $manager, $access_level, User $user, $project_id = '`project_id`', $table_alias = null)
{
if (!$manager instanceof DataManager) {
throw new Exception("Invalid manager '{$manager}' in permissions helper", -1);
return '';
}
$user_id = $user->getId();
$oup_tablename = ObjectUserPermissions::instance()->getTableName(true);
$wo_tablename = WorkspaceObjects::instance()->getTableName(true);
$users_table_name = Users::instance()->getTableName(true);
$pu_table_name = ProjectUsers::instance()->getTableName(true);
if ($user->isGuest() && $access_level == ACCESS_LEVEL_WRITE) {
return 'false';
}
if (isset($table_alias) && $table_alias && $table_alias != '') {
$object_table_name = $table_alias;
} else {
$object_table_name = $manager->getTableName();
}
if (!is_numeric($project_id)) {
$project_id = "{$object_table_name}.{$project_id}";
}
$object_id_field = $manager->getPkColumns();
$object_id = $object_table_name . '.' . $object_id_field;
$object_manager = get_class($manager);
$access_level_text = access_level_field_name($access_level);
$item_class = $manager->getItemClass();
$is_project_data_object = new $item_class() instanceof ProjectDataObject;
// permissions for contacts
if ($manager instanceof Contacts && can_manage_contacts($user)) {
return 'true';
}
if ($manager instanceof Companies && can_manage_contacts($user)) {
return 'true';
}
// permissions for file revisions
if ($manager instanceof ProjectFileRevisions) {
$pfTableName = "`" . TABLE_PREFIX . "project_files`";
return "{$object_table_name}.`file_id` IN (SELECT `id` FROM {$pfTableName} WHERE " . permissions_sql_for_listings(ProjectFiles::instance(), $access_level, $user) . ")";
}
// permissions for projects
if ($manager instanceof Projects) {
$pcTableName = "`" . TABLE_PREFIX . 'project_users`';
return "{$object_table_name}.`id` IN (SELECT `project_id` FROM {$pcTableName} `pc` WHERE `user_id` = {$user_id})";
}
// permissions for users
if ($manager instanceof Users) {
if (logged_user()->isMemberOfOwnerCompany()) {
return "true";
} else {
return "{$object_table_name}.`company_id` = " . owner_company()->getId() . " OR {$object_table_name}.`company_id` = " . logged_user()->getCompanyId();
}
}
$can_manage_object = manager_class_field_name($object_manager, $access_level);
// user is creator
$str = " ( `created_by_id` = {$user_id}) ";
// element belongs to personal project
/*if($is_project_data_object) // TODO: type of element belongs to a project
if (!in_array('project_id', $manager->getColumns())) {
$str .= "\n OR ( EXISTS(SELECT * FROM $users_table_name `xx_u`, $wo_tablename `xx_wo`
WHERE `xx_u`.`id` = $user_id
AND `xx_u`.`personal_project_id` = `xx_wo`.`workspace_id`
AND `xx_wo`.`object_id` = $object_id
AND `xx_wo`.`object_manager` = '$object_manager' )) ";
} else {
$str .= "\n OR ( $project_id = (SELECT `personal_project_id` FROM $users_table_name `xx_u` WHERE `xx_u`.`id` = $user_id)) ";
}
*/
// user or group has specific permissions over object
$group_ids = $user->getGroupsCSV();
$all_ids = '(' . $user_id . ($group_ids != '' ? ',' . $group_ids : '') . ')';
$str .= "\n OR ( EXISTS ( SELECT * FROM {$oup_tablename} `xx_oup` \n\t\t\t\tWHERE `xx_oup`.`rel_object_id` = {$object_id} \n\t\t\t\t\tAND `xx_oup`.`rel_object_manager` = '{$object_manager}' \n\t\t\t\t\tAND `xx_oup`.`user_id` IN {$all_ids} \n\t\t\t\t\tAND `xx_oup`.{$access_level_text} = true) )";
if ($is_project_data_object) {
// TODO: type of element belongs to a project
if (!in_array('project_id', $manager->getColumns())) {
$str .= "\n OR ( EXISTS ( SELECT * FROM {$pu_table_name} `xx_pu`, {$wo_tablename} `xx_wo` \n\t\t\t\tWHERE `xx_pu`.`user_id` IN {$all_ids} \n\t\t\t\t\tAND `xx_pu`.`project_id` = `xx_wo`.`workspace_id`\n\t\t\t\t\tAND `xx_wo`.`object_id` = {$object_id} \n\t\t\t\t\tAND `xx_wo`.`object_manager` = '{$object_manager}'\n\t\t\t\t\tAND `xx_pu`.{$can_manage_object} = true ) ) ";
} else {
$str .= "\n OR ( EXISTS ( SELECT * FROM {$pu_table_name} `xx_pu` \n\t\t\t\tWHERE `xx_pu`.`user_id` IN {$all_ids} \n\t\t\t\t\tAND `xx_pu`.`project_id` = {$project_id} \n\t\t\t\t\tAND `xx_pu`.{$can_manage_object} = true ) ) ";
}
}
// check account permissions in case of emails
if ($manager instanceof MailContents) {
$maccTableName = MailAccountUsers::instance()->getTableName(true);
$str .= "\n OR EXISTS(SELECT `id` FROM {$maccTableName} WHERE `account_id` = {$object_table_name}.`account_id` AND `user_id` = {$user_id})";
if (user_config_option('view deleted accounts emails', null, $user_id)) {
$str .= "\n OR ((SELECT count(*) FROM `" . TABLE_PREFIX . "mail_accounts` WHERE `id` = {$object_table_name}.`account_id`) = 0) AND `created_by_id` = {$user_id}";
}
}
$hookargs = array('manager' => $manager, 'access_level' => $access_level, 'user' => $user, 'project_id' => $project_id, 'table_alias' => $table_alias);
Hook::fire('permissions_sql', $hookargs, $str);
return ' (' . $str . ') ';
}
function new_list_tasks()
{
//load config options into cache for better performance
load_user_config_options_by_category_name('task panel');
// get query parameters, save user preferences if necessary
$status = array_var($_GET, 'status', null);
if (is_null($status) || $status == '') {
$status = user_config_option('task panel status', 2);
} else {
if (user_config_option('task panel status') != $status) {
set_user_config_option('task panel status', $status, logged_user()->getId());
}
}
$previous_filter = user_config_option('task panel filter', 'assigned_to');
$filter = array_var($_GET, 'filter');
if (is_null($filter) || $filter == '') {
$filter = user_config_option('task panel filter', 'assigned_to');
} else {
if (user_config_option('task panel filter') != $filter) {
set_user_config_option('task panel filter', $filter, logged_user()->getId());
}
}
if ($filter != 'no_filter') {
$filter_value = array_var($_GET, 'fval');
if (is_null($filter_value) || $filter_value == '') {
$filter_value = user_config_option('task panel filter value', logged_user()->getCompanyId() . ':' . logged_user()->getId());
set_user_config_option('task panel filter value', $filter_value, logged_user()->getId());
$filter = $previous_filter;
set_user_config_option('task panel filter', $filter, logged_user()->getId());
} else {
if (user_config_option('task panel filter value') != $filter_value) {
set_user_config_option('task panel filter value', $filter_value, logged_user()->getId());
}
}
}
$isJson = array_var($_GET, 'isJson', false);
if ($isJson) {
ajx_current("empty");
}
$project = active_project();
$tag = active_tag();
$template_condition = "`is_template` = 0 ";
//Get the task query conditions
$task_filter_condition = "";
switch ($filter) {
case 'assigned_to':
$assigned_to = explode(':', $filter_value);
$assigned_to_user = array_var($assigned_to, 1, 0);
$assigned_to_company = array_var($assigned_to, 0, 0);
if ($assigned_to_user > 0) {
$task_filter_condition = " AND (`assigned_to_user_id` = " . $assigned_to_user . " OR (`assigned_to_company_id` = " . $assigned_to_company . " AND `assigned_to_user_id` = 0)) ";
} else {
if ($assigned_to_company > 0) {
$task_filter_condition = " AND `assigned_to_company_id` = " . $assigned_to_company . " AND `assigned_to_user_id` = 0";
} else {
if ($assigned_to_company == -1 && $assigned_to_user == -1) {
$task_filter_condition = " AND `assigned_to_company_id` = 0 AND `assigned_to_user_id` = 0 ";
}
}
}
break;
case 'assigned_by':
if ($filter_value != 0) {
$task_filter_condition = " AND `assigned_by_id` = " . $filter_value . " ";
}
break;
case 'created_by':
if ($filter_value != 0) {
$task_filter_condition = " AND `created_by_id` = " . $filter_value . " ";
}
break;
case 'completed_by':
if ($filter_value != 0) {
$task_filter_condition = " AND `completed_by_id` = " . $filter_value . " ";
}
break;
case 'milestone':
$task_filter_condition = " AND `milestone_id` = " . $filter_value . " ";
break;
case 'priority':
$task_filter_condition = " AND `priority` = " . $filter_value . " ";
break;
case 'subtype':
if ($filter_value != 0) {
$task_filter_condition = " AND `object_subtype` = " . $filter_value . " ";
}
break;
case 'no_filter':
$task_filter_condition = "";
break;
default:
flash_error(lang('task filter criteria not recognised', $filter));
}
if ($project instanceof Project) {
$pids = $project->getAllSubWorkspacesQuery(true);
$projectstr = " AND " . ProjectTasks::getWorkspaceString($pids);
} else {
$pids = "";
$projectstr = "";
}
$permissions = " AND " . permissions_sql_for_listings(ProjectTasks::instance(), ACCESS_LEVEL_READ, logged_user());
$task_status_condition = "";
switch ($status) {
case 0:
// Incomplete tasks
$task_status_condition = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME);
break;
case 1:
// Complete tasks
$task_status_condition = " AND `completed_on` > " . DB::escape(EMPTY_DATETIME);
break;
case 10:
// Active tasks
$now = date('Y-m-j 00:00:00');
$task_status_condition = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " AND `start_date` <= '{$now}'";
break;
case 11:
// Overdue tasks
$now = date('Y-m-j 00:00:00');
$task_status_condition = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " AND `due_date` < '{$now}'";
break;
case 12:
// Today tasks
$now = date('Y-m-j 00:00:00');
$task_status_condition = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " AND `due_date` = '{$now}'";
break;
case 13:
// Today + Overdue tasks
$now = date('Y-m-j 00:00:00');
$task_status_condition = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " AND `due_date` <= '{$now}'";
break;
case 14:
// Today + Overdue tasks
$now = date('Y-m-j 00:00:00');
$task_status_condition = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " AND `due_date` <= '{$now}'";
break;
case 20:
// Actives task by current user
$now = date('Y-m-j 00:00:00');
$task_status_condition = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " AND `start_date` <= '{$now}' AND `assigned_to_user_id` = " . logged_user()->getId();
break;
case 21:
// Subscribed tasks by current user
$res20 = DB::execute("SELECT object_id FROM " . TABLE_PREFIX . "object_subscriptions WHERE `object_manager` LIKE 'ProjectTasks' AND `user_id` = " . logged_user()->getId());
$subs_rows = $res20->fetchAll($res20);
foreach ($subs_rows as $row) {
$subs[] = $row['object_id'];
}
unset($res20, $subs_rows, $row);
$now = date('Y-m-j 00:00:00');
$task_status_condition = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " AND `id` IN(" . implode(',', $subs) . ")";
break;
case 2:
// All tasks
break;
default:
throw new Exception('Task status "' . $status . '" not recognised');
}
if (!$tag) {
$tagstr = "";
} else {
$tagstr = " AND (select count(*) from " . TABLE_PREFIX . "tags where " . TABLE_PREFIX . "project_tasks.id = " . TABLE_PREFIX . "tags.rel_object_id and " . TABLE_PREFIX . "tags.tag = " . DB::escape($tag) . " and " . TABLE_PREFIX . "tags.rel_object_manager ='ProjectTasks' ) > 0 ";
}
$conditions = $template_condition . $task_filter_condition . $task_status_condition . $permissions . $tagstr . $projectstr . " AND `trashed_by_id` = 0 AND `archived_by_id` = 0";
//Now get the tasks
$tasks = ProjectTasks::findAll(array('conditions' => $conditions, 'order' => 'created_on DESC', 'limit' => user_config_option('task_display_limit') > 0 ? user_config_option('task_display_limit') + 1 : null));
ProjectTasks::populateData($tasks);
//Find all internal milestones for these tasks
$internalMilestones = ProjectMilestones::getProjectMilestones(active_or_personal_project(), null, 'DESC', "", null, null, null, $status == 0, false);
ProjectMilestones::populateData($internalMilestones);
//Find all external milestones for these tasks
$milestone_ids = array();
if ($tasks) {
foreach ($tasks as $task) {
if ($task->getMilestoneId() != 0) {
$milestone_ids[$task->getMilestoneId()] = $task->getMilestoneId();
}
}
}
$milestone_ids_condition = '';
if (count($milestone_ids) > 0) {
$milestone_ids_condition = ' OR id in (' . implode(',', $milestone_ids) . ')';
}
if ($status == 0) {
$pendingstr = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " ";
} else {
$pendingstr = "";
}
if (!$tag) {
$tagstr = "";
} else {
$tagstr = " AND (select count(*) from " . TABLE_PREFIX . "tags where " . TABLE_PREFIX . "project_milestones.id = " . TABLE_PREFIX . "tags.rel_object_id and " . TABLE_PREFIX . "tags.tag = " . DB::escape($tag) . " and " . TABLE_PREFIX . "tags.rel_object_manager ='ProjectMilestones' ) > 0 ";
}
$projectstr = " AND (" . ProjectMilestones::getWorkspaceString($pids) . $milestone_ids_condition . ")";
$archivedstr = " AND `archived_by_id` = 0 ";
$milestone_conditions = " `is_template` = false " . $archivedstr . $projectstr . $pendingstr;
$externalMilestonesTemp = ProjectMilestones::findAll(array('conditions' => $milestone_conditions));
$externalMilestones = array();
if ($externalMilestonesTemp) {
foreach ($externalMilestonesTemp as $em) {
$found = false;
if ($internalMilestones) {
foreach ($internalMilestones as $im) {
if ($im->getId() == $em->getId()) {
$found = true;
break;
}
}
}
if (!$found) {
$externalMilestones[] = $em;
}
}
}
ProjectMilestones::populateData($externalMilestones);
//Get Users Info
if (logged_user()->isMemberOfOwnerCompany()) {
$users = Users::getAll();
$allUsers = array();
} else {
$users = logged_user()->getAssignableUsers();
$allUsers = Users::getAll();
}
//Get Companies Info
if (logged_user()->isMemberOfOwnerCompany()) {
$companies = Companies::getCompaniesWithUsers();
} else {
$companies = logged_user()->getAssignableCompanies();
}
if (!$isJson) {
if (active_project() instanceof Project) {
$task_templates = WorkspaceTemplates::getTemplatesByWorkspace(active_project()->getId());
} else {
$task_templates = array();
}
tpl_assign('project_templates', $task_templates);
tpl_assign('all_templates', COTemplates::findAll());
if (user_config_option('task_display_limit') > 0 && count($tasks) > user_config_option('task_display_limit')) {
tpl_assign('displayTooManyTasks', true);
array_pop($tasks);
}
tpl_assign('tasks', $tasks);
tpl_assign('object_subtypes', ProjectCoTypes::getObjectTypesByManager('ProjectTasks'));
tpl_assign('internalMilestones', $internalMilestones);
tpl_assign('externalMilestones', $externalMilestones);
tpl_assign('users', $users);
tpl_assign('allUsers', $allUsers);
tpl_assign('companies', $companies);
tpl_assign('userPreferences', array('filterValue' => isset($filter_value) ? $filter_value : '', 'filter' => $filter, 'status' => $status, 'showWorkspaces' => user_config_option('tasksShowWorkspaces', 1), 'showTime' => user_config_option('tasksShowTime', 0), 'showDates' => user_config_option('tasksShowDates', 0), 'showTags' => user_config_option('tasksShowTags', 0), 'showEmptyMilestones' => user_config_option('tasksShowEmptyMilestones', 0), 'groupBy' => user_config_option('tasksGroupBy', 'milestone'), 'orderBy' => user_config_option('tasksOrderBy', 'priority'), 'defaultNotifyValue' => user_config_option('can notify from quick add')));
ajx_set_no_toolbar(true);
}
}
/**
* Prepare search conditions string based on input params
*
* @param string $search_for Search string
* @param string $project_csvs Search in this project
* @return array
*/
function getSearchConditions($search_for, $project_csvs = null, $include_private = false, $object_type = '', $columns_csv = null, $user_id = 0)
{
$otSearch = '';
$columnsSearch = '';
$wsSearch = '';
$search_deep = false;
$few_chars = false;
if (!is_null($columns_csv)) {
$columnsSearch = " AND `column_name` in (" . $columns_csv . ")";
}
if ($object_type != '') {
$otSearch = " AND `rel_object_manager` = '{$object_type}'";
}
if ($project_csvs) {
$wsSearch .= " AND ";
/*if ($user_id > 0)
$wsSearch .= " (`user_id` = " . $user_id . " OR ";
else
$wsSearch .= " (";*/
if ($object_type == "ProjectFileRevisions") {
$wsSearch .= "`rel_object_id` IN (SELECT o.id FROM " . TABLE_PREFIX . "project_file_revisions o where o.file_id IN (SELECT p.`object_id` FROM `" . TABLE_PREFIX . "workspace_objects` p WHERE p.`object_manager` = 'ProjectFiles' && p.`workspace_id` IN ({$project_csvs})))";
} else {
$wsSearch .= "`rel_object_id` IN (SELECT `object_id` FROM `" . TABLE_PREFIX . "workspace_objects` WHERE `object_manager` = '{$object_type}' && `workspace_id` IN ({$project_csvs}))";
}
//$wsSearch .= ')';
} else {
$wsSearch = "";
}
//Check for trashed and other permissions
$tableName = eval("return {$object_type}::instance()->getTableName();");
$trashed = '';
if ($object_type != 'Projects' && $object_type != 'Users') {
$trashed = " and EXISTS(SELECT * FROM {$tableName} co where `rel_object_id` = id and trashed_by_id = 0 ";
$trashed .= ' AND ( ' . permissions_sql_for_listings(eval("return {$object_type}::instance();"), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
$trashed .= ')';
}
//Check workspace permissions
if ($object_type == 'Projects') {
$trashed .= " AND `rel_object_id` IN (SELECT `proj`.`id` FROM {$tableName} `proj` WHERE ";
$trashed .= ' ( ' . permissions_sql_for_listings(eval("return {$object_type}::instance();"), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`proj`') . '))';
}
// if search criteria is a mail address, remove its domain to avoid matching emails with same domain that are not from this address
/*
$pos = strpos_utf($search_for, '@');
while ($pos !== FALSE) {
$esp = strpos_utf($search_for, ' ', $pos);
if ($esp !== FALSE) $search_for = substr_utf($search_for, 0, $pos) . ' ' . substr_utf($search_for, $esp+1);
else $search_for = substr_utf($search_for, 0, $pos);
$pos = strpos_utf($search_for, '@');
}
*/
// Commented by pepe
if ($include_private) {
$privSearch = 'AND `is_private` = 0';
} else {
$privSearch = '';
}
//in case the string to be looked for contains one to three chars and therefore find no objects with a 'quick search'
if (strlen($search_for) <= config_option("min_chars_for_match")) {
$few_chars = true;
}
//in case the user does a deeper search with " or '
if (str_starts_with($search_for, '"') && str_ends_with($search_for, '"')) {
$search_deep = true;
$search_for = str_replace('"', '', $search_for);
}
if (user_config_option('search_engine', substr(Localization::instance()->getLocale(), 0, 2) == 'zh' ? 'like' : null) == 'like' || $few_chars == true) {
$search_for = str_replace("*", "%", $search_for);
if (!$search_deep) {
$search_words = explode(" ", $search_for);
$search_string = "";
foreach ($search_words as $word) {
if ($search_string) {
$search_string .= " AND ";
}
$search_string .= "`content` LIKE '%{$word}%'";
}
} else {
$search_string .= "`content` LIKE '%{$search_for}%'";
}
return DB::prepareString("{$search_string} {$privSearch} {$wsSearch} {$trashed} {$otSearch} {$columnsSearch}");
} else {
$search_words = preg_split('/[\\s\\.\\+\\-\\~]/', $search_for);
if (!$search_deep) {
$search_for = "";
foreach ($search_words as $word) {
if ($word != "" && $word[0] != "+" && $word[0] != "-") {
$search_for .= " +{$word}";
}
}
} else {
$search_for = "\"" . $search_for . "\"";
}
//return DB::prepareString("MATCH (`content`) AGAINST ('$search_for' IN BOOLEAN MODE) $privSearch $wsSearch $trashed $otSearch $columnsSearch");
//@pepe - AGAINST() fails with special characters like '@' or '/' if string not scaped
return DB::prepareString("MATCH (`content`) AGAINST ('\"" . $search_for . "\"' IN BOOLEAN MODE) {$privSearch} {$wsSearch} {$trashed} {$otSearch} {$columnsSearch}");
}
}
function getMessages($tag, $project = null, $start = null, $limit = null, $order = null, $order_dir = null, $archived = false)
{
switch ($order) {
case 'updatedOn':
$order_crit = 'updated_on';
break;
case 'createdOn':
$order_crit = 'created_on';
break;
case 'title':
$order_crit = 'title';
break;
default:
$order_crit = 'updated_on';
break;
}
if (!$order_dir) {
switch ($order) {
case 'name':
$order_dir = 'ASC';
break;
default:
$order_dir = 'DESC';
}
}
if ($project instanceof Project) {
$pids = $project->getAllSubWorkspacesQuery(!$archived);
$wsConditions = " AND " . self::getWorkspaceString($pids);
} else {
$wsConditions = "";
}
if (!isset($tag) || $tag == '' || $tag == null) {
$tagstr = "";
} else {
$tagstr = "AND (SELECT count(*) FROM `" . TABLE_PREFIX . "tags` WHERE `" . TABLE_PREFIX . "project_messages`.`id` = `" . TABLE_PREFIX . "tags`.`rel_object_id` AND `" . TABLE_PREFIX . "tags`.`tag` = " . DB::escape($tag) . " AND `" . TABLE_PREFIX . "tags`.`rel_object_manager` ='ProjectMessages' ) > 0 ";
}
$permissions = ' AND ( ' . permissions_sql_for_listings(ProjectMessages::instance(), ACCESS_LEVEL_READ, logged_user(), 'project_id') . ')';
if ($archived) {
$archived_cond = "`archived_by_id` <> 0";
} else {
$archived_cond = "`archived_by_id` = 0";
}
$conditions = "`trashed_by_id` = 0 AND {$archived_cond} {$wsConditions} {$tagstr} {$permissions}";
$page = (int) ($start / $limit) + 1;
$order = "{$order_crit} {$order_dir}";
return self::paginate(array('conditions' => $conditions, 'order' => $order), $limit, $page);
}
/**
* Returns true if there is messages in this milestone
*
* @access public
* @param void
* @return boolean
*/
function hasMessages()
{
return (bool) ProjectMessages::count('`milestone_id` = ' . DB::escape($this->getId()) . " AND `trashed_on` = " . DB::escape(EMPTY_DATETIME) . " AND " . permissions_sql_for_listings(new ProjectMessages(), ACCESS_LEVEL_READ, logged_user()));
}
/**
* Returns array of queries that will return Dashboard Objects
*
* @param string $proj_ids
* @param string $tag
* @param boolean $count if false the query will return objects, if true it will return object count
*/
static function getDashboardObjectQueries($project = null, $tag = null, $count = false, $trashed = false, $linkedObject = null, $order = 'updatedOn', $filterName = '', $archived = false, $filterManager = '')
{
if ($trashed && $trashed !== 'all') {
$order = 'trashedOn';
} else {
if ($archived) {
$order = 'archivedOn';
}
}
switch ($order) {
case 'dateCreated':
$order_crit_companies = '`created_on`';
$order_crit_contacts = '`created_on`';
$order_crit_file_revisions = '`created_on`';
$order_crit_calendar = '`created_on`';
$order_crit_tasks = '`created_on`';
$order_crit_milestones = '`created_on`';
$order_crit_webpages = '`created_on`';
$order_crit_files = '`created_on`';
$order_crit_emails = '`received_date`';
$order_crit_comments = '`created_on`';
$order_crit_messages = '`created_on`';
$order_crit_workspaces = '`created_on`';
break;
case 'trashedOn':
$order_crit_companies = '`trashed_on`';
$order_crit_contacts = '`trashed_on`';
$order_crit_file_revisions = '`trashed_on`';
$order_crit_calendar = '`trashed_on`';
$order_crit_tasks = '`trashed_on`';
$order_crit_milestones = '`trashed_on`';
$order_crit_webpages = '`trashed_on`';
$order_crit_files = '`trashed_on`';
$order_crit_emails = '`trashed_on`';
$order_crit_comments = '`trashed_on`';
$order_crit_messages = '`trashed_on`';
$order_crit_workspaces = '`updated_on`';
break;
case 'archivedOn':
$order_crit_companies = '`archived_on`';
$order_crit_contacts = '`archived_on`';
$order_crit_file_revisions = '`updated_on`';
$order_crit_calendar = '`archived_on`';
$order_crit_tasks = '`archived_on`';
$order_crit_milestones = '`archived_on`';
$order_crit_webpages = '`archived_on`';
$order_crit_files = '`archived_on`';
$order_crit_emails = '`archived_on`';
$order_crit_comments = '`updated_on`';
$order_crit_messages = '`archived_on`';
$order_crit_workspaces = '`completed_on`';
break;
case 'name':
$order_crit_companies = '`name`';
$order_crit_contacts = "TRIM(CONCAT(' ', `lastname`, `firstname`, `middlename`))";
$order_crit_file_revisions = "'zzzzzzzzzzzzzz'";
//Revisar
$order_crit_calendar = '`subject`';
$order_crit_tasks = '`title`';
$order_crit_milestones = '`name`';
$order_crit_webpages = '`title`';
$order_crit_files = '`filename`';
$order_crit_emails = '`subject`';
$order_crit_comments = '`text`';
$order_crit_messages = '`title`';
$order_crit_workspaces = '`name`';
break;
default:
$order_crit_companies = '`updated_on`';
$order_crit_contacts = '`updated_on`';
$order_crit_file_revisions = '`updated_on`';
$order_crit_calendar = '`updated_on`';
$order_crit_tasks = '`updated_on`';
$order_crit_milestones = '`updated_on`';
$order_crit_webpages = '`updated_on`';
$order_crit_files = '`updated_on`';
$order_crit_emails = '`received_date`';
$order_crit_comments = '`updated_on`';
$order_crit_messages = '`updated_on`';
$order_crit_workspaces = '`updated_on`';
break;
}
if ($project instanceof Project) {
$proj_ids = $project->getAllSubWorkspacesQuery(true);
$proj_cond_companies = Companies::getWorkspaceString($proj_ids);
$proj_cond_messages = ProjectMessages::getWorkspaceString($proj_ids);
$proj_cond_documents = ProjectFiles::getWorkspaceString($proj_ids);
$proj_cond_emails = MailContents::getWorkspaceString($proj_ids);
$proj_cond_events = ProjectEvents::getWorkspaceString($proj_ids);
$proj_cond_tasks = ProjectTasks::getWorkspaceString($proj_ids);
$proj_cond_charts = ProjectCharts::getWorkspaceString($proj_ids);
$proj_cond_milestones = ProjectMilestones::getWorkspaceString($proj_ids);
$proj_cond_weblinks = ProjectWebpages::getWorkspaceString($proj_ids);
$proj_cond_contacts = Contacts::getWorkspaceString($proj_ids);
} else {
$proj_cond_companies = "true";
$proj_cond_messages = "true";
$proj_cond_documents = "true";
$proj_cond_emails = "true";
$proj_cond_events = "true";
$proj_cond_tasks = "true";
$proj_cond_charts = "true";
$proj_cond_milestones = "true";
$proj_cond_weblinks = "true";
$proj_cond_contacts = "true";
}
if ($trashed) {
if ($trashed === 'all') {
$trashed_cond = '`trashed_on` >= ' . DB::escape(EMPTY_DATETIME);
} else {
$trashed_cond = '`trashed_on` > ' . DB::escape(EMPTY_DATETIME);
}
$archived_cond = '1 = 1';
// Show all objects in trash
$comments_arch_cond = "1 = 1";
} else {
$trashed_cond = '`trashed_on` = ' . DB::escape(EMPTY_DATETIME);
if ($archived) {
$archived_cond = "`archived_by_id` > 0";
$comments_arch_cond = "1 = 0";
// Don't show comments in archived objects listings
} else {
$archived_cond = "`archived_by_id` = 0";
$comments_arch_cond = "1 = 1";
}
}
if (isset($tag) && $tag && $tag != '') {
$tag_str = " AND EXISTS (SELECT * FROM `" . TABLE_PREFIX . "tags` `t` WHERE `tag`= " . DB::escape($tag) . " AND `co`.`id` = `t`.`rel_object_id` AND `t`.`rel_object_manager` = `object_manager_value`) ";
} else {
$tag_str = ' ';
}
if ($linkedObject instanceof ProjectDataObject) {
$link_id = $linkedObject->getId();
$link_mgr = get_class($linkedObject->manager());
$link_str = " AND EXISTS (SELECT * FROM `" . TABLE_PREFIX . "linked_objects` `t` WHERE\n\t\t\t(`t`.`object_id`=" . DB::escape($link_id) . " AND `t`.object_manager = " . DB::escape($link_mgr) . " AND `co`.`id` = `t`.`rel_object_id` AND `t`.`rel_object_manager` = `object_manager_value`) OR\n\t\t\t(`t`.`rel_object_id`=" . DB::escape($link_id) . " AND `t`.rel_object_manager = " . DB::escape($link_mgr) . " AND `co`.`id` = `t`.`object_id` AND `t`.`object_manager` = `object_manager_value`)) ";
} else {
$link_str = ' ';
}
$tag_str .= $link_str;
$res = array();
/** If the name of the query ends with Comments it is assumed to be a list of Comments **/
$cfn = '';
if ($filterName != '') {
$cfn = " AND text LIKE '%" . $filterName . "%'";
}
// Notes
if (module_enabled('notes')) {
$fn = '';
if ($filterName != '') {
$fn = " AND title LIKE '%" . $filterName . "%'";
}
$permissions = ' AND ( ' . permissions_sql_for_listings(ProjectMessages::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
if ($filterManager == '' || $filterManager == "ProjectMessages") {
$res['ProjectMessages'] = "SELECT 'ProjectMessages' AS `object_manager_value`, `id` AS `oid`, {$order_crit_messages} AS `order_value` FROM `" . TABLE_PREFIX . "project_messages` `co` WHERE " . $trashed_cond . " AND {$archived_cond} AND " . $proj_cond_messages . str_replace('= `object_manager_value`', "= 'ProjectMessages'", $tag_str) . $permissions . $fn;
}
if ($filterManager == '' || $filterManager == "Comments") {
$res['ProjectMessagesComments'] = "SELECT 'Comments' AS `object_manager_value`, `id` AS `oid`, {$order_crit_comments} AS `order_value` FROM `" . TABLE_PREFIX . "comments` WHERE {$trashed_cond} AND `rel_object_manager` = 'ProjectMessages' AND `rel_object_id` IN (SELECT `co`.`id` FROM `" . TABLE_PREFIX . "project_messages` `co` WHERE `trashed_by_id` = 0 AND {$comments_arch_cond} AND " . $proj_cond_messages . str_replace('= `object_manager_value`', "= 'ProjectMessages'", $tag_str) . $permissions . $cfn . ")";
}
}
// Events
if (module_enabled("calendar")) {
$fn = '';
if ($filterName != '') {
$fn = " AND subject LIKE '%" . $filterName . "%'";
}
$permissions = ' AND ( ' . permissions_sql_for_listings(ProjectEvents::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
if ($filterManager == '' || $filterManager == "ProjectEvents") {
$res['ProjectEvents'] = "SELECT 'ProjectEvents' AS `object_manager_value`, `id` AS `oid`, {$order_crit_calendar} AS `order_value` FROM `" . TABLE_PREFIX . "project_events` `co` WHERE " . $trashed_cond . " AND {$archived_cond} AND " . $proj_cond_events . str_replace('= `object_manager_value`', "= 'ProjectEvents'", $tag_str) . $permissions . $fn;
}
if ($filterManager == '' || $filterManager == "Comments") {
$res['ProjectEventsComments'] = "SELECT 'Comments' AS `object_manager_value`, `id` AS `oid`, {$order_crit_comments} AS `order_value` FROM `" . TABLE_PREFIX . "comments` WHERE {$trashed_cond} AND `rel_object_manager` = 'ProjectEvents' AND `rel_object_id` IN (SELECT `co`.`id` FROM `" . TABLE_PREFIX . "project_events` `co` WHERE `trashed_by_id` = 0 AND {$comments_arch_cond} AND " . $proj_cond_events . str_replace('= `object_manager_value`', "= 'ProjectEvents'", $tag_str) . $permissions . $cfn . ")";
}
}
// Documents
if (module_enabled("documents")) {
$fn = '';
if ($filterName != '') {
$fn = " AND filename LIKE '%" . $filterName . "%'";
}
$permissions = ' AND ( ' . permissions_sql_for_listings(ProjectFiles::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
$typestring = array_var($_GET, "typestring");
if ($typestring) {
$typecond = " AND ((SELECT count(*) FROM `" . TABLE_PREFIX . "project_file_revisions` `pfr` WHERE `" . "pfr`.`type_string` LIKE " . DB::escape($typestring) . " AND `" . "co`.`id` = `pfr`.`file_id`) > 0)";
} else {
$typecond = "";
}
if ($filterManager == '' || $filterManager == "ProjectFiles") {
$res['ProjectFiles'] = "SELECT 'ProjectFiles' AS `object_manager_value`, `id` as `oid`, {$order_crit_files} AS `order_value` FROM `" . TABLE_PREFIX . "project_files` `co` WHERE " . $trashed_cond . " AND {$archived_cond} AND " . $proj_cond_documents . str_replace('= `object_manager_value`', "= 'ProjectFiles'", $tag_str) . $permissions . $typecond . $fn;
}
if ($filterManager == '' || $filterManager == "Comments") {
$res['ProjectFilesComments'] = "SELECT 'Comments' AS `object_manager_value`, `id` AS `oid`, {$order_crit_comments} AS `order_value` FROM `" . TABLE_PREFIX . "comments` WHERE {$trashed_cond} AND `rel_object_manager` = 'ProjectFiles' AND `rel_object_id` IN (SELECT `co`.`id` FROM `" . TABLE_PREFIX . "project_files` `co` WHERE `trashed_by_id` = 0 AND {$comments_arch_cond} AND " . $proj_cond_documents . str_replace('= `object_manager_value`', "= 'ProjectFiles'", $tag_str) . $permissions . $cfn . ")";
}
if ($trashed) {
$file_rev_docs = "SELECT `id` FROM `" . TABLE_PREFIX . "project_files` `co` WHERE `trashed_by_id` = 0 AND " . $proj_cond_documents . str_replace('= `object_manager_value`', "= 'ProjectFiles'", $tag_str) . $permissions . $typecond;
$res['FileRevisions'] = "SELECT 'ProjectFileRevisions' AS `object_manager_value`, `id` AS `oid`, {$order_crit_file_revisions} AS `order_value` FROM `" . TABLE_PREFIX . "project_file_revisions` `co` WHERE {$trashed_cond} AND `file_id` IN (" . $file_rev_docs . ")";
}
}
// Tasks and Milestones
if (module_enabled("tasks")) {
$fn = '';
if ($filterName != '') {
$fn = " AND title LIKE '%" . $filterName . "%'";
}
$completed = $trashed || $archived ? '' : 'AND `completed_on` = ' . DB::escape(EMPTY_DATETIME);
$permissions = ' AND ( ' . permissions_sql_for_listings(ProjectTasks::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
if ($filterManager == '' || $filterManager == "ProjectTasks") {
$res['ProjectTasks'] = "SELECT 'ProjectTasks' AS `object_manager_value`, `id` AS `oid`, {$order_crit_tasks} AS `order_value` FROM `" . TABLE_PREFIX . "project_tasks` `co` WHERE `is_template` = false {$completed} AND " . $trashed_cond . " AND {$archived_cond} AND `is_template` = false AND " . $proj_cond_tasks . str_replace('= `object_manager_value`', "= 'ProjectTasks'", $tag_str) . $permissions . $fn;
}
if ($filterManager == '' || $filterManager == "Comments") {
$res['ProjectTasksComments'] = "SELECT 'Comments' AS `object_manager_value`, `id` AS `oid`, {$order_crit_comments} AS `order_value` FROM `" . TABLE_PREFIX . "comments` WHERE {$trashed_cond} AND `rel_object_manager` = 'ProjectTasks' AND `rel_object_id` IN (SELECT `co`.`id` FROM `" . TABLE_PREFIX . "project_tasks` `co` WHERE `trashed_by_id` = 0 AND {$comments_arch_cond} AND `is_template` = false AND " . $proj_cond_tasks . str_replace('= `object_manager_value`', "= 'ProjectTasks'", $tag_str) . $permissions . $cfn . ")";
}
$fn = '';
if ($filterName != '') {
$fn = " AND name LIKE '%" . $filterName . "%'";
}
$permissions = ' AND ( ' . permissions_sql_for_listings(ProjectMilestones::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
if ($filterManager == '' || $filterManager == "ProjectMilestones") {
$res['ProjectMilestones'] = "SELECT 'ProjectMilestones' AS `object_manager_value`, `id` AS `oid`, {$order_crit_milestones} AS `order_value` FROM `" . TABLE_PREFIX . "project_milestones` `co` WHERE " . $trashed_cond . " AND {$archived_cond} AND `is_template` = false AND " . $proj_cond_milestones . str_replace('= `object_manager_value`', "= 'ProjectMilestones'", $tag_str) . $permissions . $fn;
}
if ($filterManager == '' || $filterManager == "Comments") {
$res['ProjectMilestonesComments'] = "SELECT 'Comments' AS `object_manager_value`, `id` AS `oid`, {$order_crit_comments} AS `order_value` FROM `" . TABLE_PREFIX . "comments` WHERE {$trashed_cond} AND `rel_object_manager` = 'ProjectMilestones' AND `rel_object_id` IN (SELECT `co`.`id` FROM `" . TABLE_PREFIX . "project_milestones` `co` WHERE `trashed_by_id` = 0 AND {$comments_arch_cond} AND `is_template` = false AND " . $proj_cond_milestones . str_replace('= `object_manager_value`', "= 'ProjectMilestones'", $tag_str) . $permissions . $cfn . ")";
}
}
// Weblinks
if (module_enabled("weblinks")) {
$fn = '';
if ($filterName != '') {
$fn = " AND title LIKE '%" . $filterName . "%'";
}
$permissions = ' AND ( ' . permissions_sql_for_listings(ProjectWebpages::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
if ($filterManager == '' || $filterManager == "ProjectWebpages") {
$res['ProjectWebPages'] = "SELECT 'ProjectWebPages' AS `object_manager_value`, `id` AS `oid`, {$order_crit_webpages} AS `order_value` FROM `" . TABLE_PREFIX . "project_webpages` `co` WHERE " . $trashed_cond . " AND {$archived_cond} AND " . $proj_cond_weblinks . str_replace('= `object_manager_value`', "= 'ProjectWebpages'", $tag_str) . $permissions . $fn;
}
if ($filterManager == '' || $filterManager == "Comments") {
$res['ProjectWebPagesComments'] = "SELECT 'Comments' AS `object_manager_value`, `id` AS `oid`, {$order_crit_comments} AS `order_value` FROM `" . TABLE_PREFIX . "comments` WHERE {$trashed_cond} AND `rel_object_manager` = 'ProjectWebpages' AND `rel_object_id` IN (SELECT `co`.`id` FROM `" . TABLE_PREFIX . "project_webpages` `co` WHERE " . $trashed_cond . " AND {$comments_arch_cond} AND " . $proj_cond_weblinks . str_replace('= `object_manager_value`', "= 'ProjectWebpages'", $tag_str) . $permissions . $cfn . ")";
}
}
// Email
if (module_enabled("email")) {
$fn = '';
if ($filterName != '') {
$fn = " AND subject LIKE '%" . $filterName . "%'";
}
$permissions = ' AND ( ' . permissions_sql_for_listings(MailContents::instance(), ACCESS_LEVEL_READ, logged_user(), $project instanceof Project ? $project->getId() : 0, '`co`') . ')';
if ($filterManager == '' || $filterManager == "MailContents") {
$res['MailContents'] = "SELECT 'MailContents' AS `object_manager_value`, `id` AS `oid`, {$order_crit_emails} AS `order_value` FROM `" . TABLE_PREFIX . "mail_contents` `co` WHERE (" . $trashed_cond . " AND {$archived_cond} AND `is_deleted` = 0 AND " . $proj_cond_emails . str_replace('= `object_manager_value`', "= 'MailContents'", $tag_str) . $permissions . ") {$fn}";
}
if ($filterManager == '' || $filterManager == "Comments") {
$res['MailContentsComments'] = "SELECT 'Comments' AS `object_manager_value`, `id` AS `oid`, {$order_crit_comments} AS `order_value` FROM `" . TABLE_PREFIX . "comments` WHERE {$trashed_cond} AND `rel_object_manager` = 'MailContents' AND `rel_object_id` IN (SELECT `co`.`id` FROM `" . TABLE_PREFIX . "mail_contents` `co` WHERE `trashed_by_id` = 0 AND {$comments_arch_cond} AND " . $proj_cond_emails . str_replace('= `object_manager_value`', "= 'MailContents'", $tag_str) . $permissions . $cfn . ")";
}
}
// Conacts and Companies
if (module_enabled("contacts")) {
$fn = '';
$fn2 = '';
if ($filterName != '') {
$fn = " AND firstname LIKE '%" . $filterName . "%'";
$fn2 = " AND name LIKE '%" . $filterName . "%'";
}
// companies
$permissions = ' AND ( ' . permissions_sql_for_listings(Companies::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
if ($filterManager == '' || $filterManager == "Companies") {
$res['Companies'] = "SELECT 'Companies' AS `object_manager_value`, `id` as `oid`, {$order_crit_companies} AS `order_value` FROM `" . TABLE_PREFIX . "companies` `co` WHERE " . $trashed_cond . " AND {$archived_cond} AND " . $proj_cond_companies . str_replace('= `object_manager_value`', "= 'Companies'", $tag_str) . $permissions . $fn2;
}
$res['CompaniesComments'] = "SELECT 'Comments' AS `object_manager_value`, `id` AS `oid`, {$order_crit_comments} AS `order_value` FROM `" . TABLE_PREFIX . "comments` WHERE {$trashed_cond} AND `rel_object_manager` = 'Companies' AND `rel_object_id` IN (SELECT `co`.`id` FROM `" . TABLE_PREFIX . "companies` `co` WHERE `trashed_by_id` = 0 AND {$comments_arch_cond} AND " . $proj_cond_documents . str_replace('= `object_manager_value`', "= 'Companies'", $tag_str) . $permissions . $cfn . ")";
// contacts
$permissions = ' AND ( ' . permissions_sql_for_listings(Contacts::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
if ($filterManager == '' || $filterManager == "Contacts") {
$res['Contacts'] = "SELECT 'Contacts' AS `object_manager_value`, `id` AS `oid`, {$order_crit_contacts} AS `order_value` FROM `" . TABLE_PREFIX . "contacts` `co` WHERE {$trashed_cond} AND {$archived_cond} AND {$proj_cond_contacts} " . str_replace('= `object_manager_value`', "= 'Contacts'", $tag_str) . $permissions . $fn;
}
$res['ContactsComments'] = "SELECT 'Comments' AS `object_manager_value`, `id` AS `oid`, {$order_crit_comments} AS `order_value` FROM `" . TABLE_PREFIX . "comments` WHERE {$trashed_cond} AND `rel_object_manager` = 'Contacts' AND `rel_object_id` IN (SELECT `co`.`id` FROM `" . TABLE_PREFIX . "contacts` `co` WHERE `trashed_by_id` = 0 AND {$comments_arch_cond} AND " . $proj_cond_documents . str_replace('= `object_manager_value`', "= 'Contacts'", $tag_str) . $permissions . $cfn . ")";
}
// Workspaces (only for archived objects view)
if ($archived) {
if ($filterManager == '' || $filterManager == "Projects") {
$res['Projects'] = "SELECT 'Projects' AS `object_manager_value`, `id` AS `oid`, {$order_crit_workspaces} AS `order_value` FROM `" . TABLE_PREFIX . "projects` `co` WHERE `completed_on` <> " . DB::escape(EMPTY_DATETIME) . " AND `id` IN (" . logged_user()->getWorkspacesQuery() . ")";
}
}
if ($count) {
foreach ($res as $p => $q) {
$res[$p] = "SELECT count(*) AS `quantity`, '{$p}' AS `objectName` FROM ( {$q} ) `table_alias`";
}
}
return $res;
}
static function getProjectMilestones($project = null, $order = null, $orderdir = 'DESC', $tag = null, $assigned_to_company = null, $assigned_to_user = null, $assigned_by_user = null, $pending = false, $is_template = false, $archived = false)
{
// default
$order_by = '`due_date` ASC';
if ($project instanceof Project) {
$pids = $project->getAllSubWorkspacesQuery(!$archived);
$projectstr = " AND " . self::getWorkspaceString($pids);
} else {
$projectstr = "";
}
if ($tag == '' || $tag == null) {
$tagstr = "";
} else {
$tagstr = " AND (select count(*) from " . TABLE_PREFIX . "tags where " . TABLE_PREFIX . "project_milestones.id = " . TABLE_PREFIX . "tags.rel_object_id and " . TABLE_PREFIX . "tags.tag = " . DB::escape($tag) . " and " . TABLE_PREFIX . "tags.rel_object_manager ='ProjectMilestones' ) > 0 ";
}
$assignedToStr = "";
if ($assigned_to_company) {
$assignedToStr .= " AND `assigned_to_company_id` = " . DB::escape($assigned_to_company) . " ";
}
if ($assigned_to_user) {
$assignedToStr .= " AND `assigned_to_user_id` = " . DB::escape($assigned_to_user) . " ";
}
$assignedByStr = "";
if ($assigned_by_user) {
$assignedByStr .= " AND (`created_by_id` = " . DB::escape($assigned_by_user) . " OR `updated_by_id` = " . DB::escape($assigned_by_user) . ") ";
}
if ($pending) {
$pendingstr = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " ";
} else {
$pendingstr = "";
}
if ($pending) {
$pendingstr = " AND `completed_on` = " . DB::escape(EMPTY_DATETIME) . " ";
} else {
$pendingstr = "";
}
if ($archived) {
$archived_cond = " AND `archived_by_id` <> 0";
} else {
$archived_cond = " AND `archived_by_id` = 0";
}
$permissionstr = ' AND ( ' . permissions_sql_for_listings(ProjectMilestones::instance(), ACCESS_LEVEL_READ, logged_user()) . ') ';
$otherConditions = $projectstr . $tagstr . $assignedToStr . $assignedByStr . $permissionstr . $pendingstr . $archived_cond;
$conditions = array(' `is_template` = ' . DB::escape($is_template) . $otherConditions);
$milestones = ProjectMilestones::find(array('conditions' => $conditions, 'order' => $order_by));
if (!is_array($milestones)) {
$milestones = array();
}
return $milestones;
}
/**
* Gets project files that satisfy condition and that the user can read
*
* @param unknown_type $condition
*/
function getUserFiles($user = null, $workspace = null, $tag = null, $type_string = null, $order = null, $orderdir = 'ASC', $offset = 0, $limit = 0, $include_sub_workspaces = true, $archived = false)
{
if (!$user instanceof User) {
$user = logged_user();
}
if ($workspace instanceof Project) {
if ($include_sub_workspaces) {
$wsids = $workspace->getAllSubWorkspacesQuery(!$archived);
} else {
$wsids = "" . $workspace->getId();
}
$wscond = " AND " . self::getWorkspaceString($wsids);
} else {
$wscond = "";
}
if ($tag == '' || $tag == null) {
$tagcond = "";
} else {
$tagcond = " AND (SELECT count(*) FROM `" . TABLE_PREFIX . "tags` WHERE `" . TABLE_PREFIX . "project_files`.`id` = `" . TABLE_PREFIX . "tags`.`rel_object_id` AND `" . TABLE_PREFIX . "tags`.`tag` = " . DB::escape($tag) . " AND `" . TABLE_PREFIX . "tags`.`rel_object_manager` ='ProjectFiles' ) > 0 ";
}
if ($type_string == '' || $type_string == null) {
$typecond = "";
} else {
$types = explode(',', $type_string);
$typessql = '(';
$cant = count($types);
$n = 0;
foreach ($types as $type) {
$type .= '%';
$typessql .= ' ' . TABLE_PREFIX . "project_file_revisions.type_string LIKE " . DB::escape($type);
$n++;
$n != $cant ? $typessql .= ' OR ' : ($typessql .= ' )');
}
$typecond = " AND (SELECT count(*) FROM " . TABLE_PREFIX . "project_file_revisions WHERE " . $typessql . " AND " . TABLE_PREFIX . "project_files.id = " . TABLE_PREFIX . "project_file_revisions.file_id)";
}
$permissions = ' AND ( ' . permissions_sql_for_listings(ProjectFiles::instance(), ACCESS_LEVEL_READ, $user) . ') ';
if ($archived) {
$archived_cond = " `archived_by_id` <> 0";
} else {
$archived_cond = " `archived_by_id` = 0";
}
$conditions = $archived_cond . $wscond . $tagcond . $typecond . $permissions;
if ($order == self::ORDER_BY_POSTTIME) {
$order_by = '`created_on` ' . $orderdir;
} else {
if ($order == self::ORDER_BY_MODIFYTIME) {
$order_by = '`updated_on` ' . $orderdir;
} else {
$order_by = '`filename`' . $orderdir;
}
}
return self::findAll(array('conditions' => $conditions, 'order' => $order_by, 'offset' => $offset, 'limit' => $limit));
}
/**
* Return Day tasks this user have access on
*
* @access public
* @param void
* @return array
*/
function getRangeTasksByUser(DateTimeValue $date_start, DateTimeValue $date_end, $assignedUser, $tags = '', $project = null, $archived = false)
{
$from_date = new DateTimeValue($date_start->getTimestamp());
$from_date = $from_date->beginningOfDay();
$to_date = new DateTimeValue($date_end->getTimestamp());
$to_date = $to_date->endOfDay();
$permissions = ' AND ( ' . permissions_sql_for_listings(ProjectTasks::instance(), ACCESS_LEVEL_READ, logged_user(), 'project_id') . ')';
if ($project instanceof Project) {
$pids = $project->getAllSubWorkspacesQuery(!$archived);
$wsstring = " AND " . self::getWorkspaceString($pids);
} else {
$wsstring = "";
}
if (isset($tags) && $tags && $tags != '') {
$tag_str = " AND exists (SELECT * from " . TABLE_PREFIX . "tags t WHERE tag=" . DB::escape($tags) . " AND " . TABLE_PREFIX . "project_tasks.id=t.rel_object_id AND t.rel_object_manager='ProjectTasks') ";
} else {
$tag_str = "";
}
$assignedFilter = '';
if ($assignedUser instanceof User) {
$assignedFilter = ' AND (`assigned_to_user_id` = ' . $assignedUser->getId() . ' OR (`assigned_to_user_id` = 0 AND `assigned_to_company_id` = ' . $assignedUser->getCompanyId() . ')) ';
}
$rep_condition = " (`repeat_forever` = 1 OR `repeat_num` > 0 OR (`repeat_end` > 0 AND `repeat_end` >= '" . $from_date->toMySQL() . "')) ";
if ($archived) {
$archived_cond = " AND `archived_by_id` <> 0";
} else {
$archived_cond = " AND `archived_by_id` = 0";
}
$result = self::findAll(array('conditions' => array('`is_template` = false AND `completed_on` = ? AND ((`due_date` >= ? AND `due_date` < ?) OR (`start_date` >= ? AND `start_date` < ?) OR ' . $rep_condition . ') ' . $archived_cond . $assignedFilter . $permissions . $wsstring . $tag_str, EMPTY_DATETIME, $from_date, $to_date, $from_date, $to_date)));
// findAll
return $result;
}
/**
* Execute a report and return results
*
* @param $id
* @param $params
*
* @return array
*/
static function executeReport($id, $params, $order_by_col = '', $order_by_asc = true, $offset = 0, $limit = 50, $to_print = false)
{
$results = array();
$report = self::getReport($id);
if ($report instanceof Report) {
$conditionsFields = ReportConditions::getAllReportConditionsForFields($id);
$conditionsCp = ReportConditions::getAllReportConditionsForCustomProperties($id);
$table = '';
$object = null;
$controller = '';
$view = '';
eval('$managerInstance = ' . $report->getObjectType() . "::instance();");
if ($report->getObjectType() == 'Companies') {
$table = 'companies';
$controller = 'company';
$view = 'card';
$object = new Company();
} else {
if ($report->getObjectType() == 'Contacts') {
$table = 'contacts';
$controller = 'contact';
$view = 'card';
$object = new Contact();
} else {
if ($report->getObjectType() == 'MailContents') {
$table = 'mail_contents';
$controller = 'mail';
$view = 'view';
$object = new MailContent();
} else {
if ($report->getObjectType() == 'ProjectEvents') {
$table = 'project_events';
$controller = 'event';
$view = 'viewevent';
$object = new ProjectEvent();
} else {
if ($report->getObjectType() == 'ProjectFiles') {
$table = 'project_files';
$controller = 'files';
$view = 'file_details';
$object = new ProjectFile();
} else {
if ($report->getObjectType() == 'ProjectMilestones') {
$table = 'project_milestones';
$controller = 'milestone';
$view = 'view';
$object = new ProjectMilestone();
} else {
if ($report->getObjectType() == 'ProjectMessages') {
$table = 'project_messages';
$controller = 'message';
$view = 'view';
$object = new ProjectMessage();
} else {
if ($report->getObjectType() == 'ProjectTasks') {
$table = 'project_tasks';
$controller = 'task';
$view = 'view_task';
$object = new ProjectTask();
} else {
if ($report->getObjectType() == 'Users') {
$table = 'users';
$controller = 'user';
$view = 'card';
$object = new User();
} else {
if ($report->getObjectType() == 'ProjectWebpages') {
$table = 'project_webpages';
$controller = 'webpage';
$view = 'view';
$object = new ProjectWebpage();
} else {
if ($report->getObjectType() == 'Projects') {
$table = 'projects';
$controller = 'project';
$view = '';
$object = new Project();
}
}
}
}
}
}
}
}
}
}
}
$order_by = '';
if (is_object($params)) {
$params = get_object_vars($params);
}
$sql = 'SELECT id FROM ' . TABLE_PREFIX . $table . ' t WHERE ';
$manager = $report->getObjectType();
$allConditions = permissions_sql_for_listings(new $manager(), ACCESS_LEVEL_READ, logged_user(), 'project_id', 't');
if (count($conditionsFields) > 0) {
foreach ($conditionsFields as $condField) {
if ($condField->getFieldName() == 'workspace' || $condField->getFieldName() == 'tag') {
//if has a tag or workspace condition
if ($condField->getFieldName() == 'workspace') {
//if is a workspace condition:
$fiterUsingWorkspace = true;
if ($condField->getIsParametrizable() && isset($params['workspace'])) {
//if is parameter condition and is set the parameter
$ws_value = $params['workspace'];
} else {
//if is a fixed workspace value and is set
$val = $condField->getValue();
if (isset($val)) {
$ws_value = $val;
} else {
//if there is no workspace to filter with it doesnt filter at all.
$fiterUsingWorkspace = false;
}
}
$wsCondition = $condField->getCondition();
if ($fiterUsingWorkspace && $ws_value != 0) {
$parentWS = Projects::findById($ws_value);
if ($parentWS instanceof Project) {
$subWorkspaces = $parentWS->getSubWorkspaces();
foreach ($subWorkspaces as $subWS) {
$ws_value .= ',' . $subWS->getId();
}
}
$allConditions .= ' AND t.id ' . ($wsCondition == '=' ? 'IN' : 'NOT IN') . ' (SELECT object_id FROM ' . TABLE_PREFIX . 'workspace_objects WHERE object_manager = \'' . $manager . '\' AND workspace_id IN ( ' . $ws_value . '))';
}
}
if ($condField->getFieldName() == 'tag') {
//if is a tag condition:
$fiterUsingTag = true;
if ($condField->getIsParametrizable() && isset($params['tag'])) {
//if is parameter condition and is set the parameter
$tags_csv = $params['tag'];
$tags = explode(',', $tags_csv);
} else {
//if is a fixed tag value and is set
$tval = $condField->getValue();
if (isset($tval)) {
$tags = explode(',', $tval);
} else {
//if there is no tag to filter with it doesnt filter at all.
$fiterUsingTag = false;
}
}
$tagCondition = $condField->getCondition();
if ($fiterUsingTag && is_array($tags)) {
foreach ($tags as $tag_value) {
$tag_value = trim($tag_value);
if ($tag_value == '') {
continue;
}
$allConditions .= ' AND t.id ' . ($tagCondition == '=' ? 'IN' : 'NOT IN') . ' (SELECT rel_object_id FROM ' . TABLE_PREFIX . 'tags WHERE rel_object_manager = \'' . $manager . '\' AND tag = \'' . $tag_value . '\')';
}
}
}
} else {
$skip_condition = false;
$model = $report->getObjectType();
$model_instance = new $model();
$col_type = $model_instance->getColumnType($condField->getFieldName());
$allConditions .= ' AND ';
$dateFormat = 'm/d/Y';
if (isset($params[$condField->getId()])) {
$value = $params[$condField->getId()];
if ($col_type == DATA_TYPE_DATE || $col_type == DATA_TYPE_DATETIME) {
$dateFormat = user_config_option('date_format');
}
} else {
$value = $condField->getValue();
}
if ($value == '' && $condField->getIsParametrizable()) {
$skip_condition = true;
}
if (!$skip_condition) {
if ($condField->getCondition() == 'like' || $condField->getCondition() == 'not like') {
$value = '%' . $value . '%';
}
if ($col_type == DATA_TYPE_DATE || $col_type == DATA_TYPE_DATETIME) {
$dtValue = DateTimeValueLib::dateFromFormatAndString($dateFormat, $value);
$value = $dtValue->format('Y-m-d');
}
if ($condField->getCondition() != '%') {
if ($col_type == DATA_TYPE_INTEGER || $col_type == DATA_TYPE_FLOAT) {
$allConditions .= '`' . $condField->getFieldName() . '` ' . $condField->getCondition() . ' ' . mysql_real_escape_string($value);
} else {
if ($condField->getCondition() == '=' || $condField->getCondition() == '<=' || $condField->getCondition() == '>=') {
$equal = 'datediff(\'' . mysql_real_escape_string($value) . '\', `' . $condField->getFieldName() . '`)=0';
switch ($condField->getCondition()) {
case '=':
$allConditions .= $equal;
break;
case '<=':
case '>=':
$allConditions .= '(`' . $condField->getFieldName() . '` ' . $condField->getCondition() . ' \'' . mysql_real_escape_string($value) . '\'' . ' OR ' . $equal . ') ';
break;
}
} else {
$allConditions .= '`' . $condField->getFieldName() . '` ' . $condField->getCondition() . ' \'' . mysql_real_escape_string($value) . '\'';
}
}
} else {
$allConditions .= '`' . $condField->getFieldName() . '` like "%' . mysql_real_escape_string($value) . '"';
}
} else {
$allConditions .= ' true';
}
}
//else
}
//foreach
}
if (count($conditionsCp) > 0) {
foreach ($conditionsCp as $condCp) {
$cp = CustomProperties::getCustomProperty($condCp->getCustomPropertyId());
$skip_condition = false;
$dateFormat = 'm/d/Y';
if (isset($params[$condCp->getId() . "_" . $cp->getName()])) {
$value = $params[$condCp->getId() . "_" . $cp->getName()];
if ($cp->getType() == 'date') {
$dateFormat = user_config_option('date_format');
}
} else {
$value = $condCp->getValue();
}
if ($value == '' && $condCp->getIsParametrizable()) {
$skip_condition = true;
}
if (!$skip_condition) {
$allConditions .= ' AND ';
$allConditions .= 't.id IN ( SELECT object_id as id FROM ' . TABLE_PREFIX . 'custom_property_values cpv WHERE ';
$allConditions .= ' cpv.custom_property_id = ' . $condCp->getCustomPropertyId();
$fieldType = $object->getColumnType($condCp->getFieldName());
if ($condCp->getCondition() == 'like' || $condCp->getCondition() == 'not like') {
$value = '%' . $value . '%';
}
if ($cp->getType() == 'date') {
$dtValue = DateTimeValueLib::dateFromFormatAndString($dateFormat, $value);
$value = $dtValue->format('Y-m-d H:i:s');
}
if ($condCp->getCondition() != '%') {
if ($cp->getType() == 'numeric') {
$allConditions .= ' AND cpv.value ' . $condCp->getCondition() . ' ' . mysql_real_escape_string($value);
} else {
$allConditions .= ' AND cpv.value ' . $condCp->getCondition() . ' "' . mysql_real_escape_string($value) . '"';
}
} else {
$allConditions .= ' AND cpv.value like "%' . mysql_real_escape_string($value) . '"';
}
$allConditions .= ')';
}
}
}
if ($manager != 'Projects' && $manager != 'Users') {
$allConditions .= ' AND t.trashed_by_id = 0 ';
}
$sql .= $allConditions;
$rows = DB::executeAll($sql);
if (is_null($rows)) {
$rows = array();
}
$totalResults = count($rows);
$results['pagination'] = Reports::getReportPagination($id, $params, $order_by_col, $order_by_asc, $offset, $limit, $totalResults);
$selectCols = 'distinct(t.id) as "id"';
$titleCols = $managerInstance->getReportObjectTitleColumns();
$titleColAlias = array();
foreach ($titleCols as $num => $title) {
$selectCols .= ', t.' . $title . ' as "titleCol' . $num . '"';
$titleColAlias['titleCol' . $num] = $title;
}
$selectFROM = TABLE_PREFIX . $table . ' t ';
$selectWHERE = "WHERE {$allConditions}";
$order = $order_by_col != '' ? $order_by_col : $report->getOrderBy();
$order_asc = $order_by_col != '' ? $order_by_asc : $report->getIsOrderByAsc();
$allColumns = ReportColumns::getAllReportColumns($id);
$print_ws_idx = -1;
$print_tags_idx = -1;
if (is_array($allColumns) && count($allColumns) > 0) {
$first = true;
$openPar = '';
$index = 0;
foreach ($allColumns as $column) {
if ($column->getCustomPropertyId() == 0) {
$field = $column->getFieldName();
if ($managerInstance->columnExists($field)) {
$selectCols .= ', t.' . $field;
$results['columns'][] = lang('field ' . $report->getObjectType() . ' ' . $field);
$results['db_columns'][lang('field ' . $report->getObjectType() . ' ' . $field)] = $field;
$first = false;
} else {
if ($field === 'workspace') {
$print_ws_idx = $index;
} else {
if ($field === 'tag') {
$print_tags_idx = $index;
}
}
}
} else {
$colCp = $column->getCustomPropertyId();
$cp = CustomProperties::getCustomProperty($colCp);
if ($cp instanceof CustomProperty) {
$selectCols .= $cp->getIsMultipleValues() ? ', GROUP_CONCAT(DISTINCT cpv' . $colCp . '.value SEPARATOR ", ") as "' . $cp->getName() . '"' : ', cpv' . $colCp . '.value as "' . $cp->getName() . '"';
$results['columns'][] = $cp->getName();
$results['db_columns'][$cp->getName()] = $colCp;
$openPar .= '(';
$selectFROM .= ' LEFT OUTER JOIN ' . TABLE_PREFIX . 'custom_property_values cpv' . $colCp . ' ON (t.id = cpv' . $colCp . '.object_id AND cpv' . $colCp . '.custom_property_id = ' . $colCp . '))';
$first = false;
if ($order == $colCp) {
if ($cp->getType() == 'date') {
$order_by = 'ORDER BY STR_TO_DATE(cpv' . $colCp . '.value, "%Y-%m-%d %H:%i:%s") ' . ($order_asc ? 'asc' : 'desc');
} else {
$order_by = 'ORDER BY cpv' . $colCp . '.value ' . ($order_asc ? 'asc' : 'desc');
}
}
}
}
$index++;
}
}
if ($order_by == '') {
if (is_numeric($order)) {
$id = $order;
$openPar .= '(';
$selectFROM .= ' LEFT OUTER JOIN ' . TABLE_PREFIX . 'custom_property_values cpv' . $id . ' ON (t.id = cpv' . $id . '.object_id AND cpv' . $id . '.custom_property_id = ' . $id . '))';
$order_by = 'ORDER BY ' . $order;
} else {
if ($object->getColumnType($order) == 'date') {
$order_by = 'ORDER BY STR_TO_DATE(t.' . $order . ', "%Y-%m-%d %H:%i:%s") ' . ($order_asc ? 'asc' : 'desc');
} else {
$order_by = 'ORDER BY t.' . $order . ' ' . ($order_asc ? 'asc' : 'desc');
}
}
}
if ($to_print) {
$limit_str = '';
} else {
$limit_str = ' LIMIT ' . $offset . ',' . $limit;
}
$sql = 'SELECT ' . $selectCols . ' FROM (' . $openPar . $selectFROM . ') ' . $selectWHERE . ' GROUP BY id ' . $order_by . $limit_str;
$rows = DB::executeAll($sql);
if (is_null($rows)) {
$rows = array();
}
$rows = Reports::removeDuplicateRows($rows);
$reportObjTitleCols = array();
foreach ($rows as &$row) {
foreach ($row as $col => $value) {
if (isset($titleColAlias[$col])) {
$reportObjTitleCols[$titleColAlias[$col]] = $value;
}
}
$title = $managerInstance->getReportObjectTitle($reportObjTitleCols);
$iconame = strtolower($managerInstance->getItemClass());
$id = $row['id'];
unset($row['id']);
$row = array_slice($row, count($titleCols));
if (!$to_print) {
$row = array('link' => '<a class="link-ico ico-' . $iconame . '" title="' . clean($title) . '" target="new" href="' . get_url($controller, $view, array('id' => $id)) . '"> </a>') + $row;
}
foreach ($row as $col => &$value) {
if (in_array($col, $managerInstance->getExternalColumns())) {
$value = self::getExternalColumnValue($col, $value);
} else {
if ($col != 'link') {
$value = html_to_text(clean($value));
}
}
if (self::isReportColumnEmail($value)) {
if (logged_user()->hasMailAccounts()) {
$value = '<a class="internalLink" href="' . get_url('mail', 'add_mail', array('to' => clean($value))) . '">' . clean($value) . '</a></div>';
} else {
$value = '<a class="internalLink" target="_self" href="mailto:' . clean($value) . '">' . clean($value) . '</a></div>';
}
}
}
if ($print_tags_idx > -1) {
$row['tag'] = implode(", ", Tags::getTagNamesByObjectIds($id, $report->getObjectType()));
}
if ($print_ws_idx > -1) {
$row['workspace'] = "";
$workspaces = WorkspaceObjects::getWorkspacesByObject($report->getObjectType(), $id, logged_user()->getWorkspacesQuery());
foreach ($workspaces as $workspace) {
$row['workspace'] .= ($row['workspace'] == "" ? "" : ", ") . $workspace->getName();
}
}
// TODO: reorder columns
$row = str_replace('|', ',', $row);
}
// TODO: reorder column titles
if ($print_tags_idx > -1) {
$results['columns'][] = lang('tags');
}
if ($print_ws_idx > -1) {
$results['columns'][] = lang('workspaces');
}
if (!$to_print) {
if (is_array($results['columns'])) {
array_unshift($results['columns'], '');
} else {
$results['columns'] = array('');
}
}
$results['rows'] = $rows;
}
return $results;
}
private static function getContactQueries($project = null, $tag = null, $count = false, $order = null, $archived = false)
{
switch ($order) {
case 'updatedOn':
$order_crit_companies = 'updated_on';
$order_crit_contacts = 'updated_on';
break;
case 'createdOn':
$order_crit_companies = 'created_on';
$order_crit_contacts = 'created_on';
break;
case 'email':
case 'email2':
case 'email3':
$order_crit_contacts = $order;
$order_crit_companies = $order == 'email' ? 'email' : "' '";
break;
default:
$order_crit_contacts = "TRIM(CONCAT(' ', `lastname`, `firstname`, `middlename`))";
$order_crit_companies = 'name';
break;
}
if ($project instanceof Project) {
$proj_ids = $project->getAllSubWorkspacesQuery(!$archived);
$proj_cond_companies = " AND " . Companies::getWorkspaceString($proj_ids);
$proj_cond_contacts = " AND " . Contacts::getWorkspaceString($proj_ids);
} else {
$proj_cond_companies = "";
$proj_cond_contacts = "";
}
if (isset($tag) && $tag && $tag != '') {
$tag_str = " AND EXISTS (SELECT * FROM `" . TABLE_PREFIX . "tags` `t` WHERE `tag` = " . DB::escape($tag) . " AND `co`.`id` = `t`.`rel_object_id` AND `t`.`rel_object_manager` = `object_manager_value`) ";
} else {
$tag_str = ' ';
}
$res = array();
if ($archived) {
$archived_cond = "AND `archived_by_id` <> 0";
} else {
$archived_cond = "AND `archived_by_id` = 0";
}
$permissions = ' AND ( ' . permissions_sql_for_listings(Companies::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
$res['Companies'] = "SELECT {$order_crit_companies} AS `order_value`, 'Companies' AS `object_manager_value`, `id` as `oid` FROM `" . TABLE_PREFIX . "companies` `co` WHERE `trashed_by_id` = 0 {$archived_cond} " . $proj_cond_companies . str_replace('= `object_manager_value`', "= 'Companies'", $tag_str) . $permissions;
$permissions = ' AND ( ' . permissions_sql_for_listings(Contacts::instance(), ACCESS_LEVEL_READ, logged_user(), '`project_id`', '`co`') . ')';
$res['Contacts'] = "SELECT {$order_crit_contacts} AS `order_value`, 'Contacts' AS `object_manager_value`, `id` AS `oid` FROM `" . TABLE_PREFIX . "contacts` `co` WHERE `trashed_by_id` = 0 {$archived_cond} {$proj_cond_contacts} " . str_replace('= `object_manager_value`', "= 'Contacts'", $tag_str) . $permissions;
if ($count) {
foreach ($res as $p => $q) {
$res[$p] = "SELECT count(*) AS `quantity`, '{$p}' AS `objectName` FROM ( {$q} ) `table_alias`";
}
}
return $res;
}
/**
* Returns an array of: (name, email)
* from companies that the logged user can access.
* @return array
*/
function getCompanyEmailAddresses()
{
$permissions = permissions_sql_for_listings(Companies::instance(), ACCESS_LEVEL_READ, logged_user());
$sql = "SELECT `name`, `email` FROM `" . TABLE_PREFIX . "companies` WHERE " . "`trashed_by_id` = 0 AND {$permissions} AND `email` <> ''";
$all = DB::executeAll($sql);
if (is_array($all)) {
return $all;
}
return array();
}
/**
* Returns an array of: (firstname, lastname, email, email2, email3)
* from contacts that the logged user can access.
* @return array
*/
function getContactEmailAddresses()
{
$permissions = permissions_sql_for_listings(Contacts::instance(), ACCESS_LEVEL_READ, logged_user());
$sql = "SELECT `firstname`, `lastname`, `email`, `email2`, `email3` FROM `" . TABLE_PREFIX . "contacts` WHERE " . "`trashed_by_id` = 0 AND {$permissions} AND (`email` <> '' OR `email2` <> '' OR `email3` <> '')";
$all = DB::executeAll($sql);
if (is_array($all)) {
return $all;
}
return array();
}
