1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
7 # Corrections & modifications by Noviat nv/sa, (http://www.noviat.be):
8 # - VAT listing based upon year in stead of fiscal year
9 # - sql query adapted to select only 'tax-out' move lines
10 # - extra button to print readable PDF report
12 # This program is free software: you can redistribute it and/or modify
13 # it under the terms of the GNU Affero General Public License as
14 # published by the Free Software Foundation, either version 3 of the
15 # License, or (at your option) any later version.
17 # This program is distributed in the hope that it will be useful,
18 # but WITHOUT ANY WARRANTY; without even the implied warranty of
19 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 # GNU Affero General Public License for more details.
22 # You should have received a copy of the GNU Affero General Public License
23 # along with this program. If not, see <http://www.gnu.org/licenses/>.
25 ##############################################################################
29 from openerp.tools.translate import _
30 from openerp.osv import fields, osv
31 from openerp.report import report_sxw
33 class vat_listing_clients(osv.osv_memory):
34 _name = 'vat.listing.clients'
36 'name': fields.char('Client Name'),
37 'vat': fields.char('VAT'),
38 'turnover': fields.float('Base Amount'),
39 'vat_amount': fields.float('VAT Amount'),
43 class partner_vat(osv.osv_memory):
47 def get_partner(self, cr, uid, ids, context=None):
48 obj_period = self.pool.get('account.period')
49 obj_partner = self.pool.get('res.partner')
50 obj_vat_lclient = self.pool.get('vat.listing.clients')
51 obj_model_data = self.pool.get('ir.model.data')
52 obj_module = self.pool.get('ir.module.module')
53 data = self.read(cr, uid, ids)[0]
55 date_start = year + '-01-01'
56 date_stop = year + '-12-31'
57 if context.get('company_id', False):
58 company_id = context['company_id']
60 company_id = self.pool.get('res.users').browse(cr, uid, uid, context=context).company_id.id
61 period_ids = obj_period.search(cr, uid, [('date_start' ,'>=', date_start), ('date_stop','<=',date_stop), ('company_id','=',company_id)])
63 raise osv.except_osv(_('Insufficient Data!'), _('No data for the selected year.'))
66 partner_ids = obj_partner.search(cr, uid, [('vat_subjected', '!=', False), ('vat','ilike','BE%')], context=context)
68 raise osv.except_osv(_('Error'),_('No belgium contact with a VAT number in your database.'))
69 cr.execute("""SELECT sub1.partner_id, sub1.name, sub1.vat, sub1.turnover, sub2.vat_amount
70 FROM (SELECT l.partner_id, p.name, p.vat, SUM(CASE WHEN c.code ='49' THEN -l.tax_amount ELSE l.tax_amount END) as turnover
71 FROM account_move_line l
72 LEFT JOIN res_partner p ON l.partner_id = p.id
73 LEFT JOIN account_tax_code c ON l.tax_code_id = c.id
74 WHERE c.code IN ('00','01','02','03','45','49')
75 AND l.partner_id IN %s
77 GROUP BY l.partner_id, p.name, p.vat) AS sub1
78 LEFT JOIN (SELECT l2.partner_id, SUM(CASE WHEN c2.code ='64' THEN -l2.tax_amount ELSE l2.tax_amount END) as vat_amount
79 FROM account_move_line l2
80 LEFT JOIN account_tax_code c2 ON l2.tax_code_id = c2.id
81 WHERE c2.code IN ('54','64')
82 AND l2.partner_id IN %s
83 AND l2.period_id IN %s
84 GROUP BY l2.partner_id) AS sub2 ON sub1.partner_id = sub2.partner_id
85 """,(tuple(partner_ids),tuple(period_ids),tuple(partner_ids),tuple(period_ids)))
86 for record in cr.dictfetchall():
87 record['vat'] = record['vat'].replace(' ','').upper()
88 if record['turnover'] >= data['limit_amount']:
89 id_client = obj_vat_lclient.create(cr, uid, record, context=context)
90 partners.append(id_client)
93 raise osv.except_osv(_('Insufficient Data!'), _('No data found for the selected year.'))
94 context.update({'partner_ids': partners, 'year': data['year'], 'limit_amount': data['limit_amount']})
95 model_data_ids = obj_model_data.search(cr, uid, [('model','=','ir.ui.view'), ('name','=','view_vat_listing')])
96 resource_id = obj_model_data.read(cr, uid, model_data_ids, fields=['res_id'])[0]['res_id']
98 'name': _('Vat Listing'),
101 'res_model': 'partner.vat.list',
102 'views': [(resource_id,'form')],
104 'type': 'ir.actions.act_window',
109 'year': fields.char('Year', size=4, required=True),
110 'limit_amount': fields.integer('Limit Amount', required=True),
114 'year': lambda *a: str(int(time.strftime('%Y'))-1),
119 class partner_vat_list(osv.osv_memory):
120 """ Partner Vat Listing """
121 _name = "partner.vat.list"
123 'partner_ids': fields.many2many('vat.listing.clients', 'vat_partner_rel', 'vat_id', 'partner_id', 'Clients', help='You can remove clients/partners which you do not want to show in xml file'),
124 'name': fields.char('File Name'),
125 'file_save' : fields.binary('Save File', readonly=True),
126 'comments': fields.text('Comments'),
129 def _get_partners(self, cr, uid, context=None):
130 return context.get('partner_ids', [])
133 'partner_ids': _get_partners,
136 def _get_datas(self, cr, uid, ids, context=None):
137 obj_vat_lclient = self.pool.get('vat.listing.clients')
139 data = self.read(cr, uid, ids)[0]
140 for partner in data['partner_ids']:
141 if isinstance(partner, list) and partner:
142 datas.append(partner[2])
144 client_data = obj_vat_lclient.read(cr, uid, partner, context=context)
145 datas.append(client_data)
155 sum_tax += line['vat_amount']
156 sum_turnover += line['turnover']
157 vat = line['vat'].replace(' ','').upper()
162 'turnover': '%.2f' %line['turnover'],
163 'vat_amount': '%.2f' %line['vat_amount'],
164 'sum_tax': '%.2f' %sum_tax,
165 'sum_turnover': '%.2f' %sum_turnover,
166 'partner_name': line['name'],
168 client_datas += [amount_data]
171 def create_xml(self, cr, uid, ids, context=None):
173 obj_sequence = self.pool.get('ir.sequence')
174 obj_users = self.pool.get('res.users')
175 obj_partner = self.pool.get('res.partner')
176 obj_model_data = self.pool.get('ir.model.data')
177 seq_declarantnum = obj_sequence.next_by_code(cr, uid, 'declarantnum')
178 obj_cmpny = obj_users.browse(cr, uid, uid, context=context).company_id
179 company_vat = obj_cmpny.partner_id.vat
182 raise osv.except_osv(_('Insufficient Data!'),_('No VAT number associated with the company.'))
184 company_vat = company_vat.replace(' ','').upper()
185 SenderId = company_vat[2:]
186 issued_by = company_vat[:2]
187 seq_declarantnum = obj_sequence.next_by_code(cr, uid, 'declarantnum')
188 dnum = company_vat[2:] + seq_declarantnum[-4:]
189 street = city = country = ''
190 addr = obj_partner.address_get(cr, uid, [obj_cmpny.partner_id.id], ['invoice'])
191 if addr.get('invoice',False):
192 ads = obj_partner.browse(cr, uid, [addr['invoice']], context=context)[0]
193 phone = ads.phone and ads.phone.replace(' ','') or ''
194 email = ads.email or ''
195 name = ads.name or ''
196 city = ads.city or ''
197 zip = obj_partner.browse(cr, uid, ads.id, context=context).zip or ''
203 street += ' ' + ads.street2
205 country = ads.country_id.code
207 data = self.read(cr, uid, ids)[0]
208 comp_name = obj_cmpny.name
211 raise osv.except_osv(_('Insufficient Data!'),_('No email address associated with the company.'))
213 raise osv.except_osv(_('Insufficient Data!'),_('No phone associated with the company.'))
214 annual_listing_data = {
215 'issued_by': issued_by,
216 'company_vat': company_vat,
217 'comp_name': comp_name,
224 'SenderId': SenderId,
225 'period': context['year'],
226 'comments': data['comments'] or ''
229 data_file = """<?xml version="1.0" encoding="ISO-8859-1"?>
230 <ns2:ClientListingConsignment xmlns="http://www.minfin.fgov.be/InputCommon" xmlns:ns2="http://www.minfin.fgov.be/ClientListingConsignment" ClientListingsNbr="1">
232 <RepresentativeID identificationType="NVAT" issuedBy="%(issued_by)s">%(SenderId)s</RepresentativeID>
233 <Name>%(comp_name)s</Name>
234 <Street>%(street)s</Street>
235 <PostCode>%(zip)s</PostCode>
236 <City>%(city)s</City>"""
237 if annual_listing_data['country']:
238 data_file +="\n\t\t<CountryCode>%(country)s</CountryCode>"
240 <EmailAddress>%(email)s</EmailAddress>
241 <Phone>%(phone)s</Phone>
242 </ns2:Representative>"""
243 data_file = data_file % annual_listing_data
247 <VATNumber>%(SenderId)s</VATNumber>
248 <Name>%(comp_name)s</Name>
249 <Street>%(street)s</Street>
250 <PostCode>%(zip)s</PostCode>
251 <City>%(city)s</City>
252 <CountryCode>%(country)s</CountryCode>
253 <EmailAddress>%(email)s</EmailAddress>
254 <Phone>%(phone)s</Phone>
256 <ns2:Period>%(period)s</ns2:Period>
257 """ % annual_listing_data
259 # Turnover and Farmer tags are not included
260 client_datas = self._get_datas(cr, uid, ids, context=context)
262 raise osv.except_osv(_('Data Insufficient!'),_('No data available for the client.'))
263 data_client_info = ''
264 for amount_data in client_datas:
265 data_client_info += """
266 <ns2:Client SequenceNumber="%(seq)s">
267 <ns2:CompanyVATNumber issuedBy="BE">%(only_vat)s</ns2:CompanyVATNumber>
268 <ns2:TurnOver>%(turnover)s</ns2:TurnOver>
269 <ns2:VATAmount>%(vat_amount)s</ns2:VATAmount>
270 </ns2:Client>""" % amount_data
272 amount_data_begin = client_datas[-1]
273 amount_data_begin.update({'dnum':dnum})
275 <ns2:ClientListing SequenceNumber="1" ClientsNbr="%(seq)s" DeclarantReference="%(dnum)s"
276 TurnOverSum="%(sum_turnover)s" VATAmountSum="%(sum_tax)s">
277 """ % amount_data_begin
281 <ns2:Comment>%(comments)s</ns2:Comment>
283 </ns2:ClientListingConsignment>
284 """ % annual_listing_data
286 data_file += data_begin + data_comp + data_client_info + data_end
287 file_save = base64.encodestring(data_file.encode('utf8'))
288 self.write(cr, uid, ids, {'file_save':file_save, 'name':'vat_list.xml'}, context=context)
289 model_data_ids = obj_model_data.search(cr, uid, [('model','=','ir.ui.view'), ('name','=','view_vat_listing_result')])
290 resource_id = obj_model_data.read(cr, uid, model_data_ids, fields=['res_id'])[0]['res_id']
293 'name': _('XML File has been Created'),
297 'res_model': 'partner.vat.list',
298 'views': [(resource_id,'form')],
300 'type': 'ir.actions.act_window',
304 def print_vatlist(self, cr, uid, ids, context=None):
308 datas['model'] = 'res.company'
309 datas['year'] = context['year']
310 datas['limit_amount'] = context['limit_amount']
311 datas['client_datas'] = self._get_datas(cr, uid, ids, context=context)
312 if not datas['client_datas']:
313 raise osv.except_osv(_('Error!'), _('No record to print.'))
314 return self.pool['report'].get_action(
315 cr, uid, [], 'l10n_be.report_l10nvatpartnerlisting', data=datas, context=context
319 class partner_vat_listing_print(report_sxw.rml_parse):
321 def __init__(self, cr, uid, name, context):
322 super(partner_vat_listing_print, self).__init__(cr, uid, name, context=context)
323 self.localcontext.update( {
327 def set_context(self, objects, data, ids, report_type=None):
328 client_datas = data['client_datas']
329 self.localcontext.update( {
330 'year': data['year'],
331 'sum_turnover': client_datas[-1]['sum_turnover'],
332 'sum_tax': client_datas[-1]['sum_tax'],
333 'client_list': client_datas,
335 super(partner_vat_listing_print, self).set_context(objects, data, ids)
338 class wrapped_vat_listing_print(osv.AbstractModel):
339 _name = 'report.l10n_be.report_l10nvatpartnerlisting'
340 _inherit = 'report.abstract_report'
341 _template = 'l10n_be.report_l10nvatpartnerlisting'
342 _wrapped_report_class = partner_vat_listing_print
344 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: