157
169
req.redirect(req.href.report(id))
159
171
def _render_confirm_delete(self, req, db, id):
160
req.perm.assert_permission('REPORT_DELETE')
172
req.perm.require('REPORT_DELETE')
162
174
cursor = db.cursor()
163
cursor.execute("SELECT title FROM report WHERE id = %s", (id,))
164
row = cursor.fetchone()
166
raise TracError('Report %s does not exist.' % id,
167
'Invalid Report Number')
168
req.hdf['title'] = 'Delete Report {%s} %s' % (id, row[0])
169
req.hdf['report'] = {
173
'href': req.href.report(id)
176
def _render_editor(self, req, db, id, copy=False):
178
req.perm.assert_permission('REPORT_CREATE')
179
title = query = description = ''
175
cursor.execute("SELECT title FROM report WHERE id=%s", (id,))
176
for title, in cursor:
177
return {'title': _('Delete Report {%(num)s} %(title)s', num=id,
180
'report': {'id': id, 'title': title}}
181
req.perm.assert_permission('REPORT_MODIFY')
182
raise TracError(_('Report %(num)s does not exist.', num=id),
183
_('Invalid Report Number'))
185
def _render_editor(self, req, db, id, copy):
187
req.perm.require('REPORT_MODIFY')
182
188
cursor = db.cursor()
183
189
cursor.execute("SELECT title,description,query FROM report "
184
190
"WHERE id=%s", (id,))
185
row = cursor.fetchone()
187
raise TracError('Report %s does not exist.' % id,
188
'Invalid Report Number')
190
description = row[1] or ''
191
for title, description, query in cursor:
194
raise TracError(_('Report %(num)s does not exist.', num=id),
195
_('Invalid Report Number'))
197
req.perm.require('REPORT_CREATE')
198
title = description = query = ''
200
# an explicitly given 'query' parameter will override the saved query
201
query = req.args.get('query', query)
194
204
title += ' (copy)'
196
206
if copy or id == -1:
197
req.hdf['title'] = 'Create New Report'
198
req.hdf['report.href'] = req.href.report()
199
req.hdf['report.action'] = 'new'
207
data = {'title': _('Create New Report'),
201
req.hdf['title'] = 'Edit Report {%d} %s' % (id, title)
202
req.hdf['report.href'] = req.href.report(id)
203
req.hdf['report.action'] = 'edit'
211
data = {'title': _('Edit Report {%(num)d} %(title)s', num=id,
214
'error': req.args.get('error')}
205
req.hdf['report.id'] = id
206
req.hdf['report.mode'] = 'edit'
207
req.hdf['report.title'] = title
208
req.hdf['report.sql'] = query
209
req.hdf['report.description'] = description
216
data['report'] = {'id': id, 'title': title,
217
'sql': query, 'description': description}
211
220
def _render_view(self, req, db, id):
213
uses a user specified sql query to extract some information
214
from the database and presents it as a html table.
216
actions = {'create': 'REPORT_CREATE', 'delete': 'REPORT_DELETE',
217
'modify': 'REPORT_MODIFY'}
218
for action in [k for k,v in actions.items()
219
if req.perm.has_permission(v)]:
220
req.hdf['report.can_' + action] = True
221
req.hdf['report.href'] = req.href.report(id)
221
"""Retrieve the report results and pre-process them for rendering."""
224
223
args = self.get_var_args(req)
225
224
except ValueError,e:
226
raise TracError, 'Report failed: %s' % e
228
title, description, sql = self.get_info(db, id, args)
225
raise TracError(_('Report failed: %(error)s', error=e))
228
# If no particular report was requested, display
229
# a list of available reports instead
230
title = _('Available Reports')
231
sql = ("SELECT id AS report, title, 'report' as _realm "
232
"FROM report ORDER BY report")
233
description = _('This is a list of available reports.')
236
cursor.execute("SELECT title,query,description from report "
237
"WHERE id=%s", (id,))
238
for title, sql, description in cursor:
241
raise ResourceNotFound(
242
_('Report %(num)s does not exist.', num=id),
243
_('Invalid Report Number'))
245
# If this is a saved custom query. redirect to the query module
247
# A saved query is either an URL query (?... or query:?...),
248
# or a query language expression (query:...).
250
# It may eventually contain newlines, for increased clarity.
252
query = ''.join([line.strip() for line in sql.splitlines()])
253
if query and (query[0] == '?' or query.startswith('query:?')):
254
query = query[0] == '?' and query or query[6:]
255
report_id = 'report=%s' % id
256
if 'report=' in query:
257
if not report_id in query:
258
err = _('When specified, the report number should be '
259
'"%(num)s".', num=id)
260
req.redirect(req.href.report(id, action='edit', error=err))
265
req.redirect(req.href.query() + query)
266
elif query.startswith('query:'):
268
from trac.ticket.query import Query, QuerySyntaxError
269
query = Query.from_string(self.env, query[6:], report=id)
270
req.redirect(query.get_href(req))
271
except QuerySyntaxError, e:
272
req.redirect(req.href.report(id, action='edit',
273
error=to_unicode(e)))
230
275
format = req.args.get('format')
231
276
if format == 'sql':
232
self._render_sql(req, id, title, description, sql)
277
self._send_sql(req, id, title, description, sql)
235
req.hdf['report.mode'] = 'list'
237
280
title = '{%i} %s' % (id, title)
238
req.hdf['title'] = title
239
req.hdf['report.title'] = title
240
req.hdf['report.id'] = id
241
req.hdf['report.description'] = wiki_to_html(description, self.env, req)
243
self.add_alternate_links(req, args)
282
report_resource = Resource('report', id)
283
context = Context.from_request(req, report_resource)
284
data = {'action': 'view', 'title': title,
285
'report': {'id': id, 'resource': report_resource},
287
'title': title, 'description': description,
288
'args': args, 'message': None, 'paginator':None}
290
page = int(req.args.get('page', '1'))
291
limit = self.items_per_page
292
if req.args.get('format', '') == 'rss':
293
limit = self.items_per_page_rss
294
offset = (page - 1) * limit
295
user = req.args.get('USER', None)
246
cols, rows = self.execute_report(req, db, id, sql, args)
298
cols, results, num_items = self.execute_paginated_report(
299
req, db, id, sql, args, limit, offset)
300
results = [list(row) for row in results]
301
numrows = len(results)
247
303
except Exception, e:
248
req.hdf['report.message'] = 'Report execution failed: %s' % e
249
return 'report.cs', None
251
# Convert the header info to HDF-format
254
title=col.capitalize()
255
prefix = 'report.headers.%d' % idx
256
req.hdf['%s.real' % prefix] = col
257
if title.startswith('__') and title.endswith('__'):
259
elif title[0] == '_' and title[-1] == '_':
260
title = title[1:-1].capitalize()
261
req.hdf[prefix + '.fullrow'] = 1
262
elif title[0] == '_':
264
elif title[-1] == '_':
266
req.hdf[prefix + '.breakrow'] = 1
267
req.hdf[prefix] = title
270
if req.args.has_key('sort'):
271
sortCol = req.args.get('sort')
274
for x in range(len(cols)):
276
if colName == sortCol:
278
if colName.startswith('__') and colName.endswith('__'):
281
k = 'report.headers.%d.asc' % (colIndex - hiddenCols)
282
asc = req.args.get('asc', None)
284
asc = int(asc) # string '0' or '1' to int/boolean
290
if isinstance(val, basestring):
293
rows = sorted(rows, key=sortkey, reverse=(not asc))
304
data['message'] = _('Report execution failed: %(error)s',
306
return 'report_view.html', data, None
308
if id != -1 and limit > 0:
309
asc = req.args.get('asc', None)
310
sort_col = req.args.get('sort', None)
311
paginator = Paginator(results, page - 1, limit, num_items)
312
data['paginator'] = paginator
313
if paginator.has_next_page:
314
next_href = req.href.report(id, asc=asc, sort=sort_col,
315
USER=user, page=page + 1)
316
add_link(req, 'next', next_href, _('Next Page'))
317
if paginator.has_previous_page:
318
prev_href = req.href.report(id, asc=asc, sort=sort_col,
319
USER=user, page=page - 1)
320
add_link(req, 'prev', prev_href, _('Previous Page'))
323
shown_pages = paginator.get_shown_pages(21)
324
for p in shown_pages:
325
pagedata.append([req.href.report(id, asc=asc, sort=sort_col,
327
None, str(p), _('Page %(num)d', num=p)])
328
fields = ['href', 'class', 'string', 'title']
329
paginator.shown_pages = [dict(zip(fields, p)) for p in pagedata]
330
paginator.current_page = {'href': None, 'class': 'current',
331
'string': str(paginator.page + 1),
333
numrows = paginator.num_items
335
sort_col = req.args.get('sort', '')
336
asc = req.args.get('asc', 1)
337
asc = bool(int(asc)) # string '0' or '1' to int/boolean
339
# Place retrieved columns in groups, according to naming conventions
340
# * _col_ means fullrow, i.e. a group with one header
341
# * col_ means finish the current group and start a new one
343
for idx, col in enumerate(cols):
346
'title': col.strip('_').capitalize(),
354
# this dict will have enum values for sorting
355
# and will be used in sortkey(), if non-empty:
357
if sort_col in ['status', 'resolution', 'priority',
359
# must fetch sort values for that columns
360
# instead of comparing them as strings
362
db = self.env.get_db_cnx()
364
cursor.execute("SELECT name," +
365
db.cast('value', 'int') +
366
" FROM enum WHERE type=%s", (sort_col,))
367
for name, value in cursor:
368
sort_values[name] = value
372
# check if we have sort_values, then use them as keys.
374
return sort_values.get(val)
375
# otherwise, continue with string comparison:
376
if isinstance(val, basestring):
379
results = sorted(results, key=sortkey, reverse=(not asc))
381
header_group = header_groups[-1]
383
if col.startswith('__') and col.endswith('__'): # __col__
384
header['hidden'] = True
385
elif col[0] == '_' and col[-1] == '_': # _col_
387
header_groups.append(header_group)
388
header_groups.append([])
389
elif col[0] == '_': # _col
390
header['hidden'] = True
391
elif col[-1] == '_': # col_
392
header_groups.append([])
393
header_group.append(header)
395
# Structure the rows and cells:
396
# - group rows according to __group__ value, if defined
397
# - group cells the same way headers are grouped
399
prev_group_value = None
400
for row_idx, result in enumerate(results):
403
row = {'cell_groups': cell_groups}
406
for header_group in header_groups:
408
for header in header_group:
409
value = unicode(result[col_idx])
410
cell = {'value': value, 'header': header, 'index': col_idx}
413
# Detect and create new group
414
if col == '__group__' and value != prev_group_value:
415
prev_group_value = value
416
# Brute force handling of email in group by header
418
(Chrome(self.env).format_author(req, value), []) )
419
# Other row properties
420
row['__idx__'] = row_idx
421
if col in ('__style__', '__color__',
422
'__fgcolor__', '__bgcolor__'):
424
if col in ('report', 'ticket', 'id', '_id'):
426
# Special casing based on column name
428
if col in ('reporter', 'cc', 'owner'):
429
email_cells.append(cell)
432
cell_group.append(cell)
433
cell_groups.append(cell_group)
434
resource = Resource(realm, row.get('id'))
435
# FIXME: for now, we still need to hardcode the realm in the action
436
if resource.realm.upper()+'_VIEW' not in req.perm(resource):
439
for cell in email_cells:
440
emails = Chrome(self.env).format_emails(context(resource),
442
result[cell['index']] = cell['value'] = emails
443
row['resource'] = resource
445
row_group = row_groups[-1][1]
448
row_groups = [(None, row_group)]
449
row_group.append(row)
295
451
# Get the email addresses of all known users
297
for username, name, email in self.env.get_known_users():
299
email_map[username] = email
301
# Convert the rows and cells to HDF-format
308
column = cols[col_idx]
310
# Special columns begin and end with '__'
311
if column.startswith('__') and column.endswith('__'):
313
elif (column[0] == '_' and column[-1] == '_'):
315
column = column[1:-1]
316
req.hdf[prefix + '.breakrow'] = 1
317
elif column[-1] == '_':
318
value['breakrow'] = 1
319
value['breakafter'] = 1
321
elif column[0] == '_':
322
value['hidehtml'] = 1
324
if column in ('ticket', 'id', '_id', '#', 'summary'):
325
id_cols = [idx for idx, col in enumerate(cols)
326
if col in ('ticket', 'id', '_id')]
328
id_val = row[id_cols[0]]
329
value['ticket_href'] = req.href.ticket(id_val)
330
elif column == 'description':
331
desc = wiki_to_html(cell, self.env, req, db,
332
absurls=(format == 'rss'))
333
value['parsed'] = format == 'rss' and unicode(desc) or desc
334
elif column == 'reporter':
335
if cell.find('@') != -1:
337
elif cell in email_map:
338
value['rss'] = email_map[cell]
339
elif column == 'report':
340
value['report_href'] = req.href.report(cell)
341
elif column in ('time', 'date','changetime', 'created', 'modified'):
343
value['date'] = value['time'] = cell
344
value['datetime'] = value['gmt'] = cell
346
value['date'] = format_date(cell)
347
value['time'] = format_time(cell)
348
value['datetime'] = format_datetime(cell)
349
value['gmt'] = http_date(cell)
350
prefix = 'report.items.%d.%s' % (row_idx, unicode(column))
351
req.hdf[prefix] = unicode(cell)
352
for key in value.keys():
353
req.hdf[prefix + '.' + key] = value[key]
357
req.hdf['report.numrows'] = row_idx
453
if Chrome(self.env).show_email_addresses:
454
for username, name, email in self.env.get_known_users():
456
email_map[username] = email
458
data.update({'header_groups': header_groups,
459
'row_groups': row_groups,
461
'sorting_enabled': len(row_groups)==1,
462
'email_map': email_map})
465
self.add_alternate_links(req, args)
359
467
if format == 'rss':
360
return 'report_rss.cs', 'application/rss+xml'
468
data['context'] = Context.from_request(req, report_resource,
470
return 'report.rss', data, 'application/rss+xml'
361
471
elif format == 'csv':
362
472
filename = id and 'report_%s.csv' % id or 'report.csv'
363
self._render_csv(req, cols, rows, mimetype='text/csv',
473
self._send_csv(req, cols, results, mimetype='text/csv',
366
475
elif format == 'tab':
367
476
filename = id and 'report_%s.tsv' % id or 'report.tsv'
368
self._render_csv(req, cols, rows, '\t',
369
mimetype='text/tab-separated-values',
373
return 'report.cs', None
477
self._send_csv(req, cols, results, '\t',
478
mimetype='text/tab-separated-values',
482
# reuse the session vars of the query module so that
483
# the query navigation links on the ticket can be used to
484
# navigate report results as well
486
req.session['query_tickets'] = \
487
' '.join([str(int(row['id']))
488
for rg in row_groups for row in rg[1]])
489
#FIXME: I am not sure the extra args are necessary
490
req.session['query_href'] = \
491
req.href.report(id, asc=not asc and '0' or None,
492
sort=sort_col, USER=user, page=page)
493
# Kludge: we have to clear the other query session
494
# variables, but only if the above succeeded
495
for var in ('query_constraints', 'query_time'):
496
if var in req.session:
498
except (ValueError, KeyError):
500
return 'report_view.html', data, None
375
502
def add_alternate_links(self, req, args):
377
if req.args.has_key('sort'):
504
if 'sort' in req.args:
378
505
params['sort'] = req.args['sort']
379
if req.args.has_key('asc'):
506
if 'asc' in req.args:
380
507
params['asc'] = req.args['asc']
383
510
href = '&' + unicode_urlencode(params)
384
add_link(req, 'alternate', '?format=rss' + href, 'RSS Feed',
511
add_link(req, 'alternate', '?format=rss' + href, _('RSS Feed'),
385
512
'application/rss+xml', 'rss')
386
513
add_link(req, 'alternate', '?format=csv' + href,
387
'Comma-delimited Text', 'text/plain')
514
_('Comma-delimited Text'), 'text/plain')
388
515
add_link(req, 'alternate', '?format=tab' + href,
389
'Tab-delimited Text', 'text/plain')
390
if req.perm.has_permission('REPORT_SQL_VIEW'):
391
add_link(req, 'alternate', '?format=sql', 'SQL Query',
516
_('Tab-delimited Text'), 'text/plain')
517
if 'REPORT_SQL_VIEW' in req.perm:
518
add_link(req, 'alternate', '?format=sql', _('SQL Query'),
394
521
def execute_report(self, req, db, id, sql, args):
395
sql, args = self.sql_sub_vars(req, sql, args, db)
522
"""Execute given sql report (0.10 backward compatibility method)
524
:see: ``execute_paginated_report``
526
return self.execute_paginated_report(req, db, id, sql, args)[:2]
528
def execute_paginated_report(self, req, db, id, sql, args,
530
sql, args = self.sql_sub_vars(sql, args, db)
397
raise TracError('Report %s has no SQL query.' % id)
398
if sql.find('__group__') == -1:
399
req.hdf['report.sorting.enabled'] = 1
401
self.log.debug('Executing report with SQL "%s" (%s)', sql, args)
532
raise TracError(_('Report %(num)s has no SQL query.', num=id))
533
self.log.debug('Executing report with SQL "%s"' % sql)
534
self.log.debug('Request args: %r' % req.args)
403
535
cursor = db.cursor()
538
if id != -1 and limit > 0:
539
# The number of tickets is obtained.
540
count_sql = 'SELECT COUNT(*) FROM (' + sql + ') AS tab'
541
cursor.execute(count_sql, args)
542
self.log.debug("Query SQL(Get num items): " + count_sql)
547
# The column name is obtained.
548
get_col_name_sql = 'SELECT * FROM ( ' + sql + ' ) AS tab LIMIT 1'
549
cursor.execute(get_col_name_sql, args)
550
self.env.log.debug("Query SQL(Get col names): " + get_col_name_sql)
551
cols = get_column_names(cursor)
553
sort_col = req.args.get('sort', '')
554
self.log.debug("Columns %r, Sort column %s" % (cols, sort_col))
557
if '__group__' in cols:
558
order_cols.append('__group__')
560
order_cols.append(sort_col)
562
raise TracError(_('Query parameter "sort=%(sort_col)s" '
563
' is invalid', sort_col=sort_col))
565
# The report-query results is obtained
566
asc = req.args.get('asc', '1')
567
asc_str = asc == '1' and 'ASC' or 'DESC'
569
if len(order_cols) != 0:
570
order = ', '.join(order_cols)
571
order_by = " ".join([' ORDER BY', order, asc_str])
572
sql = " ".join(['SELECT * FROM (', sql, ') AS tab', order_by])
573
sql =" ".join([sql, 'LIMIT', str(limit), 'OFFSET', str(offset)])
574
self.log.debug("Query SQL: " + sql)
404
575
cursor.execute(sql, args)
406
576
# FIXME: fetchall should probably not be used.
407
577
info = cursor.fetchall() or []
408
578
cols = get_column_names(cursor)
414
def get_info(self, db, id, args):
416
# If no particular report was requested, display
417
# a list of available reports instead
418
title = 'Available Reports'
419
sql = 'SELECT id AS report, title FROM report ORDER BY report'
420
description = 'This is a list of reports available.'
423
cursor.execute("SELECT title,query,description from report "
424
"WHERE id=%s", (id,))
425
row = cursor.fetchone()
427
raise TracError('Report %d does not exist.' % id,
428
'Invalid Report Number')
431
description = row[2] or ''
433
return [title, description, sql]
582
return cols, info, num_items
435
584
def get_var_args(self, req):
added
removed
trac/ticket/report.py
