addons/account/report/account_partner_ledger.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
   6 #
   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.
  11 #
  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.
  16 #
  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/>.
  19 #
  20 ##############################################################################
  21
  22 import time
  23 import re
  24
  25 import rml_parse
  26 from report import report_sxw
  27 from common_report_header import common_report_header
  28
  29 class third_party_ledger(rml_parse.rml_parse, common_report_header):
  30
  31     def __init__(self, cr, uid, name, context=None):
  32         super(third_party_ledger, self).__init__(cr, uid, name, context=context)
  33         self.localcontext.update({
  34             'time': time,
  35             'lines': self.lines,
  36             'sum_debit_partner': self._sum_debit_partner,
  37             'sum_credit_partner': self._sum_credit_partner,
  38 #            'sum_debit': self._sum_debit,
  39 #            'sum_credit': self._sum_credit,
  40             'get_currency': self._get_currency,
  41             'comma_me': self.comma_me,
  42             'get_start_period': self.get_start_period,
  43             'get_end_period': self.get_end_period,
  44             'get_account': self._get_account,
  45             'get_filter': self._get_filter,
  46             'get_start_date': self._get_start_date,
  47             'get_end_date': self._get_end_date,
  48             'get_fiscalyear': self._get_fiscalyear,
  49             'get_journal': self._get_journal,
  50             'get_partners':self._get_partners,
  51             'get_intial_balance':self._get_intial_balance,
  52             'display_initial_balance':self._display_initial_balance,
  53             'display_currency':self._display_currency,
  54             'get_target_move': self._get_target_move,
  55         })
  56
  57     def set_context(self, objects, data, ids, report_type=None):
  58         obj_move = self.pool.get('account.move.line')
  59         obj_partner = self.pool.get('res.partner')
  60         self.query = obj_move._query_get(self.cr, self.uid, obj='l', context=data['form'].get('used_context', {}))
  61         ctx2 = data['form'].get('used_context',{}).copy()
  62         ctx2.update({'initial_bal': True})
  63         self.init_query = obj_move._query_get(self.cr, self.uid, obj='l', context=ctx2)
  64         self.reconcil = data['form'].get('reconcil', True)
  65         self.initial_balance = data['form'].get('initial_balance', True)
  66         self.result_selection = data['form'].get('result_selection', 'customer')
  67         self.amount_currency = data['form'].get('amount_currency', False)
  68         self.target_move = data['form'].get('target_move', 'all')
  69         PARTNER_REQUEST = ''
  70         move_state = ['draft','posted']
  71         if self.target_move == 'posted':
  72             move_state = ['posted']
  73
  74         if (data['model'] == 'res.partner'):
  75             ## Si on imprime depuis les partenaires
  76             if ids:
  77                 PARTNER_REQUEST =  "AND line.partner_id IN %s",(tuple(ids),)
  78         if self.result_selection == 'supplier':
  79             self.ACCOUNT_TYPE = ['payable']
  80         elif self.result_selection == 'customer':
  81             self.ACCOUNT_TYPE = ['receivable']
  82         else:
  83             self.ACCOUNT_TYPE = ['payable','receivable']
  84
  85         self.cr.execute(
  86             "SELECT a.id " \
  87             "FROM account_account a " \
  88             "LEFT JOIN account_account_type t " \
  89                 "ON (a.type=t.code) " \
  90                 'WHERE a.type IN %s' \
  91                 "AND a.active", (tuple(self.ACCOUNT_TYPE), ))
  92         self.account_ids = [a for (a,) in self.cr.fetchall()]
  93         partner_to_use = []
  94         self.cr.execute(
  95                 "SELECT DISTINCT l.partner_id " \
  96                 "FROM account_move_line AS l, account_account AS account, " \
  97                 " account_move AS am " \
  98                 "WHERE l.partner_id IS NOT NULL " \
  99                     "AND l.account_id = account.id " \
 100                     "AND am.id = l.move_id " \
 101                     "AND am.state IN %s"
 102 #                    "AND " + self.query +" " \
 103                     "AND l.account_id IN %s " \
 104                     " " + PARTNER_REQUEST + " " \
 105                     "AND account.active ",
 106                 (tuple(move_state), tuple(self.account_ids),))
 107
 108         res = self.cr.dictfetchall()
 109         for res_line in res:
 110             partner_to_use.append(res_line['partner_id'])
 111         new_ids = partner_to_use
 112         self.partner_ids = new_ids
 113         objects = obj_partner.browse(self.cr, self.uid, new_ids)
 114         return super(third_party_ledger, self).set_context(objects, data, new_ids, report_type)
 115
 116     def comma_me(self, amount):
 117         if type(amount) is float:
 118             amount = str('%.2f'%amount)
 119         else:
 120             amount = str(amount)
 121         if (amount == '0'):
 122              return ' '
 123         orig = amount
 124         new = re.sub("^(-?\d+)(\d{3})", "\g<1>'\g<2>", amount)
 125         if orig == new:
 126             return new
 127         else:
 128             return self.comma_me(new)
 129
 130     def lines(self, partner):
 131         move_state = ['draft','posted']
 132         if self.target_move == 'posted':
 133             move_state = ['posted']
 134
 135         full_account = []
 136         if self.reconcil:
 137             RECONCILE_TAG = " "
 138         else:
 139             RECONCILE_TAG = "AND l.reconcile_id IS NULL"
 140         self.cr.execute(
 141             "SELECT l.id, l.date, j.code, acc.code as a_code, acc.name as a_name, l.ref, m.name as move_name, l.name, l.debit, l.credit, l.amount_currency, c.symbol AS currency_code " \
 142             "FROM account_move_line l " \
 143             "LEFT JOIN account_journal j " \
 144                 "ON (l.journal_id = j.id) " \
 145             "LEFT JOIN account_account acc " \
 146                 "ON (l.account_id = acc.id) " \
 147             "LEFT JOIN res_currency c ON (l.currency_id=c.id)" \
 148             "LEFT JOIN account_move m ON (m.id=l.move_id)" \
 149             "WHERE l.partner_id = %s " \
 150                 "AND l.account_id IN %s AND " + self.query +" " \
 151                 "AND m.state IN %s " \
 152                 " " + RECONCILE_TAG + " "\
 153                 "ORDER BY l.date",
 154                 (partner.id, tuple(self.account_ids), tuple(move_state)))
 155         res = self.cr.dictfetchall()
 156         sum = 0.0
 157         for r in res:
 158             sum = r['debit'] - r['credit']
 159             r['progress'] = sum
 160             full_account.append(r)
 161         return full_account
 162
 163     def _get_intial_balance(self, partner):
 164         move_state = ['draft','posted']
 165         if self.target_move == 'posted':
 166             move_state = ['posted']
 167
 168         self.cr.execute(
 169             "SELECT COALESCE(SUM(l.debit),0.0), COALESCE(SUM(l.credit),0.0), COALESCE(sum(debit-credit), 0.0) " \
 170             "FROM account_move_line AS l,  " \
 171             "account_move AS m "
 172             "WHERE partner_id = %s " \
 173             "AND m.id = l.move_id " \
 174             "AND m.state IN %s "
 175             "AND account_id IN %s" \
 176             "AND reconcile_id IS NULL  " \
 177             "AND " + self.init_query + "  ",
 178             (partner.id, tuple(move_state), tuple(self.account_ids)))
 179         return self.cr.fetchall()
 180
 181     def _sum_debit_partner(self, partner):
 182         move_state = ['draft','posted']
 183         if self.target_move == 'posted':
 184             move_state = ['posted']
 185
 186         result_tmp = 0.0
 187         result_init = 0.0
 188         if self.reconcil:
 189             RECONCILE_TAG = " "
 190         else:
 191             RECONCILE_TAG = "AND reconcile_id IS NULL"
 192         if self.initial_balance:
 193             self.cr.execute(
 194                     "SELECT sum(debit) " \
 195                     "FROM account_move_line AS l, " \
 196                     "account_move AS m "
 197                     "WHERE partner_id = %s" \
 198                         "AND m.id = l.move_id " \
 199                         "AND m.state IN %s "
 200                         "AND account_id IN %s" \
 201                         " " + RECONCILE_TAG + " " \
 202                         "AND " + self.init_query + " ",
 203                     (partner.id, tuple(move_state), tuple(self.account_ids)))
 204             contemp = self.cr.fetchone()
 205             if contemp != None:
 206                 result_init = contemp[0] or 0.0
 207             else:
 208                 result_init = result_tmp + 0.0
 209
 210         self.cr.execute(
 211                 "SELECT sum(debit) " \
 212                 "FROM account_move_line AS l, " \
 213                 "account_move AS m "
 214                 "WHERE partner_id = %s " \
 215                     "AND m.id = l.move_id " \
 216                     "AND m.state IN %s "
 217                     "AND account_id IN %s" \
 218                     " " + RECONCILE_TAG + " " \
 219                     "AND " + self.query + " ",
 220                 (partner.id, tuple(move_state), tuple(self.account_ids),))
 221
 222         contemp = self.cr.fetchone()
 223         if contemp != None:
 224             result_tmp = contemp[0] or 0.0
 225         else:
 226             result_tmp = result_tmp + 0.0
 227
 228         return result_tmp  + result_init
 229
 230     def _sum_credit_partner(self, partner):
 231         move_state = ['draft','posted']
 232         if self.target_move == 'posted':
 233             move_state = ['posted']
 234
 235         result_tmp = 0.0
 236         result_init = 0.0
 237         if self.reconcil:
 238             RECONCILE_TAG = " "
 239         else:
 240             RECONCILE_TAG = "AND reconcile_id IS NULL"
 241         if self.initial_balance:
 242             self.cr.execute(
 243                     "SELECT sum(credit) " \
 244                     "FROM account_move_line AS l, " \
 245                     "account_move AS m  "
 246                     "WHERE partner_id = %s" \
 247                         "AND m.id = l.move_id " \
 248                         "AND m.state IN %s "
 249                         "AND account_id IN %s" \
 250                         " " + RECONCILE_TAG + " " \
 251                         "AND " + self.init_query + " ",
 252                     (partner.id, tuple(move_state), tuple(self.account_ids)))
 253             contemp = self.cr.fetchone()
 254             if contemp != None:
 255                 result_init = contemp[0] or 0.0
 256             else:
 257                 result_init = result_tmp + 0.0
 258
 259         self.cr.execute(
 260                 "SELECT sum(credit) " \
 261                 "FROM account_move_line AS l, " \
 262                 "account_move AS m "
 263                 "WHERE partner_id=%s " \
 264                     "AND m.id = l.move_id " \
 265                     "AND m.state IN %s "
 266                     "AND account_id IN %s" \
 267                     " " + RECONCILE_TAG + " " \
 268                     "AND " + self.query + " ",
 269                 (partner.id, tuple(move_state), tuple(self.account_ids),))
 270
 271         contemp = self.cr.fetchone()
 272         if contemp != None:
 273             result_tmp = contemp[0] or 0.0
 274         else:
 275             result_tmp = result_tmp + 0.0
 276         return result_tmp  + result_init
 277
 278     # code is deprecated
 279 #    def _sum_debit(self):
 280 #        move_state = ['draft','posted']
 281 #        if self.target_move == 'posted':
 282 #            move_state = ['posted']
 283 #
 284 #        if not self.ids:
 285 #            return 0.0
 286 #        result_tmp = 0.0
 287 #        result_init = 0.0
 288 #        if self.reconcil:
 289 #            RECONCILE_TAG = " "
 290 #        else:
 291 #            RECONCILE_TAG = "AND reconcile_id IS NULL"
 292 #        if self.initial_balance:
 293 #            self.cr.execute(
 294 #                    "SELECT sum(debit) " \
 295 #                    "FROM account_move_line AS l, " \
 296 #                    "account_move AS m "
 297 #                    "WHERE partner_id IN %s" \
 298 #                        "AND m.id = l.move_id " \
 299 #                        "AND m.state IN %s "
 300 #                        "AND account_id IN %s" \
 301 #                        "AND reconcile_id IS NULL " \
 302 #                        "AND " + self.init_query + " ",
 303 #                    (tuple(self.partner_ids), tuple(move_state), tuple(self.account_ids)))
 304 #            contemp = self.cr.fetchone()
 305 #            if contemp != None:
 306 #                result_init = contemp[0] or 0.0
 307 #            else:
 308 #                result_init = result_tmp + 0.0
 309 #
 310 #        self.cr.execute(
 311 #                "SELECT sum(debit) " \
 312 #                "FROM account_move_line AS l, " \
 313 #                "account_move AS m "
 314 #                "WHERE partner_id IN %s" \
 315 #                    "AND m.id = l.move_id " \
 316 #                    "AND m.state IN %s "
 317 #                    "AND account_id IN %s" \
 318 #                    " " + RECONCILE_TAG + " " \
 319 #                    "AND " + self.query + " ",
 320 #                    (tuple(self.partner_ids), tuple(move_state) ,tuple(self.account_ids),))
 321 #        contemp = self.cr.fetchone()
 322 #        if contemp != None:
 323 #            result_tmp = contemp[0] or 0.0
 324 #        else:
 325 #            result_tmp = result_tmp + 0.0
 326 #        return result_tmp  + result_init
 327 #
 328 #    def _sum_credit(self):
 329 #        move_state = ['draft','posted']
 330 #        if self.target_move == 'posted':
 331 #            move_state = ['posted']
 332 #
 333 #        if not self.ids:
 334 #            return 0.0
 335 #        result_tmp = 0.0
 336 #        result_init = 0.0
 337 #        if self.reconcil:
 338 #            RECONCILE_TAG = " "
 339 #        else:
 340 #            RECONCILE_TAG = "AND reconcile_id IS NULL"
 341 #        if self.initial_balance:
 342 #            self.cr.execute(
 343 #                    "SELECT sum(credit) " \
 344 #                    "FROM account_move_line AS l, " \
 345 #                    "account_move AS m  "
 346 #                    "WHERE partner_id IN %s" \
 347 #                        "AND m.id = l.move_id " \
 348 #                        "AND m.state IN %s "
 349 #                        "AND account_id IN %s" \
 350 #                        "AND reconcile_id IS NULL " \
 351 #                        "AND " + self.init_query + " ",
 352 #                    (tuple(self.partner_ids), tuple(move_state), tuple(self.account_ids)))
 353 #            contemp = self.cr.fetchone()
 354 #            if contemp != None:
 355 #                result_init = contemp[0] or 0.0
 356 #            else:
 357 #                result_init = result_tmp + 0.0
 358 #
 359 #        self.cr.execute(
 360 #                "SELECT sum(credit) " \
 361 #                "FROM account_move_line AS l, " \
 362 #                "account_move AS m "
 363 #                "WHERE partner_id  IN %s" \
 364 #                    "AND m.id = l.move_id " \
 365 #                    "AND m.state IN %s "
 366 #                    "AND account_id IN %s" \
 367 #                    " " + RECONCILE_TAG + " " \
 368 #                    "AND " + self.query + " ",
 369 #                    (tuple(self.partner_ids), tuple(move_state), tuple(self.account_ids),))
 370 #        contemp = self.cr.fetchone()
 371 #        if contemp != None:
 372 #            result_tmp = contemp[0] or 0.0
 373 #        else:
 374 #            result_tmp = result_tmp + 0.0
 375 #        return result_tmp  + result_init
 376
 377     def _get_partners(self):
 378         if self.result_selection == 'customer':
 379             return 'Receivable Accounts'
 380         elif self.result_selection == 'supplier':
 381             return 'Payable Accounts'
 382         elif self.result_selection == 'customer_supplier':
 383             return 'Receivable and Payable Accounts'
 384         return ''
 385
 386     def _sum_currency_amount_account(self, account, form):
 387         self._set_get_account_currency_code(account.id)
 388         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,))
 389         total = self.cr.fetchone()
 390         if self.account_currency:
 391             return_field = str(total[0]) + self.account_currency
 392             return return_field
 393         else:
 394             currency_total = self.tot_currency = 0.0
 395             return currency_total
 396
 397     def _display_initial_balance(self, data):
 398          if self.initial_balance:
 399              return True
 400          return False
 401
 402     def _display_currency(self, data):
 403          if self.amount_currency:
 404              return True
 405          return False
 406
 407 report_sxw.report_sxw('report.account.third_party_ledger', 'res.partner',
 408         'addons/account/report/account_partner_ledger.rml',parser=third_party_ledger,
 409         header='internal')
 410
 411 report_sxw.report_sxw('report.account.third_party_ledger_other', 'res.partner',
 412         'addons/account/report/account_partner_ledger_other.rml',parser=third_party_ledger,
 413         header='internal')
 414
 415 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: