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
    }
}