Samuele Locatelli
1193 lines
47 KiB
C#
1193 lines
47 KiB
C#
using GWMS.Data.DatabaseModels;
|
|
using GWMS.Data.DTO;
|
|
using Microsoft.EntityFrameworkCore;
|
|
using Microsoft.Extensions.Configuration;
|
|
using NLog;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace GWMS.Data.Controllers
|
|
{
|
|
public class GWMSController : IDisposable
|
|
{
|
|
#region Public Constructors
|
|
|
|
public GWMSController(IConfiguration configuration)
|
|
{
|
|
_configuration = configuration;
|
|
}
|
|
|
|
#endregion Public Constructors
|
|
|
|
#region Public Methods
|
|
|
|
/// <summary>
|
|
/// Esegue pulizia allarmi impianto richiesto
|
|
/// </summary>
|
|
/// <param name="PlantId"></param>
|
|
/// <returns></returns>
|
|
public async Task<int> AlarmLogCleanDup(int PlantId)
|
|
{
|
|
int numMod = 0;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
// eseguo stored
|
|
string sqlCommand = $"CALL stp_deleteDuplicatedAlarms ({PlantId});";
|
|
numMod = await localDbCtx.Database.ExecuteSqlRawAsync(sqlCommand);
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in AlarmLogCleanDup{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return numMod;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Recupero elenco allarmi (ultimi?)
|
|
/// </summary>
|
|
/// <param name="PlantId"></param>
|
|
/// <param name="skipRec"></param>
|
|
/// <param name="numRec"></param>
|
|
/// <returns></returns>
|
|
public async Task<List<AlarmLogModel>> AlarmLogGetFilt(int PlantId, int skipRec, int numRec)
|
|
{
|
|
List<AlarmLogModel> dbResult = new List<AlarmLogModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
dbResult = await localDbCtx
|
|
.DbSetAlarmLog
|
|
.Where(x => x.PlantId == PlantId)
|
|
.Include(p => p.Plant)
|
|
.OrderByDescending(x => x.AlarmLogId)
|
|
.Skip(skipRec)
|
|
.Take(numRec)
|
|
.ToListAsync();
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione durante AlarmLogGetFilt{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Inserimento di un record AlarmLog
|
|
/// </summary>
|
|
/// <param name="newItem">Record da inserire</param>
|
|
/// <returns></returns>
|
|
public async Task<bool> AlarmLogInsert(AlarmLogModel newItem)
|
|
{
|
|
bool fatto = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
localDbCtx
|
|
.DbSetAlarmLog
|
|
.Add(newItem);
|
|
await localDbCtx.SaveChangesAsync();
|
|
fatto = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione durante AlarmLogInsert{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return fatto;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Calcola DataOra arrotondata per eccesso secondo intervallo minuti indicato
|
|
/// </summary>
|
|
/// <param name="dateOrig"></param>
|
|
/// <param name="minInt"></param>
|
|
/// <returns></returns>
|
|
public DateTime DateRoundEnd(DateTime dateOrig, int minInt)
|
|
{
|
|
// calcolo con minuto + 0.5 per poter arrotondare in modo corretto sennò se 0 min e qualche secondo --> diventa floor e arrotonda x difetto...
|
|
int minRound = (int)Math.Ceiling(((double)dateOrig.Minute + 0.5) / minInt) * minInt;
|
|
DateTime roundDate = dateOrig.Date.AddHours(dateOrig.Hour).AddMinutes(minRound);
|
|
return roundDate;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Forza migrazione DB se necessario
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public bool DbForceMigrate()
|
|
{
|
|
bool answ = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
localDbCtx.DbForceMigrate();
|
|
answ = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in DbForceMigrate{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return answ;
|
|
}
|
|
|
|
public void Dispose()
|
|
{
|
|
// Clear database context
|
|
//Log.Info("Dispose di GWMSController");
|
|
}
|
|
|
|
public List<ConfigModel> GetConfig()
|
|
{
|
|
List<ConfigModel> dbResult = new List<ConfigModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = localDbCtx
|
|
.DbSetConfig
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public List<ItemModel> GetItems()
|
|
{
|
|
List<ItemModel> dbResult = new List<ItemModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = localDbCtx
|
|
.DbSetItems
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public async Task<OrderModel> GetOrderByCodeAsync(string OrderCode)
|
|
{
|
|
OrderModel dbResult = new OrderModel();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = await localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => (x.OrderCode == OrderCode))
|
|
.Include(p => p.Plant)
|
|
.Include(s => s.Supplier)
|
|
.Include(t => t.Transporter)
|
|
.FirstOrDefaultAsync();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public async Task<OrderModel> GetOrderByIdAsync(int OrderId)
|
|
{
|
|
OrderModel dbResult = new OrderModel();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = await localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => (x.OrderId == OrderId))
|
|
.Include(p => p.Plant)
|
|
.Include(s => s.Supplier)
|
|
.Include(t => t.Transporter)
|
|
.FirstOrDefaultAsync();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public async Task<List<OrderModel>> GetOrdersFiltAsync(int PlantId, int SupplierId, int TransporterId, DateTime DtStart, DateTime DtEnd, bool ShowClosed)
|
|
{
|
|
List<OrderModel> dbResult = new List<OrderModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = await localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => (x.PlantId == PlantId || PlantId == 0)
|
|
&& (x.SupplierId == SupplierId || SupplierId == 0)
|
|
&& (x.TransporterId == TransporterId || TransporterId == 0)
|
|
&& (x.DtOrder >= DtStart && x.DtOrder <= DtEnd)
|
|
&& (x.ExecutionQty == 0 || ShowClosed))
|
|
.Include(p => p.Plant)
|
|
.Include(s => s.Supplier)
|
|
.Include(t => t.Transporter)
|
|
.OrderByDescending(x => x.DtOrder)
|
|
.ToListAsync();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public async Task<List<OrderModel>> GetOrdersOpenAsync(int PlantId)
|
|
{
|
|
List<OrderModel> dbResult = new List<OrderModel>();
|
|
// limite ordini aperti: creati all'anno 0001 --> verifico anno start < 2000
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = await localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => (x.PlantId == PlantId || PlantId == 0)
|
|
&& (x.DtExecStart < x.DtOrder && x.DtExecStart.Year < 2000))
|
|
.Include(p => p.Plant)
|
|
.Include(s => s.Supplier)
|
|
.Include(t => t.Transporter)
|
|
.OrderByDescending(x => x.DtOrder)
|
|
.ToListAsync();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public PlantDetailModel GetPlant(int PlantId)
|
|
{
|
|
PlantDetailModel dbResult = new PlantDetailModel();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = localDbCtx
|
|
.DbSetPlant
|
|
.Where(x => x.PlantId == PlantId)
|
|
.FirstOrDefault();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco in formato di lista DTO resoconti giornalieri x Plant
|
|
/// - elenco record livelli giornalieri
|
|
/// - elenco record ordini giornalieri
|
|
/// - valori start/end/min/max giornalieri x livello
|
|
/// </summary>
|
|
/// <param name="PlantId">codice plant</param>
|
|
/// <param name="DtStart">data inizio</param>
|
|
/// <param name="DtEnd">data fine</param>
|
|
/// <returns></returns>
|
|
public List<PlantLevSumDTO> GetPlantLevSumDto(int PlantId, DateTime DtStart, DateTime DtEnd)
|
|
{
|
|
List<PlantLevSumDTO> dbResult = new List<PlantLevSumDTO>();
|
|
if (PlantId > 0)
|
|
{
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
var dbLevels = localDbCtx
|
|
.DbSetPlantLog
|
|
.Where(x => x.PlantId == PlantId && x.FluxType == "Level" && x.DtEvent <= DtEnd && x.DtEvent > DtStart)
|
|
.OrderBy(x => x.DtEvent)
|
|
.ToList();
|
|
|
|
var dbOrders = localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => (x.PlantId == PlantId) && x.DtExecStart <= DtEnd && x.DtExecStart > DtStart)
|
|
.OrderBy(x => x.DtExecStart)
|
|
.ToList();
|
|
|
|
var dbPlant = localDbCtx
|
|
.DbSetPlant
|
|
.Where(x => x.PlantId == PlantId)
|
|
.FirstOrDefault();
|
|
|
|
// ora ricostruisco i record in uscita... ciclando data x data...
|
|
DateTime dataCurr = DtStart.Date;
|
|
int numDays = DtEnd.Subtract(DtStart).Days;
|
|
for (int i = 0; i < numDays; i++)
|
|
{
|
|
dataCurr = DtStart.Date.AddDays(i);
|
|
// recupero i dati della gioranta indicata
|
|
var currLevelTS = dbLevels
|
|
.Where(x => x.DtEvent > dataCurr && x.DtEvent <= dataCurr.AddDays(1))
|
|
.Select(x => new TSData(x.DtEvent, x.ValNumber, true))
|
|
//.Select(x => new TSData() { DtEvent = x.DtEvent, ValDouble = x.ValNumber })
|
|
.ToList();
|
|
// SOLO SE ho almeno 1 record livello
|
|
if (currLevelTS.Count > 0)
|
|
{
|
|
PlantLevSumDTO newRec = new PlantLevSumDTO()
|
|
{
|
|
PlantId = PlantId,
|
|
PlantCode = dbPlant.PlantCode,
|
|
PlantDesc = dbPlant.PlantDesc,
|
|
DataRif = dataCurr
|
|
};
|
|
// se ho dati giornalieri li aggiungo...
|
|
if (currLevelTS.Count > 0)
|
|
{
|
|
var minRecord = currLevelTS
|
|
.OrderBy(x => x.ValDouble)
|
|
.FirstOrDefault();
|
|
var maxRecord = currLevelTS
|
|
.OrderByDescending(x => x.ValDouble)
|
|
.FirstOrDefault();
|
|
|
|
newRec.LevelTS = currLevelTS;
|
|
newRec.LevelStart = currLevelTS
|
|
.OrderBy(x => x.DtEvent)
|
|
.FirstOrDefault().ValDouble;
|
|
newRec.LevelEnd = currLevelTS
|
|
.OrderByDescending(x => x.DtEvent)
|
|
.FirstOrDefault().ValDouble;
|
|
newRec.LevelMin = minRecord.ValDouble;
|
|
newRec.FillStart = minRecord.DtEvent;
|
|
newRec.LevelMax = maxRecord.ValDouble;
|
|
newRec.FillEnd = maxRecord.DtEvent;
|
|
}
|
|
if (dbOrders.Count > 0)
|
|
{
|
|
newRec.ExecutionTS = dbOrders
|
|
.Where(x => x.DtExecStart >= dataCurr && x.DtExecStart < dataCurr.AddDays(1))
|
|
.Select(x => new TSData(x.DtOrder, x.ExecutionQty, true)).ToList();
|
|
//.Select(x => new TSData() { DtEvent = x.DtOrder, ValDouble = x.ExecutionQty }).ToList();
|
|
|
|
newRec.OrderTS = dbOrders
|
|
.Where(x => x.DtExecStart >= dataCurr && x.DtExecStart < dataCurr.AddDays(1))
|
|
.Select(x => new TSData(x.DtOrder, x.OrderQty, true)).ToList();
|
|
//.Select(x => new TSData() { DtEvent = x.DtOrder, ValDouble = x.OrderQty }).ToList();
|
|
|
|
newRec.OrdersIds = dbOrders
|
|
.Where(x => x.DtExecStart >= dataCurr && x.DtExecStart < dataCurr.AddDays(1))
|
|
.Select(x => x.OrderId).ToList();
|
|
}
|
|
dbResult.Add(newRec);
|
|
}
|
|
}
|
|
dbResult = dbResult.OrderByDescending(x => x.DataRif).ToList();
|
|
}
|
|
}
|
|
// restituisco riordinato DESC
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco log registrati x plant
|
|
/// </summary>
|
|
/// <param name="PlantId">se 0 = tutto</param>
|
|
/// <param name="DtMaxDate">consigliato arrotondamento al minuto o multiplo</param>
|
|
/// <param name="numRec">num rec max da recuperare</param>
|
|
/// <returns></returns>
|
|
public List<PlantLogModel> GetPlantLog(int PlantId, DateTime DtMaxDate, int numRec)
|
|
{
|
|
List<PlantLogModel> dbResult = new List<PlantLogModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = localDbCtx
|
|
.DbSetPlantLog
|
|
.Where(x => (x.PlantId == PlantId || PlantId == 0) && x.DtEvent <= DtMaxDate)
|
|
.OrderByDescending(x => x.DtEvent)
|
|
.Take(numRec)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public List<PlantDetailModel> GetPlants()
|
|
{
|
|
List<PlantDetailModel> dbResult = new List<PlantDetailModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = localDbCtx
|
|
.DbSetPlant
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public List<PlantDTO> GetPlantsDTO(int maxRecords)
|
|
{
|
|
List<PlantDTO> dbResult = new List<PlantDTO>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
var plantList = localDbCtx
|
|
.DbSetPlant
|
|
.ToList();
|
|
|
|
dbResult = plantList
|
|
.Select(x => PlantDTO(x.PlantId, maxRecords))
|
|
.ToList();
|
|
}
|
|
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Restituisce ultimi eventi rebootlog
|
|
/// </summary>
|
|
/// <param name="maxNum">num eventi, se 0 = tutti</param>
|
|
/// <returns></returns>
|
|
public List<RebootLogModel> GetRebootLog(int maxNum = 100)
|
|
{
|
|
List<RebootLogModel> dbResult = new List<RebootLogModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
if (maxNum == 0)
|
|
{
|
|
maxNum = localDbCtx.DbRebootLog.Count();
|
|
}
|
|
dbResult = localDbCtx
|
|
.DbRebootLog
|
|
.OrderByDescending(x => x.DtEvent)
|
|
.Take(maxNum)
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public List<SupplierModel> GetSuppliers()
|
|
{
|
|
List<SupplierModel> dbResult = new List<SupplierModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = localDbCtx
|
|
.DbSetSupplier
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public List<TransporterModel> GetTransporters()
|
|
{
|
|
List<TransporterModel> dbResult = new List<TransporterModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = localDbCtx
|
|
.DbSetTransporter
|
|
.ToList();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public async Task<List<WeekPlanModel>> GetWeekPlanAsync()
|
|
{
|
|
List<WeekPlanModel> dbResult = new List<WeekPlanModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = await localDbCtx
|
|
.DbSetPlantSupplWeekPlan
|
|
.Include(p => p.Plant)
|
|
.Include(s => s.Supplier)
|
|
.Include(t => t.Transporter)
|
|
.ToListAsync();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
public async Task<bool> HasPlantLog()
|
|
{
|
|
bool answ = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
var numRecord = await localDbCtx
|
|
.DbSetPlantLog
|
|
.CountAsync();
|
|
answ = numRecord > 0;
|
|
}
|
|
catch
|
|
{ }
|
|
}
|
|
return answ;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Eliminazione di un ordine dato record completo
|
|
/// </summary>
|
|
/// <param name="Item2Del"></param>
|
|
/// <returns></returns>
|
|
public async Task<bool> OrderDeleteAsync(OrderModel Item2Del)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
if (Item2Del != null)
|
|
{
|
|
var rec2del = await localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => x.OrderId == Item2Del.OrderId)
|
|
.FirstOrDefaultAsync();
|
|
localDbCtx
|
|
.DbSetOrders
|
|
.Remove(rec2del);
|
|
int numDone = await localDbCtx.SaveChangesAsync();
|
|
done = numDone > 0;
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Eliminazione di un ordine dao codice ID univoco
|
|
/// </summary>
|
|
/// <param name="orderId"></param>
|
|
/// <returns></returns>
|
|
public async Task<bool> OrderDeleteAsync(int orderId)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
if (orderId > 0)
|
|
{
|
|
var rec2del = await localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => x.OrderId == orderId)
|
|
.FirstOrDefaultAsync();
|
|
localDbCtx
|
|
.DbSetOrders
|
|
.Remove(rec2del);
|
|
int numDone = await localDbCtx.SaveChangesAsync();
|
|
done = numDone > 0;
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiunta in blocco di Ordini
|
|
/// </summary>
|
|
/// <param name="newItems"></param>
|
|
/// <returns></returns>
|
|
public bool OrderInsert(List<OrderModel> newItems)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
localDbCtx
|
|
.DbSetOrders
|
|
.AddRange(newItems);
|
|
localDbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in OrderInsert:{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiorna un Ordine
|
|
/// </summary>
|
|
/// <param name="updItem"></param>
|
|
/// <returns></returns>
|
|
public bool OrderUpdate(OrderModel updItem)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
OrderModel currData = null;
|
|
currData = localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => x.OrderId == updItem.OrderId)
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
// se ho modificato data --> cambio codice ordine!
|
|
if (!localDbCtx.Entry(updItem).OriginalValues["DtOrder"].Equals(localDbCtx.Entry(updItem).CurrentValues["DtOrder"]))
|
|
{
|
|
updItem.OrderCode = $"O{updItem.Plant.PlantCode}{updItem.DtOrder:yyMMddHHmm}";
|
|
updItem.OrderDesc = $"Ordine {updItem.Plant.PlantDesc} - {updItem.DtOrder}";
|
|
}
|
|
localDbCtx.Entry(currData).CurrentValues.SetValues(updItem);
|
|
localDbCtx.SaveChanges();
|
|
}
|
|
else
|
|
{
|
|
localDbCtx
|
|
.DbSetOrders
|
|
.Add(updItem);
|
|
localDbCtx.SaveChanges();
|
|
}
|
|
done = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in OrderUpdate:{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elenco di TUTTI i parametri gestiti
|
|
/// </summary>
|
|
/// <param name="updItem"></param>
|
|
/// <returns></returns>
|
|
public async Task<List<ParamSendModel>> ParamSendGetAllAsync()
|
|
{
|
|
List<ParamSendModel> listParams = new List<ParamSendModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
// elenco parametri gestiti
|
|
listParams = await localDbCtx
|
|
.DbSetParamSend
|
|
//.Where(x => x.enabled)
|
|
.ToListAsync();
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ParamSendGetAllAsync:{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return listParams;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Recupera un record ParamSend
|
|
/// </summary>
|
|
/// <param name="updItem"></param>
|
|
/// <returns></returns>
|
|
public async Task<ParamSendModel> ParamSendGetAsync(int PlantId, string ParamUid)
|
|
{
|
|
ParamSendModel result = new ParamSendModel();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
result = await localDbCtx
|
|
.DbSetParamSend
|
|
.Where(x => x.PlantId == PlantId && x.ParamUid == ParamUid)
|
|
.FirstOrDefaultAsync();
|
|
|
|
if (result == null && PlantId > 0 && !string.IsNullOrEmpty(ParamUid))
|
|
{
|
|
ParamSendModel currData = new ParamSendModel()
|
|
{
|
|
PlantId = PlantId,
|
|
ParamUid = ParamUid,
|
|
enabled = false
|
|
};
|
|
|
|
if (currData != null)
|
|
{
|
|
localDbCtx.
|
|
DbSetParamSend
|
|
.Add(currData);
|
|
await localDbCtx.SaveChangesAsync();
|
|
}
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiorna un record ParamSend
|
|
/// </summary>
|
|
/// <param name="updItem"></param>
|
|
/// <returns></returns>
|
|
public async Task<bool> ParamSendUpdateAsync(ParamSendModel updItem)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
ParamSendModel currData = null;
|
|
currData = await localDbCtx
|
|
.DbSetParamSend
|
|
.Where(x => x.PlantId == updItem.PlantId && x.ParamUid == updItem.ParamUid)
|
|
.FirstOrDefaultAsync();
|
|
if (currData != null)
|
|
{
|
|
localDbCtx.Entry(currData).CurrentValues.SetValues(updItem);
|
|
await localDbCtx.SaveChangesAsync();
|
|
}
|
|
else
|
|
{
|
|
localDbCtx
|
|
.DbSetParamSend
|
|
.Add(updItem);
|
|
localDbCtx.SaveChanges();
|
|
}
|
|
done = true;
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Eliminazione di un ParamSet
|
|
/// </summary>
|
|
/// <param name="Item2Del"></param>
|
|
/// <returns></returns>
|
|
public async Task<bool> ParamSetDeleteAsync(ParamSetModel Item2Del)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
if (Item2Del != null)
|
|
{
|
|
var rec2del = await localDbCtx
|
|
.DbSetParamSet
|
|
.Where(x => x.ParamSetId == Item2Del.ParamSetId)
|
|
.FirstOrDefaultAsync();
|
|
localDbCtx
|
|
.DbSetParamSet
|
|
.Remove(rec2del);
|
|
await localDbCtx.SaveChangesAsync();
|
|
done = true;
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
public async Task<List<ParamSetModel>> ParamSetGetAsync(int PlantId, string ParamUid)
|
|
{
|
|
List<ParamSetModel> dbResult = new List<ParamSetModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
dbResult = await localDbCtx
|
|
.DbSetParamSet
|
|
.Where(x => (x.PlantId == PlantId || PlantId == 0) && (x.ParamUid == ParamUid || ParamUid == ""))
|
|
.OrderBy(x => x.Scadenza)
|
|
.ToListAsync();
|
|
}
|
|
return dbResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiunta in blocco di ParamSet
|
|
/// </summary>
|
|
/// <param name="newItems"></param>
|
|
/// <returns></returns>
|
|
public bool ParamSetInsert(List<ParamSetModel> newItems)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
localDbCtx
|
|
.DbSetParamSet
|
|
.AddRange(newItems);
|
|
localDbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in ParamSetInsert:{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiorna un ParamSet
|
|
/// </summary>
|
|
/// <param name="updItem"></param>
|
|
/// <returns></returns>
|
|
public async Task<bool> ParamSetUpdateAsync(ParamSetModel updItem)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
ParamSetModel currData = null;
|
|
currData = await localDbCtx
|
|
.DbSetParamSet
|
|
.Where(x => x.ParamSetId == updItem.ParamSetId)
|
|
.FirstOrDefaultAsync();
|
|
if (currData != null)
|
|
{
|
|
localDbCtx.Entry(currData).CurrentValues.SetValues(updItem);
|
|
}
|
|
else
|
|
{
|
|
localDbCtx
|
|
.DbSetParamSet
|
|
.Add(updItem);
|
|
}
|
|
await localDbCtx.SaveChangesAsync();
|
|
done = true;
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Recupero info PLANT modalità DTO
|
|
/// </summary>
|
|
/// <param name="PlantId">Impianto</param>
|
|
/// <param name="PlantId">Max numero di record da recuperare</param>
|
|
/// <returns></returns>
|
|
public PlantDTO PlantDTO(int PlantId, int maxRecords)
|
|
{
|
|
DateTime lastRec = DateTime.Today;
|
|
var currPlant = GetPlant(PlantId);
|
|
PlantDTO answ = new PlantDTO();
|
|
List<TSData> LevelTS = new List<TSData>();
|
|
Dictionary<string, double> PressAct = new Dictionary<string, double>();
|
|
Dictionary<string, List<TSData>> PressTS = new Dictionary<string, List<TSData>>();
|
|
List<TSData> PressMainTS = new List<TSData>();
|
|
List<TSData> PressBHTS = new List<TSData>();
|
|
List<TSData> PressBLTS = new List<TSData>();
|
|
List<TSData> OrderTS = new List<TSData>();
|
|
List<TSData> SoldTS = new List<TSData>();
|
|
|
|
// dati raw da recuperare...
|
|
List<PlantLogModel> rawLevelData = new List<PlantLogModel>();
|
|
List<PlantLogModel> rawMainPressData = new List<PlantLogModel>();
|
|
List<PlantLogModel> rawBHPressData = new List<PlantLogModel>();
|
|
List<PlantLogModel> rawBLPressData = new List<PlantLogModel>();
|
|
List<OrderModel> rawOpenOrderData = new List<OrderModel>();
|
|
List<OrderModel> rawDepOrderData = new List<OrderModel>();
|
|
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
// per prima cosa recupero dati RAW ultimo mese...
|
|
var rawData = localDbCtx
|
|
.DbSetPlantLog
|
|
.Where(x => x.DtEvent > DateTime.Today.AddMonths(-1) && x.PlantId == PlantId)
|
|
.AsNoTracking()
|
|
.ToList();
|
|
|
|
// ora recupero i distinct x capire COSA posso poi ricavare...
|
|
var countData = rawData
|
|
.Select(e => e.FluxType)
|
|
.Distinct()
|
|
.ToList();
|
|
|
|
// recupero dal DB i dati PRESENTI...
|
|
if (countData.Contains("Level"))
|
|
{
|
|
rawLevelData = rawData
|
|
.Where(x => x.FluxType == "Level" && x.PlantId == PlantId)
|
|
//.AsNoTracking()
|
|
.OrderByDescending(x => x.DtEvent)
|
|
.Take(maxRecords)
|
|
.OrderBy(x => x.DtEvent)
|
|
.ToList();
|
|
}
|
|
|
|
if (countData.Contains("MainPress"))
|
|
{
|
|
rawMainPressData = rawData
|
|
.Where(x => x.FluxType == "MainPress" && x.PlantId == PlantId)
|
|
//.AsNoTracking()
|
|
.OrderByDescending(x => x.DtEvent)
|
|
.Take(maxRecords)
|
|
.OrderBy(x => x.DtEvent)
|
|
.ToList();
|
|
}
|
|
|
|
if (countData.Contains("PressBH"))
|
|
{
|
|
rawBHPressData = rawData
|
|
.Where(x => x.FluxType == "PressBH" && x.PlantId == PlantId)
|
|
//.AsNoTracking()
|
|
.OrderByDescending(x => x.DtEvent)
|
|
.Take(maxRecords)
|
|
.OrderBy(x => x.DtEvent)
|
|
.ToList();
|
|
}
|
|
if (countData.Contains("PressBL"))
|
|
{
|
|
rawBLPressData = rawData
|
|
.Where(x => x.FluxType == "PressBL" && x.PlantId == PlantId)
|
|
//.AsNoTracking()
|
|
.OrderByDescending(x => x.DtEvent)
|
|
.Take(maxRecords)
|
|
.OrderBy(x => x.DtEvent)
|
|
.ToList();
|
|
}
|
|
|
|
rawOpenOrderData = localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => x.PlantId == PlantId && x.ExecutionQty == 0)
|
|
.AsNoTracking()
|
|
.OrderByDescending(x => x.DtOrder)
|
|
.Take(maxRecords)
|
|
.OrderBy(x => x.DtOrder)
|
|
.ToList();
|
|
|
|
// imposto x ora a soli 2 gg (torno indietro di 1...)
|
|
int numDays = 1;
|
|
DateTime oggi = DateTime.Today;
|
|
// recupero ultimi record carichi x numDays gg
|
|
rawDepOrderData = localDbCtx
|
|
.DbSetOrders
|
|
.Where(x => x.PlantId == PlantId && x.ExecutionQty > 0 && x.DtExecStart >= oggi.AddDays(-numDays))
|
|
.OrderBy(x => x.DtOrder)
|
|
.ToList();
|
|
|
|
// ciclo numDays giorni precedenti
|
|
double venduto = 0;
|
|
double versatoQty = 0;
|
|
PlantLogModel firstRecord = null;
|
|
PlantLogModel nextRecord = null;
|
|
DateTime currDay = oggi;
|
|
for (int i = numDays; i >= 0; i--)
|
|
{
|
|
currDay = oggi.AddDays(-i);
|
|
firstRecord = rawLevelData.Where(x => x.DtEvent >= currDay).FirstOrDefault();
|
|
nextRecord = rawLevelData.Where(x => x.DtEvent >= currDay.AddDays(1)).FirstOrDefault();
|
|
if (nextRecord == null)
|
|
{
|
|
nextRecord = rawLevelData.Where(x => x.DtEvent < currDay.AddDays(1)).OrderByDescending(x => x.DtEvent).FirstOrDefault();
|
|
}
|
|
versatoQty = rawDepOrderData.Where(x => x.DtExecStart.Date == currDay).Sum(x => x.ExecutionQty);
|
|
if (firstRecord == null || nextRecord == null)
|
|
{
|
|
venduto = 0;
|
|
}
|
|
else
|
|
{
|
|
venduto = firstRecord.ValNumber - nextRecord.ValNumber;
|
|
venduto += versatoQty;
|
|
}
|
|
// salvo TS
|
|
SoldTS.Add(new TSData() { DtEvent = currDay, ValDouble = venduto });
|
|
}
|
|
|
|
LevelTS = rawLevelData
|
|
.Select(x => new TSData(x.DtEvent, x.ValNumber, true)).ToList();
|
|
//.Select(x => new TSData() { DtEvent = x.DtEvent, ValDouble = x.ValNumber }).ToList();
|
|
|
|
OrderTS = rawOpenOrderData
|
|
.Select(x => new TSData(x.DtOrder, x.OrderQty, true)).ToList();
|
|
//.Select(x => new TSData() { DtEvent = x.DtOrder, ValDouble = x.OrderQty }).ToList();
|
|
|
|
PressMainTS = rawMainPressData
|
|
.Select(x => new TSData(x.DtEvent, x.ValNumber, true)).ToList();
|
|
//.Select(x => new TSData() { DtEvent = x.DtEvent, ValDouble = x.ValNumber }).ToList();
|
|
|
|
PressBHTS = rawBHPressData
|
|
.Select(x => new TSData(x.DtEvent, x.ValNumber, true)).ToList();
|
|
//.Select(x => new TSData() { DtEvent = x.DtEvent, ValDouble = x.ValNumber }).ToList();
|
|
|
|
PressBLTS = rawBLPressData
|
|
.Select(x => new TSData(x.DtEvent, x.ValNumber, true)).ToList();
|
|
//.Select(x => new TSData() { DtEvent = x.DtEvent, ValDouble = x.ValNumber }).ToList();
|
|
|
|
PressTS.Add("Main", PressMainTS);
|
|
PressTS.Add("BH", PressBHTS);
|
|
PressTS.Add("BL", PressBLTS);
|
|
|
|
double actLevel = LevelTS.Count > 0 ? LevelTS.OrderByDescending(x => x.DtEvent).Take(1).FirstOrDefault().ValDouble : 0;
|
|
double valMain = PressMainTS.Count > 0 ? PressMainTS.OrderByDescending(x => x.DtEvent).Take(1).FirstOrDefault().ValDouble : 0;
|
|
double valBH = PressBHTS.Count > 0 ? PressBHTS.OrderByDescending(x => x.DtEvent).Take(1).FirstOrDefault().ValDouble : 0;
|
|
double valBL = PressBLTS.Count > 0 ? PressBLTS.OrderByDescending(x => x.DtEvent).Take(1).FirstOrDefault().ValDouble : 0;
|
|
|
|
lastRec = LevelTS.Count > 0 ? LevelTS.OrderByDescending(x => x.DtEvent).Take(1).FirstOrDefault().DtEvent : DateTime.Today;
|
|
|
|
PressAct.Add("Main", valMain);
|
|
PressAct.Add("BH", valBH);
|
|
PressAct.Add("BL", valBL);
|
|
|
|
// popolo valori
|
|
answ = new PlantDTO()
|
|
{
|
|
LastUpdate = lastRec,
|
|
PlantId = PlantId,
|
|
PlantCode = currPlant.PlantCode,
|
|
PlantDesc = currPlant.PlantDesc,
|
|
LevelAct = actLevel,
|
|
LevelMax = currPlant.LevelMax,
|
|
LevelReorder = currPlant.LevelReorder,
|
|
OrderQtyStd = currPlant.OrderQtyStd,
|
|
PressAct = PressAct,
|
|
LevelTS = LevelTS,
|
|
PressTS = PressTS,
|
|
OrderTS = OrderTS,
|
|
SoldTS = SoldTS
|
|
};
|
|
}
|
|
return answ;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Recupero da DB l'ultimo record per ogni flusso
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public List<PlantLogModel> PlantLogGetLastByFlux(int PlantId)
|
|
{
|
|
List<PlantLogModel> lastRec = new List<PlantLogModel>();
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
lastRec = localDbCtx
|
|
.DbSetPlantLog
|
|
.Where(x => x.PlantId == PlantId)
|
|
.OrderByDescending(o => o.DtEvent)
|
|
.Take(1000)
|
|
.AsEnumerable()
|
|
.GroupBy(g => g.FluxType)
|
|
.Select(s => s.First())
|
|
.ToList();
|
|
}
|
|
return lastRec;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Inserimento di un set di record x plant log
|
|
/// </summary>
|
|
/// <param name="PlantLogModel">Record da inserire (senza ID...)</param>
|
|
/// <returns></returns>
|
|
public bool PlantLogInsertNew(List<PlantLogModel> newItems)
|
|
{
|
|
bool fatto = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
localDbCtx
|
|
.DbSetPlantLog
|
|
.AddRange(newItems);
|
|
localDbCtx.SaveChanges();
|
|
fatto = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione durante PlantLogInsertNew per {newItems.Count} rec{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return fatto;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiorna un Plant
|
|
/// </summary>
|
|
/// <param name="updItem"></param>
|
|
/// <returns></returns>
|
|
public async Task<bool> PlantUpdateAsync(PlantDTO updItem)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
PlantDetailModel currData = await localDbCtx
|
|
.DbSetPlant
|
|
.Where(x => x.PlantId == updItem.PlantId)
|
|
.FirstOrDefaultAsync();
|
|
if (currData != null)
|
|
{
|
|
// aggiorno valori
|
|
currData.LevelReorder = updItem.LevelReorder;
|
|
currData.LevelMax = updItem.LevelMax;
|
|
localDbCtx.Entry(currData).State = EntityState.Modified;
|
|
int numDone = await localDbCtx.SaveChangesAsync();
|
|
done = numDone > 0;
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
public async Task<bool> RecordRebootLogAsync(RebootLogModel newItem)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
localDbCtx
|
|
.DbRebootLog
|
|
.Add(newItem);
|
|
int numRec = await localDbCtx.SaveChangesAsync();
|
|
done = numRec > 0;
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Annulla modifiche su una specifica entity (cancel update)
|
|
/// </summary>
|
|
/// <param name="item"></param>
|
|
/// <returns></returns>
|
|
public bool rollBackEntity(object item)
|
|
{
|
|
bool answ = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
if (localDbCtx.Entry(item).State == EntityState.Deleted || localDbCtx.Entry(item).State == EntityState.Modified)
|
|
{
|
|
localDbCtx.Entry(item).Reload();
|
|
}
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione in rollBackEntity{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return answ;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Elimina un item WeekPlan
|
|
/// </summary>
|
|
/// <param name="selRecord"></param>
|
|
/// <returns></returns>
|
|
public bool WeekPlanDelete(WeekPlanModel selRecord)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
var item2del = localDbCtx
|
|
.DbSetPlantSupplWeekPlan
|
|
.Where(x => x.WeekPlanId == selRecord.WeekPlanId)
|
|
.FirstOrDefault();
|
|
localDbCtx.DbSetPlantSupplWeekPlan.Remove(item2del);
|
|
localDbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione durante WeekPlanDelete{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Aggiorna un item WeekPlan
|
|
/// </summary>
|
|
/// <param name="updItem"></param>
|
|
/// <returns></returns>
|
|
public bool WeekPlanUpdate(WeekPlanModel updItem)
|
|
{
|
|
bool done = false;
|
|
using (GWMSContext localDbCtx = new GWMSContext(_configuration))
|
|
{
|
|
try
|
|
{
|
|
var currData = localDbCtx
|
|
.DbSetPlantSupplWeekPlan
|
|
.Where(x => x.WeekPlanId == updItem.WeekPlanId)
|
|
.FirstOrDefault();
|
|
if (currData != null)
|
|
{
|
|
currData.DayNum = updItem.DayNum;
|
|
currData.DeliveryHour = updItem.DeliveryHour;
|
|
currData.Note = updItem.Note;
|
|
currData.PlantId = updItem.PlantId;
|
|
currData.SupplierId = updItem.SupplierId;
|
|
currData.TransporterId = updItem.TransporterId;
|
|
|
|
localDbCtx.Entry(currData).State = EntityState.Modified;
|
|
}
|
|
else
|
|
{
|
|
localDbCtx
|
|
.DbSetPlantSupplWeekPlan
|
|
.Add(updItem);
|
|
}
|
|
localDbCtx.SaveChanges();
|
|
done = true;
|
|
}
|
|
catch (Exception exc)
|
|
{
|
|
Log.Error($"Eccezione durante WeekPlanUpdate{Environment.NewLine}{exc}");
|
|
}
|
|
}
|
|
return done;
|
|
}
|
|
|
|
#endregion Public Methods
|
|
|
|
#region Private Fields
|
|
|
|
private static IConfiguration _configuration;
|
|
private static Logger Log = LogManager.GetCurrentClassLogger();
|
|
|
|
#endregion Private Fields
|
|
}
|
|
} |