jro_apps/redmine Commit - r5172:80b59f3cf454

1

# Redmine - project management software

1

# Redmine - project management software

2

3

#

3

#

4

# This program is free software; you can redistribute it and/or

4

# This program is free software; you can redistribute it and/or

5

# modify it under the terms of the GNU General Public License

5

# modify it under the terms of the GNU General Public License

6

# as published by the Free Software Foundation; either version 2

6

# as published by the Free Software Foundation; either version 2

7

# of the License, or (at your option) any later version.

7

# of the License, or (at your option) any later version.

8

#

8

#

9

# This program is distributed in the hope that it will be useful,

9

# This program is distributed in the hope that it will be useful,

10

# but WITHOUT ANY WARRANTY; without even the implied warranty of

10

# but WITHOUT ANY WARRANTY; without even the implied warranty of

11

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

11

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

12

# GNU General Public License for more details.

12

# GNU General Public License for more details.

13

#

13

#

14

# You should have received a copy of the GNU General Public License

14

# You should have received a copy of the GNU General Public License

15

# along with this program; if not, write to the Free Software

15

# along with this program; if not, write to the Free Software

16

# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.

16

# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.

17

18

class QueryColumn

18

class QueryColumn

19

attr_accessor :name, :sortable, :groupable, :default_order

19

attr_accessor :name, :sortable, :groupable, :default_order

20

include Redmine::I18n

20

include Redmine::I18n

21

22

def initialize(name, options={})

22

def initialize(name, options={})

23

self.name = name

23

self.name = name

24

self.sortable = options[:sortable]

24

self.sortable = options[:sortable]

25

self.groupable = options[:groupable] || false

25

self.groupable = options[:groupable] || false

26

if groupable == true

26

if groupable == true

27

self.groupable = name.to_s

27

self.groupable = name.to_s

28

end

28

end

29

self.default_order = options[:default_order]

29

self.default_order = options[:default_order]

30

@caption_key = options[:caption] || "field_#{name}"

30

@caption_key = options[:caption] || "field_#{name}"

31

end

31

end

32

33

def caption

33

def caption

34

l(@caption_key)

34

l(@caption_key)

35

end

35

end

36

37

# Returns true if the column is sortable, otherwise false

37

# Returns true if the column is sortable, otherwise false

38

def sortable?

38

def sortable?

39

!sortable.nil?

39

!sortable.nil?

40

end

40

end

41

42

def value(issue)

42

def value(issue)

43

issue.send name

43

issue.send name

44

end

44

end

45

end

45

end

46

47

class QueryCustomFieldColumn < QueryColumn

47

class QueryCustomFieldColumn < QueryColumn

48

49

def initialize(custom_field)

49

def initialize(custom_field)

50

self.name = "cf_#{custom_field.id}".to_sym

50

self.name = "cf_#{custom_field.id}".to_sym

51

self.sortable = custom_field.order_statement || false

51

self.sortable = custom_field.order_statement || false

52

if %w(list date bool int).include?(custom_field.field_format)

52

if %w(list date bool int).include?(custom_field.field_format)

53

self.groupable = custom_field.order_statement

53

self.groupable = custom_field.order_statement

54

end

54

end

55

self.groupable ||= false

55

self.groupable ||= false

56

@cf = custom_field

56

@cf = custom_field

57

end

57

end

58

59

def caption

59

def caption

60

@cf.name

60

@cf.name

61

end

61

end

62

63

def custom_field

63

def custom_field

64

@cf

64

@cf

65

end

65

end

66

67

def value(issue)

67

def value(issue)

68

cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}

68

cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}

69

cv && @cf.cast_value(cv.value)

69

cv && @cf.cast_value(cv.value)

70

end

70

end

71

end

71

end

72

73

class Query < ActiveRecord::Base

73

class Query < ActiveRecord::Base

74

class StatementInvalid < ::ActiveRecord::StatementInvalid

74

class StatementInvalid < ::ActiveRecord::StatementInvalid

75

end

75

end

76

77

belongs_to :project

77

belongs_to :project

78

belongs_to :user

78

belongs_to :user

79

serialize :filters

79

serialize :filters

80

serialize :column_names

80

serialize :column_names

81

serialize :sort_criteria, Array

81

serialize :sort_criteria, Array

82

83

attr_protected :project_id, :user_id

83

attr_protected :project_id, :user_id

84

85

validates_presence_of :name, :on => :save

85

validates_presence_of :name, :on => :save

86

validates_length_of :name, :maximum => 255

86

validates_length_of :name, :maximum => 255

87

88

@@operators = { "=" => :label_equals,

88

@@operators = { "=" => :label_equals,

89

"!" => :label_not_equals,

89

"!" => :label_not_equals,

90

"o" => :label_open_issues,

90

"o" => :label_open_issues,

91

"c" => :label_closed_issues,

91

"c" => :label_closed_issues,

92

"!*" => :label_none,

92

"!*" => :label_none,

93

"*" => :label_all,

93

"*" => :label_all,

94

">=" => :label_greater_or_equal,

94

">=" => :label_greater_or_equal,

95

"<=" => :label_less_or_equal,

95

"<=" => :label_less_or_equal,

96

"<t+" => :label_in_less_than,

96

"<t+" => :label_in_less_than,

97

">t+" => :label_in_more_than,

97

">t+" => :label_in_more_than,

98

"t+" => :label_in,

98

"t+" => :label_in,

99

"t" => :label_today,

99

"t" => :label_today,

100

"w" => :label_this_week,

100

"w" => :label_this_week,

101

">t-" => :label_less_than_ago,

101

">t-" => :label_less_than_ago,

102

"<t-" => :label_more_than_ago,

102

"<t-" => :label_more_than_ago,

103

"t-" => :label_ago,

103

"t-" => :label_ago,

104

"~" => :label_contains,

104

"~" => :label_contains,

105

"!~" => :label_not_contains }

105

"!~" => :label_not_contains }

106

107

cattr_reader :operators

107

cattr_reader :operators

108

109

@@operators_by_filter_type = { :list => [ "=", "!" ],

109

@@operators_by_filter_type = { :list => [ "=", "!" ],

110

:list_status => [ "o", "=", "!", "c", "*" ],

110

:list_status => [ "o", "=", "!", "c", "*" ],

111

:list_optional => [ "=", "!", "!*", "*" ],

111

:list_optional => [ "=", "!", "!*", "*" ],

112

:list_subprojects => [ "*", "!*", "=" ],

112

:list_subprojects => [ "*", "!*", "=" ],

113

:date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],

113

:date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],

114

:date_past => [ ">t-", "<t-", "t-", "t", "w" ],

114

:date_past => [ ">t-", "<t-", "t-", "t", "w" ],

115

:string => [ "=", "~", "!", "!~" ],

115

:string => [ "=", "~", "!", "!~" ],

116

:text => [ "~", "!~" ],

116

:text => [ "~", "!~" ],

117

:integer => [ "=", ">=", "<=", "!*", "*" ] }

117

:integer => [ "=", ">=", "<=", "!*", "*" ] }

118

119

cattr_reader :operators_by_filter_type

119

cattr_reader :operators_by_filter_type

120

121

@@available_columns = [

121

@@available_columns = [

122

QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),

122

QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),

123

QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),

123

QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),

124

QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),

124

QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),

125

QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),

125

QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),

126

QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),

126

QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),

127

QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),

127

QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),

128

QueryColumn.new(:author),

128

QueryColumn.new(:author),

129

QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),

129

QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),

130

QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),

130

QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),

131

QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),

131

QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),

132

QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),

132

QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),

133

QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),

133

QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),

134

QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),

134

QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),

135

QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),

135

QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),

136

QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),

136

QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),

137

QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),

137

QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),

138

]

138

]

139

cattr_reader :available_columns

139

cattr_reader :available_columns

140

141

def initialize(attributes = nil)

141

def initialize(attributes = nil)

142

super attributes

142

super attributes

143

self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }

143

self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }

144

end

144

end

145

146

def after_initialize

146

def after_initialize

147

# Store the fact that project is nil (used in #editable_by?)

147

# Store the fact that project is nil (used in #editable_by?)

148

@is_for_all = project.nil?

148

@is_for_all = project.nil?

149

end

149

end

150

151

def validate

151

def validate

152

filters.each_key do |field|

152

filters.each_key do |field|

153

errors.add label_for(field), :blank unless

153

errors.add label_for(field), :blank unless

154

# filter requires one or more values

154

# filter requires one or more values

155

(values_for(field) and !values_for(field).first.blank?) or

155

(values_for(field) and !values_for(field).first.blank?) or

156

# filter doesn't require any value

156

# filter doesn't require any value

157

["o", "c", "!*", "*", "t", "w"].include? operator_for(field)

157

["o", "c", "!*", "*", "t", "w"].include? operator_for(field)

158

end if filters

158

end if filters

159

end

159

end

160

161

def editable_by?(user)

161

def editable_by?(user)

162

return false unless user

162

return false unless user

163

# Admin can edit them all and regular users can edit their private queries

163

# Admin can edit them all and regular users can edit their private queries

164

return true if user.admin? || (!is_public && self.user_id == user.id)

164

return true if user.admin? || (!is_public && self.user_id == user.id)

165

# Members can not edit public queries that are for all project (only admin is allowed to)

165

# Members can not edit public queries that are for all project (only admin is allowed to)

166

is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)

166

is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)

167

end

167

end

168

169

def available_filters

169

def available_filters

170

return @available_filters if @available_filters

170

return @available_filters if @available_filters

171

172

trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers

172

trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers

173

174

@available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },

174

@available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },

175

"tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },

175

"tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },

176

"priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },

176

"priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },

177

"subject" => { :type => :text, :order => 8 },

177

"subject" => { :type => :text, :order => 8 },

178

"created_on" => { :type => :date_past, :order => 9 },

178

"created_on" => { :type => :date_past, :order => 9 },

179

"updated_on" => { :type => :date_past, :order => 10 },

179

"updated_on" => { :type => :date_past, :order => 10 },

180

"start_date" => { :type => :date, :order => 11 },

180

"start_date" => { :type => :date, :order => 11 },

181

"due_date" => { :type => :date, :order => 12 },

181

"due_date" => { :type => :date, :order => 12 },

182

"estimated_hours" => { :type => :integer, :order => 13 },

182

"estimated_hours" => { :type => :integer, :order => 13 },

183

"done_ratio" => { :type => :integer, :order => 14 }}

183

"done_ratio" => { :type => :integer, :order => 14 }}

184

185

user_values = []

185

user_values = []

186

user_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?

186

user_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?

187

if project

187

if project

188

user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }

188

user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }

189

else

189

else

190

all_projects = Project.visible.all

190

all_projects = Project.visible.all

191

if all_projects.any?

191

if all_projects.any?

192

# members of visible projects

192

# members of visible projects

193

user_values += User.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", all_projects.collect(&:id)]).sort.collect{|s| [s.name, s.id.to_s] }

193

user_values += User.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", all_projects.collect(&:id)]).sort.collect{|s| [s.name, s.id.to_s] }

194

195

# project filter

195

# project filter

196

project_values = []

196

project_values = []

197

Project.project_tree(all_projects) do |p, level|

197

Project.project_tree(all_projects) do |p, level|

198

prefix = (level > 0 ? ('--' * level + ' ') : '')

198

prefix = (level > 0 ? ('--' * level + ' ') : '')

199

project_values << ["#{prefix}#{p.name}", p.id.to_s]

199

project_values << ["#{prefix}#{p.name}", p.id.to_s]

200

end

200

end

201

@available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?

201

@available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?

202

end

202

end

203

end

203

end

204

@available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?

204

@available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?

205

@available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?

205

@available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?

206

207

group_values = Group.all.collect {|g| [g.name, g.id.to_s] }

207

group_values = Group.all.collect {|g| [g.name, g.id.to_s] }

208

@available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?

208

@available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?

209

210

role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }

210

role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }

211

@available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?

211

@available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?

212

213

if User.current.logged?

213

if User.current.logged?

214

@available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }

214

@available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }

215

end

215

end

216

217

if project

217

if project

218

# project specific filters

218

# project specific filters

219

~~unless~~ @project.issue_categories.~~empty?~~

219

categories = @project.issue_categories.all

220

@available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => @project.issue_categories.collect{|s| [s.name, s.id.to_s] } }

220

unless categories.empty?

221

@available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }

221

end

222

end

222

~~unless~~ @project.shared_versions.~~empty?~~

223

versions = @project.shared_versions.all

223

@available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => @project.shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }

224

unless versions.empty?

225

@available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }

226

end

227

unless @project.leaf?

228

subprojects = @project.descendants.visible.all

229

unless subprojects.empty?

230

@available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }

224

end

231

end

225

unless @project.descendants.active.empty?

226

@available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => @project.descendants.visible.collect{|s| [s.name, s.id.to_s] } }

227

end

232

end

228

add_custom_fields_filters(@project.all_issue_custom_fields)

233

add_custom_fields_filters(@project.all_issue_custom_fields)

229

else

234

else

230

# global filters for cross project issue list

235

# global filters for cross project issue list

231

system_shared_versions = Version.visible.find_all_by_sharing('system')

236

system_shared_versions = Version.visible.find_all_by_sharing('system')

232

unless system_shared_versions.empty?

237

unless system_shared_versions.empty?

233

@available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }

238

@available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }

234

end

239

end

235

add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))

240

add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))

236

end

241

end

237

@available_filters

242

@available_filters

238

end

243

end

239

244

240

def add_filter(field, operator, values)

245

def add_filter(field, operator, values)

241

# values must be an array

246

# values must be an array

242

return unless values and values.is_a? Array # and !values.first.empty?

247

return unless values and values.is_a? Array # and !values.first.empty?

243

# check if field is defined as an available filter

248

# check if field is defined as an available filter

244

if available_filters.has_key? field

249

if available_filters.has_key? field

245

filter_options = available_filters[field]

250

filter_options = available_filters[field]

246

# check if operator is allowed for that filter

251

# check if operator is allowed for that filter

247

#if @@operators_by_filter_type[filter_options[:type]].include? operator

252

#if @@operators_by_filter_type[filter_options[:type]].include? operator

248

# allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})

253

# allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})

249

# filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator

254

# filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator

250

#end

255

#end

251

filters[field] = {:operator => operator, :values => values }

256

filters[field] = {:operator => operator, :values => values }

252

end

257

end

253

end

258

end

254

259

255

def add_short_filter(field, expression)

260

def add_short_filter(field, expression)

256

return unless expression

261

return unless expression

257

parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first

262

parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first

258

add_filter field, (parms[0] || "="), [parms[1] || ""]

263

add_filter field, (parms[0] || "="), [parms[1] || ""]

259

end

264

end

260

265

261

# Add multiple filters using +add_filter+

266

# Add multiple filters using +add_filter+

262

def add_filters(fields, operators, values)

267

def add_filters(fields, operators, values)

263

if fields.is_a?(Array) && operators.is_a?(Hash) && values.is_a?(Hash)

268

if fields.is_a?(Array) && operators.is_a?(Hash) && values.is_a?(Hash)

264

fields.each do |field|

269

fields.each do |field|

265

add_filter(field, operators[field], values[field])

270

add_filter(field, operators[field], values[field])

266

end

271

end

267

end

272

end

268

end

273

end

269

274

270

def has_filter?(field)

275

def has_filter?(field)

271

filters and filters[field]

276

filters and filters[field]

272

end

277

end

273

278

274

def operator_for(field)

279

def operator_for(field)

275

has_filter?(field) ? filters[field][:operator] : nil

280

has_filter?(field) ? filters[field][:operator] : nil

276

end

281

end

277

282

278

def values_for(field)

283

def values_for(field)

279

has_filter?(field) ? filters[field][:values] : nil

284

has_filter?(field) ? filters[field][:values] : nil

280

end

285

end

281

286

282

def label_for(field)

287

def label_for(field)

283

label = available_filters[field][:name] if available_filters.has_key?(field)

288

label = available_filters[field][:name] if available_filters.has_key?(field)

284

label ||= field.gsub(/\_id$/, "")

289

label ||= field.gsub(/\_id$/, "")

285

end

290

end

286

291

287

def available_columns

292

def available_columns

288

return @available_columns if @available_columns

293

return @available_columns if @available_columns

289

@available_columns = Query.available_columns

294

@available_columns = Query.available_columns

290

@available_columns += (project ?

295

@available_columns += (project ?

291

project.all_issue_custom_fields :

296

project.all_issue_custom_fields :

292

IssueCustomField.find(:all)

297

IssueCustomField.find(:all)

293

).collect {|cf| QueryCustomFieldColumn.new(cf) }

298

).collect {|cf| QueryCustomFieldColumn.new(cf) }

294

end

299

end

295

300

296

def self.available_columns=(v)

301

def self.available_columns=(v)

297

self.available_columns = (v)

302

self.available_columns = (v)

298

end

303

end

299

304

300

def self.add_available_column(column)

305

def self.add_available_column(column)

301

self.available_columns << (column) if column.is_a?(QueryColumn)

306

self.available_columns << (column) if column.is_a?(QueryColumn)

302

end

307

end

303

308

304

# Returns an array of columns that can be used to group the results

309

# Returns an array of columns that can be used to group the results

305

def groupable_columns

310

def groupable_columns

306

available_columns.select {|c| c.groupable}

311

available_columns.select {|c| c.groupable}

307

end

312

end

308

313

309

# Returns a Hash of columns and the key for sorting

314

# Returns a Hash of columns and the key for sorting

310

def sortable_columns

315

def sortable_columns

311

{'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|

316

{'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|

312

h[column.name.to_s] = column.sortable

317

h[column.name.to_s] = column.sortable

313

h

318

h

314

})

319

})

315

end

320

end

316

321

317

def columns

322

def columns

318

if has_default_columns?

323

if has_default_columns?

319

available_columns.select do |c|

324

available_columns.select do |c|

320

# Adds the project column by default for cross-project lists

325

# Adds the project column by default for cross-project lists

321

Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)

326

Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)

322

end

327

end

323

else

328

else

324

# preserve the column_names order

329

# preserve the column_names order

325

column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact

330

column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact

326

end

331

end

327

end

332

end

328

333

329

def column_names=(names)

334

def column_names=(names)

330

if names

335

if names

331

names = names.select {|n| n.is_a?(Symbol) || !n.blank? }

336

names = names.select {|n| n.is_a?(Symbol) || !n.blank? }

332

names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }

337

names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }

333

# Set column_names to nil if default columns

338

# Set column_names to nil if default columns

334

if names.map(&:to_s) == Setting.issue_list_default_columns

339

if names.map(&:to_s) == Setting.issue_list_default_columns

335

names = nil

340

names = nil

336

end

341

end

337

end

342

end

338

write_attribute(:column_names, names)

343

write_attribute(:column_names, names)

339

end

344

end

340

345

341

def has_column?(column)

346

def has_column?(column)

342

column_names && column_names.include?(column.name)

347

column_names && column_names.include?(column.name)

343

end

348

end

344

349

345

def has_default_columns?

350

def has_default_columns?

346

column_names.nil? || column_names.empty?

351

column_names.nil? || column_names.empty?

347

end

352

end

348

353

349

def sort_criteria=(arg)

354

def sort_criteria=(arg)

350

c = []

355

c = []

351

if arg.is_a?(Hash)

356

if arg.is_a?(Hash)

352

arg = arg.keys.sort.collect {|k| arg[k]}

357

arg = arg.keys.sort.collect {|k| arg[k]}

353

end

358

end

354

c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}

359

c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}

355

write_attribute(:sort_criteria, c)

360

write_attribute(:sort_criteria, c)

356

end

361

end

357

362

358

def sort_criteria

363

def sort_criteria

359

read_attribute(:sort_criteria) || []

364

read_attribute(:sort_criteria) || []

360

end

365

end

361

366

362

def sort_criteria_key(arg)

367

def sort_criteria_key(arg)

363

sort_criteria && sort_criteria[arg] && sort_criteria[arg].first

368

sort_criteria && sort_criteria[arg] && sort_criteria[arg].first

364

end

369

end

365

370

366

def sort_criteria_order(arg)

371

def sort_criteria_order(arg)

367

sort_criteria && sort_criteria[arg] && sort_criteria[arg].last

372

sort_criteria && sort_criteria[arg] && sort_criteria[arg].last

368

end

373

end

369

374

370

# Returns the SQL sort order that should be prepended for grouping

375

# Returns the SQL sort order that should be prepended for grouping

371

def group_by_sort_order

376

def group_by_sort_order

372

if grouped? && (column = group_by_column)

377

if grouped? && (column = group_by_column)

373

column.sortable.is_a?(Array) ?

378

column.sortable.is_a?(Array) ?

374

column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :

379

column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :

375

"#{column.sortable} #{column.default_order}"

380

"#{column.sortable} #{column.default_order}"

376

end

381

end

377

end

382

end

378

383

379

# Returns true if the query is a grouped query

384

# Returns true if the query is a grouped query

380

def grouped?

385

def grouped?

381

!group_by_column.nil?

386

!group_by_column.nil?

382

end

387

end

383

388

384

def group_by_column

389

def group_by_column

385

groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}

390

groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}

386

end

391

end

387

392

388

def group_by_statement

393

def group_by_statement

389

group_by_column.try(:groupable)

394

group_by_column.try(:groupable)

390

end

395

end

391

396

392

def project_statement

397

def project_statement

393

project_clauses = []

398

project_clauses = []

394

if project && !@project.descendants.active.empty?

399

if project && !@project.descendants.active.empty?

395

ids = [project.id]

400

ids = [project.id]

396

if has_filter?("subproject_id")

401

if has_filter?("subproject_id")

397

case operator_for("subproject_id")

402

case operator_for("subproject_id")

398

when '='

403

when '='

399

# include the selected subprojects

404

# include the selected subprojects

400

ids += values_for("subproject_id").each(&:to_i)

405

ids += values_for("subproject_id").each(&:to_i)

401

when '!*'

406

when '!*'

402

# main project only

407

# main project only

403

else

408

else

404

# all subprojects

409

# all subprojects

405

ids += project.descendants.collect(&:id)

410

ids += project.descendants.collect(&:id)

406

end

411

end

407

elsif Setting.display_subprojects_issues?

412

elsif Setting.display_subprojects_issues?

408

ids += project.descendants.collect(&:id)

413

ids += project.descendants.collect(&:id)

409

end

414

end

410

project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')

415

project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')

411

elsif project

416

elsif project

412

project_clauses << "#{Project.table_name}.id = %d" % project.id

417

project_clauses << "#{Project.table_name}.id = %d" % project.id

413

end

418

end

414

project_clauses << Issue.visible_condition(User.current)

419

project_clauses << Issue.visible_condition(User.current)

415

project_clauses.join(' AND ')

420

project_clauses.join(' AND ')

416

end

421

end

417

422

418

def statement

423

def statement

419

# filters clauses

424

# filters clauses

420

filters_clauses = []

425

filters_clauses = []

421

filters.each_key do |field|

426

filters.each_key do |field|

422

next if field == "subproject_id"

427

next if field == "subproject_id"

423

v = values_for(field).clone

428

v = values_for(field).clone

424

next unless v and !v.empty?

429

next unless v and !v.empty?

425

operator = operator_for(field)

430

operator = operator_for(field)

426

431

427

# "me" value subsitution

432

# "me" value subsitution

428

if %w(assigned_to_id author_id watcher_id).include?(field)

433

if %w(assigned_to_id author_id watcher_id).include?(field)

429

v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")

434

v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")

430

end

435

end

431

436

432

sql = ''

437

sql = ''

433

if field =~ /^cf_(\d+)$/

438

if field =~ /^cf_(\d+)$/

434

# custom field

439

# custom field

435

db_table = CustomValue.table_name

440

db_table = CustomValue.table_name

436

db_field = 'value'

441

db_field = 'value'

437

is_custom_filter = true

442

is_custom_filter = true

438

sql << "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} WHERE "

443

sql << "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} WHERE "

439

sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'

444

sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'

440

elsif field == 'watcher_id'

445

elsif field == 'watcher_id'

441

db_table = Watcher.table_name

446

db_table = Watcher.table_name

442

db_field = 'user_id'

447

db_field = 'user_id'

443

sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND "

448

sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND "

444

sql << sql_for_field(field, '=', v, db_table, db_field) + ')'

449

sql << sql_for_field(field, '=', v, db_table, db_field) + ')'

445

elsif field == "member_of_group" # named field

450

elsif field == "member_of_group" # named field

446

if operator == '*' # Any group

451

if operator == '*' # Any group

447

groups = Group.all

452

groups = Group.all

448

operator = '=' # Override the operator since we want to find by assigned_to

453

operator = '=' # Override the operator since we want to find by assigned_to

449

elsif operator == "!*"

454

elsif operator == "!*"

450

groups = Group.all

455

groups = Group.all

451

operator = '!' # Override the operator since we want to find by assigned_to

456

operator = '!' # Override the operator since we want to find by assigned_to

452

else

457

else

453

groups = Group.find_all_by_id(v)

458

groups = Group.find_all_by_id(v)

454

end

459

end

455

groups ||= []

460

groups ||= []

456

461

457

members_of_groups = groups.inject([]) {|user_ids, group|

462

members_of_groups = groups.inject([]) {|user_ids, group|

458

if group && group.user_ids.present?

463

if group && group.user_ids.present?

459

user_ids << group.user_ids

464

user_ids << group.user_ids

460

end

465

end

461

user_ids.flatten.uniq.compact

466

user_ids.flatten.uniq.compact

462

}.sort.collect(&:to_s)

467

}.sort.collect(&:to_s)

463

468

464

sql << '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'

469

sql << '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'

465

470

466

elsif field == "assigned_to_role" # named field

471

elsif field == "assigned_to_role" # named field

467

if operator == "*" # Any Role

472

if operator == "*" # Any Role

468

roles = Role.givable

473

roles = Role.givable

469

operator = '=' # Override the operator since we want to find by assigned_to

474

operator = '=' # Override the operator since we want to find by assigned_to

470

elsif operator == "!*" # No role

475

elsif operator == "!*" # No role

471

roles = Role.givable

476

roles = Role.givable

472

operator = '!' # Override the operator since we want to find by assigned_to

477

operator = '!' # Override the operator since we want to find by assigned_to

473

else

478

else

474

roles = Role.givable.find_all_by_id(v)

479

roles = Role.givable.find_all_by_id(v)

475

end

480

end

476

roles ||= []

481

roles ||= []

477

482

478

members_of_roles = roles.inject([]) {|user_ids, role|

483

members_of_roles = roles.inject([]) {|user_ids, role|

479

if role && role.members

484

if role && role.members

480

user_ids << role.members.collect(&:user_id)

485

user_ids << role.members.collect(&:user_id)

481

end

486

end

482

user_ids.flatten.uniq.compact

487

user_ids.flatten.uniq.compact

483

}.sort.collect(&:to_s)

488

}.sort.collect(&:to_s)

484

489

485

sql << '(' + sql_for_field("assigned_to_id", operator, members_of_roles, Issue.table_name, "assigned_to_id", false) + ')'

490

sql << '(' + sql_for_field("assigned_to_id", operator, members_of_roles, Issue.table_name, "assigned_to_id", false) + ')'

486

else

491

else

487

# regular field

492

# regular field

488

db_table = Issue.table_name

493

db_table = Issue.table_name

489

db_field = field

494

db_field = field

490

sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'

495

sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'

491

end

496

end

492

filters_clauses << sql

497

filters_clauses << sql

493

498

494

end if filters and valid?

499

end if filters and valid?

495

500

496

(filters_clauses << project_statement).join(' AND ')

501

(filters_clauses << project_statement).join(' AND ')

497

end

502

end

498

503

499

# Returns the issue count

504

# Returns the issue count

500

def issue_count

505

def issue_count

501

Issue.count(:include => [:status, :project], :conditions => statement)

506

Issue.count(:include => [:status, :project], :conditions => statement)

502

rescue ::ActiveRecord::StatementInvalid => e

507

rescue ::ActiveRecord::StatementInvalid => e

503

raise StatementInvalid.new(e.message)

508

raise StatementInvalid.new(e.message)

504

end

509

end

505

510

506

# Returns the issue count by group or nil if query is not grouped

511

# Returns the issue count by group or nil if query is not grouped

507

def issue_count_by_group

512

def issue_count_by_group

508

r = nil

513

r = nil

509

if grouped?

514

if grouped?

510

begin

515

begin

511

# Rails will raise an (unexpected) RecordNotFound if there's only a nil group value

516

# Rails will raise an (unexpected) RecordNotFound if there's only a nil group value

512

r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)

517

r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)

513

rescue ActiveRecord::RecordNotFound

518

rescue ActiveRecord::RecordNotFound

514

r = {nil => issue_count}

519

r = {nil => issue_count}

515

end

520

end

516

c = group_by_column

521

c = group_by_column

517

if c.is_a?(QueryCustomFieldColumn)

522

if c.is_a?(QueryCustomFieldColumn)

518

r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}

523

r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}

519

end

524

end

520

end

525

end

521

r

526

r

522

rescue ::ActiveRecord::StatementInvalid => e

527

rescue ::ActiveRecord::StatementInvalid => e

523

raise StatementInvalid.new(e.message)

528

raise StatementInvalid.new(e.message)

524

end

529

end

525

530

526

# Returns the issues

531

# Returns the issues

527

# Valid options are :order, :offset, :limit, :include, :conditions

532

# Valid options are :order, :offset, :limit, :include, :conditions

528

def issues(options={})

533

def issues(options={})

529

order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')

534

order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')

530

order_option = nil if order_option.blank?

535

order_option = nil if order_option.blank?

531

536

532

Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,

537

Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,

533

:conditions => Query.merge_conditions(statement, options[:conditions]),

538

:conditions => Query.merge_conditions(statement, options[:conditions]),

534

:order => order_option,

539

:order => order_option,

535

:limit => options[:limit],

540

:limit => options[:limit],

536

:offset => options[:offset]

541

:offset => options[:offset]

537

rescue ::ActiveRecord::StatementInvalid => e

542

rescue ::ActiveRecord::StatementInvalid => e

538

raise StatementInvalid.new(e.message)

543

raise StatementInvalid.new(e.message)

539

end

544

end

540

545

541

# Returns the journals

546

# Returns the journals

542

# Valid options are :order, :offset, :limit

547

# Valid options are :order, :offset, :limit

543

def journals(options={})

548

def journals(options={})

544

Journal.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],

549

Journal.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],

545

:conditions => statement,

550

:conditions => statement,

546

:order => options[:order],

551

:order => options[:order],

547

:limit => options[:limit],

552

:limit => options[:limit],

548

:offset => options[:offset]

553

:offset => options[:offset]

549

rescue ::ActiveRecord::StatementInvalid => e

554

rescue ::ActiveRecord::StatementInvalid => e

550

raise StatementInvalid.new(e.message)

555

raise StatementInvalid.new(e.message)

551

end

556

end

552

557

553

# Returns the versions

558

# Returns the versions

554

# Valid options are :conditions

559

# Valid options are :conditions

555

def versions(options={})

560

def versions(options={})

556

Version.find :all, :include => :project,

561

Version.find :all, :include => :project,

557

:conditions => Query.merge_conditions(project_statement, options[:conditions])

562

:conditions => Query.merge_conditions(project_statement, options[:conditions])

558

rescue ::ActiveRecord::StatementInvalid => e

563

rescue ::ActiveRecord::StatementInvalid => e

559

raise StatementInvalid.new(e.message)

564

raise StatementInvalid.new(e.message)

560

end

565

end

561

566

562

private

567

private

563

568

564

# Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+

569

# Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+

565

def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)

570

def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)

566

sql = ''

571

sql = ''

567

case operator

572

case operator

568

when "="

573

when "="

569

if value.any?

574

if value.any?

570

sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"

575

sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"

571

else

576

else

572

# IN an empty set

577

# IN an empty set

573

sql = "1=0"

578

sql = "1=0"

574

end

579

end

575

when "!"

580

when "!"

576

if value.any?

581

if value.any?

577

sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"

582

sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"

578

else

583

else

579

# NOT IN an empty set

584

# NOT IN an empty set

580

sql = "1=1"

585

sql = "1=1"

581

end

586

end

582

when "!*"

587

when "!*"

583

sql = "#{db_table}.#{db_field} IS NULL"

588

sql = "#{db_table}.#{db_field} IS NULL"

584

sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter

589

sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter

585

when "*"

590

when "*"

586

sql = "#{db_table}.#{db_field} IS NOT NULL"

591

sql = "#{db_table}.#{db_field} IS NOT NULL"

587

sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter

592

sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter

588

when ">="

593

when ">="

589

sql = "#{db_table}.#{db_field} >= #{value.first.to_i}"

594

sql = "#{db_table}.#{db_field} >= #{value.first.to_i}"

590

when "<="

595

when "<="

591

sql = "#{db_table}.#{db_field} <= #{value.first.to_i}"

596

sql = "#{db_table}.#{db_field} <= #{value.first.to_i}"

592

when "o"

597

when "o"

593

sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"

598

sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"

594

when "c"

599

when "c"

595

sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"

600

sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"

596

when ">t-"

601

when ">t-"

597

sql = date_range_clause(db_table, db_field, - value.first.to_i, 0)

602

sql = date_range_clause(db_table, db_field, - value.first.to_i, 0)

598

when "<t-"

603

when "<t-"

599

sql = date_range_clause(db_table, db_field, nil, - value.first.to_i)

604

sql = date_range_clause(db_table, db_field, nil, - value.first.to_i)

600

when "t-"

605

when "t-"

601

sql = date_range_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)

606

sql = date_range_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)

602

when ">t+"

607

when ">t+"

603

sql = date_range_clause(db_table, db_field, value.first.to_i, nil)

608

sql = date_range_clause(db_table, db_field, value.first.to_i, nil)

604

when "<t+"

609

when "<t+"

605

sql = date_range_clause(db_table, db_field, 0, value.first.to_i)

610

sql = date_range_clause(db_table, db_field, 0, value.first.to_i)

606

when "t+"

611

when "t+"

607

sql = date_range_clause(db_table, db_field, value.first.to_i, value.first.to_i)

612

sql = date_range_clause(db_table, db_field, value.first.to_i, value.first.to_i)

608

when "t"

613

when "t"

609

sql = date_range_clause(db_table, db_field, 0, 0)

614

sql = date_range_clause(db_table, db_field, 0, 0)

610

when "w"

615

when "w"

611

from = l(:general_first_day_of_week) == '7' ?

616

from = l(:general_first_day_of_week) == '7' ?

612

# week starts on sunday

617

# week starts on sunday

613

((Date.today.cwday == 7) ? Time.now.at_beginning_of_day : Time.now.at_beginning_of_week - 1.day) :

618

((Date.today.cwday == 7) ? Time.now.at_beginning_of_day : Time.now.at_beginning_of_week - 1.day) :

614

# week starts on monday (Rails default)

619

# week starts on monday (Rails default)

615

Time.now.at_beginning_of_week

620

Time.now.at_beginning_of_week

616

sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(from), connection.quoted_date(from + 7.days)]

621

sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(from), connection.quoted_date(from + 7.days)]

617

when "~"

622

when "~"

618

sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"

623

sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"

619

when "!~"

624

when "!~"

620

sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"

625

sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"

621

end

626

end

622

627

623

return sql

628

return sql

624

end

629

end

625

630

626

def add_custom_fields_filters(custom_fields)

631

def add_custom_fields_filters(custom_fields)

627

@available_filters ||= {}

632

@available_filters ||= {}

628

633

629

custom_fields.select(&:is_filter?).each do |field|

634

custom_fields.select(&:is_filter?).each do |field|

630

case field.field_format

635

case field.field_format

631

when "text"

636

when "text"

632

options = { :type => :text, :order => 20 }

637

options = { :type => :text, :order => 20 }

633

when "list"

638

when "list"

634

options = { :type => :list_optional, :values => field.possible_values, :order => 20}

639

options = { :type => :list_optional, :values => field.possible_values, :order => 20}

635

when "date"

640

when "date"

636

options = { :type => :date, :order => 20 }

641

options = { :type => :date, :order => 20 }

637

when "bool"

642

when "bool"

638

options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }

643

options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }

639

when "user", "version"

644

when "user", "version"

640

next unless project

645

next unless project

641

options = { :type => :list_optional, :values => field.possible_values_options(project), :order => 20}

646

options = { :type => :list_optional, :values => field.possible_values_options(project), :order => 20}

642

else

647

else

643

options = { :type => :string, :order => 20 }

648

options = { :type => :string, :order => 20 }

644

end

649

end

645

@available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })

650

@available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })

646

end

651

end

647

end

652

end

648

653

649

# Returns a SQL clause for a date or datetime field.

654

# Returns a SQL clause for a date or datetime field.

650

def date_range_clause(table, field, from, to)

655

def date_range_clause(table, field, from, to)

651

s = []

656

s = []

652

if from

657

if from

653

s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)])

658

s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)])

654

end

659

end

655

if to

660

if to

656

s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)])

661

s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)])

657

end

662

end

658

s.join(' AND ')

663

s.join(' AND ')

659

end

664

end

660

end

665

end

	Site-wide shortcuts
/	Use quick search box
g h	Goto home page
g g	Goto my private gists page
g G	Goto my public gists page
g 0-9	Goto bookmarked items from 0-9
n r	New repository page
n g	New gist page

	Repositories
g s	Goto summary page
g c	Goto changelog page
g f	Goto files page
g F	Goto files page with file search activated
g p	Goto pull requests page
g o	Goto repository settings
g O	Goto repository access permissions settings
t s	Toggle sidebar on some pages

             # Redmine - project management software
             # Copyright (C) 2006-2011  Jean-Philippe Lang
             #
             # This program is free software; you can redistribute it and/or
             # modify it under the terms of the GNU General Public License
             # as published by the Free Software Foundation; either version 2
             # of the License, or (at your option) any later version.
             #
             # This program is distributed in the hope that it will be useful,
             # but WITHOUT ANY WARRANTY; without even the implied warranty of
             # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
             # GNU General Public License for more details.
             #
             # You should have received a copy of the GNU General Public License
             # along with this program; if not, write to the Free Software
             # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
             class QueryColumn
               attr_accessor :name, :sortable, :groupable, :default_order
               include Redmine::I18n
               def initialize(name, options={})
                 self.name = name
                 self.sortable = options[:sortable]
                 self.groupable = options[:groupable] || false
                 if groupable == true
                   self.groupable = name.to_s
                 end
                 self.default_order = options[:default_order]
                 @caption_key = options[:caption] || "field_#{name}"
               end
               def caption
                 l(@caption_key)
               end
               # Returns true if the column is sortable, otherwise false
               def sortable?
                 !sortable.nil?
               end
               def value(issue)
                 issue.send name
               end
             end
             class QueryCustomFieldColumn < QueryColumn
               def initialize(custom_field)
                 self.name = "cf_#{custom_field.id}".to_sym
                 self.sortable = custom_field.order_statement || false
                 if %w(list date bool int).include?(custom_field.field_format)
                   self.groupable = custom_field.order_statement
                 end
                 self.groupable ||= false
                 @cf = custom_field
               end
               def caption
                 @cf.name
               end
               def custom_field
                 @cf
               end
               def value(issue)
                 cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}
                 cv && @cf.cast_value(cv.value)
               end
             end
             class Query < ActiveRecord::Base
               class StatementInvalid < ::ActiveRecord::StatementInvalid
               end
               belongs_to :project
               belongs_to :user
               serialize :filters
               serialize :column_names
               serialize :sort_criteria, Array
               attr_protected :project_id, :user_id
               validates_presence_of :name, :on => :save
               validates_length_of :name, :maximum => 255
               @@operators = { "="   => :label_equals,
                               "!"   => :label_not_equals,
                               "o"   => :label_open_issues,
                               "c"   => :label_closed_issues,
                               "!*"  => :label_none,
                               "*"   => :label_all,
                               ">="  => :label_greater_or_equal,
                               "<="  => :label_less_or_equal,
                               "<t+" => :label_in_less_than,
                               ">t+" => :label_in_more_than,
                               "t+"  => :label_in,
                               "t"   => :label_today,
                               "w"   => :label_this_week,
                               ">t-" => :label_less_than_ago,
                               "<t-" => :label_more_than_ago,
                               "t-"  => :label_ago,
                               "~"   => :label_contains,
                               "!~"  => :label_not_contains }
               cattr_reader :operators
               @@operators_by_filter_type = { :list => [ "=", "!" ],
                                              :list_status => [ "o", "=", "!", "c", "*" ],
                                              :list_optional => [ "=", "!", "!*", "*" ],
                                              :list_subprojects => [ "*", "!*", "=" ],
                                              :date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],
                                              :date_past => [ ">t-", "<t-", "t-", "t", "w" ],
                                              :string => [ "=", "~", "!", "!~" ],
                                              :text => [  "~", "!~" ],
                                              :integer => [ "=", ">=", "<=", "!*", "*" ] }
               cattr_reader :operators_by_filter_type
               @@available_columns = [
                 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
                 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
                 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
                 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
                 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
                 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
                 QueryColumn.new(:author),
                 QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),
                 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
                 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
                 QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),
                 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
                 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
                 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
                 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
                 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
               ]
               cattr_reader :available_columns
               def initialize(attributes = nil)
                 super attributes
                 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
               end
               def after_initialize
                 # Store the fact that project is nil (used in #editable_by?)
                 @is_for_all = project.nil?
               end
               def validate
                 filters.each_key do |field|
                   errors.add label_for(field), :blank unless
                       # filter requires one or more values
                       (values_for(field) and !values_for(field).first.blank?) or
                       # filter doesn't require any value
                       ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
                 end if filters
               end
               def editable_by?(user)
                 return false unless user
                 # Admin can edit them all and regular users can edit their private queries
                 return true if user.admin? || (!is_public && self.user_id == user.id)
                 # Members can not edit public queries that are for all project (only admin is allowed to)
                 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
               end
               def available_filters
                 return @available_filters if @available_filters
                 trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
                 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
                                        "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
                                        "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
                                        "subject" => { :type => :text, :order => 8 },
                                        "created_on" => { :type => :date_past, :order => 9 },
                                        "updated_on" => { :type => :date_past, :order => 10 },
                                        "start_date" => { :type => :date, :order => 11 },
                                        "due_date" => { :type => :date, :order => 12 },
                                        "estimated_hours" => { :type => :integer, :order => 13 },
                                        "done_ratio" =>  { :type => :integer, :order => 14 }}
                 user_values = []
                 user_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
                 if project
                   user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }
                 else
                   all_projects = Project.visible.all
                   if all_projects.any?
                     # members of visible projects
                     user_values += User.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", all_projects.collect(&:id)]).sort.collect{|s| [s.name, s.id.to_s] }
                     # project filter
                     project_values = []
                     Project.project_tree(all_projects) do |p, level|
                       prefix = (level > 0 ? ('--' * level + ' ') : '')
                       project_values << ["#{prefix}#{p.name}", p.id.to_s]
                     end
                     @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
                   end
                 end
                 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?
                 @available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?
                 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
                 @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?
                 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
                 @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?
                 if User.current.logged?
                   @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
                 end
                 if project
                   # project specific filters
-                  unless @project.issue_categories.empty?
+                  categories = @project.issue_categories.all
-                    @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => @project.issue_categories.collect{|s| [s.name, s.id.to_s] } }
+                  unless categories.empty?
+                    @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }
                   end
-                  unless @project.shared_versions.empty?
+                  versions = @project.shared_versions.all
-                    @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => @project.shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
+                  unless versions.empty?
+                    @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
+                  end
+                  unless @project.leaf?
+                    subprojects = @project.descendants.visible.all
+                    unless subprojects.empty?
+                      @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }
                     end
-                  unless @project.descendants.active.empty?
-                    @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => @project.descendants.visible.collect{|s| [s.name, s.id.to_s] } }
                   end
                   add_custom_fields_filters(@project.all_issue_custom_fields)
                 else
                   # global filters for cross project issue list
                   system_shared_versions = Version.visible.find_all_by_sharing('system')
                   unless system_shared_versions.empty?
                     @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
                   end
                   add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
                 end
                 @available_filters
               end
               def add_filter(field, operator, values)
                 # values must be an array
                 return unless values and values.is_a? Array # and !values.first.empty?
                 # check if field is defined as an available filter
                 if available_filters.has_key? field
                   filter_options = available_filters[field]
                   # check if operator is allowed for that filter
                   #if @@operators_by_filter_type[filter_options[:type]].include? operator
                   #  allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
                   #  filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
                   #end
                   filters[field] = {:operator => operator, :values => values }
                 end
               end
               def add_short_filter(field, expression)
                 return unless expression
                 parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first
                 add_filter field, (parms[0] || "="), [parms[1] || ""]
               end
               # Add multiple filters using +add_filter+
               def add_filters(fields, operators, values)
                 if fields.is_a?(Array) && operators.is_a?(Hash) && values.is_a?(Hash)
                   fields.each do |field|
                     add_filter(field, operators[field], values[field])
                   end
                 end
               end
               def has_filter?(field)
                 filters and filters[field]
               end
               def operator_for(field)
                 has_filter?(field) ? filters[field][:operator] : nil
               end
               def values_for(field)
                 has_filter?(field) ? filters[field][:values] : nil
               end
               def label_for(field)
                 label = available_filters[field][:name] if available_filters.has_key?(field)
                 label ||= field.gsub(/\_id$/, "")
               end
               def available_columns
                 return @available_columns if @available_columns
                 @available_columns = Query.available_columns
                 @available_columns += (project ?
                                         project.all_issue_custom_fields :
                                         IssueCustomField.find(:all)
                                        ).collect {|cf| QueryCustomFieldColumn.new(cf) }
               end
               def self.available_columns=(v)
                 self.available_columns = (v)
               end
               def self.add_available_column(column)
                 self.available_columns << (column) if column.is_a?(QueryColumn)
               end
               # Returns an array of columns that can be used to group the results
               def groupable_columns
                 available_columns.select {|c| c.groupable}
               end
               # Returns a Hash of columns and the key for sorting
               def sortable_columns
                 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
                                                            h[column.name.to_s] = column.sortable
                                                            h
                                                          })
               end
               def columns
                 if has_default_columns?
                   available_columns.select do |c|
                     # Adds the project column by default for cross-project lists
                     Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)
                   end
                 else
                   # preserve the column_names order
                   column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact
                 end
               end
               def column_names=(names)
                 if names
                   names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
                   names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
                   # Set column_names to nil if default columns
                   if names.map(&:to_s) == Setting.issue_list_default_columns
                     names = nil
                   end
                 end
                 write_attribute(:column_names, names)
               end
               def has_column?(column)
                 column_names && column_names.include?(column.name)
               end
               def has_default_columns?
                 column_names.nil? || column_names.empty?
               end
               def sort_criteria=(arg)
                 c = []
                 if arg.is_a?(Hash)
                   arg = arg.keys.sort.collect {|k| arg[k]}
                 end
                 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
                 write_attribute(:sort_criteria, c)
               end
               def sort_criteria
                 read_attribute(:sort_criteria) || []
               end
               def sort_criteria_key(arg)
                 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
               end
               def sort_criteria_order(arg)
                 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
               end
               # Returns the SQL sort order that should be prepended for grouping
               def group_by_sort_order
                 if grouped? && (column = group_by_column)
                   column.sortable.is_a?(Array) ?
                     column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
                     "#{column.sortable} #{column.default_order}"
                 end
               end
               # Returns true if the query is a grouped query
               def grouped?
                 !group_by_column.nil?
               end
               def group_by_column
                 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
               end
               def group_by_statement
                 group_by_column.try(:groupable)
               end
               def project_statement
                 project_clauses = []
                 if project && !@project.descendants.active.empty?
                   ids = [project.id]
                   if has_filter?("subproject_id")
                     case operator_for("subproject_id")
                     when '='
                       # include the selected subprojects
                       ids += values_for("subproject_id").each(&:to_i)
                     when '!*'
                       # main project only
                     else
                       # all subprojects
                       ids += project.descendants.collect(&:id)
                     end
                   elsif Setting.display_subprojects_issues?
                     ids += project.descendants.collect(&:id)
                   end
                   project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
                 elsif project
                   project_clauses << "#{Project.table_name}.id = %d" % project.id
                 end
                 project_clauses <<  Issue.visible_condition(User.current)
                 project_clauses.join(' AND ')
               end
               def statement
                 # filters clauses
                 filters_clauses = []
                 filters.each_key do |field|
                   next if field == "subproject_id"
                   v = values_for(field).clone
                   next unless v and !v.empty?
                   operator = operator_for(field)
                   # "me" value subsitution
                   if %w(assigned_to_id author_id watcher_id).include?(field)
                     v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")
                   end
                   sql = ''
                   if field =~ /^cf_(\d+)$/
                     # custom field
                     db_table = CustomValue.table_name
                     db_field = 'value'
                     is_custom_filter = true
                     sql << "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} WHERE "
                     sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'
                   elsif field == 'watcher_id'
                     db_table = Watcher.table_name
                     db_field = 'user_id'
                     sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND "
                     sql << sql_for_field(field, '=', v, db_table, db_field) + ')'
                   elsif field == "member_of_group" # named field
                     if operator == '*' # Any group
                       groups = Group.all
                       operator = '=' # Override the operator since we want to find by assigned_to
                     elsif operator == "!*"
                       groups = Group.all
                       operator = '!' # Override the operator since we want to find by assigned_to
                     else
                       groups = Group.find_all_by_id(v)
                     end
                     groups ||= []
                     members_of_groups = groups.inject([]) {|user_ids, group|
                       if group && group.user_ids.present?
                         user_ids << group.user_ids
                       end
                       user_ids.flatten.uniq.compact
                     }.sort.collect(&:to_s)
                     sql << '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
                   elsif field == "assigned_to_role" # named field
                     if operator == "*" # Any Role
                       roles = Role.givable
                       operator = '=' # Override the operator since we want to find by assigned_to
                     elsif operator == "!*" # No role
                       roles = Role.givable
                       operator = '!' # Override the operator since we want to find by assigned_to
                     else
                       roles = Role.givable.find_all_by_id(v)
                     end
                     roles ||= []
                     members_of_roles = roles.inject([]) {|user_ids, role|
                       if role && role.members
                         user_ids << role.members.collect(&:user_id)
                       end
                       user_ids.flatten.uniq.compact
                     }.sort.collect(&:to_s)
                     sql << '(' + sql_for_field("assigned_to_id", operator, members_of_roles, Issue.table_name, "assigned_to_id", false) + ')'
                   else
                     # regular field
                     db_table = Issue.table_name
                     db_field = field
                     sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'
                   end
                   filters_clauses << sql
                 end if filters and valid?
                 (filters_clauses << project_statement).join(' AND ')
               end
               # Returns the issue count
               def issue_count
                 Issue.count(:include => [:status, :project], :conditions => statement)
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the issue count by group or nil if query is not grouped
               def issue_count_by_group
                 r = nil
                 if grouped?
                   begin
                     # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
                     r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
                   rescue ActiveRecord::RecordNotFound
                     r = {nil => issue_count}
                   end
                   c = group_by_column
                   if c.is_a?(QueryCustomFieldColumn)
                     r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
                   end
                 end
                 r
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the issues
               # Valid options are :order, :offset, :limit, :include, :conditions
               def issues(options={})
                 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
                 order_option = nil if order_option.blank?
                 Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
                                  :conditions => Query.merge_conditions(statement, options[:conditions]),
                                  :order => order_option,
                                  :limit  => options[:limit],
                                  :offset => options[:offset]
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the journals
               # Valid options are :order, :offset, :limit
               def journals(options={})
                 Journal.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
                                    :conditions => statement,
                                    :order => options[:order],
                                    :limit => options[:limit],
                                    :offset => options[:offset]
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the versions
               # Valid options are :conditions
               def versions(options={})
                 Version.find :all, :include => :project,
                                    :conditions => Query.merge_conditions(project_statement, options[:conditions])
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               private
               # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
               def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
                 sql = ''
                 case operator
                 when "="
                   if value.any?
                     sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
                   else
                     # IN an empty set
                     sql = "1=0"
                   end
                 when "!"
                   if value.any?
                     sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
                   else
                     # NOT IN an empty set
                     sql = "1=1"
                   end
                 when "!*"
                   sql = "#{db_table}.#{db_field} IS NULL"
                   sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
                 when "*"
                   sql = "#{db_table}.#{db_field} IS NOT NULL"
                   sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
                 when ">="
                   sql = "#{db_table}.#{db_field} >= #{value.first.to_i}"
                 when "<="
                   sql = "#{db_table}.#{db_field} <= #{value.first.to_i}"
                 when "o"
                   sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"
                 when "c"
                   sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
                 when ">t-"
                   sql = date_range_clause(db_table, db_field, - value.first.to_i, 0)
                 when "<t-"
                   sql = date_range_clause(db_table, db_field, nil, - value.first.to_i)
                 when "t-"
                   sql = date_range_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
                 when ">t+"
                   sql = date_range_clause(db_table, db_field, value.first.to_i, nil)
                 when "<t+"
                   sql = date_range_clause(db_table, db_field, 0, value.first.to_i)
                 when "t+"
                   sql = date_range_clause(db_table, db_field, value.first.to_i, value.first.to_i)
                 when "t"
                   sql = date_range_clause(db_table, db_field, 0, 0)
                 when "w"
                   from = l(:general_first_day_of_week) == '7' ?
                   # week starts on sunday
                   ((Date.today.cwday == 7) ? Time.now.at_beginning_of_day : Time.now.at_beginning_of_week - 1.day) :
                     # week starts on monday (Rails default)
                     Time.now.at_beginning_of_week
                   sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(from), connection.quoted_date(from + 7.days)]
                 when "~"
                   sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
                 when "!~"
                   sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
                 end
                 return sql
               end
               def add_custom_fields_filters(custom_fields)
                 @available_filters ||= {}
                 custom_fields.select(&:is_filter?).each do |field|
                   case field.field_format
                   when "text"
                     options = { :type => :text, :order => 20 }
                   when "list"
                     options = { :type => :list_optional, :values => field.possible_values, :order => 20}
                   when "date"
                     options = { :type => :date, :order => 20 }
                   when "bool"
                     options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
                   when "user", "version"
                     next unless project
                     options = { :type => :list_optional, :values => field.possible_values_options(project), :order => 20}
                   else
                     options = { :type => :string, :order => 20 }
                   end
                   @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
                 end
               end
               # Returns a SQL clause for a date or datetime field.
               def date_range_clause(table, field, from, to)
                 s = []
                 if from
                   s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)])
                 end
                 if to
                   s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)])
                 end
                 s.join(' AND ')
               end
             end