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
|
import time
from odoo import api, models, _
from odoo.exceptions import UserError
class ReportGeneralLedger(models.AbstractModel):
_name = 'report.account.report_generalledger'
def _get_account_move_entry_custom(self, accounts, init_balance, sortby, display_account):
"""
:param:
accounts: the recordset of accounts
init_balance: boolean value of initial_balance
sortby: sorting by date or partner and journal
display_account: type of account(receivable, payable and both)
Returns a dictionary of accounts with following key and value {
'code': account code,
'name': account name,
'debit': sum of total debit amount,
'credit': sum of total credit amount,
'balance': total balance,
'amount_currency': sum of amount_currency,
'move_lines': list of move line
}
"""
cr = self.env.cr
MoveLine = 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 = MoveLine.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)\
LEFT JOIN account_move i ON (m.id =i.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 = MoveLine._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')
# 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', 'balance_month'])
res['code'] = account.code
res['name'] = account.name
res['company_id'] = account.company_id.id
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 = data['form']['display_account']
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'])])]
accounts = docs if model == 'account.account' else self.env['account.account'].search([])
accounts_res = self.with_context(data['form'].get('used_context',{}))._get_account_move_entry_custom(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,
}
|
