openerp-expert-accounting team mailing list archive
-
openerp-expert-accounting team
-
Mailing list archive
-
Message #00419
Re: Closing of fiscal year, draft and valid move_lines
So I finally decided to patch 5.0 to get 3 additional fields for
account_account
* debit_fy (Debit FY)
* credit_fy (Credit FY)
* balance_init
additionally the balance of the "old" fields
* debit (now "Debit Tot" to avoid confusion, sorry translators)
* credit (now Credit Tot)
* balance
always adds up all open fiscal years of balance accounts until the selected
one
if no fiscal year is preselected, the current fy is used (especially for "List
of Accounts")
you may want to remove debit, credit from account.tree.view
This way all existing reports and views will return the same (sometimes quite
useless) figures, whereas it is very easy to adapt reports.
IMHO the construct is compatible with the current behaviour of selecting
valid/draft moves, although I strongly disagree to allow to select draft moves
in balance and general ledger and similar because these may not be balanced
and will cause support/bug reports. probably we need states draft, balanced,
valid and allow to select valid , balanced or both.
@Borja
I didn't implement the period stuff yet, but it will be similar to fy stuff.
I also believe that calculating some more fields on request in ONE query will
not harm performance - the most expensive is reading the database.
--
regards
Ferdinand Gassauer
ChriCar Beteiligungs- und Beratungs- GmbH
Official OpenERP Partner
Attachment:
account_balance_init.png
Description: PNG image
--- /home/terp/OpenERP/official/addons/account/account.py 2010-04-10 22:53:52.847192301 +0200
+++ account.py 2010-04-23 21:04:37.543085457 +0200
@@ -195,21 +204,54 @@ class account_account(osv.osv):
ids3 = self._get_children_and_consol(cr, uid, ids3, context)
return ids2 + ids3
+ def _get_children_and_consol_balance(self, cr, uid, ids, context={}):
+ #this function search for all the children and all consolidated children (recursively) of the given account ids
+ ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids),('user_type.close_method', '<>', 'none')], context=context)
+ ids3 = []
+ for rec in self.browse(cr, uid, ids2, context=context):
+ for child in rec.child_consol_ids:
+ ids3.append(child.id)
+ if ids3:
+ ids3 = self._get_children_and_consol(cr, uid, ids3, context)
+ return ids2 + ids3
+
+
def __compute(self, cr, uid, ids, field_names, arg, context={}, query=''):
#compute the balance/debit/credit accordingly to the value of field_name for the given account ids
+
+ periods_fy = self.pool.get('account.move.line')._periods_fy_get(cr, uid, context=context)
+
+ case_debit = 'COALESCE(SUM(case when l.period_id in ('+periods_fy+') then l.debit else 0 end),0) as debit_fy '
+ case_credit = 'COALESCE(SUM(case when l.period_id in ('+periods_fy+') then l.credit else 0 end),0 ) as credit_fy '
+ case_balance_init = 'COALESCE(SUM(case when l.period_id not in ('+periods_fy+') then l.debit - l.credit else 0 end),0) as balance_init '
+
+# mapping = {
+# 'balance': "COALESCE(SUM(l.debit),0) - COALESCE(SUM(l.credit), 0) as balance ",
+# 'debit': "COALESCE(SUM(l.debit), 0) as debit ",
+# 'credit': "COALESCE(SUM(l.credit), 0) as credit "
+# }
mapping = {
'balance': "COALESCE(SUM(l.debit),0) - COALESCE(SUM(l.credit), 0) as balance ",
'debit': "COALESCE(SUM(l.debit), 0) as debit ",
- 'credit': "COALESCE(SUM(l.credit), 0) as credit "
+ 'credit': "COALESCE(SUM(l.credit), 0) as credit ",
+ 'debit_fy': case_debit,
+ 'credit_fy': case_credit,
+ 'balance_init': case_balance_init,
}
+
#get all the necessary accounts
ids2 = self._get_children_and_consol(cr, uid, ids, context)
acc_set = ",".join(map(str, ids2))
+
+ ids2_balance = self._get_children_and_consol_balance(cr, uid, ids, context)
+ acc_set_previous = ",".join(map(str, ids2_balance)) or '0'
#compute for each account the balance/debit/credit from the move lines
accounts = {}
if ids2:
aml_query = self.pool.get('account.move.line')._query_get(cr, uid, context=context)
+ aml_query_previous = self.pool.get('account.move.line')._query_get_previous(cr, uid, context=context)
+ query2 = query
wheres = [""]
if query.strip():
wheres.append(query.strip())
@@ -217,20 +259,59 @@ class account_account(osv.osv):
wheres.append(aml_query.strip())
query = " AND ".join(wheres)
+ # for open previous years
+ wheres2 = [""]
+ if query2.strip():
+ wheres2.append(query2.strip())
+ if aml_query_previous.strip():
+ wheres2.append(aml_query_previous.strip())
+ query2 = " AND ".join(wheres2)
+
+# cr.execute(("SELECT l.account_id as id, " +\
+# ' , '.join(map(lambda x: mapping[x], field_names)) +
+# "FROM " \
+# "account_move_line l " \
+# "WHERE " \
+# "l.account_id IN (%s) " \
+# + query +
+# " GROUP BY l.account_id " ) % (acc_set,))
+#
+ # FIXME
+ # the "not in" fetches meoves for all balance accounts of prevoious open fiscal years
+ # Do not know if this can be achieved already in acc_set_previous
+ # - because of "child_of" the condition 'none' might cause problems for views.
+# cr.execute(("SELECT l.account_id as id, " +\
+# ' , '.join(map(lambda x: mapping[x], field_names)) +
+# "FROM " \
+# "account_move_line l " \
+# "WHERE " \
+# "(l.account_id IN (%s) " \
+# + query + ") or "
+# "(l.account_id not in (select a.id from account_account a, " \
+# "account_account_type t "\
+# "where t.id = a.user_type " \
+# "and t.close_method = 'none') AND " \
+# "l.account_id IN (%s) " \
+# + query2 + ") " \
+# " GROUP BY l.account_id " ) % (acc_set,acc_set_previous,))
+
cr.execute(("SELECT l.account_id as id, " +\
' , '.join(map(lambda x: mapping[x], field_names)) +
"FROM " \
"account_move_line l " \
"WHERE " \
- "l.account_id IN (%s) " \
- + query +
- " GROUP BY l.account_id") % (acc_set, ))
+ "(l.account_id IN (%s) " \
+ + query + ") or "
+ "(l.account_id IN (%s) " \
+ + query2 + ") " \
+ " GROUP BY l.account_id " ) % (acc_set,acc_set_previous,))
for res in cr.dictfetchall():
accounts[res['id']] = res
# consolidate accounts with direct children
+ ids2.reverse()
brs = list(self.browse(cr, uid, ids2, context=context))
sums = {}
while brs:
@@ -295,8 +376,11 @@ class account_account(osv.osv):
'child_consol_ids': fields.many2many('account.account', 'account_account_consol_rel', 'child_id', 'parent_id', 'Consolidated Children'),
'child_id': fields.function(_get_child_ids, method=True, type='many2many', relation="account.account", string="Child Accounts"),
'balance': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Balance', multi='balance'),
- 'credit': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Credit', multi='balance'),
- 'debit': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Debit', multi='balance'),
+ 'balance_init': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Initial Balance', multi='balance_init'),
+ 'credit': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Credit Tot', multi='balance'),
+ 'credit_fy': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Credit FY', multi='balance'),
+ 'debit': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Debit Tot', multi='balance'),
+ 'debit_fy': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Debit FY', multi='balance'),
'reconcile': fields.boolean('Reconcile', help="Check this if the user is allowed to reconcile entries in this account."),
'shortcut': fields.char('Shortcut', size=12),
'tax_ids': fields.many2many('account.tax', 'account_account_tax_default_rel',
--- /home/terp/OpenERP/official/addons/account/account_move_line.py 2010-03-14 06:25:32.972557583 +0100
+++ account_move_line.py 2010-04-23 21:21:10.631275386 +0200
@@ -29,11 +29,57 @@ import mx.DateTime
from mx.DateTime import RelativeDateTime, now, DateTime, localtime
import tools
+import sys
+#----------------------------------------------------------------------------
+# Account Move Line
+#----------------------------------------------------------------------------
class account_move_line(osv.osv):
_name = "account.move.line"
_description = "Entry lines"
+ def _query_get_previous(self, cr, uid, obj='l', context={}):
+ fiscalyear_obj = self.pool.get('account.fiscalyear')
+ if not context.get('fiscalyear', False):
+ fiscalyear_ids = fiscalyear_obj.search(cr, uid, [('state', '=', 'draftq')])
+ fiscalyear_clause = (','.join([str(x) for x in fiscalyear_ids])) or '0'
+ else:
+ fy_id = context.get('fiscalyear')
+ fys = fiscalyear_obj.search(cr, uid, [('id', '=', fy_id )])
+ for fy_ids in self.pool.get('account.fiscalyear').browse(cr, uid,fys):
+ fy_end_date = fy_ids.date_stop
+ fiscalyear_ids = fiscalyear_obj.search(cr, uid, [('date_stop', '<', fy_end_date),('state', '=', 'draft')])
+ fiscalyear_clause = (','.join([str(x) for x in fiscalyear_ids])) or '0'
+ state=context.get('state',False)
+ where_move_state = ''
+ where_move_lines_by_date = ''
+
+ if context.get('date_from', False) and context.get('date_to', False):
+ where_move_lines_by_date = " AND " +obj+".move_id in ( select id from account_move where date >= '" +context['date_from']+"' AND date <= '"+context['date_to']+"')"
+
+ if state:
+ if state.lower() not in ['all']:
+ where_move_state= " AND "+obj+".move_id in (select id from account_move where account_move.state = '"+state+"')"
+
+ if context.get('periods', False):
+ ids = ','.join([str(x) for x in context['periods']])
+ return obj+".state<>'draft' AND "+obj+".period_id in (SELECT id from account_period WHERE fiscalyear_id in (%s) AND id in (%s)) %s %s" % (fiscalyear_clause, ids,where_move_state,where_move_lines_by_date)
+ else:
+ return obj+".state<>'draft' AND "+obj+".period_id in (SELECT id from account_period WHERE fiscalyear_id in (%s) %s %s)" % (fiscalyear_clause,where_move_state,where_move_lines_by_date)
+
+
+ def _periods_fy_get(self, cr, uid, obj='l', context={}):
+ period_obj = self.pool.get('account.period')
+ if not context.get('fiscalyear', False):
+ today = now()
+ period_ids = period_obj.search(cr, uid, [('fiscalyear_id.date_start', '<=', today), ('fiscalyear_id.date_stop', '>=', today) ])
+ else:
+ fy_id = context.get('fiscalyear')
+ period_ids = period_obj.search(cr, uid, [('fiscalyear_id.id', '=', fy_id )])
+ periods_fy_selected = (','.join([str(x) for x in period_ids])) or '0'
+
+ return periods_fy_selected
+
def _query_get(self, cr, uid, obj='l', context={}):
fiscalyear_obj = self.pool.get('account.fiscalyear')
if not context.get('fiscalyear', False):
@@ -245,12 +291,20 @@ class account_move_line(osv.osv):
res={}
# TODO group the foreach in sql
for id in ids:
- cr.execute('SELECT date,account_id FROM account_move_line WHERE id=%s', (id,))
+ # FIX ME we just need to fetch ONE record
+ cr.execute('SELECT date,account_id FROM account_move_line WHERE id=%s limit 1', (id,))
dt, acc = cr.fetchone()
cr.execute('SELECT SUM(debit-credit) FROM account_move_line WHERE account_id=%s AND (date<%s OR (date=%s AND id<=%s))', (acc,dt,dt,id))
res[id] = cr.fetchone()[0]
return res
+ def _balance_move_line(self, cr, uid, ids, name, arg, context={}):
+ res = {}
+ for line in self.browse(cr, uid, ids,context):
+ balance = line.debit - line.credit
+ res[line.id] = balance
+ return res
+
def _invoice(self, cursor, user, ids, name, arg, context=None):
invoice_obj = self.pool.get('account.invoice')
res = {}
@@ -344,6 +398,7 @@ class account_move_line(osv.osv):
'product_id': fields.many2one('product.product', 'Product'),
'debit': fields.float('Debit', digits=(16,2)),
'credit': fields.float('Credit', digits=(16,2)),
+ 'balance_move_line': fields.function(_balance_move_line, method=True, string='Balance Line'),
'account_id': fields.many2one('account.account', 'Account', required=True, ondelete="cascade", domain=[('type','<>','view'), ('type', '<>', 'closed')], select=2),
'move_id': fields.many2one('account.move', 'Move', ondelete="cascade", states={'valid':[('readonly',True)]}, help="The move of this entry line.", select=2),
@@ -578,7 +633,7 @@ class account_move_line(osv.osv):
raise osv.except_osv(_('Error'), _('Entry is already reconciled'))
account = self.pool.get('account.account').browse(cr, uid, account_id, context=context)
if not context.get('fy_closing', False) and not account.reconcile:
- raise osv.except_osv(_('Error'), _('The account is not defined to be reconciled !'))
+ raise osv.except_osv(_('Error'), _('The account %s is not defined to be reconcile !' % (account.name)))
if r[0][1] != None:
raise osv.except_osv(_('Error'), _('Some entries are already reconciled !'))
--- /home/terp/OpenERP/official/addons/account/account_view.xml 2010-04-06 08:29:45.091463943 +0200
+++ account_view.xml 2010-04-23 21:26:13.075095034 +0200
@@ -163,11 +167,16 @@
<tree string="Chart of accounts" toolbar="1" colors="blue:type=='view'">
<field name="code"/>
<field name="name"/>
+ <field name="balance_init"/>
+ <field name="debit_fy"/>
+ <field name="credit_fy"/>
+ <field name="balance"/>
<field name="debit"/>
<field name="credit"/>
- <field name="balance"/>
<field name="company_currency_id"/>
- <field name="type" invisible="1"/>
+ <field name="type" />
+ <field name="user_type" />
+ <field name="parent_id" />
</tree>
</field>
</record>
References
