everlight.eap/backend/DllEapDal/MacStatusAnalysisDal.cs

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

namespace DllEapDal
{
    public class MacStatusAnalysisDal
    {
        private IDatabase CurrDb = null;
        public MacStatusAnalysisDal(IDatabase db)
        {
            CurrDb = db;
        }

        /// <summary>
        /// 获取每个机台的最后一个状态
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<MacStatus> GetMacStatuses(string filter, ref string errorinfo)
        {
            var sql = $@"select a.FCode macCode,g.FName factory,a.ipaddress,c.FCode macmodelCode,c.FName macmodelName,
                         d.pCode processCode,f.Fname processName,ifnull(b.statusid,1) statusid,ifnull(h.FName,'离线') statusName from machine a
                         left join macstatus01 b on a.FCode=b.maccode
                         left join macmodel c on a.MModeid=c.id
                         left join mactprocess d on a.id=d.macid
                         left join tprocess f on d.pcode=f.fcode
                         left join factoryregion g on a.factoryid=g.id
                         left join standardstatus h on b.statusid=h.StatusVal";
            return CurrDb.FindList<MacStatus>(sql);
        }


        public IEnumerable<MacStatusAnalysisDtoTree> GetStatusTree(string filter, ref string errorinfo)
        {
            var dtos = this.GetMacStatuses(filter, ref errorinfo);
            var trees = new List<MacStatusAnalysisDtoTree>();
            if (dtos != null && dtos.Count() > 0)
            {
                var facItem = new MacStatusAnalysisDtoTree
                {
                    Key = "全厂",
                    Name = "全厂",
                    Level = 0,
                    Code = "全厂",
                    Total = dtos.Count(),   // 机台总数
                    DisConn = dtos.Where(c => c.StatusID == 1)?.Count(),    // 未连接
                    Error = dtos.Where(c => c.StatusID == 6)?.Count(),     // 错误
                    Idle = dtos.Where(c => c.StatusID == 3)?.Count(),    // 空闲
                    Conn = dtos.Where(c => c.StatusID != 1)?.Count(),        // 已连接
                    Run = dtos.Where(c => c.StatusID == 4)?.Count(),          // 运行
                    Pause = dtos.Where(c => c.StatusID == 5)?.Count(),
                    SetUp = dtos.Where(c => c.StatusID == 2)?.Count()
                };
                if (facItem.Conn != null)
                {
                    facItem.ConnPercent = facItem.Conn / Convert.ToDecimal(facItem.Total);
                }
                var factoryGroups = dtos.GroupBy(c => c.Factory);
                if (factoryGroups != null && factoryGroups.Count() > 0)
                {
                    var regionTrees = new List<MacStatusAnalysisDtoTree>();
                    foreach (var regionItem in factoryGroups)
                    {
                        var root = new MacStatusAnalysisDtoTree
                        {
                            Key = regionItem.Key,
                            Name = regionItem.Key,
                            Code = regionItem.Key,
                            Level = 1,
                            Total = regionItem.Count(),   // 机台总数
                            DisConn = regionItem.Where(c => c.StatusID == 1)?.Count(),    // 未连接
                            Error = regionItem.Where(c => c.StatusID == 6)?.Count(),     // 错误
                            Idle = regionItem.Where(c => c.StatusID == 3)?.Count(),    // 空闲
                            Conn = regionItem.Where(c => c.StatusID != 1)?.Count(),        // 已连接
                            Run = regionItem.Where(c => c.StatusID == 4)?.Count(),          // 运行
                            Pause = regionItem.Where(c => c.StatusID == 5)?.Count(),
                            SetUp = regionItem.Where(c => c.StatusID == 2)?.Count()
                        };
                        if (root.Conn != null)
                        {
                            root.ConnPercent = root.Conn / Convert.ToDecimal(root.Total);
                        }

                        var pcodeGroups = regionItem.GroupBy(c => new { c.ProcessCode, c.ProcessName });
                        var pcodeTrees = new List<MacStatusAnalysisDtoTree>();
                        if (pcodeGroups != null && pcodeGroups.Count() > 0)
                        {
                            foreach (var item in pcodeGroups)
                            {
                                var pCodeItem = new MacStatusAnalysisDtoTree
                                {
                                    Key = root.Key + "/" + item.Key.ProcessCode,
                                    Name = item.Key.ProcessName,
                                    Level = 2,
                                    Total = item.Count(),   // 机台总数
                                    DisConn = item.Where(c => c.StatusID == 1)?.Count(),    // 未连接
                                    Error = item.Where(c => c.StatusID == 6)?.Count(),     // 错误
                                    Idle = item.Where(c => c.StatusID == 3)?.Count(),    // 空闲
                                    Conn = item.Where(c => c.StatusID != 1)?.Count(),        // 已连接
                                    Run = item.Where(c => c.StatusID == 4)?.Count(),          // 运行
                                    Pause = item.Where(c => c.StatusID == 5)?.Count(),
                                    SetUp = item.Where(c => c.StatusID == 2)?.Count(),
                                    Code = item.Key.ProcessCode
                                };
                                if (pCodeItem.Conn != null)
                                {
                                    pCodeItem.ConnPercent = pCodeItem.Conn / Convert.ToDecimal(pCodeItem.Total);
                                }
                                var macmodelGroups = item.GroupBy(e => new { e.MacModelCode, e.MacModelName });
                                if (macmodelGroups != null && macmodelGroups.Count() > 0)
                                {
                                    var modelTrees = new List<MacStatusAnalysisDtoTree>();
                                    foreach (var modelItem in macmodelGroups)
                                    {
                                        var macmodel = new MacStatusAnalysisDtoTree
                                        {
                                            Key = root.Key + "/" + modelItem.Key.MacModelCode,
                                            Code = modelItem.Key.MacModelCode,
                                            Name = modelItem.Key.MacModelName,
                                            Level = 3,
                                            Total = modelItem.Count(),   // 机台总数
                                            DisConn = modelItem.Where(c => c.StatusID == 1)?.Count(),    // 未连接
                                            Error = modelItem.Where(c => c.StatusID == 6)?.Count(),     // 错误
                                            Idle = modelItem.Where(c => c.StatusID == 3)?.Count(),    // 空闲
                                            Conn = modelItem.Where(c => c.StatusID != 1)?.Count(),        // 已连接
                                            Run = modelItem.Where(c => c.StatusID == 4)?.Count(),          // 运行
                                            Pause = modelItem.Where(c => c.StatusID == 5)?.Count(),
                                            SetUp = modelItem.Where(c => c.StatusID == 2)?.Count()
                                        };
                                        if (macmodel.Conn != null)
                                        {
                                            macmodel.ConnPercent = macmodel.Conn / Convert.ToDecimal(macmodel.Total);
                                        }
                                        #region
                                        //var macTrees = new List<OutputTree>();
                                        //foreach (var mac in modelItem)
                                        //{
                                        //    var macItem = new OutputTree
                                        //    {
                                        //        Key = mac.MacCode,
                                        //        Name = mac.MacCode,
                                        //        Total = mac.Count(),   // 机台总数
                                        //        DisConn = mac.Where(c => c.StatusID == 1)?.Count(),    // 未连接
                                        //        Error = mac.Where(c => c.StatusID == 6)?.Count(),     // 错误
                                        //        Idle = mac.Where(c => c.StatusID == 3)?.Count(),    // 空闲
                                        //        Conn = mac.Where(c => c.StatusID != 1)?.Count(),        // 已连接
                                        //        Run = mac.Where(c => c.StatusID == 4)?.Count(),          // 运行
                                        //        Pause = mac.Where(c => c.StatusID == 5)?.Count(),
                                        //        SetUp = mac.Where(c => c.StatusID == 2)?.Count()
                                        //    };
                                        //    if (mac.TimeLen == 0)
                                        //    {
                                        //        macItem.UPH = 0;
                                        //    }
                                        //    else
                                        //    {
                                        //        macItem.UPH = Convert.ToInt32(Math.Round(macItem.Count / (decimal)macItem.TimeLen * 60 * 60));
                                        //    }
                                        //    macTrees.Add(macItem);
                                        //}
                                        #endregion
                                        modelTrees.Add(macmodel);
                                    }
                                    pCodeItem.Children = modelTrees;
                                }
                                pcodeTrees.Add(pCodeItem);
                            }
                        }
                        root.Children = pcodeTrees;
                        regionTrees.Add(root);
                    }
                    facItem.Children = regionTrees;
                }
                trees.Add(facItem);
            }
            return trees;
        }
    }
}