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 from openerp.osv import osv
  24 from openerp.tools.translate import _
  25 from openerp.report import report_sxw
  26 from common_report_header import common_report_header
  27
  28
  29 class third_party_ledger(report_sxw.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.init_bal_sum = 0.0
  34         self.localcontext.update({
  35             'time': time,
  36             'lines': self.lines,
  37             'sum_debit_partner': self._sum_debit_partner,
  38             'sum_credit_partner': self._sum_credit_partner,
  39             'get_currency': self._get_currency,
  40             'get_start_period': self.get_start_period,
  41             'get_end_period': self.get_end_period,
  42             'get_account': self._get_account,
  43             'get_filter': self._get_filter,
  44             'get_start_date': self._get_start_date,
  45             'get_end_date': self._get_end_date,
  46             'get_fiscalyear': self._get_fiscalyear,
  47             'get_journal': self._get_journal,
  48             'get_partners':self._get_partners,
  49             'get_intial_balance':self._get_intial_balance,
  50             'display_initial_balance':self._display_initial_balance,
  51             'display_currency':self._display_currency,
  52             'get_target_move': self._get_target_move,
  53         })
  54
  55     def _get_filter(self, data):
  56         if data['form']['filter'] == 'unreconciled':
  57             return _('Unreconciled Entries')
  58         return super(third_party_ledger, self)._get_filter(data)
  59
  60     def set_context(self, objects, data, ids, report_type=None):
  61         obj_move = self.pool.get('account.move.line')
  62         obj_partner = self.pool.get('res.partner')
  63         self.query = obj_move._query_get(self.cr, self.uid, obj='l', context=data['form'].get('used_context', {}))
  64         ctx2 = data['form'].get('used_context',{}).copy()
  65         self.initial_balance = data['form'].get('initial_balance', True)
  66         if self.initial_balance:
  67             ctx2.update({'initial_bal': True})
  68         self.init_query = obj_move._query_get(self.cr, self.uid, obj='l', context=ctx2)
  69         self.reconcil = True
  70         if data['form']['filter'] == 'unreconciled':
  71             self.reconcil = False
  72         self.result_selection = data['form'].get('result_selection', 'customer')
  73         self.amount_currency = data['form'].get('amount_currency', False)
  74         self.target_move = data['form'].get('target_move', 'all')
  75         PARTNER_REQUEST = ''
  76         move_state = ['draft','posted']
  77         if self.target_move == 'posted':
  78             move_state = ['posted']
  79         if self.result_selection == 'supplier':
  80             self.ACCOUNT_TYPE = ['payable']
  81         elif self.result_selection == 'customer':
  82             self.ACCOUNT_TYPE = ['receivable']
  83         else:
  84             self.ACCOUNT_TYPE = ['payable','receivable']
  85
  86         self.cr.execute(
  87             "SELECT a.id " \
  88             "FROM account_account a " \
  89             "LEFT JOIN account_account_type t " \
  90                 "ON (a.type=t.code) " \
  91                 'WHERE a.type IN %s' \
  92                 "AND a.active", (tuple(self.ACCOUNT_TYPE), ))
  93         self.account_ids = [a for (a,) in self.cr.fetchall()]
  94         params = [tuple(move_state), tuple(self.account_ids)]
  95         #if we print from the partners, add a clause on active_ids
  96         if (data['model'] == 'res.partner') and ids:
  97             PARTNER_REQUEST =  "AND l.partner_id IN %s"
  98             params += [tuple(ids)]
  99         self.cr.execute(
 100                 "SELECT DISTINCT l.partner_id " \
 101                 "FROM account_move_line AS l, account_account AS account, " \
 102                 " account_move AS am " \
 103                 "WHERE l.partner_id IS NOT NULL " \
 104                     "AND l.account_id = account.id " \
 105                     "AND am.id = l.move_id " \
 106                     "AND am.state IN %s"
 107 #                    "AND " + self.query +" " \
 108                     "AND l.account_id IN %s " \
 109                     " " + PARTNER_REQUEST + " " \
 110                     "AND account.active ", params)
 111         self.partner_ids = [res['partner_id'] for res in self.cr.dictfetchall()]
 112         objects = obj_partner.browse(self.cr, self.uid, self.partner_ids)
 113         objects.sort(key=lambda x: (x.ref, x.name))
 114         return super(third_party_ledger, self).set_context(objects, data, self.partner_ids, report_type)
 115
 116     def lines(self, partner):
 117         move_state = ['draft','posted']
 118         if self.target_move == 'posted':
 119             move_state = ['posted']
 120
 121         full_account = []
 122         if self.reconcil:
 123             RECONCILE_TAG = " "
 124         else:
 125             RECONCILE_TAG = "AND l.reconcile_id IS NULL"
 126         self.cr.execute(
 127             "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 " \
 128             "FROM account_move_line l " \
 129             "LEFT JOIN account_journal j " \
 130                 "ON (l.journal_id = j.id) " \
 131             "LEFT JOIN account_account acc " \
 132                 "ON (l.account_id = acc.id) " \
 133             "LEFT JOIN res_currency c ON (l.currency_id=c.id)" \
 134             "LEFT JOIN account_move m ON (m.id=l.move_id)" \
 135             "WHERE l.partner_id = %s " \
 136                 "AND l.account_id IN %s AND " + self.query +" " \
 137                 "AND m.state IN %s " \
 138                 " " + RECONCILE_TAG + " "\
 139                 "ORDER BY l.date",
 140                 (partner.id, tuple(self.account_ids), tuple(move_state)))
 141         res = self.cr.dictfetchall()
 142         sum = 0.0
 143         if self.initial_balance:
 144             sum = self.init_bal_sum
 145         for r in res:
 146             sum += r['debit'] - r['credit']
 147             r['progress'] = sum
 148             full_account.append(r)
 149         return full_account
 150
 151     def _get_intial_balance(self, partner):
 152         move_state = ['draft','posted']
 153         if self.target_move == 'posted':
 154             move_state = ['posted']
 155         if self.reconcil:
 156             RECONCILE_TAG = " "
 157         else:
 158             RECONCILE_TAG = "AND l.reconcile_id IS NULL"
 159         self.cr.execute(
 160             "SELECT COALESCE(SUM(l.debit),0.0), COALESCE(SUM(l.credit),0.0), COALESCE(sum(debit-credit), 0.0) " \
 161             "FROM account_move_line AS l,  " \
 162             "account_move AS m "
 163             "WHERE l.partner_id = %s " \
 164             "AND m.id = l.move_id " \
 165             "AND m.state IN %s "
 166             "AND account_id IN %s" \
 167             " " + RECONCILE_TAG + " "\
 168             "AND " + self.init_query + "  ",
 169             (partner.id, tuple(move_state), tuple(self.account_ids)))
 170         res = self.cr.fetchall()
 171         self.init_bal_sum = res[0][2]
 172         return res
 173
 174     def _sum_debit_partner(self, partner):
 175         move_state = ['draft','posted']
 176         if self.target_move == 'posted':
 177             move_state = ['posted']
 178
 179         result_tmp = 0.0
 180         result_init = 0.0
 181         if self.reconcil:
 182             RECONCILE_TAG = " "
 183         else:
 184             RECONCILE_TAG = "AND reconcile_id IS NULL"
 185         if self.initial_balance:
 186             self.cr.execute(
 187                     "SELECT sum(debit) " \
 188                     "FROM account_move_line AS l, " \
 189                     "account_move AS m "
 190                     "WHERE l.partner_id = %s" \
 191                         "AND m.id = l.move_id " \
 192                         "AND m.state IN %s "
 193                         "AND account_id IN %s" \
 194                         " " + RECONCILE_TAG + " " \
 195                         "AND " + self.init_query + " ",
 196                     (partner.id, tuple(move_state), tuple(self.account_ids)))
 197             contemp = self.cr.fetchone()
 198             if contemp != None:
 199                 result_init = contemp[0] or 0.0
 200             else:
 201                 result_init = result_tmp + 0.0
 202
 203         self.cr.execute(
 204                 "SELECT sum(debit) " \
 205                 "FROM account_move_line AS l, " \
 206                 "account_move AS m "
 207                 "WHERE l.partner_id = %s " \
 208                     "AND m.id = l.move_id " \
 209                     "AND m.state IN %s "
 210                     "AND account_id IN %s" \
 211                     " " + RECONCILE_TAG + " " \
 212                     "AND " + self.query + " ",
 213                 (partner.id, tuple(move_state), tuple(self.account_ids),))
 214
 215         contemp = self.cr.fetchone()
 216         if contemp != None:
 217             result_tmp = contemp[0] or 0.0
 218         else:
 219             result_tmp = result_tmp + 0.0
 220
 221         return result_tmp  + result_init
 222
 223     def _sum_credit_partner(self, partner):
 224         move_state = ['draft','posted']
 225         if self.target_move == 'posted':
 226             move_state = ['posted']
 227
 228         result_tmp = 0.0
 229         result_init = 0.0
 230         if self.reconcil:
 231             RECONCILE_TAG = " "
 232         else:
 233             RECONCILE_TAG = "AND reconcile_id IS NULL"
 234         if self.initial_balance:
 235             self.cr.execute(
 236                     "SELECT sum(credit) " \
 237                     "FROM account_move_line AS l, " \
 238                     "account_move AS m  "
 239                     "WHERE l.partner_id = %s" \
 240                         "AND m.id = l.move_id " \
 241                         "AND m.state IN %s "
 242                         "AND account_id IN %s" \
 243                         " " + RECONCILE_TAG + " " \
 244                         "AND " + self.init_query + " ",
 245                     (partner.id, tuple(move_state), tuple(self.account_ids)))
 246             contemp = self.cr.fetchone()
 247             if contemp != None:
 248                 result_init = contemp[0] or 0.0
 249             else:
 250                 result_init = result_tmp + 0.0
 251
 252         self.cr.execute(
 253                 "SELECT sum(credit) " \
 254                 "FROM account_move_line AS l, " \
 255                 "account_move AS m "
 256                 "WHERE l.partner_id=%s " \
 257                     "AND m.id = l.move_id " \
 258                     "AND m.state IN %s "
 259                     "AND account_id IN %s" \
 260                     " " + RECONCILE_TAG + " " \
 261                     "AND " + self.query + " ",
 262                 (partner.id, tuple(move_state), tuple(self.account_ids),))
 263
 264         contemp = self.cr.fetchone()
 265         if contemp != None:
 266             result_tmp = contemp[0] or 0.0
 267         else:
 268             result_tmp = result_tmp + 0.0
 269         return result_tmp  + result_init
 270
 271     def _get_partners(self):
 272         # TODO: deprecated, to remove in trunk
 273         if self.result_selection == 'customer':
 274             return _('Receivable Accounts')
 275         elif self.result_selection == 'supplier':
 276             return _('Payable Accounts')
 277         elif self.result_selection == 'customer_supplier':
 278             return _('Receivable and Payable Accounts')
 279         return ''
 280
 281     def _sum_currency_amount_account(self, account, form):
 282         self._set_get_account_currency_code(account.id)
 283         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,))
 284         total = self.cr.fetchone()
 285         if self.account_currency:
 286             return_field = str(total[0]) + self.account_currency
 287             return return_field
 288         else:
 289             currency_total = self.tot_currency = 0.0
 290             return currency_total
 291
 292     def _display_initial_balance(self, data):
 293         if self.initial_balance:
 294             return True
 295         return False
 296
 297     def _display_currency(self, data):
 298         if self.amount_currency:
 299             return True
 300         return False
 301
 302
 303 class report_partnerledger(osv.AbstractModel):
 304     _name = 'report.account.report_partnerledger'
 305     _inherit = 'report.abstract_report'
 306     _template = 'account.report_partnerledger'
 307     _wrapped_report_class = third_party_ledger
 308
 309
 310 class report_partnerledgerother(osv.AbstractModel):
 311     _name = 'report.account.report_partnerledgerother'
 312     _inherit = 'report.abstract_report'
 313     _template = 'account.report_partnerledgerother'
 314     _wrapped_report_class = third_party_ledger
 315
 316 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: