good/money/report/bank_statements.py

# Copyright 2016 上海开阖软件有限公司 (http://www.osbzr.com)
# License AGPL-3.0 or later (http://www.gnu.org/licenses/agpl).

from odoo import fields, models, api, tools


class BankStatementsReport(models.Model):
    _name = "bank.statements.report"
    _description = u"现金银行报表"
    _auto = False
    _order = 'date'

    @api.depends('get', 'pay', 'bank_id')
    def _compute_balance(self):
        # 相邻的两条记录，bank_id不同，重新计算账户余额
        for bsr in self:
            pre_record = bsr.search(
                [('id', '<=', bsr.id), ('bank_id', '=', bsr.bank_id.id)])
            for pre in pre_record:
                bsr.balance += pre.get - pre.pay

    bank_id = fields.Many2one('bank.account', string=u'账户名称', readonly=True)
    date = fields.Date(string=u'日期', readonly=True)
    name = fields.Char(string=u'单据编号', readonly=True)
    get = fields.Float(string=u'收入', readonly=True,
                       digits='Amount')
    pay = fields.Float(string=u'支出', readonly=True,
                       digits='Amount')
    balance = fields.Float(string=u'账户余额',
                           compute='_compute_balance', readonly=True,
                           digits='Amount')
    partner_id = fields.Many2one('partner', string=u'往来单位', readonly=True)
    note = fields.Char(string=u'备注', readonly=True)

    def init(self):
        # union money_order, other_money_order, money_transfer_order
        cr = self._cr
        tools.drop_view_if_exists(cr, 'bank_statements_report')
        cr.execute("""
            CREATE or REPLACE VIEW bank_statements_report AS (
            SELECT  ROW_NUMBER() OVER(ORDER BY bank_id,date) AS id,
                    bank_id,
                    date,
                    name,
                    get,
                    pay,
                    balance,
                    partner_id,
                    note
            FROM
                (
                SELECT mol.bank_id,
                        mo.date,
                        mo.name,
                        (CASE WHEN mo.type = 'get' THEN mol.amount ELSE 0 END) AS get,
                        (CASE WHEN mo.type = 'pay' THEN mol.amount ELSE 0 END) AS pay,
                        0 AS balance,
                        mo.partner_id,
                        mo.note
                FROM money_order_line AS mol
                LEFT JOIN money_order AS mo ON mol.money_id = mo.id
                WHERE mo.state = 'done'
                UNION ALL
                SELECT  omo.bank_id,
                        omo.date,
                        omo.name,
                        (CASE WHEN omo.type = 'other_get' THEN
                         (CASE WHEN ba.currency_id IS NULL THEN omo.total_amount ELSE omo.currency_amount END)
                         ELSE 0 END) AS get,
                        (CASE WHEN omo.type = 'other_pay' THEN
                         (CASE WHEN ba.currency_id IS NULL THEN omo.total_amount ELSE omo.currency_amount END)
                         ELSE 0 END) AS pay,
                        0 AS balance,
                        omo.partner_id,
                        omo.note AS note
                FROM other_money_order AS omo
                LEFT JOIN bank_account AS ba ON ba.id = omo.bank_id
                LEFT JOIN res_currency AS rc ON rc.id = ba.currency_id
                WHERE omo.state = 'done'
                UNION ALL
                SELECT  mtol.out_bank_id AS bank_id,
                        mto.date,
                        mto.name,
                        0 AS get,
                        (CASE WHEN ba.currency_id IS NULL THEN mtol.amount ELSE mtol.currency_amount END) AS pay,
                        0 AS balance,
                        NULL AS partner_id,
                        mto.note
                FROM money_transfer_order_line AS mtol
                LEFT JOIN money_transfer_order AS mto ON mtol.transfer_id = mto.id
                LEFT JOIN bank_account AS ba ON ba.id = mtol.out_bank_id
                LEFT JOIN res_currency AS rc ON rc.id = ba.currency_id
                WHERE mto.state = 'done'
                UNION ALL
                SELECT  mtol.in_bank_id AS bank_id,
                        mto.date,
                        mto.name,
                        mtol.amount AS get,
                        0 AS pay,
                        0 AS balance,
                        NULL AS partner_id,
                        mto.note
                FROM money_transfer_order_line AS mtol
                LEFT JOIN money_transfer_order AS mto ON mtol.transfer_id = mto.id
                WHERE mto.state = 'done'
                ) AS bs)
        """)

    def find_source_order(self):
        # 查看原始单据，三种情况：收付款单、其他收支单、资金转换单
        self.ensure_one()
        model_view = {
            'money.order': {'name': u'收付款单',
                            'view': 'money.money_order_form'},
            'other.money.order': {'name': u'其他收支单',
                                  'view': 'money.other_money_order_form'},
            'money.transfer.order': {
                'name': '资金转换单',
                'view': 'money.money_transfer_order_form'}}
        for model, view_dict in model_view.items():
            res = self.env[model].search([('name', '=', self.name)])
            name = view_dict['name']
            view = self.env.ref(view_dict['view'])
            if res:
                return {
                    'name': name,
                    'view_mode': 'form',
                    'view_id': False,
                    'views': [(view.id, 'form')],
                    'res_model': model,
                    'type': 'ir.actions.act_window',
                    'res_id': res.id
                }