diff options
| author | stephanchrst <stephanchrst@gmail.com> | 2022-05-10 17:14:58 +0700 |
|---|---|---|
| committer | stephanchrst <stephanchrst@gmail.com> | 2022-05-10 17:14:58 +0700 |
| commit | 1ca3b3df3421961caec3b747a364071c80f5c7da (patch) | |
| tree | 6778a1f0f3f9b4c6e26d6d87ccde16e24da6c9d6 /dynamic_accounts_report/wizard | |
| parent | b57188be371d36d96caac4b8d65a40745c0e972c (diff) | |
initial commit
Diffstat (limited to 'dynamic_accounts_report/wizard')
| -rw-r--r-- | dynamic_accounts_report/wizard/__init__.py | 8 | ||||
| -rw-r--r-- | dynamic_accounts_report/wizard/ageing.py | 682 | ||||
| -rw-r--r-- | dynamic_accounts_report/wizard/balance_sheet.py | 611 | ||||
| -rw-r--r-- | dynamic_accounts_report/wizard/balance_sheet_config.py | 305 | ||||
| -rw-r--r-- | dynamic_accounts_report/wizard/cash_flow.py | 604 | ||||
| -rw-r--r-- | dynamic_accounts_report/wizard/daybook.py | 320 | ||||
| -rw-r--r-- | dynamic_accounts_report/wizard/general_ledger.py | 477 | ||||
| -rw-r--r-- | dynamic_accounts_report/wizard/partner_leadger.py | 456 | ||||
| -rw-r--r-- | dynamic_accounts_report/wizard/trial_balance.py | 324 |
9 files changed, 3787 insertions, 0 deletions
diff --git a/dynamic_accounts_report/wizard/__init__.py b/dynamic_accounts_report/wizard/__init__.py new file mode 100644 index 0000000..2f21060 --- /dev/null +++ b/dynamic_accounts_report/wizard/__init__.py @@ -0,0 +1,8 @@ +from . import general_ledger +from . import trial_balance +from . import cash_flow +from . import balance_sheet +from . import balance_sheet_config +from . import partner_leadger +from . import ageing +from . import daybook diff --git a/dynamic_accounts_report/wizard/ageing.py b/dynamic_accounts_report/wizard/ageing.py new file mode 100644 index 0000000..652ff29 --- /dev/null +++ b/dynamic_accounts_report/wizard/ageing.py @@ -0,0 +1,682 @@ +import time +from datetime import datetime + +from dateutil.relativedelta import relativedelta +from odoo import fields, models, api, _ +from odoo.tools import float_is_zero + +import io +import json + +try: + from odoo.tools.misc import xlsxwriter +except ImportError: + import xlsxwriter + + +class AgeingView(models.TransientModel): + _inherit = "account.common.report" + _name = 'account.partner.ageing' + + period_length = fields.Integer(string='Period Length (days)', + required=True, default=30) + date_from = fields.Date(default=lambda *a: time.strftime('%Y-%m-%d')) + result_selection = fields.Selection([('customer', 'Receivable Accounts'), + ('supplier', 'Payable Accounts'), + ('customer_supplier', + 'Receivable and Payable Accounts') + ], string="Partner's", required=True, + default='customer') + + partner_ids = fields.Many2many( + 'res.partner', string='Partner' + ) + partner_category_ids = fields.Many2many( + 'res.partner.category', string='Partner Tag', + ) + + @api.model + def view_report(self, option): + r = self.env['account.partner.ageing'].search([('id', '=', option[0])]) + + data = { + 'result_selection': r.result_selection, + 'model': self, + 'journals': r.journal_ids, + 'target_move': r.target_move, + 'period_length': r.period_length, + 'partners': r.partner_ids, + 'partner_tags': r.partner_category_ids, + + } + if r.date_from: + data.update({ + 'date_from': r.date_from, + }) + + filters = self.get_filter(option) + + records = self._get_report_values(data) + + currency = self._get_currency() + + return { + 'name': "Partner Ageing", + 'type': 'ir.actions.client', + 'tag': 'p_a', + 'filters': filters, + 'report_lines': records['Partners'], + 'currency': currency, + } + + def get_filter(self, option): + data = self.get_filter_data(option) + filters = {} + + if data.get('target_move'): + filters['target_move'] = data.get('target_move') + if data.get('date_from'): + filters['date_from'] = data.get('date_from') + if data.get('result_selection') == 'customer': + filters['result_selection'] = 'Receivable' + elif data.get('result_selection') == 'supplier': + filters['result_selection'] = 'Payable' + else: + filters['result_selection'] = 'Receivable and Payable' + + if data.get('partners'): + filters['partners'] = self.env['res.partner'].browse( + data.get('partners')).mapped('name') + else: + filters['partners'] = ['All'] + + if data.get('partner_tags', []): + filters['partner_tags'] = self.env['res.partner.category'].browse( + data.get('partner_tags', [])).mapped('name') + else: + filters['partner_tags'] = ['All'] + + filters['company_id'] = '' + filters['company_name'] = data.get('company_name') + filters['partners_list'] = data.get('partners_list') + filters['category_list'] = data.get('category_list') + filters['company_name'] = data.get('company_name') + filters['target_move'] = data.get('target_move').capitalize() + + + return filters + + def get_filter_data(self, option): + r = self.env['account.partner.ageing'].search([('id', '=', option[0])]) + default_filters = {} + company_id = self.env.company + company_domain = [('company_id', '=', company_id.id)] + partner = r.partner_ids if r.partner_ids else self.env[ + 'res.partner'].search([]) + categories = r.partner_category_ids if r.partner_category_ids \ + else self.env['res.partner.category'].search([]) + + filter_dict = { + 'partners': r.partner_ids.ids, + 'partner_tags': r.partner_category_ids.ids, + 'company_id': company_id.id, + 'date_from': r.date_from, + + 'target_move': r.target_move, + 'result_selection': r.result_selection, + 'partners_list': [(p.id, p.name) for p in partner], + 'category_list': [(c.id, c.name) for c in categories], + 'company_name': company_id and company_id.name, + } + filter_dict.update(default_filters) + return filter_dict + + def _get_report_values(self, data): + docs = data['model'] + date_from = data.get('date_from').strftime('%Y-%m-%d') + if data['result_selection'] == 'customer': + account_type = ['receivable'] + elif data['result_selection'] == 'supplier': + account_type = ['payable'] + else: + account_type = ['payable', 'receivable'] + target_move = data['target_move'] + partners = data.get('partners') + if data['partner_tags']: + partners = self.env['res.partner'].search( + [('category_id', 'in', data['partner_tags'].ids)]) + + account_res = self._get_partner_move_lines(data, partners, date_from, + target_move, + account_type, + data['period_length']) + + return { + 'doc_ids': self.ids, + 'docs': docs, + 'time': time, + 'Partners': account_res, + + } + + @api.model + def create(self, vals): + vals['target_move'] = 'posted' + res = super(AgeingView, self).create(vals) + return res + + def write(self, vals): + if vals.get('target_move'): + vals.update({'target_move': vals.get('target_move').lower()}) + + if vals.get('partner_ids'): + vals.update( + {'partner_ids': [(4, j) for j in vals.get('partner_ids')]}) + if not vals.get('partner_ids'): + vals.update({'partner_ids': [(5,)]}) + if vals.get('partner_category_ids'): + vals.update({'partner_category_ids': [(4, j) for j in vals.get( + 'partner_category_ids')]}) + if not vals.get('partner_category_ids'): + vals.update({'partner_category_ids': [(5,)]}) + + res = super(AgeingView, self).write(vals) + return res + + def _get_partner_move_lines(self, data, partners, date_from, target_move, + account_type, + period_length): + + periods = {} + start = datetime.strptime(date_from, "%Y-%m-%d") + date_from = datetime.strptime(date_from, "%Y-%m-%d").date() + for i in range(5)[::-1]: + stop = start - relativedelta(days=period_length) + period_name = str((5 - (i + 1)) * period_length + 1) + '-' + str( + (5 - i) * period_length) + period_stop = (start - relativedelta(days=1)).strftime('%Y-%m-%d') + if i == 0: + period_name = '+' + str(4 * period_length) + periods[str(i)] = { + 'name': period_name, + 'stop': period_stop, + 'start': (i != 0 and stop.strftime('%Y-%m-%d') or False), + } + start = stop + res = [] + total = [] + cr = self.env.cr + user_company = self.env.company + + user_currency = user_company.currency_id + ResCurrency = self.env['res.currency'].with_context(date=date_from) + company_ids = self._context.get('company_ids') or [user_company.id] + move_state = ['draft', 'posted'] + if target_move == 'posted': + move_state = ['posted'] + arg_list = (tuple(move_state), tuple(account_type)) + + reconciliation_clause = '(l.reconciled IS FALSE)' + cr.execute( + 'SELECT debit_move_id, credit_move_id FROM account_partial_reconcile where max_date > %s', + (date_from,)) + reconciled_after_date = [] + for row in cr.fetchall(): + reconciled_after_date += [row[0], row[1]] + if reconciled_after_date: + reconciliation_clause = '(l.reconciled IS FALSE OR l.id IN %s)' + arg_list += (tuple(reconciled_after_date),) + + arg_list += (date_from, tuple(company_ids),) + partner_list = '(l.partner_id IS NOT NULL)' + if partners: + list = tuple(partners.ids) + tuple([0]) + if list: + partner_list = '(l.partner_id IS NULL OR l.partner_id IN %s)' + arg_list += (tuple(list),) + query = ''' + SELECT DISTINCT l.partner_id, UPPER(res_partner.name) + FROM account_move_line AS l left join res_partner on l.partner_id = res_partner.id, account_account, account_move am + WHERE (l.account_id = account_account.id) + AND (l.move_id = am.id) + AND (am.state IN %s) + AND (account_account.internal_type IN %s) + + AND ''' + reconciliation_clause + ''' + AND (l.date <= %s) + AND l.company_id IN %s + AND ''' + partner_list + ''' + + ORDER BY UPPER(res_partner.name)''' + cr.execute(query, arg_list) + + + partners = cr.dictfetchall() + + # put a total of 0 + for i in range(7): + total.append(0) + + # Build a string like (1,2,3) for easy use in SQL query + partner_ids = [partner['partner_id'] for partner in partners if + partner['partner_id']] + + lines = dict( + (partner['partner_id'] or False, []) for partner in partners) + if not partner_ids: + return [], [], {} + + # This dictionary will store the not due amount of all partners + undue_amounts = {} + query = '''SELECT l.id + FROM account_move_line AS l, account_account, account_move am + WHERE (l.account_id = account_account.id) AND (l.move_id = am.id) + AND (am.state IN %s) + AND (account_account.internal_type IN %s) + AND (COALESCE(l.date_maturity,l.date) >= %s)\ + AND ((l.partner_id IN %s) OR (l.partner_id IS NULL)) + AND (l.date <= %s) + AND l.company_id IN %s''' + cr.execute(query, ( + tuple(move_state), tuple(account_type), date_from, + tuple(partner_ids), date_from, tuple(company_ids))) + aml_ids = cr.fetchall() + aml_ids = aml_ids and [x[0] for x in aml_ids] or [] + for line in self.env['account.move.line'].browse(aml_ids): + partner_id = line.partner_id.id or False + move_id = line.move_id.id + move_name = line.move_id.name + date_maturity = line.date_maturity + account_id = line.account_id.name + account_code = line.account_id.code + jrnl_id = line.journal_id.name + currency_id = line.company_id.currency_id.position + currency_symbol = line.company_id.currency_id.symbol + + if partner_id not in undue_amounts: + undue_amounts[partner_id] = 0.0 + line_amount = ResCurrency._compute(line.company_id.currency_id, + user_currency, line.balance) + if user_currency.is_zero(line_amount): + continue + for partial_line in line.matched_debit_ids: + if partial_line.max_date <= date_from: + line_amount += ResCurrency._compute( + partial_line.company_id.currency_id, user_currency, + partial_line.amount) + for partial_line in line.matched_credit_ids: + if partial_line.max_date <= date_from: + line_amount -= ResCurrency._compute( + partial_line.company_id.currency_id, user_currency, + partial_line.amount) + if not self.env.company.currency_id.is_zero(line_amount): + undue_amounts[partner_id] += line_amount + lines[partner_id].append({ + 'line': line, + 'partner_id': partner_id, + 'move': move_name, + 'jrnl': jrnl_id, + 'currency': currency_id, + 'symbol': currency_symbol, + 'acc_name': account_id, + 'mov_id': move_id, + 'acc_code': account_code, + 'date': date_maturity, + 'amount': line_amount, + 'period6': 6, + }) + + # Use one query per period and store results in history (a list variable) + # Each history will contain: history[1] = {'<partner_id>': <partner_debit-credit>} + history = [] + for i in range(5): + args_list = ( + tuple(move_state), tuple(account_type), tuple(partner_ids),) + dates_query = '(COALESCE(l.date_maturity,l.date)' + + if periods[str(i)]['start'] and periods[str(i)]['stop']: + dates_query += ' BETWEEN %s AND %s)' + + args_list += ( + periods[str(i)]['start'], periods[str(i)]['stop']) + elif periods[str(i)]['start']: + dates_query += ' >= %s)' + + args_list += (periods[str(i)]['start'],) + else: + dates_query += ' <= %s)' + args_list += (periods[str(i)]['stop'],) + + args_list += (date_from, tuple(company_ids)) + + query = '''SELECT l.id + FROM account_move_line AS l, account_account, account_move am + WHERE (l.account_id = account_account.id) AND (l.move_id = am.id) + AND (am.state IN %s) + AND (account_account.internal_type IN %s) + AND ((l.partner_id IN %s) OR (l.partner_id IS NULL)) + AND ''' + dates_query + ''' + + + AND (l.date <= %s) + AND l.company_id IN %s''' + cr.execute(query, args_list) + + partners_amount = {} + aml_ids = cr.fetchall() + aml_ids = aml_ids and [x[0] for x in aml_ids] or [] + for line in self.env['account.move.line'].browse(aml_ids): + partner_id = line.partner_id.id or False + move_id = line.move_id.id + move_name = line.move_id.name + date_maturity = line.date_maturity + account_id = line.account_id.name + account_code = line.account_id.code + jrnl_id = line.journal_id.name + currency_id = line.company_id.currency_id.position + currency_symbol = line.company_id.currency_id.symbol + if partner_id not in partners_amount: + partners_amount[partner_id] = 0.0 + line_amount = ResCurrency._compute(line.company_id.currency_id, + user_currency, line.balance) + if user_currency.is_zero(line_amount): + continue + for partial_line in line.matched_debit_ids: + if partial_line.max_date <= date_from: + line_amount += ResCurrency._compute( + partial_line.company_id.currency_id, user_currency, + partial_line.amount) + for partial_line in line.matched_credit_ids: + if partial_line.max_date <= date_from: + line_amount -= ResCurrency._compute( + partial_line.company_id.currency_id, user_currency, + partial_line.amount) + + if not self.env.company.currency_id.is_zero( + line_amount): + partners_amount[partner_id] += line_amount + if i + 1 == 5: + period5 = i + 1 + lines[partner_id].append({ + 'period5': period5, + 'line': line, + 'partner_id': partner_id, + 'move': move_name, + 'currency': currency_id, + 'symbol': currency_symbol, + 'jrnl': jrnl_id, + 'acc_name': account_id, + 'mov_id': move_id, + 'acc_code': account_code, + 'date': date_maturity, + 'amount': line_amount, + }) + elif i + 1 == 4: + period4 = i + 1 + lines[partner_id].append({ + + 'period4': period4, + 'line': line, + 'partner_id': partner_id, + 'move': move_name, + 'jrnl': jrnl_id, + 'acc_name': account_id, + 'currency': currency_id, + 'symbol': currency_symbol, + 'mov_id': move_id, + 'acc_code': account_code, + 'date': date_maturity, + 'amount': line_amount, + }) + elif i + 1 == 3: + period3 = i + 1 + lines[partner_id].append({ + + 'period3': period3, + 'line': line, + 'partner_id': partner_id, + 'move': move_name, + 'jrnl': jrnl_id, + 'acc_name': account_id, + 'currency': currency_id, + 'symbol': currency_symbol, + 'mov_id': move_id, + 'acc_code': account_code, + 'date': date_maturity, + 'amount': line_amount, + }) + elif i + 1 == 2: + period2 = i + 1 + lines[partner_id].append({ + + 'period2': period2, + 'line': line, + 'partner_id': partner_id, + 'move': move_name, + 'jrnl': jrnl_id, + 'acc_name': account_id, + 'currency': currency_id, + 'symbol': currency_symbol, + 'mov_id': move_id, + 'acc_code': account_code, + 'date': date_maturity, + 'amount': line_amount, + }) + else: + period1 = i + 1 + lines[partner_id].append({ + + 'period1': period1, + 'line': line, + 'partner_id': partner_id, + 'move': move_name, + 'jrnl': jrnl_id, + 'acc_name': account_id, + 'currency': currency_id, + 'symbol': currency_symbol, + 'mov_id': move_id, + 'acc_code': account_code, + 'date': date_maturity, + 'amount': line_amount, + }) + + history.append(partners_amount) + + for partner in partners: + if partner['partner_id'] is None: + partner['partner_id'] = False + at_least_one_amount = False + values = {} + undue_amt = 0.0 + if partner[ + 'partner_id'] in undue_amounts: # Making sure this partner actually was found by the query + undue_amt = undue_amounts[partner['partner_id']] + + total[6] = total[6] + undue_amt + values['direction'] = undue_amt + for rec in lines: + if partner['partner_id'] == rec: + child_lines = lines[rec] + values['child_lines'] = child_lines + if not float_is_zero(values['direction'], + precision_rounding=self.env.company.currency_id.rounding): + at_least_one_amount = True + + for i in range(5): + during = False + if partner['partner_id'] in history[i]: + during = [history[i][partner['partner_id']]] + # Adding counter + total[(i)] = total[(i)] + (during and during[0] or 0) + values[str(i)] = during and during[0] or 0.0 + if not float_is_zero(values[str(i)], + precision_rounding=self.env.company.currency_id.rounding): + at_least_one_amount = True + values['total'] = sum( + [values['direction']] + [values[str(i)] for i in range(5)]) + ## Add for total + total[(i + 1)] += values['total'] + values['partner_id'] = partner['partner_id'] + if partner['partner_id']: + browsed_partner = self.env['res.partner'].browse( + partner['partner_id']) + values['name'] = browsed_partner.name and len( + browsed_partner.name) >= 45 and browsed_partner.name[ + 0:40] + '...' or browsed_partner.name + values['trust'] = browsed_partner.trust + else: + values['name'] = _('Unknown Partner') + values['trust'] = False + + if at_least_one_amount or ( + self._context.get('include_nullified_amount') and lines[ + partner['partner_id']]): + res.append(values) + + return res, total, lines + + @api.model + def _get_currency(self): + journal = self.env['account.journal'].browse( + self.env.context.get('default_journal_id', False)) + if journal.currency_id: + return journal.currency_id.id + lang = self.env.user.lang + if not lang: + lang = 'en_US' + lang = lang.replace("_", '-') + currency_array = [self.env.company.currency_id.symbol, + self.env.company.currency_id.position, lang] + return currency_array + + 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', + filters.get('company_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-30', heading) + sheet.write('G7', '30-60', heading) + sheet.write('H7', '60-90', heading) + sheet.write('I7', '90-120', heading) + sheet.write('J7', '120+', 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 - 30', sub_heading) + sheet.write(row, col + 6, '30 - 60', sub_heading) + sheet.write(row, col + 7, '60 - 90', sub_heading) + sheet.write(row, col + 8, '90 - 120', sub_heading) + sheet.write(row, col + 9, '120 +', 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() diff --git a/dynamic_accounts_report/wizard/balance_sheet.py b/dynamic_accounts_report/wizard/balance_sheet.py new file mode 100644 index 0000000..ac12975 --- /dev/null +++ b/dynamic_accounts_report/wizard/balance_sheet.py @@ -0,0 +1,611 @@ +import time +from odoo import fields, models, api, _ + +import io +import json +from odoo.exceptions import AccessError, UserError, AccessDenied + +try: + from odoo.tools.misc import xlsxwriter +except ImportError: + import xlsxwriter + + +class BalanceSheetView(models.TransientModel): + _name = 'dynamic.balance.sheet.report' + + company_id = fields.Many2one('res.company', required=True, + default=lambda self: self.env.company) + journal_ids = fields.Many2many('account.journal', + string='Journals', required=True, + default=[]) + account_ids = fields.Many2many("account.account", string="Accounts") + account_tag_ids = fields.Many2many("account.account.tag", + string="Account Tags") + analytic_ids = fields.Many2many( + "account.analytic.account", string="Analytic Accounts") + analytic_tag_ids = fields.Many2many("account.analytic.tag", + string="Analytic Tags") + display_account = fields.Selection( + [('all', 'All'), ('movement', 'With movements'), + ('not_zero', 'With balance is not equal to 0')], + string='Display Accounts', required=True, default='movement') + target_move = fields.Selection( + [('all', 'All'), ('posted', 'Posted')], + string='Target Move', required=True, default='posted') + date_from = fields.Date(string="Start date") + date_to = fields.Date(string="End date") + + @api.model + def view_report(self, option, tag): + r = self.env['dynamic.balance.sheet.report'].search( + [('id', '=', option[0])]) + data = { + 'display_account': r.display_account, + 'model': self, + 'journals': r.journal_ids, + 'target_move': r.target_move, + 'accounts': r.account_ids, + 'account_tags': r.account_tag_ids, + 'analytics': r.analytic_ids, + 'analytic_tags': r.analytic_tag_ids, + } + if r.date_from: + data.update({ + 'date_from': r.date_from, + }) + if r.date_to: + data.update({ + 'date_to': r.date_to, + }) + + company_id = self.env.company + company_domain = [('company_id', '=', company_id.id)] + if r.account_tag_ids: + company_domain.append( + ('tag_ids', 'in', r.account_tag_ids.ids)) + if r.account_ids: + company_domain.append(('id', 'in', r.account_ids.ids)) + + new_account_ids = self.env['account.account'].search(company_domain) + data.update({'accounts': new_account_ids,}) + filters = self.get_filter(option) + records = self._get_report_values(data) + + if filters['account_tags'] != ['All']: + tag_accounts = list(map(lambda x: x.code, new_account_ids)) + + def filter_code(rec_dict): + if rec_dict['code'] in tag_accounts: + return True + else: + return False + + new_records = list(filter(filter_code, records['Accounts'])) + records['Accounts'] = new_records + + account_report_id = self.env['account.financial.report'].search([ + ('name', 'ilike', tag)]) + + new_data = {'id': self.id, 'date_from': False, + 'enable_filter': True, + 'debit_credit': True, + 'date_to': False, 'account_report_id': account_report_id, + 'target_move': filters['target_move'], + 'view_format': 'vertical', + 'company_id': self.company_id, + 'used_context': {'journal_ids': False, + 'state': filters['target_move'].lower(), + 'date_from': filters['date_from'], + 'date_to': filters['date_to'], + 'strict_range': False, + 'company_id': self.company_id, + 'lang': 'en_US'}} + + account_lines = self.get_account_lines(new_data) + report_lines = self.view_report_pdf(account_lines, new_data)[ + 'report_lines'] + move_line_accounts = [] + move_lines_dict = {} + + for rec in records['Accounts']: + move_line_accounts.append(rec['code']) + move_lines_dict[rec['code']] = {} + move_lines_dict[rec['code']]['debit'] = rec['debit'] + move_lines_dict[rec['code']]['credit'] = rec['credit'] + move_lines_dict[rec['code']]['balance'] = rec['balance'] + + report_lines_move = [] + parent_list = [] + + def filter_movelines_parents(obj): + for each in obj: + if each['report_type'] == 'accounts': + if each['code'] in move_line_accounts: + report_lines_move.append(each) + parent_list.append(each['p_id']) + + elif each['report_type'] == 'account_report': + report_lines_move.append(each) + else: + report_lines_move.append(each) + + filter_movelines_parents(report_lines) + + for rec in report_lines_move: + if rec['report_type'] == 'accounts': + if rec['code'] in move_line_accounts: + rec['debit'] = move_lines_dict[rec['code']]['debit'] + rec['credit'] = move_lines_dict[rec['code']]['credit'] + rec['balance'] = move_lines_dict[rec['code']]['balance'] + + parent_list = list(set(parent_list)) + max_level = 0 + for rep in report_lines_move: + if rep['level'] > max_level: + max_level = rep['level'] + + def get_parents(obj): + for item in report_lines_move: + for each in obj: + if item['report_type'] != 'account_type' and \ + each in item['c_ids']: + obj.append(item['r_id']) + if item['report_type'] == 'account_report': + obj.append(item['r_id']) + break + + get_parents(parent_list) + for i in range(max_level): + get_parents(parent_list) + + parent_list = list(set(parent_list)) + final_report_lines = [] + + for rec in report_lines_move: + if rec['report_type'] != 'accounts': + if rec['r_id'] in parent_list: + final_report_lines.append(rec) + else: + final_report_lines.append(rec) + + def filter_sum(obj): + sum_list = {} + for pl in parent_list: + sum_list[pl] = {} + sum_list[pl]['s_debit'] = 0 + sum_list[pl]['s_credit'] = 0 + sum_list[pl]['s_balance'] = 0 + + for each in obj: + if each['p_id'] and each['p_id'] in parent_list: + sum_list[each['p_id']]['s_debit'] += each['debit'] + sum_list[each['p_id']]['s_credit'] += each['credit'] + sum_list[each['p_id']]['s_balance'] += each['balance'] + return sum_list + + def assign_sum(obj): + for each in obj: + if each['r_id'] in parent_list and \ + each['report_type'] != 'account_report': + each['debit'] = sum_list_new[each['r_id']]['s_debit'] + each['credit'] = sum_list_new[each['r_id']]['s_credit'] + + for p in range(max_level): + sum_list_new = filter_sum(final_report_lines) + assign_sum(final_report_lines) + + company_id = self.env.company + currency = company_id.currency_id + symbol = currency.symbol + rounding = currency.rounding + position = currency.position + + for rec in final_report_lines: + rec['debit'] = round(rec['debit'], 2) + rec['credit'] = round(rec['credit'], 2) + rec['balance'] = rec['debit'] - rec['credit'] + rec['balance'] = round(rec['balance'], 2) + if (rec['balance_cmp'] < 0 and rec['balance'] > 0) or ( + rec['balance_cmp'] > 0 and rec['balance'] < 0): + rec['balance'] = rec['balance'] * -1 + + if position == "before": + rec['m_debit'] = symbol + " " + "{:,.2f}".format(rec['debit']) + rec['m_credit'] = symbol + " " + "{:,.2f}".format(rec['credit']) + rec['m_balance'] = symbol + " " + "{:,.2f}".format( + rec['balance']) + else: + rec['m_debit'] = "{:,.2f}".format(rec['debit']) + " " + symbol + rec['m_credit'] = "{:,.2f}".format(rec['credit']) + " " + symbol + rec['m_balance'] = "{:,.2f}".format( + rec['balance']) + " " + symbol + + return { + 'name': tag, + 'type': 'ir.actions.client', + 'tag': tag, + 'filters': filters, + 'report_lines': records['Accounts'], + 'debit_total': records['debit_total'], + 'credit_total': records['credit_total'], + 'debit_balance': records['debit_balance'], + 'currency': currency, + 'bs_lines': final_report_lines, + } + + def get_filter(self, option): + data = self.get_filter_data(option) + filters = {} + if data.get('journal_ids'): + filters['journals'] = self.env['account.journal'].browse( + data.get('journal_ids')).mapped('code') + else: + filters['journals'] = ['All'] + if data.get('account_ids', []): + filters['accounts'] = self.env['account.account'].browse( + data.get('account_ids', [])).mapped('code') + else: + filters['accounts'] = ['All'] + if data.get('target_move'): + filters['target_move'] = data.get('target_move') + else: + filters['target_move'] = 'posted' + if data.get('date_from'): + filters['date_from'] = data.get('date_from') + else: + filters['date_from'] = False + if data.get('date_to'): + filters['date_to'] = data.get('date_to') + else: + filters['date_to'] = False + if data.get('analytic_ids', []): + filters['analytics'] = self.env['account.analytic.account'].browse( + data.get('analytic_ids', [])).mapped('name') + else: + filters['analytics'] = ['All'] + + if data.get('account_tag_ids'): + filters['account_tags'] = self.env['account.account.tag'].browse( + data.get('account_tag_ids', [])).mapped('name') + else: + filters['account_tags'] = ['All'] + + if data.get('analytic_tag_ids', []): + filters['analytic_tags'] = self.env['account.analytic.tag'].browse( + data.get('analytic_tag_ids', [])).mapped('name') + else: + filters['analytic_tags'] = ['All'] + + filters['company_id'] = '' + filters['accounts_list'] = data.get('accounts_list') + filters['journals_list'] = data.get('journals_list') + filters['analytic_list'] = data.get('analytic_list') + filters['account_tag_list'] = data.get('account_tag_list') + filters['analytic_tag_list'] = data.get('analytic_tag_list') + filters['company_name'] = data.get('company_name') + filters['target_move'] = data.get('target_move').capitalize() + return filters + + def get_filter_data(self, option): + r = self.env['dynamic.balance.sheet.report'].search( + [('id', '=', option[0])]) + default_filters = {} + company_id = self.env.company + company_domain = [('company_id', '=', company_id.id)] + journals = r.journal_ids if r.journal_ids else self.env[ + 'account.journal'].search(company_domain) + analytics = self.analytic_ids if self.analytic_ids else self.env[ + 'account.analytic.account'].search( + company_domain) + account_tags = self.account_tag_ids if self.account_tag_ids else \ + self.env[ + 'account.account.tag'].search([]) + analytic_tags = self.analytic_tag_ids if self.analytic_tag_ids else \ + self.env[ + 'account.analytic.tag'].sudo().search( + ['|', ('company_id', '=', company_id.id), + ('company_id', '=', False)]) + + if r.account_tag_ids: + company_domain.append( + ('tag_ids', 'in', r.account_tag_ids.ids)) + + accounts = self.account_ids if self.account_ids else self.env[ + 'account.account'].search(company_domain) + filter_dict = { + 'journal_ids': r.journal_ids.ids, + 'account_ids': r.account_ids.ids, + 'analytic_ids': r.analytic_ids.ids, + 'company_id': company_id.id, + 'date_from': r.date_from, + 'date_to': r.date_to, + 'target_move': r.target_move, + 'journals_list': [(j.id, j.name, j.code) for j in journals], + 'accounts_list': [(a.id, a.name) for a in accounts], + 'analytic_list': [(anl.id, anl.name) for anl in analytics], + 'company_name': company_id and company_id.name, + 'analytic_tag_ids': r.analytic_tag_ids.ids, + 'analytic_tag_list': [(anltag.id, anltag.name) for anltag in + analytic_tags], + 'account_tag_ids': r.account_tag_ids.ids, + 'account_tag_list': [(a.id, a.name) for a in account_tags], + } + filter_dict.update(default_filters) + return filter_dict + + def _get_report_values(self, data): + docs = data['model'] + display_account = data['display_account'] + init_balance = True + journals = data['journals'] + accounts = self.env['account.account'].search([]) + if not accounts: + raise UserError(_("No Accounts Found! Please Add One")) + account_res = self._get_accounts(accounts, init_balance, + display_account, data) + debit_total = 0 + debit_total = sum(x['debit'] for x in account_res) + credit_total = sum(x['credit'] for x in account_res) + debit_balance = round(debit_total, 2) - round(credit_total, 2) + return { + 'doc_ids': self.ids, + 'debit_total': debit_total, + 'credit_total': credit_total, + 'debit_balance': debit_balance, + 'docs': docs, + 'time': time, + 'Accounts': account_res, + } + + @api.model + def create(self, vals): + vals['target_move'] = 'posted' + res = super(BalanceSheetView, self).create(vals) + return res + + def write(self, vals): + + if vals.get('target_move'): + vals.update({'target_move': vals.get('target_move').lower()}) + if vals.get('journal_ids'): + vals.update({'journal_ids': [(6, 0, vals.get('journal_ids'))]}) + if not vals.get('journal_ids'): + vals.update({'journal_ids': [(5,)]}) + if vals.get('account_ids'): + vals.update( + {'account_ids': [(4, j) for j in vals.get('account_ids')]}) + if not vals.get('account_ids'): + vals.update({'account_ids': [(5,)]}) + if vals.get('analytic_ids'): + vals.update( + {'analytic_ids': [(4, j) for j in vals.get('analytic_ids')]}) + if not vals.get('analytic_ids'): + vals.update({'analytic_ids': [(5,)]}) + + if vals.get('account_tag_ids'): + vals.update({'account_tag_ids': [(4, j) for j in + vals.get('account_tag_ids')]}) + if not vals.get('account_tag_ids'): + vals.update({'account_tag_ids': [(5,)]}) + + if vals.get('analytic_tag_ids'): + vals.update({'analytic_tag_ids': [(4, j) for j in + vals.get('analytic_tag_ids')]}) + if not vals.get('analytic_tag_ids'): + vals.update({'analytic_tag_ids': [(5,)]}) + + res = super(BalanceSheetView, self).write(vals) + return res + + def _get_accounts(self, accounts, init_balance, display_account, data): + cr = self.env.cr + MoveLine = self.env['account.move.line'] + move_lines = {x: [] for x in accounts.ids} + currency_id = self.env.company.currency_id + + # Prepare sql query base on selected parameters from wizard + tables, where_clause, where_params = MoveLine._query_get() + wheres = [""] + if where_clause.strip(): + wheres.append(where_clause.strip()) + final_filters = " AND ".join(wheres) + final_filters = final_filters.replace('account_move_line__move_id', + 'm').replace( + 'account_move_line', 'l') + new_final_filter = final_filters + + if data['target_move'] == 'posted': + new_final_filter += " AND m.state = 'posted'" + else: + new_final_filter += " AND m.state in ('draft','posted')" + + if data.get('date_from'): + new_final_filter += " AND l.date >= '%s'" % data.get('date_from') + if data.get('date_to'): + new_final_filter += " AND l.date <= '%s'" % data.get('date_to') + + if data['journals']: + new_final_filter += ' AND j.id IN %s' % str( + tuple(data['journals'].ids) + tuple([0])) + + if data.get('accounts'): + WHERE = "WHERE l.account_id IN %s" % str( + tuple(data.get('accounts').ids) + tuple([0])) + else: + WHERE = "WHERE l.account_id IN %s" + + if data['analytics']: + WHERE += ' AND anl.id IN %s' % str( + tuple(data.get('analytics').ids) + tuple([0])) + + if data['analytic_tags']: + WHERE += ' AND anltag.account_analytic_tag_id IN %s' % str( + tuple(data.get('analytic_tags').ids) + tuple([0])) + + # Get move lines base on sql query and Calculate the total balance of move lines + sql = ('''SELECT l.id AS lid,m.id AS move_id, l.account_id AS account_id, + l.date AS ldate, j.code AS lcode, l.currency_id, l.amount_currency, l.ref AS lref, + l.name AS lname, COALESCE(l.debit,0) AS debit, COALESCE(l.credit,0) AS credit, + COALESCE(SUM(l.balance),0) AS balance,\ + m.name AS move_name, c.symbol AS currency_code,c.position AS currency_position, p.name AS partner_name\ + FROM account_move_line l\ + JOIN account_move m ON (l.move_id=m.id)\ + LEFT JOIN res_currency c ON (l.currency_id=c.id)\ + LEFT JOIN res_partner p ON (l.partner_id=p.id)\ + LEFT JOIN account_analytic_account anl ON (l.analytic_account_id=anl.id)\ + LEFT JOIN account_analytic_tag_account_move_line_rel anltag ON (anltag.account_move_line_id = l.id)\ + JOIN account_journal j ON (l.journal_id=j.id)\ + JOIN account_account acc ON (l.account_id = acc.id) ''' + + WHERE + new_final_filter + ''' GROUP BY l.id, m.id, l.account_id, l.date, j.code, l.currency_id, l.amount_currency, l.ref, l.name, m.name, c.symbol, c.position, p.name''') + if data.get('accounts'): + params = tuple(where_params) + else: + params = (tuple(accounts.ids),) + tuple(where_params) + cr.execute(sql, params) + + for row in cr.dictfetchall(): + balance = 0 + for line in move_lines.get(row['account_id']): + balance += round(line['debit'], 2) - round(line['credit'], 2) + row['balance'] += (round(balance, 2)) + row['m_id'] = row['account_id'] + move_lines[row.pop('account_id')].append(row) + # Calculate the debit, credit and balance for Accounts + account_res = [] + for account in accounts: + currency = account.currency_id and account.currency_id or account.company_id.currency_id + res = dict((fn, 0.0) for fn in ['credit', 'debit', 'balance']) + res['code'] = account.code + res['name'] = account.name + res['id'] = account.id + res['move_lines'] = move_lines[account.id] + for line in res.get('move_lines'): + res['debit'] += round(line['debit'], 2) + res['credit'] += round(line['credit'], 2) + res['balance'] = round(line['balance'], 2) + if display_account == 'all': + account_res.append(res) + if display_account == 'movement' and res.get('move_lines'): + account_res.append(res) + if display_account == 'not_zero' and not currency.is_zero( + res['balance']): + account_res.append(res) + + return account_res + + @api.model + def _get_currency(self): + journal = self.env['account.journal'].browse( + self.env.context.get('default_journal_id', False)) + if journal.currency_id: + return journal.currency_id.id + currency_array = [self.env.company.currency_id.symbol, + self.env.company.currency_id.position] + return currency_array + + 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()
\ No newline at end of file diff --git a/dynamic_accounts_report/wizard/balance_sheet_config.py b/dynamic_accounts_report/wizard/balance_sheet_config.py new file mode 100644 index 0000000..6643ad2 --- /dev/null +++ b/dynamic_accounts_report/wizard/balance_sheet_config.py @@ -0,0 +1,305 @@ +import re +from odoo import models, fields, api + + +class BalanceSheet(models.TransientModel): + _inherit = "dynamic.balance.sheet.report" + + def view_report_pdf(self, acc, form): + data = dict() + report_lines = acc + data['form'] = form + + # find the journal items of these accounts + journal_items = self.find_journal_items(report_lines, data['form']) + + def set_report_level(rec): + """This function is used to set the level of each item. + This level will be used to set the alignment in the dynamic reports.""" + + level = 1 + if not rec['parent']: + return level + else: + for line in report_lines: + key = 'a_id' if line['type'] == 'account' else 'id' + if line[key] == rec['parent']: + return level + set_report_level(line) + + # finding the root + for item in report_lines: + item['balance'] = round(item['balance'], 2) + if not item['parent']: + item['level'] = 1 + parent = item + report_name = item['name'] + item_id = item['id'] + report_id = item['r_id'] + else: + item['level'] = set_report_level(item) + data['journal_items'] = journal_items + data['report_lines'] = report_lines + + return data + + def _compute_account_balance(self, accounts): + """ compute the balance, debit + and credit for the provided accounts + """ + + mapping = { + 'balance': + "COALESCE(SUM(debit),0) - COALESCE(SUM(credit), 0)" + " as balance", + 'debit': "COALESCE(SUM(debit), 0) as debit", + 'credit': "COALESCE(SUM(credit), 0) as credit", + } + + res = {} + for account in accounts: + res[account.id] = dict((fn, 0.0) + for fn in mapping.keys()) + if accounts: + tables, where_clause, where_params = ( + self.env['account.move.line']._query_get()) + tables = tables.replace( + '"', '') if tables else "account_move_line" + wheres = [""] + if where_clause.strip(): + wheres.append(where_clause.strip()) + filters = " AND ".join(wheres) + request = ("SELECT account_id as id, " + + ', '.join(mapping.values()) + + " FROM " + tables + + " WHERE account_id IN %s " + + filters + + " GROUP BY account_id") + params = (tuple(accounts._ids),) + tuple(where_params) + + self.env.cr.execute(request, params) + for row in self.env.cr.dictfetchall(): + res[row['id']] = row + + return res + + def _compute_report_balance(self, reports): + """returns a dictionary with key=the ID of a record and + value=the credit, debit and balance amount + computed for this record. If the record is of type : + 'accounts' : it's the sum of the linked accounts + 'account_type' : it's the sum of leaf accounts with + such an account_type + 'account_report' : it's the amount of the related report + 'sum' : it's the sum of the children of this record + (aka a 'view' record)""" + + + res = {} + fields = ['credit', 'debit', 'balance'] + for report in reports: + if report.id in res: + continue + res[report.id] = dict((fn, 0.0) for fn in fields) + if report.type == 'accounts': + # it's the sum of the linked accounts + res[report.id]['account'] = self._compute_account_balance( + report.account_ids + ) + for value in \ + res[report.id]['account'].values(): + for field in fields: + res[report.id][field] += value.get(field) + elif report.type == 'account_type': + # it's the sum the leaf accounts + # with such an account type + accounts = self.env['account.account'].search([ + ('user_type_id', 'in', report.account_type_ids.ids) + ]) + res[report.id]['account'] = self._compute_account_balance( + accounts) + for value in res[report.id]['account'].values(): + for field in fields: + res[report.id][field] += value.get(field) + elif report.type == 'account_report' and report.account_report_id: + # it's the amount of the linked report + res2 = self._compute_report_balance(report.account_report_id) + for key, value in res2.items(): + for field in fields: + res[report.id][field] += value[field] + elif report.type == 'sum': + # it's the sum of the children of this account.report + res2 = self._compute_report_balance(report.children_ids) + for key, value in res2.items(): + for field in fields: + res[report.id][field] += value[field] + return res + + def get_account_lines(self, data): + + lines = [] + account_report = data['account_report_id'] + child_reports = account_report._get_children_by_order() + res = self.with_context( + data.get('used_context'))._compute_report_balance(child_reports) + if data['enable_filter']: + comparison_res = self._compute_report_balance(child_reports) + for report_id, value in comparison_res.items(): + res[report_id]['comp_bal'] = value['balance'] + report_acc = res[report_id].get('account') + if report_acc: + for account_id, val in \ + comparison_res[report_id].get('account').items(): + report_acc[account_id]['comp_bal'] = val['balance'] + + for report in child_reports: + r_name = str(report.name) + r_name = re.sub('[^0-9a-zA-Z]+', '', r_name) + if report.parent_id: + p_name = str(report.parent_id.name) + p_name = re.sub('[^0-9a-zA-Z]+', '', p_name) + str( + report.parent_id.id) + else: + p_name = False + + child_ids = [] + for chd in report.children_ids: + child_ids.append(chd.id) + + vals = { + 'r_id': report.id, + 'p_id': report.parent_id.id, + 'report_type': report.type, + 'c_ids': child_ids, + 'id': r_name + str(report.id), + 'sequence': report.sequence, + 'parent': p_name, + 'name': report.name, + 'balance': res[report.id]['balance'] * int(report.sign), + 'type': 'report', + 'level': bool( + report.style_overwrite) and report.style_overwrite or + report.level, + 'account_type': report.type or False, + 'is_present': False, + # used to underline the financial report balances + } + if data['debit_credit']: + vals['debit'] = res[report.id]['debit'] + vals['credit'] = res[report.id]['credit'] + + if data['enable_filter']: + vals['balance_cmp'] = res[report.id]['comp_bal'] * int( + report.sign) + + lines.append(vals) + if report.display_detail == 'no_detail': + # the rest of the loop is + # used to display the details of the + # financial report, so it's not needed here. + continue + + if res[report.id].get('account'): + sub_lines = [] + for account_id, value \ + in res[report.id]['account'].items(): + # if there are accounts to display, + # we add them to the lines with a level equals + # to their level in + # the COA + 1 (to avoid having them with a too low level + # that would conflicts with the level of data + # financial reports for Assets, liabilities...) + flag = False + account = self.env['account.account'].browse(account_id) + vals = { + 'r_id': False, + 'p_id': report.id, + 'report_type': 'accounts', + 'c_ids': [], + 'account': account.id, + 'code': account.code, + 'a_id': account.code + re.sub('[^0-9a-zA-Z]+', 'acnt', + account.name) + str( + account.id), + 'name': account.code + '-' + account.name, + 'balance': value['balance'] * int(report.sign) or 0.0, + 'type': 'account', + 'parent': r_name + str(report.id), + 'level': ( + report.display_detail == 'detail_with_hierarchy' and + 4), + 'account_type': account.internal_type, + } + if data['debit_credit']: + vals['debit'] = value['debit'] + vals['credit'] = value['credit'] + if not account.company_id.currency_id.is_zero( + vals['debit']) or \ + not account.company_id.currency_id.is_zero( + vals['credit']): + flag = True + if not account.company_id.currency_id.is_zero( + vals['balance']): + flag = True + if data['enable_filter']: + vals['balance_cmp'] = value['comp_bal'] * int( + report.sign) + if not account.company_id.currency_id.is_zero( + vals['balance_cmp']): + flag = True + if flag: + sub_lines.append(vals) + lines += sorted(sub_lines, + key=lambda sub_line: sub_line['name']) + + return lines + + def find_journal_items(self, report_lines, form): + cr = self.env.cr + journal_items = [] + for i in report_lines: + if i['type'] == 'account': + account = i['account'] + if form['target_move'] == 'posted': + search_query = "select aml.id, am.id as j_id, aml.account_id, aml.date," \ + " aml.name as label, am.name, " \ + + "(aml.debit-aml.credit) as balance, aml.debit, aml.credit, aml.partner_id " \ + + " from account_move_line aml join account_move am " \ + "on (aml.move_id=am.id and am.state=%s) " \ + + " where aml.account_id=%s" + vals = [form['target_move']] + else: + search_query = "select aml.id, am.id as j_id, aml.account_id, aml.date, " \ + "aml.name as label, am.name, " \ + + "(aml.debit-aml.credit) as balance, aml.debit, aml.credit, aml.partner_id " \ + + " from account_move_line aml join account_move am on (aml.move_id=am.id) " \ + + " where aml.account_id=%s" + vals = [] + if form['date_from'] and form['date_to']: + search_query += " and aml.date>=%s and aml.date<=%s" + vals += [account, form['date_from'], form['date_to']] + elif form['date_from']: + search_query += " and aml.date>=%s" + vals += [account, form['date_from']] + elif form['date_to']: + search_query += " and aml.date<=%s" + vals += [account, form['date_to']] + else: + vals += [account] + + cr.execute(search_query, tuple(vals)) + items = cr.dictfetchall() + + for j in items: + temp = j['id'] + j['id'] = re.sub('[^0-9a-zA-Z]+', '', i['name']) + str( + temp) + j['p_id'] = str(i['a_id']) + j['type'] = 'journal_item' + journal_items.append(j) + return journal_items + + + + + + diff --git a/dynamic_accounts_report/wizard/cash_flow.py b/dynamic_accounts_report/wizard/cash_flow.py new file mode 100644 index 0000000..021ca3f --- /dev/null +++ b/dynamic_accounts_report/wizard/cash_flow.py @@ -0,0 +1,604 @@ +import time +from datetime import datetime + +from odoo import models, api, fields +FETCH_RANGE = 2000 +import io +import json +try: + from odoo.tools.misc import xlsxwriter +except ImportError: + import xlsxwriter +year = datetime.now().year + + +class AccountCasgFlow(models.TransientModel): + _name = "account.cash.flow" + _inherit = "account.common.report" + + date_from = fields.Date(string="Start Date", default=str(year)+'-01-01') + date_to = fields.Date(string="End Date", default=fields.Date.today) + today = fields.Date("Report Date", default=fields.Date.today) + levels = fields.Selection([('summary', 'Summary'), + ('consolidated', 'Consolidated'), + ('detailed', 'Detailed'), + ('very', 'Very Detailed')], + string='Levels', required=True, default='summary', + help='Different levels for cash flow statements \n' + 'Summary: Month wise report.\n' + 'Consolidated: Based on account types.\n' + 'Detailed: Based on accounts.\n' + 'Very Detailed: Accounts with their move lines') + + account_ids = fields.Many2many( + "account.account", + string="Accounts", + ) + + @api.model + def view_report(self, option): + r = self.env['account.cash.flow'].search([('id', '=', option[0])]) + data = { + 'model': self, + 'journals': r.journal_ids, + 'target_move': r.target_move, + 'levels': r.levels, + } + if r.date_from: + data.update({ + 'date_from': r.date_from, + }) + if r.date_to: + data.update({ + 'date_to': r.date_to, + }) + + filters = self.get_filter(option) + report_lines = self._get_report_values(data, option) + fetched_data = report_lines['fetched_data'] + fetched = report_lines['fetched'] + account_res = report_lines['account_res'] + journal_res = report_lines['journal_res'] + levels = report_lines['levels'] + currency = self._get_currency() + + return { + 'name': "Cash Flow Statements", + 'type': 'ir.actions.client', + 'tag': 'c_f', + 'report_lines': report_lines, + 'fetched_data': fetched_data, + 'fetched': fetched, + 'account_res': account_res, + 'journal_res': journal_res, + 'levels': r.levels, + 'filters': filters, + 'currency': currency, + } + + def get_filter(self, option): + data = self.get_filter_data(option) + filters = {} + if data.get('journal_ids'): + filters['journals'] = self.env['account.journal'].browse(data.get('journal_ids')).mapped('code') + else: + filters['journals'] = ['All'] + if data.get('account_ids', []): + filters['accounts'] = self.env['account.account'].browse(data.get('account_ids', [])).mapped('code') + else: + filters['accounts'] = ['All'] + if data.get('target_move'): + filters['target_move'] = data.get('target_move') + if data.get('date_from'): + filters['date_from'] = data.get('date_from') + if data.get('date_to'): + filters['date_to'] = data.get('date_to') + if data.get('levels'): + filters['levels'] = data.get('levels') + + filters['company_id'] = '' + filters['accounts_list'] = data.get('accounts_list') + filters['journals_list'] = data.get('journals_list') + filters['company_name'] = data.get('company_name') + filters['target_move'] = data.get('target_move').capitalize() + + return filters + + def get_filter_data(self, option): + r = self.env['account.cash.flow'].search([('id', '=', option[0])]) + default_filters = {} + company_id = self.env.company + company_domain = [('company_id', '=', company_id.id)] + journals = r.journal_ids if r.journal_ids else self.env['account.journal'].search(company_domain) + accounts = self.account_ids if self.account_ids else self.env['account.account'].search(company_domain) + + filter_dict = { + 'journal_ids': r.journal_ids.ids, + 'account_ids': self.account_ids.ids, + 'company_id': company_id.id, + 'date_from': r.date_from, + 'date_to': r.date_to, + 'levels': r.levels, + 'target_move': r.target_move, + 'journals_list': [(j.id, j.name, j.code) for j in journals], + 'accounts_list': [(a.id, a.name) for a in accounts], + 'company_name': company_id and company_id.name, + } + filter_dict.update(default_filters) + return filter_dict + + def _get_report_values(self, data, option): + cr = self.env.cr + data = self.get_filter(option) + company_id = self.env.company + currency = company_id.currency_id + symbol = company_id.currency_id.symbol + rounding = company_id.currency_id.rounding + position = company_id.currency_id.position + + fetched_data = [] + account_res = [] + journal_res = [] + fetched = [] + + account_type_id = self.env.ref('account.data_account_type_liquidity').id + model = self.env.context.get('active_model') + if data.get('levels') == 'summary': + state = """ WHERE am.state = 'posted' """ if data.get('target_move') == 'posted' else '' + query3 = """SELECT to_char(am.date, 'Month') as month_part, extract(YEAR from am.date) as year_part, + sum(aml.debit) AS total_debit, sum(aml.credit) AS total_credit, + sum(aml.balance) AS total_balance FROM (SELECT am.date, am.id, am.state FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' ) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + """ + state + """GROUP BY month_part,year_part""" + cr = self._cr + cr.execute(query3) + fetched_data = cr.dictfetchall() + elif data.get('date_from') is False: + account_type_id = self.env.ref( + 'account.data_account_type_liquidity').id + state = """AND am.state = 'posted' """ if data.get( + 'target_move') == 'posted' else '' + sql = """SELECT DISTINCT aa.id, aa.name,aa.code, sum(aml.debit) AS total_debit, + sum(aml.credit) AS total_credit,sum(aml.balance) AS total_balance + FROM (SELECT am.* FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' """ + state + """) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + GROUP BY aa.name, aa.code,aa.id""" + cr = self._cr + cr.execute(sql) + fetched_data = cr.dictfetchall() + elif data.get('date_from') is False and data.get('date_from') != False: + account_type_id = self.env.ref( + 'account.data_account_type_liquidity').id + state = """AND am.state = 'posted' """ if data.get( + 'target_move') == 'posted' else '' + sql = """SELECT DISTINCT aa.id, aa.name,aa.code, sum(aml.debit) AS total_debit, + sum(aml.credit) AS total_credit,sum(aml.balance) AS total_balance + FROM (SELECT am.* FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' """ + state + """) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + GROUP BY aa.name, aa.code,aa.id""" + cr = self._cr + cr.execute(sql) + fetched_data = cr.dictfetchall() + elif data.get('date_from') is False and data.get('date_from') != False: + account_type_id = self.env.ref( + 'account.data_account_type_liquidity').id + state = """AND am.state = 'posted' """ if data.get( + 'target_move') == 'posted' else '' + sql = """SELECT DISTINCT aa.id, aa.name,aa.code, sum(aml.debit) AS total_debit, + sum(aml.credit) AS total_credit,sum(aml.balance) AS total_balance + FROM (SELECT am.* FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' """ + state + """) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + GROUP BY aa.name, aa.code,aa.id""" + cr = self._cr + cr.execute(sql) + fetched_data = cr.dictfetchall() + + elif data.get('date_to') == " ": + account_type_id = self.env.ref( + 'account.data_account_type_liquidity').id + state = """AND am.state = 'posted' """ if data.get( + 'target_move') == 'posted' else '' + sql = """SELECT DISTINCT aa.id, aa.name,aa.code, sum(aml.debit) AS total_debit, + sum(aml.credit) AS total_credit,sum(aml.balance) AS total_balance + FROM (SELECT am.* FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' """ + state + """) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + GROUP BY aa.name, aa.code,aa.id""" + cr = self._cr + cr.execute(sql) + fetched_data = cr.dictfetchall() + + elif data.get('levels') == 'consolidated': + state = """ WHERE am.state = 'posted' """ if data.get('target_move') == 'posted' else '' + query2 = """SELECT aat.name, sum(aml.debit) AS total_debit, sum(aml.credit) AS total_credit, + sum(aml.balance) AS total_balance FROM ( SELECT am.id, am.state FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str(data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' ) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + """ + state + """GROUP BY aat.name""" + cr = self._cr + cr.execute(query2) + fetched_data = cr.dictfetchall() + elif data.get('levels') == 'detailed': + state = """ WHERE am.state = 'posted' """ if data.get('target_move') == 'posted' else '' + query1 = """SELECT aa.id,aa.name,aa.code, sum(aml.debit) AS total_debit, sum(aml.credit) AS total_credit, + sum(aml.balance) AS total_balance FROM (SELECT am.id, am.state FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' ) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + """ + state + """GROUP BY aa.name, aa.code, aa.id""" + cr = self._cr + cr.execute(query1) + fetched_data = cr.dictfetchall() + for account in self.env['account.account'].search([]): + child_lines = self.get_journal_lines(account, data) + if child_lines: + journal_res.append(child_lines) + + else: + account_type_id = self.env.ref( + 'account.data_account_type_liquidity').id + state = """AND am.state = 'posted' """ if data.get('target_move') == 'posted' else '' + sql = """SELECT DISTINCT aa.id, aa.name,aa.code, sum(aml.debit) AS total_debit, + sum(aml.credit) AS total_credit,sum(aml.balance) AS total_balance + FROM (SELECT am.* FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' """ + state + """) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + GROUP BY aa.name, aa.code,aa.id""" + cr = self._cr + cr.execute(sql) + fetched_data = cr.dictfetchall() + for account in self.env['account.account'].search([]): + child_lines = self._get_lines(account, data) + if child_lines: + account_res.append(child_lines) + journals = self.get_journal_lines(account, data) + if journals: + journal_res.append(journals) + + return { + 'date_from': data.get('date_from'), + 'date_to': data.get('date_to'), + 'levels': data.get('level'), + 'doc_ids': self.ids, + 'doc_model': model, + 'fetched_data': fetched_data, + 'account_res': account_res, + 'journal_res': journal_res, + 'fetched': fetched, + 'company_currency_id': currency, + 'company_currency_symbol': symbol, + 'company_currency_position': position, + } + + def _get_lines(self, account, data): + account_type_id = self.env.ref( + 'account.data_account_type_liquidity').id + state = """AND am.state = 'posted' """ if data.get('target_move') == 'posted' else '' + query = """SELECT aml.account_id,aj.id as j_id,aj.name,am.id, am.name as move_name, sum(aml.debit) AS total_debit, + sum(aml.credit) AS total_credit, COALESCE(SUM(aml.debit - aml.credit),0) AS balance FROM (SELECT am.* FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' """ + state + """) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_journal aj ON aj.id = am.journal_id + WHERE aa.id = """ + str(account.id) + """ + GROUP BY am.name, aml.account_id, aj.id, aj.name, am.id""" + + cr = self._cr + cr.execute(query) + fetched_data = cr.dictfetchall() + + sql2 = """SELECT aa.name as account_name,aa.id as account_id, aj.id, aj.name, sum(aml.debit) AS total_debit, + sum(aml.credit) AS total_credit, sum(aml.balance) AS total_balance FROM (SELECT am.* FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' """ + state + """) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_journal aj ON aj.id = am.journal_id + WHERE aa.id = """ + str( + account.id) + """ + GROUP BY aa.name, aj.name, aj.id,aa.id""" + + cr = self._cr + cr.execute(sql2) + fetch_data = cr.dictfetchall() + if fetched_data: + return { + 'account': account.name, + 'id': account.id, + 'code': account.code, + 'move_lines': fetched_data, + 'journal_lines': fetch_data, + } + + + def get_journal_lines(self, account, data, offset=0, fetch_range=FETCH_RANGE): + account_type_id = self.env.ref( + 'account.data_account_type_liquidity').id + offset_count = offset * fetch_range + state = """AND am.state = 'posted' """ if data.get('target_move') == 'posted' else '' + sql2 = """SELECT aa.name as account_name, aj.name, sum(aml.debit) AS total_debit, + sum(aml.credit) AS total_credit, COALESCE(SUM(aml.debit - aml.credit),0) AS balance FROM (SELECT am.* FROM account_move as am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_account_type aat ON aat.id = aa.user_type_id + WHERE am.date BETWEEN '""" + str( + data.get('date_from')) + """' and '""" + str( + data.get('date_to')) + """' AND aat.id='""" + str( + account_type_id) + """' """ + state + """) am + LEFT JOIN account_move_line aml ON aml.move_id = am.id + LEFT JOIN account_account aa ON aa.id = aml.account_id + LEFT JOIN account_journal aj ON aj.id = am.journal_id + WHERE aa.id = """ + str(account.id) + """ + GROUP BY aa.name, aj.name""" + + cr = self._cr + cr.execute(sql2) + fetched_data = cr.dictfetchall() + if fetched_data: + return { + 'account': account.name, + 'id': account.id, + 'journal_lines': fetched_data, + 'offset': offset_count, + } + + + + + @api.model + def create(self, vals): + vals['target_move'] = 'posted' + res = super(AccountCasgFlow, self).create(vals) + return res + + def write(self, vals): + if vals.get('target_move'): + vals.update({'target_move': vals.get('target_move').lower()}) + if vals.get('journal_ids'): + vals.update({'journal_ids': [(6, 0, vals.get('journal_ids'))]}) + if vals.get('journal_ids') == []: + vals.update({'journal_ids': [(5,)]}) + if vals.get('account_ids'): + vals.update({'account_ids': [(4, j) for j in vals.get('account_ids')]}) + if vals.get('account_ids') == []: + vals.update({'account_ids': [(5,)]}) + + res = super(AccountCasgFlow, self).write(vals) + return res + + @api.model + def _get_currency(self): + journal = self.env['account.journal'].browse( + self.env.context.get('default_journal_id', False)) + if journal.currency_id: + return journal.currency_id.id + lang = self.env.user.lang + if not lang: + lang = 'en_US' + lang = lang.replace("_", '-') + currency_array = [self.env.company.currency_id.symbol, + self.env.company.currency_id.position, lang] + return currency_array + + 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.company.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()
\ No newline at end of file diff --git a/dynamic_accounts_report/wizard/daybook.py b/dynamic_accounts_report/wizard/daybook.py new file mode 100644 index 0000000..90aa199 --- /dev/null +++ b/dynamic_accounts_report/wizard/daybook.py @@ -0,0 +1,320 @@ +import time +from datetime import date +from datetime import timedelta, datetime +from odoo import fields, models, api, _ +import io +import json +from odoo.exceptions import AccessError, UserError, AccessDenied + +try: + from odoo.tools.misc import xlsxwriter +except ImportError: + import xlsxwriter + + +class AgeingView(models.TransientModel): + _name = 'account.day.book' + + company_id = fields.Many2one('res.company', string='Company', + readonly=True, + default=lambda self: self.env.company) + journal_ids = fields.Many2many('account.journal', string='Journals', + required=True, + default=lambda self: self.env[ + 'account.journal'].search([])) + + account_ids = fields.Many2many('account.account', + required=True, string='Accounts', + ) + + date_from = fields.Date(string='Start Date', default=date.today(), + required=True) + date_to = fields.Date(string='End Date', default=date.today(), + required=True) + + target_move = fields.Selection([('posted', 'All Posted Entries'), + ('all', 'All Entries')], + string='Target Moves', required=True, + default='posted') + + @api.model + def view_report(self, option): + r = self.env['account.day.book'].search([('id', '=', option[0])]) + data = {} + data['ids'] = self.env.context.get('active_ids', []) + data['model'] = self.env.context.get('active_model', 'ir.ui.menu') + data['form'] = \ + r.read(['date_from', 'date_to', 'journal_ids', 'target_move', + 'account_ids'])[0] + filters = self.get_filter(option) + records = self._get_report_values(data) + currency = self._get_currency() + return { + 'name': "Day Book", + 'type': 'ir.actions.client', + 'tag': 'd_b', + 'filters': filters, + 'report_lines': records['Accounts'], + 'currency': currency, + } + + def get_filter(self, option): + data = self.get_filter_data(option) + filters = {} + + if data.get('target_move'): + filters['target_move'] = data.get('target_move') + if data.get('date_from'): + filters['date_from'] = data.get('date_from') + if data.get('date_to'): + filters['date_to'] = data.get('date_to') + if data.get('journal_ids'): + filters['journals'] = self.env['account.journal'].browse( + data.get('journal_ids')).mapped('code') + else: + filters['journals'] = ['All'] + if data.get('account_ids', []): + filters['accounts'] = self.env['account.account'].browse( + data.get('account_ids', [])).mapped('code') + else: + filters['accounts'] = ['All'] + filters['company_id'] = '' + filters['accounts_list'] = data.get('accounts_list') + filters['journals_list'] = data.get('journals_list') + filters['company_name'] = data.get('company_name') + filters['target_move'] = data.get('target_move').capitalize() + + return filters + + def get_filter_data(self, option): + r = self.env['account.day.book'].search([('id', '=', option[0])]) + default_filters = {} + company_id = self.env.company + company_domain = [('company_id', '=', company_id.id)] + journals = self.journal_ids if self.journal_ids else self.env[ + 'account.journal'].search(company_domain) + accounts = self.account_ids if self.account_ids else self.env[ + 'account.account'].search(company_domain) + + filter_dict = { + 'journal_ids': self.journal_ids.ids, + 'account_ids': self.account_ids.ids, + 'company_id': company_id.id, + 'date_from': r.date_from, + 'date_to':r.date_to, + + 'target_move': r.target_move, + 'journals_list': [(j.id, j.name, j.code) for j in journals], + 'accounts_list': [(a.id, a.name) for a in accounts], + + 'company_name': company_id and company_id.name, + } + filter_dict.update(default_filters) + return filter_dict + + def _get_report_values(self, data=None): + form_data = data['form'] + active_acc = data['form']['account_ids'] + accounts = self.env['account.account'].search( + [('id', 'in', active_acc)]) if data['form']['account_ids'] else \ + self.env['account.account'].search([]) + if not accounts: + raise UserError(_("No Accounts Found! Please Add One")) + active_jrnl = data['form']['journal_ids'] + journals = self.env['account.journal'].search( + [('id', 'in', active_jrnl)]) if data['form']['journal_ids'] else \ + self.env['account.journal'].search([]) + if not journals: + raise UserError(_("No journals Found!")) + + + date_start = datetime.strptime(str(form_data['date_from']), + '%Y-%m-%d').date() + date_end = datetime.strptime(str(form_data['date_to']), '%Y-%m-%d').date() + days = date_end - date_start + dates = [] + record = [] + for i in range(days.days + 1): + dates.append(date_start + timedelta(days=i)) + for head in dates: + pass_date = str(head) + accounts_res = self._get_account_move_entry( + accounts, form_data,journals, pass_date) + if accounts_res['lines']: + record.append({ + 'date': head, + 'debit': accounts_res['debit'], + 'credit': accounts_res['credit'], + 'balance': accounts_res['balance'], + 'child_lines': accounts_res['lines'], + 'id': accounts_res['move_id'], + }) + return { + 'doc_ids': self.ids, + 'time': time, + 'Accounts': record, + } + + @api.model + def create(self, vals): + vals['target_move'] = 'posted' + res = super(AgeingView, self).create(vals) + return res + + def write(self, vals): + if vals.get('target_move'): + vals.update({'target_move': vals.get('target_move').lower()}) + if vals.get('journal_ids'): + vals.update({'journal_ids': [(6, 0, vals.get('journal_ids'))]}) + if vals.get('journal_ids') == []: + vals.update({'journal_ids': [(5,)]}) + if vals.get('account_ids'): + vals.update( + {'account_ids': [(4, j) for j in vals.get('account_ids')]}) + if vals.get('account_ids') == []: + vals.update({'account_ids': [(5,)]}) + res = super(AgeingView, self).write(vals) + return res + + def _get_account_move_entry(self, accounts, form_data,journals, pass_date): + cr = self.env.cr + move_line = self.env['account.move.line'] + tables, where_clause, where_params = move_line._query_get() + wheres = [""] + if where_clause.strip(): + wheres.append(where_clause.strip()) + if form_data['target_move'] == 'posted': + target_move = "AND m.state = 'posted'" + else: + target_move = '' + sql = (''' + SELECT l.id AS lid,m.id AS move_id, acc.name as accname, l.account_id AS account_id, l.date AS ldate, j.code AS lcode, l.currency_id, + l.amount_currency, l.ref AS lref, l.name AS lname, COALESCE(l.debit,0) AS debit, COALESCE(l.credit,0) AS credit, + COALESCE(SUM(l.debit),0) - COALESCE(SUM(l.credit), 0) AS balance, + m.name AS move_name, c.symbol AS currency_code, p.name AS partner_name + FROM account_move_line l + JOIN account_move m ON (l.move_id=m.id) + LEFT JOIN res_currency c ON (l.currency_id=c.id) + LEFT JOIN res_partner p ON (l.partner_id=p.id) + JOIN account_journal j ON (l.journal_id=j.id) + JOIN account_account acc ON (l.account_id = acc.id) + WHERE l.account_id IN %s AND l.journal_id IN %s ''' + target_move + ''' AND l.date = %s + GROUP BY l.id,m.id, l.account_id, l.date, + j.code, l.currency_id, l.amount_currency, l.ref, l.name, m.name, c.symbol, p.name , acc.name + ORDER BY l.date DESC + ''') + params = ( + tuple(accounts.ids), tuple(journals.ids), pass_date) + cr.execute(sql, params) + data = cr.dictfetchall() + + res = {} + debit = credit = balance = 0.00 + id = '' + for line in data: + debit += line['debit'] + credit += line['credit'] + balance += line['balance'] + id = line['move_id'] + res['debit'] = debit + res['credit'] = credit + res['balance'] = balance + res['lines'] = data + res['move_id'] = id + return res + + @api.model + def _get_currency(self): + journal = self.env['account.journal'].browse( + self.env.context.get('default_journal_id', False)) + if journal.currency_id: + return journal.currency_id.id + lang = self.env.user.lang + if not lang: + lang = 'en_US' + lang = lang.replace("_", '-') + currency_array = [self.env.company.currency_id.symbol, + self.env.company.currency_id.position, lang] + return currency_array + + 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', + filters.get('company_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() diff --git a/dynamic_accounts_report/wizard/general_ledger.py b/dynamic_accounts_report/wizard/general_ledger.py new file mode 100644 index 0000000..cb994b3 --- /dev/null +++ b/dynamic_accounts_report/wizard/general_ledger.py @@ -0,0 +1,477 @@ +import time +from odoo import fields, models, api, _ + +import io +import json +from odoo.exceptions import AccessError, UserError, AccessDenied +try: + from odoo.tools.misc import xlsxwriter +except ImportError: + import xlsxwriter + + +class GeneralView(models.TransientModel): + _inherit = "account.common.report" + _name = 'account.general.ledger' + + journal_ids = fields.Many2many('account.journal', + + string='Journals', required=True, + default=[]) + account_ids = fields.Many2many( + "account.account", + string="Accounts", + ) + account_tag_ids = fields.Many2many("account.account.tag", string="Account Tags") + + analytic_ids = fields.Many2many( + "account.analytic.account", string="Analytic Accounts" + ) + analytic_tag_ids = fields.Many2many("account.analytic.tag", string="Analytic Tags") + + display_account = fields.Selection( + [('all', 'All'), ('movement', 'With movements'), + ('not_zero', 'With balance is not equal to 0')], + string='Display Accounts', required=True, default='movement') + titles = fields.Char('Title') + target_move = fields.Selection([('posted', 'All Posted Entries'), + ('all', 'All Entries')], + string='Target Moves', required=True) + + @api.model + def view_report(self, option, title): + r = self.env['account.general.ledger'].search([('id', '=', option[0])]) + new_title = '' + journals = r.journal_ids + if title == 'General Ledger': + journals = r.journal_ids + new_title = 'General Ledger' + if title == 'Bank Book': + journals = self.env['account.journal'].search([('type', '=', 'bank')], + limit=1) + new_title = 'Bank Book' + if title == 'Cash Book': + journals = self.env['account.journal'].search([('type', '=', 'cash')], + limit=1) + new_title = 'Cash Book' + r.write({ + 'titles': new_title, + }) + data = { + 'display_account': r.display_account, + 'model':self, + 'journals': journals, + 'target_move': r.target_move, + 'accounts': r.account_ids, + 'account_tags': r.account_tag_ids, + 'analytics': r.analytic_ids, + 'analytic_tags': r.analytic_tag_ids, + + } + if r.date_from: + data.update({ + 'date_from': r.date_from, + }) + if r.date_to: + data.update({ + 'date_to': r.date_to, + }) + + filters = self.get_filter(option) + records = self._get_report_values(data) + currency = self._get_currency() + return { + 'name': new_title, + 'type': 'ir.actions.client', + 'tag': 'g_l', + 'filters': filters, + 'report_lines': records['Accounts'], + 'debit_total': records['debit_total'], + 'credit_total': records['credit_total'], + 'debit_balance': records['debit_balance'], + 'currency': currency, + } + + def get_filter(self, option): + data = self.get_filter_data(option) + filters = {} + if data.get('journal_ids'): + filters['journals'] = self.env['account.journal'].browse(data.get('journal_ids')).mapped('code') + else: + filters['journals'] = ['All'] + if data.get('account_ids', []): + filters['accounts'] = self.env['account.account'].browse(data.get('account_ids', [])).mapped('code') + else: + filters['accounts'] = ['All'] + if data.get('account_tag_ids', []): + filters['account_tags'] = data.get('account_tag_ids') + else: + filters['account_tags'] = ['All'] + if data.get('target_move'): + filters['target_move'] = data.get('target_move') + if data.get('date_from'): + filters['date_from'] = data.get('date_from') + if data.get('date_to'): + filters['date_to'] = data.get('date_to') + if data.get('analytic_ids', []): + filters['analytics'] = self.env['account.analytic.account'].browse( + data.get('analytic_ids', [])).mapped('name') + else: + filters['analytics'] = ['All'] + if data.get('analytic_tag_ids', []): + filters['account_tags'] = self.env['account.account.tag'].browse( + data.get('account_tag_ids', [])).mapped('name') + else: + filters['analytic_tags'] = ['All'] + + filters['company_id'] = '' + filters['accounts_list'] = data.get('accounts_list') + filters['account_tag_list'] = data.get('account_tag_list') + filters['journals_list'] = data.get('journals_list') + filters['analytic_list'] = data.get('analytic_list') + filters['analytic_tag_list'] = data.get('analytic_tag_list') + filters['company_name'] = data.get('company_name') + filters['target_move'] = data.get('target_move').capitalize() + + return filters + + def get_filter_data(self, option): + r = self.env['account.general.ledger'].search([('id', '=', option[0])]) + default_filters = {} + company_id = self.env.company + company_domain = [('company_id', '=', company_id.id)] + journals = r.journal_ids if r.journal_ids else self.env['account.journal'].search(company_domain) + accounts = self.account_ids if self.account_ids else self.env['account.account'].search(company_domain) + account_tags = r.account_tag_ids if r.account_tag_ids else self.env[ + 'account.account.tag'].search([]) + analytics = r.analytic_ids if r.analytic_ids else self.env['account.analytic.account'].search( + company_domain) + analytic_tags = r.analytic_tag_ids if r.analytic_tag_ids else self.env[ + 'account.analytic.tag'].search([]) + filter_dict = { + 'journal_ids': r.journal_ids.ids, + 'analytic_ids': r.analytic_ids.ids, + 'analytic_tag_ids': r.analytic_tag_ids.ids, + 'account_ids': r.account_ids.ids, + 'account_tag_ids': r.account_tag_ids.ids, + 'company_id': company_id.id, + 'date_from': r.date_from, + 'date_to': r.date_to, + 'target_move': r.target_move, + 'journals_list': [(j.id, j.name, j.code) for j in journals], + 'accounts_list': [(a.id, a.name) for a in accounts], + 'account_tag_list': [(a.id, a.name) for a in account_tags], + 'analytic_list': [(anl.id, anl.name) for anl in analytics], + 'analytic_tag_list': [(anltag.id, anltag.name) for anltag in analytic_tags], + 'company_name': company_id and company_id.name, + } + filter_dict.update(default_filters) + return filter_dict + + def _get_report_values(self, data): + docs = data['model'] + display_account = data['display_account'] + init_balance = True + journals = data['journals'] + accounts = self.env['account.account'].search([]) + if not accounts: + raise UserError(_("No Accounts Found! Please Add One")) + account_res = self._get_accounts(accounts, init_balance, display_account, data) + debit_total = 0 + debit_total = sum(x['debit'] for x in account_res) + credit_total = sum(x['credit'] for x in account_res) + debit_balance = round(debit_total,2) - round(credit_total,2) + return { + 'doc_ids': self.ids, + 'debit_total': debit_total, + 'credit_total': credit_total, + 'debit_balance':debit_balance, + 'docs': docs, + 'time': time, + 'Accounts': account_res, + } + + @api.model + def create(self, vals): + vals['target_move'] = 'posted' + res = super(GeneralView, self).create(vals) + return res + + def write(self, vals): + if vals.get('target_move'): + vals.update({'target_move': vals.get('target_move').lower()}) + if vals.get('journal_ids'): + vals.update({'journal_ids': [(6, 0, vals.get('journal_ids'))]}) + if vals.get('journal_ids') == []: + vals.update({'journal_ids': [(5,)]}) + if vals.get('account_ids'): + vals.update({'account_ids': [(4, j) for j in vals.get('account_ids')]}) + if vals.get('account_ids') == []: + vals.update({'account_ids': [(5,)]}) + if vals.get('account_tag_ids'): + vals.update({'account_tag_ids': [(4, j) for j in vals.get('account_tag_ids')]}) + if vals.get('account_tag_ids') == []: + vals.update({'account_tag_ids': [(5,)]}) + if vals.get('analytic_ids'): + vals.update({'analytic_ids': [(4, j) for j in vals.get('analytic_ids')]}) + if vals.get('analytic_ids') == []: + vals.update({'analytic_ids': [(5,)]}) + if vals.get('analytic_tag_ids') == []: + vals.update({'analytic_tag_ids': [(4, j) for j in vals.get('analytic_tag_ids')]}) + if vals.get('analytic_tag_ids') == []: + vals.update({'analytic_tag_ids': [(5,)]}) + res = super(GeneralView, self).write(vals) + return res + + def _get_accounts(self, accounts, init_balance, display_account, data): + + cr = self.env.cr + MoveLine = self.env['account.move.line'] + move_lines = {x: [] for x in accounts.ids} + + # Prepare initial sql query and Get the initial move lines + if init_balance and data.get('date_from'): + init_tables, init_where_clause, init_where_params = MoveLine.with_context( + date_from=self.env.context.get('date_from'), date_to=False, + initial_bal=True)._query_get() + init_wheres = [""] + if init_where_clause.strip(): + init_wheres.append(init_where_clause.strip()) + init_filters = " AND ".join(init_wheres) + filters = init_filters.replace('account_move_line__move_id', + 'm').replace('account_move_line', + 'l') + new_filter = filters + if data['target_move'] == 'posted': + new_filter += " AND m.state = 'posted'" + else: + new_filter += " AND m.state in ('draft','posted')" + if data.get('date_from'): + new_filter += " AND l.date < '%s'" % data.get('date_from') + if data['journals']: + new_filter += ' AND j.id IN %s' % str(tuple(data['journals'].ids) + tuple([0])) + if data.get('accounts'): + WHERE = "WHERE l.account_id IN %s" % str(tuple(data.get('accounts').ids) + tuple([0])) + else: + WHERE = "WHERE l.account_id IN %s" + if data.get('analytics'): + WHERE += ' AND anl.id IN %s' % str(tuple(data.get('analytics').ids) + tuple([0])) + if data.get('analytic_tags'): + WHERE += ' AND anltag.account_analytic_tag_id IN %s' % str( + tuple(data.get('analytic_tags').ids) + tuple([0])) + + + sql = ("""SELECT 0 AS lid, l.account_id AS account_id, '' AS ldate, '' AS lcode, 0.0 AS amount_currency, '' AS lref, 'Initial Balance' AS lname, COALESCE(SUM(l.debit),0.0) AS debit, COALESCE(SUM(l.credit),0.0) AS credit, COALESCE(SUM(l.debit),0) - COALESCE(SUM(l.credit), 0) as balance, '' AS lpartner_id,\ + '' AS move_name, '' AS mmove_id, '' AS currency_code,\ + NULL AS currency_id,\ + '' AS invoice_id, '' AS invoice_type, '' AS invoice_number,\ + '' AS partner_name\ + FROM account_move_line l\ + LEFT JOIN account_move m ON (l.move_id=m.id)\ + LEFT JOIN res_currency c ON (l.currency_id=c.id)\ + LEFT JOIN res_partner p ON (l.partner_id=p.id)\ + LEFT JOIN account_move i ON (m.id =i.id)\ + LEFT JOIN account_account_tag_account_move_line_rel acc ON (acc.account_move_line_id=l.id) + LEFT JOIN account_analytic_account anl ON (l.analytic_account_id=anl.id) + LEFT JOIN account_analytic_tag_account_move_line_rel anltag ON (anltag.account_move_line_id=l.id) + JOIN account_journal j ON (l.journal_id=j.id)""" + + WHERE + new_filter + ' GROUP BY l.account_id') + if data.get('accounts'): + params = tuple(init_where_params) + else: + params = (tuple(accounts.ids),) + tuple(init_where_params) + cr.execute(sql, params) + for row in cr.dictfetchall(): + row['m_id'] = row['account_id'] + move_lines[row.pop('account_id')].append(row) + + tables, where_clause, where_params = MoveLine._query_get() + wheres = [""] + if where_clause.strip(): + wheres.append(where_clause.strip()) + final_filters = " AND ".join(wheres) + final_filters = final_filters.replace('account_move_line__move_id', 'm').replace( + 'account_move_line', 'l') + new_final_filter = final_filters + if data['target_move'] == 'posted': + new_final_filter += " AND m.state = 'posted'" + else: + new_final_filter += " AND m.state in ('draft','posted')" + if data.get('date_from'): + new_final_filter += " AND l.date >= '%s'" % data.get('date_from') + if data.get('date_to'): + new_final_filter += " AND l.date <= '%s'" % data.get('date_to') + + if data['journals']: + new_final_filter += ' AND j.id IN %s' % str(tuple(data['journals'].ids) + tuple([0])) + if data.get('accounts'): + WHERE = "WHERE l.account_id IN %s" % str(tuple(data.get('accounts').ids) + tuple([0])) + else: + WHERE = "WHERE l.account_id IN %s" + if data.get('analytics'): + WHERE += ' AND anl.id IN %s' % str(tuple(data.get('analytics').ids) + tuple([0])) + + if data.get('analytic_tags'): + WHERE += ' AND anltag.account_analytic_tag_id IN %s' % str( + tuple(data.get('analytic_tags').ids) + tuple([0])) + + # Get move lines base on sql query and Calculate the total balance of move lines + sql = ('''SELECT l.id AS lid,m.id AS move_id, l.account_id AS account_id, l.date AS ldate, j.code AS lcode, l.currency_id, l.amount_currency, l.ref AS lref, l.name AS lname, COALESCE(l.debit,0) AS debit, COALESCE(l.credit,0) AS credit, COALESCE(SUM(l.balance),0) AS balance,\ + m.name AS move_name, c.symbol AS currency_code, p.name AS partner_name\ + FROM account_move_line l\ + JOIN account_move m ON (l.move_id=m.id)\ + LEFT JOIN res_currency c ON (l.currency_id=c.id)\ + LEFT JOIN res_partner p ON (l.partner_id=p.id)\ + LEFT JOIN account_analytic_account anl ON (l.analytic_account_id=anl.id) + LEFT JOIN account_account_tag_account_move_line_rel acc ON (acc.account_move_line_id=l.id) + LEFT JOIN account_analytic_tag_account_move_line_rel anltag ON (anltag.account_move_line_id=l.id) + JOIN account_journal j ON (l.journal_id=j.id)\ + JOIN account_account a ON (l.account_id = a.id) ''' + + WHERE + new_final_filter + ''' GROUP BY l.id, m.id, l.account_id, l.date, j.code, l.currency_id, l.amount_currency, l.ref, l.name, m.name, c.symbol, c.position, p.name''' ) + if data.get('accounts'): + params = tuple(where_params) + else: + params = (tuple(accounts.ids),) + tuple(where_params) + cr.execute(sql, params) + + for row in cr.dictfetchall(): + balance = 0 + for line in move_lines.get(row['account_id']): + balance += round(line['debit'],2) - round(line['credit'],2) + row['balance'] += round(balance,2) + row['m_id'] = row['account_id'] + move_lines[row.pop('account_id')].append(row) + + # Calculate the debit, credit and balance for Accounts + account_res = [] + for account in accounts: + currency = account.currency_id and account.currency_id or account.company_id.currency_id + res = dict((fn, 0.0) for fn in ['credit', 'debit', 'balance']) + res['code'] = account.code + res['name'] = account.name + res['id'] = account.id + res['move_lines'] = move_lines[account.id] + for line in res.get('move_lines'): + res['debit'] += round(line['debit'],2) + res['credit'] += round(line['credit'],2) + res['balance'] = round(line['balance'],2) + if display_account == 'all': + account_res.append(res) + if display_account == 'movement' and res.get('move_lines'): + account_res.append(res) + if display_account == 'not_zero' and not currency.is_zero( + res['balance']): + account_res.append(res) + + return account_res + + @api.model + def _get_currency(self): + journal = self.env['account.journal'].browse( + self.env.context.get('default_journal_id', False)) + if journal.currency_id: + return journal.currency_id.id + lang = self.env.user.lang + if not lang: + lang = 'en_US' + lang = lang.replace("_", '-') + currency_array = [self.env.company.currency_id.symbol, + self.env.company.currency_id.position,lang] + return currency_array + + 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', filters.get('company_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.merge_range('A5:J6', ' Journals: ' + ', '.join( + [lt or '' for lt in + filters['journals']]) + ' Accounts: ' + ', '.join( + [lt or '' for lt in + filters['accounts']]) + ' Account 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'), + 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()
\ No newline at end of file diff --git a/dynamic_accounts_report/wizard/partner_leadger.py b/dynamic_accounts_report/wizard/partner_leadger.py new file mode 100644 index 0000000..f7bddd8 --- /dev/null +++ b/dynamic_accounts_report/wizard/partner_leadger.py @@ -0,0 +1,456 @@ +import time +from odoo import fields, models, api, _ + +import io +import json +from odoo.exceptions import AccessError, UserError, AccessDenied + +try: + from odoo.tools.misc import xlsxwriter +except ImportError: + import xlsxwriter + + +class PartnerView(models.TransientModel): + _inherit = "account.common.report" + _name = 'account.partner.ledger' + + journal_ids = fields.Many2many('account.journal', + string='Journals', required=True, + default=[]) + account_ids = fields.Many2many( + "account.account", + string="Accounts", check_company=True, + ) + + display_account = fields.Selection( + [('all', 'All'), ('movement', 'With movements'), + ('not_zero', 'With balance is not equal to 0')], + string='Display Accounts', required=True, default='movement') + + partner_ids = fields.Many2many('res.partner', string='Partner') + partner_category_ids = fields.Many2many('res.partner.category', + string='Partner tags') + reconciled = fields.Selection([ + ('unreconciled', 'Unreconciled Only')], + string='Reconcile Type', default='unreconciled') + + account_type_ids = fields.Many2many('account.account.type',string='Account Type', + domain=[('type', 'in', ('receivable', 'payable'))]) + + @api.model + def view_report(self, option): + r = self.env['account.partner.ledger'].search([('id', '=', option[0])]) + data = { + 'display_account': r.display_account, + 'model': self, + 'journals': r.journal_ids, + 'accounts': r.account_ids, + 'target_move': r.target_move, + 'partners': r.partner_ids, + 'reconciled': r.reconciled, + 'account_type': r.account_type_ids, + 'partner_tags': r.partner_category_ids, + } + + if r.date_from: + data.update({ + 'date_from':r.date_from, + }) + if r.date_to: + data.update({ + 'date_to':r.date_to, + }) + + filters = self.get_filter(option) + records = self._get_report_values(data) + currency = self._get_currency() + + return { + 'name': "partner Ledger", + 'type': 'ir.actions.client', + 'tag': 'p_l', + 'filters': filters, + 'report_lines': records['Partners'], + 'debit_total': records['debit_total'], + 'credit_total': records['credit_total'], + 'debit_balance': records['debit_balance'], + 'currency': currency, + } + + def get_filter(self, option): + data = self.get_filter_data(option) + + filters = {} + if data.get('journal_ids'): + filters['journals'] = self.env['account.journal'].browse(data.get('journal_ids')).mapped('code') + else: + filters['journals'] = ['All'] + if data.get('account_ids', []): + filters['accounts'] = self.env['account.account'].browse(data.get('account_ids', [])).mapped('code') + else: + filters['accounts'] = ['All Payable and Receivable'] + if data.get('target_move'): + filters['target_move'] = data.get('target_move').capitalize() + if data.get('date_from'): + filters['date_from'] = data.get('date_from') + if data.get('date_to'): + filters['date_to'] = data.get('date_to') + + filters['company_id'] = '' + filters['accounts_list'] = data.get('accounts_list') + filters['journals_list'] = data.get('journals_list') + + filters['company_name'] = data.get('company_name') + + if data.get('partners'): + filters['partners'] = self.env['res.partner'].browse( + data.get('partners')).mapped('name') + else: + filters['partners'] = ['All'] + + if data.get('reconciled') == 'unreconciled': + filters['reconciled'] = 'Unreconciled' + + if data.get('account_type', []): + filters['account_type'] = self.env['account.account.type'].browse(data.get('account_type', [])).mapped('name') + else: + filters['account_type'] = ['Receivable and Payable'] + + if data.get('partner_tags', []): + filters['partner_tags'] = self.env['res.partner.category'].browse( + data.get('partner_tags', [])).mapped('name') + else: + filters['partner_tags'] = ['All'] + + filters['partners_list'] = data.get('partners_list') + filters['category_list'] = data.get('category_list') + filters['account_type_list'] = data.get('account_type_list') + filters['target_move'] = data.get('target_move').capitalize() + return filters + + def get_filter_data(self, option): + r = self.env['account.partner.ledger'].search([('id', '=', option[0])]) + default_filters = {} + company_id = self.env.company + company_domain = [('company_id', '=', company_id.id)] + journals = r.journal_ids if r.journal_ids else self.env['account.journal'].search(company_domain) + accounts = self.account_ids if self.account_ids else self.env['account.account'].search(company_domain) + + partner = r.partner_ids if r.partner_ids else self.env[ + 'res.partner'].search([]) + categories = self.partner_category_ids if self.partner_category_ids \ + else self.env['res.partner.category'].search([]) + account_types = r.account_type_ids if r.account_type_ids \ + else self.env['account.account.type'].search([('type', 'in', ('receivable', 'payable'))]) + + filter_dict = { + 'journal_ids': r.journal_ids.ids, + 'account_ids': r.account_ids.ids, + 'company_id': company_id.id, + 'date_from': r.date_from, + 'date_to': r.date_to, + 'target_move': r.target_move, + 'journals_list': [(j.id, j.name, j.code) for j in journals], + 'accounts_list': [(a.id, a.name) for a in accounts], + 'company_name': company_id and company_id.name, + 'partners': r.partner_ids.ids, + 'reconciled': r.reconciled, + 'account_type': r.account_type_ids.ids, + 'partner_tags': r.partner_category_ids.ids, + 'partners_list': [(p.id, p.name) for p in partner], + 'category_list': [(c.id, c.name) for c in categories], + 'account_type_list': [(t.id, t.name) for t in account_types], + + } + filter_dict.update(default_filters) + return filter_dict + + def _get_report_values(self, data): + docs = data['model'] + display_account = data['display_account'] + init_balance = True + accounts = self.env['account.account'].search([('user_type_id.type', 'in', ('receivable', 'payable')), + ('company_id', '=', self.env.company.id)]) + if data['account_type']: + accounts = self.env['account.account'].search( + [('user_type_id.id', 'in', data['account_type'].ids),('company_id', '=', self.env.company.id)]) + + partners = self.env['res.partner'].search([]) + + if data['partner_tags']: + partners = self.env['res.partner'].search( + [('category_id', 'in', data['partner_tags'].ids)]) + if not accounts: + raise UserError(_("No Accounts Found! Please Add One")) + partner_res = self._get_partners(partners,accounts, init_balance, display_account, data) + + debit_total = 0 + debit_total = sum(x['debit'] for x in partner_res) + credit_total = sum(x['credit'] for x in partner_res) + debit_balance = round(debit_total,2) - round(credit_total,2) + return { + 'doc_ids': self.ids, + 'debit_total': debit_total, + 'credit_total': credit_total, + 'debit_balance':debit_balance, + 'docs': docs, + 'time': time, + 'Partners': partner_res, + } + + @api.model + def create(self, vals): + vals['target_move'] = 'posted' + res = super(PartnerView, self).create(vals) + return res + + def write(self, vals): + if vals.get('target_move'): + vals.update({'target_move': vals.get('target_move').lower()}) + if vals.get('journal_ids'): + vals.update({'journal_ids': [(6, 0, vals.get('journal_ids'))]}) + if not vals.get('journal_ids'): + vals.update({'journal_ids': [(5,)]}) + if vals.get('account_ids'): + vals.update({'account_ids': [(4, j) for j in vals.get('account_ids')]}) + if not vals.get('account_ids'): + vals.update({'account_ids': [(5,)]}) + if vals.get('partner_ids'): + vals.update( + {'partner_ids': [(4, j) for j in vals.get('partner_ids')]}) + if not vals.get('partner_ids'): + vals.update({'partner_ids': [(5,)]}) + if vals.get('partner_category_ids'): + vals.update({'partner_category_ids': [(4, j) for j in vals.get( + 'partner_category_ids')]}) + if not vals.get('partner_category_ids'): + vals.update({'partner_category_ids': [(5,)]}) + + if vals.get('account_type-ids'): + vals.update( + {'account_type_ids': [(4, j) for j in vals.get('account_type_ids')]}) + if not vals.get('account_type_ids'): + vals.update({'account_type_ids': [(5,)]}) + + res = super(PartnerView, self).write(vals) + return res + + def _get_partners(self, partners, accounts, init_balance, display_account, data): + + cr = self.env.cr + move_line = self.env['account.move.line'] + move_lines = {x: [] for x in partners.ids} + currency_id = self.env.company.currency_id + + tables, where_clause, where_params = move_line._query_get() + wheres = [""] + if where_clause.strip(): + wheres.append(where_clause.strip()) + final_filters = " AND ".join(wheres) + final_filters = final_filters.replace('account_move_line__move_id', 'm').replace( + 'account_move_line', 'l') + new_final_filter = final_filters + if data['target_move'] == 'posted': + new_final_filter += " AND m.state = 'posted'" + else: + new_final_filter += " AND m.state in ('draft','posted')" + if data.get('date_from'): + new_final_filter += " AND l.date >= '%s'" % data.get('date_from') + if data.get('date_to'): + new_final_filter += " AND l.date <= '%s'" % data.get('date_to') + + if data['journals']: + new_final_filter += ' AND j.id IN %s' % str(tuple(data['journals'].ids) + tuple([0])) + + if data.get('accounts'): + WHERE = "WHERE l.account_id IN %s" % str(tuple(data.get('accounts').ids) + tuple([0])) + else: + WHERE = "WHERE l.account_id IN %s" + + if data.get('partners'): + WHERE += ' AND p.id IN %s' % str( + tuple(data.get('partners').ids) + tuple([0])) + + if data.get('reconciled') == 'unreconciled': + WHERE += ' AND l.full_reconcile_id is null AND' \ + ' l.balance != 0 AND a.reconcile is true' + + sql = ('''SELECT l.id AS lid,l.partner_id AS partner_id,m.id AS move_id, + l.account_id AS account_id, l.date AS ldate, j.code AS lcode, l.currency_id, + l.amount_currency, l.ref AS lref, l.name AS lname, + COALESCE(l.debit,0) AS debit, COALESCE(l.credit,0) AS credit, + COALESCE(SUM(l.balance),0) AS balance,\ + m.name AS move_name, c.symbol AS currency_code,c.position AS currency_position, p.name AS partner_name\ + FROM account_move_line l\ + JOIN account_move m ON (l.move_id=m.id)\ + JOIN account_account a ON (l.account_id=a.id) + LEFT JOIN res_currency c ON (l.currency_id=c.id)\ + LEFT JOIN res_partner p ON (l.partner_id=p.id)\ + JOIN account_journal j ON (l.journal_id=j.id)\ + JOIN account_account acc ON (l.account_id = acc.id) ''' + + WHERE + new_final_filter + ''' GROUP BY l.id, m.id, l.account_id, l.date, j.code, l.currency_id, l.amount_currency, l.ref, l.name, m.name, c.symbol, c.position, p.name''' ) + if data.get('accounts'): + params = tuple(where_params) + else: + params = (tuple(accounts.ids),) + tuple(where_params) + cr.execute(sql, params) + + account_list = { x.id : {'name' : x.name, 'code': x.code} for x in accounts} + + for row in cr.dictfetchall(): + balance = 0 + if row['partner_id'] in move_lines: + for line in move_lines.get(row['partner_id']): + balance += round(line['debit'],2) - round(line['credit'],2) + row['balance'] += (round(balance, 2)) + row['m_id'] = row['account_id'] + row['account_name'] = account_list[row['account_id']]['name'] + "(" +account_list[row['account_id']]['code'] + ")" + move_lines[row.pop('partner_id')].append(row) + + partner_res = [] + for partner in partners: + company_id = self.env.company + currency = company_id.currency_id + res = dict((fn, 0.0) for fn in ['credit', 'debit', 'balance']) + res['name'] = partner.name + res['id'] = partner.id + res['move_lines'] = move_lines[partner.id] + for line in res.get('move_lines'): + res['debit'] += round(line['debit'], 2) + res['credit'] += round(line['credit'], 2) + res['balance'] = round(line['balance'], 2) + if display_account == 'all': + partner_res.append(res) + if display_account == 'movement' and res.get('move_lines'): + partner_res.append(res) + if display_account == 'not_zero' and not currency.is_zero( + res['balance']): + partner_res.append(res) + return partner_res + + @api.model + def _get_currency(self): + journal = self.env['account.journal'].browse( + self.env.context.get('default_journal_id', False)) + if journal.currency_id: + return journal.currency_id.id + lang = self.env.user.lang + if not lang: + lang = 'en_US' + lang = lang.replace("_", '-') + currency_array = [self.env.company.currency_id.symbol, + self.env.company.currency_id.position, lang] + return currency_array + + def get_dynamic_xlsx_report(self, data, response, report_data, dfr_data): + report_data = json.loads(report_data) + filters = json.loads(data) + + output = io.BytesIO() + workbook = xlsxwriter.Workbook(output, {'in_memory': True}) + cell_format = workbook.add_format( + {'align': 'center', 'bold': True, + 'border': 0 + }) + sheet = workbook.add_worksheet() + head = workbook.add_format({'align': 'center', 'bold': True, + 'font_size': '20px'}) + + txt = workbook.add_format({'font_size': '10px', 'border': 1}) + sub_heading_sub = workbook.add_format( + {'align': 'center', 'bold': True, 'font_size': '10px', + 'border': 1, + 'border_color': 'black'}) + sheet.merge_range('A1:H2', + filters.get('company_name') + ':' + 'Partner Ledger', + head) + date_head = workbook.add_format({'align': 'center', 'bold': True, + 'font_size': '10px'}) + + sheet.merge_range('A4:B4', + 'Target Moves: ' + filters.get('target_move'), + date_head) + + sheet.merge_range('C4:D4', 'Account Type: ' + ', ' .join( + [lt or '' for lt in + filters['account_type']]), + date_head) + sheet.merge_range('E3:F3', ' Partners: ' + ', '.join( + [lt or '' for lt in + filters['partners']]), date_head) + sheet.merge_range('G3:H3', ' Partner Tags: ' + ', '.join( + [lt or '' for lt in + filters['partner_tags']]), + date_head) + sheet.merge_range('A3:B3', ' Journals: ' + ', '.join( + [lt or '' for lt in + filters['journals']]), + date_head) + sheet.merge_range('C3:D3', ' Accounts: ' + ', '.join( + [lt or '' for lt in + filters['accounts']]), + date_head) + + if filters.get('date_from') and filters.get('date_to'): + sheet.merge_range('E4:F4', 'From: ' + filters.get('date_from'), + date_head) + + sheet.merge_range('G4:H4', 'To: ' + filters.get('date_to'), + date_head) + elif filters.get('date_from'): + sheet.merge_range('E4:F4', 'From: ' + filters.get('date_from'), + date_head) + elif filters.get('date_to'): + sheet.merge_range('E4:F4', 'To: ' + filters.get('date_to'), + date_head) + + sheet.merge_range('A5:E5', 'Partner', cell_format) + sheet.write('F5', 'Debit', cell_format) + sheet.write('G5', 'Credit', cell_format) + sheet.write('H5', 'Balance', cell_format) + + row = 4 + col = 0 + + sheet.set_column(0, 0, 15) + sheet.set_column(1, 1, 15) + sheet.set_column(2, 2, 25) + sheet.set_column(3, 3, 15) + sheet.set_column(4, 4, 36) + sheet.set_column(5, 5, 15) + sheet.set_column(6, 6, 15) + sheet.set_column(7, 7, 15) + + for report in report_data: + + row += 1 + sheet.merge_range(row, col + 0, row, col + 4, report['name'], + sub_heading_sub) + sheet.write(row, col + 5, report['debit'], sub_heading_sub) + sheet.write(row, col + 6, report['credit'], sub_heading_sub) + sheet.write(row, col + 7, report['balance'], sub_heading_sub) + row += 1 + sheet.write(row, col + 0, 'Date', cell_format) + sheet.write(row, col + 1, 'JRNL', cell_format) + sheet.write(row, col + 2, 'Account', cell_format) + sheet.write(row, col + 3, 'Move', cell_format) + sheet.write(row, col + 4, 'Entry Label', cell_format) + sheet.write(row, col + 5, 'Debit', cell_format) + sheet.write(row, col + 6, 'Credit', cell_format) + sheet.write(row, col + 7, 'Balance', cell_format) + for r_rec in report['move_lines']: + row += 1 + sheet.write(row, col + 0, r_rec['ldate'], txt) + sheet.write(row, col + 1, r_rec['lcode'], txt) + sheet.write(row, col + 2, r_rec['account_name'], txt) + sheet.write(row, col + 3, r_rec['move_name'], txt) + sheet.write(row, col + 4, r_rec['lname'], txt) + sheet.write(row, col + 5, r_rec['debit'], txt) + sheet.write(row, col + 6, r_rec['credit'], txt) + sheet.write(row, col + 7, r_rec['balance'], txt) + + workbook.close() + output.seek(0) + response.stream.write(output.read()) + output.close() diff --git a/dynamic_accounts_report/wizard/trial_balance.py b/dynamic_accounts_report/wizard/trial_balance.py new file mode 100644 index 0000000..563a88c --- /dev/null +++ b/dynamic_accounts_report/wizard/trial_balance.py @@ -0,0 +1,324 @@ +import time +from odoo import fields, models, api, _ + +import io +import json +from odoo.exceptions import AccessError, UserError, AccessDenied + +try: + from odoo.tools.misc import xlsxwriter +except ImportError: + import xlsxwriter + + +class TrialView(models.TransientModel): + _inherit = "account.common.report" + _name = 'account.trial.balance' + + journal_ids = fields.Many2many('account.journal', + + string='Journals', required=True, + default=[]) + display_account = fields.Selection( + [('all', 'All'), ('movement', 'With movements'), + ('not_zero', 'With balance is not equal to 0')], + string='Display Accounts', required=True, default='movement') + + @api.model + def view_report(self, option): + r = self.env['account.trial.balance'].search([('id', '=', option[0])]) + + data = { + 'display_account': r.display_account, + 'model':self, + 'journals': r.journal_ids, + 'target_move': r.target_move, + + } + if r.date_from: + data.update({ + 'date_from':r.date_from, + }) + if r.date_to: + data.update({ + 'date_to':r.date_to, + }) + + filters = self.get_filter(option) + records = self._get_report_values(data) + currency = self._get_currency() + + return { + 'name': "Trial Balance", + 'type': 'ir.actions.client', + 'tag': 't_b', + 'filters': filters, + 'report_lines': records['Accounts'], + 'debit_total': records['debit_total'], + 'credit_total': records['credit_total'], + 'currency': currency, + } + + def get_filter(self, option): + data = self.get_filter_data(option) + filters = {} + if data.get('journal_ids'): + filters['journals'] = self.env['account.journal'].browse(data.get('journal_ids')).mapped('code') + else: + filters['journals'] = ['All'] + if data.get('target_move'): + filters['target_move'] = data.get('target_move') + if data.get('date_from'): + filters['date_from'] = data.get('date_from') + if data.get('date_to'): + filters['date_to'] = data.get('date_to') + + filters['company_id'] = '' + filters['journals_list'] = data.get('journals_list') + filters['company_name'] = data.get('company_name') + filters['target_move'] = data.get('target_move').capitalize() + + return filters + + def get_filter_data(self, option): + r = self.env['account.trial.balance'].search([('id', '=', option[0])]) + default_filters = {} + company_id = self.env.company + company_domain = [('company_id', '=', company_id.id)] + journals = r.journal_ids if r.journal_ids else self.env['account.journal'].search(company_domain) + + filter_dict = { + 'journal_ids': r.journal_ids.ids, + 'company_id': company_id.id, + 'date_from': r.date_from, + 'date_to': r.date_to, + 'target_move': r.target_move, + 'journals_list': [(j.id, j.name, j.code) for j in journals], + 'company_name': company_id and company_id.name, + } + filter_dict.update(default_filters) + return filter_dict + + def _get_report_values(self, data): + docs = data['model'] + display_account = data['display_account'] + journals = data['journals'] + accounts = self.env['account.account'].search([]) + if not accounts: + raise UserError(_("No Accounts Found! Please Add One")) + account_res = self._get_accounts(accounts, display_account, data) + debit_total = 0 + debit_total = sum(x['debit'] for x in account_res) + credit_total = sum(x['credit'] for x in account_res) + return { + 'doc_ids': self.ids, + 'debit_total': debit_total, + 'credit_total': credit_total, + 'docs': docs, + 'time': time, + 'Accounts': account_res, + } + + @api.model + def create(self, vals): + vals['target_move'] = 'posted' + res = super(TrialView, self).create(vals) + return res + + def write(self, vals): + if vals.get('target_move'): + vals.update({'target_move': vals.get('target_move').lower()}) + if vals.get('journal_ids'): + vals.update({'journal_ids': [(6, 0, vals.get('journal_ids'))]}) + if vals.get('journal_ids') == []: + vals.update({'journal_ids': [(5,)]}) + res = super(TrialView, self).write(vals) + return res + + def _get_accounts(self, accounts, display_account, data): + + account_result = {} + # Prepare sql query base on selected parameters from wizard + tables, where_clause, where_params = self.env['account.move.line']._query_get() + tables = tables.replace('"', '') + if not tables: + tables = 'account_move_line' + wheres = [""] + if where_clause.strip(): + wheres.append(where_clause.strip()) + filters = " AND ".join(wheres) + if data['target_move'] == 'posted': + filters += " AND account_move_line__move_id.state = 'posted'" + else: + filters += " AND account_move_line__move_id.state in ('draft','posted')" + if data.get('date_from'): + filters += " AND account_move_line.date >= '%s'" % data.get('date_from') + if data.get('date_to'): + filters += " AND account_move_line.date <= '%s'" % data.get('date_to') + + if data['journals']: + filters += ' AND jrnl.id IN %s' % str(tuple(data['journals'].ids) + tuple([0])) + tables += 'JOIN account_journal jrnl ON (account_move_line.journal_id=jrnl.id)' + # compute the balance, debit and credit for the provided accounts + request = ( + "SELECT account_id AS id, SUM(debit) AS debit, SUM(credit) AS credit, (SUM(debit) - SUM(credit)) AS balance" + \ + " FROM " + tables + " WHERE account_id IN %s " + filters + " GROUP BY account_id") + params = (tuple(accounts.ids),) + tuple(where_params) + self.env.cr.execute(request, params) + for row in self.env.cr.dictfetchall(): + account_result[row.pop('id')] = row + + account_res = [] + for account in accounts: + res = dict((fn, 0.0) for fn in ['credit', 'debit', 'balance']) + currency = account.currency_id and account.currency_id or account.company_id.currency_id + res['code'] = account.code + res['name'] = account.name + res['id'] = account.id + if data.get('date_from'): + + res['Init_balance'] = self.get_init_bal(account, display_account, data) + + if account.id in account_result: + res['debit'] = account_result[account.id].get('debit') + res['credit'] = account_result[account.id].get('credit') + res['balance'] = account_result[account.id].get('balance') + if display_account == 'all': + account_res.append(res) + if display_account == 'not_zero' and not currency.is_zero( + res['balance']): + account_res.append(res) + if display_account == 'movement' and ( + not currency.is_zero(res['debit']) or not currency.is_zero( + res['credit'])): + account_res.append(res) + return account_res + + def get_init_bal(self, account, display_account, data): + if data.get('date_from'): + + tables, where_clause, where_params = self.env[ + 'account.move.line']._query_get() + tables = tables.replace('"', '') + if not tables: + tables = 'account_move_line' + wheres = [""] + if where_clause.strip(): + wheres.append(where_clause.strip()) + filters = " AND ".join(wheres) + if data['target_move'] == 'posted': + filters += " AND account_move_line__move_id.state = 'posted'" + else: + filters += " AND account_move_line__move_id.state in ('draft','posted')" + if data.get('date_from'): + filters += " AND account_move_line.date < '%s'" % data.get('date_from') + + if data['journals']: + filters += ' AND jrnl.id IN %s' % str(tuple(data['journals'].ids) + tuple([0])) + tables += 'JOIN account_journal jrnl ON (account_move_line.journal_id=jrnl.id)' + + # compute the balance, debit and credit for the provided accounts + request = ( + "SELECT account_id AS id, SUM(debit) AS debit, SUM(credit) AS credit, (SUM(debit) - SUM(credit)) AS balance" + \ + " FROM " + tables + " WHERE account_id = %s" % account.id + filters + " GROUP BY account_id") + params = tuple(where_params) + self.env.cr.execute(request, params) + for row in self.env.cr.dictfetchall(): + return row + + @api.model + def _get_currency(self): + journal = self.env['account.journal'].browse( + self.env.context.get('default_journal_id', False)) + if journal.currency_id: + return journal.currency_id.id + lang = self.env.user.lang + if not lang: + lang = 'en_US' + lang = lang.replace("_", '-') + currency_array = [self.env.company.currency_id.symbol, + self.env.company.currency_id.position, + lang] + return currency_array + + 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', filters.get('company_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() |