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 import tools
  23 from osv import fields,osv
  24
  25 class account_invoice_report(osv.osv):
  26     _name = "account.invoice.report"
  27     _description = "Invoices Statistics"
  28     _auto = False
  29     _rec_name = 'date'
  30     _columns = {
  31         'date': fields.date('Date', readonly=True),
  32         'year': fields.char('Year', size=4, readonly=True),
  33         'day': fields.char('Day', size=128, readonly=True),
  34         'month': fields.selection([('01','January'), ('02','February'), ('03','March'), ('04','April'),
  35             ('05','May'), ('06','June'), ('07','July'), ('08','August'), ('09','September'),
  36             ('10','October'), ('11','November'), ('12','December')], 'Month', readonly=True),
  37         'product_id': fields.many2one('product.product', 'Product', readonly=True),
  38         'product_qty':fields.float('Qty', readonly=True),
  39         'uom_name': fields.char('Reference Unit of Measure', size=128, readonly=True),
  40         'payment_term': fields.many2one('account.payment.term', 'Payment Term', readonly=True),
  41         'period_id': fields.many2one('account.period', 'Force Period', domain=[('state','<>','done')], readonly=True),
  42         'fiscal_position': fields.many2one('account.fiscal.position', 'Fiscal Position', readonly=True),
  43         'currency_id': fields.many2one('res.currency', 'Currency', readonly=True),
  44         'categ_id': fields.many2one('product.category','Category of Product', readonly=True),
  45         'journal_id': fields.many2one('account.journal', 'Journal', readonly=True),
  46         'partner_id': fields.many2one('res.partner', 'Partner', readonly=True),
  47         'company_id': fields.many2one('res.company', 'Company', readonly=True),
  48         'user_id': fields.many2one('res.users', 'Salesperson', readonly=True),
  49         'price_total': fields.float('Total Without Tax', readonly=True),
  50         'price_average': fields.float('Average Price', readonly=True, group_operator="avg"),
  51         'currency_rate': fields.float('Currency Rate', readonly=True),
  52         'nbr':fields.integer('# of Lines', readonly=True),
  53         'type': fields.selection([
  54             ('out_invoice','Customer Invoice'),
  55             ('in_invoice','Supplier Invoice'),
  56             ('out_refund','Customer Refund'),
  57             ('in_refund','Supplier Refund'),
  58             ],'Type', readonly=True),
  59         'state': fields.selection([
  60             ('draft','Draft'),
  61             ('proforma','Pro-forma'),
  62             ('proforma2','Pro-forma'),
  63             ('open','Open'),
  64             ('paid','Done'),
  65             ('cancel','Cancelled')
  66             ], 'Invoice State', readonly=True),
  67         'date_due': fields.date('Due Date', readonly=True),
  68         'account_id': fields.many2one('account.account', 'Account',readonly=True),
  69         'account_line_id': fields.many2one('account.account', 'Account Line',readonly=True),
  70         'partner_bank_id': fields.many2one('res.partner.bank', 'Bank Account',readonly=True),
  71         'residual': fields.float('Total Residual', readonly=True),
  72         'delay_to_pay': fields.float('Avg. Delay To Pay', readonly=True, group_operator="avg"),
  73         'due_delay': fields.float('Avg. Due Delay', readonly=True, group_operator="avg"),
  74     }
  75     _order = 'date desc'
  76     def init(self, cr):
  77         tools.drop_view_if_exists(cr, 'account_invoice_report')
  78         cr.execute("""
  79             create or replace view account_invoice_report as (
  80                  select min(ail.id) as id,
  81                     ai.date_invoice as date,
  82                     to_char(ai.date_invoice, 'YYYY') as year,
  83                     to_char(ai.date_invoice, 'MM') as month,
  84                     to_char(ai.date_invoice, 'YYYY-MM-DD') as day,
  85                     ail.product_id,
  86                     ai.partner_id as partner_id,
  87                     ai.payment_term as payment_term,
  88                     ai.period_id as period_id,
  89                     (case when u.uom_type not in ('reference') then
  90                         (select name from product_uom where uom_type='reference' and active and category_id=u.category_id LIMIT 1)
  91                     else
  92                         u.name
  93                     end) as uom_name,
  94                     ai.currency_id as currency_id,
  95                     ai.journal_id as journal_id,
  96                     ai.fiscal_position as fiscal_position,
  97                     ai.user_id as user_id,
  98                     ai.company_id as company_id,
  99                     count(ail.*) as nbr,
 100                     ai.type as type,
 101                     ai.state,
 102                     pt.categ_id,
 103                     ai.date_due as date_due,
 104                     ai.account_id as account_id,
 105                     ail.account_id as account_line_id,
 106                     ai.partner_bank_id as partner_bank_id,
 107                     sum(case when ai.type in ('out_refund','in_invoice') then
 108                          -ail.quantity / u.factor
 109                         else
 110                          ail.quantity / u.factor
 111                         end) as product_qty,
 112
 113                     sum(case when ai.type in ('out_refund','in_invoice') then
 114                          -ail.price_subtotal
 115                         else
 116                           ail.price_subtotal
 117                         end) / cr.rate as price_total,
 118
 119                     (case when ai.type in ('out_refund','in_invoice') then
 120                       sum(-ail.price_subtotal)
 121                     else
 122                       sum(ail.price_subtotal)
 123                     end) / (CASE WHEN sum(ail.quantity/u.factor) <> 0
 124                        THEN
 125                          (case when ai.type in ('out_refund','in_invoice')
 126                           then sum(-ail.quantity/u.factor)
 127                           else sum(ail.quantity/u.factor) end)
 128                        ELSE 1
 129                        END)
 130                      / cr.rate as price_average,
 131
 132                     cr.rate as currency_rate,
 133                     sum((select extract(epoch from avg(date_trunc('day',aml.date_created)-date_trunc('day',l.create_date)))/(24*60*60)::decimal(16,2)
 134                         from account_move_line as aml
 135                         left join account_invoice as a ON (a.move_id=aml.move_id)
 136                         left join account_invoice_line as l ON (a.id=l.invoice_id)
 137                         where a.id=ai.id)) as delay_to_pay,
 138                     sum((select extract(epoch from avg(date_trunc('day',a.date_due)-date_trunc('day',a.date_invoice)))/(24*60*60)::decimal(16,2)
 139                         from account_move_line as aml
 140                         left join account_invoice as a ON (a.move_id=aml.move_id)
 141                         left join account_invoice_line as l ON (a.id=l.invoice_id)
 142                         where a.id=ai.id)) as due_delay,
 143                     (case when ai.type in ('out_refund','in_invoice') then
 144                       -ai.residual
 145                     else
 146                       ai.residual
 147                     end)/ (CASE WHEN
 148                         (select count(l.id) from account_invoice_line as l
 149                          left join account_invoice as a ON (a.id=l.invoice_id)
 150                          where a.id=ai.id) <> 0
 151                        THEN
 152                         (select count(l.id) from account_invoice_line as l
 153                          left join account_invoice as a ON (a.id=l.invoice_id)
 154                          where a.id=ai.id)
 155                        ELSE 1
 156                        END) / cr.rate as residual
 157                 from account_invoice_line as ail
 158                 left join account_invoice as ai ON (ai.id=ail.invoice_id)
 159                 left join product_product pr on (pr.id=ail.product_id)
 160                 left join product_template pt on (pt.id=pr.product_tmpl_id)
 161                 left join product_uom u on (u.id=ail.uos_id),
 162                 res_currency_rate cr
 163                 where cr.id in (select id from res_currency_rate cr2  where (cr2.currency_id = ai.currency_id)
 164                 and ((ai.date_invoice is not null and cr.name <= ai.date_invoice) or (ai.date_invoice is null and cr.name <= NOW())) limit 1)
 165                 group by ail.product_id,
 166                     ai.date_invoice,
 167                     ai.id,
 168                     cr.rate,
 169                     to_char(ai.date_invoice, 'YYYY'),
 170                     to_char(ai.date_invoice, 'MM'),
 171                     to_char(ai.date_invoice, 'YYYY-MM-DD'),
 172                     ai.partner_id,
 173                     ai.payment_term,
 174                     ai.period_id,
 175                     u.name,
 176                     ai.currency_id,
 177                     ai.journal_id,
 178                     ai.fiscal_position,
 179                     ai.user_id,
 180                     ai.company_id,
 181                     ai.type,
 182                     ai.state,
 183                     pt.categ_id,
 184                     ai.date_due,
 185                     ai.account_id,
 186                     ail.account_id,
 187                     ai.partner_bank_id,
 188                     ai.residual,
 189                     ai.amount_total,
 190                     u.uom_type,
 191                     u.category_id
 192             )
 193         """)
 194
 195 account_invoice_report()
 196
 197 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: