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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
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
|
