1 # -*- encoding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
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.
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.
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/>.
21 ##############################################################################
23 from osv import osv, fields
25 class res_country(osv.osv):
27 _inherit = 'res.country'
29 'intrastat': fields.boolean('Intrastat member'),
32 'intrastat': lambda *a: False,
36 class report_intrastat_code(osv.osv):
37 _name = "report.intrastat.code"
38 _description = "Intrastat code"
40 'name': fields.char('Intrastat Code', size=16),
41 'description': fields.char('Description', size=64),
43 report_intrastat_code()
45 class product_template(osv.osv):
46 _name = "product.template"
47 _inherit = "product.template"
49 'intrastat_id': fields.many2one('report.intrastat.code', 'Intrastat code'),
53 class report_intrastat(osv.osv):
54 _name = "report.intrastat"
55 _description = "Intrastat report"
58 'name': fields.date('Month', readonly=True),
59 'code': fields.char('Country code', size="2", readonly=True),
60 'intrastat_id': fields.many2one('report.intrastat.code', 'Intrastat code', readonly=True),
61 'weight': fields.float('Weight', readonly=True),
62 'value': fields.float('Value', readonly=True),
63 'type': fields.selection([('import', 'Import'), ('export', 'Export')], 'Type'),
64 'currency_id': fields.many2one('res.currency', "Currency", readonly=True),
68 create or replace view report_intrastat as (
70 to_char(m.create_date, 'YYYY-MM-01') as name,
72 pt.intrastat_id as intrastat_id,
73 case when l.usage in ('supplier', 'customer') then upper(pc.code) else upper(c.code) end as code,
74 sum(case when pol.price_unit is not null
75 then pol.price_unit * m.product_qty
77 case when sol.price_unit is not null
78 then sol.price_unit * m.product_qty
82 sum(pt.weight_net * m.product_qty) as weight,
83 case when l.usage in ('supplier', 'customer') then 'import' else 'export' end as type,
84 case when ppl.currency_id is not null
90 left join (product_template pt
91 left join product_product pp on (pp.product_tmpl_id = pt.id))
92 on (m.product_id = pt.id)
93 left join (res_partner_address a
94 left join res_country c on (c.id = a.country_id))
95 on (a.id = m.address_id)
96 left join (stock_picking sp
97 left join (res_partner_address pa
98 left join res_country pc on (pc.id = pa.country_id))
99 on (pa.id = sp.address_id))
100 on (sp.id = m.picking_id)
101 left join stock_location l on (l.id = m.location_id)
102 left join stock_location dl on (dl.id = m.location_dest_id)
103 left join (purchase_order_line pol
104 left join (purchase_order po
105 left join product_pricelist ppl on (ppl.id = po.pricelist_id))
106 on (po.id = pol.order_id))
107 on (pol.id = m.purchase_line_id)
108 left join (sale_order_line sol
109 left join (sale_order so
110 left join product_pricelist spl on (spl.id = so.pricelist_id))
111 on (so.id = sol.order_id))
112 on (sol.id = m.sale_line_id)
115 and ((l.usage in ('supplier', 'customer') and dl.usage not in ('supplier', 'customer'))
116 or (dl.usage in ('supplier', 'customer') and l.usage not in ('supplier', 'customer')))
117 and (c.intrastat is not null or pc.intrastat is not null)
118 group by to_char(m.create_date, 'YYYY-MM-01'), pt.intrastat_id, c.code, pc.code, l.usage, dl.usage, ppl.currency_id, spl.currency_id
123 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: