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 UoM', 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', 'Salesman', 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         'address_contact_id': fields.many2one('res.partner.address', 'Contact Address Name', readonly=True),
  69         'address_invoice_id': fields.many2one('res.partner.address', 'Invoice Address Name', readonly=True),
  70         'account_id': fields.many2one('account.account', 'Account',readonly=True),
  71         'account_line_id': fields.many2one('account.account', 'Account Line',readonly=True),
  72         'partner_bank_id': fields.many2one('res.partner.bank', 'Bank Account',readonly=True),
  73         'residual': fields.float('Total Residual', readonly=True),
  74         'delay_to_pay': fields.float('Avg. Delay To Pay', readonly=True, group_operator="avg"),
  75         'due_delay': fields.float('Avg. Due Delay', readonly=True, group_operator="avg"),
  76     }
  77     _order = 'date desc'
  78     def init(self, cr):
  79         tools.drop_view_if_exists(cr, 'account_invoice_report')
  80         cr.execute("""
  81             create or replace view account_invoice_report as (
  82                  select min(ail.id) as id,
  83                     ai.date_invoice as date,
  84                     to_char(ai.date_invoice, 'YYYY') as year,
  85                     to_char(ai.date_invoice, 'MM') as month,
  86                     to_char(ai.date_invoice, 'YYYY-MM-DD') as day,
  87                     ail.product_id,
  88                     ai.partner_id as partner_id,
  89                     ai.payment_term as payment_term,
  90                     ai.period_id as period_id,
  91                     (case when u.uom_type not in ('reference') then
  92                         (select name from product_uom where uom_type='reference' and active and category_id=u.category_id LIMIT 1)
  93                     else
  94                         u.name
  95                     end) as uom_name,
  96                     ai.currency_id as currency_id,
  97                     ai.journal_id as journal_id,
  98                     ai.fiscal_position as fiscal_position,
  99                     ai.user_id as user_id,
 100                     ai.company_id as company_id,
 101                     count(ail.*) as nbr,
 102                     ai.type as type,
 103                     ai.state,
 104                     pt.categ_id,
 105                     ai.date_due as date_due,
 106                     ai.address_contact_id as address_contact_id,
 107                     ai.address_invoice_id as address_invoice_id,
 108                     ai.account_id as account_id,
 109                     ail.account_id as account_line_id,
 110                     ai.partner_bank_id as partner_bank_id,
 111                     sum(case when ai.type in ('out_refund','in_invoice') then
 112                          -ail.quantity / u.factor
 113                         else
 114                          ail.quantity / u.factor
 115                         end) as product_qty,
 116
 117                     sum(case when ai.type in ('out_refund','in_invoice') then
 118                          -ail.price_subtotal
 119                         else
 120                           ail.price_subtotal
 121                         end) / cr.rate as price_total,
 122
 123                     (case when ai.type in ('out_refund','in_invoice') then
 124                       sum(-ail.price_subtotal)
 125                     else
 126                       sum(ail.price_subtotal)
 127                     end) / (CASE WHEN sum(ail.quantity/u.factor) <> 0
 128                        THEN
 129                          (case when ai.type in ('out_refund','in_invoice')
 130                           then sum(-ail.quantity/u.factor)
 131                           else sum(ail.quantity/u.factor) end)
 132                        ELSE 1
 133                        END)
 134                      / cr.rate as price_average,
 135
 136                     cr.rate as currency_rate,
 137                     sum((select extract(epoch from avg(date_trunc('day',aml.date_created)-date_trunc('day',l.create_date)))/(24*60*60)::decimal(16,2)
 138                         from account_move_line as aml
 139                         left join account_invoice as a ON (a.move_id=aml.move_id)
 140                         left join account_invoice_line as l ON (a.id=l.invoice_id)
 141                         where a.id=ai.id)) as delay_to_pay,
 142                     sum((select extract(epoch from avg(date_trunc('day',a.date_due)-date_trunc('day',a.date_invoice)))/(24*60*60)::decimal(16,2)
 143                         from account_move_line as aml
 144                         left join account_invoice as a ON (a.move_id=aml.move_id)
 145                         left join account_invoice_line as l ON (a.id=l.invoice_id)
 146                         where a.id=ai.id)) as due_delay,
 147                     (case when ai.type in ('out_refund','in_invoice') then
 148                       -ai.residual
 149                     else
 150                       ai.residual
 151                     end)/ (CASE WHEN
 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) <> 0
 155                        THEN
 156                         (select count(l.id) from account_invoice_line as l
 157                          left join account_invoice as a ON (a.id=l.invoice_id)
 158                          where a.id=ai.id)
 159                        ELSE 1
 160                        END) / cr.rate as residual
 161                 from account_invoice_line as ail
 162                 left join account_invoice as ai ON (ai.id=ail.invoice_id)
 163                 left join product_product pr on (pr.id=ail.product_id)
 164                 left join product_template pt on (pt.id=pr.product_tmpl_id)
 165                 left join product_uom u on (u.id=ail.uos_id),
 166                 res_currency_rate cr
 167                 where cr.id in (select id from res_currency_rate cr2  where (cr2.currency_id = ai.currency_id)
 168                 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)
 169                 group by ail.product_id,
 170                     ai.date_invoice,
 171                     ai.id,
 172                     cr.rate,
 173                     to_char(ai.date_invoice, 'YYYY'),
 174                     to_char(ai.date_invoice, 'MM'),
 175                     to_char(ai.date_invoice, 'YYYY-MM-DD'),
 176                     ai.partner_id,
 177                     ai.payment_term,
 178                     ai.period_id,
 179                     u.name,
 180                     ai.currency_id,
 181                     ai.journal_id,
 182                     ai.fiscal_position,
 183                     ai.user_id,
 184                     ai.company_id,
 185                     ai.type,
 186                     ai.state,
 187                     pt.categ_id,
 188                     ai.date_due,
 189                     ai.address_contact_id,
 190                     ai.address_invoice_id,
 191                     ai.account_id,
 192                     ail.account_id,
 193                     ai.partner_bank_id,
 194                     ai.residual,
 195                     ai.amount_total,
 196                     u.uom_type,
 197                     u.category_id
 198             )
 199         """)
 200
 201 account_invoice_report()
 202
 203 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: