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