addons/account/report/account_invoice_report.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 from openerp import tools
  23 import openerp.addons.decimal_precision as dp
  24 from openerp.osv import fields,osv
  25
  26 class account_invoice_report(osv.osv):
  27     _name = "account.invoice.report"
  28     _description = "Invoices Statistics"
  29     _auto = False
  30     _rec_name = 'date'
  31
  32     def _compute_amounts_in_user_currency(self, cr, uid, ids, field_names, args, context=None):
  33         """Compute the amounts in the currency of the user
  34         """
  35         if context is None:
  36             context={}
  37         currency_obj = self.pool.get('res.currency')
  38         currency_rate_obj = self.pool.get('res.currency.rate')
  39         user_currency_id = self.pool.get('res.users').browse(cr, uid, uid, context=context).company_id.currency_id.id
  40         currency_rate_id = currency_rate_obj.search(cr, uid, [('rate', '=', 1)], limit=1, context=context)[0]
  41         base_currency_id = currency_rate_obj.browse(cr, uid, currency_rate_id, context=context).currency_id.id
  42         res = {}
  43         ctx = context.copy()
  44         for item in self.browse(cr, uid, ids, context=context):
  45             ctx['date'] = item.date
  46             price_total = currency_obj.compute(cr, uid, base_currency_id, user_currency_id, item.price_total, context=ctx)
  47             price_average = currency_obj.compute(cr, uid, base_currency_id, user_currency_id, item.price_average, context=ctx)
  48             residual = currency_obj.compute(cr, uid, base_currency_id, user_currency_id, item.residual, context=ctx)
  49             res[item.id] = {
  50                 'user_currency_price_total': price_total,
  51                 'user_currency_price_average': price_average,
  52                 'user_currency_residual': residual,
  53             }
  54         return res
  55
  56     _columns = {
  57         'date': fields.date('Date', readonly=True),
  58         'product_id': fields.many2one('product.product', 'Product', readonly=True),
  59         'product_qty':fields.float('Product Quantity', readonly=True),
  60         'uom_name': fields.char('Reference Unit of Measure', size=128, readonly=True),
  61         'payment_term': fields.many2one('account.payment.term', 'Payment Term', readonly=True),
  62         'period_id': fields.many2one('account.period', 'Force Period', domain=[('state','<>','done')], readonly=True),
  63         'fiscal_position': fields.many2one('account.fiscal.position', 'Fiscal Position', readonly=True),
  64         'currency_id': fields.many2one('res.currency', 'Currency', readonly=True),
  65         'categ_id': fields.many2one('product.category','Product Category', readonly=True),
  66         'journal_id': fields.many2one('account.journal', 'Journal', readonly=True),
  67         'partner_id': fields.many2one('res.partner', 'Partner', readonly=True),
  68         'commercial_partner_id': fields.many2one('res.partner', 'Partner Company', help="Commercial Entity"),
  69         'company_id': fields.many2one('res.company', 'Company', readonly=True),
  70         'user_id': fields.many2one('res.users', 'Salesperson', readonly=True),
  71         'price_total': fields.float('Total Without Tax', readonly=True),
  72         'user_currency_price_total': fields.function(_compute_amounts_in_user_currency, string="Total Without Tax", type='float', digits_compute=dp.get_precision('Account'), multi="_compute_amounts"),
  73         'price_average': fields.float('Average Price', readonly=True, group_operator="avg"),
  74         'user_currency_price_average': fields.function(_compute_amounts_in_user_currency, string="Average Price", type='float', digits_compute=dp.get_precision('Account'), multi="_compute_amounts"),
  75         'currency_rate': fields.float('Currency Rate', readonly=True),
  76         'nbr': fields.integer('# of Invoices', readonly=True),  # TDE FIXME master: rename into nbr_lines
  77         'type': fields.selection([
  78             ('out_invoice','Customer Invoice'),
  79             ('in_invoice','Supplier Invoice'),
  80             ('out_refund','Customer Refund'),
  81             ('in_refund','Supplier Refund'),
  82             ],'Type', readonly=True),
  83         'state': fields.selection([
  84             ('draft','Draft'),
  85             ('proforma','Pro-forma'),
  86             ('proforma2','Pro-forma'),
  87             ('open','Open'),
  88             ('paid','Done'),
  89             ('cancel','Cancelled')
  90             ], 'Invoice Status', readonly=True),
  91         'date_due': fields.date('Due Date', readonly=True),
  92         'account_id': fields.many2one('account.account', 'Account',readonly=True),
  93         'account_line_id': fields.many2one('account.account', 'Account Line',readonly=True),
  94         'partner_bank_id': fields.many2one('res.partner.bank', 'Bank Account',readonly=True),
  95         'residual': fields.float('Total Residual', readonly=True),
  96         'user_currency_residual': fields.function(_compute_amounts_in_user_currency, string="Total Residual", type='float', digits_compute=dp.get_precision('Account'), multi="_compute_amounts"),
  97         'country_id': fields.many2one('res.country', 'Country of the Partner Company'),
  98         'account_analytic_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
  99     }
 100     _order = 'date desc'
 101
 102     _depends = {
 103         'account.invoice': [
 104             'account_id', 'amount_total', 'commercial_partner_id', 'company_id',
 105             'currency_id', 'date_due', 'date_invoice', 'fiscal_position',
 106             'journal_id', 'partner_bank_id', 'partner_id', 'payment_term',
 107             'period_id', 'residual', 'state', 'type', 'user_id',
 108         ],
 109         'account.invoice.line': [
 110             'account_id', 'invoice_id', 'price_subtotal', 'product_id',
 111             'quantity', 'uos_id', 'account_analytic_id',
 112         ],
 113         'product.product': ['product_tmpl_id'],
 114         'product.template': ['categ_id'],
 115         'product.uom': ['category_id', 'factor', 'name', 'uom_type'],
 116         'res.currency.rate': ['currency_id', 'name'],
 117         'res.partner': ['country_id'],
 118     }
 119
 120     def _select(self):
 121         select_str = """
 122             SELECT sub.id, sub.date, sub.product_id, sub.partner_id, sub.country_id, sub.account_analytic_id,
 123                 sub.payment_term, sub.period_id, sub.uom_name, sub.currency_id, sub.journal_id,
 124                 sub.fiscal_position, sub.user_id, sub.company_id, sub.nbr, sub.type, sub.state,
 125                 sub.categ_id, sub.date_due, sub.account_id, sub.account_line_id, sub.partner_bank_id,
 126                 sub.product_qty, sub.price_total / cr.rate as price_total, sub.price_average /cr.rate as price_average,
 127                 cr.rate as currency_rate, sub.residual / cr.rate as residual, sub.commercial_partner_id as commercial_partner_id
 128         """
 129         return select_str
 130
 131     def _sub_select(self):
 132         select_str = """
 133                 SELECT min(ail.id) AS id,
 134                     ai.date_invoice AS date,
 135                     ail.product_id, ai.partner_id, ai.payment_term, ai.period_id, ail.account_analytic_id,
 136                     CASE
 137                      WHEN u.uom_type::text <> 'reference'::text
 138                         THEN ( SELECT product_uom.name
 139                                FROM product_uom
 140                                WHERE product_uom.uom_type::text = 'reference'::text
 141                                 AND product_uom.active
 142                                 AND product_uom.category_id = u.category_id LIMIT 1)
 143                         ELSE u.name
 144                     END AS uom_name,
 145                     ai.currency_id, ai.journal_id, ai.fiscal_position, ai.user_id, ai.company_id,
 146                     count(ail.*) AS nbr,
 147                     ai.type, ai.state, pt.categ_id, ai.date_due, ai.account_id, ail.account_id AS account_line_id,
 148                     ai.partner_bank_id,
 149                     SUM(CASE
 150                          WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 151                             THEN (- ail.quantity) / u.factor
 152                             ELSE ail.quantity / u.factor
 153                         END) AS product_qty,
 154                     SUM(CASE
 155                          WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 156                             THEN - ail.price_subtotal
 157                             ELSE ail.price_subtotal
 158                         END) AS price_total,
 159                     CASE
 160                      WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 161                         THEN SUM(- ail.price_subtotal)
 162                         ELSE SUM(ail.price_subtotal)
 163                     END / CASE
 164                            WHEN SUM(ail.quantity / u.factor) <> 0::numeric
 165                                THEN CASE
 166                                      WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 167                                         THEN SUM((- ail.quantity) / u.factor)
 168                                         ELSE SUM(ail.quantity / u.factor)
 169                                     END
 170                                ELSE 1::numeric
 171                           END AS price_average,
 172                     CASE
 173                      WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 174                         THEN - ai.residual
 175                         ELSE ai.residual
 176                     END / CASE
 177                            WHEN (( SELECT count(l.id) AS count
 178                                    FROM account_invoice_line l
 179                                    LEFT JOIN account_invoice a ON a.id = l.invoice_id
 180                                    WHERE a.id = ai.id)) <> 0
 181                                THEN ( SELECT count(l.id) AS count
 182                                       FROM account_invoice_line l
 183                                       LEFT JOIN account_invoice a ON a.id = l.invoice_id
 184                                       WHERE a.id = ai.id)
 185                                ELSE 1::bigint
 186                           END::numeric AS residual,
 187                     ai.commercial_partner_id as commercial_partner_id,
 188                     partner.country_id
 189         """
 190         return select_str
 191
 192     def _from(self):
 193         from_str = """
 194                 FROM account_invoice_line ail
 195                 JOIN account_invoice ai ON ai.id = ail.invoice_id
 196                 JOIN res_partner partner ON ai.commercial_partner_id = partner.id
 197                 LEFT JOIN product_product pr ON pr.id = ail.product_id
 198                 left JOIN product_template pt ON pt.id = pr.product_tmpl_id
 199                 LEFT JOIN product_uom u ON u.id = ail.uos_id
 200         """
 201         return from_str
 202
 203     def _group_by(self):
 204         group_by_str = """
 205                 GROUP BY ail.product_id, ail.account_analytic_id, ai.date_invoice, ai.id,
 206                     ai.partner_id, ai.payment_term, ai.period_id, u.name, ai.currency_id, ai.journal_id,
 207                     ai.fiscal_position, ai.user_id, ai.company_id, ai.type, ai.state, pt.categ_id,
 208                     ai.date_due, ai.account_id, ail.account_id, ai.partner_bank_id, ai.residual,
 209                     ai.amount_total, u.uom_type, u.category_id, ai.commercial_partner_id, partner.country_id
 210         """
 211         return group_by_str
 212
 213     def init(self, cr):
 214         # self._table = account_invoice_report
 215         tools.drop_view_if_exists(cr, self._table)
 216         cr.execute("""CREATE or REPLACE VIEW %s as (
 217             %s
 218             FROM (
 219                 %s %s %s
 220             ) AS sub
 221             JOIN res_currency_rate cr ON (cr.currency_id = sub.currency_id)
 222             WHERE
 223                 cr.id IN (SELECT id
 224                           FROM res_currency_rate cr2
 225                           WHERE (cr2.currency_id = sub.currency_id)
 226                               AND ((sub.date IS NOT NULL AND cr2.name <= sub.date)
 227                                     OR (sub.date IS NULL AND cr2.name <= NOW()))
 228                           ORDER BY name DESC LIMIT 1)
 229         )""" % (
 230                     self._table,
 231                     self._select(), self._sub_select(), self._from(), self._group_by()))
 232
 233
 234 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: