/**
  * 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);
 }
Example #5
0
/**
 * 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);
}