def createNewByOffice(self, dst_path=None):
"""
Create a new report using LibreOffice Calc.
:param dst_path: Destination report folder path.
"""
try:
src_filename = DEFAULT_REP_TMPL_FILE
new_filename = dlg_func.getTextEntryDlg(self._parent_window,
u'Create new',
u'Enter a file name for the report template')
if os.path.splitext(new_filename)[1] != '.ods':
new_filename += '.ods'
if dst_path is None:
# It is necessary to determine the resulting path
dst_path = dlg_func.getDirDlg(self._parent_window,
u'Report folder')
if not dst_path:
dst_path = os.getcwd()
dst_filename = os.path.join(dst_path, new_filename)
if os.path.exists(dst_filename):
if dlg_func.openAskBox(u'Rewrite existing file?'):
shutil.copyfile(src_filename, dst_filename)
else:
shutil.copyfile(src_filename, dst_filename)
cmd = '%s %s' % (UNIX_OFFICE_OPEN, dst_filename)
log_func.debug(u'Command <%s>' % str_func.toUnicode(cmd))
os.system(cmd)
return True
except:
log_func.fatal(u'Error create new report template by LibreOffice Calc')
def _execCodeBlock(self, cur_func, locals, globals):
"""
Execute code block.
In the code block, new_cell and record objects are available.
If you need to display information, then it must be displayed in the variable value.
For example:
[=value = '-' if record['name']=='My name' else ''=]
:param cur_func: Code block text.
:param locals: Local name space.
:param globals: Global name space.
:return: The calculated value as a string or an empty string in case of an error.
"""
value = u''
exec_func = cur_func[2:-2].strip()
try:
exec(exec_func, globals, locals)
# When the code block is executed, the value of the variable is located in the locals namespace.
# Therefore, after executing the code block, it is necessary
# to return the variable back to the current function
value = locals.get('value', u'')
log_func.debug(u'Execute code block <%s>. Value [%s]' % (exec_func, value))
except:
log_func.fatal(u'Error code block execute <%s>' % str_func.toUnicode(exec_func))
return str(value)
def _genHeader(self, header):
"""
Generate report header.
:param header: Header band.
:return: True/False.
"""
try:
# log_func.debug(u'Generate header')
# We will add to the end of the report, therefore, determine the maximum line
max_row = len(self._Rep['sheet'])
i_row = 0
cur_height = 0
for row in range(header['row'], header['row'] + header['row_size']):
for col in range(header['col'], header['col'] + header['col_size']):
if self._TemplateSheet[row][col]:
self._genCell(self._TemplateSheet, row, col,
self._Rep, max_row+i_row, col, self._CurRec)
cur_height = self._TemplateSheet[row][col]['height']
i_row += 1
# Current coordinate Y
self._cur_top += cur_height
self._Rep['header'] = {'row': max_row,
'col': header['col'],
'row_size': i_row,
'col_size': header['col_size'],
}
# Clear sums
self._TemplateSheet = self._clearSum(self._TemplateSheet, 0, len(self._TemplateSheet))
return True
except:
log_func.fatal(u'Error report header generate <%s>.' % str_func.toUnicode(self._RepName))
return False
def _getFieldValue(self, cur_func, locals, globals):
"""
Get field value of current record.
:param cur_func: Field name.
:param locals: Local name space.
:param globals: Global name space.
:return: Value as string.
"""
value = u''
field_name = str((cur_func[2:-2]))
record = locals['record'] if 'record' in locals else globals.get('record', dict())
try:
value = record[field_name]
except KeyError:
log_func.warning(u'In record (%s) field <%s> not found' % (str_func.toUnicode(record),
str_func.toUnicode(field_name)))
return value
def write(self, rep_filename, rec_data):
"""
Save the completed report to a file.
:param rep_filename: Report filename.
:param rec_data: Report data.
:return: Created xml filename or None if error.
"""
xml_file = None
if not rep_filename:
log_func.warning(u'Not define report file')
return None
try:
rep_dirname = os.path.dirname(rep_filename)
if not os.path.exists(rep_dirname):
file_func.createDir(rep_dirname)
xml_file = open(rep_filename, 'wt')
xml_gen = iqXMLSSGenerator(xml_file)
xml_gen.startDocument()
xml_gen.startBook()
# Page setup
# xml_gen.savePageSetup(rep_name,report)
# Styles
xml_gen.scanStyles(rec_data['sheet'])
xml_gen.saveStyles()
# Data
xml_gen.startSheet(rec_data['name'], rec_data)
xml_gen.saveColumns(rec_data['sheet'])
for i_row in range(len(rec_data['sheet'])):
xml_gen.startRow(rec_data['sheet'][i_row])
# Reset cell index
xml_gen.cell_idx = 1
for i_col in range(len(rec_data['sheet'][i_row])):
cell = rec_data['sheet'][i_row][i_col]
xml_gen.saveCell(i_row + 1, i_col + 1, cell,
rec_data['sheet'])
xml_gen.endRow()
xml_gen.endSheet(rec_data)
xml_gen.endBook()
xml_gen.endDocument()
xml_file.close()
return rep_filename
except:
if xml_file:
xml_file.close()
log_func.fatal(u'Error report write <%s>' %
str_func.toUnicode(rep_filename))
return None
def generate(self,
report=None,
db_url=None,
sql=None,
stylelib=None,
vars=None,
*args,
**kwargs):
"""
Generate report.
:param report: Report template data.
:param db_url: Connection string as url.
For example:
postgresql+psycopg2://postgres:[email protected]:5432/realization.
:param sql: SQL query.
:param stylelib: Style library.
:param vars: Report variables dictionary.
:return: Generated report or None if error.
"""
try:
if report is not None:
self._report_template = report
if stylelib:
self._report_template['style_lib'] = stylelib
if vars:
self._report_template['variables'] = vars
# 1. Get query table
_kwargs = copy.deepcopy(kwargs)
_kwargs.update(
dict(db_url=db_url, sql=sql, stylelib=stylelib,
variables=vars))
query_tbl = self.getQueryTbl(self._report_template, **_kwargs)
if self._isEmptyQueryTbl(query_tbl):
dlg_func.openWarningBox(u'WARNING',
u'No report data\nQuery <%s>' %
self._report_template['query'],
parent=self._parent_window)
return None
# 2. Generate
rep = report_generator.iqReportGenerator()
data_rep = rep.generate(self._report_template,
query_tbl,
name_space=vars,
*args,
**kwargs)
return data_rep
except:
log_func.fatal(u'Error generate report <%s>.' %
str_func.toUnicode(self._report_template['name']))
return None
def save(self, report_data=None, to_virtual_spreadsheet=True):
"""
Save generated report to file.
:param report_data: Generated report data.
:param to_virtual_spreadsheet: Save by Virtual SpreadSheet?
True - yes,
False - Save by UNOCONV convertation.
When converting using UNOCONV, the cells are not dimensioned.
Cell sizes remain by default.
UNOCONV does not translate all cell styles and attributes.
:return: Destination report filename or None if error.
"""
if report_data:
rep_file = report_file.iqXMLSpreadSheetReportFile()
save_dir = self.getProfileDir()
if not save_dir:
save_dir = report_gen_system.DEFAULT_REPORT_DIR
xml_rep_file_name = os.path.join(
save_dir, '%s_report_result.xml' % report_data['name'])
rep_file_name = os.path.join(
save_dir, '%s_report_result.ods' % report_data['name'])
rep_file.write(xml_rep_file_name, report_data)
if to_virtual_spreadsheet:
log_func.info(u'Convert report <%s> to file <%s>' %
(str_func.toUnicode(xml_rep_file_name),
str_func.toUnicode(rep_file_name)))
spreadsheet = v_spreadsheet.iqVSpreadsheet()
spreadsheet.load(xml_rep_file_name)
spreadsheet.saveAs(rep_file_name)
else:
cmd = 'unoconv --format=ods %s' % xml_rep_file_name
log_func.info(u'UNOCONV. Convert report <%s> to file <%s>' %
(str_func.toUnicode(xml_rep_file_name),
str_func.toUnicode(rep_file_name)))
log_func.info(u'Execute command <%s>' % cmd)
os.system(cmd)
return rep_file_name
return None
def setReportNameTitle(self, report_name):
"""
Set the name of the report in the title of the dialog box.
:param report_name: Report name.
:return: True/False.
"""
if not isinstance(report_name, str):
report_name = str_func.toUnicode(report_name, DEFAULT_UNICODE)
title = _(u'Report:') + ' ' + report_name
self.SetLabel(title)
return True
def getReportDescription(self, report_filename):
"""
Get report description.
:param report_filename: Report template filename.
:return: Report description or base report template file name if description not defined.
"""
res_filename = self.getReportTemplateFilename(report_filename,
self.getReportDir())
report_res = self.loadReportTemplate(res_filename)
description = report_res.get(
'description', u'') if report_res and report_res.get(
'description', None) else report_filename
return str_func.toUnicode(description)
def _getSQLQueryTable(self, report, db_url=None, sql=None):
"""
Get query table.
:param report: Report template data.
:param db_url: Connection string as url.
For example:
postgresql+psycopg2://postgres:[email protected]:5432/realization.
:param sql: SQL query text.
:return: Query table dictionary:
{'__fields__': field_name_list, '__data__': table_data}
"""
result = None
db_connection = None
try:
if not db_url:
data_source = report['data_source']
if not data_source:
log_func.warning(u'Report data source not defined')
return {'__fields__': list(), '__data__': list()}
signature = data_source[:4].upper()
if signature != DB_URL_SIGNATURE:
log_func.warning('Not support DB type <%s>' % signature)
return result
# DB is set using standard DB URL
db_url = data_source[4:].lower().strip()
log_func.info(u'DB URL <%s>' % db_url)
db_connection = sqlalchemy.create_engine(db_url)
log_func.info(u'SQL <%s>' % str_func.toUnicode(sql, 'utf-8'))
sql_result = db_connection.execute(sql)
rows = sql_result.fetchall()
cols = rows[0].keys() if rows else []
db_connection.dispose()
db_connection = None
result = {'__fields__': cols, '__data__': list(rows)}
return result
except:
if db_connection:
db_connection.dispose()
log_func.fatal(u'Error defining SQL query table <%s>.' % sql)
return None
def createReportResourceFile(template_filename):
"""
Create a resource file for the template by the name of the requested.
:param template_filename: The name of the requested template file.
:return: Corrected name of created template file or None in case of error.
"""
dir_name = os.path.dirname(template_filename)
base_filename = os.path.basename(template_filename).replace(' ', '_')
base_filename = str_func.rus2lat(base_filename) if str_func.isRUSText(
base_filename) else base_filename
norm_tmpl_filename = os.path.join(dir_name, base_filename)
log_func.info(u'Create new template file <%s>' % norm_tmpl_filename)
# We consistently check which file can be taken as the basis for the template
for ext in report_gen_func.SRC_REPORT_EXT:
src_filename = os.path.splitext(template_filename)[0] + ext
unicode_src_filename = str_func.toUnicode(src_filename)
if os.path.exists(src_filename):
# Yes, there is such a file and it can act as a source for the template
log_func.info(u'Report template source found <%s>' %
unicode_src_filename)
try:
rep_generator = report_gen_func.createReportGeneratorSystem(
ext)
return rep_generator.update(src_filename)
except:
log_func.fatal(
u'Error converting report template <%s> -> <%s>' %
(unicode_src_filename, norm_tmpl_filename))
return None
log_func.warning(
u'Report template sources not found in folder <%s> for <%s>' %
(dir_name, str_func.toUnicode(os.path.basename(template_filename))))
return None
def _genTxt(self, cell, record=None, cell_row=None, cell_col=None):
"""
Generate text.
:param cell: Cell.
:param record: Current record data.
Format:
{ <field name> : <value>, ...}
:param cell_row: Cell row number.
:param cell_col: Cell column number.
:return: Generated text value or None if error.
"""
value = u''
try:
cell_val = cell['value']
if cell_val is not None and not isinstance(cell_val, str):
cell_val = str(cell_val)
if cell_val not in self._cellFmt:
parsed_fmt = self.parseFuncText(cell_val)
self._cellFmt[cell_val] = parsed_fmt
else:
parsed_fmt = self._cellFmt[cell_val]
func_str = list() # Result value list
i_sum = 0
for cur_func in parsed_fmt['func']:
# Function
if re.search(REP_FUNC_PATT, cur_func):
value = self._execFunction(cur_func, locals(), globals())
# Expression
elif re.search(REP_EXP_PATT, cur_func):
value = self._execExpression(cur_func, locals(), globals())
# Lambda
elif re.search(REP_LAMBDA_PATT, cur_func):
value = self._execLambda(cur_func, locals(), globals())
# Variable
elif re.search(REP_VAR_PATT, cur_func):
value = self._getVariable(cur_func, locals(), globals())
# Code block
elif re.search(REP_EXEC_PATT, cur_func):
value = self._execCodeBlock(cur_func, locals(), globals())
# System function
elif re.search(REP_SYS_PATT, cur_func):
# Sum function
if cur_func[2:6].lower() == 'sum(':
value = str(cell['sum'][i_sum]['value'])
i_sum += 1 # Next sum
# Average calculation function
elif cur_func[2:6].lower() == 'avg(':
if 'sys_num_rec_idx' not in record:
record['sys_num_rec_idx'] = 0
value = str(cell['sum'][i_sum]['value'] / (record['sys_num_rec_idx'] + 1))
i_sum += 1 # Next sum
elif cur_func[2:-2].lower() == 'n':
if 'sys_num_rec_idx' not in record:
record['sys_num_rec_idx'] = 0
sys_num_rec = record['sys_num_rec_idx']
value = str(sys_num_rec + 1)
else:
log_func.warning(u'Unknown system function <%s> in <%s>' % (str_func.toUnicode(cur_func),
self._RepName))
value = ''
# Style
elif re.search(REP_STYLE_PATT, cur_func):
value = self._setStyle(cur_func, locals(), globals())
# Field
elif re.search(REP_FIELD_PATT, cur_func):
value = self._getFieldValue(cur_func, locals(), globals())
# Sub report
elif re.search(REP_SUBREPORT_PATT, cur_func):
value = self._genSubReportBlock(cur_func, locals(), globals())
else:
log_func.warning(u'Unsupported function <%s>' % str(cur_func))
# The cell value may also contain control codes
value = self._genTxt({'value': value}, record)
func_str.append(value)
return self._valueFormat(parsed_fmt['fmt'], func_str)
except:
log_func.fatal(u'Error cell text generate <%s> in <%s>.' % (str_func.toUnicode(cell['value']),
self._RepName))
return None
def generate(self, rep_template, query_table, name_space=None, coord_fill=None):
"""
Generate report.
:param rep_template: Report template data.
:param query_table: Query table:
{
'__name__': query table name,
'__fields__': [field names],
'__data__': [query table data],
'__sub__': {sub report data},
}.
:param name_space: Report name space.
{
'variable name': variable value,
}.
This dictionary can be transmitted in the query table key __variables__.
:param coord_fill: Coordinate filling in cell values.
Format:
{
(row, col): 'value',
}.
This dictionary can be transmitted in the query table key __coord_fill__.
:return: Generated report data.
"""
try:
# Coordinate filling in cell values
self._CoordFill = coord_fill
if query_table and '__coord_fill__' in query_table:
if self._CoordFill is None:
self._CoordFill = dict()
self._CoordFill.update(query_table['__coord_fill__'])
# Group list
self._RepGrp = list()
# I. Define all bands in the template and amount cells
if isinstance(rep_template, dict):
self._Template = rep_template
else:
log_func.warning(u'Error report template type <%s>.' % type(rep_template))
return None
# Init report name
if 'name' in query_table and query_table['name']:
# If the query table is named, then this is the name of the finished report
self._RepName = str(query_table['name'])
elif 'name' in self._Template:
self._RepName = self._Template['name']
# Init name space
self._NameSpace = name_space
if self._NameSpace is None:
self._NameSpace = dict()
self._NameSpace.update(self._Template['variables'])
if query_table and '__variables__' in query_table:
self._NameSpace.update(query_table['__variables__'])
if self._NameSpace:
log_func.debug(u'Report variables: %s' % str(list(self._NameSpace.keys())))
# Style library
self._StyleLib = None
if 'style_lib' in self._Template:
self._StyleLib = self._Template['style_lib']
self._TemplateSheet = self._Template['sheet']
self._TemplateSheet = self._initSumCells(self._TemplateSheet)
# II. Init query table
self._QueryTbl = query_table
# Determine the number of records in the query table
self._QueryTblRecCount = 0
if self._QueryTbl and '__data__' in self._QueryTbl:
self._QueryTblRecCount = len(self._QueryTbl['__data__'])
# Init group band
for grp in self._Template['groups']:
grp['old_rec'] = None
time_start = time.time()
log_func.info(u'Report <%s>. Generate start' % str_func.toUnicode(self._RepName))
# III. Fill report
# Create report
self._Rep = copy.deepcopy(REPORT_TEMPLATE)
self._Rep['name'] = self._RepName
# Init variables
field_idx = dict() # Field indexes
i = 0
i_rec = 0
# Iterate through the fields of a query table
if self._QueryTbl and '__fields__' in self._QueryTbl:
for cur_field in self._QueryTbl['__fields__']:
field_idx[cur_field] = i
i += 1
if self._QueryTblRecCount:
# Init current record
rec = self._QueryTbl['__data__'][i_rec]
for field_name in field_idx.keys():
val = rec[field_idx[field_name]]
self._CurRec[field_name] = val
# Current record index
self._CurRec['sys_num_rec_idx'] = i_rec
# Upper
if self._Template['upper']:
self._genUpper(self._Template['upper'])
# Header
self._genHeader(self._Template['header'])
# Main loop
while i_rec < self._QueryTblRecCount:
# Group
# Check group change and find the index of the most common change group
i_grp_out = -1
# Start generate flag
start_gen = False
for i_grp in range(len(self._Template['groups'])):
grp = self._Template['groups'][i_grp]
if grp['old_rec']:
# Check group note output condition
if self._CurRec[grp['field']] != grp['old_rec'][grp['field']]:
i_grp_out = i_grp
break
else:
i_grp_out = 0
start_gen = True
break
if i_grp_out != -1:
# Display notes
if start_gen is False:
for i_grp in range(len(self._Template['groups'])-1, i_grp_out-1, -1):
grp = self._Template['groups'][i_grp]
self._genGrpFooter(grp)
# Show headers
for i_grp in range(i_grp_out, len(self._Template['groups'])):
grp = self._Template['groups'][i_grp]
grp['old_rec'] = copy.deepcopy(self._CurRec)
self._genGrpHeader(grp)
# Data area
self._genDetail(self._Template['detail'])
# Increase the sum of summing cells
self._sumIterate(self._TemplateSheet, self._CurRec)
# Next record
i_rec += 1
# Set current record
if i_rec < self._QueryTblRecCount:
rec = self._QueryTbl['__data__'][i_rec]
for field_name in field_idx.keys():
val = rec[field_idx[field_name]]
self._CurRec[field_name] = val
# Set current record index
self._CurRec['sys_num_rec_idx'] = i_rec
# Footer
for i_grp in range(len(self._Template['groups'])-1, -1, -1):
grp = self._Template['groups'][i_grp]
if grp['old_rec']:
self._genGrpFooter(grp)
else:
break
self._genFooter(self._Template['footer'])
# Under
if self._Template['under']:
self._genUnder(self._Template['under'])
# Page setup
self._Rep['page_setup'] = self._Template['page_setup']
log_func.info(u'Report <%s>. Generate end. Time: %d sec.' % (str_func.toUnicode(self._RepName),
time.time()-time_start))
return self._Rep
except:
log_func.fatal(u'Error report generate')
return None
