everlight.eap/backend/DllEapDal/McaSecVMstDal.cs

using Cksoft.Data;
using System;
using System.Collections.Generic;
using System.Text;
using DllEapEntity.Dtos;
using System.Linq;
using Cksoft.Unity;

namespace DllEapDal
{
    /// <summary>
    /// 机台事件主档
    /// </summary>
    public class McaSecVMstDal
    {
        public McaSecVMstDal(IDatabase db)
        {
            CurrDb = db;
        }

        private IDatabase CurrDb;

        /// <summary>
        /// 机台某一时间段报警统计（柱状图）
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public ChartDto CreateCategory(string filter)
        {
            var dto = GetAlarmAnalysis(filter, "name", "asc");
            var chartDto = new ChartDto
            {
                text = "机台报警统计",
                legend = new string[] { "报警次数" },
                xdata = dto.Select(c => c.name).ToArray(),
                ydata = dto.Select(c => (float)c.count).ToArray()
            };
            return chartDto;
        }

        /// <summary>
        /// 机台报警次数分析
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public IEnumerable<AnalysisDto> GetAlarmAnalysis(string filter, string sort = "name", string order = "asc")
        {
            if (string.IsNullOrEmpty(sort) || sort == "null")
                sort = "name";
            if (string.IsNullOrEmpty(order) || order == "null")
                order = "asc";
            string sql = "select McaCode as name,sum(1) as count,c.FName as macname,a.McaCode as maccode,c.FModel as macmodel,d.PCode from McaSecVMst a "
                       + "left join McaSecTime b on a.ID = b.PreID "
                       + "left join Machine c on a.McaCode = c.FCode "
                       + "left join MacTProcess d on c.ID = d.MacID "
                       + "left join TProcess e on d.PCode = e.FCode "
                       + $"where 1=1 {filter}  and a.EventCode='C00007' "
                       + $"group by McaCode,c.FName,c.FCode,c.FModel,d.PCode  order by {sort} {order} ";
            var dtos = CurrDb.FindList<AnalysisDto>(sql);
            return dtos;
        }

        /// <summary>
        /// 报警代码分析
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="macModel"></param>
        /// <param name="pCode"></param>
        /// <returns></returns>
        public IEnumerable<AnalysisDto> GetAlarmCodeAnalysis(string filter, string sort = "name", string order = "asc")
        {
            if (string.IsNullOrEmpty(sort) || sort == "null")
                sort = "name";
            if (string.IsNullOrEmpty(order) || order == "null")
                order = "asc";
            string sql = "select f.FVal as name,g.Fval as description,SUM(1) as count from McaSecVMst a "
                       + "left join McaSecTime b on a.ID = b.PreID "
                       + "left join Machine c on a.McaCode = c.FCode "
                       + "left join MacModel d on c.FModel = d.FCode "
                       + "left join MacTProcess e on c.ID = e.MacID "
                       + "left join (select * from McaSecVDetail where FNum=2) f on a.ID=f.PreID "
                       + "left join (select * from McaSecVDetail where FNum=3) g on a.ID=g.PreID "
                       + $"where 1=1  {filter}  and a.EventCode = 'C00007' "
                       + filter
                       + $"group by f.FVal,g.FVal order by {sort} {order}";
            var dtos = CurrDb.FindList<AnalysisDto>(sql);
            return dtos;
        }

        /// <summary>
        /// 根据报警代码进行统计分析
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="macModel"></param>
        /// <param name="pCode"></param>
        /// <returns></returns>
        public ChartDto CreateAlarmCat(string filter)
        {
            var dto = this.GetAlarmCodeAnalysis(filter);
            var chartDto = new ChartDto
            {
                text = "报警统计",
                legend = new string[] { "报警次数" },
                xdata = dto.Select(c => c.name).ToArray(),
                ydata = dto.Select(c => (float)c.count).ToArray()
            };
            return chartDto;
        }

        /// <summary>
        /// 获取报警代码详情
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public IEnumerable<AlarmCode> GetAramCodeDetail(string filter, int start, int end, string sort = "b.ptime", string order = "desc")
        {
            if (string.IsNullOrEmpty(sort) || sort == "null")
                sort = "b.ptime";
            if (string.IsNullOrEmpty(order) || order == "null")
                order = "desc";
            string sql = $"select * from(select row_number() over (order by {sort} {order}) as rowNum,a.McaCode as maccode,c.FName as macname,f.FVal as name,h.FVal as description,e.Pcode,g.FName as pname,c.Fmodel as macmodel,b.ptime from McaSecVMst a "
                       + "left join McaSecTime b on a.ID = b.PreID "
                       + "left join Machine c on a.McaCode = c.FCode "
                       + "left join MacModel d on c.FModel = d.FCode "
                       + "left join MacTProcess e on c.ID = e.MacID "
                       + "left join (select * from McaSecVDetail where FNum=2) f on a.ID=f.PreID "
                       + "left join (select * from McaSecVDetail where FNum=3) h on a.ID=h.PreID "
                       + "left join TProcess g on e.Pcode=g.FCode "
                       + $"where 1=1  {filter}  and a.EventCode = 'C00007' "
                       + filter
                       + $" ) t where t.rowNum>={start} and t.rowNum<{end}";
            return CurrDb.FindList<AlarmCode>(sql);
        }

        public int GetAralrCodeCount(string filter)
        {
            string sql = "select count(1) from McaSecVMst a "
                       + "left join McaSecTime b on a.ID = b.PreID "
                       + "left join Machine c on a.McaCode = c.FCode "
                       + "left join MacModel d on c.FModel = d.FCode "
                       + "left join MacTProcess e on c.ID = e.MacID "
                       + "left join (select * from McaSecVDetail where FNum=3) h on a.ID=h.PreID "
                       + "left join TProcess g on e.Pcode=g.FCode "
                       + $"where 1=1  {filter}  and a.EventCode = 'C00007' "
                       + filter;
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
        }

        public IEnumerable<AlarmCode> GetMsts(string filter)
        {
            string sql = "select a.McaCode as maccode,c.FName as macname,f.FVal as name,h.FVal as description,e.Pcode,g.FName as pname,c.Fmodel as macmodel,a.EventCode,b.ptime,d.FName as macmodelname " +
                         " from McaSecVMst a "
                         + "left join McaSecTime b on a.ID = b.PreID "
                       + "left join Machine c on a.McaCode = c.FCode "
                       + "left join MacModel d on c.FModel = d.FCode "
                       + "left join MacTProcess e on c.ID = e.MacID "
                       + "left join (select * from McaSecVDetail where FNum=2) f on a.ID=f.PreID "
                       + "left join (select * from McaSecVDetail where FNum=3) h on a.ID=h.PreID "
                       + "left join TProcess g on e.Pcode=g.FCode "
                       + $" where a.EventCode='C00007' or a.EventCode='C00008' {filter}";
            var msts = CurrDb.FindList<AlarmCode>(sql).OrderBy(c => c.name);
            return msts;
        }

        /// <summary>
        /// 分析报警代码记录获取报警开始时间和结束时间
        /// </summary>
        /// <param name="msts"></param>
        /// <returns></returns>
        public IEnumerable<McaVMstADto> AppendTime(IList<AlarmCode> msts, string type)
        {
            var mstStarts = msts.Where(c => c.eventCode == "C00007");    //报警开始的记录
                                                                         // var mstEnds = msts.Where(c => c.EventCode == "C00008");    //报警解除的记录
            List<McaVMstADto> dtos = new List<McaVMstADto>();
            switch (type)
            {
                case "machine":
                    dtos.AddRange(mstStarts.Select(c => new McaVMstADto()
                    {
                        Name = c.macname,
                        StartTime = c.ptime,
                        Desc = c.description,
                        FCode = c.maccode,
                        ErrorCode = c.name
                    }));
                    foreach (var item in dtos)
                    {
                        var endItem = msts.Where(c => c.maccode == item.FCode && c.eventCode == "C00008" && c.ptime > item.StartTime).OrderBy(c => c.ptime).FirstOrDefault();
                        if (endItem == null)
                        {
                            continue;
                        }
                        item.EndTime = endItem.ptime;
                    }
                    break;
                case "macmodel":
                    dtos.AddRange(mstStarts.Select(c => new McaVMstADto()
                    {
                        Name = c.macmodelname,
                        StartTime = c.ptime,
                        Desc = c.description,
                        FCode = c.macmodel,
                        ErrorCode = c.name
                    }));
                    foreach (var item in dtos)
                    {
                        var endItem = msts.Where(c => c.macmodel == item.FCode && c.eventCode == "C00008" && c.ptime > item.StartTime).OrderBy(c => c.ptime).FirstOrDefault();
                        if (endItem == null)
                        {
                            continue;
                        }
                        item.EndTime = endItem.ptime;
                    }
                    break;
                case "process":
                    dtos.AddRange(mstStarts.Select(c => new McaVMstADto()
                    {
                        Name = c.pname,
                        StartTime = c.ptime,
                        Desc = c.description,
                        FCode = c.pcode,
                        ErrorCode = c.name
                    }));
                    foreach (var item in dtos)
                    {
                        var endItem = msts.Where(c => c.macmodel == item.FCode && c.eventCode == "C00008" && c.ptime > item.StartTime).OrderBy(c => c.ptime).FirstOrDefault();
                        if (endItem == null)
                        {
                            continue;
                        }
                        item.EndTime = endItem.ptime;
                    }
                    break;
            }


            return dtos.Where(c => c.EndTime != null);
        }

        /// <summary>
        /// 获取MTBF统计
        /// </summary>
        /// <param name="mcaVMsts"></param>
        /// <returns></returns>
        public IEnumerable<MTBFDto> GetMTBFs(string filter, string type)
        {
            var msts = GetMsts(filter).ToList();
            var mcaVMsts = AppendTime(msts, type);
            var groups = mcaVMsts.GroupBy(c => new { c.FCode, c.Name });
            var list = new List<MTBFDto>();
            foreach (var item in groups)
            {
                var mtbf = new MTBFDto()
                {
                    name = item.Key.Name,
                    count = item.Count(),
                    code = item.Key.FCode,
                    minutes = item.Sum(c =>
                    {
                        return (float)(c.EndTime - c.StartTime).Value.Seconds / 60f;
                    })
                };
                mtbf.MTBF = mtbf.count / mtbf.minutes;
                list.Add(mtbf);
            }
            return list;
        }

        /// <summary>
        /// 组装返回客户端的最终数据
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public LineDateDto GetAlarmTimeDtos(string filter, string type)
        {
            var dtos = GetDatasFromDb(filter, type);
            if (dtos == null || dtos.Count() == 0)
                return null;
            var dateStart = dtos.Min(c => c.Time);    //统计开始时间
            var dateEnd = dtos.Max(c => c.Time);      //统计结束时间

            TimeSpan timeSpan = dateEnd - dateStart;
            var days = (int)Math.Ceiling(timeSpan.TotalDays);
            var groups = dtos.GroupBy(c => c.Name);
            var series = new List<LineSeries>();
            if (groups != null && groups.Count() > 0)
            {
                foreach (var item in groups)
                {
                    var temps = dtos.Where(c => c.Name == item.Key).ToList();
                    for (var i = 0; i <= days; i++)
                    {
                        if (temps.FirstOrDefault(c => c.Time == dateStart.AddDays(i)) == null)
                        {
                            temps.Add(new AlarmTimeDto()
                            {
                                Name = item.Key,
                                Count = 0,
                                Time = dateStart.AddMinutes(i)
                            });
                        }
                    }
                    series.Add(new LineSeries()
                    {
                        Name = item.Key,
                        AlarmTimeDtos = temps.OrderBy(c => c.Time)
                    });
                }
            }
            var dates = new List<DateTime>();
            for (var j = 0; j <= days; j++)
            {
                dates.Add(dateStart.AddDays(j));
            }
            var lineDateDto = new LineDateDto()
            {
                LineSeries = series,
                Dates = dates
            };
            return lineDateDto;
        }

        /// <summary>
        /// 从数据库中读取原始统计数据
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private IEnumerable<AlarmTimeDto> GetDatasFromDb(string filter, string type)
        {
            string sql = string.Empty;
            switch (type)
            {
                case "machine":
                    sql = $"  select mcacode as name,SUM(1) as count,SUBSTRING(CONVERT(varchar(50),b.ptime,20),0,11) time from McaSecVMst a "
                      + "left join McaSecTime b on a.ID = b.PreID "
                      + "where mcacode in (select top 10 mcacode from McaSecVMst where EventCode = 'C00007' group by McaCode order by count(1) desc)  "
                      + $"and EventCode = 'C00007' {filter} "
                      + "group by McaCode,SUBSTRING(CONVERT(varchar(50), b.ptime, 20), 0, 11)  order by McaCode,SUBSTRING(CONVERT(varchar(50), b.ptime, 20), 0, 11) ";
                    break;
                case "macmodel":
                    sql = $"  select c.FModel as name,SUM(1) as count,SUBSTRING(CONVERT(varchar(50),b.ptime,20),0,11) time from McaSecVMst a "
                       + "left join McaSecTime b on a.ID = b.PreID "
                       + "left join Machine c on a.mcacode=c.FCode "
                       + "where c.FModel in (select top 10 FModel from McaSecVMst e,Machine f where e.mcacode=f.Fcode and  e.EventCode = 'C00007' " +
                       "group by f.FModel order by count(1) desc)  "
                       + $"and EventCode = 'C00007' {filter} "
                       + "group by c.FModel,SUBSTRING(CONVERT(varchar(50), b.ptime, 20), 0, 11)  order by c.FModel,SUBSTRING(CONVERT(varchar(50), b.ptime, 20), 0, 11) ";
                    break;
                case "process":
                    sql = $"  select e.FName as name,SUM(1) as count,SUBSTRING(CONVERT(varchar(50),b.ptime,20),0,11) time from McaSecVMst a "
                       + "left join McaSecTime b on a.ID = b.PreID "
                       + "left join Machine c on a.mcacode=c.FCode "
                       + "left join MacTProcess d on c.ID = d.MacID "
                       + "left join TProcess e on d.PCode =e.FCode "
                       + "where d.PCode in (select top 10 PCode from McaSecVMst h "
                       + "left join Machine i on h.mcacode=i.Fcode "
                       + "left join MacTProcess j on i.ID=j.MacID "
                       + " where h.EventCode = 'C00007' "
                       + "group by j.PCode order by count(1) desc)  "
                       + $"and EventCode = 'C00007' {filter} "
                       + "group by e.FName,SUBSTRING(CONVERT(varchar(50), b.ptime, 20), 0, 11)  order by e.FName,SUBSTRING(CONVERT(varchar(50), b.ptime, 20), 0, 11) ";
                    break;
            }
            IEnumerable<AlarmTimeDto> dtos = CurrDb.FindList<AlarmTimeDto>(sql);
            return dtos;
        }

        /// <summary>
        /// 获取数据库中所有的机台的运行开始结束时间
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        private IEnumerable<McaEffiencyEntity> GetEffiencyEntities(string filter)
        {
            string sql = @"
                          select MAX(b.ptime) as endTime,MIN(b.ptime) as startTime,a.mcacode,c.FModel as MacModel,
                          d.FName as MacModelName,e.PCode,f.FName as PName
                          from McaSecVMst a 
                          left join McaSecTime b on a.ID=b.PreID
                          left join Machine c on a.McaCode=c.FCode
                          left join MacModel d on c.FModel=d.FCode
                          left join MacTProcess e on c.ID=e.MacID
                          left join TProcess f on e.PCode=f.FCode "
                        + $" where 1=1 {filter}"
                        + " group by a.McaCode,c.FModel,d.FName,e.PCOde,f.FName";
            return CurrDb.FindList<McaEffiencyEntity>(sql);
        }

        /// <summary>
        /// 获取以机台为基准的效率统计列表
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<MachineEfficiency> GetMachineEfficienciesGroupByMcaCode(string filter)
        {
            var datas = GetEffiencyEntities(filter);
            var list = new List<MachineEfficiency>();
            int j = 0;
            foreach (var item in datas)
            {
             
                list.Add(new MachineEfficiency
                {
                    Index = ++j,
                    Count = 1,
                    Name = item.McaCode,
                  //  RunTime = (item.EndTime - item.StartTime).Value.TotalMinutes,
                    Key = item.McaCode,
                    HasChildren = false,
                    Type = "machine"
                });
            }
            return list;
        }

        /// <summary>
        /// 以机型为基准的效率统计列表
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<MachineEfficiency> GetMachineEfficienciesGroupByMacModel(string filter)
        {
            var datas = GetEffiencyEntities(filter);
            var list = new List<MachineEfficiency>();
            var groups = datas.GroupBy(c => new { c.MacModel, c.MacModelName });
            int j = 0;
            foreach (var item in groups)
            {
              
                list.Add(new MachineEfficiency
                {
                    Index = ++j,
                    Name = item.Key.MacModelName,
                    Count = item.Count(),
                  //  RunTime = item.Sum(c => (c.EndTime - c.StartTime).Value.TotalMinutes),
                    Key = item.Key.MacModel,
                    HasChildren = true,
                    Type = "macmodel"
                });
            }
            return list;
        }

        /// <summary>
        /// 以制程为基准的效率统计列表
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<MachineEfficiency> GetMachineEfficienciesGroupByPcode(string filter)
        {
            var datas = GetEffiencyEntities(filter);
            var list = new List<MachineEfficiency>();
            var groups = datas.GroupBy(c => new { c.PCode, c.PName });
            int j = 0;
            foreach (var item in groups)
            { 
                list.Add(new MachineEfficiency
                {
                    Index = ++j,
                    Name = item.Key.PName,
                    Count = item.Count(),
                 //   RunTime = item.Sum(c => (c.EndTime - c.StartTime).Value.TotalMinutes),
                    Key = item.Key.PCode,
                    HasChildren = true,
                    Type = "pcode"
                });
            }
            return list;
        }
        //添加采集数据主档
        //public McaSecVMst BatMcaSecVMst(McaSecVMst mst,List<McaSecVDetail> details,UInt32 orgnum, string usercode, ref string errorinfo)
        //{
        //    try
        //    {
        //        McaSecVMst tempmst = JudgeEventSame(mst, details, ref errorinfo);
        //        if(!string.IsNullOrEmpty(errorinfo))
        //        {
        //            return null;
        //        }
        //        if(tempmst!=null)
        //        {
        //            mst = tempmst;
        //        }
        //        else
        //        {
        //            mst = IMcaSecVMst(mst, usercode, ref errorinfo);
        //            if (mst == null)
        //                return null;
        //        }
        //        McaSecTime entity = IMcaSecTime(mst.ID, mst.ptime, orgnum, usercode, ref errorinfo);
        //        if (entity == null)
        //            return null;
        //        if(details!=null)
        //        {
        //            foreach (var item in details)
        //                item.PreID = mst.ID;
        //            int result = CurrDb.InsertFor<McaSecVDetail>(details, usercode);
        //            if (result < 0)
        //                return null;
        //        }
                
        //        //计算数量
        //        string sqlstr = $@"update mcasecvmst 
        //            set fcount = (SELECT count(id) FROM mcasectime where preid = {mst.ID})
        //            where id = {mst.ID}";
        //        CurrDb.ExecuteBySql(sqlstr);
        //        return mst;
        //    }
        //    catch (Exception e)
        //    {
        //        errorinfo = e.Message;
        //        return null;
        //    }

        //}

        public McaSecVMst BatMcaSecVMst(McaSecVMst mst, List<McaSecVDetail> details, UInt32 orgnum, string usercode, ref string errorinfo)
        {
            try
            {
                mst = IMcaSecVMst(mst, usercode, ref errorinfo);
                if (mst == null)
                    return null;
                McaSecTime entity = IMcaSecTime(mst.ID, mst.ptime, orgnum, usercode, ref errorinfo);
                if (entity == null)
                    return null;
                if (details != null)
                {
                    foreach (var item in details)
                        item.PreID = mst.ID;
                    int result = CurrDb.InsertFor<McaSecVDetail>(details, usercode);
                    if (result < 0)
                        return null;
                }
                //计算数量
                string sqlstr = $@"update mcasecvmst 
                    set fcount = (SELECT count(id) FROM mcasectime where preid = {mst.ID})
                    where id = {mst.ID}";
                CurrDb.ExecuteBySql(sqlstr);
                return mst;
            }
            catch (Exception e)
            {
                errorinfo = e.ToString();
                return null;
            }

        }

        //判断该机台最后事件与当前事件是否相同
        private McaSecVMst JudgeEventSame(McaSecVMst mst, List<McaSecVDetail> details,ref string errorinfo)
        {
            try
            {
                string condition = $" and a.id=(SELECT max(id) FROM mcasecvmst where McaCode='{mst.McaCode}')";
                List<McaSecVMst> temps = CurrDb.FindListForCondition<McaSecVMst>(condition, ref errorinfo).ToList();
                if (temps.Count <= 0)
                    return null;
                //小于0，则无需比较参数
                if (mst.FType <= 0 && mst.FType == temps[0].FType && mst.FStatus == temps[0].FStatus)
                    return temps[0];
                if (details == null)
                    return null;
                if (mst.FType > 0 && mst.FType == temps[0].FType && mst.FStatus == temps[0].FStatus&&mst.EventCode==temps[0].EventCode)
                {
                    //比较参数
                    condition = $" and a.preid={temps[0].ID}";
                    List<McaSecVDetail> orgdetails = CurrDb.FindListForCondition<McaSecVDetail>(condition, ref errorinfo).ToList();
                    if (details.Count != orgdetails.Count)
                        return null;
                    for(int i=0;i<details.Count;i++)
                    {
                        if (details[i].FCode != orgdetails[i].FCode || details[i].FVal != orgdetails[i].FVal)
                            return null;
                    }
                    return temps[0];
                }
                else
                {
                    return null;
                }
            }
            catch(Exception ex)
            {
                errorinfo = ex.Message.ToString();
                return null;
            }
        }
        //添加采集数据主档
        public McaSecVMst IMcaSecVMst(McaSecVMst mst, string usercode, ref string errorinfo)
        {
            try
            {

                int result = 0;
                int id = mst.ID;
                result = CurrDb.InsertFor(mst, usercode);
                if (result < 0)
                {
                    return null;
                }
                object objid = CurrDb.FindObject("select @@IDENTITY");
                if (objid.ToString() == "")
                {
                    return null;
                }
                id = int.Parse(objid.ToString());
                mst = CurrDb.FindEntityFor<McaSecVMst>(id);

                return mst;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return null;
            }

        }
        public McaSecTime IMcaSecTime(int mstid,DateTime ptime,UInt32 orgnum, string usercode, ref string errorinfo)
        {
            try
            {
                McaSecTime entity = new McaSecTime();
                entity.PreID = mstid;
                entity.ptime = ptime;
                entity.OrgFNum = orgnum;
                int result = CurrDb.InsertFor(entity, usercode);
                if (result < 0)
                {
                    return null;
                }
                object objid = CurrDb.FindObject("select @@IDENTITY");
                if (objid.ToString() == "")
                {
                    return null;
                }
                int id = int.Parse(objid.ToString());
                entity = CurrDb.FindEntityFor<McaSecTime>(id);
                return entity;
            }
            catch (Exception e)
            {
                errorinfo = e.Message;
                return null;
            }

        }
    }
}