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','Category of Product', 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     }
  99     _order = 'date desc'
 100
 101     _depends = {
 102         'account.invoice': [
 103             'account_id', 'amount_total', 'commercial_partner_id', 'company_id',
 104             'currency_id', 'date_due', 'date_invoice', 'fiscal_position',
 105             'journal_id', 'partner_bank_id', 'partner_id', 'payment_term',
 106             'period_id', 'residual', 'state', 'type', 'user_id',
 107         ],
 108         'account.invoice.line': [
 109             'account_id', 'invoice_id', 'price_subtotal', 'product_id',
 110             'quantity', 'uos_id',
 111         ],
 112         'product.product': ['product_tmpl_id'],
 113         'product.template': ['categ_id'],
 114         'product.uom': ['category_id', 'factor', 'name', 'uom_type'],
 115         'res.currency.rate': ['currency_id', 'name'],
 116         'res.partner': ['country_id'],
 117     }
 118
 119     def _select(self):
 120         select_str = """
 121             SELECT sub.id, sub.date, sub.product_id, sub.partner_id, sub.country_id,
 122                 sub.payment_term, sub.period_id, sub.uom_name, sub.currency_id, sub.journal_id,
 123                 sub.fiscal_position, sub.user_id, sub.company_id, sub.nbr, sub.type, sub.state,
 124                 sub.categ_id, sub.date_due, sub.account_id, sub.account_line_id, sub.partner_bank_id,
 125                 sub.product_qty, sub.price_total / cr.rate as price_total, sub.price_average /cr.rate as price_average,
 126                 cr.rate as currency_rate, sub.residual / cr.rate as residual, sub.commercial_partner_id as commercial_partner_id
 127         """
 128         return select_str
 129
 130     def _sub_select(self):
 131         select_str = """
 132                 SELECT min(ail.id) AS id,
 133                     ai.date_invoice AS date,
 134                     ail.product_id, ai.partner_id, ai.payment_term, ai.period_id,
 135                     CASE
 136                      WHEN u.uom_type::text <> 'reference'::text
 137                         THEN ( SELECT product_uom.name
 138                                FROM product_uom
 139                                WHERE product_uom.uom_type::text = 'reference'::text
 140                                 AND product_uom.active
 141                                 AND product_uom.category_id = u.category_id LIMIT 1)
 142                         ELSE u.name
 143                     END AS uom_name,
 144                     ai.currency_id, ai.journal_id, ai.fiscal_position, ai.user_id, ai.company_id,
 145                     count(ail.*) AS nbr,
 146                     ai.type, ai.state, pt.categ_id, ai.date_due, ai.account_id, ail.account_id AS account_line_id,
 147                     ai.partner_bank_id,
 148                     SUM(CASE
 149                          WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 150                             THEN (- ail.quantity) / u.factor
 151                             ELSE ail.quantity / u.factor
 152                         END) AS product_qty,
 153                     SUM(CASE
 154                          WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 155                             THEN - ail.price_subtotal
 156                             ELSE ail.price_subtotal
 157                         END) AS price_total,
 158                     CASE
 159                      WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 160                         THEN SUM(- ail.price_subtotal)
 161                         ELSE SUM(ail.price_subtotal)
 162                     END / CASE
 163                            WHEN SUM(ail.quantity / u.factor) <> 0::numeric
 164                                THEN CASE
 165                                      WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 166                                         THEN SUM((- ail.quantity) / u.factor)
 167                                         ELSE SUM(ail.quantity / u.factor)
 168                                     END
 169                                ELSE 1::numeric
 170                           END AS price_average,
 171                     CASE
 172                      WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 173                         THEN - ai.residual
 174                         ELSE ai.residual
 175                     END / CASE
 176                            WHEN (( SELECT count(l.id) AS count
 177                                    FROM account_invoice_line l
 178                                    LEFT JOIN account_invoice a ON a.id = l.invoice_id
 179                                    WHERE a.id = ai.id)) <> 0
 180                                THEN ( SELECT count(l.id) AS count
 181                                       FROM account_invoice_line l
 182                                       LEFT JOIN account_invoice a ON a.id = l.invoice_id
 183                                       WHERE a.id = ai.id)
 184                                ELSE 1::bigint
 185                           END::numeric AS residual,
 186                     ai.commercial_partner_id as commercial_partner_id,
 187                     partner.country_id
 188         """
 189         return select_str
 190
 191     def _from(self):
 192         from_str = """
 193                 FROM account_invoice_line ail
 194                 JOIN account_invoice ai ON ai.id = ail.invoice_id
 195                 JOIN res_partner partner ON ai.commercial_partner_id = partner.id
 196                 LEFT JOIN product_product pr ON pr.id = ail.product_id
 197                 left JOIN product_template pt ON pt.id = pr.product_tmpl_id
 198                 LEFT JOIN product_uom u ON u.id = ail.uos_id
 199         """
 200         return from_str
 201
 202     def _group_by(self):
 203         group_by_str = """
 204                 GROUP BY ail.product_id, ai.date_invoice, ai.id,
 205                     ai.partner_id, ai.payment_term, ai.period_id, u.name, ai.currency_id, ai.journal_id,
 206                     ai.fiscal_position, ai.user_id, ai.company_id, ai.type, ai.state, pt.categ_id,
 207                     ai.date_due, ai.account_id, ail.account_id, ai.partner_bank_id, ai.residual,
 208                     ai.amount_total, u.uom_type, u.category_id, ai.commercial_partner_id, partner.country_id
 209         """
 210         return group_by_str
 211
 212     def init(self, cr):
 213         # self._table = account_invoice_report
 214         tools.drop_view_if_exists(cr, self._table)
 215         cr.execute("""CREATE or REPLACE VIEW %s as (
 216             %s
 217             FROM (
 218                 %s %s %s
 219             ) AS sub
 220             JOIN res_currency_rate cr ON (cr.currency_id = sub.currency_id)
 221             WHERE
 222                 cr.id IN (SELECT id
 223                           FROM res_currency_rate cr2
 224                           WHERE (cr2.currency_id = sub.currency_id)
 225                               AND ((sub.date IS NOT NULL AND cr2.name <= sub.date)
 226                                     OR (sub.date IS NULL AND cr2.name <= NOW()))
 227                           ORDER BY name DESC LIMIT 1)
 228         )""" % (
 229                     self._table,
 230                     self._select(), self._sub_select(), self._from(), self._group_by()))
 231
 232
 233 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: