Amit Dodiya (OpenERP) has proposed merging lp:~openerp-dev/openobject-addons/5.0-opw-381491-Vat_Listing-ado into lp:openobject-addons/5.0.
Requested reviews: Xavier ALT (OpenERP) (xal-openerp) Naresh(OpenERP) (nch-openerp) For more details, see: https://code.launchpad.net/~openerp-dev/openobject-addons/5.0-opw-381491-Vat_Listing-ado/+merge/95353 Hello, Improve the Vat_List.xml file creation from wizard as per the new VAT format. Thanks, Amit -- https://code.launchpad.net/~openerp-dev/openobject-addons/5.0-opw-381491-Vat_Listing-ado/+merge/95353 Your team OpenERP R&D Team is subscribed to branch lp:~openerp-dev/openobject-addons/5.0-opw-381491-Vat_Listing-ado.
=== modified file 'l10n_be/company.py' --- l10n_be/company.py 2010-04-06 09:41:18 +0000 +++ l10n_be/company.py 2012-03-01 13:16:18 +0000 @@ -1,7 +1,7 @@ # -*- encoding: utf-8 -*- ############################################################################## # -# OpenERP, Open Source Management Solution +# OpenERP, Open Source Management Solution # Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved # $Id$ # @@ -21,6 +21,8 @@ ############################################################################## from osv import osv +from osv import fields, osv + class res_company(osv.osv): _inherit = "res.company" @@ -42,4 +44,16 @@ return city, post_code, address, country_code res_company() +class vat_listing_clients(osv.osv): + + _name = 'vat.listing.clients' + _columns = { + 'name': fields.char('Client Name', size=32), + 'vat': fields.char('VAT', size=64), + 'vat_amount': fields.float('Vat Amount'), + 'turnover': fields.float('Base Turnover'), + } +vat_listing_clients() + + # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: === modified file 'l10n_be/wizard/partner_vat_listing.py' --- l10n_be/wizard/partner_vat_listing.py 2012-02-29 14:14:59 +0000 +++ l10n_be/wizard/partner_vat_listing.py 2012-03-01 13:16:18 +0000 @@ -26,6 +26,8 @@ import base64 from tools.translate import _ import tools +from osv import fields, osv + form = """<?xml version="1.0"?> <form string="Select Fiscal Year"> @@ -35,8 +37,6 @@ <newline/> <field name="date_stop"/> <newline/> - <field name="mand_id" help="Should be provided when subscription of INTERVAT service is done"/> - <newline/> <field name="limit_amount" help="Limit under which the partners will not be included into the listing"/> <newline/> <field name="test_xml" help="Set the XML output as test file"/> @@ -45,42 +45,161 @@ fields = { 'date_start': {'string':"Start date",'type':'date','required':True ,'default': lambda *a: time.strftime('%Y-01-01')}, 'date_stop': {'string':"End date",'type':'date','required':True, 'default': lambda *a: time.strftime('%Y-12-31')}, - 'mand_id':{'string':'MandataireId','type':'char','size':'30','required': True,}, 'limit_amount':{'string':'Limit Amount','type':'integer','required': True, }, 'test_xml': {'string':'Test XML file', 'type':'boolean', }, } + msg_form = """<?xml version="1.0"?> + <form string="Select Fiscal Year"> + <notebook colspan="4"> + <page string="Details"> + <label string="You can remove customers which you do not want in exported xml file" colspan="4"/> + <separator string="Customers" colspan="4"/> + <field name="partner_ids" colspan="4" nolabel="1" default_focus="1" height="200" width="500"/> + </page> + <page string="Comments"> + <field name="comments" colspan="4" nolabel="1"/> + </page> + </notebook> + </form>""" + +msg_fields = { + 'partner_ids': {'string':'Clients','type':'many2many', 'relation':'vat.listing.clients'}, + 'comments': {'string': 'Comments', 'type': 'text', 'size' : 64}, +} + +msg_form_new = """<?xml version="1.0"?> <form string="Notification"> <separator string="XML File has been Created." colspan="4"/> <field name="msg" colspan="4" nolabel="1"/> - <field name="file_save" /> + <field name="file" /> </form>""" - -msg_fields = { +msg_fields_new = { 'msg': {'string':'File created', 'type':'text', 'size':'100','readonly':True}, - 'file_save':{'string': 'Save File', + 'file':{'string': 'Save File', 'type': 'binary', 'readonly': True,}, } + class wizard_vat(wizard.interface): + + def _check_data(self, cr, uid, data, context): + obj_cmpny = pooler.get_pool(cr.dbname).get('res.users').browse(cr, uid, uid).company_id + company_vat = obj_cmpny.partner_id.vat + if not company_vat: + raise wizard.except_wizard(_('Data Insufficient'),_('No VAT Number Associated with Main Company!')) + + domains = [('date_start','>=',data['form']['date_start']),('date_stop','<=',data['form']['date_stop'])] + date_stop = data['form']['date_stop'] + period_ids = pooler.get_pool(cr.dbname).get('account.period').search(cr, uid, domains) + if not period_ids: + raise wizard.except_wizard(_('Warning !'), _('Please select the proper Start date and End Date')) + return data['form'] + + def _get_partner(self, cr, uid, data, context=None): + domains = [('date_start','>=',data['form']['date_start']),('date_stop','<=',data['form']['date_stop'])] + date_stop = data['form']['date_stop'] + period_ids = pooler.get_pool(cr.dbname).get('account.period').search(cr, uid, domains) + obj_partner = pooler.get_pool(cr.dbname).get('res.partner') + obj_vat_lclient = pooler.get_pool(cr.dbname).get('vat.listing.clients') + domains = [('date_start','>=',data['form']['date_start']),('date_stop','<=',data['form']['date_stop'])] + period_ids = pooler.get_pool(cr.dbname).get('account.period').search(cr, uid, domains) + + if context.get('company_id', False): + company_id = context['company_id'] + else: + company_id = pooler.get_pool(cr.dbname).get('res.users').browse(cr, uid, uid, context=context).company_id.id + + partners = [] + partner_ids = obj_partner.search(cr, uid, [('vat_subjected', '!=', False), ('vat','ilike','BG%')], context=context) + cr.execute("""SELECT sub1.partner_id, sub1.name, sub1.vat, sub1.turnover, sub2.vat_amount + 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 + FROM account_move_line l + LEFT JOIN res_partner p ON l.partner_id = p.id + LEFT JOIN account_tax_code c ON l.tax_code_id = c.id + WHERE c.code IN ('01','02','03','45','49') + AND l.partner_id IN %s + AND l.period_id IN %s + GROUP BY l.partner_id, p.name, p.vat) AS sub1 + LEFT JOIN (SELECT l2.partner_id, SUM(CASE WHEN c2.code ='64' THEN -l2.tax_amount ELSE l2.tax_amount END) as vat_amount + FROM account_move_line l2 + LEFT JOIN account_tax_code c2 ON l2.tax_code_id = c2.id + WHERE c2.code IN ('54','64') + AND l2.partner_id IN %s + AND l2.period_id IN %s + GROUP BY l2.partner_id) AS sub2 ON sub1.partner_id = sub2.partner_id + """,(tuple(partner_ids),tuple(period_ids),tuple(partner_ids),tuple(period_ids))) + for record in cr.dictfetchall(): + record['vat'] = record['vat'].replace(' ','').upper() + if record['turnover'] >= data['limit_amount']: + id_client = obj_vat_lclient.create(cr, uid, record, context=context) + partners.append(id_client) + data['form']['partner_ids'] = partners + return data['form'] + + def _get_datas(self, cr, uid, data, context=None): + obj_vat_lclient = pooler.get_pool(cr.dbname).get('vat.listing.clients') + datas = [] + for partner in data['form']['partner_ids']: + if isinstance(partner, list) and partner: + datas.append(partner[2]) + else: + client_data = obj_vat_lclient.browse(cr, uid, partner[2]) + for i in client_data: + datas.append(i) + + seq=0 + data_clientinfo='' + data_decl = '' + sum_tax=0.00 + sum_turnover=0.00 + client_datas = [] + for line in datas: + if not line['vat']: + raise wizard.except_wizard(_('Warning !'), _("Missing VAT ,Partner does not have a vat number")) + vat_issued = line['vat'][:2] + if vat_issued == 'BE': + vat_issued = '' + else: + vat_issued = vat_issued + if line['turnover'] < data['form']['limit_amount']: + continue + seq +=1 + sum_tax +=line['vat_amount'] + sum_turnover +=line['turnover'] + vat = line['vat'].replace(' ','').upper() + client_datas.append({ + 'seq': str(seq), + 'vat': vat, + 'only_vat': vat[2:], + 'turnover': '%.2f' %line['turnover'], + 'vat_amount': '%.2f' %line['vat_amount'], + 'sum_tax': '%.2f' %sum_tax, + 'sum_turnover': '%.2f' %sum_turnover, + 'partner_name': line['name'], + }) + return client_datas def _create_xml(self, cr, uid, data, context): datas=[] - seq_controlref = pooler.get_pool(cr.dbname).get('ir.sequence').get(cr, uid,'controlref') seq_declarantnum = pooler.get_pool(cr.dbname).get('ir.sequence').get(cr, uid,'declarantnum') obj_cmpny = pooler.get_pool(cr.dbname).get('res.users').browse(cr, uid, uid).company_id + obj_vat_lclient = pooler.get_pool(cr.dbname).get('vat.listing.clients') + company_vat = obj_cmpny.partner_id.vat if not company_vat: raise wizard.except_wizard(_('Data Insufficient'),_('No VAT Number Associated with Main Company!')) - cref = company_vat + seq_controlref - dnum = cref + seq_declarantnum - + dnum = company_vat[2:] + seq_declarantnum[-4:] p_id_list = pooler.get_pool(cr.dbname).get('res.partner').search(cr,uid,[('vat_subjected','!=',False)]) if not p_id_list: raise wizard.except_wizard(_('Data Insufficient!'),_('No partner has a VAT Number associated with him.')) domains = [('date_start','>=',data['form']['date_start']),('date_stop','<=',data['form']['date_stop'])] + dnum = company_vat[2:] + seq_declarantnum[-4:] + company_vat = company_vat.replace(' ','').upper() + SenderId = company_vat[2:] + issued_by = company_vat[:2] date_stop = data['form']['date_stop'] period_ids = pooler.get_pool(cr.dbname).get('account.period').search(cr, uid, domains) if not period_ids: @@ -91,8 +210,10 @@ addr = pooler.get_pool(cr.dbname).get('res.partner').address_get(cr, uid, [obj_cmpny.partner_id.id], ['invoice']) if addr.get('invoice',False): ads=pooler.get_pool(cr.dbname).get('res.partner.address').browse(cr,uid,[addr['invoice']])[0] - + email = ads.email or '' + phone = ads.phone or '' zip_city = pooler.get_pool(cr.dbname).get('res.partner.address').get_city(cr,uid,ads.id) + zip = pooler.get_pool(cr.dbname).get('res.partner.address').browse(cr, uid, ads.id, context=context).zip or '' if not zip_city: zip_city = '' if ads.street: @@ -103,106 +224,96 @@ country = ads.country_id.code sender_date = time.strftime('%Y-%m-%d') + + annual_listing_data = { + 'issued_by': issued_by, + 'company_vat': company_vat, + 'comp_name': obj_cmpny.name, + 'street': street, + 'zip': zip, + 'city': zip_city, + 'country': country, + 'email': email, + 'phone': phone, + 'SenderId': SenderId, + 'period': date_stop[:4], + 'comments': data['form']['comments'] or '' + } + comp_name = obj_cmpny.name - data_file = '<?xml version="1.0"?>\n<VatList xmlns="http://www.minfin.fgov.be/VatList"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://www.minfin.fgov.be/VatList VatList.xml" RecipientId="VAT-ADMIN" SenderId="'+ str(company_vat) + '"' - data_file +=' ControlRef="'+ cref + '" MandataireId="'+ data['form']['mand_id'] + '" SenderDate="'+ str(sender_date)+ '"' - if data['form']['test_xml']: - data_file += ' Test="0"' - data_file += ' VersionTech="1.2">' - data_file += '\n<AgentRepr DecNumber="1">\n\t<CompanyInfo>\n\t\t<VATNum>'+str(company_vat)+'</VATNum>\n\t\t<Name>'+ comp_name +'</Name>\n\t\t<Street>'+ street +'</Street>\n\t\t<CityAndZipCode>'+ zip_city +'</CityAndZipCode>' - data_file += '\n\t\t<Country>'+ country +'</Country>\n\t</CompanyInfo>\n</AgentRepr>' - data_comp = '\n<CompanyInfo>\n\t<VATNum>'+str(company_vat)+'</VATNum>\n\t<Name>'+ comp_name +'</Name>\n\t<Street>'+ street +'</Street>\n\t<CityAndZipCode>'+ zip_city +'</CityAndZipCode>\n\t<Country>'+ country +'</Country>\n</CompanyInfo>' - data_period = '\n<Period>'+ date_stop[:4] +'</Period>' - error_message = [] - - for p_id in p_id_list: - record = {} # this holds record per partner - obj_partner = pooler.get_pool(cr.dbname).get('res.partner').browse(cr,uid,p_id) - - #This listing is only for customers located in belgium, that's the - #reason why we skip all the partners that haven't their - #(or one of their) default address(es) located in Belgium. - go_ahead = False - for ads in obj_partner.address: - if ads.type == 'default' and (ads.country_id and ads.country_id.code == 'BE'): - go_ahead = True - break - if not go_ahead: - continue - query = '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 ('"'produit'"','"'tax'"') and l.partner_id='+str(p_id)+' and l.period_id in '+period+' group by b.code' - cr.execute(query) - line_info = cr.fetchall() - if not line_info: - continue - - record['name'] = obj_partner.name - record['vat'] = obj_partner.vat - - #it seems that this listing is only for belgian customers - record['country'] = 'BE' - - #...deprecated... - #~addr = pooler.get_pool(cr.dbname).get('res.partner').address_get(cr, uid, [obj_partner.id], ['invoice']) - - #~ if addr.get('invoice',False): - #~ads=pooler.get_pool(cr.dbname).get('res.partner.address').browse(cr,uid,[addr['invoice']])[0] - - #~ if ads.country_id: - #~ record.append(ads.country_id.code) - #~ else: - #~ error_message.append('Data Insufficient! : '+ 'The Partner "'+obj_partner.name + '"'' has no country associated with its Invoice address!') - - #~ if len(record)<2: - #~ record.append('') - #~ error_message.append('Data Insufficient! : '+ 'The Partner "'+obj_partner.name + '"'' has no Invoice address!') - - record['amount'] = 0 - record['turnover'] = 0 - - for item in line_info: - if item[0]=='produit': - record['turnover'] += item[1] - else: - record['amount'] += item[1] - datas.append(record) - - seq=0 - data_clientinfo='' - sum_tax=0.00 - sum_turnover=0.00 - if len(error_message): - data['form']['msg']='Exception : \n' +'-'*50+'\n'+ '\n'.join(error_message) - return data['form'] - for line in datas: - if line['turnover'] < data['form']['limit_amount']: - continue - seq +=1 - sum_tax +=line['amount'] - sum_turnover +=line['turnover'] - if not line['vat']: - raise wizard.except_wizard(_('Warning !'), _("Missing VAT ,Partner %s does not have a vat number") % line['name']) - if not line['country']: - raise wizard.except_wizard(_('Warning !'), _("Missing Country ,Partner %s does not have a country") % line['name']) - - data_clientinfo +='\n<ClientList SequenceNum="'+str(seq)+'">\n\t<CompanyInfo>\n\t\t<VATNum>'+line['vat'] +'</VATNum>\n\t\t<Country>'+tools.ustr(line['country']) +'</Country>\n\t</CompanyInfo>\n\t<Amount>'+str(int(line['amount'] * 100)) +'</Amount>\n\t<TurnOver>'+str(int(line['turnover'] * 100)) +'</TurnOver>\n</ClientList>' - - data_decl ='\n<DeclarantList SequenceNum="1" DeclarantNum="'+ dnum + '" ClientNbr="'+ str(seq) +'" TurnOverSum="'+ str(int(sum_turnover * 100)) +'" TaxSum="'+ str(int(sum_tax * 100)) +'" />' - data_file += data_decl + data_comp + str(data_period) + data_clientinfo + '\n</VatList>' - + data_file = """<?xml version="1.0"?> + <ns2:ClientListingConsignment xmlns="http://www.minfin.fgov.be/InputCommon"; xmlns:ns2="http://www.minfin.fgov.be/ClientListingConsignment"; ClientListingsNbr="1"> + <ns2:Representative> + <RepresentativeID identificationType="NVAT" issuedBy="%(issued_by)s">%(SenderId)s</RepresentativeID> + <Name>%(comp_name)s</Name> + <Street>%(street)s</Street> + <PostCode>%(zip)s</PostCode> + <City>%(city)s</City>""" + if annual_listing_data['country']: + data_file +="\n\t\t<CountryCode>%(country)s</CountryCode>" + data_file += """ + <EmailAddress>%(email)s</EmailAddress> + <Phone>%(phone)s</Phone> + </ns2:Representative>""" + data_file = data_file % annual_listing_data + data_comp = """ + <ns2:Declarant> + <VATNumber>%(SenderId)s</VATNumber> + <Name>%(comp_name)s</Name> + <Street>%(street)s</Street> + <PostCode>%(zip)s</PostCode> + <City>%(city)s</City> + <CountryCode>%(country)s</CountryCode> + <EmailAddress>%(email)s</EmailAddress> + <Phone>%(phone)s</Phone> + </ns2:Declarant> + <ns2:Period>%(period)s</ns2:Period> + """ % annual_listing_data + + client_datas = self._get_datas(cr, uid, data, context=context) + if client_datas == []: + raise wizard.except_wizard(_('Warning !'), _("No data for Vat Listing file creation")) + data_client_info = '' + for amount_data in client_datas: + data_client_info += """ + <ns2:Client SequenceNumber="%(seq)s"> + <ns2:CompanyVATNumber issuedBy="BE">%(only_vat)s</ns2:CompanyVATNumber> + <ns2:TurnOver>%(turnover)s</ns2:TurnOver> + <ns2:VATAmount>%(vat_amount)s</ns2:VATAmount> + </ns2:Client>""" % amount_data + + amount_data_begin = client_datas[-1] + amount_data_begin.update({'dnum':dnum}) + data_begin = """ + <ns2:ClientListing SequenceNumber="1" ClientsNbr="%(seq)s" DeclarantReference="%(dnum)s" + TurnOverSum="%(sum_turnover)s" VATAmountSum="%(sum_tax)s"> + """ % amount_data_begin + + data_end = """ + <ns2:Comment>%(comments)s</ns2:Comment> + </ns2:ClientListing> + </ns2:ClientListingConsignment> + """ % annual_listing_data + + data_file += data_begin + data_comp + data_client_info + data_end data['form']['msg'] = 'Save the File with '".xml"' extension.' - data['form']['file_save'] = base64.encodestring(data_file.encode('utf8')) + data['form']['name'] = 'vat_list.xml' + data['form']['file'] = base64.encodestring(data_file.encode('utf8')) return data['form'] states = { 'init': { 'actions': [], - 'result': {'type':'form', 'arch':form, 'fields':fields, 'state':[('end','Cancel'),('go','Create XML')]}, + 'result': {'type':'form', 'arch':form, 'fields':fields, 'state':[('end','Cancel'),('go','View Client')]}, }, 'go': { + 'actions': [_check_data,_get_partner], + 'result': {'type': 'form', 'arch':msg_form, 'fields':msg_fields, 'state':[('end','Close'),('create_file','Create XML')]} + }, + 'create_file': { 'actions': [_create_xml], - 'result': {'type':'form', 'arch':msg_form, 'fields':msg_fields, 'state':[('end','Ok')]}, + 'result': {'type': 'form', 'arch':msg_form_new, 'fields':msg_fields_new, 'state':[('end','Close')]} } - } wizard_vat('list.vat.detail')
_______________________________________________ Mailing list: https://launchpad.net/~openerp-dev-gtk Post to : [email protected] Unsubscribe : https://launchpad.net/~openerp-dev-gtk More help : https://help.launchpad.net/ListHelp
