jro_apps/redmine Commit - r12445:dff2721fbe52

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

@inline = options.key?(:inline) ? options[:inline] : true

30

@inline = options.key?(:inline) ? options[:inline] : true

31

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

31

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

32

@frozen = options[:frozen]

32

@frozen = options[:frozen]

33

end

33

end

34

35

def caption

35

def caption

36

@caption_key.is_a?(Symbol) ? l(@caption_key) : @caption_key

36

@caption_key.is_a?(Symbol) ? l(@caption_key) : @caption_key

37

end

37

end

38

39

# Returns true if the column is sortable, otherwise false

39

# Returns true if the column is sortable, otherwise false

40

def sortable?

40

def sortable?

41

!@sortable.nil?

41

!@sortable.nil?

42

end

42

end

43

44

def sortable

44

def sortable

45

@sortable.is_a?(Proc) ? @sortable.call : @sortable

45

@sortable.is_a?(Proc) ? @sortable.call : @sortable

46

end

46

end

47

48

def inline?

48

def inline?

49

@inline

49

@inline

50

end

50

end

51

52

def frozen?

52

def frozen?

53

@frozen

53

@frozen

54

end

54

end

55

56

def value(object)

56

def value(object)

57

object.send name

57

object.send name

58

end

58

end

59

60

def css_classes

60

def css_classes

61

name

61

name

62

end

62

end

63

end

63

end

64

65

class QueryCustomFieldColumn < QueryColumn

65

class QueryCustomFieldColumn < QueryColumn

66

67

def initialize(custom_field)

67

def initialize(custom_field)

68

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

68

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

69

self.sortable = custom_field.order_statement || false

69

self.sortable = custom_field.order_statement || false

70

self.groupable = custom_field.group_statement || false

70

self.groupable = custom_field.group_statement || false

71

@inline = true

71

@inline = true

72

@cf = custom_field

72

@cf = custom_field

73

end

73

end

74

75

def caption

75

def caption

76

@cf.name

76

@cf.name

77

end

77

end

78

79

def custom_field

79

def custom_field

80

@cf

80

@cf

81

end

81

end

82

83

def value(object)

83

def value(object)

84

if custom_field.visible_by?(object.project, User.current)

84

if custom_field.visible_by?(object.project, User.current)

85

cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}

85

cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}

86

cv.size > 1 ? cv.sort {|a,b| a.to_s <=> b.to_s} : cv.first

86

cv.size > 1 ? cv.sort {|a,b| a.to_s <=> b.to_s} : cv.first

87

else

87

else

88

nil

88

nil

89

end

89

end

90

end

90

end

91

92

def css_classes

92

def css_classes

93

@css_classes ||= "#{name} #{@cf.field_format}"

93

@css_classes ||= "#{name} #{@cf.field_format}"

94

end

94

end

95

end

95

end

96

97

class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn

97

class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn

98

99

def initialize(association, custom_field)

99

def initialize(association, custom_field)

100

super(custom_field)

100

super(custom_field)

101

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

101

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

102

# TODO: support sorting/grouping by association custom field

102

# TODO: support sorting/grouping by association custom field

103

self.sortable = false

103

self.sortable = false

104

self.groupable = false

104

self.groupable = false

105

@association = association

105

@association = association

106

end

106

end

107

108

def value(object)

108

def value(object)

109

if assoc = object.send(@association)

109

if assoc = object.send(@association)

110

super(assoc)

110

super(assoc)

111

end

111

end

112

end

112

end

113

114

def css_classes

114

def css_classes

115

@css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"

115

@css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"

116

end

116

end

117

end

117

end

118

119

class Query < ActiveRecord::Base

119

class Query < ActiveRecord::Base

120

class StatementInvalid < ::ActiveRecord::StatementInvalid

120

class StatementInvalid < ::ActiveRecord::StatementInvalid

121

end

121

end

122

123

VISIBILITY_PRIVATE = 0

123

VISIBILITY_PRIVATE = 0

124

VISIBILITY_ROLES = 1

124

VISIBILITY_ROLES = 1

125

VISIBILITY_PUBLIC = 2

125

VISIBILITY_PUBLIC = 2

126

127

belongs_to :project

127

belongs_to :project

128

belongs_to :user

128

belongs_to :user

129

has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"

129

has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"

130

serialize :filters

130

serialize :filters

131

serialize :column_names

131

serialize :column_names

132

serialize :sort_criteria, Array

132

serialize :sort_criteria, Array

133

serialize :options, Hash

133

serialize :options, Hash

134

135

attr_protected :project_id, :user_id

135

attr_protected :project_id, :user_id

136

137

validates_presence_of :name

137

validates_presence_of :name

138

validates_length_of :name, :maximum => 255

138

validates_length_of :name, :maximum => 255

139

validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }

139

validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }

140

validate :validate_query_filters

140

validate :validate_query_filters

141

validate do |query|

141

validate do |query|

142

errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?

142

errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?

143

end

143

end

144

145

after_save do |query|

145

after_save do |query|

146

if query.visibility_changed? && query.visibility != VISIBILITY_ROLES

146

if query.visibility_changed? && query.visibility != VISIBILITY_ROLES

147

query.roles.clear

147

query.roles.clear

148

end

148

end

149

end

149

end

150

151

class_attribute :operators

151

class_attribute :operators

152

self.operators = {

152

self.operators = {

153

"=" => :label_equals,

153

"=" => :label_equals,

154

"!" => :label_not_equals,

154

"!" => :label_not_equals,

155

"o" => :label_open_issues,

155

"o" => :label_open_issues,

156

"c" => :label_closed_issues,

156

"c" => :label_closed_issues,

157

"!*" => :label_none,

157

"!*" => :label_none,

158

"*" => :label_any,

158

"*" => :label_any,

159

">=" => :label_greater_or_equal,

159

">=" => :label_greater_or_equal,

160

"<=" => :label_less_or_equal,

160

"<=" => :label_less_or_equal,

161

"><" => :label_between,

161

"><" => :label_between,

162

"<t+" => :label_in_less_than,

162

"<t+" => :label_in_less_than,

163

">t+" => :label_in_more_than,

163

">t+" => :label_in_more_than,

164

"><t+"=> :label_in_the_next_days,

164

"><t+"=> :label_in_the_next_days,

165

"t+" => :label_in,

165

"t+" => :label_in,

166

"t" => :label_today,

166

"t" => :label_today,

167

"ld" => :label_yesterday,

167

"ld" => :label_yesterday,

168

"w" => :label_this_week,

168

"w" => :label_this_week,

169

"lw" => :label_last_week,

169

"lw" => :label_last_week,

170

"l2w" => [:label_last_n_weeks, {:count => 2}],

170

"l2w" => [:label_last_n_weeks, {:count => 2}],

171

"m" => :label_this_month,

171

"m" => :label_this_month,

172

"lm" => :label_last_month,

172

"lm" => :label_last_month,

173

"y" => :label_this_year,

173

"y" => :label_this_year,

174

">t-" => :label_less_than_ago,

174

">t-" => :label_less_than_ago,

175

"<t-" => :label_more_than_ago,

175

"<t-" => :label_more_than_ago,

176

"><t-"=> :label_in_the_past_days,

176

"><t-"=> :label_in_the_past_days,

177

"t-" => :label_ago,

177

"t-" => :label_ago,

178

"~" => :label_contains,

178

"~" => :label_contains,

179

"!~" => :label_not_contains,

179

"!~" => :label_not_contains,

180

"=p" => :label_any_issues_in_project,

180

"=p" => :label_any_issues_in_project,

181

"=!p" => :label_any_issues_not_in_project,

181

"=!p" => :label_any_issues_not_in_project,

182

"!p" => :label_no_issues_in_project

182

"!p" => :label_no_issues_in_project

183

}

183

}

184

185

class_attribute :operators_by_filter_type

185

class_attribute :operators_by_filter_type

186

self.operators_by_filter_type = {

186

self.operators_by_filter_type = {

187

:list => [ "=", "!" ],

187

:list => [ "=", "!" ],

188

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

188

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

189

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

189

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

190

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

190

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

191

:date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],

191

:date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],

192

:date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],

192

:date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],

193

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

193

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

194

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

194

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

195

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

195

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

196

:float => [ "=", ">=", "<=", "><", "!*", "*" ],

196

:float => [ "=", ">=", "<=", "><", "!*", "*" ],

197

:relation => ["=", "=p", "=!p", "!p", "!*", "*"]

197

:relation => ["=", "=p", "=!p", "!p", "!*", "*"]

198

}

198

}

199

200

class_attribute :available_columns

200

class_attribute :available_columns

201

self.available_columns = []

201

self.available_columns = []

202

203

class_attribute :queried_class

203

class_attribute :queried_class

204

205

def queried_table_name

205

def queried_table_name

206

@queried_table_name ||= self.class.queried_class.table_name

206

@queried_table_name ||= self.class.queried_class.table_name

207

end

207

end

208

209

def initialize(attributes=nil, *args)

209

def initialize(attributes=nil, *args)

210

super attributes

210

super attributes

211

@is_for_all = project.nil?

211

@is_for_all = project.nil?

212

end

212

end

213

214

# Builds the query from the given params

214

# Builds the query from the given params

215

def build_from_params(params)

215

def build_from_params(params)

216

if params[:fields] || params[:f]

216

if params[:fields] || params[:f]

217

self.filters = {}

217

self.filters = {}

218

add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])

218

add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])

219

else

219

else

220

available_filters.keys.each do |field|

220

available_filters.keys.each do |field|

221

add_short_filter(field, params[field]) if params[field]

221

add_short_filter(field, params[field]) if params[field]

222

end

222

end

223

end

223

end

224

self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])

224

self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])

225

self.column_names = params[:c] || (params[:query] && params[:query][:column_names])

225

self.column_names = params[:c] || (params[:query] && params[:query][:column_names])

226

self

226

self

227

end

227

end

228

229

# Builds a new query from the given params and attributes

229

# Builds a new query from the given params and attributes

230

def self.build_from_params(params, attributes={})

230

def self.build_from_params(params, attributes={})

231

new(attributes).build_from_params(params)

231

new(attributes).build_from_params(params)

232

end

232

end

233

234

def validate_query_filters

234

def validate_query_filters

235

filters.each_key do |field|

235

filters.each_key do |field|

236

if values_for(field)

236

if values_for(field)

237

case type_for(field)

237

case type_for(field)

238

when :integer

238

when :integer

239

add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }

239

add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }

240

when :float

240

when :float

241

add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }

241

add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }

242

when :date, :date_past

242

when :date, :date_past

243

case operator_for(field)

243

case operator_for(field)

244

when "=", ">=", "<=", "><"

244

when "=", ">=", "<=", "><"

245

add_filter_error(field, :invalid) if values_for(field).detect {|v|

245

add_filter_error(field, :invalid) if values_for(field).detect {|v|

246

v.present? && (!v.match(/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){0,2}(Z|\d{2}:?\d{2})?)?\z/) || parse_date(v).nil?)

246

v.present? && (!v.match(/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){0,2}(Z|\d{2}:?\d{2})?)?\z/) || parse_date(v).nil?)

247

}

247

}

248

when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"

248

when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"

249

add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }

249

add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }

250

end

250

end

251

end

251

end

252

end

252

end

253

254

add_filter_error(field, :blank) unless

254

add_filter_error(field, :blank) unless

255

# filter requires one or more values

255

# filter requires one or more values

256

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

256

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

257

# filter doesn't require any value

257

# filter doesn't require any value

258

["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y"].include? operator_for(field)

258

["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y"].include? operator_for(field)

259

end if filters

259

end if filters

260

end

260

end

261

262

def add_filter_error(field, message)

262

def add_filter_error(field, message)

263

m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')

263

m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')

264

errors.add(:base, m)

264

errors.add(:base, m)

265

end

265

end

266

267

def editable_by?(user)

267

def editable_by?(user)

268

return false unless user

268

return false unless user

269

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

269

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

270

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

270

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

271

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

271

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

272

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

272

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

273

end

273

end

274

275

def trackers

275

def trackers

276

@trackers ||= project.nil? ? Tracker.sorted.all : project.rolled_up_trackers

276

@trackers ||= project.nil? ? Tracker.sorted.all : project.rolled_up_trackers

277

end

277

end

278

279

# Returns a hash of localized labels for all filter operators

279

# Returns a hash of localized labels for all filter operators

280

def self.operators_labels

280

def self.operators_labels

281

operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}

281

operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}

282

end

282

end

283

284

# Returns a representation of the available filters for JSON serialization

284

# Returns a representation of the available filters for JSON serialization

285

def available_filters_as_json

285

def available_filters_as_json

286

json = {}

286

json = {}

287

available_filters.each do |field, options|

287

available_filters.each do |field, options|

288

json[field] = options.slice(:type, :name, :values).stringify_keys

288

json[field] = options.slice(:type, :name, :values).stringify_keys

289

end

289

end

290

json

290

json

291

end

291

end

292

293

def all_projects

293

def all_projects

294

@all_projects ||= Project.visible.all

294

@all_projects ||= Project.visible.all

295

end

295

end

296

297

def all_projects_values

297

def all_projects_values

298

return @all_projects_values if @all_projects_values

298

return @all_projects_values if @all_projects_values

299

300

values = []

300

values = []

301

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

301

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

302

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

302

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

303

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

303

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

304

end

304

end

305

@all_projects_values = values

305

@all_projects_values = values

306

end

306

end

307

308

# Adds available filters

308

# Adds available filters

309

def initialize_available_filters

309

def initialize_available_filters

310

# implemented by sub-classes

310

# implemented by sub-classes

311

end

311

end

312

protected :initialize_available_filters

312

protected :initialize_available_filters

313

314

# Adds an available filter

314

# Adds an available filter

315

def add_available_filter(field, options)

315

def add_available_filter(field, options)

316

@available_filters ||= ActiveSupport::OrderedHash.new

316

@available_filters ||= ActiveSupport::OrderedHash.new

317

@available_filters[field] = options

317

@available_filters[field] = options

318

@available_filters

318

@available_filters

319

end

319

end

320

321

# Removes an available filter

321

# Removes an available filter

322

def delete_available_filter(field)

322

def delete_available_filter(field)

323

if @available_filters

323

if @available_filters

324

@available_filters.delete(field)

324

@available_filters.delete(field)

325

end

325

end

326

end

326

end

327

328

# Return a hash of available filters

328

# Return a hash of available filters

329

def available_filters

329

def available_filters

330

unless @available_filters

330

unless @available_filters

331

initialize_available_filters

331

initialize_available_filters

332

@available_filters.each do |field, options|

332

@available_filters.each do |field, options|

333

options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))

333

options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))

334

end

334

end

335

end

335

end

336

@available_filters

336

@available_filters

337

end

337

end

338

339

def add_filter(field, operator, values=nil)

339

def add_filter(field, operator, values=nil)

340

# values must be an array

340

# values must be an array

341

return unless values.nil? || values.is_a?(Array)

341

return unless values.nil? || values.is_a?(Array)

342

# check if field is defined as an available filter

342

# check if field is defined as an available filter

343

if available_filters.has_key? field

343

if available_filters.has_key? field

344

filter_options = available_filters[field]

344

filter_options = available_filters[field]

345

filters[field] = {:operator => operator, :values => (values || [''])}

345

filters[field] = {:operator => operator, :values => (values || [''])}

346

end

346

end

347

end

347

end

348

349

def add_short_filter(field, expression)

349

def add_short_filter(field, expression)

350

return unless expression && available_filters.has_key?(field)

350

return unless expression && available_filters.has_key?(field)

351

field_type = available_filters[field][:type]

351

field_type = available_filters[field][:type]

352

operators_by_filter_type[field_type].sort.reverse.detect do |operator|

352

operators_by_filter_type[field_type].sort.reverse.detect do |operator|

353

next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/

353

next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/

354

values = $1

354

values = $1

355

add_filter field, operator, values.present? ? values.split('|') : ['']

355

add_filter field, operator, values.present? ? values.split('|') : ['']

356

end || add_filter(field, '=', expression.split('|'))

356

end || add_filter(field, '=', expression.split('|'))

357

end

357

end

358

359

# Add multiple filters using +add_filter+

359

# Add multiple filters using +add_filter+

360

def add_filters(fields, operators, values)

360

def add_filters(fields, operators, values)

361

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

361

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

362

fields.each do |field|

362

fields.each do |field|

363

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

363

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

364

end

364

end

365

end

365

end

366

end

366

end

367

368

def has_filter?(field)

368

def has_filter?(field)

369

filters and filters[field]

369

filters and filters[field]

370

end

370

end

371

372

def type_for(field)

372

def type_for(field)

373

available_filters[field][:type] if available_filters.has_key?(field)

373

available_filters[field][:type] if available_filters.has_key?(field)

374

end

374

end

375

376

def operator_for(field)

376

def operator_for(field)

377

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

377

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

378

end

378

end

379

380

def values_for(field)

380

def values_for(field)

381

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

381

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

382

end

382

end

383

384

def value_for(field, index=0)

384

def value_for(field, index=0)

385

(values_for(field) || [])[index]

385

(values_for(field) || [])[index]

386

end

386

end

387

388

def label_for(field)

388

def label_for(field)

389

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

389

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

390

label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)

390

label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)

391

end

391

end

392

393

def self.add_available_column(column)

393

def self.add_available_column(column)

394

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

394

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

395

end

395

end

396

397

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

397

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

398

def groupable_columns

398

def groupable_columns

399

available_columns.select {|c| c.groupable}

399

available_columns.select {|c| c.groupable}

400

end

400

end

401

402

# Returns a Hash of columns and the key for sorting

402

# Returns a Hash of columns and the key for sorting

403

def sortable_columns

403

def sortable_columns

404

available_columns.inject({}) {|h, column|

404

available_columns.inject({}) {|h, column|

405

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

405

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

406

h

406

h

407

}

407

}

408

end

408

end

409

410

def columns

410

def columns

411

# preserve the column_names order

411

# preserve the column_names order

412

cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|

412

cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|

413

available_columns.find { |col| col.name == name }

413

available_columns.find { |col| col.name == name }

414

end.compact

414

end.compact

415

available_columns.select(&:frozen?) | cols

415

available_columns.select(&:frozen?) | cols

416

end

416

end

417

418

def inline_columns

418

def inline_columns

419

columns.select(&:inline?)

419

columns.select(&:inline?)

420

end

420

end

421

422

def block_columns

422

def block_columns

423

columns.reject(&:inline?)

423

columns.reject(&:inline?)

424

end

424

end

425

426

def available_inline_columns

426

def available_inline_columns

427

available_columns.select(&:inline?)

427

available_columns.select(&:inline?)

428

end

428

end

429

430

def available_block_columns

430

def available_block_columns

431

available_columns.reject(&:inline?)

431

available_columns.reject(&:inline?)

432

end

432

end

433

434

def default_columns_names

434

def default_columns_names

435

[]

435

[]

436

end

436

end

437

438

def column_names=(names)

438

def column_names=(names)

439

if names

439

if names

440

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

440

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

441

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

441

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

442

# Set column_names to nil if default columns

442

# Set column_names to nil if default columns

443

if names == default_columns_names

443

if names == default_columns_names

444

names = nil

444

names = nil

445

end

445

end

446

end

446

end

447

write_attribute(:column_names, names)

447

write_attribute(:column_names, names)

448

end

448

end

449

450

def has_column?(column)

450

def has_column?(column)

451

column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)

451

column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)

452

end

452

end

453

454

def has_custom_field_column?

454

def has_custom_field_column?

455

columns.any? {|column| column.is_a? QueryCustomFieldColumn}

455

columns.any? {|column| column.is_a? QueryCustomFieldColumn}

456

end

456

end

457

458

def has_default_columns?

458

def has_default_columns?

459

column_names.nil? || column_names.empty?

459

column_names.nil? || column_names.empty?

460

end

460

end

461

462

def sort_criteria=(arg)

462

def sort_criteria=(arg)

463

c = []

463

c = []

464

if arg.is_a?(Hash)

464

if arg.is_a?(Hash)

465

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

465

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

466

end

466

end

467

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

467

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

468

write_attribute(:sort_criteria, c)

468

write_attribute(:sort_criteria, c)

469

end

469

end

470

471

def sort_criteria

471

def sort_criteria

472

read_attribute(:sort_criteria) || []

472

read_attribute(:sort_criteria) || []

473

end

473

end

474

475

def sort_criteria_key(arg)

475

def sort_criteria_key(arg)

476

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

476

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

477

end

477

end

478

479

def sort_criteria_order(arg)

479

def sort_criteria_order(arg)

480

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

480

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

481

end

481

end

482

483

def sort_criteria_order_for(key)

483

def sort_criteria_order_for(key)

484

sort_criteria.detect {|k, order| key.to_s == k}.try(:last)

484

sort_criteria.detect {|k, order| key.to_s == k}.try(:last)

485

end

485

end

486

487

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

487

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

488

def group_by_sort_order

488

def group_by_sort_order

489

if grouped? && (column = group_by_column)

489

if grouped? && (column = group_by_column)

490

order = sort_criteria_order_for(column.name) || column.default_order

490

order = sort_criteria_order_for(column.name) || column.default_order

491

column.sortable.is_a?(Array) ?

491

column.sortable.is_a?(Array) ?

492

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

492

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

493

"#{column.sortable} #{order}"

493

"#{column.sortable} #{order}"

494

end

494

end

495

end

495

end

496

497

# Returns true if the query is a grouped query

497

# Returns true if the query is a grouped query

498

def grouped?

498

def grouped?

499

!group_by_column.nil?

499

!group_by_column.nil?

500

end

500

end

501

502

def group_by_column

502

def group_by_column

503

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

503

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

504

end

504

end

505

506

def group_by_statement

506

def group_by_statement

507

group_by_column.try(:groupable)

507

group_by_column.try(:groupable)

508

end

508

end

509

510

def project_statement

510

def project_statement

511

project_clauses = []

511

project_clauses = []

512

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

512

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

513

ids = [project.id]

513

ids = [project.id]

514

if has_filter?("subproject_id")

514

if has_filter?("subproject_id")

515

case operator_for("subproject_id")

515

case operator_for("subproject_id")

516

when '='

516

when '='

517

# include the selected subprojects

517

# include the selected subprojects

518

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

518

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

519

when '!*'

519

when '!*'

520

# main project only

520

# main project only

521

else

521

else

522

# all subprojects

522

# all subprojects

523

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

523

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

524

end

524

end

525

elsif Setting.display_subprojects_issues?

525

elsif Setting.display_subprojects_issues?

526

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

526

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

527

end

527

end

528

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

528

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

529

elsif project

529

elsif project

530

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

530

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

531

end

531

end

532

project_clauses.any? ? project_clauses.join(' AND ') : nil

532

project_clauses.any? ? project_clauses.join(' AND ') : nil

533

end

533

end

534

535

def statement

535

def statement

536

# filters clauses

536

# filters clauses

537

filters_clauses = []

537

filters_clauses = []

538

filters.each_key do |field|

538

filters.each_key do |field|

539

next if field == "subproject_id"

539

next if field == "subproject_id"

540

v = values_for(field).clone

540

v = values_for(field).clone

541

next unless v and !v.empty?

541

next unless v and !v.empty?

542

operator = operator_for(field)

542

operator = operator_for(field)

543

544

# "me" value subsitution

544

# "me" value subsitution

545

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

545

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

546

if v.delete("me")

546

if v.delete("me")

547

if User.current.logged?

547

if User.current.logged?

548

v.push(User.current.id.to_s)

548

v.push(User.current.id.to_s)

549

v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'

549

v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'

550

else

550

else

551

v.push("0")

551

v.push("0")

552

end

552

end

553

end

553

end

554

end

554

end

555

556

if field == 'project_id'

556

if field == 'project_id'

557

if v.delete('mine')

557

if v.delete('mine')

558

v += User.current.memberships.map(&:project_id).map(&:to_s)

558

v += User.current.memberships.map(&:project_id).map(&:to_s)

559

end

559

end

560

end

560

end

561

562

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

562

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

563

# custom field

563

# custom field

564

filters_clauses << sql_for_custom_field(field, operator, v, $1)

564

filters_clauses << sql_for_custom_field(field, operator, v, $1)

565

elsif respond_to?("sql_for_#{field}_field")

565

elsif respond_to?("sql_for_#{field}_field")

566

# specific statement

566

# specific statement

567

filters_clauses << send("sql_for_#{field}_field", field, operator, v)

567

filters_clauses << send("sql_for_#{field}_field", field, operator, v)

568

else

568

else

569

# regular field

569

# regular field

570

filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'

570

filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'

571

end

571

end

572

end if filters and valid?

572

end if filters and valid?

573

574

if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)

574

if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)

575

# Excludes results for which the grouped custom field is not visible

575

# Excludes results for which the grouped custom field is not visible

576

filters_clauses << c.custom_field.visibility_by_project_condition

576

filters_clauses << c.custom_field.visibility_by_project_condition

577

end

577

end

578

579

filters_clauses << project_statement

579

filters_clauses << project_statement

580

filters_clauses.reject!(&:blank?)

580

filters_clauses.reject!(&:blank?)

581

582

filters_clauses.any? ? filters_clauses.join(' AND ') : nil

582

filters_clauses.any? ? filters_clauses.join(' AND ') : nil

583

end

583

end

584

585

private

585

private

586

587

def sql_for_custom_field(field, operator, value, custom_field_id)

587

def sql_for_custom_field(field, operator, value, custom_field_id)

588

db_table = CustomValue.table_name

588

db_table = CustomValue.table_name

589

db_field = 'value'

589

db_field = 'value'

590

filter = @available_filters[field]

590

filter = @available_filters[field]

591

return nil unless filter

591

return nil unless filter

592

if filter[:field].format.target_class && filter[:field].format.target_class <= User

592

if filter[:field].format.target_class && filter[:field].format.target_class <= User

593

if value.delete('me')

593

if value.delete('me')

594

value.push User.current.id.to_s

594

value.push User.current.id.to_s

595

end

595

end

596

end

596

end

597

not_in = nil

597

not_in = nil

598

if operator == '!'

598

if operator == '!'

599

# Makes ! operator work for custom fields with multiple values

599

# Makes ! operator work for custom fields with multiple values

600

operator = '='

600

operator = '='

601

not_in = 'NOT'

601

not_in = 'NOT'

602

end

602

end

603

customized_key = "id"

603

customized_key = "id"

604

customized_class = queried_class

604

customized_class = queried_class

605

if field =~ /^(.+)\.cf_/

605

if field =~ /^(.+)\.cf_/

606

assoc = $1

606

assoc = $1

607

customized_key = "#{assoc}_id"

607

customized_key = "#{assoc}_id"

608

customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil

608

customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil

609

raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class

609

raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class

610

end

610

end

611

where = sql_for_field(field, operator, value, db_table, db_field, true)

611

where = sql_for_field(field, operator, value, db_table, db_field, true)

612

if operator =~ /[<>]/

612

if operator =~ /[<>]/

613

where = "(#{where}) AND #{db_table}.#{db_field} <> ''"

613

where = "(#{where}) AND #{db_table}.#{db_field} <> ''"

614

end

614

end

615

"#{queried_table_name}.#{customized_key} #{not_in} IN (" +

615

"#{queried_table_name}.#{customized_key} #{not_in} IN (" +

616

"SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +

616

"SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +

617

" 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}" +

617

" 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}" +

618

" WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"

618

" WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"

619

end

619

end

620

621

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

621

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

622

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

622

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

623

sql = ''

623

sql = ''

624

case operator

624

case operator

625

when "="

625

when "="

626

if value.any?

626

if value.any?

627

case type_for(field)

627

case type_for(field)

628

when :date, :date_past

628

when :date, :date_past

629

sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first))

629

sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first))

630

when :integer

630

when :integer

631

if is_custom_filter

631

if is_custom_filter

632

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_i})"

632

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_i})"

633

else

633

else

634

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

634

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

635

end

635

end

636

when :float

636

when :float

637

if is_custom_filter

637

if is_custom_filter

638

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})"

638

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})"

639

else

639

else

640

sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"

640

sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"

641

end

641

end

642

else

642

else

643

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

643

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

644

end

644

end

645

else

645

else

646

# IN an empty set

646

# IN an empty set

647

sql = "1=0"

647

sql = "1=0"

648

end

648

end

649

when "!"

649

when "!"

650

if value.any?

650

if value.any?

651

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

651

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

652

else

652

else

653

# NOT IN an empty set

653

# NOT IN an empty set

654

sql = "1=1"

654

sql = "1=1"

655

end

655

end

656

when "!*"

656

when "!*"

657

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

657

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

658

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

658

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

659

when "*"

659

when "*"

660

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

660

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

661

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

661

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

662

when ">="

662

when ">="

663

if [:date, :date_past].include?(type_for(field))

663

if [:date, :date_past].include?(type_for(field))

664

sql = date_clause(db_table, db_field, parse_date(value.first), nil)

664

sql = date_clause(db_table, db_field, parse_date(value.first), nil)

665

else

665

else

666

if is_custom_filter

666

if is_custom_filter

667

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})"

667

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})"

668

else

668

else

669

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

669

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

670

end

670

end

671

end

671

end

672

when "<="

672

when "<="

673

if [:date, :date_past].include?(type_for(field))

673

if [:date, :date_past].include?(type_for(field))

674

sql = date_clause(db_table, db_field, nil, parse_date(value.first))

674

sql = date_clause(db_table, db_field, nil, parse_date(value.first))

675

else

675

else

676

if is_custom_filter

676

if is_custom_filter

677

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})"

677

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})"

678

else

678

else

679

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

679

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

680

end

680

end

681

end

681

end

682

when "><"

682

when "><"

683

if [:date, :date_past].include?(type_for(field))

683

if [:date, :date_past].include?(type_for(field))

684

sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]))

684

sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]))

685

else

685

else

686

if is_custom_filter

686

if is_custom_filter

687

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})"

687

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})"

688

else

688

else

689

sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"

689

sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"

690

end

690

end

691

end

691

end

692

when "o"

692

when "o"

693

sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_false})" if field == "status_id"

693

sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{ActiveRecord::Base.connection.quoted_false})" if field == "status_id"

694

when "c"

694

when "c"

695

sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_true})" if field == "status_id"

695

sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{ActiveRecord::Base.connection.quoted_true})" if field == "status_id"

696

when "><t-"

696

when "><t-"

697

# between today - n days and today

697

# between today - n days and today

698

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

698

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

699

when ">t-"

699

when ">t-"

700

# >= today - n days

700

# >= today - n days

701

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

701

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

702

when "<t-"

702

when "<t-"

703

# <= today - n days

703

# <= today - n days

704

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

704

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

705

when "t-"

705

when "t-"

706

# = n days in past

706

# = n days in past

707

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

707

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

708

when "><t+"

708

when "><t+"

709

# between today and today + n days

709

# between today and today + n days

710

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

710

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

711

when ">t+"

711

when ">t+"

712

# >= today + n days

712

# >= today + n days

713

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

713

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

714

when "<t+"

714

when "<t+"

715

# <= today + n days

715

# <= today + n days

716

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

716

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

717

when "t+"

717

when "t+"

718

# = today + n days

718

# = today + n days

719

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

719

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

720

when "t"

720

when "t"

721

# = today

721

# = today

722

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

722

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

723

when "ld"

723

when "ld"

724

# = yesterday

724

# = yesterday

725

sql = relative_date_clause(db_table, db_field, -1, -1)

725

sql = relative_date_clause(db_table, db_field, -1, -1)

726

when "w"

726

when "w"

727

# = this week

727

# = this week

728

first_day_of_week = l(:general_first_day_of_week).to_i

728

first_day_of_week = l(:general_first_day_of_week).to_i

729

day_of_week = Date.today.cwday

729

day_of_week = Date.today.cwday

730

days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)

730

days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)

731

sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)

731

sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)

732

when "lw"

732

when "lw"

733

# = last week

733

# = last week

734

first_day_of_week = l(:general_first_day_of_week).to_i

734

first_day_of_week = l(:general_first_day_of_week).to_i

735

day_of_week = Date.today.cwday

735

day_of_week = Date.today.cwday

736

days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)

736

days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)

737

sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1)

737

sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1)

738

when "l2w"

738

when "l2w"

739

# = last 2 weeks

739

# = last 2 weeks

740

first_day_of_week = l(:general_first_day_of_week).to_i

740

first_day_of_week = l(:general_first_day_of_week).to_i

741

day_of_week = Date.today.cwday

741

day_of_week = Date.today.cwday

742

days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)

742

days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)

743

sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1)

743

sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1)

744

when "m"

744

when "m"

745

# = this month

745

# = this month

746

date = Date.today

746

date = Date.today

747

sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)

747

sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)

748

when "lm"

748

when "lm"

749

# = last month

749

# = last month

750

date = Date.today.prev_month

750

date = Date.today.prev_month

751

sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)

751

sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)

752

when "y"

752

when "y"

753

# = this year

753

# = this year

754

date = Date.today

754

date = Date.today

755

sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year)

755

sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year)

756

when "~"

756

when "~"

757

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

757

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

758

when "!~"

758

when "!~"

759

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

759

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

760

else

760

else

761

raise "Unknown query operator #{operator}"

761

raise "Unknown query operator #{operator}"

762

end

762

end

763

764

return sql

764

return sql

765

end

765

end

766

767

# Adds a filter for the given custom field

767

# Adds a filter for the given custom field

768

def add_custom_field_filter(field, assoc=nil)

768

def add_custom_field_filter(field, assoc=nil)

769

options = field.format.query_filter_options(field, self)

769

options = field.format.query_filter_options(field, self)

770

if field.format.target_class && field.format.target_class <= User

770

if field.format.target_class && field.format.target_class <= User

771

if options[:values].is_a?(Array) && User.current.logged?

771

if options[:values].is_a?(Array) && User.current.logged?

772

options[:values].unshift ["<< #{l(:label_me)} >>", "me"]

772

options[:values].unshift ["<< #{l(:label_me)} >>", "me"]

773

end

773

end

774

end

774

end

775

776

filter_id = "cf_#{field.id}"

776

filter_id = "cf_#{field.id}"

777

filter_name = field.name

777

filter_name = field.name

778

if assoc.present?

778

if assoc.present?

779

filter_id = "#{assoc}.#{filter_id}"

779

filter_id = "#{assoc}.#{filter_id}"

780

filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)

780

filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)

781

end

781

end

782

add_available_filter filter_id, options.merge({

782

add_available_filter filter_id, options.merge({

783

:name => filter_name,

783

:name => filter_name,

784

:field => field

784

:field => field

785

})

785

})

786

end

786

end

787

788

# Adds filters for the given custom fields scope

788

# Adds filters for the given custom fields scope

789

def add_custom_fields_filters(scope, assoc=nil)

789

def add_custom_fields_filters(scope, assoc=nil)

790

scope.visible.where(:is_filter => true).sorted.each do |field|

790

scope.visible.where(:is_filter => true).sorted.each do |field|

791

add_custom_field_filter(field, assoc)

791

add_custom_field_filter(field, assoc)

792

end

792

end

793

end

793

end

794

795

# Adds filters for the given associations custom fields

795

# Adds filters for the given associations custom fields

796

def add_associations_custom_fields_filters(*associations)

796

def add_associations_custom_fields_filters(*associations)

797

fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)

797

fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)

798

associations.each do |assoc|

798

associations.each do |assoc|

799

association_klass = queried_class.reflect_on_association(assoc).klass

799

association_klass = queried_class.reflect_on_association(assoc).klass

800

fields_by_class.each do |field_class, fields|

800

fields_by_class.each do |field_class, fields|

801

if field_class.customized_class <= association_klass

801

if field_class.customized_class <= association_klass

802

fields.sort.each do |field|

802

fields.sort.each do |field|

803

add_custom_field_filter(field, assoc)

803

add_custom_field_filter(field, assoc)

804

end

804

end

805

end

805

end

806

end

806

end

807

end

807

end

808

end

808

end

809

810

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

810

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

811

def date_clause(table, field, from, to)

811

def date_clause(table, field, from, to)

812

s = []

812

s = []

813

if from

813

if from

814

if from.is_a?(Date)

814

if from.is_a?(Date)

815

from = Time.local(from.year, from.month, from.day).yesterday.end_of_day

815

from = Time.local(from.year, from.month, from.day).yesterday.end_of_day

816

else

816

else

817

from = from - 1 # second

817

from = from - 1 # second

818

end

818

end

819

if self.class.default_timezone == :utc

819

if self.class.default_timezone == :utc

820

from = from.utc

820

from = from.utc

821

end

821

end

822

s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from)])

822

s << ("#{table}.#{field} > '%s'" % [ActiveRecord::Base.connection.quoted_date(from)])

823

end

823

end

824

if to

824

if to

825

if to.is_a?(Date)

825

if to.is_a?(Date)

826

to = Time.local(to.year, to.month, to.day).end_of_day

826

to = Time.local(to.year, to.month, to.day).end_of_day

827

end

827

end

828

if self.class.default_timezone == :utc

828

if self.class.default_timezone == :utc

829

to = to.utc

829

to = to.utc

830

end

830

end

831

s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to)])

831

s << ("#{table}.#{field} <= '%s'" % [ActiveRecord::Base.connection.quoted_date(to)])

832

end

832

end

833

s.join(' AND ')

833

s.join(' AND ')

834

end

834

end

835

836

# Returns a SQL clause for a date or datetime field using relative dates.

836

# Returns a SQL clause for a date or datetime field using relative dates.

837

def relative_date_clause(table, field, days_from, days_to)

837

def relative_date_clause(table, field, days_from, days_to)

838

date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))

838

date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))

839

end

839

end

840

841

# Returns a Date or Time from the given filter value

841

# Returns a Date or Time from the given filter value

842

def parse_date(arg)

842

def parse_date(arg)

843

if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/

843

if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/

844

Time.parse(arg) rescue nil

844

Time.parse(arg) rescue nil

845

else

845

else

846

Date.parse(arg) rescue nil

846

Date.parse(arg) rescue nil

847

end

847

end

848

end

848

end

849

850

# Additional joins required for the given sort options

850

# Additional joins required for the given sort options

851

def joins_for_order_statement(order_options)

851

def joins_for_order_statement(order_options)

852

joins = []

852

joins = []

853

854

if order_options

854

if order_options

855

if order_options.include?('authors')

855

if order_options.include?('authors')

856

joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"

856

joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"

857

end

857

end

858

order_options.scan(/cf_\d+/).uniq.each do |name|

858

order_options.scan(/cf_\d+/).uniq.each do |name|

859

column = available_columns.detect {|c| c.name.to_s == name}

859

column = available_columns.detect {|c| c.name.to_s == name}

860

join = column && column.custom_field.join_for_order_statement

860

join = column && column.custom_field.join_for_order_statement

861

if join

861

if join

862

joins << join

862

joins << join

863

end

863

end

864

end

864

end

865

end

865

end

866

867

joins.any? ? joins.join(' ') : nil

867

joins.any? ? joins.join(' ') : nil

868

end

868

end

869

end

869

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-2013  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]
                 @inline = options.key?(:inline) ? options[:inline] : true
                 @caption_key = options[:caption] || "field_#{name}".to_sym
                 @frozen = options[:frozen]
               end
               def caption
                 @caption_key.is_a?(Symbol) ? l(@caption_key) : @caption_key
               end
               # Returns true if the column is sortable, otherwise false
               def sortable?
                 !@sortable.nil?
               end
               def sortable
                 @sortable.is_a?(Proc) ? @sortable.call : @sortable
               end
               def inline?
                 @inline
               end
               def frozen?
                 @frozen
               end
               def value(object)
                 object.send name
               end
               def css_classes
                 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
                 self.groupable = custom_field.group_statement || false
                 @inline = true
                 @cf = custom_field
               end
               def caption
                 @cf.name
               end
               def custom_field
                 @cf
               end
               def value(object)
                 if custom_field.visible_by?(object.project, User.current)
                   cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}
                   cv.size > 1 ? cv.sort {|a,b| a.to_s <=> b.to_s} : cv.first
                 else
                   nil
                 end
               end
               def css_classes
                 @css_classes ||= "#{name} #{@cf.field_format}"
               end
             end
             class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
               def initialize(association, custom_field)
                 super(custom_field)
                 self.name = "#{association}.cf_#{custom_field.id}".to_sym
                 # TODO: support sorting/grouping by association custom field
                 self.sortable = false
                 self.groupable = false
                 @association = association
               end
               def value(object)
                 if assoc = object.send(@association)
                   super(assoc)
                 end
               end
               def css_classes
                 @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
               end
             end
             class Query < ActiveRecord::Base
               class StatementInvalid < ::ActiveRecord::StatementInvalid
               end
               VISIBILITY_PRIVATE = 0
               VISIBILITY_ROLES   = 1
               VISIBILITY_PUBLIC  = 2
               belongs_to :project
               belongs_to :user
               has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"
               serialize :filters
               serialize :column_names
               serialize :sort_criteria, Array
               serialize :options, Hash
               attr_protected :project_id, :user_id
               validates_presence_of :name
               validates_length_of :name, :maximum => 255
               validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
               validate :validate_query_filters
               validate do |query|
                 errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?
               end
               after_save do |query|
                 if query.visibility_changed? && query.visibility != VISIBILITY_ROLES
             	    query.roles.clear
             	  end
               end
               class_attribute :operators
               self.operators = {
                 "="   => :label_equals,
                 "!"   => :label_not_equals,
                 "o"   => :label_open_issues,
                 "c"   => :label_closed_issues,
                 "!*"  => :label_none,
                 "*"   => :label_any,
                 ">="  => :label_greater_or_equal,
                 "<="  => :label_less_or_equal,
                 "><"  => :label_between,
                 "<t+" => :label_in_less_than,
                 ">t+" => :label_in_more_than,
                 "><t+"=> :label_in_the_next_days,
                 "t+"  => :label_in,
                 "t"   => :label_today,
                 "ld"  => :label_yesterday,
                 "w"   => :label_this_week,
                 "lw"  => :label_last_week,
                 "l2w" => [:label_last_n_weeks, {:count => 2}],
                 "m"   => :label_this_month,
                 "lm"  => :label_last_month,
                 "y"   => :label_this_year,
                 ">t-" => :label_less_than_ago,
                 "<t-" => :label_more_than_ago,
                 "><t-"=> :label_in_the_past_days,
                 "t-"  => :label_ago,
                 "~"   => :label_contains,
                 "!~"  => :label_not_contains,
                 "=p"  => :label_any_issues_in_project,
                 "=!p" => :label_any_issues_not_in_project,
                 "!p"  => :label_no_issues_in_project
               }
               class_attribute :operators_by_filter_type
               self.operators_by_filter_type = {
                 :list => [ "=", "!" ],
                 :list_status => [ "o", "=", "!", "c", "*" ],
                 :list_optional => [ "=", "!", "!*", "*" ],
                 :list_subprojects => [ "*", "!*", "=" ],
                 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
                 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
                 :string => [ "=", "~", "!", "!~", "!*", "*" ],
                 :text => [  "~", "!~", "!*", "*" ],
                 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
                 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
                 :relation => ["=", "=p", "=!p", "!p", "!*", "*"]
               }
               class_attribute :available_columns
               self.available_columns = []
               class_attribute :queried_class
               def queried_table_name
                 @queried_table_name ||= self.class.queried_class.table_name
               end
               def initialize(attributes=nil, *args)
                 super attributes
                 @is_for_all = project.nil?
               end
               # Builds the query from the given params
               def build_from_params(params)
                 if params[:fields] || params[:f]
                   self.filters = {}
                   add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
                 else
                   available_filters.keys.each do |field|
                     add_short_filter(field, params[field]) if params[field]
                   end
                 end
                 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
                 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
                 self
               end
               # Builds a new query from the given params and attributes
               def self.build_from_params(params, attributes={})
                 new(attributes).build_from_params(params)
               end
               def validate_query_filters
                 filters.each_key do |field|
                   if values_for(field)
                     case type_for(field)
                     when :integer
                       add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }
                     when :float
                       add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
                     when :date, :date_past
                       case operator_for(field)
                       when "=", ">=", "<=", "><"
                         add_filter_error(field, :invalid) if values_for(field).detect {|v|
                           v.present? && (!v.match(/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){0,2}(Z|\d{2}:?\d{2})?)?\z/) || parse_date(v).nil?)
                         }
                       when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
                         add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
                       end
                     end
                   end
                   add_filter_error(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", "ld", "w", "lw", "l2w", "m", "lm", "y"].include? operator_for(field)
                 end if filters
               end
               def add_filter_error(field, message)
                 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
                 errors.add(:base, m)
               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_private? && 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 trackers
                 @trackers ||= project.nil? ? Tracker.sorted.all : project.rolled_up_trackers
               end
               # Returns a hash of localized labels for all filter operators
               def self.operators_labels
                 operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
               end
               # Returns a representation of the available filters for JSON serialization
               def available_filters_as_json
                 json = {}
                 available_filters.each do |field, options|
                   json[field] = options.slice(:type, :name, :values).stringify_keys
                 end
                 json
               end
               def all_projects
                 @all_projects ||= Project.visible.all
               end
               def all_projects_values
                 return @all_projects_values if @all_projects_values
                 values = []
                 Project.project_tree(all_projects) do |p, level|
                   prefix = (level > 0 ? ('--' * level + ' ') : '')
                   values << ["#{prefix}#{p.name}", p.id.to_s]
                 end
                 @all_projects_values = values
               end
               # Adds available filters
               def initialize_available_filters
                 # implemented by sub-classes
               end
               protected :initialize_available_filters
               # Adds an available filter
               def add_available_filter(field, options)
                 @available_filters ||= ActiveSupport::OrderedHash.new
                 @available_filters[field] = options
                 @available_filters
               end
               # Removes an available filter
               def delete_available_filter(field)
                 if @available_filters
                   @available_filters.delete(field)
                 end
               end
               # Return a hash of available filters
               def available_filters
                 unless @available_filters
                   initialize_available_filters
                   @available_filters.each do |field, options|
                     options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
                   end
                 end
                 @available_filters
               end
               def add_filter(field, operator, values=nil)
                 # values must be an array
                 return unless values.nil? || values.is_a?(Array)
                 # check if field is defined as an available filter
                 if available_filters.has_key? field
                   filter_options = available_filters[field]
                   filters[field] = {:operator => operator, :values => (values || [''])}
                 end
               end
               def add_short_filter(field, expression)
                 return unless expression && available_filters.has_key?(field)
                 field_type = available_filters[field][:type]
                 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
                   next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
                   values = $1
                   add_filter field, operator, values.present? ? values.split('|') : ['']
                 end || add_filter(field, '=', expression.split('|'))
               end
               # Add multiple filters using +add_filter+
               def add_filters(fields, operators, values)
                 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
                   fields.each do |field|
                     add_filter(field, operators[field], values && values[field])
                   end
                 end
               end
               def has_filter?(field)
                 filters and filters[field]
               end
               def type_for(field)
                 available_filters[field][:type] if available_filters.has_key?(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 value_for(field, index=0)
                 (values_for(field) || [])[index]
               end
               def label_for(field)
                 label = available_filters[field][:name] if available_filters.has_key?(field)
                 label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
               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
                 available_columns.inject({}) {|h, column|
                   h[column.name.to_s] = column.sortable
                   h
                 }
               end
               def columns
                 # preserve the column_names order
                 cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
                    available_columns.find { |col| col.name == name }
                 end.compact
                 available_columns.select(&:frozen?) | cols
               end
               def inline_columns
                 columns.select(&:inline?)
               end
               def block_columns
                 columns.reject(&:inline?)
               end
               def available_inline_columns
                 available_columns.select(&:inline?)
               end
               def available_block_columns
                 available_columns.reject(&:inline?)
               end
               def default_columns_names
                 []
               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 == default_columns_names
                     names = nil
                   end
                 end
                 write_attribute(:column_names, names)
               end
               def has_column?(column)
                 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
               end
               def has_custom_field_column?
                 columns.any? {|column| column.is_a? QueryCustomFieldColumn}
               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 == false) ? 'desc' : '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
               def sort_criteria_order_for(key)
                 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
               end
               # Returns the SQL sort order that should be prepended for grouping
               def group_by_sort_order
                 if grouped? && (column = group_by_column)
                   order = sort_criteria_order_for(column.name) || column.default_order
                   column.sortable.is_a?(Array) ?
                     column.sortable.collect {|s| "#{s} #{order}"}.join(',') :
                     "#{column.sortable} #{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.any? ? project_clauses.join(' AND ') : nil
               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 user_id watcher_id).include?(field)
                     if v.delete("me")
                       if User.current.logged?
                         v.push(User.current.id.to_s)
                         v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
                       else
                         v.push("0")
                       end
                     end
                   end
                   if field == 'project_id'
                     if v.delete('mine')
                       v += User.current.memberships.map(&:project_id).map(&:to_s)
                     end
                   end
                   if field =~ /cf_(\d+)$/
                     # custom field
                     filters_clauses << sql_for_custom_field(field, operator, v, $1)
                   elsif respond_to?("sql_for_#{field}_field")
                     # specific statement
                     filters_clauses << send("sql_for_#{field}_field", field, operator, v)
                   else
                     # regular field
                     filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
                   end
                 end if filters and valid?
                 if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
                   # Excludes results for which the grouped custom field is not visible
                   filters_clauses << c.custom_field.visibility_by_project_condition
                 end
                 filters_clauses << project_statement
                 filters_clauses.reject!(&:blank?)
                 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
               end
               private
               def sql_for_custom_field(field, operator, value, custom_field_id)
                 db_table = CustomValue.table_name
                 db_field = 'value'
                 filter = @available_filters[field]
                 return nil unless filter
                 if filter[:field].format.target_class && filter[:field].format.target_class <= User
                   if value.delete('me')
                     value.push User.current.id.to_s
                   end
                 end
                 not_in = nil
                 if operator == '!'
                   # Makes ! operator work for custom fields with multiple values
                   operator = '='
                   not_in = 'NOT'
                 end
                 customized_key = "id"
                 customized_class = queried_class
                 if field =~ /^(.+)\.cf_/
                   assoc = $1
                   customized_key = "#{assoc}_id"
                   customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
                   raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
                 end
                 where = sql_for_field(field, operator, value, db_table, db_field, true)
                 if operator =~ /[<>]/
                   where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
                 end
                 "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
                   "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
                   " 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}" +
                   " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
               end
               # 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?
                     case type_for(field)
                     when :date, :date_past
                       sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first))
                     when :integer
                       if is_custom_filter
                         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_i})"
                       else
                         sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
                       end
                     when :float
                       if is_custom_filter
                         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})"
                       else
                         sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
                       end
                     else
-                      sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
+                      sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{ActiveRecord::Base.connection.quote_string(val)}'"}.join(",") + ")"
                     end
                   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(",") + "))"
+                    sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{ActiveRecord::Base.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 ">="
                   if [:date, :date_past].include?(type_for(field))
                     sql = date_clause(db_table, db_field, parse_date(value.first), nil)
                   else
                     if is_custom_filter
                       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})"
                     else
                       sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
                     end
                   end
                 when "<="
                   if [:date, :date_past].include?(type_for(field))
                     sql = date_clause(db_table, db_field, nil, parse_date(value.first))
                   else
                     if is_custom_filter
                       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})"
                     else
                       sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
                     end
                   end
                 when "><"
                   if [:date, :date_past].include?(type_for(field))
                     sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]))
                   else
                     if is_custom_filter
                       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})"
                     else
                       sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
                     end
                   end
                 when "o"
-                  sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_false})" if field == "status_id"
+                  sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{ActiveRecord::Base.connection.quoted_false})" if field == "status_id"
                 when "c"
-                  sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_true})" if field == "status_id"
+                  sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{ActiveRecord::Base.connection.quoted_true})" if field == "status_id"
                 when "><t-"
                   # between today - n days and today
                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
                 when ">t-"
                   # >= today - n days
                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil)
                 when "<t-"
                   # <= today - n days
                   sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
                 when "t-"
                   # = n days in past
                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
                 when "><t+"
                   # between today and today + n days
                   sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
                 when ">t+"
                   # >= today + n days
                   sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
                 when "<t+"
                   # <= today + n days
                   sql = relative_date_clause(db_table, db_field, nil, value.first.to_i)
                 when "t+"
                   # = today + n days
                   sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
                 when "t"
                   # = today
                   sql = relative_date_clause(db_table, db_field, 0, 0)
                 when "ld"
                   # = yesterday
                   sql = relative_date_clause(db_table, db_field, -1, -1)
                 when "w"
                   # = this week
                   first_day_of_week = l(:general_first_day_of_week).to_i
                   day_of_week = Date.today.cwday
                   days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
                   sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
                 when "lw"
                   # = last week
                   first_day_of_week = l(:general_first_day_of_week).to_i
                   day_of_week = Date.today.cwday
                   days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
                   sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1)
                 when "l2w"
                   # = last 2 weeks
                   first_day_of_week = l(:general_first_day_of_week).to_i
                   day_of_week = Date.today.cwday
                   days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
                   sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1)
                 when "m"
                   # = this month
                   date = Date.today
                   sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
                 when "lm"
                   # = last month
                   date = Date.today.prev_month
                   sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
                 when "y"
                   # = this year
                   date = Date.today
                   sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year)
                 when "~"
-                  sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
+                  sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{ActiveRecord::Base.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)}%'"
+                  sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{ActiveRecord::Base.connection.quote_string(value.first.to_s.downcase)}%'"
                 else
                   raise "Unknown query operator #{operator}"
                 end
                 return sql
               end
               # Adds a filter for the given custom field
               def add_custom_field_filter(field, assoc=nil)
                 options = field.format.query_filter_options(field, self)
                 if field.format.target_class && field.format.target_class <= User
                   if options[:values].is_a?(Array) && User.current.logged?
                     options[:values].unshift ["<< #{l(:label_me)} >>", "me"]
                   end
                 end
                 filter_id = "cf_#{field.id}"
                 filter_name = field.name
                 if assoc.present?
                   filter_id = "#{assoc}.#{filter_id}"
                   filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
                 end
                 add_available_filter filter_id, options.merge({
                   :name => filter_name,
                   :field => field
                 })
               end
               # Adds filters for the given custom fields scope
               def add_custom_fields_filters(scope, assoc=nil)
                 scope.visible.where(:is_filter => true).sorted.each do |field|
                   add_custom_field_filter(field, assoc)
                 end
               end
               # Adds filters for the given associations custom fields
               def add_associations_custom_fields_filters(*associations)
                 fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
                 associations.each do |assoc|
                   association_klass = queried_class.reflect_on_association(assoc).klass
                   fields_by_class.each do |field_class, fields|
                     if field_class.customized_class <= association_klass
                       fields.sort.each do |field|
                         add_custom_field_filter(field, assoc)
                       end
                     end
                   end
                 end
               end
               # Returns a SQL clause for a date or datetime field.
               def date_clause(table, field, from, to)
                 s = []
                 if from
                   if from.is_a?(Date)
                     from = Time.local(from.year, from.month, from.day).yesterday.end_of_day
                   else
                     from = from - 1 # second
                   end
                   if self.class.default_timezone == :utc
                     from = from.utc
                   end
-                  s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from)])
+                  s << ("#{table}.#{field} > '%s'" % [ActiveRecord::Base.connection.quoted_date(from)])
                 end
                 if to
                   if to.is_a?(Date)
                     to = Time.local(to.year, to.month, to.day).end_of_day
                   end
                   if self.class.default_timezone == :utc
                     to = to.utc
                   end
-                  s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to)])
+                  s << ("#{table}.#{field} <= '%s'" % [ActiveRecord::Base.connection.quoted_date(to)])
                 end
                 s.join(' AND ')
               end
               # Returns a SQL clause for a date or datetime field using relative dates.
               def relative_date_clause(table, field, days_from, days_to)
                 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
               end
               # Returns a Date or Time from the given filter value
               def parse_date(arg)
                 if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
                   Time.parse(arg) rescue nil
                 else
                   Date.parse(arg) rescue nil
                 end
               end
               # Additional joins required for the given sort options
               def joins_for_order_statement(order_options)
                 joins = []
                 if order_options
                   if order_options.include?('authors')
                     joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
                   end
                   order_options.scan(/cf_\d+/).uniq.each do |name|
                     column = available_columns.detect {|c| c.name.to_s == name}
                     join = column && column.custom_field.join_for_order_statement
                     if join
                       joins << join
                     end
                   end
                 end
                 joins.any? ? joins.join(' ') : nil
               end
             end