using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using MP.Core.Objects;
using MP.Data.DbModels;
using NLog;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using static MP.Core.Objects.Enums;

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

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

        #endregion Public Constructors

        #region Public Methods

        /// <summary>
        /// Insert record allarme
        /// </summary>
        /// <param name="dtRif">Data evento</param>
        /// <param name="machineId">Id macchina</param>
        /// <param name="memAddress">area memoria</param>
        /// <param name="memAddress">indice memoria</param>
        /// <param name="memAddress">valore status</param>
        /// <param name="memAddress">valore decodificato</param>
        /// <returns></returns>
        public bool AlarmLogInsert(DateTime dtRif, string machineId, string memAddress, int memIndex, int statusVal, string valDecoded)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var DtRif = new SqlParameter("@DtRif", dtRif);
                var MachineId = new SqlParameter("@MachineId", machineId);
                var MemAddress = new SqlParameter("@MemAddress", memAddress);
                var MemIndex = new SqlParameter("@MemIndex", memIndex);
                var StatusVal = new SqlParameter("@StatusVal", statusVal);
                var ValDecoded = new SqlParameter("@ValDecoded", valDecoded);

                var result = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_AL_insertQuery @DtRif, @MachineId, @MemAddress, @MemIndex, @StatusVal, @ValDecoded, ", DtRif, MachineId, MemAddress, MemIndex, StatusVal, ValDecoded);
                fatto = result != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elenco allarmi macchina
        /// </summary>
        /// <param name="idxMacchina">Macchina</param>
        /// <param name="dtFrom">Inizio periodo</param>
        /// <param name="dtTo">Fine periodo</param>
        /// <param name="showMulti"></param>
        /// <returns></returns>
        public List<AlarmLogModel> AlarmLogListFilt(string idxMacchina, DateTime dtFrom, DateTime dtTo, bool showMulti)
        {
            List<AlarmLogModel> dbResult = new List<AlarmLogModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var DtFrom = new SqlParameter("@dtFrom", dtFrom);
                var DtTo = new SqlParameter("@dtTo", dtTo);
                var ShowMulti = new SqlParameter("@showMulti", showMulti);

                dbResult = dbCtx
                            .DbSetAlarmLog
                            .FromSqlRaw("EXEC stp_AL_getFilt @IdxMacchina, @dtFrom, @DtTo, @showMulti", IdxMacc, DtFrom, DtTo, ShowMulti)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Registrato ACK allarme
        /// </summary>
        /// <param name="alarmLogId">Id Allarme</param>
        /// <param name="dtAck">Data Ack</param>
        /// <param name="userAck">User Ack</param>
        /// <returns></returns>
        public bool AlarmLogSetAck(int alarmLogId, DateTime dtAck, string userAck)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var AlarmLogId = new SqlParameter("@MachineId", alarmLogId);
                var DtAck = new SqlParameter("@DtRif", dtAck);
                var UserAck = new SqlParameter("@MemAddress", userAck);

                var result = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_AL_setAck @MachineId, @DtRif, @MemAddress", AlarmLogId, DtAck, UserAck);
                fatto = result != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Registrato invio notifica allarme
        /// </summary>
        /// <param name="alarmLogId">Id Allarme</param>
        /// <param name="dtNotify">Data notifica</param>
        /// <returns></returns>
        public bool AlarmLogSetNotify(int alarmLogId, DateTime dtNotify)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var AlarmLogId = new SqlParameter("@MachineId", alarmLogId);
                var DtNotify = new SqlParameter("@DtNotify", dtNotify);

                var result = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_AL_setNotify @MachineId, @DtNotify", AlarmLogId, DtNotify);
                fatto = result != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Restituisce l'anagrafica EVENTI per intero
        /// </summary>
        /// <returns></returns>
        public List<AnagEventiModel> AnagEventiGetAll()
        {
            List<AnagEventiModel> dbResult = new List<AnagEventiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                           .DbSetAnagEventi
                           .AsNoTracking()
                           .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>
        /// Restituisce l'anagrafica STATI per intero
        /// </summary>
        /// <returns></returns>
        public List<AnagStatiModel> AnagStatiGetAll()
        {
            List<AnagStatiModel> dbResult = new List<AnagStatiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetAnagStati
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Restituisce l'anagrafica EVENTI per intero
        /// </summary>
        /// <returns></returns>
        public List<AnagTagsModel> AnagTagsOrd()
        {
            List<AnagTagsModel> dbResult = new List<AnagTagsModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                           .DbSetAnagTags
                           .FromSqlRaw("exec dbo.stp_AT_getOrd")
                           .AsNoTracking()
                           .AsEnumerable()
                           .ToList();
            }
            return dbResult;
        }

        /// <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>
        /// Verifica se sia necessario inserire un cambio di stato impianto in modalità batch
        /// </summary>
        /// <param name="tipoInput"></param>
        /// <param name="IdxMacchina"></param>
        /// <param name="InizioStato"></param>
        /// <param name="IdxTipo"></param>
        /// <param name="CodArt"></param>
        /// <param name="Value"></param>
        /// <param name="MatrOpr"></param>
        /// <param name="pallet"></param>
        public void CheckCambiaStatoBatch(tipoInputEvento tipoInput, string IdxMacchina, DateTime InizioStato, int IdxTipo, string CodArt, string Value, int MatrOpr, string pallet)
        {
            List<TransizioneStatiModel> tabTransStati = new List<TransizioneStatiModel>();
            TransizioneStatiModel rigaTransStati = new TransizioneStatiModel();
            switch (tipoInput)
            {
                case tipoInputEvento.barcode:
                    // effettuo cambio stato INDIPENDENTEMENTE da stato precedente
                    try
                    {
                        tabTransStati = SMES_getUserForced(IdxMacchina, IdxTipo);
                        if (tabTransStati != null)
                        {
                            if (tabTransStati.Count > 0)
                            {
                                rigaTransStati = tabTransStati.FirstOrDefault();
                                // solo se cambia stato...
                                if (rigaTransStati.IdxStato != rigaTransStati.next_IdxStato)
                                {
                                    DDB_InsStatoBatch(IdxMacchina, InizioStato, rigaTransStati.next_IdxStato, CodArt, Value, MatrOpr, pallet);
                                    // aggiorno MSE
                                    RicalcMse(IdxMacchina, 0);
                                }
                            }
                            else
                            {
                                Log.Debug($"Non trovata riga per: BARCODE | IdxMacchina: {IdxMacchina} | IdxTipo: {IdxTipo} | CodArt: {CodArt} | Value: {Value} | MatrOpr: {MatrOpr} | pallet: {pallet}");
                            }
                        }
                    }
                    catch (Exception exc)
                    {
                        // non dovrebbe succedere... input utente da barcode dovrebbero TUTTI essere
                        // inseriti in tab transizione con famiglia 1...
                        Log.Error($"Eccezione controllo transizione stato x evento barcode: BARCODE | IdxMacchina: {IdxMacchina} | IdxTipo: {IdxTipo} | CodArt: {CodArt} | Value: {Value} | MatrOpr: {MatrOpr} | pallet: {pallet}{Environment.NewLine}{exc}");
                    }
                    break;

                case tipoInputEvento.hw:
                    // verifico se ci sia necessità di cambio stato
                    try
                    {
                        tabTransStati = SMES_getHwTransitions(IdxMacchina, IdxTipo);
                        if (tabTransStati != null)
                        {
                            if (tabTransStati.Count > 0)
                            {
                                //rigaTransStati = tabTransStati[0];
                                rigaTransStati = tabTransStati.FirstOrDefault();
                                if (rigaTransStati != null)
                                {
                                    // solo se cambia stato...
                                    if (rigaTransStati.IdxStato != rigaTransStati.next_IdxStato)
                                    {
                                        DDB_InsStatoBatch(IdxMacchina, InizioStato, rigaTransStati.next_IdxStato, CodArt, Value, MatrOpr, pallet);
                                    }
                                }
                            }
                            else
                            {
                                Log.Debug($"Non trovata riga per: HW | IdxMacchina: {IdxMacchina} | IdxTipo: {IdxTipo} | CodArt: {CodArt} | Value: {Value} | MatrOpr: {MatrOpr} | pallet: {pallet}");
                            }
                        }
                    }
                    catch (Exception exc)
                    {
                        // non trovo riga [0]... NON scrivo!
                        Log.Error($"Errore controllo transizione stato x evento barcode: HW | IdxMacchina: {IdxMacchina} | IdxTipo: {IdxTipo} | CodArt: {CodArt} | Value: {Value} | MatrOpr: {MatrOpr} | pallet: {pallet}{Environment.NewLine}{exc}");
                    }
                    break;
            }
        }

        /// <summary>
        /// Recupera elenco ultimi commenti x macchina
        /// </summary>
        /// <param name="idxMacchina">Idx macchina, "*" = tutte</param>
        /// <param name="numDays">Num massimo di record da recuperare</param>
        /// <returns></returns>
        public List<CommentiModel> CommentiGetLastByMacc(string idxMacchina, int numDays)
        {
            List<CommentiModel> dbResult = new List<CommentiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var NumDays = new SqlParameter("@NumDays", numDays);

                dbResult = dbCtx
                            .DbSetCommenti
                            .FromSqlRaw("exec dbo.stp_Comm_getLastByMacchinaDays @IdxMacchina, @numDays", IdxMacchina, NumDays)
                            .AsNoTracking()
                            //.AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Effettua conferma prod macchina dell'intero periodo da confermare (ultima conferma
        /// --&gt; dtEvent)
        /// </summary>
        /// <param name="idxMacchina">idx macchina da confermare</param>
        /// <param name="modoConfProd">0=periodo, 1 = giorno, 2 = turno</param>
        /// <param name="numPzConfermati">qta pezzi BUONI da confermare</param>
        /// <param name="numPzScarto">qta pezzi SCARTO da confermare</param>
        /// <param name="DataOraApp">DataOra in cui registrare approvazione</param>
        /// <param name="MatrOpr">Matricola operatore</param>
        /// <returns></returns>
        public bool ConfermaProdMacchina(string idxMacchina, int modoConfProd, int numPzConfermati, int numPzScarto, DateTime DataOraApp, int MatrOpr)
        {
            bool answ = false;
            try
            {
                var rigaProd = StatoProdMacchina(idxMacchina, DateTime.Now);
                using (var dbCtx = new MoonProContext(options))
                {
                    var IdxMacchina = new SqlParameter("@idxMacchina", idxMacchina);
                    var MatrApp = new SqlParameter("@MatrApp ", MatrOpr);
                    var DataFrom = new SqlParameter("@dataFrom ", rigaProd.DataFrom);
                    var DataTo = new SqlParameter("@dataTo", DataOraApp);
                    var PezziConf = new SqlParameter("@pezziConf", numPzConfermati);
                    var PezziScar = new SqlParameter("@pezziScar", numPzScarto);
                    var TipoConf = new SqlParameter("@TipoConf", modoConfProd);
                    var DtOraApp = new SqlParameter("@DataOraApp", DataOraApp);
                    var TestConferma = new SqlParameter("@TestConferma", true);

                    var result = dbCtx
                                    .Database
                                    .ExecuteSqlRaw("EXEC stp_ConfermaProduzCompleta @idxMacchina, @MatrApp, @dataFrom, @dataTo, @pezziConf, @pezziScar, @TipoConf, @DataOraApp, @TestConferma ", IdxMacchina, MatrApp, DataFrom, DataTo, PezziConf, PezziScar, TipoConf, DtOraApp, TestConferma);

                    // indico eseguito!
                    answ = result > 0;
                }
            }
            catch (Exception exc)
            {
                Log.Error($"Eccezione in ConfermaProdMacchina:{Environment.NewLine}{exc}");
            }
            return answ;
        }

        /// <summary>
        /// Effettua conferma prod macchina dell'intero periodo da confermare (ultima conferma
        /// --&gt; dtEvent)
        /// </summary>
        /// <param name="idxMacchina">idx macchina da confermare</param>
        /// <param name="modoConfProd">0=periodo, 1 = giorno, 2 = turno</param>
        /// <param name="numPzConfermati">qta pezzi BUONI da confermare</param>
        /// <param name="numPzLasciati">
        /// qta pezzi LASCIATI alla macchina da confermare (2 eventi 121 rettifica neg/pos)
        /// </param>
        /// <param name="numPzScarto">qta pezzi SCARTO da confermare</param>
        /// <param name="DataOraApp">DataOra in cui registrare approvazione</param>
        /// <param name="MatrOpr">Matricola operatore</param>
        /// <returns></returns>
        public bool ConfermaProdMacchinaFull(string idxMacchina, int modoConfProd, int numPzConfermati, int numPzLasciati, int numPzScarto, DateTime DataOraApp, int MatrOpr)
        {
            bool answ = false;
            try
            {
                var rigaProd = StatoProdMacchina(idxMacchina, DateTime.Now);
                using (var dbCtx = new MoonProContext(options))
                {
                    var IdxMacchina = new SqlParameter("@idxMacchina", idxMacchina);
                    var MatrApp = new SqlParameter("@MatrApp ", MatrOpr);
                    var DataFrom = new SqlParameter("@dataFrom ", rigaProd.DataFrom);
                    var DataTo = new SqlParameter("@dataTo", DataOraApp);
                    var PezziConf = new SqlParameter("@pezziConf", numPzConfermati);
                    var PezziLasc = new SqlParameter("@pezziLasciati", numPzLasciati);
                    var PezziScar = new SqlParameter("@pezziScar", numPzScarto);
                    var TipoConf = new SqlParameter("@TipoConf", modoConfProd);
                    var DtOraApp = new SqlParameter("@DataOraApp", DataOraApp);
                    var TestConferma = new SqlParameter("@TestConferma", true);

                    var result = dbCtx
                                    .Database
                                    .ExecuteSqlRaw("EXEC stp_ConfermaProduzCompletaFull @idxMacchina, @MatrApp, @dataFrom, @dataTo, @pezziConf, @pezziLasciati, @pezziScar, @TipoConf, @DataOraApp, @TestConferma ", IdxMacchina, MatrApp, DataFrom, DataTo, PezziConf, PezziLasc, PezziScar, TipoConf, DtOraApp, TestConferma);

                    // indico eseguito!
                    answ = result > 0;
                }
            }
            catch (Exception exc)
            {
                Log.Error($"Eccezione in ConfermaProdMacchinaFull con rett ev121:{Environment.NewLine}{exc}");
            }
            return answ;
        }

        /// <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>
        /// Inserimento record in DDB
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="inizio"></param>
        /// <param name="idxStatoStart"></param>
        /// <param name="nStepEventi"></param>
        /// <param name="nRecCheck"></param>
        /// <param name="checkOnly"></param>
        /// <returns></returns>
        public bool DDB_DoRecalc(string idxMacchina, DateTime inizio, int idxStatoStart, int nStepEventi, int nRecCheck, bool checkOnly)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var Inizio = new SqlParameter("@inizio", inizio);
                var IdxStatoStart = new SqlParameter("@idxStatoStart", idxStatoStart);
                var NStepEventi = new SqlParameter("@nStepEventi", nStepEventi);
                var NRecCheck = new SqlParameter("@nRecCheck", nRecCheck);
                var CheckOnly = new SqlParameter("@CheckOnly", checkOnly);

                var result = dbCtx
                            .Database
                            .ExecuteSqlRaw("exec man.stp_ricalcolaDatiMacchinaFromDateFork @IdxMacchina, @inizio, @idxStatoStart, @nStepEventi, @nRecCheck, @CheckOnly", IdxMacchina, Inizio, IdxStatoStart, NStepEventi, NRecCheck, CheckOnly);

                // indico eseguito!
                fatto = result != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Recupera record successivo da DDB
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="inizioStato"></param>
        /// <returns></returns>
        public DiarioDiBordoModel DDB_getNext(string idxMacchina, DateTime inizioStato)
        {
            DiarioDiBordoModel dbResult = new DiarioDiBordoModel();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var DataRif = new SqlParameter("@dataRif", inizioStato);

                dbResult = dbCtx
                            .DbSetDDB
                            .FromSqlRaw("exec dbo.stp_DDB_getNextByMacchinaFrom @IdxMacchina, @dataRif", IdxMacchina, DataRif)
                            .AsNoTracking()
                            .AsEnumerable()
                            .FirstOrDefault();
            }
            return dbResult;
        }

        /// <summary>
        /// Inserimento record in DDB
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="inizioStato"></param>
        /// <param name="idxStato"></param>
        /// <param name="codArt"></param>
        /// <param name="value"></param>
        /// <param name="matrOpr"></param>
        /// <param name="pallet"></param>
        /// <returns></returns>
        public bool DDB_InsStatoBatch(string idxMacchina, DateTime inizioStato, int idxStato, string codArt, string value, int matrOpr, string pallet)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var InizioStato = new SqlParameter("@InizioStato", inizioStato);
                var IdxStato = new SqlParameter("@IdxStato", idxStato);
                var CodArticolo = new SqlParameter("@CodArticolo", codArt);
                var Value = new SqlParameter("@Value", value);
                var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                var Pallet = new SqlParameter("@pallet", pallet);

                var result = dbCtx
                            .Database
                            .ExecuteSqlRaw("exec dbo.stp_DDB_InsStatoBatch @IdxMacchina, @InizioStato, @IdxStato, @CodArticolo, @Value, @MatrOpr, @pallet", IdxMacchina, InizioStato, IdxStato, CodArticolo, Value, MatrOpr, Pallet);

                // indico eseguito!
                fatto = result > 0;
            }
            return fatto;
        }

        public void Dispose()
        {
            _configuration = null;
        }

        /// <summary>
        /// Restituisce elenco RC filtrato
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="dataFrom"></param>
        /// <param name="dataTo"></param>
        /// <returns></returns>
        public List<ElencoConfermeProdModel> ElencoConfProdFilt(string idxMacchina, DateTime dataFrom, DateTime dataTo)
        {
            List<ElencoConfermeProdModel> dbResult = new List<ElencoConfermeProdModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetElConfProd
                            .Where(x => x.IdxMacchina == idxMacchina && (dataFrom <= x.DataOraConf && x.DataOraConf <= dataTo))
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Fix ODL per Elenco Lotti
        /// </summary>
        /// <param name="idxOdl"></param>
        /// <param name="flgStorico"></param>
        /// <returns></returns>
        public bool ElencoLottiUpsertByOdl(int idxOdl, bool flgStorico)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_MagContext(_configuration))
            {
                try
                {
                    var IdxOdl = new SqlParameter("@IdxOdl", idxOdl);
                    var FlgStorico = new SqlParameter("@flgStorico", flgStorico);
                    var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_EL_UpsertByOdl @IdxODL, @flgStorico", IdxOdl, FlgStorico);
                    fatto = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ElLottiUpsertByOdl{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <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>
        /// Eliminazione record EventList (SE trovato)
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="dtEvento"></param>
        /// <param name="idxTipo"></param>
        /// <returns></returns>
        public bool EvListDelete(string idxMacchina, DateTime dtEvento, int idxTipo)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var InizioStato = new SqlParameter("@InizioStato", dtEvento);
                var IdxTipo = new SqlParameter("@IdxTipo", idxTipo);

                try
                {
                    var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("exec dbo.stp_EL_Delete @IdxMacchina, @InizioStato, @IdxTipo", IdxMacchina, InizioStato, IdxTipo);
                    // indico eseguito!
                    fatto = result > 0;
                }
                catch (Exception exc)
                {
                    string logMsg = $"Eccezione in stp_EL_Delete | macchina: {idxMacchina} | DataEv: {dtEvento} | idxTipo: {idxTipo}{Environment.NewLine}{exc}";
                    Log.Error(logMsg);
                }
            }
            return fatto;
        }

        /// <summary>
        /// Recupera record EventList date condizioni filtro
        /// </summary>
        /// <param name="idxMacchina">Idx macchina, "*" = tutte</param>
        /// <param name="dtLimit">Data limite per recupero antecedenti</param>
        /// <param name="idxTipo">Tipo evento cercato, 0 = tutti</param>
        /// <param name="maxRec">Num massimo di record da recuperare</param>
        /// <returns></returns>
        public async Task<List<EventListModel>> EvListGetLastBySearch(string idxMacchina, DateTime dtLimit, int idxTipo, int maxRec)
        {
            List<EventListModel> dbResult = new List<EventListModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = await dbCtx
                             .DbSetEvList
                             .Where(x => (idxMacchina == "*" || x.IdxMacchina == idxMacchina)
                                    && (x.InizioStato <= dtLimit)
                                    && (idxTipo == 0 || x.IdxTipo == idxTipo))
                             .OrderByDescending(x => x.InizioStato)
                             .Take(maxRec)
                             .ToListAsync();
            }
            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>
        /// Recupera elenco fermi non qualificati da filtro
        /// </summary>
        /// <param name="idxMacchina">Idx macchina, "*" = tutte</param>
        /// <param name="gg">Num massimo di giorni antecedenti</param>
        /// <param name="durataMin">Durata minima (in minuti)</param>
        /// <returns></returns>
        public List<FermiNonQualModel> FermiNonQualificatiFilt(string idxMacchina, int gg, double durataMin)
        {
            List<FermiNonQualModel> dbResult = new List<FermiNonQualModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var GG = new SqlParameter("@gg", gg);
                var DurataMin = new SqlParameter("@durataMin", durataMin);

                dbResult = dbCtx
                            .DbSetFNQ
                            .FromSqlRaw("exec dbo.stp_FNQ_getFilt @IdxMacchina, @gg, @durataMin", IdxMacchina, GG, DurataMin)
                            .AsNoTracking()
                            .AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elimina record
        /// </summary>
        /// <param name="currRecord"></param>
        public bool InsManDelete(InsManualiModel currRecord)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                                 .DbSetInsManuali
                                .Where(x => currRecord.IdxInsMan > 0 && x.IdxInsMan == currRecord.IdxInsMan)
                                .FirstOrDefault();
                // se ci fosse aggiorno...
                if (actRec != null)
                {
                    dbCtx
                        .DbSetInsManuali
                        .Remove(actRec);
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elenco insert manuali dato
        /// </summary>
        /// <param name="IdxMacc"></param>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public List<InsManualiModel> InsManFilt(string IdxMacc, DateTime dtStart, DateTime dtEnd)
        {
            List<InsManualiModel> dbResult = new List<InsManualiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                           .DbSetInsManuali
                           .Where(x => x.IdxMacchina == IdxMacc && x.InizioStato >= dtStart && x.InizioStato <= dtEnd)
                           .AsNoTracking()
                           .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Congela la giornata di ins manuali richeista
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="dtCurr"></param>
        /// <returns></returns>
        public async Task<bool> InsManFreezeDay(string idxMacchina, DateTime dtCurr)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var DataElab = new SqlParameter("@pDataOra", dtCurr);

                var dbResult = await dbCtx
                                .Database
                                .ExecuteSqlRawAsync("EXEC stp_IM_ElaboraInsManuali @IdxMacchina, @pDataOra", IdxMacc, DataElab);
                fatto = dbResult > 0;
            }

            return fatto;
        }

        /// <summary>
        /// Esegue upsert record
        /// </summary>
        /// <param name="currRecord"></param>
        public bool InsManUpsert(InsManualiModel currRecord)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                                 .DbSetInsManuali
                                .Where(x => currRecord.IdxInsMan > 0 && x.IdxInsMan == currRecord.IdxInsMan)
                                .FirstOrDefault();
                // se ci fosse aggiorno...
                if (actRec == null)
                {
                    dbCtx
                        .DbSetInsManuali
                        .Add(currRecord);
                }
                else
                {
                    actRec.CodArticolo = currRecord.CodArticolo;
                    actRec.FineStato = currRecord.FineStato;
                    actRec.IdxMacchina = currRecord.IdxMacchina;
                    actRec.IdxTipoEv = currRecord.IdxTipoEv;
                    actRec.Imported = currRecord.Imported;
                    actRec.InizioStato = currRecord.InizioStato;
                    actRec.KeyRichiesta = currRecord.KeyRichiesta;
                    actRec.MatrOpr = currRecord.MatrOpr;
                    actRec.MinProd = currRecord.MinProd;
                    actRec.PzBuoni = currRecord.PzBuoni;
                    actRec.TCiclo = currRecord.TCiclo;
                    dbCtx.Entry(actRec).State = EntityState.Modified;
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Esegue salvataggio lista records + svuotamento cache
        /// </summary>
        /// <param name="currRecord"></param>
        public bool InsManUpsert(List<InsManualiModel> listRecord)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                foreach (var currRecord in listRecord)
                {
                    var actRec = dbCtx
                                     .DbSetInsManuali
                                    .Where(x => currRecord.IdxInsMan > 0 && x.IdxInsMan == currRecord.IdxInsMan)
                                    .FirstOrDefault();
                    // se ci fosse aggiorno...
                    if (actRec == null)
                    {
                        dbCtx
                            .DbSetInsManuali
                            .Add(currRecord);
                    }
                    else
                    {
                        actRec.CodArticolo = currRecord.CodArticolo;
                        actRec.FineStato = currRecord.FineStato;
                        actRec.IdxMacchina = currRecord.IdxMacchina;
                        actRec.IdxTipoEv = currRecord.IdxTipoEv;
                        actRec.Imported = currRecord.Imported;
                        actRec.InizioStato = currRecord.InizioStato;
                        actRec.KeyRichiesta = currRecord.KeyRichiesta;
                        actRec.MatrOpr = currRecord.MatrOpr;
                        actRec.MinProd = currRecord.MinProd;
                        actRec.PzBuoni = currRecord.PzBuoni;
                        actRec.TCiclo = currRecord.TCiclo;
                        dbCtx.Entry(actRec).State = EntityState.Modified;
                    }
                }
                var res = dbCtx.SaveChanges();
                fatto = res != 0;
            }
            return fatto;
        }

        /// <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 macchina
        /// </summary>
        /// <param name="idxMacchina">Macchina</param>
        /// <param name="onlyFree">Solo disponibili (1) o tutte (0)</param>
        /// <param name="onlyDirect">Gruppo</param>
        /// <returns></returns>
        public List<PODLExpModel> ListPODLByMacc(string idxMacchina, bool onlyFree, bool onlyDirect)
        {
            List<PODLExpModel> dbResult = new List<PODLExpModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var OnlyFree = new SqlParameter("@onlyFree", onlyFree);
                var OnlyDirect = new SqlParameter("@onlyDirect", onlyDirect);

                dbResult = dbCtx
                            .DbSetPODLExp
                            .FromSqlRaw("EXEC stp_PODL_getByIdxMacc @IdxMacchina, @onlyFree, @onlyDirect", IdxMacc, OnlyFree, OnlyDirect)
                            .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>
        /// Imposto abilitazione insert eventi macchina (x fermare/avviare dati da IOB)
        /// </summary>
        /// <param name="idxMacchina">Idx macchina, "*" = tutte</param>
        /// <param name="insEnabled">Abilitazione insert ev macchina</param>
        /// <returns></returns>
        public bool MacchinaSetInsEnab(string idxMacchina, bool insEnabled)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@idxMacchina", idxMacchina);
                var InsEnabled = new SqlParameter("@insEnabled", insEnabled);

                var result = dbCtx
                                 .Database
                                 .ExecuteSqlRaw("exec dbo.stp_STM_setInsEnabled @idxMacchina, @insEnabled", IdxMacchina, InsEnabled);
                // indico eseguito!
                fatto = result > 0;
            }
            return fatto;
        }

        /// <summary>
        /// Intera tabella relazione master/slave in machine (gestione setup master --&gt; slave)
        /// </summary>
        /// <returns></returns>
        public List<Macchine2SlaveModel> Macchine2Slave()
        {
            List<Macchine2SlaveModel> dbResult = new List<Macchine2SlaveModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetM2S
                            .AsNoTracking()
                            .OrderBy(x => x.IdxMacchina)
                            .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>
        /// MicroStato macchina (da key)
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public MicroStatoMacchinaModel MicroStatoMacchina(string idxMacchina)
        {
            MicroStatoMacchinaModel dbResult = new MicroStatoMacchinaModel();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                                .DbSetMicroStatoMacc
                                .Where(x => x.IdxMacchina == idxMacchina)
                                .AsNoTracking()
                                .FirstOrDefault();
            }
            return dbResult;
        }

        //stp_STM_setInsEnabled
        /// <summary>
        /// Stato macchina - tutte
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public List<MicroStatoMacchinaModel> MicroStatoMacchinaGetAll()
        {
            List<MicroStatoMacchinaModel> dbResult = new List<MicroStatoMacchinaModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                                .DbSetMicroStatoMacc
                                .AsNoTracking()
                                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiornamento record Microstato macchina
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public bool MicroStatoMacchinaUpsert(MicroStatoMacchinaModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var actRec = dbCtx
                                .DbSetMicroStatoMacc
                                .Where(x => x.IdxMacchina == newRec.IdxMacchina)
                                .AsNoTracking()
                                .FirstOrDefault();
                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;
                }
                dbCtx.SaveChanges();
                fatto = true;
            }
            return fatto;
        }

        /// <summary>
        /// Dati MSE x singola macchina SUB (tipicamente TAV)
        /// </summary>
        /// <param name="idxMacc"></param>
        /// <param name="idxMacchSub"></param>
        /// <returns></returns>
        public List<MappaStatoExplModel> MseGetSub(string idxMacc, string idxMacchSub)
        {
            List<MappaStatoExplModel> dbResult = new List<MappaStatoExplModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxMacc = new SqlParameter("@IdxMacchina", idxMacc);
                    var IdxMacchSub = new SqlParameter("@IdxMacchSub", idxMacchSub);
                    dbResult = dbCtx
                                .DbSetMSE
                                .FromSqlRaw("EXEC stp_MSE_getByIdxMacchAndSub @IdxMacchina, @IdxMacchSub", IdxMacc, IdxMacchSub)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante MseGetSub{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// ODL da key
        /// </summary>
        /// <param name="idxOdl"></param>
        /// <param name="onlyUnused"></param>
        /// <returns></returns>
        public List<ODLExpModel> OdlByIdx(int idxOdl, bool onlyUnused)
        {
            List<ODLExpModel> dbResult = new List<ODLExpModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxOdl = new SqlParameter("@IdxOdl", idxOdl);
                    var OnlyUnused = new SqlParameter("@onlyUnused", onlyUnused);
                    dbResult = dbCtx
                                .DbSetODLExp
                                .FromSqlRaw("EXEC stp_ODL_getByIdx @IdxOdl, @onlyUnused", IdxOdl, OnlyUnused)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlByIdx{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Clear operazione setup ODL (annullamento)
        /// </summary>
        /// <param name="idxODL"></param>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public bool OdlClearSetup(int idxODL, string idxMacchina)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxODL = new SqlParameter("@idxODL", idxODL);
                    var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                    var result = dbCtx
                                  .Database
                                  .ExecuteSqlRaw("EXEC stp_ODL_clearSetup @IdxODL, @IdxMacchina", IdxODL, IdxMacchina);
                    answ = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlClearSetup{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// ODL corrente macchina
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public List<ODLExpModel> OdlCurrByMacc(string idxMacchina)
        {
            List<ODLExpModel> dbResult = new List<ODLExpModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                    dbResult = dbCtx
                                .DbSetODLExp
                                .FromSqlRaw("EXEC stp_ODL_getByMacchina @IdxMacchina", IdxMacchina)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlCurrByMaccAsync{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Attrezzo stesso ODL dell'altra tavola
        /// </summary>
        /// <param name="idxODL"></param>
        /// <param name="matrOpr"></param>
        /// <param name="idxMacchinaTo"></param>
        /// <returns></returns>
        public bool OdlDividiDaAltraTavola(int idxODL, int matrOpr, string idxMacchinaTo)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxODL = new SqlParameter("@idxODL", idxODL);
                    var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                    var IdxMaccTo = new SqlParameter("@IdxMacchinaTo", idxMacchinaTo);
                    var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_ODL_dividiDaAltraTav @idxODL, @MatrOpr, @IdxMacchinaTo", IdxODL, MatrOpr, IdxMaccTo);
                    fatto = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlDividiDaAltraTavola{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Registro fine prod ODL
        /// </summary>
        /// <param name="idxODL"></param>
        /// <param name="idxMacchina"></param>
        /// <param name="dtRif"></param>
        /// <returns></returns>
        public bool OdlFineProd(int idxODL, string idxMacchina, DateTime dtRif)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxODL = new SqlParameter("@IdxODL", idxODL);
                    var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                    var DataChiusura = new SqlParameter("@DataChiusura", dtRif);
                    var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_ODL_fineProd @IdxODL, @IdxMacchina, @DataChiusura", IdxODL, IdxMacc, DataChiusura);
                    fatto = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlFineProd{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Fix ODL per macchine SLAVE
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="numDayPrev"></param>
        /// <param name="doInsert"></param>
        /// <returns></returns>
        public bool OdlFixMachineSlave(string idxMacchina, int numDayPrev, int doInsert)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                    var NumDayPrev = new SqlParameter("@NumDayPrev", numDayPrev);
                    var DoInsert = new SqlParameter("@DoInsert", doInsert);
                    var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_ODL_fixMachineSlave @IdxMacchina, @NumDayPrev, @DoInsert", IdxMacc, NumDayPrev, DoInsert);
                    fatto = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlFixMachineSlave{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Setup PODL Postumo
        /// </summary>
        /// <param name="idxODL"></param>
        /// <param name="matrOpr"></param>
        /// <param name="idxMacchina"></param>
        /// <param name="tcRich"></param>
        /// <param name="pzPallet"></param>
        /// <param name="note"></param>
        /// <returns></returns>
        public bool OdlInizioSetup(int idxODL, int matrOpr, string idxMacchina, decimal tcRich, int pzPallet, string note)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxODL = new SqlParameter("@idxPromessa", idxODL);
                    var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                    var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                    var TCRichAttr = new SqlParameter("@TCRichAttr", tcRich);
                    var PzPallet = new SqlParameter("@PzPallet", pzPallet);
                    var Note = new SqlParameter("@Note", note);
                    var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_ODL_inizioSetup @idxODL, @MatrOpr, @IdxMacchina, @TCRichAttr, @PzPallet, @Note", IdxODL, MatrOpr, IdxMacc, TCRichAttr, PzPallet, Note);
                    fatto = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlInizioSetup{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Ultimo ODL data macchina
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public List<ODLModel> OdlLastByMacc(string idxMacchina)
        {
            List<ODLModel> dbResult = new List<ODLModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                    dbResult = dbCtx
                                .DbSetODL
                                .FromSqlRaw("EXEC stp_ODL_getLastByMacchina @IdxMacchina", IdxMacchina)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlLastByMacc{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco ODL data macchina e periodo
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public List<ODLExpModel> OdlListByMaccPeriodo(string idxMacchina, DateTime dtStart, DateTime dtEnd)
        {
            List<ODLExpModel> dbResult = new List<ODLExpModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                    var DataFrom = new SqlParameter("@dataFrom", dtStart);
                    var DataTo = new SqlParameter("@dataTo", dtEnd);
                    dbResult = dbCtx
                                .DbSetODLExp
                                .FromSqlRaw("EXEC stp_ODL_getByMacchinaPeriodo @IdxMacchina, @dataFrom, @dataTo", IdxMacchina, DataFrom, DataTo)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlListByMaccPeriodo{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Riapertura ULTIMO ODL data macchina
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public List<ODLModel> OdlReopenOdlMacc(string idxMacchina)
        {
            List<ODLModel> dbResult = new List<ODLModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                    dbResult = dbCtx
                                .DbSetODL
                                .FromSqlRaw("EXEC stp_ODL_reopenOdlMacc @IdxMacchina", IdxMacchina)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlReopenOdlMacc{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Setup ODL Postumo
        /// </summary>
        /// <param name="idxODL"></param>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public bool OdlSetupPostumo(int idxODL, string idxMacchina)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxODL = new SqlParameter("@idxODL", idxODL);
                    var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                    var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_ODL_insPostumo @idxODL, @IdxMacchina", IdxODL, IdxMacc);
                    fatto = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlSetupPostumo{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Setup PODL Postumo
        /// </summary>
        /// <param name="idxPromOdl"></param>
        /// <param name="matrOpr"></param>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public bool OdlSetupPromPostuma(int idxPromOdl, int matrOpr, string idxMacchina)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxPODL = new SqlParameter("@idxPromessa", idxPromOdl);
                    var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                    var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                    var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_ODL_inizioSetupPromessaPostuma @idxPromessa, @MatrOpr, @IdxMacchina", IdxPODL, MatrOpr, IdxMacc);
                    fatto = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlSetupPromPostuma{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Split ODL
        /// </summary>
        /// <param name="idxODL"></param>
        /// <param name="matrOpr"></param>
        /// <param name="idxMacchina"></param>
        /// <param name="TCRich"></param>
        /// <param name="pzPallet"></param>
        /// <param name="note"></param>
        /// <param name="startNewOdl"></param>
        /// <param name="qtyRich"></param>
        /// <returns></returns>
        public bool OdlSplit(int idxODL, int matrOpr, string idxMacchina, decimal TCRich, int pzPallet, string note, bool startNewOdl, int qtyRich)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxODL = new SqlParameter("@idxODL", idxODL);
                    var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                    var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                    var TCRichAttr = new SqlParameter("@TCRichAttr", TCRich);
                    var PzPallet = new SqlParameter("@PzPallet", pzPallet);
                    var Note = new SqlParameter("@Note", note);
                    var StartNewOdl = new SqlParameter("@StartNewOdl", startNewOdl);
                    var QtyRich = new SqlParameter("@QtyRich", qtyRich);
                    var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_ODL_split @idxODL, @MatrOpr, @IdxMacchina, @TCRichAttr, @PzPallet, @Note, @StartNewOdl, @QtyRich", IdxODL, MatrOpr, IdxMacc, TCRichAttr, PzPallet, Note, StartNewOdl, QtyRich);
                    fatto = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlSplit{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Update ODL (es: in setup x chiusura attrezzaggio)
        /// </summary>
        /// <param name="idxODL"></param>
        /// <param name="matrOpr"></param>
        /// <param name="tCRichAttr"></param>
        /// <param name="pzPallet"></param>
        /// <param name="note"></param>
        /// <returns></returns>
        public bool OdlUpdate(int idxODL, int matrOpr, decimal tCRichAttr, int pzPallet, string note)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxODL = new SqlParameter("@idxODL", idxODL);
                    var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                    var TCRichAttr = new SqlParameter("@TCRichAttr", tCRichAttr);
                    var PzPallet = new SqlParameter("@PzPallet", pzPallet);
                    var Note = new SqlParameter("@Note", note);
                    var result = dbCtx
                                  .Database
                                  .ExecuteSqlRaw("EXEC stp_ODL_updateSetup @IdxODL, @MatrOpr, @TCRichAttr, @PzPallet, @Note", IdxODL, MatrOpr, TCRichAttr, PzPallet, Note);
                    answ = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OdlUpdate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// login operatori
        /// </summary>
        /// ///
        /// <param name="matrOpr"></param>
        /// ///
        /// <param name="authKey"></param>
        /// <returns></returns>
        public AnagOperatoriModel OperatoreSearch(int matrOpr, string authKey)
        {
            AnagOperatoriModel dbResult = null;
            AnagOperatoriModel answ = new AnagOperatoriModel();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbOperatori
                            .Where(s => (s.MatrOpr > 0) && (s.MatrOpr == matrOpr) && (s.authKey == authKey))
                            .AsNoTracking()
                            .FirstOrDefault();
                if (dbResult != null)
                {
                    answ = dbResult;
                }
            }
            return answ;
        }

        /// <summary>
        /// Stato prod macchina
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public List<PzProdModel> PezziProdMacchina(string idxMacchina)
        {
            List<PzProdModel> dbResult = new List<PzProdModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                try
                {
                    dbResult = dbCtx
                                .DbSetPzProd
                                .FromSqlRaw("EXEC stp_PzProd_getByMacchina @IdxMacchina", IdxMacchina)
                                .AsNoTracking()
                                .ToList();
                }
                catch
                { }
            }
            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>
        /// <param name="dtEvent"></param>
        /// <returns></returns>
        public bool PODL_startSetup(PODLExpModel editRec, int matrOpr, decimal 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 FlagNoteAppend = new SqlParameter("@FlgNoteAppend", false);
                        var callResult = dbCtx
                                            .Database
                                            .ExecuteSqlRaw("EXEC stp_ODL_inizioSetupPromessa @idxPromessa, @MatrOpr, @IdxMacchina, @TCRichAttr, @PzPallet, @Note, @dtEvento, @FlgNoteAppend", IdxPromessa, MatrOpr, IdxMacchina, TCRichAttr, PzPallet, Note, DtEvento, FlagNoteAppend);

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

        /// <summary>
        /// Recupero PODL da chiave
        /// </summary>
        /// <param name="idxPODL"></param>
        /// <returns></returns>
        public PODLExpModel PODLExp_getByKey(int idxPODL)
        {
            PODLExpModel dbResult = new PODLExpModel();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxPromessa = new SqlParameter("@IdxPromessa", idxPODL);
                    var rawResult = dbCtx
                                .DbSetPODLExp
                                .FromSqlRaw("EXEC stp_PODL_getByIdx @IdxPromessa", IdxPromessa)
                                .AsNoTracking()
                                .ToList();
                    if (rawResult != null && rawResult.Count > 0)
                    {
                        dbResult = rawResult.FirstOrDefault();
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante PODL_getByKey{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero PODL da chiave
        /// </summary>
        /// <param name="idxPODL"></param>
        /// <returns></returns>
        public async Task<PODLExpModel> PODLExp_getByKeyAsync(int idxPODL)
        {
            PODLExpModel dbResult = new PODLExpModel();
            using (var dbCtx = new MoonProContext(options))
            {
                try
                {
                    var IdxPromessa = new SqlParameter("@IdxPromessa", idxPODL);
                    var rawResult = await dbCtx
                                .DbSetPODLExp
                                .FromSqlRaw("EXEC stp_PODL_getByIdx @IdxPromessa", IdxPromessa)
                                .AsNoTracking()
                                .ToListAsync();
                    if (rawResult != null && rawResult.Count > 0)
                    {
                        dbResult = rawResult.FirstOrDefault();
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante PODLExp_getByKeyAsync{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Restituisce elenco RC filtrato
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="idxODL"></param>
        /// <param name="dataFrom"></param>
        /// <param name="dataTo"></param>
        /// <returns></returns>
        public List<RegistroControlliModel> RegControlliFilt(string idxMacchina, int idxODL, DateTime dataFrom, DateTime dataTo, bool showMulti)
        {
            List<RegistroControlliModel> dbResult = new List<RegistroControlliModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var IdxODL = new SqlParameter("@IdxODL", idxODL);
                var DataFrom = new SqlParameter("@DataFrom", dataFrom);
                var DataTo = new SqlParameter("@DataTo", dataTo);
                var ShowMulti = new SqlParameter("@showMulti", showMulti);

                dbResult = dbCtx
                            .DbSetRegControlli
                            .FromSqlRaw("EXEC stp_RC_getByFilt @IdxMacchina, @IdxODL, @DataFrom, @DataTo, @ShowMulti", IdxMacc, IdxODL, DataFrom, DataTo, ShowMulti)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Registra controllo
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="matrOpr"></param>
        /// <param name="esitoOk"></param>
        /// <param name="note"></param>
        /// <param name="dataOra"></param>
        /// <returns></returns>
        public bool RegControlliInsert(string idxMacchina, int matrOpr, bool esitoOk, string note, DateTime dataOra)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                var EsitoOk = new SqlParameter("@EsitoOk", esitoOk);
                var Note = new SqlParameter("@Note", note);
                var DataOra = new SqlParameter("@DataOra", dataOra);

                var result = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_RC_insert @IdxMacchina, @MatrOpr, @EsitoOk, @Note, @DataOra", IdxMacc, MatrOpr, EsitoOk, Note, DataOra);
                fatto = result != 0;
            }
            return fatto;
        }

        /// Restituisce elenco Ultimi RC macchina </summary> <param name="idxMacchina"></param>
        /// <param name="idxODL"></param> <param name="dataFrom"></param> <param
        /// name="dataTo"></param> <returns></returns>
        public List<RegistroControlliModel> RegControlliLast(string idxMacchina)
        {
            List<RegistroControlliModel> dbResult = new List<RegistroControlliModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);

                dbResult = dbCtx
                            .DbSetRegControlli
                            .FromSqlRaw("EXEC stp_RC_getLast @IdxMacchina", IdxMacc)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiunta record RegistroScarti
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="tagCode"></param>
        /// <param name="matrOpr"></param>
        /// <param name="dataFrom"></param>
        /// <param name="dataTo"></param>
        /// <returns></returns>
        public List<RegistroDichiarazioniModel> RegDichiarGetFilt(string idxMacchina, string tagCode, int matrOpr, int idxODL, DateTime dataFrom, DateTime dataTo)
        {
            List<RegistroDichiarazioniModel> dbResult = new List<RegistroDichiarazioniModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var TagCode = new SqlParameter("@TagCode", tagCode);
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                var IdxODL = new SqlParameter("@IdxODL", idxODL);
                var DtFrom = new SqlParameter("@DataFrom", dataFrom);
                var DtTo = new SqlParameter("@DataTo", dataTo);

                dbResult = dbCtx
                            .DbSetRegDich
                            .FromSqlRaw("EXEC stp_DD_getFilt @TagCode, @IdxMacchina, @MatrOpr, @IdxODL, @DataFrom, @DataTo", TagCode, IdxMacchina, MatrOpr, IdxODL, DtFrom, DtTo)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiunta record Registro Dichiarazioni
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> RegDichiarInsert(RegistroDichiarazioniModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var TagCode = new SqlParameter("@TagCode", newRec.TagCode);
                var IdxMacchina = new SqlParameter("@IdxMacchina", newRec.IdxMacchina);
                var DtRec = new SqlParameter("@DtRec", newRec.DtRec);
                var MatrOpr = new SqlParameter("@MatrOpr", newRec.MatrOpr);
                var ValString = new SqlParameter("@ValString", newRec.ValString);

                var result = await dbCtx
                                 .Database
                                 .ExecuteSqlRawAsync("exec dbo.stp_DD_insertQuery @TagCode, @IdxMacchina, @DtRec, @MatrOpr, @ValString", TagCode, IdxMacchina, DtRec, MatrOpr, ValString);
                // indico eseguito!
                fatto = result > 0;
            }
            return fatto;
        }

        /// <summary>
        /// Update record Registro Dichiarazioni
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> RegDichiarUpdate(RegistroDichiarazioniModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var Original_IdxDich = new SqlParameter("@Original_IdxDich", newRec.IdxDich);
                var DtRec = new SqlParameter("@DtRec", newRec.DtRec);
                var TagCode = new SqlParameter("@TagCode", newRec.TagCode);
                var ValString = new SqlParameter("@ValString", newRec.ValString);
                var MatrOpr = new SqlParameter("@MatrOpr", newRec.MatrOpr);

                var result = await dbCtx
                                 .Database
                                 .ExecuteSqlRawAsync("exec dbo.stp_DD_updateQuery @Original_IdxDich, @DtRec, @TagCode, @ValString, @MatrOpr", Original_IdxDich, DtRec, TagCode, ValString, MatrOpr);
                // indico eseguito!
                fatto = result > 0;
            }
            return fatto;
        }

        /// <summary>
        /// Aggiunta record RegistroScarti
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="idxODL"></param>
        /// <param name="dataFrom"></param>
        /// <param name="dataTo"></param>
        /// <param name="showMulti"></param>
        /// <returns></returns>
        public List<RegistroScartiModel> RegScartiGetFilt(string idxMacchina, int idxODL, DateTime dataFrom, DateTime dataTo, bool showMulti)
        {
            List<RegistroScartiModel> dbResult = new List<RegistroScartiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var IdxODL = new SqlParameter("@IdxODL", idxODL);
                var DataFrom = new SqlParameter("@DataFrom", dataFrom);
                var DataTo = new SqlParameter("@DataTo", dataTo);
                var ShowMulti = new SqlParameter("@showMulti", showMulti);

                dbResult = dbCtx
                            .DbSetRegScarti
                            .FromSqlRaw("EXEC .stp_RS_getByFilt @IdxMacchina, @IdxODL, @DataFrom, @DataTo, @ShowMulti", IdxMacc, IdxODL, DataFrom, DataTo, ShowMulti)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiunta record RegistroScarti
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public bool RegScartiInsert(RegistroScartiModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@idxMacchina", newRec.IdxMacchina);
                var DataOra = new SqlParameter("@DataOra", newRec.DataOra);
                var Causale = new SqlParameter("@Causale", newRec.Causale);
                var Qta = new SqlParameter("@Qta", newRec.Qta);
                var Note = new SqlParameter("@Note", newRec.Note);
                var MatrOpr = new SqlParameter("@MatrOpr", newRec.MatrOpr);

                var result = dbCtx
                                 .DbSetRegWithCheck
                                 .FromSqlRaw("exec dbo.stp_RS_Insert_withCheck @idxMacchina, @DataOra, @Causale, @Qta, @Note, @MatrOpr", IdxMacchina, DataOra, Causale, Qta, Note, MatrOpr)
                                 .AsNoTracking()
                                 .ToList();
                // indico eseguito!
                // -1 = restituisce una select
                fatto = result.Count > 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elimina scarti KIT dato record parent (e lo resetta)
        /// </summary>
        /// <param name="parentRec"></param>
        /// <returns></returns>
        public bool RegScartiKitDelete(RegistroScartiModel parentRec)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", parentRec.IdxMacchina);
                var DtRif = new SqlParameter("@DataOra", parentRec.DataOra);
                var Causale = new SqlParameter("@Causale", parentRec.Causale);

                var dbResult = dbCtx
                              .Database
                              .ExecuteSqlRaw("EXEC stp_RSK_Delete @IdxMacchina, @DataOra, @Causale", IdxMacc, DtRif, Causale);

                answ = dbResult != 0;
            }
            return answ;
        }

        /// <summary>
        /// Elenco scarti KIT dato record parent
        /// </summary>
        /// <param name="parentRec"></param>
        /// <returns></returns>
        public List<RegistroScartiKitModel> RegScartiKitGetFilt(RegistroScartiModel parentRec)
        {
            List<RegistroScartiKitModel> dbResult = new List<RegistroScartiKitModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", parentRec.IdxMacchina);
                var DtRif = new SqlParameter("@DataRif", parentRec.DataOra);
                var Causale = new SqlParameter("@Causale", parentRec.Causale);

                dbResult = dbCtx
                            .DbSetRegScartiKit
                            .FromSqlRaw("EXEC stp_RSK_getByFilt @IdxMacchina, @DataRif, @Causale", IdxMacc, DtRif, Causale)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiunta record RegistroScartiKit in tab RSK esplodendo x kit
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> RegScartiKitSplit(RegistroScartiModel newRec)
        {
            await Task.Delay(1);
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@idxMacchina", newRec.IdxMacchina);
                var DataOra = new SqlParameter("@DataOra", newRec.DataOra);
                var Causale = new SqlParameter("@Causale", newRec.Causale);
                var CodArt = new SqlParameter("@CodArticolo", newRec.CodArticolo);
                var Qta = new SqlParameter("@QtyKit", newRec.Qta);

                var result = dbCtx
                                 .DbSetRegWithCheck
                                 .FromSqlRaw("exec dbo.stp_RSK_Split @idxMacchina, @DataOra, @Causale, @CodArticolo, @QtyKit", IdxMacchina, DataOra, Causale, CodArt, Qta)
                                 .AsNoTracking()
                                 .ToList();
                // indico eseguito!
                fatto = result.Count != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Aggiorna un record scarti KIT ( SE ESISTE...)
        /// </summary>
        /// <param name="currRec"></param>
        /// <returns></returns>
        public bool RegScartiKitUpdateQty(RegistroScartiKitModel currRec)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", currRec.IdxMacchina);
                var DtRif = new SqlParameter("@DataOra", currRec.DataOra);
                var Causale = new SqlParameter("@Causale", currRec.Causale);
                var CodArticolo = new SqlParameter("@CodArticolo", currRec.CodArticolo);
                var Qty = new SqlParameter("@Qty", currRec.Qta);

                var dbResult = dbCtx
                              .Database
                              .ExecuteSqlRaw("EXEC stp_RSK_UpdateQty @IdxMacchina, @DataOra, @Causale, @CodArticolo, @Qty", IdxMacc, DtRif, Causale, CodArticolo, Qty);

                answ = dbResult != 0;
            }
            return answ;
        }

        /// <summary>
        /// Effettua ricalcolo MSE x macchina indicata
        /// </summary>
        /// <param name="idxMacchina">idx macchina da confermare</param>
        /// <param name="maxAgeSec">Num massimo secondi di "vecchiaia" del dato</param>
        /// <returns></returns>
        public bool RicalcMse(string idxMacchina, int maxAgeSec)
        {
            bool answ = false;
            try
            {
                var rigaProd = StatoProdMacchina(idxMacchina, DateTime.Now);
                using (var dbCtx = new MoonProContext(options))
                {
                    var MaxAgeSec = new SqlParameter("@maxAgeSec ", maxAgeSec);
                    var IdxMacchina = new SqlParameter("@idxMacchina", idxMacchina);

                    var result = dbCtx
                                    .Database
                                    .ExecuteSqlRaw("EXEC stp_MSE_recalc @maxAgeSec, @idxMacchina ", MaxAgeSec, IdxMacchina);

                    // indico eseguito!
                    answ = result > 0;
                }
            }
            catch (Exception exc)
            {
                Log.Error($"Eccezione in RicalcMse:{Environment.NewLine}{exc}");
            }
            return answ;
        }

        /// <summary>
        /// Esegue il ripristino stato precedente x una macchina che abbia una dichiarazione manuale (es Pausa pranzo) da cui "uscire"
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="dtCurr"></param>
        /// <param name="valore"></param>
        /// <param name="idxStato"></param>
        /// <param name="matrOpr"></param>
        /// <returns></returns>
        public async Task<bool> RipristinaStatoPrec(string idxMacchina, DateTime dtCurr, string valore, int idxStato = 0, int matrOpr = 0)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var pIdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var pDataOra = new SqlParameter("@DataOra", dtCurr);
                var pIdxStato = new SqlParameter("@IdxStato", idxStato);
                var pMatrOpr = new SqlParameter("@MatrOpr", matrOpr);
                var pValueEv = new SqlParameter("@ValueEv", valore);

                var dbResult = await dbCtx
                                .Database
                                .ExecuteSqlRawAsync("EXEC stp_DDB_RipristinaStato @IdxMacchina, @DataOra, @IdxStato, @MatrOpr, @ValueEv", pIdxMacc, pDataOra, pIdxStato, pMatrOpr, pValueEv);
                fatto = dbResult > 0;
            }

            return fatto;
        }

        public bool SetDerogaSt(StCheckOverride deroga)
        {
            bool fatto = false;
#if false
            try
            {
                string keyDerogaST = memLayer.ML.redHash($"DerogaSt:{user_std.UtSn.utente}:{deroga.IdxST:000}");
                string rawData = JsonConvert.SerializeObject(deroga);
                memLayer.ML.setRSV(keyDerogaST, rawData, 60 * 2);
                fatto = true;
            }
            catch
            { }
#endif
            return fatto;
        }

        /// <summary>
        /// Intera tabella state machine eventi 2 stati
        /// </summary>
        /// <returns></returns>
        public List<TransizioneStatiModel> SMES_GetAll()
        {
            List<TransizioneStatiModel> dbResult = new List<TransizioneStatiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetSMES
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Tabella state machine eventi 2 stati data famiglia
        /// </summary>
        /// <returns></returns>
        public List<TransizioneStatiModel> SMES_GetByFam(int idxFam)
        {
            List<TransizioneStatiModel> dbResult = new List<TransizioneStatiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetSMES
                            .Where(x => x.IdxFamiglia == idxFam)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Tabella state machine eventi 2 stati data macchina e tipo evento
        /// </summary>
        /// <param name="idxTipo"></param>
        /// <returns></returns>
        public List<TransizioneStatiModel> SMES_getHwTransitions(string idxMacchina, int idxTipo)
        {
            List<TransizioneStatiModel> dbResult = new List<TransizioneStatiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var IdxTipo = new SqlParameter("@IdxTipo", idxTipo);
                dbResult = dbCtx
                            .DbSetSMES
                            .FromSqlRaw("exec dbo.stp_TS_getByIdxMacchIdxTipoEv @IdxMacchina, @IdxTipo", IdxMacchina, IdxTipo)
                            .AsNoTracking()
                            .AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Tabella state machine eventi 2 stati data macchina e tipo evento
        /// </summary>
        /// <param name="idxTipo"></param>
        /// <returns></returns>
        public List<TransizioneStatiModel> SMES_getUserForced(string idxMacchina, int idxTipo)
        {
            List<TransizioneStatiModel> dbResult = new List<TransizioneStatiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var IdxTipo = new SqlParameter("@IdxTipo", idxTipo);
                dbResult = dbCtx
                            .DbSetSMES
                            .FromSqlRaw("exec dbo.stp_TS_getUserForcedTrans @IdxMacchina, @IdxTipo", IdxMacchina, IdxTipo)
                            .AsNoTracking()
                            .AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Restituisce elenco gruppi Scheda tecnica
        /// </summary>
        /// <returns></returns>
        public List<ST_AnagGruppi> ST_AnagGruppiList()
        {
            List<ST_AnagGruppi> dbResult = new List<ST_AnagGruppi>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetStAnagGruppi
                            .OrderBy(x => x.OrdVisual)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        public bool ST_CheckCleanByOdl(int idxOdl)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxOdl = new SqlParameter("@IdxOdl", idxOdl);

                var result = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_ST_CHK_cleanByOdl @IdxOdl", IdxOdl);
                fatto = result != 0;
            }
            return fatto;
        }

        public bool ST_CheckUpsert(int idxOdl, int idxST, int oggetto, int num, string valueRead, string extCode, bool checkOk, string userMod, bool forced)
        {
            bool fatto = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxOdl = new SqlParameter("@IdxOdl", idxOdl);
                var IdxST = new SqlParameter("@IdxST", idxST);
                var Oggetto = new SqlParameter("@Oggetto", oggetto);
                var Num = new SqlParameter("@Num", num);
                var ValueRead = new SqlParameter("@ValueRead", valueRead);
                var ExtCode = new SqlParameter("@ExtCode", extCode);
                var CheckOk = new SqlParameter("@CheckOk", checkOk);
                var UserMod = new SqlParameter("@UserMod", userMod);
                var Forced = new SqlParameter("@Forced", forced);

                var result = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_ST_CHK_upsert @IdxOdl, @IdxST, @Oggetto, @Num, @ValueRead, @ExtCode, @CheckOk, @UserMod, @Forced", IdxOdl, IdxST, Oggetto, Num, ValueRead, ExtCode, CheckOk, UserMod, Forced);
                fatto = result != 0;
            }
            return fatto;
        }

        /// <summary>
        /// Recupero Righe (Actual) della scheda tecnica da GRUPPO + ODL
        /// </summary>
        /// <param name="codGruppo"></param>
        /// <param name="idxODL"></param>
        /// <returns></returns>
        public List<ST_ActRow> STAR_byGrpOdl(string codGruppo, int idxODL)
        {
            List<ST_ActRow> dbResult = new List<ST_ActRow>();
            using (var dbCtx = new MoonProContext(options))
            {
                var CodGruppo = new SqlParameter("@CodGruppo", codGruppo);
                var IdxODL = new SqlParameter("@IdxODL", idxODL);

                dbResult = dbCtx
                            .DbSetStActRow
                            .FromSqlRaw("exec dbo.stp_ST_AR_getByGrpOdl @CodGruppo, @IdxODL", CodGruppo, IdxODL)
                            .AsNoTracking()
                            //.AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero Righe (Actual) della scheda tecnica da GRUPPO + ODL + label
        /// </summary>
        /// <param name="codGruppo"></param>
        /// <param name="label"></param>
        /// <param name="idxODL"></param>
        /// <returns></returns>
        public List<ST_ActRow> STAR_byGrpOdlLbl(string codGruppo, string label, int idxODL)
        {
            List<ST_ActRow> dbResult = new List<ST_ActRow>();
            using (var dbCtx = new MoonProContext(options))
            {
                var CodGruppo = new SqlParameter("@CodGruppo", codGruppo);
                var Label = new SqlParameter("@Label", label);
                var IdxODL = new SqlParameter("@IdxODL", idxODL);

                dbResult = dbCtx
                            .DbSetStActRow
                            .FromSqlRaw("exec dbo.stp_ST_AR_getGrpOdlLabel @CodGruppo, @Label, @IdxODL", CodGruppo, Label, IdxODL)
                            .AsNoTracking()
                            //.AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero Righe pending da ODL
        /// </summary>
        /// <param name="idxODL"></param>
        /// <returns></returns>
        public List<ST_ActRow> STAR_pendByOdl(int idxODL)
        {
            List<ST_ActRow> dbResult = new List<ST_ActRow>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxODL = new SqlParameter("@IdxODL", idxODL);

                dbResult = dbCtx
                            .DbSetStActRow
                            .FromSqlRaw("exec dbo.stp_ST_AR_getPendingOdl @IdxODL", IdxODL)
                            .AsNoTracking()
                            //.AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Intera tabella state machine ingressi 2 eventi data famiglia
        /// </summary>
        /// <param name="idxFam"></param>
        /// <returns></returns>
        public List<TransizioneIngressiModel> StateMachineIngressi(int idxFam)
        {
            List<TransizioneIngressiModel> dbResult = new List<TransizioneIngressiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxFamIn = new SqlParameter("@IdxFamigliaIngresso", idxFam);
                dbResult = dbCtx
                            .DbSetSMI
                            .FromSqlRaw("exec dbo.stp_TRI_getByIdxFamIng @IdxFamigliaIngresso", IdxFamIn)
                            .AsNoTracking()
                            .AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        /// <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>
        /// Stato prod macchina (completo)
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="dtReq"></param>
        /// <returns></returns>
        public StatoProdModel StatoProdMacchina(string idxMacchina, DateTime dtReq)
        {
            StatoProdModel dbResult = new StatoProdModel();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var DataOra = new SqlParameter("@DataOra ", dtReq);
                var rawData = dbCtx
                            .DbSetStatoProd
                            .FromSqlRaw("EXEC stp_StatoProd_getByMacchina @IdxMacchina, @DataOra ", IdxMacchina, DataOra)
                            .AsNoTracking()
                            .AsEnumerable()
                            .ToList();
                dbResult = rawData
                            .FirstOrDefault();
            }
            return dbResult;
        }

        /// <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>
        /// Elenco turni macchina (all)
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public TurniMaccModel TurnoMacchinaGet(string idxMacchina)
        {
            TurniMaccModel dbResult = new TurniMaccModel();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                dbResult = dbCtx
                            .DbSetTurniMacc
                            .FromSqlRaw("exec dbo.stp_turniMacchineByIdxMacc @IdxMacchina", IdxMacchina)
                            .AsNoTracking()
                            .AsEnumerable()
                            .FirstOrDefault();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco turni macchina (all)
        /// </summary>
        /// <returns></returns>
        public List<TurniMaccModel> TurnoMacchinaGetAll()
        {
            List<TurniMaccModel> dbResult = new List<TurniMaccModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                                .DbSetTurniMacc
                                .AsNoTracking()
                                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Toggle turno macchina (da numero)
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="numTurno"></param>
        /// <returns></returns>
        public bool TurnoMacchinaToggle(string idxMacchina, int numTurno)
        {
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
                var NumTurno = new SqlParameter("@numTurno", numTurno);

                var result = dbCtx
                                .Database
                                .ExecuteSqlRaw("exec dbo.stp_turniMacchineUpdateTurno @IdxMacchina, @numTurno", IdxMacchina, NumTurno);

                // indico eseguito!
                answ = result > 0;
            }
            return answ;
        }

        /// <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>
        /// Elenco causali scarto
        /// </summary>
        /// <returns></returns>
        public List<vSelCauScartoModel> VSCS_getAll()
        {
            List<vSelCauScartoModel> dbResult = new List<vSelCauScartoModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetVSCS
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco ultimi ODL x macchina
        /// </summary>
        /// <param name="idxMacchina">Macchina</param>
        /// <param name="numRec"></param>
        /// <returns></returns>
        public List<vSelOdlModel> VSOdlGetLastByMacc(string idxMacchina, int numRec)
        {
            List<vSelOdlModel> dbResult = new List<vSelOdlModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var NumRec = new SqlParameter("@numRec", numRec);

                dbResult = dbCtx
                            .DbSetVSODL
                            .FromSqlRaw("EXEC stp_vsODL_getLastByMacc @IdxMacchina, @numRec", IdxMacc, NumRec)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco prossimi ODL/PODL x macchina
        /// </summary>
        /// <param name="idxMacchina">Macchina</param>
        /// <param name="showAll"></param>
        /// <param name="numDayAdd"></param>
        /// <returns></returns>
        public List<vSelOdlModel> VSOdlGetUnused(string idxMacchina, bool showAll, int numDayAdd)
        {
            List<vSelOdlModel> dbResult = new List<vSelOdlModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
                var ShowAll = new SqlParameter("@showAll", showAll);
                var NumDayAdd = new SqlParameter("@numDayAdd", numDayAdd);

                dbResult = dbCtx
                            .DbSetVSODL
                            .FromSqlRaw("EXEC stp_vsODL_getUnused @IdxMacchina, @showAll, @numDayAdd", IdxMacc, ShowAll, NumDayAdd)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        #endregion Public Methods

        #region Private Fields

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

        #endregion Private Fields
    }
}