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
  37 res_country()
  38
  39
  40 class report_intrastat_code(osv.osv):
  41     _name = "report.intrastat.code"
  42     _description = "Intrastat code"
  43     _columns = {
  44         'name': fields.char('Intrastat Code', size=16),
  45         'description': fields.char('Description', size=64),
  46     }
  47
  48 report_intrastat_code()
  49
  50
  51 class product_template(osv.osv):
  52     _name = "product.template"
  53     _inherit = "product.template"
  54     _columns = {
  55         'intrastat_id': fields.many2one('report.intrastat.code', 'Intrastat code'),
  56     }
  57
  58 product_template()
  59
  60 class report_intrastat(osv.osv):
  61     _name = "report.intrastat"
  62     _description = "Intrastat report"
  63     _auto = False
  64     _columns = {
  65         'name': fields.many2one('account.period', 'Period', readonly=True, select=True),
  66         'supply_units':fields.float('Supply Units', readonly=True),
  67         'ref':fields.char('Origin',size=64, readonly=True),
  68         'code': fields.char('Country code', size="2", readonly=True),
  69         'intrastat_id': fields.many2one('report.intrastat.code', 'Intrastat code', readonly=True),
  70         'weight': fields.float('Weight', readonly=True),
  71         'value': fields.float('Value', readonly=True),
  72         'type': fields.selection([('import', 'Import'), ('export', 'Export')], 'Type'),
  73         'currency_id': fields.many2one('res.currency', "Currency", readonly=True),
  74     }
  75     def init(self, cr):
  76         drop_view_if_exists(cr, 'report_intrastat')
  77         cr.execute("""
  78             create or replace view report_intrastat as (
  79                 select
  80                     inv.period_id as name,
  81                     min(inv_line.id) as id,
  82                     intrastat.id as intrastat_id,
  83                     upper(inv_country.code) as code,
  84                     sum(case when inv_line.price_unit is not null
  85                             then inv_line.price_unit * inv_line.quantity
  86                             else 0
  87                         end) as value,
  88                     sum(
  89                         case when uom.category_id != puom.category_id then pt.weight_net * inv_line.quantity
  90                         else
  91                             case when uom.factor_inv_data > 0
  92                                 then
  93                                     pt.weight_net * inv_line.quantity * uom.factor_inv_data
  94                                 else
  95                                     pt.weight_net * inv_line.quantity / uom.factor
  96                             end
  97                         end
  98                     ) as weight,
  99                     sum(
 100                         case when uom.category_id != puom.category_id then inv_line.quantity
 101                         else
 102                             case when uom.factor_inv_data > 0
 103                                 then
 104                                     inv_line.quantity * uom.factor_inv_data
 105                                 else
 106                                     inv_line.quantity / uom.factor
 107                             end
 108                         end
 109                     ) as supply_units,
 110
 111                     inv.currency_id as currency_id,
 112                     inv.number as ref,
 113                     case when inv.type in ('out_invoice','in_refund')
 114                         then 'export'
 115                         else 'import'
 116                         end as type
 117                 from
 118                     account_invoice inv
 119                     left join account_invoice_line inv_line on inv_line.invoice_id=inv.id
 120                     left join (product_template pt
 121                         left join product_product pp on (pp.product_tmpl_id = pt.id))
 122                     on (inv_line.product_id = pt.id)
 123                     left join product_uom uom on uom.id=inv_line.uos_id
 124                     left join product_uom puom on puom.id = pt.uom_id
 125                     left join report_intrastat_code intrastat on pt.intrastat_id = intrastat.id
 126                     left join (res_partner_address inv_address
 127                         left join res_country inv_country on (inv_country.id = inv_address.country_id))
 128                     on (inv_address.id = inv.address_invoice_id)
 129
 130                 where
 131                     inv.state in ('open','paid')
 132                     and inv_line.product_id is not null
 133                     and inv_country.intrastat=true
 134                 group by inv.period_id,intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number,  inv.currency_id
 135             )""")
 136
 137 report_intrastat()
 138