mapo-iob-libs/EgwProxy.SqlDb/Controllers/DbController.cs

using EgwProxy.SqlDb.DbModels;
using NLog;
using System;
using System.Collections.Generic;
using System.Linq;

namespace EgwProxy.SqlDb.Controllers
{
    /// <summary>
    /// Controller accesso dati DB IOB_ISF
    /// </summary>
    public class DbController : IDisposable
    {
        #region Public Constructors

        /// <summary>
        /// Avvio dell'oggetto gestione DB con stringa di connessione specifica
        /// </summary>
        /// <param name="connSyncState">Connessione DB locale di SYNC</param>
        public DbController(string connSyncState)
        {
            connString = connSyncState;
            Log.Info("Avviata classe DbController");
        }

        #endregion Public Constructors

        #region Public Methods

        /// <summary>
        /// Recupera la tab di ActList corrente
        /// </summary>
        /// <returns></returns>
        public List<ActListModel> ActListGetAll()
        {
            List<ActListModel> dbResult = new List<ActListModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                            .DbSetActions
                            .ToList();
            }

            return dbResult;
        }

        /// <summary>
        /// Recupera la tab di ActType corrente
        /// </summary>
        /// <returns></returns>
        public List<ActTypeModel> ActTypeGetAll()
        {
            List<ActTypeModel> dbResult = new List<ActTypeModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                            .DbSetActListType
                            .ToList();
            }
            return dbResult;
        }

        public bool DbForceMigrate()
        {
            bool fatto = false;
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbSyncStateCtx.DbForceMigrate();
                fatto = true;
            }
            return fatto;
        }

        /// <summary>
        /// Dispose classe
        /// </summary>
        public void Dispose()
        {
            // Clear database context
            connString = "";
        }

        /// <summary>
        /// Recupera la tab FluxLog x i record successivi all'ID già processato
        /// </summary>
        /// <param name="lastIdx"></param>
        /// <returns></returns>
        public List<MachFluxLogModel> MachFluxLogGetNew(long lastIdx)
        {
            List<MachFluxLogModel> dbResult = new List<MachFluxLogModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                            .DbSetMachFluxLog
                            .Where(x => x.Id > lastIdx)
                            .OrderBy(x => x.Id)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera la tab PODL Macchina x i record successivi all'ID già processato
        /// </summary>
        /// <param name="lastIdx"></param>
        /// <returns></returns>
        public List<MachPODLModel> MachPOdlGetNew(long lastIdx)
        {
            List<MachPODLModel> dbResult = new List<MachPODLModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                            .DbSetMachPODL
                            .Where(x => x.IdxPromessa > lastIdx)
                            .OrderBy(x => x.IdxPromessa)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera la tab ProdData x i record successivi all'ID già processato
        /// </summary>
        /// <param name="lastIdx"></param>
        /// <returns></returns>
        public List<MachProdDataModel> MachProdDataGetNew(long lastIdx)
        {
            List<MachProdDataModel> dbResult = new List<MachProdDataModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                            .DbSetMachProdData
                            .Where(x => x.Id > lastIdx)
                            .OrderBy(x => x.Id)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupera la tab EvList x i record successivi all'ID già processato
        /// </summary>
        /// <param name="lastIdx"></param>
        /// <returns></returns>
        public List<MachSigLogModel> MachSigLogGetNew(long lastIdx)
        {
            List<MachSigLogModel> dbResult = new List<MachSigLogModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                        .DbSetMachEvList
                        .Where(x => x.Id > lastIdx)
                        .OrderBy(x => x.Id)
                        .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Scrive elenco PODL attivi al momento x processing successivo
        /// </summary>
        /// <param name="CurrPodlReq"></param>
        /// <returns></returns>
        public bool MesPodlWriteReq(List<MesPODLReqModel> CurrPodlReq)
        {
            bool fatto = false;
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                try
                {
                    // in primis verifica SE sia vuota la tab req...
                    var oldData = dbSyncStateCtx
                                   .DbSetMesPodlReq
                                   .ToList();
                    // se vuota scrive i record e salva
                    if (oldData.Count == 0)
                    {
                        // aggiungo i nuovi record
                        var dbResult = dbSyncStateCtx
                                    .DbSetMesPodlReq
                                    .AddRange(CurrPodlReq);
                        // salvo
                        dbSyncStateCtx.SaveChanges();
                        fatto = true;
                    }
                }
                catch //(Exception exc)
                { }
            }
            return fatto;
        }

        /// <summary>
        /// Esegue reset tabelle PODL MEs e restituisce elenco finale (vuoto)
        /// </summary>
        /// <returns></returns>
        public List<MesPODLReqModel> ResetPODLMes()
        {
            List<MesPODLReqModel> dbResult = new List<MesPODLReqModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                            .Database
                            .SqlQuery<MesPODLReqModel>("EXEC dbo.stp_ResetPodlMes")
                            .ToList();
            }
            return dbResult;
        }

        public string ServerOk()
        {
            return SyncStateGetAll().Count() > 0 ? "OK" : "NA";
        }

        /// <summary>
        /// Esegue una volta la stored di ImportAll (x recupero dati da DB esterni) e poi
        /// restitusice in output la tab di SyncState x verificare lo stato
        /// </summary>
        /// <returns></returns>
        public List<SyncStateModel> SyncStateDoExportAll()
        {
            List<SyncStateModel> dbResult = new List<SyncStateModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                            .Database
                            .SqlQuery<SyncStateModel>("EXEC dbo.stp_ExportAll")
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Esegue una volta la stored di ImportAll (x recupero dati da DB esterni) e poi
        /// restitusice in output la tab di SyncState x verificare lo stato
        /// </summary>
        /// <returns></returns>
        public List<SyncStateModel> SyncStateDoImportAll()
        {
            List<SyncStateModel> dbResult = new List<SyncStateModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                            .Database
                            .SqlQuery<SyncStateModel>("EXEC dbo.stp_ImportAll")
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// recupera la tab di SyncState corrente
        /// </summary>
        /// <returns></returns>
        public List<SyncStateModel> SyncStateGetAll()
        {
            List<SyncStateModel> dbResult = new List<SyncStateModel>();
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                dbResult = dbSyncStateCtx
                            .DbSetSyncState
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiorna record SyncState corrente
        /// </summary>
        /// <returns></returns>
        public bool SyncStateUpsert(SyncStateModel newVal)
        {
            bool fatto = false;
            using (var dbSyncStateCtx = new SyncStateDbContext(connString))
            {
                // cerco record
                var dbResult = dbSyncStateCtx
                                  .DbSetSyncState
                                  .Where(x => x.TableName == newVal.TableName)
                                  .FirstOrDefault();
                // se trovato aggiorno
                if (dbResult != null)
                {
                    dbResult.LastIdx = newVal.LastIdx;
                    dbResult.Note = newVal.Note;
                    dbResult.LastUpdate = newVal.LastUpdate;
                    //dbSyncStateCtx.Entry(dbResult).State = System.Data.Entity.EntityState.Modified;
                }
                // altrimenti aggiungo
                else
                {
                    dbSyncStateCtx.DbSetSyncState.Add(newVal);
                }

                // salvo
                dbSyncStateCtx.SaveChanges();
                fatto = true;
            }
            return fatto;
        }

        #endregion Public Methods

        #region Protected Fields

        protected static string connString = "";

        #endregion Protected Fields

        #region Private Fields

        private static NLog.Logger Log = LogManager.GetCurrentClassLogger();

        #endregion Private Fields
    }
}