jro_apps/redmine Commit - r15834:82afdc7f7858

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, :totalable, :default_order

19

attr_accessor :name, :sortable, :groupable, :totalable, :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.totalable = options[:totalable] || false

29

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

30

self.default_order = options[:default_order]

30

self.default_order = options[:default_order]

31

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

31

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

32

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

32

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

33

@frozen = options[:frozen]

33

@frozen = options[:frozen]

34

end

34

end

35

36

def caption

36

def caption

37

case @caption_key

37

case @caption_key

38

when Symbol

38

when Symbol

39

l(@caption_key)

39

l(@caption_key)

40

when Proc

40

when Proc

41

@caption_key.call

41

@caption_key.call

42

else

42

else

43

@caption_key

43

@caption_key

44

end

44

end

45

end

45

end

46

47

# Returns true if the column is sortable, otherwise false

47

# Returns true if the column is sortable, otherwise false

48

def sortable?

48

def sortable?

49

!@sortable.nil?

49

!@sortable.nil?

50

end

50

end

51

52

def sortable

52

def sortable

53

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

53

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

54

end

54

end

55

56

def inline?

56

def inline?

57

@inline

57

@inline

58

end

58

end

59

60

def frozen?

60

def frozen?

61

@frozen

61

@frozen

62

end

62

end

63

64

def value(object)

64

def value(object)

65

object.send name

65

object.send name

66

end

66

end

67

68

def value_object(object)

68

def value_object(object)

69

object.send name

69

object.send name

70

end

70

end

71

72

def css_classes

72

def css_classes

73

name

73

name

74

end

74

end

75

end

75

end

76

77

class QueryAssociationColumn < QueryColumn

77

class QueryAssociationColumn < QueryColumn

78

79

def initialize(association, attribute, options={})

79

def initialize(association, attribute, options={})

80

@association = association

80

@association = association

81

@attribute = attribute

81

@attribute = attribute

82

name_with_assoc = "#{association}.#{attribute}".to_sym

82

name_with_assoc = "#{association}.#{attribute}".to_sym

83

super(name_with_assoc, options)

83

super(name_with_assoc, options)

84

end

84

end

85

86

def value_object(object)

86

def value_object(object)

87

if assoc = object.send(@association)

87

if assoc = object.send(@association)

88

assoc.send @attribute

88

assoc.send @attribute

89

end

89

end

90

end

90

end

91

92

def css_classes

92

def css_classes

93

@css_classes ||= "#{@association}-#{@attribute}"

93

@css_classes ||= "#{@association}-#{@attribute}"

94

end

94

end

95

end

95

end

96

97

class QueryCustomFieldColumn < QueryColumn

97

class QueryCustomFieldColumn < QueryColumn

98

99

def initialize(custom_field, options={})

99

def initialize(custom_field, options={})

100

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

100

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

101

self.sortable = custom_field.order_statement || false

101

self.sortable = custom_field.order_statement || false

102

self.groupable = custom_field.group_statement || false

102

self.groupable = custom_field.group_statement || false

103

self.totalable = options.key?(:totalable) ? !!options[:totalable] : custom_field.totalable?

103

self.totalable = options.key?(:totalable) ? !!options[:totalable] : custom_field.totalable?

104

@inline = true

104

@inline = true

105

@cf = custom_field

105

@cf = custom_field

106

end

106

end

107

108

def caption

108

def caption

109

@cf.name

109

@cf.name

110

end

110

end

111

112

def custom_field

112

def custom_field

113

@cf

113

@cf

114

end

114

end

115

116

def value_object(object)

116

def value_object(object)

117

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

117

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

118

cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}

118

cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}

119

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

119

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

120

else

120

else

121

nil

121

nil

122

end

122

end

123

end

123

end

124

125

def value(object)

125

def value(object)

126

raw = value_object(object)

126

raw = value_object(object)

127

if raw.is_a?(Array)

127

if raw.is_a?(Array)

128

raw.map {|r| @cf.cast_value(r.value)}

128

raw.map {|r| @cf.cast_value(r.value)}

129

elsif raw

129

elsif raw

130

@cf.cast_value(raw.value)

130

@cf.cast_value(raw.value)

131

else

131

else

132

nil

132

nil

133

end

133

end

134

end

134

end

135

136

def css_classes

136

def css_classes

137

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

137

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

138

end

138

end

139

end

139

end

140

141

class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn

141

class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn

142

143

def initialize(association, custom_field, options={})

143

def initialize(association, custom_field, options={})

144

super(custom_field, options)

144

super(custom_field, options)

145

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

145

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

146

# TODO: support sorting/grouping by association custom field

146

# TODO: support sorting/grouping by association custom field

147

self.sortable = false

147

self.sortable = false

148

self.groupable = false

148

self.groupable = false

149

@association = association

149

@association = association

150

end

150

end

151

152

def value_object(object)

152

def value_object(object)

153

if assoc = object.send(@association)

153

if assoc = object.send(@association)

154

super(assoc)

154

super(assoc)

155

end

155

end

156

end

156

end

157

158

def css_classes

158

def css_classes

159

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

159

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

160

end

160

end

161

end

161

end

162

163

class QueryFilter

163

class QueryFilter

164

include Redmine::I18n

164

include Redmine::I18n

165

166

def initialize(field, options)

166

def initialize(field, options)

167

@field = field.to_s

167

@field = field.to_s

168

@options = options

168

@options = options

169

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

169

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

170

# Consider filters with a Proc for values as remote by default

170

# Consider filters with a Proc for values as remote by default

171

@remote = options.key?(:remote) ? options[:remote] : options[:values].is_a?(Proc)

171

@remote = options.key?(:remote) ? options[:remote] : options[:values].is_a?(Proc)

172

end

172

end

173

174

def [](arg)

174

def [](arg)

175

if arg == :values

175

if arg == :values

176

values

176

values

177

else

177

else

178

@options[arg]

178

@options[arg]

179

end

179

end

180

end

180

end

181

182

def values

182

def values

183

@values ||= begin

183

@values ||= begin

184

values = @options[:values]

184

values = @options[:values]

185

if values.is_a?(Proc)

185

if values.is_a?(Proc)

186

values = values.call

186

values = values.call

187

end

187

end

188

values

188

values

189

end

189

end

190

end

190

end

191

192

def remote

192

def remote

193

@remote

193

@remote

194

end

194

end

195

end

195

end

196

197

class Query < ActiveRecord::Base

197

class Query < ActiveRecord::Base

198

class StatementInvalid < ::ActiveRecord::StatementInvalid

198

class StatementInvalid < ::ActiveRecord::StatementInvalid

199

end

199

end

200

201

include Redmine::SubclassFactory

201

include Redmine::SubclassFactory

202

203

VISIBILITY_PRIVATE = 0

203

VISIBILITY_PRIVATE = 0

204

VISIBILITY_ROLES = 1

204

VISIBILITY_ROLES = 1

205

VISIBILITY_PUBLIC = 2

205

VISIBILITY_PUBLIC = 2

206

207

belongs_to :project

207

belongs_to :project

208

belongs_to :user

208

belongs_to :user

209

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

209

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

210

serialize :filters

210

serialize :filters

211

serialize :column_names

211

serialize :column_names

212

serialize :sort_criteria, Array

212

serialize :sort_criteria, Array

213

serialize :options, Hash

213

serialize :options, Hash

214

215

attr_protected :project_id, :user_id

215

attr_protected :project_id, :user_id

216

217

validates_presence_of :name

217

validates_presence_of :name

218

validates_length_of :name, :maximum => 255

218

validates_length_of :name, :maximum => 255

219

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

219

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

220

validate :validate_query_filters

220

validate :validate_query_filters

221

validate do |query|

221

validate do |query|

222

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

222

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

223

end

223

end

224

225

after_save do |query|

225

after_save do |query|

226

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

226

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

227

query.roles.clear

227

query.roles.clear

228

end

228

end

229

end

229

end

230

231

class_attribute :operators

231

class_attribute :operators

232

self.operators = {

232

self.operators = {

233

"=" => :label_equals,

233

"=" => :label_equals,

234

"!" => :label_not_equals,

234

"!" => :label_not_equals,

235

"o" => :label_open_issues,

235

"o" => :label_open_issues,

236

"c" => :label_closed_issues,

236

"c" => :label_closed_issues,

237

"!*" => :label_none,

237

"!*" => :label_none,

238

"*" => :label_any,

238

"*" => :label_any,

239

">=" => :label_greater_or_equal,

239

">=" => :label_greater_or_equal,

240

"<=" => :label_less_or_equal,

240

"<=" => :label_less_or_equal,

241

"><" => :label_between,

241

"><" => :label_between,

242

"<t+" => :label_in_less_than,

242

"<t+" => :label_in_less_than,

243

">t+" => :label_in_more_than,

243

">t+" => :label_in_more_than,

244

"><t+"=> :label_in_the_next_days,

244

"><t+"=> :label_in_the_next_days,

245

"t+" => :label_in,

245

"t+" => :label_in,

246

"t" => :label_today,

246

"t" => :label_today,

247

"ld" => :label_yesterday,

247

"ld" => :label_yesterday,

248

"w" => :label_this_week,

248

"w" => :label_this_week,

249

"lw" => :label_last_week,

249

"lw" => :label_last_week,

250

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

250

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

251

"m" => :label_this_month,

251

"m" => :label_this_month,

252

"lm" => :label_last_month,

252

"lm" => :label_last_month,

253

"y" => :label_this_year,

253

"y" => :label_this_year,

254

">t-" => :label_less_than_ago,

254

">t-" => :label_less_than_ago,

255

"<t-" => :label_more_than_ago,

255

"<t-" => :label_more_than_ago,

256

"><t-"=> :label_in_the_past_days,

256

"><t-"=> :label_in_the_past_days,

257

"t-" => :label_ago,

257

"t-" => :label_ago,

258

"~" => :label_contains,

258

"~" => :label_contains,

259

"!~" => :label_not_contains,

259

"!~" => :label_not_contains,

260

"=p" => :label_any_issues_in_project,

260

"=p" => :label_any_issues_in_project,

261

"=!p" => :label_any_issues_not_in_project,

261

"=!p" => :label_any_issues_not_in_project,

262

"!p" => :label_no_issues_in_project,

262

"!p" => :label_no_issues_in_project,

263

"*o" => :label_any_open_issues,

263

"*o" => :label_any_open_issues,

264

"!o" => :label_no_open_issues

264

"!o" => :label_no_open_issues

265

}

265

}

266

267

class_attribute :operators_by_filter_type

267

class_attribute :operators_by_filter_type

268

self.operators_by_filter_type = {

268

self.operators_by_filter_type = {

269

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

269

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

270

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

270

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

271

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

271

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

272

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

272

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

273

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

273

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

274

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

274

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

275

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

275

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

276

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

276

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

277

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

277

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

278

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

278

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

279

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

279

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

280

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

280

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

281

}

281

}

282

283

class_attribute :available_columns

283

class_attribute :available_columns

284

self.available_columns = []

284

self.available_columns = []

285

286

class_attribute :queried_class

286

class_attribute :queried_class

287

288

# Permission required to view the queries, set on subclasses.

288

# Permission required to view the queries, set on subclasses.

289

class_attribute :view_permission

289

class_attribute :view_permission

290

291

# Scope of queries that are global or on the given project

291

# Scope of queries that are global or on the given project

292

scope :global_or_on_project, lambda {|project|

292

scope :global_or_on_project, lambda {|project|

293

where(:project_id => (project.nil? ? nil : [nil, project.id]))

293

where(:project_id => (project.nil? ? nil : [nil, project.id]))

294

}

294

}

295

296

scope :sorted, lambda {order(:name, :id)}

296

scope :sorted, lambda {order(:name, :id)}

297

298

# Scope of visible queries, can be used from subclasses only.

298

# Scope of visible queries, can be used from subclasses only.

299

# Unlike other visible scopes, a class methods is used as it

299

# Unlike other visible scopes, a class methods is used as it

300

# let handle inheritance more nicely than scope DSL.

300

# let handle inheritance more nicely than scope DSL.

301

def self.visible(*args)

301

def self.visible(*args)

302

if self == ::Query

302

if self == ::Query

303

# Visibility depends on permissions for each subclass,

303

# Visibility depends on permissions for each subclass,

304

# raise an error if the scope is called from Query (eg. Query.visible)

304

# raise an error if the scope is called from Query (eg. Query.visible)

305

raise Exception.new("Cannot call .visible scope from the base Query class, but from subclasses only.")

305

raise Exception.new("Cannot call .visible scope from the base Query class, but from subclasses only.")

306

end

306

end

307

308

user = args.shift || User.current

308

user = args.shift || User.current

309

base = Project.allowed_to_condition(user, view_permission, *args)

309

base = Project.allowed_to_condition(user, view_permission, *args)

310

scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").

310

scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").

311

where("#{table_name}.project_id IS NULL OR (#{base})")

311

where("#{table_name}.project_id IS NULL OR (#{base})")

312

313

if user.admin?

313

if user.admin?

314

scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)

314

scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)

315

elsif user.memberships.any?

315

elsif user.memberships.any?

316

scope.where("#{table_name}.visibility = ?" +

316

scope.where("#{table_name}.visibility = ?" +

317

" OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +

317

" OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +

318

"SELECT DISTINCT q.id FROM #{table_name} q" +

318

"SELECT DISTINCT q.id FROM #{table_name} q" +

319

" INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +

319

" INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +

320

" INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +

320

" INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +

321

" INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +

321

" INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +

322

" WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +

322

" WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +

323

" OR #{table_name}.user_id = ?",

323

" OR #{table_name}.user_id = ?",

324

VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)

324

VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)

325

elsif user.logged?

325

elsif user.logged?

326

scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)

326

scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)

327

else

327

else

328

scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)

328

scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)

329

end

329

end

330

end

330

end

331

332

# Returns true if the query is visible to +user+ or the current user.

332

# Returns true if the query is visible to +user+ or the current user.

333

def visible?(user=User.current)

333

def visible?(user=User.current)

334

return true if user.admin?

334

return true if user.admin?

335

return false unless project.nil? || user.allowed_to?(self.class.view_permission, project)

335

return false unless project.nil? || user.allowed_to?(self.class.view_permission, project)

336

case visibility

336

case visibility

337

when VISIBILITY_PUBLIC

337

when VISIBILITY_PUBLIC

338

true

338

true

339

when VISIBILITY_ROLES

339

when VISIBILITY_ROLES

340

if project

340

if project

341

(user.roles_for_project(project) & roles).any?

341

(user.roles_for_project(project) & roles).any?

342

else

342

else

343

Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?

343

Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?

344

end

344

end

345

else

345

else

346

user == self.user

346

user == self.user

347

end

347

end

348

end

348

end

349

350

def is_private?

350

def is_private?

351

visibility == VISIBILITY_PRIVATE

351

visibility == VISIBILITY_PRIVATE

352

end

352

end

353

354

def is_public?

354

def is_public?

355

!is_private?

355

!is_private?

356

end

356

end

357

358

def queried_table_name

358

def queried_table_name

359

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

359

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

360

end

360

end

361

362

def initialize(attributes=nil, *args)

362

def initialize(attributes=nil, *args)

363

super attributes

363

super attributes

364

@is_for_all = project.nil?

364

@is_for_all = project.nil?

365

end

365

end

366

367

# Builds the query from the given params

367

# Builds the query from the given params

368

def build_from_params(params)

368

def build_from_params(params)

369

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

369

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

370

self.filters = {}

370

self.filters = {}

371

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

371

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

372

else

372

else

373

available_filters.keys.each do |field|

373

available_filters.keys.each do |field|

374

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

374

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

375

end

375

end

376

end

376

end

377

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

377

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

378

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

378

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

379

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

379

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

380

self

380

self

381

end

381

end

382

383

# Builds a new query from the given params and attributes

383

# Builds a new query from the given params and attributes

384

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

384

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

385

new(attributes).build_from_params(params)

385

new(attributes).build_from_params(params)

386

end

386

end

387

388

def validate_query_filters

388

def validate_query_filters

389

filters.each_key do |field|

389

filters.each_key do |field|

390

if values_for(field)

390

if values_for(field)

391

case type_for(field)

391

case type_for(field)

392

when :integer

392

when :integer

393

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

393

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

394

when :float

394

when :float

395

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

395

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

396

when :date, :date_past

396

when :date, :date_past

397

case operator_for(field)

397

case operator_for(field)

398

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

398

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

399

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

399

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

400

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?)

400

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?)

401

}

401

}

402

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

402

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

403

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

403

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

404

end

404

end

405

end

405

end

406

end

406

end

407

408

add_filter_error(field, :blank) unless

408

add_filter_error(field, :blank) unless

409

# filter requires one or more values

409

# filter requires one or more values

410

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

410

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

411

# filter doesn't require any value

411

# filter doesn't require any value

412

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

412

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

413

end if filters

413

end if filters

414

end

414

end

415

416

def add_filter_error(field, message)

416

def add_filter_error(field, message)

417

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

417

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

418

errors.add(:base, m)

418

errors.add(:base, m)

419

end

419

end

420

421

def editable_by?(user)

421

def editable_by?(user)

422

return false unless user

422

return false unless user

423

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

423

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

424

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

424

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

425

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

425

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

426

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

426

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

427

end

427

end

428

429

def trackers

429

def trackers

430

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

430

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

431

end

431

end

432

433

# Returns a hash of localized labels for all filter operators

433

# Returns a hash of localized labels for all filter operators

434

def self.operators_labels

434

def self.operators_labels

435

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

435

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

436

end

436

end

437

438

# Returns a representation of the available filters for JSON serialization

438

# Returns a representation of the available filters for JSON serialization

439

def available_filters_as_json

439

def available_filters_as_json

440

json = {}

440

json = {}

441

available_filters.each do |field, filter|

441

available_filters.each do |field, filter|

442

options = {:type => filter[:type], :name => filter[:name]}

442

options = {:type => filter[:type], :name => filter[:name]}

443

options[:remote] = true if filter.remote

443

options[:remote] = true if filter.remote

444

445

if has_filter?(field) || !filter.remote

445

if has_filter?(field) || !filter.remote

446

options[:values] = filter.values

446

options[:values] = filter.values

447

if options[:values] && values_for(field)

447

if options[:values] && values_for(field)

448

missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)

448

missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)

449

if missing.any? && respond_to?(method = "find_#{field}_filter_values")

449

if missing.any? && respond_to?(method = "find_#{field}_filter_values")

450

options[:values] += send(method, missing)

450

options[:values] += send(method, missing)

451

end

451

end

452

end

452

end

453

end

453

end

454

json[field] = options.stringify_keys

454

json[field] = options.stringify_keys

455

end

455

end

456

json

456

json

457

end

457

end

458

459

def all_projects

459

def all_projects

460

@all_projects ||= Project.visible.to_a

460

@all_projects ||= Project.visible.to_a

461

end

461

end

462

463

def all_projects_values

463

def all_projects_values

464

return @all_projects_values if @all_projects_values

464

return @all_projects_values if @all_projects_values

465

466

values = []

466

values = []

467

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

467

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

468

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

468

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

469

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

469

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

470

end

470

end

471

@all_projects_values = values

471

@all_projects_values = values

472

end

472

end

473

474

def project_values

474

def project_values

475

project_values = []

475

project_values = []

476

if User.current.logged? && User.current.memberships.any?

476

if User.current.logged? && User.current.memberships.any?

477

project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]

477

project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]

478

end

478

end

479

project_values += all_projects_values

479

project_values += all_projects_values

480

project_values

480

project_values

481

end

481

end

482

483

def subproject_values

483

def subproject_values

484

project.descendants.visible.collect{|s| [s.name, s.id.to_s] }

484

project.descendants.visible.collect{|s| [s.name, s.id.to_s] }

485

end

485

end

486

487

def principals

487

def principals

488

@principal ||= begin

488

@principal ||= begin

489

principals = []

489

principals = []

490

if project

490

if project

491

principals += project.principals.visible

491

principals += project.principals.visible

492

unless project.leaf?

492

unless project.leaf?

493

principals += Principal.member_of(project.descendants.visible).visible

493

principals += Principal.member_of(project.descendants.visible).visible

494

end

494

end

495

else

495

else

496

principals += Principal.member_of(all_projects).visible

496

principals += Principal.member_of(all_projects).visible

497

end

497

end

498

principals.uniq!

498

principals.uniq!

499

principals.sort!

499

principals.sort!

500

principals.reject! {|p| p.is_a?(GroupBuiltin)}

500

principals.reject! {|p| p.is_a?(GroupBuiltin)}

501

principals

501

principals

502

end

502

end

503

end

503

end

504

505

def users

505

def users

506

principals.select {|p| p.is_a?(User)}

506

principals.select {|p| p.is_a?(User)}

507

end

507

end

508

509

def author_values

509

def author_values

510

author_values = []

510

author_values = []

511

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

511

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

512

author_values += users.collect{|s| [s.name, s.id.to_s] }

512

author_values += users.collect{|s| [s.name, s.id.to_s] }

513

author_values

513

author_values

514

end

514

end

515

516

def assigned_to_values

516

def assigned_to_values

517

assigned_to_values = []

517

assigned_to_values = []

518

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

518

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

519

assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }

519

assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }

520

assigned_to_values

520

assigned_to_values

521

end

521

end

522

523

def fixed_version_values

523

def fixed_version_values

524

versions = []

524

versions = []

525

if project

525

if project

526

versions = project.shared_versions.to_a

526

versions = project.shared_versions.to_a

527

else

527

else

528

versions = Version.visible.where(:sharing => 'system').to_a

528

versions = Version.visible.where(:sharing => 'system').to_a

529

end

529

end

530

Version.sort_by_status(versions).collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s, l("version_status_#{s.status}")] }

530

Version.sort_by_status(versions).collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s, l("version_status_#{s.status}")] }

531

end

531

end

532

533

# Adds available filters

533

# Adds available filters

534

def initialize_available_filters

534

def initialize_available_filters

535

# implemented by sub-classes

535

# implemented by sub-classes

536

end

536

end

537

protected :initialize_available_filters

537

protected :initialize_available_filters

538

539

# Adds an available filter

539

# Adds an available filter

540

def add_available_filter(field, options)

540

def add_available_filter(field, options)

541

@available_filters ||= ActiveSupport::OrderedHash.new

541

@available_filters ||= ActiveSupport::OrderedHash.new

542

@available_filters[field] = QueryFilter.new(field, options)

542

@available_filters[field] = QueryFilter.new(field, options)

543

@available_filters

543

@available_filters

544

end

544

end

545

546

# Removes an available filter

546

# Removes an available filter

547

def delete_available_filter(field)

547

def delete_available_filter(field)

548

if @available_filters

548

if @available_filters

549

@available_filters.delete(field)

549

@available_filters.delete(field)

550

end

550

end

551

end

551

end

552

553

# Return a hash of available filters

553

# Return a hash of available filters

554

def available_filters

554

def available_filters

555

unless @available_filters

555

unless @available_filters

556

initialize_available_filters

556

initialize_available_filters

557

@available_filters ||= {}

557

@available_filters ||= {}

558

end

558

end

559

@available_filters

559

@available_filters

560

end

560

end

561

562

def add_filter(field, operator, values=nil)

562

def add_filter(field, operator, values=nil)

563

# values must be an array

563

# values must be an array

564

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

564

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

565

# check if field is defined as an available filter

565

# check if field is defined as an available filter

566

if available_filters.has_key? field

566

if available_filters.has_key? field

567

filter_options = available_filters[field]

567

filter_options = available_filters[field]

568

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

568

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

569

end

569

end

570

end

570

end

571

572

def add_short_filter(field, expression)

572

def add_short_filter(field, expression)

573

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

573

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

574

field_type = available_filters[field][:type]

574

field_type = available_filters[field][:type]

575

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

575

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

576

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

576

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

577

values = $1

577

values = $1

578

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

578

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

579

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

579

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

580

end

580

end

581

582

# Add multiple filters using +add_filter+

582

# Add multiple filters using +add_filter+

583

def add_filters(fields, operators, values)

583

def add_filters(fields, operators, values)

584

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

584

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

585

fields.each do |field|

585

fields.each do |field|

586

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

586

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

587

end

587

end

588

end

588

end

589

end

589

end

590

591

def has_filter?(field)

591

def has_filter?(field)

592

filters and filters[field]

592

filters and filters[field]

593

end

593

end

594

595

def type_for(field)

595

def type_for(field)

596

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

596

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

597

end

597

end

598

599

def operator_for(field)

599

def operator_for(field)

600

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

600

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

601

end

601

end

602

603

def values_for(field)

603

def values_for(field)

604

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

604

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

605

end

605

end

606

607

def value_for(field, index=0)

607

def value_for(field, index=0)

608

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

608

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

609

end

609

end

610

611

def label_for(field)

611

def label_for(field)

612

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

612

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

613

label ||= queried_class.human_attribute_name(field, :default => field)

613

label ||= queried_class.human_attribute_name(field, :default => field)

614

end

614

end

615

616

def self.add_available_column(column)

616

def self.add_available_column(column)

617

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

617

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

618

end

618

end

619

620

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

620

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

621

def groupable_columns

621

def groupable_columns

622

available_columns.select {|c| c.groupable}

622

available_columns.select {|c| c.groupable}

623

end

623

end

624

625

# Returns a Hash of columns and the key for sorting

625

# Returns a Hash of columns and the key for sorting

626

def sortable_columns

626

def sortable_columns

627

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

627

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

628

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

628

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

629

h

629

h

630

}

630

}

631

end

631

end

632

633

def columns

633

def columns

634

# preserve the column_names order

634

# preserve the column_names order

635

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

635

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

636

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

636

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

637

end.compact

637

end.compact

638

available_columns.select(&:frozen?) | cols

638

available_columns.select(&:frozen?) | cols

639

end

639

end

640

641

def inline_columns

641

def inline_columns

642

columns.select(&:inline?)

642

columns.select(&:inline?)

643

end

643

end

644

645

def block_columns

645

def block_columns

646

columns.reject(&:inline?)

646

columns.reject(&:inline?)

647

end

647

end

648

649

def available_inline_columns

649

def available_inline_columns

650

available_columns.select(&:inline?)

650

available_columns.select(&:inline?)

651

end

651

end

652

653

def available_block_columns

653

def available_block_columns

654

available_columns.reject(&:inline?)

654

available_columns.reject(&:inline?)

655

end

655

end

656

657

def available_totalable_columns

657

def available_totalable_columns

658

available_columns.select(&:totalable)

658

available_columns.select(&:totalable)

659

end

659

end

660

661

def default_columns_names

661

def default_columns_names

662

[]

662

[]

663

end

663

end

664

665

def default_totalable_names

665

def default_totalable_names

666

[]

666

[]

667

end

667

end

668

669

def column_names=(names)

669

def column_names=(names)

670

if names

670

if names

671

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

671

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

672

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

672

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

673

# Set column_names to nil if default columns

673

# Set column_names to nil if default columns

674

if names == default_columns_names

674

if names == default_columns_names

675

names = nil

675

names = nil

676

end

676

end

677

end

677

end

678

write_attribute(:column_names, names)

678

write_attribute(:column_names, names)

679

end

679

end

680

681

def has_column?(column)

681

def has_column?(column)

682

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

682

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

683

end

683

end

684

685

def has_custom_field_column?

685

def has_custom_field_column?

686

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

686

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

687

end

687

end

688

689

def has_default_columns?

689

def has_default_columns?

690

column_names.nil? || column_names.empty?

690

column_names.nil? || column_names.empty?

691

end

691

end

692

693

def totalable_columns

693

def totalable_columns

694

names = totalable_names

694

names = totalable_names

695

available_totalable_columns.select {|column| names.include?(column.name)}

695

available_totalable_columns.select {|column| names.include?(column.name)}

696

end

696

end

697

698

def totalable_names=(names)

698

def totalable_names=(names)

699

if names

699

if names

700

names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}

700

names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}

701

end

701

end

702

options[:totalable_names] = names

702

options[:totalable_names] = names

703

end

703

end

704

705

def totalable_names

705

def totalable_names

706

options[:totalable_names] || default_totalable_names || []

706

options[:totalable_names] || default_totalable_names || []

707

end

707

end

708

709

def sort_criteria=(arg)

709

def sort_criteria=(arg)

710

c = []

710

c = []

711

if arg.is_a?(Hash)

711

if arg.is_a?(Hash)

712

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

712

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

713

end

713

end

714

if arg

714

if arg

715

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

715

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

716

end

716

end

717

write_attribute(:sort_criteria, c)

717

write_attribute(:sort_criteria, c)

718

end

718

end

719

720

def sort_criteria

720

def sort_criteria

721

read_attribute(:sort_criteria) || []

721

read_attribute(:sort_criteria) || []

722

end

722

end

723

724

def sort_criteria_key(arg)

724

def sort_criteria_key(arg)

725

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

725

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

726

end

726

end

727

728

def sort_criteria_order(arg)

728

def sort_criteria_order(arg)

729

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

729

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

730

end

730

end

731

732

def sort_criteria_order_for(key)

732

def sort_criteria_order_for(key)

733

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

733

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

734

end

734

end

735

736

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

736

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

737

def group_by_sort_order

737

def group_by_sort_order

738

if column = group_by_column

738

if column = group_by_column

739

order = (sort_criteria_order_for(column.name) || column.default_order || 'asc').try(:upcase)

739

order = (sort_criteria_order_for(column.name) || column.default_order || 'asc').try(:upcase)

740

Array(column.sortable).map {|s| "#{s} #{order}"}

740

Array(column.sortable).map {|s| "#{s} #{order}"}

741

end

741

end

742

end

742

end

743

744

# Returns true if the query is a grouped query

744

# Returns true if the query is a grouped query

745

def grouped?

745

def grouped?

746

!group_by_column.nil?

746

!group_by_column.nil?

747

end

747

end

748

749

def group_by_column

749

def group_by_column

750

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

750

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

751

end

751

end

752

753

def group_by_statement

753

def group_by_statement

754

group_by_column.try(:groupable)

754

group_by_column.try(:groupable)

755

end

755

end

756

757

def project_statement

757

def project_statement

758

project_clauses = []

758

project_clauses = []

759

active_subprojects_ids = []

759

active_subprojects_ids = []

760

761

active_subprojects_ids = project.descendants.active.map(&:id) if project

761

active_subprojects_ids = project.descendants.active.map(&:id) if project

762

if active_subprojects_ids.any?

762

if active_subprojects_ids.any?

763

if has_filter?("subproject_id")

763

if has_filter?("subproject_id")

764

case operator_for("subproject_id")

764

case operator_for("subproject_id")

765

when '='

765

when '='

766

# include the selected subprojects

766

# include the selected subprojects

767

ids = [project.id] + values_for("subproject_id").map(&:to_i)

767

ids = [project.id] + values_for("subproject_id").map(&:to_i)

768

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

768

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

769

when '!'

769

when '!'

770

# exclude the selected subprojects

770

# exclude the selected subprojects

771

ids = [project.id] + active_subprojects_ids - values_for("subproject_id").map(&:to_i)

771

ids = [project.id] + active_subprojects_ids - values_for("subproject_id").map(&:to_i)

772

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

772

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

773

when '!*'

773

when '!*'

774

# main project only

774

# main project only

775

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

775

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

776

else

776

else

777

# all subprojects

777

# all subprojects

778

project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"

778

project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"

779

end

779

end

780

elsif Setting.display_subprojects_issues?

780

elsif Setting.display_subprojects_issues?

781

project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"

781

project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"

782

else

782

else

783

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

783

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

784

end

784

end

785

elsif project

785

elsif project

786

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

786

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

787

end

787

end

788

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

788

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

789

end

789

end

790

791

def statement

791

def statement

792

# filters clauses

792

# filters clauses

793

filters_clauses = []

793

filters_clauses = []

794

filters.each_key do |field|

794

filters.each_key do |field|

795

next if field == "subproject_id"

795

next if field == "subproject_id"

796

v = values_for(field).clone

796

v = values_for(field).clone

797

next unless v and !v.empty?

797

next unless v and !v.empty?

798

operator = operator_for(field)

798

operator = operator_for(field)

799

800

# "me" value substitution

800

# "me" value substitution

801

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

801

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

802

if v.delete("me")

802

if v.delete("me")

803

if User.current.logged?

803

if User.current.logged?

804

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

804

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

805

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

805

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

806

else

806

else

807

v.push("0")

807

v.push("0")

808

end

808

end

809

end

809

end

810

end

810

end

811

812

if field == 'project_id'

812

if field == 'project_id'

813

if v.delete('mine')

813

if v.delete('mine')

814

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

814

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

815

end

815

end

816

end

816

end

817

818

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

818

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

819

filters_clauses << sql_for_chained_custom_field(field, operator, v, $1, $2)

819

filters_clauses << sql_for_chained_custom_field(field, operator, v, $1, $2)

820

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

820

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

821

# custom field

821

# custom field

822

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

822

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

823

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

823

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

824

filters_clauses << sql_for_custom_field_attribute(field, operator, v, $1, $2)

824

filters_clauses << sql_for_custom_field_attribute(field, operator, v, $1, $2)

825

elsif respond_to?(method = "sql_for_#{field.gsub('.','_')}_field")

825

elsif respond_to?(method = "sql_for_#{field.gsub('.','_')}_field")

826

# specific statement

826

# specific statement

827

filters_clauses << send(method, field, operator, v)

827

filters_clauses << send(method, field, operator, v)

828

else

828

else

829

# regular field

829

# regular field

830

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

830

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

831

end

831

end

832

end if filters and valid?

832

end if filters and valid?

833

834

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

834

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

835

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

835

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

836

filters_clauses << c.custom_field.visibility_by_project_condition

836

filters_clauses << c.custom_field.visibility_by_project_condition

837

end

837

end

838

839

filters_clauses << project_statement

839

filters_clauses << project_statement

840

filters_clauses.reject!(&:blank?)

840

filters_clauses.reject!(&:blank?)

841

842

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

842

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

843

end

843

end

844

845

# Returns the sum of values for the given column

845

# Returns the sum of values for the given column

846

def total_for(column)

846

def total_for(column)

847

total_with_scope(column, base_scope)

847

total_with_scope(column, base_scope)

848

end

848

end

849

850

# Returns a hash of the sum of the given column for each group,

850

# Returns a hash of the sum of the given column for each group,

851

# or nil if the query is not grouped

851

# or nil if the query is not grouped

852

def total_by_group_for(column)

852

def total_by_group_for(column)

853

grouped_query do |scope|

853

grouped_query do |scope|

854

total_with_scope(column, scope)

854

total_with_scope(column, scope)

855

end

855

end

856

end

856

end

857

858

def totals

858

def totals

859

totals = totalable_columns.map {|column| [column, total_for(column)]}

859

totals = totalable_columns.map {|column| [column, total_for(column)]}

860

yield totals if block_given?

860

yield totals if block_given?

861

totals

861

totals

862

end

862

end

863

864

def totals_by_group

864

def totals_by_group

865

totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}

865

totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}

866

yield totals if block_given?

866

yield totals if block_given?

867

totals

867

totals

868

end

868

end

869

870

private

870

private

871

872

def grouped_query(&block)

872

def grouped_query(&block)

873

r = nil

873

r = nil

874

if grouped?

874

if grouped?

875

begin

875

begin

876

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

876

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

877

r = yield base_group_scope

877

r = yield base_group_scope

878

rescue ActiveRecord::RecordNotFound

878

rescue ActiveRecord::RecordNotFound

879

r = {nil => yield(base_scope)}

879

r = {nil => yield(base_scope)}

880

end

880

end

881

c = group_by_column

881

c = group_by_column

882

if c.is_a?(QueryCustomFieldColumn)

882

if c.is_a?(QueryCustomFieldColumn)

883

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

883

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

884

end

884

end

885

end

885

end

886

r

886

r

887

rescue ::ActiveRecord::StatementInvalid => e

887

rescue ::ActiveRecord::StatementInvalid => e

888

raise StatementInvalid.new(e.message)

888

raise StatementInvalid.new(e.message)

889

end

889

end

890

891

def total_with_scope(column, scope)

891

def total_with_scope(column, scope)

892

unless column.is_a?(QueryColumn)

892

unless column.is_a?(QueryColumn)

893

column = column.to_sym

893

column = column.to_sym

894

column = available_totalable_columns.detect {|c| c.name == column}

894

column = available_totalable_columns.detect {|c| c.name == column}

895

end

895

end

896

if column.is_a?(QueryCustomFieldColumn)

896

if column.is_a?(QueryCustomFieldColumn)

897

custom_field = column.custom_field

897

custom_field = column.custom_field

898

send "total_for_custom_field", custom_field, scope

898

send "total_for_custom_field", custom_field, scope

899

else

899

else

900

send "total_for_#{column.name}", scope

900

send "total_for_#{column.name}", scope

901

end

901

end

902

rescue ::ActiveRecord::StatementInvalid => e

902

rescue ::ActiveRecord::StatementInvalid => e

903

raise StatementInvalid.new(e.message)

903

raise StatementInvalid.new(e.message)

904

end

904

end

905

906

def base_scope

906

def base_scope

907

raise "unimplemented"

907

raise "unimplemented"

908

end

908

end

909

910

def base_group_scope

910

def base_group_scope

911

base_scope.

911

base_scope.

912

joins(joins_for_order_statement(group_by_statement)).

912

joins(joins_for_order_statement(group_by_statement)).

913

group(group_by_statement)

913

group(group_by_statement)

914

end

914

end

915

916

def total_for_custom_field(custom_field, scope, &block)

916

def total_for_custom_field(custom_field, scope, &block)

917

total = custom_field.format.total_for_scope(custom_field, scope)

917

total = custom_field.format.total_for_scope(custom_field, scope)

918

total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}

918

total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}

919

total

919

total

920

end

920

end

921

922

def map_total(total, &block)

922

def map_total(total, &block)

923

if total.is_a?(Hash)

923

if total.is_a?(Hash)

924

total.keys.each {|k| total[k] = yield total[k]}

924

total.keys.each {|k| total[k] = yield total[k]}

925

else

925

else

926

total = yield total

926

total = yield total

927

end

927

end

928

total

928

total

929

end

929

end

930

931

def sql_for_custom_field(field, operator, value, custom_field_id)

931

def sql_for_custom_field(field, operator, value, custom_field_id)

932

db_table = CustomValue.table_name

932

db_table = CustomValue.table_name

933

db_field = 'value'

933

db_field = 'value'

934

filter = @available_filters[field]

934

filter = @available_filters[field]

935

return nil unless filter

935

return nil unless filter

936

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

936

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

937

if value.delete('me')

937

if value.delete('me')

938

value.push User.current.id.to_s

938

value.push User.current.id.to_s

939

end

939

end

940

end

940

end

941

not_in = nil

941

not_in = nil

942

if operator == '!'

942

if operator == '!'

943

# Makes ! operator work for custom fields with multiple values

943

# Makes ! operator work for custom fields with multiple values

944

operator = '='

944

operator = '='

945

not_in = 'NOT'

945

not_in = 'NOT'

946

end

946

end

947

customized_key = "id"

947

customized_key = "id"

948

customized_class = queried_class

948

customized_class = queried_class

949

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

949

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

950

assoc = $1

950

assoc = $1

951

customized_key = "#{assoc}_id"

951

customized_key = "#{assoc}_id"

952

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

952

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

953

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

953

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

954

end

954

end

955

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

955

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

956

if operator =~ /[<>]/

956

if operator =~ /[<>]/

957

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

957

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

958

end

958

end

959

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

959

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

960

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

960

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

961

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

961

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

962

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

962

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

963

end

963

end

964

965

def sql_for_chained_custom_field(field, operator, value, custom_field_id, chained_custom_field_id)

965

def sql_for_chained_custom_field(field, operator, value, custom_field_id, chained_custom_field_id)

966

not_in = nil

966

not_in = nil

967

if operator == '!'

967

if operator == '!'

968

# Makes ! operator work for custom fields with multiple values

968

# Makes ! operator work for custom fields with multiple values

969

operator = '='

969

operator = '='

970

not_in = 'NOT'

970

not_in = 'NOT'

971

end

971

end

972

973

filter = available_filters[field]

973

filter = available_filters[field]

974

target_class = filter[:through].format.target_class

974

target_class = filter[:through].format.target_class

975

976

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

976

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

977

"SELECT customized_id FROM #{CustomValue.table_name}" +

977

"SELECT customized_id FROM #{CustomValue.table_name}" +

978

" WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +

978

" WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +

979

" AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +

979

" AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +

980

" SELECT customized_id FROM #{CustomValue.table_name}" +

980

" SELECT customized_id FROM #{CustomValue.table_name}" +

981

" WHERE customized_type='#{target_class}' AND custom_field_id=#{chained_custom_field_id}" +

981

" WHERE customized_type='#{target_class}' AND custom_field_id=#{chained_custom_field_id}" +

982

" AND #{sql_for_field(field, operator, value, CustomValue.table_name, 'value')}))"

982

" AND #{sql_for_field(field, operator, value, CustomValue.table_name, 'value')}))"

983

984

end

984

end

985

986

def sql_for_custom_field_attribute(field, operator, value, custom_field_id, attribute)

986

def sql_for_custom_field_attribute(field, operator, value, custom_field_id, attribute)

987

attribute = 'effective_date' if attribute == 'due_date'

987

attribute = 'effective_date' if attribute == 'due_date'

988

not_in = nil

988

not_in = nil

989

if operator == '!'

989

if operator == '!'

990

# Makes ! operator work for custom fields with multiple values

990

# Makes ! operator work for custom fields with multiple values

991

operator = '='

991

operator = '='

992

not_in = 'NOT'

992

not_in = 'NOT'

993

end

993

end

994

995

filter = available_filters[field]

995

filter = available_filters[field]

996

target_table_name = filter[:field].format.target_class.table_name

996

target_table_name = filter[:field].format.target_class.table_name

997

998

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

998

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

999

"SELECT customized_id FROM #{CustomValue.table_name}" +

999

"SELECT customized_id FROM #{CustomValue.table_name}" +

1000

" WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +

1000

" WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +

1001

" AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +

1001

" AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +

1002

" SELECT id FROM #{target_table_name} WHERE #{sql_for_field(field, operator, value, filter[:field].format.target_class.table_name, attribute)}))"

1002

" SELECT id FROM #{target_table_name} WHERE #{sql_for_field(field, operator, value, filter[:field].format.target_class.table_name, attribute)}))"

1003

end

1003

end

1004

1005

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

1005

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

1006

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

1006

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

1007

sql = ''

1007

sql = ''

1008

case operator

1008

case operator

1009

when "="

1009

when "="

1010

if value.any?

1010

if value.any?

1011

case type_for(field)

1011

case type_for(field)

1012

when :date, :date_past

1012

when :date, :date_past

1013

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

1013

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

1014

when :integer

1014

when :integer

1015

int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")

1015

int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")

1016

if int_values.present?

1016

if int_values.present?

1017

if is_custom_filter

1017

if is_custom_filter

1018

sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) IN (#{int_values}))"

1018

sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) IN (#{int_values}))"

1019

else

1019

else

1020

sql = "#{db_table}.#{db_field} IN (#{int_values})"

1020

sql = "#{db_table}.#{db_field} IN (#{int_values})"

1021

end

1021

end

1022

else

1022

else

1023

sql = "1=0"

1023

sql = "1=0"

1024

end

1024

end

1025

when :float

1025

when :float

1026

if is_custom_filter

1026

if is_custom_filter

1027

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

1027

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

1028

else

1028

else

1029

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

1029

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

1030

end

1030

end

1031

else

1031

else

1032

sql = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])

1032

sql = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])

1033

end

1033

end

1034

else

1034

else

1035

# IN an empty set

1035

# IN an empty set

1036

sql = "1=0"

1036

sql = "1=0"

1037

end

1037

end

1038

when "!"

1038

when "!"

1039

if value.any?

1039

if value.any?

1040

sql = queried_class.send(:sanitize_sql_for_conditions, ["(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (?))", value])

1040

sql = queried_class.send(:sanitize_sql_for_conditions, ["(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (?))", value])

1041

else

1041

else

1042

# NOT IN an empty set

1042

# NOT IN an empty set

1043

sql = "1=1"

1043

sql = "1=1"

1044

end

1044

end

1045

when "!*"

1045

when "!*"

1046

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

1046

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

1047

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

1047

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

1048

when "*"

1048

when "*"

1049

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

1049

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

1050

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

1050

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

1051

when ">="

1051

when ">="

1052

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

1052

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

1053

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

1053

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

1054

else

1054

else

1055

if is_custom_filter

1055

if is_custom_filter

1056

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

1056

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

1057

else

1057

else

1058

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

1058

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

1059

end

1059

end

1060

end

1060

end

1061

when "<="

1061

when "<="

1062

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

1062

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

1063

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

1063

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

1064

else

1064

else

1065

if is_custom_filter

1065

if is_custom_filter

1066

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

1066

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

1067

else

1067

else

1068

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

1068

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

1069

end

1069

end

1070

end

1070

end

1071

when "><"

1071

when "><"

1072

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

1072

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

1073

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

1073

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

1074

else

1074

else

1075

if is_custom_filter

1075

if is_custom_filter

1076

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

1076

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

1077

else

1077

else

1078

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

1078

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

1079

end

1079

end

1080

end

1080

end

1081

when "o"

1081

when "o"

1082

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

1082

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

1083

when "c"

1083

when "c"

1084

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

1084

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

1085

when "><t-"

1085

when "><t-"

1086

# between today - n days and today

1086

# between today - n days and today

1087

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

1087

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

1088

when ">t-"

1088

when ">t-"

1089

# >= today - n days

1089

# >= today - n days

1090

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

1090

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

1091

when "<t-"

1091

when "<t-"

1092

# <= today - n days

1092

# <= today - n days

1093

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

1093

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

1094

when "t-"

1094

when "t-"

1095

# = n days in past

1095

# = n days in past

1096

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

1096

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

1097

when "><t+"

1097

when "><t+"

1098

# between today and today + n days

1098

# between today and today + n days

1099

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

1099

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

1100

when ">t+"

1100

when ">t+"

1101

# >= today + n days

1101

# >= today + n days

1102

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

1102

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

1103

when "<t+"

1103

when "<t+"

1104

# <= today + n days

1104

# <= today + n days

1105

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

1105

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

1106

when "t+"

1106

when "t+"

1107

# = today + n days

1107

# = today + n days

1108

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

1108

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

1109

when "t"

1109

when "t"

1110

# = today

1110

# = today

1111

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

1111

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

1112

when "ld"

1112

when "ld"

1113

# = yesterday

1113

# = yesterday

1114

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

1114

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

1115

when "w"

1115

when "w"

1116

# = this week

1116

# = this week

1117

first_day_of_week = l(:general_first_day_of_week).to_i

1117

first_day_of_week = l(:general_first_day_of_week).to_i

1118

day_of_week = User.current.today.cwday

1118

day_of_week = User.current.today.cwday

1119

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

1119

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

1120

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

1120

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

1121

when "lw"

1121

when "lw"

1122

# = last week

1122

# = last week

1123

first_day_of_week = l(:general_first_day_of_week).to_i

1123

first_day_of_week = l(:general_first_day_of_week).to_i

1124

day_of_week = User.current.today.cwday

1124

day_of_week = User.current.today.cwday

1125

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

1125

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

1126

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

1126

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

1127

when "l2w"

1127

when "l2w"

1128

# = last 2 weeks

1128

# = last 2 weeks

1129

first_day_of_week = l(:general_first_day_of_week).to_i

1129

first_day_of_week = l(:general_first_day_of_week).to_i

1130

day_of_week = User.current.today.cwday

1130

day_of_week = User.current.today.cwday

1131

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

1131

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

1132

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

1132

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

1133

when "m"

1133

when "m"

1134

# = this month

1134

# = this month

1135

date = User.current.today

1135

date = User.current.today

1136

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

1136

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

1137

when "lm"

1137

when "lm"

1138

# = last month

1138

# = last month

1139

date = User.current.today.prev_month

1139

date = User.current.today.prev_month

1140

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

1140

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

1141

when "y"

1141

when "y"

1142

# = this year

1142

# = this year

1143

date = User.current.today

1143

date = User.current.today

1144

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

1144

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

1145

when "~"

1145

when "~"

1146

sql = sql_contains("#{db_table}.#{db_field}", value.first)

1146

sql = sql_contains("#{db_table}.#{db_field}", value.first)

1147

when "!~"

1147

when "!~"

1148

sql = sql_contains("#{db_table}.#{db_field}", value.first, false)

1148

sql = sql_contains("#{db_table}.#{db_field}", value.first, false)

1149

else

1149

else

1150

raise "Unknown query operator #{operator}"

1150

raise "Unknown query operator #{operator}"

1151

end

1151

end

1152

1153

return sql

1153

return sql

1154

end

1154

end

1155

1156

# Returns a SQL LIKE statement with wildcards

1156

# Returns a SQL LIKE statement with wildcards

1157

def sql_contains(db_field, value, match=true)

1157

def sql_contains(db_field, value, match=true)

1158

queried_class.send :sanitize_sql_for_conditions,

1158

queried_class.send :sanitize_sql_for_conditions,

1159

[Redmine::Database.like(db_field, '?', :match => match), "%#{value}%"]

1159

[Redmine::Database.like(db_field, '?', :match => match), "%#{value}%"]

1160

end

1160

end

1161

1162

# Adds a filter for the given custom field

1162

# Adds a filter for the given custom field

1163

def add_custom_field_filter(field, assoc=nil)

1163

def add_custom_field_filter(field, assoc=nil)

1164

options = field.query_filter_options(self)

1164

options = field.query_filter_options(self)

1165

1166

filter_id = "cf_#{field.id}"

1166

filter_id = "cf_#{field.id}"

1167

filter_name = field.name

1167

filter_name = field.name

1168

if assoc.present?

1168

if assoc.present?

1169

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

1169

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

1170

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

1170

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

1171

end

1171

end

1172

add_available_filter filter_id, options.merge({

1172

add_available_filter filter_id, options.merge({

1173

:name => filter_name,

1173

:name => filter_name,

1174

:field => field

1174

:field => field

1175

})

1175

})

1176

end

1176

end

1177

1178

# Adds filters for custom fields associated to the custom field target class

1178

# Adds filters for custom fields associated to the custom field target class

1179

# Eg. having a version custom field "Milestone" for issues and a date custom field "Release date"

1179

# Eg. having a version custom field "Milestone" for issues and a date custom field "Release date"

1180

# for versions, it will add an issue filter on Milestone'e Release date.

1180

# for versions, it will add an issue filter on Milestone'e Release date.

1181

def add_chained_custom_field_filters(field)

1181

def add_chained_custom_field_filters(field)

1182

klass = field.format.target_class

1182

klass = field.format.target_class

1183

if klass

1183

if klass

1184

CustomField.where(:is_filter => true, :type => "#{klass.name}CustomField").each do |chained|

1184

CustomField.where(:is_filter => true, :type => "#{klass.name}CustomField").each do |chained|

1185

options = chained.query_filter_options(self)

1185

options = chained.query_filter_options(self)

1186

1187

filter_id = "cf_#{field.id}.cf_#{chained.id}"

1187

filter_id = "cf_#{field.id}.cf_#{chained.id}"

1188

filter_name = chained.name

1188

filter_name = chained.name

1189

1190

add_available_filter filter_id, options.merge({

1190

add_available_filter filter_id, options.merge({

1191

:name => l(:label_attribute_of_object, :name => chained.name, :object_name => field.name),

1191

:name => l(:label_attribute_of_object, :name => chained.name, :object_name => field.name),

1192

:field => chained,

1192

:field => chained,

1193

:through => field

1193

:through => field

1194

})

1194

})

1195

end

1195

end

1196

end

1196

end

1197

end

1197

end

1198

1199

# Adds filters for the given custom fields scope

1199

# Adds filters for the given custom fields scope

1200

def add_custom_fields_filters(scope, assoc=nil)

1200

def add_custom_fields_filters(scope, assoc=nil)

1201

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

1201

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

1202

add_custom_field_filter(field, assoc)

1202

add_custom_field_filter(field, assoc)

1203

if assoc.nil?

1203

if assoc.nil?

1204

add_chained_custom_field_filters(field)

1204

add_chained_custom_field_filters(field)

1205

1206

if field.format.target_class && field.format.target_class == Version

1206

if field.format.target_class && field.format.target_class == Version

1207

add_available_filter "cf_#{field.id}.due_date",

1207

add_available_filter "cf_#{field.id}.due_date",

1208

:type => :date,

1208

:type => :date,

1209

:field => field,

1209

:field => field,

1210

:name => l(:label_attribute_of_object, :name => l(:field_effective_date), :object_name => field.name)

1210

:name => l(:label_attribute_of_object, :name => l(:field_effective_date), :object_name => field.name)

1211

1212

add_available_filter "cf_#{field.id}.status",

1212

add_available_filter "cf_#{field.id}.status",

1213

:type => :list,

1213

:type => :list,

1214

:field => field,

1214

:field => field,

1215

:name => l(:label_attribute_of_object, :name => l(:field_status), :object_name => field.name),

1215

:name => l(:label_attribute_of_object, :name => l(:field_status), :object_name => field.name),

1216

:values => Version::VERSION_STATUSES.map{|s| [l("version_status_#{s}"), s] }

1216

:values => Version::VERSION_STATUSES.map{|s| [l("version_status_#{s}"), s] }

1217

end

1217

end

1218

end

1218

end

1219

end

1219

end

1220

end

1220

end

1221

1222

# Adds filters for the given associations custom fields

1222

# Adds filters for the given associations custom fields

1223

def add_associations_custom_fields_filters(*associations)

1223

def add_associations_custom_fields_filters(*associations)

1224

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

1224

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

1225

associations.each do |assoc|

1225

associations.each do |assoc|

1226

association_klass = queried_class.reflect_on_association(assoc).klass

1226

association_klass = queried_class.reflect_on_association(assoc).klass

1227

fields_by_class.each do |field_class, fields|

1227

fields_by_class.each do |field_class, fields|

1228

if field_class.customized_class <= association_klass

1228

if field_class.customized_class <= association_klass

1229

fields.sort.each do |field|

1229

fields.sort.each do |field|

1230

add_custom_field_filter(field, assoc)

1230

add_custom_field_filter(field, assoc)

1231

end

1231

end

1232

end

1232

end

1233

end

1233

end

1234

end

1234

end

1235

end

1235

end

1236

1237

def quoted_time(time, is_custom_filter)

1237

def quoted_time(time, is_custom_filter)

1238

if is_custom_filter

1238

if is_custom_filter

1239

# Custom field values are stored as strings in the DB

1239

# Custom field values are stored as strings in the DB

1240

# using this format that does not depend on DB date representation

1240

# using this format that does not depend on DB date representation

1241

time.strftime("%Y-%m-%d %H:%M:%S")

1241

time.strftime("%Y-%m-%d %H:%M:%S")

1242

else

1242

else

1243

self.class.connection.quoted_date(time)

1243

self.class.connection.quoted_date(time)

1244

end

1244

end

1245

end

1245

end

1246

1247

def date_for_user_time_zone(y, m, d)

1247

def date_for_user_time_zone(y, m, d)

1248

if tz = User.current.time_zone

1248

if tz = User.current.time_zone

1249

tz.local y, m, d

1249

tz.local y, m, d

1250

else

1250

else

1251

Time.local y, m, d

1251

Time.local y, m, d

1252

end

1252

end

1253

end

1253

end

1254

1255

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

1255

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

1256

def date_clause(table, field, from, to, is_custom_filter)

1256

def date_clause(table, field, from, to, is_custom_filter)

1257

s = []

1257

s = []

1258

if from

1258

if from

1259

if from.is_a?(Date)

1259

if from.is_a?(Date)

1260

from = date_for_user_time_zone(from.year, from.month, from.day).yesterday.end_of_day

1260

from = date_for_user_time_zone(from.year, from.month, from.day).yesterday.end_of_day

1261

else

1261

else

1262

from = from - 1 # second

1262

from = from - 1 # second

1263

end

1263

end

1264

if self.class.default_timezone == :utc

1264

if self.class.default_timezone == :utc

1265

from = from.utc

1265

from = from.utc

1266

end

1266

end

1267

s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])

1267

s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])

1268

end

1268

end

1269

if to

1269

if to

1270

if to.is_a?(Date)

1270

if to.is_a?(Date)

1271

to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day

1271

to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day

1272

end

1272

end

1273

if self.class.default_timezone == :utc

1273

if self.class.default_timezone == :utc

1274

to = to.utc

1274

to = to.utc

1275

end

1275

end

1276

s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])

1276

s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])

1277

end

1277

end

1278

s.join(' AND ')

1278

s.join(' AND ')

1279

end

1279

end

1280

1281

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

1281

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

1282

def relative_date_clause(table, field, days_from, days_to, is_custom_filter)

1282

def relative_date_clause(table, field, days_from, days_to, is_custom_filter)

1283

date_clause(table, field, (days_from ? User.current.today + days_from : nil), (days_to ? User.current.today + days_to : nil), is_custom_filter)

1283

date_clause(table, field, (days_from ? User.current.today + days_from : nil), (days_to ? User.current.today + days_to : nil), is_custom_filter)

1284

end

1284

end

1285

1286

# Returns a Date or Time from the given filter value

1286

# Returns a Date or Time from the given filter value

1287

def parse_date(arg)

1287

def parse_date(arg)

1288

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

1288

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

1289

Time.parse(arg) rescue nil

1289

Time.parse(arg) rescue nil

1290

else

1290

else

1291

Date.parse(arg) rescue nil

1291

Date.parse(arg) rescue nil

1292

end

1292

end

1293

end

1293

end

1294

1295

# Additional joins required for the given sort options

1295

# Additional joins required for the given sort options

1296

def joins_for_order_statement(order_options)

1296

def joins_for_order_statement(order_options)

1297

joins = []

1297

joins = []

1298

1299

if order_options

1299

if order_options

1300

if order_options.include?('authors')

1301

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

1302

end

1303

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

1300

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

1304

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

1301

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

1305

join = column && column.custom_field.join_for_order_statement

1302

join = column && column.custom_field.join_for_order_statement

1306

if join

1303

if join

1307

joins << join

1304

joins << join

1308

end

1305

end

1309

end

1306

end

1310

end

1307

end

1311

1308

1312

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

1309

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

1313

end

1310

end

1314

end

1311

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-2016  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 IssueQuery < Query
               self.queried_class = Issue
               self.view_permission = :view_issues
               self.available_columns = [
                 QueryColumn.new(:id, :sortable => "#{Issue.table_name}.id", :default_order => 'desc', :caption => '#', :frozen => true),
                 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
                 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
                 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
                 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
                 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
                 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
                 QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
                 QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
                 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
                 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
                 QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
                 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
                 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
                 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours", :totalable => true),
                 QueryColumn.new(:total_estimated_hours,
                   :sortable => "COALESCE((SELECT SUM(estimated_hours) FROM #{Issue.table_name} subtasks" +
                     " WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",
                   :default_order => 'desc'),
                 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
                 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
                 QueryColumn.new(:closed_on, :sortable => "#{Issue.table_name}.closed_on", :default_order => 'desc'),
                 QueryColumn.new(:relations, :caption => :label_related_issues),
                 QueryColumn.new(:description, :inline => false)
               ]
               def initialize(attributes=nil, *args)
                 super attributes
                 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
               end
               def draw_relations
                 r = options[:draw_relations]
                 r.nil? || r == '1'
               end
               def draw_relations=(arg)
                 options[:draw_relations] = (arg == '0' ? '0' : nil)
               end
               def draw_progress_line
                 r = options[:draw_progress_line]
                 r == '1'
               end
               def draw_progress_line=(arg)
                 options[:draw_progress_line] = (arg == '1' ? '1' : nil)
               end
               def build_from_params(params)
                 super
                 self.draw_relations = params[:draw_relations] || (params[:query] && params[:query][:draw_relations])
                 self.draw_progress_line = params[:draw_progress_line] || (params[:query] && params[:query][:draw_progress_line])
                 self
               end
               def initialize_available_filters
                 add_available_filter "status_id",
                   :type => :list_status, :values => lambda { IssueStatus.sorted.collect{|s| [s.name, s.id.to_s] } }
                 add_available_filter("project_id",
                   :type => :list, :values => lambda { project_values }
                 ) if project.nil?
                 add_available_filter "tracker_id",
                   :type => :list, :values => trackers.collect{|s| [s.name, s.id.to_s] }
                 add_available_filter "priority_id",
                   :type => :list, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }
                 add_available_filter("author_id",
                   :type => :list, :values => lambda { author_values }
                 )
                 add_available_filter("assigned_to_id",
                   :type => :list_optional, :values => lambda { assigned_to_values }
                 )
                 add_available_filter("member_of_group",
                   :type => :list_optional, :values => lambda { Group.givable.visible.collect {|g| [g.name, g.id.to_s] } }
                 )
                 add_available_filter("assigned_to_role",
                   :type => :list_optional, :values => lambda { Role.givable.collect {|r| [r.name, r.id.to_s] } }
                 )
                 add_available_filter "fixed_version_id",
                   :type => :list_optional, :values => lambda { fixed_version_values }
                 add_available_filter "fixed_version.due_date",
                   :type => :date,
                   :name => l(:label_attribute_of_fixed_version, :name => l(:field_effective_date))
                 add_available_filter "fixed_version.status",
                   :type => :list,
                   :name => l(:label_attribute_of_fixed_version, :name => l(:field_status)),
                   :values => Version::VERSION_STATUSES.map{|s| [l("version_status_#{s}"), s] }
                 add_available_filter "category_id",
                   :type => :list_optional,
                   :values => lambda { project.issue_categories.collect{|s| [s.name, s.id.to_s] } } if project
                 add_available_filter "subject", :type => :text
                 add_available_filter "description", :type => :text
                 add_available_filter "created_on", :type => :date_past
                 add_available_filter "updated_on", :type => :date_past
                 add_available_filter "closed_on", :type => :date_past
                 add_available_filter "start_date", :type => :date
                 add_available_filter "due_date", :type => :date
                 add_available_filter "estimated_hours", :type => :float
                 add_available_filter "done_ratio", :type => :integer
                 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
                   User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
                   add_available_filter "is_private",
                     :type => :list,
                     :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]
                 end
                 if User.current.logged?
                   add_available_filter "watcher_id",
                     :type => :list, :values => [["<< #{l(:label_me)} >>", "me"]]
                 end
                 if project && !project.leaf?
                   add_available_filter "subproject_id",
                     :type => :list_subprojects,
                     :values => lambda { subproject_values }
                 end
                 issue_custom_fields = project ? project.all_issue_custom_fields : IssueCustomField.where(:is_for_all => true)
                 add_custom_fields_filters(issue_custom_fields)
                 add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
                 IssueRelation::TYPES.each do |relation_type, options|
                   add_available_filter relation_type, :type => :relation, :label => options[:name], :values => lambda {all_projects_values}
                 end
                 add_available_filter "parent_id", :type => :tree, :label => :field_parent_issue
                 add_available_filter "child_id", :type => :tree, :label => :label_subtask_plural
                 add_available_filter "issue_id", :type => :integer, :label => :label_issue
                 Tracker.disabled_core_fields(trackers).each {|field|
                   delete_available_filter field
                 }
               end
               def available_columns
                 return @available_columns if @available_columns
                 @available_columns = self.class.available_columns.dup
                 @available_columns += (project ?
                                         project.all_issue_custom_fields :
                                         IssueCustomField
                                        ).visible.collect {|cf| QueryCustomFieldColumn.new(cf) }
                 if User.current.allowed_to?(:view_time_entries, project, :global => true)
                   index = @available_columns.find_index {|column| column.name == :total_estimated_hours}
                   index = (index ? index + 1 : -1)
                   # insert the column after total_estimated_hours or at the end
                   @available_columns.insert index, QueryColumn.new(:spent_hours,
                     :sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id), 0)",
                     :default_order => 'desc',
                     :caption => :label_spent_time,
                     :totalable => true
                   )
                   @available_columns.insert index+1, QueryColumn.new(:total_spent_hours,
                     :sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} JOIN #{Issue.table_name} subtasks ON subtasks.id = #{TimeEntry.table_name}.issue_id" +
                       " WHERE subtasks.root_id = #{Issue.table_name}.root_id AND subtasks.lft >= #{Issue.table_name}.lft AND subtasks.rgt <= #{Issue.table_name}.rgt), 0)",
                     :default_order => 'desc',
                     :caption => :label_total_spent_time
                   )
                 end
                 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
                   User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
                   @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
                 end
                 disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
                 @available_columns.reject! {|column|
                   disabled_fields.include?(column.name.to_s)
                 }
                 @available_columns
               end
               def default_columns_names
                 @default_columns_names ||= begin
                   default_columns = Setting.issue_list_default_columns.map(&:to_sym)
                   project.present? ? default_columns : [:project] | default_columns
                 end
               end
               def default_totalable_names
                 Setting.issue_list_default_totals.map(&:to_sym)
               end
               def base_scope
                 Issue.visible.joins(:status, :project).where(statement)
               end
               # Returns the issue count
               def issue_count
                 base_scope.count
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the issue count by group or nil if query is not grouped
               def issue_count_by_group
                 grouped_query do |scope|
                   scope.count
                 end
               end
               # Returns sum of all the issue's estimated_hours
               def total_for_estimated_hours(scope)
                 map_total(scope.sum(:estimated_hours)) {|t| t.to_f.round(2)}
               end
               # Returns sum of all the issue's time entries hours
               def total_for_spent_hours(scope)
                 total = if group_by_column.try(:name) == :project
                   # TODO: remove this when https://github.com/rails/rails/issues/21922 is fixed
                   # We have to do a custom join without the time_entries.project_id column
                   # that would trigger a ambiguous column name error
                   scope.joins("JOIN (SELECT issue_id, hours FROM #{TimeEntry.table_name}) AS joined_time_entries ON joined_time_entries.issue_id = #{Issue.table_name}.id").
                     sum("joined_time_entries.hours")
                 else
                   scope.joins(:time_entries).sum("#{TimeEntry.table_name}.hours")
                 end
                 map_total(total) {|t| t.to_f.round(2)}
               end
               # Returns the issues
               # Valid options are :order, :offset, :limit, :include, :conditions
               def issues(options={})
                 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
                 scope = Issue.visible.
                   joins(:status, :project).
                   where(statement).
                   includes(([:status, :project] + (options[:include] || [])).uniq).
                   where(options[:conditions]).
                   order(order_option).
                   joins(joins_for_order_statement(order_option.join(','))).
                   limit(options[:limit]).
                   offset(options[:offset])
                 scope = scope.preload(:custom_values)
                 if has_column?(:author)
                   scope = scope.preload(:author)
                 end
                 issues = scope.to_a
                 if has_column?(:spent_hours)
                   Issue.load_visible_spent_hours(issues)
                 end
                 if has_column?(:total_spent_hours)
                   Issue.load_visible_total_spent_hours(issues)
                 end
                 if has_column?(:relations)
                   Issue.load_visible_relations(issues)
                 end
                 issues
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the issues ids
               def issue_ids(options={})
                 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
                 Issue.visible.
                   joins(:status, :project).
                   where(statement).
                   includes(([:status, :project] + (options[:include] || [])).uniq).
                   references(([:status, :project] + (options[:include] || [])).uniq).
                   where(options[:conditions]).
                   order(order_option).
                   joins(joins_for_order_statement(order_option.join(','))).
                   limit(options[:limit]).
                   offset(options[:offset]).
                   pluck(:id)
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the journals
               # Valid options are :order, :offset, :limit
               def journals(options={})
                 Journal.visible.
                   joins(:issue => [:project, :status]).
                   where(statement).
                   order(options[:order]).
                   limit(options[:limit]).
                   offset(options[:offset]).
                   preload(:details, :user, {:issue => [:project, :author, :tracker, :status]}).
                   to_a
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               # Returns the versions
               # Valid options are :conditions
               def versions(options={})
                 Version.visible.
                   where(project_statement).
                   where(options[:conditions]).
                   includes(:project).
                   references(:project).
                   to_a
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               def sql_for_watcher_id_field(field, operator, value)
                 db_table = Watcher.table_name
                 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
                   sql_for_field(field, '=', value, db_table, 'user_id') + ')'
               end
               def sql_for_member_of_group_field(field, operator, value)
                 if operator == '*' # Any group
                   groups = Group.givable
                   operator = '=' # Override the operator since we want to find by assigned_to
                 elsif operator == "!*"
                   groups = Group.givable
                   operator = '!' # Override the operator since we want to find by assigned_to
                 else
                   groups = Group.where(:id => value).to_a
                 end
                 groups ||= []
                 members_of_groups = groups.inject([]) {|user_ids, group|
                   user_ids + group.user_ids + [group.id]
                 }.uniq.compact.sort.collect(&:to_s)
                 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
               end
               def sql_for_assigned_to_role_field(field, operator, value)
                 case operator
                 when "*", "!*" # Member / Not member
                   sw = operator == "!*" ? 'NOT' : ''
                   nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
                   "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
                     " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
                 when "=", "!"
                   role_cond = value.any? ?
                     "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{self.class.connection.quote_string(val)}'"}.join(",") + ")" :
                     "1=0"
                   sw = operator == "!" ? 'NOT' : ''
                   nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
                   "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
                     " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
                 end
               end
               def sql_for_fixed_version_status_field(field, operator, value)
                 where = sql_for_field(field, operator, value, Version.table_name, "status")
                 version_ids = versions(:conditions => [where]).map(&:id)
                 nl = operator == "!" ? "#{Issue.table_name}.fixed_version_id IS NULL OR" : ''
                 "(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"
               end
               def sql_for_fixed_version_due_date_field(field, operator, value)
                 where = sql_for_field(field, operator, value, Version.table_name, "effective_date")
                 version_ids = versions(:conditions => [where]).map(&:id)
                 nl = operator == "!*" ? "#{Issue.table_name}.fixed_version_id IS NULL OR" : ''
                 "(#{nl} #{sql_for_field("fixed_version_id", "=", version_ids, Issue.table_name, "fixed_version_id")})"
               end
               def sql_for_is_private_field(field, operator, value)
                 op = (operator == "=" ? 'IN' : 'NOT IN')
                 va = value.map {|v| v == '0' ? self.class.connection.quoted_false : self.class.connection.quoted_true}.uniq.join(',')
                 "#{Issue.table_name}.is_private #{op} (#{va})"
               end
               def sql_for_parent_id_field(field, operator, value)
                 case operator
                 when "="
                   "#{Issue.table_name}.parent_id = #{value.first.to_i}"
                 when "~"
                   root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first
                   if root_id && lft && rgt
                     "#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft > #{lft} AND #{Issue.table_name}.rgt < #{rgt}"
                   else
                     "1=0"
                   end
                 when "!*"
                   "#{Issue.table_name}.parent_id IS NULL"
                 when "*"
                   "#{Issue.table_name}.parent_id IS NOT NULL"
                 end
               end
               def sql_for_child_id_field(field, operator, value)
                 case operator
                 when "="
                   parent_id = Issue.where(:id => value.first.to_i).pluck(:parent_id).first
                   if parent_id
                     "#{Issue.table_name}.id = #{parent_id}"
                   else
                     "1=0"
                   end
                 when "~"
                   root_id, lft, rgt = Issue.where(:id => value.first.to_i).pluck(:root_id, :lft, :rgt).first
                   if root_id && lft && rgt
                     "#{Issue.table_name}.root_id = #{root_id} AND #{Issue.table_name}.lft < #{lft} AND #{Issue.table_name}.rgt > #{rgt}"
                   else
                     "1=0"
                   end
                 when "!*"
                   "#{Issue.table_name}.rgt - #{Issue.table_name}.lft = 1"
                 when "*"
                   "#{Issue.table_name}.rgt - #{Issue.table_name}.lft > 1"
                 end
               end
               def sql_for_issue_id_field(field, operator, value)
                 if operator == "="
                   # accepts a comma separated list of ids
                   ids = value.first.to_s.scan(/\d+/).map(&:to_i)
                   if ids.present?
                     "#{Issue.table_name}.id IN (#{ids.join(",")})"
                   else
                     "1=0"
                   end
                 else
                   sql_for_field("id", operator, value, Issue.table_name, "id")
                 end
               end
               def sql_for_relations(field, operator, value, options={})
                 relation_options = IssueRelation::TYPES[field]
                 return relation_options unless relation_options
                 relation_type = field
                 join_column, target_join_column = "issue_from_id", "issue_to_id"
                 if relation_options[:reverse] || options[:reverse]
                   relation_type = relation_options[:reverse] || relation_type
                   join_column, target_join_column = target_join_column, join_column
                 end
                 sql = case operator
                   when "*", "!*"
                     op = (operator == "*" ? 'IN' : 'NOT IN')
                     "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}')"
                   when "=", "!"
                     op = (operator == "=" ? 'IN' : 'NOT IN')
                     "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = #{value.first.to_i})"
                   when "=p", "=!p", "!p"
                     op = (operator == "!p" ? 'NOT IN' : 'IN')
                     comp = (operator == "=!p" ? '<>' : '=')
                     "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name}, #{Issue.table_name} relissues WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.project_id #{comp} #{value.first.to_i})"
                   when "*o", "!o"
                     op = (operator == "!o" ? 'NOT IN' : 'IN')
                     "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name}, #{Issue.table_name} relissues WHERE #{IssueRelation.table_name}.relation_type = '#{self.class.connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false}))"
                   end
                 if relation_options[:sym] == field && !options[:reverse]
                   sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]
                   sql = sqls.join(["!", "!*", "!p"].include?(operator) ? " AND " : " OR ")
                 end
                 "(#{sql})"
               end
               def find_assigned_to_id_filter_values(values)
                 Principal.visible.where(:id => values).map {|p| [p.name, p.id.to_s]}
               end
               alias :find_author_id_filter_values :find_assigned_to_id_filter_values
               IssueRelation::TYPES.keys.each do |relation_type|
                 alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations
               end
+              def joins_for_order_statement(order_options)
+                joins = [super]
+                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
+                end
+                joins.any? ? joins.join(' ') : nil
+              end
             end

             # Redmine - project management software
             # Copyright (C) 2006-2016  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, :totalable, :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.totalable = options[:totalable] || false
                 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
                 case @caption_key
                 when Symbol
                   l(@caption_key)
                 when Proc
                   @caption_key.call
                 else
                   @caption_key
                 end
               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 value_object(object)
                 object.send name
               end
               def css_classes
                 name
               end
             end
             class QueryAssociationColumn < QueryColumn
               def initialize(association, attribute, options={})
                 @association = association
                 @attribute = attribute
                 name_with_assoc = "#{association}.#{attribute}".to_sym
                 super(name_with_assoc, options)
               end
               def value_object(object)
                 if assoc = object.send(@association)
                   assoc.send @attribute
                 end
               end
               def css_classes
                 @css_classes ||= "#{@association}-#{@attribute}"
               end
             end
             class QueryCustomFieldColumn < QueryColumn
               def initialize(custom_field, options={})
                 self.name = "cf_#{custom_field.id}".to_sym
                 self.sortable = custom_field.order_statement || false
                 self.groupable = custom_field.group_statement || false
                 self.totalable = options.key?(:totalable) ? !!options[:totalable] : custom_field.totalable?
                 @inline = true
                 @cf = custom_field
               end
               def caption
                 @cf.name
               end
               def custom_field
                 @cf
               end
               def value_object(object)
                 if custom_field.visible_by?(object.project, User.current)
                   cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
                   cv.size > 1 ? cv.sort {|a,b| a.value.to_s <=> b.value.to_s} : cv.first
                 else
                   nil
                 end
               end
               def value(object)
                 raw = value_object(object)
                 if raw.is_a?(Array)
                   raw.map {|r| @cf.cast_value(r.value)}
                 elsif raw
                   @cf.cast_value(raw.value)
                 else
                   nil
                 end
               end
               def css_classes
                 @css_classes ||= "#{name} #{@cf.field_format}"
               end
             end
             class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
               def initialize(association, custom_field, options={})
                 super(custom_field, options)
                 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(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 QueryFilter
               include Redmine::I18n
               def initialize(field, options)
                 @field = field.to_s
                 @options = options
                 @options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
                 # Consider filters with a Proc for values as remote by default
                 @remote = options.key?(:remote) ? options[:remote] : options[:values].is_a?(Proc)
               end
               def [](arg)
                 if arg == :values
                   values
                 else
                   @options[arg]
                 end
               end
               def values
                 @values ||= begin
                   values = @options[:values]
                   if values.is_a?(Proc)
                     values = values.call
                   end
                   values
                 end
               end
               def remote
                 @remote
               end
             end
             class Query < ActiveRecord::Base
               class StatementInvalid < ::ActiveRecord::StatementInvalid
               end
               include Redmine::SubclassFactory
               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,
                 "*o"  => :label_any_open_issues,
                 "!o"  => :label_no_open_issues
               }
               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", "*o", "!o", "!*", "*"],
                 :tree => ["=", "~", "!*", "*"]
               }
               class_attribute :available_columns
               self.available_columns = []
               class_attribute :queried_class
               # Permission required to view the queries, set on subclasses.
               class_attribute :view_permission
               # Scope of queries that are global or on the given project
               scope :global_or_on_project, lambda {|project|
                 where(:project_id => (project.nil? ? nil : [nil, project.id]))
               }
               scope :sorted, lambda {order(:name, :id)}
               # Scope of visible queries, can be used from subclasses only.
               # Unlike other visible scopes, a class methods is used as it
               # let handle inheritance more nicely than scope DSL.
               def self.visible(*args)
                 if self == ::Query
                   # Visibility depends on permissions for each subclass,
                   # raise an error if the scope is called from Query (eg. Query.visible)
                   raise Exception.new("Cannot call .visible scope from the base Query class, but from subclasses only.")
                 end
                 user = args.shift || User.current
                 base = Project.allowed_to_condition(user, view_permission, *args)
                 scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").
                   where("#{table_name}.project_id IS NULL OR (#{base})")
                 if user.admin?
                   scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)
                 elsif user.memberships.any?
                   scope.where("#{table_name}.visibility = ?" +
                     " OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +
                       "SELECT DISTINCT q.id FROM #{table_name} q" +
                       " INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +
                       " INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +
                       " INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +
                       " WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
                     " OR #{table_name}.user_id = ?",
                     VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)
                 elsif user.logged?
                   scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)
                 else
                   scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)
                 end
               end
               # Returns true if the query is visible to +user+ or the current user.
               def visible?(user=User.current)
                 return true if user.admin?
                 return false unless project.nil? || user.allowed_to?(self.class.view_permission, project)
                 case visibility
                 when VISIBILITY_PUBLIC
                   true
                 when VISIBILITY_ROLES
                   if project
                     (user.roles_for_project(project) & roles).any?
                   else
                     Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
                   end
                 else
                   user == self.user
                 end
               end
               def is_private?
                 visibility == VISIBILITY_PRIVATE
               end
               def is_public?
                 !is_private?
               end
               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.totalable_names = params[:t] || (params[:query] && params[:query][:totalable_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(/\A[+-]?\d+(,[+-]?\d+)*\z/) }
                     when :float
                       add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(\.\d*)?\z/) }
                     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", "*o", "!o"].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.all : project.rolled_up_trackers).visible.sorted
               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, filter|
                   options = {:type => filter[:type], :name => filter[:name]}
                   options[:remote] = true if filter.remote
                   if has_filter?(field) || !filter.remote
                     options[:values] = filter.values
                     if options[:values] && values_for(field)
                       missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)
                       if missing.any? && respond_to?(method = "find_#{field}_filter_values")
                         options[:values] += send(method, missing)
                       end
                     end
                   end
                   json[field] = options.stringify_keys
                 end
                 json
               end
               def all_projects
                 @all_projects ||= Project.visible.to_a
               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
               def project_values
                 project_values = []
                 if User.current.logged? && User.current.memberships.any?
                   project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
                 end
                 project_values += all_projects_values
                 project_values
               end
               def subproject_values
                 project.descendants.visible.collect{|s| [s.name, s.id.to_s] }
               end
               def principals
                 @principal ||= begin
                   principals = []
                   if project
                     principals += project.principals.visible
                     unless project.leaf?
                       principals += Principal.member_of(project.descendants.visible).visible
                     end
                   else
                     principals += Principal.member_of(all_projects).visible
                   end
                   principals.uniq!
                   principals.sort!
                   principals.reject! {|p| p.is_a?(GroupBuiltin)}
                   principals
                 end
               end
               def users
                 principals.select {|p| p.is_a?(User)}
               end
               def author_values
                 author_values = []
                 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
                 author_values += users.collect{|s| [s.name, s.id.to_s] }
                 author_values
               end
               def assigned_to_values
                 assigned_to_values = []
                 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
                 assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }
                 assigned_to_values
               end
               def fixed_version_values
                 versions = []
                 if project
                   versions = project.shared_versions.to_a
                 else
                   versions = Version.visible.where(:sharing => 'system').to_a
                 end
                 Version.sort_by_status(versions).collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s, l("version_status_#{s.status}")] }
               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] = QueryFilter.new(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 ||= {}
                 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.to_s.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 ||= queried_class.human_attribute_name(field, :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 available_totalable_columns
                 available_columns.select(&:totalable)
               end
               def default_columns_names
                 []
               end
               def default_totalable_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 totalable_columns
                 names = totalable_names
                 available_totalable_columns.select {|column| names.include?(column.name)}
               end
               def totalable_names=(names)
                 if names
                   names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}
                 end
                 options[:totalable_names] = names
               end
               def totalable_names
                 options[:totalable_names] || default_totalable_names || []
               end
               def sort_criteria=(arg)
                 c = []
                 if arg.is_a?(Hash)
                   arg = arg.keys.sort.collect {|k| arg[k]}
                 end
                 if arg
                   c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
                 end
                 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 column = group_by_column
                   order = (sort_criteria_order_for(column.name) || column.default_order || 'asc').try(:upcase)
                   Array(column.sortable).map {|s| "#{s} #{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 = []
                 active_subprojects_ids = []
                 active_subprojects_ids = project.descendants.active.map(&:id) if project
                 if active_subprojects_ids.any?
                   if has_filter?("subproject_id")
                     case operator_for("subproject_id")
                     when '='
                       # include the selected subprojects
                       ids = [project.id] + values_for("subproject_id").map(&:to_i)
                       project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
                     when '!'
                       # exclude the selected subprojects
                       ids = [project.id] + active_subprojects_ids - values_for("subproject_id").map(&:to_i)
                       project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
                     when '!*'
                       # main project only
                       project_clauses << "#{Project.table_name}.id = %d" % project.id
                     else
                       # all subprojects
                       project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
                     end
                   elsif Setting.display_subprojects_issues?
                     project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
                   else
                     project_clauses << "#{Project.table_name}.id = %d" % project.id
                   end
                 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 substitution
                   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+)\.cf_(\d+)$/
                     filters_clauses << sql_for_chained_custom_field(field, operator, v, $1, $2)
                   elsif field =~ /cf_(\d+)$/
                     # custom field
                     filters_clauses << sql_for_custom_field(field, operator, v, $1)
                   elsif field =~ /^cf_(\d+)\.(.+)$/
                     filters_clauses << sql_for_custom_field_attribute(field, operator, v, $1, $2)
                   elsif respond_to?(method = "sql_for_#{field.gsub('.','_')}_field")
                     # specific statement
                     filters_clauses << send(method, 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
               # Returns the sum of values for the given column
               def total_for(column)
                 total_with_scope(column, base_scope)
               end
               # Returns a hash of the sum of the given column for each group,
               # or nil if the query is not grouped
               def total_by_group_for(column)
                 grouped_query do |scope|
                   total_with_scope(column, scope)
                 end
               end
               def totals
                 totals = totalable_columns.map {|column| [column, total_for(column)]}
                 yield totals if block_given?
                 totals
               end
               def totals_by_group
                 totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
                 yield totals if block_given?
                 totals
               end
               private
               def grouped_query(&block)
                 r = nil
                 if grouped?
                   begin
                     # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
                     r = yield base_group_scope
                   rescue ActiveRecord::RecordNotFound
                     r = {nil => yield(base_scope)}
                   end
                   c = group_by_column
                   if c.is_a?(QueryCustomFieldColumn)
                     r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
                   end
                 end
                 r
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               def total_with_scope(column, scope)
                 unless column.is_a?(QueryColumn)
                   column = column.to_sym
                   column = available_totalable_columns.detect {|c| c.name == column}
                 end
                 if column.is_a?(QueryCustomFieldColumn)
                   custom_field = column.custom_field
                   send "total_for_custom_field", custom_field, scope
                 else
                   send "total_for_#{column.name}", scope
                 end
               rescue ::ActiveRecord::StatementInvalid => e
                 raise StatementInvalid.new(e.message)
               end
               def base_scope
                 raise "unimplemented"
               end
               def base_group_scope
                 base_scope.
                   joins(joins_for_order_statement(group_by_statement)).
                   group(group_by_statement)
               end
               def total_for_custom_field(custom_field, scope, &block)
                 total = custom_field.format.total_for_scope(custom_field, scope)
                 total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
                 total
               end
               def map_total(total, &block)
                 if total.is_a?(Hash)
                   total.keys.each {|k| total[k] = yield total[k]}
                 else
                   total = yield total
                 end
                 total
               end
               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
               def sql_for_chained_custom_field(field, operator, value, custom_field_id, chained_custom_field_id)
                 not_in = nil
                 if operator == '!'
                   # Makes ! operator work for custom fields with multiple values
                   operator = '='
                   not_in = 'NOT'
                 end
                 filter = available_filters[field]
                 target_class = filter[:through].format.target_class
                 "#{queried_table_name}.id #{not_in} IN (" +
                   "SELECT customized_id FROM #{CustomValue.table_name}" +
                   " WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +
                   "  AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +
                   "  SELECT customized_id FROM #{CustomValue.table_name}" +
                   "  WHERE customized_type='#{target_class}' AND custom_field_id=#{chained_custom_field_id}" +
                   "  AND #{sql_for_field(field, operator, value, CustomValue.table_name, 'value')}))"
               end
               def sql_for_custom_field_attribute(field, operator, value, custom_field_id, attribute)
                 attribute = 'effective_date' if attribute == 'due_date'
                 not_in = nil
                 if operator == '!'
                   # Makes ! operator work for custom fields with multiple values
                   operator = '='
                   not_in = 'NOT'
                 end
                 filter = available_filters[field]
                 target_table_name = filter[:field].format.target_class.table_name
                 "#{queried_table_name}.id #{not_in} IN (" +
                   "SELECT customized_id FROM #{CustomValue.table_name}" +
                   " WHERE customized_type='#{queried_class}' AND custom_field_id=#{custom_field_id}" +
                   "  AND CAST(CASE value WHEN '' THEN '0' ELSE value END AS decimal(30,0)) IN (" +
                   "  SELECT id FROM #{target_table_name} WHERE #{sql_for_field(field, operator, value, filter[:field].format.target_class.table_name, attribute)}))"
               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), is_custom_filter)
                     when :integer
                       int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")
                       if int_values.present?
                         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)) IN (#{int_values}))"
                         else
                           sql = "#{db_table}.#{db_field} IN (#{int_values})"
                         end
                       else
                         sql = "1=0"
                       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 = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])
                     end
                   else
                     # IN an empty set
                     sql = "1=0"
                   end
                 when "!"
                   if value.any?
                     sql = queried_class.send(:sanitize_sql_for_conditions, ["(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (?))", value])
                   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, is_custom_filter)
                   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), is_custom_filter)
                   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]), is_custom_filter)
                   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=#{self.class.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=#{self.class.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, is_custom_filter)
                 when ">t-"
                   # >= today - n days
                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
                 when "<t-"
                   # <= today - n days
                   sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
                 when "t-"
                   # = n days in past
                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
                 when "><t+"
                   # between today and today + n days
                   sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
                 when ">t+"
                   # >= today + n days
                   sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
                 when "<t+"
                   # <= today + n days
                   sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, is_custom_filter)
                 when "t+"
                   # = today + n days
                   sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
                 when "t"
                   # = today
                   sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
                 when "ld"
                   # = yesterday
                   sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
                 when "w"
                   # = this week
                   first_day_of_week = l(:general_first_day_of_week).to_i
                   day_of_week = User.current.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, is_custom_filter)
                 when "lw"
                   # = last week
                   first_day_of_week = l(:general_first_day_of_week).to_i
                   day_of_week = User.current.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, is_custom_filter)
                 when "l2w"
                   # = last 2 weeks
                   first_day_of_week = l(:general_first_day_of_week).to_i
                   day_of_week = User.current.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, is_custom_filter)
                 when "m"
                   # = this month
                   date = User.current.today
                   sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
                 when "lm"
                   # = last month
                   date = User.current.today.prev_month
                   sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
                 when "y"
                   # = this year
                   date = User.current.today
                   sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
                 when "~"
                   sql = sql_contains("#{db_table}.#{db_field}", value.first)
                 when "!~"
                   sql = sql_contains("#{db_table}.#{db_field}", value.first, false)
                 else
                   raise "Unknown query operator #{operator}"
                 end
                 return sql
               end
               # Returns a SQL LIKE statement with wildcards
               def sql_contains(db_field, value, match=true)
                 queried_class.send :sanitize_sql_for_conditions,
                   [Redmine::Database.like(db_field, '?', :match => match), "%#{value}%"]
               end
               # Adds a filter for the given custom field
               def add_custom_field_filter(field, assoc=nil)
                 options = field.query_filter_options(self)
                 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 custom fields associated to the custom field target class
               # Eg. having a version custom field "Milestone" for issues and a date custom field "Release date"
               # for versions, it will add an issue filter on Milestone'e Release date.
               def add_chained_custom_field_filters(field)
                 klass = field.format.target_class
                 if klass
                   CustomField.where(:is_filter => true, :type => "#{klass.name}CustomField").each do |chained|
                     options = chained.query_filter_options(self)
                     filter_id = "cf_#{field.id}.cf_#{chained.id}"
                     filter_name = chained.name
                     add_available_filter filter_id, options.merge({
                       :name => l(:label_attribute_of_object, :name => chained.name, :object_name => field.name),
                       :field => chained,
                       :through => field
                     })
                   end
                 end
               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)
                   if assoc.nil?
                     add_chained_custom_field_filters(field)
                     if field.format.target_class && field.format.target_class == Version
                       add_available_filter "cf_#{field.id}.due_date",
                         :type => :date,
                         :field => field,
                         :name => l(:label_attribute_of_object, :name => l(:field_effective_date), :object_name => field.name)
                       add_available_filter "cf_#{field.id}.status",
                         :type => :list,
                         :field => field,
                         :name => l(:label_attribute_of_object, :name => l(:field_status), :object_name => field.name),
                         :values => Version::VERSION_STATUSES.map{|s| [l("version_status_#{s}"), s] }
                     end
                   end
                 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
               def quoted_time(time, is_custom_filter)
                 if is_custom_filter
                   # Custom field values are stored as strings in the DB
                   # using this format that does not depend on DB date representation
                   time.strftime("%Y-%m-%d %H:%M:%S")
                 else
                   self.class.connection.quoted_date(time)
                 end
               end
               def date_for_user_time_zone(y, m, d)
                 if tz = User.current.time_zone
                   tz.local y, m, d
                 else
                   Time.local y, m, d
                 end
               end
               # Returns a SQL clause for a date or datetime field.
               def date_clause(table, field, from, to, is_custom_filter)
                 s = []
                 if from
                   if from.is_a?(Date)
                     from = date_for_user_time_zone(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'" % [quoted_time(from, is_custom_filter)])
                 end
                 if to
                   if to.is_a?(Date)
                     to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day
                   end
                   if self.class.default_timezone == :utc
                     to = to.utc
                   end
                   s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
                 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, is_custom_filter)
                 date_clause(table, field, (days_from ? User.current.today + days_from : nil), (days_to ? User.current.today + days_to : nil), is_custom_filter)
               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