1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
|
# -*- coding: utf-8 -*-
#############################################################################
#
# Cybrosys Technologies Pvt. Ltd.
#
# Copyright (C) 2019-TODAY Cybrosys Technologies(<https://www.cybrosys.com>)
# Author: Cybrosys Techno Solutions(<https://www.cybrosys.com>)
#
# You can modify it under the terms of the GNU LESSER
# GENERAL PUBLIC LICENSE (LGPL v3), Version 3.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU LESSER GENERAL PUBLIC LICENSE (LGPL v3) for more details.
#
# You should have received a copy of the GNU LESSER GENERAL PUBLIC LICENSE
# (LGPL v3) along with this program.
# If not, see <http://www.gnu.org/licenses/>.
#
#############################################################################
from datetime import time
from odoo import models, api, _
from odoo.exceptions import UserError
class ReportCashBook(models.AbstractModel):
_name = 'report.base_accounting_kit.report_cash_book'
_description = 'Cash Book Report'
def _get_account_move_entry(self, accounts, init_balance, sortby,
display_account):
cr = self.env.cr
move_line = 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:
init_tables, init_where_clause, init_where_params = move_line.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')
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)\
JOIN account_journal j ON (l.journal_id=j.id)\
WHERE l.account_id IN %s""" + filters + ' GROUP BY l.account_id')
params = (tuple(accounts.ids),) + tuple(init_where_params)
cr.execute(sql, params)
for row in cr.dictfetchall():
move_lines[row.pop('account_id')].append(row)
sql_sort = 'l.date, l.move_id'
if sortby == 'sort_journal_partner':
sql_sort = 'j.code, p.name, l.move_id'
# Prepare sql query base on selected parameters from wizard
tables, where_clause, where_params = move_line._query_get()
wheres = [""]
if where_clause.strip():
wheres.append(where_clause.strip())
filters = " AND ".join(wheres)
filters = filters.replace('account_move_line__move_id', 'm').replace(
'account_move_line', 'l')
if not accounts:
journals = self.env['account.journal'].search([('type', '=', 'cash')])
accounts = []
for journal in journals:
accounts.append(journal.payment_credit_account_id.id)
accounts = self.env['account.account'].search([('id','in',accounts)])
# Get move lines base on sql query and Calculate the total balance of move lines
sql = ('''SELECT l.id AS lid, 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 ''' + filters + ''' GROUP BY l.id, l.account_id, l.date, j.code, l.currency_id, l.amount_currency, l.ref, l.name, m.name, c.symbol, p.name ORDER BY ''' + sql_sort)
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 += line['debit'] - line['credit']
row['balance'] += balance
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['move_lines'] = move_lines[account.id]
for line in res.get('move_lines'):
res['debit'] += line['debit']
res['credit'] += line['credit']
res['balance'] = line['balance']
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_report_values(self, docids, data=None):
if not data.get('form') or not self.env.context.get('active_model'):
raise UserError(
_("Form content is missing, this report cannot be printed."))
model = self.env.context.get('active_model')
docs = self.env[model].browse(
self.env.context.get('active_ids', []))
init_balance = data['form'].get('initial_balance', True)
sortby = data['form'].get('sortby', 'sort_date')
display_account = 'movement'
codes = []
if data['form'].get('journal_ids', False):
codes = [journal.code for journal in
self.env['account.journal'].search(
[('id', 'in', data['form']['journal_ids'])])]
account_ids = data['form']['account_ids']
accounts = self.env['account.account'].search(
[('id', 'in', account_ids)])
if not accounts:
journals = self.env['account.journal'].search([('type', '=', 'cash')])
accounts = []
for journal in journals:
accounts.append(journal.payment_credit_account_id.id)
accounts = self.env['account.account'].search([('id', 'in', accounts)])
accounts_res = self.with_context(
data['form'].get('used_context', {}))._get_account_move_entry(
accounts,
init_balance,
sortby,
display_account)
return {
'doc_ids': docids,
'doc_model': model,
'data': data['form'],
'docs': docs,
'time': time,
'Accounts': accounts_res,
'print_journal': codes,
}
|
