1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
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.
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.
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/>.
20 ##############################################################################
22 from osv import osv, fields
23 from tools.sql import drop_view_if_exists
26 class res_country(osv.osv):
28 _inherit = 'res.country'
30 'intrastat': fields.boolean('Intrastat member'),
33 'intrastat': lambda * a: False,
39 class report_intrastat_code(osv.osv):
40 _name = "report.intrastat.code"
41 _description = "Intrastat code"
43 'name': fields.char('Intrastat Code', size=16),
44 'description': fields.char('Description', size=64),
47 report_intrastat_code()
50 class product_template(osv.osv):
51 _name = "product.template"
52 _inherit = "product.template"
54 'intrastat_id': fields.many2one('report.intrastat.code', 'Intrastat code'),
59 class report_intrastat(osv.osv):
60 _name = "report.intrastat"
61 _description = "Intrastat report"
64 'name': fields.char('Year', size=64, required=False, readonly=True),
65 'month':fields.selection([('01', 'January'), ('02', 'February'), ('03', 'March'), ('04', 'April'), ('05', 'May'), ('06', 'June'),
66 ('07', 'July'), ('08', 'August'), ('09', 'September'), ('10', 'October'), ('11', 'November'), ('12', 'December')], 'Month', readonly=True),
67 'supply_units':fields.float('Supply Units', readonly=True),
68 'ref':fields.char('Source document', size=64, readonly=True),
69 'code': fields.char('Country code', size="2", readonly=True),
70 'intrastat_id': fields.many2one('report.intrastat.code', 'Intrastat code', readonly=True),
71 'weight': fields.float('Weight', readonly=True),
72 'value': fields.float('Value', readonly=True),
73 'type': fields.selection([('import', 'Import'), ('export', 'Export')], 'Type'),
74 'currency_id': fields.many2one('res.currency', "Currency", readonly=True),
77 drop_view_if_exists(cr, 'report_intrastat')
79 create or replace view report_intrastat as (
81 to_char(inv.create_date, 'YYYY') as name,
82 to_char(inv.create_date, 'MM') as month,
83 min(inv_line.id) as id,
84 intrastat.id as intrastat_id,
85 upper(inv_country.code) as code,
86 sum(case when inv_line.price_unit is not null
87 then inv_line.price_unit * inv_line.quantity
91 case when uom.category_id != puom.category_id then pt.weight_net * inv_line.quantity
93 case when uom.factor_inv_data > 0
95 pt.weight_net * inv_line.quantity * uom.factor_inv_data
97 pt.weight_net * inv_line.quantity / uom.factor
102 case when uom.category_id != puom.category_id then inv_line.quantity
104 case when uom.factor_inv_data > 0
106 inv_line.quantity * uom.factor_inv_data
108 inv_line.quantity / uom.factor
113 inv.currency_id as currency_id,
115 case when inv.type in ('out_invoice','in_refund')
121 left join account_invoice_line inv_line on inv_line.invoice_id=inv.id
122 left join (product_template pt
123 left join product_product pp on (pp.product_tmpl_id = pt.id))
124 on (inv_line.product_id = pt.id)
125 left join product_uom uom on uom.id=inv_line.uos_id
126 left join product_uom puom on puom.id = pt.uom_id
127 left join report_intrastat_code intrastat on pt.intrastat_id = intrastat.id
128 left join (res_partner_address inv_address
129 left join res_country inv_country on (inv_country.id = inv_address.country_id))
130 on (inv_address.id = inv.address_invoice_id)
133 inv.state in ('open','paid')
134 and inv_line.product_id is not null
135 and inv_country.intrastat=true
136 group by to_char(inv.create_date, 'YYYY'), to_char(inv.create_date, 'MM'),intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number, inv.currency_id