public static void AtualizaValorTotal()
{
using (var banco = new AppDb())
{
banco.ExecuteNonQuery(@"
UPDATE cl_deputado dp SET
valor_total = (
SELECT SUM(ds.valor) FROM cl_despesa ds WHERE ds.id_cl_deputado = dp.id
);");
}
}
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;");
}
}
private static string InsereDespesaFinal(AppDb banco, string referencia)
{
if (string.IsNullOrEmpty(referencia))
{
referencia = "concat(year(d.data_emissao), LPAD(month(d.data_emissao), 2, '0')) AS ano_mes";
}
banco.ExecuteNonQuery(string.Format(@"
UPDATE cl_despesa_temp SET cnpj_cpf = NULL WHERE cnpj_cpf = '';
UPDATE cl_despesa_temp SET observacao = NULL WHERE observacao = 'não consta documento';
INSERT INTO cl_despesa (
id_cl_deputado,
id_cl_despesa_tipo,
id_fornecedor,
data,
ano_mes,
numero_documento,
valor,
observacao,
hash
)
SELECT
p.id AS id_cl_deputado,
dt.id AS id_cl_despesa_tipo,
IFNULL(f.id, 82624) AS id_fornecedor,
d.data_emissao,
{0},
d.documento AS numero_documento,
d.valor AS valor,
d.observacao AS observacao,
d.hash
FROM cl_despesa_temp d
inner join cl_deputado p on p.cpf = d.cpf
left join cl_despesa_tipo dt on dt.descricao = d.tipo_despesa
LEFT join fornecedor f on f.cnpj_cpf = d.cnpj_cpf
ORDER BY d.id;
" , referencia), 3600);
if (banco.Rows > 0)
{
return("<p>" + banco.Rows + "+ Despesa</p>");
}
return(string.Empty);
}
private static string InsereDeputadoFaltante(AppDb banco)
{
banco.ExecuteNonQuery(@"
INSERT INTO cl_deputado (nome_parlamentar, cpf, id_estado)
select distinct Nome, cpf, 53
from cl_despesa_temp
where cpf not in (
select cpf from cl_deputado
);
");
if (banco.Rows > 0)
{
return("<p>" + banco.Rows + "+ Deputado</p>");
}
return(string.Empty);
}
//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");
}
}
}
}
private static string InsereTipoDespesaFaltante(AppDb banco)
{
banco.ExecuteNonQuery(@"
INSERT INTO cl_despesa_tipo (descricao)
select distinct tipo_despesa
from cl_despesa_temp
where tipo_despesa is not null
and tipo_despesa not in (
select descricao from cl_despesa_tipo
);
");
if (banco.Rows > 0)
{
return("<p>" + banco.Rows + "+ Deputado</p>");
}
return(string.Empty);
}
//private static string InsereFornecedorFaltante(Banco banco)
// {
// banco.ExecuteNonQuery(@"
// INSERT INTO fornecedor (nome, cnpj_cpf)
// select MAX(dt.empresa), dt.cnpj_cpf
// from cl_despesa_temp dt
// left join fornecedor f on f.cnpj_cpf = dt.cnpj_cpf
// where dt.cnpj_cpf is not null
// and f.id is null
// -- and LENGTH(dt.cnpj_cpf) <= 14
// GROUP BY dt.cnpj_cpf;
//");
// if (banco.Rows > 0)
// {
// return "<p>" + banco.Rows + "+ Fornecedor</p>";
// }
// return string.Empty;
// }
private static string InsereDespesaFinal(AppDb banco)
{
banco.ExecuteNonQuery(@"
-- UPDATE cl_despesa_temp SET observacao = NULL WHERE observacao = 'não consta documento';
INSERT INTO cl_despesa (
id_cl_deputado,
id_cl_despesa_tipo,
id_fornecedor,
data,
ano_mes,
numero_documento,
valor,
favorecido,
observacao,
hash
)
SELECT
p.id AS id_cl_deputado,
dts.id_cl_despesa_tipo,
IFNULL(f.id, 82624) AS id_fornecedor,
d.data_emissao,
concat(year(d.data_emissao), LPAD(month(d.data_emissao), 2, '0')) AS ano_mes,
d.documento AS numero_documento,
d.valor AS valor,
d.favorecido,
d.observacao AS observacao,
null -- d.hash
FROM cl_despesa_temp d
inner join cl_deputado p on p.nome_parlamentar like d.nome and id_estado = 42
left join cl_despesa_tipo_sub dts on dts.descricao = d.tipo_despesa
LEFT join fornecedor f on f.cnpj_cpf = d.cnpj_cpf
ORDER BY d.id;
" , 3600);
if (banco.Rows > 0)
{
return("<p>" + banco.Rows + "+ Despesa</p>");
}
return(string.Empty);
}
private static string InsereFornecedorFaltante(AppDb banco)
{
banco.ExecuteNonQuery(@"
INSERT INTO fornecedor (nome, cnpj_cpf)
select MAX(dt.empresa), dt.cnpj_cpf
from cl_despesa_temp dt
left join fornecedor f on f.cnpj_cpf = dt.cnpj_cpf
where dt.cnpj_cpf is not null
and f.id is null
-- and LENGTH(dt.cnpj_cpf) <= 14
GROUP BY dt.cnpj_cpf;
" );
if (banco.Rows > 0)
{
return("<p>" + banco.Rows + "+ Fornecedor</p>");
}
return(string.Empty);
}
private static void LimpaDespesaTemporaria(AppDb banco)
{
banco.ExecuteNonQuery(@"
truncate table cl_despesa_temp;
" );
}
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 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());
}
