502 lines
22 KiB
C#
502 lines
22 KiB
C#
using Microsoft.AspNetCore.Builder;
|
|
using Microsoft.Extensions.DependencyInjection;
|
|
using Microsoft.Extensions.Logging;
|
|
using TarifasANEEL.Services;
|
|
using TarifasANEEL.Utilities;
|
|
using System.Data.OleDb;
|
|
using System.Text.Json.Nodes;
|
|
|
|
namespace TarifasANEEL
|
|
{
|
|
public class Program
|
|
{
|
|
private static async Task Main(string[] args)
|
|
{
|
|
try
|
|
{
|
|
// Configuração inicial
|
|
ConfigurationService.LoadConfiguration();
|
|
|
|
// Inicialização do aplicativo
|
|
var builder = WebApplication.CreateBuilder(args);
|
|
var app = builder.Build();
|
|
var logger = app.Services.GetRequiredService<ILogger<Program>>();
|
|
|
|
// Conexão ao banco de dados
|
|
using var resourcesConnection = DatabaseService.CreateConnection(ConfigurationService.ResourcesDbConnectionString);
|
|
resourcesConnection.Open();
|
|
|
|
if (!DatabaseService.TableExists(resourcesConnection, ConfigurationService.ResourcesTableName))
|
|
{
|
|
throw new Exception($"Falha ao acessar [{ConfigurationService.ResourcesTableName}]");
|
|
}
|
|
|
|
using var dataConnection = DatabaseService.CreateConnection(ConfigurationService.MainDbConnectionString);
|
|
dataConnection.Open();
|
|
DatabaseService.LoadDistribuidoras(dataConnection);
|
|
|
|
using var command = new OleDbCommand(
|
|
$"SELECT {ConfigurationService.ResourcesTableName}.resource_id, * " +
|
|
$"FROM {ConfigurationService.ResourcesTableName} " +
|
|
$"WHERE ((Not ({ConfigurationService.ResourcesTableName}.resource_id)=\"\"))" +
|
|
$"ORDER BY {ConfigurationService.ResourcesTableName}.Ano DESC;",
|
|
resourcesConnection);
|
|
|
|
using var reader = command.ExecuteReader() ?? throw new Exception("Falha ao acessar os recursos");
|
|
int iRows = 0;
|
|
var tasks = new List<Task>();
|
|
while (reader.Read())
|
|
{
|
|
// Create a copy of the current row since reader will move to next row
|
|
var readerValues = new Dictionary<string, object>();
|
|
for (int i = 0; i < reader.FieldCount; i++)
|
|
{
|
|
readerValues[reader.GetName(i)] = reader.GetValue(i);
|
|
}
|
|
|
|
//tasks.Add(ProcessResourceAsync(readerValues, resourcesConnection, dataConnection));
|
|
await ProcessResourceAsync(readerValues, resourcesConnection, dataConnection);
|
|
Console.WriteLine(iRows);
|
|
iRows++;
|
|
}
|
|
|
|
await Task.WhenAll(tasks);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
Console.WriteLine($"Erro: {ex.Message}");
|
|
}
|
|
finally
|
|
{
|
|
TimerHelper.SetTimer(ConfigurationService.TimerDurationToClose);
|
|
}
|
|
}
|
|
|
|
private static async Task ProcessResourceAsync(Dictionary<string, object> readerValues, OleDbConnection resourcesConnection, OleDbConnection dataConnection)
|
|
{
|
|
string tableName = $"Tarifas_{readerValues["Ano"]}";
|
|
if (tableName == "Tarifas_2021") { int test1 = 0; }
|
|
int offset = DatabaseService.GetOffsetForTable(
|
|
tableName,
|
|
(string)readerValues["Ano"] == "All",
|
|
"_id",
|
|
resourcesConnection);
|
|
|
|
offset = 0;
|
|
|
|
string apiUrl = ApiService.BuildUrl(
|
|
ConfigurationService.ApiDadosAbertosUrl,
|
|
readerValues,
|
|
"Ano");
|
|
|
|
bool hasNextPage = true;
|
|
while (hasNextPage)
|
|
{
|
|
JsonNode rootObject = await ApiService.PullDataAsync($"{apiUrl}offset={offset}");
|
|
JsonNode records = rootObject["result"]?["records"] ?? new JsonArray();
|
|
|
|
if (records.AsArray().Count > 0)
|
|
{
|
|
var formattedData = TarifaHelper.FormatData(records);
|
|
DatabaseService.AddDataToTable(dataConnection, formattedData, ConfigurationService.DataTableName, (string)readerValues["Ano"]);
|
|
offset += records.AsArray().Count;
|
|
}
|
|
else
|
|
{
|
|
hasNextPage = false;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
//using System.Data;
|
|
//using System.Data.OleDb;
|
|
//using System.Text.Json;
|
|
//using System.Text.Json.Nodes;
|
|
//using System.Timers;
|
|
//using Microsoft.AspNetCore.Builder;
|
|
//using Microsoft.Extensions.Configuration;
|
|
//using Microsoft.Extensions.DependencyInjection;
|
|
//using Microsoft.Extensions.Logging;
|
|
|
|
//namespace TarifasANEEL.App
|
|
//{
|
|
// public class Program
|
|
// {
|
|
// static DateTime dtTimerEndTime;
|
|
// static string? sApiDadosAbertosUrl;
|
|
// static string? sResourcesDbPath;
|
|
// static string? sDbPath;
|
|
// static string? sResourcesTableName;
|
|
// static string? sResourcesDbConnection;
|
|
// static string? sDbConnection;
|
|
// static DataTable? dtDistribuidoras = new();
|
|
// static string? sTimerDurationToClose;
|
|
|
|
// static void SetConfig()
|
|
// {
|
|
// IConfigurationRoot config;
|
|
// config = new ConfigurationBuilder()
|
|
// .AddUserSecrets<Program>()
|
|
// .Build();
|
|
|
|
// sApiDadosAbertosUrl = config["sApiDadosAbertosUrl"]!;
|
|
// sResourcesDbPath = config["sResourcesDbPath"]!;
|
|
// sDbPath = config["sDbPath"]!;
|
|
// sResourcesTableName = config["sResourcesTableName"]!;
|
|
// sTimerDurationToClose = config["sTimerDurationToClose"];
|
|
// sResourcesDbConnection = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={sResourcesDbPath};Persist Security Info=False;";
|
|
// sDbConnection = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={sDbPath};Jet OLEDB:Database Password=gds21";
|
|
|
|
// if (config.AsEnumerable().Where(c => c.Value == "" ^ c.Value == null).ToList().Any()) { throw new Exception("Falha ao acessar o arquivo de configuração"); }
|
|
// }
|
|
// static void SetDistTable(OleDbConnection conn)
|
|
// {
|
|
// if (!TabelaExiste(conn, "Distribuidoras_tarifas") ^ !TabelaExiste(conn, "Distribuidoras_geral"))
|
|
// {
|
|
// throw new Exception($"Falha ao acessar [Distribuidoras_tarifas] ou [Distribuidoras_geral]");
|
|
// }
|
|
|
|
// string sQueryDist = $"SELECT *\r\nFROM Distribuidoras_geral\r\nWHERE ((Not (Distribuidoras_geral.CNPJ)=\"\"));";
|
|
// using OleDbCommand cmd = new(sQueryDist, conn);
|
|
// using OleDbDataReader distReader = cmd.ExecuteReader() ?? throw new Exception();
|
|
|
|
// dtDistribuidoras!.Load(distReader);
|
|
// }
|
|
// private static bool TabelaExiste(OleDbConnection connection, string TableName)
|
|
// {
|
|
// try
|
|
// {
|
|
// using OleDbCommand command = new(@$"SELECT COUNT(*) FROM {TableName}", connection);
|
|
// command.ExecuteScalar();
|
|
// return true;
|
|
// }
|
|
// catch (OleDbException)
|
|
// {
|
|
// return false;
|
|
// }
|
|
// }
|
|
// public static int GetCountIdFromTable(string sTableName, string sIdColumnName, OleDbConnection connection)
|
|
// {
|
|
// int iOffset = 0;
|
|
|
|
// if (TabelaExiste(connection, sTableName))
|
|
// {
|
|
// using OleDbCommand cmd = new($"SELECT Count([{sIdColumnName}]) FROM {sTableName}", connection);
|
|
// var test = cmd.ExecuteScalar() ?? "0";
|
|
// if (test.ToString() != "")
|
|
// {
|
|
// iOffset = int.Parse(test!.ToString() ?? "0");
|
|
// }
|
|
// }
|
|
|
|
// return iOffset;
|
|
// }
|
|
// public static int GetLastIdFromTable(string sTableName, string sIdColumnName, OleDbConnection connection)
|
|
// {
|
|
// int iOffset = 0;
|
|
|
|
// if (TabelaExiste(connection, sTableName))
|
|
// {
|
|
// using OleDbCommand cmd = new($"SELECT Last([{sIdColumnName}]) FROM {sTableName}", connection);
|
|
// var test = cmd.ExecuteScalar() ?? "0";
|
|
// if (test.ToString() != "")
|
|
// {
|
|
// iOffset = int.Parse(test!.ToString() ?? "0");
|
|
// }
|
|
// }
|
|
|
|
// return iOffset;
|
|
// }
|
|
// public static string BuildUrl(string sBaseUrl, OleDbDataReader reader, string sReaderColumn)
|
|
// {
|
|
// string sWebString = $@"{sBaseUrl}?";
|
|
|
|
// for (int iCol = 0; iCol < reader.FieldCount; iCol++)
|
|
// {
|
|
// if (reader.GetName(iCol) != sReaderColumn)
|
|
// {
|
|
// if (reader[iCol].ToString() != "")
|
|
// {
|
|
// sWebString = $@"{sWebString}{reader.GetName(iCol)}={reader[iCol]}&";
|
|
// }
|
|
// }
|
|
// }
|
|
|
|
// return sWebString;
|
|
// }
|
|
// private static async Task<JsonNode> PullDataAsync(string sURL)
|
|
// {
|
|
// Console.WriteLine("Carregando dados da API");
|
|
// var client = new HttpClient();
|
|
// var request = new HttpRequestMessage(HttpMethod.Get, sURL);
|
|
// var response = await client.SendAsync(request);
|
|
// response.EnsureSuccessStatusCode();
|
|
|
|
// string sResponse = await response.Content.ReadAsStringAsync();
|
|
// JsonNode? rootObject = JsonSerializer.Deserialize<JsonNode?>(sResponse.ToString()
|
|
// .Replace(@"""NomPostoTarifario"":""Não se aplica""", @"""NomPostoTarifario"":""N""")
|
|
// .Replace(@"""DscPostoTarifario"":""Não se aplica""", @"""DscPostoTarifario"":""N""")
|
|
// .Replace(@"""DscSubClasseConsumidor"":""Residencial""", @"""DscSubClasseConsumidor"":""""")
|
|
// .Replace(@"""DscClasseConsumidor"":""Iluminação pública""", @"""DscClasseConsumidor"":""""")
|
|
// .Replace(@"""DscModalidadeTarifaria"":""Convencional pré-pagamento""", @"""DscModalidadeTarifaria"":""pré-pagamento""")
|
|
// .Replace(@"""Não se aplica""", @""""""))!;
|
|
// Console.WriteLine("Dados carregados.");
|
|
// return rootObject;
|
|
// }
|
|
// private static void CriarTabela(OleDbConnection connection, string TableName, string columnNames)
|
|
// {
|
|
// try
|
|
// {
|
|
// using OleDbCommand command = new(@$"CREATE TABLE {TableName} ({columnNames})", connection);
|
|
// command.ExecuteNonQuery();
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// Console.WriteLine($"Ocorreu um erro: {ex.Message}");
|
|
// }
|
|
// }
|
|
// private static void AdicionarDadosAoBanco(OleDbConnection connection, JsonNode records, string TableName)
|
|
// {
|
|
// try
|
|
// {
|
|
// string stringColumns = string.Join(", \n", records[0]!.AsObject().AsEnumerable().Select(d => "@" + d.Key).ToList());
|
|
// string createColumns = string.Join(", \n", records[0]!.AsObject().AsEnumerable().Select(d => d.Key + " TEXT").ToList());
|
|
|
|
// if (!TabelaExiste(connection, TableName))
|
|
// {
|
|
// CriarTabela(connection, TableName, createColumns);
|
|
// }
|
|
|
|
// using OleDbTransaction transaction = connection.BeginTransaction();
|
|
// try
|
|
// {
|
|
// for (int iRow = 0; iRow < records!.AsArray().Count; iRow++)
|
|
// {
|
|
// var record = records[iRow];
|
|
|
|
// using OleDbCommand command = new(@$"INSERT INTO {TableName} VALUES ({stringColumns})", connection, transaction);
|
|
|
|
// foreach (var child in record!.AsObject())
|
|
// {
|
|
// command.Parameters.AddWithValue(child.Key, (child.Value ?? "")!.ToString());
|
|
// }
|
|
|
|
// command.ExecuteNonQuery();
|
|
// }
|
|
|
|
// transaction.Commit();
|
|
// Console.WriteLine($"{records!.AsArray().Count} dados adicionados com sucesso à [{TableName}] - Last _id {records[records!.AsArray().Count - 1]!["_id"]}");
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// transaction.Rollback();
|
|
// Console.WriteLine($"Erro ao adicionar dados: {ex.Message}");
|
|
// }
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// Console.WriteLine($"Ocorreu um erro: {ex.Message}");
|
|
// }
|
|
// }
|
|
// static void SetTimerEndTime(DateTime date) => dtTimerEndTime = date;
|
|
// private static void WhileTimerRuns(object sender, ElapsedEventArgs e)
|
|
// {
|
|
// Console.Write($"\rEncerrando em {dtTimerEndTime.Subtract(e.SignalTime).Seconds}");
|
|
// }
|
|
// static void SetTimer()
|
|
// {
|
|
// var vTimer = new System.Timers.Timer(100);
|
|
// double dRemainingTime;
|
|
// int iInterval = Convert.ToInt32(sTimerDurationToClose!);
|
|
// SetTimerEndTime(DateTime.Now.AddSeconds(iInterval));
|
|
// vTimer.Elapsed += WhileTimerRuns!;
|
|
// vTimer.AutoReset = true;
|
|
// vTimer.Enabled = true;
|
|
|
|
// Console.WriteLine("");
|
|
// Console.WriteLine("Pressione qualquer tecla para encerrar o programa...");
|
|
// Console.WriteLine("");
|
|
|
|
// Task.Factory.StartNew(
|
|
// () =>
|
|
// {
|
|
// Console.ReadKey();
|
|
// dRemainingTime = DateTime.Now.Subtract(dtTimerEndTime).TotalSeconds;
|
|
// }
|
|
// ).Wait(
|
|
// TimeSpan.FromSeconds(iInterval)
|
|
// );
|
|
// }
|
|
// static JsonNode FormatData(JsonNode jsData)
|
|
// {
|
|
// JsonNode data = (JsonNode)JsonDocument.Parse(jsData.Root.ToString()).Deserialize(typeof(JsonNode))!;
|
|
// dynamic jsNewData = new JsonObject();
|
|
|
|
// DataTable vDistTable = dtDistribuidoras!;
|
|
|
|
// int iCount = data["result"]!["records"]!.AsArray().Count;
|
|
|
|
// for (int i = 0; i < iCount; i++)
|
|
// {
|
|
// //jsNewData.Add(new JsonObject());
|
|
// var record = data["result"]!["records"]![i];
|
|
|
|
// string ID_dist = vDistTable.Select($"CNPJ LIKE '%{record!["NumCPFCNPJ"]}%'").First()[0].ToString()!;
|
|
// string Grupo = record["DscSubGrupoTarifario"]!.ToString();
|
|
// string Perfil = "";
|
|
// if (record["DscDetalheConsumidor"]!.ToString() == "SCEE")
|
|
// {
|
|
// Perfil = "SCEE - " + record["DscModalidadeTarifaria"]!.ToString().ToUpper();
|
|
// }
|
|
// else if (record["DscDetalheConsumidor"]!.ToString() == "APE")
|
|
// {
|
|
// Perfil = record["DscModalidadeTarifaria"]!.ToString().ToUpper() + " APE";
|
|
// }
|
|
// else
|
|
// {
|
|
// Perfil = string.Join('_', (new string[] { record!["DscModalidadeTarifaria"]!.ToString().ToUpper(), record!["DscDetalheConsumidor"]!.ToString().ToUpper() }).Where(x => !string.IsNullOrEmpty(x)));
|
|
// }
|
|
|
|
// Perfil = string.Join(
|
|
// '_',
|
|
// (new string[] {
|
|
// Perfil.ToString(),
|
|
// record["DscClasseConsumidor"]!.ToString().ToUpper(),
|
|
// record["DscSubClasseConsumidor"]!.ToString().ToUpper()
|
|
// }).Where(x => !string.IsNullOrEmpty(x)));
|
|
|
|
// string Ciclo = "RH" + record!["DscResolucaoHomologatoria"]!.ToString().Split(' ')[3].Replace(".", "").Replace(",", "");
|
|
// string DatInicioVigencia = DateOnly.Parse(record!["DatInicioVigencia"]!.ToString()).ToString();
|
|
// string DatFimVigencia = DateOnly.Parse(record!["DatFimVigencia"]!.ToString()).ToString();
|
|
|
|
// string Energia_P = "";
|
|
// string Energia_FP = "";
|
|
// string Enc_P = "";
|
|
// string Enc_FP = "";
|
|
// string Dem_P = "";
|
|
// string Dem_FP = "";
|
|
// string ER_P = "";
|
|
// string ER_FP = "";
|
|
// string Megaflex = "";
|
|
// string Covid_P = "";
|
|
// string Covid_FP = "";
|
|
// string Desc_Rural = "";
|
|
// string EH_P = "";
|
|
// string EH_FP = "";
|
|
|
|
// switch (record["DscComponenteTarifario"]!.ToString(), record["DscSubGrupoTarifario"]!.ToString()[0], record["DscModalidadeTarifaria"]!.ToString())
|
|
// {
|
|
// case ("TE", 'B', _):
|
|
// Energia_P = record["VlrComponenteTarifario"]!.ToString();
|
|
// Energia_FP = record["VlrComponenteTarifario"]!.ToString();
|
|
// break;
|
|
// case ("TE", 'A', "Ponta"):
|
|
// Energia_P = record["VlrComponenteTarifario"]!.ToString();
|
|
// break;
|
|
// }
|
|
|
|
// if (jsNewData[ID_dist] == null) { jsNewData[ID_dist] = new JsonObject(); }
|
|
// if (jsNewData[ID_dist][Grupo] == null) { jsNewData[ID_dist][Grupo] = new JsonObject(); }
|
|
// if (jsNewData[ID_dist][Grupo][Perfil] == null) { jsNewData[ID_dist][Grupo][Perfil] = new JsonObject(); }
|
|
// if (jsNewData[ID_dist][Grupo][Perfil][Ciclo] == null) { jsNewData[ID_dist][Grupo][Perfil][Ciclo] = new JsonObject(); }
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["DatInicioVigencia"] = DatInicioVigencia;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["DatFimVigencia"] = DatFimVigencia;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Energia_P"] = Energia_P;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Energia_FP"] = Energia_FP;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Enc_P"] = Enc_P;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Enc_FP"] = Enc_FP;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Dem_P"] = Dem_P;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Dem_FP"] = Dem_FP;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["ER_P"] = ER_P;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["ER_FP"] = ER_FP;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Megaflex"] = Megaflex;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Covid_P"] = Covid_P;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Covid_FP"] = Covid_FP;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["Desc_Rural"] = Desc_Rural;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["EH_P"] = EH_P;
|
|
// jsNewData[ID_dist][Grupo][Perfil][Ciclo]["EH_FP"] = EH_FP;
|
|
// }
|
|
|
|
// return jsNewData;
|
|
// }
|
|
// private static async Task Main(string[] args)
|
|
// {
|
|
// try
|
|
// {
|
|
// SetConfig();
|
|
// int iOffset = 0;
|
|
|
|
// var builder = WebApplication.CreateBuilder(args);
|
|
// var app = builder.Build();
|
|
// var logger = app.Services.GetRequiredService<ILogger<Program>>();
|
|
|
|
// using OleDbConnection connection = new(sResourcesDbConnection);
|
|
// connection.Open();
|
|
|
|
// if (!TabelaExiste(connection, sResourcesTableName!)) { throw new Exception($"Falha ao acessar [{sResourcesTableName}]"); }
|
|
|
|
// using OleDbConnection conn = new(sDbConnection);
|
|
// conn.Open();
|
|
// SetDistTable(conn);
|
|
|
|
// using OleDbCommand command = new($"SELECT {sResourcesTableName}.resource_id, *\r\nFROM {sResourcesTableName}\r\nWHERE ((Not ({sResourcesTableName}.resource_id)=\"\"));\r\n", connection);
|
|
// using OleDbDataReader reader = command.ExecuteReader() ?? throw new Exception();
|
|
|
|
// while (reader.Read())
|
|
// {
|
|
// iOffset = 0;
|
|
// string sTableName = $"Tarifas_{reader["Ano"]}";
|
|
|
|
// if ((string)reader["Ano"] == "All")
|
|
// {
|
|
// iOffset = GetLastIdFromTable(sTableName, "_id", connection);
|
|
// }
|
|
// else
|
|
// {
|
|
// iOffset = GetCountIdFromTable(sTableName, "_id", connection);
|
|
// }
|
|
|
|
// string sWebString = BuildUrl(sApiDadosAbertosUrl!, reader, "Ano");
|
|
|
|
// bool hasNextPage = true;
|
|
// JsonNode? tempRecords = null;
|
|
|
|
// while (hasNextPage)
|
|
// {
|
|
// JsonNode? rootObject = await PullDataAsync($@"{sWebString}offset={iOffset}");
|
|
// tempRecords = rootObject["result"]!["records"]!;
|
|
|
|
// int iTotal = (int)rootObject!["result"]!["total"]!;
|
|
|
|
// if (tempRecords != null && tempRecords.AsArray().Count != 0 && iOffset < iTotal)
|
|
// {
|
|
// JsonNode jsFormatedData = FormatData(tempRecords);
|
|
|
|
// AdicionarDadosAoBanco(connection, tempRecords, sTableName);
|
|
|
|
// iOffset += tempRecords!.AsArray().Count + iOffset;
|
|
// }
|
|
// else
|
|
// {
|
|
// Console.WriteLine("Nada mais a adicionar.");
|
|
// hasNextPage = false;
|
|
// }
|
|
// }
|
|
// }
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// Console.WriteLine(ex.Message);
|
|
// }
|
|
// finally
|
|
// {
|
|
// SetTimer();
|
|
// }
|
|
// }
|
|
// }
|
|
//}
|
|
|