# -*- coding: utf-8 -*- import datetime from collections import defaultdict from odoo import api, models, fields from odoo.exceptions import UserError, MissingError BALANCE_DIRECTION = [('debit', '借'), ('credit', '贷'), ('balance', '平')] ACCOUNTING_ELEMENTS = [ ('asset', '资产'), ('liability', '负债'), ('equity', '所有者权益'), ('income', '收入'), ('expense', '费用'), ('profit', '利润'), ] ACCOUNT_LEVEL = [ ('1', '总分类科目'), ('2', '二级明细科目'), ('3', '三级明细科目'), ('4', '四级明细科目'), ('5', '五级明细科目'), ('6', '六级明细科目'), ('7', '七级明细科目'), ('8', '八级明细科目'), ('9', '九级明细科目'), ('10', '十级明细科目'), ] def balance_direction_to_debit_credit(balance, direction): if direction == 'debit' and balance > 0: debit = balance credit = 0 elif direction == 'debit' and balance < 0: debit = 0 credit = - balance elif direction == 'credit' and balance > 0: debit = 0 credit = balance elif direction == 'credit' and balance < 0: debit = - balance credit = 0 elif direction == 'balance' or balance == 0: debit = credit = 0 else: raise ValueError('参数direction的值不正确！') return debit, credit def balance_to_balance_direction(balance): if balance > 0: balance = balance direction = 'debit' elif balance < 0: balance = - balance direction = 'credit' else: balance = 0 direction = 'balance' return balance, direction class InquiryWizardMixin(models.AbstractModel): _name = 'inquiry.wizard.mixin' _description = '查询向导基类' fiscalyear_from = fields.Many2one('fr.account.fiscalyear', string='开始年度', required=True, ondelete='cascade') fiscalyear_to = fields.Many2one('fr.account.fiscalyear', string='结束年度', required=True, ondelete='cascade') period_from = fields.Many2one('fr.account.period', string='开始期间', required=True, ondelete='cascade') period_to = fields.Many2one('fr.account.period', string='结束期间', required=True, ondelete='cascade') account_ids = fields.Many2many('account.account', string='科目') account_from = fields.Many2one('account.account', string='起始科目') account_to = fields.Many2one('account.account', string='结束科目') level_from = fields.Selection(ACCOUNT_LEVEL, string='起始等级') level_to = fields.Selection(ACCOUNT_LEVEL, string='结束等级') level_is = fields.Boolean(string='等级非连续查询') level_from_one = fields.Selection(ACCOUNT_LEVEL, string='总分类科目', default='1') level_to_all = fields.Selection(ACCOUNT_LEVEL, string='其他等级') internal_group = fields.Selection(ACCOUNTING_ELEMENTS, string='会计要素') user_type_id = fields.Many2one('account.account.type', string='科目类型') include_unpost = fields.Boolean(string='包含未过账凭证') def get_accounts(self): self.ensure_one() accounts = self.account_ids if not self.account_ids: accounts = self.env['account.account'].search( [('company_id', '=', self.period_from.company_id.id), ('state', '=', 'on_use')]) # 筛选科目会计要素 if self.internal_group: accounts = accounts.filtered(lambda x: x.internal_group == self.internal_group) # 筛选科目类型 if self.user_type_id: accounts = accounts.filtered(lambda x: x.user_type_id == self.user_type_id) # 筛选科目代码 if self.account_from and self.account_to: accounts = accounts.filtered(lambda x: x.code >= self.account_from.code and x.code <= self.account_to.code) elif self.account_from: accounts = accounts.filtered(lambda x: x.code >= self.account_from.code) elif self.account_to: accounts = accounts.filtered(lambda x: x.code <= self.account_to.code) # 筛选科目等级 if self.level_from and self.level_to: accounts = accounts.filtered(lambda x: x.fr_account_level >= self.level_from and x.fr_account_level <= self.level_to) elif self.level_from: accounts = accounts.filtered(lambda x: x.fr_account_level >= self.level_from) elif self.level_to: accounts = accounts.filtered(lambda x: x.fr_account_level <= self.level_to) # 筛选科目等级 if self.level_is: if self.level_from_one and self.level_to_all: accounts = accounts.filtered(lambda x: x.fr_account_level == self.level_from_one or x.fr_account_level == self.level_to_all) return accounts @api.model def default_get(self, fields_list): defaults = super(InquiryWizardMixin, self).default_get(fields_list) fiscalyear = self.env['fr.account.fiscalyear'].search([], limit=1) defaults.update({ 'fiscalyear_from': fiscalyear.id, 'fiscalyear_to': fiscalyear.id, }) return defaults @api.onchange('fiscalyear_from') def _change_period_from(self): """自动获取开始期间""" if self.fiscalyear_from: period = self.env['fr.account.period'].search( [('fiscalyear_id', '=', self.fiscalyear_from.id), ('state', '!=', 'unuse')], limit=1) self.period_from = period @api.onchange('fiscalyear_to') def _change_period_to(self): """自动获取结束期间""" if self.fiscalyear_to: period = self.env['fr.account.period'].search( [('fiscalyear_id', '=', self.fiscalyear_to.id), ('state', '!=', 'unuse')], limit=1) self.period_to = period class BalanceInquiryWizard(models.TransientModel): """科目余额表查询向导""" _name = 'balance.inquiry.wizard' _inherit = 'inquiry.wizard.mixin' _description = '科目余额表查询向导' only_top_accounts = fields.Boolean(string='仅显示一级科目') hidden_no_occur = fields.Boolean(string='隐藏无发生额的科目') hidden_no_balance = fields.Boolean(string='隐藏余额为零的科目') company_id = fields.Many2one('res.company', string='公司', default=lambda self: self.env.company.id) def confirm(self): self.ensure_one() date_start = self.period_from.date_start date_end = self.period_to.date_end # 获取科目 account_ids = self.get_accounts() occur_data = self._fetch_occur_data(self.period_from.company_id.id, date_start, date_end, self.include_unpost) balance_data = self._fetch_balance_data(self.period_from.company_id.id, date_end, self.include_unpost) # 生成科目余额表汇总值 TODO 添加资产负债等小计 vals_list = [] for account in account_ids: # 获取借方和贷方发生 descendant_ids = account.get_descendant_ids(leaf=True) debit_occur = sum([occur_data[descendant_id][0] for descendant_id in descendant_ids]) credit_occur = sum([occur_data[descendant_id][1] for descendant_id in descendant_ids]) if self.hidden_no_occur and debit_occur == 0 and credit_occur == 0: continue # 获取期末借方和贷方余额 balance_end = sum([balance_data[descendant_id] for descendant_id in descendant_ids]) debit_end, credit_end = balance_direction_to_debit_credit(balance_end, 'debit') if all([self.hidden_no_balance, debit_end == 0, credit_end == 0, debit_occur == 0, credit_occur == 0]): continue # 获取期初借方和贷方余额 balance_start = balance_end - debit_occur + credit_occur debit_start, credit_start = balance_direction_to_debit_credit(balance_start, 'debit') # 添加结果, 此处添加结果时，如果是小数位数，会按照多位数进行存储。 # 多位小数位数 vals_list.append({ 'account_id': account.id, 'debit_start': debit_start, 'credit_start': credit_start, 'debit_occur': debit_occur, 'credit_occur': credit_occur, 'debit_end': debit_end, 'credit_end': credit_end, 'date_start': date_start, 'date_end': date_end, 'include_unpost': self.include_unpost, }) # 创建记录 balance_summaries = self.env['account.balance.temp'].create(vals_list) # 获取总账窗口动作 act_window = self.env['ir.actions.act_window']._for_xml_id('account_ledger.AccountBalanceTempViewActionList') act_window['domain'] = [('id', 'in', balance_summaries.ids)] act_window['display_name'] = f'科目余额表：{self.period_from.name} - {self.period_to.name}' return act_window def _fetch_occur_data(self, company_id, date_start, date_end, include_unpost=False): if include_unpost is False: state_condition = "and fr_state = 'posted'" else: state_condition = "" # 借贷发生预读取 self.env.cr.execute(f""" SELECT account_id, sum( debit ) AS debit, sum( credit ) AS credit FROM account_move_line WHERE company_id = {company_id} and date <= '{date_end}' and date >= '{date_start}' {state_condition} GROUP BY account_id """) occur_data = defaultdict(lambda: (0, 0)) for res in self.env.cr.fetchall(): occur_data[res[0]] = (res[1], res[2]) return occur_data def _fetch_balance_data(self, company_id, date_end, include_unpost=False): if include_unpost is False: state_condition = "and fr_state = 'posted'" else: state_condition = "" # 期末余额预读取 self.env.cr.execute(f""" SELECT account_id, sum( balance ) AS balance FROM account_move_line WHERE company_id = {company_id} and date <= '{date_end}' {state_condition} GROUP BY account_id """) balance_data = defaultdict(float) for res in self.env.cr.fetchall(): balance_data[res[0]] = res[1] return balance_data class AccountBalanceTemp(models.TransientModel): _name = 'account.balance.temp' _description = '科目余额表查询结果临时表' _order = 'account_id' # 基础字段 date_start = fields.Date(string='开始日期') date_end = fields.Date(string='结束日期') debit_start = fields.Monetary(string='期初借方') credit_start = fields.Monetary(string='期初贷方') debit_occur = fields.Monetary(string='借方发生') credit_occur = fields.Monetary(string='贷方发生') debit_end = fields.Monetary(string='期末借方') credit_end = fields.Monetary(string='期末贷方') include_unpost = fields.Boolean(string='包含未过账凭证') # 关系字段 account_id = fields.Many2one('account.account', string='会计科目', required=True, ondelete='cascade') # account_line_id = fields.Many2one('account.move.line', string='会计科目明细', required=True,ondelete='cascade') # 关联字段 # internal_group = fields.Selection(related='account_id.user_type_id.internal_group', string='会计要素', store=True) internal_group = fields.Selection(related='account_id.account_type', string='会计要素', store=True) account_level = fields.Selection(related='account_id.fr_account_level', string='科目等级') company_id = fields.Many2one(related='account_id.company_id', string='公司') currency_id = fields.Many2one(related='account_id.currency_id', string='币种') # partner_id = fields.Many2one(related='account_line_id.partner_id',string="业务伙伴") # analytic_account_id = fields.Many2one(related='account_line_id.analytic_account_id',string="分析账户") # analytic_tag_ids = fields.Many2many(related='account_line_id.analytic_tag_ids',string="分析标签") def action_move_line_list(self): account_ids = self.account_id.get_descendant_ids() domain = [('account_id', 'in', account_ids), ('date', '>=', self.date_start), ('date', '<=', self.date_end)] if self.include_unpost is False: domain.append(('fr_state', '=', 'posted'), ) return { 'type': 'ir.actions.act_window', 'res_model': 'account.move.line', 'name': self.account_id.name_get()[0][1], 'views': [[False, 'list'], [False, 'form']], 'domain': domain, 'context': {'create': False} } # 重复联系人明细行合并 def patch_account(self, jsd): a = {} for js in jsd: key = f'{js["account_id"]}_{js["partner_id"]}' if key not in a.keys(): a[key] = {} a[key]['debit'] = js['debit'] a[key]['credit'] = js['credit'] a[key]['account_balance_id'] = js['account_balance_id'] a[key]['account_balance_date'] = js['account_balance_date'] a[key]['date_start'] = js['date_start'] a[key]['date_end'] = js['date_end'] else: a[key]['debit'] += js['debit'] a[key]['credit'] += js['credit'] ret = [{'account_id': int(key.split("_")[0]), 'partner_id': int(key.split("_")[1]), 'debit': value['debit'], 'credit': value['credit'], 'account_balance_id': value['account_balance_id'], 'account_balance_date': value['account_balance_date'], 'date_start': value['date_start'], 'date_end': value['date_end'] } for key, value in a.items()] return ret def action_move_line_list_yeb(self): account_balance_date = fields.Datetime.now() account_ids = self.account_id.get_descendant_ids() domain = [('account_id', 'in', account_ids), ('date', '>=', self.date_start), ('date', '<=', self.date_end)] domain_all = [('account_id', 'in', account_ids)] # 判断余额表查询，是否添加包含未过账凭证 if self.include_unpost is False: domain_all.append(('fr_state', '=', 'posted'), ) data = [] for account in self.env['account.move.line'].search(domain_all): if account.partner_id: if (account.date >= self.date_start) and (account.date <= self.date_end): if not account.account_balance_id: account.account_balance_id = self.id res = { 'account_id': account.account_id.id, 'partner_id': account.partner_id.id, 'debit': account.debit, 'credit': account.credit, 'account_balance_id': self.id, 'account_balance_date': account_balance_date, 'date_start': self.date_start, 'date_end': self.date_end, } data.append(res) if account.date < self.date_start: if not account.account_balance_id: account.account_balance_id = self.id res = { 'account_id': account.account_id.id, 'partner_id': account.partner_id.id, 'debit': 0, 'credit': 0, 'account_balance_id': self.id, 'account_balance_date': account_balance_date, 'date_start': account.date, 'date_end': self.date_end, } data.append(res) # 6月9号修改。期初取值问题，取时间之前的值，业务伙伴 new_data = self.patch_account(data) end_data = [] for partner_line in new_data: qc = self.env['account.move.line'].search([ ('account_id', '=', partner_line.get('account_id')), ('date', '<', self.date_start), ('partner_id', '=', partner_line.get('partner_id'))]) # 期初余额 时间点下所有科目，借方 - 贷方 beginning = sum(qc.mapped('debit')) - sum(qc.mapped('credit')) # 期末余额 = 期初 + 借方 - 贷方 balance = beginning + partner_line.get('debit') - partner_line.get('credit') partner_line.update({ 'beginning': beginning, 'balance': balance, }) end_data.append(partner_line) self.env['fr.partner.balance'].create(end_data) # if self.include_unpost is True: # domain.append(('fr_state', '=', 'posted'), ) balance_domain = [('account_balance_id', '=', self.id), ('account_balance_date', '=', account_balance_date)] # if (self.account_id.user_type_id.type == 'receivable') or (self.account_id.user_type_id.type == 'payable'): if (self.account_id.account_type == 'receivable') or (self.account_id.account_type == 'payable'): view_id = self.env.ref('account_ledger.PartnerBalanceTree').id return { 'type': 'ir.actions.act_window', 'res_model': 'fr.partner.balance', 'search_view_id': self.env.ref('account_ledger.PartnerBalanceViewSearch').id, 'name': self.account_id.name_get()[0][1], 'views': [(view_id, 'list')], 'domain': balance_domain, 'context': {'create': 0, 'search_default_partner_id_group': 1} } else: return def action_move_line_list_yeb_prompt(self): raise UserError('只可进一步查看类型为：“应收”或“应付” 类科目') def print_quotation(self, displayName): """余额表打印功能""" vals_list = [] res_balance = { 'date': datetime.date.today(), 'company_id': self.env.user.company_id.id, 'displayName': displayName, } report_recs = self.env['report.account.balance'].create(res_balance) for balance in self: res = { 'detail_id': report_recs.id, 'account_id': balance.account_id.id, 'debit_start': balance.debit_start, 'credit_start': balance.credit_start, 'debit_occur': balance.debit_occur, 'credit_occur': balance.credit_occur, 'debit_end': balance.debit_end, 'credit_end': balance.credit_end, 'company_id': balance.company_id.id, 'currency_id': balance.currency_id.id, } vals_list.append(res) self.env['report.account.balance.line'].create(vals_list) context = {'lang': 'zh_CN', 'tz': 'Asia/Shanghai', 'uid': self.env.user.id, 'active_ids': [report_recs.id]} return { 'context': context, 'type': 'ir.actions.report', 'report_name': 'account_ledger.account_balance_temp', 'report_type': 'qweb-html', 'report_file': 'account_ledger.report_balance.temp', 'name': displayName, } class PartnerBalance(models.Model): _name = 'fr.partner.balance' _description = '客户余额表' account_id = fields.Many2one('account.account', string='会计科目', required=True, ondelete='cascade') partner_id = fields.Many2one('res.partner', string='业务伙伴') beginning = fields.Monetary(string='期初') debit = fields.Monetary(string='借方') credit = fields.Monetary(string='贷方') balance = fields.Monetary(string='余额') company_id = fields.Many2one(related='account_id.company_id', string='公司') currency_id = fields.Many2one(related='account_id.currency_id', string='币种') account_balance_id = fields.Char(string="余额表id") account_balance_date = fields.Datetime(string="查询时间") date_start = fields.Date(string='查询开始期间') date_end = fields.Date(string='查询结束期间')