mapo-core/MP.Data/Controllers/MpMonController.cs

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

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

        public MpMonController(IConfiguration configuration)
        {
            _configuration = configuration;

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

        #endregion Public Constructors

        #region Public Methods

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

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

        public void Dispose()
        {
        }

        /// <summary>
        /// Elenco da tabella Macchine
        /// </summary>
        /// <returns></returns>
        public List<DbModels.MacchineModel> MacchineGetAll()
        {
            List<DbModels.MacchineModel> dbResult = new List<DbModels.MacchineModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                .DbSetMacchine
                .AsNoTracking()
                .OrderBy(x => x.IdxMacchina)
                .ToList();
            }
            return dbResult;
        }

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

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

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

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

        #endregion Public Methods

        #region Private Fields

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

        #endregion Private Fields
    }
}