def get_summary(self, req, fromdate = None, todate = None):
def myformat_date(dte):
if dte:
return format_date(dte, '%e %b %Y')
return 'No date set'
def myformat_hours(hrs, fallback='No estimate available'):
from math import floor
if hrs:
hrs = float(hrs)
if 0 != hrs:
neg = False
if hrs < 0:
neg = True
hours *= -1
mins = floor((hrs - floor(hrs)) * 60)
str = ''
if neg:
str = '-'
if hrs:
str = "%s%sh" % (str, int(floor(hrs)))
if mins:
str = "%s %sm" % (str, int(mins))
return str;
return fallback
client = self.client
xml = etree.Element('clientsplugin')
# Place basic client info here
xclient = etree.SubElement(xml, 'client')
etree.SubElement(xclient, 'name').text = client
if fromdate:
etree.SubElement(xclient, 'lastupdate').text = myformat_date(fromdate)
# Information about milestones
milestones = {}
xmilestones = etree.SubElement(xml, 'milestones')
db = self.env.get_read_db()
have_data = False
# Load in a summary of the client's tickets
sql = ("""\
SELECT t.id, t.summary, t.description, t.status, t.milestone,
m.due, m.completed, m.description AS mdesc,
tcust2.value AS estimatedhours, tcust3.value AS totalhours
FROM ticket_custom AS tcust
INNER JOIN ticket AS t ON tcust.ticket=t.id
LEFT JOIN ticket_custom AS tcust2 ON t.id=tcust2.ticket AND tcust2.name='estimatedhours'
LEFT JOIN ticket_custom AS tcust3 ON t.id=tcust3.ticket AND tcust3.name='totalhours'
LEFT JOIN milestone m ON t.milestone=m.name
WHERE tcust.name = 'client'
AND tcust.value = %s
AND t.milestone IN (
SELECT DISTINCT st.milestone
FROM ticket_custom AS stcust
INNER JOIN ticket AS st ON stcust.ticket=st.id
INNER JOIN milestone AS sm ON st.milestone=sm.name
WHERE stcust.name = tcust.name
AND stcust.value = tcust.value
AND (sm.due > %s OR sm.completed > %s))
ORDER BY m.due ASC
""")
cur2 = db.cursor()
now = int(time.time()*1000000)
cur2.execute(sql, (client, now, (now - (7*24*60*60*1000000))))
xsummary = etree.SubElement(xml, 'summary')
for tid, summary, description, status, milestone, due, completed, mdescription, estimatedhours, totalhours in cur2:
have_data = True
if milestone:
if not milestones.has_key(milestone):
xmilestone = etree.SubElement(xmilestones, 'milestone')
etree.SubElement(xmilestone, 'name').text = milestone
etree.SubElement(xmilestone, 'duetimestamp').text = str(due)
etree.SubElement(xmilestone, 'due').text = myformat_date(due)
if completed:
etree.SubElement(xmilestone, 'completed').text = myformat_date(completed)
if mdescription:
xmilestone.append(etree.XML('<description>%s</description>' % wiki_to_html(extract_client_text(mdescription), self.env, req)))
else:
etree.SubElement(xmilestone, 'description').text = ''
# Store for use
milestones[milestone] = { 'hours': 0, 'xml': xmilestone }
# Add hours to create a total.
if estimatedhours:
milestones[milestone]['hours'] += float(estimatedhours)
self.env.log.debug(" Summarising ticket #%s" % tid)
ticket = etree.SubElement(xsummary, 'ticket')
etree.SubElement(ticket, 'id').text = str(tid)
etree.SubElement(ticket, 'summary').text = summary
ticket.append(etree.XML('<description>%s</description>' % wiki_to_html(extract_client_text(description), self.env, req)))
etree.SubElement(ticket, 'status').text = status
etree.SubElement(ticket, 'milestone').text = milestone
# For conveneince, put the date here too (keeps the XSLTs simpler)
etree.SubElement(ticket, 'due').text = myformat_date(due)
if estimatedhours:
etree.SubElement(ticket, 'estimatedhours').text = myformat_hours(estimatedhours)
if totalhours:
etree.SubElement(ticket, 'totalhours').text = myformat_hours(totalhours, 'None')
# Put the total hours into the milestone info
for milestone in milestones:
etree.SubElement(milestones[milestone]['xml'], 'estimatedhours').text = myformat_hours(milestones[milestone]['hours'])
if self.debug:
file = open('/tmp/send-client-email.xml', 'w')
file.write(etree.tostring(xml, pretty_print=True))
file.close()
self.env.log.debug(" Wrote XML to /tmp/send-client-email.xml")
if not have_data:
return None
return xml
def get_summary(self, req, fromdate = None, todate = None):
def myformat_date(dte):
if dte:
return format_date(dte, '%e %b %Y')
return 'No date set'
def myformat_hours(hrs, fallback='No estimate available'):
from math import floor
if hrs:
hrs = float(hrs)
if 0 != hrs:
neg = False
if hrs < 0:
neg = True
hours *= -1
mins = floor((hrs - floor(hrs)) * 60)
str = ''
if neg:
str = '-'
if hrs:
str = "%s%sh" % (str, int(floor(hrs)))
if mins:
str = "%s %sm" % (str, int(mins))
return str;
return fallback
client = self.client
xml = etree.Element('clientsplugin')
# Place basic client info here
xclient = etree.SubElement(xml, 'client')
etree.SubElement(xclient, 'name').text = client
if fromdate:
etree.SubElement(xclient, 'lastupdate').text = myformat_date(fromdate)
# Information about milestones
months = {}
xmonths = etree.SubElement(xml, 'months')
db = self.env.get_read_db()
have_data = False
# Load in a summary of the client's tickets
sql = ("""\
SELECT t.id, t.summary, t.description, t.status,
SUM(tchng.newvalue) AS totalhours, CONCAT(MONTHNAME(FROM_UNIXTIME(tchng.time/1000000)), " ", YEAR(FROM_UNIXTIME(tchng.time/1000000))) AS month
FROM ticket_custom AS tcust
INNER JOIN ticket AS t ON tcust.ticket=t.id
INNER JOIN ticket_change AS tchng ON t.id=tchng.ticket AND tchng.field='hours' AND tchng.oldvalue=0
WHERE tcust.name = 'client'
AND tcust.value = %s
AND tchng.time >= (UNIX_TIMESTAMP(PERIOD_ADD(EXTRACT(YEAR_MONTH FROM NOW()), -3)*100+1)*1000000)
GROUP BY t.id, MONTH(FROM_UNIXTIME(tchng.time/1000000))
ORDER BY tchng.time desc;
""")
cur2 = db.cursor()
cur2.execute(sql, (client,))
xsummary = etree.SubElement(xml, 'summary')
for tid, summary, description, status, totalhours, month in cur2:
have_data = True
if not months.has_key(month):
xmonth = etree.SubElement(xmonths, 'month')
etree.SubElement(xmonth, 'name').text = month
months[month] = { 'totalhours': 0, 'xml': xmonth }
# Add hours to create a total.
months[month]['totalhours'] += float(totalhours)
self.env.log.debug(" Summarising ticket #%s in %s" % (tid, month))
ticket = etree.SubElement(xsummary, 'ticket')
etree.SubElement(ticket, 'id').text = str(tid)
etree.SubElement(ticket, 'summary').text = summary
ticket.append(etree.XML('<description>%s</description>' % wiki_to_html(extract_client_text(description), self.env, req)))
etree.SubElement(ticket, 'status').text = status
etree.SubElement(ticket, 'month').text = month
etree.SubElement(ticket, 'totalhours').text = myformat_hours(totalhours, 'None')
# Put the total hours into the month info
for month in months:
etree.SubElement(months[month]['xml'], 'totalhours').text = myformat_hours(months[month]['totalhours'])
if self.debug:
file = open('/tmp/send-client-email.xml', 'w')
file.write(etree.tostring(xml, pretty_print=True))
file.close()
self.env.log.debug(" Wrote XML to /tmp/send-client-email.xml")
if not have_data:
return None
return xml
def get_summary(self, req, fromdate = None, todate = None):
def myformat_date(dte):
if dte:
return format_date(dte, '%e %b %Y')
return 'No date set'
def myformat_hours(hrs):
from math import floor
if hrs:
hrs = float(hrs)
if 0 != hrs:
neg = False
if hrs < 0:
neg = True
hours *= -1
mins = floor((hrs - floor(hrs)) * 60)
str = ''
if neg:
str = '-'
if hrs:
str = "%s%sh" % (str, int(floor(hrs)))
if mins:
str = "%s %sm" % (str, int(mins))
return str;
return 'No estimate available'
client = self.client
xml = etree.Element('clientsplugin')
# Place basic client info here
xclient = etree.SubElement(xml, 'client')
etree.SubElement(xclient, 'name').text = client
if fromdate:
etree.SubElement(xclient, 'lastupdate').text = myformat_date(fromdate)
db = self.env.get_read_db()
have_data = False
# Load in any changes that have happend
sql = ("""\
SELECT t.id, t.summary, t.description, t.status, t.resolution, t.milestone, m.due,
tchng.field, tchng.oldvalue, tchng.newvalue
FROM ticket_custom tcust
INNER JOIN ticket AS t ON tcust.ticket=t.id
INNER JOIN ticket_change AS tchng ON t.id=tchng.ticket
LEFT JOIN milestone AS m ON t.milestone=m.name
WHERE tcust.name = 'client'
AND tcust.value = %s
AND tchng.field IN ('comment', 'status', 'resolution', 'milestone')
AND tchng.time >= %s
AND tchng.time < %s
AND t.milestone IN (
SELECT DISTINCT st.milestone
FROM ticket_custom AS stcust
INNER JOIN ticket AS st ON stcust.ticket=st.id
INNER JOIN milestone AS sm ON st.milestone=sm.name
WHERE stcust.name = tcust.name
AND stcust.value = tcust.value
AND sm.due > 0)
ORDER BY t.time
""")
cur2 = db.cursor()
cur2.execute(sql, (client, fromdate*1000000, todate*1000000))
changes = etree.SubElement(xml, 'changes')
lasttid = 0
for tid, summary, description, status, resolution, milestone, due, cgfield, oldvalue, newvalue in cur2:
text = ''
if 'status' == cgfield:
text = 'Status changed from "%s" to "%s"' % (oldvalue, newvalue)
elif 'milestone' == cgfield:
text = 'Milestone changed from "%s" to "%s" - please check for revised delivery date.' % (oldvalue, newvalue)
elif 'resolution' == cgfield:
if oldvalue and not newvalue:
text = 'Resolution removed'
elif not oldvalue and newvalue:
text = 'Resolution set to "%s"' % (newvalue)
else:
text = 'Resolution changed from "%s" to "%s"' % (oldvalue, newvalue)
elif 'comment' == cgfield:
# Todo - extract...
text = extract_client_text(newvalue).strip()
if '' == text:
# No comments for the client here so ignore it.
continue
text = "''Comment for your information:''[[BR]][[BR]]" + text
else:
# Client should not know any more than this
continue
if self.debug:
self.env.log.debug(" Change notification (%s) for ticket #%s" % (cgfield, tid))
have_data = True
if lasttid != tid:
ticket = etree.SubElement(changes, 'ticket')
etree.SubElement(ticket, 'id').text = str(tid)
etree.SubElement(ticket, 'summary').text = summary
ticket.append(etree.XML('<description>%s</description>' % wiki_to_html(extract_client_text(description), self.env, req)))
etree.SubElement(ticket, 'status').text = status
etree.SubElement(ticket, 'resolution').text = resolution
etree.SubElement(ticket, 'milestone').text = milestone
etree.SubElement(ticket, 'due').text = myformat_date(due)
changelog = etree.SubElement(ticket, 'changelog')
detail = etree.XML('<detail>%s</detail>' % wiki_to_html(text, self.env, req))
detail.set('field', cgfield)
if oldvalue:
detail.set('oldvalue', oldvalue)
if newvalue:
detail.set('newvalue', newvalue)
changelog.append(detail)
lasttid = tid
if self.debug:
file = open('/tmp/send-client-email.xml', 'w')
file.write(etree.tostring(xml, pretty_print=True))
file.close()
self.env.log.debug(" Wrote XML to /tmp/send-client-email.xml")
if not have_data:
return None
return xml
def get_summary(self, req, fromdate=None, todate=None):
def myformat_date(dte):
if dte:
return format_date(dte, '%e %b %Y')
return "No date set"
def myformat_hours(hrs, fallback='No estimate available'):
from math import floor
if hrs:
hrs = float(hrs)
if 0 != hrs:
neg = False
if hrs < 0:
neg = True
hrs *= -1
mins = floor((hrs - floor(hrs)) * 60)
s = ''
if neg:
s = '-'
if hrs:
s = "%s%sh" % (s, int(floor(hrs)))
if mins:
s = "%s %sm" % (s, int(mins))
return s
return fallback
client = self.client
xml = etree.Element('clientsplugin')
# Place basic client info here
xclient = etree.SubElement(xml, 'client')
etree.SubElement(xclient, 'name').text = client
if fromdate:
etree.SubElement(xclient, 'lastupdate').text = \
myformat_date(fromdate)
# Information about milestones
months = {}
xmonths = etree.SubElement(xml, 'months')
have_data = False
# Load in a summary of the client's tickets
sql = ("""\
SELECT t.id, t.summary, t.description, t.status,
SUM(tchng.newvalue) AS totalhours,
CONCAT(MONTHNAME(FROM_UNIXTIME(tchng.time/1000000)),
" ",
YEAR(FROM_UNIXTIME(tchng.time/1000000))) AS month
FROM ticket_custom AS tcust
INNER JOIN ticket AS t ON tcust.ticket=t.id
INNER JOIN ticket_change AS tchng
ON t.id=tchng.ticket
AND tchng.field='hours'
AND tchng.oldvalue=0
WHERE tcust.name = 'client'
AND tcust.value = %s
AND tchng.time >= (UNIX_TIMESTAMP(PERIOD_ADD(EXTRACT(YEAR_MONTH FROM NOW()), -3)*100+1)*1000000)
GROUP BY t.id, MONTH(FROM_UNIXTIME(tchng.time/1000000))
ORDER BY tchng.time DESC;
""")
xsummary = etree.SubElement(xml, 'summary')
for tid, summary, description, status, totalhours, month \
in self.env.db_query(sql, (client,)):
have_data = True
if month not in months:
xmonth = etree.SubElement(xmonths, 'month')
etree.SubElement(xmonth, 'name').text = month
months[month] = {'totalhours': 0, 'xml': xmonth}
# Add hours to create a total.
months[month]['totalhours'] += float(totalhours)
self.log.debug(" Summarising ticket #%s in %s", tid, month)
ticket = etree.SubElement(xsummary, 'ticket')
etree.SubElement(ticket, 'id').text = str(tid)
etree.SubElement(ticket, 'summary').text = summary
ticket.append(
etree.XML('<description>%s</description>' %
format_to_html(self.env, web_context(req),
extract_client_text(description))))
etree.SubElement(ticket, 'status').text = status
etree.SubElement(ticket, 'month').text = month
etree.SubElement(ticket, 'totalhours').text = myformat_hours(
totalhours, 'None')
# Put the total hours into the month info
for month in months:
etree.SubElement(months[month]['xml'], 'totalhours').\
text = myformat_hours(months[month]['totalhours'])
if self.debug:
with open('/tmp/send-client-email.xml', 'w') as file_:
file_.write(etree.tostring(xml, pretty_print=True))
self.log.debug(" Wrote XML to /tmp/send-client-email.xml")
if not have_data:
return None
return xml