using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using MP.Core.DTO;
using MP.Core.Objects;
using MP.Data.DbModels;
using NLog;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using static EgwCoreLib.Utils.DtUtils;

namespace MP.Data.Controllers
{
    public class MpSpecController : IDisposable
    {
        #region Public Constructors

        public MpSpecController(IConfiguration configuration)
        {
            _configuration = configuration;
            string connStr = _configuration.GetConnectionString("MP.Data");
            options = new DbContextOptionsBuilder<MoonProContext>()
                                .UseSqlServer(connStr)
                                .Options;
            Log.Info("Avviata classe MpSpecController");
        }

        #endregion Public Constructors

        #region Public Methods

        /// <summary>
        /// Elenco di tutti i counter coi valori correnti
        /// </summary>
        /// <returns></returns>
        public List<AnagCountersModel> AnagCounters()
        {
            List<AnagCountersModel> dbResult = new List<AnagCountersModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                           .DbSetAnagCount
                           .AsNoTracking()
                           .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Stacca un nuovo counter x il tipo richiesto
        /// </summary>
        /// <returns></returns>
        public AnagCountersModel AnagCountersGetNext(string cntType)
        {
            AnagCountersModel answ = new AnagCountersModel();
            bool outTable = true;
            if (outTable)
            {
                using (var dbCtx = new MoonProContext(options))
                {
                    var pCntType = new SqlParameter("@CntType", cntType);
                    var pLastNum = new SqlParameter
                    {
                        ParameterName = "@LastNum",
                        SqlDbType = SqlDbType.Int,
                        Direction = ParameterDirection.Output
                    };

                    var dbResult = dbCtx
                                    .DbSetAnagCount
                                    .FromSqlRaw("EXEC dbo.stp_getNextNumb @CntType, @LastNum OUTPUT", pCntType, pLastNum)
                                    .AsNoTracking()
                                    .AsEnumerable()
                                    .FirstOrDefault();
                    if (dbResult != null)
                    {
                        answ = dbResult;
                    }
                }
            }
            else
            {
                // se si volessero impiegare parametri OUTPUT (qui ne mancherebbe 1 nella stored x CntCode...)
                using (var dbCtx = new MoonProContext(options))
                {
                    var pCntType = new SqlParameter("@CntType", cntType);
                    var pLastNum = new SqlParameter
                    {
                        ParameterName = "@LastNum",
                        SqlDbType = SqlDbType.Int,
                        Direction = ParameterDirection.Output
                    };
                    var pCntCode = new SqlParameter
                    {
                        ParameterName = "@CntCode",
                        SqlDbType = SqlDbType.NVarChar,
                        Direction = ParameterDirection.Output
                    };
                    var dbResult = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC dbo.stp_getNextNumb @CntType, @LastNum OUTPUT, @CntCode OUTPUT", pCntType, pLastNum, pCntCode);
                    if (dbResult != 0)
                    {
                        answ.CntType = cntType;
                        answ.CntCode = $"{pCntCode.Value}";
                        int lNum = 0;
                        int.TryParse($"{pLastNum.Value}", out lNum);
                        answ.LastNum = lNum;
                    }
                }
            }
            return answ;
        }

        /// <summary>
        /// Restituisce l'anagrafica EVENTI generalmente disponibile per OGNI macchina
        /// </summary>
        /// <param name="TableName">Nome Table x filtro (std: EvList)</param>
        /// <param name="FieldName">Nome Field x filtro (std: Common)</param>
        /// <returns></returns>
        public List<vSelEventiBCodeModel> AnagEventiGeneral(string TableName = "EvList", string FieldName = "Common")
        {
            List<vSelEventiBCodeModel> dbResult = new List<vSelEventiBCodeModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var pTableName = new SqlParameter("@TableName", TableName);
                var pFieldName = new SqlParameter("@FieldName", FieldName);
                dbResult = dbCtx
                           .DbSetVSEB
                           .FromSqlRaw("exec dbo.stp_vseb_getGenerallyAvailable @TableName, @FieldName", pTableName, pFieldName)
                           .AsNoTracking()
                           .AsEnumerable()
                           .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Restituisce l'anagrafica EVENTI per macchina
        /// </summary>
        /// <returns></returns>
        public List<vSelEventiBCodeModel> AnagEventiGetByMacc(string IdxMac)
        {
            List<vSelEventiBCodeModel> dbResult = new List<vSelEventiBCodeModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacch = new SqlParameter("@idxMacchina", IdxMac);

                dbResult = dbCtx
                           .DbSetVSEB
                           .FromSqlRaw("exec dbo.stp_vseb_getByIdxMacchinaFull @idxMacchina", IdxMacch)
                           .AsNoTracking()
                           .AsEnumerable()
                           .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Gruppi tipo Azienda
        /// </summary>
        /// <returns></returns>
        public List<AnagGruppiModel> AnagGruppiAziende()
        {
            return AnagGruppiGetTipo("AZIENDA");
        }

        /// <summary>
        /// Delete record AnagraficaGruppi
        /// </summary>
        /// <returns></returns>
        public bool AnagGruppiDelete(AnagGruppiModel updRec)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var dbRec = dbCtx
                            .DbSetAnagGruppi
                            .AsNoTracking()
                            .Where(x => x.CodGruppo == updRec.CodGruppo)
                            .FirstOrDefault();
                // se trovato aggiorno descrizione (resto immutato x sicurezza!)
                if (dbRec != null)
                {
                    dbCtx.DbSetAnagGruppi.Remove(dbRec);
                }
                var numRes = dbCtx.SaveChanges();
                answ = numRes != 0;
            }
            return answ;
        }

        /// <summary>
        /// Elenco Gruppi tipo Fasi
        /// </summary>
        /// <returns></returns>
        public List<AnagGruppiModel> AnagGruppiFase()
        {
            return AnagGruppiGetTipo("FASE");
        }

        /// <summary>
        /// Elenco Gruppi
        /// </summary>
        /// <returns></returns>
        public List<AnagGruppiModel> AnagGruppiGetAll()
        {
            List<AnagGruppiModel> dbResult = new List<AnagGruppiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetAnagGruppi
                            .AsNoTracking()
                            .OrderBy(x => x.CodGruppo)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Gruppi x tipo
        /// </summary>
        /// <param name="tipoGruppo"></param>
        /// <returns></returns>
        public List<AnagGruppiModel> AnagGruppiGetTipo(string tipoGruppo)
        {
            List<AnagGruppiModel> dbResult = new List<AnagGruppiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetAnagGruppi
                            .Where(x => x.TipoGruppo == tipoGruppo)
                            .AsNoTracking()
                            .OrderBy(x => x.CodGruppo)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Gruppi tipo REPARTO (x associazione Macchine-Operatori)
        /// </summary>
        /// <returns></returns>
        public List<AnagGruppiModel> AnagGruppiReparto()
        {
            return AnagGruppiGetTipo("REPARTO");
        }

        /// <summary>
        /// Elenco Gruppi tipo REPARTO (x associazione Macchine-Operatori) in formato DTO con conteggi del numero record trovati
        /// </summary>
        /// <returns></returns>
        public List<RepartiDTO> AnagGruppiRepartoDTO()
        {
            List<RepartiDTO> dbResult = new List<RepartiDTO>();
            using (var dbCtx = new MoonProContext(options))
            {
                // in primis recupero i reparti...
                var listReparti = AnagGruppiGetTipo("REPARTO");

                // recupero TUTTE le macchine da DbSetGrp2Macc
                var listMacc = dbCtx
                                .DbSetGrp2Macc
                                .AsNoTracking()
                                .ToList();
                // recupero TUTTI gli operatori da DbSetGrp2Oper
                var listOpr = dbCtx
                                .DbSetGrp2Oper
                                .AsNoTracking()
                                .ToList();

                dbResult = listReparti
                                .Select(x => new RepartiDTO()
                                {
                                    CodGruppo = x.CodGruppo,
                                    TipoGruppo = x.TipoGruppo,
                                    DescrGruppo = x.DescrGruppo,
                                    SelEnabled = x.SelEnabled,
                                    CountMacc = listMacc.Where(y => y.CodGruppo == x.CodGruppo).Select(x => x.IdxMacchina).Distinct().Count(),
                                    CountOpr = listOpr.Where(y => y.CodGruppo == x.CodGruppo).Select(x => x.MatrOpr).Distinct().Count()
                                })
                                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Upsert record AnagraficaGruppi (solo codice/descrizione)
        /// </summary>
        /// <param name="updRec"></param>
        /// <returns></returns>
        public bool AnagGruppiUpsert(AnagGruppiModel updRec)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var dbRec = dbCtx
                            .DbSetAnagGruppi
                            .AsNoTracking()
                            .Where(x => x.CodGruppo == updRec.CodGruppo)
                            .FirstOrDefault();
                // se trovato aggiorno descrizione (resto immutato x sicurezza!)
                if (dbRec != null)
                {
                    dbRec.DescrGruppo = updRec.DescrGruppo;
                    dbCtx.Entry(dbRec).State = EntityState.Modified;
                }
                // altrimenti aggiungo
                else
                {
                    dbCtx.DbSetAnagGruppi.Add(updRec);
                }
                var numRes = dbCtx.SaveChanges();
                answ = numRes != 0;
            }
            return answ;
        }

        /// <summary>
        /// Elenco Gruppi
        /// </summary>
        /// <returns></returns>
        public List<AnagKeyValueModel> AnagKeyValGetAll()
        {
            List<AnagKeyValueModel> dbResult = new List<AnagKeyValueModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetAKV
                            .AsNoTracking()
                            .OrderBy(x => x.nomeVar)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco valori ammessi x Stati commessa (es Yacht Baglietto)
        /// </summary>
        /// <returns></returns>
        public List<ListValuesModel> AnagStatiComm()
        {
            return ListValuesFilt("PODL", "StatoComm");
        }

        /// <summary>
        /// Elenco valori ammessi x Tipo articoli
        /// </summary>
        /// <returns></returns>
        public List<ListValuesModel> AnagTipoArtLV()
        {
            return ListValuesFilt("AnagArticoli", "Tipo");
        }

        /// <summary>
        /// Elenco codice articoli che abbiano dati Dossier
        /// </summary>
        /// <returns></returns>
        public List<string> ArticleWithDossier()
        {
            List<string> dbResult = new List<string>();
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetDossiers
                            .AsNoTracking()
                            .Select(i => i.CodArticolo)
                            .Distinct()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Eliminazione Record
        /// </summary>
        /// <param name="currRec"></param>
        /// <returns></returns>
        public async Task<bool> ArticoliDeleteRecord(AnagArticoliModel currRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var currVal = dbCtx
                                    .DbSetArticoli
                                    .Where(x => x.CodArticolo == currRec.CodArticolo)
                                    .FirstOrDefault();
                    dbCtx
                        .DbSetArticoli
                        .Remove(currVal);
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ArticoliDeleteRecord{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Restitusice elenco articoli dato tipo (es KIT)
        /// </summary>
        /// <param name="tipo"></param>
        /// <param name="azienda"></param>
        /// </summary>
        /// <returns></returns>
        public List<AnagArticoliModel> ArticoliGetByTipo(string tipo, string azienda = "*")
        {
            List<AnagArticoliModel> dbResult = new List<AnagArticoliModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetArticoli
                            .AsNoTracking()
                            .Where(x => x.Tipo.ToUpper() == tipo.ToUpper() && (azienda == "*" || x.Azienda.ToUpper() == azienda.ToUpper()))
                            .OrderBy(x => x.CodArticolo)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco tabella Articoli da filtro
        /// </summary>
        /// <param name="numRecord"></param>
        /// <param name="searchVal"></param>
        /// <returns></returns>
        public List<AnagArticoliModel> ArticoliGetSearch(int numRecord, string searchVal = "")
        {
            List<AnagArticoliModel> dbResult = new List<AnagArticoliModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                .DbSetArticoli
                .AsNoTracking()
                .Where(x => x.CodArticolo.Contains(searchVal) || x.DescArticolo.Contains(searchVal) || x.Disegno.Contains(searchVal) || string.IsNullOrEmpty(searchVal))
                .OrderBy(x => x.CodArticolo)
                .Take(numRecord)
                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco tabella Articoli da filtro
        /// </summary>
        /// <param name="numRecord"></param>
        /// <param name="azienda"></param>
        /// <param name="searchVal"></param>
        /// <returns></returns>
        public List<AnagArticoliModel> ArticoliGetSearch(int numRecord, string azienda = "*", string searchVal = "")
        {
            List<AnagArticoliModel> dbResult = new List<AnagArticoliModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                    .DbSetArticoli
                    .AsNoTracking()
                    .Where(x => (azienda == "*" || x.Azienda.ToUpper() == azienda.ToUpper()) && (string.IsNullOrEmpty(searchVal) || x.CodArticolo.Contains(searchVal) || x.DescArticolo.Contains(searchVal) || x.Disegno.Contains(searchVal)))
                    .OrderBy(x => x.CodArticolo)
                    .Take(numRecord)
                    .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco tabella Articoli IMPIEGATI (da stored stp_ART_getUsed)
        /// </summary>
        /// <returns></returns>
        public List<AnagArticoliModel> ArticoliGetUsed()
        {
            List<AnagArticoliModel> dbResult = new List<AnagArticoliModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                    .DbSetArticoli
                    .FromSqlRaw("EXEC stp_ART_getUsed")
                    .AsNoTracking()
                    .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Update Record
        /// </summary>
        /// <param name="currRec"></param>
        /// <returns></returns>
        public async Task<bool> ArticoliUpdateRecord(AnagArticoliModel editRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var currRec = dbCtx
                                    .DbSetArticoli
                                    .Where(x => x.CodArticolo == editRec.CodArticolo)
                                    .FirstOrDefault();
                    if (currRec != null)
                    {
                        currRec.Disegno = editRec.Disegno;
                        currRec.DescArticolo = editRec.DescArticolo;
                        currRec.Tipo = editRec.Tipo;
                        currRec.Azienda = editRec.Azienda;
                        dbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx
                            .DbSetArticoli
                            .Add(editRec);
                    }
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ArticoliUpdateRecord{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Elenco da tabella Config
        /// </summary>
        /// <returns></returns>
        public List<ConfigModel> ConfigGetAll()
        {
            List<ConfigModel> dbResult = new List<ConfigModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetConfig
                            .AsNoTracking()
                            .OrderBy(x => x.Chiave)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Update record config
        /// </summary>
        /// <returns></returns>
        public bool ConfigUpdate(ConfigModel updRec)
        {
            bool fatto = false;
            ConfigModel dbResult = new ConfigModel();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetConfig
                            .Where(x => x.Chiave == updRec.Chiave)
                            .FirstOrDefault();
                if (dbResult != null)
                {
                    dbResult.Valore = updRec.Valore;
                    dbCtx.SaveChanges();
                    fatto = true;
                }
            }
            return fatto;
        }

        /// <summary>
        /// Intera tab dati macchina
        /// </summary>
        /// <returns></returns>
        public List<DatiMacchineModel> DatiMacchineGetAll()
        {
            List<DatiMacchineModel> dbResult = new List<DatiMacchineModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetDatiMacchine
                            .AsNoTracking()
                            .OrderBy(x => x.IdxMacchina)
                            .ToList();
            }
            return dbResult;
        }

        public void Dispose()
        {
            _configuration = null;
        }

        /// <summary>
        /// Eliminazione di un dossier
        /// </summary>
        /// <param name="currRec">record dossier da eliminare</param>
        /// <returns></returns>
        public async Task<bool> DossiersDeleteRecord(DossierModel currRec)
        {
            bool answ = false;
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                try
                {
                    var currVal = dbCtx
                                .DbSetDossiers
                                .Where(x => x.IdxDossier == currRec.IdxDossier)
                                .FirstOrDefault();
                    dbCtx
                        .DbSetDossiers
                        .Remove(currVal);
                    await dbCtx.SaveChangesAsync();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DossiersDeleteRecord{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Elenco ultimi n record DOssiers (che contengono ad esempio "salvataggi" di FLuxLog) dato
        /// macchina (ordinato x data registrazione)
        /// </summary>
        /// <param name="IdxMacchina">* = tutte, altrimenti solo x una data macchina</param>
        /// <param name="CodArticolo">* = tutti, altrimenti solo x un dato articolo</param>
        /// <param name="DtStart">Data minima per estrazione records</param>
        /// <param name="DtEnd">Data Massima per estrazione records</param>
        /// <param name="MaxRec">Num max record recuperati</param>
        /// <returns></returns>
        public async Task<List<DossierModel>> DossiersGetLastFiltAsync(string IdxMacchina, string CodArticolo, DateTime DtStart, DateTime DtEnd, int MaxRec)
        {
            List<DossierModel> dbResult = new List<DossierModel>();
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                dbResult = await dbCtx
                            .DbSetDossiers
                            .AsNoTracking()
                            .Where(x => (IdxMacchina == "*" || x.IdxMacchina == IdxMacchina) && (CodArticolo == "*" || x.CodArticolo == CodArticolo) && (x.DtRif >= DtStart && x.DtRif <= DtEnd))
                            .Include(m => m.MachineNav)
                            .Include(a => a.ArticoloNav)
                            .OrderByDescending(x => x.DtRif)
                            .Take(MaxRec)
                            .ToListAsync();
            }
            return dbResult;
        }

        /// <summary>
        /// insert di un record Dossier
        /// </summary>
        /// <param name="editRec">record dossier da modificare</param>
        /// <returns></returns>
        public async Task<bool> DossiersInsert(DossierModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                try
                {
                    dbCtx
                        .DbSetDossiers
                        .Add(newRec);
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DossiersInsert{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Effettua salvataggio snapshot parametri (con stored) + svuota eventuale cache _redisConn
        /// </summary>
        /// <param name="idxMacchina">macchina</param>
        /// <param name="maxSec">Num massimo secondi per recuperare dati correnti</param>
        /// <param name="dtRif">DataOra riferimento x cui prendere valori antecedenti</param>
        public bool DossiersTakeParamsSnapshot(string idxMacchina, int maxSec, DateTime dtRif)
        {
            bool answ = false;
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                var pIdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var pMaxSec = new SqlParameter("@MaxSec", maxSec);
                var pDtRif = new SqlParameter("@DtRif", dtRif);

                var dbResult = dbCtx
                    .Database
                    .ExecuteSqlRaw("EXEC stp_FL_TakeSnapshot @IdxMacchina,@MaxSec,@DtRif", pIdxMacchina, pMaxSec, pDtRif);
                answ = true;
            }
            return answ;
        }

        /// <summary>
        /// Effettua salvataggio snapshot parametri (con stored) + svuota eventuale cache _redisConn
        /// </summary>
        /// <param name="idxMacchina">macchina</param>
        /// <param name="dtMin">Data min x selezione</param>
        /// <param name="dtMax">Data MAX x selezione</param>
        public bool DossiersTakeParamsSnapshotLast(string idxMacchina, DateTime dtMin, DateTime dtMax)
        {
            bool answ = false;
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                var pIdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var pDtMin = new SqlParameter("@DtMin", dtMin);
                var pDtMax = new SqlParameter("@DtMax", dtMax);

                var dbResult = dbCtx
                    .Database
                    .ExecuteSqlRaw("EXEC stp_FL_TakeSnapshotLast @IdxMacchina,@DtMin,@DtMax", pIdxMacchina, pDtMin, pDtMax);
                answ = true;
            }
            return answ;
        }

        /// <summary>
        /// Update del campo VALORE di un dossier (che contiene json flux log serializzati)
        /// </summary>
        /// <param name="editRec">record dossier da modificare</param>
        /// <returns></returns>
        public async Task<bool> DossiersUpdateValore(DossierModel editRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                try
                {
                    var currRec = dbCtx
                                    .DbSetDossiers
                                    .Where(x => x.IdxDossier == editRec.IdxDossier)
                                    .FirstOrDefault();
                    if (currRec != null)
                    {
                        currRec.Valore = editRec.Valore;
                        dbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx
                            .DbSetDossiers
                            .Add(editRec);
                    }
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DossiersUpdateRecord{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Elenco valori link (x home e navMenu laterale)
        /// </summary>
        /// <returns></returns>
        public List<LinkMenuModel> ElencoLink()
        {
            return ListLinkFilt("SpecLink");
        }

        /// <summary>
        /// Elenco operatori
        /// </summary>
        /// <returns></returns>
        public List<AnagOperatoriModel> ElencoOperatori()
        {
            List<AnagOperatoriModel> dbResult = new List<AnagOperatoriModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbOperatori
                            .Where(s => s.MatrOpr > 0)
                            .AsNoTracking()
                            .OrderBy(x => x.MatrOpr)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiunta record EventList
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> EvListInsert(EventListModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var currRec = dbCtx
                                    .DbSetEvList
                                    .Add(newRec);
                    await dbCtx.SaveChangesAsync();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante EvListInsert{Environment.NewLine}{exc}");
                }
            }
            await Task.Delay(1);
            return fatto;
        }

        /// <summary>
        /// Funzione di Data Reduction x FluxLog
        /// </summary>
        /// <param name="idxMaccSel"></param>
        /// <param name="fluxList"></param>
        /// <param name="currPeriodo"></param>
        /// <param name="valMode"></param>
        /// <param name="intReq"></param>
        /// <returns>
        /// Restitusice list dei record statistiche raccolti (da integrare a quelli rpesenti in Redis...)
        /// </returns>
        public async Task<List<StatDedupDTO>> FluxLogDataRedux(string idxMaccSel, List<string> fluxList, Periodo currPeriodo, Enums.ValSelection valMode, Enums.DataInterval intReq, int maxItem)
        {
            List<StatDedupDTO> procStats = new List<StatDedupDTO>();
            Log.Info($"Inizio FluxLogDataRedux | idxMaccSel: {idxMaccSel} | periodo: {currPeriodo.Inizio:yyyy-MM-dd} --> {currPeriodo.Fine:yyyy-MM-dd}");
            TimeSpan step = TimeSpan.FromHours(1);
            switch (intReq)
            {
                case Enums.DataInterval.minute:
                    step = TimeSpan.FromMinutes(1.00 / maxItem);
                    break;

                case Enums.DataInterval.hour:
                    step = TimeSpan.FromHours(1.00 / maxItem);
                    break;

                case Enums.DataInterval.day:
                    step = TimeSpan.FromDays(1.00 / maxItem);
                    break;

                default:
                    break;
            }

            // setup parametri costanti x stored
            var pIdxMacchina = new SqlParameter("@IdxMacchina", idxMaccSel);
            var pOnlyTest = new SqlParameter("@OnlyTest", false);

            // processo 1:1 ogni flusso
            foreach (var item in fluxList)
            {
                Log.Info($"FluxLogDataRedux | Flux: {item}");
                int numRecProc = 0;
                Stopwatch sw = new Stopwatch();
                sw.Start();
                // parametri x flusso
                var pCodFlux = new SqlParameter("@CodFlux", item);
                // inizializzo cursore timer
                DateTime dtCursStart = currPeriodo.Inizio;
                DateTime dtCursEnd = dtCursStart.Add(step);
                bool setCompleted = false;
                // dbContext x ogni singolo flusso
                using (var dbCtx = new MoonPro_FluxContext(_configuration))
                {
                    // opzionalmente timeout comandi a 2 minuti... NON usato x ora e da testare
                    //dbCtx.Database.SetCommandTimeout(TimeSpan.FromMinutes(2));
                    // li processo per intervallo richiesto, cercando dati nel periodo e
                    // selezionando VC
                    while (!setCompleted)
                    {
                        // ora recupero TUTTI i dati della macchina
                        var currFlux = await dbCtx
                                 .DbSetFluxLog
                                 .Where(x => (x.CodFlux == item) && (x.dtEvento >= dtCursStart && x.dtEvento < dtCursEnd) && (x.IdxMacchina == idxMaccSel))
                                 .ToListAsync();

                        int numRec = currFlux.Count;
                        numRecProc += numRec;
                        if (numRec > maxItem)
                        {
                            List<Periodo> listPeriodi = new List<Periodo>();

                            switch (valMode)
                            {
                                case Enums.ValSelection.First:
                                    // recupero 2° item
                                    var recStart = currFlux.Skip(1).FirstOrDefault();
                                    // salvo periodo!
                                    listPeriodi.Add(new Periodo(recStart.dtEvento, dtCursEnd));
                                    break;

                                case Enums.ValSelection.Last:
                                    // recupero ultimo item
                                    var recEnd = currFlux.LastOrDefault();
                                    // salvo periodo!
                                    listPeriodi.Add(new Periodo(dtCursStart, recEnd.dtEvento));
                                    break;

                                case Enums.ValSelection.Center:
                                    int idx = 1;
                                    // per iniziare mi metto a 1/(n+1) rec come step
                                    var recCent = currFlux.Skip(idx / (maxItem + 1)).FirstOrDefault();
                                    listPeriodi.Add(new Periodo(dtCursStart, recCent.dtEvento));
                                    // salvo restanti periodi (se > 1)!
                                    if (maxItem > 1)
                                    {
                                        for (int i = 2; i < maxItem; i++)
                                        {
                                            DateTime dtInizio = recCent.dtEvento;
                                            recCent = currFlux.Skip(i / (maxItem + 1)).FirstOrDefault();
                                            listPeriodi.Add(new Periodo(dtInizio, recCent.dtEvento));
                                        }
                                    }
                                    // aggiungo ultimo...
                                    listPeriodi.Add(new Periodo(recCent.dtEvento.AddSeconds(1), dtCursEnd));
                                    break;

                                default:
                                    break;
                            }

                            // ciclo x tutti i periodi e chiamo stored...
                            foreach (var slot in listPeriodi)
                            {
                                // parametri x periodo (base)
                                var pDtStart = new SqlParameter("@DtStart", slot.Inizio);
                                var pDtEnd = new SqlParameter("@DtEnd", slot.Fine);
                                var dbResult = dbCtx
                                        .Database
                                        .ExecuteSqlRaw("EXEC man.stp_ReduceFluxLog @IdxMacchina, @CodFlux, @DtStart, @DtEnd, @OnlyTest", pIdxMacchina, pCodFlux, pDtStart, pDtEnd, pOnlyTest);
                            }
                        }

                        // incremento dt fine periodo
                        dtCursStart = dtCursEnd;
                        dtCursEnd = dtCursStart.Add(step);
                        setCompleted = dtCursStart >= currPeriodo.Fine;
                    }
                }
                // fermo cronometro e salvo su DB...
                sw.Stop();
                StatDedupDTO currStat = new StatDedupDTO()
                {
                    IdxMacchina = idxMaccSel,
                    CodFlux = item,
                    Interval = intReq,
                    Num4Int = maxItem,
                    NumRec = numRecProc,
                    ProcTime = sw.Elapsed.TotalSeconds
                };
                procStats.Add(currStat);
            }
            Log.Info($"FINE FluxLogDataRedux | idxMaccSel: {idxMaccSel} | periodo: {currPeriodo.Inizio:yyyy-MM-dd} --> {currPeriodo.Fine:yyyy-MM-dd}");
            return procStats;
        }

        /// <summary>
        /// Elenco ultimi n record flux log dato macchina e flusso (ordinato x data registrazione)
        /// </summary>
        /// <param name="DtMax">Data massima x eventi</param>
        /// <param name="DtMin">Data minima x eventi</param>
        /// <param name="IdxMacchina">* = tutte, altrimenti solo x una data macchina</param>
        /// <param name="CodFlux">*=tutti, altrimenti solo selezionato</param>
        /// <param name="MaxRec">numero massimo record da restituire</param>
        /// <returns></returns>
        public List<FluxLogModel> FluxLogGetLastFilt(DateTime DtMax, DateTime DtMin, string IdxMacchina, string CodFlux, int MaxRec)
        {
            List<FluxLogModel> dbResult = new List<FluxLogModel>();
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetFluxLog
                            .AsNoTracking()
                            .Where(x => (x.dtEvento >= DtMin && x.dtEvento <= DtMax) && (IdxMacchina == "*" || x.IdxMacchina == IdxMacchina) && (CodFlux == "*" || x.CodFlux == CodFlux))
                            .OrderByDescending(x => x.dtEvento)
                            .Take(MaxRec)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Gruppi
        /// </summary>
        /// <returns></returns>
        public List<ParetoFluxLogDTO> FluxLogPareto(string idxMacchina, DateTime dtFrom, DateTime dtTo)
        {
            List<ParetoFluxLogDTO> dbResult = new List<ParetoFluxLogDTO>();
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetFluxLog
                            .Where(x => (string.IsNullOrEmpty(idxMacchina) || x.IdxMacchina == idxMacchina) && (dtFrom <= x.dtEvento && x.dtEvento <= dtTo))
                            .AsNoTracking()
                            .GroupBy(x => x.CodFlux)
                            .Select(g => new ParetoFluxLogDTO() { IdxMacchina = idxMacchina, CodFlux = g.Key, Qty = g.Count() })
                            .OrderByDescending(x => x.Qty)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Stored manutenzione del DB
        /// </summary>
        /// <param name="doExec">Esegue realmente il task</param>
        /// <param name="doUpdStat">Aggiornamento statistiche</param>
        /// <param name="doSave">Salvataggio</param>
        /// <param name="minPgCnt">def: 1000</param>
        /// <param name="minAvgFrag">def: 10</param>
        /// <param name="maxAvgFragReb">def: 50</param>
        /// <returns></returns>
        public async Task<bool> ForceDbMaint(bool doExec, bool doUpdStat, bool doSave, int minPgCnt, int minAvgFrag, int maxAvgFragReb)
        {
            Log.Info($"Inizio ForceDbMaint on MoonProAdminContext");
            bool fatto = false;
            // uso context admin x query lunghe
            using (var dbCtx = new MoonProAdminContext(_configuration))
            {
                var pFlgExec = new SqlParameter("@FlgExec", doExec ? "Y" : "N");
                var pFlgUpdStat = new SqlParameter("@FlgUpdStat", doUpdStat ? "Y" : "N");
                var pFlgSave = new SqlParameter("@FlgSave", doSave ? "Y" : "N");
                var pMinPgCnt = new SqlParameter("@min_page_count", minPgCnt);
                var pMinAvgFrag = new SqlParameter("@min_avg_fragmentation_in_percent", minAvgFrag);
                var pMaxAvgFrag = new SqlParameter("@max_avg_fragmentation_per_rebuild", maxAvgFragReb);

                var dbResult = await dbCtx
                        .Database
                        .ExecuteSqlRawAsync("EXEC man.stp_Utility_Maintanance");
                //.ExecuteSqlRaw("EXEC man.stp_Utility_Maintanance @FlgExec, @FlgUpdStat, @FlgSave, @min_page_count, @min_avg_fragmentation_in_percent, @max_avg_fragmentation_per_rebuild", pFlgExec, pFlgUpdStat, pFlgSave, pMinPgCnt, pMinAvgFrag, pMaxAvgFrag);
                fatto = true;
            }
            Log.Info($"FINE ForceDbMaint on MoonProAdminContext");
            return fatto;
        }

        /// <summary>
        /// Eliminazione di un record macchina dal gruppo
        /// </summary>
        /// <param name="rec2del"></param>
        /// <returns></returns>
        public bool Grp2MaccDelete(Gruppi2MaccModel rec2del)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var dbRec = dbCtx
                            .DbSetGrp2Macc
                            .Where(x => x.CodGruppo == rec2del.CodGruppo && x.IdxMacchina == rec2del.IdxMacchina)
                            .FirstOrDefault();
                if (dbRec != null)
                {
                    dbCtx.DbSetGrp2Macc.Remove(dbRec);
                    int numDone = dbCtx.SaveChanges();
                    answ = numDone != 0;
                }
            }
            return answ;
        }

        /// <summary>
        /// Insert  di un record macchina
        /// </summary>
        /// <param name="upsRec"></param>
        /// <returns></returns>
        public bool Grp2MaccInsert(Gruppi2MaccModel upsRec)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var dbRec = dbCtx
                            .DbSetGrp2Macc
                            .Where(x => x.CodGruppo == upsRec.CodGruppo && x.IdxMacchina == upsRec.IdxMacchina)
                            .FirstOrDefault();
                if (dbRec == null)
                {
                    dbCtx.DbSetGrp2Macc.Add(upsRec);
                    // salvo
                    int numDone = dbCtx.SaveChanges();
                    answ = numDone != 0;
                }
            }
            return answ;
        }

        /// <summary>
        /// Eliminazione di un record operatore dal gruppo
        /// </summary>
        /// <param name="rec2del"></param>
        /// <returns></returns>
        public bool Grp2OperDelete(Gruppi2OperModel rec2del)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var dbRec = dbCtx
                            .DbSetGrp2Oper
                            .Where(x => x.CodGruppo == rec2del.CodGruppo && x.MatrOpr == rec2del.MatrOpr)
                            .FirstOrDefault();
                if (dbRec != null)
                {
                    dbCtx.DbSetGrp2Oper.Remove(dbRec);
                    int numDone = dbCtx.SaveChanges();
                    answ = numDone != 0;
                }
            }
            return answ;
        }

        /// <summary>
        /// Insert di un record operatore
        /// </summary>
        /// <param name="upsRec"></param>
        /// <returns></returns>
        public bool Grp2OperInsert(Gruppi2OperModel upsRec)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var dbRec = dbCtx
                            .DbSetGrp2Oper
                            .Where(x => x.CodGruppo == upsRec.CodGruppo && x.MatrOpr == upsRec.MatrOpr)
                            .FirstOrDefault();
                if (dbRec == null)
                {
                    dbCtx.DbSetGrp2Oper.Add(upsRec);
                    // salvo
                    int numDone = dbCtx.SaveChanges();
                    answ = numDone != 0;
                }
            }
            return answ;
        }

        /// <summary>
        /// Elimina record
        /// </summary>
        /// <param name="rec2del"></param>
        public bool IstKitDelete(IstanzeKitModel rec2del)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                                .DbSetInstKit
                                .Where(x => x.KeyKit == rec2del.KeyKit && x.KeyExtOrd == rec2del.KeyExtOrd)
                                .FirstOrDefault();
                // se ci fosse aggiorno...
                if (actRec != null)
                {
                    dbCtx
                        .DbSetInstKit
                        .Remove(actRec);
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elenco istanze KIT da ricerca
        /// </summary>
        /// <param name="keyKit"></param>
        /// <param name="keyExtOrd"></param>
        /// <returns></returns>
        public List<IstanzeKitModel> IstKitFilt(string keyKit, string keyExtOrd)
        {
            List<IstanzeKitModel> dbResult = new List<IstanzeKitModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                           .DbSetInstKit
                           .Where(x => (string.IsNullOrEmpty(keyKit) && string.IsNullOrEmpty(keyExtOrd)) || (x.KeyKit.Contains(keyKit) && !string.IsNullOrEmpty(keyKit)) || (x.KeyExtOrd.Contains(keyExtOrd) && !string.IsNullOrEmpty(keyExtOrd)))
                           .AsNoTracking()
                           .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Effettua creazione istanza KIT
        /// </summary>
        /// <param name="CodArtParent">Articolo KIT (fittizio)</param>
        /// <param name="KeyFilt">Chiave x filtro conf su tab WKS</param>
        public bool IstKitInsertByWKS(string CodArtParent, string KeyFilt)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var pCodArtParent = new SqlParameter("@CodArtParent", CodArtParent);
                var pKeyFilt = new SqlParameter("@KeyFilt", KeyFilt);

                var dbResult = dbCtx
                    .Database
                    .ExecuteSqlRaw("EXEC dbo.stp_IstKit_insertByWKS @CodArtParent,@KeyFilt", pCodArtParent, pKeyFilt);
                answ = true;
            }
            return answ;
        }

        /// <summary>
        /// Esegue upsert record
        /// </summary>
        /// <param name="editRec"></param>
        public bool IstKitUpsert(IstanzeKitModel editRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                                .DbSetInstKit
                                .Where(x => x.KeyKit == editRec.KeyKit && x.KeyExtOrd == editRec.KeyExtOrd)
                                .FirstOrDefault();

                // se ci fosse aggiorno...
                if (actRec == null)
                {
                    dbCtx
                        .DbSetInstKit
                        .Add(editRec);
                }
                else
                {
                    actRec.CodArtParent = editRec.CodArtParent;
                    actRec.CodArtChild = editRec.CodArtChild;
                    actRec.QtyART = editRec.QtyART;
                    actRec.QtyKIT = editRec.QtyKIT;
                    dbCtx.Entry(actRec).State = EntityState.Modified;
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elenco giacenze
        /// </summary>
        /// <param name="IdxOdl">id odl da cercare</param>
        /// <returns></returns>
        public List<AnagGiacenzeModel> ListGiacenze(int IdxOdl)
        {
            List<AnagGiacenzeModel> dbResult = new List<AnagGiacenzeModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    dbResult = dbCtx
                                .DbGiacenzeData
                                .AsNoTracking()
                                .Where(x => x.IdxOdl == IdxOdl)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ListGiacenze{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco link JQM completo
        /// </summary>
        /// <param name="tipoLink"></param>
        /// <returns></returns>
        public List<LinkMenuModel> ListLinkAll()
        {
            List<LinkMenuModel> dbResult = new List<LinkMenuModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetLinkMenu
                            .AsNoTracking()
                            .OrderBy(x => x.Ordine)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco link JQM dato filtro tipo
        /// </summary>
        /// <param name="tipoLink"></param>
        /// <returns></returns>
        public List<LinkMenuModel> ListLinkFilt(string tipoLink)
        {
            List<LinkMenuModel> dbResult = new List<LinkMenuModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetLinkMenu
                            .Where(x => x.TipoLink == tipoLink)
                            .AsNoTracking()
                            .OrderBy(x => x.Ordine)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco ODL filtrati x stato, articolo, KeyRich (che contiene stato)
        /// </summary>
        /// <param name="inCorso">Stato ODL: true=in corso/completato</param>
        /// <param name="codArt">Cod articolo</param>
        /// <param name="keyRichPart">KeyRich (parziale) da cercare (es cod stato x yacht)</param>
        /// <param name="Reparto">Reparto selezionato</param>
        /// <param name="IdxMacchina">Macchina selezionata</param>
        /// <param name="startDate">Data inizio</param>
        /// <param name="endDate">Data fine</param>
        /// <returns></returns>
        public List<ODLExpModel> ListODLFilt(bool inCorso, string codArt, string keyRichPart, string Reparto, string IdxMacchina, DateTime startDate, DateTime endDate)
        {
            List<ODLExpModel> dbResult = new List<ODLExpModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var InCorso = new SqlParameter("@InCorso", inCorso);
                var CodArt = new SqlParameter("@CodArt", codArt);
                var KeyRich = new SqlParameter("@KeyRich", keyRichPart);
                var CodGruppo = new SqlParameter("@CodGruppo", Reparto);
                var IdxMacc = new SqlParameter("@IdxMacchina", IdxMacchina);
                var DataFrom = new SqlParameter("@DataFrom", startDate);
                var DataTo = new SqlParameter("@DataTo", endDate);

                dbResult = dbCtx
                            .DbSetODLExp
                            .FromSqlRaw("EXEC stp_ODL_getByFiltSpec @InCorso, @CodArt, @KeyRich, @CodGruppo, @IdxMacchina, @DataFrom, @DataTo", InCorso, CodArt, KeyRich, CodGruppo, IdxMacc, DataFrom, DataTo)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero elenco PODL EXPL filtrati
        /// </summary>
        /// <param name="CodArticolo"></param>
        /// <param name="OnlyAvail">True = aperti (=senza ODL)</param>
        /// <returns></returns>
        public List<PODLExpModel> ListPODL_ByCodArt(string CodArticolo, bool OnlyAvail)
        {
            List<PODLExpModel> dbResult = new List<PODLExpModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var pCodArticolo = new SqlParameter("@CodArticolo", CodArticolo);
                var pOnlyAvail = new SqlParameter("@onlyAvail", OnlyAvail);

                dbResult = dbCtx
                            .DbSetPODLExp
                            .FromSqlRaw("EXEC stp_PODL_getByCodArt @CodArticolo, @onlyAvail", pCodArticolo, pOnlyAvail)
                            .AsNoTracking()
                            .ToList();
            }

            return dbResult;
        }

        /// <summary>
        /// Elenco PODL in un istanza KIT dall'ID del parent
        /// </summary>
        /// <param name="IdxPodlParent">IDX PODL parent</param>
        /// <returns></returns>
        public List<PODLExpModel> ListPODL_ByKitParent(int IdxPodlParent)
        {
            List<PODLExpModel> dbResult = new List<PODLExpModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var pIdxPodlParent = new SqlParameter("@IdxPodlParent", IdxPodlParent);

                dbResult = dbCtx
                            .DbSetPODLExp
                            .FromSqlRaw("EXEC stp_PODL_getByParentKitIdx @IdxPodlParent", pIdxPodlParent)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco PODL per composizione KIT non avviati filtrati x articolo, KeyRich (che contiene stato)
        /// </summary>
        /// <param name="lanciato">Solo lanciati (1) o ancora disponibili (0)</param>
        /// <param name="keyRichPart">KeyRich (parziale) da cercare (es cod stato x yacht)</param>
        /// <param name="idxMacchina">Macchina</param>
        /// <param name="codGruppo">Gruppo</param>
        /// <returns></returns>
        public List<PODLExpModel> ListPODL_KitFilt(bool lanciato, string keyRichPart, string idxMacchina, string codGruppo, DateTime startDate, DateTime endDate)
        {
            List<PODLExpModel> dbResult = new List<PODLExpModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var Lanc = new SqlParameter("@Lanciato", lanciato);
                var KeyRich = new SqlParameter("@KeyRich", keyRichPart);
                var CodGrp = new SqlParameter("@CodGruppo", codGruppo);
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var DateFrom = new SqlParameter("@DtInizio", startDate);
                var DateTo = new SqlParameter("@DtFine", endDate);

                dbResult = dbCtx
                            .DbSetPODLExp
                            .FromSqlRaw("EXEC stp_PODL_getByFiltSpecKit @Lanciato, @KeyRich, @CodGruppo, @IdxMacchina, @DtInizio, @DtFine", Lanc, KeyRich, CodGrp, IdxMacc, DateFrom, DateTo)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco PODL non avviati filtrati x articolo, KeyRich (che contiene stato)
        /// </summary>
        /// <param name="lanciato">Solo lanciati (1) o ancora disponibili (0)</param>
        /// <param name="keyRichPart">KeyRich (parziale) da cercare (es cod stato x yacht)</param>
        /// <param name="idxMacchina">Macchina</param>
        /// <param name="codGruppo">Gruppo</param>
        /// <returns></returns>
        public List<PODLExpModel> ListPODLFilt(bool lanciato, string keyRichPart, string idxMacchina, string codGruppo, DateTime startDate, DateTime endDate)
        {
            List<PODLExpModel> dbResult = new List<PODLExpModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var Lanc = new SqlParameter("@Lanciato", lanciato);
                var KeyRich = new SqlParameter("@KeyRich", keyRichPart);
                var CodGrp = new SqlParameter("@CodGruppo", codGruppo);
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var DateFrom = new SqlParameter("@DtInizio", startDate);
                var DateTo = new SqlParameter("@DtFine", endDate);

                dbResult = dbCtx
                            .DbSetPODLExp
                            .FromSqlRaw("EXEC stp_PODL_getByFiltSpec @Lanciato, @KeyRich, @CodGruppo, @IdxMacchina, @DtInizio, @DtFine", Lanc, KeyRich, CodGrp, IdxMacc, DateFrom, DateTo)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco valori ammessi x tabella/colonna
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public List<ListValuesModel> ListValuesFilt(string tabName, string fieldName)
        {
            List<ListValuesModel> dbResult = new List<ListValuesModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                .DbSetListValues
                .Where(x => x.TableName == tabName && x.FieldName == fieldName)
                .AsNoTracking()
                .OrderBy(x => x.ordinal)
                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Macchine dato operatore secondo gruppi (macchine/operatore)
        /// </summary>
        /// <param name="MatrOpr"></param>
        /// <returns></returns>
        public List<MacchineModel> MacchineByMatrOper(int MatrOpr)
        {
            List<MacchineModel> dbResult = new List<MacchineModel>();
            try
            {
                using (var dbCtx = new MoonProContext(options))
                {
                    if (MatrOpr == 0)
                    {
                        dbResult = dbCtx
                                    .DbSetMacchine
                                    .AsNoTracking()
                                    .OrderBy(x => x.IdxMacchina)
                                    .ToList();
                    }
                    else
                    {
                        dbResult = dbCtx
                                    .DbSetGrp2Oper
                                    .Where(g => g.MatrOpr == MatrOpr)
                                    .Join(dbCtx.DbSetGrp2Macc,
                                        g => g.CodGruppo,
                                        m => m.CodGruppo,
                                        (g, m) => m
                                    )
                                    .Distinct()
                                    .Join(dbCtx.DbSetMacchine,
                                        g => g.IdxMacchina,
                                        m => m.IdxMacchina,
                                        (g, m) => m
                                    )
                                    .Distinct()
                                    .AsNoTracking()
                                    .OrderBy(x => x.IdxMacchina)
                                    .ToList();
                    }
                }
            }
            catch (Exception exc)
            {
                Log.Error($"Eccezione in MacchineByMatrOper{Environment.NewLine}{exc}");
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco da tabella Macchine filtro x gruppo
        /// </summary>
        /// <param name="codGruppo"></param>
        /// <returns></returns>
        public List<MacchineModel> MacchineGetFilt(string codGruppo)
        {
            List<MacchineModel> dbResult = new List<MacchineModel>();
            try
            {
                using (var dbCtx = new MoonProContext(options))
                {
                    if (codGruppo == "*")
                    {
                        dbResult = dbCtx
                                    .DbSetMacchine
                                    .AsNoTracking()
                                    .OrderBy(x => x.IdxMacchina)
                                    .ToList();
                    }
                    else
                    {
                        dbResult = dbCtx
                                    .DbSetGrp2Macc
                                    .Where(g => g.CodGruppo == codGruppo)
                                    .Join(dbCtx.DbSetMacchine,
                                        g => g.IdxMacchina,
                                        m => m.IdxMacchina,
                                        (g, m) => m
                                    )
                                    .AsNoTracking()
                                    .OrderBy(x => x.IdxMacchina)
                                    .ToList();
                    }
                }
            }
            catch (Exception exc)
            {
                Log.Error($"Eccezione in MacchineGetFilt{Environment.NewLine}{exc}");
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco id MacchineModel che abbiano dati FLuxLog, nel periodo indicato
        /// </summary>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public async Task<List<string>> MacchineWithFlux(DateTime dtStart, DateTime dtEnd)
        {
            List<string> dbResult = new List<string>();
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                dbResult = await dbCtx
                                .DbSetFluxLog
                                .AsNoTracking()
                                .Where(x => x.dtEvento >= dtStart && x.dtEvento <= dtEnd)
                                .Select(i => i.IdxMacchina)
                                .Distinct()
                                .ToListAsync();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiornamento record Microstato macchina
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> MicroStatoMacchinaUpsert(MicroStatoMacchinaModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = await dbCtx
                                .DbSetMicroStatoMacc
                                .Where(x => x.IdxMacchina == newRec.IdxMacchina)
                                .AsNoTracking()
                                .FirstOrDefaultAsync();
                if (actRec == null)
                {
                    dbCtx
                        .DbSetMicroStatoMacc
                        .Add(newRec);
                }
                else
                {
                    actRec.IdxMicroStato = newRec.IdxMicroStato;
                    actRec.InizioStato = newRec.InizioStato;
                    actRec.Value = newRec.Value;

                    dbCtx.Entry(actRec).State = EntityState.Modified;
                }
                await dbCtx.SaveChangesAsync();
                fatto = true;
            }
            return fatto;
        }

        /// <summary>
        /// Elenco da tabella MappaStatoExplModel
        /// </summary>
        /// <returns></returns>
        public List<MappaStatoExplModel> MseGetAll(int maxAge = 2000)
        {
            List<MappaStatoExplModel> dbResult = new List<MappaStatoExplModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var maxAgeSec = new SqlParameter("@maxAgeSec", maxAge);

                dbResult = dbCtx
                    .DbSetMSE
                    .FromSqlRaw("EXEC stp_MSE_getData @maxAgeSec", maxAgeSec)
                    .AsNoTracking()
                    .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco ODL dato batch selezionato
        /// </summary>
        /// <param name="BatchSel">Batch richiesto</param>
        /// <returns></returns>
        public List<int> OdlByBatch(string batchSel)
        {
            List<int> dbResult = new List<int>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    dbResult = dbCtx
                                .DbGiacenzeData
                                .AsNoTracking()
                                .Where(x => x.IdentRG == batchSel)
                                .Select(x => x.IdxOdl)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlByBatch{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// ODL da chiave
        /// </summary>
        /// <returns></returns>
        public ODLExpModel OdlByKey(int IdxOdl)
        {
            ODLExpModel dbResult = new ODLExpModel();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    dbResult = dbCtx
                                .DbSetODLExp
                                .AsNoTracking()
                                .Where(x => x.IdxOdl == IdxOdl)
                                .FirstOrDefault();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlByKey{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Chiusura ODL con eventuale conferma pezzi
        /// </summary>
        /// <param name="idxOdl">idx odl da chiudere</param>
        /// <param name="idxMacchina">idx macchina</param>
        /// <param name="matrOpr">matricola operatore</param>
        /// <param name="confPezzi">indica se confermare i pezzi prima di chiudere ODL</param>
        /// <param name="confRett">Conferma con rettifica (ev 121) x pezzi lasciati in macchina</param>
        /// <param name="modoConfProd">Modo conferma produzione (0=periodo, 1=giorno, 2=turno)</param>
        /// <returns></returns>
        public async Task<bool> ODLClose(int idxOdl, string idxMacchina, int matrOpr, bool confPezzi, bool confRett, int modoConfProd)
        {
            bool fatto = false;
            await Task.Delay(1);
            if (idxOdl > 0)
            {
                using (var dbCtx = new MoonProContext(options))
                {
                    DateTime adesso = DateTime.Now;
                    // preparo i parametri
                    var IdxODL = new SqlParameter("@IdxODL", idxOdl);
                    var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);

                    // FARE FIXME TODO !!! da valutare casi setup/autoconferma...
#if false
                    // controllo se HO pezzi da confermare...
                    var statoProd = StatoProdMacchina(idxMacchina);
                    if (statoProd.pezziNonConfermati < 1)
                    { }
#endif

                    // se richiesto confermo produzione
                    if (confPezzi)
                    {
                        var MatrApp = new SqlParameter("@MatrApp", idxMacchina);

                        /* ----------------------------------
                         * CONFERMA PEZZI
                         *
                         * condizioni da verificare:
                         * - gestione rettifica (ev121) / pezzi da LASCIARE in macchina
                         * - conferma a zero pezzi (setup) oppure con i pezzi fatti e non ancora confermati
                         *
                         *
                         *
                         * */

                        // recupero i dati dei pezzi da confermare... con DbSetPzProd + exec
                        // stp_PzProd_getByMacchina 'SIMUL_01'

                        // stp_ConfermaProduzCompletaFull
                        /*
                         *  @idxMacchina    NVARCHAR(50),
                            @MatrApp        INT,
                            @dataFrom       DATETIME,
                            @dataTo         DATETIME,
                            @pezziConf      INT,
                            @pezziLasciati  INT,				-- pezzi lasciati = evento 121 (-) pre conferma e (+) dopo --> da lasciare in macchina post conferma
                            @pezziScar      INT = 0,            -- pezzi scartati (registrati da 2016.11.20) DA INDICARE COME VALORE > 0!!! sennò faccio ABS...
                            @TipoConf       INT = 0,            -- Tipo intervallo conferma: 0 = periodo intero, 1 = per giorni, 2 = per turni
                            @DataOraApp     DATETIME = NULL,    -- di norma GETDATE() nel programma - serve per ricalcolo
                            @TestConferma   BIT = 1             -- TestConferma : 1 = verifica conf. duplicata e inserisci in ElencoConfermeProd, 0 = nessuna verifica e inserimento ( per ricalcolo )
                         */
                    }

                    // ora chiudo ODL con stored SENZA ritorno...
                    try
                    {
                        var dbResult = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_ODL_fineProd @IdxODL, @IdxMacchina", IdxODL, IdxMacchina);
                        fatto = true;
                    }
                    catch (Exception exc)
                    {
                        Log.Error($"Eccezione durante ODLClose{Environment.NewLine}{exc}");
                    }
                }
            }
            return fatto;
        }

        /// <summary>
        /// Recupero odl data chiave
        /// </summary>
        /// <param name="idxOdl"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<ODLModel> OdlGetByKey(int idxOdl)
        {
            ODLModel dbResult = new ODLModel();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = await dbCtx
                            .DbSetODL
                            .FirstOrDefaultAsync(x => x.IdxOdl == idxOdl);
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero Odl CORRENTI
        /// </summary>
        /// <returns></returns>
        public List<ODLModel> OdlGetCurrent()
        {
            List<ODLModel> dbResult = new List<ODLModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetODL
                            .Where(x => x.DataInizio != null && x.DataFine == null)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco TUTTI GLI ODL
        /// </summary>
        /// <returns></returns>
        public List<ODLModel> OdlListAll()
        {
            List<ODLModel> dbResult = new List<ODLModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    dbResult = dbCtx
                                .DbSetODL
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ListOdlAll{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Statistiche ODL calcolate (da stored stp_STAT_ODL)
        /// </summary>
        /// <returns></returns>
        public async Task<List<StatODLModel>> OdlStart(int IdxOdl)
        {
            List<StatODLModel> dbResult = new List<StatODLModel>();
            if (IdxOdl > 0)
            {
                using (var dbCtx = new MoonProContext(options))
                {
                    var IdxODL = new SqlParameter("@IdxODL", IdxOdl);

                    dbResult = await dbCtx
                        .DbSetStatOdl
                        .FromSqlRaw("EXEC stp_STAT_ODL @IdxODL", IdxODL)
                        .AsNoTracking()
                        .ToListAsync();
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco da tabella Operatori filtro x gruppo
        /// </summary>
        /// <param name="codGruppo"></param>
        /// <returns></returns>
        public List<AnagOperatoriModel> OperatoriGetFilt(string codGruppo)
        {
            List<AnagOperatoriModel> dbResult = new List<AnagOperatoriModel>();
            try
            {
                using (var dbCtx = new MoonProContext(options))
                {
                    if (codGruppo == "*")
                    {
                        dbResult = dbCtx
                                    .DbOperatori
                                    .AsNoTracking()
                                    .OrderBy(x => x.MatrOpr)
                                    .ToList();
                    }
                    else
                    {
                        dbResult = dbCtx
                                    .DbSetGrp2Oper
                                    .Where(g => g.CodGruppo == codGruppo)
                                    .Join(dbCtx.DbOperatori,
                                        g => g.MatrOpr,
                                        m => m.MatrOpr,
                                        (g, m) => m
                                    )
                                    .AsNoTracking()
                                    .OrderBy(x => x.MatrOpr)
                                    .ToList();
                    }
                }
            }
            catch (Exception exc)
            {
                Log.Error($"Eccezione in OperatoriGetFilt{Environment.NewLine}{exc}");
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco parametri validi x una data macchina
        /// </summary>
        /// <param name="IdxMacchina"></param>
        /// <returns></returns>
        public List<string> ParametriGetFilt(string IdxMacchina)
        {
            List<string> dbResult = new List<string>();
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetFluxLog
                            .AsNoTracking()
                            .Where(x => (IdxMacchina == "*" || x.IdxMacchina == IdxMacchina))
                            .Take(1000)
                            .Select(i => i.CodFlux)
                            .Distinct()
                            .OrderBy(x => x)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Stato prod macchina
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public PzProdModel PezziProdMacchina(string idxMacchina)
        {
            PzProdModel dbResult = new PzProdModel();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                dbResult = dbCtx
                            .DbSetPzProd
                            .FromSqlRaw("EXEC stp_PzProd_getByMacchina @IdxMacchina", IdxMacchina)
                            .AsNoTracking()
                            .FirstOrDefault();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero PODL da chiave
        /// </summary>
        /// <param name="idxPODL"></param>
        /// <returns></returns>
        public async Task<PODLModel> PODL_getByKey(int idxPODL)
        {
            PODLModel dbResult = new PODLModel();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    dbResult = dbCtx
                                    .DbSetPODL
                                    .AsNoTracking()
                                    .Where(x => x.IdxPromessa == idxPODL)
                                    .Include(a => a.ArticoloNav)
                                    .FirstOrDefault();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante PODL_getByKey{Environment.NewLine}{exc}");
                }
            }
            await Task.Delay(1);
            return dbResult;
        }

        /// <summary>
        /// Recupero PODL da IdxOdl
        /// </summary>
        /// <param name="idxPODL"></param>
        /// <returns></returns>
        public PODLModel PODL_getByOdl(int idxODL)
        {
            PODLModel dbResult = new PODLModel();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    dbResult = dbCtx
                                    .DbSetPODL
                                    .AsNoTracking()
                                    .Where(x => x.IdxOdl == idxODL)
                                    .FirstOrDefault();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante PODL_getByOdl{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Avvio setup ODL da PODL
        /// </summary>
        /// <param name="editRec"></param>
        /// <param name="matrOpr"></param>
        /// <param name="tcRich"></param>
        /// <param name="pzPallet"></param>
        /// <param name="note"></param>
        /// <returns></returns>
        public async Task<bool> PODL_startSetup(PODLExpModel editRec, int matrOpr, double tcRich, int pzPallet, string note, DateTime dtEvent)
        {
            bool answ = false;

            PODLModel recPODL = new PODLModel()
            {
                IdxPromessa = editRec.IdxPromessa,
                KeyRichiesta = editRec.KeyRichiesta,
                KeyBCode = editRec.KeyBCode,
                IdxOdl = editRec.IdxOdl,
                CodArticolo = editRec.CodArticolo,
                CodGruppo = editRec.CodGruppo,
                IdxMacchina = editRec.IdxMacchina,
                NumPezzi = editRec.NumPezzi,
                Tcassegnato = editRec.Tcassegnato,
                DueDate = editRec.DueDate,
                Priorita = editRec.Priorita,
                PzPallet = editRec.PzPallet,
                Note = editRec.Note,
                CodCli = editRec.CodCli,
                InsertDate = editRec.InsertDate
            };

            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var currRec = dbCtx
                                    .DbSetPODL
                                    .AsNoTracking()
                                    .Where(x => x.IdxPromessa == recPODL.IdxPromessa)
                                    .FirstOrDefault();

                    if (currRec != null)
                    {
                        // eseguo stored attrezzaggio
                        var IdxPromessa = new SqlParameter("@idxPromessa", recPODL.IdxPromessa);
                        var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                        var IdxMacchina = new SqlParameter("@IdxMacchina", recPODL.IdxMacchina);
                        var TCRichAttr = new SqlParameter("@TCRichAttr", tcRich);
                        var PzPallet = new SqlParameter("@PzPallet", pzPallet);
                        var Note = new SqlParameter("@Note", note);
                        var DtEvento = new SqlParameter("@dtEvento", dtEvent);
                        var callResult = await dbCtx
                                        .Database
                                        .ExecuteSqlRawAsync("EXEC stp_ODL_inizioSetupPromessa @idxPromessa, @MatrOpr, @IdxMacchina, @TCRichAttr, @PzPallet, @Note, @dtEvento", IdxPromessa, MatrOpr, IdxMacchina, TCRichAttr, PzPallet, Note, DtEvento);

                        answ = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante PODL_doSetup{Environment.NewLine}{exc}");
                }
            }
            await Task.Delay(1);
            return answ;
        }

        /// <summary>
        /// Chiamata salvataggio ricetta su DB
        /// </summary>
        /// <param name="idxPODL"></param>
        /// <param name="recipeName"></param>
        /// <returns></returns>
        public async Task<bool> PODL_updateRecipe(int idxPODL, string recipeName)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var currRec = dbCtx
                                    .DbSetPODL
                                    .Where(x => x.IdxPromessa == idxPODL)
                                    .FirstOrDefault();

                    if (currRec != null)
                    {
                        currRec.Recipe = recipeName;
                        dbCtx.Entry(currRec).State = EntityState.Modified;
                        await dbCtx.SaveChangesAsync();
                        answ = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante PODL_updateRecipe{Environment.NewLine}{exc}");
                }
            }
            await Task.Delay(1);
            return answ;
        }

        /// <summary>
        /// Eliminazione Record
        /// </summary>
        /// <param name="currRec"></param>
        /// <returns></returns>
        public async Task<bool> PODLDeleteRecord(PODLExpModel currRec)
        {
            PODLModel recPODL = new PODLModel()
            {
                IdxPromessa = currRec.IdxPromessa,
                KeyRichiesta = currRec.KeyRichiesta,
                KeyBCode = currRec.KeyBCode,
                IdxOdl = currRec.IdxOdl,
                CodArticolo = currRec.CodArticolo,
                CodGruppo = currRec.CodGruppo,
                IdxMacchina = currRec.IdxMacchina,
                NumPezzi = currRec.NumPezzi,
                Tcassegnato = currRec.Tcassegnato,
                DueDate = currRec.DueDate,
                Priorita = currRec.Priorita,
                PzPallet = currRec.PzPallet,
                Note = currRec.Note,
                CodCli = currRec.CodCli,
                InsertDate = currRec.InsertDate
            };
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var currVal = dbCtx
                                    .DbSetPODL
                                    .Where(x => x.IdxPromessa == recPODL.IdxPromessa)
                                    .FirstOrDefault();
                    dbCtx
                        .DbSetPODL
                        .Remove(currVal);
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante PODLDeleteRecord{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Effettua il task di eliminazione PODL KIT + istanze + riattivazione PODL originali disattivate tramite stored
        /// </summary>
        /// <param name="IdxPODL">IdxPODL parent</param>
        public bool PodlIstKitDelete(int IdxPODL)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var pIdxPODL = new SqlParameter("@IdxPODL", IdxPODL);

                var dbResult = dbCtx
                    .Database
                    .ExecuteSqlRaw("EXEC dbo.stp_PodlIstKit_delete @IdxPODL", pIdxPODL);
                answ = dbResult != 0;
            }
            return answ;
        }

        /// <summary>
        /// Update Record
        /// </summary>
        /// <param name="currRec"></param>
        /// <returns></returns>
        public async Task<bool> PODLUpdateRecord(PODLModel editRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var currRec = dbCtx
                                    .DbSetPODL
                                    .Where(x => x.IdxPromessa == editRec.IdxPromessa)
                                    .FirstOrDefault();
                    if (currRec != null)
                    {
                        currRec.CodGruppo = editRec.CodGruppo;
                        currRec.CodArticolo = editRec.CodArticolo;
                        currRec.IdxMacchina = editRec.IdxMacchina;
                        currRec.KeyBCode = editRec.KeyBCode;
                        currRec.KeyRichiesta = editRec.KeyRichiesta;
                        currRec.NumPezzi = editRec.NumPezzi;
                        currRec.Tcassegnato = editRec.Tcassegnato;
                        currRec.Attivabile = editRec.Attivabile;
                        currRec.Note = editRec.Note;
                        dbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx
                            .DbSetPODL
                            .Add(editRec);
                    }
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante PODLUpdateRecord{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <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 (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    if (dbCtx.Entry(item).State == Microsoft.EntityFrameworkCore.EntityState.Deleted || dbCtx.Entry(item).State == Microsoft.EntityFrameworkCore.EntityState.Modified)
                    {
                        dbCtx.Entry(item).Reload();
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in rollBackEntity{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Stato macchina (da key)
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public StatoMacchineModel StatoMacchina(string idxMacchina)
        {
            StatoMacchineModel dbResult = new StatoMacchineModel();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                                .DbSetStatoMacc
                                .Where(x => x.IdxMacchina == idxMacchina)
                                .AsNoTracking()
                                .FirstOrDefault();
            }
            return dbResult;
        }

        /// <summary>
        /// Elimina record
        /// </summary>
        /// <param name="rec2del"></param>
        public bool TemplateKitDelete(TemplateKitModel rec2del)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                                .DbSetTempKit
                                .Where(x => x.CodArtParent == rec2del.CodArtParent && x.CodArtChild == rec2del.CodArtChild)
                                .FirstOrDefault();
                // se ci fosse aggiorno...
                if (actRec != null)
                {
                    dbCtx
                        .DbSetTempKit
                        .Remove(actRec);
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elenco template KIT da ricerca
        /// </summary>
        /// <param name="KitCode"></param>
        /// <param name="codChild"></param>
        /// <returns></returns>
        public List<TemplateKitModel> TemplateKitFilt(string KitCode, string codChild)
        {
            List<TemplateKitModel> dbResult = new List<TemplateKitModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                           .DbSetTempKit
                           .Where(x => (string.IsNullOrEmpty(KitCode) && string.IsNullOrEmpty(codChild)) || (x.CodArtParent.Contains(KitCode) && !string.IsNullOrEmpty(KitCode)) || (x.CodArtChild.Contains(codChild) && !string.IsNullOrEmpty(codChild)))
                           .AsNoTracking()
                           .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Esegue upsert record
        /// </summary>
        /// <param name="editRec"></param>
        /// <param name="codAzienda"></param>
        public bool TemplateKitUpsert(TemplateKitModel editRec, string codAzienda)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                // verifico preliminarmente articolo...
                var recArt = dbCtx
                                .DbSetArticoli
                                .FirstOrDefault(x => x.CodArticolo == editRec.CodArtParent);
                // se mancasse...
                if (recArt == null)
                {
                    // aggiungo!
                    AnagArticoliModel newRecArt = new AnagArticoliModel()
                    {
                        CodArticolo = editRec.CodArtParent,
                        Tipo = "KIT",
                        DescArticolo = $"Articolo KIT - {DateTime.Now:yyyy-MM-dd HH:mm:ss}",
                        Disegno = "",
                        Azienda = codAzienda,
                        CurrRev = "",
                        ProdRev = ""
                    };
                    dbCtx
                        .DbSetArticoli
                        .Add(newRecArt);
                }

                // proseguo col KIT
                var actRec = dbCtx
                                .DbSetTempKit
                                .Where(x => x.CodArtParent == editRec.CodArtParent && x.CodArtChild == editRec.CodArtChild)
                                .FirstOrDefault();

                // se NON ci fosse aggiungo...
                if (actRec == null)
                {
                    dbCtx
                        .DbSetTempKit
                        .Add(editRec);
                }
                else
                {
                    actRec.Qty = editRec.Qty;
                    dbCtx.Entry(actRec).State = EntityState.Modified;
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Punteggio compatibilità KIT per KeyFilt indicato
        /// </summary>
        /// <param name="KeyFilt"></param>
        /// <param name="MaxResult"></param>
        /// <returns></returns>
        public List<TksScoreModel> TksScore(string KeyFilt, int MaxResult)
        {
            List<TksScoreModel> dbResult = new List<TksScoreModel>();
            if (!string.IsNullOrEmpty(KeyFilt))
            {
                using (var dbCtx = new MoonProContext(options))
                {
                    var pKeyFilt = new SqlParameter("@KeyFilt", KeyFilt);
                    var pMaxRes = new SqlParameter("@maxResult", MaxResult);

                    dbResult = dbCtx
                                .DbSetTksScore
                                .FromSqlRaw("EXEC stp_TKS_Search @KeyFilt, @maxResult", pKeyFilt, pMaxRes)
                                .AsNoTracking()
                                .ToList();
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Vocabolario (completo)
        /// </summary>
        /// <returns></returns>
        public List<VocabolarioModel> VocabolarioGetAll()
        {
            List<VocabolarioModel> dbResult = new List<VocabolarioModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                    .DbSetVocabolario
                    .AsNoTracking()
                    .OrderBy(x => x.Lemma)
                    .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elimina record
        /// </summary>
        /// <param name="rec2del"></param>
        public bool WipKitDelete(WipSetupKitModel rec2del)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                               .DbSetWipKit
                               .Where(x => x.KeyFilt == rec2del.KeyFilt && x.CodOrd == rec2del.CodOrd)
                               .FirstOrDefault();
                // se ci fosse aggiorno...
                if (actRec != null)
                {
                    dbCtx
                        .DbSetWipKit
                        .Remove(actRec);
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elimina i record associati al KeyFilt indicato
        /// </summary>
        /// <param name="KeyFilt"></param>
        public bool WipKitDeleteGroup(string KeyFilt)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                               .DbSetWipKit
                               .Where(x => x.KeyFilt == KeyFilt)
                               .ToList();
                // se ci fosse aggiorno...
                if (actRec != null)
                {
                    dbCtx
                        .DbSetWipKit
                        .RemoveRange(actRec);
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elimina record + vecchi della data-ora indicata
        /// </summary>
        /// <param name="dateLimit"></param>
        /// <returns></returns>
        public bool WipKitDeleteOlder(DateTime dateLimit)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                               .DbSetWipKit
                               .Where(x => x.DataIns < dateLimit)
                               .ToList();
                // se ci fosse aggiorno...
                if (actRec != null)
                {
                    dbCtx
                        .DbSetWipKit
                        .RemoveRange(actRec);
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elenco record WipSetupKit da KeyFilt
        /// </summary>
        /// <param name="KeyFilt"></param>
        /// <returns></returns>
        public List<WipSetupKitModel> WipKitFilt(string KeyFilt)
        {
            List<WipSetupKitModel> dbResult = new List<WipSetupKitModel>();
            // solo se filtro valido...
            if (!string.IsNullOrEmpty(KeyFilt))
            {
                using (var dbCtx = new MoonProContext(options))
                {
                    dbResult = dbCtx
                               .DbSetWipKit
                               .Where(x => x.KeyFilt.Contains(KeyFilt))
                               .AsNoTracking()
                               .ToList();
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Esegue upsert record
        /// </summary>
        /// <param name="editRec"></param>
        public bool WipKitUpsert(WipSetupKitModel editRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                                .DbSetWipKit
                                .Where(x => x.KeyFilt == editRec.KeyFilt && x.CodOrd == editRec.CodOrd)
                                .FirstOrDefault();

                // se ci fosse aggiorno...
                if (actRec == null)
                {
                    dbCtx
                        .DbSetWipKit
                        .Add(editRec);
                }
                else
                {
                    actRec.CodArt = editRec.CodArt;
                    actRec.DescArt = editRec.DescArt;
                    actRec.Qta = editRec.Qta;
                    actRec.DataIns = editRec.DataIns;
                    dbCtx.Entry(actRec).State = EntityState.Modified;
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        #endregion Public Methods

        #region Private Fields

        private static IConfiguration _configuration;
        private static NLog.Logger Log = LogManager.GetCurrentClassLogger();
        private DbContextOptions<MoonProContext> options;

        #endregion Private Fields
    }
}