/** * Return tasks on which the user has an open timeslot * * @param User $user * @param Project $project * @return array */ static function getOpenTimeslotTasks(User $user, User $logged_user, $project = null, $tag = null, $assigned_to_company = null, $assigned_to_user = null, $archived = false) { if ($project) { $project_ids = $project->getAllSubWorkspacesQuery(!$archived); $wsstring = " AND " . self::getWorkspaceString($project_ids); } else { $wsstring = ""; } $openTimeslot = " AND id in (SELECT object_id from " . TABLE_PREFIX . "timeslots t WHERE user_id=" . $user->getId() . " AND t.object_manager='ProjectTasks' AND t.end_time='" . EMPTY_DATETIME . "')"; $permissions = ' AND ( ' . permissions_sql_for_listings(ProjectTasks::instance(), ACCESS_LEVEL_READ, logged_user(), 'project_id') . ')'; $tagStr = $tag ? " AND id in (SELECT rel_object_id from " . TABLE_PREFIX . "tags t WHERE tag=" . DB::escape($tag) . " AND t.rel_object_manager = 'ProjectTasks')" : ''; $assignedToStr = ""; if ($assigned_to_company) { if ($assigned_to_company == -1) { $assigned_to_company = 0; } $assignedToStr .= " AND `assigned_to_company_id` = " . DB::escape($assigned_to_company) . " "; } if ($assigned_to_user) { if ($assigned_to_user == -1) { $assigned_to_user = 0; } $assignedToStr .= " AND `assigned_to_user_id` = " . DB::escape($assigned_to_user) . " "; } if ($archived) { $archived_cond = "`archived_by_id` <> 0 "; } else { $archived_cond = "`archived_by_id` = 0 "; } $objects = self::findAll(array('conditions' => array('`is_template` = false AND ' . $archived_cond . $wsstring . $permissions . $tagStr . $assignedToStr . $openTimeslot), 'order' => 'due_date ASC, `created_on` DESC')); return $objects; }
/** * Returns a list of emails according to the requested parameters * * @param string $tag * @param array $attributes * @param Project $project * @return array */ function getEmails($tag = null, $account_id = null, $state = null, $read_filter = "", $classif_filter = "", $project = null, $start = null, $limit = null, $order_by = 'received_date', $dir = 'ASC', $archived = false, $count = false) { // Check for accounts $accountConditions = ""; if (isset($account_id) && $account_id > 0) { //Single account $accountConditions = " AND `account_id` = " . DB::escape($account_id); } // Check for unclassified emails if ($classif_filter != '' && $classif_filter != 'all') { if ($classif_filter == 'unclassified') { $classified = "AND NOT "; } else { $classified = "AND "; } $classified .= "`id` IN (SELECT `object_id` FROM `" . TABLE_PREFIX . "workspace_objects` WHERE `object_manager` = 'MailContents')"; } else { $classified = ""; } // Check for drafts emails if ($state == "draft") { $stateConditions = " `state` = '2'"; } else { if ($state == "sent") { $stateConditions = " (`state` = '1' OR `state` = '3' OR `state` = '5')"; } else { if ($state == "received") { $stateConditions = " (`state` = '0' OR `state` = '5')"; } else { if ($state == "junk") { $stateConditions = " `state` = '4'"; } else { if ($state == "outbox") { $stateConditions = " `state` >= 200"; } else { $stateConditions = ""; } } } } } // Check read emails if ($read_filter != "" && $read_filter != "all") { if ($read_filter == "unread") { $read = "AND NOT "; $subread = "AND NOT `mc`."; } else { $read = "AND "; $subread = "AND `mc`."; } $read2 = "`id` IN (SELECT `rel_object_id` FROM `" . TABLE_PREFIX . "read_objects` `t` WHERE `user_id` = " . logged_user()->getId() . " AND `t`.`rel_object_manager` = 'MailContents' AND `t`.`is_read` = '1')"; $read .= $read2; $subread .= $read2; } else { $read = ""; $subread = ""; } //Check for tags if (!isset($tag) || $tag == '' || $tag == null) { $tagstr = ""; // dummy condition $subtagstr = ""; } else { $tagstr = "AND (SELECT count(*) FROM `" . TABLE_PREFIX . "tags` WHERE `" . TABLE_PREFIX . "mail_contents`.`id` = `" . TABLE_PREFIX . "tags`.`rel_object_id` AND `" . TABLE_PREFIX . "tags`.`tag` = " . DB::escape($tag) . " AND `" . TABLE_PREFIX . "tags`.`rel_object_manager` ='MailContents' ) > 0 "; $subtagstr = "AND (SELECT count(*) FROM `" . TABLE_PREFIX . "tags` WHERE " . "`mc`.`id` = `" . TABLE_PREFIX . "tags`.`rel_object_id` AND `" . TABLE_PREFIX . "tags`.`tag` = " . DB::escape($tag) . " AND `" . TABLE_PREFIX . "tags`.`rel_object_manager` ='MailContents' ) > 0 "; } $permissions = ' AND ( ' . permissions_sql_for_listings(MailContents::instance(), ACCESS_LEVEL_READ, logged_user(), $project instanceof Project ? $project->getId() : 0) . ')'; //Check for projects (uses accountConditions if ($project instanceof Project) { $pids = $project->getAllSubWorkspacesQuery(!$archived); $projectConditions = " AND " . self::getWorkspaceString($pids); } else { $projectConditions = ""; } if ($archived) { $archived_cond = "AND `archived_by_id` <> 0"; } else { $archived_cond = "AND `archived_by_id` = 0"; } $state_conv_cond_1 = $state != 'received' ? " {$stateConditions} AND " : " `state` <> '2' AND "; $state_conv_cond_2 = $state != 'received' ? " AND (`mc`.`state` = '1' OR `mc`.`state` = '3' OR `mc`.`state` = '5') " : " AND `mc`.`state` <> '2' "; if (user_config_option('show_emails_as_conversations')) { $archived_by_id = $archived ? "AND `mc`.`archived_by_id` != 0" : "AND `mc`.`archived_by_id` = 0"; $trashed_by_id = "AND `mc`.`trashed_on` = " . DB::escape(EMPTY_DATETIME); $conversation_cond = "AND IF(`conversation_id` = 0, {$stateConditions}, {$state_conv_cond_1} NOT EXISTS (SELECT * FROM `" . TABLE_PREFIX . "mail_contents` `mc` WHERE `" . TABLE_PREFIX . "mail_contents`.`conversation_id` = `mc`.`conversation_id` AND `" . TABLE_PREFIX . "mail_contents`.`account_id` = `mc`.`account_id` AND `" . TABLE_PREFIX . "mail_contents`.`received_date` < `mc`.`received_date` {$archived_by_id} AND `mc`.`is_deleted` = 0 {$trashed_by_id} {$subtagstr} {$subread} {$state_conv_cond_2}))"; $box_cond = "AND IF(EXISTS(SELECT * FROM `" . TABLE_PREFIX . "mail_contents` `mc` WHERE `" . TABLE_PREFIX . "mail_contents`.`conversation_id` = `mc`.`conversation_id` AND `" . TABLE_PREFIX . "mail_contents`.`id` <> `mc`.`id` AND `" . TABLE_PREFIX . "mail_contents`.`account_id` = `mc`.`account_id` {$archived_by_id} AND `mc`.`is_deleted` = 0 {$trashed_by_id} AND {$stateConditions}), TRUE, {$stateConditions})"; } else { $conversation_cond = ""; $box_cond = "AND {$stateConditions}"; } $conditions = "`is_deleted` = 0 {$archived_cond} {$projectConditions} {$accountConditions} {$tagstr} {$classified} {$read} {$permissions} {$conversation_cond} {$box_cond}"; if ($count) { return self::count($conditions); } else { if (!defined('EMAIL_PERFORMANCE_WORKAROUND') || !EMAIL_PERFORMANCE_WORKAROUND) { return self::paginate(array('conditions' => $conditions, 'order' => "{$order_by} {$dir}"), config_option('files_per_page'), $start / $limit + 1); } else { /* COMPLEX WORKAROUND FOR PERFORMANCE */ $conditions = "`is_deleted` = 0 {$archived_cond} {$projectConditions} {$tagstr} {$classified} {$read} {$accountConditions}"; $page = (int) ($start / $limit) + 1; $order = "{$order_by} {$dir}"; $count = null; if (defined('INFINITE_PAGING') && INFINITE_PAGING) { $count = 10000000; } $pagination = new DataPagination($count ? $count : self::count($conditions), $limit, $page); $ids = self::findAll(array('conditions' => $conditions, 'id' => true)); $ids = array_reverse($ids); $ret_ids = array(); $offset = 0; $block_len = $pagination->getItemsPerPage(); while (count($ret_ids) < $pagination->getLimitStart() + $pagination->getItemsPerPage() && $offset < count($ids)) { $tmp_ids = array(); for ($i = $offset; $i < count($ids) && $i < $offset + $block_len; $i++) { $tmp_ids[] = $ids[$i]; } $tmp_ids = self::findAll(array('conditions' => "`id` IN (" . implode(",", $tmp_ids) . ") {$permissions} {$conversation_cond} {$box_cond}", 'order' => $order, 'id' => true)); $ret_ids = array_merge($ret_ids, $tmp_ids); $offset += $block_len; } $ids = array(); for ($i = $pagination->getLimitStart(); $i < count($ret_ids); $i++) { $ids[] = $ret_ids[$i]; } $objects = array(); foreach ($ids as $id) { $objects[] = self::findById($id); } return array($objects, $pagination); } } }
/** * 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; }
/** * Return paged project files * * @param Project $project * @param ProjectFolder $folder * @param boolean $hide_private Don't show private files * @param string $order Order files by name or by posttime (desc) * @param integer $page Current page * @param integer $files_per_page Number of files that will be showed per single page * @param boolean $group_by_order Group files by order field * added by msaiz 03/10/07: * @param string for tag filter * @return array * */ static function getProjectFiles($project = null, $folderId = null, $hide_private = false, $order = null, $orderdir = 'ASC', $page = null, $files_per_page = null, $group_by_order = false, $tag = null, $type_string = null, $userId = null, $archived = false) { 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; } } // if if ((int) $page < 1) { $page = 1; } // if if ((int) $files_per_page < 1) { $files_per_page = 10; } // if 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_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) { $typestr = ""; } 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 .= ' )'); } $typestr = " AND (select count(*) from " . TABLE_PREFIX . "project_file_revisions where " . $typessql . " AND " . TABLE_PREFIX . "project_files.id = " . TABLE_PREFIX . "project_file_revisions.file_id)"; } if ($userId == null || $userId == 0) { $userstr = ""; } else { $userstr = " AND `created_by_id` = " . DB::escape($userId) . " "; } $permissionstr = ' AND ( ' . permissions_sql_for_listings(ProjectFiles::instance(), ACCESS_LEVEL_READ, logged_user()) . ') '; if ($archived) { $archived_cond = " AND `archived_by_id` <> 0"; } else { $archived_cond = " AND `archived_by_id` = 0"; } $otherConditions = $projectstr . $tagstr . $typestr . $userstr . $permissionstr . $archived_cond; if ($hide_private) { $conditions = array('`is_visible` = ?' . $otherConditions, true); } else { $conditions = array(' true ' . $otherConditions); } list($files, $pagination) = ProjectFiles::paginate(array('conditions' => $conditions, 'order' => $order_by), $files_per_page, $page); return array($files, $pagination); }
/** * Render assign to SELECT * @param string $list_name Name of the select control * @param Project $project Selected project, if NULL active project will be used * @param integer $selected ID of selected user * @param array $attributes Array of select box attributes, if needed * @return null */ function filter_assigned_to_select_box($list_name, $project = null, $selected = null, $attributes = null) { $logged_user = logged_user(); if ($project) { $project_ids = $project->getAllSubWorkspacesQuery(true, logged_user()); } else { $project_ids = logged_user()->getWorkspacesQuery(true); } $grouped_users = Users::getGroupedByCompanyFromProjectIds($project_ids); $options = array(option_tag(lang('anyone'), '0:0'), option_tag(lang('unassigned'), '-1:-1', '-1:-1' == $selected ? array('selected' => 'selected') : null)); if (is_array($grouped_users) && count($grouped_users)) { foreach ($grouped_users as $company_id => $users) { $company = Companies::findById($company_id); if (!$company instanceof Company) { continue; } // if $options[] = option_tag('--', '0:0'); // separator $option_attributes = $company->getId() . ':0' == $selected ? array('selected' => 'selected') : null; $options[] = option_tag($company->getName(), $company_id . ':0', $option_attributes); if (is_array($users)) { foreach ($users as $user) { $option_attributes = $company_id . ':' . $user->getId() == $selected ? array('selected' => 'selected') : null; $options[] = option_tag($user->getDisplayName() . ' : ' . $company->getName(), $company_id . ':' . $user->getId(), $option_attributes); } // foreach } // if } // foreach } // if return select_box($list_name, $options, $attributes); }