app/models/task_filter.rb
# encoding: UTF-8
###
# A task filter is used to find tasks matching the filters set up
# in session.
###
class TaskFilter < ActiveRecord::Base
# column `name` is of type `VARCHAR(255)`.
# Note that MySQL 5.x+ and PostgreSQL both track length of "characters" and not "bytes"
MAXIMUM_NAME_LENGTH = 255
belongs_to :user
belongs_to :company
has_many(:qualifiers, :dependent => :destroy, :class_name => 'TaskFilterQualifier')
has_many :keywords, :dependent => :destroy
has_many :task_filter_users, :dependent => :delete_all
accepts_nested_attributes_for :keywords
accepts_nested_attributes_for :qualifiers
validates :user, :presence => true
validates :name, :presence => true, :length => {:maximum => MAXIMUM_NAME_LENGTH}
scope :shared, -> { where(:shared => true) }
scope :visible, -> { where(:system => false, :recent_for_user_id => nil) }
scope :recent_for, lambda { |user| where(:recent_for_user_id => user.id).order('id DESC') }
before_create :set_company_from_user
after_create :set_task_filter_status, :if => Proc.new { |x| x.recent_for_user_id.blank? && !x.system }
# Returns the system filter for the given user. If none is found,
# create and saves a new one and returns that.
def self.system_filter(user)
filter = user.task_filters.where(:system => true).first
if filter.nil?
filter = user.task_filters.build(:name => "System filter for #{ user }",
:user_id => user.id, :system => true)
filter.save!
end
return filter
end
# Returns an array of all tasks matching the conditions from this filter.
# If extra_conditions is passed, that will be ANDed to the conditions
# If limit is false, no limit will be set on the tasks returned (otherwise
# a default limit will be applied)
def tasks(extra_conditions = nil)
return TaskRecord.all_accessed_by(user).where(conditions(extra_conditions)).includes(to_include).references(to_include).limit(500).uniq
end
# Returns an array of all tasks matching the conditions from this filter.
def tasks_for_fullcalendar(parameters)
tasks(parse_fullcalendar_params(parameters)).joins(:milestone)
end
def tasks_for_gantt(parameters)
tasks.includes(:milestone).order('projects.name, milestones.name')
end
def projects_for_fullcalendar(parameters)
projects = tasks(parse_fullcalendar_params(parameters)).includes(:project).collect { |t| t.project }.uniq
end
# Returns the count of tasks matching the conditions of this filter.
# if extra_conditions is passed, that will be ANDed to the conditions
def count(extra_conditions = nil)
TaskRecord.all_accessed_by(user).includes(to_include).references(to_include).where(conditions(extra_conditions)).uniq.count
end
# Returns a count to display for this filter. The count represents the
# number of tasks that look they need attention for the given user -
# unassigned tasks and unread tasks are counted.
# The value will be cached and re-used unless force_recount is passed.
def display_count(user, force_recount = false)
@display_count = nil if force_recount
@display_count ||= count(unread_conditions(user, true))
end
# Returns an array of the conditions to use for a sql lookup
# of tasks for this filter
def conditions(extra_conditions = nil)
time_qualifiers = qualifiers.select { |q| q.qualifiable_type == 'TimeRange' }
status_qualifiers = qualifiers.select { |q| q.qualifiable_type == 'Status' }
property_qualifiers = qualifiers.select { |q| q.qualifiable_type == 'PropertyValue' }
customer_qualifiers = qualifiers.select { |q| q.qualifiable_type == 'Customer' }
standard_qualifiers = (qualifiers - property_qualifiers - status_qualifiers -
customer_qualifiers - time_qualifiers)
res = conditions_for_standard_qualifiers(standard_qualifiers)
res += conditions_for_property_qualifiers(property_qualifiers)
res << conditions_for_status_qualifiers(status_qualifiers)
res << conditions_for_customer_qualifiers(customer_qualifiers)
res << conditions_for_time_qualifiers(time_qualifiers)
res << conditions_for_keywords
res << extra_conditions if extra_conditions
res << unread_conditions(user) if unread_only?
res << '(task_users.id is null)' if unassigned?
res = res.select { |c| !c.blank? }
res = res.join(' AND ')
return res
end
def cache_key
key = super
if unread_only?
# we can't cache the whole filter when unread_only set
"#{ key }/Time.now.to_i/#{ user.id }/#{ rand }/"
else
last_task_update = user.company.tasks.where(conditions).joins(:task_users).includes(to_include).maximum(:updated_at)
"#{ key }/#{ last_task_update.to_i }/#{ user.id }"
end
end
def copy_from(filter)
self.unread_only = filter.unread_only
filter.qualifiers.each do |q|
self.qualifiers.build(:task_filter_id => q.task_filter_id,
:qualifiable_type => q.qualifiable_type,
:qualifiable_id => q.qualifiable_id,
:qualifiable_column => q.qualifiable_column,
:reversed => q.reversed)
end
filter.keywords.each do |kw|
# N.B Shouldn't have to pass in all these values, but it
# doesn't work when we don't, so...
self.keywords.build(:task_filter => self,
:company => filter.company,
:word => kw.word)
end
end
def select_filter(filter)
TaskFilter.transaction do
self.qualifiers.all.delete_all
self.keywords.all.delete_all
self.copy_from(filter)
self.save!
end
end
def store_for(user)
ActiveRecord::Base.transaction do
if TaskFilter.recent_for(user).count >= 10
TaskFilter.recent_for(user).last.destroy
end
filter=TaskFilter.new(:recent_for_user_id => user.id, :user => user, :company => self.company)
filter.name= generate_name
filter.name= self.name if filter.name.blank?
filter.copy_from(self)
filter.save!
end
end
def update_filter(params)
ActiveRecord::Base.transaction do
self.keywords.all.delete_all
self.qualifiers.all.delete_all
self.unread_only = false
self.attributes = params
self.save!
end
end
def show?(user)
task_filter_users.where(:user_id => user.id).count != 0
end
private
###
# This method generate filter name based on qualifiers and keywords
# this name will include all projects, milestones, statuses, clients, users qualifiers in this order
# then all keywords, then other qualifiers
###
def generate_name
counter = 0
arr=[]
types=['Project', 'Milestone', 'Status', 'Client', 'User']
types.each do |type|
qualifiers.select { |q| q.qualifiable_type == type }.each do |qualifier|
arr<< (qualifier.reversed? ? 'not ' : '') + qualifier.qualifiable.to_s
end
end
keywords.each do |kw|
arr<< (kw.reversed? ? 'not ' : '') + kw.word;
end
qualifiers.select { |q| !types.include?(q.qualifiable_type) }.each do |qualifier|
arr<< (qualifier.reversed? ? 'not ' : '') + qualifier.qualifiable.to_s
end
arr<< 'Unread only' if unread_only?
return arr.join(', ').truncate(MAXIMUM_NAME_LENGTH)
end
def to_include
to_include = [:project, :task_users]
to_include = :task_owners if unassigned?
to_include << :tags if qualifiers.for('Tag').any?
to_include << :task_property_values if qualifiers.for('PropertyValue').any?
to_include << :customers if qualifiers.for('Customer').any?
return to_include
end
def set_company_from_user
self.company = user.company
end
# Returns a conditions hash the will filter tasks based on the
# given property value qualifiers
def conditions_for_property_qualifiers(property_qualifiers)
property_qualifiers = property_qualifiers.group_by { |qualifier| qualifier.reversed? }
simple_conditions_for_property_qualifiers(property_qualifiers[false], false) + simple_conditions_for_property_qualifiers(property_qualifiers[true], true)
end
def simple_conditions_for_property_qualifiers(property_qualifiers, reverse)
return [] if property_qualifiers.nil?
name = 'task_property_values.property_value_id'
grouped = property_qualifiers.group_by { |q| q.qualifiable.property }
res = []
grouped.each do |property, qualifiers|
ids = qualifiers.map { |q| q.qualifiable.id }
if reverse
res << "(#{ name } NOT IN (#{ ids.join(', ') }) AND task_property_values.property_id = #{property.id})"
else
res << "(#{ name } IN (#{ ids.join(', ') }) AND task_property_values.property_id = #{property.id})"
end
end
return res
end
# Returns an array of conditions that will filter tasks based on the
# given standard qualifiers.
# Standard qualifiers are things like project, milestone, user, where
# a filter will OR the different users, but and between different types
def conditions_for_standard_qualifiers(standard_qualifiers)
standard_qualifiers = standard_qualifiers.group_by { |qualifier| qualifier.reversed? }
simple_conditions_for_standard_qualifiers(standard_qualifiers[false])+ simple_conditions_for_standard_qualifiers(standard_qualifiers[true]).map { |sql| 'not ' + sql }
end
def simple_conditions_for_standard_qualifiers(standard_qualifiers)
return [] if standard_qualifiers.nil?
res = []
grouped_conditions = standard_qualifiers.group_by { |q| q.qualifiable_type }
grouped_conditions.each do |type, values|
name = column_name_for(type)
ids = values.map { |v| v.qualifiable_id }
res << "#{ name } in (#{ ids.join(',') })"
end
return res
end
# Returns a string sql fragment that will limit tasks to
# those that match the set keywords
def conditions_for_keywords
kws = keywords.group_by { |keyword| keyword.reversed? }
compose_sql(simple_conditions_for_keywords(kws[false]), simple_conditions_for_keywords(kws[true]))
end
def simple_conditions_for_keywords(keywords_arg)
return if keywords_arg.nil?
sql = []
params = []
keywords_arg.each do |kw|
str = 'lower(tasks.name) like ?'
str += ' or lower(tasks.description) like ?'
sql << "coalesce((#{str}), FALSE)"
2.times { params << "%#{ kw.word.downcase }%" }
end
sql = sql.join(' or ')
res = TaskFilter.send(:sanitize_sql_array, [sql] + params)
return "(#{ res })" unless res.blank?
end
# Returns a sql string fragment that will limit tasks to only
# status set by the status qualifiers.
# Status qualifiers have to be handled especially until the
# migration from an array in code to db backed statuses is complete
def conditions_for_status_qualifiers(status_qualifiers)
status_qualifiers = status_qualifiers.group_by { |qualifier| qualifier.reversed? }
compose_sql(simple_conditions_for_status_qualifiers(status_qualifiers[false]), simple_conditions_for_status_qualifiers(status_qualifiers[true]))
end
def simple_conditions_for_status_qualifiers(status_qualifiers)
return if status_qualifiers.nil?
old_status_ids = []
c = company || user.company
status_qualifiers.each do |q|
status = q.qualifiable
old_status = c.statuses.index(status)
old_status_ids << old_status
end
old_status_ids = old_status_ids.compact.join(',')
return "tasks.status in (#{ old_status_ids })" unless old_status_ids.blank?
end
# Returns a sql string fragment that will limit tasks to only
# those in a project belonging to customers, or linked directly
# to the customer
def conditions_for_customer_qualifiers(customer_qualifiers)
customer_qualifiers = customer_qualifiers.group_by { |qualifier| qualifier.reversed? }
compose_sql(simple_conditions_for_customer_qualifiers(customer_qualifiers[false]), simple_conditions_for_customer_qualifiers(customer_qualifiers[true]))
end
def simple_conditions_for_customer_qualifiers(customer_qualifiers)
return if customer_qualifiers.nil?
ids = customer_qualifiers.map { |q| q.qualifiable.id }
ids = ids.join(',')
unless ids.blank?
res = "projects.customer_id in (#{ ids })"
res += " or coalesce(task_customers.customer_id in (#{ ids }),0)"
return "(#{ res })"
end
end
# Returns a sql string fragment that will limit tasks to only those
# which match the given time qualifiers
def conditions_for_time_qualifiers(time_qualifiers)
time_qualifiers = time_qualifiers.group_by { |qualifier| qualifier.reversed? }
compose_sql(simple_conditions_for_time_qualifiers(time_qualifiers[false]), simple_conditions_for_time_qualifiers(time_qualifiers[true]))
end
def simple_conditions_for_time_qualifiers(time_qualifiers)
return if time_qualifiers.nil? or time_qualifiers.empty?
res = []
time_qualifiers.each do |tq|
start_time = tq.qualifiable.start_time
end_time = tq.qualifiable.end_time
column = tq.qualifiable_column
column = TaskRecord.connection.quote_column_name(column)
sql = "tasks.#{ column } >= '#{ start_time.to_formatted_s(:db) }'"
sql += " and tasks.#{ column } < '#{ end_time.to_formatted_s(:db) }'"
res << "coalesce((#{sql}),0)"
end
res = res.join(' or ')
return "(#{ res })"
end
# Returns the column name to use for lookup for the given
# class_type
def column_name_for(class_type)
if class_type == 'User'
return "task_users.type= 'TaskOwner' AND task_users.user_id"
elsif class_type == 'Project'
return 'tasks.project_id'
elsif class_type == 'Task'
return 'tasks.id'
elsif class_type == 'Customer'
return 'projects.customer_id'
elsif class_type == 'Company'
return 'tasks.company_id'
elsif class_type == 'Milestone'
return 'tasks.milestone_id'
elsif class_type == 'Tag'
return 'task_tags.tag_id'
else
return "#{ class_type.downcase }_id"
end
end
def unread_conditions(user, include_orphaned = false)
count_conditions = []
count_conditions << "(task_users.unread = ? AND task_users.user_id = #{ user.id })"
count_conditions << '(task_users.id IS NULL)' if include_orphaned
sql = count_conditions.join(' OR ')
params = [true]
sql = TaskFilter.send(:sanitize_sql_array, [sql] + params)
"(#{ sql })"
end
def compose_sql(arg1, arg2)
if arg1.blank?
if arg2.blank?
''
else
"( not #{arg2} )"
end
else
if arg2.blank?
arg1
else
"(#{arg1} and not #{arg2})"
end
end
end
#This function parse fullCalendar `start` and `end` date(in Unix format) from params
#return conditions for TaskFilter#tasks, unfortunately TaskFilter#task does not support active record :conditions, only plain sql:(
def parse_fullcalendar_params(calendar_params)
if !calendar_params[:end].blank? and !calendar_params[:start].blank?
#return TaskFilter.send(:sanitize_sql_array, ["if(isnull(tasks.estimate_date), (milestones.due_at < ? and milestones.due_at > ?),(tasks.estimate_date < ? and tasks.estimate_date > ?))", Time.at(calendar_params[:end].to_i), Time.at(calendar_params[:start].to_i), Time.at(calendar_params[:end].to_i), Time.at(calendar_params[:start].to_i)])
return TaskFilter.send(:sanitize_sql_array, [
'((tasks.estimate_date IS NULL AND milestones.due_at < ? AND milestones.due_at > ?) OR (tasks.estimate_date IS NOT NULL AND tasks.estimate_date < ? AND tasks.estimate_date > ?))',
Time.at(calendar_params[:end].to_i), Time.at(calendar_params[:start].to_i), Time.at(calendar_params[:end].to_i), Time.at(calendar_params[:start].to_i)
]
)
else
return nil
end
end
def set_task_filter_status
# make owner can see his/her own filter, can be changed later on manage filter
self.task_filter_users.create(:user_id => self.user_id)
end
end
# == Schema Information
#
# Table name: task_filters
#
# id :integer(4) not null, primary key
# name :string(255)
# company_id :integer(4)
# user_id :integer(4)
# shared :boolean(1)
# created_at :datetime
# updated_at :datetime
# system :boolean(1) default(FALSE)
# unread_only :boolean(1) default(FALSE)
# recent_for_user_id :integer(4)
#
# Indexes
#
# fk_task_filters_company_id (company_id)
# fk_task_filters_user_id (user_id)
#