public async Task <IActionResult> Registro(RegistroModelo model)
{
if (ModelState.IsValid)
{
var user = new UsuarioAplicacacion
{
UserName = model.Email,
Email = model.Email,
ayudaPass = model.ayudaPass
};
//Se guarda la data del usuario en la tabla de base de datos AspNetUser
var resultado = await gestionUsuarios.CreateAsync(user, model.Password);
//si el usuario se creo correctamente se logea y se redirige al inicio
if (resultado.Succeeded)
{
if (gestionLogin.IsSignedIn(User) && User.IsInRole("Administracion"))
{
return(RedirectToAction("ListarUsuarios", "Administracion"));
}
await gestionLogin.SignInAsync(user, isPersistent : false);
return(RedirectToAction("index", "home"));
}
//control del erroe en caso de que se produzca
foreach (var error in resultado.Errors)
{
ModelState.AddModelError(string.Empty, error.Description);
}
}
return(View(model));
}
public async Task <IActionResult> Registro_Usuario([FromBody] RegistroModelo modelo)
{
var userExists = await usuarioManager.FindByNameAsync(modelo.Username);
if (userExists != null)
{
return(StatusCode(StatusCodes.Status500InternalServerError, new Respuesta {
Estado = "Error", Mensaje = "el usuario ya existe!"
}));
}
AplicacionUsuario user = new()
{
Email = modelo.Email,
SecurityStamp = Guid.NewGuid().ToString(),
UserName = modelo.Username
};
var result = await usuarioManager.CreateAsync(user, modelo.Password);
if (!result.Succeeded)
{
return(StatusCode(StatusCodes.Status500InternalServerError,
new Respuesta
{
Estado = "Error", Mensaje = "Error al crear el usuario, chechee los detalles he intentelo de nuevo."
}));
}
return(Ok(new Respuesta {
Estado = "Success", Mensaje = "Usuario creado con exito!"
}));
}
}
public IActionResult GetModelos()
{
try
{
EfetuarPrevisao efetuaModelos = new EfetuarPrevisao(configuration, logger);
var modelos = efetuaModelos.CarregaModeloMetrica();
if (modelos == null)
{
modelos = new RegistroModelo();
}
Dictionary <string, object> param = new Dictionary <string, object>
{
{ "filiais", modelos.Filial.ToString() },
{ "modelos", modelos.Secao.ToString() },
{ "rmse", modelos.RootMeanSquaredError.ToString("N2") }
};
return(Json(param));
}
catch (Exception ex)
{
logger.LogError(ex, "Consulta modelos");
throw ex;
}
}
private void GravarModelo(RegistroModelo registroModelo)
{
DalConnection dal = new DalConnection(configuration, logger);
try
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("DELETE FROM DSDH_MODELOS_CMV");
sb.AppendLine("WHERE FILIAL = &FIL");
sb.AppendLine("AND SECAO = &SEC");
sb.AppendLine("AND GRUPO = &GRP");
sb.AppendLine("AND SUBGRUPO = &SGRP");
Dictionary <string, object> param = new Dictionary <string, object>
{
{ "FIL", registroModelo.Filial },
{ "SEC", registroModelo.Secao },
{ "GRP", registroModelo.Grupo },
{ "SGRP", registroModelo.SubGrupo }
};
dal.ExecuteNonQuery(sb, param);
sb = new StringBuilder();
sb.AppendLine("INSERT INTO DSDH_MODELOS_CMV (");
sb.AppendLine("FILIAL, SECAO, GRUPO, SUBGRUPO,");
sb.AppendLine("MEANABSOLUTEERROR,MEANSQUAREDERROR,ROOTMEANSQUAREDERROR,");
sb.AppendLine("LOSSFUNC,RSQUARED,MODELO)");
sb.AppendLine("VALUES (&FIL, &SEC, &GRP, &SGRP,");
sb.AppendLine("&MAB, &MSE, &RMSE, &LSFNC, &RSQ, &MDL)");
param = new Dictionary <string, object>
{
{ "FIL", registroModelo.Filial },
{ "SEC", registroModelo.Secao },
{ "GRP", registroModelo.Grupo },
{ "SGRP", registroModelo.SubGrupo },
{ "MAB", registroModelo.MeanAbsoluteError },
{ "MSE", registroModelo.MeanSquaredError },
{ "RMSE", registroModelo.RootMeanSquaredError },
{ "LSFNC", registroModelo.LossFunc },
{ "RSQ", registroModelo.RSquared },
{ "MDL", registroModelo.Modelo }
};
dal.ExecuteNonQuery(sb, param);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dal = null;
}
}
private void GravaPrevisoes(List <RegistroCmv> previsoes, RegistroModelo modelo)
{
DalConnection dal = new DalConnection(configuration, logger);
try
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("DELETE FROM DSDH_PREVISAO_CMV");
sb.AppendLine("WHERE FILIAL = &FIL");
sb.AppendLine("AND SECAO = &SEC");
sb.AppendLine("AND GRUPO = &GRP");
sb.AppendLine("AND SUBGRUPO = &SGRP");
Dictionary <string, object> param = new Dictionary <string, object>
{
{ "FIL", modelo.Filial },
{ "SEC", modelo.Secao },
{ "GRP", modelo.Grupo },
{ "SGRP", modelo.SubGrupo }
};
dal.ExecuteNonQuery(sb, param);
sb = new StringBuilder();
sb.AppendLine("INSERT INTO DSDH_PREVISAO_CMV (");
sb.AppendLine("DIA, FILIAL, SECAO, GRUPO, SUBGRUPO, CMV )");
sb.AppendLine("VALUES (&DIA, &FIL, &SEC, &GRP, &SGRP, &CMV)");
foreach (var previsao in previsoes)
{
param = new Dictionary <string, object>
{
{ "DIA", ((previsao.Dia.Year - 1900) * 100 + previsao.Dia.Month) * 100 + previsao.Dia.Day },
{ "FIL", modelo.Filial },
{ "SEC", modelo.Secao },
{ "GRP", modelo.Grupo },
{ "SGRP", modelo.SubGrupo },
{ "CMV", Math.Round(previsao.Valor, 2) }
};
dal.ExecuteNonQuery(sb, param);
}
}
catch
{
throw;
}
finally
{
dal = null;
}
}
public ModelOutput Predict(ModelInput input, RegistroModelo registroModelo)
{
Stream stream = new MemoryStream(registroModelo.Modelo);
// Create new MLContext
MLContext mlContext = new MLContext();
// Load model & create prediction engine
ITransformer mlModel = mlContext.Model.Load(stream, out var modelInputSchema);
var predEngine = mlContext.Model.CreatePredictionEngine <ModelInput, ModelOutput>(mlModel);
// Use model to make prediction on input data
ModelOutput result = predEngine.Predict(input);
return(result);
}
public RegistroModelo CarregaModeloMetrica()
{
DalConnection dal = new DalConnection(configuration, logger);
try
{
RegistroModelo registro = new RegistroModelo();
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT count(distinct FILIAL) filial, ");
sb.AppendLine(" count(1) MODELOS, ");
sb.AppendLine(" round(avg(rootmeansquarederror), 2) rmse ");
sb.AppendLine("FROM DSDH_MODELOS_CMV ");
var dt = dal.ExecuteQuery(sb, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
foreach (DataColumn dc in dt.Columns)
{
switch (dc.ColumnName.ToUpper())
{
case "FILIAL": registro.Filial = Convert.ToInt32(dr[dc]); break;
case "MODELOS": registro.Secao = Convert.ToInt32(dr[dc]); break;
case "RMSE": registro.RootMeanSquaredError = Convert.ToSingle(dr[dc]); break;
default:
break;
}
}
}
}
return(registro);
}
catch
{
throw;
}
finally
{
dal = null;
}
}
private List <RegistroCmv> EfetuaPrevisao(DateTime dtIni, DateTime dtFim, RegistroModelo modelo)
{
ConsomeModel ml = new ConsomeModel();
TransformData transformData = new TransformData();
List <ModelInput> modelInputs = new List <ModelInput>();
List <RegistroCmv> registroCmvs = new List <RegistroCmv>();
var dtAtual = dtIni;
do
{
registroCmvs.Add(new RegistroCmv {
Dia = dtAtual
});
dtAtual = dtAtual.AddDays(1);
} while (dtAtual <= dtFim);
foreach (var registro in registroCmvs)
{
registro.Feriado = VerificaFeriado(registro.Dia, modelo.Filial);
var clima = CarregaClima(registro.Dia, modelo.Filial);
if (clima != null)
{
registro.MaxTempC = clima.MaxTempC;
registro.MinTempC = clima.MinTempC;
registro.AvgTempC = clima.AvgTempC;
registro.PrecipMm = clima.PrecipMm;
}
}
modelInputs = transformData.TransformaDados(registroCmvs);
int atual = 0;
foreach (var item in modelInputs)
{
var result = ml.Predict(item, modelo);
if (result.Score < 0)
{
result.Score = 0;
}
registroCmvs[atual].Valor = result.Score;
atual++;
}
return(registroCmvs);
}
public RegistroModelo CreateModel(List <ModelInput> modelInputs)
{
IDataView trainingDataView = mlContext.Data.LoadFromEnumerable(modelInputs);
IEstimator <ITransformer> trainingPipeline = BuildTrainingPipeline(mlContext);
var crossValidationResults = Evalute(mlContext, trainingDataView, trainingPipeline);
ITransformer mlModel = TrainModel(mlContext, trainingDataView, trainingPipeline);
MemoryStream memoryStream = SaveModel(mlContext, mlModel, trainingDataView.Schema);
RegistroModelo registroModelo = new RegistroModelo
{
LossFunc = crossValidationResults != null?crossValidationResults.Select(r => r.Metrics.LossFunction).Average() : 0,
MeanAbsoluteError = crossValidationResults != null?crossValidationResults.Select(r => r.Metrics.MeanAbsoluteError).Average() : 0,
MeanSquaredError = crossValidationResults != null?crossValidationResults.Select(r => r.Metrics.MeanSquaredError).Average() : 0,
RootMeanSquaredError = crossValidationResults != null?crossValidationResults.Select(r => r.Metrics.RootMeanSquaredError).Average() : 0,
RSquared = crossValidationResults != null?crossValidationResults.Select(r => r.Metrics.RSquared).Average() : 0,
Modelo = memoryStream.ToArray()
};
return(registroModelo);
}
public List <RegistroModelo> CarregaModelosMetricas()
{
DalConnection dal = new DalConnection(configuration, logger);
List <RegistroModelo> registroModelos = null;
try
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT FILIAL, SECAO, GRUPO, SUBGRUPO,");
sb.AppendLine("ROUND(MEANABSOLUTEERROR, 2) MEANABSOLUTEERROR, ROUND(MEANSQUAREDERROR, 2) MEANSQUAREDERROR, ROUND(ROOTMEANSQUAREDERROR, 2) ROOTMEANSQUAREDERROR");
sb.AppendLine("FROM DSDH_MODELOS_CMV");
sb.AppendLine("ORDER BY FILIAL, SECAO, GRUPO, SUBGRUPO");
var dt = dal.ExecuteQuery(sb, null);
if (dt != null && dt.Rows.Count > 0)
{
registroModelos = new List <RegistroModelo>();
foreach (DataRow dr in dt.Rows)
{
RegistroModelo modelo = new RegistroModelo();
foreach (DataColumn dc in dt.Columns)
{
switch (dc.ColumnName.ToUpper())
{
case "FILIAL": modelo.Filial = Convert.ToInt32(dr[dc]); break;
case "SECAO": modelo.Secao = Convert.ToInt32(dr[dc]); break;
case "GRUPO": modelo.Grupo = Convert.ToInt32(dr[dc]); break;
case "SUBGRUPO": modelo.SubGrupo = Convert.ToInt32(dr[dc]); break;
case "MODELO": modelo.Modelo = dr[dc] != DBNull.Value ? (byte[])dr[dc] : null; break;
case "MEANABSOLUTEERROR": modelo.MeanAbsoluteError = Convert.ToDouble(dr[dc]); break;
case "MEANSQUAREDERROR": modelo.MeanSquaredError = Convert.ToDouble(dr[dc]); break;
case "ROOTMEANSQUAREDERROR": modelo.RootMeanSquaredError = Convert.ToDouble(dr[dc]); break;
default:
break;
}
}
registroModelos.Add(modelo);
}
}
return(registroModelos);
}
catch
{
throw;
}
finally
{
dal = null;
}
}
