ShanXi_SmartControl/GDNXFD.Data/Repositories/AlertSnapRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GDNXFD.Data.Repositories
{
    public class AlertSnapRepository
    {

        #region 报警历史

        public static IList<AlertInfo> GetRealTimeAlertInfos()
        {
            try
            {
                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    DateTime startTime = DateTime.Now.AddDays(-1);

                    var results = ctx.AlertInfo
                        .Where(q => q.IsConfirmed == false)
                        .Where(q => q.IsOpened == true)
                        .Where(q => q.AlertTime > startTime)
                        .Where(q => q.MessageType == "1")
                        .OrderByDescending(q => q.Rank)
                        .OrderByDescending(q => q.AlertTime)
                        //.Take(1000)
                        .ToList();
                    return results;
                }
            }
            catch (Exception ex)
            {
                var o = ex;
                return null;
            }
        }


        public static IList<AlertSnap> GetRealTimeAlertSnaps()
        {
            try
            {
                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                   // DateTime startTime = DateTime.Now.AddHours(-8);

                    var results = ctx.AlertSnap
                        // .Where(q => q.IsConfirmed == false)
                        .Where(q => q.IsOpened == true)
                       // .Where(q => q.LastUpdateTime > startTime)
                        .OrderByDescending(q => q.Rank)
                        .OrderByDescending(q => q.LastUpdateTime)
                        .ToList();
                    return results;
                }
            }
            catch (Exception ex)
            {
                var o = ex;
                return null;
            }
        }


        public static IList<AlertSnap> GetLatestAlertSnaps(int count)
        {
            try
            {
                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    DateTime startTime = DateTime.Now.AddHours(-8);

                    var results = ctx.AlertSnap
                        .Where(q => q.IsOpened == true)
                        .Where(q => q.LastUpdateTime > startTime)
                        .OrderByDescending(q => q.LastUpdateTime)
                        .Take(count)
                        .ToList();
                    return results;
                }
            }
            catch (Exception ex)
            {
                var o = ex;
                return null;
            }
        }


        public static IList<ShutdownEvent> GetLatestShutdownEvents()
        {
            try
            {
                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    DateTime startTime = DateTime.Now.AddDays(-1);

                    var results = ctx.ShutdownEvent
                        .Where(q => q.StopHours > 0)
                        .Where(q => q.StopTime > startTime)
                        .OrderByDescending(q => q.StopTime)
                        .Take(1000)
                        .ToList();
                    return results;
                }
            }
            catch (Exception ex)
            {
                var o = ex;
                return null;
            }
        }



        public static IList<AlertInfo> GetAlertInfosPage(string filter, int pageIndex, int pageSize, ref int total)
        {
            try
            {
                string[] cri = filter.Split(',');
                DateTime startDt = DateTime.Parse(cri[0]);
                DateTime endDt = DateTime.Parse(cri[1]);
                string stationId = cri[2];
                string rank = cri[3];
                string category1 = cri[4];
                string category2 = cri[5];
                string keyWords = cri[6];

                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    var r1 = ctx.AlertInfo.AsQueryable();

                    r1 = r1.Where(q => q.AlertTime > startDt && q.AlertTime < endDt);

                    if (!string.IsNullOrWhiteSpace(stationId))
                    {
                        r1 = r1.Where(q => q.StationId == stationId);
                    }
                    else
                    {
                        r1 = r1.Where(q => q.StationId != null);
                    }

                    if (!string.IsNullOrWhiteSpace(rank))
                    {
                        r1 = r1.Where(q => q.Rank == rank);
                    }
                    if (!string.IsNullOrWhiteSpace(category1))
                    {
                        r1 = r1.Where(q => q.Category1 == category1);
                    }
                    if (!string.IsNullOrWhiteSpace(category2))
                    {
                        r1 = r1.Where(q => q.Category2 == category2);
                    }
                    if (!string.IsNullOrWhiteSpace(keyWords))
                    {
                        r1 = r1.Where(q => q.AlertText.Contains(keyWords));
                    }

                    r1 = r1.Where(q => q.MessageType == "1");
                    r1 = r1.Where(q => q.AlertValue > 0);
                    r1 = r1.Where(q => q.SnapID > 0);

                    total = r1.Count();
                    var results = r1.OrderByDescending(q => q.AlertTime)
                        .Skip(--pageIndex * pageSize)
                        .Take(pageSize)
                        .ToList();

                    return results;
                }
            }
            catch (Exception ex)
            {
                var o = ex;
                return null;
            }
        }

        public static IList<IFixBJTestingPointDI> GetIFixBjPage(string filter, int pageIndex, int pageSize, ref int total)
        {
            try
            {
                string[] cri = filter.Split(',');
                string stationId = cri[0];
                string rank = cri[1];
                string category1 = cri[2];
                string status = cri[3];
                string tagDesc = cri[4];

                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    var r1 = ctx.IFixBJTestingPointDI.AsQueryable();

                    if (!string.IsNullOrWhiteSpace(stationId))
                    {
                        r1 = r1.Where(q => q.StationId == stationId);
                    }
                    if (!string.IsNullOrWhiteSpace(rank))
                    {
                        r1 = r1.Where(q => q.LevelId == rank);
                    }
                    if (!string.IsNullOrWhiteSpace(category1))
                    {
                        r1 = r1.Where(q => q.Area1 == category1);
                    }
                    if (!string.IsNullOrWhiteSpace(status))
                    {
                        bool isEnabled = status == "0" ? false : true;
                        r1 = r1.Where(q => q.Enabled == isEnabled);
                    }
                    if (!string.IsNullOrWhiteSpace(tagDesc))
                    {
                        r1 = r1.Where(q => q.PointKey.Contains(tagDesc) || q.Description.Contains(tagDesc));
                    }

                    total = r1.Count();
                    var results = r1.OrderBy(q => q.PointKey)
                        .Skip(--pageIndex * pageSize)
                        .Take(pageSize)
                        .ToList();

                    return results;
                }
            }
            catch (Exception ex)
            {
                var o = ex;
                return null;
            }
        }

        public static IFixBJTestingPointDI GetIFixBjModel(string pointKey)
        {
            using (GDNXFDDbContext ctx = new GDNXFDDbContext())
            {
                return ctx.IFixBJTestingPointDI.Where(q => q.PointKey == pointKey).FirstOrDefault();
            }
        }

        public static void UpdateIFixBj(IFixBJTestingPointDI model)
        {
            using (GDNXFDDbContext ctx = new GDNXFDDbContext())
            {
                var obj = ctx.IFixBJTestingPointDI.Where(q => q.Id == model.Id).FirstOrDefault();
                if (obj != null)
                {
                    MergeIFixBj(obj, model);
                    ctx.SaveChanges();
                }
            }
        }

        public static void AddIFixBj(IFixBJTestingPointDI model)
        {
            using (GDNXFDDbContext ctx = new GDNXFDDbContext())
            {
                var obj = ctx.IFixBJTestingPointDI.Where(q => q.PointKey == model.PointKey).FirstOrDefault();
                if (obj == null)
                {
                    ctx.IFixBJTestingPointDI.Add(model);
                    ctx.SaveChanges();
                }
            }
        }

        public static IList<AlertInfo> GetWindturbineAlertInfoByPage(string filter, int pageIndex, int pageSize, ref int total)
        {
            try
            {
                string[] cri = filter.Split(',');
                DateTime startDt = DateTime.Parse(cri[0]);
                DateTime endDt = DateTime.Parse(cri[1]);
                string windturbineId = cri[2];

                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    var r1 = ctx.AlertInfo.AsQueryable();

                    r1 = r1.Where(q => q.AlertTime > startDt && q.AlertTime < endDt);

                    r1 = r1.Where(q => q.WindturbineId == windturbineId);


                    r1 = r1.Where(q => q.MessageType == "1");
                    r1 = r1.Where(q => q.AlertValue > 0);
                    r1 = r1.Where(q => q.SnapID > 0);

                    total = r1.Count();
                    var results = r1.OrderByDescending(q => q.AlertTime)
                        .Skip(--pageIndex * pageSize)
                        .Take(pageSize)
                        .ToList();

                    return results;
                }
            }
            catch (Exception ex)
            {
                var o = ex;
                return null;
            }
        }

        #endregion



        public static IList<AlertSnap> GetTop1000Snaps()
        {
            try
            {
                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    DateTime startTime = DateTime.Now.AddDays(-3);

                    var results = ctx.AlertSnap
                        .Where(q => q.IsOpened == true)
                        .Where(q => q.LastUpdateTime > startTime)
                        .OrderByDescending(q => q.LastUpdateTime)
                        .Take(5000)
                        .ToList();
                    return results;
                }
            }
            catch (Exception ex)
            {
                var o = ex;
                return null;
            }
        }

        public static IList<AlertSnap> GetTop1000ClosedSnaps()
        {
            try
            {
                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    DateTime startTime = DateTime.Now.AddDays(-3);

                    var results = ctx.AlertSnap
                        .Where(q => q.IsOpened == false)
                        .Where(q => q.LastCloseTime > startTime)
                        .OrderByDescending(q => q.LastCloseTime)
                        .Take(1000)
                        .ToList();
                    return results;
                }
            }
            catch (Exception ex)
            {
                var o = ex;
                return null;
            }
        }

        public static IList<AlertSnap> GetAlertSnaps(string stationId, string rank, string category, string category2, string filter, string status)
        {
            using (GDNXFDDbContext ctx = new GDNXFDDbContext())
            {
                DateTime startTime = DateTime.Now.AddDays(-3);

                var r1 = ctx.AlertSnap.Where(q => q.IsOpened == true).Where(q => q.LastUpdateTime > startTime);
                if (!string.IsNullOrWhiteSpace(stationId))
                {
                    r1 = r1.Where(q => q.StationId == stationId);
                }
                if (!string.IsNullOrWhiteSpace(rank))
                {
                    r1 = r1.Where(q => q.Rank == rank);
                }
                if (!string.IsNullOrWhiteSpace(category))
                {
                    r1 = r1.Where(q => q.Category1 == category);
                }
                if (!string.IsNullOrWhiteSpace(category2))
                {
                    r1 = r1.Where(q => q.Category2 == category2);
                }
                if (!string.IsNullOrWhiteSpace(status))
                {
                    bool isConfirmed = status == "0" ? false : true;
                    r1 = r1.Where(q => q.IsConfirmed == isConfirmed);
                }
                if (!string.IsNullOrWhiteSpace(filter))
                {
                    r1 = r1.Where(q => q.AlertText.Contains(filter) ||
                    q.StationName.Contains(filter) ||
                    q.ProjectName.Contains(filter) ||
                    q.LineName.Contains(filter) ||
                    q.WindturbineName.Contains(filter));
                }

                var results = r1.OrderByDescending(q => q.LastUpdateTime)
                    .Take(1000)
                    .ToList();
                return results;
            }
        }

        // 
        public static void ConfirmAlert(long id)
        {
            try
            {
                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    string sql = "update alertsnap set isconfirmed='1', confirmtime=sysdate where  id='" + id + "'";
                    ctx.Database.ExecuteSqlCommand(sql, new object[] { });

                }
            }
            catch (Exception ex)
            {
                var x = ex.Message;
            }
        }

        public static void ConfirmAlertPage(string[] ids)
        {
            try
            {
                //ids = '2016-10-13 15:38:30','MHS_FDC','3','custom','1','96'

                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    StringBuilder sb = new StringBuilder("update view_alerthistory set isconfirmed='1', confirmtime=sysdate where isconfirmed='0' ");
                    sb.AppendFormat("and alerttime <= to_date('{0}','yyyy-mm-dd hh24:mi:ss') and alerttime > sysdate-4 ", ids[0]);
                    if (string.IsNullOrWhiteSpace(ids[1]) == false)
                    {
                        sb.AppendFormat("and stationid='{0}' ", ids[1]);
                    }
                    if (string.IsNullOrWhiteSpace(ids[2]) == false)
                    {
                        sb.AppendFormat("and rank='{0}' ", ids[2]);
                    }
                    if (string.IsNullOrWhiteSpace(ids[3]) == false)
                    {
                        sb.AppendFormat("and category1='{0}' ", ids[3]);
                    }
                    if (string.IsNullOrWhiteSpace(ids[4]) == false)
                    {
                        sb.AppendFormat("and category2='{0}' ", ids[4]);
                    }
                    if (string.IsNullOrWhiteSpace(ids[5]) == false)
                    {
                        sb.AppendFormat("and ( stationname like '%{0}%' or projectname like '%{0}%' or linename like '%{0}%' or windturbinename like '%{0}%' or alerttext like '%{0}%') ", ids[5]);
                    }

                    ctx.Database.ExecuteSqlCommand(sb.ToString(), new object[] { });

                }

            }
            catch (Exception ex)
            { var tmp = ex.Message; }
        }

        public static void ConfirmAll()
        {
            try
            {
                using (GDNXFDDbContext ctx = new GDNXFDDbContext())
                {
                    string sql = "update alerthistory set isconfirmed='1', confirmtime=sysdate where alerttime > sysdate-3 and isconfirmed='0'";
                    ctx.Database.ExecuteSqlCommand(sql, new object[] { });

                }
            }
            catch { }
        }

        public static AlertSnap GetAlertSnap(AlertObjectType oType, string objectId, long eDnaValue)
        {
            using (GDNXFDDbContext ctx = new GDNXFDDbContext())
            {
                string category = "custom";
                switch (oType)
                {
                    case AlertObjectType.WindTurbine:
                        return ctx.AlertSnap.Where(q => q.Category1 == category && q.Category2 == "1" && q.WindturbineId == objectId && q.AlertValue == eDnaValue).FirstOrDefault();
                    case AlertObjectType.WindPowerStation:
                        return ctx.AlertSnap.Where(q => q.Category1 == category && q.Category2 == "2" && q.StationId == objectId && q.AlertValue == eDnaValue).FirstOrDefault();
                    case AlertObjectType.Project:
                        return ctx.AlertSnap.Where(q => q.Category1 == category && q.Category2 == "3" && q.ProjectId == objectId && q.AlertValue == eDnaValue).FirstOrDefault();
                    case AlertObjectType.Line:
                        return ctx.AlertSnap.Where(q => q.Category1 == category && q.Category2 == "4" && q.LineId == objectId && q.AlertValue == eDnaValue).FirstOrDefault();
                    case AlertObjectType.Electrical:
                        return ctx.AlertSnap.Where(q => q.Category1 == category && q.Category2 == "5" && q.StationId == objectId && q.AlertValue == eDnaValue).FirstOrDefault();
                }
            }

            return null;
        }

        public static AlertSnap GetWindturbineAlertSnap(string windturbineId, long eDnaValue)
        {
            using (GDNXFDDbContext ctx = new GDNXFDDbContext())
            {
                return ctx.AlertSnap.Where(q => q.Category1 == "windturbine" &&
                        q.WindturbineId == windturbineId
                        && q.AlertValue == eDnaValue).FirstOrDefault();
            }
        }

        public static AlertSnap GetIFixAlertSnap(string pointKey)
        {
            using (GDNXFDDbContext ctx = new GDNXFDDbContext())
            {
                return ctx.AlertSnap.Where(q => (q.Category1 == "SYZ" || q.Category1 == "GF") &&
                        q.TestingPointKey == pointKey).FirstOrDefault();
            }
        }

        public static IList<AlertSnap> GetTestingPointAlertSnap()
        {
            using (GDNXFDDbContext ctx = new GDNXFDDbContext())
            {
                return ctx.AlertSnap.Where(q => string.IsNullOrEmpty(q.TestingPointKey) == false).ToList();
            }
        }


        public static void SaveAlertSnap(AlertSnap snap)
        {
            using (GDNXFDDbContext ctx = new GDNXFDDbContext())
            {
                var obj = ctx.AlertSnap.Where(q => q.Id == snap.Id).FirstOrDefault();
                if (obj != null)
                {
                    MergeRule(obj, snap);
                }
                else
                {
                    ctx.AlertSnap.Add(snap);
                }

                AlertHistory his = AlertSnapFactory.CreateAlertHistory(snap);
                ctx.AlertHistory.Add(his);
                ctx.SaveChanges();
            }
        }

        private static void MergeRule(AlertSnap pr, AlertSnap ur)
        {
            pr.AlertText = ur.AlertText;
            pr.AlertValue = ur.AlertValue;
            pr.Category1 = ur.Category1;
            pr.Category2 = ur.Category2;
            pr.Category3 = ur.Category3;
            pr.ConfirmPerson = ur.ConfirmPerson;
            pr.ConfirmTime = ur.ConfirmTime;
            pr.IsConfirmed = ur.IsConfirmed;
            pr.IsOpened = ur.IsOpened;
            pr.LastUpdatePerson = ur.LastUpdatePerson;
            pr.LastUpdateTime = ur.LastUpdateTime;
            pr.LastClosePerson = ur.LastClosePerson;
            pr.LastCloseTime = ur.LastCloseTime;
            pr.LineId = ur.LineId;
            pr.LineName = ur.LineName;
            pr.ModelId = ur.ModelId;
            pr.ProjectId = ur.ProjectId;
            pr.ProjectName = ur.ProjectName;
            pr.Rank = ur.Rank;
            pr.StationId = ur.StationId;
            pr.StationName = ur.StationName;
            pr.WindturbineId = ur.WindturbineId;
            pr.WindturbineName = ur.WindturbineName;
            pr.TestingPointKey = ur.TestingPointKey;
            pr.DataInfo = ur.DataInfo;
        }

        private static void MergeIFixBj(IFixBJTestingPointDI pr, IFixBJTestingPointDI ur)
        {
            pr.PointKey = ur.PointKey;
            pr.Area1 = ur.Area1;
            pr.Description = ur.Description;
            pr.Enabled = ur.Enabled;
            //pr.Id = ur.Id;
            pr.LevelId = ur.LevelId;
            pr.StationId = ur.StationId;
        }

    }
}