using EgwCoreLib.Utils;
using GPW.CORE.Data.DbModels;
using GPW.CORE.Data.DTO;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using NLog;
using System.Data;

namespace GPW.CORE.Data.Controllers
{
    public class GPWController : IDisposable
    {
        #region Public Constructors

        public GPWController(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        #endregion Public Constructors

        #region Public Methods

        /// <summary>
        /// Recupera l'elenco Clienti (tutti)
        /// </summary>
        /// <returns></returns>
        public List<AnagClientiModel> AnagClientiAll()
        {
            // init dati necessari
            List<AnagClientiModel> dbResult = new List<AnagClientiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetAnagClienti
                                .OrderBy(x => x.RagSociale)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagClientiAllAsync:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elimina il cliente (se non ci sono relazioni con fasi attive, in tal caso rende disattivo...)
        /// </summary>
        /// <returns></returns>
        public bool AnagClientiDelete(AnagClientiModel remRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                  .DbSetAnagClienti
                                  .Where(x => x.IdxCliente == remRec.IdxCliente)
                                  .FirstOrDefault();
                    if (dbRec != null)
                    {
                        // controllo se abbia progetti associati
                        var listFasi = dbCtx
                                        .DbSetAnagProgetti
                                        .Where(x => x.IdxCliente == remRec.IdxCliente)
                                        .ToList();
                        // --> in tal caso disattivo...
                        if (listFasi != null && listFasi.Count > 0)
                        {
                            dbRec.Attivo = false;
                            //set modificato
                            dbCtx.Entry(dbRec).State = EntityState.Modified;
                        }
                        else
                        {
                            // altrimenti elimino davvero...
                            dbCtx
                                .DbSetAnagClienti
                                .Remove(dbRec);
                        }
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagClientiDelete:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupera l'elenco Clienti (tutti)
        /// </summary>
        /// <returns></returns>
        public bool AnagClientiUpsert(AnagClientiModel upsRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetAnagClienti
                                    .Where(x => x.IdxCliente == upsRec.IdxCliente)
                                    .FirstOrDefault();
                    if (dbRec == null)
                    {
                        dbCtx
                            .DbSetAnagClienti
                            .Add(upsRec);
                    }
                    else
                    {
                        dbRec.Attivo = upsRec.Attivo;
                        dbRec.Cap = upsRec.Cap;
                        dbRec.Cf = upsRec.Cf;
                        dbRec.Citta = upsRec.Citta;
                        dbRec.CodExt = upsRec.CodExt;
                        dbRec.Email = upsRec.Email;
                        dbRec.Indirizzo = upsRec.Indirizzo;
                        dbRec.Nota = upsRec.Nota;
                        dbRec.PIva = upsRec.PIva;
                        dbRec.Prov = upsRec.Prov;
                        dbRec.RagSociale = upsRec.RagSociale;
                        dbRec.Tel = upsRec.Tel;
                        //set modificato
                        dbCtx.Entry(dbRec).State = EntityState.Modified;
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagClientiUpsert:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Cerca un device dato il suo secret
        /// </summary>
        /// <param name="devSecret"></param>
        /// <returns></returns>
        public AnagDeviceModel? AnagDeviceByKey(string devSecret)
        {
            // init dati necessari
            AnagDeviceModel? dbResult = new AnagDeviceModel();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetAnagDevices
                                .Where(x => x.DeviceSecret == devSecret)
                                .FirstOrDefault();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagDeviceByKey:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Crea un record device
        /// </summary>
        /// <param name="newRecord"></param>
        /// <returns></returns>
        public bool AnagDeviceInsert(AnagDeviceModel newRecord)
        {
            // init dati necessari
            bool done = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    dbCtx
                            .DbSetAnagDevices
                            .Add(newRecord);
                    // ora salvo!
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagDeviceInsert:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Crea un record device
        /// </summary>
        /// <param name="currItem"></param>
        /// <returns></returns>
        public bool AnagDeviceUpdate(AnagDeviceModel currItem)
        {
            // init dati necessari
            bool done = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    if (currItem.IdxDevice == 0)
                    {
                        dbCtx.Entry(currItem).State = EntityState.Added;
                    }
                    else
                    {
                        // recupero vecchio
                        var oldRec = dbCtx
                                        .DbSetAnagDevices
                                        .Where(x => x.IdxDevice == currItem.IdxDevice)
                                        .FirstOrDefault();
                        if (oldRec != null)
                        {
                            oldRec.LastIpv4 = currItem.LastIpv4;
                            oldRec.DataOraLastSeen = DateTime.Now;
                            oldRec.Description = currItem.Description;
                            oldRec.ScreenSize = currItem.ScreenSize;
                            dbCtx
                                .DbSetAnagDevices
                                .Update(oldRec);
                        }
                        // altrimenti aggiungo
                        else
                        {
                            dbCtx
                                .DbSetAnagDevices
                                .Update(currItem);
                        }
                    }
                    // ora salvo!
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagDeviceUpdate:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Recupera l'elenco delle fasi (tutte)
        /// </summary>
        /// <returns></returns>
        public List<AnagFasiModel> AnagFasiAll(bool onlyActive)
        {
            // init dati necessari
            List<AnagFasiModel> dbResult = new List<AnagFasiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetAnagFasi
                                .Where(x => (x.ProgettoNav != null && x.ProgettoNav.Attivo == true) && (((bool)x.Attivo == true && onlyActive == true) || !onlyActive))
                                .Include(p => p.ProgettoNav)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco fasi dato ancestor
        /// </summary>
        /// <param name="idxFaseAnc"></param>
        /// <returns></returns>
        public List<AnagFasiModel> AnagFasiByAncestor(int idxFaseAnc)
        {
            // init dati necessari
            List<AnagFasiModel> dbResult = new List<AnagFasiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    var searchRecord = dbCtx
                                .DbSetAnagFasi
                                .Where(x => x.IdxFaseAncest == idxFaseAnc)
                                .Include(p => p.ProgettoNav)
                                .ToList();
                    if (searchRecord != null)
                    {
                        dbResult = searchRecord;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiByAncestor:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera singola fase
        /// </summary>
        /// <param name="idxFase"></param>
        /// <returns></returns>
        public AnagFasiModel AnagFasiByKey(int idxFase)
        {
            // init dati necessari
            AnagFasiModel dbResult = new AnagFasiModel();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    var searchRecord = dbCtx
                                .DbSetAnagFasi
                                .Where(x => x.IdxFase == idxFase)
                                .Include(p => p.ProgettoNav)
                                .FirstOrDefault();
                    if (searchRecord != null)
                    {
                        dbResult = searchRecord;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiByKey:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco fasi dato chiave progetto
        /// </summary>
        /// <param name="idxProj"></param>
        /// <returns></returns>
        public List<AnagFasiModel> AnagFasiByProj(int idxProj)
        {
            // init dati necessari
            List<AnagFasiModel> dbResult = new List<AnagFasiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetAnagFasi
                                .Where(x => x.IdxProgetto == idxProj)
                                .Include(p => p.ProgettoNav)
                                .OrderBy(x => x.CodFase)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiByProj:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public bool AnagFasiDelete(int IdxFase)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                  .DbSetAnagFasi
                                  .Where(x => x.IdxFase == IdxFase)
                                  .FirstOrDefault();
                    if (dbRec != null)
                    {
                        // verifico NON abbia child...
                        var listChild = dbCtx
                                          .DbSetAnagFasi
                                          .Where(x => x.IdxFaseAncest == IdxFase)
                                          .ToList();
                        if (listChild == null || listChild.Count == 0)
                        {
                            dbCtx
                                .DbSetAnagFasi
                                .Remove(dbRec);
                        }
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiDelete:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Esegue stored x DUPLICARE Fase (ancestor) tra progetti
        /// </summary>
        /// <param name="idxProjDest"></param>
        /// <param name="idxFaseSrc"></param>
        /// <returns></returns>
        public bool AnagFasiDupFase(int idxProjDest, int idxFaseSrc)
        {
            bool fatto = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxPrj = new SqlParameter("@idxProgetto", idxProjDest);
                    var pIdxFase = new SqlParameter("@Original_idxFase", idxFaseSrc);

                    var dbResult = dbCtx
                                   .Database
                                   .ExecuteSqlRaw("EXEC stp_AF_clonaFaseAnc @idxProgetto, @Original_idxFase", pIdxPrj, pIdxFase);
                    fatto = dbResult != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiDupFase:{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Esegue stored x DUPLICARE SottoFase in un altra Fase (ancestor)
        /// </summary>
        /// <param name="idxFaseDest"></param>
        /// <param name="idxFaseSrc"></param>
        /// <returns></returns>
        public bool AnagFasiDupSubFase(int idxFaseDest, int idxFaseSrc)
        {
            bool fatto = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxFaseDest = new SqlParameter("@idxFaseAncest", idxFaseDest);
                    var pIdxFaseSrc = new SqlParameter("@Original_idxFase", idxFaseSrc);

                    var dbResult = dbCtx
                                   .Database
                                   .ExecuteSqlRaw("EXEC stp_AF_clonaFase @idxFaseAncest, @Original_idxFase", pIdxFaseDest, pIdxFaseSrc);
                    fatto = dbResult != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiDupSubFase:{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Elenco FasiExpl dato chiave progetto
        /// </summary>
        /// <param name="idxProj"></param>
        /// <returns></returns>
        public List<AnagFasiExplModel> AnagFasiExplByProj(int idxProj)
        {
            // init dati necessari
            List<AnagFasiExplModel> dbResult = new List<AnagFasiExplModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxPrj = new SqlParameter("@idxProgetto", idxProj);

                    dbResult = dbCtx
                                .DbSetAnagFasiExpl
                                .FromSqlRaw("EXEC stp_AF_getByIdxProj @idxProgetto", pIdxPrj)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiExplByProj:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiorno tutte le fasi del proj con il tag indicato
        /// </summary>
        /// <param name="idxProj"></param>
        /// <param name="CodTagFase"></param>
        /// <returns></returns>
        public bool AnagFasiForceTag(int idxProj, string CodTagFase)
        {
            int result = 0;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxPrj = new SqlParameter("@idxProgetto", idxProj);
                    var pCodTag = new SqlParameter("@CodTagFase", CodTagFase);

                    result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_AF_updCodFaseByProj @idxProgetto, @CodTagFase", pIdxPrj, pCodTag);
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiForceTag:{Environment.NewLine}{exc}");
                }
            }
            return result != 0;
        }

        /// <summary>
        /// Esegue stored x spostare Fase (ancestor) tra progetti
        /// </summary>
        /// <param name="idxProjDest"></param>
        /// <param name="idxFaseSrc"></param>
        /// <returns></returns>
        public bool AnagFasiMovFase(int idxProjDest, int idxFaseSrc)
        {
            bool fatto = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxPrj = new SqlParameter("@idxProgetto", idxProjDest);
                    var pIdxFase = new SqlParameter("@Original_idxFase", idxFaseSrc);

                    var dbResult = dbCtx
                                   .Database
                                   .ExecuteSqlRaw("EXEC stp_AF_updateProgetto @idxProgetto, @Original_idxFase", pIdxPrj, pIdxFase);
                    fatto = dbResult != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiMovFase:{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Esegue stored x spostare SottoFase in un altra Fase (ancestor)
        /// </summary>
        /// <param name="idxFaseDest"></param>
        /// <param name="idxFaseSrc"></param>
        /// <returns></returns>
        public bool AnagFasiMovSubFase(int idxFaseDest, int idxFaseSrc)
        {
            bool fatto = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxFaseDest = new SqlParameter("@idxFaseAncest", idxFaseDest);
                    var pIdxFaseSrc = new SqlParameter("@Original_idxFase", idxFaseSrc);

                    var dbResult = dbCtx
                                   .Database
                                   .ExecuteSqlRaw("EXEC stp_AF_updateFaseAncest @idxFaseAncest, @Original_idxFase", pIdxFaseDest, pIdxFaseSrc);
                    fatto = dbResult != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagFasiMovSubFase:{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Esecuzione upsert fase
        /// </summary>
        /// <param name="upsRec"></param>
        /// <returns></returns>
        public bool AnagFasiUpsert(AnagFasiModel upsRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetAnagFasi
                                    .Where(x => x.IdxFase == upsRec.IdxFase && upsRec.IdxFase > 0)
                                    .FirstOrDefault();
                    if (dbRec == null)
                    {
                        dbCtx
                            .DbSetAnagFasi
                            .Add(upsRec);
                    }
                    else
                    {
                        dbRec.Attivo = upsRec.Attivo;
                        dbRec.BudgetMoney = upsRec.BudgetMoney;
                        dbRec.BudgetTime = upsRec.BudgetTime;
                        dbRec.CodExt = upsRec.CodExt;
                        dbRec.CodTagFase = upsRec.CodTagFase;
                        dbRec.DescrizioneFase = upsRec.DescrizioneFase;
                        dbRec.EnableMoney = upsRec.EnableMoney;
                        dbRec.EnableTime = upsRec.EnableTime;
                        dbRec.IdxFaseAncest = upsRec.IdxFaseAncest;
                        dbRec.IdxProgetto = upsRec.IdxProgetto;
                        dbRec.NomeFase = upsRec.NomeFase;
                        dbRec.PercOpen = upsRec.PercOpen;
                        //set modificato
                        dbCtx.Entry(dbRec).State = EntityState.Modified;
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagTagFasiUpsert:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Elenco Giustificativi
        /// </summary>
        /// <returns></returns>
        public List<AnagGiustModel> AnagGiust()
        {
            // init dati necessari
            List<AnagGiustModel> dbResult = new List<AnagGiustModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetAnagGiust
                                .OrderBy(x => x.codGiust)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagGiust:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public List<AnagGruppiModel> AnagGruppiAll()
        {
            // init dati necessari
            List<AnagGruppiModel> dbResult = new List<AnagGruppiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetAnagGruppi
                                .Include(d => d.Dipendenti2GruppiNav)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagGruppiAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Eliminazione record gruppo
        /// </summary>
        /// <param name="currItem"></param>
        /// <returns></returns>
        public bool AnagGruppiDelete(AnagGruppiModel currItem)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetAnagGruppi
                                    .Where(x => x.Gruppo == currItem.Gruppo)
                                    .FirstOrDefault();
                    if (dbRec != null)
                    {
                        dbCtx
                            .DbSetAnagGruppi
                            .Remove(dbRec);
                        dbCtx.SaveChanges();
                        answ = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagGruppiDelete:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Upsert record gruppo
        /// </summary>
        /// <param name="upsRec"></param>
        /// <returns></returns>
        public bool AnagGruppiUpsert(AnagGruppiModel upsRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetAnagGruppi
                                    .Where(x => x.Gruppo == upsRec.Gruppo)
                                    .FirstOrDefault();
                    if (dbRec == null)
                    {
                        dbCtx
                            .DbSetAnagGruppi
                            .Add(upsRec);
                    }
                    else
                    {
                        dbRec.CodExt = upsRec.CodExt;
                        dbRec.DescrGruppo = upsRec.DescrGruppo;
                        dbRec.ExportEnab = upsRec.ExportEnab;
                        dbRec.Gruppo = upsRec.Gruppo;
                        //set modificato
                        dbCtx.Entry(dbRec).State = EntityState.Modified;
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagGruppiUpsert:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        public List<AnagGruppiModel> AnagGruppiUser(int idxDipendente)
        {
            // init dati necessari
            List<AnagGruppiModel> dbResult = new List<AnagGruppiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetAnagGruppi
                                .Where(x => x.Dipendenti2GruppiNav.Contains(new Dipendenti2GruppiModel() { IdxDipendente = idxDipendente, Gruppo = x.Gruppo }))
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagGruppiUser:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public List<AnagKeyValueModel> AnagKeyValGetAll()
        {
            List<AnagKeyValueModel> dbResult = new List<AnagKeyValueModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                dbResult = dbCtx
                    .DbSetAnagKeyValue
                    .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Anagrafica orari gestiti
        /// </summary>
        /// <returns></returns>

        public List<AnagOrariModel> AnagOrarioAll()
        {
            List<AnagOrariModel> dbResult = new List<AnagOrariModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set....
                    dbResult = dbCtx
                                .DbSetAnagOrari
                                .Include(d => d.DipendentiNav)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagOrarioAll:{Environment.NewLine}{exc}");
                }
            }
            if (dbResult == null)
            {
                dbResult = new List<AnagOrariModel>();
            }
            return dbResult;
        }

        /// <summary>
        /// Dati orario del dipendente indicato
        /// </summary>
        /// <param name="idxProj"></param>
        /// <returns></returns>
        public AnagOrariModel AnagOrarioByDip(int idxDip)
        {
            AnagOrariModel? dbResult = new AnagOrariModel();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set....
                    dbResult = dbCtx
                                .DbSetAnagOrari
                                .Where(x => x.DipendentiNav.Count > 0 && x.DipendentiNav.First().IdxDipendente == idxDip)
                                .FirstOrDefault();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagOrarioByDip:{Environment.NewLine}{exc}");
                }
            }
            if (dbResult == null)
            {
                dbResult = new AnagOrariModel();
            }
            return dbResult;
        }

        /// <summary>
        /// Eliminazione orario, con controllo NON sia usato...
        /// </summary>
        /// <returns></returns>
        public bool AnagOrarioDelete(AnagOrariModel rec2del)
        {
            bool fatto = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // verifica preliminare NON sia usato da dip...
                    int numDip = dbCtx
                                    .DbSetDipendenti
                                    .Where(x => x.CodOrario == rec2del.codOrario)
                                    .Count();
                    if (numDip == 0)
                    {
                        // recupero record....
                        var dbRec = dbCtx
                                    .DbSetAnagOrari
                                    .Where(x => x.codOrario == rec2del.codOrario)
                                    .FirstOrDefault();
                        if (dbRec != null)
                        {
                            dbCtx.DbSetAnagOrari.Remove(dbRec);
                            int numRec = dbCtx.SaveChanges();
                            fatto = numRec != 0;
                        }
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagOrarioDelete:{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Tabella setup dati orari - elenco completo
        /// </summary>
        /// <param name="idxProj"></param>
        /// <returns></returns>
        public List<AnagOrariModel> AnagOrarioGetAll()
        {
            List<AnagOrariModel> dbResult = new List<AnagOrariModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set....
                    dbResult = dbCtx
                                .DbSetAnagOrari
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagOrarioGetAll:{Environment.NewLine}{exc}");
                }
            }
            if (dbResult == null)
            {
                dbResult = new List<AnagOrariModel>();
            }
            return dbResult;
        }

        /// <summary>
        /// Insert/Update record orario
        /// </summary>
        /// <param name="rec2upd"></param>
        /// <param name="codOrarioOrig"></param>
        /// <returns></returns>
        public bool AnagOrarioUpsert(AnagOrariModel rec2upd, string codOrarioOrig)
        {
            bool fatto = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero record....
                    var dbRec = dbCtx
                                .DbSetAnagOrari
                                .Where(x => x.codOrario == codOrarioOrig && !string.IsNullOrEmpty(codOrarioOrig))
                                .FirstOrDefault();
                    if (dbRec != null)
                    {
                        dbRec.codOrario = rec2upd.codOrario;
                        dbRec.descOrario = rec2upd.descOrario;
                        dbRec.oreLun = rec2upd.oreLun;
                        dbRec.oreMar = rec2upd.oreMar;
                        dbRec.oreMer = rec2upd.oreMer;
                        dbRec.oreGio = rec2upd.oreGio;
                        dbRec.oreVen = rec2upd.oreVen;
                        dbRec.oreSab = rec2upd.oreSab;
                        dbRec.oreDom = rec2upd.oreDom;
                        dbRec.oreStraordAss = dbRec.oreStraordAss;
                        dbRec.autoCompOreOrd = dbRec.autoCompOreOrd;
                        dbRec.oraInizio1 = dbRec.oraInizio1;
                        dbRec.oraFine1 = dbRec.oraFine1;
                        dbRec.oraInizio2 = dbRec.oraInizio2;
                        dbRec.oraFine2 = dbRec.oraFine2;
                        dbRec.oraInizio3 = dbRec.oraInizio3;
                        dbRec.oraFine3 = dbRec.oraFine3;
                        dbCtx.Entry(dbRec).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx.DbSetAnagOrari.Add(rec2upd);
                    }
                    int numRec = dbCtx.SaveChanges();
                    fatto = numRec != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagOrarioUpsert:{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        public List<AnagProgettiModel> AnagProjAll(bool onlyActive)
        {
            // init dati necessari
            List<AnagProgettiModel> dbResult = new List<AnagProgettiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set....
                    dbResult = dbCtx
                                .DbSetAnagProgetti
                                .Where(x => ((bool)(x.Attivo ?? false) == true && onlyActive == true) || !onlyActive)
                                .Include(c => c.ClienteNav)
                                .Include(g => g.GruppiNav)
                                .OrderBy(x => x.NomeProj)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagProjAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera elenco record da idx cliente
        /// </summary>
        /// <returns></returns>
        public List<AnagProgettiModel> AnagProjByCli(int idxCliente)
        {
            // init dati necessari
            List<AnagProgettiModel> dbResult = new List<AnagProgettiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set....
                    dbResult = dbCtx
                                .DbSetAnagProgetti
                                .Where(x => x.IdxCliente == idxCliente)
                                .OrderBy(x => x.NomeProj)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagProjByCliAsync:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public AnagProgettiModel AnagProjByKey(int IdxProj)
        {
            // init dati necessari
            AnagProgettiModel dbResult = new AnagProgettiModel();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set....
                    var dbRec = dbCtx
                                .DbSetAnagProgetti
                                .Where(x => (x.IdxProgetto == IdxProj))
                                .FirstOrDefault();
                    if (dbRec != null)
                    {
                        dbResult = dbRec;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagProjByKey:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco progetti calcolati con ore avanzamento filtrata
        /// </summary>
        /// <param name="gruppo"></param>
        /// <param name="idxCliente"></param>
        /// <param name="showPrjArch"></param>
        /// <param name="showPrjZeroH"></param>
        /// <param name="showPrjStar"></param>
        /// <returns></returns>
        public List<CalcOreProgettiModel> AnagProjCalcFilt(string gruppo, int idxCliente, bool showPrjArch, bool showPrjZeroH, bool showPrjStar)
        {
            List<CalcOreProgettiModel> dbResult = new List<CalcOreProgettiModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                var pGruppo = new SqlParameter("@gruppo", gruppo);
                var pIdxCli = new SqlParameter("@idxCliente", idxCliente);
                var pShowPAr = new SqlParameter("@showPrjArch", showPrjArch);
                var pShowPZH = new SqlParameter("@showPrjZeroH", showPrjZeroH);
                var pShowPSt = new SqlParameter("@showPrjStar", showPrjStar);

                dbResult = dbCtx
                            .DbSetCalcOreProj
                            .FromSqlRaw("EXEC stp_AP_Expl_getData @gruppo, @idxCliente, @showPrjArch, @showPrjZeroH, @showPrjStar", pGruppo, pIdxCli, pShowPAr, pShowPZH, pShowPSt)
                            .ToList();
            }
            return dbResult;
        }

        public bool AnagProjDelete(AnagProgettiModel remRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                  .DbSetAnagProgetti
                                  .Where(x => x.IdxProgetto == remRec.IdxProgetto)
                                  .FirstOrDefault();
                    if (dbRec != null)
                    {
                        // controllo se abbia progetti associati
                        var listFasi = dbCtx
                                        .DbSetAnagFasi
                                        .Where(x => x.IdxProgetto == remRec.IdxProgetto)
                                        .ToList();
                        // --> in tal caso disattivo...
                        if (listFasi != null && listFasi.Count > 0)
                        {
                            dbRec.Attivo = false;
                            //set modificato
                            dbCtx.Entry(dbRec).State = EntityState.Modified;
                        }
                        else
                        {
                            // altrimenti elimino davvero...
                            dbCtx
                                .DbSetAnagProgetti
                                .Remove(dbRec);
                        }
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagProjDelete:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Clona record Progetti con stored
        /// </summary>
        /// <param name="IdxProj"></param>
        /// <param name="NewName"></param>
        /// <param name="DoYearSubst"></param>
        /// <returns></returns>
        public bool AnagProjDoClone(int IdxProj, string NewName, bool DoYearSubst)
        {
            int result = 0;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                var pIdxProj = new SqlParameter("@idxProgetto", IdxProj);
                var pNomeProj = new SqlParameter("@nomeProj", NewName);
                var pSostAnnoFasi = new SqlParameter("@sostAnnoFasi", DoYearSubst);

                result = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_AP_duplicateProj @idxProgetto, @nomeProj, @sostAnnoFasi", pIdxProj, pNomeProj, pSostAnnoFasi);
            }
            return result != 0;
        }

        /// <summary>
        /// Esegue update calcolo dati proj x tutti (anche archiviati)
        /// </summary>
        /// <returns></returns>
        public bool AnagProjForceUpdate()
        {
            int result = 0;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                // imposto default x ricalcolo di tutti
                int IdxProj = 0;
                string gruppo = "";
                int IdxCli = 0;
                bool DoArch = true;
                var pIdxProj = new SqlParameter("@idxProgetto", IdxProj);
                var pGruppo = new SqlParameter("@gruppo", gruppo);
                var pIdxCli = new SqlParameter("@idxCliente", IdxCli);
                var pPrjArch = new SqlParameter("@PrjArch", DoArch);

                result = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_AP_Summary_Update @idxProgetto, @gruppo, @idxCliente, @PrjArch", pIdxProj, pGruppo, pIdxCli, pPrjArch);
            }
            return result != 0;
        }

        public bool AnagProjUpdStatus(int IdxProj, bool Attivo, bool Starred)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetAnagProgetti
                                    .Where(x => x.IdxProgetto == IdxProj)
                                    .FirstOrDefault();
                    if (dbRec != null)
                    {
                        dbRec.Attivo = Attivo;
                        dbRec.Starred = Starred;
                        //set modificato
                        dbCtx.Entry(dbRec).State = EntityState.Modified;
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagProjUpdStatus:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        public bool AnagProjUpsert(AnagProgettiModel upsRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetAnagProgetti
                                    .Where(x => x.IdxProgetto == upsRec.IdxProgetto)
                                    .FirstOrDefault();
                    if (dbRec == null)
                    {
                        dbCtx
                            .DbSetAnagProgetti
                            .Add(upsRec);
                    }
                    else
                    {
                        dbRec.Attivo = upsRec.Attivo;
                        dbRec.Avvio = upsRec.Avvio;
                        dbRec.Chiusura = upsRec.Chiusura;
                        dbRec.CodExt = upsRec.CodExt;
                        dbRec.DescrProj = upsRec.DescrProj;
                        dbRec.Gruppo = upsRec.Gruppo;
                        dbRec.IdxCliente = upsRec.IdxCliente;
                        dbRec.NomeProj = upsRec.NomeProj;
                        dbRec.OldIdx = upsRec.OldIdx;
                        dbRec.Starred = upsRec.Starred;
                        //set modificato
                        dbCtx.Entry(dbRec).State = EntityState.Modified;
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagProjUpsert:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Elenco Tags
        /// </summary>
        /// <returns></returns>
        public List<AnagTagModel> AnagTagAll()
        {
            // init dati necessari
            List<AnagTagModel> dbResult = new List<AnagTagModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetAnagTag
                                .OrderBy(x => x.Descrizione)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagTagAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Tags
        /// </summary>
        /// <returns></returns>
        public List<TagFasiDTO> AnagTagFasiAll()
        {
            // init dati necessari
            List<TagFasiDTO> dbResult = new List<TagFasiDTO>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                        .DbSetAnagTagFasiDTO
                                        .OrderBy(x => x.Descrizione)
                                        .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagTagFasiAllAsync:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public bool AnagTagFasiDelete(TagFasiDTO upsRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetAnagTagFasi
                                    .Where(x => x.CodTagFase == upsRec.CodTagFase)
                                    .FirstOrDefault();
                    if (dbRec != null)
                    {
                        dbCtx
                            .DbSetAnagTagFasi
                            .Remove(dbRec);
                        dbCtx.SaveChanges();
                        answ = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagTagFasiDelete:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        public bool AnagTagFasiUpsert(TagFasiDTO upsRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetAnagTagFasi
                                    .Where(x => x.CodTagFase == upsRec.CodTagFase)
                                    .FirstOrDefault();
                    if (dbRec == null)
                    {
                        // creo nuovo rec...
                        dbRec = new AnagTagFasiModel()
                        {
                            CodTagFase = upsRec.CodTagFase,
                            CodGruppo = upsRec.CodGruppo,
                            Descrizione = upsRec.Descrizione,
                            Enabled = upsRec.Enabled
                        };
                        dbCtx
                            .DbSetAnagTagFasi
                            .Add(dbRec);
                    }
                    else
                    {
                        dbRec.Descrizione = upsRec.Descrizione;
                        dbRec.CodGruppo = upsRec.CodGruppo;
                        dbRec.Enabled = upsRec.Enabled;
                        //dbRec.CodTagFase = upsRec.CodTagFase;
                        //set modificato
                        dbCtx.Entry(dbRec).State = EntityState.Modified;
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AnagTagFasiUpsert:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Vista dati per FASE con totalizzaizone ore budget/real
        /// </summary>
        /// <param name="idxFase"></param>
        /// <returns></returns>
        public CalcOreFasiModel CalcOreFase(int idxFase)
        {
            CalcOreFasiModel dbResult = new CalcOreFasiModel();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                var parIdxFase = new SqlParameter("@idxFaseAnc", idxFase);

                var rawResult = dbCtx
                    .DbSetCalcOreFasi
                    .FromSqlRaw("EXEC stp_AF_getByIdxFase_Child @idxFaseAnc", parIdxFase)
                    .ToList();
                if (rawResult != null && rawResult.Count > 0)
                {
                    dbResult = rawResult[0];
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Vista dati per progetto con totalizzaizone ore budget/real
        /// </summary>
        /// <param name="idxProj"></param>
        /// <returns></returns>
        public CalcOreProgettiModel CalcOreProj(int idxProj)
        {
            CalcOreProgettiModel dbResult = new CalcOreProgettiModel();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                var idxProgetto = new SqlParameter("@idxProgetto", idxProj);

                var rawResult = dbCtx
                    .DbSetCalcOreProj
                    .FromSqlRaw("EXEC stp_AP_getByIdxPrj @idxProgetto", idxProgetto)
                    .ToList();
                if (rawResult != null && rawResult.Count > 0)
                {
                    dbResult = rawResult[0];
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Lista chiusure aziendali dato periodo
        /// </summary>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public List<CalFesteFerieModel> CalFestFeriePeriodo(DateTime dtStart, DateTime dtEnd)
        {
            List<CalFesteFerieModel> dbResult = new List<CalFesteFerieModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetCalFesteFerie
                            .Where(x => x.data >= dtStart && x.data <= dtEnd)
                            .OrderBy(x => x.data)
                            .ToList();
            }
            return dbResult;
        }

        public bool CalFestFerieUpsert(CalFesteFerieModel upsRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetCalFesteFerie
                                    .Where(x => x.data == upsRec.data)
                                    .FirstOrDefault();
                    if (dbRec == null)
                    {
                        dbCtx
                            .DbSetCalFesteFerie
                            .Add(upsRec);
                    }
                    else
                    {
                        dbRec.codGiust = upsRec.codGiust;
                        dbRec.descrizione = upsRec.descrizione;
                        //set modificato
                        dbCtx.Entry(dbRec).State = EntityState.Modified;
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in CalFestFerieUpsert:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupera l'elenco dei Rilievi temperatura nel periodo indicato x dipendente
        /// </summary>
        /// <param name="idxDipendente">Dipendente interessato</param>
        /// <param name="dtInizio">Data di riferimento (ultima/corrente)</param>
        /// <param name="dtFine">NUm settimane precedenti da recuperare</param>
        /// <returns></returns>
        public List<CheckVc19Model> CheckVC19List(int idxDipendente, DateTime dtInizio, DateTime dtFine)
        {
            // init dati necessari
            List<CheckVc19Model> dbResult = new List<CheckVc19Model>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                dbResult = dbCtx
                                .DbSetCheckVc19
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DtCheck && x.DtCheck <= dtFine)
                                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Lista configurazione
        /// </summary>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public List<ConfigModel> ConfigGetAll()
        {
            List<ConfigModel> dbResult = new List<ConfigModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetConfig
                            .OrderBy(x => x.chiave)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera l'elenco dei dettaglio giornaliero attività di tutti i dipendenti, dato giorno riferimento
        /// </summary>
        /// <param name="dtRif">Data di riferimento</param>
        /// <returns></returns>
        public List<DailyDataDTO> DailyDetailAllDip(DateTime dtRif)
        {
            //resetto valoredtRif a giornata (mezzanotte)
            dtRif = dtRif.Date;
            // init dati necessari
            List<DailyDataDTO> dbResult = new List<DailyDataDTO>();
            List<TimbratureExplModel> rawTimbExp = new List<TimbratureExplModel>();
            List<TimbratureModel> rawTimbr = new List<TimbratureModel>();
            List<RegAttivitaModel> rawRegAtt = new List<RegAttivitaModel>();
            List<RilievoTempModel> rawRilTemp = new List<RilievoTempModel>();
            List<CheckVc19Model> rawCheckC19 = new List<CheckVc19Model>();

            // fermate/festività/ferie
            List<CalFesteFerieModel> rawFermateAz = new List<CalFesteFerieModel>();
            List<RegMalattieModel> rawMalattie = new List<RegMalattieModel>();
            List<RegRichiesteModel> rawRichDip = new List<RegRichiesteModel>();

            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                // recupero intero set dati timbrature e attività....
                rawTimbExp = dbCtx
                                .DbSetTimbratureExpl
                                .Where(x => dtRif == x.DataLav)
                                .ToList();

                rawTimbr = dbCtx
                                .DbSetTimbrature
                                .Where(x => dtRif == x.DataOra.Date)
                                .ToList();

                rawRegAtt = dbCtx
                                .DbSetRegAttivita
                                .Where(x => dtRif == x.Inizio.Date)
                                .Include(a => a.FasiNav).ThenInclude(p => p.ProgettoNav).ThenInclude(c => c.ClienteNav)
                                .ToList();

                rawRilTemp = dbCtx
                                .DbSetRilievoTemp
                                .Where(x => dtRif <= x.DtRilievo.Date)
                                .ToList();

                rawCheckC19 = dbCtx
                                .DbSetCheckVc19
                                .Where(x => dtRif <= x.DtCheck.Date)
                                .ToList();

                // recupero fermate e malattie/permessi del dipendente...
                rawFermateAz = dbCtx
                                .DbSetCalFesteFerie
                                .Where(x => x.data == dtRif)
                                .ToList();
                rawMalattie = dbCtx
                               .DbSetRegMalattie
                               .Where(x => x.DtInizio.Date <= dtRif && x.DtInizio.AddDays(x.NumGG) >= dtRif)
                               .ToList();
                rawRichDip = dbCtx
                               .DbSetRegRichieste
                               .Where(x => x.DtStart.Date <= dtRif && x.DtEnd >= dtRif)
                               .ToList();

                // calcolo elenco DIP
                List<int> listDip = rawTimbExp.Select(x => x.IdxDipendente).Distinct().ToList();

                // x ogni giorno scompongo il set di info...
                foreach (var idxDip in listDip)
                {
                    DailyDataDTO currDayDto = new DailyDataDTO()
                    {
                        IdxDipendente = idxDip,
                        DtRif = dtRif,
                        ListRA = rawRegAtt
                                .Where(x => x.IdxDipendente == idxDip)
                                .OrderBy(x => x.Inizio)
                                .ToList(),
                        ListTimbr = rawTimbr
                                .Where(x => x.IdxDipendente == idxDip)
                                .OrderBy(x => x.DataOra)
                                .ToList(),
                        TimbrExpl = rawTimbExp
                                .Where(x => x.IdxDipendente == idxDip)
                                .FirstOrDefault(),
                        ListRilTemp = rawRilTemp
                                .Where(x => x.IdxDipendente == idxDip)
                                .ToList(),
                        ListCheckC19 = rawCheckC19
                                .Where(x => x.IdxDipendente == idxDip)
                                .ToList(),
                        ListFermateAzienda = rawFermateAz,
                        ListMalattie = rawMalattie
                                .Where(x => x.IdxDipendente == idxDip)
                                .ToList(),
                        ListFerieDip = rawRichDip
                                .Where(x => x.IdxDipendente == idxDip)
                                .ToList(),
                        ListRichiesteDip = rawRichDip
                                .Where(x => x.IdxDipendente == idxDip)
                                .ToList()
                    };

                    dbResult.Add(currDayDto);
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera l'elenco dei dettagli giornalieri attività di un dipendente, dato periodo riferimento
        /// </summary>
        /// <param name="idxDipendente">Dipendente interessato</param>
        /// <param name="dtInizio">Data di riferimento (ultima/corrente)</param>
        /// <param name="dtFine">NUm settimane precedenti da recuperare</param>
        /// <returns></returns>
        public List<DailyDataDTO> DailyDetails(int idxDipendente, DateTime dtInizio, DateTime dtFine)
        {
            // init dati necessari
            List<DailyDataDTO> dbResult = new List<DailyDataDTO>();
            List<TimbratureExplModel> rawTimbExp = new List<TimbratureExplModel>();
            List<TimbratureModel> rawTimbr = new List<TimbratureModel>();
            List<RegAttivitaModel> rawRegAtt = new List<RegAttivitaModel>();
            List<RilievoTempModel> rawRilTemp = new List<RilievoTempModel>();
            List<CheckVc19Model> rawCheckC19 = new List<CheckVc19Model>();

            // fermate/festività/ferie
            List<CalFesteFerieModel> rawFermateAz = new List<CalFesteFerieModel>();
            List<RegMalattieModel> rawMalattie = new List<RegMalattieModel>();
            List<RegRichiesteModel> rawRichDip = new List<RegRichiesteModel>();

            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                // recupero intero set dati timbrature e attività....
                rawTimbExp = dbCtx
                                .DbSetTimbratureExpl
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DataLav && x.DataLav <= dtFine)
                                .ToList();

                rawTimbr = dbCtx
                                .DbSetTimbrature
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DataOra && x.DataOra <= dtFine.AddDays(1))
                                .ToList();

                rawRegAtt = dbCtx
                                .DbSetRegAttivita
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.Inizio && x.Inizio <= dtFine.AddDays(1))
                                .Include(a => a.FasiNav).ThenInclude(p => p.ProgettoNav).ThenInclude(c => c.ClienteNav)
                                .ToList();

                rawRilTemp = dbCtx
                                .DbSetRilievoTemp
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DtRilievo && x.DtRilievo <= dtFine.AddDays(1))
                                .ToList();

                rawCheckC19 = dbCtx
                                .DbSetCheckVc19
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DtCheck && x.DtCheck <= dtFine.AddDays(1))
                                .ToList();

                // recupero fermate e malattie/permessi del dipendente...
                rawFermateAz = dbCtx
                                .DbSetCalFesteFerie
                                .Where(x => x.data >= dtInizio && x.data <= dtFine)
                                .ToList();
                rawMalattie = dbCtx
                               .DbSetRegMalattie
                               .Where(x => (x.DtInizio >= dtInizio && x.DtInizio <= dtFine) || (x.DtInizio.AddDays(x.NumGG) >= dtInizio.AddDays(x.NumGG) && x.DtInizio <= dtFine))
                               .ToList();
                rawRichDip = dbCtx
                               .DbSetRegRichieste
                               .Where(x => (idxDipendente == 0 || x.IdxDipendente == idxDipendente) && ((x.DtStart >= dtInizio && x.DtStart <= dtFine) || (x.DtStart <= dtInizio && dtFine <= x.DtEnd) || (x.DtEnd >= dtInizio && x.DtEnd <= dtFine)))
                               .ToList();

                // calcolo intervallo date...
                int numDays = dtFine.Subtract(dtInizio).Days;

                // x ogni giorno scompongo il set di info...
                for (int i = 0; i <= numDays; i++)
                {
                    DateTime thisDay = dtInizio.AddDays(i);
                    DailyDataDTO currDayDto = new DailyDataDTO()
                    {
                        IdxDipendente = idxDipendente,
                        DtRif = thisDay,
                        ListRA = rawRegAtt
                                .Where(x => thisDay <= x.Inizio && x.Inizio < thisDay.AddDays(1))
                                .OrderBy(x => x.Inizio)
                                .ToList(),
                        ListTimbr = rawTimbr
                                .Where(x => thisDay <= x.DataOra && x.DataOra < thisDay.AddDays(1))
                                .OrderBy(x => x.DataOra)
                                .ToList(),
                        TimbrExpl = rawTimbExp
                                .Where(x => thisDay == x.DataLav)
                                .FirstOrDefault(),
                        ListRilTemp = rawRilTemp
                                .Where(x => thisDay <= x.DtRilievo && x.DtRilievo < thisDay.AddDays(1))
                                .ToList(),
                        ListCheckC19 = rawCheckC19
                                .Where(x => thisDay <= x.DtCheck && x.DtCheck < thisDay.AddDays(1))
                                .ToList(),
                        ListFermateAzienda = rawFermateAz
                                .Where(x => thisDay == x.data)
                                .ToList(),
                        ListMalattie = rawMalattie
                                .Where(x => thisDay >= x.DtInizio && thisDay <= x.DtInizio.AddDays(x.NumGG))
                                .ToList(),
                        ListFerieDip = rawRichDip
                                .Where(x => x.CodGiust == "FER" && (thisDay >= x.DtStart && thisDay <= x.DtEnd))
                                .ToList(),
                        ListRichiesteDip = rawRichDip
                                .Where(x => x.CodGiust != "FER" && (thisDay.Date == x.DtStart.Date))
                                .ToList()
                    };

                    dbResult.Add(currDayDto);
                }
            }
            return dbResult;
        }

        public bool DbForceMigrate()
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    dbCtx.DbForceMigrate();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in DbForceMigrate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupera record anomalie dipendenti
        /// </summary>
        /// <param name="inizio"></param>
        /// <param name="fine"></param>
        /// <param name="notOkApp"></param>
        /// <param name="notOkTim"></param>
        /// <param name="notOkLav"></param>
        /// <returns></returns>
        public List<DipendendiAndAnomalie> DipAndAnomGetAll(DateTime inizio, DateTime fine, bool notOkApp, bool notOkTim, bool notOkLav)
        {
            List<DipendendiAndAnomalie> dbResult = new List<DipendendiAndAnomalie>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                SqlParameter parInizio = new SqlParameter("@inizio", inizio);
                SqlParameter parFine = new SqlParameter("@pCodCert", fine);
                SqlParameter parNotOkApp = new SqlParameter("@notOkApp", notOkApp);
                SqlParameter parNotOkTim = new SqlParameter("@notOkTim", notOkTim);
                SqlParameter parNotOkLav = new SqlParameter("@notOkLav", notOkLav);

                var rawResult = dbCtx
                                .DbSetDipAndAnom
                                .FromSqlRaw("EXEC stp_DipendentiAndAnomalie @inizio,@pCodCert,@notOkApp,@notOkTim,@notOkLav", parInizio, parFine, parNotOkApp, parNotOkTim, parNotOkLav)
                                .ToList();
                if (rawResult == null)
                {
                    dbResult = new List<DipendendiAndAnomalie>();
                }
                else
                {
                    dbResult = rawResult;
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Clona assegnazione gruppi dipendente
        /// </summary>
        /// <param name="idxDipFrom"></param>
        /// <param name="idxDipTo"></param>
        /// <returns></returns>
        public bool Dipendenti2GrpClona(int idxDipFrom, int idxDipTo)
        {
            int result = 0;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxFrom = new SqlParameter("@idxDipFrom", idxDipFrom);
                    var pIdxTo = new SqlParameter("@idxDipTo", idxDipTo);

                    result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_Dip2Gruppi_clone @idxDipFrom, @idxDipTo", pIdxFrom, pIdxTo);
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in Dipendenti2GrpClona:{Environment.NewLine}{exc}");
                }
            }
            return result != 0;
        }

        /// <summary>
        /// Elimina dip 2 gruppo
        /// </summary>
        /// <param name="IdxDip"></param>
        /// <param name="CodGruppo"></param>
        /// <returns></returns>
        public bool Dipendenti2GrpDelete(int idxDip, string codGruppo)
        {
            bool fatto = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                var dbRec = dbCtx
                                .DbSetDipendenti2Gruppi
                                .Where(x => x.IdxDipendente == idxDip && x.Gruppo == codGruppo)
                                .FirstOrDefault();
                if (dbRec != null)
                {
                    dbCtx.DbSetDipendenti2Gruppi.Remove(dbRec);
                    dbCtx.SaveChanges();
                }
            }
            return fatto;
        }

        /// <summary>
        /// Aggiunge dip a gruppo
        /// </summary>
        /// <param name="IdxDip"></param>
        /// <param name="CodGruppo"></param>
        /// <returns></returns>
        public bool Dipendenti2GrpInsert(int idxDip, string codGruppo)
        {
            bool fatto = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                var dbRec = dbCtx
                                .DbSetDipendenti2Gruppi
                                .Where(x => x.IdxDipendente == idxDip && x.Gruppo == codGruppo)
                                .FirstOrDefault();
                if (dbRec == null)
                {
                    Dipendenti2GruppiModel newRec = new Dipendenti2GruppiModel()
                    {
                        IdxDipendente = idxDip,
                        Gruppo = codGruppo
                    };
                    dbCtx.DbSetDipendenti2Gruppi.Add(newRec);
                    dbCtx.SaveChanges();
                }
            }
            return fatto;
        }

        public List<Dipendenti2RuoliModel> Dipendenti2RuoliGetAll()
        {
            List<Dipendenti2RuoliModel> dbResult = new List<Dipendenti2RuoliModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetDip2Ruoli
                            .Include(o => o.DipNav)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco dip filtrati x gruppo
        /// </summary>
        /// <param name="Gruppo"></param>
        /// <returns></returns>
        public List<DipendentiModel> DipendentiByGrp(string Gruppo)
        {
            List<DipendentiModel> dbResult = new List<DipendentiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetDipendenti2Gruppi
                                .Where(x => x.Gruppo == Gruppo || string.IsNullOrEmpty(Gruppo))
                                .Select(x => x.DipNav)
                                .Distinct()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in DipendentiByGrp:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public List<DipendentiModel> DipendentiGetAll()
        {
            List<DipendentiModel> dbResult = new List<DipendentiModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                dbResult = dbCtx
                    .DbSetDipendenti
                    .Include(o => o.OrarioNav)
                    .ToList();
            }
            return dbResult;
        }

        public bool DipendentiUpdate(DipendentiModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    if (currItem.IdxDipendente > 0)
                    {
                        var currRec = dbCtx
                                        .DbSetDipendenti
                                        .FirstOrDefault(x => x.IdxDipendente == currItem.IdxDipendente);
                        if (currRec != null)
                        {
                            currRec.Cognome = currItem.Cognome;
                            currRec.Nome = currItem.Nome;
                            currRec.AuthKey = currItem.AuthKey;
                            currRec.Cf = currItem.Cf;
                            currRec.Email = currItem.Email;
                            currRec.DataNascita = currItem.DataNascita;
                            currRec.LuogoNascita = currItem.LuogoNascita;

                            dbCtx
                                .DbSetDipendenti
                                .Update(currRec);
                        }
                        // altrimenti aggiungo
                        else
                        {
                            dbCtx
                                .DbSetDipendenti
                                .Update(currItem);
                        }
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in DipendentiUpdate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Ricalcola Tag Mensili dato dipendente, periodo, tag
        /// </summary>
        /// <param name="idxDipendente">IdxDipendente</param>
        /// <param name="attivo">Stato attivo richiesto</param>
        /// <returns></returns>
        public bool DipUpdateActive(int idxDipendente, bool attivo)
        {
            // init dati necessari
            bool fatto = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pAttivo = new SqlParameter("@attivo", attivo);
                    var pIdxDip = new SqlParameter("@Original_idxDipendente", idxDipendente);

                    var dbResult = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC dbo.stp_AD_updateActive @attivo, @Original_idxDipendente", pAttivo, pIdxDip);
                    fatto = dbResult > 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in DipUpdateActive:{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        public void Dispose()
        {
            Log.Info("Dispose di GPWController");
        }

        /// <summary>
        /// Recupera Elenco Report x mostrare link
        /// </summary>
        /// <param name="Area">Se vuoto = tutti</param>
        /// <returns></returns>
        public List<ElencoReportModel> ElencoReportFilt(string Area)
        {
            List<ElencoReportModel> dbResult = new List<ElencoReportModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = dbCtx
                            .DbSetElencoReport
                            .Where(x => string.IsNullOrEmpty(Area) || x.Area == Area)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Esegue recupero dati in formato ExportCommessa
        /// </summary>
        /// <param name="idxDipendente"></param>
        /// <param name="dtInizio"></param>
        /// <param name="dtFine"></param>
        /// <returns></returns>
        public List<ExpCommessaModel> ExportCommessa(int idxDipendente, DateTime dtInizio, DateTime dtFine)
        {
            List<ExpCommessaModel> dbResult = new List<ExpCommessaModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                SqlParameter pIdxDip = new SqlParameter("@idxRA", idxDipendente);
                SqlParameter pInizio = new SqlParameter("@dataFrom", dtInizio);
                SqlParameter pFine = new SqlParameter("@dataTo", dtFine);

                try
                {
                    dbResult = dbCtx
                                    .DbSetExpCommessa
                                    .FromSqlRaw("EXEC stp_RA_pivot_getByIdxDipData @idxRA, @dataFrom, @dataTo", pIdxDip, pInizio, pFine)
                                    .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ExportCommessa{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Esegue recupero dati in formato ExportZucchetti
        /// </summary>
        /// <param name="idxDipendente"></param>
        /// <param name="dtInizio"></param>
        /// <param name="dtFine"></param>
        /// <param name="Timbratrice"></param>
        /// <returns></returns>
        public List<ExpZucchettiModel> ExportZucchetti(int idxDipendente, DateTime dtInizio, DateTime dtFine, string Timbratrice)
        {
            List<ExpZucchettiModel> dbResult = new List<ExpZucchettiModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                SqlParameter pInizio = new SqlParameter("@dataFrom", dtInizio);
                SqlParameter pFine = new SqlParameter("@dataTo", dtFine);
                SqlParameter pIdxDip = new SqlParameter("@Dipendente", idxDipendente);
                SqlParameter pTimbra = new SqlParameter("@Timbratrice", Timbratrice);

                try
                {
                    dbResult = dbCtx
                                    .DbSetExpZucchetti
                                    .FromSqlRaw("EXEC export.stp_TimbratureZucchetti @dataFrom, @dataTo, @Dipendente, @Timbratrice", pInizio, pFine, pIdxDip, pTimbra)
                                    .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ExportZucchetti{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Imposta stato di una fase
        /// </summary>
        /// <param name="idxFase">ID</param>
        /// <param name="isActive">
        /// Stato attivo true/false (se è amster -_&gt; aggiorna sottofasi x trigger su DB
        /// </param>
        /// <returns></returns>
        public async Task<bool> FasiSetActive(int idxFase, bool isActive)
        {
            // init dati necessari
            bool fatto = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    var dbResult = dbCtx
                                  .DbSetAnagFasi
                                  .Where(x => x.IdxFase == idxFase)
                                  .FirstOrDefault();
                    if (dbResult != null)
                    {
                        dbResult.Attivo = isActive;
                        await dbCtx.SaveChangesAsync();
                        fatto = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in FasiSetActive:{Environment.NewLine}{exc}");
                }
            }
            await Task.Delay(1);
            return fatto;
        }

        public List<CheckVc19Model> GetChecksVC19(int maxNum)
        {
            List<CheckVc19Model> dbResult = new List<CheckVc19Model>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                int numRec = dbCtx
                .DbSetCheckVc19
                .Count();

                // se ho meno record che quelli richiesti --> riduco
                maxNum = numRec > maxNum ? maxNum : numRec;

                dbResult = dbCtx
                .DbSetCheckVc19
                .OrderByDescending(x => x.DtCheck)
                .Take(maxNum)
                .ToList();
            }
            return dbResult;
        }

        public List<CheckVc19Model> GetChecksVC19Filt(int idxDip, DateTime dtStart, DateTime dtEnd)
        {
            List<CheckVc19Model> dbResult = new List<CheckVc19Model>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = dbCtx
                .DbSetCheckVc19
                .Where(x => (idxDip == 0 || x.IdxDipendente == idxDip) && (x.DtCheck >= dtStart && x.DtCheck <= dtEnd))
                .OrderByDescending(x => x.DtCheck)
                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Eliminazione record giustificativo
        /// </summary>
        /// <param name="upsRec"></param>
        /// <returns></returns>
        public bool GiustificativiDelete(GiustificativiModel upsRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetGiustificativi
                                    .Where(x => x.IdxDipendente == upsRec.IdxDipendente
                                            && x.DataLav == upsRec.DataLav
                                            && x.CodGiust == upsRec.CodGiust)
                                    .FirstOrDefault();
                    if (dbRec != null)
                    {
                        dbCtx
                            .DbSetGiustificativi
                            .Remove(dbRec);
                        dbCtx.SaveChanges();
                        answ = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in GiustificativiDelete:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Elenco Giustificativi filtrati x dip
        /// </summary>
        /// <param name="IdxDip"></param>
        /// <param name="DtFrom"></param>
        /// <param name="DtTo"></param>
        /// <returns></returns>
        public List<GiustificativiModel> GiustificativiFilt(int IdxDip, DateTime DtFrom, DateTime DtTo)
        {
            // init dati necessari
            List<GiustificativiModel> dbResult = new List<GiustificativiModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero intero set...
                    dbResult = dbCtx
                                .DbSetGiustificativi
                                .Where(x => x.IdxDipendente == IdxDip && (DtFrom <= x.DataLav && x.DataLav <= DtTo))
                                .Include(p => p.DipNav)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in GiustificativiFilt:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Chiama stored inserimento giust a copertura compelta x la data + utente indicati
        /// </summary>
        /// <param name="idxDip"></param>
        /// <param name="DtRif"></param>
        /// <param name="CodGiust"></param>
        /// <returns></returns>
        public bool GiustificativiInsByDate(int idxDip, DateTime DtRif, string CodGiust)
        {
            int result = 0;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                var idxDipendente = new SqlParameter("@IdxDip", idxDip);
                var pDateRif = new SqlParameter("@dataRif", DtRif);
                var pCodGiust = new SqlParameter("@codGiust", CodGiust);

                result = dbCtx
                            .Database
                            .ExecuteSqlRaw("EXEC stp_giust_insByDate @IdxDip, @dataRif, @codGiust", idxDipendente, pDateRif, pCodGiust);
            }
            return result != 0;
        }

        /// <summary>
        /// Update record giustificativo
        /// </summary>
        /// <param name="oldRec">Vecchio record (x poter cambiare chaive elimina)</param>
        /// <param name="upsRec">Nuovo record</param>
        /// <returns></returns>
        public bool GiustificativiUpdate(GiustificativiModel oldRec, GiustificativiModel upsRec)
        {
            // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                    .DbSetGiustificativi
                                    .Where(x => x.IdxDipendente == oldRec.IdxDipendente
                                            && x.DataLav == oldRec.DataLav
                                            && x.CodGiust == oldRec.CodGiust
                                            )
                                    .FirstOrDefault();
                    if (dbRec != null)
                    {
                        // elimino vecchio
                        dbCtx
                                .DbSetGiustificativi
                                .Remove(dbRec);

                        dbCtx.SaveChanges();

                        // tolgo proprietà di navigazione x evitare errore insert...
                        upsRec.DipNav = null;

                        // aggiungo nuovo
                        dbCtx
                                .DbSetGiustificativi
                                .Add(upsRec);

                        // salvo
                        dbCtx.SaveChanges();
                        answ = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in GiustificativiUpdate:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        public bool InsertCheck(DCCDecode updItem, string clientIp)
        {
            bool done = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    int idxDip = 0;
                    DipendentiModel? currUser = new DipendentiModel()
                    {
                        IdxDipendente = 0
                    };
                    // per prima cosa cerca il dipendente x cognome/nome...
                    var userList = dbCtx
                                        .DbSetDipendenti
                                        .Where(x => x.Cognome.ToUpper() == updItem.nam.fn.ToUpper() && x.Nome.ToUpper() == updItem.nam.gn.ToUpper())
                                        .ToList();
                    // se ne ho solo 1 ok...
                    if (userList.Count > 1)
                    {
                        currUser = userList
                                       .Where(x => x.DataNascita != null && updItem.dob.Date == ((DateTime)x.DataNascita).Date)
                                       .FirstOrDefault();
                    }
                    // altrimenti cerco anche con DOB...
                    else if (userList.Count == 1)
                    {
                        // se lo trova inserisce check...
                        currUser = userList[0];
                    }

                    if (currUser != null)
                    {
                        // fisso dipendente
                        idxDip = currUser.IdxDipendente;
                    }

                    string rawPayload = JsonConvert.SerializeObject(updItem);
                    CheckVc19Model newItem = new CheckVc19Model()
                    {
                        IdxDipendente = idxDip,
                        DtCheck = DateTime.Now,
                        Cognome = updItem.nam.fn.ToUpper(),
                        Nome = updItem.nam.gn.ToUpper(),
                        Payload = rawPayload,
                        Dob = updItem.dob.Date
                    };
                    // aggiungo!
                    dbCtx
                        .DbSetCheckVc19
                        .Add(newItem);

                    // ora salvo!
                    dbCtx.SaveChanges();

                    // se trovato procedo ANCHE x timbratura
                    if (idxDip > 0)
                    {
                        // verifico se ho timbratura ingresso
                        var listTimbra = dbCtx
                                            .DbSetTimbrature
                                            .Where(x => x.IdxDipendente == idxDip && x.DataOra.Date == DateTime.Today && x.Entrata == true)
                                            .ToList();
                        // se non ci fosse aggiungo
                        if (listTimbra == null || listTimbra.Count == 0)
                        {
                            var newIngresso = new TimbratureModel()
                            {
                                IdxDipendente = idxDip,
                                DataOra = DateTime.Now,
                                CodTipoTimb = "Web",
                                Ipv4 = clientIp,
                                Entrata = true,
                                Approv = true
                            };

                            // aggiungo!
                            dbCtx
                                .DbSetTimbrature
                                .Add(newIngresso);
                        }
                        // ora salvo!
                        dbCtx.SaveChanges();
                        done = true;
                    }
                    // altrimenti NON FA NULLA
                    else
                    {
                        Log.Info($"Attenzione, dipendente non trovato: {updItem.nam.fn} {updItem.nam.gn}");
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in InsertCheck:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        public bool InsertManual(int idxDip, string clientIp)
        {
            bool done = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // se trovato procedo
                    if (idxDip > 0)
                    {
                        string rawPayload = "MANUAL";
                        CheckVc19Model newItem = new CheckVc19Model()
                        {
                            IdxDipendente = idxDip,
                            DtCheck = DateTime.Now,
                            Payload = rawPayload
                        };
                        // aggiungo!
                        dbCtx
                            .DbSetCheckVc19
                            .Add(newItem);

                        // ora salvo!
                        dbCtx.SaveChanges();
                        done = true;
                    }
                    // altrimenti NON FA NULLA
                    else
                    {
                        Log.Info($"Attenzione, dipendente non trovato: {idxDip}");
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in InsertManual:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Recupero dati dettaglio tag da stored dedicata
        /// </summary>
        /// <param name="idxDipendente"></param>
        /// <param name="dataFrom"></param>
        /// <param name="dataTo"></param>
        /// <returns></returns>
        public List<ListTagDDModel> ListTagDD(int idxDipendente, DateTime dataFrom, DateTime dataTo)
        {
            // init dati necessari
            List<ListTagDDModel> dbResult = new List<ListTagDDModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pidxDipendente = new SqlParameter("@idxRA", idxDipendente);
                    var pdataFrom = new SqlParameter("@dataFrom", dataFrom);
                    var pdataTo = new SqlParameter("@dataTo", dataTo);

                    dbResult = dbCtx
                                .DbSetListTagDD
                                .FromSqlRaw("EXEC dbo.stp_LTDD_ByUserDate @idxRA, @dataFrom, @dataTo", pidxDipendente, pdataFrom, pdataTo)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in ListTagDD:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Toggle attiazione record da stored dedicata
        /// </summary>
        /// <param name="idxTagDD"></param>
        /// <returns></returns>
        public bool ListTagDDToggle(int idxTagDD)
        {
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxTagDD = new SqlParameter("@IdxTagDD", idxTagDD);

                    var dbResult = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC dbo.stp_LTDD_toggleActive @IdxTagDD", pIdxTagDD);
                    answ = dbResult > 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in ListTagDDToggle:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupero dati month tag da stored dedicata
        /// </summary>
        /// <param name="idxDipendente"></param>
        /// <param name="dataFrom"></param>
        /// <param name="dataTo"></param>
        /// <returns></returns>
        public List<MonthTagModel> MonthTagList(int idxDipendente, DateTime dataFrom, DateTime dataTo)
        {
            // init dati necessari
            List<MonthTagModel> dbResult = new List<MonthTagModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pidxDipendente = new SqlParameter("@idxDipendente", idxDipendente);
                    var pdataFrom = new SqlParameter("@dataFrom", dataFrom);
                    var pdataTo = new SqlParameter("@dataTo", dataTo);

                    dbResult = dbCtx
                                .DbSetMonthTag
                                .FromSqlRaw("EXEC dbo.stp_LTM_ByUserDate @idxDipendente, @dataFrom, @dataTo", pidxDipendente, pdataFrom, pdataTo)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in MonthTagList:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Ricalcola Tag Mensili dato dipendente, periodo, tag
        /// </summary>
        /// <param name="idxDipendente">IdxDipendente</param>
        /// <param name="dataFrom">Inizio periodo ricalcolo</param>
        /// <param name="dataTo">Fine periodo ricalcolo</param>
        /// <param name="codTag">Cod TAG da ricalcolare</param>
        /// <param name="minParam">Valore minimo parametri</param>
        /// <returns></returns>
        public bool MonthTagRecalc(int idxDipendente, DateTime dataFrom, DateTime dataTo, string codTag, int minParam)
        {
            // init dati necessari
            bool fatto = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pCodTag = new SqlParameter("@CodTag", codTag);
                    var pIdxDipendente = new SqlParameter("@idxDipendente", idxDipendente);
                    var pDataFrom = new SqlParameter("@DtStart", dataFrom);
                    var pDataTo = new SqlParameter("@DtEnd", dataTo);
                    var pMinParam = new SqlParameter("@minParam", minParam);

                    var dbResult = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC dbo.stp_LTDD_checkCreateByDipDate @CodTag, @idxDipendente, @DtStart, @DtEnd, @minParam", pCodTag, pIdxDipendente, pDataFrom, pDataTo, pMinParam);
                    fatto = dbResult > 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in MonthTagRecalc:{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Elenco pareto progetti ordinati da filtro
        /// </summary>
        /// <param name="idxDip"></param>
        /// <param name="dataStart"></param>
        /// <param name="dataEnd"></param>
        /// <param name="maxResult"></param>
        /// <returns></returns>
        public List<ParetoRegAttModel> ParetoRegAtt(int idxDip, DateTime dataStart, DateTime dataEnd, int maxResult)
        {
            List<ParetoRegAttModel> dbResult = new List<ParetoRegAttModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                var idxDipendente = new SqlParameter("@IdxDip", idxDip);
                var inizio = new SqlParameter("@inizio", dataStart);
                var fine = new SqlParameter("@pCodCert", dataEnd);
                var maxRes = new SqlParameter("@maxRes", maxResult);

                dbResult = dbCtx
                    .DbSetParetoRegAtt
                    .FromSqlRaw("EXEC stp_freqProjByDipPeriodo @IdxDip, @inizio,@pCodCert,@maxRes", idxDipendente, inizio, fine, maxRes)
                    .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Righe presenze giornaliere
        /// </summary>
        /// <param name="dtRif">DataOra riferimento per richiesta presenze</param>
        /// <returns></returns>
        public List<StatsDayPresModel> PresenzeDay(DateTime dtRif)
        {
            List<StatsDayPresModel> dbResult = new List<StatsDayPresModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                var idxProgetto = new SqlParameter("@dataRif", dtRif);

                dbResult = dbCtx
                            .DbSetStatsDayPres
                            .FromSqlRaw("EXEC stp_statsDayPres @dataRif", idxProgetto)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero dati delle attività filtrate x spostamento ore tra progetti
        /// </summary>
        /// <param name="idxDipendente"></param>
        /// <param name="dataFrom"></param>
        /// <param name="dataTo"></param>
        /// <param name="idxCliente"></param>
        /// <param name="idxProgetto"></param>
        /// <param name="idxFase"></param>
        /// <param name="isAncest"></param>
        /// <returns></returns>
        public List<RegAttivitaDayExplModel> RegAttDayExplList(int idxDipendente, DateTime dataFrom, DateTime dataTo, int idxCliente, int idxProgetto, int idxFase, bool isAncest)
        {
            // init dati necessari
            List<RegAttivitaDayExplModel> dbResult = new List<RegAttivitaDayExplModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxDipendente = new SqlParameter("@idxRA", idxDipendente);
                    var pInizio = new SqlParameter("@dataFrom", dataFrom);
                    var pFine = new SqlParameter("@dataTo", dataTo);
                    var pIdxCliente = new SqlParameter("@idxCliente", idxCliente);
                    var pIdxProgetto = new SqlParameter("@idxProgetto", idxProgetto);
                    var pIdxFase = new SqlParameter("@idxFase", idxFase);
                    var pIsAncest = new SqlParameter("@soloAncest", isAncest);

                    dbResult = dbCtx
                                .DbSetDayRaExpl
                                .FromSqlRaw("EXEC dbo.stp_RAD_Expl_getByIdxDipPeriodo @idxRA, @dataFrom, @dataTo, @idxCliente, @idxProgetto, @idxFase, @soloAncest", pIdxDipendente, pInizio, pFine, pIdxCliente, pIdxProgetto, pIdxFase, pIsAncest)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in RegAttDayExplList:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public bool RegAttDelete(RegAttivitaModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    dbCtx.Remove(currItem);

                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegAttUpdate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupera l'elenco delle attività di un dipendente in un giorno
        /// </summary>
        /// <param name="idxDipendente">Dipendente interessato</param>
        /// <param name="DtRif">Data eventi da recuperare</param>
        /// <returns></returns>
        public List<RegAttivitaModel> RegAttDipDate(int idxDipendente, DateTime DtRif)
        {
            List<RegAttivitaModel> listRec = new List<RegAttivitaModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                listRec = dbCtx
                                .DbSetRegAttivita
                                .Where(x => x.IdxDipendente == idxDipendente && x.Inizio.Date == DtRif.Date)
                                .Include(a => a.FasiNav)
                                .ThenInclude(p => p.ProgettoNav)
                                .ThenInclude(c => c.ClienteNav)
                                .OrderByDescending(x => x.IdxRa)
                                .AsNoTracking()
                                .ToList();
            }
            return listRec;
        }

        /// <summary>
        /// Recupera ultima attività dipendente
        /// </summary>
        /// <param name="IdxDipendente"></param>
        /// <param name="onlyActive">cerca ultima solo tra attive (=true) o tutte (=false)</param>
        /// <returns></returns>
        public RegAttivitaModel RegAttLastByDip(int IdxDipendente, bool onlyActive)
        {
            RegAttivitaModel? dbResult = new RegAttivitaModel();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    dbResult = dbCtx
                            .DbSetRegAttivita
                            .Where(x => x.FasiNav.Attivo || !onlyActive)
                            .OrderByDescending(x => x.Inizio)
                            .FirstOrDefault(x => x.IdxDipendente == IdxDipendente);
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegAttLastByDip{Environment.NewLine}{exc}");
                }
                if (dbResult == null)
                {
                    dbResult = new RegAttivitaModel();
                }
            }
            if (dbResult == null)
            {
                dbResult = new RegAttivitaModel();
            }
            return dbResult;
        }

        /// <summary>
        /// Esegue stored x ricalcolo RegAttExpl x il periodo indicato l'utente indicato
        /// </summary>
        /// <param name="IdxDip">Se 0 significa tutti</param>
        /// <param name="DtStart"></param>
        /// <param name="DtEnd"></param>
        /// <returns></returns>
        public bool RegAttRicalcola(int IdxDip, DateTime DtStart, DateTime DtEnd)
        {
            int result = 0;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxDip = new SqlParameter("@idxRA", IdxDip);
                    var pDtStart = new SqlParameter("@inizio", DtStart);
                    var pDtEnd = new SqlParameter("@fine", DtEnd);

                    result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_ricalcolaRegAttivitaExpl_byPeriodoUser @idxRA, @inizio, @fine", pIdxDip, pDtStart, pDtEnd);
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in RegAttRicalcola:{Environment.NewLine}{exc}");
                }
            }
            return result != 0;
        }

        /// <summary>
        /// Insert/Update attività
        /// </summary>
        /// <param name="currItem"></param>
        /// <returns></returns>
        public bool RegAttUpdate(RegAttivitaModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    if (currItem.IdxRa == 0)
                    {
                        dbCtx.Entry(currItem).State = EntityState.Added;
                    }
                    else
                    {
                        var currRec = dbCtx
                                .DbSetRegAttivita
                                .FirstOrDefault(x => x.IdxRa == currItem.IdxRa);
                        if (currRec != null)
                        {
                            currRec.IdxDipendente = currItem.IdxDipendente;
                            currRec.IdxFase = currItem.IdxFase;
                            currRec.Inizio = currItem.Inizio;
                            currRec.Fine = currItem.Fine;
                            currRec.Descrizione = currItem.Descrizione;

                            dbCtx.Entry(currRec).State = EntityState.Modified;

                            dbCtx
                                .DbSetRegAttivita
                                .Update(currRec);
                        }
                        // altrimenti aggiungo
                        else
                        {
                            dbCtx
                                .DbSetRegAttivita
                                .Update(currItem);
                        }
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegAttUpdate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Effettua spostamento di un record RA alla nuova fase indicata
        /// </summary>
        /// <param name="idxRA"></param>
        /// <param name="idxFase"></param>
        /// <returns></returns>
        public bool RegAttUpdateFase(int idxRA, int idxFase)
        {
            // init dati necessari
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxRA = new SqlParameter("@Original_idxRA", idxRA);
                    var pIdxFase = new SqlParameter("@idxFase", idxFase);

                    var dbResult = dbCtx
                                   .Database
                                   .ExecuteSqlRaw("EXEC dbo.stp_RA_updateFase @idxFase, @Original_idxRA", pIdxFase, pIdxRA);
                    answ = dbResult > 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in RegAttUpdateFase:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupera eventi dato criteri filtro
        /// </summary>
        /// <param name="inizio"></param>
        /// <param name="fine"></param>
        /// <param name="evento"></param>
        /// <returns></returns>
        public List<RegistroEventiModel> RegEventiGetFilt(DateTime inizio, DateTime fine, string evento)
        {
            List<RegistroEventiModel> dbResult = new List<RegistroEventiModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = dbCtx
                            .DbSetRegEventi
                            .Where(x => x.DataOra >= inizio && x.DataOra <= fine && (string.IsNullOrEmpty(evento) || x.Evento == evento))
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Insert/Update di un record in Registro Eventi
        /// </summary>
        /// <param name="currItem"></param>
        /// <returns></returns>
        public bool RegEventiUpdate(RegistroEventiModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var currRec = dbCtx
                            .DbSetRegEventi
                            .FirstOrDefault(x => x.DataOra == currItem.DataOra && x.Evento == currItem.Evento);
                    if (currRec != null)
                    {
                        currRec.Evento = currItem.Evento;
                        currRec.Commento = currItem.Commento;

                        dbCtx
                            .DbSetRegEventi
                            .Update(currRec);
                    }
                    // altrimenti aggiungo
                    else
                    {
                        dbCtx
                            .DbSetRegEventi
                            .Add(currItem);
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegEventiUpdate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Aggiorna record registro malattie approvando o rifiutando la richiesta e gestendo i
        /// giustificativi associati Da impiegare con pagiina ADM
        /// </summary>
        /// <param name="currItem"></param>
        public bool RegMalattieAppRif(RegMalattieModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // versione stored-based che sistema tutti i record dipendenti
                    SqlParameter pIdxRich = new SqlParameter("@Original_IdxRegMal", currItem.IdxRegMal);
                    SqlParameter pCodCert = new SqlParameter("@CodCert", currItem.CodCert);
                    SqlParameter pNumGG = new SqlParameter("@NumGG", currItem.NumGG);
                    SqlParameter pConf = new SqlParameter("@Conf", currItem.Conf);

                    int result = dbCtx
                                    .Database
                                    .ExecuteSqlRaw("EXEC stp_RM_updateQuery @Original_IdxRegMal, @CodCert, @NumGG, @Conf", pIdxRich, pCodCert, pNumGG, pConf);
                    answ = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegMalattieAppRif{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Elimina record registro malattie (SE non confermato)
        /// </summary>
        /// <param name="currItem"></param>
        public bool RegMalattieDelete(RegMalattieModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var currRec = dbCtx
                            .DbSetRegMalattie
                            .FirstOrDefault(x => x.IdxRegMal == currItem.IdxRegMal && !x.Conf);
                    if (currRec != null)
                    {
                        dbCtx
                            .DbSetRegMalattie
                            .Remove(currRec);
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegMalattieDelete{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupera elenco reg malattie (tutte, dato max dalla + recente)
        /// </summary>
        /// <param name="maxRecord"></param>
        /// <returns></returns>
        public List<RegMalattieModel> RegMalattieGetAll(int maxRecord)
        {
            List<RegMalattieModel> dbResult = new List<RegMalattieModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = dbCtx
                            .DbSetRegMalattie
                            .Include(d => d.DipNav)
                            .OrderByDescending(x => x.DtInizio)
                            .Take(maxRecord)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// recupera elenco reg malattie del dipendente
        /// </summary>
        /// <param name="idxDipendente"></param>
        /// <param name="maxRecord"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<RegMalattieModel> RegMalattieGetByDip(int idxDipendente, int maxRecord)
        {
            List<RegMalattieModel> dbResult = new List<RegMalattieModel>();
            if (idxDipendente > 0)
            {
                using (GPWContext dbCtx = new GPWContext(_configuration))
                {
                    DateTime oggi = DateTime.Today;
                    dbResult = dbCtx
                                .DbSetRegMalattie
                                .Where(x => x.IdxDipendente == idxDipendente)
                                .Include(d => d.DipNav)
                                .OrderByDescending(x => x.DtInizio)
                                .Take(maxRecord)
                                .ToList();
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera elenco reg malattie dato periodo
        /// </summary>
        /// <param name="DtFrom"></param>
        /// <param name="DtTo"></param>
        /// <returns></returns>
        public List<RegMalattieModel> RegMalattieGetByPeriod(DateTime DtFrom, DateTime DtTo)
        {
            List<RegMalattieModel> dbResult = new List<RegMalattieModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = dbCtx
                            .DbSetRegMalattie
                            .Where(x => (x.DtInizio.AddDays(x.NumGG) >= DtFrom && x.DtInizio <= DtTo))
                            .Include(d => d.DipNav)
                            .OrderByDescending(x => x.DtInizio)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Inserisce/Aggiorna record registro malattie
        /// </summary>
        /// <param name="currItem"></param>
        public bool RegMalattieUpsert(RegMalattieModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // prima versione EFCore senza stored che NON fa partire tutte le operazioni
#if false

                    // versione stored-based che sistema tutti i record dipendenti

                    SqlParameter pIdxDip = new SqlParameter("@IdxDip", IdxDip);
                    SqlParameter pDataFrom = new SqlParameter("@DtFrom", DtFrom);
                    SqlParameter pDataTo = new SqlParameter("@DtTo", DtTo);
                    SqlParameter pInatt = new SqlParameter("@pInatt", ShowIn);
                    SqlParameter pShowWE = new SqlParameter("@ShowWE", ShowWE);
                    SqlParameter pMaxErrMin = new SqlParameter("@MaxErrMin", MaxErrMin);
                    SqlParameter pMaxErrPlus = new SqlParameter("@MaxErrPlus", MaxErrPlus);

                    dbResult = dbCtx
                                    .DbSetTeRaExpl
                                    .FromSqlRaw("EXEC stp_TE_RA_ByUserDate @IdxDip,@DtFrom,@DtTo,@pInatt,@ShowWE,@MaxErrMin,@MaxErrPlus", pIdxDip, pDataFrom, pDataTo, pInatt, pShowWE, pMaxErrMin, pMaxErrPlus)
                                    .ToList();
#endif
                    var currRec = dbCtx
                                       .DbSetRegMalattie
                                       .FirstOrDefault(x => x.IdxRegMal == currItem.IdxRegMal);
                    if (currRec != null)
                    {
                        // aggiorno i campi
                        currRec.IdxDipendente = currItem.IdxDipendente;
                        currRec.DtInizio = currItem.DtInizio;
                        currRec.NumGG = currItem.NumGG;
                        currRec.CodCert = currItem.CodCert ?? "ND";
                        currRec.Conf = currItem.Conf;

                        dbCtx
                            .DbSetRegMalattie
                            .Update(currRec);
                    }
                    // altrimenti aggiungo
                    else
                    {
                        dbCtx
                            .DbSetRegMalattie
                            .Add(currItem);
                    }

                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegMalattieUpsert{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Aggiorna record registro richieste approvando o rifiutando la richiesta e gestendo i
        /// giustificativi associati Da impiegare con pagiina ADM
        /// </summary>
        /// <param name="currItem"></param>
        public bool RegRichiesteAppRif(RegRichiesteModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // versione stored-based che sistema tutti i record dipendenti
                    SqlParameter pIdxRich = new SqlParameter("@Original_IdxRegRich", currItem.IdxRegRich);
                    SqlParameter pConf = new SqlParameter("@Conf", currItem.Conf);
                    SqlParameter pCodGiust = new SqlParameter("@CodGiust", currItem.CodGiust);
                    SqlParameter pNote = new SqlParameter("@Note", currItem.Note);

                    int result = dbCtx
                                    .Database
                                    .ExecuteSqlRaw("EXEC stp_RR_updateQuery @Original_IdxRegRich, @Conf, @CodGiust, @Note", pIdxRich, pConf, pCodGiust, pNote);
                    answ = result != 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegRichiesteAppRif{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Elimina record registro richieste (SE non confermato)
        /// </summary>
        /// <param name="currItem"></param>
        public bool RegRichiesteDelete(RegRichiesteModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var currRec = dbCtx
                            .DbSetRegRichieste
                            .FirstOrDefault(x => x.IdxRegRich == currItem.IdxRegRich && !x.Conf);
                    if (currRec != null)
                    {
                        dbCtx
                            .DbSetRegRichieste
                            .Remove(currRec);
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegRichiesteDelete{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// recupera elenco reg richieste del dipendente
        /// </summary>
        /// <param name="idxDipendente"></param>
        /// <param name="dtFrom">Inizio periodo richiesto</param>
        /// <param name="dtTo">Fine periodo richiesto</param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<RegRichiesteModel> RegRichiesteGetByDip(int idxDipendente, DateTime dtFrom, DateTime dtTo)
        {
            List<RegRichiesteModel> dbResult = new List<RegRichiesteModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = dbCtx
                            .DbSetRegRichieste
                            .Where(x => (idxDipendente == 0 || x.IdxDipendente == idxDipendente) && (x.DtStart >= dtFrom && x.DtStart <= dtTo))
                            .Include(d => d.DipNav)
                            .OrderByDescending(x => x.DtStart)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Inserisce/Aggiorna record registro richieste
        /// </summary>
        /// <param name="currItem"></param>
        public bool RegRichiesteUpsert(RegRichiesteModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var currRec = dbCtx
                            .DbSetRegRichieste
                            .FirstOrDefault(x => x.IdxRegRich == currItem.IdxRegRich);
                    if (currRec != null)
                    {
                        // aggiorno i campi
                        currRec.IdxDipendente = currItem.IdxDipendente;
                        currRec.DtStart = currItem.DtStart;
                        currRec.DtEnd = currItem.DtEnd;
                        currRec.CodGiust = currItem.CodGiust;
                        currRec.Note = currItem.Note ?? "";
                        currRec.Conf = currItem.Conf;

                        dbCtx
                            .DbSetRegRichieste
                            .Update(currRec);
                    }
                    // altrimenti aggiungo
                    else
                    {
                        dbCtx
                            .DbSetRegRichieste
                            .Add(currItem);
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RegRichiesteUpsert{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupera l'elenco dei dettagli attività e timbrature di un dipendente, dato periodo riferimento
        /// </summary>
        /// <param name="idxDipendente">Dipendente interessato</param>
        /// <param name="periodo">Periodo di riferimento</param>
        /// <returns></returns>
        public ReportDataDTO ReportDetails(int idxDipendente, DtUtils.Periodo periodo)
        {
            // init dati necessari
            DateTime dtInizio = periodo.Inizio;
            DateTime dtFine = periodo.Fine;
            ReportDataDTO dbResult = new ReportDataDTO();

            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                // recupero intero set dati timbrature e attività....
                dbResult.TimbrExpl = dbCtx
                                .DbSetTimbratureExpl
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DataLav && x.DataLav <= dtFine)
                                .ToList();

                dbResult.ListTimbr = dbCtx
                                .DbSetTimbrature
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DataOra && x.DataOra <= dtFine.AddDays(1))
                                .ToList();

                dbResult.ListRA = dbCtx
                                .DbSetRegAttivita
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.Inizio && x.Inizio <= dtFine.AddDays(1))
                                .Include(a => a.FasiNav).ThenInclude(p => p.ProgettoNav).ThenInclude(c => c.ClienteNav)
                                .ToList();

                // effettuo calcoli!
                dbResult.DoCalc();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera l'elenco dei Rilievi temperatura nel periodo indicato x dipendente
        /// </summary>
        /// <param name="idxDipendente">Dipendente interessato</param>
        /// <param name="dtInizio">Data di riferimento (ultima/corrente)</param>
        /// <param name="dtFine">NUm settimane precedenti da recuperare</param>
        /// <returns></returns>
        public List<RilievoTempModel> RilTempList(int idxDipendente, DateTime dtInizio, DateTime dtFine)
        {
            // init dati necessari
            List<RilievoTempModel> dbResult = new List<RilievoTempModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                dbResult = dbCtx
                                .DbSetRilievoTemp
                                .Where(x => x.IdxDipendente == idxDipendente && dtInizio <= x.DtRilievo && x.DtRilievo <= dtFine)
                                .ToList();
            }
            return dbResult;
        }

        public bool RilTempUpdate(RilievoTempModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var currRec = dbCtx
                            .DbSetRilievoTemp
                            .FirstOrDefault(x => x.IdxDipendente == currItem.IdxDipendente && x.DtRilievo == currItem.DtRilievo);
                    if (currRec != null)
                    {
                        // aggiorno solo entrata/uscita
                        currRec.TempRil = currItem.TempRil;

                        dbCtx
                            .DbSetRilievoTemp
                            .Update(currRec);
                    }
                    // altrimenti aggiungo
                    else
                    {
                        dbCtx
                            .DbSetRilievoTemp
                            .Add(currItem);
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RilTempUpdate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Annulla modifiche su una specifica entity (cancel update)
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool rollBackEntity(object item)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    if (dbCtx.Entry(item).State == EntityState.Deleted || dbCtx.Entry(item).State == EntityState.Modified)
                    {
                        dbCtx.Entry(item).Reload();
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in rollBackEntity{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupera record timbrature explose + consuntivo attivita dipendenti
        /// </summary>
        /// <param name="IdxDip">singolo idx oppure 0 = tutti</param>
        /// <param name="DtFrom">Inizio periodo</param>
        /// <param name="DtTo">Fine periodo</param>
        /// <param name="ShowIn">Mostra inattivi</param>
        /// <param name="ShowWE">Mostra weekend</param>
        /// <param name="MaxErrMin">max consentito in mancanza</param>
        /// <param name="MaxErrPlus">max consentito in eccesso</param>
        /// <returns></returns>
        public List<TeRaExplModel> TeRaExplGetFilt(int IdxDip, DateTime DtFrom, DateTime DtTo, bool ShowIn, bool ShowWE, int MaxErrMin, int MaxErrPlus)
        {
            List<TeRaExplModel> dbResult = new List<TeRaExplModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                SqlParameter pIdxDip = new SqlParameter("@idxRA", IdxDip);
                SqlParameter pDataFrom = new SqlParameter("@dataFrom", DtFrom);
                SqlParameter pDataTo = new SqlParameter("@dataTo", DtTo);
                SqlParameter pInatt = new SqlParameter("@showInattivi", ShowIn);
                SqlParameter pShowWE = new SqlParameter("@showWE", ShowWE);
                SqlParameter pMaxErrMin = new SqlParameter("@maxErrMin", MaxErrMin);
                SqlParameter pMaxErrPlus = new SqlParameter("@maxErrPlus", MaxErrPlus);

                dbResult = dbCtx
                                .DbSetTeRaExpl
                                .FromSqlRaw("EXEC stp_TE_RA_ByUserDate @idxRA, @dataFrom, @dataTo, @showInattivi, @showWE, @maxErrMin, @maxErrPlus", pIdxDip, pDataFrom, pDataTo, pInatt, pShowWE, pMaxErrMin, pMaxErrPlus)
                                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera record anomalie dipendenti
        /// </summary>
        /// <param name="idxDipendente">singolo idx oppure 0 = tutti</param>
        /// <param name="inizio"></param>
        /// <param name="fine"></param>
        /// <param name="notOkApp"></param>
        /// <param name="notOkTim"></param>
        /// <param name="notOkLav"></param>
        /// <returns></returns>
        public List<TimbratureExplModel> TimbExplGetAnomalie(int idxDipendente, DateTime inizio, DateTime fine, bool notOkApp, bool notOkTim, bool notOkLav)
        {
            List<TimbratureExplModel> dbResult = new List<TimbratureExplModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                SqlParameter pIdxDip = new SqlParameter("@IdxDip", idxDipendente);
                SqlParameter pInizio = new SqlParameter("@inizio", inizio);
                SqlParameter pFine = new SqlParameter("@fine", fine);
                SqlParameter pNotOkApp = new SqlParameter("@notOkApp", notOkApp);
                SqlParameter pNotOkTim = new SqlParameter("@notOkTim", notOkTim);
                SqlParameter pNotOkLav = new SqlParameter("@notOkLav", notOkLav);

                dbResult = dbCtx
                                .DbSetTimbratureExpl
                                .FromSqlRaw("EXEC stp_timbratureExpl_getByAnomalia @IdxDip,@inizio,@fine,@notOkApp,@notOkTim,@notOkLav", pIdxDip, pInizio, pFine, pNotOkApp, pNotOkTim, pNotOkLav)
                                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera record anomalie dipendenti x orario continuato
        /// </summary>
        /// <param name="idxDipendente">singolo idx oppure 0 = tutti</param>
        /// <param name="inizio"></param>
        /// <param name="fine"></param>
        /// <returns></returns>
        public List<TimbratureExplModel> TimbExplGetContinuato(int idxDipendente, DateTime inizio, DateTime fine)
        {
            List<TimbratureExplModel> dbResult = new List<TimbratureExplModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                SqlParameter parIdxDip = new SqlParameter("@IdxDip", idxDipendente);
                SqlParameter parInizio = new SqlParameter("@inizio", inizio);
                SqlParameter parFine = new SqlParameter("@pCodCert", fine);

                dbResult = dbCtx
                                .DbSetTimbratureExpl
                                .FromSqlRaw("EXEC stp_timbratureExpl_getContinuato @IdxDip,@inizio,@pCodCert", parIdxDip, parInizio, parFine)
                                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Esegue stored x ricalcolo TimbExpl x il periodo indicato l'utente indicato
        /// </summary>
        /// <param name="IdxDip">Se 0 significa tutti</param>
        /// <param name="DtStart"></param>
        /// <param name="DtEnd"></param>
        /// <returns></returns>
        public bool TimbExplRicalcola(int IdxDip, DateTime DtStart, DateTime DtEnd)
        {
            int result = 0;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pIdxDip = new SqlParameter("@idxRA", IdxDip);
                    var pDtStart = new SqlParameter("@inizio", DtStart);
                    var pDtEnd = new SqlParameter("@fine", DtEnd);

                    result = dbCtx
                                .Database
                                .ExecuteSqlRaw("EXEC stp_ricalcolaTimbExpl_byPeriodoUser @idxRA, @inizio, @fine", pIdxDip, pDtStart, pDtEnd);
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in TimbExplRicalcola:{Environment.NewLine}{exc}");
                }
            }
            return result != 0;
        }

        /// <summary>
        /// Recupero dati di sunto delle timbrature mensili x dipendente da stored dedicata
        /// </summary>
        /// <param name="idxDipendente"></param>
        /// <param name="dataFrom"></param>
        /// <param name="dataTo"></param>
        /// <returns></returns>
        public List<TimbMeseExplModel> TimbMeseExplList(int idxDipendente, DateTime dataFrom, DateTime dataTo)
        {
            // init dati necessari
            List<TimbMeseExplModel> dbResult = new List<TimbMeseExplModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var pidxDipendente = new SqlParameter("@idxRA", idxDipendente);
                    var pInizio = new SqlParameter("@inizio", dataFrom);
                    var pFine = new SqlParameter("@fine", dataTo);

                    dbResult = dbCtx
                                .DbSetTimbMeseExpl
                                .FromSqlRaw("EXEC dbo.stp_TimbMeseExpl @inizio, @fine, @idxRA", pInizio, pFine, pidxDipendente)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in TimbMeseExplList:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco timbrature successive alla data odierna x ogni dipendente (x recupero errori
        /// timbrature future)
        /// - per il check imposta la mezzanotte del giorno successivo
        /// </summary>
        /// <returns></returns>
        public List<TimbratureModel> TimbratureAnomalieFuture()
        {
            // imposta data a domani
            DateTime dateRif = DateTime.Today.AddDays(1);
            List<TimbratureModel> dbResult = new List<TimbratureModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    dbResult = dbCtx
                                    .DbSetTimbrature
                                    .Where(x => x.DataOra.Date >= dateRif.Date)
                                    .OrderBy(x => x.IdxDipendente)
                                    .ThenByDescending(x => x.DataOra)
                                    .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in TimbratureAnomalieFuture{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public List<TimbratureModel> TimbratureDay(DateTime dateRif, int IdxDip)
        {
            List<TimbratureModel> dbResult = new List<TimbratureModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    dbResult = dbCtx
                                    .DbSetTimbrature
                                    .Where(x => x.DataOra.Date == dateRif.Date && x.IdxDipendente == IdxDip)
                                    .OrderByDescending(x => x.DataOra)
                                    .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in TimbratureDay{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public async Task<TimbratureModel?> TimbratureByUidAsync(string UID)
        {
            TimbratureModel? dbRec = null;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // UID = $"{IdxDipendente:000}_{DataOra:yyyyMMdd}_{DataOra:HHmmss}"
                    var parts = UID.Split('_');
                    if (parts.Length == 3)
                    {
                        int idxDip = int.Parse(parts[0]);
                        DateTime dataOra = DateTime.ParseExact(parts[1] + "_" + parts[2], "yyyyMMdd_HHmmss", null);

                        dbRec = await dbCtx
                                .DbSetTimbrature
                                .FirstOrDefaultAsync(x => x.IdxDipendente == idxDip && x.DataOra == dataOra);
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in TimbratureByUidAsync: {Environment.NewLine}{exc}");
                }
            }
            return dbRec;
        }

        public bool TimbratureDelete(TimbratureModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    dbCtx.Remove(currItem);

                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in TimbratureDelete{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Elenco di tutte le timbrature richieste (= NON approvate)
        /// </summary>
        /// <returns></returns>
        public List<TimbratureModel> TimbratureRichieste()
        {
            List<TimbratureModel> dbResult = new List<TimbratureModel>();
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    dbResult = dbCtx
                                    .DbSetTimbrature
                                    .Where(x => x.Approv == false)
                                    .Include(d => d.DipNav)
                                    .OrderByDescending(x => x.DataOra)
                                    .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in TimbratureRichieste{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public bool TimbratureUpdate(TimbratureModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var currRec = dbCtx
                            .DbSetTimbrature
                            .FirstOrDefault(x => x.IdxDipendente == currItem.IdxDipendente && x.DataOra == currItem.DataOra);
                    if (currRec != null)
                    {
                        // aggiorno solo entrata/uscita
                        currRec.Entrata = currItem.Entrata;
                        currRec.Approv = currItem.Approv;
                        currRec.Ipv4 = currItem.Ipv4;
                        currRec.DataOra = currItem.DataOra;

                        dbCtx
                            .DbSetTimbrature
                            .Update(currRec);
                    }
                    // altrimenti aggiungo
                    else
                    {
                        dbCtx
                            .DbSetTimbrature
                            .Add(currItem);
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in TimbratureUpdate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Registra update + arrotondamento orario
        /// </summary>
        /// <param name="currItem"></param>
        /// <returns></returns>
        public bool TimbratureUpdateRound(TimbratureModel currItem)
        {
            bool answ = false;
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    var currRec = dbCtx
                            .DbSetTimbrature
                            .FirstOrDefault(x => x.IdxDipendente == currItem.IdxDipendente && x.DataOra == currItem.DataOra);
                    if (currRec != null)
                    {
                        // se non si tratta di una timbratura "last second"...
                        if (currItem.DataOra.Hour != 23 && currItem.DataOra.Minute != 59 && currItem.DataOra.Second != 59)
                        {
                            // arrotondo ingresso/uscita ai 5 minuti secondo sia entrata o uscita...
                            var dtTimb = Utils.DateRounded(currItem.DataOra, 5, !(currItem.Entrata ?? false));
                            currItem.DataOra = dtTimb;
                        }
                        //// aggiorno solo entrata/uscita
                        //currRec.Entrata = currItem.Entrata;
                        //currRec.Approv = currItem.Approv;
                        //currRec.Ipv4 = currItem.Ipv4;
                        //currRec.DataOra = currItem.DataOra;

                        // elimino e reinserisco!
                        dbCtx.Remove(currRec);
                        dbCtx.SaveChanges();
                        dbCtx.Add(currItem);

                        //dbCtx
                        //    .DbSetTimbrature
                        //    .Update(currRec);
                    }
                    // altrimenti aggiungo
                    else
                    {
                        dbCtx
                            .DbSetTimbrature
                            .Add(currItem);
                    }
                    dbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in TimbratureUpdate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Recupera l'elenco delle ultime attività inserite da un dipendente
        /// </summary>
        /// <param name="idxDipendente">Dipendente interessato</param>
        /// <param name="numRec">Num record da recuperare</param>
        /// <returns></returns>
        public List<RegAttivitaModel> UserLastRec(int idxDipendente, int numRec)
        {
            List<RegAttivitaModel> listRec = new List<RegAttivitaModel>();
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                listRec = dbCtx
                                .DbSetRegAttivita
                                .Where(x => x.IdxDipendente == idxDipendente)
                                .Include(a => a.FasiNav).ThenInclude(p => p.ProgettoNav).ThenInclude(c => c.ClienteNav)
                                .OrderByDescending(x => x.IdxRa)
                                .Take(numRec)
                                .AsNoTracking()
                                .ToList();
            }
            return listRec;
        }

        /// <summary>
        /// Recupera overview di un periodo settimanale x dipendente, data riferimento, numero
        /// settimane precedenti
        /// </summary>
        /// <param name="idxDipendente">Dipendente interessato</param>
        /// <param name="dtRif">Data di riferimento (ultima/corrente)</param>
        /// <param name="numWeek">NUm settimane precedenti da recuperare</param>
        /// <returns></returns>
        public List<WeekStatDTO> WeekOverview(int idxDipendente, DateTime dtRif, int numWeek)
        {
            // init dati necessari
            List<WeekStatDTO> dbResult = new List<WeekStatDTO>();
            List<WeekData> weekList = new List<WeekData>();

            // aggiungo sett corrente + quelle richieste...
            for (int i = numWeek; i >= 0; i--)
            {
                var currWeek = new WeekData(dtRif.AddDays(-i * 7));
                weekList.Add(currWeek);
            }

            // cerco su DB settimana x settimana....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                foreach (var item in weekList)
                {
                    var totOreTim = dbCtx
                                   .DbSetTimbratureExpl
                                   .Where(x => x.IdxDipendente == idxDipendente && x.DataLav >= item.inizio && x.DataLav <= item.fine)
                                   .Sum(x => (double)x.HLav);
                    var totMinLav = dbCtx
                                   .DbSetRegAttivitaExpl
                                   .Where(x => x.IdxDipendente == idxDipendente && x.DataLav >= item.inizio && x.DataLav <= item.fine)
                                   .Sum(x => (int)x.MinRegAtt);

                    // aggiungo alla lista...
                    var weekData = new WeekStatDTO()
                    {
                        IdxDipendente = idxDipendente,
                        Anno = item.anno,
                        WeekNumber = item.weekNumber,
                        Inizio = item.inizio,
                        Fine = item.fine,
                        SumOreLav = totOreTim,
                        SumOreComm = (double)totMinLav / 60,
                    };
                    dbResult.Add(weekData);
                }
            }
            return dbResult;
        }

        #endregion Public Methods

        #region Internal Methods

        internal bool CalFestFerieDelete(CalFesteFerieModel remRec)
        {  // init
            bool answ = false;
            // cerco su DB recuperando set di dati....
            using (GPWContext dbCtx = new GPWContext(_configuration))
            {
                try
                {
                    // recupero cliente in primis...
                    var dbRec = dbCtx
                                  .DbSetCalFesteFerie
                                  .Where(x => x.data == remRec.data)
                                  .FirstOrDefault();
                    if (dbRec != null)
                    {
                        dbCtx
                            .DbSetCalFesteFerie
                            .Remove(dbRec);
                        dbCtx.SaveChanges();
                        answ = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in CalFestFerieDelete:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        #endregion Internal Methods

        #region Private Fields

        private static IConfiguration _configuration = null!;
        private static NLog.Logger Log = LogManager.GetCurrentClassLogger();

        #endregion Private Fields
    }
}