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

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

namespace MP.Data.Controllers
{
    /// <summary>
    /// Controller specifico x LAND FixMe ToDo !!! è ancora necessario con obj task estratti?!?!?
    /// </summary>
    public class MpLandController : IDisposable
    {
        #region Public Constructors

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

        #endregion Public Constructors

        #region Public Methods

        /// <summary>
        /// Restituisce info dimensione, tabelle e num righe DB gestiti
        /// </summary>
        /// <returns></returns>
        public List<DbSizeModel> AllDbInfo()
        {
            List<DbSizeModel> dbResult = new List<DbSizeModel>();
            string stp_DbInfo = @"
                                ;WITH TableRowCounts AS (
                                    SELECT
                                        t.name AS TableName,
                                        SUM(p.rows) AS RowNum
                                    FROM sys.tables t
                                    JOIN sys.partitions p ON t.object_id = p.object_id
                                    WHERE p.index_id IN (0, 1) -- heap or clustered index
                                    GROUP BY t.name
                                ),
                                LargestTable AS (
                                    SELECT TOP 1 RowNum, TableName
                                    FROM TableRowCounts
                                    ORDER BY RowNum DESC
                                )
                                SELECT
                                    DB_name() as DbName,
                                    CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(18,2)) AS DbSizeMb,
                                    (SELECT COUNT(*) FROM sys.tables) AS NumTables,
                                    (SELECT TableName FROM LargestTable) AS BigTable,
                                    (SELECT RowNum FROM LargestTable) AS BigTableRows
                                FROM sys.master_files
                                WHERE database_id = DB_ID();
                            ";
            try
            {
                // leggo per DB principale
                if (!string.IsNullOrEmpty(_configuration.GetConnectionString("MP.All")))
                {
                    using (var dbCtx = new MoonProContext(options))
                    {
                        var singleRes = dbCtx
                                    .DbSetDbSize
                                    .FromSqlRaw(stp_DbInfo)
                                    .AsEnumerable()
                                    .FirstOrDefault();
                        if (singleRes != null)
                        {
                            dbResult.Add(singleRes);
                        }
                    }
                }
                // leggo per FluxLog
                if (!string.IsNullOrEmpty(_configuration.GetConnectionString("MP.Flux")))
                {
                    using (var dbCtx = new MoonPro_FluxContext(_configuration))
                    {
                        var singleRes = dbCtx
                                    .DbSetDbSize
                                    .FromSqlRaw(stp_DbInfo)
                                    .AsEnumerable()
                                    .FirstOrDefault();
                        if (singleRes != null)
                        {
                            dbResult.Add(singleRes);
                        }
                    }
                }
                // leggo per Stats
                if (!string.IsNullOrEmpty(_configuration.GetConnectionString("MP.Stats")))
                {
                    using (var dbCtx = new MoonPro_STATSContext(_configuration))
                    {
                        var singleRes = dbCtx
                                    .DbSetDbSize
                                    .FromSqlRaw(stp_DbInfo)
                                    .AsEnumerable()
                                    .FirstOrDefault();
                        if (singleRes != null)
                        {
                            dbResult.Add(singleRes);
                        }
                    }
                }
            }
            catch (Exception exc)
            {
                Log.Error($"Eccezione in AllDbInfo:{Environment.NewLine}{exc}");
            }
            return dbResult;
        }

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

        public void Dispose()
        {
            _configuration = null;
        }

        /// <summary>
        /// Elenco operatori
        /// </summary>
        /// <returns></returns>
        public List<AnagOperatoriModel> ElencoOperatori()
        {
            List<AnagOperatoriModel> dbResult = new List<AnagOperatoriModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbOperatori
                            .Where(s => s.MatrOpr > 0)
                            .AsNoTracking()
                            .OrderBy(x => x.MatrOpr)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Record Macchine
        /// </summary>
        /// <returns></returns>
        public List<MacchineModel> MacchineGetAll()
        {
            List<MacchineModel> dbResult = new List<MacchineModel>();
            using (MoonProContext localDbCtx = new MoonProContext(options))
            {
                dbResult = localDbCtx
                            .DbSetMacchine
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera tutti i record di RemoteRebootLog
        /// </summary>
        /// <returns></returns>
        public List<RemoteRebootLogModel> RemRebootLogGetAll()
        {
            List<RemoteRebootLogModel> dbResult = new List<RemoteRebootLogModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetRemRebLog
                            .AsNoTracking()
                            .OrderByDescending(x => x.IdxReboot)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera ultimo record x ogni IdxMacchina x avere ultimo attivo
        /// </summary>
        /// <returns></returns>
        public List<RemoteRebootLogModel> RemRebootLogGetLast()
        {
            List<RemoteRebootLogModel> dbResult = new List<RemoteRebootLogModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetRemRebLog
                            .FromSqlRaw("EXEC stp_RRL_getLast")
                                        .AsNoTracking()
                                        .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera ultimo record x ogni IdxMacchina x avere ultimo attivo per impianti EXTRA (=no record Macchine)
        /// </summary>
        /// <returns></returns>
        public List<RemoteRebootLogModel> RemRebootLogGetLastNoMacc()
        {
            List<RemoteRebootLogModel> dbResult = new List<RemoteRebootLogModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbSetRemRebLog
                            .FromSqlRaw("EXEC stp_RRL_GetLastNoMachine")
                                        .AsNoTracking()
                                        .ToList();
            }
            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 MoonPro_STATSContext(_configuration))
            {
                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 Logger Log = LogManager.GetCurrentClassLogger();
        private DbContextOptions<MoonProContext> options;

        #endregion Private Fields
    }
}