def encode_pt(pt, title):
"""
Encode a S3PivotTable as XLS sheet
@param pt: the S3PivotTable
@param title: the title for the report
@returns: the XLS file as stream
"""
output = BytesIO()
book = S3PivotTableXLS(pt).encode(title)
book.save(output)
output.seek(0)
return output
def encode(self, resource, **attr):
"""
Export data as a Microsoft Excel spreadsheet
@param resource: the source of the data that is to be encoded
as a spreadsheet, can be either of:
1) an S3Resource
2) an array of value dicts (dict of
column labels as first item, list of
field types as second item)
3) a dict like:
{columns: [key, ...],
headers: {key: label},
types: {key: type},
rows: [{key:value}],
}
@param attr: keyword arguments (see below)
@keyword as_stream: return the buffer (BytesIO) rather than
its contents (str), useful when the output
is supposed to be stored locally
@keyword title: the main title of the report
@keyword list_fields: fields to include in list views
@keyword report_groupby: used to create a grouping of the result:
either a Field object of the resource
or a string which matches a value in
the heading
@keyword use_colour: True to add colour to the cells, default False
@keyword evenodd: render different background colours
for even/odd rows ("stripes")
"""
# Do not redirect from here!
# ...but raise proper status code, which can be caught by caller
try:
import xlwt
except ImportError:
error = self.ERROR.XLWT_ERROR
current.log.error(error)
raise HTTP(503, body=error)
try:
from xlrd.xldate import xldate_from_date_tuple, \
xldate_from_time_tuple, \
xldate_from_datetime_tuple
except ImportError:
error = self.ERROR.XLRD_ERROR
current.log.error(error)
raise HTTP(503, body=error)
import datetime
MAX_CELL_SIZE = self.MAX_CELL_SIZE
COL_WIDTH_MULTIPLIER = self.COL_WIDTH_MULTIPLIER
# Get the attributes
title = attr.get("title")
if title is None:
title = current.T("Report")
list_fields = attr.get("list_fields")
group = attr.get("dt_group")
use_colour = attr.get("use_colour", False)
evenodd = attr.get("evenodd", True)
# Extract the data from the resource
if isinstance(resource, dict):
headers = resource.get("headers", {})
lfields = resource.get("columns", list_fields)
column_types = resource.get("types")
types = [column_types[col] for col in lfields]
rows = resource.get("rows")
elif isinstance(resource, (list, tuple)):
headers = resource[0]
types = resource[1]
rows = resource[2:]
lfields = list_fields
else:
if not list_fields:
list_fields = resource.list_fields()
(title, types, lfields, headers, rows) = self.extract(resource,
list_fields,
)
# Verify columns in items
request = current.request
if len(rows) > 0 and len(lfields) > len(rows[0]):
msg = """modules/s3/codecs/xls: There is an error in the list items, a field doesn't exist
requesting url %s
Headers = %d, Data Items = %d
Headers %s
List Fields %s""" % (request.url, len(lfields), len(rows[0]), headers, lfields)
current.log.error(msg)
# Grouping
report_groupby = lfields[group] if group else None
groupby_label = headers[report_groupby] if report_groupby else None
# Date/Time formats from L10N deployment settings
settings = current.deployment_settings
date_format = settings.get_L10n_date_format()
date_format_str = str(date_format)
dt_format_translate = self.dt_format_translate
date_format = dt_format_translate(date_format)
time_format = dt_format_translate(settings.get_L10n_time_format())
datetime_format = dt_format_translate(settings.get_L10n_datetime_format())
title_row = settings.get_xls_title_row()
# Get styles
styles = self._styles(use_colour = use_colour,
evenodd = evenodd,
datetime_format = datetime_format,
)
# Create the workbook
book = xlwt.Workbook(encoding="utf-8")
# Add sheets
sheets = []
# XLS exports are limited to 65536 rows per sheet, we bypass
# this by creating multiple sheets
row_limit = 65536
sheetnum = len(rows) / row_limit
# Can't have a / in the sheet_name, so replace any with a space
sheet_name = s3_str(title.replace("/", " "))
if len(sheet_name) > 28:
# Sheet name cannot be over 31 chars
# (take sheet number suffix into account)
sheet_name = sheet_name[:28]
count = 1
while len(sheets) <= sheetnum:
sheets.append(book.add_sheet("%s-%s" % (sheet_name, count)))
count += 1
if callable(title_row):
# Calling with sheet None to get the number of title rows
title_row_length = title_row(None)
else:
title_row_length = 2
# Add header row to all sheets, determine columns widths
header_style = styles["header"]
for sheet in sheets:
# Move this down if a title row will be added
if title_row:
header_row = sheet.row(title_row_length)
else:
header_row = sheet.row(0)
column_widths = []
has_id = False
col_index = 0
for selector in lfields:
if selector == report_groupby:
continue
label = headers[selector]
if label == "Id":
# Indicate to adjust col_index when writing out
has_id = True
column_widths.append(0)
col_index += 1
continue
if label == "Sort":
continue
if has_id:
# Adjust for the skipped column
write_col_index = col_index - 1
else:
write_col_index = col_index
header_row.write(write_col_index, str(label), header_style)
width = max(len(label) * COL_WIDTH_MULTIPLIER, 2000)
width = min(width, 65535) # USHRT_MAX
column_widths.append(width)
sheet.col(write_col_index).width = width
col_index += 1
title = s3_str(title)
# Title row (optional, deployment setting)
if title_row:
T = current.T
large_header_style = styles["large_header"]
notes_style = styles["notes"]
for sheet in sheets:
if callable(title_row):
# Custom title rows
title_row(sheet)
else:
# First row => Title (standard = "title_list" CRUD string)
current_row = sheet.row(0)
if col_index > 0:
sheet.write_merge(0, 0, 0, col_index,
title,
large_header_style,
)
current_row.height = 500
# Second row => Export date/time
current_row = sheet.row(1)
current_row.write(0, "%s:" % T("Date Exported"), notes_style)
current_row.write(1, request.now, notes_style)
# Fix the size of the last column to display the date
if 16 * COL_WIDTH_MULTIPLIER > width:
sheet.col(col_index).width = 16 * COL_WIDTH_MULTIPLIER
# Initialize counters
total_cols = col_index
# Move the rows down if a title row is included
if title_row:
row_index = title_row_length
else:
row_index = 0
# Helper function to get the current row
def get_current_row(row_count, row_limit):
sheet_count = int(row_count / row_limit)
row_number = row_count - (sheet_count * row_limit)
if sheet_count > 0:
row_number += 1
return sheets[sheet_count], sheets[sheet_count].row(row_number)
# Write the table contents
subheading = None
odd_style = styles["odd"]
even_style = styles["even"]
subheader_style = styles["subheader"]
for row in rows:
# Current row
row_index += 1
current_sheet, current_row = get_current_row(row_index, row_limit)
style = even_style if row_index % 2 == 0 else odd_style
# Group headers
if report_groupby:
represent = s3_strip_markup(s3_unicode(row[report_groupby]))
if subheading != represent:
# Start of new group - write group header
subheading = represent
current_sheet.write_merge(row_index, row_index, 0, total_cols,
subheading,
subheader_style,
)
# Move on to next row
row_index += 1
current_sheet, current_row = get_current_row(row_index, row_limit)
style = even_style if row_index % 2 == 0 else odd_style
col_index = 0
remaining_fields = lfields
# Custom row style?
row_style = None
if "_style" in row:
stylename = row["_style"]
if stylename in styles:
row_style = styles[stylename]
# Group header/footer row?
if "_group" in row:
group_info = row["_group"]
label = group_info.get("label")
totals = group_info.get("totals")
if label:
label = s3_strip_markup(s3_unicode(label))
style = row_style or subheader_style
span = group_info.get("span")
if span == 0:
current_sheet.write_merge(row_index,
row_index,
0,
total_cols - 1,
label,
style,
)
if totals:
# Write totals into the next row
row_index += 1
current_sheet, current_row = \
get_current_row(row_index, row_limit)
else:
current_sheet.write_merge(row_index,
row_index,
0,
span - 1,
label,
style,
)
col_index = span
remaining_fields = lfields[span:]
if not totals:
continue
for field in remaining_fields:
label = headers[field]
if label == groupby_label:
continue
if label == "Id":
# Skip the ID column from XLS exports
col_index += 1
continue
if field not in row:
represent = ""
else:
represent = s3_strip_markup(s3_unicode(row[field]))
coltype = types[col_index]
if coltype == "sort":
continue
if len(represent) > MAX_CELL_SIZE:
represent = represent[:MAX_CELL_SIZE]
value = represent
if coltype == "date":
try:
cell_datetime = datetime.datetime.strptime(value,
date_format_str)
date_tuple = (cell_datetime.year,
cell_datetime.month,
cell_datetime.day)
value = xldate_from_date_tuple(date_tuple, 0)
style.num_format_str = date_format
except:
pass
elif coltype == "datetime":
try:
cell_datetime = datetime.datetime.strptime(value,
date_format_str)
date_tuple = (cell_datetime.year,
cell_datetime.month,
cell_datetime.day,
cell_datetime.hour,
cell_datetime.minute,
cell_datetime.second)
value = xldate_from_datetime_tuple(date_tuple, 0)
style.num_format_str = datetime_format
except:
pass
elif coltype == "time":
try:
cell_datetime = datetime.datetime.strptime(value,
date_format_str)
date_tuple = (cell_datetime.hour,
cell_datetime.minute,
cell_datetime.second)
value = xldate_from_time_tuple(date_tuple)
style.num_format_str = time_format
except:
pass
elif coltype == "integer":
try:
value = int(value)
style.num_format_str = "0"
except:
pass
elif coltype == "double":
try:
value = float(value)
style.num_format_str = "0.00"
except:
pass
if has_id:
# Adjust for the skipped column
write_col_index = col_index - 1
else:
write_col_index = col_index
current_row.write(write_col_index, value, style)
width = len(represent) * COL_WIDTH_MULTIPLIER
if width > column_widths[col_index]:
column_widths[col_index] = width
current_sheet.col(write_col_index).width = width
col_index += 1
# Additional sheet settings
for sheet in sheets:
sheet.panes_frozen = True
sheet.horz_split_pos = 1
# Write output
output = BytesIO()
book.save(output)
output.seek(0)
if attr.get("as_stream", False):
return output
# Response headers
filename = "%s_%s.xls" % (request.env.server_name, title)
disposition = "attachment; filename=\"%s\"" % filename
response = current.response
response.headers["Content-Type"] = contenttype(".xls")
response.headers["Content-disposition"] = disposition
return output.read()
def kit_export_pdf():
"""
Export a list of Kits in Adobe PDF format
Uses Geraldo SubReport
"""
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 BytesIO
output = BytesIO()
#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 item_export_pdf():
"""
Export a list of Items in Adobe PDF format
Uses Geraldo Grouping Report
"""
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 BytesIO
output = BytesIO()
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_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 BytesIO
output = BytesIO()
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()
def import_xls(uploadFile):
"""
Import Assessment Spreadsheet
"""
from s3compat import BytesIO
if series_id is None:
response.error = T("Series details missing")
return
openFile = BytesIO()
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 range(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 xls(self, r, **attr):
"""
Export the performance indicators as XLS data sheet
@param r: the S3Request instance
@param attr: controller attributes
"""
try:
import xlwt
except ImportError:
raise HTTP(503, body="XLWT not installed")
T = current.T
resource = self.resource
table = resource.table
# Get the statistics
indicators = self.indicators
# Create workbook and sheet
book = xlwt.Workbook(encoding="utf-8")
title = s3_str(T("Performance Indicators"))
sheet = book.add_sheet(title)
# Title and Report Dates (from filter)
dates = []
get_vars = r.get_vars
field = table.date
for fvar in ("~.start_date__ge", "~.end_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("...")
dates = " -- ".join(dates) if dates else None
# Write the performance indicators
indicators.export(resource, sheet, title, subtitle=dates)
# Output
output = BytesIO()
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 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 = BytesIO()
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 = BytesIO()
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,
)