addons/report_intrastat/report_intrastat.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
   6 #    $Id$
   7 #
   8 #    This program is free software: you can redistribute it and/or modify
   9 #    it under the terms of the GNU General Public License as published by
  10 #    the Free Software Foundation, either version 3 of the License, or
  11 #    (at your option) any later version.
  12 #
  13 #    This program is distributed in the hope that it will be useful,
  14 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  15 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16 #    GNU General Public License for more details.
  17 #
  18 #    You should have received a copy of the GNU General Public License
  19 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  20 #
  21 ##############################################################################
  22
  23 from osv import osv, fields
  24 from tools.sql import drop_view_if_exists
  25
  26
  27 class res_country(osv.osv):
  28     _name = 'res.country'
  29     _inherit = 'res.country'
  30     _columns = {
  31         'intrastat': fields.boolean('Intrastat member'),
  32     }
  33     _defaults = {
  34         'intrastat': lambda *a: False,
  35     }
  36 res_country()
  37
  38 class report_intrastat_code(osv.osv):
  39     _name = "report.intrastat.code"
  40     _description = "Intrastat code"
  41     _columns = {
  42         'name': fields.char('Intrastat Code', size=16),
  43         'description': fields.char('Description', size=64),
  44     }
  45 report_intrastat_code()
  46
  47 class product_template(osv.osv):
  48     _name = "product.template"
  49     _inherit = "product.template"
  50     _columns = {
  51         'intrastat_id': fields.many2one('report.intrastat.code', 'Intrastat code'),
  52     }
  53 product_template()
  54
  55 class report_intrastat(osv.osv):
  56     _name = "report.intrastat"
  57     _description = "Intrastat report"
  58     _auto = False
  59     _columns = {
  60         'name': fields.many2one('account.period', 'Period', readonly=True,select=True),
  61         'supply_units':fields.float('Supply Units', readonly=True),
  62         'ref':fields.char('Origin',size=64, readonly=True),
  63         'code': fields.char('Country code', size="2", readonly=True),
  64         'intrastat_id': fields.many2one('report.intrastat.code', 'Intrastat code', readonly=True),
  65         'weight': fields.float('Weight', readonly=True),
  66         'value': fields.float('Value', readonly=True),
  67         'type': fields.selection([('import', 'Import'), ('export', 'Export')], 'Type'),
  68         'currency_id': fields.many2one('res.currency', "Currency", readonly=True),
  69     }
  70     def init(self, cr):
  71         drop_view_if_exists(cr, 'report_intrastat')
  72         cr.execute("""
  73             create or replace view report_intrastat as (
  74                 select
  75                     inv.period_id as name,
  76                     min(inv_line.id) as id,
  77                     intrastat.id as intrastat_id,
  78                     upper(inv_country.code) as code,
  79                     sum(case when inv_line.price_unit is not null
  80                             then inv_line.price_unit * inv_line.quantity
  81                             else 0
  82                         end) as value,
  83                     sum(
  84                         case when uom.category_id != puom.category_id then pt.weight_net * inv_line.quantity
  85                         else
  86                             case when uom.factor_inv_data > 0
  87                                 then
  88                                     pt.weight_net * inv_line.quantity * uom.factor_inv_data
  89                                 else
  90                                     pt.weight_net * inv_line.quantity / uom.factor
  91                             end
  92                         end
  93                     ) as weight,
  94                     sum(
  95                         case when uom.category_id != puom.category_id then inv_line.quantity
  96                         else
  97                             case when uom.factor_inv_data > 0
  98                                 then
  99                                     inv_line.quantity * uom.factor_inv_data
 100                                 else
 101                                     inv_line.quantity / uom.factor
 102                             end
 103                         end
 104                     ) as supply_units,
 105
 106                     inv.currency_id as currency_id,
 107                     inv.number as ref,
 108                     case when inv.type in ('out_invoice','in_refund')
 109                         then 'export'
 110                         else 'import'
 111                         end as type
 112                 from
 113                     account_invoice inv
 114                     left join account_invoice_line inv_line on inv_line.invoice_id=inv.id
 115                     left join (product_template pt
 116                         left join product_product pp on (pp.product_tmpl_id = pt.id))
 117                     on (inv_line.product_id = pt.id)
 118                     left join product_uom uom on uom.id=inv_line.uos_id
 119                     left join product_uom puom on puom.id = pt.uom_id
 120                     left join report_intrastat_code intrastat on pt.intrastat_id = intrastat.id
 121                     left join (res_partner_address inv_address
 122                         left join res_country inv_country on (inv_country.id = inv_address.country_id))
 123                     on (inv_address.id = inv.address_invoice_id)
 124
 125                 where
 126                     inv.state in ('open','paid')
 127                     and inv_line.product_id is not null
 128                     and inv_country.intrastat=true
 129                 group by inv.period_id,intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number,  inv.currency_id
 130             )""")
 131 report_intrastat()
 132
 133
 134 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 135