using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using NLog;
using System.Data;
using WebDoorCreator.Data.DbModels;
using WebDoorCreator.Data.DTO;

namespace WebDoorCreator.Data.Controllers
{
    public class WebDoorCreatorController : IDisposable
    {
        #region Public Constructors

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

        #endregion Public Constructors

        #region Public Methods

        /// <summary>
        /// Adding a new company
        /// </summary>
        /// <param name="addEditRec">Record to edit or add</param>
        /// <returns></returns>
        public async Task<bool> CompanyAddMod(CompanyModel addEditRec)
        {
            bool fatto = false;
            //List<ItemModel> dbResult = new List<ItemModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetCompany
                                        .Where(x => x.CompanyId == addEditRec.CompanyId)
                                        .FirstOrDefault();
                    if (currRec != null) //if is not null edit the record found
                    {
                        currRec.CompanyId = addEditRec.CompanyId;
                        currRec.CompanyName = addEditRec.CompanyName;
                        currRec.CompanyExtCode = addEditRec.CompanyExtCode;
                        currRec.Address = addEditRec.Address;
                        currRec.City = addEditRec.City;
                        currRec.State = addEditRec.State;
                        currRec.ZipCode = addEditRec.ZipCode;
                        currRec.VAT = addEditRec.VAT;
                        currRec.PrivateNote = addEditRec.PrivateNote;
                        currRec.CompanyToken = addEditRec.CompanyToken;
                        localDbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    else //if is null add the record as new in the table
                    {
                        localDbCtx
                            .DbSetCompany
                            .Add(addEditRec);
                    }
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante CompanyAddMod: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Company list (All)
        /// </summary>
        /// <returns></returns>
        public List<CompanyModel> CompanyGetByKey(int id)
        {
            List<CompanyModel> dbResult = new List<CompanyModel>();
            // cerco su DB recuperando set di dati....
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    if (id == 0)
                    {
                        // extracting entire set
                        dbResult = localDbCtx
                                    .DbSetCompany
                                    .OrderBy(x => x.CompanyName)
                                    .ToList();
                    }
                    else
                    {
                        var risultato = localDbCtx
                                .DbSetCompany
                                .Where(x => x.CompanyId == id)
                                .OrderBy(x => x.CompanyName)
                                .ToList();
                        if (risultato != null)
                        {
                            dbResult = risultato;
                        }
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in CompanyAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Adding a new Compo
        /// </summary>
        /// <param name="addRec">Record to add</param>
        /// <returns></returns>
        public async Task<bool> CompoAdd(HardwareModel addRec)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    localDbCtx
                        .DbSetHardware
                        .Add(addRec);
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante CompoAdd: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <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 (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetConfig
                            .OrderBy(x => x.chiave)
                            .ToList();
            }
            return dbResult;
        }

        public void Dispose()
        {
            // Clear database context
            //Log.Info("Dispose di GWMSController");
        }

        /// <summary>
        /// Remove door
        /// </summary>
        /// <param name="DoorId">ID da eliminare</param>
        /// <returns></returns>
        public async Task<bool> DoorDelete(int DoorId)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetDoor
                                        .Where(x => x.DoorId == DoorId)
                                        .FirstOrDefault();
                    // procedo solo se c'è
                    if (currRec != null)
                    {
                        var newDbRec = localDbCtx
                                     .DbSetDoor
                                     .Remove(currRec);
                        await localDbCtx.SaveChangesAsync();
                        fatto = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorDelete: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Getting door list 2 delete
        /// </summary>
        /// <returns></returns>
        public List<DoorModel>? DoorGet2Del()
        {
            List<DoorModel>? dbResult = new List<DoorModel>();
            // retrieving data from db
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx.DbSetDoor
                                .Where(x => x.isLogicDel)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in DoorGet2Del:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Getting door data by key
        /// </summary>
        /// <returns></returns>
        public DoorModel? DoorGetByKey(int doorId)
        {
            DoorModel? dbResult = new DoorModel();
            // retrieving data from db
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx.DbSetDoor
                                .Where(x => x.DoorId == doorId)
                                .Include(o => o.OrderNav)
                                .OrderBy(x => x.DoorId)
                                .AsNoTracking()
                                .FirstOrDefault();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in DoorGetByKey:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Adding a new door
        /// </summary>
        /// <param name="newRec">Record to edit or add</param>
        /// <returns></returns>
        public async Task<int> DoorInsert(DoorModel newRec)
        {
            int newId = 0;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetDoor
                                        .Where(x => x.DoorId == newRec.DoorId)
                                        .FirstOrDefault();
                    // procedo solo se non c'è già
                    if (currRec == null)
                    {
                        var newDbRec = localDbCtx
                                     .DbSetDoor
                                     .Add(newRec);
                        await localDbCtx.SaveChangesAsync();
                        newId = newRec.DoorId;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorInsert: {Environment.NewLine}{exc}");
                }
            }
            return newId;
        }

        /// <summary>
        /// Delete doorOp instance
        /// </summary>
        /// <param name="modOpRec">Record to delete</param>
        /// <returns></returns>
        public async Task<bool> DoorOpDelete(DoorOpModel modOpRec)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetDoorOp
                                        .Where(x => x.DoorId == modOpRec.DoorId && x.DoorOpId == modOpRec.DoorOpId)
                                        .FirstOrDefault();
                    //if is not null edit the record found
                    if (currRec != null)
                    {
                        var recToRem = localDbCtx
                            .DbSetDoorOp
                            .Remove(currRec);
                        await localDbCtx.SaveChangesAsync();
                        fatto = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorOpDelete: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Delete doorOp list
        /// </summary>
        /// <param name="modOpRec">Record to delete</param>
        /// <returns></returns>
        public async Task<bool> DoorOpDeleteRange(List<DoorOpModel> listOpRec)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    localDbCtx
                        .DbSetDoorOp
                        .RemoveRange(listOpRec);
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorOpDeleteRange: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Retrieving current data from door
        /// </summary>
        /// <returns></returns>
        public List<DoorOpModel> DoorOpGetByDoorId(int doorId)
        {
            List<DoorOpModel> dbResult = new List<DoorOpModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx
                                .DbSetDoorOp
                                .Where(x => x.DoorId == doorId)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in DoorOpGetByDoorId:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Retrieving current door operations by id
        /// </summary>
        /// <returns></returns>
        public DoorOpModel DoorOpGetById(int doorOpId)
        {
            DoorOpModel dbResult = new DoorOpModel();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    var temp = localDbCtx
                                .DbSetDoorOp
                                .Where(x => x.DoorOpId == doorOpId)
                                .FirstOrDefault();

                    if (temp != null)
                    {
                        dbResult = temp;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in DoorOpGetById:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Adding door's OP
        /// </summary>
        /// <param name="newOpRec">Records to add</param>
        /// <returns></returns>
        public async Task<bool> DoorOpInsert(List<DoorOpModel> newOpRec)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    localDbCtx
                        .DbSetDoorOp
                        .AddRange(newOpRec);
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorInsert: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Adding a new DoorOpType
        /// </summary>
        /// <param name="addRec">Record to add</param>
        /// <returns></returns>
        public async Task<bool> DoorOpTypeAdd(DoorOpTypeModel addRec)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    localDbCtx
                        .DbSetDoorOpType
                        .Add(addRec);
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorOpTypeAdd:{Environment.NewLine}{exc}");
                }
            }

            return fatto;
        }

        /// <summary>
        /// Adding new DoorOpType data
        /// </summary>
        /// <param name="addRec">Record to add</param>
        /// <returns></returns>
        public async Task<bool> DoorOpTypeAdd(List<DoorOpTypeTempModel> addList)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // stored di reset ListValues
                    var storedRes = localDbCtx
                                    .Database
                                    .ExecuteSqlRaw("exec dbo.stp_DoorOpType_Prepare");
                    await localDbCtx.SaveChangesAsync();

                    // import massivo dati in tab temp
                    localDbCtx
                        .DbSetDoorOpTypeTemp
                        .AddRange(addList);
                    await localDbCtx.SaveChangesAsync();

                    // stored di merge dati in DoorOpType
                    storedRes = localDbCtx
                                    .Database
                                    .ExecuteSqlRaw("exec dbo.stp_DoorOpType_Import");

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

        /// <summary>
        /// Adding a new DoorOpType
        /// </summary>
        /// <param name="listRec">Record to add</param>
        /// <returns></returns>
        public async Task<bool> DoorOpTypeAddRange(List<DoorOpTypeModel> listRec)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    localDbCtx
                        .DbSetDoorOpType
                        .AddRange(listRec);
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorOpTypeAddRange:{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Retrieving data from door operation type table
        /// </summary>
        /// <returns></returns>
        public List<DoorOpTypeModel> DoorOpTypeGetAll()
        {
            List<DoorOpTypeModel> dbResult = new List<DoorOpTypeModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx
                                .DbSetDoorOpType
                                .OrderBy(x => x.DoorOpTypId)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in DoorOpTypeGetAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Retrieving data from door operation type table filtered by path
        /// </summary>
        /// <returns></returns>
        public List<DoorOpTypeModel> DoorOpTypeGetByPath(string path)
        {
            List<DoorOpTypeModel> dbResult = new List<DoorOpTypeModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    dbResult = localDbCtx
                                .DbSetDoorOpType
                                .Where(x => x.OpCode.StartsWith(path))
                                .OrderBy(x => x.DoorOpTypId)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in DoorOpTypeGetByPath:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public async Task<List<DoorOpTypeModel>> DoorOpTypeGetDefault()
        {
            List<DoorOpTypeModel> dbResult = new List<DoorOpTypeModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    dbResult = localDbCtx
                                 .DbSetDoorOpType
                                 .Where(x => x.IsDefault)
                                 .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorOpTypeGetDefault: {Environment.NewLine}{exc}");
                }
            }
            await Task.Delay(1);
            return dbResult;
        }

        /// <summary>
        /// Retrieving data from door operation type table filtered by Hw Code
        /// </summary>
        /// <returns></returns>
        public List<DoorOpTypeModel> DoorOpTypeGetFiltered(string hwCode, int parentId)
        {
            List<DoorOpTypeModel> dbResult = new List<DoorOpTypeModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    if (hwCode == "*" && parentId == -1)
                    {
                        // extracting entire set
                        dbResult = localDbCtx
                                    .DbSetDoorOpType
                                    .OrderBy(x => x.DoorOpTypId)
                                    .ToList();
                    }
                    else if (hwCode != "*" && parentId == -1)
                    {
                        dbResult = localDbCtx
                                    .DbSetDoorOpType
                                    .Where(x => x.HwCode == hwCode)
                                    .OrderBy(x => x.DoorOpTypId)
                                    .ToList();
                    }
                    else if (hwCode == "*" && parentId >= 0)
                    {
                        dbResult = localDbCtx
                                    .DbSetDoorOpType
                                    .Where(x => x.ParentId == parentId)
                                    .OrderBy(x => x.DoorOpTypId)
                                    .ToList();
                    }
                    else if (hwCode != "*" && parentId >= 0)
                    {
                        dbResult = localDbCtx
                                    .DbSetDoorOpType
                                    .Where(x => (x.ParentId == parentId) && x.HwCode == hwCode)
                                    .OrderBy(x => x.DoorOpTypId)
                                    .ToList();
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in DoorOpTypeGetAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Modifying doorOpType
        /// </summary>
        /// <param name="addEditRec">Record to edit</param>
        /// <returns></returns>
        public async Task<bool> DoorOpTypeUpdate(DoorOpTypeModel addEditRec)
        {
            bool fatto = false;
            //List<ItemModel> dbResult = new List<ItemModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetDoorOpType
                                        .Where(x => x.DoorOpTypId == addEditRec.DoorOpTypId)
                                        .FirstOrDefault();
                    //if is not null edit the record found
                    if (currRec != null)
                    {
                        currRec.ParentId = addEditRec.ParentId;
                        currRec.OpCode = addEditRec.OpCode;
                        currRec.Description = addEditRec.Description;
                        currRec.IsDefault = addEditRec.IsDefault;
                        currRec.HasHw = addEditRec.HasHw;
                        currRec.IsConcrete = addEditRec.IsConcrete;
                        currRec.HwCode = addEditRec.HwCode;
                        currRec.HwDescription = addEditRec.HwDescription;
                        currRec.DisplayUrl = addEditRec.DisplayUrl;
                        currRec.FPath = addEditRec.FPath;
                        currRec.UnitCost = addEditRec.UnitCost;
                        currRec.ExtOpCode = addEditRec.ExtOpCode;
                        currRec.ExtDescript = addEditRec.ExtDescript;
                        currRec.Rev = addEditRec.Rev;
                        currRec.ValidFrom = addEditRec.ValidFrom;
                        currRec.ValidUntil = addEditRec.ValidUntil;
                        currRec.FileMD5 = addEditRec.FileMD5;
                        currRec.FileDim = addEditRec.FileDim;
                        currRec.LastMod = addEditRec.LastMod;
                        localDbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    //if is null add the record as new in the table
                    else
                    {
                        localDbCtx
                            .DbSetDoorOpType
                            .Add(addEditRec);
                    }
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorOpTypeUpdate: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Update door's OP
        /// </summary>
        /// <param name="modOpRec">Records to add</param>
        /// <returns></returns>
        public async Task<bool> DoorOpUpdate(List<DoorOpModel> modOpRec)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    foreach (var item in modOpRec)
                    {
                        var currRec = localDbCtx
                                            .DbSetDoorOp
                                            .Where(x => x.DoorId == item.DoorId && x.DoorOpId == item.DoorOpId)
                                            .FirstOrDefault();
                        if (currRec != null) //if is not null edit the record found
                        {
                            currRec.JsoncActVal = item.JsoncActVal;
                            currRec.JsoncConfigVal = item.JsoncConfigVal;
                            currRec.userConfirm = item.userConfirm;
                            currRec.DtConfirm = item.DtConfirm;
                            localDbCtx.Entry(currRec).State = EntityState.Modified;
                        }
                    }
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorOpUpdate: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Getting door data by numRec
        /// </summary>
        /// <returns></returns>
        public List<DoorModel> DoorsGetByOrderId(int orderId)
        {
            List<DoorModel> dbResult = new List<DoorModel>();
            // retrieving data from db
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx
                                .DbSetDoor
                                .Where(x => x.OrderId == orderId)
                                .Include(o => o.OrderNav)
                                .OrderBy(x => x.DoorId)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in DoorsGetByOrderId:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Getting door list (last numRec)
        /// </summary>
        /// <returns></returns>
        public List<DoorModel> DoorsGetLast(int numRec)
        {
            List<DoorModel> dbResult = new List<DoorModel>();
            // retrieving data from db
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx
                                .DbSetDoor
                                .Include(o => o.OrderNav)
                                .OrderByDescending(x => x.DoorId)
                                .Take(numRec)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in DoorsGetLast:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Update costing for dictionary of doors
        /// </summary>
        /// <param name="DoorUnitCosts"></param>
        /// <returns></returns>
        public async Task<bool> DoorUpdateCosts(Dictionary<int, decimal> DoorUnitCosts)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // ciclo x ogni porta...

                    foreach (var item in DoorUnitCosts)
                    {
                        var currRec = localDbCtx
                                            .DbSetDoor
                                            .Where(x => x.DoorId == item.Key)
                                            .FirstOrDefault();
                        if (currRec != null) //if is not null edit the record found
                        {
                            currRec.UnitCost = item.Value;
                            localDbCtx.Entry(currRec).State = EntityState.Modified;
                        }
                    }
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorUpdateCosts: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Modifying or adding a new door
        /// </summary>
        /// <param name="addEditRec">Record to edit or add</param>
        /// <returns></returns>
        public async Task<bool> DoorUpsert(DoorModel addEditRec)
        {
            bool fatto = false;
            //List<ItemModel> dbResult = new List<ItemModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetDoor
                                        .Where(x => x.DoorId == addEditRec.DoorId)
                                        .FirstOrDefault();
                    //if is not null edit the record found
                    if (currRec != null)
                    {
                        currRec.Quantity = addEditRec.Quantity;
                        currRec.DoorExtCode = addEditRec.DoorExtCode;
                        currRec.DoorDescript = addEditRec.DoorDescript;
                        currRec.ParentId = addEditRec.ParentId;
                        currRec.isLogicDel = addEditRec.isLogicDel;
                        currRec.UnitCost = addEditRec.UnitCost;
                        localDbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    //if is null add the record as new in the table
                    else
                    {
                        localDbCtx
                            .DbSetDoor
                            .Add(addEditRec);
                    }
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante DoorUpsert: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Estraggo tutte le lingue disponibili per questa applicazione
        /// </summary>
        /// <returns></returns>
        public List<LanguageModel> LanguageGetAll()
        {
            List<LanguageModel> dbResult = new List<LanguageModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx
                                .DbSetLanguages
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in LanguageGetAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Adding new list value set
        /// </summary>
        /// <param name="addRec">Record to add</param>
        /// <returns></returns>
        public async Task<bool> ListValuesAdd(List<ListValuesTempModel> addList)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // stored di reset ListValues
                    var storedRes = localDbCtx
                                    .Database
                                    .ExecuteSqlRaw("exec dbo.stp_ListVal_Prepare");
                    await localDbCtx.SaveChangesAsync();

                    // import massivo dati in tab temp
                    localDbCtx
                        .DbSetValuesTemp
                        .AddRange(addList);
                    await localDbCtx.SaveChangesAsync();

                    // stored di merge dati in ListVal
                    storedRes = localDbCtx
                                    .Database
                                    .ExecuteSqlRaw("exec dbo.stp_ListVal_Import");

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

        /// <summary>
        /// ListValues list (All)
        /// </summary>
        /// <returns></returns>
        public List<ListValuesModel> ListValuesGetAll(string tableName, string fieldName)
        {
            List<ListValuesModel> dbResult = new List<ListValuesModel>();
            // cerco su DB recuperando set di dati....
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    if (tableName != "*" && fieldName != "*")
                    {
                        // extracting entire set
                        dbResult = localDbCtx
                                    .DbSetValues
                                    .Where(x => (x.TableName == tableName) && (x.FieldName == fieldName))
                                    .OrderBy(x => x.Ordinal)
                                    .ToList();
                    }
                    else if (tableName == "*" && fieldName != "*")
                    {
                        dbResult = localDbCtx
                                    .DbSetValues
                                    .Where(x => (x.FieldName == fieldName))
                                    .OrderBy(x => x.Ordinal)
                                    .ToList();
                    }
                    else if (tableName != "*" && fieldName == "*")
                    {
                        dbResult = localDbCtx
                        .DbSetValues
                                    .Where(x => (x.TableName == tableName))
                                    .OrderBy(x => x.Ordinal)
                                    .ToList();
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in ListValuesGetAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Adding a new order
        /// </summary>
        /// <param name="addRec">Record to add</param>
        /// <returns>OrderId</returns>
        public async Task<int> OrderAdd(OrderModel addRec)
        {
            int newOrdId = 0;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    localDbCtx
                        .DbSetOrders
                        .Add(addRec);
                    await localDbCtx.SaveChangesAsync();
                    newOrdId = addRec.OrderId;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OrderAdd: {Environment.NewLine}{exc}");
                }
            }
            return newOrdId;
        }

        /// <summary>
        /// Duplicazione di un ordine da SRC a DEST (Doors + DoorOp)
        /// </summary>
        /// <param name="OrdIdSrc"></param>
        /// <param name="OrdIdDest"></param>
        /// <param name="UsrIdMod"></param>
        /// <returns></returns>
        public async Task<bool> OrderDuplicate(int OrdIdSrc, int OrdIdDest, string UsrIdMod)
        {
            await Task.Delay(1);
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var OrderIdSrc = new SqlParameter("@OrderIdSrc", OrdIdSrc);
                    var OrderIdDest = new SqlParameter("@OrderIdDest", OrdIdDest);
                    var UserIdMod = new SqlParameter("@UserIdMod", UsrIdMod);

                    // stored di cloning dati ordine
                    var storedRes = localDbCtx
                                    .Database
                                    .ExecuteSqlRaw("exec dbo.stp_OrderClone @OrderIdSrc, @OrderIdDest, @UserIdMod", OrderIdSrc, OrderIdDest, UserIdMod);

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

        public List<OrderModel> OrderGetByCompStatus(int CompanyId, int StatusId)
        {
            List<OrderModel> dbResult = new List<OrderModel>();
            using (var dbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var rawData = dbCtx
                                .DbSetOrders
                                .Where(x => x.CompanyId == CompanyId && x.Status == StatusId)
                                .AsNoTracking()
                                .ToList();
                    if (rawData != null)
                    {
                        dbResult = rawData;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in OrderGetByCompStatus:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public OrderModel OrderGetByKey(int orderId)
        {
            OrderModel dbResult = new OrderModel();
            using (var dbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var rawData = dbCtx
                                .DbSetOrders
                                .Where(x => x.OrderId == orderId)
                                .Include(c => c.CompanyNav)
                                .AsNoTracking()
                                .FirstOrDefault();
                    if (rawData != null)
                    {
                        dbResult = rawData;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in OrderGetByKey:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Remove order
        /// </summary>
        /// <param name="OrdId">Record to add</param>
        /// <returns></returns>
        public async Task<bool> OrderRem(int OrdId)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var selRec = localDbCtx
                                    .DbSetOrders
                                    .Where(x => x.OrderId == OrdId)
                                    .FirstOrDefault();
                    if (selRec != null)
                    {
                        localDbCtx.DbSetOrders.Remove(selRec);
                        await localDbCtx.SaveChangesAsync();
                        fatto = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OrderRem: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Order status
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="orderStatus"></param>
        /// <param name="dataFrom"></param>
        /// <param name="dataTo"></param>
        /// <returns></returns>
        public List<OrderStatusViewModel> OrderStatusGetAll(int companyId, int orderStatus, DateTime dataFrom, DateTime dataTo)
        {
            List<OrderStatusViewModel> dbResult = new List<OrderStatusViewModel>();
            using (var dbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var CompanyId = new SqlParameter("@CompanyId", companyId);
                    var OrderStatus = new SqlParameter("@OrderStatus", orderStatus);
                    var DataFrom = new SqlParameter("@DataFrom", dataFrom);
                    var DataTo = new SqlParameter("@DataTo", dataTo);

                    dbResult = dbCtx
                                .DbSetOrderStatus
                                .FromSqlRaw("exec dbo.stp_OrderList_getFilt @CompanyId, @OrderStatus, @DataFrom, @DataTo", CompanyId, OrderStatus, DataFrom, DataTo)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in GetOrderStatus:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Updating an order code/description
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="newCode"></param>
        /// <param name="newDescript"></param>
        /// <returns></returns>
        public async Task<bool> OrderUpdateDescript(int orderId, string newCode, string newDescript)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetOrders
                                        .Where(x => x.OrderId == orderId)
                                        .FirstOrDefault();
                    //if is not null edit the record found
                    if (currRec != null)
                    {
                        currRec.OrderExtCode = newCode;
                        currRec.OrderDescript = newDescript;
                        localDbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OrderUpdateDescript: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Updating an order promised date
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="dateProm"></param>
        /// <returns></returns>
        public async Task<bool> OrderUpdatePromDate(int orderId, DateTime dateProm)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetOrders
                                        .Where(x => x.OrderId == orderId)
                                        .FirstOrDefault();
                    //if is not null edit the record found
                    if (currRec != null)
                    {
                        currRec.DateProm = dateProm;
                        localDbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OrderUpdatePromDate: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Updating an order status
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="newStatus"></param>
        /// <returns></returns>
        public async Task<bool> OrderUpdateStatus(int orderId, int newStatus)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetOrders
                                        .Where(x => x.OrderId == orderId)
                                        .FirstOrDefault();
                    //if is not null edit the record found
                    if (currRec != null)
                    {
                        currRec.Status = newStatus;
                        // controllo gli stati speciali...
                        if (newStatus == 10)
                        {
                            currRec.DateOrd = DateTime.MinValue;
                        }
                        else if (newStatus == 40)
                        {
                            currRec.DateOrd = DateTime.Now;
                        }
                        else if (newStatus == 70)
                        {
                            currRec.DateDelivery = DateTime.Now;
                        }
                        localDbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante OrderUpdateStatus: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Adding a new graphic param
        /// </summary>
        /// <param name="addRec">Record to add</param>
        /// <returns></returns>
        public async Task<bool> ParamAdd(GraphicParamsModel addRec)
        {
            bool fatto = false;
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    localDbCtx
                        .DbSetGraphicParams
                        .Add(addRec);
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ParamAdd: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Getting all the graphic parameters by hw id
        /// </summary>
        /// <param name="hwId">Hardware's id to search for</param>
        /// <returns></returns>
        public List<GraphicParamsModel> ParamGetByHwId(int hwId)
        {
            List<GraphicParamsModel> dbResult = new List<GraphicParamsModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    dbResult = localDbCtx
                        .DbSetGraphicParams
                        .Where(x => x.compoId == hwId)
                        .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ParamGetByHwId: {Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Order Data for report printing
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public List<PrtRepOrderModel> PreRepOrderGetByKey(int orderId)
        {
            List<PrtRepOrderModel> dbResult = new List<PrtRepOrderModel>();
            using (var dbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var OrderId = new SqlParameter("@OrderId", orderId);

                    dbResult = dbCtx
                                .DbSetPrtRepOrder
                                .FromSqlRaw("exec dbo.stp_prt_OrderReportExpl @OrderId", OrderId)
                                .AsNoTracking()
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in PreRepOrderGetByKey:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Retrieving data from roles table
        /// </summary>
        /// <returns></returns>
        public List<AspNetRoles> RolesGetAll()
        {
            List<AspNetRoles> dbResult = new List<AspNetRoles>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx
                                .DbSetRoles
                                .OrderBy(x => x.Name)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in GetRolesAll:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// truncate tables for testing purposes
        /// </summary>
        /// <returns></returns>
        public bool TestTablesTruncate()
        {
            bool dbResult = false;
            using (var dbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    dbCtx
                        .Database
                        .ExecuteSqlRaw("EXEC man.[stp_TruncTables]");

                    dbResult = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in TablesTruncate:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Adding a new User
        /// </summary>
        /// <param name="addEditRec">Record to edit or add</param>
        /// <returns></returns>
        public async Task<bool> UserAddMod(AspNetUsers addEditRec)
        {
            bool fatto = false;
            //List<ItemModel> dbResult = new List<ItemModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    var currRec = localDbCtx
                                        .DbSetUsers
                                        .Where(x => x.Id == addEditRec.Id)
                                        .FirstOrDefault();
                    //if is not null edit the record found
                    if (currRec != null)
                    {
                        currRec.Id = addEditRec.Id;
                        currRec.UserName = addEditRec.UserName;
                        currRec.NormalizedUserName = addEditRec.NormalizedUserName;
                        currRec.Email = addEditRec.Email;
                        currRec.EmailConfirmed = addEditRec.EmailConfirmed;
                        currRec.LockoutEnabled = addEditRec.LockoutEnabled;
                        currRec.LockoutEnd = addEditRec.LockoutEnd;
                        currRec.ConcurrencyStamp = addEditRec.ConcurrencyStamp;
                        currRec.SecurityStamp = addEditRec.SecurityStamp;
                        currRec.AccessFailedCount = addEditRec.AccessFailedCount;
                        currRec.PhoneNumberConfirmed = addEditRec.PhoneNumberConfirmed;
                        currRec.PhoneNumber = addEditRec.PhoneNumber;
                        currRec.TwoFactorEnabled = addEditRec.TwoFactorEnabled;
                        localDbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    //if is null add the record as new in the table
                    else
                    {
                        localDbCtx
                            .DbSetUsers
                            .Add(addEditRec);
                    }
                    await localDbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante CompanyAddMod: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Populating DTO to handle users
        /// </summary>
        /// <param name="userId">User id to search for</param>
        /// <returns></returns>
        public List<UserRoleClaimDTO> UserRoleClaimGetByUserId()
        {
            List<UserRoleClaimDTO> dbResult = new List<UserRoleClaimDTO>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                var users = localDbCtx.DbSetUsers.ToList();

                foreach (var item in users)
                {
                    var userRoles = localDbCtx.DbSetUserRoles
                        .Where(x => x.UserId == item.Id)
                        .Include(u => u.UsersNav)
                        .Include(u => u.RolesNav)
                        .ToList();
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Retrieving data from users table
        /// </summary>
        /// <returns></returns>
        public List<AspNetUsers> UsersGetAll()
        {
            List<AspNetUsers> dbResult = new List<AspNetUsers>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx
                                .DbSetUsers
                                .OrderBy(x => x.Id)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in GetUserRole:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Retrieving data from users table filtered by id
        /// </summary>
        /// <returns></returns>
        public AspNetUsers UsersGetById(string userId)
        {
            AspNetUsers dbResult = new AspNetUsers();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    var risultato = localDbCtx
                                .DbSetUsers
                                .Where(x => x.Id == userId)
                                .AsNoTracking()
                                .OrderBy(x => x.Id)
                                .FirstOrDefault();

                    if (risultato != null)
                    {
                        dbResult = risultato;
                    }
                    else
                    {
                        dbResult = new AspNetUsers();
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in GetUsersById:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Retrieving data from user/roles relation
        /// </summary>
        /// <returns></returns>
        public List<UsersViewModel> UserViewGetAll()
        {
            List<UsersViewModel> dbResult = new List<UsersViewModel>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // extracting entire set
                    dbResult = localDbCtx
                                .DbSetUsersView
                                .OrderBy(x => x.UserId)
                                .ToList();
                }
                catch (Exception exc)
                {
                    Log.Error($"Error in GetUserRole:{Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public async Task<VocabularyTempModel> VocLemmaFindByKeys(string lingua, string lemma)
        {
            await Task.Delay(1);
            VocabularyTempModel dbResult = new VocabularyTempModel();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // import massivo dati in tab temp
                    var termSearch = localDbCtx
                                .DbSetVocabularyTemp
                                .Where(x => x.Lingua == lingua && x.Lemma == lemma)
                                .FirstOrDefault();

                    if (termSearch != null)
                    {
                        dbResult = termSearch;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante VocLemmaCheckConf: {Environment.NewLine}{exc}");
                }
            }
            return dbResult;
        }

        public Dictionary<string, Dictionary<string, string>> VocLemmaGetAll()
        {
            Dictionary<string, Dictionary<string, string>> DTOResult = new Dictionary<string, Dictionary<string, string>>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    List<string> lingue = new List<string>()
                    {
                        "IT",
                        "EN"
                    };

                    // extracting entire set
                    var allVoc = localDbCtx
                                .DbSetVocabulary
                                .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;
        }

        /// <summary>
        /// Aggiunta di un nuovo set di lemmi
        /// </summary>
        /// <returns></returns>
        public async Task<bool> VocLemmaInsert()
        {
            await Task.Delay(1);
            bool fatto = false;

            //var o = listNewTerms.Where(x => x.Traduzione.Length > 200).ToList();

            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    //bool isOk = await VocLemmaInsertPrepare(listNewTerms);

                    // stored di merge dati in vocabolario
                    var storedRes = localDbCtx
                                    .Database
                                    .ExecuteSqlRaw("exec dbo.stp_Voc_Import");

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

        public async Task<bool> VocLemmaInsertPrepare(List<VocabularyTempModel> listNewTerms)
        {
            bool fatto = false;

            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // stored di reset vocabolario
                    var storedRes = localDbCtx
                                    .Database
                                    .ExecuteSqlRaw("exec dbo.stp_Voc_Prepare");
                    await localDbCtx.SaveChangesAsync();

                    // import massivo dati in tab temp
                    localDbCtx
                            .DbSetVocabularyTemp
                            .AddRange(listNewTerms);
                    await localDbCtx.SaveChangesAsync();

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

        public async Task<bool> VocLemmaSetConf(string lingua, string lemma, bool isConf)
        {
            await Task.Delay(1);
            bool fatto = false;

            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    // import massivo dati in tab temp
                    var termSearch = localDbCtx
                                .DbSetVocabularyTemp
                                .Where(x => x.Lingua == lingua && x.Lemma == lemma)
                                .FirstOrDefault();

                    if (termSearch != null)
                    {
                        termSearch.IsConfSave = isConf;

                        localDbCtx.Entry(termSearch).State = EntityState.Modified;
                        await localDbCtx.SaveChangesAsync();
                        fatto = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante VocLemmaSetConf: {Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        public Dictionary<string, Dictionary<string, string>> VocLemmaTEMPGetAll()
        {
            Dictionary<string, Dictionary<string, string>> DTOResult = new Dictionary<string, Dictionary<string, string>>();
            using (WDCDataContext localDbCtx = new WDCDataContext(_configuration))
            {
                try
                {
                    List<string> lingue = new List<string>()
                    {
                        "IT",
                        "EN"
                    };

                    // extracting entire set
                    var allVoc = localDbCtx
                                .DbSetVocabularyTemp
                                .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 VocLemmaTEMPGetAll:{Environment.NewLine}{exc}");
                }
            }
            return DTOResult;
        }

        #endregion Public Methods

        #region Private Fields

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

        #endregion Private Fields
    }
}