using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using NLog;
using SHERPA.Data.DbModels;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace SHERPA.Data.Controllers
{
    public class SAcDoController : IDisposable
    {
        #region Public Constructors

        public SAcDoController(IConfiguration configuration)
        {
            _configuration = configuration;
            Log.Info("Avviata classe SHERPA.Data.SAcDoController");
        }

        #endregion Public Constructors

        #region Public Methods

        public bool AccDocUpdate(AccoDocModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    var currData = dbCtx
                        .DbSetAccoDocs
                        .Where(x => x.IdxFatt == updItem.IdxFatt)
                        .FirstOrDefault();
                    if (currData != null)
                    {
                        currData.Anno = updItem.Anno;
                        currData.Num = updItem.Num;
                        currData.IdxCli = updItem.IdxCli;
                        currData.Emesso = updItem.Emesso;
                        currData.Ritenuta = updItem.Ritenuta;
                        currData.Scadenza = updItem.Scadenza;
                        currData.IdxTipo = updItem.IdxTipo;
                        currData.NewId = updItem.NewId;
                        currData.Token = updItem.Token;
                        currData.Changed = updItem.Changed;
                        dbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx
                            .DbSetAccoDocs
                            .Add(updItem);
                    }
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in AccDocUpdate{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Duplicazione doc selezionato
        /// </summary>
        /// <param name="idxDoc"></param>
        /// <returns></returns>
        public bool AccoDocClone(int idxDoc)
        {
            bool fatto = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                SqlParameter IdxFatt = new SqlParameter("@IdxFatt", idxDoc);
                var result = dbCtx.Database.ExecuteSqlRaw("EXEC stp_docs_clone @IdxFatt", IdxFatt);
                fatto = result > 0;
            }
            return fatto;
        }

        /// <summary>
        /// Elimina item AccoDoc (Accounting Document)
        /// </summary>
        /// <param name="FattId"></param>
        /// <returns></returns>
        public bool AccoDocDelete(int FattId)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    // verifico nuovamente che NON sia stato caricato su cloud
                    var item2del = dbCtx
                          .DbSetAccoDocs
                          .Where(x => x.IdxFatt == FattId && string.IsNullOrEmpty(x.NewId))
                          .FirstOrDefault();
                    if (item2del != null)
                    {
                        // elimina eventuali scadenze...
                        var scad2Del = dbCtx.DbSetScadenze.Where(x => x.IdFatt == FattId).FirstOrDefault();
                        if (scad2Del != null)
                        {
                            dbCtx.DbSetScadenze.Remove(scad2Del);
                        }
                        dbCtx.DbSetAccoDocs.Remove(item2del);
                        dbCtx.SaveChanges();
                        done = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in AccoDocDelete{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Recupera AccoDoc (Accounting Document) da Key
        /// </summary>
        /// <param name="newItem"></param>
        /// <returns></returns>
        public AccoDocModel? AccoDocGetByKey(int DocId)
        {
            AccoDocModel? currDoc = new AccoDocModel();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    currDoc = dbCtx
                                .DbSetAccoDocs
                                .Include(d => d.RigheFattNav)
                                .ThenInclude(r => r.VatNav)
                                .Where(x => x.IdxFatt == DocId)
                                .FirstOrDefault();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in AccoDocGetByKey{Environment.NewLine}{exc}");
                }
            }
            return currDoc;
        }

        public List<AccoDocModel> AccoDocGetFilt(int anno, int numero, int idxTipoDoc, bool soloMod, string searchVal)
        {
            List<AccoDocModel> dbResult = new List<AccoDocModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                        .DbSetAccoDocs
                        .Where(x => (x.Anno == anno || anno == 0) && (x.Num == numero || numero == 0) && ((x.Changed > 0 && soloMod) || !soloMod) && (x.IdxTipo == idxTipoDoc || idxTipoDoc == 0) && ((x.CustomerNav != null && x.CustomerNav.RagSoc != null && x.CustomerNav.RagSoc.Contains(searchVal)) || string.IsNullOrEmpty(searchVal)))
                        .Include(d => d.RigheFattNav)
                        .Include(d => d.CustomerNav)
                        .Include(d => d.ContributiNav)
                        .Include(d => d.ScadenzeNav)
                        .Include(d => d.PagamentiNav)
                        .OrderByDescending(x => x.Anno)
                        .ThenByDescending(x => x.Num)
                        .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco ultimi AccoDoc (Accounting Document)
        /// </summary>
        /// <param name="numRecord"></param>
        /// <returns></returns>
        public List<AccoDocModel> AccoDocGetLastDesc(int numRecord)
        {
            List<AccoDocModel> dbResult = new List<AccoDocModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                // se numRecord = 0 --> passo tutti
                if (numRecord == 0)
                {
                    numRecord = dbCtx.DbSetAccoDocs.Count();
                }
                dbResult = dbCtx
                    .DbSetAccoDocs
                    .OrderByDescending(x => x.Anno)
                    .ThenByDescending(x => x.Num)
                    .Take(numRecord)
                    .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 (SHERPAFattContext localDbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetConfig
                            .OrderBy(x => x.Chiave)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Crea scadenze da elenco fatture
        /// </summary>
        /// <param name="idxDoc"></param>
        /// <returns></returns>
        public bool CreaScadenze()
        {
            bool fatto = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                var result = dbCtx.Database.ExecuteSqlRaw("EXEC stp_creaScadenze");
                fatto = result > 0;
            }
            return fatto;
        }

        /// <summary>
        /// Clona un item Customer
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool CustomerClona(CustomerModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    // clono il record
                    CustomerModel newRec = new CustomerModel()
                    {
                        Attivo = updItem.Attivo,
                        Cap = updItem.Cap,
                        CFisc = updItem.CFisc,
                        CodiceCliente = $"COPIA-{updItem.CodiceCliente}",
                        CodUnivoco = updItem.CodUnivoco,
                        IdxGruppo = updItem.IdxGruppo,
                        Localita = updItem.Localita,
                        Note = updItem.Note,
                        Pagamento = updItem.Pagamento,
                        Pec = updItem.Pec,
                        PIva = updItem.PIva,
                        Provincia = updItem.Provincia,
                        RagSoc = $"__COPIA - {updItem.RagSoc} - COPIA",
                        Valuta = updItem.Valuta,
                        Via = updItem.Via
                    };

                    dbCtx.DbSetCustomers.Add(newRec);
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in CustomerClona{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Elimina un item Customer
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool CustomerDelete(CustomerModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    var item2del = dbCtx
                                .DbSetCustomers
                                .Where(x => x.IdxCli == updItem.IdxCli)
                                .FirstOrDefault();
                    if (item2del != null)
                    {
                        dbCtx
                            .DbSetCustomers
                            .Remove(item2del);
                        dbCtx.SaveChanges();
                        done = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in CustomerDelete{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        public int CustomersCount()
        {
            int answ = 0;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                answ = dbCtx
                        .DbSetCustomers
                        .Count();
            }
            return answ;
        }

        /// <summary>
        /// Elenco Customers che possono essere eliminati (NIENTE doc collegati)
        /// </summary>
        /// <returns></returns>
        public List<CustomerModel> CustomersDeletable()
        {
            List<CustomerModel> dbResult = new List<CustomerModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetCustomers
                            .Where(x => (x.FattureNav.Count == 0))
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Customers
        /// </summary>
        /// <param name="searchVal"></param>
        /// <returns></returns>
        public List<CustomerModel> CustomersGetAll(string searchVal)
        {
            List<CustomerModel> dbResult = new List<CustomerModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetCustomers
                            .Where(x => ((x.RagSoc != null && x.RagSoc.Contains(searchVal)) || x.CodUnivoco.Contains(searchVal) || string.IsNullOrEmpty(searchVal)))
                            .OrderBy(x => x.RagSoc)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Ricerca Customer da idx
        /// </summary>
        /// <param name="idxCli"></param>
        /// <returns></returns>
        public CustomerModel? CustomersGetByKey(int idxCli)
        {
            CustomerModel? dbResult = new CustomerModel();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetCustomers
                            .Where(x => (x.IdxCli == idxCli))
                            .FirstOrDefault();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Customers che richiedono sync tramite stored
        /// </summary>
        /// <param name="idxTipoDoc">Tipo doc, 0: tutti</param>
        /// <param name="anno">Anno doc, 0: tutti</param>
        /// <param name="notUpl">Indica se solo non caricati (1: NON caricati)</param>
        /// <returns></returns>
        public List<CustomerModel> CustomersToSync(int idxTipoDoc, int anno, bool notUpl)
        {
            List<CustomerModel> dbResult = new List<CustomerModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                SqlParameter IdxTipo = new SqlParameter("@idxTipo", idxTipoDoc);
                SqlParameter Anno = new SqlParameter("@anno", anno);
                SqlParameter NotUpl = new SqlParameter("@notUpl", notUpl);

                dbResult = dbCtx
                              .DbSetCustomers
                              .FromSqlRaw("EXEC stp_clienti_getByAnno @idxTipo,@anno,@notUpl", IdxTipo, Anno, NotUpl)
                              .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiorna un record Cliente
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool CustomerUpdate(CustomerModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    var currData = dbCtx
                        .DbSetCustomers
                        .Where(x => x.IdxCli == updItem.IdxCli)
                        .FirstOrDefault();
                    if (currData != null)
                    {
                        currData.Attivo = updItem.Attivo;
                        currData.Cap = updItem.Cap;
                        currData.CFisc = updItem.CFisc;
                        currData.CodiceCliente = updItem.CodiceCliente;
                        currData.CodUnivoco = updItem.CodUnivoco;
                        currData.IdExt = updItem.IdExt;
                        currData.IdxGruppo = updItem.IdxGruppo;
                        currData.Localita = updItem.Localita;
                        currData.Note = updItem.Note;
                        currData.Pagamento = updItem.Pagamento;
                        currData.Pec = updItem.Pec;
                        currData.PIva = updItem.PIva;
                        currData.Provincia = updItem.Provincia;
                        currData.RagSoc = updItem.RagSoc;
                        currData.Valuta = updItem.Valuta;
                        currData.Via = updItem.Via;
                        dbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx
                            .DbSetCustomers
                            .Add(updItem);
                    }
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in CustomerUpdate{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        public void Dispose()
        {
            GC.Collect();
        }

        /// <summary>
        /// Elenco AccoDoc (Accounting Document) filtrati
        /// </summary>
        /// <param name="anno">anno di riferimento (0: tutti)</param>
        /// <param name="numero">num documento (0: tutti)</param>
        /// <param name="tipoDoc">tipo Doc</param>
        /// <param name="soloMod">Solo documenti modificati (da inviare in cloud)</param>
        /// <param name="searchVal">Ricerca libera</param>
        /// <summary>
        /// Aggiunge un nuovo item Doc
        /// </summary>
        /// <param name="newItem"></param>
        /// <returns></returns>
        public bool DocsAddNew(AccoDocModel newItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    dbCtx
                        .DbSetAccoDocs
                        .Add(newItem);
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in DocsAddNew{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Restituisce prossimo indice libero documenti x criterio tipo
        /// </summary>
        /// <param name="anno"></param>
        /// <param name="idxTipoDoc"></param>
        /// <returns></returns>
        public int DocsGetNextNum(int anno, int idxTipoDoc)
        {
            int answ = 0;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                SqlParameter Anno = new SqlParameter("@Anno", anno);
                SqlParameter IdxTipo = new SqlParameter("@IdxTipoDoc", idxTipoDoc);

                var paramRet = new SqlParameter
                {
                    ParameterName = "ReturnValue",
                    SqlDbType = System.Data.SqlDbType.Int,
                    Direction = System.Data.ParameterDirection.Output,
                };

                var dbResult = dbCtx
                              .Database
                              .ExecuteSqlRaw("EXEC @ReturnValue = stp_docs_getNum @Anno,@IdxTipoDoc", paramRet, Anno, IdxTipo);
                answ=(int)paramRet.Value;
            }
            return answ;
        }

        /// <summary>
        /// Conteggio documenti x criteri
        /// </summary>
        /// <param name="anno"></param>
        /// <param name="idxTipoDoc"></param>
        /// <returns></returns>
        public int DocsCount(int anno, int idxTipoDoc)
        {
            int answ = 0;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                answ = dbCtx
                        .DbSetAccoDocs
                        .Where(x => (x.Anno == anno || anno == 0) && (x.IdxTipo == idxTipoDoc || idxTipoDoc == 0))
                        .Count();
            }
            return answ;
        }

        /// <summary>
        /// Aggiorna un item Doc
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool DocUpdate(AccoDocModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    var currData = dbCtx
                        .DbSetAccoDocs
                        .Where(x => x.IdxFatt == updItem.IdxFatt)
                        .FirstOrDefault();
                    if (currData != null)
                    {
                        currData.Anno = updItem.Anno;
                        currData.Num = updItem.Num;
                        currData.DtEvent = updItem.DtEvent;
                        currData.IdxCli = updItem.IdxCli;
                        currData.IdxTipo = updItem.IdxTipo;
                        currData.Ritenuta = updItem.Ritenuta;
                        currData.Scelta = updItem.Scelta;
                        currData.Token = updItem.Token;
                        currData.NewId = updItem.NewId;
                        // segno modificato!
                        dbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx
                            .DbSetAccoDocs
                            .Add(updItem);
                    }
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in DocUpdate{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Elenco pagamenti dato doc (fattura)
        /// </summary>
        /// <param name="IdxDoc"></param>
        /// <returns></returns>
        public List<PagamentiModel> PagamByDoc(int IdxDoc)
        {
            List<PagamentiModel> dbResult = new List<PagamentiModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetPagamenti
                            .Where(x => (x.IdFatt == IdxDoc))
                            .OrderByDescending(x => x.Data)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elimina un item Pagamento, sistemando le scadenze
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool PagamDelete(PagamentiModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    SqlParameter idFatt = new SqlParameter("@Original_Id_fatt", updItem.IdFatt);
                    SqlParameter dataScad = new SqlParameter("@Original_data", updItem.Data);

                    var dbResult = dbCtx
                                  .Database
                                  .ExecuteSqlRaw("EXEC stp_pag_deleteQuery @Original_Id_fatt,@Original_data", idFatt, dataScad);
                    done = dbResult > 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in PagamDelete{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Aggiorna un pagamento (chiamando stored)
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool PagamUpdate(PagamentiModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    SqlParameter idFatt = new SqlParameter("@Id_fatt", updItem.IdFatt);
                    SqlParameter dataScad = new SqlParameter("@dataScad", updItem.Data);
                    SqlParameter dataPag = new SqlParameter("@dataPag", updItem.Data);
                    SqlParameter netto = new SqlParameter("@netto", updItem.Netto);
                    SqlParameter importo = new SqlParameter("@importo", updItem.Importo);
                    SqlParameter note = new SqlParameter("@note", updItem.Note);
                    SqlParameter forcePag = new SqlParameter("@forcePag", true);

                    var dbResult = dbCtx
                                  .Database
                                  .ExecuteSqlRaw("EXEC stp_setPagato @Id_fatt,@dataScad,@dataPag,@netto,@importo,@note,@forcePag", idFatt, dataScad, dataPag, netto, importo, note, forcePag);
                    done = dbResult > 0;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in PagamUpdate{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Elenco completo permessi2funzione
        /// </summary>
        /// <returns></returns>
        public List<Permessi2FunzioneModel> Permessi2FunzioneGetAll()
        {
            List<Permessi2FunzioneModel> dbResult = new List<Permessi2FunzioneModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                              .DbSetPermessi2Funzione
                              .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco permessi2funzione filtrato x elenco funzioni
        /// </summary>
        /// <returns></returns>
        public List<Permessi2FunzioneModel> Permessi2FunzioneGetFilt(List<string> FunList)
        {
            List<Permessi2FunzioneModel> dbResult = new List<Permessi2FunzioneModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                              .DbSetPermessi2Funzione
                              .Where(x => FunList.Contains(x.CodFunzione))
                              .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco completo permessi
        /// </summary>
        /// <returns></returns>
        public List<PermessiModel> PermessiGetAll()
        {
            List<PermessiModel> dbResult = new List<PermessiModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                              .DbSetPermessi
                              .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco permessi dato elenco funzioni
        /// </summary>
        /// <param name="ListCodFun"></param>
        /// <returns></returns>
        public List<PermessiModel> PermessiGetByFunc(List<string> ListCodFun)
        {
            List<PermessiModel> dbResult = new List<PermessiModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                var listPer = PermessiGetAll();
                var listP2F = Permessi2FunzioneGetFilt(ListCodFun);

                var query = from permesso in listPer
                            join p2f in listP2F on permesso.CodPermesso equals p2f.CodPermesso
                            select permesso;

                dbResult = query.ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Clona un item RigaDoc
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool RigaDocClona(RigheFattModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    //calcolo il NUOVO numero riga da inserire...
                    int numRow = dbCtx.DbSetRigheFatt.Where(x => x.IdFatt == updItem.IdFatt).Count();
                    // clono il record
                    RigheFattModel newRec = new RigheFattModel()
                    {
                        IdxDoc = updItem.IdxDoc,
                        Descrizione = updItem.Descrizione,
                        Um = updItem.Um,
                        PrezzoUm = updItem.PrezzoUm,
                        Qta = updItem.Qta,
                        Sconto = updItem.Sconto,
                        CIva = updItem.CIva,
                        NoRitenuta = updItem.NoRitenuta,
                        IdFatt = updItem.IdFatt,
                        Valuta = updItem.Valuta,
                        numRiga = numRow + 1
                    };

                    dbCtx.DbSetRigheFatt.Add(newRec);
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RigaDocClona{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Elimina una riga Doc
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool RigaDocDelete(int RowId)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    var item2del = dbCtx
                        .DbSetRigheFatt
                        .Where(x => x.Id == RowId)
                        .FirstOrDefault();
                    if (item2del != null)
                    {
                        dbCtx.DbSetRigheFatt.Remove(item2del);
                        dbCtx.SaveChanges();
                        done = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RigaDocDelete{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Aggiorna un item RigaDoc cambiando solamente il numero di riga (e spostando un eventuale
        /// altro elemento al suo posto)
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool RigaDocMove(RigheFattModel updItem, int delta)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    var currData = dbCtx
                        .DbSetRigheFatt
                        .Where(x => x.Id == updItem.Id)
                        .FirstOrDefault();
                    // sposto riga attuale (SE spostabile...)
                    if (currData != null)
                    {
                        int newNum = currData.numRiga + delta;
                        // verifico di essere entro i limiti
                        newNum = newNum > 0 ? newNum : 1;
                        // cerco altro record x scambiare
                        var otherRow = dbCtx
                                        .DbSetRigheFatt
                                        .Where(x => x.IdFatt == updItem.IdFatt && x.numRiga == newNum)
                                        .FirstOrDefault();
                        // sposto altra riga (SE trovata)
                        if (otherRow != null)
                        {
                            otherRow.numRiga = currData.numRiga;
                            dbCtx.Entry(otherRow).State = EntityState.Modified;
                        }
                        // sposto riga attuale
                        currData.numRiga = newNum;
                        // segno modificato!
                        dbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RigaDocMove{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Aggiorna un item RigaDoc
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool RigaDocUpdate(RigheFattModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    var currData = dbCtx
                        .DbSetRigheFatt
                        .Where(x => x.Id == updItem.Id)
                        .FirstOrDefault();
                    if (currData != null)
                    {
                        currData.IdxDoc = updItem.IdxDoc;
                        currData.Descrizione = updItem.Descrizione;
                        currData.Um = updItem.Um;
                        currData.PrezzoUm = updItem.PrezzoUm;
                        currData.Qta = updItem.Qta;
                        currData.Sconto = updItem.Sconto;
                        currData.CIva = updItem.CIva;
                        currData.NoRitenuta = updItem.NoRitenuta;
                        currData.numRiga = updItem.numRiga;
                        // segno modificato!
                        dbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx
                            .DbSetRigheFatt
                            .Add(updItem);
                    }
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in RigaDocUpdate{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        public List<AnagTipoDocModel> TipoDocGetAll()
        {
            List<AnagTipoDocModel> dbResult = new List<AnagTipoDocModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetTipoDoc
                            .Where(x => x.IsActive)
                            .OrderBy(x => x.Descrizione)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Clona un item Customer
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool VATClona(VatModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    // clono il record
                    VatModel newRec = new VatModel()
                    {
                        Descrizione = $"CLONE - {updItem.Descrizione}",
                        Enabled = true,
                        Iva = updItem.Iva,
                        SplitPay = updItem.SplitPay,
                        id_ext = 0
                    };

                    dbCtx.DbSetVat.Add(newRec);
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in VATClona{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Elenco VAT che possono essere eliminati (NIENTE doc collegati)
        /// </summary>
        /// <returns></returns>
        public List<VatModel> VATDeletable()
        {
            List<VatModel> dbResult = new List<VatModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetVat
                            .Where(x => (x.RigheFattNav.Count == 0))
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elimina un item Customer
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool VATDelete(VatModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    var item2del = dbCtx
                                .DbSetVat
                                .Where(x => x.IdxVat == updItem.IdxVat)
                                .FirstOrDefault();
                    if (item2del != null)
                    {
                        dbCtx
                            .DbSetVat
                            .Remove(item2del);
                        dbCtx.SaveChanges();
                        done = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in VATDelete{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Elenco VAT / CIva
        /// </summary>
        /// <param name="searchVal"></param>
        /// <returns></returns>
        public List<VatModel> VATGetAll(string searchVal)
        {
            List<VatModel> dbResult = new List<VatModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetVat
                            .Where(x => (x.Descrizione.Contains(searchVal) || string.IsNullOrEmpty(searchVal)))
                            .OrderBy(x => x.Descrizione)
                            .ToList();
            }
            return dbResult;
        }

        public List<VatModel> VATGetAll()
        {
            List<VatModel> dbResult = new List<VatModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                              .DbSetVat
                              .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Ricerca VAT / CIva da idx
        /// </summary>
        /// <param name="idxVat"></param>
        /// <returns></returns>
        public VatModel? VATGetByKey(int idxVat)
        {
            VatModel? dbResult = new VatModel();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                            .DbSetVat
                            .Where(x => (x.IdxVat == idxVat))
                            .FirstOrDefault();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco VAT / CIva che richiedono sync tramite stored
        /// </summary>
        /// <param name="idxTipoDoc">Tipo doc, 0: tutti</param>
        /// <param name="anno">Anno doc, 0: tutti</param>
        /// <param name="notUpl">Indica se solo non caricati (1 = NON caricati)</param>
        /// <returns></returns>
        public List<VatModel> VATToSync(int idxTipoDoc, int anno, bool notUpl)
        {
            List<VatModel> dbResult = new List<VatModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                SqlParameter IdxTipo = new SqlParameter("@idxTipo", idxTipoDoc);
                SqlParameter Anno = new SqlParameter("@anno", anno);
                SqlParameter NotUpl = new SqlParameter("@notUpl", notUpl);

                dbResult = dbCtx
                              .DbSetVat
                              .FromSqlRaw("EXEC stp_VAT_getByAnno @idxTipo,@anno,@notUpl", IdxTipo, Anno, NotUpl)
                              .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiorna un record VAT / CIva
        /// </summary>
        /// <param name="updItem"></param>
        /// <returns></returns>
        public bool VATUpdate(VatModel updItem)
        {
            bool done = false;
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    var currData = dbCtx
                        .DbSetVat
                        .Where(x => x.IdxVat == updItem.IdxVat)
                        .FirstOrDefault();
                    if (currData != null)
                    {
                        currData.Iva = updItem.Iva;
                        currData.Descrizione = updItem.Descrizione;
                        currData.Enabled = updItem.Enabled;
                        currData.SplitPay = updItem.SplitPay;
                        currData.id_ext = updItem.id_ext;
                        dbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx
                            .DbSetVat
                            .Add(updItem);
                    }
                    dbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in VATUpdate{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Elenco AccoDoc (Accounting Document) filtrati
        /// </summary>
        /// <param name="anno">anno di riferimento (0: tutti)</param>
        /// <param name="idxCli">Idx Cliente (0: tutti)</param>
        /// <param name="idxTipoDoc">tipo Doc (0: tutti)</param>
        /// <param name="idxGruppo">Idx Gruppo (0: tutti)</param>
        /// <param name="ragSoc">tipo Doc (*: tutti)</param>
        /// <param name="inizio">Data inizio ricerca (emissione)</param>
        /// <param name="fine">Data inizio ricerca (emissione)</param>
        /// <param name="aperta">0: tutto / 1: aperta, NON pagata</param>
        /// <param name="searchVal">Ricerca libera</param>
        /// <returns></returns>
        public List<vDocsExplModel> VDocExplGetFilt(int anno, int idxCli, int idxTipoDoc, string idxGruppo, string ragSoc, DateTime inizio, DateTime fine, int aperta)
        {
            List<vDocsExplModel> dbResult = new List<vDocsExplModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                SqlParameter Anno = new SqlParameter("@anno", anno);
                SqlParameter IdxCli = new SqlParameter("@idxCli", idxCli);
                SqlParameter IdxTipo = new SqlParameter("@idxTipo", idxTipoDoc);
                SqlParameter IdxGruppo = new SqlParameter("@idxGruppo", idxGruppo);
                SqlParameter RagSoc = new SqlParameter("@ragSoc", ragSoc);
                SqlParameter DataFrom = new SqlParameter("@inizio", inizio);
                SqlParameter DataTo = new SqlParameter("@fine", fine);
                SqlParameter Aperta = new SqlParameter("@aperta", aperta);

                dbResult = dbCtx
                              .DbSetDocExpl
                              .FromSqlRaw("EXEC stp_docs_getFilt @anno,@idxCli,@idxTipo,@idxGruppo,@ragSoc,@inizio,@fine,@aperta", Anno, IdxCli, IdxTipo, IdxGruppo, RagSoc, DataFrom, DataTo, Aperta)
                              //.FromSqlRaw("EXEC stp_docs_getFilt @anno,@idxCli,@idxTipo,@idxGruppo,@ragSoc,@inizio,@fine,@aperta,@search", Anno, IdxCli, IdxTipo, IdxGruppo, RagSoc, DataFrom, DataTo, Aperta, Search)
                              .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Fatture Aperte filtrate
        /// </summary>
        /// <param name="showAll">0 = solo scadute ad oggi / 1 = Tutte</param>
        /// <param name="idxCli">Idx Cliente (0: tutti)</param>
        /// <param name="idxGruppo">Idx Gruppo (0: tutti)</param>
        /// <param name="idxDoc">IDX Doc (0: tutti)</param>
        /// <param name="idxTipoDoc">tipo Doc (0: tutti)</param>
        /// <returns></returns>
        public List<vFattAperteModel> VFattAperteGetFilt(bool showAll, int idxCli, string idxGruppo, int idxDoc, int idxTipoDoc)
        {
            List<vFattAperteModel> dbResult = new List<vFattAperteModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                SqlParameter ShowAll = new SqlParameter("@showAll", showAll);
                SqlParameter IdxCli = new SqlParameter("@idxCli", idxCli);
                SqlParameter IdxGruppo = new SqlParameter("@idxGruppo", idxGruppo);
                SqlParameter IdxDoc = new SqlParameter("@idxDoc", idxDoc);
                SqlParameter IdxTipoDoc = new SqlParameter("@idxTipo", idxTipoDoc);

                dbResult = dbCtx
                              .DbSetFattAperte
                              .FromSqlRaw("EXEC stp_fattureAperte @showAll,@idxCli,@idxGruppo,@idxDoc,@idxTipo", ShowAll, IdxCli, IdxGruppo, IdxDoc, IdxTipoDoc)
                              .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero oggetto vocabolario diviso x lingua
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, Dictionary<string, string>> VocLemmaGetAll()
        {
            Dictionary<string, Dictionary<string, string>> DTOResult = new Dictionary<string, Dictionary<string, string>>();
            using (SHERPAFattContext localDbCtx = new SHERPAFattContext(_configuration))
            {
                try
                {
                    List<string> lingue = new List<string>()
                    {
                        "IT",
                        "EN"
                    };

                    // extracting entire set
                    var allVoc = localDbCtx
                                .DbSetVocabolario
                                .AsNoTracking()
                                .ToList();

                    foreach (var lingua in lingue)
                    {
                        Dictionary<string, string> dict = new Dictionary<string, string>();
                        foreach (var lemma in allVoc.Where(x => x.Lingua == lingua))
                        {
                            dict.Add(lemma.Lemma, lemma.Traduzione);
                        }
                        DTOResult.Add(lingua, dict);
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in VocLemmaGetAll:{Environment.NewLine}{exc}");
                }
            }
            return DTOResult;
        }

        public List<vSelCliModel> VSelCliGetAll()
        {
            List<vSelCliModel> dbResult = new List<vSelCliModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                        .DbSetSelCli
                        .OrderBy(x => x.label)
                        .ToList();
            }
            return dbResult;
        }

        public List<vSelGruppiModel> VSelGruppiGetAll()
        {
            List<vSelGruppiModel> dbResult = new List<vSelGruppiModel>();
            using (SHERPAFattContext dbCtx = new SHERPAFattContext(_configuration))
            {
                dbResult = dbCtx
                        .DbSetSelGruppi
                        .OrderBy(x => x.label)
                        .ToList();
            }
            return dbResult;
        }

        #endregion Public Methods

        #region Private Fields

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

        #endregion Private Fields
    }
}