public bool Importar(ImportacaoPlanilha importacao)
{
string sheetName = "importar";
//var connectionString = "";
var pathArquivoPendente = string.Format("{0}{1}", importacao._diretorioPendente, importacao.nomeArquivoProcessado);
var pathArquivoProcessado = string.Format("{0}{1}", importacao._diretorioProcessado, importacao.nomeArquivoProcessado);
var pathArquivoLog = string.Format("{0}{1}", importacao._diretorioLog, importacao.nomeArquivoErro);
try
{
FileInfo file = new FileInfo(pathArquivoPendente);
using (var package = new ExcelPackage(file))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First(p => p.Name.ToLower() == sheetName.ToLower()); //abre a planilha chamada "importar"
var totalColuna = workSheet.Dimension.End.Column;
var totalLinha = workSheet.Dimension.End.Row;
//Importar conteúdo da planilha
int totalErros = 0;
int totalSucesso = 0;
int numeroLinha = 1;
//ignora primeira linha cabeçalho
for (int rowIterator = 2; rowIterator <= totalLinha; rowIterator++)
{
//user.FirstName = workSheet.Cells[rowIterator, 1].Value.ToString();
numeroLinha++;
int columnIterator = 0;
try
{
//Prossegue apenas se existir numero do terminal
if (workSheet.Cells[rowIterator, 3].Value != null)
{
if (!string.IsNullOrEmpty(workSheet.Cells[rowIterator, 3].Value.ToString()))
{
VendasViva obj = new VendasViva();
#region Mapeamento das colunas excel
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.dataVenda = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroPDV = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroLinha = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeVendedor = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomePlano = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomePacoteDados = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeTipo = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeTitular = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeDependente = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.dadosDependente = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroOs = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroICCD = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroContrato = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.valorVenda = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal();
}
#endregion
//Atualizar importacao
obj.importacaoPlanilha = importacao;
obj.idImportacaoPlanilha = importacao.id;
//Inserir no banco
this.Salvar(obj);
totalSucesso++;
}
else
{
totalErros++;
var log = string.Format("Linha: {0} - {1}", numeroLinha, "LINHA/TERMINAL não pode ser vazio");
this.GerarArquivoLogErro(pathArquivoLog, log);
}
}
else
{
totalErros++;
var log = string.Format("Linha: {0} - {1}", numeroLinha, "LINHA/TERMINAL não pode ser vazio");
this.GerarArquivoLogErro(pathArquivoLog, log);
}
}
catch (Exception ex)
{
totalErros++;
var log = string.Format("Linha: {0} - {1}", numeroLinha, ex.Message);
this.GerarArquivoLogErro(pathArquivoLog, log);
}
}
importacao.dataFimProcessamento = DateTime.Now;
importacao.qtdImportacaoSucesso = totalSucesso;
importacao.qtdImportacaoIgnorada = totalErros;
importacao.idStatus = 1;
ImportacaoPlanilhaDAL _dalImportacaoPlanilhaDAL = new ImportacaoPlanilhaDAL();
_dalImportacaoPlanilhaDAL.Salvar(importacao);
//remover os arquivos temporarios
if ((File.Exists(pathArquivoPendente)))
{
File.Move(pathArquivoPendente, pathArquivoProcessado);
}
}
return(true);
}
catch (Exception ex)
{
var mensagemErro = ex.Message;
if (mensagemErro.ToUpper().Contains("SEQUENCE CONTAINS NO MATCHING ELEMENT"))
{
mensagemErro = "Não foi encontrada nenhuma planilha com nome 'importar', favor verificar no EXCEL";
}
var log = string.Format("Arquivo: {0} - Erro: {1}", pathArquivoPendente, mensagemErro);
this.GerarArquivoLogErro(pathArquivoLog, log);
//Cancelar processamento
importacao.dataFimProcessamento = DateTime.Now;
importacao.idStatus = 3;
ImportacaoPlanilhaDAL _dalImportacaoPlanilhaDAL = new ImportacaoPlanilhaDAL();
_dalImportacaoPlanilhaDAL.Salvar(importacao);
//remover os arquivos temporarios
if ((File.Exists(pathArquivoPendente)))
{
File.Move(pathArquivoPendente, pathArquivoProcessado);
}
return(false);
}
}
public bool Importar(ImportacaoPlanilha importacao)
{
string sheetName = "importar";
//var connectionString = "";
var pathArquivoPendente = string.Format("{0}{1}", importacao._diretorioPendente, importacao.nomeArquivoProcessado);
var pathArquivoProcessado = string.Format("{0}{1}", importacao._diretorioProcessado, importacao.nomeArquivoProcessado);
var pathArquivoLog = string.Format("{0}{1}", importacao._diretorioLog, importacao.nomeArquivoErro);
try
{
FileInfo file = new FileInfo(pathArquivoPendente);
using (var package = new ExcelPackage(file))
{
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First(p => p.Name.ToLower() == sheetName.ToLower()); //abre a planilha chamada "importar"
var totalColuna = workSheet.Dimension.End.Column;
var totalLinha = workSheet.Dimension.End.Row;
//Importar conteúdo da planilha
int totalErros = 0;
int totalSucesso = 0;
int numeroLinha = 1;
//ignora primeira linha cabeçalho
for (int rowIterator = 2; rowIterator <= totalLinha; rowIterator++)
{
//user.FirstName = workSheet.Cells[rowIterator, 1].Value.ToString();
numeroLinha++;
int columnIterator = 0;
try
{
var numeroMSISDN = string.Empty;
var descricao = string.Empty;
if (workSheet.Cells[rowIterator, 16].Value != null)
{
numeroMSISDN = workSheet.Cells[rowIterator, 16].Value.ToString();
}
if (workSheet.Cells[rowIterator, 4].Value != null)
{
descricao = workSheet.Cells[rowIterator, 4].Value.ToString();
//caso no arquivo nao venha preenchido o MSISDN, pega o numero do telefone da coluna de Descrição
if (string.IsNullOrEmpty(numeroMSISDN))
{
//Se no campo descrição contem a palavra MSISDN, então captura o numero telefone
if (descricao.Contains("MSISDN"))
{
//Captura as ultimas 11 posicoes, que correspondem ao telefone
numeroMSISDN = descricao.Trim().Substring(descricao.Trim().IndexOf("MSISDN") + 7, 11);
}
}
}
//Prossegue apenas se existir (coluna P) numero do terminal ou se a (coluna D) Descrição esta preenchida
if (!string.IsNullOrEmpty(descricao) || !string.IsNullOrEmpty(numeroMSISDN))
{
PagamentoOperadora obj = new PagamentoOperadora();
#region Mapeamento das colunas excel
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeCicloPagamento = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.tipoIncentivo = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.tipoEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
obj.descricao = descricao;
//if (workSheet.Cells[rowIterator, columnIterator].Value != null)
// obj.descricao = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.categoria = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroPDV = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomePDV = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroSFIDPDVPagador = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomePDVPagador = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.dataEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.dataAtivacao = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.dataDesativacao = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.dataDocumento = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.motivo = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroContrato = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
obj.numeroMSISDN = numeroMSISDN;
//if (workSheet.Cells[rowIterator, columnIterator].Value != null)
// obj.numeroMSISDN = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroIMEI = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroICCID = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeAparelho = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeCampanha = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeCampanhaAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeOferta = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.numeroIDBundle = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeBundle = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.dataBundle = workSheet.Cells[rowIterator, columnIterator].Value.ToDateTime();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeRelacionamento = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeRelacionamentoAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.tipoOiTV = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.isCliente_string = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.tipoMigracao = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomePlanoEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomePlanoAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomePlanoGrupo = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeGrupoPlanoContabil = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeServicoEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeServicoAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeServicoGrupo = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeGrupoServicoContabil = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.totalVoiceTerminal = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.totalVoiceDias = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.nomeCartaoCredito = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.isPortabilidade_string = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.isOCT_string = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.quantidade = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.tipoPex = workSheet.Cells[rowIterator, columnIterator].Value.ToString();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.valor = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.valorAnterior = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.valorEvento = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.valorCalculado = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal();
}
columnIterator++;
if (workSheet.Cells[rowIterator, columnIterator].Value != null)
{
obj.valorPago = workSheet.Cells[rowIterator, columnIterator].Value.ToDecimal();
}
#endregion
//Atualizar importacao
obj.importacaoPlanilha = importacao;
obj.idImportacaoPlanilha = importacao.id;
//Inserir no banco
this.Salvar(obj);
totalSucesso++;
}
else
{
totalErros++;
var log = string.Format("Linha: {0} - {1}", numeroLinha, "Campo 'Descrição' ou 'MSISDN/Terminal' não podem ser vazios");
this.GerarArquivoLogErro(pathArquivoLog, log);
}
}
catch (Exception ex)
{
totalErros++;
var log = string.Format("Linha: {0} - {1}", numeroLinha, ex.Message);
this.GerarArquivoLogErro(pathArquivoLog, log);
}
}
importacao.dataFimProcessamento = DateTime.Now;
importacao.qtdImportacaoSucesso = totalSucesso;
importacao.qtdImportacaoIgnorada = totalErros;
importacao.idStatus = 1;
ImportacaoPlanilhaDAL _dalImportacaoPlanilhaDAL = new ImportacaoPlanilhaDAL();
_dalImportacaoPlanilhaDAL.Salvar(importacao);
//remover os arquivos temporarios
if ((File.Exists(pathArquivoPendente)))
{
File.Move(pathArquivoPendente, pathArquivoProcessado);
}
}
return(true);
}
catch (Exception ex)
{
var mensagemErro = ex.Message;
if (mensagemErro.ToUpper().Contains("SEQUENCE CONTAINS NO MATCHING ELEMENT"))
{
mensagemErro = "Não foi encontrada nenhuma planilha com nome 'importar', favor verificar no EXCEL";
}
var log = string.Format("Arquivo: {0} - Erro: {1}", pathArquivoPendente, mensagemErro);
this.GerarArquivoLogErro(pathArquivoLog, log);
//Cancelar processamento
importacao.dataFimProcessamento = DateTime.Now;
importacao.idStatus = 3;
ImportacaoPlanilhaDAL _dalImportacaoPlanilhaDAL = new ImportacaoPlanilhaDAL();
_dalImportacaoPlanilhaDAL.Salvar(importacao);
//remover os arquivos temporarios
if ((File.Exists(pathArquivoPendente)))
{
File.Move(pathArquivoPendente, pathArquivoProcessado);
}
return(false);
}
}
