from datetime import datetime, timedelta import json import datetime import io from odoo import api, fields, models, _ from odoo.tools import date_utils try: from odoo.tools.misc import xlsxwriter except ImportError: import xlsxwriter class AccountingReport(models.TransientModel): _inherit = "accounting.report.xlsx" by_month = fields.Boolean(_("Group By Month")) def get_xlsx_report(self, options, response): obj = self.search([('id', '=', options['form']['id'])]) if not obj.by_month: res = super(AccountingReport, self).get_xlsx_report(options, response) return res output = io.BytesIO() workbook = xlsxwriter.Workbook(output, {'in_memory': True}) journal_entries = self.env['account.move'].search([('move_type', '=', 'entry')], order='date asc', limit=1) first_journal_date = journal_entries.date data = {} data['form'] = obj.read([])[0] date_from = data['form']['date_from'] date_to = data['form']['date_to'] obj = self.search([('id', '=', options['form']['id'])]) data['form'] = obj.read([])[0] comp_dic = {} env_obj = obj.env['report.account.report_financial'] data['form']['used_context'] = {} data['form']['used_context']['journal_ids'] = data['form']['journal_ids'] data['form']['used_context']['state'] = 'posted' data['form']['used_context']['strict_range'] = True sheet = workbook.add_worksheet() format1 = workbook.add_format({'font_size': 16, 'align': 'center', 'bg_color': '#D3D3D3', 'bold': True}) format1.set_font_color('#000080') format1.set_font_name('Times New Roman') format2 = workbook.add_format({'font_size': 12, 'bold': True, 'bg_color': '#D3D3D3'}) format3 = workbook.add_format({'font_size': 10, 'bold': True}) format4 = workbook.add_format({'font_size': 10}) format6 = workbook.add_format({'font_size': 10, 'bold': True}) format7 = workbook.add_format({'font_size': 10}) format8 = workbook.add_format({'font_size': 12, 'bold': True, 'bg_color': '#D3D3D3'}) format1.set_align('center') format1.set_align('vcenter') format2.set_align('center') format3.set_align('center') format4.set_align('center') format6.set_align('right') format8.set_align('center') sheet.set_column('E:E', 10, format4) sheet.set_column('H:H', 10, format4) sheet.set_column('I:I', 10, format4) sheet.set_column('J:J', 10, format4) date_ranges = self.generate_date_ranges(date_from, date_to) col_number = 0 balance_col_number = 4 for date_range in date_ranges: row_number = 9 data['form']['used_context']['date_to'] = date_range['date_to'] if obj.account_report_id.id in [338]: data['form']['used_context']['date_from'] = datetime.date(2021, 11, 30) else: data['form']['used_context']['date_from'] = date_range['date_from'] comp_dic['state'] = 'posted' comp_dic['journal_ids'] = data['form']['journal_ids'] data['form']['comparison_context'] = comp_dic data['account_report_id'] = data['form']['account_report_id'] accounting_data = env_obj.get_account_lines(data.get('form')) currency = self.env.user.company_id.currency_id.symbol format7.set_num_format('0.00 ' + currency) format6.set_num_format('0.00 ' + currency) report_date = datetime.datetime.now().strftime("%Y-%m-%d") sheet.merge_range('A1:B1', _("Report Date"), format6) sheet.merge_range('C1:D1', report_date, format7) if obj.account_report_id.name: sheet.merge_range(3, 0, 4, 3 + len(date_ranges), obj.account_report_id.name, format1) sheet.merge_range('C3:G3', _("PT. Indoteknik Dotcom Gemilang"), format4) if obj.target_move == 'all': target_moves = 'All entries' else: target_moves = 'All posted entries' sheet.merge_range('A7:B7', _("Target Moves:"), format6) sheet.write('C7', target_moves, format7) if obj.date_from: sheet.write(row_number, col_number, _("Date From:"), format6) sheet.write(row_number, col_number + 1, str(obj.date_from), format7) if obj.date_to: sheet.write(row_number, col_number + 3, _("Date To:"), format6) sheet.write(row_number, balance_col_number, str(date_range['date_to']), format7) row_number += 1 if obj.debit_credit == 1: sheet.merge_range('A9:G9', _("Name"), format8) sheet.write('H9', _("Debit"), format2) sheet.write('I9', _("Credit"), format2) sheet.write('J9', _("Balance"), format2) for values in accounting_data: if not self.env.user.company_id.parent_id: if values['level'] != 0: pass else: if values['level'] != 0: if values['level'] == 1: sheet.write(row_number, col_number, values['name'], format6) sheet.write(row_number, col_number + 7, values['debit'], format6) sheet.write(row_number, col_number + 8, values['credit'], format6) sheet.write(row_number, balance_col_number, values['balance'], format6) row_number += 1 elif not values['account_type'] == 'sum': sheet.write(row_number, col_number, values['name'], format7) sheet.write(row_number, col_number + 7, values['debit'], format7) sheet.write(row_number, col_number + 8, values['credit'], format7) sheet.write(row_number, balance_col_number, values['balance'], format7) row_number += 1 if not obj.enable_filter and not obj.debit_credit: sheet.write('A9', _("Name"), format8) sheet.merge_range(8, 1, 8, len(date_ranges) + 3, _("Balance"), format2) for values in accounting_data: if values['level'] != 0: if values['level'] == 1: # assets and liabilities sheet.write(row_number, col_number, values['name'], format6) sheet.write(row_number, balance_col_number, values['balance'], format6) row_number += 1 elif not values['account_type'] == 'sum': sheet.write(row_number, col_number, values['name'], format7) sheet.write(row_number, balance_col_number, values['balance'], format7) row_number += 1 if obj.enable_filter and not obj.debit_credit: sheet.merge_range('A9:H9', _("Name"), format8) sheet.write('I9', _("Balance"), format2) sheet.write('J9', data['form']['label_filter'], format2) for values in accounting_data: if values['level'] != 0: if values['level'] == 1: sheet.write(row_number, col_number, values['name'], format6) sheet.write(row_number, balance_col_number, values['balance'], format6) sheet.write(row_number, col_number + 9, values['balance_cmp'], format6) row_number += 1 elif not values['account_type'] == 'sum': sheet.write(row_number, col_number, values['name'], format7) sheet.write(row_number, balance_col_number, values['balance'], format7) sheet.write(row_number, col_number + 9, values['balance_cmp'], format7) row_number += 1 balance_col_number += 1 workbook.close() output.seek(0) response.stream.write(output.read()) output.close() def generate_date_ranges(self, date_from, date_to): current_date = date_from date_ranges = [] while current_date <= date_to: next_month = current_date.replace(day=1) + timedelta(days=32) end_of_month = min(next_month - timedelta(days=next_month.day), date_to) date_ranges.append({ "date_from": current_date, "date_to": end_of_month }) current_date = end_of_month + timedelta(days=1) return date_ranges