openerp-expert-accounting team mailing list archive
-
openerp-expert-accounting team
-
Mailing list archive
-
Message #00432
Re: Closing of fiscal year, draft and valid move_lines - some more thoughts about special periods
now "feature complete"
my idea is that this way OpenERP accounting works also for "dummies".
no need to close fiscal years - but if required it will also return correct
results as long the special periods are used for closing.
Especially no need to close a fiscal year before everything is finished.
Nevertheless I agree the option of including/excluding certain journals should
be integrated, but I do not need it now, so do not wait for it.
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
for current or selected period
* balance_period_init
* balance_period_end
* balance_period
* debit_period
* credit_period
pls check
I have added all fields to the account tree view for demo purpose.
--
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/wizard/wizard_account_chart.py 2009-07-12 21:47:52.302565904 +0200
+++ wizard/wizard_account_chart.py 2010-04-26 18:21:16.619637652 +0200
@@ -27,6 +27,7 @@ class wizard_account_chart(wizard.interf
_account_chart_arch = '''<?xml version="1.0"?>
<form string="Account charts">
<field name="fiscalyear"/>
+ <field name="period"/>
<label align="0.7" colspan="6" string="(If you do not select Fiscal year it will take all open fiscal years)"/>
<field name="target_move"/>
</form>'''
@@ -38,6 +39,12 @@ class wizard_account_chart(wizard.interf
'relation': 'account.fiscalyear',
'help': 'Keep empty for all open fiscal year',
},
+ 'period': {
+ 'string': 'Period',
+ 'type': 'many2one',
+ 'relation': 'account.period',
+ 'help': 'Keep empty for all periods',
+ },
'target_move': {
'string': 'Target Moves',
'type': 'selection',
@@ -60,9 +67,11 @@ class wizard_account_chart(wizard.interf
result = mod_obj._get_id(cr, uid, 'account', 'action_account_tree')
id = mod_obj.read(cr, uid, [result], ['res_id'])[0]['res_id']
result = act_obj.read(cr, uid, [id], context=context)[0]
- result['context'] = str({'fiscalyear': data['form']['fiscalyear'],'state':data['form']['target_move']})
+ result['context'] = str({'fiscalyear': data['form']['fiscalyear'],'period':data['form']['period'],'state':data['form']['target_move']})
if data['form']['fiscalyear']:
- result['name']+=':'+pooler.get_pool(cr.dbname).get('account.fiscalyear').read(cr,uid,[data['form']['fiscalyear']])[0]['code']
+ result['name']+=': '+pooler.get_pool(cr.dbname).get('account.fiscalyear').read(cr,uid,[data['form']['fiscalyear']])[0]['code']
+ if data['form']['period']:
+ result['name']+='/'+pooler.get_pool(cr.dbname).get('account.period').read(cr,uid,[data['form']['period']])[0]['code']
return result
states = {
--- /home/terp/OpenERP/official/addons/account/account_view.xml 2010-04-06 08:29:45.091463943 +0200
+++ account_view.xml 2010-04-26 17:01:40.514894142 +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,21 @@
<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="balance_period_init"/>
+ <field name="debit_period"/>
+ <field name="credit_period"/>
+ <field name="balance_period_end"/>
<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 18:07:34.392519163 +0200
@@ -31,7 +31,11 @@ import mx.DateTime
from mx.DateTime import RelativeDateTime, now, DateTime, localtime
from tools import config
+import sys
+#----------------------------------------------------------------------------
+# Account Payment Term
+#----------------------------------------------------------------------------
class account_payment_term(osv.osv):
_name = "account.payment.term"
_description = "Payment Term"
@@ -195,21 +204,81 @@ 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)
+ period_current = self.pool.get('account.move.line')._period_current_get(cr, uid, context=context)
+ print >>sys.stderr,'TEST period current', period_current, type(period_current)
+ periods_exclude = self.pool.get('account.move.line')._periods_exclude_get(cr, uid, context=context)
+ print >>sys.stderr,'TEST periods exclude', periods_exclude
+ if type(period_current).__name__ == 'int':
+ period_current = str(period_current)
+ if type(periods_exclude).__name__ == 'int':
+ periods_exclude = str(periods_exclude)
+ #print >>sys.stderr,'TEST period current', period_current, type(period_current)
+ periods_exclude2 = period_current +','+ periods_exclude
+ print >>sys.stderr,'TEST periods exclude 2', periods_exclude2
+
+ 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_debit_period = 'COALESCE(SUM(case when l.period_id in ('+period_current+') then l.debit else 0 end),0) as debit_period '
+ case_credit_period = 'COALESCE(SUM(case when l.period_id in ('+period_current+') then l.credit else 0 end),0 ) as credit_period '
+ case_balance_period = 'COALESCE(SUM(case when l.period_id in ('+period_current+') then l.debit -l.credit else 0 end),0 ) as balance_period '
+ 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 '
+ case_balance_period_init = 'COALESCE(SUM(case when l.period_id in ('+periods_exclude2+') then 0 else l.debit - l.credit end),0) as balance_period_init '
+ case_balance_period_end = 'COALESCE(SUM(case when l.period_id in ('+periods_exclude+') then 0 else l.debit - l.credit end),0) as balance_period_end '
+ print >>sys.stderr,'TEST unclosed', case_balance_unclosed
+ print >>sys.stderr,'TEST current', case_debit_period
+
+# 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,
+ 'debit_period': case_debit_period,
+ 'credit_period': case_credit_period,
+ 'balance_init': case_balance_init,
+ 'balance_unclosed': case_balance_unclosed,
+ 'balance_period_init': case_balance_period_init,
+ 'balance_period_end': case_balance_period_end,
+ 'balance_period': case_balance_period,
}
+
#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 +286,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 +358,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 +403,18 @@ 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_period_init': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Period Initial Balance', multi='balance'),
+ 'balance_period_end': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Period End Balance', multi='balance'),
+ 'balance_period': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Period 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'),
+ 'credit_period': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Credit Period', 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'),
+ 'debit_period': fields.function(__compute, digits=(16, int(config['price_accuracy'])), method=True, string='Debit Period', 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',
@@ -477,6 +595,9 @@ class account_account(osv.osv):
account_account()
--- /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 18:15:54.746418203 +0200
@@ -29,11 +29,98 @@ 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_current_get(self, cr, uid, obj='l', context={}):
+ period_obj = self.pool.get('account.period')
+ if not context.get('period', False):
+ today = now()
+ #to get special you must explicitely select
+ period_ids = period_obj.search(cr, uid, [('special','=',False),('date_start', '<=', today), ('date_stop', '>=', today) ])
+ period_id_selected = (','.join([str(x) for x in period_ids])) or '0'
+ else:
+ period_id_selected = context.get('period')
+ return period_id_selected
+
+ def _periods_exclude_get(self, cr, uid, obj='l', context={}):
+ period_obj = self.pool.get('account.period')
+ if not context.get('period', False):
+ today = now()
+ #to get special you must explicitely select
+ period_ids = period_obj.search(cr, uid, [('date_start', '>', today)] )
+ else:
+ # FIXME
+ period_id = context.get('period',False)
+ period_current = period_obj.search(cr, uid, [('id','=',period_id)])
+ for period_current in self.pool.get('account.period').browse(cr, uid,period_current):
+ period_ids = period_obj.search(cr, uid, [('date_start', '>', period_current.date_start)] )
+ period_ids_selected = (','.join([str(x) for x in period_ids])) or '0'
+ return period_ids_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):
Follow ups
