def get_reports_by_group(self, group):
"""Gets all of the reports for a given group"""
rv = {}
try:
res = dbhelper.get_result_set(
self.env,
"SELECT custom_report.subgroup,report.id,report.title,"
" custom_report.version, custom_report.uuid "
"FROM custom_report "
"LEFT JOIN report ON custom_report.id=report.id "
"WHERE custom_report.maingroup=%s "
"ORDER BY custom_report.subgroup,custom_report.ordering",
group)
if not res:
return rv
for subgroup, id, title, version, uuid in res.rows:
if not rv.has_key(subgroup):
rv[subgroup] = {"title": subgroup, "reports": []}
rv[subgroup]["reports"].append({
"id": int(id),
"title": title,
"version": version,
"uuid": uuid
})
except:
self.log.exception("Error getting reports by group")
return rv
def set_request_billing_dates(self, req):
billing_dates = []
billing_time_sql = """
SELECT DISTINCT time as value, str_value as [text]
FROM bill_date
"""
rs = dbhelper.get_result_set(self.env.get_db_cnx(), billing_time_sql)
for (value, text) in rs.rows:
billing_info = {'text': text, 'value': value}
billing_dates.extend([billing_info])
self.log.debug("bill-dates: %s" % billing_dates)
req.hdf['billing_info.billdates'] = billing_dates
def set_request_billing_dates(self, req):
billing_dates = []
billing_time_sql = """
SELECT DISTINCT time as value, str_value as [text]
FROM bill_date
"""
rs = dbhelper.get_result_set(self.env.get_db_cnx(), billing_time_sql)
for (value, text) in rs.rows:
billing_info = {'text':text , 'value':value}
billing_dates.extend([billing_info])
self.log.debug("bill-dates: %s"%billing_dates)
req.hdf['billing_info.billdates'] = billing_dates
def set_request_billing_dates(self, data):
billing_dates = []
billing_time_sql = """
SELECT DISTINCT time as value, str_value as text
FROM bill_date
"""
rs = dbhelper.get_result_set(self, billing_time_sql)
if rs:
for (value, text) in rs.rows:
billing_info = {'text':text , 'value':value}
billing_dates.extend([billing_info])
#self.log.debug("bill-dates: %s"%billing_dates)
data['billing_info']["billdates"] = billing_dates
def set_request_billing_dates(self, data):
billing_dates = []
billing_time_sql = """
SELECT DISTINCT time as value, str_value as text
FROM bill_date
"""
rs = dbhelper.get_result_set(billing_time_sql)
if rs:
for (value, text) in rs.rows:
billing_info = {'text': text, 'value': value}
billing_dates.extend([billing_info])
#self.log.debug("bill-dates: %s"%billing_dates)
data['billing_info']["billdates"] = billing_dates
def reports_need_upgrade(self):
bit = False
tables = dbhelper.get_database_table_names(self.env.get_db_cnx())
report_version = [
table for table in tables if table == 'report_version'
]
if not report_version:
return True
#make versions hash
_versions = dbhelper.get_result_set(
self.env.get_db_cnx(), """
SELECT report as id, version, r.title as title
FROM report_version
JOIN report r ON r.Id = report_version.report
""")
versions = {}
for (id, version, title) in _versions.rows:
versions[title] = (id, version)
for report_group in all_reports:
rlist = report_group["reports"]
for report in rlist:
title = report["title"]
new_version = report["version"]
#the report map is a list of (id, name) tuples for the reports
# here we want to see if our report is in the list
idx = [
i for i in range(0, len(self.reportmap))
if self.reportmap[i][1] == title
]
if not idx:
self.log.warning("Report '%s' needs to be added" % title)
bit = True
else:
# If we had a report make sure its at the correct version
if versions.has_key(title):
(id, ver) = versions[title]
else:
ver = 0
if ver < new_version:
bit = True
return bit
def get_version_hash_by_group(self, group):
"""Gets all of the reports for a given group as a uuid=>version hash"""
rv = {}
try:
res = dbhelper.get_result_set(
self.env,
"SELECT custom_report.subgroup,report.id,report.title,"
" custom_report.version, custom_report.uuid "
"FROM custom_report "
"LEFT JOIN report ON custom_report.id=report.id "
"WHERE custom_report.maingroup=%s "
"ORDER BY custom_report.subgroup,custom_report.ordering",
group)
if not res:
return rv
for subgroup, id, title, version, uuid in res.rows:
rv[uuid] = version
except:
self.log.exception("Failed to get_version_hash_by_group")
return rv
def get_version_hash_by_group(self, group):
"""Gets all of the reports for a given group as a uuid=>version hash"""
rv = {}
try:
res = dbhelper.get_result_set(
self.env,
"SELECT custom_report.subgroup,report.id,report.title,"
" custom_report.version, custom_report.uuid "
"FROM custom_report "
"LEFT JOIN report ON custom_report.id=report.id "
"WHERE custom_report.maingroup=%s "
"ORDER BY custom_report.subgroup,custom_report.ordering",
group)
if not res:
return rv;
for subgroup, id, title, version, uuid in res.rows:
rv[uuid] = version
except:
self.log.exception("Failed to get_version_hash_by_group")
return rv
def get_reports_by_group(self, group):
"""Gets all of the reports for a given group"""
rv = {}
try:
res = dbhelper.get_result_set(
self.env,
"SELECT custom_report.subgroup,report.id,report.title,"
" custom_report.version, custom_report.uuid "
"FROM custom_report "
"LEFT JOIN report ON custom_report.id=report.id "
"WHERE custom_report.maingroup=%s "
"ORDER BY custom_report.subgroup,custom_report.ordering", group)
if not res:
return rv
for subgroup, id, title, version, uuid in res.rows:
if not rv.has_key(subgroup):
rv[subgroup] = { "title": subgroup,
"reports": [] }
rv[subgroup]["reports"].append( { "id": int(id), "title": title, "version":version, "uuid":uuid } )
except:
self.log.exception("Error getting reports by group")
return rv
def reports_need_upgrade(self):
bit = False
tables = dbhelper.get_database_table_names(self.env.get_db_cnx())
report_version = [table for table in tables if table == 'report_version']
if not report_version:
return True
#make versions hash
_versions = dbhelper.get_result_set(self.env.get_db_cnx(),
"""
SELECT report as id, version, r.title as title
FROM report_version
JOIN report r ON r.Id = report_version.report
""")
versions = {}
for (id, version, title) in _versions.rows:
versions[title] = (id, version)
for report_group in all_reports:
rlist = report_group["reports"]
for report in rlist:
title = report["title"]
new_version = report["version"]
#the report map is a list of (id, name) tuples for the reports
# here we want to see if our report is in the list
idx = [i for i in range(0, len(self.reportmap)) if self.reportmap[i][1] == title]
if not idx:
self.log.warning("Report '%s' needs to be added" % title)
bit = True;
else:
# If we had a report make sure its at the correct version
if versions.has_key(title):
(id, ver) = versions[title]
else:
ver = 0
if ver < new_version:
bit = True
return bit
def do_reports_upgrade(self):
print "Beginning Reports Upgrade"
#make version hash
_versions = dbhelper.get_result_set(
self.env.get_db_cnx(), """
SELECT report as id, version, r.title as title
FROM report_version
JOIN report r ON r.Id = report_version.report
""")
versions = {}
for (id, version, title) in _versions.rows:
versions[title] = (id, version)
biggestId = dbhelper.get_scalar(
self.env.get_db_cnx(),
"SELECT ID FROM report ORDER BY ID DESC LIMIT 1")
def insert_report_version(id, ver):
sql = "DELETE FROM report_version WHERE report = %s;"
dbhelper.execute_non_query(self.env.get_db_cnx(), sql, id)
sql = """
INSERT INTO report_version (report, version)
VALUES (%s, %s);"""
# print "about to insert report_version"
dbhelper.execute_non_query(self.env.get_db_cnx(), sql, id, ver)
# print "inserted report_version"
for report_group in all_reports:
rlist = report_group["reports"]
for report in rlist:
title = report["title"]
new_version = report["version"]
report_id = [
rid for (rid, map_title) in self.reportmap
if map_title == title
]
if not report_id:
bit = True
sql = """INSERT INTO report (id,author,title,query,description)
VALUES (%s,'Timing and Estimation Plugin',%s,%s,'') """
biggestId += 1
dbhelper.execute_non_query(self.env.get_db_cnx(), sql,
biggestId, title, report["sql"])
insert_report_version(biggestId, new_version)
report["reportnumber"] = biggestId
self.reportmap.extend([(biggestId, title)])
else:
report_id = report_id[0]
report["reportnumber"] = report_id
# If we had a report make sure its at the correct version
if versions.has_key(title):
(_, ver) = versions[title]
else:
ver = 0
if not ver:
sql = """
UPDATE report
SET query=%s
WHERE id = %s """
print "updating report: %s" % title
dbhelper.execute_non_query(self.env.get_db_cnx(), sql,
report["sql"], report_id)
insert_report_version(report_id, new_version)
elif ver < new_version:
sql = """
UPDATE report
SET query=%s
WHERE id = %s """
print "updating report to new version: %s" % title
dbhelper.execute_non_query(self.env.get_db_cnx(), sql,
report["sql"], report_id)
sql = """
UPDATE report_version
SET version = %s
WHERE report = %s
"""
dbhelper.execute_non_query(self.env.get_db_cnx(), sql,
new_version, report_id)
def getEstimateLineItemsResultSet(env, id):
return dbhelper.get_result_set(
env, "SELECT * FROM estimate_line_item WHERE estimate_id=%s", id)
def getEstimateResultSet(env, id):
return dbhelper.get_result_set(env, "SELECT * FROM estimate WHERE id=%s",
id)
def getEstimateLineItemsResultSet(env, id):
return dbhelper.get_result_set(env, "SELECT * FROM estimate_line_item WHERE estimate_id=%s", id)
def getEstimateResultSet(env, id):
return dbhelper.get_result_set(env, "SELECT * FROM estimate WHERE id=%s", id)
def do_reports_upgrade(self):
print "Beginning Reports Upgrade"
#make version hash
_versions = dbhelper.get_result_set(self.env.get_db_cnx(),
"""
SELECT report as id, version, r.title as title
FROM report_version
JOIN report r ON r.Id = report_version.report
""")
versions = {}
for (id, version, title) in _versions.rows:
versions[title] = (id, version)
biggestId = dbhelper.get_scalar(self.env.get_db_cnx(),
"SELECT ID FROM report ORDER BY ID DESC LIMIT 1")
def insert_report_version(id, ver):
sql = "DELETE FROM report_version WHERE report = %s;"
dbhelper.execute_non_query(self.env.get_db_cnx(), sql, id )
sql = """
INSERT INTO report_version (report, version)
VALUES (%s, %s);"""
# print "about to insert report_version"
dbhelper.execute_non_query(self.env.get_db_cnx(), sql, id, ver )
# print "inserted report_version"
for report_group in all_reports:
rlist = report_group["reports"]
for report in rlist:
title = report["title"]
new_version = report["version"]
report_id = [rid
for (rid, map_title) in self.reportmap
if map_title == title]
if not report_id:
bit = True;
sql = """INSERT INTO report (id,author,title,query,description)
VALUES (%s,'Timing and Estimation Plugin',%s,%s,'') """
biggestId += 1
dbhelper.execute_non_query(self.env.get_db_cnx(), sql,
biggestId, title, report["sql"])
insert_report_version(biggestId, new_version)
report["reportnumber"] = biggestId
self.reportmap.extend([(biggestId,title)])
else:
report_id = report_id[0]
report["reportnumber"] = report_id
# If we had a report make sure its at the correct version
if versions.has_key(title):
( _ , ver) = versions[title]
else:
ver = 0
if not ver:
sql = """
UPDATE report
SET query=%s
WHERE id = %s """
print "updating report: %s" % title
dbhelper.execute_non_query(self.env.get_db_cnx(), sql,
report["sql"], report_id )
insert_report_version( report_id, new_version )
elif ver < new_version:
sql = """
UPDATE report
SET query=%s
WHERE id = %s """
print "updating report to new version: %s" % title
dbhelper.execute_non_query(self.env.get_db_cnx(), sql,
report["sql"], report_id )
sql = """
UPDATE report_version
SET version = %s
WHERE report = %s
"""
dbhelper.execute_non_query(self.env.get_db_cnx(), sql, new_version, report_id)
