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         'payment_term': fields.many2one('account.payment.term', 'Payment Term',readonly=True),
  40         'period_id': fields.many2one('account.period', 'Force Period', domain=[('state','<>','done')],readonly=True),
  41         'fiscal_position': fields.many2one('account.fiscal.position', 'Fiscal Position',readonly=True),
  42         'currency_id': fields.many2one('res.currency', 'Currency', readonly=True),
  43         'journal_id': fields.many2one('account.journal', 'Journal',readonly=True),
  44         'partner_id':fields.many2one('res.partner', 'Partner', readonly=True),
  45         'company_id':fields.many2one('res.company', 'Company', readonly=True),
  46         'user_id':fields.many2one('res.users', 'Salesman', readonly=True),
  47         'price_total':fields.float('Total Price', readonly=True),
  48         'price_average':fields.float('Average Price', readonly=True),
  49         'nbr':fields.integer('# of Lines', readonly=True),
  50         'type': fields.selection([
  51             ('out_invoice','Customer Invoice'),
  52             ('in_invoice','Supplier Invoice'),
  53             ('out_refund','Customer Refund'),
  54             ('in_refund','Supplier Refund'),
  55             ],'Type', readonly=True),
  56         'state': fields.selection([
  57             ('draft','Draft'),
  58             ('proforma','Pro-forma'),
  59             ('proforma2','Pro-forma'),
  60             ('open','Open'),
  61             ('paid','Done'),
  62             ('cancel','Cancelled')
  63             ], 'Order State', readonly=True),
  64         'date_due': fields.date('Due Date', readonly=True),
  65         'address_contact_id': fields.many2one('res.partner.address', 'Contact Address Name', readonly=True),
  66         'address_invoice_id': fields.many2one('res.partner.address', 'Invoice Address Name', readonly=True),
  67         'account_id': fields.many2one('account.account', 'Account',readonly=True),
  68         'partner_bank': fields.many2one('res.partner.bank', 'Bank Account',readonly=True),
  69         'residual':fields.float('Total Residual', readonly=True),
  70         'delay_to_pay':fields.float('Avg. Delay To Pay', readonly=True, group_operator="avg"),
  71     }
  72     _order = 'date desc'
  73     def init(self, cr):
  74         tools.drop_view_if_exists(cr, 'account_invoice_report')
  75         cr.execute("""
  76             create or replace view account_invoice_report as (
  77                  select
  78                      min(l.id) as id,
  79                      s.date_invoice as date,
  80                      to_char(s.date_invoice, 'YYYY') as year,
  81                      to_char(s.date_invoice, 'MM') as month,
  82                      to_char(s.date_invoice, 'YYYY-MM-DD') as day,
  83                      l.product_id as product_id,
  84                      sum(l.quantity * u.factor) as product_qty,
  85                      s.partner_id as partner_id,
  86                      s.payment_term as payment_term,
  87                      s.period_id as period_id,
  88                      s.currency_id as currency_id,
  89                      s.journal_id as journal_id,
  90                      s.fiscal_position as fiscal_position,
  91                      s.user_id as user_id,
  92                      s.company_id as company_id,
  93                      sum(l.quantity*l.price_unit) as price_total,
  94                      (sum(l.quantity*l.price_unit)/sum(l.quantity * u.factor))::decimal(16,2) as price_average,
  95                      count(*) as nbr,
  96                      s.type as type,
  97                      s.state,
  98                      s.date_due as date_due,
  99                      s.address_contact_id as address_contact_id,
 100                      s.address_invoice_id as address_invoice_id,
 101                      s.account_id as account_id,
 102                      s.partner_bank as partner_bank,
 103                      sum(s.residual) as residual,
 104                      case when s.state != 'paid' then null else
 105                             extract(epoch from avg(am.date_created-l.create_date))/(24*60*60)::decimal(16,2)
 106                      end as delay_to_pay
 107                  from
 108                  account_invoice_line l
 109                  left join
 110                      account_invoice s on (s.id=l.invoice_id)
 111                      left join product_uom u on (u.id=l.uos_id),
 112                  account_move_line am left join account_invoice i on (i.move_id=am.move_id)
 113                  where
 114                         am.account_id=i.account_id
 115                  group by
 116                      s.type,
 117                      s.date_invoice,
 118                      s.partner_id,
 119                      l.product_id,
 120                      l.uos_id,
 121                      s.user_id,
 122                      s.state,
 123                      s.company_id,
 124                      s.payment_term,
 125                      s.period_id,
 126                      s.fiscal_position,
 127                      s.currency_id,
 128                      s.journal_id,
 129                      s.date_due,
 130                      s.address_contact_id,
 131                      s.address_invoice_id,
 132                      s.account_id,
 133                      s.partner_bank
 134             )
 135         """)
 136 account_invoice_report()