def importxml(db, xmlinput):
"""
Converts the XML to a CSV compatible with the import_from_csv_file of web2py
@ToDo: rewrite this to go via S3Resource for proper Auth checking, Audit.
@todo: deprecate
"""
from s3compat import StringIO
import xml.dom.minidom
try:
doc = xml.dom.minidom.parseString(xmlinput)
except:
raise Exception("XML parse error")
parent = doc.childNodes[0].tagName
csvout = csvheader(parent, doc.childNodes[0].childNodes)
for subnode in doc.childNodes:
csvout = csvout + csvdata(subnode.childNodes)
fh = StringIO()
fh.write(csvout)
fh.seek(0, 0)
db[parent].import_from_csv_file(fh)
def import_xls(uploadFile):
"""
Import Assessment Spreadsheet
"""
from s3compat import StringIO
if series_id is None:
response.error = T("Series details missing")
return
openFile = StringIO()
try:
import xlrd
from xlwt.Utils import cell_to_rowcol2
except ImportError:
current.log.error("ERROR: xlrd & xlwt modules are needed for importing spreadsheets")
return None
workbook = xlrd.open_workbook(file_contents=uploadFile)
try:
sheetR = workbook.sheet_by_name("Assessment")
sheetM = workbook.sheet_by_name("Metadata")
except:
session.error = T("You need to use the spreadsheet which you can download from this page")
redirect(URL(c="survey", f="new_assessment", args=[],
vars={"viewing": "survey_series.%s" % series_id}))
header = ""
body = ""
for row in xrange(1, sheetM.nrows):
header += ',"%s"' % sheetM.cell_value(row, 0)
code = sheetM.cell_value(row, 0)
qstn = s3.survey_getQuestionFromCode(code, series_id)
type = qstn["type"]
count = sheetM.cell_value(row, 1)
if count != "":
count = int(count)
optionList = sheetM.cell_value(row, 2).split("|#|")
else:
count = 1
optionList = None
if type == "Location" and optionList != None:
answerList = {}
elif type == "MultiOption":
answerList = []
else:
answerList = ""
for col in range(count):
cell = sheetM.cell_value(row, 3 + col)
(rowR, colR) = cell_to_rowcol2(cell)
try:
cellValue = sheetR.cell_value(rowR, colR)
except IndexError:
cellValue = ""
# BUG: The option list needs to work in different ways
# depending on the question type. The question type should
# be added to the spreadsheet to save extra db calls:
# * Location save all the data as a hierarchy
# * MultiOption save all selections
# * Option save the last selection
if cellValue != "":
if optionList != None:
if type == "Location":
answerList[optionList[col]]=cellValue
elif type == "MultiOption":
answerList.append(optionList[col])
else:
answerList = optionList[col]
else:
if type == "Date":
try:
(dtYear, dtMonth, dtDay, dtHour, dtMinute, dtSecond) = \
xlrd.xldate_as_tuple(cellValue,
workbook.datemode)
dtValue = datetime.date(dtYear, dtMonth, dtDay)
cellValue = dtValue.isoformat()
except:
pass
elif type == "Time":
try:
time = cellValue
hour = int(time * 24)
minute = int((time * 24 - hour) * 60)
cellValue = "%s:%s" % (hour, minute)
except:
pass
answerList += "%s" % cellValue
body += ',"%s"' % answerList
openFile.write(header)
openFile.write("\n")
openFile.write(body)
openFile.seek(0)
return openFile
def encode(self, resource, **attr):
"""
API Method to encode a resource as cards
@param resource: the S3Resource, or
- the data items as list [{fieldname: representation, ...}, ...], or
- a callable that produces such a list of items
@param attr: additional encoding parameters (see below)
@keyword layout: the layout (a S3PDFCardLayout subclass, overrides
the resource's pdf_card_layout setting
@keyword orderby: orderby-expression for data extraction, overrides
the resource's orderby setting
@keyword labels: the labels for the fields,
- a dict {colname: label}, or
- a callable that produces it,
- defaults to the labels of the extracted fields
@keyword pagesize: the PDF page size,
- a string "A4" or "Letter", or
- a tuple (width, height), in points
- defaults to the layout's card size
@keyword margins: the page margins,
- a tuple (N, E, S, W), in points, or
- a single number, in points
- will be computed if omitted
@keyword spacing: the spacing between cards,
- a tuple (H, V), in points, or
- a single number, in points
- defaults to 18 points in both directions
@keyword title: the document title,
- defaults to title_list crud string of the resource
@return: a handle to the output
"""
if not REPORTLAB:
# FIXME is this the correct handling of a dependency failure?
raise HTTP(503, "Python ReportLab library not installed")
# Do we operate on a S3Resource?
is_resource = isinstance(resource, S3Resource)
# The card layout
layout = attr.get("layout")
if layout is None and is_resource:
layout = resource.get_config("pdf_card_layout")
if layout is None:
layout = S3PDFCardLayout
# Card (and hence page) orientation
orientation = layout.orientation
if orientation == "Landscape":
orientation = landscape
else:
orientation = portrait
# Card and page size
cardsize = orientation(layout.cardsize)
pagesize = attr.get("pagesize")
if pagesize == "A4":
pagesize = A4
elif pagesize == "Letter":
pagesize = LETTER
elif not isinstance(pagesize, (tuple, list)):
pagesize = cardsize
pagesize = orientation(pagesize)
# Extract the data
if is_resource:
# Extract the data items from the resource
fields = layout.fields(resource)
data = self.extract(resource, fields, orderby=attr.get("orderby"))
items = data.rows
elif callable(resource):
# External getter => call with resource, returns the data items
data = None
items = resource()
else:
# The data items have been passed-in in place of the resource
data = None
items = resource
# Get the labels
labels = attr.get("labels")
if callable(labels):
labels = labels(resource)
elif not isinstance(labels, dict):
if data and hasattr(data, "rfields"):
# Collect the labels from rfields
rfields = data.rfields
labels = {rfield.colname: rfield.label for rfield in rfields}
else:
labels = {}
# Document title
title = attr.get("title")
if not title and is_resource:
crud_strings = current.response.s3.crud_strings[resource.tablename]
if crud_strings:
title = crud_strings["title_list"]
# Instantiate the doc template
doc = S3PDFCardTemplate(
pagesize,
cardsize,
margins=attr.get("margins"),
spacing=attr.get("spacing"),
title=title,
)
# Produce the flowables
flowables = self.get_flowables(
layout,
resource,
items,
labels=labels,
cards_per_page=doc.cards_per_page,
)
# Build the doc
output_stream = StringIO()
doc.build(
flowables,
output_stream,
#canvasmaker=canvas.Canvas, # is default
)
output_stream.seek(0)
return output_stream
def xls(self, r, **attr):
"""
Export the performance indicators as XLS data sheet
@param r: the S3Request instance
@param attr: controller attributes
"""
T = current.T
s3db = current.s3db
try:
import xlwt
except ImportError:
raise HTTP(503, body="XLWT not installed")
title = s3_str(T("Performance Indicators"))
write = self.write
# Create workbook and sheet
book = xlwt.Workbook(encoding="utf-8")
sheet = book.add_sheet(title)
# Get the statistics
resource = self.resource
table = resource.table
indicators = self.indicators(resource)
# Title and Report Dates (from filter)
write(sheet, 0, 0, title, style="header")
dates = []
get_vars = r.get_vars
field = table.date
for fvar in ("~.date__ge", "~.date__le"):
dtstr = get_vars.get(fvar)
if dtstr:
try:
dt = s3_decode_iso_datetime(dtstr).date()
except (ValueError, AttributeError):
dt = None
else:
dates.append(field.represent(dt))
else:
dates.append("...")
if dates:
write(sheet, 1, 0, " -- ".join(dates))
# Basic performance indicators
rowindex = 3
# Total number of consultations
write(sheet, rowindex, 0, T("Total Number of Consultations"))
write(sheet, rowindex, 1, indicators.get("total_responses", ""))
rowindex += 1
write(sheet, rowindex, 0, T("Total Number of Clients"))
write(sheet, rowindex, 1, indicators.get("total_clients", ""))
rowindex += 1
write(sheet, rowindex, 0, T("Average Duration of Consultations (minutes)"))
avg_hours_per_response = indicators.get("avg_hours_per_response")
if avg_hours_per_response:
avg_minutes_per_response = int(round(avg_hours_per_response * 60))
else:
avg_minutes_per_response = ""
write(sheet, rowindex, 1, avg_minutes_per_response)
rowindex += 1
write(sheet, rowindex, 0, T("Average Number of Consultations per Client"))
write(sheet, rowindex, 1, indicators.get("avg_responses_per_client", ""))
# Distribution
rowindex = 8
write(sheet, rowindex, 0, T("Distribution of Clients"))
write(sheet, rowindex, 1, T("Single"))
write(sheet, rowindex, 2, indicators.get("singles", ""))
rowindex += 1
write(sheet, rowindex, 1, T("Family"))
write(sheet, rowindex, 2, indicators.get("families", ""))
rowindex += 1
write(sheet, rowindex, 1, T("Group Counseling"))
rowindex += 1
write(sheet, rowindex, 1, T("Individual Counseling"))
write(sheet, rowindex, 2, indicators.get("total_responses", ""))
# Top-5's
rowindex = 13
write(sheet, rowindex, 0, T("Top 5 Countries of Origin"))
top_5_nationalities = indicators.get("top_5_nationalities")
if top_5_nationalities:
dtable = s3db.pr_person_details
field = dtable.nationality
for rank, nationality in enumerate(top_5_nationalities):
write(sheet, rowindex, 1, "%s - %s" % (rank + 1, field.represent(nationality)))
rowindex += 1
rowindex += 1
write(sheet, rowindex, 0, T("Top 5 Counseling Reasons"))
top_5_needs = indicators.get("top_5_needs")
if top_5_needs:
ttable = s3db.dvr_response_theme
field = ttable.need_id
for rank, need in enumerate(top_5_needs):
write(sheet, rowindex, 1, "%s - %s" % (rank + 1, field.represent(need)))
rowindex += 1
# Output
output = StringIO()
book.save(output)
output.seek(0)
# Response headers
from gluon.contenttype import contenttype
disposition = "attachment; filename=\"%s\"" % "indicators.xls"
response = current.response
response.headers["Content-Type"] = contenttype(".xls")
response.headers["Content-disposition"] = disposition
from gluon.streamer import DEFAULT_CHUNK_SIZE
return response.stream(output,
chunk_size=DEFAULT_CHUNK_SIZE,
request=r,
)
def item_export_pdf():
"""
Export a list of Items in Adobe PDF format
Uses Geraldo Grouping Report
@ToDo: Use S3PDF Method
"""
try:
from reportlab.lib.units import cm
from reportlab.lib.pagesizes import A4
from reportlab.lib.enums import TA_CENTER, TA_RIGHT
except ImportError:
session.error = "Python needs the ReportLab module installed for PDF export"
redirect(URL(c="item"))
try:
from geraldo import Report, ReportBand, ReportGroup, Label, ObjectValue, SystemField, landscape, BAND_WIDTH
from geraldo.generators import PDFGenerator
except ImportError:
session.error = "Python needs the Geraldo module installed for PDF export"
redirect(URL(c="item"))
table = db.budget_item
objects_list = db(table.id > 0).select(orderby=table.category_type)
if not objects_list:
session.warning = T("No data in this table - cannot create PDF!")
redirect(URL(f="item"))
from s3compat import StringIO
output = StringIO()
class MyReport(Report):
def __init__(self, queryset=None, T=None):
" Initialise parent class & make any necessary modifications "
Report.__init__(self, queryset)
self.T = T
def _T(self, rawstring):
return self.T(rawstring)
# can't use T() here!
#title = _T("Items")
title = "Items"
page_size = landscape(A4)
class band_page_header(ReportBand):
height = 1.3 * cm
elements = [
SystemField(expression="%(report_title)s",
top=0.1 * cm,
left=0,
width=BAND_WIDTH,
style={
"fontName": "Helvetica-Bold",
"fontSize": 14,
"alignment": TA_CENTER
}),
Label(text="Code", top=0.8 * cm, left=0.2 * cm),
Label(text="Description", top=0.8 * cm, left=3 * cm),
Label(text="Unit Cost", top=0.8 * cm, left=13 * cm),
Label(text="per Month", top=0.8 * cm, left=15 * cm),
Label(text="per Minute", top=0.8 * cm, left=17 * cm),
Label(text="per Megabyte", top=0.8 * cm, left=19 * cm),
Label(text="Comments", top=0.8 * cm, left=21 * cm),
]
borders = {"bottom": True}
class band_page_footer(ReportBand):
height = 0.5 * cm
elements = [
Label(text="%s" % request.utcnow.date(), top=0.1 * cm, left=0),
SystemField(
expression="Page # %(page_number)d of %(page_count)d",
top=0.1 * cm,
width=BAND_WIDTH,
style={"alignment": TA_RIGHT}),
]
borders = {"top": True}
class band_detail(ReportBand):
height = 0.5 * cm
auto_expand_height = True
elements = (
ObjectValue(attribute_name="code",
left=0.2 * cm,
width=2.8 * cm),
ObjectValue(attribute_name="description",
left=3 * cm,
width=10 * cm),
ObjectValue(attribute_name="unit_cost",
left=13 * cm,
width=2 * cm),
ObjectValue(attribute_name="monthly_cost",
left=15 * cm,
width=2 * cm),
ObjectValue(attribute_name="minute_cost",
left=17 * cm,
width=2 * cm),
ObjectValue(attribute_name="megabyte_cost",
left=19 * cm,
width=2 * cm),
ObjectValue(attribute_name="comments",
left=21 * cm,
width=6 * cm),
)
groups = [
ReportGroup(
attribute_name="category_type",
band_header=ReportBand(
height=0.7 * cm,
elements=[
ObjectValue(
attribute_name="category_type",
left=0,
top=0.1 * cm,
get_value=lambda instance: instance.category_type
and budget_category_type_opts[instance.
category_type],
style={
"fontName": "Helvetica-Bold",
"fontSize": 12
})
],
borders={"bottom": True},
),
),
]
#report = MyReport(queryset=objects_list)
report = MyReport(queryset=objects_list, T=T)
report.generate_by(PDFGenerator, filename=output)
output.seek(0)
import gluon.contenttype
response.headers["Content-Type"] = gluon.contenttype.contenttype(".pdf")
filename = "%s_items.pdf" % (request.env.server_name)
response.headers[
"Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def kit_export_pdf():
"""
Export a list of Kits in Adobe PDF format
Uses Geraldo SubReport
@ToDo: Use S3PDF Method
"""
try:
from reportlab.lib.units import cm
from reportlab.lib.pagesizes import A4
from reportlab.lib.enums import TA_CENTER, TA_RIGHT
except ImportError:
session.error = "Python needs the ReportLab module installed for PDF export"
redirect(URL(c="kit"))
try:
from geraldo import Report, ReportBand, SubReport, Label, ObjectValue, SystemField, landscape, BAND_WIDTH
from geraldo.generators import PDFGenerator
except ImportError:
session.error = "Python needs the Geraldo module installed for PDF export"
redirect(URL(c="kit"))
table = db.budget_kit
objects_list = db(table.id > 0).select()
if not objects_list:
session.warning = T("No data in this table - cannot create PDF!")
redirect(URL(r=request))
from s3compat import StringIO
output = StringIO()
#class MySubReport(SubReport):
# def __init__(self, db=None, **kwargs):
# " Initialise parent class & make any necessary modifications "
# self.db = db
# SubReport.__init__(self, **kwargs)
class MyReport(Report):
def __init__(self, queryset=None, db=None):
" Initialise parent class & make any necessary modifications "
Report.__init__(self, queryset)
self.db = db
# can't use T() here!
title = "Kits"
page_size = landscape(A4)
class band_page_header(ReportBand):
height = 1.3 * cm
elements = [
SystemField(expression="%(report_title)s",
top=0.1 * cm,
left=0,
width=BAND_WIDTH,
style={
"fontName": "Helvetica-Bold",
"fontSize": 14,
"alignment": TA_CENTER
}),
Label(text="Code", top=0.8 * cm, left=0.2 * cm),
Label(text="Description", top=0.8 * cm, left=2 * cm),
Label(text="Cost", top=0.8 * cm, left=10 * cm),
Label(text="Monthly", top=0.8 * cm, left=12 * cm),
Label(text="per Minute", top=0.8 * cm, left=14 * cm),
Label(text="per Megabyte", top=0.8 * cm, left=16 * cm),
Label(text="Comments", top=0.8 * cm, left=18 * cm),
]
borders = {"bottom": True}
class band_page_footer(ReportBand):
height = 0.5 * cm
elements = [
Label(text="%s" % request.utcnow.date(), top=0.1 * cm, left=0),
SystemField(
expression="Page # %(page_number)d of %(page_count)d",
top=0.1 * cm,
width=BAND_WIDTH,
style={"alignment": TA_RIGHT}),
]
borders = {"top": True}
class band_detail(ReportBand):
height = 0.5 * cm
auto_expand_height = True
elements = (
ObjectValue(attribute_name="code",
left=0.2 * cm,
width=1.8 * cm),
ObjectValue(attribute_name="description",
left=2 * cm,
width=8 * cm),
ObjectValue(attribute_name="total_unit_cost",
left=10 * cm,
width=2 * cm),
ObjectValue(attribute_name="total_monthly_cost",
left=12 * cm,
width=2 * cm),
ObjectValue(attribute_name="total_minute_cost",
left=14 * cm,
width=2 * cm),
ObjectValue(attribute_name="total_megabyte_cost",
left=16 * cm,
width=2 * cm),
ObjectValue(attribute_name="comments",
left=18 * cm,
width=6 * cm),
)
subreports = [
SubReport(
#queryset_string = "db((db.budget_kit_item.kit_id == %(object)s.id) & (db.budget_item.id == db.budget_kit_item.item_id)).select(db.budget_item.code, db.budget_item.description, db.budget_item.unit_cost)",
#queryset_string = "db(db.budget_kit_item.kit_id == %(object)s.id).select()",
band_header=ReportBand(
height=0.5 * cm,
elements=[
Label(text="Item ID",
top=0,
left=0.2 * cm,
style={"fontName": "Helvetica-Bold"}),
Label(text="Quantity",
top=0,
left=2 * cm,
style={"fontName": "Helvetica-Bold"}),
#Label(text="Unit Cost", top=0, left=4*cm, style={"fontName": "Helvetica-Bold"}),
],
borders={
"top": True,
"left": True,
"right": True
},
),
detail_band=ReportBand(
height=0.5 * cm,
elements=[
ObjectValue(attribute_name="item_id",
top=0,
left=0.2 * cm),
ObjectValue(attribute_name="quantity",
top=0,
left=2 * cm),
#ObjectValue(attribute_name="unit_cost", top=0, left=4*cm),
]),
),
]
#report = MyReport(queryset=objects_list)
report = MyReport(queryset=objects_list, db=db)
report.generate_by(PDFGenerator, filename=output)
output.seek(0)
import gluon.contenttype
response.headers["Content-Type"] = gluon.contenttype.contenttype(".pdf")
filename = "%s_kits.pdf" % (request.env.server_name)
response.headers[
"Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def kit_export_xls():
"""
Export a list of Kits in Excel XLS format
Sheet 1 is a list of Kits
Then there is a separate sheet per kit, listing it's component items
"""
try:
import xlwt
except ImportError:
session.error = "xlwt module not available within the running Python - this needs installing for XLS output!"
redirect(URL(c="kit"))
from s3compat import StringIO
output = StringIO()
book = xlwt.Workbook()
# List of Kits
sheet1 = book.add_sheet("Kits")
# Header row for Kits sheet
row0 = sheet1.row(0)
cell = 0
table = db.budget_kit
kits = db(table.id > 0).select()
fields = [table[f] for f in table.fields if table[f].readable]
for field in fields:
row0.write(cell, field.label, xlwt.easyxf("font: bold True;"))
cell += 1
# For Header row on Items sheets
table = db.budget_item
fields_items = [table[f] for f in table.fields if table[f].readable]
row = 1
for kit in kits:
# The Kit details on Sheet 1
rowx = sheet1.row(row)
row += 1
cell1 = 0
for field in fields:
tab, col = str(field).split(".")
rowx.write(cell1, kit[col])
cell1 += 1
# Sheet per Kit detailing constituent Items
# Replace characters which are illegal in sheetnames
sheetname = kit.code.replace("/", "_")
sheet = book.add_sheet(sheetname)
# Header row for Items sheet
row0 = sheet.row(0)
cell = 0
for field_item in fields_items:
row0.write(cell, field_item.label, xlwt.easyxf("font: bold True;"))
cell += 1
# List Items in each Kit
table = db.budget_kit_item
contents = db(table.kit_id == kit.id).select()
rowy = 1
for content in contents:
table = db.budget_item
item = db(table.id == content.item_id).select().first()
rowx = sheet.row(rowy)
rowy += 1
cell = 0
for field_item in fields_items:
tab, col = str(field_item).split(".")
# Do lookups for option fields
if col == "cost_type":
opt = item[col]
value = str(budget_cost_type_opts[opt])
elif col == "category_type":
opt = item[col]
value = str(budget_category_type_opts[opt])
else:
value = item[col]
rowx.write(cell, value)
cell += 1
book.save(output)
output.seek(0)
import gluon.contenttype
response.headers["Content-Type"] = gluon.contenttype.contenttype(".xls")
filename = "%s_kits.xls" % (request.env.server_name)
response.headers[
"Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
