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