1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
7 # This program is free software: you can redistribute it and/or modify
8 # it under the terms of the GNU Affero General Public License as
9 # published by the Free Software Foundation, either version 3 of the
10 # License, or (at your option) any later version.
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU Affero General Public License for more details.
17 # You should have received a copy of the GNU Affero General Public License
18 # along with this program. If not, see <http://www.gnu.org/licenses/>.
20 ##############################################################################
24 from openerp.tools.translate import _
25 from openerp.report import report_sxw
26 from common_report_header import common_report_header
28 class partner_balance(report_sxw.rml_parse, common_report_header):
30 def __init__(self, cr, uid, name, context=None):
31 super(partner_balance, self).__init__(cr, uid, name, context=context)
33 self.localcontext.update( {
36 'sum_debit': self._sum_debit,
37 'sum_credit': self._sum_credit,
38 'sum_litige': self._sum_litige,
39 'get_fiscalyear': self._get_fiscalyear,
40 'get_journal': self._get_journal,
41 'get_filter': self._get_filter,
42 'get_account': self._get_account,
43 'get_start_date':self._get_start_date,
44 'get_end_date':self._get_end_date,
45 'get_start_period': self.get_start_period,
46 'get_end_period': self.get_end_period,
47 'get_partners':self._get_partners,
48 'get_target_move': self._get_target_move,
51 def set_context(self, objects, data, ids, report_type=None):
52 self.display_partner = data['form'].get('display_partner', 'non-zero_balance')
53 obj_move = self.pool.get('account.move.line')
54 self.query = obj_move._query_get(self.cr, self.uid, obj='l', context=data['form'].get('used_context', {}))
55 self.result_selection = data['form'].get('result_selection')
56 self.target_move = data['form'].get('target_move', 'all')
58 if (self.result_selection == 'customer' ):
59 self.ACCOUNT_TYPE = ('receivable',)
60 elif (self.result_selection == 'supplier'):
61 self.ACCOUNT_TYPE = ('payable',)
63 self.ACCOUNT_TYPE = ('payable', 'receivable')
65 self.cr.execute("SELECT a.id " \
66 "FROM account_account a " \
67 "LEFT JOIN account_account_type t " \
68 "ON (a.type = t.code) " \
69 "WHERE a.type IN %s " \
70 "AND a.active", (self.ACCOUNT_TYPE,))
71 self.account_ids = [a for (a,) in self.cr.fetchall()]
72 return super(partner_balance, self).set_context(objects, data, ids, report_type=report_type)
75 move_state = ['draft','posted']
76 if self.target_move == 'posted':
77 move_state = ['posted']
81 "SELECT p.ref,l.account_id,ac.name AS account_name,ac.code AS code,p.name, sum(debit) AS debit, sum(credit) AS credit, " \
82 "CASE WHEN sum(debit) > sum(credit) " \
83 "THEN sum(debit) - sum(credit) " \
86 "CASE WHEN sum(debit) < sum(credit) " \
87 "THEN sum(credit) - sum(debit) " \
90 "(SELECT sum(debit-credit) " \
91 "FROM account_move_line l " \
92 "WHERE partner_id = p.id " \
93 "AND " + self.query + " " \
94 "AND blocked = TRUE " \
96 "FROM account_move_line l LEFT JOIN res_partner p ON (l.partner_id=p.id) " \
97 "JOIN account_account ac ON (l.account_id = ac.id)" \
98 "JOIN account_move am ON (am.id = l.move_id)" \
99 "WHERE ac.type IN %s " \
100 "AND am.state IN %s " \
101 "AND " + self.query + "" \
102 "GROUP BY p.id, p.ref, p.name,l.account_id,ac.name,ac.code " \
103 "ORDER BY l.account_id,p.name",
104 (self.ACCOUNT_TYPE, tuple(move_state)))
105 res = self.cr.dictfetchall()
108 if self.display_partner == 'non-zero_balance':
109 full_account = [r for r in res if r['sdebit'] > 0 or r['scredit'] > 0]
111 full_account = [r for r in res]
113 for rec in full_account:
114 if not rec.get('name', False):
115 rec.update({'name': _('Unknown Partner')})
117 ## We will now compute Total
118 subtotal_row = self._add_subtotal(full_account)
121 def _add_subtotal(self, cleanarray):
130 # For the first element we always add the line
131 # type = 1 is the line is the first of the account
132 # type = 2 is an other line of the account
134 # We add the first as the header
138 new_header['ref'] = ''
139 new_header['name'] = r['account_name']
140 new_header['code'] = r['code']
141 new_header['debit'] = r['debit']
142 new_header['credit'] = r['credit']
143 new_header['scredit'] = tot_scredit
144 new_header['sdebit'] = tot_sdebit
145 new_header['enlitige'] = tot_enlitige
146 new_header['balance'] = r['debit'] - r['credit']
147 new_header['type'] = 3
149 completearray.append(new_header)
152 r['balance'] = float(r['sdebit']) - float(r['scredit'])
154 completearray.append(r)
156 tot_debit = r['debit']
157 tot_credit = r['credit']
158 tot_scredit = r['scredit']
159 tot_sdebit = r['sdebit']
160 tot_enlitige = (r['enlitige'] or 0.0)
163 if cleanarray[i]['account_id'] <> cleanarray[i-1]['account_id']:
165 new_header['debit'] = tot_debit
166 new_header['credit'] = tot_credit
167 new_header['scredit'] = tot_scredit
168 new_header['sdebit'] = tot_sdebit
169 new_header['enlitige'] = tot_enlitige
170 new_header['balance'] = float(tot_sdebit) - float(tot_scredit)
171 new_header['type'] = 3
172 # we reset the counter
173 tot_debit = r['debit']
174 tot_credit = r['credit']
175 tot_scredit = r['scredit']
176 tot_sdebit = r['sdebit']
177 tot_enlitige = (r['enlitige'] or 0.0)
181 new_header['ref'] = ''
182 new_header['name'] = r['account_name']
183 new_header['code'] = r['code']
184 new_header['debit'] = tot_debit
185 new_header['credit'] = tot_credit
186 new_header['scredit'] = tot_scredit
187 new_header['sdebit'] = tot_sdebit
188 new_header['enlitige'] = tot_enlitige
189 new_header['balance'] = float(tot_sdebit) - float(tot_scredit)
190 new_header['type'] = 3
194 completearray.append(new_header)
199 r['balance'] = float(r['sdebit']) - float(r['scredit'])
201 completearray.append(r)
203 if cleanarray[i]['account_id'] == cleanarray[i-1]['account_id']:
204 # we reset the counter
206 new_header['debit'] = tot_debit
207 new_header['credit'] = tot_credit
208 new_header['scredit'] = tot_scredit
209 new_header['sdebit'] = tot_sdebit
210 new_header['enlitige'] = tot_enlitige
211 new_header['balance'] = float(tot_sdebit) - float(tot_scredit)
212 new_header['type'] = 3
214 tot_debit = tot_debit + r['debit']
215 tot_credit = tot_credit + r['credit']
216 tot_scredit = tot_scredit + r['scredit']
217 tot_sdebit = tot_sdebit + r['sdebit']
218 tot_enlitige = tot_enlitige + (r['enlitige'] or 0.0)
220 new_header['debit'] = tot_debit
221 new_header['credit'] = tot_credit
222 new_header['scredit'] = tot_scredit
223 new_header['sdebit'] = tot_sdebit
224 new_header['enlitige'] = tot_enlitige
225 new_header['balance'] = float(tot_sdebit) - float(tot_scredit)
230 r['balance'] = float(r['sdebit']) - float(r['scredit'])
233 completearray.append(r)
238 def _sum_debit(self):
239 move_state = ['draft','posted']
240 if self.target_move == 'posted':
241 move_state = ['posted']
246 "SELECT sum(debit) " \
247 "FROM account_move_line AS l " \
248 "JOIN account_move am ON (am.id = l.move_id)" \
249 "WHERE l.account_id IN %s" \
250 "AND am.state IN %s" \
251 "AND " + self.query + "",
252 (tuple(self.account_ids), tuple(move_state)))
253 temp_res = float(self.cr.fetchone()[0] or 0.0)
256 def _sum_credit(self):
257 move_state = ['draft','posted']
258 if self.target_move == 'posted':
259 move_state = ['posted']
264 "SELECT sum(credit) " \
265 "FROM account_move_line AS l " \
266 "JOIN account_move am ON (am.id = l.move_id)" \
267 "WHERE l.account_id IN %s" \
268 "AND am.state IN %s" \
269 "AND " + self.query + "",
270 (tuple(self.account_ids), tuple(move_state)))
271 temp_res = float(self.cr.fetchone()[0] or 0.0)
274 def _sum_litige(self):
275 #gives the total of move lines with blocked boolean set to TRUE for the report selection
276 move_state = ['draft','posted']
277 if self.target_move == 'posted':
278 move_state = ['posted']
283 "SELECT sum(debit-credit) " \
284 "FROM account_move_line AS l " \
285 "JOIN account_move am ON (am.id = l.move_id)" \
286 "WHERE l.account_id IN %s" \
287 "AND am.state IN %s" \
288 "AND " + self.query + " " \
289 "AND l.blocked=TRUE ",
290 (tuple(self.account_ids), tuple(move_state), ))
291 temp_res = float(self.cr.fetchone()[0] or 0.0)
294 def _get_partners(self):
296 if self.result_selection == 'customer':
297 return _('Receivable Accounts')
298 elif self.result_selection == 'supplier':
299 return _('Payable Accounts')
300 elif self.result_selection == 'customer_supplier':
301 return _('Receivable and Payable Accounts')
304 report_sxw.report_sxw('report.account.partner.balance', 'res.partner', 'account/report/account_partner_balance.rml',parser=partner_balance, header="internal")
306 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: