def print_account_totals(self, _xs, xlwt, ws, row_start_account,
row_position, current_account, _p):
cell_format = _xs['bold'] + _xs['fill'] + \
_xs['borders_all'] + _xs['wrap'] + _xs['top']
cell_style = xlwt.easyxf(cell_format)
cell_style_decimal = xlwt.easyxf(
cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
c_specs = [
('acc_title', 2, 0, 'text', current_account.name),
('code', 1, 0, 'text', current_account.code),
]
for column in range(3, 7):
# in case of one single comparison, the column 6 will contain
# percentages
if (_p.comparison_mode == 'single' and column == 6):
total_diff = rowcol_to_cell(row_position, column - 1)
total_balance = rowcol_to_cell(row_position, column - 2)
account_formula = 'Round(' + total_diff + \
'/' + total_balance + '*100;0)'
else:
account_start = rowcol_to_cell(row_start_account, column)
account_end = rowcol_to_cell(row_position - 1, column)
account_formula = 'Round(SUM(' + \
account_start + ':' + account_end + ');2)'
c_specs += [('total%s' % column, 1, 0, 'text', None,
account_formula, None, cell_style_decimal)]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_position = self.xls_write_row(ws, row_position, row_data,
cell_style)
return row_position + 1
def generate_xls_report(self, _p, _xs, data, objects, wb):
#print "generate_xls_report......data......",data,objects , wb
wanted_list = _p.wanted_list
#print "_p._......",_p._
_ = _p._
self.wanted_list = wanted_list
self.col_specs_template_caloptima.update(_p.template_changes)
#_logger.warn("test data %s,%s,%s", wanted_list,data,objects,wb)
amount_pos = 'amount' in wanted_list and wanted_list.index('amount')
if not (amount_pos ) and 'amount' in wanted_list:
raise UserError(_("The 'Amount' field is a calculated XLS field requiring the presence of the 'Amount' field !"))
report_name = "Report Sheet"
ws = wb.add_sheet(report_name[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
# set print header/footer
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# Title
# cell_style = xlwt.easyxf(_xs['xls_title'])
# c_specs = [
# ('report_name', 1, 0, 'text', 'Report for Kaiser'),
# ]
# row_data = self.xls_row_template(c_specs, ['report_name'])
# row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style)
# row_pos += 1
# Column headers
c_specs = map(lambda x: self.render(x, self.col_specs_template_caloptima, 'header', render_space={'_': _p._}), wanted_list)
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=self.rh_cell_style, set_column_size=True)
ws.set_horz_split_pos(row_pos)
aml_start_pos = row_pos
# cnt = 0
for line in objects:
# cnt += 1
amount_cell = rowcol_to_cell(row_pos, amount_pos)
c_specs = map(lambda x: self.render(x, self.col_specs_template_caloptima, 'lines'), wanted_list)
try:
if c_specs and c_specs[34] and c_specs[34][4]:
c_specs[34][4]=jQ(c_specs[34][4]).text()
except:
pass
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=self.aml_cell_style)
amount_start = rowcol_to_cell(aml_start_pos, amount_pos)
amount_stop = rowcol_to_cell(row_pos - 1, amount_pos)
amount_total_formula = 'SUM(%s:%s)' % (amount_start, amount_stop)
amount_cell = rowcol_to_cell(row_pos, amount_pos)
c_specs = map(lambda x: self.render(x, self.col_specs_template_caloptima, 'totals'), wanted_list)
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=self.rt_cell_style_right)
return True
def generate_xls_report(self, _p, _xs, data, objects, wb):
#print "generate_xls_report......data......",data,objects , wb
wanted_list = _p.wanted_list
#print "_p._......",_p._
_ = _p._
self.wanted_list = wanted_list
self.col_specs_template_john_muir.update(_p.template_changes)
amount_pos = 'amount' in wanted_list and wanted_list.index('amount')
if not (amount_pos ) and 'amount' in wanted_list:
raise UserError(_("The 'Amount' field is a calculated XLS field requiring the presence of the 'Amount' field !"))
report_name = "Report Sheet"
ws = wb.add_sheet(report_name[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
# set print header/footer
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# Title
# cell_style = xlwt.easyxf(_xs['xls_title'])
# c_specs = [
# ('report_name', 1, 0, 'text', 'Report for Kaiser'),
# ]
# row_data = self.xls_row_template(c_specs, ['report_name'])
# row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style)
# row_pos += 1
# Column headers
c_specs = map(lambda x: self.render(x, self.col_specs_template_john_muir, 'header', render_space={'_': _p._}), wanted_list)
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=self.rh_cell_style, set_column_size=True)
ws.set_horz_split_pos(row_pos)
aml_start_pos = row_pos
# cnt = 0
for line in objects:
invoice_obj_srch = self.env['account.invoice'].search([('type','=','in_invoice'),('event_id','=',line.invoice_id.event_id.id)])
for each_supp_rec in invoice_obj_srch:
supp_inv_rate = each_supp_rec.invoice_line_ids and each_supp_rec.invoice_line_ids[0].price_unit or 0.0
# cnt += 1
amount_cell = rowcol_to_cell(row_pos, amount_pos)
c_specs = map(lambda x: self.render(x, self.col_specs_template_john_muir, 'lines'), wanted_list)
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=self.aml_cell_style)
amount_start = rowcol_to_cell(aml_start_pos, amount_pos)
amount_stop = rowcol_to_cell(row_pos - 1, amount_pos)
amount_total_formula = 'SUM(%s:%s)' % (amount_start, amount_stop)
amount_cell = rowcol_to_cell(row_pos, amount_pos)
c_specs = map(lambda x: self.render(x, self.col_specs_template_john_muir, 'totals'), wanted_list)
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=self.rt_cell_style_right)
return True
def print_group_cumul_partner(self, row_position, row_start_partner,
account, _p, data):
# the text "Cumulated Balance on Partner starts in column 4 when
# selecting the option regroup by currency, 5 in the other case
start_col = 4
debit_partner_start = rowcol_to_cell(row_start_partner, start_col + 3)
debit_partner_end = rowcol_to_cell(row_position - 1, start_col + 3)
debit_partner_total = 'SUM(' + debit_partner_start + \
':' + debit_partner_end + ')'
credit_partner_start = rowcol_to_cell(row_start_partner, start_col + 4)
credit_partner_end = rowcol_to_cell(row_position - 1, start_col + 4)
credit_partner_total = 'SUM(' + credit_partner_start + \
':' + credit_partner_end + ')'
bal_curr_start = rowcol_to_cell(row_start_partner, start_col + 5)
bal_curr_end = rowcol_to_cell(row_position - 1, start_col + 5)
cumul_balance_curr = 'SUM(' + bal_curr_start + ':' + bal_curr_end + ')'
bal_partner_debit = rowcol_to_cell(row_position, start_col + 3)
bal_partner_credit = rowcol_to_cell(row_position, start_col + 4)
bal_partner_total = bal_partner_debit + '-' + bal_partner_credit
c_specs = [('empty%s' % x, 1, 0, 'text', None)
for x in range(start_col)]
c_specs += [
# , style_bold_italic),
('init_bal', 1, 0, 'text', _('Cumulated Balance on Partner')),
('rec', 1, 0, 'text', None),
('empty5', 1, 0, 'text', None),
('debit', 1, 0, 'number', None, debit_partner_total,
self.style_partner_cumul_decimal),
('credit', 1, 0, 'number', None, credit_partner_total,
self.style_partner_cumul_decimal),
('cumul_bal', 1, 0, 'number', None, bal_partner_total,
self.style_partner_cumul_decimal),
]
if account.currency_id:
c_specs += [
('cumul_bal_curr', 1, 0, 'number', None, cumul_balance_curr,
self.style_partner_cumul_decimal),
('curr_name', 1, 0, 'text', account.currency_id.name, None,
self.style_partner_cumul_right),
]
else:
c_specs += [
('cumul_bal_curr', 1, 0, 'text', "-", None,
self.style_partner_cumul_right),
('curr_name', 1, 0, 'text', "", None,
self.style_partner_cumul_right),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_position = self.xls_write_row(self.ws, row_position, row_data,
self.style_partner_cumul)
return row_position + 1
def generate_xls_report(self, _p, _xs, data, objects, wb):
# Initialisations
ws = wb.add_sheet(_p.report_name[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# Print Title
row_pos = self.print_title(ws, _p, row_pos, xlwt, _xs)
# Print empty row to define column sizes
row_pos = self.print_empty_row(ws, row_pos)
# Print Header Table titles (Fiscal Year - Accounts Filter - Periods
# Filter...)
row_pos = self.print_header_titles(ws, _p, data, row_pos, xlwt, _xs)
initial_balance_text = {
'initial_balance': _('Computed'),
'opening_balance': _('Opening Entries'),
False: _('No')
} # cf. account_report_partner_balance.mako
# Print Header Table data
row_pos = self.print_header_data(ws, _p, data, row_pos, xlwt, _xs,
initial_balance_text)
# Print comparison header table
if _p.comparison_mode in ('single', 'multiple'):
row_pos += 1
row_pos = self.print_comparison_header(_xs, xlwt, row_pos, _p, ws,
initial_balance_text)
# Freeze the line
ws.set_horz_split_pos(row_pos)
# cell styles for account data
regular_cell_format = _xs['borders_all']
regular_cell_style = xlwt.easyxf(regular_cell_format)
regular_cell_style_decimal = xlwt.easyxf(
regular_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
row_pos += 1
for current_account in objects:
partners_order = _p['partners_order_accounts']\
.get(current_account.id, False)
# do not display accounts without partners
if not partners_order:
continue
comparisons = _p['comparisons_accounts']\
.get(current_account.id, False)
# in multiple columns mode, we do not want to print accounts
# without any rows
if _p.comparison_mode in ('single', 'multiple'):
all_comparison_lines = [
comp['partners_amounts'][partner_id[1]]
for partner_id in partners_order for comp in comparisons
]
if not display_line(all_comparison_lines):
continue
current_partner_amounts = _p['partners_amounts_accounts']\
.get(current_account.id, False)
if _p.comparison_mode in ('single', 'multiple'):
comparison_total = {}
for i, comp in enumerate(comparisons):
comparison_total[i] = {'balance': 0.0}
# print row: Code - Account name
row_pos = self.print_row_code_account(ws, current_account, row_pos,
_xs, xlwt)
row_account_start = row_pos
# Print row: Titles "Account/Partner Name-Code/ref-Initial
# Balance-Debit-Credit-Balance" or "Account/Partner
# Name-Code/ref-Balance Year-Balance Year2-Balance C2-Balance C3"
row_pos = self.print_account_header(ws, _p, _xs, xlwt, row_pos)
for (partner_code_name, partner_id, partner_ref, partner_name) \
in partners_order:
partner = current_partner_amounts.get(partner_id, {})
# in single mode, we have to display all the partners even if
# their balance is 0.0 because the initial balance should match
# with the previous year closings
# in multiple columns mode, we do not want to print partners
# which have a balance at 0.0 in each comparison column
if _p.comparison_mode in ('single', 'multiple'):
all_comparison_lines = [
comp['partners_amounts'][partner_id]
for comp in comparisons
if comp['partners_amounts'].get(partner_id)
]
if not display_line(all_comparison_lines):
continue
# display data row
if len(_p.comp_params) == 2:
account_span = 3
else:
account_span = _p.initial_balance_mode and 2 or 3
c_specs = [('acc_title', account_span, 0, 'text',
partner_name if partner_name else _('Unallocated'))
]
c_specs += [('partner_ref', 1, 0, 'text',
partner_ref if partner_ref else '')]
if _p.comparison_mode == 'no_comparison':
bal_formula = ''
if _p.initial_balance_mode:
init_bal_cell = rowcol_to_cell(row_pos, 3)
bal_formula = init_bal_cell + '+'
debit_col = 4
c_specs += [
('init_bal', 1, 0, 'number',
partner.get('init_balance', 0.0), None,
regular_cell_style_decimal),
]
else:
debit_col = 3
c_specs += [
('debit', 1, 0, 'number', partner.get('debit', 0.0),
None, regular_cell_style_decimal),
('credit', 1, 0, 'number', partner.get('credit', 0.0),
None, regular_cell_style_decimal),
]
debit_cell = rowcol_to_cell(row_pos, debit_col)
credit_cell = rowcol_to_cell(row_pos, debit_col + 1)
bal_formula += debit_cell + '-' + credit_cell
c_specs += [
('bal', 1, 0, 'number', None, bal_formula,
regular_cell_style_decimal),
]
else:
c_specs += [
('bal', 1, 0, 'number', partner.get('balance', 0.0),
None, regular_cell_style_decimal),
]
if _p.comparison_mode in ('single', 'multiple'):
for i, comp in enumerate(comparisons):
comp_partners = comp['partners_amounts']
balance = diff = percent_diff = 0
if comp_partners.get(partner_id):
balance = comp_partners[partner_id]['balance']
diff = comp_partners[partner_id]['diff']
percent_diff = comp_partners[partner_id][
'percent_diff']
comparison_total[i]['balance'] += balance
c_specs += [
('balance_%s' % i, 1, 0, 'number', balance, None,
regular_cell_style_decimal),
]
# no diff in multiple comparisons because it shows too much
# data
if _p.comparison_mode == 'single':
c_specs += [
('balance_diff', 1, 0, 'number', diff, None,
regular_cell_style_decimal),
]
if percent_diff is False:
c_specs += [
('balance', 1, 0, 'number', diff, None,
regular_cell_style_decimal),
]
else:
c_specs += [
('perc_diff', 1, 0, 'number',
int(round(percent_diff))),
]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
regular_cell_style)
row_pos = self.print_account_totals(_xs, xlwt, ws,
row_account_start, row_pos,
current_account, _p)
def generate_xls_report(self, _p, _xs, data, objects, wb):
ws = wb.add_sheet(_p.report_name[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
# set print header/footer
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# cf. account_report_partner_ledger.mako
initial_balance_text = {
'initial_balance': _('Computed'),
'opening_balance': _('Opening Entries'),
False: _('No')
}
# Title
cell_style = xlwt.easyxf(_xs['xls_title'])
report_name = ' - '.join([
_p.report_name.upper(), _p.company.partner_id.name,
_p.company.currency_id.name
])
c_specs = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
row_style=cell_style)
# write empty row to define column sizes
c_sizes = self.column_sizes
c_specs = [('empty%s' % i, 1, c_sizes[i], 'text', None)
for i in range(0, len(c_sizes))]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
set_column_size=True)
# Header Table
nbr_columns = 10
if _p.amount_currency(data):
nbr_columns = 12
cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all']
cell_style = xlwt.easyxf(cell_format)
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_specs = [
('coa', 2, 0, 'text', _('Chart of Account')),
('fy', 1, 0, 'text', _('Fiscal Year')),
('df', 2, 0, 'text',
_p.filter_form(data) == 'filter_date' and _('Dates Filter')
or _('Periods Filter')),
('af', 1, 0, 'text', _('Accounts Filter')),
('tm', 2, 0, 'text', _('Target Moves')),
('ib', nbr_columns - 8, 0, 'text', _('Initial Balance')),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
row_style=cell_style_center)
cell_format = _xs['borders_all']
cell_style = xlwt.easyxf(cell_format)
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_specs = [
('coa', 2, 0, 'text', _p.chart_account.name),
('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'),
]
df = _('From') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.start_date if _p.start_date else u''
else:
df += _p.start_period.name if _p.start_period else u''
df += ' ' + _('To') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.stop_date if _p.stop_date else u''
else:
df += _p.stop_period.name if _p.stop_period else u''
c_specs += [
('df', 2, 0, 'text', df),
('af', 1, 0, 'text', _('Custom Filter')
if _p.partner_ids else _p.display_partner_account(data)),
('tm', 2, 0, 'text', _p.display_target_move(data)),
('ib', nbr_columns - 8, 0, 'text',
initial_balance_text[_p.initial_balance_mode]),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
row_style=cell_style_center)
ws.set_horz_split_pos(row_pos)
row_pos += 1
# Account Title Row
cell_format = _xs['xls_title'] + _xs['bold'] + \
_xs['fill'] + _xs['borders_all']
account_cell_style = xlwt.easyxf(cell_format)
account_cell_style_right = xlwt.easyxf(cell_format + _xs['right'])
account_cell_style_decimal = xlwt.easyxf(
cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
# Column Title Row
cell_format = _xs['bold']
c_title_cell_style = xlwt.easyxf(cell_format)
# Column Header Row
cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
c_hdr_cell_style = xlwt.easyxf(cell_format)
c_hdr_cell_style_right = xlwt.easyxf(cell_format + _xs['right'])
c_hdr_cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
# Column Initial Balance Row
cell_format = _xs['italic'] + _xs['borders_all']
c_init_cell_style = xlwt.easyxf(cell_format)
c_init_cell_style_decimal = xlwt.easyxf(
cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
# Column Cumulated balance Row
cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
c_cumul_cell_style = xlwt.easyxf(cell_format)
c_cumul_cell_style_right = xlwt.easyxf(cell_format + _xs['right'])
c_cumul_cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_cumul_cell_style_decimal = xlwt.easyxf(
cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
# Column Partner Row
cell_format = _xs['bold']
c_part_cell_style = xlwt.easyxf(cell_format)
c_specs = [
('date', 1, 0, 'text', _('Date'), None, c_hdr_cell_style),
('period', 1, 0, 'text', _('Period'), None, c_hdr_cell_style),
('move', 1, 0, 'text', _('Entry'), None, c_hdr_cell_style),
('journal', 1, 0, 'text', _('Journal'), None, c_hdr_cell_style),
('partner', 1, 0, 'text', _('Partner'), None, c_hdr_cell_style),
('label', 1, 0, 'text', _('Label'), None, c_hdr_cell_style),
('rec', 1, 0, 'text', _('Rec.'), None, c_hdr_cell_style),
('debit', 1, 0, 'text', _('Debit'), None, c_hdr_cell_style_right),
('credit', 1, 0, 'text', _('Credit'), None,
c_hdr_cell_style_right),
('cumul_bal', 1, 0, 'text', _('Cumul. Bal.'), None,
c_hdr_cell_style_right),
]
if _p.amount_currency(data):
c_specs += [
('curr_bal', 1, 0, 'text', _('Curr. Bal.'), None,
c_hdr_cell_style_right),
('curr_code', 1, 0, 'text', _('Curr.'), None,
c_hdr_cell_style_center),
]
c_hdr_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
# cell styles for ledger lines
ll_cell_format = _xs['borders_all']
ll_cell_style = xlwt.easyxf(ll_cell_format)
ll_cell_style_center = xlwt.easyxf(ll_cell_format + _xs['center'])
ll_cell_style_date = xlwt.easyxf(ll_cell_format + _xs['left'],
num_format_str=report_xls.date_format)
ll_cell_style_decimal = xlwt.easyxf(
ll_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
cnt = 0
for account in objects:
if _p['ledger_lines'].get(account.id, False) or \
_p['init_balance'].get(account.id, False):
if not _p['partners_order'].get(account.id, False):
continue
cnt += 1
account_total_debit = 0.0
account_total_credit = 0.0
account_balance_cumul = 0.0
account_balance_cumul_curr = 0.0
c_specs = [
('acc_title', nbr_columns, 0, 'text',
' - '.join([account.code,
account.name]), None, account_cell_style),
]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
c_title_cell_style)
row_pos += 1
for partner_name, p_id, p_ref, p_name in \
_p['partners_order'][account.id]:
total_debit = 0.0
total_credit = 0.0
cumul_balance = 0.0
cumul_balance_curr = 0.0
part_cumul_balance = 0.0
part_cumul_balance_curr = 0.0
c_specs = [
('partner_title', nbr_columns, 0, 'text', partner_name
or _('No Partner'), None, c_part_cell_style),
]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
c_title_cell_style)
row_pos = self.xls_write_row(ws, row_pos, c_hdr_data)
row_start_partner = row_pos
total_debit = _p['init_balance'][account.id].get(
p_id, {}).get('debit') or 0.0
total_credit = _p['init_balance'][account.id].get(
p_id, {}).get('credit') or 0.0
init_line = False
if _p.initial_balance_mode and \
(total_debit or total_credit):
init_line = True
part_cumul_balance = \
_p['init_balance'][account.id].get(
p_id, {}).get('init_balance') or 0.0
part_cumul_balance_curr = \
_p['init_balance'][account.id].get(
p_id, {}).get('init_balance_currency') or 0.0
balance_forward_currency = \
_p['init_balance'][account.id].get(
p_id, {}).get('currency_name') or ''
cumul_balance += part_cumul_balance
cumul_balance_curr += part_cumul_balance_curr
debit_cell = rowcol_to_cell(row_pos, 7)
credit_cell = rowcol_to_cell(row_pos, 8)
init_bal_formula = debit_cell + '-' + credit_cell
# Print row 'Initial Balance' by partn
c_specs = [('empty%s' % x, 1, 0, 'text', None)
for x in range(5)]
c_specs += [
('init_bal', 1, 0, 'text', _('Initial Balance')),
('rec', 1, 0, 'text', None),
('debit', 1, 0, 'number', total_debit, None,
c_init_cell_style_decimal),
('credit', 1, 0, 'number', total_credit, None,
c_init_cell_style_decimal),
('cumul_bal', 1, 0, 'number', None,
init_bal_formula, c_init_cell_style_decimal),
]
if _p.amount_currency(data):
c_specs += [
('curr_bal', 1, 0, 'number',
part_cumul_balance_curr, None,
c_init_cell_style_decimal),
('curr_code', 1, 0, 'text',
balance_forward_currency),
]
row_data = self.xls_row_template(
c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
c_init_cell_style)
for line in _p['ledger_lines'][account.id].get(p_id, []):
total_debit += line.get('debit') or 0.0
total_credit += line.get('credit') or 0.0
label_elements = [line.get('lname') or '']
if line.get('invoice_number'):
label_elements.append("(%s)" %
(line['invoice_number'], ))
label = ' '.join(label_elements)
cumul_balance += line.get('balance') or 0.0
if init_line or row_pos > row_start_partner:
cumbal_formula = rowcol_to_cell(row_pos - 1,
9) + '+'
else:
cumbal_formula = ''
debit_cell = rowcol_to_cell(row_pos, 7)
credit_cell = rowcol_to_cell(row_pos, 8)
cumbal_formula += debit_cell + '-' + credit_cell
# Print row ledger line data #
if line.get('ldate'):
c_specs = [
('ldate', 1, 0, 'date',
datetime.strptime(line['ldate'], '%Y-%m-%d'),
None, ll_cell_style_date),
]
else:
c_specs = [
('ldate', 1, 0, 'text', None),
]
c_specs += [
('period', 1, 0, 'text', line.get('period_code')
or ''),
('move', 1, 0, 'text', line.get('move_name')
or ''),
('journal', 1, 0, 'text', line.get('jcode') or ''),
('partner', 1, 0, 'text', line.get('partner_name')
or ''),
('label', 1, 0, 'text', label),
('rec_name', 1, 0, 'text', line.get('rec_name')
or ''),
('debit', 1, 0, 'number', line.get('debit'), None,
ll_cell_style_decimal),
('credit', 1, 0, 'number', line.get('credit'),
None, ll_cell_style_decimal),
('cumul_bal', 1, 0, 'number', None, cumbal_formula,
ll_cell_style_decimal),
]
if _p.amount_currency(data):
c_specs += [
('curr_bal', 1, 0, 'number',
line.get('amount_currency')
or 0.0, None, ll_cell_style_decimal),
('curr_code', 1, 0, 'text',
line.get('currency_code')
or '', None, ll_cell_style_center),
]
row_data = self.xls_row_template(
c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
ll_cell_style)
# end for line
# Print row Cumulated Balance by partner #
debit_partner_start = rowcol_to_cell(row_start_partner, 7)
debit_partner_end = rowcol_to_cell(row_pos - 1, 7)
debit_partner_total = 'SUM(' + debit_partner_start + \
':' + debit_partner_end + ')'
credit_partner_start = rowcol_to_cell(row_start_partner, 8)
credit_partner_end = rowcol_to_cell(row_pos - 1, 8)
credit_partner_total = 'SUM(' + credit_partner_start + \
':' + credit_partner_end + ')'
bal_partner_debit = rowcol_to_cell(row_pos, 7)
bal_partner_credit = rowcol_to_cell(row_pos, 8)
bal_partner_total = bal_partner_debit + \
'-' + bal_partner_credit
c_specs = [('empty%s' % x, 1, 0, 'text', None)
for x in range(5)]
c_specs += [
('init_bal', 1, 0, 'text',
_('Cumulated balance on Partner')),
('rec', 1, 0, 'text', None),
('debit', 1, 0, 'number', None, debit_partner_total,
c_cumul_cell_style_decimal),
('credit', 1, 0, 'number', None, credit_partner_total,
c_cumul_cell_style_decimal),
('cumul_bal', 1, 0, 'number', None, bal_partner_total,
c_cumul_cell_style_decimal),
]
if _p.amount_currency(data):
if account.currency_id:
c_specs += [
('curr_bal', 1, 0, 'number', cumul_balance_curr
or 0.0, None, c_cumul_cell_style_decimal)
]
else:
c_specs += [('curr_bal', 1, 0, 'text', '-', None,
c_cumul_cell_style_right)]
c_specs += [('curr_code', 1, 0, 'text',
account.currency_id.name
if account.currency_id else u'', None,
c_cumul_cell_style_center)]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
c_cumul_cell_style)
row_pos += 1
account_total_debit += total_debit
account_total_credit += total_credit
account_balance_cumul += cumul_balance
account_balance_cumul_curr += cumul_balance_curr
# Print row Cumulated Balance by account #
c_specs = [
('acc_title', 5, 0, 'text',
' - '.join([account.code, account.name])),
]
c_specs += [
('label', 1, 0, 'text', _('Cumulated balance on Account')),
('rec', 1, 0, 'text', None),
('debit', 1, 0, 'number', account_total_debit, None,
account_cell_style_decimal),
('credit', 1, 0, 'number', account_total_credit, None,
account_cell_style_decimal),
('cumul_bal', 1, 0, 'number', account_balance_cumul, None,
account_cell_style_decimal),
]
if _p.amount_currency(data):
if account.currency_id:
c_specs += [('curr_bal', 1, 0, 'number',
account_balance_cumul_curr
or 0.0, None, account_cell_style_decimal)]
else:
c_specs += [('curr_bal', 1, 0, 'text', '-', None,
account_cell_style_right)]
c_specs += [
('curr_code', 1, 0, 'text', account.currency_id.name
if account.currency_id else u'', None,
account_cell_style)
]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
account_cell_style)
row_pos += 2
def generate_xls_report(self, _p, _xs, data, objects, wb):
ws = wb.add_sheet(_p.report_name[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
# set print header/footer
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# cf. account_report_general_ledger.mako
initial_balance_text = {
'initial_balance': _('Computed'),
'opening_balance': _('Opening Entries'),
False: _('No')
}
# Title
cell_style = xlwt.easyxf(_xs['xls_title'])
report_name = ' - '.join([
_p.report_name.upper(), _p.company.partner_id.name,
_p.company.currency_id.name
])
c_specs = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
row_style=cell_style)
# write empty row to define column sizes
c_sizes = self.column_sizes
c_specs = [('empty%s' % i, 1, c_sizes[i], 'text', None)
for i in range(0, len(c_sizes))]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
set_column_size=True)
# Header Table
cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all']
cell_style = xlwt.easyxf(cell_format)
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_specs = [
('coa', 2, 0, 'text', _('Chart of Account')),
('fy', 1, 0, 'text', _('Fiscal Year')),
('df', 3, 0, 'text',
_p.filter_form(data) == 'filter_date' and _('Dates Filter')
or _('Periods Filter')),
('af', 1, 0, 'text', _('Accounts Filter')),
('tm', 2, 0, 'text', _('Target Moves')),
('ib', 2, 0, 'text', _('Initial Balance')),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
row_style=cell_style_center)
cell_format = _xs['borders_all']
cell_style = xlwt.easyxf(cell_format)
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_specs = [
('coa', 2, 0, 'text', _p.chart_account.name),
('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'),
]
df = _('From') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.start_date if _p.start_date else u''
else:
df += _p.start_period.name if _p.start_period else u''
df += ' ' + _('To') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.stop_date if _p.stop_date else u''
else:
df += _p.stop_period.name if _p.stop_period else u''
c_specs += [
('df', 3, 0, 'text', df),
('af', 1, 0, 'text', _p.accounts(data)
and ', '.join([account.code for account in _p.accounts(data)])
or _('All')),
('tm', 2, 0, 'text', _p.display_target_move(data)),
('ib', 2, 0, 'text',
initial_balance_text[_p.initial_balance_mode]),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
row_style=cell_style_center)
ws.set_horz_split_pos(row_pos)
row_pos += 1
# Column Title Row
cell_format = _xs['bold']
c_title_cell_style = xlwt.easyxf(cell_format)
# Column Header Row
cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
c_hdr_cell_style = xlwt.easyxf(cell_format)
c_hdr_cell_style_right = xlwt.easyxf(cell_format + _xs['right'])
c_hdr_cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_hdr_cell_style_decimal = xlwt.easyxf(
cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
# Column Initial Balance Row
cell_format = _xs['italic'] + _xs['borders_all']
c_init_cell_style = xlwt.easyxf(cell_format)
c_init_cell_style_decimal = xlwt.easyxf(
cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
c_specs = [
('date', 1, 0, 'text', _('Date'), None, c_hdr_cell_style),
('period', 1, 0, 'text', _('Period'), None, c_hdr_cell_style),
('move', 1, 0, 'text', _('Entry'), None, c_hdr_cell_style),
('journal', 1, 0, 'text', _('Journal'), None, c_hdr_cell_style),
('account_code', 1, 0, 'text', _('Account'), None,
c_hdr_cell_style),
('partner', 1, 0, 'text', _('Partner'), None, c_hdr_cell_style),
('ref', 1, 0, 'text', _('Reference'), None, c_hdr_cell_style),
('label', 1, 0, 'text', _('Label'), None, c_hdr_cell_style),
('counterpart', 1, 0, 'text', _('Counterpart'), None,
c_hdr_cell_style),
('debit', 1, 0, 'text', _('Debit'), None, c_hdr_cell_style_right),
('credit', 1, 0, 'text', _('Credit'), None,
c_hdr_cell_style_right),
('cumul_bal', 1, 0, 'text', _('Cumul. Bal.'), None,
c_hdr_cell_style_right),
]
if _p.amount_currency(data):
c_specs += [
('curr_bal', 1, 0, 'text', _('Curr. Bal.'), None,
c_hdr_cell_style_right),
('curr_code', 1, 0, 'text', _('Curr.'), None,
c_hdr_cell_style_center),
]
c_hdr_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
# cell styles for ledger lines
ll_cell_format = _xs['borders_all']
ll_cell_style = xlwt.easyxf(ll_cell_format)
ll_cell_style_center = xlwt.easyxf(ll_cell_format + _xs['center'])
ll_cell_style_date = xlwt.easyxf(ll_cell_format + _xs['left'],
num_format_str=report_xls.date_format)
ll_cell_style_decimal = xlwt.easyxf(
ll_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
cnt = 0
for account in objects:
display_initial_balance = _p['init_balance'][account.id] and \
(_p['init_balance'][account.id].get(
'debit', 0.0) != 0.0 or
_p['init_balance'][account.id].get('credit', 0.0) != 0.0)
display_ledger_lines = _p['ledger_lines'][account.id]
if _p.display_account_raw(data) == 'all' or \
(display_ledger_lines or display_initial_balance):
# TO DO : replace cumul amounts by xls formulas
cnt += 1
cumul_debit = 0.0
cumul_credit = 0.0
cumul_balance = 0.0
cumul_balance_curr = 0.0
c_specs = [
('acc_title', 11, 0, 'text',
' - '.join([account.code, account.name])),
]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
c_title_cell_style)
row_pos = self.xls_write_row(ws, row_pos, c_hdr_data)
row_start = row_pos
if display_initial_balance:
init_balance = _p['init_balance'][account.id]
cumul_debit = init_balance.get('debit') or 0.0
cumul_credit = init_balance.get('credit') or 0.0
cumul_balance = init_balance.get('init_balance') or 0.0
cumul_balance_curr = init_balance.get(
'init_balance_currency') or 0.0
c_specs = [('empty%s' % x, 1, 0, 'text', None)
for x in range(6)]
c_specs += [
('None', 1, 0, 'text', None),
('init_bal', 1, 0, 'text', _('Initial Balance')),
('counterpart', 1, 0, 'text', None),
('debit', 1, 0, 'number', cumul_debit, None,
c_init_cell_style_decimal),
('credit', 1, 0, 'number', cumul_credit, None,
c_init_cell_style_decimal),
('cumul_bal', 1, 0, 'number', cumul_balance, None,
c_init_cell_style_decimal),
]
if _p.amount_currency(data):
c_specs += [
('curr_bal', 1, 0, 'number', cumul_balance_curr,
None, c_init_cell_style_decimal),
('curr_code', 1, 0, 'text', None),
]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
c_init_cell_style)
for line in _p['ledger_lines'][account.id]:
cumul_debit += line.get('debit') or 0.0
cumul_credit += line.get('credit') or 0.0
cumul_balance_curr += line.get('amount_currency') or 0.0
cumul_balance += line.get('balance') or 0.0
label_elements = [line.get('lname') or '']
if line.get('invoice_number'):
label_elements.append("(%s)" %
(line['invoice_number'], ))
label = ' '.join(label_elements)
if line.get('ldate'):
c_specs = [
('ldate', 1, 0, 'date',
datetime.strptime(line['ldate'], '%Y-%m-%d'),
None, ll_cell_style_date),
]
else:
c_specs = [
('ldate', 1, 0, 'text', None),
]
c_specs += [
('period', 1, 0, 'text', line.get('period_code')
or ''),
('move', 1, 0, 'text', line.get('move_name') or ''),
('journal', 1, 0, 'text', line.get('jcode') or ''),
('account_code', 1, 0, 'text', account.code),
('partner', 1, 0, 'text', line.get('partner_name')
or ''),
('ref', 1, 0, 'text', line.get('lref')),
('label', 1, 0, 'text', label),
('counterpart', 1, 0, 'text', line.get('counterparts')
or ''),
('debit', 1, 0, 'number', line.get('debit', 0.0), None,
ll_cell_style_decimal),
('credit', 1, 0, 'number', line.get('credit', 0.0),
None, ll_cell_style_decimal),
('cumul_bal', 1, 0, 'number', cumul_balance, None,
ll_cell_style_decimal),
]
if _p.amount_currency(data):
c_specs += [
('curr_bal', 1, 0, 'number',
line.get('amount_currency')
or 0.0, None, ll_cell_style_decimal),
('curr_code', 1, 0, 'text',
line.get('currency_code')
or '', None, ll_cell_style_center),
]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
ll_cell_style)
debit_start = rowcol_to_cell(row_start, 9)
debit_end = rowcol_to_cell(row_pos - 1, 9)
debit_formula = 'SUM(' + debit_start + ':' + debit_end + ')'
credit_start = rowcol_to_cell(row_start, 10)
credit_end = rowcol_to_cell(row_pos - 1, 10)
credit_formula = 'SUM(' + credit_start + ':' + credit_end + ')'
balance_debit = rowcol_to_cell(row_pos, 9)
balance_credit = rowcol_to_cell(row_pos, 10)
balance_formula = balance_debit + '-' + balance_credit
c_specs = [
('acc_title', 8, 0, 'text',
' - '.join([account.code, account.name])),
('cum_bal', 1, 0, 'text',
_('Cumulated Balance on Account'), None,
c_hdr_cell_style_right),
('debit', 1, 0, 'number', None, debit_formula,
c_hdr_cell_style_decimal),
('credit', 1, 0, 'number', None, credit_formula,
c_hdr_cell_style_decimal),
('balance', 1, 0, 'number', None, balance_formula,
c_hdr_cell_style_decimal),
]
if _p.amount_currency(data):
if account.currency_id:
c_specs += [
('curr_bal', 1, 0, 'number', cumul_balance_curr,
None, c_hdr_cell_style_decimal)
]
else:
c_specs += [('curr_bal', 1, 0, 'text', None)]
c_specs += [('curr_code', 1, 0, 'text', None)]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
c_hdr_cell_style)
row_pos += 1
def generate_xls_report(self, _p, _xs, data, objects, wb):
ws = wb.add_sheet(_p.report_name[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
# set print header/footer
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# cf. account_report_trial_balance.mako
initial_balance_text = {'initial_balance': _('Computed'),
'opening_balance': _('Opening Entries'),
False: _('No')}
# Title
cell_style = xlwt.easyxf(_xs['xls_title'])
report_name = ' - '.join([_p.report_name.upper(),
_p.company.partner_id.name,
_p.company.currency_id.name])
c_specs = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=cell_style)
# write empty row to define column sizes
c_sizes = self.column_sizes
c_specs = [('empty%s' % i, 1, c_sizes[i], 'text', None)
for i in range(0, len(c_sizes))]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, set_column_size=True)
# Header Table
cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all']
cell_style = xlwt.easyxf(cell_format)
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_specs = [
('fy', 1, 0, 'text', _('Fiscal Year')),
('af', 2, 0, 'text', _('Accounts Filter')),
('df', 1, 0, 'text', _p.filter_form(data) ==
'filter_date' and _('Dates Filter') or _('Periods Filter')),
('tm', 2, 0, 'text', _('Target Moves'), None, cell_style_center),
('ib', 1, 0, 'text', _('Initial Balance'),
None, cell_style_center),
('coa', 1, 0, 'text', _('Chart of Account'),
None, cell_style_center),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=cell_style)
cell_format = _xs['borders_all'] + _xs['wrap'] + _xs['top']
cell_style = xlwt.easyxf(cell_format)
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_specs = [
('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'),
('af', 2, 0, 'text', _p.accounts(data) and ', '.join(
[account.code for account in _p.accounts(data)]) or _('All')),
]
df = _('From') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.start_date if _p.start_date else u''
else:
df += _p.start_period.name if _p.start_period else u''
df += ' ' + _('\nTo') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.stop_date if _p.stop_date else u''
else:
df += _p.stop_period.name if _p.stop_period else u''
c_specs += [
('df', 1, 0, 'text', df),
('tm', 2, 0, 'text', _p.display_target_move(
data), None, cell_style_center),
('ib', 1, 0, 'text', initial_balance_text[
_p.initial_balance_mode], None, cell_style_center),
('coa', 1, 0, 'text', _p.chart_account.name,
None, cell_style_center),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=cell_style)
# comparison header table
if _p.comparison_mode in ('single', 'multiple'):
row_pos += 1
cell_format_ct = _xs['bold'] + \
_xs['fill_blue'] + _xs['borders_all']
cell_style_ct = xlwt.easyxf(cell_format_ct)
c_specs = [('ct', 8, 0, 'text', _('Comparisons'))]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=cell_style_ct)
cell_style_center = xlwt.easyxf(cell_format)
for index, params in enumerate(_p.comp_params):
c_specs = [
('c', 3, 0, 'text', _('Comparison') + str(index + 1) +
' (C' + str(index + 1) + ')')]
if params['comparison_filter'] == 'filter_date':
c_specs += [('f', 3, 0, 'text', _('Dates Filter') + ': ' +
_p.formatLang(
params['start'], date=True) + ' - ' +
_p.formatLang(params['stop'], date=True))]
elif params['comparison_filter'] == 'filter_period':
c_specs += [('f', 3, 0, 'text', _('Periods Filter') +
': ' + params['start'].name + ' - ' +
params['stop'].name)]
else:
c_specs += [('f', 3, 0, 'text', _('Fiscal Year') +
': ' + params['fiscalyear'].name)]
c_specs += [('ib', 2, 0, 'text', _('Initial Balance') +
': ' +
initial_balance_text[
params['initial_balance_mode']])]
row_data = self.xls_row_template(
c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=cell_style_center)
row_pos += 1
# Column Header Row
cell_format = _xs['bold'] + _xs['fill_blue'] + \
_xs['borders_all'] + _xs['wrap'] + _xs['top']
cell_style = xlwt.easyxf(cell_format)
cell_style_right = xlwt.easyxf(cell_format + _xs['right'])
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
if len(_p.comp_params) == 2:
account_span = 3
else:
account_span = _p.initial_balance_mode and 2 or 3
c_specs = [
('code', 1, 0, 'text', _('Code')),
('account', account_span, 0, 'text', _('Account')),
]
if _p.comparison_mode == 'no_comparison':
if _p.initial_balance_mode:
c_specs += [('init_bal', 1, 0, 'text',
_('Initial Balance'), None, cell_style_right)]
c_specs += [
('debit', 1, 0, 'text', _('Debit'), None, cell_style_right),
('credit', 1, 0, 'text', _('Credit'), None, cell_style_right),
]
if _p.comparison_mode == 'no_comparison' or not _p.fiscalyear:
c_specs += [('balance', 1, 0, 'text',
_('Balance'), None, cell_style_right)]
else:
c_specs += [('balance_fy', 1, 0, 'text', _('Balance %s') %
_p.fiscalyear.name, None, cell_style_right)]
if _p.comparison_mode in ('single', 'multiple'):
for index in range(_p.nb_comparison):
if _p.comp_params[index][
'comparison_filter'] == 'filter_year' \
and _p.comp_params[index].get('fiscalyear', False):
c_specs += [('balance_%s' % index, 1, 0, 'text',
_('Balance %s') %
_p.comp_params[index]['fiscalyear'].name,
None, cell_style_right)]
else:
c_specs += [('balance_%s' % index, 1, 0, 'text',
_('Balance C%s') % (index + 1), None,
cell_style_right)]
if _p.comparison_mode == 'single':
c_specs += [
('diff', 1, 0, 'text', _('Difference'),
None, cell_style_right),
('diff_percent', 1, 0, 'text',
_('% Difference'), None, cell_style_center),
]
c_specs += [('type', 1, 0, 'text', _('Type'), None, cell_style_center)]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=cell_style)
ws.set_horz_split_pos(row_pos)
last_child_consol_ids = []
# cell styles for account data
view_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
view_cell_style = xlwt.easyxf(view_cell_format)
view_cell_style_center = xlwt.easyxf(view_cell_format + _xs['center'])
view_cell_style_decimal = xlwt.easyxf(
view_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
view_cell_style_pct = xlwt.easyxf(
view_cell_format + _xs['center'], num_format_str='0')
regular_cell_format = _xs['borders_all']
regular_cell_style = xlwt.easyxf(regular_cell_format)
regular_cell_style_center = xlwt.easyxf(
regular_cell_format + _xs['center'])
regular_cell_style_decimal = xlwt.easyxf(
regular_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
regular_cell_style_pct = xlwt.easyxf(
regular_cell_format + _xs['center'], num_format_str='0')
for current_account in objects:
if not _p['to_display_accounts'][current_account.id]:
continue
if current_account.type == 'view':
cell_style = view_cell_style
cell_style_center = view_cell_style_center
cell_style_decimal = view_cell_style_decimal
cell_style_pct = view_cell_style_pct
else:
cell_style = regular_cell_style
cell_style_center = regular_cell_style_center
cell_style_decimal = regular_cell_style_decimal
cell_style_pct = regular_cell_style_pct
comparisons = _p['comparisons_accounts'][current_account.id]
if current_account.id not in last_child_consol_ids:
# current account is a not a consolidation child: use its own
# level
last_child_consol_ids = [
child_consol_id.id for child_consol_id in
current_account.child_consol_ids]
c_specs = [
('code', 1, 0, 'text', current_account.code),
('account', account_span, 0, 'text', current_account.name),
]
if _p.comparison_mode == 'no_comparison':
debit_cell = rowcol_to_cell(row_pos, 4)
credit_cell = rowcol_to_cell(row_pos, 5)
bal_formula = debit_cell + '-' + credit_cell
if _p.initial_balance_mode:
init_cell = rowcol_to_cell(row_pos, 3)
debit_cell = rowcol_to_cell(row_pos, 4)
credit_cell = rowcol_to_cell(row_pos, 5)
bal_formula = init_cell + '+' + \
debit_cell + '-' + credit_cell
c_specs += [('init_bal', 1, 0, 'number',
_p['init_balance_'
'accounts'][current_account.id],
None,
cell_style_decimal)]
c_specs += [
('debit', 1, 0, 'number',
_p['debit_accounts'][current_account.id],
None, cell_style_decimal),
('credit', 1, 0, 'number',
_p['credit_accounts'][current_account.id],
None, cell_style_decimal),
]
c_specs += [('balance', 1, 0, 'number', None,
bal_formula, cell_style_decimal)]
else:
c_specs += [('balance', 1, 0, 'number',
_p['balance_accounts'][current_account.id],
None, cell_style_decimal)]
if _p.comparison_mode in ('single', 'multiple'):
c = 1
for comp_account in comparisons:
c_specs += [('balance_%s' % c, 1, 0, 'number',
comp_account['balance'], None,
cell_style_decimal)]
c += 1
if _p.comparison_mode == 'single':
c_specs += [
('diff', 1, 0, 'number', comp_account[
'diff'], None, cell_style_decimal),
('diff_percent', 1, 0, 'number', comp_account[
'percent_diff'] and
comp_account['percent_diff'] or 0, None,
cell_style_pct),
]
c_specs += [('type', 1, 0, 'text',
current_account.type, None, cell_style_center)]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=cell_style)
def print_group_cumul_account(self, row_position, row_start_account,
account):
# This procedure will create an Excel sumif function that will check
# in the column "label" for the "Cumulated Balance.." string and make a
# sum of the debit & credit data
# the text "Cumulated Balance on Partner starts in column 4 when
# selecting the option regroup by currency, 5 in the other case
start_col = 4
# range in which we search for the text "Cumulated Balance on Partner"
reference_start = rowcol_to_cell(row_start_account, start_col)
reference_stop = rowcol_to_cell(row_position - 1, start_col)
# range in which we make the sum of all the cumulated balance lines
# (debit)
range_debit_start = rowcol_to_cell(row_start_account, start_col + 3)
range_debit_stop = rowcol_to_cell(row_position - 1, start_col + 3)
# range in which we make the sum of all the cumulated balance lines
# (crebit)
range_credit_start = rowcol_to_cell(row_start_account, start_col + 4)
range_credit_stop = rowcol_to_cell(row_position - 1, start_col + 4)
search_key = _('Cumulated Balance on Partner')
total_debit_account = 'SUMIF(' + reference_start + ':' + \
reference_stop + ';"' + search_key + '";' + range_debit_start + \
':' + range_debit_stop + ')'
total_credit_account = 'SUMIF(' + reference_start + ':' + \
reference_stop + ';"' + search_key + '";' + range_credit_start + \
':' + range_credit_stop + ')'
bal_account_debit = rowcol_to_cell(row_position, start_col + 3)
bal_account_credit = rowcol_to_cell(row_position, start_col + 4)
bal_account_total = bal_account_debit + '-' + bal_account_credit
bal_curr_start = rowcol_to_cell(row_start_account, start_col + 6)
bal_curr_end = rowcol_to_cell(row_position - 1, start_col + 6)
cumul_balance_curr = 'SUMIF(' + reference_start + ':' + \
reference_stop + ';"' + search_key + '";' + \
bal_curr_start + ':' + bal_curr_end + ')'
c_specs = [
('acc_title', start_col, 0, 'text',
' - '.join([account.code, account.name])),
('init_bal', 2, 0, 'text', _('Cumulated Balance on Account')),
('empty2', 1, 0, 'text', None),
('debit', 1, 0, 'number', None, total_debit_account,
self.style_account_title_decimal),
('credit', 1, 0, 'number', None, total_credit_account,
self.style_account_title_decimal),
('balance', 1, 0, 'number', None, bal_account_total,
self.style_account_title_decimal),
]
if account.currency_id:
c_specs += [
('cumul_bal_curr', 1, 0, 'number', None, cumul_balance_curr,
self.style_account_title_decimal),
('curr_name', 1, 0, 'text', account.currency_id.name, None,
self.style_account_title_decimal),
]
else:
c_specs += [('cumul_bal_curr', 1, 0, 'text', "-", None,
self.style_account_title_right),
('curr_name', 1, 0, 'text', "", None,
self.style_account_title_right)]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_position = self.xls_write_row(self.ws, row_position, row_data,
self.style_account_title)
return row_position + 1
def print_group_lines(self, row_position, account, line, _p, line_number):
label_elements = [line.get('lname') or '']
if line.get('invoice_number'):
label_elements.append("(%s)" % (line['invoice_number'], ))
label = ' '.join(label_elements)
# Mako: <div class="act_as_row lines
# ${line.get('is_from_previous_periods') and
# 'open_invoice_previous_line' or ''} ${line.get('is_clearance_line')
# and 'clearance_line' or ''}">
if line.get('is_from_previous_periods') or \
line.get('is_clearance_line'):
style_line_default = self.style_default_italic
style_line_right = self.style_right_italic
style_line_date = self.style_date_italic
style_line_decimal = self.style_decimal_italic
else:
style_line_default = self.style_default
style_line_right = self.style_right
style_line_date = self.style_date
style_line_decimal = self.style_decimal
debit_cell = rowcol_to_cell(row_position, 7)
credit_cell = rowcol_to_cell(row_position, 8)
previous_balance = rowcol_to_cell(row_position - 1, 9)
# if it is the first line, the balance is only debit - credit
if line_number == 1:
cumul_balance = debit_cell + '-' + credit_cell
# cumulate devit - credit and balance of previous line
else:
cumul_balance = debit_cell + '-' + \
credit_cell + '+' + previous_balance
if line.get('ldate'):
c_specs = [('date', 1, 0, 'date',
datetime.strptime(line['ldate'],
'%Y-%m-%d'), None, style_line_date)]
else:
c_specs = [('date', 1, 0, 'text', None)]
c_specs += [
('period_code', 1, 0, 'text', line.get('period_code') or ''),
('entry', 1, 0, 'text', line.get('move_name') or ''),
('journal', 1, 0, 'text', line.get('jcode') or ''),
('label', 1, 0, 'text', label),
('rec', 1, 0, 'text', line.get('rec_name') or ''),
]
if line.get('date_maturity'):
c_specs += [('datedue', 1, 0, 'date',
datetime.strptime(line['date_maturity'],
'%Y-%m-%d'), None, style_line_date)]
else:
c_specs += [('datedue', 1, 0, 'text', None)]
c_specs += [
('debit', 1, 0, 'number', line.get('debit')
or 0.0, None, style_line_decimal),
('credit', 1, 0, 'number', line.get('credit')
or 0.0, None, style_line_decimal),
('cumul', 1, 0, 'number', None, cumul_balance, style_line_decimal),
]
if account.currency_id:
c_specs += [
('curramount', 1, 0, 'number', line.get('amount_currency')
or 0.0, None, style_line_decimal),
('currcode', 1, 0, 'text', line.get('currency_code')
or '', None, style_line_right),
]
else:
c_specs += [
('curramount', 1, 0, 'text', '-', None, style_line_right),
('currcode', 1, 0, 'text', '', None, style_line_right),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_position = self.xls_write_row(self.ws, row_position, row_data,
style_line_default)
return (row_position, cumul_balance)
