from openerp import models, fields, api, _
from openerp.osv import osv
import base64
from openerp.tools.translate import _
from openerp.addons.l10n_br_base.tools import fiscal
import csv
from datetime import datetime, date
from cStringIO import StringIO
import tempfile
import time
import xlrd
import re
class ea_import_client(models.Model):
_name = 'ea_import.client'
name = fields.Char('Descrição', size=256, required=True)
input_file = fields.Binary('Arquivo', required=False)
user_id = fields.Many2one('res.users', 'Inserido por', track_visibility='onchange')
header = fields.Boolean('Header')
state = fields.Selection(_TASK_STATE, 'Situacao', required=True,)
_defaults = {
'state': 'open',
'user_id': lambda obj, cr, uid, context: uid,
'name':'/',
}
def set_done(self, cr, uid, ids, context=None):
return self.write(cr, uid, ids, {'state':'done'}, context=context)
def set_open(self, cr, uid, ids, context=None):
return self.write(cr, uid, ids, {'state':'open'}, context=context)
def set_cancelled(self, cr, uid, ids, context=None):
return self.write(cr, uid, ids, {'state':'cancelled'}, context=context)
def import_to_xls(self, cr, uid, ids, context={}):
client_id = 0
client_obj = self.pool.get('res.partner')
context['result_ids'] = []
for chain in self.browse(cr, uid, ids, context=context):
file_path = tempfile.gettempdir()+'/file.xls'
data = chain.input_file
f = open(file_path,'wb')
f.write(data.decode('base64'))
f.close()
book = xlrd.open_workbook(file_path)
first_sheet = book.sheet_by_index(0)
parent_id = 0
for rownum in range(first_sheet.nrows):
rowValues = first_sheet.row_values(rownum)
if rownum > 0 and rownum < 1500:
anotacoes = ''
cobranca = {}
cnpj_cpf = rowValues[3]
parent_id = 0
# TRATANDO DATA
if rowValues[122]:
data_1 = rowValues[122]
data_reajuste = datetime(*xlrd.xldate_as_tuple(data_1, book.datemode))
# TRATANDO DATA
if rowValues[122]:
data_1 = rowValues[122]
data_reajuste = datetime(*xlrd.xldate_as_tuple(data_1, book.datemode))
if cnpj_cpf == '000.000.000-00':
cnpj_cpf = ''
empresa = False
if len(cnpj_cpf) > 14:
empresa = True
if empresa:
if not fiscal.validate_cnpj(cnpj_cpf):
anotacoes = ' CNPJ Invalido : %s \n' %(cnpj_cpf)
cnpj_cpf = ''
else:
if not fiscal.validate_cpf(cnpj_cpf):
anotacoes = ' CPF Invalido : %s \n' %(cnpj_cpf)
cnpj_cpf = ''
if cnpj_cpf == '000.000.000.00':
cnpj_cpf = ''
codigo = int(rowValues[0])
nome = u'%s' %(rowValues[2])
legalname = u'%s' %(rowValues[1])
#+ '-' + str(rowValues[14]) + '-' + str(rowValues[15])
ie = str(rowValues[4])
endereco = rowValues[8]
numero = rowValues[9]
bairro = rowValues[15]
cidade = rowValues[16]
if rowValues[18]:
uf = rowValues[18]
else:
uf = 'SP'
anotacoes = anotacoes + rowValues[12]
complemento = rowValues[18]
cep = rowValues[20]
fone = rowValues[21]
fone1 = rowValues[22]
fone2 = rowValues[10]
recado = rowValues[30]
profissao = rowValues[21]
email = rowValues[47]
endereco_cob = rowValues[31]
numero_cob = rowValues[57]
bairro_cob = rowValues[32]
cidade_cob = rowValues[35]
# buscar o codigo da cidade
uf_cob = rowValues[34]
# buscar o estado
cep_cob = rowValues[33]
emp_cob = rowValues[72]
dia_vencimento = rowValues[74]
#import pudb;pudb.set_trace()
if endereco_cob != '' and endereco_cob != endereco and numero_cob != numero:
# buscando a cidade
sql_req= """
SELECT p.id, p.name
FROM l10n_br_base_city p
WHERE
(UPPER(p.name) like '%s')
""" % (cidade_cob)
cr.execute(sql_req)
sql_res = cr.dictfetchone()
if sql_res:
cobranca['l10n_br_city_id'] = sql_res['id']
cobranca['street'] = endereco_cob
cobranca['number'] = numero_cob
cobranca['district'] = bairro_cob
if uf_cob == 'SP':
state = 79
elif uf_cob == 'MG':
state = 76
elif uf_cob == 'RJ':
state = 78
elif uf_cob == 'PR':
state = 80
elif uf_cob == 'RS':
state = 82
cobranca['state_id'] = state
cobranca['name'] = 'End. Faturamento'
cobranca['use_parent_address'] = False
cobranca['type'] = 'invoice'
# verificando se o cnpj ja esta cadastrado
nome = nome.replace('\'',' ')
if nome == '':
nome = legalname
if nome == ',':
nome = legalname
sql_req= """
SELECT p.id, p.ref
FROM res_partner p
WHERE
((p.cnpj_cpf = '%s')
or (p.name = '%s'))
and (p.ref = '%s')
""" % (cnpj_cpf, nome, str(int(codigo)))
cr.execute(sql_req)
sql_cnpj = cr.dictfetchone()
if sql_cnpj:
parent_id = sql_cnpj['id']
cod_ref = float(str(sql_cnpj['ref']))
if parent_id != 0 and sql_cnpj:
# limpo o cnpj pois, ja tem na empresa principal
cnpj_cpf = ''
if int(codigo) == int(cod_ref):
continue # estou inserindo o contato
if sql_cnpj:
# já inserido
if parent_id != 0 and int(codigo) == int(cod_ref):
continue
print " codigo: %s , nome : %s " %(codigo, rowValues[0])
# buscando a cidade
n_cidade = ''
sql_req= """
SELECT p.id, p.name
FROM l10n_br_base_city p
WHERE
(UPPER(p.name) like '%s')
""" % (cidade)
cr.execute(sql_req)
sql_res = cr.dictfetchone()
if sql_res:
cidade = sql_res['id']
n_cidade = sql_res['name']
else:
#raise osv.except_osv(u'Erro importação',u'CIDADA NÃO ENCONTRADA : %s' %(cidade))
cidade = 0
if cidade:
if uf == 'SP':
state = 79
elif uf == 'MG':
state = 76
elif uf == 'RJ':
state = 78
elif uf == 'PR':
state = 80
elif uf == 'RS':
state = 82
vals = {'name': nome,
'is_company': empresa,
'legal_name': legalname,
'street': endereco,
'number': numero,
'district': bairro,
'country_id': 32,
'state_id': state,
'city': sql_res['name'],
'phone': fone,
'mobile': fone1,
'email': email,
'zip': cep,
'ref': str(codigo),
'comment': anotacoes,
'property_payment_term': vencimento,
}
# 'child_ids': cobranca,
if cidade != 0:
vals['l10n_br_ciy_id'] = cidade
if parent_id > 0:
vals['parent_id'] = parent_id
if cnpj_cpf != '':
val = re.sub('[^0-9]', '', cnpj_cpf)
if len(val) == 14:
cnpj_cpf = "%s.%s.%s/%s-%s"\
% (val[0:2], val[2:5], val[5:8], val[8:12], val[12:14])
elif len(val) == 11:
cnpj_cpf = "%s.%s.%s-%s"\
% (val[0:3], val[3:6], val[6:9], val[9:11])
else:
raise Warning(_(u'Verifique o CNPJ/CPF'))
vals['cnpj_cpf'] = cnpj_cpf
if cep:
val = re.sub('[^0-9]', '', cep)
if len(val) == 8:
cep = "%s-%s" % (val[0:5], val[5:8])
vals['zip'] = cep
if ie:
if ie == 'ISENTA':
ie = 'ISENTO'
if uf == 'SP':
if not fiscal.validate_ie_sp(ie):
anotacoes = anotacoes + '\n' + ' IE Invalido : %s \n' %(ie)
ie = ''
if uf == 'MG':
if ie != 'ISENTO':
# não descobri a causa mas quando passo o IE de MG com máscara
# valida, mas quando vai incluir dá erro de IE inválido
ie = re.sub('[^0-9]', '', ie)
if not fiscal.validate_ie_mg(ie):
anotacoes = anotacoes + '\n' + ' IE Invalido : %s \n' %(ie)
ie = ''
if uf == 'RS':
# if not fiscal.validate_ie_rs(ie):
anotacoes = anotacoes + '\n' + ' IE RS: %s \n' %(ie)
ie = ''
if ie != '':
vals['inscr_est'] = ie
print vals
#import pudb;pudb.set_trace()
client_id = client_obj.create(cr, uid, vals, context=None)
cat = {'category_id': [(4,empresa_cob)]
}
client_obj.write(cr, uid, client_id, cat, context=context)
if cobranca:
cobranca_cad = {'child_ids':[(0,0,cobranca)]}
client_obj.write(cr, uid, client_id, cobranca_cad, context=context)
#vals['child_ids'] = cobranca
c_ids = client_obj.browse(cr, uid, [client_id], context=None)
if not c_ids.l10n_br_city_id and n_cidade:
city_obj = self.pool.get('l10n_br_base.city')
city_id = city_obj.search(cr, uid, [('name','=', n_cidade)], context=None)
if city_id:
client_obj.write(cr, uid, client_id, {'l10n_br_city_id': city_id[0]}, context=context)
return client_id
ea_import_client_view.xml
<?xml version="1.0"?>
<openerp>
<data>
<record model="ir.ui.view" id="ea_import_client_tree">
<field name="name">ea_import.client.tree</field>
<field name="model">ea_import.client</field>
<field name="type">tree</field>
<field name="arch" type="xml">
<tree string="Importar Clientes">
<field name="name"/>
<field name="input_file"/>
<field name="state"/>
<field name="user_id" colspan="2"/>
</tree>
</field>
</record>
<record model="ir.ui.view" id="ea_import_client_form">
<field name="name">ea_import.client.form</field>
<field name="model">ea_import.client</field>
<field name="arch" type="xml">
<form string="Importar Clientes" col="8">
<group>
<label for="name" string="Planilha"/>
<field name="name" colspan="2"/>
<field name="input_file" colspan="2"/>
<field name="header" colspan="2" invisible="1"/>
<field name="user_id" colspan="2" invisible="1"/>
<button string="Import Data" type="object" name="import_to_db" icon="terp-stock_effects-object-colorize" colspan="2"/>
<field name="state" widget="statusbar" statusbar_visible="open,close" statusbar_colors='{"pending":"blue"}' readonly="1" />
</group>
<group>
<button name="set_done" string="Importado" type="object" states="open" groups="base.group_user"/>
<button name="set_open" string="Re-enviar" type="object" states="close,cancelled" groups="base.group_user"/>
<button name="set_cancelled" string="Cancelar" type="object" states="open" groups="base.group_user"/>
</group>
</form>
</field>
</record>
<record model="ir.ui.view" id="ea_view_client_form">
<field name="name">Import Client</field>
<field name="model">ea_import.client</field>
<field name="arch" type="xml">
<form string="Importar Clientes" version="7.0">
<header>
<button name="set_done" string="Importado" type="object" states="open" groups="base.group_user" colspan="10"/>
<button name="set_open" string="Re-ativar" type="object" states="done,cancelled" groups="base.group_user" colspan="10"/>
<button name="set_cancelled" string="Suspender" type="object" states="open" groups="base.group_user"/>
<field name="state" widget="statusbar" statusbar_visible="open,done" statusbar_colors='{"pending":"blue"}' readonly="1" />
</header>
<sheet>
<field name="name" colspan="2"/>
<field name="input_file" colspan="2"/>
<field name="header" colspan="2" invisible="1"/>
<field name="user_id" colspan="2" invisible="1"/>
<button string="Importar Planilha" type="object" name="import_to_xls" icon="terp-stock_effects-object-colorize" colspan="2"/>
<button string="Criar Contratos" type="object" name="criar_contratos" icon="terp-stock_effects-object-colorize" colspan="2"/>
</sheet>
</form>
</field>
</record>
<record model="ir.actions.act_window" id="ea_import_client_action">
<field name="name">Importar Clientes</field>
<field name="res_model">ea_import.client</field>
<field name="view_mode">tree,form</field>
<field name="view_type">form</field>
<field name="view_id" ref="ea_view_client_form"/>
<field name="target">current</field>
</record>
<menuitem name="Importar Clientes" id="ea_import_client_menu" parent="purchase.menu_purchase_config_purchase" action="ea_import_client_action"/>
</data>
</openerp>
ea_import_client_view.xml
<?xml version="1.0"?>
<openerp>
<data>
<record model="ir.ui.view" id="ea_import_client_tree">
<field name="name">ea_import.client.tree</field>
<field name="model">ea_import.client</field>
<field name="type">tree</field>
<field name="arch" type="xml">
<tree string="Importar Clientes">
<field name="name"/>
<field name="input_file"/>
<field name="state"/>
<field name="user_id" colspan="2"/>
</tree>
</field>
</record>
<record model="ir.ui.view" id="ea_import_client_form">
<field name="name">ea_import.client.form</field>
<field name="model">ea_import.client</field>
<field name="arch" type="xml">
<form string="Importar Clientes" col="8">
<group>
<label for="name" string="Planilha"/>
<field name="name" colspan="2"/>
<field name="input_file" colspan="2"/>
<field name="header" colspan="2" invisible="1"/>
<field name="user_id" colspan="2" invisible="1"/>
<button string="Import Data" type="object" name="import_to_db" icon="terp-stock_effects-object-colorize" colspan="2"/>
<field name="state" widget="statusbar" statusbar_visible="open,close" statusbar_colors='{"pending":"blue"}' readonly="1" />
</group>
<group>
<button name="set_done" string="Importado" type="object" states="open" groups="base.group_user"/>
<button name="set_open" string="Re-enviar" type="object" states="close,cancelled" groups="base.group_user"/>
<button name="set_cancelled" string="Cancelar" type="object" states="open" groups="base.group_user"/>
</group>
</form>
</field>
</record>
<record model="ir.ui.view" id="ea_view_client_form">
<field name="name">Import Client</field>
<field name="model">ea_import.client</field>
<field name="arch" type="xml">
<form string="Importar Clientes" version="7.0">
<header>
<button name="set_done" string="Importado" type="object" states="open" groups="base.group_user" colspan="10"/>
<button name="set_open" string="Re-ativar" type="object" states="done,cancelled" groups="base.group_user" colspan="10"/>
<button name="set_cancelled" string="Suspender" type="object" states="open" groups="base.group_user"/>
<field name="state" widget="statusbar" statusbar_visible="open,done" statusbar_colors='{"pending":"blue"}' readonly="1" />
</header>
<sheet>
<field name="name" colspan="2"/>
<field name="input_file" colspan="2"/>
<field name="header" colspan="2" invisible="1"/>
<field name="user_id" colspan="2" invisible="1"/>
<button string="Importar Planilha" type="object" name="import_to_xls" icon="terp-stock_effects-object-colorize" colspan="2"/>
<button string="Criar Contratos" type="object" name="criar_contratos" icon="terp-stock_effects-object-colorize" colspan="2"/>
</sheet>
</form>
</field>
</record>
<record model="ir.actions.act_window" id="ea_import_client_action">
<field name="name">Importar Clientes</field>
<field name="res_model">ea_import.client</field>
<field name="view_mode">tree,form</field>
<field name="view_type">form</field>
<field name="view_id" ref="ea_view_client_form"/>
<field name="target">current</field>
</record>
<menuitem name="Importar Clientes" id="ea_import_client_menu" parent="purchase.menu_purchase_config_purchase" action="ea_import_client_action"/>
</data>
</openerp>
