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