/** * Prepare conditions based on filter settings * * @param User $user * @return string */ function prepareConditions($user) { $project_objects_table = TABLE_PREFIX . 'project_objects'; $assignments_table = TABLE_PREFIX . 'assignments'; $completable_types = get_completable_project_object_types(); $conditions = array(); // Selected projects filter if ($this->getProjectFilter() == PROJECT_FILTER_SELECTED) { $project_ids = $this->getProjectFilterData(); if ($project_ids) { $conditions[] = db_prepare_string("({$project_objects_table}.project_id IN (?))", array($project_ids)); $types_filter = ProjectUsers::getVisibleTypesFilter($user, null, $completable_types); if ($types_filter) { $conditions[] = $types_filter; } else { return false; // No access to any of the projects? } // if } // if } // if // All projects if (count($conditions) == 0) { $types_filter = ProjectUsers::getVisibleTypesFilter($user, array(PROJECT_STATUS_ACTIVE), $completable_types); if ($types_filter) { $conditions[] = $types_filter; } else { return false; // No access to any of the projects? } // if } // if // User filter switch ($this->getUserFilter()) { // Not assigned to anyone case USER_FILTER_NOT_ASSIGNED: $user_id = $user->getId(); $conditions[] = "({$assignments_table}.user_id IS NULL)"; break; // Logged user // Logged user case USER_FILTER_LOGGED_USER: $user_id = $user->getId(); $conditions[] = "({$assignments_table}.user_id = {$user_id})"; break; // Logged user is responsible // Logged user is responsible case USER_FILTER_LOGGED_USER_RESPONSIBLE: $user_id = $user->getId(); $conditions[] = "({$assignments_table}.user_id = {$user_id}) AND ({$assignments_table}.is_owner = 1)"; break; // All members of a specific company // All members of a specific company case USER_FILTER_COMPANY: $visible_user_ids = $user->visibleUserIds(); if (!is_foreachable($visible_user_ids)) { return false; } // if $company_id = $this->getUserFilterData(); if ($company_id) { $company = Companies::findById($company_id); if (instance_of($company, 'Company')) { $user_ids = Users::findUserIdsByCompany($company); if (is_foreachable($user_ids)) { foreach ($user_ids as $k => $v) { if (!in_array($v, $visible_user_ids)) { unset($user_ids[$k]); } // if } // if if (count($user_ids) > 0) { $imploded = implode(', ', $user_ids); $conditions[] = "({$assignments_table}.user_id IN ({$imploded}))"; } else { return false; } // if } // if } // if } // if break; // Selected users // Selected users case USER_FILTER_SELECTED: $visible_user_ids = $user->visibleUserIds(); if (!is_foreachable($visible_user_ids)) { return false; } // if $user_ids = $this->getUserFilterData(); if (is_foreachable($user_ids)) { foreach ($user_ids as $k => $v) { if (!in_array($v, $visible_user_ids)) { unset($user_ids[$k]); } // if } // foreach if (count($user_ids) > 0) { $imploded = implode(', ', $user_ids); $conditions[] = "({$assignments_table}.user_id IN ({$imploded}))"; } else { return false; } // if } // if break; } // switch $today = new DateValue(time() + get_user_gmt_offset($user)); // Calculate user timezone when determining today switch ($this->getDateFilter()) { // List late assignments case DATE_FILTER_LATE: $today_str = db_escape($today->toMySQL()); $conditions[] = "({$project_objects_table}.due_on < {$today_str})"; break; // List today assignments // List today assignments case DATE_FILTER_TODAY: $today_str = db_escape($today->toMySQL()); $conditions[] = "({$project_objects_table}.due_on = {$today_str})"; break; // List tomorrow assignments // List tomorrow assignments case DATE_FILTER_TOMORROW: $tomorrow = $today->advance(86400, false); $tomorrow_str = db_escape($tomorrow->toMySQL()); $conditions[] = "({$project_objects_table}.due_on = {$tomorrow_str})"; break; // List this week assignments // List this week assignments case DATE_FILTER_THIS_WEEK: $first_day_sunday = UserConfigOptions::getValue('time_first_week_day', $user) == 0; $week_start = $today->beginningOfWeek($first_day_sunday); $week_end = $today->endOfWeek($first_day_sunday); $week_start_str = db_escape($week_start->toMySQL()); $week_end_str = db_escape($week_end->toMySQL()); $conditions[] = "({$project_objects_table}.due_on >= {$week_start_str} AND {$project_objects_table}.due_on <= {$week_end_str})"; break; // List next week assignments // List next week assignments case DATE_FILTER_NEXT_WEEK: $first_day_sunday = UserConfigOptions::getValue('time_first_week_day', $user) == 0; $next_week = $today->advance(604800, false); $week_start = $next_week->beginningOfWeek($first_day_sunday); $week_end = $next_week->endOfWeek($first_day_sunday); $week_start_str = db_escape($week_start->toMySQL()); $week_end_str = db_escape($week_end->toMySQL()); $conditions[] = "({$project_objects_table}.due_on >= {$week_start_str} AND {$project_objects_table}.due_on <= {$week_end_str})"; break; // List this month assignments // List this month assignments case DATE_FILTER_THIS_MONTH: $month_start = DateTimeValue::beginningOfMonth($today->getMonth(), $today->getYear()); $month_end = DateTimeValue::endOfMonth($today->getMonth(), $today->getYear()); $month_start_str = db_escape($month_start->toMySQL()); $month_end_str = db_escape($month_end->toMySQL()); $conditions[] = "({$project_objects_table}.due_on >= {$month_start_str} AND {$project_objects_table}.due_on <= {$month_end_str})"; break; // List next month assignments // List next month assignments case DATE_FILTER_NEXT_MONTH: $month = $today->getMonth() + 1; $year = $today->getYear(); if ($month == 13) { $month = 1; $year += 1; } // if $month_start = DateTimeValue::beginningOfMonth($month, $year); $month_end = DateTimeValue::endOfMonth($month, $year); $month_start_str = db_escape($month_start->toMySQL()); $month_end_str = db_escape($month_end->toMySQL()); $conditions[] = "({$project_objects_table}.due_on >= {$month_start_str} AND {$project_objects_table}.due_on <= {$month_end_str})"; break; // Specific date // Specific date case DATE_FILTER_SELECTED_DATE: $date_from = $this->getDateFrom(); if (instance_of($date_from, 'DateTimeValue')) { $date_from_str = db_escape($date_from->toMySql()); $conditions[] = "({$project_objects_table}.due_on = {$date_from_str})"; } // if break; // Specific range // Specific range case DATE_FILTER_SELECTED_RANGE: $date_from = $this->getDateFrom(); $date_to = $this->getDateTo(); if (instance_of($date_from, 'DateValue') && instance_of($date_to, 'DateValue')) { $date_from_str = db_escape($date_from->toMySQL()); $date_to_str = db_escape($date_to->toMySQL()); $conditions[] = "({$project_objects_table}.due_on >= {$date_from_str} AND {$project_objects_table}.due_on <= {$date_to_str})"; } // if break; } // switch // Status filter switch ($this->getStatusFilter()) { case STATUS_FILTER_ACTIVE: $conditions[] = "({$project_objects_table}.completed_on IS NULL)"; break; case STATUS_FILTER_COMPLETED: $conditions[] = "({$project_objects_table}.completed_on IS NOT NULL)"; break; } // if // Additional filters $state = STATE_VISIBLE; $visibility = $user->getVisibility(); $conditions[] = "({$project_objects_table}.state >= {$state} AND {$project_objects_table}.visibility >= {$visibility})"; return implode(' AND ', $conditions); }
/** * Prepare conditions based on report settings * * @param User $user * @param Project $project * @return string */ function prepareConditions($user, $project = null) { $project_objects_table = TABLE_PREFIX . 'project_objects'; $conditions = array(); // Project and type if (instance_of($project, 'Project')) { $conditions[] = db_prepare_string('project_id = ? AND type = ?', array($project->getId(), 'timerecord')); } else { $conditions[] = db_prepare_string('type = ?', array('timerecord')); } // if // User filter switch ($this->getUserFilter()) { // Anyone - This filter used to filter only time tracked from all // visible users, but that did not include deleted accounts. Fixed now // //case USER_FILTER_ANYBODY: // $visible_user_ids = $user->visibleUserIds(); // if(is_foreachable($visible_user_ids)) { // $conditions[] = "($project_objects_table.integer_field_1 IN (" . db_escape($visible_user_ids) . '))'; // } else { // return false; // not visible users // } // if // break; // Logged user case USER_FILTER_LOGGED_USER: $user_id = $user->getId(); $conditions[] = "({$project_objects_table}.integer_field_1 = {$user_id})"; break; // All members of a specific company // All members of a specific company case USER_FILTER_COMPANY: $visible_user_ids = $user->visibleUserIds(); if (!is_foreachable($visible_user_ids)) { return false; } // if $company_id = $this->getUserFilterData(); if ($company_id) { $company = Companies::findById($company_id); if (instance_of($company, 'Company')) { $user_ids = Users::findUserIdsByCompany($company); if (is_foreachable($user_ids)) { foreach ($user_ids as $k => $v) { if (!in_array($v, $visible_user_ids)) { unset($user_ids[$k]); } // if } // if if (count($user_ids) > 0) { $imploded = implode(', ', $user_ids); $conditions[] = "({$project_objects_table}.integer_field_1 IN ({$imploded}))"; } else { return false; } // if } // if } // if } // if break; // Selected users // Selected users case USER_FILTER_SELECTED: $visible_user_ids = $user->visibleUserIds(); if (!is_foreachable($visible_user_ids)) { return false; } // if $user_ids = $this->getUserFilterData(); if (is_foreachable($user_ids)) { foreach ($user_ids as $k => $v) { if (!in_array($v, $visible_user_ids)) { unset($user_ids[$k]); } // if } // foreach if (count($user_ids) > 0) { $imploded = implode(', ', $user_ids); $conditions[] = "({$project_objects_table}.integer_field_1 IN ({$imploded}))"; } else { return false; } // if } // if break; } // switch $today = new DateValue(time() + get_user_gmt_offset($user)); // Calculate user timezone when determining today switch ($this->getDateFilter()) { // List time records posted for today case DATE_FILTER_TODAY: $today_str = db_escape($today->toMySQL()); $conditions[] = "({$project_objects_table}.date_field_1 = {$today_str})"; break; // List next week records // List next week records case DATE_FILTER_LAST_WEEK: $first_day_sunday = UserConfigOptions::getValue('time_first_week_day', $user) == 0; $last_week = $today->advance(-604800, false); $week_start = $last_week->beginningOfWeek($first_day_sunday); $week_end = $last_week->endOfWeek($first_day_sunday); $week_start_str = db_escape($week_start->toMySQL()); $week_end_str = db_escape($week_end->toMySQL()); $conditions[] = "({$project_objects_table}.date_field_1 >= {$week_start_str} AND {$project_objects_table}.date_field_1 <= {$week_end_str})"; break; // List this week records // List this week records case DATE_FILTER_THIS_WEEK: $first_day_sunday = UserConfigOptions::getValue('time_first_week_day', $user) == 0; $week_start = $today->beginningOfWeek($first_day_sunday); $week_end = $today->endOfWeek($first_day_sunday); $week_start_str = db_escape($week_start->toMySQL()); $week_end_str = db_escape($week_end->toMySQL()); $conditions[] = "({$project_objects_table}.date_field_1 >= {$week_start_str} AND {$project_objects_table}.date_field_1 <= {$week_end_str})"; break; // List this month time records // List this month time records case DATE_FILTER_LAST_MONTH: $month = $today->getMonth() - 1; $year = $today->getYear(); if ($month == 0) { $month = 12; $year -= 1; } // if $month_start = DateTimeValue::beginningOfMonth($month, $year); $month_end = DateTimeValue::endOfMonth($month, $year); $month_start_str = db_escape($month_start->toMySQL()); $month_end_str = db_escape($month_end->toMySQL()); $conditions[] = "({$project_objects_table}.date_field_1 >= {$month_start_str} AND {$project_objects_table}.date_field_1 <= {$month_end_str})"; break; // Last month // Last month case DATE_FILTER_THIS_MONTH: $month_start = DateTimeValue::beginningOfMonth($today->getMonth(), $today->getYear()); $month_end = DateTimeValue::endOfMonth($today->getMonth(), $today->getYear()); $month_start_str = db_escape($month_start->toMySQL()); $month_end_str = db_escape($month_end->toMySQL()); $conditions[] = "({$project_objects_table}.date_field_1 >= {$month_start_str} AND {$project_objects_table}.date_field_1 <= {$month_end_str})"; break; // Specific date // Specific date case DATE_FILTER_SELECTED_DATE: $date_from = $this->getDateFrom(); if (instance_of($date_from, 'DateValue')) { $date_from_str = db_escape($date_from->toMySql()); $conditions[] = "({$project_objects_table}.date_field_1 = {$date_from_str})"; } // if break; // Specific range // Specific range case DATE_FILTER_SELECTED_RANGE: $date_from = $this->getDateFrom(); $date_to = $this->getDateTo(); if (instance_of($date_from, 'DateValue') && instance_of($date_to, 'DateValue')) { $date_from_str = db_escape($date_from->toMySQL()); $date_to_str = db_escape($date_to->toMySQL()); $conditions[] = "({$project_objects_table}.date_field_1 >= {$date_from_str} AND {$project_objects_table}.date_field_1 <= {$date_to_str})"; } // if break; } // switch // Billable filter switch ($this->getBillableFilter()) { case BILLABLE_FILTER_BILLABLE: $conditions[] = "({$project_objects_table}.integer_field_2 = '" . BILLABLE_STATUS_BILLABLE . "')"; break; case BILLABLE_FILTER_NOT_BILLABLE: $conditions[] = "({$project_objects_table}.integer_field_2 = '" . BILLABLE_STATUS_NOT_BILLABLE . "' OR {$project_objects_table}.integer_field_2 IS NULL)"; break; case BILLABLE_FILTER_BILLABLE_BILLED: $conditions[] = "({$project_objects_table}.integer_field_2 >= '" . BILLABLE_STATUS_BILLED . "')"; break; case BILLABLE_FILTER_PENDING_PAYMENT: $conditions[] = "({$project_objects_table}.integer_field_2 = '" . BILLABLE_STATUS_PENDING_PAYMENT . "')"; break; case BILLABLE_FILTER_BILLABLE_NOT_BILLED: $conditions[] = "({$project_objects_table}.integer_field_2 IN ('" . BILLABLE_STATUS_BILLABLE . "', '" . BILLABLE_STATUS_PENDING_PAYMENT . "'))"; break; } // switch // Additional filters $state = STATE_VISIBLE; $visibility = $user->getVisibility(); $conditions[] = "({$project_objects_table}.state >= {$state} AND {$project_objects_table}.visibility >= {$visibility})"; return implode(' AND ', $conditions); }