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 osv import osv, fields
25 from tools.translate import _
27 class account_automatic_reconcile(osv.osv_memory):
28 _name = 'account.automatic.reconcile'
29 _description = 'Automatic Reconcile'
32 'account_ids': fields.many2many('account.account', 'reconcile_account_rel', 'reconcile_id', 'account_id', 'Accounts to Reconcile', domain = [('reconcile','=',1)],),
33 'writeoff_acc_id': fields.many2one('account.account', 'Account'),
34 'journal_id': fields.many2one('account.journal', 'Journal'),
35 'period_id': fields.many2one('account.period', 'Period'),
36 'max_amount': fields.float('Maximum write-off amount'),
37 'power': fields.selection([(p, str(p)) for p in range(2, 10)], 'Power', required=True, help='Number of partial amounts that can be combined to find a balance point can be chosen as the power of the automatic reconciliation'),
38 'date1': fields.date('Starting Date', required=True),
39 'date2': fields.date('Ending Date', required=True),
40 'reconciled': fields.integer('Reconciled transactions', readonly=True),
41 'unreconciled': fields.integer('Not reconciled transactions', readonly=True),
42 'allow_write_off': fields.boolean('Allow write off')
45 def _get_reconciled(self, cr, uid, context=None):
48 return context.get('reconciled', 0)
50 def _get_unreconciled(self, cr, uid, context=None):
53 return context.get('unreconciled', 0)
56 'date1': lambda *a: time.strftime('%Y-01-01'),
57 'date2': lambda *a: time.strftime('%Y-%m-%d'),
58 'reconciled': _get_reconciled,
59 'unreconciled': _get_unreconciled,
63 #TODO: cleanup and comment this code... For now, it is awfulllll
64 # (way too complex, and really slow)...
65 def do_reconcile(self, cr, uid, credits, debits, max_amount, power, writeoff_acc_id, period_id, journal_id, context=None):
66 # for one value of a credit, check all debits, and combination of them
67 # depending on the power. It starts with a power of one and goes up
68 # to the max power allowed
69 move_line_obj = self.pool.get('account.move.line')
72 def check2(value, move_list, power):
73 def check(value, move_list, power):
74 for i in range(len(move_list)):
77 if abs(value - move[1]) <= max_amount + 0.00001:
81 res = check(value - move[1], move_list, power-1)
82 move_list[i:i] = [move]
88 for p in range(1, power+1):
89 res = check(value, move_list, p)
94 # for a list of credit and debit and a given power, check if there
95 # are matching tuples of credit and debits, check all debits, and combination of them
96 # depending on the power. It starts with a power of one and goes up
97 # to the max power allowed
98 def check4(list1, list2, power):
99 def check3(value, list1, list2, list1power, power):
100 for i in range(len(list1)):
103 res = check2(value + move[1], list2, power - 1)
105 return ([move[0]], res)
108 res = check3(value + move[1], list1, list2, list1power-1, power-1)
116 for p in range(1, power):
117 res = check3(0, list1, list2, p, power)
122 def check5(list1, list2, max_power):
123 for p in range(2, max_power+1):
124 res = check4(list1, list2, p)
130 while credits and debits and ok:
131 res = check5(credits, debits, power)
133 move_line_obj.reconcile(cr, uid, res[0] + res[1], 'auto', writeoff_acc_id, period_id, journal_id, context)
134 reconciled += len(res[0]) + len(res[1])
135 credits = [(id, credit) for (id, credit) in credits if id not in res[0]]
136 debits = [(id, debit) for (id, debit) in debits if id not in res[1]]
139 return (reconciled, len(credits)+len(debits))
141 def reconcile(self, cr, uid, ids, context=None):
142 move_line_obj = self.pool.get('account.move.line')
143 obj_model = self.pool.get('ir.model.data')
146 form = self.browse(cr, uid, ids, context=context)[0]
147 max_amount = form.max_amount or 0.0
149 allow_write_off = form.allow_write_off
150 reconciled = unreconciled = 0
151 if not form.account_ids:
152 raise osv.except_osv(_('UserError'), _('You must select accounts to reconcile'))
153 for account_id in form.account_ids:
154 params = (account_id.id,)
155 if not allow_write_off:
156 query = """SELECT partner_id FROM account_move_line WHERE account_id=%s AND reconcile_id IS NULL
157 AND state <> 'draft' GROUP BY partner_id
158 HAVING ABS(SUM(debit-credit)) = 0.0 AND count(*)>0"""
160 query = """SELECT partner_id FROM account_move_line WHERE account_id=%s AND reconcile_id IS NULL
161 AND state <> 'draft' GROUP BY partner_id
162 HAVING ABS(SUM(debit-credit)) < %s AND count(*)>0"""
163 params += (max_amount,)
164 # reconcile automatically all transactions from partners whose balance is 0
165 cr.execute(query, params)
166 partner_ids = [id for (id,) in cr.fetchall()]
167 for partner_id in partner_ids:
170 "FROM account_move_line " \
171 "WHERE account_id=%s " \
172 "AND partner_id=%s " \
173 "AND state <> 'draft' " \
174 "AND reconcile_id IS NULL",
175 (account_id.id, partner_id))
176 line_ids = [id for (id,) in cr.fetchall()]
178 reconciled += len(line_ids)
180 move_line_obj.reconcile(cr, uid, line_ids, 'auto', form.writeoff_acc_id.id, form.period_id.id, form.journal_id.id, context)
182 move_line_obj.reconcile_partial(cr, uid, line_ids, 'manual', context=context)
184 # get the list of partners who have more than one unreconciled transaction
186 "SELECT partner_id " \
187 "FROM account_move_line " \
188 "WHERE account_id=%s " \
189 "AND reconcile_id IS NULL " \
190 "AND state <> 'draft' " \
191 "AND partner_id IS NOT NULL " \
192 "GROUP BY partner_id " \
195 partner_ids = [id for (id,) in cr.fetchall()]
197 for partner_id in partner_ids:
198 # get the list of unreconciled 'debit transactions' for this partner
200 "SELECT id, debit " \
201 "FROM account_move_line " \
202 "WHERE account_id=%s " \
203 "AND partner_id=%s " \
204 "AND reconcile_id IS NULL " \
205 "AND state <> 'draft' " \
207 "ORDER BY date_maturity",
208 (account_id.id, partner_id))
209 debits = cr.fetchall()
211 # get the list of unreconciled 'credit transactions' for this partner
213 "SELECT id, credit " \
214 "FROM account_move_line " \
215 "WHERE account_id=%s " \
216 "AND partner_id=%s " \
217 "AND reconcile_id IS NULL " \
218 "AND state <> 'draft' " \
220 "ORDER BY date_maturity",
221 (account_id.id, partner_id))
222 credits = cr.fetchall()
224 (rec, unrec) = self.do_reconcile(cr, uid, credits, debits, max_amount, power, form.writeoff_acc_id.id, form.period_id.id, form.journal_id.id, context)
226 unreconciled += unrec
228 # add the number of transactions for partners who have only one
229 # unreconciled transactions to the unreconciled count
230 partner_filter = partner_ids and 'AND partner_id not in (%s)' % ','.join(map(str, filter(None, partner_ids))) or ''
233 "FROM account_move_line " \
234 "WHERE account_id=%s " \
235 "AND reconcile_id IS NULL " \
236 "AND state <> 'draft' " + partner_filter,
238 additional_unrec = cr.fetchone()[0]
239 unreconciled = unreconciled + additional_unrec
240 context.update({'reconciled': reconciled, 'unreconciled': unreconciled})
241 model_data_ids = obj_model.search(cr,uid,[('model','=','ir.ui.view'),('name','=','account_automatic_reconcile_view1')])
242 resource_id = obj_model.read(cr, uid, model_data_ids, fields=['res_id'])[0]['res_id']
246 'res_model': 'account.automatic.reconcile',
247 'views': [(resource_id,'form')],
248 'type': 'ir.actions.act_window',
253 account_automatic_reconcile()
255 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: