Context Navigation

ticketbudgeting.py

Last change on this file was 15689, checked in by Ryan J Ollos, 7 years ago
Fix indentation and strip trailing whitespace
File size: 35.1 KB

Line
1	import re
2	import os
3	import time
4	import locale
5	import pkg_resources
6
7	from pkg_resources import resource_filename
8	from trac.util.translation import domain_functions
9	from os import path
10	from trac.core import *
11	from trac.web.api import ITemplateStreamFilter, IRequestFilter
12	from genshi.filters import Transformer
13	from genshi.builder import tag
14	from genshi import HTML, XML
15	from trac.web.chrome import ITemplateProvider, add_script, add_script_data
16	from trac.ticket.api import ITicketManipulator
17	from string import upper
18	from genshi.filters.transform import StreamBuffer
19
20	from trac.db.schema import Table, Column, Index
21	from trac.db.api import DatabaseManager
22	from trac.config import Option
23	from trac.ticket.model import Ticket
24	import __init__
25	from trac.perm import PermissionSystem
26	from compiler.ast import Printnl
27
28	_, tag_, N_, add_domain = domain_functions('ticketbudgeting', '_', 'tag_', 'N_', 'add_domain')
29
30	""" budgeting table object \
31	see trac/db_default.py for samples and trac/db/schema.py for implementation of objects """
32	BUDGETING_TABLE = Table('budgeting', key=('ticket', 'position'))[
33	Column('ticket', type='int'),
34	Column('position', type='int'),
35	Column('username'),
36	Column('type'),
37	Column('estimation', type='int64'),
38	Column('cost', type='int64'),
39	Column('status', type='int'),
40	Column('comment')
41	]
42
43	BUDGET_REPORT_ALL_ID = 90
44
45	class Budget:
46	""" Container class for budgeting info"""
47	_budget_dict = None
48	_action = None
49	_VALUE_LIST = ['username', 'type', 'estimation', 'cost', 'status', 'comment']
50
51	def __init__(self):
52	self._budget_dict = {}
53
54	def set(self, number, value):
55	if number == None:
56	return
57
58	number = int (number)
59	if number > 0 and number < self._VALUE_LIST.__len__() + 1:
60	fld = self._VALUE_LIST[number - 1]
61	if fld in ('status'):
62	try:
63	if value == '':
64	self._budget_dict[fld] = 0
65	else:
66	self._budget_dict[fld] = int (value)
67	except Exception, e:
68	# print "exception: %s" % e
69	fld = '%s.%s' % (BUDGETING_TABLE.name, fld)
70	raise Exception (fld, e)
71	elif fld in ('estimation', 'cost'):
72	try:
73	if value == '':
74	self._budget_dict[fld] = 0
75	else:
76	try:
77	self._budget_dict[fld] = locale.atof(value)
78	except Exception, ex:
79	# print "exception (locale.atof): %s" % ex
80	self._budget_dict[fld] = float(value)
81	except Exception, e:
82	# print "exception (float): %s" % e
83	fld = '%s.%s' % (BUDGETING_TABLE.name, fld)
84	raise Exception (fld, e)
85	else:
86	self._budget_dict[fld] = value
87	# print "[Budget.set] budget_dict: %s" % (self._budget_dict)
88
89	def do_action(self, env, ticket_id, position):
90	if not self._action:
91	env.log.warn('no action defined!')
92	return
93
94	# print "[do_action] ticket_id: %s, position: %s" % (ticket_id, position)
95	db = env.get_read_db()
96	cursor = db.cursor()
97
98	flds = None
99	vals = None
100
101	if not ticket_id or not position:
102	env.log.error('no ticket-id or position available!')
103	elif self._action == 1:
104	# print "do action 'insert' with budget_dict: %s" % self._budget_dict
105	for key, value in self._budget_dict.iteritems():
106	if not flds: flds = key
107	else: flds += "," + key
108
109	if key in ('username', 'type', 'comment'): value = "'%s'" % value
110
111	if not vals: vals = str(value)
112	else: vals += ",%s" % value
113
114	sql = 'insert into %s (ticket, position, %s) values (%s, %s, %s)' % (BUDGETING_TABLE.name, flds, ticket_id, position, vals)
115	# print "[action:insert] sql: %s" % sql
116	cursor.execute(sql)
117	db.commit()
118	elif self._action == 2:
119	# print "do action 'update' with budget_dict: %s" % self._budget_dict
120	sql = ''
121	i = 0
122	for key, value in self._budget_dict.iteritems():
123	if i > 0: sql += ","
124	if key in ('username', 'type', 'comment'): value = "'%s'" % value
125
126	sql += "%s=%s" % (key, value)
127	i += 1
128	sql = 'update %s set %s where ticket=%s and position=%s' % (BUDGETING_TABLE.name, sql, ticket_id, position)
129	# print "[action:update] sql: %s" % sql
130	cursor.execute(sql)
131	db.commit()
132	elif self._action == 3:
133	# print "do action 'delete' with budget_dict: %s" % self._budget_dict
134	sql = 'delete from %s where ticket=%s and position=%s' % (BUDGETING_TABLE.name, ticket_id, position)
135	# print "[action:delete] sql: %s" % sql
136	cursor.execute(sql)
137	db.commit()
138	else:
139	env.log.error('no appropriate action found! _action is: %s' % self._action)
140
141	def get_values(self):
142	return self._budget_dict
143
144	def get_value(self, number):
145	if number == None:
146	return ""
147
148	number = int (number)
149	if number > 0 and number < self._VALUE_LIST.__len__() + 1:
150	fld = self._VALUE_LIST[number - 1]
151	if fld in ('estimation', 'cost'):
152	return locale.format('%.2f', self._budget_dict[fld])
153	return self._budget_dict[fld]
154	return ""
155
156	def get_action(self):
157	return self._action
158
159	def get_action_name(self):
160	if self._action == 1:
161	return "insert"
162	elif self._action == 2:
163	return "update"
164	elif self._action == 3:
165	return "delete"
166	else:
167	return "unknown"
168
169	def set_as_insert(self):
170	self._action = 1
171
172	def set_as_update(self):
173	self._action = 2
174
175	def set_as_delete(self):
176	self._action = 3
177
178	"""
179	Main Api Module for Plugin ticketbudgeting
180	"""
181	class TicketBudgetingView(Component):
182	implements(ITemplateProvider, IRequestFilter, ITemplateStreamFilter, ITicketManipulator)
183	# ITicketChangeListener
184
185	_CONFIG_SECTION = 'budgeting-plugin'
186	# these options won't be saved to trac.ini
187	_types = Option(_CONFIG_SECTION, 'types', 'Implementation\|Documentation\|Specification\|Test',
188	"""Types of work, which could be selected in select-box.""")
189	Option(_CONFIG_SECTION, 'retrieve_users', "permission",
190	'indicates whether users should be retrieved from session or permission table; possible values: permission, session')
191	Option(_CONFIG_SECTION, 'exclude_users',
192	"'anonymous','authenticated','tracadmin'",
193	'list of users, which should be excluded to show in the drop-down list; should be usable as SQL-IN list')
194	_type_list = None
195	_name_list = None
196	_name_list_str = None
197	_budgets = None
198	_changed_by_author = None
199
200
201
202	#===============================================================================
203	# see trac/db_default.py, method get_reports (line 175)
204	#===============================================================================
205	# BUDGET_REPORT_ALL_MINE_ID = self.BUDGET_REPORT_ALL_ID + 1
206
207	BUDGET_REPORTS = [(BUDGET_REPORT_ALL_ID, 'report_title_90', 'report_description_90',
208	u"""SELECT t.id, t.summary, t.milestone AS __group__, '../milestone/' \|\| t.milestone AS __grouplink__,
209	t.owner, t.reporter, t.status, t.type, t.priority, t.component,
210	count(b.ticket) AS Anz, sum(b.cost) AS Aufwand, sum(b.estimation) AS Schaetzung,
211	floor(avg(b.status)) \|\| '%' AS "Status",
212	(CASE t.status
213	WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
214	ELSE
215	(CASE sum(b.cost) > sum(b.estimation) WHEN true THEN 'font-weight: bold; background: orange;' END)
216	END) AS __style__
217	from ticket t
218	left join budgeting b ON b.ticket = t.id
219	where t.milestone like
220	(CASE $MILESTONE
221	WHEN '''' THEN ''%''
222	ELSE $MILESTONE END) and
223	(t.component like (CASE $COMPONENT
224	WHEN '' THEN '%'
225	ELSE $COMPONENT END) or t.component is null) and
226	(t.owner like (CASE $OWNER
227	WHEN '' THEN $USER
228	ELSE $OWNER END) or t.owner is null or
229	b.username like (CASE $OWNER
230	WHEN '' THEN $USER
231	ELSE $OWNER END) )
232	group by t.id, t.type, t.priority, t.summary, t.owner, t.reporter, t.component, t.status, t.milestone
233	having count(b.ticket) > 0
234	order by t.milestone desc, t.status, t.id desc""")
235	]
236
237	#===============================================================================
238	# SELECT t.id, t.summary, t.milestone AS __group__, t.owner, t.reporter, t.type, t.priority, t.component,
239	# count(b.ticket) AS Anz, sum(b.cost) AS Aufwand, sum(b.estimation) AS Schätzung, floor(avg(b.status)) AS "Status in %%",
240	# (CASE floor(avg(b.status)) = 100
241	# WHEN true THEN 'font-weight: bold; color: green;'
242	# ELSE CASE sum(b.cost) > sum(b.estimation)
243	# WHEN true THEN 'font-weight: bold; background: orange;'
244	# ELSE '' END END) AS __style__
245	# from ticket t
246	# left join budgeting b ON b.ticket = t.id
247	# where t.milestone like
248	# (CASE $MILESTONE
249	# WHEN '' THEN '%'
250	# ELSE $MILESTONE END) and
251	# t.component like (CASE $COMPONENT
252	# WHEN '' THEN '%'
253	# ELSE $COMPONENT END) and
254	# (t.owner like (CASE $OWNER
255	# WHEN '' THEN $USER
256	# ELSE $OWNER END) or
257	# b.username like (CASE $OWNER
258	# WHEN '' THEN $USER
259	# ELSE $OWNER END) )
260	# group by t.id, t.type, t.priority, t.summary, t.owner, t.reporter, t.component, t.milestone
261	# having count(b.ticket) > 0
262	# order by t.milestone desc, t.id desc
263	#===============================================================================
264
265
266	#Alle Tickets, in der der angemeldete Benutzer beteiligt ist.
267	# (BUDGET_REPORT_ALL_MINE_ID, '[budgeting] All tickets, I am involved',
268	# """All tickets with budget data, where logged user is involved in budget, or as reporter or owner.
269	# """,
270	# """SELECT t.id, t.summary, t.owner, t.reporter, t.type, t.priority, t.component,
271	# count(b.ticket) AS Anz, sum(b.cost) AS Aufwand, sum(b.estimation) AS Schätzung, avg(b.status) AS Status,
272	# t.milestone AS __group__
273	# from ticket t
274	# left join budgeting b ON b.ticket = t.id
275	# where b.username = $USER or t.owner = $USER or t.reporter = $USER
276	# group by t.id, t.type, t.priority, t.summary, t.owner, t.reporter, t.component, t.milestone""")
277
278	def __init__(self):
279	locale_dir = pkg_resources.resource_filename(__name__, 'locale')
280	add_domain(self.env.path, locale_dir)
281
282	def filter_stream(self, req, method, filename, stream, data):
283	""" overloaded from ITemplateStreamFilter """
284	# print "-------- > filename: %s" % filename
285	if filename == 'ticket.html' and data:
286	if self._check_init() == False:
287	self.create_table()
288	self.log.info("table successfully initialized")
289	tkt = data['ticket']
290	if tkt and tkt.id:
291	self._load_budget(tkt.id)
292	else:
293	self._budgets = {}
294
295	input_html, preview_html = self._get_ticket_html()
296	if 'TICKET_MODIFY' in req.perm(tkt.resource):
297	visibility = ' style="visibility:hidden"'
298	if self._budgets:
299	visibility = ''
300
301	# Load default values for Type, Estimation, Cost an State from trac.ini
302	def_type = self.config.get('budgeting-plugin', 'default_type')
303	if not def_type:
304	# If the configured default-type is not available, submit -1 ==> first element in type list will be selected
305	def_type = '-1'
306	def_est = self.config.get('budgeting-plugin', 'default_estimation')
307	if not def_est:
308	def_est = '0.0'
309	def_cost = self.config.get('budgeting-plugin', 'default_cost')
310	if not def_cost:
311	def_est = '0.0'
312	def_state = self.config.get('budgeting-plugin', 'default_state')
313	if not def_state:
314	def_state = '0'
315
316	fieldset_str = self._get_budget_fieldset() % (visibility, input_html)
317	html = HTML('<div style="display: none" id="selectTypes">%s</div>' \
318	'<div style="display: none" id="selectNames">%s</div>' \
319	'<div style="display: none" id="def_name">%s</div>' \
320	'<div style="display: none" id="def_type">%s</div>' \
321	'<div style="display: none" id="def_est">%s</div>' \
322	'<div style="display: none" id="def_cost">%s</div>' \
323	'<div style="display: none" id="def_state">%s</div>' \
324	'%s' % (self._type_list, self._name_list_str, req.authname, def_type , def_est, def_cost, def_state, fieldset_str))
325
326	stream \|= Transformer('.//fieldset [@id="properties"]').after(html)
327
328	if preview_html:
329	# print "preview_html: %s" % preview_html
330	fieldset_str = self._get_budget_preview() % preview_html
331	stream \|= Transformer('//div [@id="content"]//div [@id="ticket"]') \
332	.after(HTML(fieldset_str))
333	elif filename == 'milestone_view.html':
334	# print "________________ MILESTONE !!"
335	# print "req.args: %s " % req.args
336	by = 'component'
337	if 'by' in req.args:
338	by = req.args['by']
339	# print "------------- link to by: %s " % req.href.query(component=by)
340	budget_stats, stats_by = self._get_milestone_html(req, by)
341	stats_by = "<fieldset><legend>Budget</legend><table>%s</table></fieldset>" % stats_by
342	stream \|= Transformer('//form[@id="stats"]').append(HTML(stats_by))
343	stream \|= Transformer('//div[@class="info"]').append(HTML(budget_stats))
344	# print input / preview
345	return stream
346
347	def _get_budget_fieldset(self):
348	title = _('in hours')
349	fieldset = '<fieldset id="budget">' \
350	'<legend>' + _('Budget Estimation') + '</legend>' \
351	'<button type="button" onclick="addBudgetRow()">[+]</button> ' \
352	'<label>' + _('Add a new row') + '</label>' \
353	'<span id="hiddenbudgettable"%s>' \
354	'<table>' \
355	'<thead id="budgethead">' \
356	'<tr>' \
357	'<th>' + _('Person') + '</th>' \
358	'<th>' + _('Type') + '</th>' \
359	'<th title="' + title + '">' + _('Estimation') + '</th>' \
360	'<th title="' + title + '">' + _('Cost') + '</th>' \
361	'<th>' + _('State') + '</th>' \
362	'<th>' + _('Comment') + '</th>' \
363	'</tr>' \
364	'</thead>' \
365	'<tbody id="budget_container">%s</tbody>' \
366	'</table>' \
367	'</span>' \
368	'</fieldset>'
369
370	return fieldset
371
372	def _get_budget_preview(self):
373	fieldset = '<div id="budgetpreview">' \
374	'<h2 class="foldable">' + _('Budget Estimation') + '</h2>' \
375	'<table class="listing">' \
376	'<thead>' \
377	'<tr>' \
378	'<th>' + _('Person') + '</th>' \
379	'<th>' + _('Type') + '</th>' \
380	'<th>' + _('Estimation') + '</th>' \
381	'<th>' + _('Cost') + '</th>' \
382	'<th>' + _('State') + '</th>' \
383	'<th>' + _('Comment') + '</th>' \
384	'</tr>' \
385	'</thead>' \
386	'<tbody id="previewContainer">%s' \
387	'</tbody>' \
388	'</table>' \
389	'</div>'
390	return fieldset
391
392	def pre_process_request(self, req, handler):
393	""" overridden from IRequestFilter"""
394	return handler
395
396	def post_process_request(self, req, template, data, content_type):
397	""" overridden from IRequestFilter"""
398	if req.path_info.startswith('/newticket') or \
399	req.path_info.startswith('/ticket'):
400	add_script(req, 'hw/js/budgeting.js')
401	if not data:
402	return template, data, content_type
403	tkt = data['ticket']
404
405	if tkt and tkt.id and Ticket.id_is_valid(tkt.id): # ticket is ready for saving
406	if self._changed_by_author:
407	self._save_budget(tkt)
408	self._budgets = None
409	return template, data, content_type
410
411	def _get_fields(self, req):
412	budget_dict = {}
413	budget_obj = None
414	# searching budgetfields an send them to db
415	for arg in req.args:
416	list = []
417	list = arg.split(":")
418	if len(list) == 3:
419	row_no = list[1]
420	if budget_dict.has_key(row_no):
421	budget_obj = budget_dict[row_no]
422	else:
423	budget_obj = Budget()
424	budget_dict[row_no] = budget_obj
425	budget_obj.set(list[2], req.args.get(arg))
426
427	# New created field, should be insered
428	if list[0] == "GSfield":
429	budget_obj.set_as_insert()
430	elif list[0] == "GSDBfield":
431	budget_obj.set_as_update()
432	elif list[0] == "DELGSDBfield":
433	budget_obj.set_as_delete()
434
435	return budget_dict
436
437	def _get_milestone_html(self, req, group_by):
438	html = ''
439	stats_by = ''
440	db = self.env.get_read_db()
441	cursor = db.cursor()
442	ms = req.args['id']
443
444	sql = "select sum(b.cost),sum(b.estimation), avg(b.status) from budgeting b, ticket t" \
445	" where b.ticket=t.id and t.milestone='%s'" % ms
446
447	try:
448	# print "milestone sql: %s" % sql
449	cursor.execute(sql)
450	for row in cursor:
451	# print "row"
452	# html = self._get_progress_html(row[0], row[1], row[2])
453	html = '<dl><dt>' + _('Budget in hours') + ':</dt><dd> </dd>' \
454	'<dt>' + _('Cost') + ': <dd>%.2f</dd></dt>' \
455	'<dt>' + _('Estimation') + ': <dd>%.2f</dd></dt>' \
456	'<dt>' + _('Status') + ': <dd>%.1f%%</dd></dt></dl>'
457	html = html % (row[0], row[1], row[2])
458	html = self._get_progress_html(row[0], row[1], row[2]) + html
459	except Exception, e:
460	self.log.error("Error executing SQL Statement \n %s" % e)
461	db.rollback();
462
463	if not group_by:
464	return html, stats_by
465
466	sql = "select t.%s, sum(b.cost), sum(b.estimation), avg(b.status) from budgeting b, ticket t" \
467	" where b.ticket=t.id and t.milestone='%s'" \
468	" group by t.%s order by t.%s" % (group_by, ms, group_by, group_by)
469
470	try:
471	# print "sql: %s" % sql
472	cursor.execute(sql)
473	for row in cursor:
474	status_bar = self._get_progress_html(row[1], row[2], row[3], 75)
475	link = req.href.query({'milestone': ms, group_by: row[0]})
476	if group_by == 'component':
477	link = req.href.report(BUDGET_REPORT_ALL_ID, {'MILESTONE': ms, 'COMPONENT': row[0], 'OWNER': '%'})
478
479	stats_by += '<tr><th scope="row"><a href="%s">' \
480	'%s</a></th>' % (link, row[0])
481	stats_by += '<td>%s</td></tr>' % status_bar
482	except Exception, e:
483	self.log.error("Error executing SQL Statement \n %s" % e)
484	db.rollback();
485
486	return html, stats_by
487
488	def _get_progress_html(self, cost, estimation, status, width=None):
489	ratio = int (0)
490	if estimation > 0 and cost:
491	leftBarValue = int(round((cost * 100) / estimation, 0))
492	ratio = leftBarValue
493	rightBarValue = int(round(100 - leftBarValue, 0))
494	if(rightBarValue + leftBarValue < 100):
495	rightBarValue += 1
496	elif leftBarValue > 100:
497	leftBarValue = int(100)
498	rightBarValue = int(0)
499	else:
500	leftBarValue = int(0)
501	rightBarValue = int(100)
502
503	# print "leftBarValue: %s , rightBarValue: %s" % (leftBarValue, rightBarValue)
504	style_cost = "width: " + str(leftBarValue) + "%"
505	style_est = "width: " + str(rightBarValue) + "%"
506	title = ' title="' + _('Cost') + ' / ' + _('Estimation') + ': %.1f / %.1f (%.0f %%); ' + _('Status') + ': %.1f%%"'
507	title = title % (cost, estimation, ratio, status)
508	right_legend = "%.0f %%" % ratio
509
510	if int(status) == 100:
511	style_cost += ";background:none repeat scroll 0 0 #3300FF;"
512	# style_est += ";background:none repeat scroll 0 0 #C3C3C3;"
513	style_est += ";background:none repeat scroll 0 0 #00BB00;"
514	elif ratio > 100:
515	style_cost += ";background:none repeat scroll 0 0 #BB0000;"
516
517	status_bar = '<table class="progress"'
518	if width:
519	status_bar += ' style="width: ' + str(width) + '%"'
520	right_legend = "%.0f / %.0f" % (cost, estimation)
521	status_bar += '><tr><td class="closed" style="' + style_cost + '">\
522	<a' + title + '></a> \
523	</td><td style="' + style_est + '" class="open">\
524	<a' + title + '></a> \
525	</td></tr></table><p class="percent"' + title + '>' + right_legend + '</p>'
526
527	# print "status_bar: %s" % (status_bar)
528	return status_bar
529
530	def _get_ticket_html(self):
531	# print "[filter_stream] self._budgets: %s" % self._budgets
532	input_html = ''
533	preview_html = ''
534
535	if not self._type_list:
536	types_str = self.config.get(self._CONFIG_SECTION, 'types')
537	self._type_list = re.sub(r'\\|', ';', types_str)
538	self.log.debug("INIT self._type_list: %s" % self._type_list)
539	types = self._type_list.split(';')
540
541	if not self._name_list:
542	self._name_list = self.get_user_list()
543	self.log.debug("INIT self._name_list: %s" % self._name_list)
544	for user in self._name_list:
545	if not self._name_list_str:
546	self._name_list_str = str(user)
547	else:
548	self._name_list_str += ';' + str(user)
549
550	if self._budgets:
551	for pos, budget in self._budgets.iteritems():
552	user_options = ''
553	type_options = ''
554	values = budget.get_values()
555	input_html += '<tr id="row:%s">' % pos
556	preview_html += '<tr>'
557	el_in_list = False
558
559	if self._name_list:
560	for opt in self._name_list:
561	selected = ''
562	if values['username'] == opt:
563	selected = ' selected'
564	el_in_list = True
565	# preview_html += '<td>%s</td>' % opt
566	user_options += '<option%s>%s</option>' % (selected, opt)
567	if not el_in_list:
568	user_options += '<option selected>%s</option>' % (values['username'])
569
570	el_in_list = False
571	for t in types:
572	selected = ''
573	if values['type'] == t:
574	selected = ' selected'
575	el_in_list = True
576	# preview_html += '<td>%s</td>' % t
577	type_options += '<option%s>%s</option>' % (selected, t)
578	if not el_in_list:
579	type_options += '<option selected>%s</option>' % (values['type'])
580
581	input_html += '<td><select name="GSDBfield:%s:1" >%s</select></td>' % (pos, user_options)
582	preview_html += '<td>%s</td>' % values['username']
583	input_html += '<td><select name="GSDBfield:%s:2">%s</select></td>' % (pos, type_options)
584	preview_html += '<td>%s</td>' % values['type']
585	size = 10
586	for col in range(3, 7):
587	col_val = budget.get_value(col)
588	if col == 6 and col_val: # comment
589	col_val = col_val.replace('"', """)
590	size = 20
591	elif not col_val:
592	if col < 6:
593	col_val = '0'
594	else:
595	col_val = ''
596	size = 20
597	input_html += '<td><input size="%s" name="GSDBfield:%s:%s" value="%s"></td>' % (size, pos, col, col_val)
598	preview_html += '<td>%s' % col_val
599	if col == 5:
600	preview_html += ' %'
601	preview_html += '</td>'
602	input_html += '<td><button type="button" name="deleteRow%s" onclick="deleteRow(%s)">[-]</button></td>' % (pos, pos)
603	input_html += '</tr>'
604	preview_html += '</tr>'
605	# print "input_html: %s \n\n preview_html: %s" % (input_html, preview_html)
606	return input_html, preview_html
607
608	def _check_init(self):
609	"""First setup or initentities deleted
610	check initialization, like db setup etc."""
611	if (self.config.get(self._CONFIG_SECTION, 'version')):
612	self.log.debug ("have local ini, so everything is set")
613	return True
614	else:
615	self.log.debug ("check database")
616	sql = "select ticket from %s" % BUDGETING_TABLE.name
617	db = self.env.get_read_db()
618	myCursor = db.cursor()
619	try:
620	myCursor.execute(sql)
621	self.config.set(self._CONFIG_SECTION, 'version', '1')
622	self.config.save()
623	self.log.info ("created local ini entries with name budgeting")
624	# print "created local ini entries with name budgeting"
625	return True
626	except Exception:
627	self.log.warn ("[_check_init] error while checking database; table 'budgeting' is probably not present")
628	db.close()
629
630	return False
631
632	#===============================================================================
633	# ITemplateProvider methods
634	# Used to add the plugin's templates and htdocs
635	#===============================================================================
636	def get_templates_dirs(self):
637	return [resource_filename(__name__, 'htdocs')]
638
639	def get_htdocs_dirs(self):
640	return [('hw', resource_filename(__name__, 'htdocs'))]
641
642
643	def _load_budget(self, ticket_id):
644	self._budgets = {}
645	if not ticket_id:
646	return
647
648	db = self.env.get_read_db()
649	cursor = db.cursor()
650	sql = "SELECT position, username, type, estimation, cost, status, comment" \
651	" FROM budgeting where ticket=%s order by position" % ticket_id
652
653	# print "[_load_budget] sql: %s " % sql
654	try:
655	cursor.execute(sql)
656	rows = list(cursor.fetchall())
657	# print "after execute -- rows: %s" % rows
658	for row in rows:
659	# print "row"
660	budget = Budget()
661	for i, col in enumerate(row):
662	# print "%s. col: %s" % (i, col)
663	if i > 0:
664	budget.set(i, row[i])
665	pos = int (row[0])
666	self._budgets[pos] = budget
667	self.log.debug("[_load_budget] loaded budget: %s" % budget.get_values())
668	except Exception, e:
669	self.log.error("Error executing SQL Statement %s \n Error: %s" % (sql, e))
670	db.rollback();
671	db.close()
672	# print "[_load_budget] loaded self._budgets: %s for ticket %s" % (self._budgets, ticket_id)
673
674	def _save_budget(self, tkt):
675	if self._budgets and tkt and tkt.id:
676	user = self._changed_by_author
677	self._changed_by_author = None
678	# print "======> SAVE ticket"
679	# print "[_save_budget] self._budgets: %s " % self._budgets
680	for pos, budget in self._budgets.iteritems():
681	budget.do_action(self.env, tkt.id, int(pos))
682	self.log.debug("saved budget of position: %s" % pos)
683	self._log_changes(tkt, user)
684	self._budgets = None
685
686	def _log_changes(self, tkt, change_user):
687	if not tkt or not tkt.id:
688	return
689	cur_time = self._get_current_time()
690	db = self.env.get_read_db()
691
692	try:
693	for pos, budget in self._budgets.iteritems():
694	action = budget.get_action_name()
695	old_value = ''
696	new_value = ''
697	if action == 'insert':
698	new_value = "%s, %s: %s" % (budget.get_value(1), budget.get_value(2), budget.get_value(6))
699	elif action == 'delete':
700	old_value = "%s, %s: %s" % (budget.get_value(1), budget.get_value(2), budget.get_value(6))
701	elif action == 'update':
702	continue
703
704	sql = "INSERT INTO ticket_change(ticket, time, author, field, oldvalue, newvalue)" \
705	" VALUES(%s, %s, '%s', 'budgeting.%s', '%s', '%s')" % (tkt.id, cur_time, change_user, pos, old_value, new_value)
706	db.cursor().execute(sql)
707	db.commit()
708	self.log.debug("successfully logged budget, pos %s for ticket %s" % (pos, tkt.id))
709
710	db.close()
711	except Exception, ex:
712	self.log.error("Error while logging change: %s" % ex)
713
714	def _get_current_time(self):
715	return (time.time() - 1) * 1000000
716
717	#===========================================================================
718	# If a valid validation check was performed, the budgeting data will
719	# be stored to database
720	#===========================================================================
721	def validate_ticket(self, req, ticket):
722	""" overriden from ITicketManipulator """
723	errors = []
724	try:
725	self._budgets = self._get_fields(req)
726	self._changed_by_author = req.authname or 'anonymous'
727	self.log.info("[validate] budget has changed by author: %s" % self._changed_by_author)
728	except Exception, ex:
729	self.log.error("Error while validating: %s" % ex)
730	fld, e = ex
731	errors.append([fld, str(e)])
732
733	return errors
734
735
736	def create_table(self):
737	'''
738	Constructor, see trac/postgres_backend.py:95 (method init_db)
739	'''
740	conn, dummyArgs = DatabaseManager(self.env).get_connector()
741	db = self.env.get_read_db()
742	cursor = db.cursor()
743	try:
744	for stmt in conn.to_sql(BUDGETING_TABLE):
745	if db.schema:
746	stmt = re.sub(r'CREATE TABLE ', 'CREATE TABLE "'
747	+ db.schema + '".', stmt)
748	stmt = re.sub(r'(?i)bigint', 'NUMERIC(10,2)', stmt)
749	stmt += ";"
750	self.log.info("[INIT table] executing sql: %s" % stmt)
751	cursor.execute(stmt)
752	self.log.info("[INIT table] successfully created table %s" % BUDGETING_TABLE.name)
753	db.commit()
754	except Exception, e:
755	self.log.error("[INIT table] Error executing SQL Statement \n %s" % e)
756	db.rollback();
757	finally:
758	db.close()
759	self.create_reports()
760
761	def create_reports(self):
762	# print "[INIT report] create_reports: %s" % self.BUDGET_REPORTS
763	for report in self.BUDGET_REPORTS:
764	try:
765	db = self.env.get_read_db()
766	myCursor = db.cursor()
767	self.log.info("having myCursor")
768	descr = _(report[2])
769	self.log.info("descr: %s" % descr)
770	descr = re.sub(r"'", "''", descr)
771	self.log.info("report[3]: %s" % report[3])
772	self.log.info(" VALUES: %s, '%s', '%s'" % (report[0], _(report[1]), report[3]))
773	sql = "INSERT INTO report (id, author, title, query, description) "
774	sql += " VALUES(%s, null, '%s', '%s', '%s');" % (report[0], _(report[1]), report[3], descr)
775	self.log.info("[INIT reports] executing sql: %s" % sql)
776	myCursor.execute(sql)
777	db.commit()
778	self.log.info("[INIT reports] successfully created report with id %s" % report[0])
779	except Exception, e:
780	self.log.error("[INIT reports] Error executing SQL Statement \n %s" % e)
781	db.rollback();
782	raise e
783	finally:
784	db.close()
785
786
787	def get_col_list(self, ignore_cols=None):
788	""" return col list as string; usable for selecting all cols
789	from budgeting table """
790	col_list = "";
791	i = 0
792	for col in BUDGETING_TABLE.columns:
793	try:
794	if ignore_cols and ignore_cols.index(col.name) > -1: continue
795	except: pass
796
797	if (i > 0):
798	col_list += ","
799	col_list += col.name
800	i += 1
801	return col_list
802
803
804	def get_user_list(self):
805	db = self.env.get_read_db()
806	myCursor = db.cursor()
807	sqlResult = []
808
809	sql = "select distinct sid from session where authenticated > 0 order by sid"
810
811
812	if self.config.get(self._CONFIG_SECTION, 'retrieve_users') == "permission":
813	sql = "select distinct username from permission"
814	if self.config.get(self._CONFIG_SECTION, 'exclude_users'):
815	excl_user = self.config.get(self._CONFIG_SECTION, 'exclude_users')
816	sql = "%s where username not in (%s)" % (sql, excl_user)
817	sql += " order by username"
818	try:
819	myCursor.execute(sql)
820	for row in myCursor:
821	sqlResult.append(row[0])
822	except Exception, e:
823	self.log.error("Error executing SQL Statement \n %s" % e)
824	db.rollback();
825	db.close()
826	return sqlResult

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: budgetingplugin/0.12/ticketbudgeting/ticketbudgeting.py

Download in other formats: