private static string CarregaDadosXlsx(string file, int ano, int mes) { var sb = new StringBuilder(); string sResumoValores = string.Empty; int indice = 1; int NOME_DEPUTADO = indice++; int CPF_DEPUTADO = indice++; int NOME_FORNECEDOR = indice++; int CNPJ_CPF_FORNECEDOR = indice++; int CPF_FORNECEDOR = indice++; int DOCUMENTO = indice++; int DATA = indice++; int VALOR = indice++; int CLASSIFICACAO = indice++; int linhaAtual = 0; int inserido = 0; // Controle, estão vindo itens duplicados no XML var lstHash = new Dictionary <string, long>(); // Controle, lista para remover caso não constem no XML var lstHashExcluir = new Dictionary <string, long>(); using (var banco = new AppDb()) { using (var dReader = banco.ExecuteReader(string.Format("select id, hash from cl_despesa where ano_mes = {0}{1:00}", ano, mes))) { while (dReader.Read()) { if (!lstHash.ContainsKey(dReader["hash"].ToString())) { lstHash.Add(dReader["hash"].ToString(), Convert.ToInt64(dReader["id"])); lstHashExcluir.Add(dReader["hash"].ToString(), Convert.ToInt64(dReader["id"])); } } } using (var dReader = banco.ExecuteReader(string.Format("select sum(valor) as valor, count(1) as itens from cl_despesa where ano_mes = {0}{1:00}", ano, mes))) { if (dReader.Read()) { sResumoValores = string.Format("[{0}]={1}", dReader["itens"], Utils.FormataValor(dReader["valor"])); } } LimpaDespesaTemporaria(banco); using (var package = new ExcelPackage(new FileInfo(file))) { ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; for (int i = 1; i <= worksheet.Dimension.End.Row; i++) { if (i == 1) { if ( (worksheet.Cells[i, NOME_DEPUTADO].Value.ToString() != "Nome do(a) Deputado(a)") || (worksheet.Cells[i, CPF_DEPUTADO].Value.ToString() != "CPF do(a) Deputado(a)") || (worksheet.Cells[i, NOME_FORNECEDOR].Value.ToString() != "Nome do Estabelecimento") || (worksheet.Cells[i, CNPJ_CPF_FORNECEDOR].Value.ToString() != "CNPJ") || (worksheet.Cells[i, CPF_FORNECEDOR].Value.ToString() != "CPF") || (worksheet.Cells[i, DOCUMENTO].Value.ToString() != "N° do Recibo ou Nota Fiscal") || (worksheet.Cells[i, DATA].Value.ToString() != "Data do Recibo/NF") || (worksheet.Cells[i, VALOR].Value.ToString() != "Valor") || (worksheet.Cells[i, CLASSIFICACAO].Value.ToString() != "Classificação") ) { throw new Exception("Mudança de integração detectada para o Câmara Legislativa do Distrito Federal"); } // Pular linha de titulo continue; } if (string.IsNullOrEmpty((string)worksheet.Cells[i, NOME_DEPUTADO].Value)) { continue; //Linha vazia } banco.AddParameter("Nome", worksheet.Cells[i, NOME_DEPUTADO].Value.ToString().Replace("Deputado", "").Replace("Deputada", "")); banco.AddParameter("CPF", !string.IsNullOrEmpty(worksheet.Cells[i, CPF_DEPUTADO].Value.ToString()) ? Utils.RemoveCaracteresNaoNumericos(worksheet.Cells[i, CPF_DEPUTADO].Value.ToString()) : ""); banco.AddParameter("Empresa", worksheet.Cells[i, NOME_FORNECEDOR].Value.ToString().Trim().Replace("NÃO INFORMADO", "").Replace("DOCUMENTO DANIFICADO", "").Replace("não consta documento", "").Trim()); string cnpj_cpf = ""; if (!string.IsNullOrEmpty((string)worksheet.Cells[i, CNPJ_CPF_FORNECEDOR].Value)) { cnpj_cpf = Utils.RemoveCaracteresNaoNumericos(worksheet.Cells[i, CNPJ_CPF_FORNECEDOR].Value.ToString()); } else if (!string.IsNullOrEmpty((string)worksheet.Cells[i, CPF_FORNECEDOR].Value)) { cnpj_cpf = Utils.RemoveCaracteresNaoNumericos(worksheet.Cells[i, CPF_FORNECEDOR].Value.ToString()); } banco.AddParameter("CNPJ_CPF", cnpj_cpf); if (worksheet.Cells[i, DATA].Value != null) { banco.AddParameter("DataEmissao", (DateTime)worksheet.Cells[i, DATA].Value); } else { // Quando a data não estiver difinida colocamos no feriado; banco.AddParameter("DataEmissao", new DateTime(ano, 1, 1)); } banco.AddParameter("Documento", worksheet.Cells[i, DOCUMENTO].Value); string valor = worksheet.Cells[i, VALOR].Value.ToString(); // Valor 1.500.00 é na verdade 1.500,00 Regex myRegex = new Regex(@"\.(\d\d$)", RegexOptions.Singleline); if (myRegex.IsMatch(valor)) { valor = myRegex.Replace(valor, @",$1"); } try { banco.AddParameter("Valor", !string.IsNullOrEmpty(valor) ? (object)Convert.ToDouble(valor) : 0); } catch (Exception) { if (valor.EndsWith(".")) { valor = valor.Substring(0, valor.Length - 1).Trim(); } valor = valor.Replace(" ", ""); banco.AddParameter("Valor", !string.IsNullOrEmpty(valor) ? (object)Convert.ToDouble(valor) : 0); } string hash = banco.ParametersHash(); if (lstHash.ContainsKey(hash)) { lstHashExcluir.Remove(hash); banco.ClearParameters(); continue; } banco.AddParameter("hash", hash); banco.AddParameter("TipoDespesa", worksheet.Cells[i, CLASSIFICACAO].Value.ToString()); if (string.IsNullOrEmpty(cnpj_cpf)) { banco.AddParameter("Observacao", worksheet.Cells[i, NOME_FORNECEDOR].Value.ToString()); } else if (!Regex.IsMatch(cnpj_cpf, @"\d")) { banco.AddParameter("Observacao", cnpj_cpf + " - " + worksheet.Cells[i, NOME_FORNECEDOR].Value.ToString()); } else { banco.AddParameter("Observacao", DBNull.Value); } banco.AddParameter("Ano", ano); banco.ExecuteNonQuery( @"INSERT INTO cl_despesa_temp ( nome, cpf, empresa, cnpj_cpf, data_emissao, documento, valor, tipo_despesa, observacao, ano, hash ) VALUES ( @Nome, @CPF, @Empresa, @CNPJ_CPF, @DataEmissao, @Documento, @Valor, @TipoDespesa, @Observacao, @Ano, @hash )" ); inserido++; } } if (++linhaAtual % 100 == 0) { Console.WriteLine(linhaAtual); } sb.Append(ProcessarDespesasTemp(banco, string.Format("{0}{1:00}", ano, mes))); } //if (ano == DateTime.Now.Year) //{ // //AtualizaCampeoesGastos(); // //AtualizaResumoMensal(); // AtualizaValorTotal(); //} using (var banco = new AppDb()) { if (lstHashExcluir.Count > 0) { int skip = 0; while (true) { var lstHashExcluirTemp = lstHashExcluir.Skip(skip).Take(50); if (lstHashExcluirTemp.Count() == 0) { break; } string lstExcluir = lstHashExcluirTemp.Aggregate("", (keyString, pair) => keyString + "," + pair.Value); banco.ExecuteNonQuery(string.Format("delete from cf_despesa where id IN({0})", lstExcluir.Substring(1))); skip += 50; } Console.WriteLine("Registros para exluir: " + lstHashExcluir.Count); sb.AppendFormat("<p>{0} registros excluidos</p>", lstHashExcluir.Count); } else if (inserido == 0) { sb.Append("<p>Não há novos itens para importar! #2</p>"); return(sb.ToString()); } using (var dReader = banco.ExecuteReader(string.Format("select sum(valor) as valor, count(1) as itens from cl_despesa where ano_mes = {0}{1:00}", ano, mes))) { if (dReader.Read()) { sResumoValores += string.Format(" -> [{0}]={1}", dReader["itens"], Utils.FormataValor(dReader["valor"])); } } sb.AppendFormat("<p>Resumo atualização: {0}</p>", sResumoValores); } return(sb.ToString()); }
public static string ConsultarReceitaWS() { var sb = new StringBuilder(); var strInfoAdicional = new StringBuilder(); int RateLimit_Remaining = 3; int totalImportados = 0; DataTable dtFornecedores; DataTable dtFornecedoresAtividade; DataTable dtFornecedoresNatJu; using (var banco = new AppDb()) { dtFornecedores = banco.GetTable( @"select cnpj_cpf, f.id, fi.id_fornecedor, f.nome from fornecedor f left join fornecedor_info fi on f.id = fi.id_fornecedor where char_length(f.cnpj_cpf) = 14 and f.cnpj_cpf <> '00000000000000' -- and obtido_em < '2018-01-01' and fi.id_fornecedor is null -- and ip_colaborador not like '1805%' -- and fi.id_fornecedor is null -- and ip_colaborador is null -- not in ('170509', '170510', '170511', '170512') -- and controle is null -- and controle <> 0 -- and (f.mensagem is null or f.mensagem <> 'Uma tarefa foi cancelada.') -- and controle <> 5 and (controle is null or controle NOT IN (2, 3, 5)) order by f.id desc"); if (dtFornecedores.Rows.Count == 0) { Console.WriteLine("Não há fornecedores para consultar"); return("<p>Não há fornecedores para consultar</p>"); } dtFornecedoresAtividade = banco.GetTable("SELECT * FROM fornecedor_atividade;"); dtFornecedoresNatJu = banco.GetTable("SELECT * FROM fornecedor_natureza_juridica;"); } Console.WriteLine("Consultando CNPJ's Local: {0} itens.", dtFornecedores.Rows.Count); //var watch = System.Diagnostics.Stopwatch.StartNew(); int i = 0; foreach (DataRow item in dtFornecedores.Rows) { // the code that you want to measure comes here //watch.Stop(); //Console.WriteLine(watch.ElapsedMilliseconds); //watch.Restart(); i++; if (!validarCNPJ(item["cnpj_cpf"].ToString())) { InserirControle(3, item["cnpj_cpf"].ToString(), "CNPJ Invalido"); Console.WriteLine("CNPJ Invalido: " + item["cnpj_cpf"] + " - " + i); strInfoAdicional.Append("<p>Empresa invalida importada:" + item["id"].ToString() + " - " + item["cnpj_cpf"].ToString() + " - " + item["nome"].ToString() + "; Motivo: CNPJ Invalido</p>"); continue; } Console.WriteLine("Consultando CNPJ: " + item["cnpj_cpf"] + " - " + i); FornecedorInfo receita = null; try { using (HttpClient client = new HttpClient()) { //-------------------------------- string uriString; HttpResponseMessage response = null; uriString = string.Format("https://www.receitaws.com.br/v1/cnpj/{0}", item["cnpj_cpf"].ToString()); client.BaseAddress = new Uri(uriString); if (RateLimit_Remaining == 0) { Console.WriteLine("Rate limit atingido!"); System.Threading.Thread.Sleep(60000); } //Setar o Timeout do client quando é API BASICA client.Timeout = TimeSpan.FromMilliseconds(1000); response = client.GetAsync(string.Empty).Result; //var rateLimit = response.Headers.FirstOrDefault(x => x.Key == "X-RateLimit-Limit"); //var retryAfter = response.Headers.FirstOrDefault(x => x.Key == "RetryAfter"); // A API do ReceitaWS infelizmente não retorna um valor de retryAfter, então temos que usar um sleep num valor padrão. var rateLimit_Remaining = response.Headers.FirstOrDefault(x => x.Key == "X-RateLimit-Remaining"); if (rateLimit_Remaining.Value != null) { int temp; int.TryParse(rateLimit_Remaining.Value.First(), out temp); RateLimit_Remaining = temp; } else { RateLimit_Remaining = 3; } if (response.IsSuccessStatusCode) { var responseContent = response.Content; string responseString = responseContent.ReadAsStringAsync().Result; receita = (FornecedorInfo)JsonConvert.DeserializeObject(responseString, typeof(FornecedorInfo)); } else { InserirControle(1, item["cnpj_cpf"].ToString(), response.RequestMessage.ToString()); continue; } } } catch (Exception ex) { //if (receita == null) //{ // receita = new ReceitaWSData(); // receita.status = ReceitaWSData.STATUS_ERROR; // receita.ultima_atualizacao = DateTime.Now.ToString(DateMask_UltimaAtualizacao); // if (ex is AggregateException) // { // receita.message = "API Básica -- Gateway Time-out -- Exception: " + ex.InnerException.Message; // receita.ControleProcessamento = ReceitaWSData.ControleProcessamentoReceitaWS.Ignorar; // } // else // { // receita.message = ex.Message; // } //} InserirControle(1, item["cnpj_cpf"].ToString(), ex.GetBaseException().Message); continue; } string strSql2; using (var banco = new AppDb()) { //if (receita?.status == null) //{ // Thread.Sleep(60000); //1 MINUTO // continue; //} if (receita.status == "OK") { banco.BeginTransaction(); try { if (item["id_fornecedor"] != DBNull.Value) { banco.AddParameter("@id_fornecedor", item["id_fornecedor"]); banco.ExecuteNonQuery(@" delete from fornecedor_info where id_fornecedor=@id_fornecedor; delete from fornecedor_atividade_secundaria where id_fornecedor=@id_fornecedor; delete from fornecedor_socio where id_fornecedor=@id_fornecedor; "); } var strSql = @"insert into fornecedor_info( id_fornecedor, cnpj, tipo, nome, data_de_abertura, nome_fantasia, id_fornecedor_atividade_principal, id_fornecedor_natureza_juridica, logradouro, numero, complemento, cep, bairro, municipio, estado, endereco_eletronico, telefone, ente_federativo_responsavel, situacao_cadastral, data_da_situacao_cadastral, motivo_situacao_cadastral, situacao_especial, data_situacao_especial, capital_social, obtido_em, ip_colaborador ) values ( @id_fornecedor, @cnpj, @tipo, @nome, @data_de_abertura, @nome_fantasia, @id_fornecedor_atividade_principal, @id_fornecedor_natureza_juridica, @logradouro, @numero, @complemento, @cep, @bairro, @municipio, @estado, @endereco_eletronico, @telefone, @ente_federativo_responsavel, @situacao_cadastral, @data_da_situacao_cadastral, @motivo_situacao_cadastral, @situacao_especial, @data_situacao_especial, @capital_social, @obtido_em, @ip_colaborador )" ; banco.AddParameter("@id_fornecedor", item["id"]); banco.AddParameter("@cnpj", item["cnpj_cpf"]); banco.AddParameter("@tipo", receita.tipo); banco.AddParameter("@nome", receita.nome); banco.AddParameter("@data_de_abertura", ParseDate(receita.abertura)); banco.AddParameter("@nome_fantasia", receita.fantasia); if (receita.atividade_principal != null && receita.atividade_principal.Count > 0) { var drAt = LocalizaInsereAtividade(dtFornecedoresAtividade, receita.atividade_principal[0]); banco.AddParameter("@id_fornecedor_atividade_principal", drAt["id"]); } else { banco.AddParameter("@id_fornecedor_atividade_principal", DBNull.Value); } if (receita.atividade_principal != null) { var drNj = dtFornecedoresNatJu.Select("codigo='" + receita.natureza_juridica.Split(' ')[0] + "'"); banco.AddParameter("@id_fornecedor_natureza_juridica", drNj.Length > 0 ? drNj[0]["id"] : DBNull.Value); } else { banco.AddParameter("@id_fornecedor_natureza_juridica", DBNull.Value); } banco.AddParameter("@logradouro", receita.logradouro); banco.AddParameter("@numero", receita.numero); banco.AddParameter("@complemento", receita.complemento); banco.AddParameter("@cep", receita.cep); banco.AddParameter("@bairro", receita.bairro); banco.AddParameter("@municipio", receita.municipio); banco.AddParameter("@estado", receita.uf); banco.AddParameter("@endereco_eletronico", receita.email); banco.AddParameter("@telefone", receita.telefone); banco.AddParameter("@ente_federativo_responsavel", receita.efr); banco.AddParameter("@situacao_cadastral", receita.situacao); banco.AddParameter("@data_da_situacao_cadastral", ParseDate(receita.data_situacao)); banco.AddParameter("@motivo_situacao_cadastral", receita.motivo_situacao); banco.AddParameter("@situacao_especial", receita.situacao_especial); banco.AddParameter("@data_situacao_especial", ParseDate(receita.data_situacao_especial)); banco.AddParameter("@capital_social", ObterValor(receita.capital_social)); banco.AddParameter("@obtido_em", ParseDate(receita.ultima_atualizacao)); banco.AddParameter("@ip_colaborador", DateTime.Now.ToString("yyMMdd")); banco.ExecuteNonQuery(strSql); banco.AddParameter("@id", item["id"]); banco.AddParameter("@nome", receita.nome); banco.ExecuteNonQuery(@"update fornecedor set nome=@nome where id=@id"); strSql2 = @"insert into fornecedor_atividade_secundaria values (@id_fornecedor_info, @id_fornecedor_atividade)"; foreach (var atividadesSecundaria in receita.atividades_secundarias) { banco.ClearParameters(); try { var drAt = LocalizaInsereAtividade(dtFornecedoresAtividade, atividadesSecundaria); banco.AddParameter("@id_fornecedor_info", item["id"]); banco.AddParameter("@id_fornecedor_atividade", drAt["id"]); banco.ExecuteNonQuery(strSql2); } catch (MySqlException ex) { if (!ex.Message.Contains("Duplicate entry")) { throw; } } } strSql2 = @"insert into fornecedor_socio (id_fornecedor, nome, pais_origem, id_fornecedor_socio_qualificacao, nome_representante, id_fornecedor_socio_representante_qualificacao) values (@id_fornecedor, @nome, @pais_origem, @id_fornecedor_socio_qualificacao, @nome_representante, @id_fornecedor_socio_representante_qualificacao)"; foreach (var qsa in receita.qsa) { banco.ClearParameters(); banco.AddParameter("@id_fornecedor", item["id"]); banco.AddParameter("@nome", qsa.nome); banco.AddParameter("@pais_origem", qsa.pais_origem); banco.AddParameter("@id_fornecedor_socio_qualificacao", qsa.qual.Split('-')[0]); banco.AddParameter("@nome_representante", qsa.nome_rep_legal); banco.AddParameter("@id_fornecedor_socio_representante_qualificacao", !string.IsNullOrEmpty(qsa.qual_rep_legal) && qsa.qual_rep_legal.Contains("-") ? (object)qsa.qual_rep_legal.Split('-')[0] : DBNull.Value); banco.ExecuteNonQuery(strSql2); } totalImportados++; if (receita.situacao != "ATIVA") { strInfoAdicional.Append("<p>Empresa inativa importada:" + item["id"].ToString() + " - " + receita.cnpj + " - " + receita.nome + "</p>"); } banco.CommitTransaction(); InserirControle(0, item["cnpj_cpf"].ToString(), ""); Console.WriteLine("Atualizando CNPJ: " + item["cnpj_cpf"] + " - " + i); } catch (Exception) { banco.RollBackTransaction(); } } else { InserirControle(2, item["cnpj_cpf"].ToString(), receita.message); strInfoAdicional.Append("<p>Empresa invalida importada:" + item["id"].ToString() + " - " + receita.cnpj + " - " + item["nome"].ToString() + "; Motivo: " + receita.message + "</p>"); } } } using (var banco = new AppDb()) { banco.ExecuteNonQuery(@" update fornecedor_info set nome_fantasia=null where nome_fantasia = '' or nome_fantasia = '********'; update fornecedor_info set logradouro=null where logradouro = '' or logradouro = '********'; update fornecedor_info set numero=null where numero = '' or numero = '********'; update fornecedor_info set complemento=null where complemento = '' or complemento = '********'; update fornecedor_info set cep=null where cep = '' or cep = '********'; update fornecedor_info set bairro=null where bairro = '' or bairro = '********'; update fornecedor_info set municipio=null where municipio = '' or municipio = '********'; update fornecedor_info set estado=null where estado = '' or estado = '**'; update fornecedor_info set endereco_eletronico=null where endereco_eletronico = '' or endereco_eletronico = '********'; update fornecedor_info set telefone=null where telefone = '' or telefone = '********'; update fornecedor_info set ente_federativo_responsavel=null where ente_federativo_responsavel = '' or ente_federativo_responsavel = '********'; update fornecedor_info set motivo_situacao_cadastral=null where motivo_situacao_cadastral = '' or motivo_situacao_cadastral = '********'; update fornecedor_info set situacao_especial=null where situacao_especial = '' or situacao_especial = '********'; " ); } return(string.Format("<p>{0} de {1} fornecedores novos importados</p>", totalImportados, dtFornecedores.Rows.Count) + strInfoAdicional.ToString()); }