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         'year': fields.char('Year', size=4, readonly=True),
  59         'day': fields.char('Day', size=128, readonly=True),
  60         'month': fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'),
  61             ('05','May'), ('06','June'), ('07','July'), ('08','August'), ('09','September'),
  62             ('10','October'), ('11','November'), ('12','December')], 'Month', readonly=True),
  63         'product_id': fields.many2one('product.product', 'Product', readonly=True),
  64         'product_qty':fields.float('Qty', readonly=True),
  65         'uom_name': fields.char('Reference Unit of Measure', size=128, readonly=True),
  66         'payment_term': fields.many2one('account.payment.term', 'Payment Terms', readonly=True),
  67         'period_id': fields.many2one('account.period', 'Force Period', domain=[('state','<>','done')], readonly=True),
  68         'fiscal_position': fields.many2one('account.fiscal.position', 'Fiscal Position', readonly=True),
  69         'currency_id': fields.many2one('res.currency', 'Currency', readonly=True),
  70         'categ_id': fields.many2one('product.category','Category of Product', readonly=True),
  71         'journal_id': fields.many2one('account.journal', 'Journal', readonly=True),
  72         'partner_id': fields.many2one('res.partner', 'Partner', readonly=True),
  73         'company_id': fields.many2one('res.company', 'Company', readonly=True),
  74         'user_id': fields.many2one('res.users', 'Salesperson', readonly=True),
  75         'price_total': fields.float('Total Without Tax', readonly=True),
  76         '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"),
  77         'price_average': fields.float('Average Price', readonly=True, group_operator="avg"),
  78         '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"),
  79         'currency_rate': fields.float('Currency Rate', readonly=True),
  80         'nbr':fields.integer('# of Lines', readonly=True),
  81         'type': fields.selection([
  82             ('out_invoice','Customer Invoice'),
  83             ('in_invoice','Supplier Invoice'),
  84             ('out_refund','Customer Refund'),
  85             ('in_refund','Supplier Refund'),
  86             ],'Type', readonly=True),
  87         'state': fields.selection([
  88             ('draft','Draft'),
  89             ('proforma','Pro-forma'),
  90             ('proforma2','Pro-forma'),
  91             ('open','Open'),
  92             ('paid','Done'),
  93             ('cancel','Cancelled')
  94             ], 'Invoice Status', readonly=True),
  95         'date_due': fields.date('Due Date', readonly=True),
  96         'account_id': fields.many2one('account.account', 'Account',readonly=True),
  97         'account_line_id': fields.many2one('account.account', 'Account Line',readonly=True),
  98         'partner_bank_id': fields.many2one('res.partner.bank', 'Bank Account',readonly=True),
  99         'residual': fields.float('Total Residual', readonly=True),
 100         'user_currency_residual': fields.function(_compute_amounts_in_user_currency, string="Total Residual", type='float', digits_compute=dp.get_precision('Account'), multi="_compute_amounts"),
 101     }
 102     _order = 'date desc'
 103
 104     def _select(self):
 105         select_str = """
 106             SELECT sub.id, sub.date, sub.year, sub.month, sub.day, sub.product_id, sub.partner_id,
 107                 sub.payment_term, sub.period_id, sub.uom_name, sub.currency_id, sub.journal_id,
 108                 sub.fiscal_position, sub.user_id, sub.company_id, sub.nbr, sub.type, sub.state,
 109                 sub.categ_id, sub.date_due, sub.account_id, sub.account_line_id, sub.partner_bank_id,
 110                 sub.product_qty, sub.price_total / cr.rate as price_total, sub.price_average /cr.rate as price_average,
 111                 cr.rate as currency_rate, sub.residual / cr.rate as residual
 112         """
 113         return select_str
 114
 115     def _sub_select(self):
 116         select_str = """
 117                 SELECT min(ail.id) AS id,
 118                     ai.date_invoice AS date,
 119                     to_char(ai.date_invoice::timestamp with time zone, 'YYYY'::text) AS year,
 120                     to_char(ai.date_invoice::timestamp with time zone, 'MM'::text) AS month,
 121                     to_char(ai.date_invoice::timestamp with time zone, 'YYYY-MM-DD'::text) AS day,
 122                     ail.product_id, ai.partner_id, ai.payment_term, ai.period_id,
 123                     CASE
 124                      WHEN u.uom_type::text <> 'reference'::text
 125                         THEN ( SELECT product_uom.name
 126                                FROM product_uom
 127                                WHERE product_uom.uom_type::text = 'reference'::text
 128                                 AND product_uom.active
 129                                 AND product_uom.category_id = u.category_id LIMIT 1)
 130                         ELSE u.name
 131                     END AS uom_name,
 132                     ai.currency_id, ai.journal_id, ai.fiscal_position, ai.user_id, ai.company_id,
 133                     count(ail.*) AS nbr,
 134                     ai.type, ai.state, pt.categ_id, ai.date_due, ai.account_id, ail.account_id AS account_line_id,
 135                     ai.partner_bank_id,
 136                     SUM(CASE
 137                          WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 138                             THEN (- ail.quantity) / u.factor
 139                             ELSE ail.quantity / u.factor
 140                         END) AS product_qty,
 141                     SUM(CASE
 142                          WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 143                             THEN - ail.price_subtotal
 144                             ELSE ail.price_subtotal
 145                         END) AS price_total,
 146                     CASE
 147                      WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 148                         THEN SUM(- ail.price_subtotal)
 149                         ELSE SUM(ail.price_subtotal)
 150                     END / CASE
 151                            WHEN SUM(ail.quantity / u.factor) <> 0::numeric
 152                                THEN CASE
 153                                      WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 154                                         THEN SUM((- ail.quantity) / u.factor)
 155                                         ELSE SUM(ail.quantity / u.factor)
 156                                     END
 157                                ELSE 1::numeric
 158                           END AS price_average,
 159                     CASE
 160                      WHEN ai.type::text = ANY (ARRAY['out_refund'::character varying::text, 'in_invoice'::character varying::text])
 161                         THEN - ai.residual
 162                         ELSE ai.residual
 163                     END / CASE
 164                            WHEN (( SELECT count(l.id) AS count
 165                                    FROM account_invoice_line l
 166                                    LEFT JOIN account_invoice a ON a.id = l.invoice_id
 167                                    WHERE a.id = ai.id)) <> 0
 168                                THEN ( SELECT count(l.id) AS count
 169                                       FROM account_invoice_line l
 170                                       LEFT JOIN account_invoice a ON a.id = l.invoice_id
 171                                       WHERE a.id = ai.id)
 172                                ELSE 1::bigint
 173                           END::numeric AS residual
 174         """
 175         return select_str
 176
 177     def _from(self):
 178         from_str = """
 179                 FROM account_invoice_line ail
 180                 JOIN account_invoice ai ON ai.id = ail.invoice_id
 181                 LEFT JOIN product_product pr ON pr.id = ail.product_id
 182                 left JOIN product_template pt ON pt.id = pr.product_tmpl_id
 183                 LEFT JOIN product_uom u ON u.id = ail.uos_id
 184         """
 185         return from_str
 186
 187     def _group_by(self):
 188         group_by_str = """
 189                 GROUP BY ail.product_id, ai.date_invoice, ai.id,
 190                     to_char(ai.date_invoice::timestamp with time zone, 'YYYY'::text),
 191                     to_char(ai.date_invoice::timestamp with time zone, 'MM'::text),
 192                     to_char(ai.date_invoice::timestamp with time zone, 'YYYY-MM-DD'::text),
 193                     ai.partner_id, ai.payment_term, ai.period_id, u.name, ai.currency_id, ai.journal_id,
 194                     ai.fiscal_position, ai.user_id, ai.company_id, ai.type, ai.state, pt.categ_id,
 195                     ai.date_due, ai.account_id, ail.account_id, ai.partner_bank_id, ai.residual,
 196                     ai.amount_total, u.uom_type, u.category_id
 197         """
 198         return group_by_str
 199
 200     def init(self, cr):
 201         # self._table = account_invoice_report
 202         tools.drop_view_if_exists(cr, self._table)
 203         cr.execute("""CREATE or REPLACE VIEW %s as (
 204             %s
 205             FROM (
 206                 %s %s %s
 207             ) AS sub
 208             JOIN res_currency_rate cr ON (cr.currency_id = sub.currency_id)
 209             WHERE
 210                 cr.id IN (SELECT id
 211                           FROM res_currency_rate cr2
 212                           WHERE (cr2.currency_id = sub.currency_id)
 213                               AND ((sub.date IS NOT NULL AND cr.name <= sub.date)
 214                                     OR (sub.date IS NULL AND cr.name <= NOW()))
 215                           ORDER BY name DESC LIMIT 1)
 216         )""" % (
 217                     self._table,
 218                     self._select(), self._sub_select(), self._from(), self._group_by()))
 219
 220 account_invoice_report()
 221
 222 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: