def __init__(self, field_names, row_count=0):
self.field_names = field_names
self.output = io.BytesIO()
self.workbook = xlsxwriter.Workbook(self.output, {'in_memory': True})
self.base_style = self.workbook.add_format({'text_wrap': True})
self.header_style = self.workbook.add_format({'bold': True})
self.header_bold_style = self.workbook.add_format({
'text_wrap': True,
'bold': True,
'bg_color': '#e9ecef'
})
self.date_style = self.workbook.add_format({
'text_wrap': True,
'num_format': 'yyyy-mm-dd'
})
self.datetime_style = self.workbook.add_format({
'text_wrap':
True,
'num_format':
'yyyy-mm-dd hh:mm:ss'
})
self.worksheet = self.workbook.add_worksheet()
self.value = False
if row_count > self.worksheet.xls_rowmax:
raise UserError(
_('There are too many rows (%s rows, limit: %s) to export as Excel 2007-2013 (.xlsx) format. Consider splitting the export.'
) % (row_count, self.worksheet.xls_rowmax))
def _export_xlsx(self, products):
"""
This method use for export selected product in XLSX file for Map product
Develop by : Meera Sidapara
Date : 02/12/2021
:param products: Selected product listing ids
:return: selected product data and file name
"""
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet('Map Product')
header = list(products[0].keys())
header_format = workbook.add_format({'bold': True, 'font_size': 10})
general_format = workbook.add_format({'font_size': 10})
worksheet.write_row(0, 0, header, header_format)
index = 0
for product in products:
index += 1
worksheet.write_row(index, 0, list(product.values()),
general_format)
workbook.close()
b_data = base64.b64encode(output.getvalue())
filename = 'woo_product_export_{}_{}.xlsx'.format(
self.id,
datetime.now().strftime("%m_%d_%Y-%H_%M_%S"))
return {'file': b_data, 'file_name': filename}
def generate_excel(self):
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet()
domain = []
if self.name:
domain = expression.AND(
[domain, [('date', '=', fields.Date.to_string(self.name))]])
if self.category_id:
domain = expression.AND(
[domain, [('category_id', '=', self.category_id.id)]])
consults = self.env['hospital.consult'].search(domain)
# Formatos / Estilos
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})
merge_format = workbook.add_format({'align': 'center', 'bold': True})
# Ancho de Columnas
worksheet.set_column(0, 0, 3) # A
worksheet.set_column(1, 1, 17) # B
worksheet.set_column(3, 3, 17) # C
worksheet.set_column(4, 4, 17) # E
# Cabezera
worksheet.merge_range('B2:E2', 'Patient', merge_format)
# Sub Cabezeras
worksheet.write_string('B3', 'Name')
worksheet.write_string('C3', 'Patient')
worksheet.write_string('D3', 'Date')
worksheet.write_string('E3', 'Category')
row = 3
for consult in consults:
worksheet.write_string(row, 1, consult.name)
worksheet.write_string(row, 2, consult.patient_id.name)
worksheet.write_datetime(row, 3, consult.date, date_format)
worksheet.write_string(row, 4, consult.category_id.name)
row += 1 # row = row + 1
workbook.close()
output.seek(0)
self.write({
'file': base64.b64encode(output.getvalue()),
'file_name': 'prueba.xlsx',
'state': 'finish',
})
return {
'type': 'ir.actions.act_window',
'name': 'Hospital Excel Consult',
'res_model': 'hospital.excel_consult_modal',
'view_mode': 'form',
'res_id': self.id,
'target': 'new'
}
def get_xlsx(self, options, response):
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet(self.get_report_name()[:31])
def_style = workbook.add_format({'font_name': 'Arial'})
title_style = workbook.add_format({'font_name': 'Arial', 'bold': True})
level_0_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2
})
# sheet.set_column(0, 0, 15) # Set the first column width to 15
sheet.write(0, 0, u'零用金月报', title_style)
sheet.write(2, 0, u'院舍名称：%s' % (self.env.user.company_id.name),
title_style)
sheet.write(
3, 0, u'地址：%s' % (self.env.user.company_id.partner_id.street +
self.env.user.company_id.partner_id.street2),
title_style)
now = datetime.datetime.now()
sheet.write(4, 0, u'时段：%s' % (str(now.year) + '-' + str(now.month)),
title_style)
y_offset = 6
x = 0
for column in self.get_columns_name(options)[1:]:
sheet.write(
y_offset, x,
column.get('name', '').replace('<br/>',
' ').replace(' ', ' '),
level_0_style)
x += 1
ctx = self.set_context(options)
ctx.update({'no_format': True, 'print_mode': True})
lines = self.with_context(ctx).get_lines(options)
y_offset = 7
for i in xrange(len(lines)):
datas = lines[i]['columns']
for j in xrange(len(datas)):
sheet.write(y_offset, j, datas[j]['name'], def_style)
y_offset += 1
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def _export_allocation_lines(self):
calculation_type = self.get_calculation_type()
headers, lines_values = self._get_allocation_lines_data()
report_name = '%s-%02d %s' % (self.year, int(self.month), self.name)
report_name = slugify(report_name)
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet(calculation_type)
style_header = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bg_color': '#eeeeee'
})
style_data = workbook.add_format({'font_name': 'Arial'})
offset_x = 0
offset_y = 0
for header in headers:
sheet.write(offset_y, offset_x, header, style_header)
offset_x += 1
for row in lines_values:
offset_y += 1
offset_x = 0
for value in row:
sheet.write(offset_y, offset_x, value, style_data)
offset_x += 1
workbook.close()
output.seek(0)
generated_file = output.read()
output.close()
self.write(
{'allocation_lines_xlsx': base64.encodebytes(generated_file)})
def get_xlsx(self, options, response=None):
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet(self._get_report_name()[:31])
date_default_col1_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'indent': 2,
'num_format': 'yyyy-mm-dd',
'border': 1
})
date_default_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'num_format': 'yyyy-mm-dd',
'border': 1
})
default_col1_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'indent': 2,
'border': 1
})
default_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'border': 1
})
title_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'border': 1,
'align': 'center'
})
super_col_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'align': 'center',
'border': 1
})
level_0_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 13,
'border': 1,
'font_color': '#666666'
})
level_1_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 13,
'border': 1,
'font_color': '#666666'
})
level_2_col1_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666',
'indent': 1,
'border': 1
})
level_2_col1_total_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666',
'border': 1
})
level_2_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666',
'border': 1
})
level_3_col1_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'indent': 2,
'border': 1
})
level_3_col1_total_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666',
'indent': 1,
'border': 1
})
level_3_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'border': 1
})
header_right_bold = workbook.add_format({
'font_name': 'Arial',
'font_size': 11,
'align': 'center',
'bold': True,
'font_color': 'black'
})
header_right_normal = workbook.add_format({
'font_name': 'Arial',
'font_size': 9,
'align': 'center',
'font_color': 'black'
})
header_right_bottom = workbook.add_format({
'font_name': 'Arial',
'font_size': 11,
'align': 'right',
'font_color': 'black'
})
header_left_bold = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'align': 'left',
'bold': True,
'font_color': 'black'
})
header_left_normal = workbook.add_format({
'font_name': 'Arial',
'font_size': 11,
'align': 'left',
'font_color': 'black'
})
footer_left_bold = workbook.add_format({
'font_name': 'Arial',
'font_size': 11,
'align': 'left',
'bold': True,
'font_color': 'black'
})
footer_right_center = workbook.add_format({
'font_name': 'Arial',
'font_size': 11,
'align': 'center',
'font_color': 'black'
})
footer_right_center_blod = workbook.add_format({
'font_name': 'Arial',
'font_size': 11,
'align': 'center',
'bold': True,
'font_color': 'black'
})
# Set the first column width to 50
sheet.set_column(0, 0, 27)
sheet.set_column('B:B', 17)
sheet.set_column('C:F', 17)
sheet.set_column('G:G', 20)
sheet.set_column('H:H', 13)
sheet.set_column('I:I', 55)
super_columns = self._get_super_columns(options)
y_offset = 12
# Todo in master: Try to put this logic elsewhere
x = super_columns.get('x_offset', 0)
for super_col in super_columns.get('columns', []):
cell_content = super_col.get('string',
'').replace('<br/>', ' ').replace(
' ', ' ')
x_merge = 0
if x_merge and x_merge > 1:
sheet.merge_range(0, x, 0, x + (x_merge - 1), cell_content,
super_col_style)
x += x_merge
else:
sheet.write(0, x, cell_content, super_col_style)
x += 1
for row in self.get_header(options):
x = 0
for column in row:
colspan = 1
header_label = column.get('name',
'').replace('<br/>', ' ').replace(
' ', ' ')
if colspan == 1:
sheet.write(y_offset, x, header_label, title_style)
else:
sheet.merge_range(y_offset, x, y_offset, x + colspan - 1,
header_label, title_style)
x += colspan
y_offset += 1
ctx = self._set_context(options)
ctx.update({
'no_format': True,
'print_mode': True,
'prefetch_fields': False
})
# deactivating the prefetching saves ~35% on get_lines running time
lines = self.with_context(ctx)._get_lines(options)
if options.get('hierarchy'):
lines = self._create_hierarchy(lines)
sheet.hide_gridlines(2)
sheet.write('I2', 'Mẫu số: 01-1/GTGT', header_right_bold)
sheet.write('I3', '(Ban hành kèm theo Thông tư số 156/2013/TT-BTC',
header_right_normal)
sheet.write('I4', 'ngày 06/11/2013 của Bộ Tài chính)',
header_right_normal)
sheet.write('A3', 'BẢNG KÊ HOÁ ĐƠN, CHỨNG TỪ HÀNG HOÁ, DỊCH VỤ BÁN RA',
header_left_bold)
sheet.write('A4', '(Kèm theo tờ khai thuế GTGT theo mẫu số 01/GTGT)',
header_left_normal)
date_from = options['date']['date_from']
date_to = options['date']['date_to']
month_date_to = int(date_to.split('-')[1])
if month_date_to <= 3:
quarter = "1"
elif month_date_to >= 4 and month_date_to <= 6:
quarter = "2"
elif month_date_to >= 7 and month_date_to <= 9:
quarter = "3"
else:
quarter = "4"
sheet.write(
'A5', 'Kỳ tính thuế: Tháng ' + date_from.split('-')[1] + ' năm ' +
date_from.split('-')[0] + ' / Quý ' + quarter + ' Năm ' +
date_to.split('-')[0], header_left_normal)
sheet.write('A7', 'Người nộp thuế: ' + self.env.user.company_id.name,
header_left_normal)
sheet.write('A8', 'Mã số thuế: ' + (options['vat_number'] or ''),
header_left_normal)
sheet.write('A9', 'Tên đại lý thuế (nếu có):', header_left_normal)
sheet.write('A10', 'Mã số thuế: ', header_left_normal)
sheet.write('I12', 'Đơn vị tiền: đồng Việt Nam', header_right_bottom)
amount_without_tax = 0
amount_with_tax = 0
taxes = 0
# write all data rows
for y in range(0, len(lines)):
level = lines[y].get('level')
if lines[y].get('caret_options'):
style = level_3_style
col1_style = level_3_col1_style
elif level == 0:
y_offset += 1
style = level_0_style
col1_style = style
elif level == 1:
style = level_1_style
col1_style = style
elif level == 2:
style = level_2_style
col1_style = 'total' in lines[y].get('class', '').split(
' ') and level_2_col1_total_style or level_2_col1_style
elif level == 3:
style = level_3_style
col1_style = 'total' in lines[y].get('class', '').split(
' ') and level_3_col1_total_style or level_3_col1_style
else:
style = default_style
col1_style = default_col1_style
# write the first column, with a specific style to manage the indentation
cell_type, cell_value = self._get_cell_type_value(lines[y])
if cell_type == 'date':
sheet.write_datetime(y + y_offset, 0, cell_value,
date_default_col1_style)
else:
sheet.write(y + y_offset, 0, cell_value, col1_style)
if lines[y].get('id') == 'total_global':
amount_without_tax = lines[y]['columns'][5].get('name')
taxes = lines[y]['columns'][6].get('name')
amount_with_tax = amount_without_tax + taxes
# write all the remaining cells
for x in range(1, len(lines[y]['columns']) + 1):
cell_type, cell_value = self._get_cell_type_value(
lines[y]['columns'][x - 1])
if cell_type == 'date':
sheet.write_datetime(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
cell_value, date_default_style)
else:
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1, cell_value,
style)
y += y_offset + 2
sheet.write(
y, 0, 'Tổng doanh thu hàng hóa, dịch vụ bán ra : ' +
str(amount_without_tax), header_left_normal)
sheet.write(
y + 1, 0,
'Tổng doanh thu hàng hoá, dịch vụ bán ra chịu thuế GTGT : ' +
str(amount_with_tax), header_left_normal)
sheet.write(
y + 2, 0,
'Tổng thuế GTGT của hàng hóa, dịch vụ bán ra : ' + str(taxes),
header_left_normal)
sheet.write(
y + 4, 0,
'Tôi cam đoan số liệu khai trên là đúng và chịu trách nhiệm trước pháp luật về những số liệu đã khai.',
header_left_normal)
sheet.write(y + 6, 0, 'NHÂN VIÊN ĐẠI LÝ THUẾ', footer_left_bold)
sheet.write(y + 7, 0, 'Họ và tên: _________________',
header_left_normal)
sheet.write(y + 8, 0, 'Chứng chỉ hành nghề số: _________________',
header_left_normal)
sheet.write(
y + 5, 8,
'_____________ , ngày ___________ tháng ___________ năm ___________',
footer_right_center)
sheet.write(y + 6, 8, 'NGƯỜI NỘP THUẾ hoặc', footer_right_center_blod)
sheet.write(y + 7, 8, 'ĐẠI DIỆN HỢP PHÁP CỦA NGƯỜI NỘP THUẾ',
footer_right_center_blod)
sheet.write(y + 8, 8, 'Ký tên, đóng dấu (ghi rõ họ tên và chức vụ)',
footer_right_center)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_xlsx_report(self, data, response):
# Initialize
#############################################################
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet(data['account_report_id'][1])
sheet.set_zoom(95)
sheet2 = workbook.add_worksheet('Filters')
sheet2.protect()
# Get record and data
record = self.env['ins.financial.report'].browse(data.get('id',
[])) or False
data = record.get_report_values()
# Formats
############################################################
sheet2.set_column(0, 0, 25)
sheet2.set_column(1, 1, 25)
sheet2.set_column(2, 2, 25)
sheet2.set_column(3, 3, 25)
sheet2.set_column(4, 4, 25)
sheet2.set_column(5, 5, 25)
sheet2.set_column(6, 6, 25)
sheet.freeze_panes(4, 0)
sheet.screen_gridlines = False
sheet2.screen_gridlines = False
sheet2.protect()
format_title = workbook.add_format({
'bold': True,
'align': 'center',
'font_size': 12,
'border': False,
'font': 'Arial',
})
format_header = workbook.add_format({
'bold': True,
'font_size': 10,
'align': 'center',
'font': 'Arial',
'bottom': False
})
content_header = workbook.add_format({
'bold': False,
'font_size': 10,
'align': 'center',
'font': 'Arial',
})
content_header_date = workbook.add_format({
'bold': False,
'font_size': 10,
'align': 'center',
'font': 'Arial',
# 'num_format': 'dd/mm/yyyy',
})
line_header = workbook.add_format({
'bold': False,
'font_size': 10,
'align': 'right',
'font': 'Arial',
'bottom': False
})
line_header_bold = workbook.add_format({
'bold': True,
'font_size': 10,
'align': 'right',
'font': 'Arial',
'bottom': True
})
line_header_string = workbook.add_format({
'bold': False,
'font_size': 10,
'align': 'left',
'font': 'Arial',
'bottom': False
})
line_header_string_bold = workbook.add_format({
'bold': True,
'font_size': 10,
'align': 'left',
'font': 'Arial',
'bottom': True
})
lang = self.env.user.lang
lang_id = self.env['res.lang'].search([('code', '=', lang)])[0]
currency_id = self.env.user.company_id.currency_id
line_header.num_format = currency_id.excel_format
line_header_bold.num_format = currency_id.excel_format
content_header_date.num_format = DATE_DICT.get(lang_id.date_format,
'dd/mm/yyyy')
# Write data
################################################################
row_pos_2 = 0
row_pos = 0
sheet2.write(row_pos_2, 0, _('Date from'), format_header)
datestring = fields.Date.from_string(
str(data['form']['date_from']
and data['form']['date_from'])).strftime(lang_id.date_format)
if data['form'].get('date_from'):
sheet2.write(row_pos_2, 1, datestring, content_header_date)
row_pos_2 += 1
# Date to
sheet2.write(row_pos_2, 0, _('Date to'), format_header)
datestring = fields.Date.from_string(
str(data['form']['date_to']
and data['form']['date_to'])).strftime(lang_id.date_format)
if data['form'].get('date_to'):
sheet2.write(row_pos_2, 1, datestring, content_header_date)
row_pos_2 += 1
if data['form']['enable_filter']:
# Compariosn Date from
sheet2.write(row_pos_2, 0, _('Comparison Date from'),
format_header)
datestring = fields.Date.from_string(
str(data['form']['comparison_context']['date_from']
and data['form']['comparison_context']
['date_from'].strftime('%Y-%m-%d')))
if data['form']['comparison_context'].get('date_from'):
sheet2.write(row_pos_2, 1, datestring, content_header_date)
row_pos_2 += 1
# Compariosn Date to
sheet2.write(row_pos_2, 0, _('Comparison Date to'), format_header)
datestring = fields.Date.from_string(
str(data['form']['comparison_context']['date_to']
and data['form']['comparison_context']['date_to'].strftime(
'%Y-%m-%d')))
if data['form']['comparison_context'].get('date_to'):
sheet2.write(row_pos_2, 1, datestring, content_header_date)
# Write Ledger details
row_pos += 3
if data['form']['debit_credit'] == 1:
sheet.set_column(0, 0, 90)
sheet.set_column(1, 1, 15)
sheet.set_column(2, 3, 15)
sheet.set_column(3, 3, 15)
sheet.write(row_pos, 0, _('Name'), format_header)
sheet.write(row_pos, 1, _('Debit'), format_header)
sheet.write(row_pos, 2, _('Credit'), format_header)
sheet.write(row_pos, 3, _('Balance'), format_header)
for a in data['report_lines']:
if a['level'] == 2:
row_pos += 1
row_pos += 1
if a.get('account', False):
tmp_style_str = line_header_string
tmp_style_num = line_header
else:
tmp_style_str = line_header_string_bold
tmp_style_num = line_header_bold
sheet.write(row_pos, 0,
' ' * len(a.get('list_len', [])) + a.get('name'),
tmp_style_str)
sheet.write(row_pos, 1, float(a.get('debit')), tmp_style_num)
sheet.write(row_pos, 2, float(a.get('credit')), tmp_style_num)
sheet.write(row_pos, 3, float(a.get('balance')), tmp_style_num)
if data['form']['debit_credit'] != 1:
sheet.set_column(0, 0, 105)
sheet.set_column(1, 1, 15)
sheet.set_column(2, 2, 15)
sheet.write(row_pos, 0, _('Name'), format_header)
if data['form']['enable_filter']:
sheet.write(row_pos, 1, data['form']['label_filter'],
format_header)
sheet.write(row_pos, 2, _('Balance'), format_header)
else:
sheet.write(row_pos, 1, _('Balance'), format_header)
for a in data['report_lines']:
if a['level'] == 2:
row_pos += 1
row_pos += 1
if a.get('account', False):
tmp_style_str = line_header_string
tmp_style_num = line_header
else:
tmp_style_str = line_header_string_bold
tmp_style_num = line_header_bold
sheet.write(row_pos, 0,
' ' * len(a.get('list_len', [])) + a.get('name'),
tmp_style_str)
if data['form']['enable_filter']:
sheet.write(row_pos, 1, float(a.get('balance_cmp')),
tmp_style_num)
sheet.write(row_pos, 2, float(a.get('balance')),
tmp_style_num)
else:
sheet.write(row_pos, 1, float(a.get('balance')),
tmp_style_num)
if data.get('initial_balance') or data.get(
'current_balance') or data.get('ending_balance'):
row_pos += 2
sheet.merge_range(row_pos, 1, row_pos, 2, 'Initial Cash Balance',
tmp_style_num)
sheet.write(row_pos, 3, float(data.get('initial_balance')),
tmp_style_num)
row_pos += 1
sheet.merge_range(row_pos, 1, row_pos, 2, 'Current Cash Balance',
tmp_style_num)
sheet.write(row_pos, 3, float(data.get('current_balance')),
tmp_style_num)
row_pos += 1
sheet.merge_range(row_pos, 1, row_pos, 2, 'Net Cash Balance',
tmp_style_num)
sheet.write(row_pos, 3, float(data.get('ending_balance')),
tmp_style_num)
# Close and return
#################################################################
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_xlsx_report(self, options, response):
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
env_obj = self.search([
('id', '=', options['form']['id'])
]).env['report.account.report_agedpartnerbalance']
vals = self.search([('id', '=', options['form']['id'])])
result_selection = vals.result_selection
if result_selection == 'customer':
account_type = ['receivable']
account_type_name = "Receivable Accounts"
elif result_selection == 'supplier':
account_type = ['payable']
account_type_name = "Payable Accounts"
else:
account_type = ['payable', 'receivable']
account_type_name = "Receivable and Payable Accounts"
# sales_person_wise = vals.user_wise_report
date_from = options['form']['date_from']
target_move = options['form']['target_move']
if target_move == 'all':
target_move_name = "All Entries"
else:
target_move_name = "All Posted Entries"
period_length = vals.period_length
# # user_ids = vals.user_ids.ids
move_lines, total, dummy = env_obj._get_partner_move_lines(
account_type, date_from, target_move, period_length)
sheet = workbook.add_worksheet()
format1 = workbook.add_format({
'font_size': 16,
'align': 'center',
'bg_color': '#D3D3D3',
'bold': True
})
format1.set_font_color('#000080')
format2 = workbook.add_format({'font_size': 10, 'bold': True})
format3 = workbook.add_format({'font_size': 10})
logged_users = self.env['res.company']._company_default_get(
'account.account')
sheet.write('A1', logged_users.name, format3)
sheet.write('A3', 'Start Date:', format2)
sheet.write('B3', date_from, format3)
sheet.merge_range('E3:G3', 'Period Length (days):', format2)
sheet.write('H3', period_length, format3)
sheet.write('A4', "Partner's:", format2)
sheet.merge_range('B4:C4', account_type_name, format3)
sheet.merge_range('E4:F4', 'Target Moves:', format2)
sheet.merge_range('G4:H4', target_move_name, format3)
sheet.set_column(0, 0, 20)
# # if sales_person_wise:
# # sheet.set_column(1, 1, 20)
# # sheet.merge_range(5, 0, 7, 8, "Aged Partner Balance", format1)
# # else:
sheet.merge_range(5, 0, 7, 7, "Aged Partner Balance", format1)
row_value = 8
column_value = 0
# # if sales_person_wise:
# # sheet.write(row_value, column_value, "Sales Person", format2)
# # column_value += 1
sheet.write(row_value, column_value, "Partners", format2)
sheet.write(row_value, column_value + 1, "Not due", format2)
sheet.write(row_value, column_value + 2, "0-" + str(period_length),
format2)
sheet.write(row_value, column_value + 3,
str(period_length) + "-" + str(2 * period_length), format2)
sheet.write(row_value, column_value + 4,
str(2 * period_length) + "-" + str(3 * period_length),
format2)
sheet.write(row_value, column_value + 5,
str(3 * period_length) + "-" + str(4 * period_length),
format2)
sheet.write(row_value, column_value + 6, "+" + str(4 * period_length),
format2)
sheet.write(row_value, column_value + 7, "Total", format2)
row_value += 1
column_value = 0
if move_lines:
sheet.write(row_value, column_value, "Account Total", format2)
sheet.write(row_value, column_value + 1, total[6], format2)
sheet.write(row_value, column_value + 2, total[4], format2)
sheet.write(row_value, column_value + 3, total[3], format2)
sheet.write(row_value, column_value + 4, total[2], format2)
sheet.write(row_value, column_value + 5, total[1], format2)
sheet.write(row_value, column_value + 6, total[0], format2)
sheet.write(row_value, column_value + 7, total[5], format2)
row_value += 1
for i in move_lines:
partner_ref = self.env['res.partner'].browse(
i['partner_id']).ref
if partner_ref:
partner_ref = "[" + str(partner_ref) + "] "
partner_name = partner_ref + str(i['name'])
else:
partner_name = str(i['name'])
sheet.write(row_value, column_value, partner_name, format3)
sheet.write(row_value, column_value + 1, i['direction'],
format3)
sheet.write(row_value, column_value + 2, i['4'], format3)
sheet.write(row_value, column_value + 3, i['3'], format3)
sheet.write(row_value, column_value + 4, i['2'], format3)
sheet.write(row_value, column_value + 5, i['1'], format3)
sheet.write(row_value, column_value + 6, i['0'], format3)
sheet.write(row_value, column_value + 7, i['total'], format3)
row_value += 1
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_dynamic_xlsx_report(self, data, response, report_data, dfr_data):
report_data_main = json.loads(report_data)
output = io.BytesIO()
filters = json.loads(data)
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '20px'
})
sub_heading = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '10px',
'border': 1,
'border_color': 'black'
})
txt = workbook.add_format({'font_size': '10px', 'border': 1})
txt_l = workbook.add_format({
'font_size': '10px',
'border': 1,
'bold': True
})
sheet.merge_range('A2:D3',
self.env.user.company_id.name + ':' + ' Day Book',
head)
date_head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '10px'
})
date_style = workbook.add_format({
'align': 'center',
'font_size': '10px'
})
if filters.get('date_from'):
sheet.merge_range('A4:B4', 'From: ' + filters.get('date_from'),
date_head)
if filters.get('date_to'):
sheet.merge_range('C4:D4', 'To: ' + filters.get('date_to'),
date_head)
sheet.write(
'A5',
'Journals: ' + ', '.join([lt or '' for lt in filters['journals']]),
date_head)
sheet.merge_range('E4:F4',
'Target Moves: ' + filters.get('target_move'),
date_head)
sheet.merge_range(
'B5:D5', 'Account Type: ' +
', '.join([lt or '' for lt in filters['accounts']]), date_head)
sheet.merge_range('A7:E7', 'Date', sub_heading)
sheet.write('F7', 'Debit', sub_heading)
sheet.write('G7', 'Credit', sub_heading)
sheet.write('H7', 'Balance', sub_heading)
row = 6
col = 0
sheet.set_column(4, 0, 15)
sheet.set_column(5, 0, 15)
sheet.set_column(6, 1, 15)
sheet.set_column(7, 2, 15)
sheet.set_column(8, 3, 15)
sheet.set_column(9, 4, 15)
sheet.set_column(10, 5, 15)
sheet.set_column(11, 6, 15)
for rec_data in report_data_main:
one_lst = []
two_lst = []
row += 1
sheet.merge_range(row, col, row, col + 4, rec_data['date'], txt_l)
sheet.write(row, col + 5, rec_data['debit'], txt_l)
sheet.write(row, col + 6, rec_data['credit'], txt_l)
sheet.write(row, col + 7, rec_data['balance'], txt_l)
for line_data in rec_data['child_lines']:
row += 1
sheet.write(row, col, line_data.get('ldate'), txt)
sheet.write(row, col + 1, line_data.get('lcode'), txt)
sheet.write(row, col + 2, line_data.get('partner_name'), txt)
sheet.write(row, col + 3, line_data.get('move_name'), txt)
sheet.write(row, col + 4, line_data.get('lname'), txt)
sheet.write(row, col + 5, line_data.get('debit'), txt)
sheet.write(row, col + 6, line_data.get('credit'), txt)
sheet.write(row, col + 7, line_data.get('balance'), txt)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_xlsx(self, options, response):
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet(self.get_report_name()[:31])
def_style = workbook.add_format({'font_name': 'Arial'})
title_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2
})
level_0_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'pattern': 1,
'font_color': '#FFFFFF'
})
level_0_style_left = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'left': 2,
'pattern': 1,
'font_color': '#FFFFFF'
})
level_0_style_right = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'right': 2,
'pattern': 1,
'font_color': '#FFFFFF'
})
level_1_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2
})
level_1_style_left = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'left': 2
})
level_1_style_right = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'right': 2
})
level_2_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'top': 2
})
level_2_style_left = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'top': 2,
'left': 2
})
level_2_style_right = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'top': 2,
'right': 2
})
level_3_style = def_style
level_3_style_left = workbook.add_format({
'font_name': 'Arial',
'left': 2
})
level_3_style_right = workbook.add_format({
'font_name': 'Arial',
'right': 2
})
domain_style = workbook.add_format({
'font_name': 'Arial',
'italic': True
})
domain_style_left = workbook.add_format({
'font_name': 'Arial',
'italic': True,
'left': 2
})
domain_style_right = workbook.add_format({
'font_name': 'Arial',
'italic': True,
'right': 2
})
upper_line_style = workbook.add_format({
'font_name': 'Arial',
'top': 2
})
sheet.set_column(0, 0, 15) # Set the first column width to 15
sheet.write(0, 0, '', title_style)
y_offset = 0
# if self.get_report_obj().get_name() == 'coa' and self.get_special_date_line_names():
# sheet.write(y_offset, 0, '', title_style)
# sheet.write(y_offset, 1, '', title_style)
# x = 2
# for column in self.with_context(is_xls=True).get_special_date_line_names():
# sheet.write(y_offset, x, column, title_style)
# sheet.write(y_offset, x+1, '', title_style)
# x += 2
# sheet.write(y_offset, x, '', title_style)
# y_offset += 1
x = 0
for column in self.get_columns_name(options):
sheet.write(
y_offset, x,
column.get('name', '').replace('<br/>',
' ').replace(' ', ' '),
title_style)
x += 1
y_offset += 1
ctx = self.set_context(options)
ctx.update({'no_format': True, 'print_mode': True})
lines = self.with_context(ctx).get_lines(options)
if options.get('hierarchy'):
lines = self.create_hierarchy(lines)
if lines:
max_width = max([len(l['columns']) for l in lines])
for y in range(0, len(lines)):
if lines[y].get('level') == 0:
for x in range(0, len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, upper_line_style)
y_offset += 1
style_left = level_0_style_left
style_right = level_0_style_right
style = level_0_style
elif lines[y].get('level') == 1:
for x in range(0, len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, upper_line_style)
y_offset += 1
style_left = level_1_style_left
style_right = level_1_style_right
style = level_1_style
elif lines[y].get('level') == 2:
style_left = level_2_style_left
style_right = level_2_style_right
style = level_2_style
elif lines[y].get('level') == 3:
style_left = level_3_style_left
style_right = level_3_style_right
style = level_3_style
# elif lines[y].get('type') != 'line':
# style_left = domain_style_left
# style_right = domain_style_right
# style = domain_style
else:
style = def_style
style_left = def_style
style_right = def_style
sheet.write(y + y_offset, 0, lines[y]['name'], style_left)
for x in range(1, max_width - len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, style)
for x in range(1, len(lines[y]['columns']) + 1):
# if isinstance(lines[y]['columns'][x - 1], tuple):
# lines[y]['columns'][x - 1] = lines[y]['columns'][x - 1][0]
if x < len(lines[y]['columns']):
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
lines[y]['columns'][x - 1].get('name',
''), style)
else:
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
lines[y]['columns'][x - 1].get('name', ''),
style_right)
if 'total' in lines[y].get('class',
'') or lines[y].get('level') == 0:
for x in range(len(lines[0]['columns']) + 1):
sheet.write(y + 1 + y_offset, x, None, upper_line_style)
y_offset += 1
if lines:
for x in range(max_width + 1):
sheet.write(len(lines) + y_offset, x, None, upper_line_style)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_dynamic_xlsx_report(self, data, response, report_data, dfr_data):
report_data_main = json.loads(report_data)
output = io.BytesIO()
name_data = json.loads(dfr_data)
filters = json.loads(data)
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '20px'
})
sub_heading = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '10px',
'border': 1,
'border_color': 'black'
})
txt = workbook.add_format({'font_size': '10px', 'border': 1})
txt_l = workbook.add_format({
'font_size': '10px',
'border': 1,
'bold': True
})
sheet.merge_range(
'A2:J3',
self.env.user.company_id.name + ':' + name_data.get('name'), head)
date_head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '10px'
})
date_style = workbook.add_format({
'align': 'center',
'font_size': '10px'
})
if filters.get('date_from'):
sheet.merge_range('B4:C4', 'From: ' + filters.get('date_from'),
date_head)
if filters.get('date_to'):
sheet.merge_range('H4:I4', 'To: ' + filters.get('date_to'),
date_head)
sheet.merge_range(
'A5:J6',
'Journals: ' + ', '.join([lt or ''
for lt in filters['journals']]) +
' Target Moves: ' + filters.get('target_move'), date_head)
sheet.write('A8', 'Code', sub_heading)
sheet.write('B8', 'Amount', sub_heading)
sheet.write('C8', 'Date', sub_heading)
sheet.write('D8', 'JRNL', sub_heading)
sheet.write('E8', 'Partner', sub_heading)
sheet.write('F8', 'Move', sub_heading)
sheet.write('G8', 'Entry Label', sub_heading)
sheet.write('H8', 'Debit', sub_heading)
sheet.write('I8', 'Credit', sub_heading)
sheet.write('J8', 'Balance', sub_heading)
row = 6
col = 0
sheet.set_column(8, 0, 15)
sheet.set_column('B:B', 40)
sheet.set_column(8, 2, 15)
sheet.set_column(8, 3, 15)
sheet.set_column(8, 4, 15)
sheet.set_column(8, 5, 15)
sheet.set_column(8, 6, 50)
sheet.set_column(8, 7, 26)
sheet.set_column(8, 8, 15)
sheet.set_column(8, 9, 15)
for rec_data in report_data_main:
row += 1
sheet.write(row + 1, col, rec_data['code'], txt)
sheet.write(row + 1, col + 1, rec_data['name'], txt)
sheet.write(row + 1, col + 2, '', txt)
sheet.write(row + 1, col + 3, '', txt)
sheet.write(row + 1, col + 4, '', txt)
sheet.write(row + 1, col + 5, '', txt)
sheet.write(row + 1, col + 6, '', txt)
sheet.write(row + 1, col + 7, rec_data['debit'], txt)
sheet.write(row + 1, col + 8, rec_data['credit'], txt)
sheet.write(row + 1, col + 9, rec_data['balance'], txt)
for line_data in rec_data['move_lines']:
row += 1
sheet.write(row + 1, col, '', txt)
sheet.write(row + 1, col + 1, '', txt)
sheet.write(row + 1, col + 2, line_data.get('ldate'), txt)
sheet.write(row + 1, col + 3, line_data.get('lcode'), txt)
sheet.write(row + 1, col + 4, line_data.get('partner_name'),
txt)
sheet.write(row + 1, col + 5, line_data.get('move_name'), txt)
sheet.write(row + 1, col + 6, line_data.get('lname'), txt)
sheet.write(row + 1, col + 7, line_data.get('debit'), txt)
sheet.write(row + 1, col + 8, line_data.get('credit'), txt)
sheet.write(row + 1, col + 9, line_data.get('balance'), txt)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_dynamic_xlsx_report(self, data, response, report_data, dfr_data ):
report_data_main = json.loads(report_data)
output = io.BytesIO()
filters = json.loads(data)
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
head = workbook.add_format({'align': 'center', 'bold': True,
'font_size': '20px'})
sub_heading = workbook.add_format(
{'align': 'center', 'bold': True, 'font_size': '10px',
'border': 1,
'border_color': 'black'})
heading = workbook.add_format(
{'align': 'center', 'bold': True, 'font_size': '10px',
'border': 2,
'border_color': 'black'})
txt = workbook.add_format({'font_size': '10px', 'border': 1})
txt_l = workbook.add_format(
{'font_size': '10px', 'border': 1, 'bold': True})
txt_v = workbook.add_format(
{'align': 'right', 'font_size': '10px', 'border': 1})
sheet.merge_range('A2:H3',
self.env.user.company_id.name + ':' + ' Partner Ageing',
head)
date_head = workbook.add_format({'align': 'center', 'bold': True,
'font_size': '10px'})
date_style = workbook.add_format({'align': 'center',
'font_size': '10px'})
if filters.get('date_from'):
sheet.merge_range('A4:B4',
'As On Date: ' + filters.get('date_from'),
date_head)
sheet.merge_range('C4:E4',
'Account Type: ' + filters.get('result_selection'),
date_head)
sheet.merge_range('A5:B5',
'Target Moves: ' + filters.get('target_move'),
date_head)
sheet.merge_range('D5:F5', ' Partners: ' + ', '.join(
[lt or '' for lt in
filters['partners']]), date_head)
sheet.merge_range('G5:H5', ' Partner Type: ' + ', '.join(
[lt or '' for lt in
filters['partner_tags']]),
date_head)
sheet.merge_range('A7:C7', 'Partner', heading)
sheet.write('D7', 'Total', heading)
sheet.write('E7', 'Not Due', heading)
sheet.write('F7', '0-20', heading)
sheet.write('G7', '20-40', heading)
sheet.write('H7', '40-60', heading)
sheet.write('I7', '60-80', heading)
sheet.write('J7', '80+', heading)
lst = []
for rec in report_data_main[0]:
lst.append(rec)
row = 6
col = 0
sheet.set_column(5, 0, 15)
sheet.set_column(6, 1, 15)
sheet.set_column(7, 2, 15)
sheet.set_column(8, 3, 15)
sheet.set_column(9, 4, 15)
sheet.set_column(10, 5, 15)
sheet.set_column(11, 6, 15)
for rec_data in report_data_main[0]:
one_lst = []
two_lst = []
row += 1
sheet.merge_range(row, col, row, col + 2, rec_data['name'], txt_l)
sheet.write(row, col + 3, rec_data['total'], txt_l)
sheet.write(row, col + 4, rec_data['direction'], txt_l)
sheet.write(row, col + 5, rec_data['4'], txt_l)
sheet.write(row, col + 6, rec_data['3'], txt_l)
sheet.write(row, col + 7, rec_data['2'], txt_l)
sheet.write(row, col + 8, rec_data['1'], txt_l)
sheet.write(row, col + 9, rec_data['0'], txt_l)
row += 1
sheet.write(row, col, 'Entry Label', sub_heading)
sheet.write(row, col + 1, 'Due Date', sub_heading)
sheet.write(row, col + 2, 'Journal', sub_heading)
sheet.write(row, col + 3, 'Account', sub_heading)
sheet.write(row, col + 4, 'Not Due', sub_heading)
sheet.write(row, col + 5, '0 - 20', sub_heading)
sheet.write(row, col + 6, '21 - 40', sub_heading)
sheet.write(row, col + 7, '41 - 60', sub_heading)
sheet.write(row, col + 8, '61 - 80', sub_heading)
sheet.write(row, col + 9, '81 - 100', sub_heading)
for line_data in rec_data['child_lines']:
row += 1
sheet.write(row, col, line_data.get('move'), txt)
sheet.write(row, col + 1, line_data.get('date'), txt)
sheet.write(row, col + 2, line_data.get('jrnl'), txt)
sheet.write(row, col + 3, line_data.get('acc_code'), txt)
if line_data.get('period6'):
sheet.write(row, col + 4, line_data.get('amount'), txt)
else:
sheet.write(row, col + 4, "0", txt_v)
if line_data.get('period5'):
sheet.write(row, col + 5, line_data.get('amount'), txt)
else:
sheet.write(row, col + 5, "0", txt_v)
if line_data.get('period4'):
sheet.write(row, col + 6, line_data.get('amount'), txt)
else:
sheet.write(row, col + 6, "0", txt_v)
if line_data.get('period3'):
sheet.write(row, col + 7, line_data.get('amount'), txt)
else:
sheet.write(row, col + 7, "0", txt_v)
if line_data.get('period2'):
sheet.write(row, col + 8, line_data.get('amount'), txt)
else:
sheet.write(row, col + 8, "0", txt_v)
if line_data.get('period1'):
sheet.write(row, col + 9, line_data.get('amount'), txt)
else:
sheet.write(row, col + 9, "0", txt_v)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def create_excel_workbook(self, file_pointer):
workbook = xlsxwriter.Workbook(file_pointer)
return workbook
def button_generate_excel(self):
self.ensure_one()
self.check_dates()
budget = self.budget_id
report_data = self._prepare_report_data()
currency = budget.company_id.currency_id
currency_number = '#,##0'
if currency.decimal_places:
currency_number += ('.' + '0' * currency.decimal_places)
if currency.position == 'after':
currency_format = currency_number + ' "' + currency.symbol + '"'
else:
currency_format = '"' + currency.symbol + '" ' + currency_number
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output)
# Formats
# Report Information
report_text = workbook.add_format({
'bold': True,
})
# Table header
table_header = workbook.add_format({
'border': 1,
'text_wrap': True,
'valign': 'vcenter',
'align': 'center',
'bold': True,
'bg_color': '#dddddd',
})
# Budget line
budget_line_format = {
'border': 1,
'text_wrap': True,
'valign': 'vcenter',
'bold': True,
'bg_color': '#eeeeee',
}
budget_line = workbook.add_format(budget_line_format)
budget_line_currency_format = dict(budget_line_format)
budget_line_currency_format['num_format'] = currency_format
budget_line_currency = workbook.add_format(budget_line_currency_format)
budget_line_percentage_format = dict(budget_line_format)
budget_line_percentage_format['num_format'] = '0.00%'
budget_line_percentage = workbook.add_format(
budget_line_percentage_format)
budget_line_date_format = dict(budget_line_format)
budget_line_date_format['num_format'] = _excel_date_format
budget_line_date = workbook.add_format(budget_line_date_format)
# Account breakdown line
account_line = workbook.add_format({'border': 1, 'align': 'left'})
account_line_currency = workbook.add_format({
'border':
1,
'align':
'right',
'num_format':
currency_format,
})
account_line_percentage = workbook.add_format({
'border': 1,
'align': 'right',
'num_format': '0.00%',
})
sheet = workbook.add_worksheet(report_data['sheet_name'])
# Set column width
sheet.set_column(0, 1, 30)
sheet.set_column(2, len(_headers) - 1, 27)
headers_offset_y = 4
headers_offset_x = 0
# Write report information
sheet.merge_range('A1:B1', report_data['report_name'], report_text)
sheet.merge_range("A2:B2", report_data['report_company'], report_text)
sheet.merge_range("A3:B3", report_data['report_period'], report_text)
# Write table headers
for header_index in range(len(_headers)):
sheet.write(headers_offset_y, header_index, _headers[header_index],
table_header)
lines_offset_y = headers_offset_y + 1
for budget_line_data in report_data['budget_lines']:
# Write budget line with totals
line_offset_y = lines_offset_y
lines_offset_y += 1
for lines_offset_x in range(len(_headers)):
sheet.write_blank(line_offset_y, lines_offset_x, None,
budget_line)
if 'budgetary_position' in budget_line_data:
sheet.write(line_offset_y, 0,
budget_line_data['budgetary_position'],
budget_line)
if 'analytic_account' in budget_line_data:
sheet.write(line_offset_y, 2,
budget_line_data['analytic_account'], budget_line)
sheet.write(line_offset_y, 3, budget_line_data['date_from'],
budget_line_date)
sheet.write(line_offset_y, 4, budget_line_data['date_to'],
budget_line_date)
sheet.write(line_offset_y, 5, budget_line_data['planned_amount'],
budget_line_currency)
sheet.write(line_offset_y, 6, budget_line_data['practical_amount'],
budget_line_currency)
sheet.write(line_offset_y, 7, budget_line_data['achievement'],
budget_line_percentage)
if 'account_lines' in budget_line_data:
for line in budget_line_data['account_lines']:
# Write account line
for lines_offset_x in range(len(_headers)):
sheet.write_blank(lines_offset_y, lines_offset_x, None,
account_line)
sheet.write(lines_offset_y, 1, line['name'], account_line)
sheet.write(lines_offset_y, 6, line['practical_amount'],
account_line_currency)
sheet.write(lines_offset_y, 7, line['achievement'],
account_line_percentage)
lines_offset_y += 1
workbook.close()
out = base64.encodebytes(output.getvalue())
output.close()
filename = _('budget_report_%s-%s_%s') % (
self.start_date.strftime(_fn_date_format),
self.end_date.strftime(_fn_date_format), self.budget_id.name)
filename = slugify(filename) + '.xlsx'
return self.set_data_excel(out, filename)
def print_date_wise_stock_register(self):
Move = self.env['stock.move']
Product = self.env['product.product']
start_time = fields.datetime.now()
from_date = self.from_date
to_date = self.to_date
if not (self.product_ids or self.categ_ids):
products = Product.search([('type', '=', 'product')])
elif self.report_by == 'by_products':
products = self.product_ids
else:
products = Product.search([('categ_id', 'in', self.categ_ids.ids)])
# Date wise opening quantity
product_quantities = products._compute_quantities_dict(
False, False, False, from_date, to_date)
# Received data
in_moves = Move.read_group(
[('date', '>=', from_date), ('date', '<', to_date),
('product_id', 'in', products.ids), ('state', '=', 'done'),
('picking_code', '=', 'incoming'),
('location_dest_id.usage', '=', 'internal')],
['product_uom_qty', 'price_unit'], ['product_id'])
in_move_dict = dict((item['product_id'][0], (item['product_uom_qty'],
item['price_unit']))
for item in in_moves)
# Issued data
out_moves = Move.read_group([('date', '>=', from_date),
('date', '<', to_date),
('product_id', 'in', products.ids),
('state', '=', 'done'),
('picking_code', '=', 'outgoing')],
['product_uom_qty', 'price_unit'],
['product_id'])
out_move_dict = dict((item['product_id'][0], (item['product_uom_qty'],
item['price_unit']))
for item in out_moves)
report_data = []
for categ in products.categ_id:
report_data.append([categ.display_name])
categ_products = products.filtered(lambda x: x.categ_id == categ)
for product in categ_products:
received_qty = received_price_unit = issued_qty = issued_value = 0
product_id = product.id
# Prepare Opening Data
opening_qty = product_quantities[product_id]['qty_available']
opening_value = round(opening_qty * product.standard_price,
precision_rounding=4)
# Prepare Received data
if in_move_dict.get(product_id):
received_qty = in_move_dict[product_id][0]
received_price_unit = in_move_dict[product_id][1]
received_value = round(received_qty * received_price_unit,
precision_rounding=4)
# prepare Issued Data
if out_move_dict.get(product_id):
issued_qty = out_move_dict[product_id][0]
issued_value = (issued_qty * out_move_dict[product_id][1])
# Prepare Closing Quantity
closing_qty = opening_qty + received_qty - issued_qty
closing_value = opening_value + received_value - issued_value
product_data = [
product.name,
opening_qty,
opening_value,
received_qty,
received_value,
issued_qty,
issued_value,
closing_qty,
closing_value,
]
report_data.append(product_data)
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet()
report_title_style = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': 16,
'bg_color': '#C8EAAB'
})
worksheet.merge_range('C2:F2', 'Datewise Stock Register',
report_title_style)
report_small_title_style = workbook.add_format({
'bold': True,
'font_size': 14
})
worksheet.write(3, 3, ('From %s to %s' % (format_date(
self.env, from_date), format_date(self.env, to_date))),
report_small_title_style)
column_product_style = workbook.add_format({
'bold': True,
'bg_color': '#EEED8A',
'font_size': 12
})
column_received_style = workbook.add_format({
'bold': True,
'bg_color': '#A2D374',
'font_size': 12
})
column_issued_style = workbook.add_format({
'bold': True,
'bg_color': '#F8715F',
'font_size': 12
})
row_categ_style = workbook.add_format({
'bold': True,
'bg_color': '#6B8DE3'
})
# set the width od the column
worksheet.set_column(0, 8, 20)
worksheet.write(6, 0, 'Product', column_product_style)
worksheet.write(6, 1, 'Opening Quantity', column_product_style)
worksheet.write(6, 2, 'Opening Value', column_product_style)
worksheet.write(6, 3, 'Received Quantity', column_received_style)
worksheet.write(6, 4, 'Received Value', column_received_style)
worksheet.write(6, 5, 'Issued Quantity', column_issued_style)
worksheet.write(6, 6, 'Issued Value', column_issued_style)
worksheet.write(6, 7, 'Closing Quantity', column_product_style)
worksheet.write(6, 8, 'Closing Value', column_product_style)
col = 0
row = 7
for line in report_data:
col = 0
if len(line) == 1:
# worksheet.write(row, col, line[0], row_categ_style)
worksheet.merge_range('A%s:I%s' % (row + 1, row + 1), line[0],
row_categ_style)
else:
for l in line:
worksheet.write(row, col, l)
col += 1
row += 1
workbook.close()
xlsx_data = output.getvalue()
self.file_data = base64.encodebytes(xlsx_data)
end_time = fields.datetime.now()
_logger.info("\n\nTOTAL PRINTING TIME IS : %s \n" %
(end_time - start_time))
return {
'type':
'ir.actions.act_url',
'url':
'/web/content/?model={}&id={}&field=file_data&filename={}&download=true'
.format(self._name, self.id, 'StockRegisterReport'),
'target':
'self',
}
def get_xlsx_report(self, data, response):
print("XSSSSSSSSSSSSSSSSSSSSSSSCCCCCCCCCCCCCCCCCCCcc")
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
print("1233333333333333333333333")
main_heading = workbook.add_format({
"bold": 1,
"border": 1,
"align": 'center',
"valign": 'vcenter',
"font_color": 'white',
"bg_color": '548235',
'font_size': '12',
})
main_heading1 = workbook.add_format({
"bold": 1,
"border": 1,
"align": 'right',
"valign": 'vcenter',
'font_size': '11',
})
employee_heading = workbook.add_format({
"bold": 1,
"border": 1,
"valign": 'vcenter',
'font_size': '11',
})
TotalHeading = workbook.add_format({
"bold": 1,
"border": 1,
"bg_color": '548235',
"align": 'center',
"valign": 'vcenter',
'font_size': '14',
})
merge_format = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'font_size': '12',
"font_color": 'white',
'fg_color': '7030a0'
})
main_data = workbook.add_format({
"align": 'center',
"valign": 'vcenter',
'font_size': '8',
})
worksheet = workbook.add_worksheet('Salary Sheet')
print("5555555555555555555555555555")
head = "Customer invoices "
worksheet.write(0, 0, 'Order Ref', employee_heading)
worksheet.write(0, 1, 'Session', employee_heading)
worksheet.write(0, 2, 'Date', employee_heading)
worksheet.write(0, 3, 'Receipt Number', employee_heading)
worksheet.write(0, 4, 'Customer', employee_heading)
worksheet.write(0, 5, 'Cashier', employee_heading)
worksheet.write(0, 6, 'Total', employee_heading)
print("999999999999999999999999999")
partinvoi = self.getPartnerInvoices(data)
print(partinvoi, "parttttttttttttttttttttttttttttttt")
rows = 1
print("0000000000000000000000000000000000000")
print(partinvoi, "parttttttttttttttttttttttttttttttt")
for line in partinvoi:
print(line, "Libeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee")
worksheet.write(rows, 0, '#' + str(line.name) + '.',
employee_heading)
worksheet.write(rows, 1, str(line.session_id.name),
employee_heading)
worksheet.write(rows, 2, str(line.date_order), employee_heading)
worksheet.write(rows, 3, str(line.pos_reference), employee_heading)
worksheet.write(rows, 4, str(line.partner_id.name),
employee_heading)
worksheet.write(rows, 5, str(line.user_id.name), employee_heading)
worksheet.write(rows, 6, str(line.amount_total), employee_heading)
# worksheet.write(rows, 7, str(line.amount_total), employee_heading)
# worksheet.write(rows, 8, str(line.amount_residual), employee_heading)
# worksheet.write(rows, 9, str(line.state), employee_heading)
rows += 1
workbook.close()
print("6666666666666666666666666666666")
output.seek(0)
response.stream.write(output.read())
output.close()
def get_xlsx_report(self, data, response):
# Initialize
#############################################################
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet('Trial Balance')
sheet.set_zoom(95)
sheet_2 = workbook.add_worksheet('Filters')
sheet_2.protect()
# Get record and data
record = self.env['ins.trial.balance'].browse(data.get('id',
[])) or False
filter, account_lines, retained, subtotal = record.get_report_datas()
# Formats
############################################################
sheet.set_column(0, 0, 30)
sheet.set_column(1, 1, 15)
sheet.set_column(2, 2, 15)
sheet.set_column(3, 3, 15)
sheet.set_column(4, 4, 15)
sheet.set_column(5, 5, 15)
sheet.set_column(6, 6, 15)
sheet.set_column(7, 7, 15)
sheet.set_column(8, 8, 15)
sheet.set_column(9, 9, 15)
sheet_2.set_column(0, 0, 35)
sheet_2.set_column(1, 1, 25)
sheet_2.set_column(2, 2, 25)
sheet_2.set_column(3, 3, 25)
sheet_2.set_column(4, 4, 25)
sheet_2.set_column(5, 5, 25)
sheet_2.set_column(6, 6, 25)
sheet.freeze_panes(5, 0)
sheet.set_zoom(80)
sheet.screen_gridlines = False
sheet_2.screen_gridlines = False
sheet_2.protect()
format_title = workbook.add_format({
'bold': True,
'align': 'center',
'font_size': 12,
'font': 'Arial',
})
format_header = workbook.add_format({
'bold': True,
'font_size': 10,
'font': 'Arial',
# 'border': True
})
format_merged_header = workbook.add_format({
'bold': True,
'font_size': 10,
'align': 'center',
'right': True,
'left': True,
'font': 'Arial',
})
format_merged_header_without_border = workbook.add_format({
'bold':
True,
'font_size':
10,
'align':
'center',
'font':
'Arial',
})
content_header = workbook.add_format({
'bold': False,
'font_size': 10,
'font': 'Arial',
})
line_header = workbook.add_format({
'bold': True,
'font_size': 10,
'align': 'right',
'font': 'Arial',
})
line_header_total = workbook.add_format({
'bold': True,
'font_size': 10,
'align': 'right',
'font': 'Arial',
'top': True,
'bottom': True,
})
line_header_left = workbook.add_format({
'bold': True,
'font_size': 10,
'align': 'left',
'font': 'Arial',
})
line_header_left_total = workbook.add_format({
'bold': True,
'font_size': 10,
'align': 'left',
'font': 'Arial',
'top': True,
'bottom': True,
})
line_header_light = workbook.add_format({
'bold': False,
'font_size': 10,
'align': 'right',
'font': 'Arial',
})
line_header_light_total = workbook.add_format({
'bold': False,
'font_size': 10,
'align': 'right',
'font': 'Arial',
'top': True,
'bottom': True,
})
line_header_light_left = workbook.add_format({
'bold': False,
'font_size': 10,
'align': 'left',
'font': 'Arial',
})
line_header_highlight = workbook.add_format({
'bold': True,
'font_size': 10,
'align': 'right',
'font': 'Arial',
})
line_header_light_date = workbook.add_format({
'bold': False,
'font_size': 10,
'align': 'center',
'font': 'Arial',
})
lang = self.env.user.lang
lang_id = self.env['res.lang'].search([('code', '=', lang)])[0]
currency_id = self.env.user.company_id.currency_id
line_header.num_format = currency_id.excel_format
line_header_light.num_format = currency_id.excel_format
line_header_highlight.num_format = currency_id.excel_format
line_header_light_date.num_format = DATE_DICT.get(
lang_id.date_format, 'dd/mm/yyyy')
format_merged_header_without_border.num_format = DATE_DICT.get(
lang_id.date_format, 'dd/mm/yyyy')
# Write data
################################################################
row_pos_2 = 0
row_pos = 0
sheet.merge_range(0, 0, 0, 10,
'Trial Balance' + ' - ' + data['company_id'][1],
format_title)
# Write filters
row_pos_2 += 2
sheet_2.write(row_pos_2, 0, _('Date from'), format_header)
datestring = fields.Date.from_string(str(
filter['date_from'])).strftime(lang_id.date_format)
sheet_2.write(row_pos_2, 1, datestring or '', line_header_light_date)
row_pos_2 += 1
sheet_2.write(row_pos_2, 0, _('Date to'), format_header)
datestring = fields.Date.from_string(str(filter['date_to'])).strftime(
lang_id.date_format)
sheet_2.write(row_pos_2, 1, datestring or '', line_header_light_date)
row_pos_2 += 1
sheet_2.write(row_pos_2, 0, _('Display accounts'), format_header)
sheet_2.write(row_pos_2, 1, filter['display_accounts'], content_header)
# Journals
row_pos_2 += 1
sheet_2.write(row_pos_2, 0, _('Journals'), format_header)
j_list = ', '.join([lt or '' for lt in filter.get('journals')])
sheet_2.write(row_pos_2, 1, j_list, content_header)
# Accounts
row_pos_2 += 1
sheet_2.write(row_pos_2, 0, _('Analytic Accounts'), format_header)
a_list = ', '.join([lt or '' for lt in filter.get('analytics')])
sheet_2.write(row_pos_2, 1, a_list, content_header)
# Write Ledger details
row_pos += 3
sheet.merge_range(row_pos, 1, row_pos, 3, 'Initial Balance',
format_merged_header)
datestring = fields.Date.from_string(str(
filter.get('date_from'))).strftime(lang_id.date_format)
sheet.write(row_pos, 4, datestring,
format_merged_header_without_border)
sheet.write(row_pos, 5, _(' To '), format_merged_header_without_border)
datestring = fields.Date.from_string(str(
filter.get('date_to'))).strftime(lang_id.date_format)
sheet.write(row_pos, 6, datestring,
format_merged_header_without_border)
sheet.merge_range(row_pos, 7, row_pos, 9, 'Ending Balance',
format_merged_header)
row_pos += 1
sheet.write(row_pos, 0, _('Account'), format_header)
sheet.write(row_pos, 1, _('Debit'), format_header)
sheet.write(row_pos, 2, _('Credit'), format_header)
sheet.write(row_pos, 3, _('Balance'), format_header)
sheet.write(row_pos, 4, _('Debit'), format_header)
sheet.write(row_pos, 5, _('Credit'), format_header)
sheet.write(row_pos, 6, _('Balance'), format_header)
sheet.write(row_pos, 7, _('Debit'), format_header)
sheet.write(row_pos, 8, _('Credit'), format_header)
sheet.write(row_pos, 9, _('Balance'), format_header)
if account_lines:
if not filter.get('show_hierarchy'):
for line in account_lines: # Normal lines
row_pos += 1
sheet.write(
row_pos, 0, account_lines[line].get('code') + ' ' +
account_lines[line].get('name'),
line_header_light_left)
sheet.write(
row_pos, 1,
float(account_lines[line].get('initial_debit')),
line_header_light)
sheet.write(
row_pos, 2,
float(account_lines[line].get('initial_credit')),
line_header_light)
sheet.write(
row_pos, 3,
float(account_lines[line].get('initial_balance')),
line_header_highlight)
sheet.write(row_pos, 4,
float(account_lines[line].get('debit')),
line_header_light)
sheet.write(row_pos, 5,
float(account_lines[line].get('credit')),
line_header_light)
sheet.write(row_pos, 6,
float(account_lines[line].get('balance')),
line_header_highlight)
sheet.write(row_pos, 7,
float(account_lines[line].get('ending_debit')),
line_header_light)
sheet.write(
row_pos, 8,
float(account_lines[line].get('ending_credit')),
line_header_light)
sheet.write(
row_pos, 9,
float(account_lines[line].get('ending_balance')),
line_header_highlight)
else:
for line in account_lines: # Normal lines
row_pos += 1
blank_space = ' ' * len(line.get('indent_list'))
if line.get('dummy'):
sheet.write(row_pos, 0, blank_space + line.get('code'),
line_header_light_left)
else:
sheet.write(
row_pos, 0, blank_space + line.get('code') + ' ' +
line.get('name'), line_header_light_left)
sheet.write(row_pos, 1, float(line.get('initial_debit')),
line_header_light)
sheet.write(row_pos, 2, float(line.get('initial_credit')),
line_header_light)
sheet.write(row_pos, 3, float(line.get('initial_balance')),
line_header_highlight)
sheet.write(row_pos, 4, float(line.get('debit')),
line_header_light)
sheet.write(row_pos, 5, float(line.get('credit')),
line_header_light)
sheet.write(row_pos, 6, float(line.get('balance')),
line_header_highlight)
sheet.write(row_pos, 7, float(line.get('ending_debit')),
line_header_light)
sheet.write(row_pos, 8, float(line.get('ending_credit')),
line_header_light)
sheet.write(row_pos, 9, float(line.get('ending_balance')),
line_header_highlight)
if filter.get('strict_range'):
# Retained Earnings line
row_pos += 1
sheet.write(row_pos, 0,
' ' + retained['RETAINED'].get('name'),
line_header_light_left)
sheet.write(row_pos, 1,
float(retained['RETAINED'].get('initial_debit')),
line_header_light)
sheet.write(row_pos, 2,
float(retained['RETAINED'].get('initial_credit')),
line_header_light)
sheet.write(row_pos, 3,
float(retained['RETAINED'].get('initial_balance')),
line_header_highlight)
sheet.write(row_pos, 4,
float(retained['RETAINED'].get('debit')),
line_header_light)
sheet.write(row_pos, 5,
float(retained['RETAINED'].get('credit')),
line_header_light)
sheet.write(row_pos, 6,
float(retained['RETAINED'].get('balance')),
line_header_highlight)
sheet.write(row_pos, 7,
float(retained['RETAINED'].get('ending_debit')),
line_header_light)
sheet.write(row_pos, 8,
float(retained['RETAINED'].get('ending_credit')),
line_header_light)
sheet.write(row_pos, 9,
float(retained['RETAINED'].get('ending_balance')),
line_header_highlight)
# Sub total line
row_pos += 2
sheet.write(
row_pos, 0, subtotal['SUBTOTAL'].get('code') + ' ' +
subtotal['SUBTOTAL'].get('name'), line_header_left_total)
sheet.write(row_pos, 1,
float(subtotal['SUBTOTAL'].get('initial_debit')),
line_header_light_total)
sheet.write(row_pos, 2,
float(subtotal['SUBTOTAL'].get('initial_credit')),
line_header_light_total)
sheet.write(row_pos, 3,
float(subtotal['SUBTOTAL'].get('initial_balance')),
line_header_total)
sheet.write(row_pos, 4, float(subtotal['SUBTOTAL'].get('debit')),
line_header_light_total)
sheet.write(row_pos, 5, float(subtotal['SUBTOTAL'].get('credit')),
line_header_light_total)
sheet.write(row_pos, 6, float(subtotal['SUBTOTAL'].get('balance')),
line_header_total)
sheet.write(row_pos, 7,
float(subtotal['SUBTOTAL'].get('ending_debit')),
line_header_light_total)
sheet.write(row_pos, 8,
float(subtotal['SUBTOTAL'].get('ending_credit')),
line_header_light_total)
sheet.write(row_pos, 9,
float(subtotal['SUBTOTAL'].get('ending_balance')),
line_header_total)
# Close and return
#################################################################
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_dynamic_xlsx_report(self, data, response, report_data, dfr_data):
report_data_main = json.loads(report_data)
output = io.BytesIO()
total = json.loads(dfr_data)
filters = json.loads(data)
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '20px'
})
sub_heading = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '10px',
'border': 1,
'border_color': 'black'
})
txt = workbook.add_format({'font_size': '10px', 'border': 1})
txt_l = workbook.add_format({
'font_size': '10px',
'border': 1,
'bold': True
})
sheet.merge_range(
'A2:D3', self.env.user.company_id.name + ':' + ' Trial Balance',
head)
date_head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '10px'
})
date_style = workbook.add_format({
'align': 'center',
'font_size': '10px'
})
if filters.get('date_from'):
sheet.merge_range('A4:B4', 'From: ' + filters.get('date_from'),
date_head)
if filters.get('date_to'):
sheet.merge_range('C4:D4', 'To: ' + filters.get('date_to'),
date_head)
sheet.merge_range(
'A5:D6',
'Journals: ' + ', '.join([lt or ''
for lt in filters['journals']]) +
' Target Moves: ' + filters.get('target_move'), date_head)
sheet.write('A7', 'Code', sub_heading)
sheet.write('B7', 'Amount', sub_heading)
if filters.get('date_from'):
sheet.write('C7', 'Initial Debit', sub_heading)
sheet.write('D7', 'Initial Credit', sub_heading)
sheet.write('E7', 'Debit', sub_heading)
sheet.write('F7', 'Credit', sub_heading)
else:
sheet.write('C7', 'Debit', sub_heading)
sheet.write('D7', 'Credit', sub_heading)
row = 6
col = 0
sheet.set_column(5, 0, 15)
sheet.set_column(6, 1, 15)
sheet.set_column(7, 2, 26)
if filters.get('date_from'):
sheet.set_column(8, 3, 15)
sheet.set_column(9, 4, 15)
sheet.set_column(10, 5, 15)
sheet.set_column(11, 6, 15)
else:
sheet.set_column(8, 3, 15)
sheet.set_column(9, 4, 15)
for rec_data in report_data_main:
row += 1
sheet.write(row, col, rec_data['code'], txt)
sheet.write(row, col + 1, rec_data['name'], txt)
if filters.get('date_from'):
if rec_data.get('Init_balance'):
sheet.write(row, col + 2,
rec_data['Init_balance']['debit'], txt)
sheet.write(row, col + 3,
rec_data['Init_balance']['credit'], txt)
else:
sheet.write(row, col + 2, 0, txt)
sheet.write(row, col + 3, 0, txt)
sheet.write(row, col + 4, rec_data['debit'], txt)
sheet.write(row, col + 5, rec_data['credit'], txt)
else:
sheet.write(row, col + 2, rec_data['debit'], txt)
sheet.write(row, col + 3, rec_data['credit'], txt)
sheet.write(row + 1, col, 'Total', sub_heading)
if filters.get('date_from'):
sheet.write(row + 1, col + 4, total.get('debit_total'), txt_l)
sheet.write(row + 1, col + 5, total.get('credit_total'), txt_l)
else:
sheet.write(row + 1, col + 2, total.get('debit_total'), txt_l)
sheet.write(row + 1, col + 3, total.get('credit_total'), txt_l)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_dynamic_xlsx_report(self, data, response, report_data, dfr_data):
report_main_data = json.loads(dfr_data)
data = json.loads(data)
report_data = report_main_data.get('report_lines')
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
fetched_data = report_data.get('fetched_data')
account_res = report_data.get('account_res')
journal_res = report_data.get('journal_res')
fetched = report_data.get('fetched')
account_type_id = self.env.ref('account.data_account_type_liquidity').id
currency_symbol = self.env.user.company_id.currency_id.symbol
logged_users = self.env['res.company']._company_default_get('account.account')
sheet = workbook.add_worksheet()
bold = workbook.add_format({'align': 'center',
'bold': True,
'font_size': '10px',
'border': 1})
date = workbook.add_format({'font_size': '10px'})
cell_format = workbook.add_format({'bold': True,
'font_size': '10px'})
head = workbook.add_format({'align': 'center',
'bold': True,
'bg_color': '#D3D3D3',
'font_size': '15px'})
txt = workbook.add_format({'align': 'left',
'font_size': '10px'})
txt_left = workbook.add_format({'align': 'left',
'font_size': '10px',
'border': 1})
txt_center = workbook.add_format({'align': 'center',
'font_size': '10px',
'border': 1})
amount = workbook.add_format({'align': 'right',
'font_size': '10px',
'border': 1})
amount_bold = workbook.add_format({'align': 'right',
'bold': True,
'font_size': '10px',
'border': 1})
txt_bold = workbook.add_format({'align': 'left',
'bold': True,
'font_size': '10px',
'border': 1})
sheet.set_column('C:C', 30, cell_format)
sheet.set_column('D:E', 20, cell_format)
sheet.set_column('F:F', 20, cell_format)
sheet.merge_range('C3:F5', '')
sheet.merge_range('C3:F4', 'CASH FLOW STATEMENTS', head)
sheet.merge_range('C4:F4', '')
sheet.write('C6', "Date From", cell_format)
sheet.write('D6', str(data['date_from']), date)
sheet.write('E6', "Date To", cell_format)
sheet.write('F6', str(data['date_to']), date)
if data.get('levels'):
sheet.write('C7', "Level", cell_format)
sheet.write('D7', data.get("levels"), date)
sheet.write('E7', "Target Moves", cell_format)
sheet.write('F7', data.get("target_move"), date)
sheet.write('C9', 'NAME', bold)
sheet.write('D9', 'CASH IN', bold)
sheet.write('E9', 'CASH OUT', bold)
sheet.write('F9', 'BALANCE', bold)
row_num = 9
col_num = 2
fetched_data_list = fetched_data
account_res_list = account_res
journal_res_list = journal_res
fetched_list = fetched
for i_rec in fetched_data_list:
if data['levels'] == 'summary':
sheet.write(row_num + 1, col_num, str(i_rec['month_part']) + str(int(i_rec['year_part'])), txt_left)
sheet.write(row_num + 1, col_num + 1, str(i_rec['total_debit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 2, str(i_rec['total_credit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 3,
str(i_rec['total_debit'] - i_rec['total_credit']) + str(currency_symbol),
amount)
row_num = row_num + 1
elif data['levels'] == 'consolidated':
sheet.write(row_num + 1, col_num, i_rec['name'], txt_left)
sheet.write(row_num + 1, col_num + 1, str(i_rec['total_debit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 2, str(i_rec['total_credit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 3,
str(i_rec['total_debit'] - i_rec['total_credit']) + str(currency_symbol),
amount)
row_num = row_num + 1
for j_rec in journal_res_list:
if data['levels'] == 'detailed':
for k in fetched_data_list:
if k['name'] == j_rec['account']:
sheet.write(row_num + 1, col_num, str(k['code']) + str(k['name']), txt_bold)
sheet.write(row_num + 1, col_num + 1, str(k['total_debit']) + str(currency_symbol), amount_bold)
sheet.write(row_num + 1, col_num + 2, str(k['total_credit']) + str(currency_symbol), amount_bold)
sheet.write(row_num + 1, col_num + 3,
str(k['total_debit'] - k['total_credit']) + str(currency_symbol), amount_bold)
row_num = row_num + 1
for l_jrec in j_rec['journal_lines']:
sheet.write(row_num + 1, col_num, l_jrec['name'], txt_left)
sheet.write(row_num + 1, col_num + 1, str(l_jrec['total_debit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 2, str(l_jrec['total_credit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 3,
str(l_jrec['total_debit'] - l_jrec['total_credit']) + str(currency_symbol),
amount)
row_num = row_num + 1
for j_rec in account_res_list:
if data['levels'] == 'very':
for k in fetched_data_list:
if k['name'] == j_rec['account']:
sheet.write(row_num + 1, col_num, str(k['code']) + str(k['name']), txt_bold)
sheet.write(row_num + 1, col_num + 1, str(k['total_debit']) + str(currency_symbol), amount_bold)
sheet.write(row_num + 1, col_num + 2, str(k['total_credit']) + str(currency_symbol), amount_bold)
sheet.write(row_num + 1, col_num + 3,
str(k['total_debit'] - k['total_credit']) + str(currency_symbol), amount_bold)
row_num = row_num + 1
for l_jrec in j_rec['journal_lines']:
if l_jrec['account_name'] == j_rec['account']:
sheet.write(row_num + 1, col_num, l_jrec['name'], txt_left)
sheet.write(row_num + 1, col_num + 1, str(l_jrec['total_debit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 2, str(l_jrec['total_credit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 3,
str(l_jrec['total_debit'] - l_jrec['total_credit']) + str(currency_symbol),
amount)
row_num = row_num + 1
for m_rec in j_rec['move_lines']:
if m_rec['name'] == l_jrec['name']:
sheet.write(row_num + 1, col_num, m_rec['move_name'], txt_center)
sheet.write(row_num + 1, col_num + 1, str(m_rec['total_debit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 2, str(m_rec['total_credit']) + str(currency_symbol), amount)
sheet.write(row_num + 1, col_num + 3,
str(m_rec['total_debit'] - m_rec['total_credit']) + str(currency_symbol),
amount)
row_num = row_num + 1
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def do_report(self):
""" Imprimir el reporte, en planilla excel
"""
headers = [
'Cuit Retenido', 'Nombre Retenido', 'Impuesto', 'Monto retenido',
'Nro Factura', 'Nro Comprobante', 'Fecha Emisión'
]
sheet_lines = list()
# obtener los datos y ponerlos en una estructura
for payment in self:
line = list()
cuit = payment.payment_group_id.partner_id.vat
line.append(cuit)
name = payment.payment_group_id.partner_id.name
line.append(name)
tax = payment.tax_withholding_id.name
line.append(tax)
amount = payment.amount
line.append(amount)
try:
payment_group = payment.payment_group_id
invoice = payment_group.matched_move_line_ids[0].move_id
_date = invoice.invoice_date.strftime('%d/%m/%Y')
except Exception as _ex:
raise UserError(
_('La linea %s del pago %s no tiene un comprobante '
'asociado. Posiblemente falte conciliar el comprobante '
'con este pago.') %
(payment.name, payment.payment_group_id.name))
line.append(invoice.name)
line.append(payment.withholding_number)
line.append(payment.payment_date)
sheet_lines.append(line)
# Crear planilla en memoria
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet()
# Escribir los titulos en negrita
bold = workbook.add_format({'bold': True})
for col, header in enumerate(headers):
worksheet.write(0, col, header, bold)
# Escribir los datos
date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})
for row, line in enumerate(sheet_lines):
for col, cell in enumerate(line):
if col == 3:
worksheet.write(row + 1, col, cell, date_format)
else:
worksheet.write(row + 1, col, cell)
workbook.close()
# encode
output.seek(0)
attachment_obj = self.env['ir.attachment']
# crear el attachment
attachment_id = attachment_obj.create({
'name':
'erp.xlsx',
'datas':
base64.b64encode(output.read()),
})
return {
'type': 'ir.actions.act_url',
'url': '/web/content/' + str(attachment_id.id) + '?download=true',
'target': 'new'
}
def get_xlsx(self, response):
output = StringIO.StringIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
report_id = self.get_report_obj()
sheet = workbook.add_worksheet(report_id.get_title())
def_style = workbook.add_format({'font_name': 'Arial'})
title_style2 = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_color': '#A001A2'
})
title_style2.set_font_size(14)
title_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2
})
level_0_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'pattern': 1,
'font_color': '#FFFFFF'
})
level_0_style_left = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'left': 2,
'pattern': 1,
'font_color': '#FFFFFF'
})
level_0_style_right = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'right': 2,
'pattern': 1,
'font_color': '#FFFFFF',
'num_format': '#,##0.00'
})
level_1_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2
})
level_1_style_left = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'left': 2
})
level_1_style_right = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'right': 2,
'num_format': '#,##0.00'
})
level_2_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'top': 2
})
level_2_style_left = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'top': 2,
'left': 2
})
level_2_style_right = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'top': 2,
'right': 2,
'num_format': '#,##0.00'
})
level_3_style = def_style
level_3_style_left = workbook.add_format({
'font_name': 'Arial',
'left': 2
})
level_3_style_right = workbook.add_format({
'font_name': 'Arial',
'right': 2,
'num_format': '#,##0.00'
})
domain_style = workbook.add_format({
'font_name': 'Arial',
'italic': True
})
domain_style_left = workbook.add_format({
'font_name': 'Arial',
'italic': True,
'left': 2
})
domain_style_right = workbook.add_format({
'font_name': 'Arial',
'italic': True,
'right': 2,
'num_format': '#,##0.00'
})
upper_line_style = workbook.add_format({
'font_name': 'Arial',
'top': 2
})
# Set column widhts
sheet.set_column(0, 0, 40)
sheet.set_column(1, 2, 15)
sheet.set_column(3, 7, 10)
sheet.set_column(8, 9, 15)
sheet.set_column(11, 12, 15)
sheet.write(0, 0, self.env.user.company_id.partner_id.name,
title_style2)
sheet.write(
1, 0,
self.env.user.company_id.partner_id._display_address(
without_company=True), title_style2)
sheet.write(3, 0, report_id.get_title(), title_style2)
y_offset = 5
if self.get_report_obj().get_name(
) == 'coa' and self.get_special_date_line_names():
sheet.write(y_offset, 0, '', title_style)
sheet.write(y_offset, 1, '', title_style)
x = 2
for column in self.with_context(
is_xls=True).get_special_date_line_names():
sheet.write(y_offset, x, column, title_style)
sheet.write(y_offset, x + 1, '', title_style)
x += 2
# sheet.write(y_offset, x, '', title_style)
y_offset += 1
x = 1
sheet.write(y_offset, 0, 'Code / Account', title_style)
for column in self.with_context(is_xls=True).get_columns_names():
sheet.write(y_offset, x,
column.replace('<br/>', ' ').replace(' ', ' '),
title_style)
x += 1
y_offset += 1
lines = report_id.with_context(no_format=True,
print_mode=True).get_lines(self)
if self.partner_ids:
filter_line = []
for iline in lines:
if iline.get('id') in self.partner_ids.ids or iline.get(
'partnerid') in self.partner_ids.ids:
filter_line.append(iline)
lines = filter_line
if lines:
max_width = max([len(l['columns']) for l in lines])
for y in range(0, len(lines)):
if lines[y].get('type') == 'partner_id':
lines[y]['columns'] = ['', ''] + lines[y]['columns']
if lines[y].get('level') == 0 and lines[y].get('type') == 'line':
for x in range(0, len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, upper_line_style)
y_offset += 1
style_left = level_0_style_left
style_right = level_0_style_right
style = level_0_style
elif lines[y].get('level') == 1 and lines[y].get('type') == 'line':
for x in range(0, len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, upper_line_style)
y_offset += 1
style_left = level_1_style_left
style_right = level_1_style_right
style = level_1_style
elif lines[y].get('level') == 2:
style_left = level_2_style_left
style_right = level_2_style_right
style = level_2_style
elif lines[y].get('level') == 3:
style_left = level_3_style_left
style_right = level_3_style_right
style = level_3_style
elif lines[y].get('type') != 'line':
style_left = domain_style_left
style_right = domain_style_right
style = domain_style
else:
style = def_style
style_left = def_style
style_right = def_style
sheet.write(y + y_offset, 0, lines[y]['name'], style_left)
for x in xrange(1, max_width - len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, style)
for x in xrange(1, len(lines[y]['columns']) + 1):
if isinstance(lines[y]['columns'][x - 1], tuple):
lines[y]['columns'][x - 1] = lines[y]['columns'][x - 1][0]
if x < len(lines[y]['columns']):
style.set_num_format('#,##0.00')
if lines[y] == 'o_account_reports_domain_total':
style.bold = True
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
lines[y]['columns'][x - 1], style)
else:
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
lines[y]['columns'][x - 1], style_right)
if lines[y]['type'] == 'total' or lines[y].get('level') == 0:
for x in xrange(0, len(lines[0]['columns']) + 1):
sheet.write(y + 1 + y_offset, x, None, upper_line_style)
y_offset += 1
if lines:
for x in xrange(0, max_width + 1):
sheet.write(len(lines) + y_offset, x, None, upper_line_style)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_xlsx(self,
options,
response=None,
workbook=None,
headers=None,
lines=None,
obj=None):
output = io.BytesIO()
if not workbook:
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
# sheet = workbook.add_worksheet(self._get_report_name()[:31])
sheet = workbook.add_worksheet('abc')
date_default_col1_style = workbook.add_format({
'font_name':
'Arial',
'font_size':
12,
'font_color':
'#666666',
'indent':
2,
'num_format':
'yyyy-mm-dd'
})
date_default_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'num_format': 'yyyy-mm-dd'
})
default_col1_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'indent': 2
})
default_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666'
})
title_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2
})
level_0_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 13,
'bottom': 6,
'font_color': '#666666'
})
level_1_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 13,
'bottom': 1,
'font_color': '#666666'
})
level_2_col1_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666',
'indent': 1
})
level_2_col1_total_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666'
})
level_2_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666'
})
level_3_col1_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'indent': 2
})
level_3_col1_total_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666',
'indent': 1
})
level_3_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666'
})
#Set the first column width to 50
report_name_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 14,
'bold': True,
'font_color': 'red',
'align': 'center',
'valign': 'vcenter'
})
title_style_center_bold = workbook.add_format({
'font_name': 'Arial',
'font_size': 10,
'bold': True,
'align': 'center',
'valign': 'vcenter'
})
sheet.set_column(0, 0, 50)
y_offset = 0
if not headers and lines:
headers, lines = self.with_context(
no_format=True, print_mode=True,
prefetch_fields=False)._get_table(options)
# print ('**options**', options)
# print ('headers', headers)
# print ('lines', lines)
# Add headers.
default_style1 = workbook.add_format({
'font_name': 'Arial',
'font_size': 10,
'bold': 0
})
y_offset = 0
x_offset = 0
sheet.fit_to_pages(1, 0)
sheet.set_column(1, 1, 5)
sheet.set_column(2, 3, 10)
sheet.write(y_offset, x_offset, self.env.company.display_name,
default_style1)
y_offset += 1
sheet.write(y_offset, x_offset, self.env.company.street,
default_style1)
y_offset += 1
sheet.write(y_offset, x_offset, obj.name, default_style1)
y_offset += 1
y_offset += 3
merge_from = 'A'
merge_to = 'E'
report_title = ''
report_title = 'LỆNH SẢN XUẤT'
sheet.merge_range(
merge_from + str(y_offset) + ':' + merge_to + str(y_offset),
report_title, report_name_style)
y_offset += 1
for header in headers:
x_offset = 0
for column in header:
column_name_formated = column.get('name', '').replace(
'<br/>', ' ').replace(' ', ' ')
colspan = column.get('colspan', 1)
if colspan == 1:
sheet.write(y_offset, x_offset, column_name_formated,
title_style)
else:
sheet.merge_range(y_offset, x_offset, y_offset,
x_offset + colspan - 1,
column_name_formated, title_style)
x_offset += colspan
y_offset += 1
if options.get('hierarchy'):
lines = self._create_hierarchy(lines, options)
if options.get('selected_column'):
lines = self._sort_lines(lines, options)
# Add lines.
for y in range(0, len(lines)):
level = lines[y].get('level')
if lines[y].get('caret_options'):
style = level_3_style
col1_style = level_3_col1_style
elif level == 0:
y_offset += 1
style = level_0_style
col1_style = style
elif level == 1:
style = level_1_style
col1_style = style
elif level == 2:
style = level_2_style
col1_style = 'total' in lines[y].get('class', '').split(
' ') and level_2_col1_total_style or level_2_col1_style
elif level == 3:
style = level_3_style
col1_style = 'total' in lines[y].get('class', '').split(
' ') and level_3_col1_total_style or level_3_col1_style
else:
style = default_style
col1_style = default_col1_style
#write the first column, with a specific style to manage the indentation
cell_type, cell_value = self._get_cell_type_value(lines[y])
if cell_type == 'date':
sheet.write_datetime(y + y_offset, 0, cell_value,
date_default_col1_style)
else:
sheet.write(y + y_offset, 0, cell_value, col1_style)
#write all the remaining cells
for x in range(1, len(lines[y]['columns']) + 1):
cell_type, cell_value = self._get_cell_type_value(
lines[y]['columns'][x - 1])
if cell_type == 'date':
sheet.write_datetime(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
cell_value, date_default_style)
else:
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1, cell_value,
style)
y_offset += y + 3
sheet.write(y_offset, 3, 'Ngày ... tháng ... năm ...', default_style1)
y_offset += 1
sheet.write(y_offset, 0,
"Người lập biểu Kế toán trưởng ",
title_style_center_bold)
sheet.write(y_offset, 3, "Giám đốc", title_style_center_bold)
# workbook.close()
# output.seek(0)
# generated_file = output.read()
# output.close()
# return generated_file
return workbook
def action_generate_graph(self):
r_model = self.env[self.graph_modelo.model]
filters = []
abc = [
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK',
'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV',
'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG',
'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR',
'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ'
]
if len(self.data_lines) < 1 or len(self.data_lines) >= len(abc):
raise exceptions.ValidationError(
"Se ha superado el limite de datos: " + len(abc))
#Read Filters
for filt in self.filter_lines:
if filt.data_1.name:
value = filt.searchvalue
condition = filt.condition
if condition == 'False' or condition == 'True':
condition = ('!=' if condition == 'True' else '=')
value = False
elif condition == 'inicia':
condition = 'ilike'
value = str(value) + '%'
elif condition == 'in' and filt.searchvalue and filt.searchvalue2:
value = []
value.append(filt.searchvalue)
if filt.searchvalue2: value.append(filt.searchvalue2)
if filt.searchvalue3: value.append(filt.searchvalue3)
if filt.searchvalue4: value.append(filt.searchvalue4)
if filt.searchvalue5: value.append(filt.searchvalue5)
f = filt.data_1.name + (
'.' + filt.data_2.name if filt.data_2 else
'') + ('.' + filt.data_3.name if filt.data_3 else '') + (
'.' + filt.data_4.name if filt.data_4 else
'') + ('.' + filt.data_5.name if filt.data_5 else '')
if not f or not condition:
raise exceptions.ValidationError(
"Complete los datos de todos los filtros.")
filters.append([f, condition, value])
else:
raise exceptions.ValidationError(
"Complete los datos de todos los filtros.")
#Searching data using filters
result = False
if filters:
result = r_model.search(filters)
if not result:
raise exceptions.ValidationError(
"No se encontro ningun resultado con los filtros datos.")
else:
raise exceptions.ValidationError(
"No se ha detectado ningun filtro.")
for dt in self.data_lines:
if not dt.name or not dt.data_1.name:
raise exceptions.ValidationError(
"Favor llene el nombre y los datos que desea tener en el Grafico."
)
graph = []
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
cell_format = workbook.add_format({'font_size': '12px'})
head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '20px'
})
txt = workbook.add_format({'font_size': '10px'})
row = 1
for d in self.data_lines:
if d.secuence > 0 and d.secuence < len(abc):
sheet.write(abc[d.secuence - 1] + '1', d.name, head)
ylabel = ""
xlabel = ""
xdata = []
ydata = []
data = False
for r in result:
row += 1
idx = False
idy = False
for dt in self.data_lines:
if dt.secuence > 0 and dt.secuence < len(abc):
data = False
if dt.data_5.name:
data = r[dt.data_1.name][dt.data_2.name][
dt.data_3.name][dt.data_4.name][dt.data_5.name]
elif dt.data_4.name:
data = r[dt.data_1.name][dt.data_2.name][
dt.data_3.name][dt.data_4.name]
elif dt.data_3.name:
data = r[dt.data_1.name][dt.data_2.name][
dt.data_3.name]
elif dt.data_2.name:
data = r[dt.data_1.name][dt.data_2.name]
elif dt.data_1.name:
data = r[dt.data_1.name]
if data:
sheet.write(abc[dt.secuence - 1] + str(row), data, txt)
if dt.graph_data_type and dt.graph_data_type == 'xdata' and not idx:
if not xlabel: xlabel = dt.name
if data not in xdata:
xdata.append(data)
ydata.append(0)
idx = xdata.index(data)
if dt.graph_data_type and dt.graph_data_type == 'ydata' and idx >= 0 and not idy:
try:
if dt.operation != 'count':
data = float(data)
except ValueError:
raise exceptions.ValidationError(
"Solo son aceptados valores numericos para el eje Y."
)
if not ylabel: ylabel = dt.name
if dt.operation == 'sum': ydata[idx] += data
elif dt.operation == 'count': ydata[idx] += 1
elif dt.operation == 'rest': ydata[idx] -= data
else:
raise exceptions.ValidationError(
"Operacion no aceptada.")
idy = True
workbook.close()
output.seek(0)
self.graph_report = base64.encodestring(output.getvalue())
self.graph_report_filename = self.name + "_" + str(
date.today()) + ".xlsx"
output.close()
self.graph_graph = self.create_plot(self.name, ylabel, xlabel, xdata,
ydata)
self.graph_graph_filename = self.name + "_" + str(
date.today()) + ".png"
def get_xlsx_report(self, data, response):
value = []
model_id = data['model_id']
date_from = data['date_from']
date_to = data['date_to']
customer = data['customer']
today = fields.Date.today()
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
cell_format = workbook.add_format({'font_size': '12px'})
cell2_format = workbook.add_format({
'align': 'left',
'font_size': '14px'
})
head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '25px'
})
heading = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '11px'
})
txt = workbook.add_format({'font_size': '10px'})
subhead = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '11px'
})
date = workbook.add_format({
'num_format': 'dd/mm/yyyy',
'font_size': '10px',
'align': 'left'
})
sheet.merge_range('F4:S5', 'Travels Booking Report', head)
if customer:
sheet.merge_range('L7:M7', customer, heading)
if date_from:
sheet.write('G8', 'From Date:', txt)
sheet.write('H8', data['date_from'], date)
if date_to:
sheet.write('P8', 'To Date:', txt)
sheet.write('Q8', data['date_to'], date)
sheet.write('F10', 'SL No', subhead)
sheet.merge_range('G10:I10', 'Source Location', subhead)
sheet.merge_range('J10:L10', 'Destination Location', subhead)
sheet.merge_range('N10:P10', 'Vehicle Name', subhead)
sheet.merge_range('R10:S10', 'State', subhead)
query_start = """SELECT DISTINCT ON (booking.id) booking.id, customer.name,
location.locations_name AS source_location, locations.locations_name AS
destination_location, vehicle.name AS vehicle, booking.state AS state FROM
travels_booking AS booking INNER JOIN res_partner AS customer ON
booking.customer_id = customer.id INNER JOIN travels_locations AS
location ON booking.source_location = location.id INNER JOIN
travels_locations AS locations ON booking.destination_location =
locations.id LEFT JOIN vehicle_types AS vehicle ON
booking.vehicle_id = vehicle.id"""
if customer and date_from and date_to:
query = query_start + """ WHERE customer.name = ('%s') AND
CAST(booking.booking_date AS DATE) BETWEEN CAST('%s' AS
DATE) AND CAST('%s' AS DATE) AND state NOT IN
('draft')""" % (customer, date_from, date_to)
elif not customer and date_from and date_to:
query = query_start + """ WHERE CAST(booking.booking_date AS DATE)
BETWEEN CAST('%s' AS DATE) AND CAST('%s' AS DATE) AND
state NOT IN ('draft')""" % (date_from, date_to)
elif customer and date_from and date_to:
query = query_start + """ WHERE customer.name = ('%s') AND CAST
(booking.booking_date AS DATE) BETWEEN
CAST('%s' AS DATE) AND CAST('%s' AS DATE) AND
state NOT IN (''draft'')""" % (customer, today,
date_to)
elif customer and date_from and not date_to:
query = query_start + """ WHERE customer.name = ('%s') AND
CAST(booking.booking_date AS DATE) BETWEEN CAST('%s' AS
DATE) AND CAST('%s' AS DATE) AND state NOT IN
('draft')""" % (customer, date_from, today)
elif not customer and date_from and not date_to:
query = query_start + """ WHERE CAST(booking.booking_date AS DATE)
BETWEEN CAST('%s' AS DATE) AND CAST('%s' AS DATE) AND
state NOT IN ('draft')""" % (date_from,
today)
elif customer:
query = query_start + """ WHERE customer.name = ('%s') AND state
NOT IN ('draft')""" % customer
else:
query = query_start + """ WHERE state NOT IN ('draft')"""
value.append(model_id)
self._cr.execute(query, value)
record = self._cr.dictfetchall()
print(record)
row = 10
col = 4
index = 1
for rec in record:
sheet.write(row, col + 1, index, cell2_format)
sheet.merge_range(row, col + 2, row, col + 3,
rec['source_location'], cell_format)
sheet.merge_range(row, col + 5, row, col + 7,
rec['destination_location'], cell_format)
sheet.merge_range(row, col + 9, row, col + 11, rec['vehicle'],
cell_format)
sheet.merge_range(row, col + 13, row, col + 14, rec['state'],
cell_format)
row += 1
index += 1
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_xlsx(self, options, response):
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet(self.get_report_name()[:31])
def_style = workbook.add_format({'font_name': 'Arial'})
title_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2
})
level_0_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'pattern': 1,
'font_color': '#FFFFFF'
})
level_0_style_left = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'left': 2,
'pattern': 1,
'font_color': '#FFFFFF'
})
level_0_style_right = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'right': 2,
'pattern': 1,
'font_color': '#FFFFFF'
})
level_1_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2
})
level_1_style_left = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'left': 2
})
level_1_style_right = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2,
'top': 2,
'right': 2
})
level_2_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'top': 2
})
level_2_style_left = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'top': 2,
'left': 2
})
level_2_style_right = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'top': 2,
'right': 2
})
level_3_style = def_style
level_3_style_left = workbook.add_format({
'font_name': 'Arial',
'left': 2
})
level_3_style_right = workbook.add_format({
'font_name': 'Arial',
'right': 2
})
upper_line_style = workbook.add_format({
'font_name': 'Arial',
'top': 2
})
sheet.set_column(0, 0, 15)
sheet.write(0, 0, '', title_style)
y_offset = 0
x = 0
for column in self.get_columns_name(options):
sheet.write(
y_offset, x,
column.get('name', '').replace('<br/>',
' ').replace(' ', ' '),
title_style)
x += 1
y_offset += 1
ctx = self.set_context(options)
ctx.update({'no_format': True, 'print_mode': True})
lines = self.with_context(ctx).get_lines(options)
if lines:
max_width = max([len(l['columns']) for l in lines])
for y in range(0, len(lines)):
if lines[y].get('level') == 0:
for x in range(0, len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, upper_line_style)
y_offset += 1
style_left = level_0_style_left
style_right = level_0_style_right
style = level_0_style
elif lines[y].get('level') == 1:
for x in range(0, len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, upper_line_style)
y_offset += 1
style_left = level_1_style_left
style_right = level_1_style_right
style = level_1_style
elif lines[y].get('level') == 2:
style_left = level_2_style_left
style_right = level_2_style_right
style = level_2_style
elif lines[y].get('level') == 3:
style_left = level_3_style_left
style_right = level_3_style_right
style = level_3_style
else:
style = def_style
style_left = def_style
style_right = def_style
sheet.write(y + y_offset, 0, lines[y]['name'], style_left)
for x in range(1, max_width - len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, style)
for x in range(1, len(lines[y]['columns']) + 1):
if x < len(lines[y]['columns']):
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
lines[y]['columns'][x - 1].get('name',
''), style)
else:
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
lines[y]['columns'][x - 1].get('name', ''),
style_right)
if 'total' in lines[y].get('class',
'') or lines[y].get('level') == 0:
for x in range(len(lines[0]['columns']) + 1):
sheet.write(y + 1 + y_offset, x, None, upper_line_style)
y_offset += 1
if lines:
for x in range(max_width + 1):
sheet.write(len(lines) + y_offset, x, None, upper_line_style)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_xlsx(self, options, response):
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet(self.get_report_name()[:31])
def_style = workbook.add_format({'font_name': 'Arial'})
title_style = workbook.add_format({'font_name': 'Arial', 'bold': True, 'bottom': 2})
super_col_style = workbook.add_format({'font_name': 'Arial', 'bold': True, 'align': 'center'})
level_0_style = workbook.add_format({'font_name': 'Arial', 'bold': True, 'bottom': 2, 'top': 2, 'pattern': 1, 'font_color': '#FFFFFF'})
level_0_style_left = workbook.add_format({'font_name': 'Arial', 'bold': True, 'bottom': 2, 'top': 2, 'left': 2, 'pattern': 1, 'font_color': '#FFFFFF'})
level_0_style_right = workbook.add_format({'font_name': 'Arial', 'bold': True, 'bottom': 2, 'top': 2, 'right': 2, 'pattern': 1, 'font_color': '#FFFFFF'})
level_1_style = workbook.add_format({'font_name': 'Arial', 'bold': True, 'bottom': 2, 'top': 2})
level_1_style_left = workbook.add_format({'font_name': 'Arial', 'bold': True, 'bottom': 2, 'top': 2, 'left': 2})
level_1_style_right = workbook.add_format({'font_name': 'Arial', 'bold': True, 'bottom': 2, 'top': 2, 'right': 2})
level_2_style = workbook.add_format({'font_name': 'Arial', 'bold': True, 'top': 2})
level_2_style_left = workbook.add_format({'font_name': 'Arial', 'bold': True, 'top': 2, 'left': 2})
level_2_style_right = workbook.add_format({'font_name': 'Arial', 'bold': True, 'top': 2, 'right': 2})
level_3_style = def_style
level_3_style_left = workbook.add_format({'font_name': 'Arial', 'left': 2})
level_3_style_right = workbook.add_format({'font_name': 'Arial', 'right': 2})
domain_style = workbook.add_format({'font_name': 'Arial', 'italic': True})
domain_style_left = workbook.add_format({'font_name': 'Arial', 'italic': True, 'left': 2})
domain_style_right = workbook.add_format({'font_name': 'Arial', 'italic': True, 'right': 2})
upper_line_style = workbook.add_format({'font_name': 'Arial', 'top': 2})
sheet.set_column(0, 0, 15) # Set the first column width to 15
super_columns = self._get_super_columns(options)
y_offset = bool(super_columns.get('columns')) and 1 or 0
sheet.write(y_offset, 0, '', title_style)
# Todo in master: Try to put this logic elsewhere
x = super_columns.get('x_offset', 0)
for super_col in super_columns.get('columns', []):
cell_content = super_col.get('string', '').replace('<br/>', ' ').replace(' ', ' ')
x_merge = super_columns.get('merge')
if x_merge and x_merge > 1:
sheet.merge_range(0, x, 0, x + (x_merge - 1), cell_content, super_col_style)
x += x_merge
else:
sheet.write(0, x, cell_content, super_col_style)
x += 1
x = 0
for column in self.get_columns_name(options):
sheet.write(y_offset, x, column.get('name', '').replace('<br/>', ' ').replace(' ', ' '), title_style)
x += 1
y_offset += 1
ctx = self.set_context(options)
ctx.update({'no_format':True, 'print_mode':True})
lines = self.with_context(ctx).get_lines(options)
if options.get('hierarchy'):
lines = self.create_hierarchy(lines)
if lines:
max_width = max([len(l['columns']) for l in lines])
for y in range(0, len(lines)):
if lines[y].get('level') == 0:
for x in range(0, len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, upper_line_style)
y_offset += 1
style_left = level_0_style_left
style_right = level_0_style_right
style = level_0_style
elif lines[y].get('level') == 1:
for x in range(0, len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, upper_line_style)
y_offset += 1
style_left = level_1_style_left
style_right = level_1_style_right
style = level_1_style
elif lines[y].get('level') == 2:
style_left = level_2_style_left
style_right = level_2_style_right
style = level_2_style
elif lines[y].get('level') == 3:
style_left = level_3_style_left
style_right = level_3_style_right
style = level_3_style
# elif lines[y].get('type') != 'line':
# style_left = domain_style_left
# style_right = domain_style_right
# style = domain_style
else:
style = def_style
style_left = def_style
style_right = def_style
sheet.write(y + y_offset, 0, lines[y]['name'], style_left)
for x in range(1, max_width - len(lines[y]['columns']) + 1):
sheet.write(y + y_offset, x, None, style)
for x in range(1, len(lines[y]['columns']) + 1):
# if isinstance(lines[y]['columns'][x - 1], tuple):
# lines[y]['columns'][x - 1] = lines[y]['columns'][x - 1][0]
if x < len(lines[y]['columns']):
sheet.write(y + y_offset, x + lines[y].get('colspan', 1) - 1, lines[y]['columns'][x - 1].get('name', ''), style)
else:
sheet.write(y + y_offset, x + lines[y].get('colspan', 1) - 1, lines[y]['columns'][x - 1].get('name', ''), style_right)
if 'total' in lines[y].get('class', '') or lines[y].get('level') == 0:
for x in range(len(lines[0]['columns']) + 1):
sheet.write(y + 1 + y_offset, x, None, upper_line_style)
y_offset += 1
if lines:
for x in range(max_width + 1):
sheet.write(len(lines) + y_offset, x, None, upper_line_style)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_xlsx(self, options, response):
report = self.env['payroll.wage.history.excel.report'].browse(
int(options['report_id']))
# ctx = self.set_context(options)
ctx = {}
ctx.update({
'no_format': True,
'print_mode': True,
'prefetch_fields': False
})
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
# report_name_new = self.get_report_name_new(options)#[:31]
report_name_new = 'Wage History Summary'
sheet = workbook.add_worksheet(report_name_new)
header_columns = self.with_context(ctx).get_columns_name(options)
# len_header_columns = len(header_columns)
style_xl = self.get_style(workbook)
style_xl.update(self.get_style_update(workbook))
###############
# self.set_col_width(sheet)
# đưa report_name vào đây luôn
y_offset = 0
# super_columns_list = self._super_columns_list( workbook, options, ctx, report_name_new, len_header_columns)
# y_offset = self._ndt_write_super_columns(workbook, super_columns_list, sheet, y_offset)
y_offset = self.write_header(sheet, y_offset, header_columns, style_xl)
# y_offset +=1
# x = 0
# for column in header_columns: # ghi header
# sheet.write(y_offset, x, column.get('name', '').replace('<br/>', ' ').replace(' ', ' '), title_style)
# cw = column.get('cw', self.default_cw)
# if cw:
# sheet.set_column(x, x, cw)
# x += 1
domain = [
('create_date', '>=', report.from_date),
('create_date', '<=', report.to_date),
]
if len(report.employee_ids.ids) > 0:
domain.append(('employee_id', 'in', report.employee_ids.ids))
if report.department_id:
domain.append(('department_id', '=', report.department_id.id))
if report.job_id:
domain.append(('job_id', '=', report.job_id.id))
can_read = self.env['payroll.wage.history'].check_access_rights('read')
if not can_read:
raise UserError(
_('You are not authorized to read Payroll Wage History Report')
)
query = self.env['payroll.wage.history']._where_calc(domain)
self.env['payroll.wage.history']._apply_ir_rules(query, 'read')
_, where, where_params = query.get_sql()
where = where.replace('payroll_wage_history', 'p')
if where:
where = 'and %(where)s' % {'where': where}
order_by = ''
if report.order_by:
order_by = 'order by %(order_by)s' % {'order_by': report.order_by}
sql_string = """with tmp1 as (
select p.*, e.name as employee_name, hd.name as department_name, j.name as job_title
from payroll_wage_history p
inner join hr_employee e on e.id = p.employee_id
inner join hr_department hd on hd.id = p.department_id
inner join hr_job j on j.id = p.job_id
where e.active = true
%(user_where_clause)s
%(order_by)s
)
SELECT
jsonb_object_agg(key, value) as result
FROM (
SELECT
jsonb_build_object(department_name, jsonb_object_agg(key, value)) as departments
FROM (
SELECT
case department_name when NULL then 'undefined' else department_name end,
jsonb_build_object(job_title, jsonb_agg(json_build_object(
'employee_name', employee_name,
'revision', revision,
'current_wage', current_wage,
'previous_wage', previous_wage,
'raise', percentage,
'effective_months', date_part('month', (SELECT current_timestamp)) - effective_month
))) AS job_title
FROM tmp1
GROUP BY department_name, job_title
) s,
jsonb_each(job_title)
GROUP BY department_name
) s,
jsonb_each(departments)""" % {
'user_where_clause': where,
'order_by': order_by
}
self.env.cr.execute(sql_string, tuple(where_params))
result = self.env.cr.dictfetchall()
departments = result[0]['result']
row = 0
for department in departments.keys():
row = row + 1
sheet.write(row, 0, department)
jobs = departments[department]
for job in jobs.keys():
row = row + 1
sheet.write(row, 1, job)
employees = jobs[job]
for employee in employees:
row = row + 1
sheet.write(row, 2, employee['employee_name'])
sheet.write(row, 3, employee['current_wage'])
sheet.write(row, 4, employee['previous_wage'])
sheet.write(row, 5, employee['raise'])
sheet.write(row, 6, employee['effective_months'])
workbook.close()
output.seek(0)
# return output
response.stream.write(output.read())
output.close()
def get_xlsx_report(self, data, response):
report_obj = self.env['beban.administrasi.report'].search(
[('id', '=', data['res_id'])], limit=1)
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
cell_format = workbook.add_format({'font_size': '10pt'})
curr_row = 6
curr_col = 1
head = workbook.add_format({
'align': 'center',
'valign': 'vcenter',
'bold': True,
'font_size': '10pt'
})
sub_head = workbook.add_format({
'align': 'center',
'valign': 'vcenter',
'bold': True,
'font_size': '8pt'
})
table_head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '10pt',
'top': 1,
'bottom': 1,
'left': 1,
'right': 1
})
number_row_format = workbook.add_format({
'num_format': '#,##0.00',
'top': 1,
'bottom': 1,
'left': 1,
'right': 1
})
total_number_row_format = workbook.add_format({
'num_format': '#,##0.00',
'bold': True,
'top': 1,
'bottom': 1,
'left': 1,
'right': 1
})
ref_col_width = 10 # 10
desc_col_width = 10 # 80
this_month_col_width = 10 # 25
to_this_month_col_width = 10 # 25
# txt = workbook.add_format({'font_size': '10px'})
# sheet.merge_range('B2:I3', 'EXCEL REPORT', head)
sheet.merge_range('B2:E2', self.env.user.company_id.name, sub_head)
sheet.merge_range('B3:E3', 'Beban Administrasi', head)
sheet.merge_range('B4:E4', data['date_from'] + ' - ' + data['date_to'],
sub_head)
sheet.write('B6', 'Ref', table_head)
sheet.write('C6', 'Uraian', table_head)
sheet.write('D6', 'Bulan ini', table_head)
sheet.write('E6', 'S/d. bulan ini', table_head)
# table content
# BEBAN ADMINISTRASI
accounts = self.get_accounts(
'accounting_custom_report_mod.accounting_custom_beban_administrasi_report'
)
for acc in accounts:
sheet.write(curr_row, curr_col, acc.code, number_row_format)
sheet.write(curr_row, curr_col + 1, acc.name, number_row_format)
ml = report_obj.account_move_lines.filtered(
lambda x: x.account_id.id == acc.id)
to_this_month_ml = report_obj.to_this_month_move_lines.filtered(
lambda x: x.account_id.id == acc.id)
ml_balance = sum(ml.mapped('balance'))
if ml_balance == 0:
ml_balance = ''
sheet.write(curr_row, curr_col + 2, ml_balance, number_row_format)
tth_ml = sum(to_this_month_ml.mapped('balance'))
if tth_ml == 0:
tth_ml = ''
sheet.write(curr_row, curr_col + 3, tth_ml, number_row_format)
curr_row = curr_row + 1
# getting column width
if len(str(acc.code)) > ref_col_width:
ref_col_width = len(str(acc.code)) + 5
if len(str(acc.name)) > desc_col_width:
desc_col_width = len(str(acc.name)) + 5
if ml_balance != '':
ml_balance_frmt = "{:,.2f}".format(float(ml_balance))
if len(ml_balance_frmt) > this_month_col_width:
this_month_col_width = len(ml_balance_frmt) + 5
if tth_ml != '':
tth_ml_frmt = "{:,.2f}".format(float(tth_ml))
if len(tth_ml_frmt) > to_this_month_col_width:
to_this_month_col_width = len(tth_ml_frmt) + 5
# Total
sheet.merge_range(curr_row, 1, curr_row, curr_col + 1,
'Jumlah Beban Administrasi', table_head)
sheet.write(curr_row, curr_col + 2,
sum(report_obj.account_move_lines.mapped('balance')),
total_number_row_format)
sheet.write(curr_row, curr_col + 3,
sum(report_obj.to_this_month_move_lines.mapped('balance')),
total_number_row_format)
curr_row = curr_row + 1
# BEBAN PENYUSUTAAN
penyusutan_accounts = self.get_accounts(
'accounting_custom_report_mod.accounting_custom_beban_penyusutan_report'
)
if penyusutan_accounts:
for acc in penyusutan_accounts:
sheet.write(curr_row, curr_col, acc.code, number_row_format)
sheet.write(curr_row, curr_col + 1, acc.name,
number_row_format)
current_period_move_lines = report_obj.get_current_period_move_lines(
penyusutan_accounts).filtered(
lambda x: x.account_id.id == acc.id)
this_year_move_lines = report_obj.get_this_year_move_lines(
penyusutan_accounts).filtered(
lambda x: x.account_id.id == acc.id)
ml_balance = sum(current_period_move_lines.mapped('balance'))
if ml_balance == 0:
ml_balance = ''
sheet.write(curr_row, curr_col + 2, ml_balance,
number_row_format)
tth_ml = sum(this_year_move_lines.mapped('balance'))
if tth_ml == 0:
tth_ml = ''
sheet.write(curr_row, curr_col + 3, tth_ml, number_row_format)
curr_row = curr_row + 1
# getting column width
if len(str(acc.code)) > ref_col_width:
ref_col_width = len(str(acc.code)) + 5
if len(str(acc.name)) > desc_col_width:
desc_col_width = len(str(acc.name)) + 5
if ml_balance != '':
ml_balance_frmt = "{:,.2f}".format(float(ml_balance))
if len(ml_balance_frmt) > this_month_col_width:
this_month_col_width = len(ml_balance_frmt) + 5
if tth_ml != '':
tth_ml_frmt = "{:,.2f}".format(float(tth_ml))
if len(tth_ml_frmt) > to_this_month_col_width:
to_this_month_col_width = len(tth_ml_frmt) + 5
# Total Beban Bersih
sheet.merge_range(curr_row, 1, curr_row, curr_col + 1,
'Jumlah Beban Administrasi Bersih', table_head)
sheet.write(
curr_row, curr_col + 2,
sum(report_obj.account_move_lines.mapped('balance')) + sum(
report_obj.get_current_period_move_lines(
penyusutan_accounts).mapped('balance')),
total_number_row_format)
sheet.write(
curr_row, curr_col + 3,
sum(report_obj.to_this_month_move_lines.mapped('balance')) +
+sum(
report_obj.get_this_year_move_lines(
penyusutan_accounts).mapped('balance')),
total_number_row_format)
# set column width
sheet.set_column(0, 0, 3)
sheet.set_column(1, 1, ref_col_width)
sheet.set_column(2, 2, desc_col_width)
sheet.set_column(3, 3, this_month_col_width)
sheet.set_column(4, 4, to_this_month_col_width)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_xlsx(self, options, response):
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet(self._get_report_name()[:31])
date_default_col1_style = workbook.add_format({
'font_name':
'Arial',
'font_size':
12,
'font_color':
'#666666',
'indent':
2,
'num_format':
'yyyy-mm-dd'
})
date_default_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'num_format': 'yyyy-mm-dd'
})
default_col1_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'indent': 2
})
default_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666'
})
title_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'bottom': 2
})
super_col_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'align': 'center'
})
level_0_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 13,
'bottom': 6,
'font_color': '#666666'
})
level_1_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 13,
'bottom': 1,
'font_color': '#666666'
})
level_2_col1_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666',
'indent': 1
})
level_2_col1_total_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666'
})
level_2_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666'
})
level_3_col1_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666',
'indent': 2
})
level_3_col1_total_style = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'font_size': 12,
'font_color': '#666666',
'indent': 1
})
level_3_style = workbook.add_format({
'font_name': 'Arial',
'font_size': 12,
'font_color': '#666666'
})
# Set the first column width to 50
sheet.set_column(0, 0, 50)
super_columns = self._get_super_columns(options)
y_offset = bool(super_columns.get('columns')) and 1 or 0
sheet.write(y_offset, 0, '', title_style)
is_profit = False
profit_losse_action = self.env.ref(
'account_reports.account_financial_report_profitandloss0')
if profit_losse_action and profit_losse_action.id == self.id:
is_profit = True
# Todo in master: Try to put this logic elsewhere
x = super_columns.get('x_offset', 0)
for super_col in super_columns.get('columns', []):
cell_content = super_col.get('string',
'').replace('<br/>', ' ').replace(
' ', ' ')
x_merge = super_columns.get('merge')
if x_merge and x_merge > 1:
sheet.merge_range(0, x, 0, x + (x_merge - 1), cell_content,
super_col_style)
x += x_merge
else:
sheet.write(0, x, cell_content, super_col_style)
x += 1
ctx = self._set_context(options)
ctx.update({'is_profit': is_profit})
header = self.get_header(options)
if len(header) > 0:
# Below Code to print the On-Screen Report Header Portion
merge_format = workbook.add_format({
'bold': True,
'align': 'center',
'valign': 'vcenter',
})
cell_left_bold_fmt = workbook.add_format({
'font_name': 'Arial',
'bold': True,
'align': 'left'
})
cell_left_fmt = workbook.add_format({
'font_name': 'Arial',
'align': 'left'
})
comapny_name = self.env.user.company_id and \
self.env.user.company_id.name or ''
sheet.merge_range(0, 0, 1,
len(header[0]) - 1, comapny_name, merge_format)
sheet.merge_range(2, 0, 2,
len(header[0]) - 1, self._get_report_name(),
merge_format)
current_dt = datetime.datetime.now().strftime(DTF)
tz = pytz.timezone(self._context.get('tz', 'Asia/Calcutta'))
current_dt = pytz.timezone('UTC').localize(
datetime.datetime.strptime(current_dt, DTF)).\
astimezone(tz).strftime(DTF)
sheet.set_column(3, 2, 12)
sheet.set_column(3, 3, 12)
sheet.write(3, 3, "Printing Date:", cell_left_bold_fmt)
sheet.set_column(3, 4, 15)
sheet.write(3, 4, current_dt, cell_left_fmt)
# sheet.set_row(0, 25)
# sheet.set_row(1, 25)
# sheet.set_row(2, 25)
for row in self.with_context({
'is_profit': is_profit
}).get_header(options):
y_offset = y_offset + 5
x = 0
for column in row:
colspan = column.get('colspan', 1)
header_label = column.get('name',
'').replace('<br/>', ' ').replace(
' ', ' ')
if colspan == 1:
sheet.write(y_offset, x, header_label, title_style)
else:
sheet.merge_range(y_offset, x, y_offset, x + colspan - 1,
header_label, title_style)
x += colspan
y_offset += 1
options['is_profit'] = is_profit
ctx = self._set_context(options)
ctx.update({
'no_format': True,
'print_mode': True,
'is_profit': is_profit
})
lines = self.with_context(ctx)._get_lines(options)
if options.get('hierarchy'):
lines = self._create_hierarchy(lines)
# write all data rows
for y in range(0, len(lines)):
level = lines[y].get('level')
if lines[y].get('caret_options'):
style = level_3_style
col1_style = level_3_col1_style
elif level == 0:
y_offset += 1
style = level_0_style
col1_style = style
elif level == 1:
style = level_1_style
col1_style = style
elif level == 2:
style = level_2_style
col1_style = 'total' in lines[y].get('class', '').split(
' ') and level_2_col1_total_style or level_2_col1_style
elif level == 3:
style = level_3_style
col1_style = 'total' in lines[y].get('class', '').split(
' ') and level_3_col1_total_style or level_3_col1_style
else:
style = default_style
col1_style = default_col1_style
if 'date' in lines[y].get('class', ''):
# write the dates with a specific format to avoid them being casted as floats in the XLSX
if isinstance(lines[y]['name'],
(datetime.date, datetime.datetime)):
sheet.write_datetime(y + y_offset, 0, lines[y]['name'],
date_default_col1_style)
else:
sheet.write(y + y_offset, 0, lines[y]['name'],
date_default_col1_style)
else:
# write the first column, with a specific style to manage the indentation
sheet.write(y + y_offset, 0, lines[y]['name'], col1_style)
# write all the remaining cells
for x in range(1, len(lines[y]['columns']) + 1):
this_cell_style = style
if 'date' in lines[y]['columns'][x - 1].get('class', ''):
# write the dates with a specific format to avoid them being casted as floats in the XLSX
this_cell_style = date_default_style
if isinstance(lines[y]['columns'][x - 1].get('name', ''),
(datetime.date, datetime.datetime)):
sheet.write_datetime(
y + y_offset, x + lines[y].get('colspan', 1) - 1,
lines[y]['columns'][x - 1].get('name', ''),
this_cell_style)
else:
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
lines[y]['columns'][x - 1].get('name', ''),
this_cell_style)
else:
sheet.write(y + y_offset,
x + lines[y].get('colspan', 1) - 1,
lines[y]['columns'][x - 1].get('name', ''),
this_cell_style)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()
def get_dynamic_xlsx_report(self, options, response, report_data,
dfr_data):
i_data = str(report_data)
filters = json.loads(options)
j_data = dfr_data
rl_data = json.loads(j_data)
output = io.BytesIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
sheet = workbook.add_worksheet()
head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '20px'
})
sub_heading = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '10px',
'border': 1,
'border_color': 'black'
})
side_heading_main = workbook.add_format({
'align': 'left',
'bold': True,
'font_size': '10px',
'border': 1,
'border_color': 'black'
})
side_heading_sub = workbook.add_format({
'align': 'left',
'bold': True,
'font_size': '10px',
'border': 1,
'border_color': 'black'
})
side_heading_sub.set_indent(1)
txt = workbook.add_format({'font_size': '10px', 'border': 1})
txt_name = workbook.add_format({'font_size': '10px', 'border': 1})
txt_name_bold = workbook.add_format({
'font_size': '10px',
'border': 1,
'bold': True
})
txt_name.set_indent(2)
txt_name_bold.set_indent(2)
txt = workbook.add_format({'font_size': '10px', 'border': 1})
sheet.merge_range('A2:D3',
filters.get('company_name') + ' : ' + i_data, head)
date_head = workbook.add_format({
'align': 'center',
'bold': True,
'font_size': '10px'
})
date_head.set_align('vcenter')
date_head.set_text_wrap()
date_head.set_shrink()
date_head_left = workbook.add_format({
'align': 'left',
'bold': True,
'font_size': '10px'
})
date_head_right = workbook.add_format({
'align': 'right',
'bold': True,
'font_size': '10px'
})
date_head_left.set_indent(1)
date_head_right.set_indent(1)
if filters.get('date_from'):
sheet.merge_range('A4:B4', 'From: ' + filters.get('date_from'),
date_head_left)
if filters.get('date_to'):
sheet.merge_range('C4:D4', 'To: ' + filters.get('date_to'),
date_head_right)
sheet.merge_range(
'A5:D6', ' Accounts: ' +
', '.join([lt or ''
for lt in filters['accounts']]) + '; Journals: ' +
', '.join([lt or ''
for lt in filters['journals']]) + '; Account Tags: ' +
', '.join([lt or '' for lt in filters['account_tags']]) +
'; Analytic Tags: ' +
', '.join([lt or ''
for lt in filters['analytic_tags']]) + '; Analytic: ' +
', '.join([at or ''
for at in filters['analytics']]) + '; Target Moves: ' +
filters.get('target_move').capitalize(), date_head)
sheet.set_column(0, 0, 30)
sheet.set_column(1, 1, 20)
sheet.set_column(2, 2, 15)
sheet.set_column(3, 3, 15)
row = 5
col = 0
row += 2
sheet.write(row, col, '', sub_heading)
sheet.write(row, col + 1, 'Debit', sub_heading)
sheet.write(row, col + 2, 'Credit', sub_heading)
sheet.write(row, col + 3, 'Balance', sub_heading)
if rl_data:
for fr in rl_data:
row += 1
if fr['level'] == 1:
sheet.write(row, col, fr['name'], side_heading_main)
elif fr['level'] == 2:
sheet.write(row, col, fr['name'], side_heading_sub)
else:
sheet.write(row, col, fr['name'], txt_name)
sheet.write(row, col + 1, fr['debit'], txt)
sheet.write(row, col + 2, fr['credit'], txt)
sheet.write(row, col + 3, fr['balance'], txt)
workbook.close()
output.seek(0)
response.stream.write(output.read())
output.close()