public List <ImportacaoPlanilha> ListaImportacoesConcluidasPorLoja()
{
var retornoLista = new List <ImportacaoPlanilha>();
var commandText = ImportacaoPlanilhaSQL.ListaImportacoesConcluidasPorLoja;
var linhas = contexto.ExecutaComandoComRetorno(commandText);
foreach (var row in linhas)
{
var retorno = new ImportacaoPlanilha
{
id = row["id"].ToLong(),
idTipoPlanilha = row["idTipoPlanilha"].ToLong(),
idUsuarioImportacao = row["idUsuarioImportacao"].ToLong(),
idLoja = row["idLoja"].ToLong(),
dataInicioProcessamento = row["dataInicioProcessamento"].ToDateTime(),
dataFimProcessamento = row["dataFimProcessamento"].ToDateTime(),
idStatus = row["idStatus"].ToLong(),
qtdImportacaoSucesso = row["qtdImportacaoSucesso"].ToInt32(),
qtdImportacaoIgnorada = row["qtdImportacaoIgnorada"].ToInt32(),
nomeArquivoOriginal = row["nomeArquivoOriginal"],
nomeArquivoProcessado = row["nomeArquivoProcessado"],
nomeArquivoErro = row["nomeArquivoErro"]
};
retornoLista.Add(retorno);
}
return(retornoLista);
}
public ImportacaoPlanilha ListarPorId(long id)
{
var retorno = new List <ImportacaoPlanilha>();
var commandText = ImportacaoPlanilhaSQL.ListarPorId;
var parametros = new Dictionary <string, object>
{
{ "id", id }
};
var linhas = contexto.ExecutaComandoComRetorno(commandText, parametros);
foreach (var row in linhas)
{
var tempImportacaoPlanilha = new ImportacaoPlanilha
{
id = row["id"].ToLong(),
idTipoPlanilha = row["idTipoPlanilha"].ToLong(),
idUsuarioImportacao = row["idUsuarioImportacao"].ToLong(),
idLoja = row["idLoja"].ToLong(),
dataInicioProcessamento = row["dataInicioProcessamento"].ToDateTime(),
dataFimProcessamento = row["dataFimProcessamento"].ToDateTime(),
idStatus = row["idStatus"].ToLong(),
qtdImportacaoSucesso = row["qtdImportacaoSucesso"].ToInt32(),
qtdImportacaoIgnorada = row["qtdImportacaoIgnorada"].ToInt32(),
nomeArquivoOriginal = row["nomeArquivoOriginal"],
nomeArquivoProcessado = row["nomeArquivoProcessado"],
nomeArquivoErro = row["nomeArquivoErro"]
};
retorno.Add(tempImportacaoPlanilha);
}
return(retorno.FirstOrDefault());
}
public long Salvar(ImportacaoPlanilha obj)
{
if (obj.id > 0)
{
Alterar(obj);
}
else
{
obj.id = Inserir(obj);
}
return(obj.id);
}
private long Inserir(ImportacaoPlanilha obj)
{
var commandText = ImportacaoPlanilhaSQL.Inserir;
var parametros = new Dictionary <string, object>
{
{ "idTipoPlanilha", obj.idTipoPlanilha },
{ "idUsuarioImportacao", obj.idUsuarioImportacao },
{ "idLoja", obj.idLoja },
{ "dataInicioProcessamento", obj.dataInicioProcessamento },
{ "dataFimProcessamento", obj.dataFimProcessamento },
{ "idStatus", obj.idStatus },
{ "qtdImportacaoSucesso", obj.qtdImportacaoSucesso },
{ "qtdImportacaoIgnorada", obj.qtdImportacaoIgnorada },
{ "nomeArquivoOriginal", obj.nomeArquivoOriginal },
{ "nomeArquivoProcessado", obj.nomeArquivoProcessado },
{ "nomeArquivoErro", obj.nomeArquivoErro }
};
return(contexto.ExecutaComando(commandText, parametros));
}
public ActionResult Upload(FormCollection formCollection)
{
if (Request != null)
{
var appSettings = ConfigurationManager.AppSettings;
string _diretorioPendente = string.Format("{0}{1}", "~/", appSettings["_diretorioPendente"]);
string _diretorioProcessado = string.Format("{0}{1}", "~/", appSettings["_diretorioProcessado"]);
string _diretorioLog = string.Format("{0}{1}", "~/", appSettings["_diretorioLog"]);
HttpPostedFileBase file = Request.Files["uploadArquivo"];
if (file != null)
{
if (file.ContentType == "application/vnd.ms-excel" || file.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
//Importa arquivo
ImportacaoPlanilha importacao = new ImportacaoPlanilha();
importacao.idStatus = 4;
importacao.idTipoPlanilha = 2;
importacao.idUsuarioImportacao = User.Identity.Name.Split('|')[0].Split(':')[1].ToInt32();
importacao.idLoja = User.Identity.Name.Split('|')[1].Split(':')[1].ToLong();
importacao._diretorioPendente = Server.MapPath(_diretorioPendente);
importacao._diretorioProcessado = Server.MapPath(_diretorioProcessado);
importacao._diretorioLog = Server.MapPath(_diretorioLog);
importacao.dataInicioProcessamento = DateTime.Now;
//realiza copia do arquivo importado
importacao.nomeArquivoOriginal = file.FileName;
importacao.nomeArquivoProcessado = file.FileName.GerarNomeUnico();
importacao.nomeArquivoErro = string.Format("ERRO_{0}.log", importacao.nomeArquivoProcessado);
file.SaveAs(string.Format("{0}{1}", importacao._diretorioPendente, importacao.nomeArquivoProcessado));
//loga no servidor que o arquivo foi copiado
_bll.SalvarImportacaoPendente(importacao);
}
}
}
return(RedirectToAction("Index"));
}
public List <ImportacaoPlanilha> ListarTodos(int?tipoPlanilha)
{
var retornoLista = new List <ImportacaoPlanilha>();
var commandText = ImportacaoPlanilhaSQL.Listar;
var linhas = contexto.ExecutaComandoComRetorno(commandText);
foreach (var row in linhas)
{
var retorno = new ImportacaoPlanilha
{
id = row["id"].ToLong(),
idTipoPlanilha = row["idTipoPlanilha"].ToLong(),
idUsuarioImportacao = row["idUsuarioImportacao"].ToLong(),
idLoja = row["idLoja"].ToLong(),
dataInicioProcessamento = row["dataInicioProcessamento"].ToDateTime(),
dataFimProcessamento = row["dataFimProcessamento"].ToDateTime(),
idStatus = row["idStatus"].ToLong(),
qtdImportacaoSucesso = row["qtdImportacaoSucesso"].ToInt32(),
qtdImportacaoIgnorada = row["qtdImportacaoIgnorada"].ToInt32(),
nomeArquivoOriginal = row["nomeArquivoOriginal"],
nomeArquivoProcessado = row["nomeArquivoProcessado"],
nomeArquivoErro = row["nomeArquivoErro"]
};
retornoLista.Add(retorno);
}
if (tipoPlanilha.HasValue)
{
return(retornoLista.Where(w => w.idTipoPlanilha.Equals(tipoPlanilha.Value)).ToList());
}
else
{
return(retornoLista);
}
}
public bool SalvarImportacaoPendente(ImportacaoPlanilha importacao)
{
importacao.id = _bllImportacaoPlanilha.Salvar(importacao);
return(importacao.id > 0);
}
public bool Importar(ImportacaoPlanilha importacao)
{
return(_dal.Importar(importacao));
}
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);
}
}
