addons/account/report/general_ledger_landscape.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 # Copyright (c) 2005-2006 CamptoCamp
   5 #
   6 # WARNING: This program as such is intended to be used by professional
   7 # programmers who take the whole responsability of assessing all potential
   8 # consequences resulting from its eventual inadequacies and bugs
   9 # End users who are looking for a ready-to-use solution with commercial
  10 # garantees and support are strongly adviced to contract a Free Software
  11 # Service Company
  12 #
  13 # This program is Free Software; you can redistribute it and/or
  14 # modify it under the terms of the GNU General Public License
  15 # as published by the Free Software Foundation; either version 2
  16 # of the License, or (at your option) any later version.
  17 #
  18 # This program is distributed in the hope that it will be useful,
  19 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  20 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  21 # GNU General Public License for more details.
  22 #
  23 # You should have received a copy of the GNU General Public License
  24 # along with this program; if not, write to the Free Software
  25 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  26 #
  27 ##############################################################################
  28
  29 import time
  30 from mx.DateTime import *
  31 from report import report_sxw
  32 import xml
  33 import rml_parse
  34 import pooler
  35
  36 class general_ledger_landscape(rml_parse.rml_parse):
  37     _name = 'report.account.general.ledger_landscape'
  38
  39
  40     def set_context(self, objects, data, ids, report_type = None):
  41         ##
  42         self.borne_date = self.get_min_date(data['form'])
  43         ##
  44         new_ids = []
  45         if (data['model'] == 'account.account'):
  46             new_ids = ids
  47         else:
  48             new_ids.append(data['form']['Account_list'])
  49
  50             objects = self.pool.get('account.account').browse(self.cr, self.uid, new_ids)
  51
  52         super(general_ledger_landscape, self).set_context(objects, data, new_ids,report_type)
  53
  54     def __init__(self, cr, uid, name, context):
  55         super(general_ledger_landscape, self).__init__(cr, uid, name, context)
  56         self.date_borne = {}
  57         self.query = ""
  58         self.child_ids = ""
  59         self.tot_currency = 0.0
  60         self.period_sql = ""
  61         self.sold_accounts = {}
  62         self.localcontext.update( {
  63             'time': time,
  64             'lines': self.lines,
  65             'sum_debit_account': self._sum_debit_account,
  66             'sum_credit_account': self._sum_credit_account,
  67             'sum_solde_account': self._sum_solde_account,
  68             'sum_debit': self._sum_debit,
  69             'sum_credit': self._sum_credit,
  70             'sum_solde': self._sum_solde,
  71             'get_children_accounts': self.get_children_accounts,
  72             'sum_currency_amount_account': self._sum_currency_amount_account
  73         })
  74         self.context = context
  75     def _calc_contrepartie(self,cr,uid,ids, context={}):
  76         result = {}
  77             #for id in ids:
  78         #    result.setdefault(id, False)
  79
  80         for account_line in self.pool.get('account.move.line').browse(cr, uid, ids, context):
  81             # For avoid long text in the field we will limit it to 5 lines
  82             #
  83             #
  84             #
  85             result[account_line.id] = ' '
  86             num_id_move = str(account_line.move_id.id)
  87             num_id_line = str(account_line.id)
  88             account_id = str(account_line.account_id.id)
  89             # search the basic account
  90             # We have the account ID we will search all account move line from now until this time
  91             # We are in the case of we are on the top of the account move Line
  92             cr.execute('SELECT distinct(ac.code) as code_rest,ac.name as name_rest from account_account AS ac, account_move_line mv\
  93                     where ac.id = mv.account_id and mv.move_id = ' + num_id_move +' and mv.account_id <> ' + account_id )
  94             res_mv = cr.dictfetchall()
  95             # we need a result more than 2 line to make the test so we will made the the on 1 because we have exclude the current line
  96             if (len(res_mv) >=1):
  97                 concat = ''
  98                 rup_id = 0
  99                 for move_rest in res_mv:
 100                     concat = concat + move_rest['code_rest'] + '|'
 101                     result[account_line.id] = concat
 102                     if rup_id >5:
 103                         # we need to stop the computing and to escape but before we will add "..."
 104                         result[account_line.id] = concat + '...'
 105                         break
 106                     rup_id+=1
 107         return result
 108
 109     def get_min_date(self,form):
 110
 111         ## Get max born from account_fiscal year
 112         #
 113         sql = """ select min(fy.date_start) as start_date,max(fy.date_stop) as stop_date from account_fiscalyear
 114               As fy where fy.state <> 'close'
 115             """
 116         self.cr.execute(sql)
 117         res = self.cr.dictfetchall()
 118         borne_min = res[0]['start_date']
 119         borne_max = res[0]['stop_date']
 120         if form['state'] == 'byperiod':
 121             ## This function will return the most aged date
 122             periods = form['periods'][0][2]
 123             if not periods:
 124                 sql = """
 125                     Select min(p.date_start) as start_date,max(p.date_stop) as stop_date from account_period as p where p.fiscalyear_id = """ + str(form['fiscalyear'])   + """
 126                     """
 127             else:
 128                 periods_id = ','.join(map(str, periods))
 129                 sql = """
 130                     Select min(p.date_start) as start_date,max(p.date_stop) as stop_date from account_period as p where p.id in ( """ + periods_id   + """)
 131                     """
 132             self.cr.execute(sql)
 133             res = self.cr.dictfetchall()
 134             borne_min = res[0]['start_date']
 135             borne_max = res[0]['stop_date']
 136         elif form['state'] == 'bydate':
 137             borne_min = form['date_from']
 138             borne_max = form['date_to']
 139         elif form['state'] == 'all':
 140             periods = form['periods'][0][2]
 141             if not periods:
 142                 sql = """
 143                     Select min(p.date_start) as start_date,max(p.date_stop) as stop_date from account_period as p where p.fiscalyear_id = """ + str(form['fiscalyear'])   + """
 144                     """
 145             else:
 146                 periods_id = ','.join(map(str, periods))
 147                 sql = """
 148                     Select min(p.date_start) as start_date,max(p.date_stop) as stop_date from account_period as p where p.id in ( """ + periods_id   + """)
 149                     """
 150             self.cr.execute(sql)
 151             res = self.cr.dictfetchall()
 152             period_min = res[0]['start_date']
 153             period_max = res[0]['stop_date']
 154             date_min = form['date_from']
 155             date_max = form['date_to']
 156             if period_min<date_min:
 157                 borne_min = period_min
 158             else :
 159                 borne_min = date_min
 160             if date_max<period_max:
 161                 borne_max = period_max
 162             else :
 163                 borne_max = date_max
 164         elif form['state'] == 'none':
 165             sql = """
 166                     SELECT min(date) as start_date,max(date) as stop_date FROM account_move_line """
 167             self.cr.execute(sql)
 168             res = self.cr.dictfetchall()
 169             borne_min = res[0]['start_date']
 170             borne_max = res[0]['stop_date']
 171         self.date_borne = {
 172             'min_date': borne_min,
 173             'max_date': borne_max,
 174             }
 175         return self.date_borne
 176
 177
 178
 179
 180     def get_children_accounts(self, account, form):
 181
 182         print self.ids
 183         self.child_ids = self.pool.get('account.account').search(self.cr, self.uid,
 184             [('parent_id', 'child_of', self.ids)])
 185 #
 186         res = []
 187         ctx = self.context.copy()
 188         ## We will make the test for period or date
 189         ## We will now make the test
 190         #
 191         if form.has_key('fiscalyear'):
 192             ctx['fiscalyear'] = form['fiscalyear']
 193             ctx['periods'] = form['periods'][0][2]
 194         else:
 195             ctx['date_from'] = form['date_from']
 196             ctx['date_to'] = form['date_to']
 197         ##
 198
 199         #
 200         self.query = self.pool.get('account.move.line')._query_get(self.cr, self.uid, context=ctx)
 201         if account and account.child_consol_ids: # add ids of consolidated childs also of selected account
 202             ctx['consolidate_childs'] = True
 203             ctx['account_id'] = account.id
 204         ids_acc = self.pool.get('account.account').search(self.cr, self.uid,[('parent_id', 'child_of', [account.id])], context=ctx)
 205         for child_id in ids_acc:
 206             child_account = self.pool.get('account.account').browse(self.cr, self.uid, child_id)
 207             sold_account = self._sum_solde_account(child_account,form)
 208             self.sold_accounts[child_account.id] = sold_account
 209             if form['display_account'] == 'bal_mouvement':
 210                 if child_account.type != 'view' \
 211                 and len(self.pool.get('account.move.line').search(self.cr, self.uid,
 212                     [('account_id','=',child_account.id)],
 213                     context=ctx)) <> 0 :
 214                     res.append(child_account)
 215             elif form['display_account'] == 'bal_solde':
 216                 if child_account.type != 'view' \
 217                 and len(self.pool.get('account.move.line').search(self.cr, self.uid,
 218                     [('account_id','=',child_account.id)],
 219                     context=ctx)) <> 0 :
 220                     if ( sold_account <> 0.0):
 221                         res.append(child_account)
 222             else:
 223                 if child_account.type != 'view' \
 224                 and len(self.pool.get('account.move.line').search(self.cr, self.uid,
 225                     [('account_id','>=',child_account.id)],
 226                     context=ctx)) <> 0 :
 227                     res.append(child_account)
 228         ##
 229         if not len(res):
 230
 231             return [account]
 232         else:
 233             ## We will now compute solde initiaux
 234             for move in res:
 235                 SOLDEINIT = "SELECT sum(l.debit) AS sum_debit, sum(l.credit) AS sum_credit FROM account_move_line l WHERE l.account_id = " + str(move.id) +  " AND l.date < '" + self.borne_date['max_date'] + "'" +  " AND l.date > '" + self.borne_date['min_date'] + "'"
 236                 self.cr.execute(SOLDEINIT)
 237                 resultat = self.cr.dictfetchall()
 238                 if resultat[0] :
 239                     if resultat[0]['sum_debit'] == None:
 240                         sum_debit = 0
 241                     else:
 242                         sum_debit = resultat[0]['sum_debit']
 243                     if resultat[0]['sum_credit'] == None:
 244                         sum_credit = 0
 245                     else:
 246                         sum_credit = resultat[0]['sum_credit']
 247
 248                     move.init_credit = sum_credit
 249                     move.init_debit = sum_debit
 250
 251                 else:
 252                     move.init_credit = 0
 253                     move.init_debit = 0
 254
 255
 256         return res
 257
 258     def lines(self, account, form):
 259         inv_types = {
 260                 'out_invoice': 'CI: ',
 261                 'in_invoice': 'SI: ',
 262                 'out_refund': 'OR: ',
 263                 'in_refund': 'SR: ',
 264                 }
 265
 266         if form['sortbydate'] == 'sort_date':
 267             sorttag = 'l.date'
 268         else:
 269             sorttag = 'j.code'
 270         sql = """
 271             SELECT l.id, l.date, j.code,c.code AS currency_code,l.amount_currency,l.ref, l.name , l.debit, l.credit, l.period_id
 272                     FROM account_move_line as l
 273                        LEFT JOIN res_currency c on (l.currency_id=c.id)
 274                           JOIN account_journal j on (l.journal_id=j.id)
 275                              AND account_id = %%s
 276                              AND %s
 277                                WHERE l.date<=%%s
 278                                AND l.date>=%%s
 279                                ORDER by %s""" % (self.query, sorttag)
 280
 281         self.cr.execute(sql, (account.id, self.date_borne['max_date'], self.date_borne['min_date'],))
 282
 283         res = self.cr.dictfetchall()
 284         sum = 0.0
 285         account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 286         for l in res:
 287             line = self.pool.get('account.move.line').browse(self.cr, self.uid, l['id'])
 288             l['move'] = line.move_id.name
 289             self.cr.execute('Select id from account_invoice where move_id =%s'%(line.move_id.id))
 290             tmpres = self.cr.dictfetchall()
 291             if len(tmpres) > 0 :
 292                 inv = self.pool.get('account.invoice').browse(self.cr, self.uid, tmpres[0]['id'])
 293                 l['ref'] = inv_types[inv.type] + ': '+str(inv.number)
 294             if line.partner_id :
 295                 l['partner'] = line.partner_id.name
 296             else :
 297                 l['partner'] = ''
 298             sum = l['debit'] - l ['credit']
 299 #            c = time.strptime(l['date'],"%Y-%m-%d")
 300 #            l['date'] = time.strftime("%d-%m-%Y",c)
 301             l['progress'] = sum
 302             l['line_corresp'] = self._calc_contrepartie(self.cr,self.uid,[l['id']])[l['id']]
 303             # Modification du amount Currency
 304             if (l['credit'] > 0):
 305                 if l['amount_currency'] != None:
 306                     l['amount_currency'] = abs(l['amount_currency']) * -1
 307
 308             #
 309             if l['amount_currency'] != None:
 310                 self.tot_currency = self.tot_currency + l['amount_currency']
 311         return res
 312
 313     def _sum_debit_account(self, account, form):
 314
 315         self.cr.execute("SELECT sum(debit) "\
 316                 "FROM account_move_line l "\
 317                 "WHERE l.account_id = %s AND %s "%(account.id, self.query))
 318         ## Add solde init to the result
 319         #
 320         sum_debit = self.cr.fetchone()[0] or 0.0
 321         if form['soldeinit']:
 322             sum_debit += account.init_debit
 323         #
 324         ##
 325         return sum_debit
 326
 327     def _sum_credit_account(self, account, form):
 328
 329         self.cr.execute("SELECT sum(credit) "\
 330                 "FROM account_move_line l "\
 331                 "WHERE l.account_id = %s AND %s "%(account.id,self.query))
 332         ## Add solde init to the result
 333         #
 334         sum_credit = self.cr.fetchone()[0] or 0.0
 335         if form['soldeinit']:
 336             sum_credit += account.init_credit
 337         #
 338         ##
 339
 340         return sum_credit
 341
 342     def _sum_solde_account(self, account, form):
 343         self.cr.execute("SELECT (sum(debit) - sum(credit)) as tot_solde "\
 344                 "FROM account_move_line l "\
 345                 "WHERE l.account_id = %s AND %s"%(account.id,self.query))
 346         sum_solde = self.cr.fetchone()[0] or 0.0
 347         if form.get('soldeinit',False):
 348             sum_solde += account.init_debit - account.init_credit
 349
 350         return sum_solde
 351
 352     def _sum_debit(self, form):
 353         if not self.ids:
 354             return 0.0
 355         self.cr.execute("SELECT sum(debit) "\
 356                 "FROM account_move_line l "\
 357                 "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query)
 358         sum_debit = self.cr.fetchone()[0] or 0.0
 359         return sum_debit
 360
 361     def _sum_credit(self, form):
 362         if not self.ids:
 363             return 0.0
 364         self.cr.execute("SELECT sum(credit) "\
 365                 "FROM account_move_line l "\
 366                 "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query)
 367         ## Add solde init to the result
 368         #
 369         sum_credit = self.cr.fetchone()[0] or 0.0
 370         return sum_credit
 371
 372     def _sum_solde(self, form):
 373         if not self.ids:
 374             return 0.0
 375         self.cr.execute("SELECT (sum(debit) - sum(credit)) as tot_solde "\
 376                 "FROM account_move_line l "\
 377                 "WHERE l.account_id in ("+','.join(map(str, self.child_ids))+") AND "+self.query)
 378         sum_solde = self.cr.fetchone()[0] or 0.0
 379         return sum_solde
 380
 381     def _set_get_account_currency_code(self, account_id):
 382         self.cr.execute("SELECT c.code as code "\
 383                 "FROM res_currency c,account_account as ac "\
 384                 "WHERE ac.id = %s AND ac.currency_id = c.id"%(account_id))
 385         result = self.cr.fetchone()
 386         if result:
 387             self.account_currency = result[0]
 388         else:
 389             self.account_currency = False
 390
 391     def _sum_currency_amount_account(self, account, form):
 392         self._set_get_account_currency_code(account.id)
 393         self.cr.execute("SELECT sum(aml.amount_currency) FROM account_move_line as aml,res_currency as rc WHERE aml.currency_id = rc.id AND aml.account_id= %s ", (account.id,))
 394         total = self.cr.fetchone()
 395
 396         if self.account_currency:
 397             return_field = str(total[0]) + self.account_currency
 398             return return_field
 399         else:
 400             currency_total = self.tot_currency = 0.0
 401             return currency_total
 402
 403 report_sxw.report_sxw('report.account.general.ledger_landscape', 'account.account', 'addons/account/report/general_ledger_landscape.rml', parser=general_ledger_landscape, header=False)
 404 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: