private static DataRow LocalizaInsereAtividade(DataTable dtFornecedoresAtividade, IAtividade atividadesSecundaria) { var drs = dtFornecedoresAtividade.Select("codigo='" + atividadesSecundaria.code + "'"); var dr = dtFornecedoresAtividade.NewRow(); if (drs.Length == 0) { using (var banco = new AppDb()) { var strSql = @"insert into fornecedor_atividade (codigo, descricao) values (@codigo, @descricao); SELECT LAST_INSERT_ID();"; banco.AddParameter("@codigo", atividadesSecundaria.code); banco.AddParameter("@descricao", atividadesSecundaria.text); dr["id"] = Convert.ToInt32(banco.ExecuteScalar(strSql)); dr["codigo"] = atividadesSecundaria.code; dr["descricao"] = atividadesSecundaria.text; dtFornecedoresAtividade.Rows.Add(dr); return(dr); } } return(drs[0]); }
private static void InserirControle(int controle, string cnpj_cpf, string mensagem) { using (var banco = new AppDb()) { banco.AddParameter("@cnpj_cpf", cnpj_cpf); banco.AddParameter("@controle", controle); banco.AddParameter("@mensagem", mensagem); banco.ExecuteNonQuery(@"update fornecedor set controle=@controle, mensagem=@mensagem where cnpj_cpf=@cnpj_cpf;"); } }
//public static void ConsultarCNPJ() //{ // int totalErros = 0, totalAcertos = 0; // while (true) // { // DataTable dtFornecedores; // using (var banco = new Banco()) // { // dtFornecedores = banco.GetTable( // @"select cnpj_cpf, f.id, fi.id_fornecedor // 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 < '2017-01-01' // and fi.id_fornecedor is null // -- and ip_colaborador is null -- not in ('170509', '170510', '170511', '170512') // -- and controle is null // -- and controle = 1 // and controle <> 5 // -- and (f.mensagem is null or f.mensagem <> 'O número do CNPJ não foi localizado na Receita Federal') // order by 1 desc"); // } // if (dtFornecedores.Rows.Count == 0) break; // Console.WriteLine("Consultando CNPJ's Local: {0} itens.", dtFornecedores.Rows.Count); // foreach (DataRow item in dtFornecedores.Rows) // { // try // { // Receita.ConsultarCNPJ(item["cnpj_cpf"].ToString(), ref totalAcertos, ref totalErros); // } // catch (Exception e) // { // Console.WriteLine(e.Message); // System.Threading.Thread.Sleep(3600000); // } // } // }; //} public static void AtualizaFornecedorDoador() { using (var banco = new AppDb()) { var dt = banco.GetTable("select id, cnpj_cpf from fornecedor"); foreach (DataRow dr in dt.Rows) { banco.AddParameter("cnpj", dr["cnpj_cpf"]); var existe = banco.ExecuteScalar("select 1 from eleicao_doacao where raiz_cnpj_cpf_doador=@cnpj;"); if (existe != null) { banco.AddParameter("id", dr["id"]); banco.ExecuteNonQuery("update fornecedor set doador=1 where id=@id"); } } } }
public dynamic Consultar(int id) { using (AppDb banco = new AppDb()) { var strSql = @" SELECT d.id as id_cl_deputado , d.nome_parlamentar , d.nome_civil , d.nascimento , d.sexo , d.id_partido , p.sigla as sigla_partido , p.nome as nome_partido , d.id_estado , e.sigla as sigla_estado , e.nome as nome_estado , d.email , d.valor_total FROM cl_deputado d LEFT JOIN partido p on p.id = d.id_partido LEFT JOIN estado e on e.id = d.id_estado WHERE d.id = @id " ; banco.AddParameter("@id", id); using (var reader = banco.ExecuteReader(strSql)) { if (reader.Read()) { return(new { id_cl_deputado = reader["id_cl_deputado"], nome_parlamentar = reader["nome_parlamentar"].ToString(), nome_civil = reader["nome_civil"].ToString(), nascimento = Utils.FormataData(reader["nascimento"]), sexo = reader["sexo"].ToString(), id_partido = reader["id_partido"], sigla_estado = reader["sigla_estado"].ToString(), nome_partido = reader["nome_partido"].ToString(), id_estado = reader["id_estado"], sigla_partido = reader["sigla_partido"].ToString(), nome_estado = reader["nome_estado"].ToString(), email = reader["email"].ToString(), valor_total = Utils.FormataValor(reader["valor_total"]), }); } return(null); } } }
public dynamic MaioresNotas(int id) { using (AppDb banco = new AppDb()) { var strSql = new StringBuilder(); strSql.AppendLine(@" SELECT l1.id as id_cl_despesa , l1.id_fornecedor , pj.cnpj_cpf , pj.nome AS nome_fornecedor , l1.valor from ( SELECT l.id , l.valor , l.id_fornecedor FROM cl_despesa l WHERE l.id_cl_deputado = @id order by l.valor desc LIMIT 10 ) l1 LEFT JOIN fornecedor pj on pj.id = l1.id_fornecedor order by l1.valor desc " ); banco.AddParameter("@id", id); using (var reader = banco.ExecuteReader(strSql.ToString())) { List <dynamic> lstRetorno = new List <dynamic>(); while (reader.Read()) { lstRetorno.Add(new { id_cl_despesa = reader["id_cl_despesa"].ToString(), id_fornecedor = reader["id_fornecedor"].ToString(), cnpj_cpf = Utils.FormatCnpjCpf(reader["cnpj_cpf"].ToString()), nome_fornecedor = reader["nome_fornecedor"].ToString(), valor = Utils.FormataValor(reader["valor"]) }); } return(lstRetorno); } } }
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()); }
private static string CarregaDadosCsv(string file, int ano) { var sb = new StringBuilder(); string sResumoValores = string.Empty; int indice = 0; int GABINETE = indice++; int NOME_DEPUTADO = indice++; int CPF_DEPUTADO = indice++; int NOME_FORNECEDOR = indice++; int CNPJ_CPF_FORNECEDOR = indice++; int DATA = indice++; int DOCUMENTO = indice++; int VALOR = indice++; int CPF_FORNECEDOR = 0; int CLASSIFICACAO = 0; if (ano == 2018 || ano == 2019) { indice = 0; NOME_DEPUTADO = indice++; CPF_DEPUTADO = indice++; NOME_FORNECEDOR = indice++; CNPJ_CPF_FORNECEDOR = indice++; CPF_FORNECEDOR = indice++; DOCUMENTO = indice++; DATA = indice++; VALOR = indice++; CLASSIFICACAO = indice++; } int linhaAtual = 0; using (var banco = new AppDb()) { //var lstHash = new Dictionary<string, long>(); //using (var dReader = banco.ExecuteReader("select id, hash from cl_despesa where ano=" + ano)) //{ // while (dReader.Read()) // { // lstHash.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 between {0}01 and {0}12", ano))) //{ // if (dReader.Read()) // { // sResumoValores = string.Format("[{0}]={1}", dReader["itens"], Utils.FormataValor(dReader["valor"])); // } //} LimpaDespesaTemporaria(banco); using (var reader = new StreamReader(file, Encoding.GetEncoding("ISO-8859-1"))) { short count = 0; using (var csv = new CsvReader(reader, System.Globalization.CultureInfo.CreateSpecificCulture("pt-BR"))) { while (csv.Read()) { //Id = csv.GetField<int>("Id") //Name = csv.GetField("Name") count++; if (count == 1) { if (ano == 2013 || ano == 2014) { if ( (csv[GABINETE] != "Gabinete") || (csv[NOME_DEPUTADO] != "Nome") || (csv[CPF_DEPUTADO] != "CPF") || (csv[NOME_FORNECEDOR] != "EMPRESA (OU PROFISSIONAL)") || (csv[CNPJ_CPF_FORNECEDOR] != "CNPJ(ouCPF)") || (csv[DATA] != "Data de Emissão") || (csv[DOCUMENTO] != "NºDocumento") || (csv[VALOR] != "Valor") ) { throw new Exception("Mudança de integração detectada para o Câmara Legislativa do Distrito Federal"); } } else if (ano == 2015) { if ( (csv[GABINETE] != "GAB") || (csv[NOME_DEPUTADO] != "DEPUTADO") || (csv[CPF_DEPUTADO] != "CPF") || (csv[NOME_FORNECEDOR] != "LOCAL") || (csv[CNPJ_CPF_FORNECEDOR] != "CNPJ") || (csv[DATA] != "DATA") || (csv[DOCUMENTO] != "NUMERO") || (csv[VALOR] != "VALOR") ) { throw new Exception("Mudança de integração detectada para o Câmara Legislativa do Distrito Federal"); } } else if (ano == 2016 || ano == 2017) { if ( (csv[GABINETE] != "Gabinete") || (csv[NOME_DEPUTADO] != "Nome") || (csv[CPF_DEPUTADO] != "CPF") || (csv[NOME_FORNECEDOR].ToUpper() != "EMPRESA (OU PROFISSIONAL)") || (csv[CNPJ_CPF_FORNECEDOR] != "CNPJ (ou CPF)") || (csv[DATA] != "Data de Emissão") || (csv[DOCUMENTO] != "Nº Documento") || (csv[VALOR].Trim() != "Valor") ) { throw new Exception("Mudança de integração detectada para o Câmara Legislativa do Distrito Federal"); } } else if (ano == 2018) { if ( (csv[NOME_DEPUTADO] != "Nome do(a) Deputado(a)") || (csv[CPF_DEPUTADO] != "CPF do(a) Deputado(a)") || (csv[NOME_FORNECEDOR] != "Nome do Estabelecimento") || (csv[CNPJ_CPF_FORNECEDOR] != "CNPJ") || (csv[CPF_FORNECEDOR] != "CPF") || (csv[DOCUMENTO] != "No. do Recibo ou NF") || (csv[DATA] != "Data do Recibo") || (csv[VALOR] != "Valor") || (csv[CLASSIFICACAO] != "Classificação") ) { throw new Exception("Mudança de integração detectada para o Câmara Legislativa do Distrito Federal"); } } else if (ano == 2019) { if ( (csv[NOME_DEPUTADO] != "Nome do(a) Deputado(a)") || (csv[CPF_DEPUTADO] != "CPF do(a) Deputado(a)") || (csv[NOME_FORNECEDOR] != "Nome do Estabelecimento") || (csv[CNPJ_CPF_FORNECEDOR] != "CNPJ") || (csv[CPF_FORNECEDOR] != "CPF") || (csv[DOCUMENTO] != "N° do Recibo ou Nota Fiscal") || (csv[DATA] != "Data do Recibo/NF") || (csv[VALOR] != "Valor") || (csv[CLASSIFICACAO] != "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(csv[NOME_DEPUTADO])) { continue; //Linha vazia } banco.AddParameter("Nome", csv[NOME_DEPUTADO].Replace("Deputado", "").Replace("Deputada", "")); banco.AddParameter("CPF", !string.IsNullOrEmpty(csv[CPF_DEPUTADO]) ? Utils.RemoveCaracteresNaoNumericos(csv[CPF_DEPUTADO]) : ""); banco.AddParameter("Empresa", csv[NOME_FORNECEDOR].Trim().Replace("NÃO INFORMADO", "").Replace("DOCUMENTO DANIFICADO", "").Replace("não consta documento", "").Trim()); string cnpj_cpf = ""; if (ano < 2018) { if (!string.IsNullOrEmpty(csv[CNPJ_CPF_FORNECEDOR])) { cnpj_cpf = Utils.RemoveCaracteresNaoNumericos(csv[CNPJ_CPF_FORNECEDOR]); } } else { if (!string.IsNullOrEmpty(csv[CNPJ_CPF_FORNECEDOR])) { cnpj_cpf = Utils.RemoveCaracteresNaoNumericos(csv[CNPJ_CPF_FORNECEDOR]); } else if (!string.IsNullOrEmpty(csv[CPF_FORNECEDOR])) { cnpj_cpf = Utils.RemoveCaracteresNaoNumericos(csv[CPF_FORNECEDOR]); } } banco.AddParameter("CNPJ_CPF", cnpj_cpf); DateTime data; if (DateTime.TryParse(csv[DATA], out data)) { banco.AddParameter("DataEmissao", data); } else { // Quando a data não estiver difinida colocamos no feriado; banco.AddParameter("DataEmissao", new DateTime(ano, 1, 1)); } banco.AddParameter("Documento", csv[DOCUMENTO]); string valor = csv[VALOR].Replace(" .", "").Replace(" ", ""); // 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.Remove(hash)) //{ // banco.ClearParameters(); // continue; //} //banco.AddParameter("hash", hash); if (ano < 2018) { banco.AddParameter("TipoDespesa", DBNull.Value); } else { banco.AddParameter("TipoDespesa", csv[CLASSIFICACAO]); } if (string.IsNullOrEmpty(cnpj_cpf)) { banco.AddParameter("Observacao", csv[NOME_FORNECEDOR]); } else if (!Regex.IsMatch(cnpj_cpf, @"\d")) { banco.AddParameter("Observacao", cnpj_cpf + " - " + csv[NOME_FORNECEDOR]); } 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 ) VALUES ( @Nome, @CPF, @Empresa, @CNPJ_CPF, @DataEmissao, @Documento, @Valor, @TipoDespesa, @Observacao, @Ano )" ); } } if (++linhaAtual % 100 == 0) { Console.WriteLine(linhaAtual); } } //if (lstHash.Count == 0 && linhaAtual == 0) //{ // sb.AppendFormat("<p>Não há novos itens para importar! #2</p>"); // return sb.ToString(); //} //if (lstHash.Count > 0) //{ // string lstExcluir = lstHash.Aggregate("", (keyString, pair) => keyString + "," + pair.Value); // banco.ExecuteNonQuery(string.Format("delete from sf_despesa where id IN({0})", lstExcluir.Substring(1))); // Console.WriteLine("Registros para exluir: " + lstHash.Count); // sb.AppendFormat("<p>{0} registros excluidos</p>", lstHash.Count); //} sb.Append(ProcessarDespesasTemp(banco)); } //if (ano == DateTime.Now.Year) //{ // //AtualizaCampeoesGastos(); // //AtualizaResumoMensal(); // AtualizaValorTotal(); //} using (var banco = new AppDb()) { using (var dReader = banco.ExecuteReader(string.Format("select sum(valor) as valor, count(1) as itens from cl_despesa where ano_mes between {0}01 and {0}12", ano))) { 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 dynamic GastosMensaisPorAno(int id) { using (AppDb banco = new AppDb()) { var strSql = new StringBuilder(); strSql.AppendLine(@" SELECT YEAR(d.data) as ano, MONTH(d.data) as mes, SUM(d.valor) AS valor_total FROM cl_despesa d WHERE d.id_cl_deputado = @id group by YEAR(DATA), MONTH(data) order BY ano, mes " ); banco.AddParameter("@id", id); using (var reader = banco.ExecuteReader(strSql.ToString())) { List <dynamic> lstRetorno = new List <dynamic>(); var lstValoresMensais = new decimal?[12]; string anoControle = string.Empty; bool existeGastoNoAno = false; while (reader.Read()) { if (reader["ano"].ToString() != anoControle) { if (existeGastoNoAno) { lstRetorno.Add(new { name = anoControle.ToString(), data = lstValoresMensais }); lstValoresMensais = new decimal?[12]; existeGastoNoAno = false; } anoControle = reader["ano"].ToString(); } if (Convert.ToDecimal(reader["valor_total"]) > 0) { lstValoresMensais[Convert.ToInt32(reader["mes"]) - 1] = Convert.ToDecimal(reader["valor_total"]); existeGastoNoAno = true; } } if (existeGastoNoAno) { lstRetorno.Add(new { name = anoControle.ToString(), data = lstValoresMensais }); } return(lstRetorno); // Ex: [{"$id":"1","name":"2015","data":[null,18404.57,25607.82,29331.99,36839.82,24001.68,40811.97,33641.20,57391.30,60477.07,90448.58,13285.14]}] } } }
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()); }
private static string CarregaDadosCsv(string file, int ano) { var sb = new StringBuilder(); string sResumoValores = string.Empty; int indice = 0; int Verba = indice++; int Descricao = indice++; int Conta = indice++; int Favorecido = indice++; int Trecho = indice++; int Vencimento = indice++; int Valor = indice++; int linhaAtual = 0; using (var banco = new AppDb()) { //var lstHash = new Dictionary<string, long>(); //using (var dReader = banco.ExecuteReader("select id, hash from cl_despesa where ano=" + ano)) //{ // while (dReader.Read()) // { // lstHash.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 between {0}01 and {0}12", ano))) //{ // if (dReader.Read()) // { // sResumoValores = string.Format("[{0}]={1}", dReader["itens"], Utils.FormataValor(dReader["valor"])); // } //} //LimpaDespesaTemporaria(banco); using (var reader = new StreamReader(file, Encoding.GetEncoding("ISO-8859-1"))) { short count = 0; using (var csv = new CsvReader(reader, System.Globalization.CultureInfo.CreateSpecificCulture("pt-BR"))) { while (csv.Read()) { //Id = csv.GetField<int>("Id") //Name = csv.GetField("Name") count++; if (count == 1) { if ( (csv[Verba] != "Verba") || (csv[Descricao] != "Descrição") || (csv[Conta] != "Conta") || (csv[Favorecido] != "Favorecido") || (csv[Trecho] != "Trecho") || (csv[Vencimento] != "Vencimento") || (csv[Valor] != "Valor") ) { 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(csv[Verba])) { continue; //Linha vazia } banco.AddParameter("Verba", csv[Verba]); banco.AddParameter("Descricao", csv[Descricao]); banco.AddParameter("Conta", csv[Conta]); banco.AddParameter("Favorecido", csv[Favorecido]); banco.AddParameter("Trecho", csv[Trecho]); banco.AddParameter("Vencimento", DateTime.Parse(csv[Vencimento])); string valorTmp = csv[Valor]; // Valor 1.500.00 é na verdade 1.500,00 Regex myRegex = new Regex(@"\.(\d\d$)", RegexOptions.Singleline); if (myRegex.IsMatch(valorTmp)) { valorTmp = myRegex.Replace(valorTmp, @",$1"); } banco.AddParameter("Valor", !string.IsNullOrEmpty(valorTmp) ? (object)Convert.ToDouble(valorTmp) : 0); //string hash = banco.ParametersHash(); //if (lstHash.Remove(hash)) //{ // banco.ClearParameters(); // continue; //} //banco.AddParameter("hash", hash); banco.AddParameter("Ano", ano); banco.ExecuteNonQuery( @"INSERT INTO cl_despesa_temp ( tipo_verba, tipo_despesa, nome, favorecido, observacao, data_emissao, valor, ano ) VALUES ( @Verba, @Descricao, @Conta, @Favorecido, @Trecho, @Vencimento, @Valor, @Ano )" ); } } if (++linhaAtual % 100 == 0) { Console.WriteLine(linhaAtual); } } //if (lstHash.Count == 0 && linhaAtual == 0) //{ // sb.AppendFormat("<p>Não há novos itens para importar! #2</p>"); // return sb.ToString(); //} //if (lstHash.Count > 0) //{ // string lstExcluir = lstHash.Aggregate("", (keyString, pair) => keyString + "," + pair.Value); // banco.ExecuteNonQuery(string.Format("delete from sf_despesa where id IN({0})", lstExcluir.Substring(1))); // Console.WriteLine("Registros para exluir: " + lstHash.Count); // sb.AppendFormat("<p>{0} registros excluidos</p>", lstHash.Count); //} //sb.Append(ProcessarDespesasTemp(banco)); } //if (ano == DateTime.Now.Year) //{ // //AtualizaCampeoesGastos(); // //AtualizaResumoMensal(); // AtualizaValorTotal(); //} //using (var banco = new Banco()) //{ // using (var dReader = banco.ExecuteReader(string.Format("select sum(valor) as valor, count(1) as itens from cl_despesa where ano_mes between {0}01 and {0}12", ano))) // { // 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()); }