SunjiERP/odoo/custom/account/account_ledger/wizard/balance_inquiry.py

# -*- 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='查询结束期间')