"Fossies" - the Fresh Open Source Software Archive

Member "redmine-4.1.1/app/models/query.rb" (6 Apr 2020, 48926 Bytes) of package /linux/www/redmine-4.1.1.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) Ruby source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file. See also the latest Fossies "Diffs" side-by-side code changes report for "query.rb": 4.1.0_vs_4.1.1.

    1 # frozen_string_literal: true
    2 
    3 # Redmine - project management software
    4 # Copyright (C) 2006-2019  Jean-Philippe Lang
    5 #
    6 # This program is free software; you can redistribute it and/or
    7 # modify it under the terms of the GNU General Public License
    8 # as published by the Free Software Foundation; either version 2
    9 # of the License, or (at your option) any later version.
   10 #
   11 # This program is distributed in the hope that it will be useful,
   12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
   13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   14 # GNU General Public License for more details.
   15 #
   16 # You should have received a copy of the GNU General Public License
   17 # along with this program; if not, write to the Free Software
   18 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
   19 
   20 require 'redmine/sort_criteria'
   21 
   22 class QueryColumn
   23   attr_accessor :name, :groupable, :totalable, :default_order
   24   attr_writer   :sortable
   25   include Redmine::I18n
   26 
   27   def initialize(name, options={})
   28     self.name = name
   29     self.sortable = options[:sortable]
   30     self.groupable = options[:groupable] || false
   31     if groupable == true
   32       self.groupable = name.to_s
   33     end
   34     self.totalable = options[:totalable] || false
   35     self.default_order = options[:default_order]
   36     @inline = options.key?(:inline) ? options[:inline] : true
   37     @caption_key = options[:caption] || "field_#{name}".to_sym
   38     @frozen = options[:frozen]
   39   end
   40 
   41   def caption
   42     case @caption_key
   43     when Symbol
   44       l(@caption_key)
   45     when Proc
   46       @caption_key.call
   47     else
   48       @caption_key
   49     end
   50   end
   51 
   52   # Returns true if the column is sortable, otherwise false
   53   def sortable?
   54     !@sortable.nil?
   55   end
   56 
   57   def sortable
   58     @sortable.is_a?(Proc) ? @sortable.call : @sortable
   59   end
   60 
   61   def inline?
   62     @inline
   63   end
   64 
   65   def frozen?
   66     @frozen
   67   end
   68 
   69   def value(object)
   70     object.send name
   71   end
   72 
   73   def value_object(object)
   74     object.send name
   75   end
   76 
   77   # Returns the group that object belongs to when grouping query results
   78   def group_value(object)
   79     value(object)
   80   end
   81 
   82   def css_classes
   83     name
   84   end
   85 end
   86 
   87 class TimestampQueryColumn < QueryColumn
   88 
   89   def groupable
   90     if @groupable
   91       Redmine::Database.timestamp_to_date(sortable, User.current.time_zone)
   92     end
   93   end
   94 
   95   def group_value(object)
   96     if time = value(object)
   97       User.current.time_to_date(time)
   98     end
   99   end
  100 end
  101 
  102 class QueryAssociationColumn < QueryColumn
  103 
  104   def initialize(association, attribute, options={})
  105     @association = association
  106     @attribute = attribute
  107     name_with_assoc = "#{association}.#{attribute}".to_sym
  108     super(name_with_assoc, options)
  109   end
  110 
  111   def value_object(object)
  112     if assoc = object.send(@association)
  113       assoc.send @attribute
  114     end
  115   end
  116 
  117   def css_classes
  118     @css_classes ||= "#{@association}-#{@attribute}"
  119   end
  120 end
  121 
  122 class QueryCustomFieldColumn < QueryColumn
  123 
  124   def initialize(custom_field, options={})
  125     self.name = "cf_#{custom_field.id}".to_sym
  126     self.sortable = custom_field.order_statement || false
  127     self.groupable = custom_field.group_statement || false
  128     self.totalable = options.key?(:totalable) ? !!options[:totalable] : custom_field.totalable?
  129     @inline = custom_field.full_width_layout? ? false : true
  130     @cf = custom_field
  131   end
  132 
  133   def caption
  134     @cf.name
  135   end
  136 
  137   def custom_field
  138     @cf
  139   end
  140 
  141   def value_object(object)
  142     if custom_field.visible_by?(object.project, User.current)
  143       cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
  144       cv.size > 1 ? cv.sort_by {|e| e.value.to_s} : cv.first
  145     else
  146       nil
  147     end
  148   end
  149 
  150   def value(object)
  151     raw = value_object(object)
  152     if raw.is_a?(Array)
  153       raw.map {|r| @cf.cast_value(r.value)}
  154     elsif raw
  155       @cf.cast_value(raw.value)
  156     else
  157       nil
  158     end
  159   end
  160 
  161   def css_classes
  162     @css_classes ||= "#{name} #{@cf.field_format}"
  163   end
  164 end
  165 
  166 class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
  167 
  168   def initialize(association, custom_field, options={})
  169     super(custom_field, options)
  170     self.name = "#{association}.cf_#{custom_field.id}".to_sym
  171     # TODO: support sorting/grouping by association custom field
  172     self.sortable = false
  173     self.groupable = false
  174     @association = association
  175   end
  176 
  177   def value_object(object)
  178     if assoc = object.send(@association)
  179       super(assoc)
  180     end
  181   end
  182 
  183   def css_classes
  184     @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
  185   end
  186 end
  187 
  188 class QueryFilter
  189   include Redmine::I18n
  190 
  191   def initialize(field, options)
  192     @field = field.to_s
  193     @options = options
  194     @options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
  195     # Consider filters with a Proc for values as remote by default
  196     @remote = options.key?(:remote) ? options[:remote] : options[:values].is_a?(Proc)
  197   end
  198 
  199   def [](arg)
  200     if arg == :values
  201       values
  202     else
  203       @options[arg]
  204     end
  205   end
  206 
  207   def values
  208     @values ||= begin
  209       values = @options[:values]
  210       if values.is_a?(Proc)
  211         values = values.call
  212       end
  213       values
  214     end
  215   end
  216 
  217   def remote
  218     @remote
  219   end
  220 end
  221 
  222 class Query < ActiveRecord::Base
  223   class StatementInvalid < ::ActiveRecord::StatementInvalid
  224   end
  225 
  226   include Redmine::SubclassFactory
  227 
  228   VISIBILITY_PRIVATE = 0
  229   VISIBILITY_ROLES   = 1
  230   VISIBILITY_PUBLIC  = 2
  231 
  232   belongs_to :project
  233   belongs_to :user
  234   has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"
  235   serialize :filters
  236   serialize :column_names
  237   serialize :sort_criteria, Array
  238   serialize :options, Hash
  239 
  240   validates_presence_of :name
  241   validates_length_of :name, :maximum => 255
  242   validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
  243   validate :validate_query_filters
  244   validate do |query|
  245     errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?
  246   end
  247 
  248   after_save do |query|
  249     if query.saved_change_to_visibility? && query.visibility != VISIBILITY_ROLES
  250       query.roles.clear
  251     end
  252   end
  253 
  254   class_attribute :operators
  255   self.operators = {
  256     "="   => :label_equals,
  257     "!"   => :label_not_equals,
  258     "o"   => :label_open_issues,
  259     "c"   => :label_closed_issues,
  260     "!*"  => :label_none,
  261     "*"   => :label_any,
  262     ">="  => :label_greater_or_equal,
  263     "<="  => :label_less_or_equal,
  264     "><"  => :label_between,
  265     "<t+" => :label_in_less_than,
  266     ">t+" => :label_in_more_than,
  267     "><t+"=> :label_in_the_next_days,
  268     "t+"  => :label_in,
  269     "nd"  => :label_tomorrow,
  270     "t"   => :label_today,
  271     "ld"  => :label_yesterday,
  272     "nw"  => :label_next_week,
  273     "w"   => :label_this_week,
  274     "lw"  => :label_last_week,
  275     "l2w" => [:label_last_n_weeks, {:count => 2}],
  276     "nm"  => :label_next_month,
  277     "m"   => :label_this_month,
  278     "lm"  => :label_last_month,
  279     "y"   => :label_this_year,
  280     ">t-" => :label_less_than_ago,
  281     "<t-" => :label_more_than_ago,
  282     "><t-"=> :label_in_the_past_days,
  283     "t-"  => :label_ago,
  284     "~"   => :label_contains,
  285     "!~"  => :label_not_contains,
  286     "^"   => :label_starts_with,
  287     "$"   => :label_ends_with,
  288     "=p"  => :label_any_issues_in_project,
  289     "=!p" => :label_any_issues_not_in_project,
  290     "!p"  => :label_no_issues_in_project,
  291     "*o"  => :label_any_open_issues,
  292     "!o"  => :label_no_open_issues,
  293   }
  294 
  295   class_attribute :operators_by_filter_type
  296   self.operators_by_filter_type = {
  297     :list => [ "=", "!" ],
  298     :list_status => [ "o", "=", "!", "c", "*" ],
  299     :list_optional => [ "=", "!", "!*", "*" ],
  300     :list_subprojects => [ "*", "!*", "=", "!" ],
  301     :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "nd", "t", "ld", "nw", "w", "lw", "l2w", "nm", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
  302     :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
  303     :string => [ "~", "=", "!~", "!", "^", "$", "!*", "*" ],
  304     :text => [  "~", "!~", "^", "$", "!*", "*" ],
  305     :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
  306     :float => [ "=", ">=", "<=", "><", "!*", "*" ],
  307     :relation => ["=", "!", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
  308     :tree => ["=", "~", "!*", "*"]
  309   }
  310 
  311   class_attribute :available_columns
  312   self.available_columns = []
  313 
  314   class_attribute :queried_class
  315 
  316   # Permission required to view the queries, set on subclasses.
  317   class_attribute :view_permission
  318 
  319   # Scope of queries that are global or on the given project
  320   scope :global_or_on_project, lambda {|project|
  321     where(:project_id => (project.nil? ? nil : [nil, project.id]))
  322   }
  323 
  324   scope :sorted, lambda {order(:name, :id)}
  325 
  326   # Scope of visible queries, can be used from subclasses only.
  327   # Unlike other visible scopes, a class methods is used as it
  328   # let handle inheritance more nicely than scope DSL.
  329   def self.visible(*args)
  330     if self == ::Query
  331       # Visibility depends on permissions for each subclass,
  332       # raise an error if the scope is called from Query (eg. Query.visible)
  333       raise "Cannot call .visible scope from the base Query class, but from subclasses only."
  334     end
  335 
  336     user = args.shift || User.current
  337     base = Project.allowed_to_condition(user, view_permission, *args)
  338     scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").
  339       where("#{table_name}.project_id IS NULL OR (#{base})")
  340 
  341     if user.admin?
  342       scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)
  343     elsif user.memberships.any?
  344       scope.where(
  345         "#{table_name}.visibility = ?" +
  346           " OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +
  347           "SELECT DISTINCT q.id FROM #{table_name} q" +
  348           " INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +
  349           " INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +
  350           " INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +
  351           " INNER JOIN #{Project.table_name} p ON p.id = m.project_id AND p.status <> ?" +
  352           " WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
  353           " OR #{table_name}.user_id = ?",
  354         VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, Project::STATUS_ARCHIVED, user.id)
  355     elsif user.logged?
  356       scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)
  357     else
  358       scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)
  359     end
  360   end
  361 
  362   # Returns true if the query is visible to +user+ or the current user.
  363   def visible?(user=User.current)
  364     return true if user.admin?
  365     return false unless project.nil? || user.allowed_to?(self.class.view_permission, project)
  366     case visibility
  367     when VISIBILITY_PUBLIC
  368       true
  369     when VISIBILITY_ROLES
  370       if project
  371         (user.roles_for_project(project) & roles).any?
  372       else
  373         user.memberships.joins(:member_roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
  374       end
  375     else
  376       user == self.user
  377     end
  378   end
  379 
  380   def is_private?
  381     visibility == VISIBILITY_PRIVATE
  382   end
  383 
  384   def is_public?
  385     !is_private?
  386   end
  387 
  388   # Returns true if the query is available for all projects
  389   def is_global?
  390     new_record? ? project_id.nil? : project_id_in_database.nil?
  391   end
  392 
  393   def queried_table_name
  394     @queried_table_name ||= self.class.queried_class.table_name
  395   end
  396 
  397   # Builds the query from the given params
  398   def build_from_params(params, defaults={})
  399     if params[:fields] || params[:f]
  400       self.filters = {}
  401       add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
  402     else
  403       available_filters.each_key do |field|
  404         add_short_filter(field, params[field]) if params[field]
  405       end
  406     end
  407 
  408     query_params = params[:query] || defaults || {}
  409     self.group_by = params[:group_by] || query_params[:group_by] || self.group_by
  410     self.column_names = params[:c] || query_params[:column_names] || self.column_names
  411     self.totalable_names = params[:t] || query_params[:totalable_names] || self.totalable_names
  412     self.sort_criteria = params[:sort] || query_params[:sort_criteria] || self.sort_criteria
  413     self.display_type = params[:display_type] || query_params[:display_type] || self.display_type
  414     self
  415   end
  416 
  417   # Builds a new query from the given params and attributes
  418   def self.build_from_params(params, attributes={})
  419     new(attributes).build_from_params(params)
  420   end
  421 
  422   def as_params
  423     if new_record?
  424       params = {}
  425       filters.each do |field, options|
  426         params[:f] ||= []
  427         params[:f] << field
  428         params[:op] ||= {}
  429         params[:op][field] = options[:operator]
  430         params[:v] ||= {}
  431         params[:v][field] = options[:values]
  432       end
  433       params[:c] = column_names
  434       params[:group_by] = group_by.to_s if group_by.present?
  435       params[:t] = totalable_names.map(&:to_s) if totalable_names.any?
  436       params[:sort] = sort_criteria.to_param
  437       params[:set_filter] = 1
  438       params
  439     else
  440       {:query_id => id}
  441     end
  442   end
  443 
  444   def validate_query_filters
  445     filters.each_key do |field|
  446       if values_for(field)
  447         case type_for(field)
  448         when :integer
  449           add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !/\A[+-]?\d+(,[+-]?\d+)*\z/.match?(v) }
  450         when :float
  451           add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !/\A[+-]?\d+(\.\d*)?\z/.match?(v) }
  452         when :date, :date_past
  453           case operator_for(field)
  454           when "=", ">=", "<=", "><"
  455             add_filter_error(field, :invalid) if values_for(field).detect {|v|
  456               v.present? && (!/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){0,2}(Z|\d{2}:?\d{2})?)?\z/.match?(v) || parse_date(v).nil?)
  457             }
  458           when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
  459             add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !/^\d+$/.match?(v) }
  460           end
  461         end
  462       end
  463 
  464       add_filter_error(field, :blank) unless
  465           # filter requires one or more values
  466           (values_for(field) and !values_for(field).first.blank?) or
  467           # filter doesn't require any value
  468           ["o", "c", "!*", "*", "nd", "t", "ld", "nw", "w", "lw", "l2w", "nm", "m", "lm", "y", "*o", "!o"].include? operator_for(field)
  469     end if filters
  470   end
  471 
  472   def add_filter_error(field, message)
  473     m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
  474     errors.add(:base, m)
  475   end
  476 
  477   def editable_by?(user)
  478     return false unless user
  479     # Admin can edit them all and regular users can edit their private queries
  480     return true if user.admin? || (is_private? && self.user_id == user.id)
  481     # Members can not edit public queries that are for all project (only admin is allowed to)
  482     is_public? && !is_global? && user.allowed_to?(:manage_public_queries, project)
  483   end
  484 
  485   def trackers
  486     @trackers ||= (project.nil? ? Tracker.all : project.rolled_up_trackers).visible.sorted
  487   end
  488 
  489   # Returns a hash of localized labels for all filter operators
  490   def self.operators_labels
  491     operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
  492   end
  493 
  494   # Returns a representation of the available filters for JSON serialization
  495   def available_filters_as_json
  496     json = {}
  497     available_filters.each do |field, filter|
  498       options = {:type => filter[:type], :name => filter[:name]}
  499       options[:remote] = true if filter.remote
  500 
  501       if has_filter?(field) || !filter.remote
  502         options[:values] = filter.values
  503         if options[:values] && values_for(field)
  504           missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)
  505           if missing.any? && respond_to?(method = "find_#{field}_filter_values")
  506             options[:values] += send(method, missing)
  507           end
  508         end
  509       end
  510       json[field] = options.stringify_keys
  511     end
  512     json
  513   end
  514 
  515   def all_projects
  516     @all_projects ||= Project.visible.to_a
  517   end
  518 
  519   def all_projects_values
  520     return @all_projects_values if @all_projects_values
  521 
  522     values = []
  523     Project.project_tree(all_projects) do |p, level|
  524       prefix = (level > 0 ? ('--' * level + ' ') : '')
  525       values << ["#{prefix}#{p.name}", p.id.to_s]
  526     end
  527     @all_projects_values = values
  528   end
  529 
  530   def project_values
  531     project_values = []
  532     if User.current.logged?
  533       project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"] if User.current.memberships.any?
  534       project_values << ["<< #{l(:label_my_bookmarks).downcase} >>", "bookmarks"] if User.current.bookmarked_project_ids.any?
  535     end
  536     project_values += all_projects_values
  537     project_values
  538   end
  539 
  540   def subproject_values
  541     project.descendants.visible.collect{|s| [s.name, s.id.to_s] }
  542   end
  543 
  544   def principals
  545     @principal ||= begin
  546       principals = []
  547       if project
  548         principals += Principal.member_of(project).visible
  549         unless project.leaf?
  550           principals += Principal.member_of(project.descendants.visible).visible
  551         end
  552       else
  553         principals += Principal.member_of(all_projects).visible
  554       end
  555       principals.uniq!
  556       principals.sort!
  557       principals.reject! {|p| p.is_a?(GroupBuiltin)}
  558       principals
  559     end
  560   end
  561 
  562   def users
  563     principals.select {|p| p.is_a?(User)}
  564   end
  565 
  566   def author_values
  567     author_values = []
  568     author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
  569     author_values += users.sort_by(&:status).collect{|s| [s.name, s.id.to_s, l("status_#{User::LABEL_BY_STATUS[s.status]}")] }
  570     author_values << [l(:label_user_anonymous), User.anonymous.id.to_s]
  571     author_values
  572   end
  573 
  574   def assigned_to_values
  575     assigned_to_values = []
  576     assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
  577     assigned_to_values += (Setting.issue_group_assignment? ? principals : users).sort_by(&:status).collect{|s| [s.name, s.id.to_s, l("status_#{User::LABEL_BY_STATUS[s.status]}")] }
  578     assigned_to_values
  579   end
  580 
  581   def fixed_version_values
  582     versions = []
  583     if project
  584       versions = project.shared_versions.to_a
  585     else
  586       versions = Version.visible.to_a
  587     end
  588     Version.sort_by_status(versions).collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s, l("version_status_#{s.status}")] }
  589   end
  590 
  591   # Returns a scope of issue statuses that are available as columns for filters
  592   def issue_statuses_values
  593     if project
  594       statuses = project.rolled_up_statuses
  595     else
  596       statuses = IssueStatus.all.sorted
  597     end
  598     statuses.collect{|s| [s.name, s.id.to_s]}
  599   end
  600 
  601   def watcher_values
  602     watcher_values = [["<< #{l(:label_me)} >>", "me"]]
  603     watcher_values += users.sort_by(&:status).collect{|s| [s.name, s.id.to_s, l("status_#{User::LABEL_BY_STATUS[s.status]}")] } if User.current.allowed_to?(:view_issue_watchers, self.project)
  604     watcher_values
  605   end
  606 
  607   # Returns a scope of issue custom fields that are available as columns or filters
  608   def issue_custom_fields
  609     if project
  610       project.rolled_up_custom_fields
  611     else
  612       IssueCustomField.all
  613     end
  614   end
  615 
  616   # Returns a scope of project custom fields that are available as columns or filters
  617   def project_custom_fields
  618     ProjectCustomField.all
  619   end
  620 
  621   # Returns a scope of project statuses that are available as columns or filters
  622   def project_statuses_values
  623     [
  624       [l(:project_status_active), "#{Project::STATUS_ACTIVE}"],
  625       [l(:project_status_closed), "#{Project::STATUS_CLOSED}"]
  626     ]
  627   end
  628 
  629   # Adds available filters
  630   def initialize_available_filters
  631     # implemented by sub-classes
  632   end
  633   protected :initialize_available_filters
  634 
  635   # Adds an available filter
  636   def add_available_filter(field, options)
  637     @available_filters ||= ActiveSupport::OrderedHash.new
  638     @available_filters[field] = QueryFilter.new(field, options)
  639     @available_filters
  640   end
  641 
  642   # Removes an available filter
  643   def delete_available_filter(field)
  644     if @available_filters
  645       @available_filters.delete(field)
  646     end
  647   end
  648 
  649   # Return a hash of available filters
  650   def available_filters
  651     unless @available_filters
  652       initialize_available_filters
  653       @available_filters ||= {}
  654     end
  655     @available_filters
  656   end
  657 
  658   def add_filter(field, operator, values=nil)
  659     # values must be an array
  660     return unless values.nil? || values.is_a?(Array)
  661     # check if field is defined as an available filter
  662     if available_filters.has_key? field
  663       filters[field] = {:operator => operator, :values => (values || [''])}
  664     end
  665   end
  666 
  667   def add_short_filter(field, expression)
  668     return unless expression && available_filters.has_key?(field)
  669     field_type = available_filters[field][:type]
  670     operators_by_filter_type[field_type].sort.reverse.detect do |operator|
  671       next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
  672       values = $1
  673       add_filter field, operator, values.present? ? values.split('|') : ['']
  674     end || add_filter(field, '=', expression.to_s.split('|'))
  675   end
  676 
  677   # Add multiple filters using +add_filter+
  678   def add_filters(fields, operators, values)
  679     if fields.present? && operators.present?
  680       fields.each do |field|
  681         add_filter(field, operators[field], values && values[field])
  682       end
  683     end
  684   end
  685 
  686   def has_filter?(field)
  687     filters and filters[field]
  688   end
  689 
  690   def type_for(field)
  691     available_filters[field][:type] if available_filters.has_key?(field)
  692   end
  693 
  694   def operator_for(field)
  695     has_filter?(field) ? filters[field][:operator] : nil
  696   end
  697 
  698   def values_for(field)
  699     has_filter?(field) ? filters[field][:values] : nil
  700   end
  701 
  702   def value_for(field, index=0)
  703     (values_for(field) || [])[index]
  704   end
  705 
  706   def label_for(field)
  707     label = available_filters[field][:name] if available_filters.has_key?(field)
  708     label ||= queried_class.human_attribute_name(field, :default => field)
  709   end
  710 
  711   def self.add_available_column(column)
  712     self.available_columns << (column) if column.is_a?(QueryColumn)
  713   end
  714 
  715   # Returns an array of columns that can be used to group the results
  716   def groupable_columns
  717     available_columns.select {|c| c.groupable}
  718   end
  719 
  720   # Returns a Hash of columns and the key for sorting
  721   def sortable_columns
  722     available_columns.inject({}) {|h, column|
  723       h[column.name.to_s] = column.sortable
  724       h
  725     }
  726   end
  727 
  728   def columns
  729     return [] if available_columns.empty?
  730     # preserve the column_names order
  731     cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
  732        available_columns.find { |col| col.name == name }
  733     end.compact
  734     available_columns.select(&:frozen?) | cols
  735   end
  736 
  737   def inline_columns
  738     columns.select(&:inline?)
  739   end
  740 
  741   def block_columns
  742     columns.reject(&:inline?)
  743   end
  744 
  745   def available_inline_columns
  746     available_columns.select(&:inline?)
  747   end
  748 
  749   def available_block_columns
  750     available_columns.reject(&:inline?)
  751   end
  752 
  753   def available_totalable_columns
  754     available_columns.select(&:totalable)
  755   end
  756 
  757   def default_columns_names
  758     []
  759   end
  760 
  761   def default_totalable_names
  762     []
  763   end
  764 
  765   def column_names=(names)
  766     if names
  767       names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
  768       names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
  769       if names.delete(:all_inline)
  770         names = available_inline_columns.map(&:name) | names
  771       end
  772       # Set column_names to nil if default columns
  773       if names == default_columns_names
  774         names = nil
  775       end
  776     end
  777     write_attribute(:column_names, names)
  778   end
  779 
  780   def has_column?(column)
  781     name = column.is_a?(QueryColumn) ? column.name : column
  782     columns.detect {|c| c.name == name}
  783   end
  784 
  785   def has_custom_field_column?
  786     columns.any? {|column| column.is_a? QueryCustomFieldColumn}
  787   end
  788 
  789   def has_default_columns?
  790     column_names.nil? || column_names.empty?
  791   end
  792 
  793   def totalable_columns
  794     names = totalable_names
  795     available_totalable_columns.select {|column| names.include?(column.name)}
  796   end
  797 
  798   def totalable_names=(names)
  799     if names
  800       names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}
  801     end
  802     options[:totalable_names] = names
  803   end
  804 
  805   def totalable_names
  806     options[:totalable_names] || default_totalable_names || []
  807   end
  808 
  809   def default_sort_criteria
  810     []
  811   end
  812 
  813   def sort_criteria=(arg)
  814     c = Redmine::SortCriteria.new(arg)
  815     write_attribute(:sort_criteria, c.to_a)
  816     c
  817   end
  818 
  819   def sort_criteria
  820     c = read_attribute(:sort_criteria)
  821     if c.blank?
  822       c = default_sort_criteria
  823     end
  824     Redmine::SortCriteria.new(c)
  825   end
  826 
  827   def sort_criteria_key(index)
  828     sort_criteria[index].try(:first)
  829   end
  830 
  831   def sort_criteria_order(index)
  832     sort_criteria[index].try(:last)
  833   end
  834 
  835   def sort_clause
  836     if clause = sort_criteria.sort_clause(sortable_columns)
  837       clause.map {|c| Arel.sql c}
  838     end
  839   end
  840 
  841   # Returns the SQL sort order that should be prepended for grouping
  842   def group_by_sort_order
  843     if column = group_by_column
  844       order = (sort_criteria.order_for(column.name) || column.default_order || 'asc').try(:upcase)
  845 
  846       column_sortable = column.sortable
  847       if column.is_a?(TimestampQueryColumn)
  848         column_sortable = Redmine::Database.timestamp_to_date(column.sortable, User.current.time_zone)
  849       end
  850       Array(column_sortable).map {|s| Arel.sql("#{s} #{order}")}
  851     end
  852   end
  853 
  854   # Returns true if the query is a grouped query
  855   def grouped?
  856     !group_by_column.nil?
  857   end
  858 
  859   def group_by_column
  860     groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
  861   end
  862 
  863   def group_by_statement
  864     group_by_column.try(:groupable)
  865   end
  866 
  867   def project_statement
  868     project_clauses = []
  869     active_subprojects_ids = []
  870 
  871     active_subprojects_ids = project.descendants.active.map(&:id) if project
  872     if active_subprojects_ids.any?
  873       if has_filter?("subproject_id")
  874         case operator_for("subproject_id")
  875         when '='
  876           # include the selected subprojects
  877           ids = [project.id] + values_for("subproject_id").map(&:to_i)
  878           project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
  879         when '!'
  880           # exclude the selected subprojects
  881           ids = [project.id] + active_subprojects_ids - values_for("subproject_id").map(&:to_i)
  882           project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
  883         when '!*'
  884           # main project only
  885           project_clauses << "#{Project.table_name}.id = %d" % project.id
  886         else
  887           # all subprojects
  888           project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
  889         end
  890       elsif Setting.display_subprojects_issues?
  891         project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
  892       else
  893         project_clauses << "#{Project.table_name}.id = %d" % project.id
  894       end
  895     elsif project
  896       project_clauses << "#{Project.table_name}.id = %d" % project.id
  897     end
  898     project_clauses.any? ? project_clauses.join(' AND ') : nil
  899   end
  900 
  901   def statement
  902     # filters clauses
  903     filters_clauses = []
  904     filters.each_key do |field|
  905       next if field == "subproject_id"
  906       v = values_for(field).clone
  907       next unless v and !v.empty?
  908       operator = operator_for(field)
  909 
  910       # "me" value substitution
  911       if %w(assigned_to_id author_id user_id watcher_id updated_by last_updated_by).include?(field)
  912         if v.delete("me")
  913           if User.current.logged?
  914             v.push(User.current.id.to_s)
  915             v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
  916           else
  917             v.push("0")
  918           end
  919         end
  920       end
  921 
  922       if field == 'project_id' || (self.type == 'ProjectQuery' && %w[id parent_id].include?(field))
  923         if v.delete('mine')
  924           v += User.current.memberships.map(&:project_id).map(&:to_s)
  925         end
  926         if v.delete('bookmarks')
  927           v += User.current.bookmarked_project_ids
  928         end
  929       end
  930 
  931       if field =~ /^cf_(\d+)\.cf_(\d+)$/
  932         filters_clauses << sql_for_chained_custom_field(field, operator, v, $1, $2)
  933       elsif field =~ /cf_(\d+)$/
  934         # custom field
  935         filters_clauses << sql_for_custom_field(field, operator, v, $1)
  936       elsif field =~ /^cf_(\d+)\.(.+)$/
  937         filters_clauses << sql_for_custom_field_attribute(field, operator, v, $1, $2)
  938       elsif respond_to?(method = "sql_for_#{field.tr('.','_')}_field")
  939         # specific statement
  940         filters_clauses << send(method, field, operator, v)
  941       else
  942         # regular field
  943         filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
  944       end
  945     end if filters and valid?
  946 
  947     if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
  948       # Excludes results for which the grouped custom field is not visible
  949       filters_clauses << c.custom_field.visibility_by_project_condition
  950     end
  951 
  952     filters_clauses << project_statement
  953     filters_clauses.reject!(&:blank?)
  954 
  955     filters_clauses.any? ? filters_clauses.join(' AND ') : nil
  956   end
  957 
  958   # Returns the result count by group or nil if query is not grouped
  959   def result_count_by_group
  960     grouped_query do |scope|
  961       scope.count
  962     end
  963   end
  964 
  965   # Returns the sum of values for the given column
  966   def total_for(column)
  967     total_with_scope(column, base_scope)
  968   end
  969 
  970   # Returns a hash of the sum of the given column for each group,
  971   # or nil if the query is not grouped
  972   def total_by_group_for(column)
  973     grouped_query do |scope|
  974       total_with_scope(column, scope)
  975     end
  976   end
  977 
  978   def totals
  979     totals = totalable_columns.map {|column| [column, total_for(column)]}
  980     yield totals if block_given?
  981     totals
  982   end
  983 
  984   def totals_by_group
  985     totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
  986     yield totals if block_given?
  987     totals
  988   end
  989 
  990   def css_classes
  991     s = sort_criteria.first
  992     if s.present?
  993       key, asc = s
  994       "sort-by-#{key.to_s.dasherize} sort-#{asc}"
  995     end
  996   end
  997 
  998   def display_type
  999     options[:display_type] || self.available_display_types.first
 1000   end
 1001 
 1002   def display_type=(type)
 1003     unless type || self.available_display_types.include?(type)
 1004       type = self.available_display_types.first
 1005     end
 1006     options[:display_type] = type
 1007   end
 1008 
 1009   def available_display_types
 1010     ['list']
 1011   end
 1012 
 1013   private
 1014 
 1015   def grouped_query(&block)
 1016     r = nil
 1017     if grouped?
 1018       r = yield base_group_scope
 1019       c = group_by_column
 1020       if c.is_a?(QueryCustomFieldColumn)
 1021         r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
 1022       end
 1023     end
 1024     r
 1025   rescue ::ActiveRecord::StatementInvalid => e
 1026     raise StatementInvalid.new(e.message)
 1027   end
 1028 
 1029   def total_with_scope(column, scope)
 1030     unless column.is_a?(QueryColumn)
 1031       column = column.to_sym
 1032       column = available_totalable_columns.detect {|c| c.name == column}
 1033     end
 1034     if column.is_a?(QueryCustomFieldColumn)
 1035       custom_field = column.custom_field
 1036       send "total_for_custom_field", custom_field, scope
 1037     else
 1038       send "total_for_#{column.name}", scope
 1039     end
 1040   rescue ::ActiveRecord::StatementInvalid => e
 1041     raise StatementInvalid.new(e.message)
 1042   end
 1043 
 1044   def base_scope
 1045     raise "unimplemented"
 1046   end
 1047 
 1048   def base_group_scope
 1049     base_scope.
 1050       joins(joins_for_order_statement(group_by_statement)).
 1051       group(group_by_statement)
 1052   end
 1053 
 1054   def total_for_custom_field(custom_field, scope, &block)
 1055     total = custom_field.format.total_for_scope(custom_field, scope)
 1056     total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
 1057     total
 1058   end
 1059 
 1060   def map_total(total, &block)
 1061     if total.is_a?(Hash)
 1062       total.each_key {|k| total[k] = yield total[k]}
 1063     else
 1064       total = yield total
 1065     end
 1066     total
 1067   end
 1068 
 1069   def sql_for_custom_field(field, operator, value, custom_field_id)
 1070     db_table = CustomValue.table_name
 1071     db_field = 'value'
 1072     filter = @available_filters[field]
 1073     return nil unless filter
 1074     if filter[:field].format.target_class && filter[:field].format.target_class <= User
 1075       if value.delete('me')
 1076         value.push User.current.id.to_s
 1077       end
 1078     end
 1079     not_in = nil
 1080     if operator == '!'
 1081       # Makes ! operator work for custom fields with multiple values
 1082       operator = '='
 1083       not_in = 'NOT'
 1084     end
 1085     customized_key = "id"
 1086     customized_class = queried_class
 1087     if field =~ /^(.+)\.cf_/
 1088       assoc = $1
 1089       customized_key = "#{assoc}_id"
 1090       customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
 1091       raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
 1092     end
 1093     where = sql_for_field(field, operator, value, db_table, db_field, true)
 1094     if /[<>]/.match?(operator)
 1095       where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
 1096     end
 1097     "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
 1098       "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
 1099       " LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id}" +
 1100       " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
 1101   end
 1102 
 1103   def sql_for_chained_custom_field(field, operator, value, custom_field_id, chained_custom_field_id)
 1104     not_in = nil
 1105     if operator == '!'
 1106       # Makes ! operator work for custom fields with multiple values
 1107       operator = '='
 1108       not_in = 'NOT'
 1109     end
 1110 
 1111     filter = available_filters[field]
 1112     target_class = filter[:through].format.target_class
 1113 
 1114     "#{queried_table_name}.id #{not_in} IN (" +
 1115       "SELECT customized_id FROM #{CustomValue.table_name}" +
 1116       " WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +
 1117       "  AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +
 1118       "  SELECT customized_id FROM #{CustomValue.table_name}" +
 1119       "  WHERE customized_type='#{target_class}' AND custom_field_id=#{chained_custom_field_id}" +
 1120       "  AND #{sql_for_field(field, operator, value, CustomValue.table_name, 'value')}))"
 1121 
 1122   end
 1123 
 1124   def sql_for_custom_field_attribute(field, operator, value, custom_field_id, attribute)
 1125     attribute = 'effective_date' if attribute == 'due_date'
 1126     not_in = nil
 1127     if operator == '!'
 1128       # Makes ! operator work for custom fields with multiple values
 1129       operator = '='
 1130       not_in = 'NOT'
 1131     end
 1132 
 1133     filter = available_filters[field]
 1134     target_table_name = filter[:field].format.target_class.table_name
 1135 
 1136     "#{queried_table_name}.id #{not_in} IN (" +
 1137       "SELECT customized_id FROM #{CustomValue.table_name}" +
 1138       " WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +
 1139       "  AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +
 1140       "  SELECT id FROM #{target_table_name} WHERE #{sql_for_field(field, operator, value, filter[:field].format.target_class.table_name, attribute)}))"
 1141   end
 1142 
 1143   # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
 1144   def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
 1145     sql = ''
 1146     case operator
 1147     when "="
 1148       if value.any?
 1149         case type_for(field)
 1150         when :date, :date_past
 1151           sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
 1152         when :integer
 1153           int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")
 1154           if int_values.present?
 1155             if is_custom_filter
 1156               sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) IN (#{int_values}))"
 1157             else
 1158               sql = "#{db_table}.#{db_field} IN (#{int_values})"
 1159             end
 1160           else
 1161             sql = "1=0"
 1162           end
 1163         when :float
 1164           if is_custom_filter
 1165             sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
 1166           else
 1167             sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
 1168           end
 1169         else
 1170           sql = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])
 1171         end
 1172       else
 1173         # IN an empty set
 1174         sql = "1=0"
 1175       end
 1176     when "!"
 1177       if value.any?
 1178         sql = queried_class.send(:sanitize_sql_for_conditions, ["(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (?))", value])
 1179       else
 1180         # NOT IN an empty set
 1181         sql = "1=1"
 1182       end
 1183     when "!*"
 1184       sql = "#{db_table}.#{db_field} IS NULL"
 1185       sql += " OR #{db_table}.#{db_field} = ''" if is_custom_filter || [:text, :string].include?(type_for(field))
 1186     when "*"
 1187       sql = "#{db_table}.#{db_field} IS NOT NULL"
 1188       sql += " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
 1189     when ">="
 1190       if [:date, :date_past].include?(type_for(field))
 1191         sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
 1192       else
 1193         if is_custom_filter
 1194           sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) >= #{value.first.to_f})"
 1195         else
 1196           sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
 1197         end
 1198       end
 1199     when "<="
 1200       if [:date, :date_past].include?(type_for(field))
 1201         sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
 1202       else
 1203         if is_custom_filter
 1204           sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) <= #{value.first.to_f})"
 1205         else
 1206           sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
 1207         end
 1208       end
 1209     when "><"
 1210       if [:date, :date_past].include?(type_for(field))
 1211         sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
 1212       else
 1213         if is_custom_filter
 1214           sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
 1215         else
 1216           sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
 1217         end
 1218       end
 1219     when "o"
 1220       sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false})" if field == "status_id"
 1221     when "c"
 1222       sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_true})" if field == "status_id"
 1223     when "><t-"
 1224       # between today - n days and today
 1225       sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
 1226     when ">t-"
 1227       # >= today - n days
 1228       sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
 1229     when "<t-"
 1230       # <= today - n days
 1231       sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
 1232     when "t-"
 1233       # = n days in past
 1234       sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
 1235     when "><t+"
 1236       # between today and today + n days
 1237       sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
 1238     when ">t+"
 1239       # >= today + n days
 1240       sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
 1241     when "<t+"
 1242       # <= today + n days
 1243       sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, is_custom_filter)
 1244     when "t+"
 1245       # = today + n days
 1246       sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
 1247     when "t"
 1248       # = today
 1249       sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
 1250     when "ld"
 1251       # = yesterday
 1252       sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
 1253     when "nd"
 1254       # = tomorrow
 1255       sql = relative_date_clause(db_table, db_field, 1, 1, is_custom_filter)
 1256     when "w"
 1257       # = this week
 1258       first_day_of_week = l(:general_first_day_of_week).to_i
 1259       day_of_week = User.current.today.cwday
 1260       days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
 1261       sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
 1262     when "lw"
 1263       # = last week
 1264       first_day_of_week = l(:general_first_day_of_week).to_i
 1265       day_of_week = User.current.today.cwday
 1266       days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
 1267       sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
 1268     when "l2w"
 1269       # = last 2 weeks
 1270       first_day_of_week = l(:general_first_day_of_week).to_i
 1271       day_of_week = User.current.today.cwday
 1272       days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
 1273       sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
 1274     when "nw"
 1275       # = next week
 1276       first_day_of_week = l(:general_first_day_of_week).to_i
 1277       day_of_week = User.current.today.cwday
 1278       from = -(day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week) + 7
 1279       sql = relative_date_clause(db_table, db_field, from, from + 6, is_custom_filter)
 1280     when "m"
 1281       # = this month
 1282       date = User.current.today
 1283       sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
 1284     when "lm"
 1285       # = last month
 1286       date = User.current.today.prev_month
 1287       sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
 1288     when "nm"
 1289       # = next month
 1290       date = User.current.today.next_month
 1291       sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
 1292     when "y"
 1293       # = this year
 1294       date = User.current.today
 1295       sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
 1296     when "~"
 1297       sql = sql_contains("#{db_table}.#{db_field}", value.first)
 1298     when "!~"
 1299       sql = sql_contains("#{db_table}.#{db_field}", value.first, :match => false)
 1300     when "^"
 1301       sql = sql_contains("#{db_table}.#{db_field}", value.first, :starts_with => true)
 1302     when "$"
 1303       sql = sql_contains("#{db_table}.#{db_field}", value.first, :ends_with => true)
 1304     else
 1305       raise "Unknown query operator #{operator}"
 1306     end
 1307 
 1308     return sql
 1309   end
 1310 
 1311   # Returns a SQL LIKE statement with wildcards
 1312   def sql_contains(db_field, value, options={})
 1313     options = {} unless options.is_a?(Hash)
 1314     options.symbolize_keys!
 1315     prefix = suffix = nil
 1316     prefix = '%' if options[:ends_with]
 1317     suffix = '%' if options[:starts_with]
 1318     prefix = suffix = '%' if prefix.nil? && suffix.nil?
 1319     queried_class.send(
 1320       :sanitize_sql_for_conditions,
 1321       [Redmine::Database.like(db_field, '?', :match => options[:match]), "#{prefix}#{value}#{suffix}"])
 1322   end
 1323 
 1324   # Adds a filter for the given custom field
 1325   def add_custom_field_filter(field, assoc=nil)
 1326     options = field.query_filter_options(self)
 1327 
 1328     filter_id = "cf_#{field.id}"
 1329     filter_name = field.name
 1330     if assoc.present?
 1331       filter_id = "#{assoc}.#{filter_id}"
 1332       filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
 1333     end
 1334     add_available_filter filter_id, options.merge({
 1335       :name => filter_name,
 1336       :field => field
 1337     })
 1338   end
 1339 
 1340   # Adds filters for custom fields associated to the custom field target class
 1341   # Eg. having a version custom field "Milestone" for issues and a date custom field "Release date"
 1342   # for versions, it will add an issue filter on Milestone'e Release date.
 1343   def add_chained_custom_field_filters(field)
 1344     klass = field.format.target_class
 1345     if klass
 1346       CustomField.where(:is_filter => true, :type => "#{klass.name}CustomField").each do |chained|
 1347         options = chained.query_filter_options(self)
 1348 
 1349         filter_id = "cf_#{field.id}.cf_#{chained.id}"
 1350         filter_name = chained.name
 1351 
 1352         add_available_filter filter_id, options.merge({
 1353           :name => l(:label_attribute_of_object, :name => chained.name, :object_name => field.name),
 1354           :field => chained,
 1355           :through => field
 1356         })
 1357       end
 1358     end
 1359   end
 1360 
 1361   # Adds filters for the given custom fields scope
 1362   def add_custom_fields_filters(scope, assoc=nil)
 1363     scope.visible.where(:is_filter => true).sorted.each do |field|
 1364       add_custom_field_filter(field, assoc)
 1365       if assoc.nil?
 1366         add_chained_custom_field_filters(field)
 1367         if field.format.target_class && field.format.target_class == Version
 1368           add_available_filter(
 1369             "cf_#{field.id}.due_date",
 1370             :type => :date,
 1371             :field => field,
 1372             :name => l(:label_attribute_of_object, :name => l(:field_effective_date), :object_name => field.name))
 1373           add_available_filter(
 1374             "cf_#{field.id}.status",
 1375             :type => :list,
 1376             :field => field,
 1377             :name => l(:label_attribute_of_object, :name => l(:field_status), :object_name => field.name),
 1378             :values => Version::VERSION_STATUSES.map{|s| [l("version_status_#{s}"), s]})
 1379         end
 1380       end
 1381     end
 1382   end
 1383 
 1384   # Adds filters for the given associations custom fields
 1385   def add_associations_custom_fields_filters(*associations)
 1386     fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
 1387     associations.each do |assoc|
 1388       association_klass = queried_class.reflect_on_association(assoc).klass
 1389       fields_by_class.each do |field_class, fields|
 1390         if field_class.customized_class <= association_klass
 1391           fields.sort.each do |field|
 1392             add_custom_field_filter(field, assoc)
 1393           end
 1394         end
 1395       end
 1396     end
 1397   end
 1398 
 1399   def quoted_time(time, is_custom_filter)
 1400     if is_custom_filter
 1401       # Custom field values are stored as strings in the DB
 1402       # using this format that does not depend on DB date representation
 1403       time.strftime("%Y-%m-%d %H:%M:%S")
 1404     else
 1405       self.class.connection.quoted_date(time)
 1406     end
 1407   end
 1408 
 1409   def date_for_user_time_zone(y, m, d)
 1410     if tz = User.current.time_zone
 1411       tz.local y, m, d
 1412     else
 1413       Time.local y, m, d
 1414     end
 1415   end
 1416 
 1417   # Returns a SQL clause for a date or datetime field.
 1418   def date_clause(table, field, from, to, is_custom_filter)
 1419     s = []
 1420     if from
 1421       if from.is_a?(Date)
 1422         from = date_for_user_time_zone(from.year, from.month, from.day).yesterday.end_of_day
 1423       else
 1424         from = from - 1 # second
 1425       end
 1426       if self.class.default_timezone == :utc
 1427         from = from.utc
 1428       end
 1429       s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
 1430     end
 1431     if to
 1432       if to.is_a?(Date)
 1433         to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day
 1434       end
 1435       if self.class.default_timezone == :utc
 1436         to = to.utc
 1437       end
 1438       s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
 1439     end
 1440     s.join(' AND ')
 1441   end
 1442 
 1443   # Returns a SQL clause for a date or datetime field using relative dates.
 1444   def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
 1445     date_clause(table, field, (days_from ? User.current.today + days_from : nil), (days_to ? User.current.today + days_to : nil), is_custom_filter)
 1446   end
 1447 
 1448   # Returns a Date or Time from the given filter value
 1449   def parse_date(arg)
 1450     if /\A\d{4}-\d{2}-\d{2}T/.match?(arg.to_s)
 1451       Time.parse(arg) rescue nil
 1452     else
 1453       Date.parse(arg) rescue nil
 1454     end
 1455   end
 1456 
 1457   # Additional joins required for the given sort options
 1458   def joins_for_order_statement(order_options)
 1459     joins = []
 1460 
 1461     if order_options
 1462       order_options.scan(/cf_\d+/).uniq.each do |name|
 1463         column = available_columns.detect {|c| c.name.to_s == name}
 1464         join = column && column.custom_field.join_for_order_statement
 1465         if join
 1466           joins << join
 1467         end
 1468       end
 1469     end
 1470 
 1471     joins.any? ? joins.join(' ') : nil
 1472   end
 1473 end