openerp-expert-accounting team mailing list archive
-
openerp-expert-accounting team
-
Mailing list archive
-
Message #00427
Re: Closing of fiscal year, draft and valid move_lines - some more thoughts about special periods
Special Periods in account_period
* special_open
* special_close
+ calculated and displayed values for selected fiscal year in account_account
* balance_init
* balance_unclosed
* debit_fy
* credit_fy
pls check
--
Best Regards
ChriCar Beteiligungs- und Beratungs- GmbH
http://www.chricar.at/ChriCar/index.html
Dr. Ferdinand Gassauer
Official Tiny Partner
--- /home/terp/OpenERP/official/addons/account/account_view.xml 2010-04-06 08:29:45.091463943 +0200
+++ account_view.xml 2010-04-26 12:28:05.592692116 +0200
@@ -93,6 +93,8 @@
<field name="date_start"/>
<field name="date_stop"/>
<field name="special"/>
+ <field name="special_open"/>
+ <field name="special_close"/>
<field name="state"/>
</tree>
</field>
@@ -163,11 +169,17 @@
<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_unclosed"/>
+ <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>
--- /home/terp/OpenERP/official/addons/account/account.py 2010-04-24 13:19:30.858232000 +0200
+++ account.py 2010-04-26 12:21:10.426023255 +0200
@@ -195,21 +204,59 @@ 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=''):
+ print >>sys.stderr,'TEST ', field_names
#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)
+ period_closed = self.pool.get('account.move.line')._period_closed_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 in ('+periods_fy+') then 0 else l.debit - l.credit end),0) as balance_init '
+ case_balance_unclosed = 'COALESCE(SUM(case when l.period_id in ('+period_closed+') then 0 else l.debit - l.credit end),0) as balance_unclosed '
+ print >>sys.stderr,'TEST unclosed', case_balance_unclosed
+
+# 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,
+ 'balance_unclosed': case_balance_unclosed,
}
+
#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 +264,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:
@@ -250,6 +336,7 @@ class account_account(osv.osv):
if current.child_id:
sums[current.id][fn] += sum(sums[child.id][fn] for child in current.child_id)
res = {}
+
null_result = dict((fn, 0.0) for fn in field_names)
for id in ids:
res[id] = sums.get(id, null_result)
@@ -294,9 +381,13 @@ class account_account(osv.osv):
'child_parent_ids': fields.one2many('account.account','parent_id','Children'),
'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': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Balance Closed', multi='balance'),
+ 'balance_init': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Initial Balance', multi='balance'),
+ 'balance_unclosed': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Balance Unclosed', multi='balance'),
+ '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-26 12:12:37.666374614 +0200
@@ -29,11 +29,72 @@ 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', '=', 'draft')])
+ 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()
+ # periods of current year without opening/closing periods
+ period_ids = period_obj.search(cr, uid, [('special','=',False),('fiscalyear_id.date_start', '<=', today), ('fiscalyear_id.date_stop', '>=', today) ])
+ else:
+ fy_id = context.get('fiscalyear')
+ period_ids = period_obj.search(cr, uid, [('special','=',False),('fiscalyear_id.id', '=', fy_id )])
+ periods_fy_selected = (','.join([str(x) for x in period_ids])) or '0'
+
+ return periods_fy_selected
+
+ def _period_closed_get(self, cr, uid, obj='l', context={}):
+ period_obj = self.pool.get('account.period')
+ if not context.get('fiscalyear', False):
+ today = now()
+ # periods of current year without opening/closing periods
+ period_ids = period_obj.search(cr, uid, [('special_close','=',True),('fiscalyear_id.date_start', '<=', today), ('fiscalyear_id.date_stop', '>=', today) ])
+ else:
+ fy_id = context.get('fiscalyear')
+ period_ids = period_obj.search(cr, uid, [('special_close','=',True),('fiscalyear_id.id', '=', fy_id )])
+ period_closed_selected = (','.join([str(x) for x in period_ids])) or '0'
+
+ return period_closed_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 +306,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 +413,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 +648,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 !'))
Follow ups
