addons/l10n_be/wizard/l10n_be_partner_vat_listing.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
   6 #
   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
  11 #
  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.
  16 #
  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.
  21 #
  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/>.
  24 #
  25 ##############################################################################
  26
  27 import time
  28 import base64
  29 from tools.translate import _
  30 from osv import fields, osv
  31
  32 class vat_listing_clients(osv.osv_memory):
  33     _name = 'vat.listing.clients'
  34     _columns = {
  35         'name': fields.char('Client Name', size=32),
  36         'vat': fields.char('VAT', size=64),
  37         'country': fields.char('Country', size=16),
  38         'amount': fields.float('Amount'),
  39         'turnover': fields.float('Turnover'),
  40     }
  41
  42     def name_get(self, cr, uid, ids, context=None):
  43         res = self.read(cr, uid, ids, ['name', 'vat'], context=context, load='_classic_write')
  44         return [(r['id'], '%s - %s' % (r['name'] or '', r['vat'] or '')) for r in res]
  45
  46     def name_search(self, cr, uid, name, args=None, operator='ilike', context=None, limit=100):
  47         args.append(['id', 'in', context['partner_ids']])
  48         client_ids = self.search(cr, uid, [('vat', '=', name)] + args, limit=limit, context=context)
  49         if not client_ids:
  50             client_ids = self.search(cr, uid, [('name', 'ilike', name)] + args, limit=limit, context=context)
  51         return self.name_get(cr, uid, client_ids, context=context)
  52
  53 vat_listing_clients()
  54
  55 class partner_vat_13(osv.osv_memory):
  56     """ Vat Listing """
  57     _name = "partner.vat_13"
  58
  59     def get_partner(self, cursor, user, ids, context=None):
  60         obj_period = self.pool.get('account.period')
  61         obj_partner = self.pool.get('res.partner')
  62         obj_vat_lclient = self.pool.get('vat.listing.clients')
  63         obj_model_data = self.pool.get('ir.model.data')
  64         data  = self.read(cursor, user, ids)[0]
  65         #period = obj_period.search(cursor, user, [('fiscalyear_id', '=', data['fyear'])], context=context)
  66         year = data['year']
  67         date_start = year + '-01-01'
  68         date_stop = year + '-12-31'
  69         period = obj_period.search(cursor, user, [('date_start' ,'>=', date_start), ('date_stop','<=',date_stop)])
  70         if not period:
  71              raise osv.except_osv(_('Data Insufficient!'), _('No data for the selected Year.'))
  72         #logger.notifyChannel('addons.'+self._name, netsvc.LOG_WARNING, 'period =  %s' %period )
  73
  74         p_id_list = obj_partner.search(cursor, user, [('vat_subjected', '!=', False)], context=context)
  75         if not p_id_list:
  76              raise osv.except_osv(_('Data Insufficient!'), _('No partner has a VAT Number asociated with him.'))
  77         partners = []
  78         records = []
  79         for obj_partner in obj_partner.browse(cursor, user, p_id_list, context=context):
  80             record = {} # this holds record per partner
  81
  82             #This listing is only for customers located in belgium, that's the
  83             #reason why we skip all the partners that haven't their
  84             #(or one of their) default address(es) located in Belgium.
  85             go_ahead = False
  86             for ads in obj_partner.address:
  87                 if ads.type == 'default' and (ads.country_id and ads.country_id.code == 'BE') and (obj_partner.vat or '').startswith('BE'):
  88                     go_ahead = True
  89                     break
  90             if not go_ahead:
  91                 continue
  92             cursor.execute('select b.code, sum(credit)-sum(debit) from account_move_line l left join account_account a on (l.account_id=a.id) left join account_account_type b on (a.user_type=b.id) where b.code IN %s and l.partner_id=%s and l.period_id IN %s group by b.code',(('income','produit','tax_out'),obj_partner.id,tuple(period),))
  93             line_info = cursor.fetchall()
  94             if not line_info:
  95                 continue
  96
  97             record['vat'] = obj_partner.vat.replace(' ','').upper()
  98
  99             #it seems that this listing is only for belgian customers
 100             record['country'] = 'BE'
 101
 102             record['amount'] = 0
 103             record['turnover'] = 0
 104             record['name'] = obj_partner.name
 105             for item in line_info:
 106                 if item[0] in ('income','produit'):
 107                     record['turnover'] += item[1]
 108                 else:
 109                     record['amount'] += item[1]
 110             id_client = obj_vat_lclient.create(cursor, user, record, context=context)
 111             partners.append(id_client)
 112             records.append(record)
 113         context.update({'partner_ids': partners, 'year': data['year'], 'limit_amount': data['limit_amount']})
 114         model_data_ids = obj_model_data.search(cursor, user, [('model','=','ir.ui.view'), ('name','=','view_vat_listing_13')])
 115         resource_id = obj_model_data.read(cursor, user, model_data_ids, fields=['res_id'])[0]['res_id']
 116         return {
 117             'name': 'Vat Listing',
 118             'view_type': 'form',
 119             'view_mode': 'form',
 120             'res_model': 'partner.vat.list_13',
 121             'views': [(resource_id,'form')],
 122             'context': context,
 123             'type': 'ir.actions.act_window',
 124             'target': 'new',
 125         }
 126
 127     _columns = {
 128         'year': fields.char('Year', size=4, required=True),
 129         'limit_amount': fields.integer('Limit Amount', required=True),
 130     }
 131
 132     _defaults={
 133         'year': lambda *a: str(int(time.strftime('%Y'))-1),
 134         'limit_amount': 250,
 135     }
 136
 137 partner_vat_13()
 138
 139 class partner_vat_list_13(osv.osv_memory):
 140     """ Partner Vat Listing """
 141     _name = "partner.vat.list_13"
 142     _columns = {
 143         # TODO the referenced model has been deleted at revno 4672.1.2.
 144         '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'),
 145         'name': fields.char('File Name', size=32),
 146         'msg': fields.text('File created', size=64, readonly=True),
 147         'file_save' : fields.binary('Save File', readonly=True),
 148         'identification_type': fields.selection([('tin','TIN'), ('nvat','NVAT'), ('other','Other')], 'Identification Type', required=True),
 149         'other': fields.char('Other Qlf', size=16, help="Description of a Identification Type"),
 150         'comments': fields.text('Comments'),
 151     }
 152
 153     def _get_partners(self, cursor, user, context=None):
 154         return context.get('partner_ids', [])
 155
 156     _defaults={
 157         # TODO the referenced model has been deleted at revno 4672.1.2.
 158         'partner_ids': _get_partners,
 159         'identification_type' : 'tin',
 160     }
 161
 162     def create_xml(self, cursor, user, ids, context=None):
 163         datas = []
 164         obj_sequence = self.pool.get('ir.sequence')
 165         obj_users = self.pool.get('res.users')
 166         obj_partner = self.pool.get('res.partner')
 167         obj_fyear = self.pool.get('account.fiscalyear')
 168         obj_addr = self.pool.get('res.partner.address')
 169         obj_vat_lclient = self.pool.get('vat.listing.clients')
 170
 171         seq_controlref = obj_sequence.get(cursor, user, 'controlref')
 172         seq_declarantnum = obj_sequence.get(cursor, user, 'declarantnum')
 173         obj_cmpny = obj_users.browse(cursor, user, user, context=context).company_id
 174         company_vat = obj_cmpny.partner_id.vat
 175
 176         if not company_vat:
 177             raise osv.except_osv(_('Data Insufficient'),_('No VAT Number Associated with Main Company!'))
 178
 179         company_vat = company_vat.replace(' ','').upper()
 180         SenderId = company_vat[2:]
 181         issued_by = company_vat[:2]
 182         cref = SenderId + seq_controlref
 183         dnum = cref + seq_declarantnum
 184         #obj_year= obj_fyear.browse(cursor, user, context['fyear'], context=context)
 185         street = city = country = ''
 186         addr = obj_partner.address_get(cursor, user, [obj_cmpny.partner_id.id], ['invoice'])
 187         if addr.get('invoice',False):
 188             ads = obj_addr.browse(cursor, user, [addr['invoice']], context=context)[0]
 189             phone = ads.phone or ''
 190             email = ads.email or ''
 191             name = ads.name or ''
 192
 193             city = obj_addr.get_city(cursor, user, ads.id)
 194             zip = obj_addr.browse(cursor, user, ads.id, context=context).zip or ''
 195             if not city:
 196                 city = ''
 197             if ads.street:
 198                 street = ads.street + ' '
 199             if ads.street2:
 200                 street += ads.street2
 201             if ads.country_id:
 202                 country = ads.country_id.code
 203
 204         data = self.read(cursor, user, ids)[0]
 205         other = data['other'] or ''
 206         sender_date = time.strftime('%Y-%m-%d')
 207         comp_name = obj_cmpny.name
 208
 209         annual_listing_data = {
 210             'identificationType': data['identification_type'].upper(),
 211             'issued_by': issued_by,
 212             'other': other,
 213             'company_vat': company_vat,
 214             'comp_name': comp_name,
 215             'street': street,
 216             'zip': zip,
 217             'city': city,
 218             'country': country,
 219             'email': email,
 220             'phone': phone,
 221             'SenderId': SenderId,
 222             'period': context['year'],
 223             'comments': data['comments'] or ''
 224         }
 225
 226         data_file = """<?xml version="1.0"?>
 227 <ClientListingConsignment xmlns="http://www.minfin.fgov.be/ClientListingConsignment" ClientListingsNbr="1">
 228     <Representative>
 229         <RepresentativeID identificationType="%(identificationType)s" issuedBy="%(issued_by)s" otherQlf="%(other)s">%(company_vat)s</RepresentativeID>
 230         <Name>%(comp_name)s</Name>
 231         <Street>%(street)s</Street>
 232         <PostCode>%(zip)s</PostCode>
 233         <City>%(city)s</City>
 234         <CountryCode>%(country)s</CountryCode>
 235         <EmailAddress>%(email)s</EmailAddress>
 236         <Phone>%(phone)s</Phone>
 237     </Representative>
 238     <RepresentativeReference></RepresentativeReference>
 239 """ % annual_listing_data
 240
 241         data_comp = """
 242         <ReplacedClientListing></ReplacedClientListing>
 243         <Declarant>
 244             <VATNumber xmlns="http://www.minfin.fgov.be/InputCommon">%(SenderId)s</VATNumber>
 245             <Name>%(comp_name)s</Name>
 246             <Street>%(street)s</Street>
 247             <PostCode>%(zip)s</PostCode>
 248             <City>%(city)s</City>
 249             <CountryCode>%(country)s</CountryCode>
 250             <EmailAddress>%(email)s</EmailAddress>
 251             <Phone>%(phone)s</Phone>
 252         </Declarant>
 253         <Period>%(period)s</Period>
 254         """ % annual_listing_data
 255
 256         error_message = []
 257
 258         for partner in data['partner_ids']:
 259             if isinstance(partner, list) and partner:
 260                 datas.append(partner[2])
 261             else:
 262                 client_data = obj_vat_lclient.read(cursor, user, partner, context=context)
 263                 datas.append(client_data)
 264         seq = 0
 265         data_client_info = ''
 266         sum_tax = 0.00
 267         sum_turnover = 0.00
 268         if len(error_message):
 269             return 'Exception : \n' +'-'*50+'\n'+ '\n'.join(error_message)
 270         amount_data = {
 271             'seq': str(seq),
 272             'dnum': dnum,
 273             'sum_tax': str(0),
 274             'sum_turnover': str(0),
 275         }
 276         for line in datas:
 277             vat_issued = line['vat'][:2]
 278             if vat_issued == 'BE':
 279                 vat_issued = ''
 280             else:
 281                 vat_issued = vat_issued
 282             if not line:
 283                 continue
 284             if line['turnover'] < context['limit_amount']:
 285                 continue
 286             seq += 1
 287             sum_tax += line['amount']
 288             sum_turnover += line['turnover']
 289
 290             amount_data.update({
 291                 'seq': str(seq),
 292                 'vat_issued': vat_issued,
 293                 'only_vat': line['vat'].replace(' ','').upper()[2:],
 294                 'turnover': str(int(round(line['turnover'] * 100))),
 295                 'vat_amount': str(int(round(line['amount'] * 100))),
 296                 'sum_tax': str(int(round(sum_tax * 100))),
 297                 'sum_turnover': str(int(round(sum_turnover * 100))),
 298             })
 299             # Turnover and Farmer tags are not included
 300             data_client_info += """
 301         <Client SequenceNumber="%(seq)s">
 302             <CompanyVATNumber issuedby="%(vat_issued)s">%(only_vat)s</CompanyVATNumber>
 303             <TurnOver>%(turnover)s</TurnOver>
 304             <VATAmount>%(vat_amount)s</VATAmount>
 305         </Client>""" % amount_data
 306
 307         data_begin = """
 308     <ClientListing SequenceNumber="1" ClientsNbr="%(seq)s" DeclarantReference="%(dnum)s"
 309         TurnOverSum="%(sum_turnover)s" VATAmountSum="%(sum_tax)s">
 310 """ % amount_data
 311
 312         data_end = """
 313         <FileAttachment></FileAttachment>
 314         <Comment>%(comments)s</Comment>
 315     </ClientListing>
 316 </ClientListingConsignment>
 317 """ % annual_listing_data
 318
 319         data_file += data_begin + data_comp + data_client_info + data_end
 320         msg = 'Save the File with '".xml"' extension.'
 321         file_save = base64.encodestring(data_file.encode('utf8'))
 322         self.write(cursor, user, ids, {'file_save':file_save, 'msg':msg, 'name':'vat_list.xml'}, context=context)
 323         return True
 324
 325 #    Not fully implemented
 326
 327 #    def print_vatlist(self, cursor, user, ids, context=None):
 328 #        if context is None:
 329 #            context = {}
 330 #        obj_vat_lclient = self.pool.get('vat.listing.clients')
 331 #        client_datas = []
 332 #        data = self.read(cursor, user, ids)[0]
 333 #        for partner in data['partner_ids']:
 334 #            if isinstance(partner, list) and partner:
 335 #                client_datas.append(partner[2])
 336 #            else:
 337 #                client_data = obj_vat_lclient.read(cursor, user, partner, context=context)
 338 #                client_datas.append(client_data)
 339 #
 340 #        datas = {'ids': []}
 341 #        datas['model'] = 'res.company'
 342 #        datas['year'] = context['year']
 343 #        datas['limit_amount'] = context['limit_amount']
 344 #        datas['client_datas'] = client_datas
 345 #        return {
 346 #            'type': 'ir.actions.report.xml',
 347 #            'report_name': 'partner.vat.listing.print',
 348 #            'datas': datas,
 349 #        }
 350
 351 partner_vat_list_13()
 352
 353 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: