everlight.eap/backend/DllEapDal/McaEventStatisticByDayDal.cs

using System;
using System.Collections.Generic;
using Cksoft.Data;
using DllEapEntity;
using System.Linq;
using System.Threading.Tasks;
using DllEapEntity.Dtos;
using Cksoft.Data.Repository;
using Cksoft.Unity;
using System.Collections.Concurrent;
using DllEapEntity.Mes;
using DllHsms;
using Cksoft.Unity.Log4NetConfig;
using DllAmbiguity;

namespace DllEapDal
{
    public class McaEventStatisticByDayDal
    {
        public IDatabase CurrDb;

        public McaEventStatisticByDayDal(IDatabase db)
        {
            this.CurrDb = db;
        }

        #region 页面数据分析相关
        public IEnumerable<McaEventStatisticByDay> GetStatisticDetails(string filter, string subFilter)
        {
            string sql = $@"select t.id,t.eventcode,t.alarmcode,t.alarmdesc,t.mcacode,t.starttime,t.endtime,t.TimeSpan,
                           f.FCode as Manufacturer,b.FName as McaName,e.FName as MacModelName,e.FCode as MacModel,c.PCode,d.FName as PName,g.FName as Factory,b.RegionId 
                            from (
                           select year(a.starttime) as year,MONTH(a.StartTime) as month,DAY(a.StartTime) as day,
                           a.Id,a.EventCode,a.AlarmCode,a.AlarmDesc,a.McaCode,
                           a.StartTime,a.EndTime,a.TimeSpan
                           from McaEventStatisticByDay a 
                           where 1=1 {filter}
                           ) t
                           left join Machine b on t.McaCode=b.FCode 
                           left join MacTProcess c on b.id=c.MacID 
                           left join TProcess d on c.PCode=d.FCode  
                           left join MacModel e on b.MModeId=e.id 
                           left join Supplier f on e.SupplierId=f.id 
                           left join FactoryRegion g on b.FactoryId=g.id 
                           where 1=1 and b.isanalysis<>-1 {subFilter}";
            return CurrDb.FindList<McaEventStatisticByDay>(sql);
        }

        public IEnumerable<McaEventStatisticByDay> GetStatisticDetailsForDisplay(string filter)
        {
            string sql = @"select SUBSTRING(CONVERT(varchar(50),StartTime,24),0,3) hour,
                           a.Id,f.FCode as Manufacturer,a.EventCode,a.AlarmCode,a.AlarmDesc,a.McaCode,b.FName as McaName,
                           e.FName as MacModelName,a.StartTime,a.EndTime,a.TimeSpan,a.IsInProducing,a.IsHandled,
                           e.FCode as MacModel,c.PCode,d.FName as PName from McaEventStatisticByDay a 
                           left join Machine b on a.McaCode=b.FCode
                           left join MacTProcess c on b.id=c.MacID
                           left join TProcess d on c.PCode=d.FCode 
                           left join MacModel e on b.MModeId=e.id 
                           left join Supplier f on e.SupplierId=f.id "
                       + $" where 1=1 and b.isanalysis<>-1 {filter}";
            return CurrDb.FindList<McaEventStatisticByDay>(sql);
        }

        /// <summary>
        /// 机台某一时间段报警统计（柱状图）
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public ChartDto CreateCategory(string filter, string subFilter, int take = 10)
        {
            //var dto = GetAlarmAnalysis(filter, subFilter, "name", "asc").OrderByDescending(c => c.count).Take(take).OrderBy(c => c.maccode);
            var dto = GetAlarmAnalysis(filter, subFilter, "name", "asc").OrderByDescending(c => c.count).Take(take);
            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 mainFilter, string subFilter, string sort = "name", string order = "asc", int take = 10)
        {
            if (string.IsNullOrEmpty(sort) || sort == "null")
                sort = "name";
            if (string.IsNullOrEmpty(order) || order == "null")
                order = "asc";
            string sql = $@"
SELECT Name,SUM(count) count,macname,maccode,macnumber,macmodel,PCode,PName,PName,regionId,FactoryName,FloorName,PlantName,RegionName
FROM(
select a.name as name,count as count,b.FName as macname,a.name as maccode,machinenumber.MacNumber as macnumber,e.FCode as macmodel,
                         c.PCode,d.FName as PName,b.regionId,g.FName FactoryName,
	                     i.FName FloorName,j.FName PlantName,concat(g.FName,'/',i.FName,'/',h.FName) as RegionName from 
                         (select ac.McaCode as name,AlarmCode,sum(1) as count from McaEventStatisticByDay ac 
                         where 1=1 {mainFilter} 
                         group by mcacode,AlarmCode ) a 
                         left join Machine b on a.name=b.FCode 
                         left join MacTProcess c on b.id=c.MacId 
                         left join TProcess d on c.Pcode=d.FCode 
                         left join MacModel e on b.MModeId= e.id
INNER JOIN mtbacode p on p.MModeID=e.id and p.alarmcode=a.AlarmCode
                         left join Supplier f on e.SupplierID=f.id 
                         left join FactoryRegion g on b.factoryId=g.id 
                         left join FactoryRegion h on b.regionId=h.id 
                         left join FactoryRegion i on h.parentid=i.id 
                         left join FactoryRegion j on i.parentid=j.id
                         		LEFT JOIN machinenumber on b.ID=machinenumber.MacId
                         where 1=1 {subFilter}  order by count desc 
)t
	GROUP BY t.Name,macname,maccode,macnumber,macmodel,PCode,PName,PName,regionId,FactoryName,FloorName,PlantName,RegionName
 	ORDER BY SUM(count) desc";
            //var dtos = CurrDb.FindList<AnalysisDto>(sql).OrderByDescending(c => c.count).Take(take).OrderBy(c => c.maccode);
            var dtos = CurrDb.FindList<AnalysisDto>(sql).OrderByDescending(c => c.count).Take(take);
            //if (dtos != null && dtos.Count() > 0)
            //{
            //    var regionDal = new FactoryRegionDal(CurrDb);
            //    string errorinfo = string.Empty;
            //    var allRegions = regionDal.Get(1, 10000, "ID", "asc", string.Empty, errorinfo);
            //    foreach (var item in dtos)
            //    {
            //        item.RegionName = regionDal.GetFullRegionName(item.RegionId, allRegions);
            //    }
            //}
            return dtos;
        }

        public ChartDto2 CreateMtbaCategory(DateTime startTime, DateTime endTime, string tablename, string subFilter, string sub, int take = 10)
        {
            var dto = GetMtbaAnalysis(startTime, endTime, tablename, subFilter, sub, "name", "asc").OrderByDescending(c => c.count).Take(10);
            //var chartDto = new ChartDto
            //{
            //    text = "",
            //    legend = new string[] { "设备MTBA top10" },
            //    xdata = dto.Select(c => c.name).ToArray(),
            //    ydata = dto.Select(c => (float)c.count).ToArray()
            //};
            var chartDto2 = new ChartDto2();
            List<string> vs = new List<string>();
            vs.Add("汇总");
            //chartDto2.legend = new string[] { "汇总", "故障", "非故障" };
            chartDto2.xdata = dto.Select(c => c.name).ToArray();
            List<Series> Seriess = new List<Series>();
            Series series = new Series();
            ItemStyle itemStyle = new ItemStyle();
            Normal normal = new Normal();
            Label label = new Label();
            label.show = true;
            normal.label = label;
            itemStyle.normal = normal;
            series.itemStyle = itemStyle;

            series.name = "汇总";
            series.type = "bar";
            series.data = dto.Select(c => (float)c.count).ToArray();
            Seriess.Add(series);
            if (string.IsNullOrEmpty(sub) || sub.Contains("1"))
            {
                vs.Add("故障");
                series = new Series();
                series.itemStyle = itemStyle;
                series.name = "故障";
                series.type = "bar";
                series.data = dto.Select(c => (float)c.countOne).ToArray();
                Seriess.Add(series);
            }
            if (string.IsNullOrEmpty(sub) || sub.Contains("2"))
            {
                vs.Add("非故障");
                series = new Series();
                series.itemStyle = itemStyle;
                series.name = "非故障";
                series.type = "bar";
                series.data = dto.Select(c => (float)c.countTwo).ToArray();
                Seriess.Add(series);
            }
            chartDto2.legend = vs.ToArray();
            chartDto2.series = Seriess;
            return chartDto2;
        }
        public IEnumerable<AnalysisDto> GetMtbaAnalysis(DateTime startTime, DateTime endTime, string tablename, string subFilter, string sub, string sort = "name", string order = "asc", int take = 10)
        {
            if (string.IsNullOrEmpty(sort) || sort == "null")
                sort = "name";
            if (string.IsNullOrEmpty(order) || order == "null")
                order = "asc";
            #region
            //string sql = $@"SELECT
            //                 c.fcode name,
            //                 c.timespan / c.count / 60 count,
            //                 timespan 
            //                FROM
            //                 (
            //                 SELECT
            //                  a.FCode,
            //                  ifnull( b.timespan, '0' ) timespan,
            //                  ifnull( b.count, '0' ) count 
            //                 FROM
            //                  machine a
            //                  LEFT JOIN (
            //                  SELECT
            //                   mcacode,
            //                   sum( CASE WHEN ( EventCode = 'C00007' ) THEN 1 ELSE 0 END ) AS count,
            //                   sum( CASE WHEN EventCode = 'C00012' THEN timespan ELSE 0 END ) AS timespan 
            //                  FROM
            //                   mcaeventstatisticbyday 
            //                  WHERE
            //                   starttime >= '{startTime.ToString("yyyy-MM-dd HH:mm:ss")}'   and starttime < '{endTime.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' 
            //                  GROUP BY
            //                   mcacode 
            //                  ) b ON a.fcode = b.mcacode 
            //                         inner JOIN factoryregion i ON i.Id=a.FactoryId 
            //                        INNER JOIN factoryregion j ON j.Id=a.RegionId
            //                        INNER JOIN factoryregion k ON k.Id=j.ParentId 
            //                        INNER JOIN factoryregion l ON l.id=k.ParentId 
            //                        where 1=1 {subFilter}
            //                 ) c 
            //                ORDER BY
            //                 c.timespan / c.count DESC 
            //                 LIMIT 10 ";
            #endregion
            #region
            //            string sql = $@"SELECT
            //	c.fcode NAME,
            //sum(	c.timespan) /(case when sum( c.count)=0 then 1 else sum( c.count) end) / 60 count,
            //SUM(c.count) allcount,
            //SUM(TimeSpan) TimeSpan,
            //sum( CASE  WHEN faulttype=1 THEN Count WHEN faulttype='' THEN  Count WHEN faulttype IS NULL THEN Count ELSE 0 END ) CountOne,
            //sum( CASE faulttype WHEN 2 THEN Count ELSE 0 END ) CountTwo
            //FROM
            //	(
            //	SELECT
            //		a.FCode,
            //		ifnull( b.timespan, '0' ) timespan,
            //		ifnull( b.count, '0' ) count 
            //		,p.faulttype
            //	FROM
            //		machine a
            //		LEFT JOIN (
            //		SELECT
            //			mcacode,AlarmCode
            //			,sum( CASE WHEN ( EventCode = 'C00007' ) THEN 1 ELSE 0 END ) AS count
            //			,sum( CASE WHEN EventCode = 'C00012' THEN timespan ELSE 0 END ) AS timespan 
            //		FROM
            //			mcaeventstatisticbyday 
            //		WHERE
            //			starttime >= '{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' 
            //			AND starttime < '{endTime.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss")}' 
            //		GROUP BY
            //			mcacode ,AlarmCode
            //		) b ON a.fcode = b.mcacode
            //		LEFT JOIN mtbacode p on p.MModeID = a.MModeID and b.AlarmCode = p.AlarmCode
            //		INNER JOIN factoryregion i ON i.Id = a.FactoryId
            //		INNER JOIN factoryregion j ON j.Id = a.RegionId
            //		INNER JOIN factoryregion k ON k.Id = j.ParentId
            //		INNER JOIN factoryregion l ON l.id = k.ParentId 
            //	WHERE
            //		1 = 1 {subFilter} {sub}
            //	) c 
            //	GROUP BY FCode
            //ORDER BY
            //	sum(	c.timespan) /(case when sum( c.count)=0 then 1 else sum( c.count) end) / 60 DESC 
            //	LIMIT 10";
            #endregion
            string sql = $@" 
select
a.FCode NAME
,b.timespan /(case when  b.count=0 then 1 else b.count end) / 60 count
,b.timespan /(case when  b.CountOne=0 then 1 else b.CountOne end) / 60 CountOne
,b.timespan /(case when  b.CountTwo=0 then 1 else b.CountTwo end) / 60 CountTwo
,b.timespan
from 
machine a
INNER JOIN(SELECT
            			maccode MacCode,
            			sum( CASE WHEN ( EventCode = 'C00007' ) THEN count ELSE 0 END ) AS count,
						sum( CASE WHEN ( EventCode = 'C00007' ) THEN CountOne ELSE 0 END ) AS CountOne,
						sum( CASE WHEN ( EventCode = 'C00007' ) THEN CountTwo ELSE 0 END ) AS CountTwo,
            			sum( CASE WHEN EventCode = 'C00012' THEN timespan ELSE 0 END ) AS timespan 
            		FROM
            			{tablename} 
            		GROUP BY
            			maccode)  b on a.FCode=b.MacCode
LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
INNER JOIN factoryregion i ON i.Id = a.FactoryId
INNER JOIN factoryregion j ON j.Id = a.RegionId
INNER JOIN factoryregion k ON k.Id = j.ParentId
INNER JOIN factoryregion l ON l.id = k.ParentId

ORDER BY b.timespan /(case when  b.count=0 then 1 else b.count end) / 60 DESC limit 10;
 ";
            var dtos = CurrDb.FindList<AnalysisDto>(sql).OrderByDescending(c => c.count).Take(take).OrderBy(c => c.maccode);
            //foreach (var item in dtos)
            //{
            //    if (item.countOne == 0)
            //    {
            //        item.countOne = 1;
            //    }
            //    if (item.countTwo == 0)
            //    {
            //        item.countTwo = 1;
            //    }
            //    //if (item.allcount == 0)
            //    //{
            //    //    item.count = item.TimeSpan / 60;
            //    //}
            //    item.countOne = item.TimeSpan / item.countOne / 60;
            //    item.countTwo = item.TimeSpan / item.countTwo / 60;
            //}
            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 subFilter, int pageSize, int pageIndex, out int total, string sort = "count", string order = "desc")
        {
            if (string.IsNullOrEmpty(sort) || sort == "null")
                sort = "count";
            if (string.IsNullOrEmpty(order) || order == "null")
                order = "desc";

            string sql = $@"select name,chalarm,description,sum(count) as count from (
                         select name,p.chalarm,description,t.count,t.mcacode from (                    
                         select AlarmCode as name,AlarmDesc as description,mcacode,SUM(1) as count 
                         from McaEventStatisticByDay a 
						 where 1=1 {filter} group by alarmcode,alarmdesc,
                         mcacode) t 
                         left join Machine b on t.mcacode=b.FCode  
                         left join MacTProcess c on b.id=c.MacId 
						 left join TProcess d on c.Pcode=d.FCode 
                         left join MacModel e on b.MModeId= e.id 
INNER JOIN mtbacode p on p.MModeID=e.id and p.alarmcode=t.name
						 left join Supplier f on e.Supplierid=f.id 
                         left join FactoryRegion g on b.factoryId=g.id 
                         left join FactoryRegion h on b.regionId=h.id 
                         left join FactoryRegion i on h.parentid=i.id 
                         left join FactoryRegion j on i.parentid=j.id 
                         where 1=1 {subFilter}) aa group by name,chalarm,description 
                         order by {sort} {order} limit {(pageIndex - 1) * pageSize},{pageSize}";
            var dtos = CurrDb.FindList<AnalysisDto>(sql);
            sql = $@"select count(1) from (select name,description,sum(count) as count from (
                         select name,description,t.count,t.mcacode from (                    
                         select AlarmCode as name,AlarmDesc as description,mcacode,SUM(1) as count 
                         from McaEventStatisticByDay a 
						 where 1=1 {filter} group by alarmcode,alarmdesc,
                         mcacode) t 
                         left join Machine b on t.mcacode=b.FCode  
                         left join MacTProcess c on b.id=c.MacId 
						 left join TProcess d on c.Pcode=d.FCode 
                         left join MacModel e on b.MModeId= e.id 
INNER JOIN mtbacode p on p.MModeID=e.id and p.alarmcode=t.name
						 left join Supplier f on e.Supplierid=f.id
                         left join FactoryRegion g on b.factoryId=g.id 
                         left join FactoryRegion h on b.regionId=h.id 
                         left join FactoryRegion i on h.parentid=i.id 
                         left join FactoryRegion j on i.parentid=j.id 
                         where 1=1 {subFilter}) aa group by name,description) temp";
            total = Convert.ToInt32(CurrDb.FindObject(sql) ?? "0");
            return dtos;
        }
        public IEnumerable<DllEapEntity.OFILM.PostbondviewDto> GetLHADataAnalysis(string filter, string subFilter, int pageSize, int pageIndex, out int total, string sort = "time", string order = "asc")
        {
            if (string.IsNullOrEmpty(sort) || sort == "null")
                sort = "time";
            if (string.IsNullOrEmpty(order) || order == "null")
                order = "asc";
            string sql = $@"SELECT
	                            {OfilmRecipeProvider.GenRecipeString("b")} recipe,g.FCode,
h.rownum,h.Time,h.MacCode,h.OffsetX,h.OffsetY,h.OffsetT,diff_X,diff_Y,diff_T
                            FROM
	                           --  	(SELECT * FROM  postbondview a FORCE INDEX(Time) where 1=1 {filter}) h
-- postbondview p
(
select  t1.rownum rownum, t2.rownum t2_rownum,
t1.Time,t1.MacCode,
t1.OffsetX ,t1.OffsetY,t1.OffsetT,
(t1.OffsetX - t2.OffsetX) diff_X,
(t1.OffsetY - t2.OffsetY) diff_Y,
(t1.OffsetT - t2.OffsetT) diff_T
from
(SELECT
	(@rownum :=@rownum+1) as  rownum,p.Time,p.OffsetX,p.OffsetY,p.OffsetT,p.MacCode
FROM
	postbondview p  FORCE INDEX(Time),(select @rownum :=0) r
    where 1=1 {filter}
	ORDER BY p.time
) t1
inner JOIN
(SELECT
	(@index :=@index+1) as  rownum,p.Time,p.OffsetX,p.OffsetY,p.OffsetT,p.MacCode
FROM
	postbondview p  FORCE INDEX(Time),(select @index :=0) r
    where 1=1 {filter}
	ORDER BY p.time
) t2
ON  t1.rownum-1 = t2.rownum) h
	                            LEFT JOIN machine a ON a.fcode = h.maccode
	                            LEFT JOIN macstatus01 b ON a.FCode = b.maccode
	                            LEFT JOIN mactprocess c ON a.id = c.macid
	                            LEFT JOIN tprocess g ON c.pcode = g.fcode
                         where 1=1 {subFilter}
                         order by {sort} {order} limit {(pageIndex - 1) * pageSize},{pageSize}";
            var dtos = CurrDb.FindList<DllEapEntity.OFILM.PostbondviewDto>(sql);
            sql = $@" SELECT count(1) from postbondview p where 1=1 {filter} ";
            total = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
            // 第一行极差值为0
            if (dtos.Count() >= 1)
            {
                dtos.FirstOrDefault().Diff_X = "0";
                dtos.FirstOrDefault().Diff_Y = "0";
                dtos.FirstOrDefault().Diff_T = "0";
            }
            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, string subFilter, int take = 10)
        {
            int total;
            var dto = this.GetAlarmCodeAnalysis(filter, subFilter, 10, 1, out total).OrderByDescending(c => c.count).Take(take).OrderBy(c => c.maccode);
            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, string subFilter, string sub, int start, int length, out int total, string sort = "ptime", string order = "desc")
        {
            if (string.IsNullOrEmpty(sort) || sort == "null")
                sort = "ptime";
            if (string.IsNullOrEmpty(order) || order == "null")
                order = "desc";
            string sql = $@"select tt.maccode as maccode,b.Fname as macname,tt.name as name,tt.description as description,
                            c.Pcode,d.fname as pname,e.FCode as macmodel,tt.ptime as ptime,b.RegionId,g.FName FactoryName,
                            i.FName FloorName,j.FName PlantName,p.chalarm  
                            from 
                            (select a.McaCode as maccode,a.AlarmCode as name,a.AlarmDesc as description,a.StartTime as ptime 
                            from  McaEventStatisticByDay a FORCE INDEX(starttime) where 1=1 {filter}) tt  
                            left join machine b on tt.maccode=b.FCode 
                            left join mactprocess c on b.id=c.macid 
                            left join tprocess d on c.pcode=d.fcode 
                            inner join MacModel e on b.MModeId= e.id 
                            INNER JOIN mtbacode p on p.MModeID=e.id and p.alarmcode=tt.name 
                            left join Supplier f on e.SupplierId=f.id 
                            left join FactoryRegion g on b.factoryId = g.id 
                            left join FactoryRegion h on b.regionId=h.id 
                            left join FactoryRegion i on h.parentId = i.id 
                            left join FactoryRegion j on i.parentId = j.id 
                            where 1=1 and b.isanalysis<>-1 {subFilter} {sub} order by {sort} {order} limit {start - 1},{length}";
            var datas = CurrDb.FindList<AlarmCode>(sql);
            sql = $@"select sum(alarmcount) from (select mcacode,count(1) as alarmcount from McaEventStatisticByDay a
                     where 1=1 {filter} and EventCode='C00007' 
                     and EventCode='C00007' group by mcacode ) tt 
                     inner join
                     (select b.fcode from machine b
                     inner join mactprocess c on b.id=c.macid 
                     inner join tprocess d on c.pcode=d.fcode 
                     inner join MacModel e on b.MModeId= e.id 
-- 		INNER JOIN mtbacode p ON p.MModeID = e.id
                     inner join Supplier f on e.SupplierId=f.id 
                     inner join FactoryRegion g on b.factoryId = g.id 
                     inner join FactoryRegion h on b.regionId=h.id 
                     inner join FactoryRegion i on h.parentId = i.id 
                     inner join FactoryRegion j on i.parentId = j.id 
                     where 1=1 {subFilter}) macinfo on tt.mcacode=macinfo.fcode ";
            total = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
            return datas;
        }

        public int GetAlarmCodeCount(string filter)
        {
            string sql = $@"select count(*)
                         from McaEventStatisticByDay a  force index(starttime)
                         where 1=1  
                         and a.eventcode='C00007' {filter}";
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
        }

        public IEnumerable<MacTProcess> GetAllMachines(string filter)
        {
            string sql = $@"select a.*,b.FCode MacCode,b.FName MacName,d.FCode MacModel,c.FName ProcessName,
                        c.FCode ProcessCode,d.FName MacModelName,e.FName factory,f.FName line,g.FName floor from MacTProcess a 
                        left join Machine b on a.MacID=b.ID 
                        left join TProcess c on a.PCode = c.FCode 
                        left join MacModel d on b.MModeId=d.id 
                        left join factoryregion e on b.factoryId=e.id
                        left join factoryregion f on b.regionId=f.id
                        left join factoryregion g on f.parentid=g.id
                        where 1=1 {filter}";
            var machines = CurrDb.FindList<MacTProcess>(sql);
            if (machines != null && machines.Count() > 0)
            {
                foreach (var item in machines)
                {
                    item.RegionName = ((!string.IsNullOrEmpty(item.Factory)) ? ("/" + item.Factory) : "")
                + ((!string.IsNullOrEmpty(item.Floor)) ? ("/" + item.Floor) : "")
                + ((!string.IsNullOrEmpty(item.Line)) ? ("/" + item.Line) : "");
                    if (!string.IsNullOrEmpty(item.RegionName))
                        item.RegionName = item.RegionName.Substring(1);
                }
            }
            return machines;
        }

        /// <summary>
        /// 获取MTBF统计
        /// </summary>
        /// <param name="mcaVMsts"></param>
        /// <returns></returns>
        public IEnumerable<MTBFDto> GetMTBFs(string filter, string subFilter, string type)
        {
            var msts = GetStatisticDetails($" and EventCode = '{DllHsms.StandardCode.CEID_Run}'" + filter, subFilter).Where(c => c.TimeSpan != null);
            var alarms = GetStatisticDetails($" and EventCode = '{DllHsms.StandardCode.CEID_AlarmOccurred}'" + filter, subFilter).Where(c => c.TimeSpan != null);
            switch (type)
            {
                case "machine":
                    var groups = alarms.GroupBy(c => new { c.McaCode, c.McaName });
                    var list = new List<MTBFDto>();
                    foreach (var item in groups)
                    {
                        var mtbf = new MTBFDto()
                        {
                            name = item.Key.McaName,
                            count = item.Count(),
                            code = item.Key.McaCode,
                            pcode = item.FirstOrDefault().PCode,
                            minutes = msts.Where(c => c.McaCode == item.Key.McaCode).Sum(c =>
                            {
                                return (float)(c.TimeSpan == null ? 0 : c.TimeSpan) / 60f;
                            })
                        };
                        mtbf.MTBF = mtbf.minutes / mtbf.count;
                        list.Add(mtbf);
                    }
                    return list.Where(c => !string.IsNullOrEmpty(c.code)).OrderByDescending(c => c.MTBF).Take(10);
                case "macmodel":
                    var groupsMacModels = alarms.GroupBy(c => new { c.MacModel });
                    var listMacModels = new List<MTBFDto>();
                    foreach (var item in groupsMacModels)
                    {
                        var mtbf = new MTBFDto()
                        {
                            name = item.Key.MacModel,
                            count = item.Count(),
                            code = item.Key.MacModel,
                            minutes = msts.Where(c => c.MacModel == item.Key.MacModel).Sum(c =>
                            {
                                return (float)(c.TimeSpan == null ? 0 : c.TimeSpan) / 60f;
                            })
                        };
                        mtbf.MTBF = mtbf.minutes / mtbf.count;
                        listMacModels.Add(mtbf);
                    }
                    return listMacModels.Where(c => !string.IsNullOrEmpty(c.code)).OrderByDescending(c => c.MTBF).Take(10);
                case "process":
                    var groupProcesses = alarms.GroupBy(c => new { c.PCode, c.PName });
                    var listProcesses = new List<MTBFDto>();
                    foreach (var item in groupProcesses)
                    {
                        var mtbf = new MTBFDto()
                        {
                            name = item.Key.PName,
                            count = item.Count(),
                            code = item.Key.PCode,
                            minutes = msts.Where(c => c.PCode == item.Key.PCode).Sum(c =>
                            {
                                return (float)(c.TimeSpan == null ? 0 : c.TimeSpan) / 60f;
                            })
                        };
                        mtbf.MTBF = mtbf.minutes / mtbf.count;
                        listProcesses.Add(mtbf);
                    }
                    return listProcesses.Where(c => !string.IsNullOrEmpty(c.code)).OrderByDescending(c => c.MTBF).Take(10);
                default:
                    return null;
            }
        }

        public string AppendFilter(string type, DateType dateType, IEnumerable<string> codes)
        {
            if (codes == null || codes.Count() <= 0)
                return null;
            var filterCodes = string.Join(",", codes.Select(c => $"'{c}'"));
            var typeFilter = "";
            if (type == "machine")
            {
                typeFilter = $" and a.McaCode in ({filterCodes})";
            }
            else if (type == "macmodel")
            {
                typeFilter = $" and c.FCode in ({filterCodes})";
            }
            else
            {
                typeFilter = $" and c.PCode in ({filterCodes})";
            }
            return typeFilter;
        }

        public EChartsBar GetMTBFsNew(string type, DateType dateType, string filter, string subFilter, IEnumerable<string> codes)
        {

            if (string.IsNullOrEmpty(filter))
                return null;
            //var msts = GetStatisticDetails($" and EventCode = '{DllHsms.StandardCode.CEID_Run}'" + filter, subFilter).Where(c => c.TimeSpan != null);
            //var alarms = GetStatisticDetails($" and EventCode = '{DllHsms.StandardCode.CEID_AlarmOccurred}'" + filter, subFilter).Where(c => c.TimeSpan != null);
            switch (type)
            {
                case "machine":
                    filter += AppendFilter(type, dateType, codes);
                    return GetBarsForMachine(filter, subFilter, dateType);
                case "macmodel":
                    subFilter += AppendFilter(type, dateType, codes);
                    return GetBarsForMacModel(filter, subFilter, dateType);
                case "process":
                    subFilter += AppendFilter(type, dateType, codes);
                    return GetBarsForProcess(filter, subFilter, dateType);
                default:
                    return null;
            }
        }

        public EChartsBar GetMtbfForDisplay(string filter)
        {
            var msts = GetStatisticDetailsForDisplay($" and EventCode = '{DllHsms.StandardCode.CEID_Run}'" + filter).Where(c => c.TimeSpan != null);
            var alarms = GetStatisticDetailsForDisplay($" and EventCode = '{DllHsms.StandardCode.CEID_AlarmOccurred}'" + filter).Where(c => c.TimeSpan != null);
            alarms = alarms.Where(c => !string.IsNullOrEmpty(c.PCode)).OrderBy(c => c.StartTime);
            msts = msts.Where(c => !string.IsNullOrEmpty(c.PCode));
            var barData = new List<EChartsBarItem>();
            // DateTime dateNow = DateTime.Now;
            var min = 0;
            //   var max = alarms.Select(c => Convert.ToInt32(c.Hour)).Max();
            var max = DateTime.Now.Hour;
            var groups = alarms.GroupBy(c => new { c.Hour, c.PCode, c.PName });
            var hours = groups
                .Select(c => c.Key.Hour).Distinct();
            for (var i = 0; i < max; i++)
            {
                var barItemDatas = new List<BarItemData>();
                var yearGroups = alarms.Where(c => Convert.ToInt32(c.Hour) == min + i).GroupBy(c => new { c.PCode, c.PName });
                var list = new List<MTBFDto>();
                if (yearGroups == null || yearGroups.Count() <= 0)
                {
                    var mtbf = new MTBFDto()
                    {
                        name = "装片",
                        code = "DB",
                        MTBF = 0
                    };
                    list.Add(mtbf);
                    barItemDatas.Add(new BarItemData { Code = mtbf.code, Value = mtbf.MTBF.Value });
                    list.Add(new MTBFDto
                    {
                        name = "焊线",
                        code = "WB",
                        MTBF = 0
                    });
                    barItemDatas.Add(new BarItemData { Code = "WB", Value = 0 });
                }
                else
                {
                    foreach (var item in yearGroups)
                    {
                        var mtbf = new MTBFDto()
                        {
                            name = item.Key.PName,
                            count = item.Count(),
                            code = item.Key.PCode,
                            pcode = item.FirstOrDefault().PCode,
                            minutes = msts.Where(c => c.PCode == item.Key.PCode && Convert.ToInt32(c.Hour) == min + i).Sum(c =>
                            {
                                return (float)(c.TimeSpan == null ? 0 : c.TimeSpan) / 60f;
                            })
                        };
                        mtbf.MTBF = mtbf.minutes / mtbf.count;
                        list.Add(mtbf);
                        barItemDatas.Add(new BarItemData { Code = item.Key.PCode, Value = mtbf.MTBF.Value });
                    }
                }
                barData.Add(new EChartsBarItem { Title = (min + i).ToString(), Data = barItemDatas });
            }
            var bar = new EChartsBar();
            bar.Legend = alarms.Select(c => c.PCode).Distinct().ToArray();
            bar.Sources = barData;
            return bar;
        }

        private string CreateMachineMTBASql(string filter, string subFilter, string eventCode, DateType dateType)
        {
            string sql = "";
            switch (dateType)
            {
                case DateType.Year:
                    sql = $@"select t.date,t.count,t.timelen,t.mcacode from 
                             (select year(a.starttime) as date,count(1) as count,sum(timespan) timelen,mcacode 
                             from mcaeventstatisticbyday a
                             where EventCode='{eventCode}' {filter} 
                             group by year(a.starttime),mcacode) t
                             left join machine b on t.mcacode=b.Fcode
                             where 1=1 {subFilter}";
                    break;
                case DateType.Month:
                    sql = $@"select t.date,t.count,t.timelen,t.mcacode from 
                             (select concat_ws('-',year(a.starttime),month(a.starttime)) as date,count(1) as count,sum(timespan) timelen,mcacode 
                             from mcaeventstatisticbyday a
                             where EventCode='{eventCode}' {filter} 
                             group by concat_ws('-',year(a.starttime),month(a.starttime)),mcacode) t
                             left join machine b on t.mcacode=b.Fcode
                             where 1=1 {subFilter}";
                    break;
                case DateType.Day:
                    sql = $@"select t.date,t.count,t.timelen,t.mcacode from 
                             (select concat_ws('-',year(a.starttime),month(a.starttime),day(a.starttime)) as date,count(1) as count,sum(timespan) timelen,mcacode 
                             from mcaeventstatisticbyday a
                             where EventCode='{eventCode}' {filter} 
                             group by concat_ws('-',year(a.starttime),month(a.starttime),day(a.starttime)),mcacode) t
                             left join machine b on t.mcacode=b.Fcode
                             where 1=1 {subFilter}";
                    break;
            }
            return sql;
        }

        private string CreateMacModelMTBASql(string filter, string subFilter, string eventCode, DateType dateType)
        {
            string sql = "";
            switch (dateType)
            {
                case DateType.Year:
                    sql = $@"select t.date,sum(t.count) count,sum(t.timelen) timelen,c.fcode macmodel from 
                             (select year(a.starttime) as date,count(1) as count,sum(timespan) timelen,mcacode 
                             from mcaeventstatisticbyday a
                             where eventCode='{eventCode}' {filter} 
                             group by year(a.starttime),mcacode) t
                             left join machine b on t.mcacode=b.Fcode
                             left join macmodel c on b.mmodeid=c.id 
                             where 1=1 {subFilter}
                             group by t.date,c.fcode";
                    break;
                case DateType.Month:
                    sql = $@"select t.date,sum(t.count) count,sum(t.timelen) timelen,c.fcode macmodel from 
                             (select concat_ws('-',year(a.starttime),month(a.starttime)) as date,count(1) as count,sum(timespan) timelen,mcacode 
                             from mcaeventstatisticbyday a
                             where EventCode='{eventCode}' {filter} 
                             group by concat_ws('-',year(a.starttime),month(a.starttime)),mcacode) t
                             left join machine b on t.mcacode=b.Fcode
                             left join macmodel c on b.mmodeid=c.id 
                             where 1=1 {subFilter}
                             group by t.date,c.fcode";
                    break;
                case DateType.Day:
                    sql = $@"select t.date,sum(t.count) count,sum(t.timelen) timelen,c.fcode macmodel from 
                             (select concat_ws('-',year(a.starttime),month(a.starttime),day(a.starttime)) as date,count(1) as count,sum(timespan) timelen,mcacode 
                             from mcaeventstatisticbyday a
                             where EventCode='{eventCode}' {filter} 
                             group by concat_ws('-',year(a.starttime),month(a.starttime),day(a.starttime)),mcacode) t
                             left join machine b on t.mcacode=b.Fcode
                             left join macmodel c on b.mmodeid=c.id
                             where 1=1 {subFilter}
                             group by t.date,c.fcode";
                    break;
            }
            return sql;
        }

        private string CreatePCodeMTBASql(string filter, string subFilter, string eventCode, DateType dateType)
        {
            string sql = "";
            switch (dateType)
            {
                case DateType.Year:
                    sql = $@"select t.date,sum(t.count) count,sum(t.timelen) timelen,c.pcode Pcode,d.FName pname from 
                             (select year(a.starttime) as date,count(1) as count,sum(timespan) timelen,mcacode 
                             from mcaeventstatisticbyday a
                             where EventCode='{eventCode}' {filter} 
                             group by year(a.starttime),mcacode) t
                             left join machine b on t.mcacode=b.Fcode
                             left join mactprocess c on b.id=c.macid 
                             left join tprocess d on c.pcode=d.fcode
                             where 1=1 {subFilter}
                             group by t.date,c.pcode,d.FName";
                    break;
                case DateType.Month:
                    sql = $@"select t.date,sum(t.count) count,sum(t.timelen) timelen,c.pcode Pcode,d.FName pname from 
                             (select concat_ws('-',year(a.starttime),month(a.starttime)) as date,count(1) as count,sum(timespan) timelen,mcacode 
                             from mcaeventstatisticbyday a
                             where EventCode='{eventCode}' {filter} 
                             group by concat_ws('-',year(a.starttime),month(a.starttime)),mcacode) t
                             left join machine b on t.mcacode=b.Fcode
                             left join mactprocess c on b.id=c.macid 
                             left join tprocess d on c.pcode=d.fcode
                             where 1=1 {subFilter}
                             group by t.date,c.pcode,d.FName";
                    break;
                case DateType.Day:
                    sql = $@"select t.date,sum(t.count) count,sum(t.timelen) timelen,c.pcode Pcode,d.FName pname from 
                             (select concat_ws('-',year(a.starttime),month(a.starttime),day(a.starttime)) as date,count(1) as count,sum(timespan) timelen,mcacode 
                             from mcaeventstatisticbyday a
                             where EventCode='{eventCode}' {filter} 
                             group by concat_ws('-',year(a.starttime),month(a.starttime),day(a.starttime)),mcacode) t
                             left join machine b on t.mcacode=b.Fcode
                             left join mactprocess c on b.id=c.macid 
                             left join tprocess d on c.pcode=d.fcode
                             where 1=1 {subFilter}
                             group by t.date,c.pcode,d.FName";
                    break;
            }
            return sql;
        }

        public EChartsBar GetBarsForMachine(string filter, string subFilter, DateType dateType)
        {
            var barData = new List<EChartsBarItem>();
            string[] legends = null;
            var alarms = CurrDb.FindList<MTBFFromDb>(CreateMachineMTBASql(filter, subFilter, StandardCode.CEID_AlarmOccurred, dateType));
            var runs = CurrDb.FindList<MTBFFromDb>(CreateMachineMTBASql(filter, subFilter, StandardCode.CEID_Run, dateType));
            var list = new List<MTBFDto>();
            IList<BarItemData> barItemDatas = null;
            var dateGroups = runs.GroupBy(c => c.Date).OrderBy(c => c.Key.Length).ThenBy(c => c.Key);
            foreach (var dateItem in dateGroups)
            {
                barItemDatas = new List<BarItemData>();
                foreach (var item in dateItem)
                {
                    var count = 0;
                    var currAlarms = alarms.Where(c => c.McaCode == item.McaCode && c.Date == item.Date);
                    if (currAlarms == null || currAlarms.Count() <= 0)
                    {
                        count = 1;
                    }
                    else
                    {
                        count = currAlarms.Sum(c => c.Count);
                    }
                    var mtbf = new MTBFDto()
                    {
                        name = item.McaCode,
                        count = count,
                        code = item.McaCode,
                        pcode = item.PCode,
                        minutes = item.TimeLen / 60f
                    };
                    mtbf.MTBF = mtbf.minutes / (mtbf.count == 0 ? 1 : mtbf.count);
                    list.Add(mtbf);
                    barItemDatas.Add(new BarItemData { Code = item.McaCode, Value = mtbf.MTBF.Value });
                }
                barData.Add(new EChartsBarItem { Title = dateItem.Key, Data = barItemDatas });
            }
            legends = runs.Select(c => c.McaCode).Distinct().ToArray();
            var bar = new EChartsBar();
            bar.Legend = legends;
            bar.Sources = barData;
            return bar;
        }

        public EChartsBar GetBarsForMacModel(string filter, string subFilter, DateType dateType)
        {
            var barData = new List<EChartsBarItem>();
            string[] legends = null;
            var alarms = CurrDb.FindList<MTBFFromDb>(CreateMacModelMTBASql(filter, subFilter, StandardCode.CEID_AlarmOccurred, dateType));
            var runs = CurrDb.FindList<MTBFFromDb>(CreateMacModelMTBASql(filter, subFilter, StandardCode.CEID_Run, dateType));
            var list = new List<MTBFDto>();
            IList<BarItemData> barItemDatas = null;
            var dateGroups = runs.GroupBy(c => c.Date).OrderBy(c => c.Key.Length).ThenBy(c => c.Key); ;
            foreach (var dateItem in dateGroups)
            {
                barItemDatas = new List<BarItemData>();
                foreach (var item in dateItem)
                {
                    var count = 0;
                    var currAlarms = alarms.Where(c => c.MacModel == item.MacModel && c.Date == item.Date);
                    if (currAlarms == null || currAlarms.Count() <= 0)
                    {
                        count = 1;
                    }
                    else
                    {
                        count = currAlarms.Sum(c => c.Count);
                    }
                    var mtbf = new MTBFDto()
                    {
                        name = item.MacModel,
                        count = count,
                        code = item.MacModel,
                        pcode = item.PCode,
                        minutes = item.TimeLen / 60f
                    };
                    mtbf.MTBF = mtbf.minutes / (mtbf.count == 0 ? 1 : mtbf.count);
                    list.Add(mtbf);
                    barItemDatas.Add(new BarItemData { Code = item.MacModel, Value = mtbf.MTBF.Value });
                }
                barData.Add(new EChartsBarItem { Title = dateItem.Key, Data = barItemDatas });
            }
            legends = runs.Select(c => c.MacModel).Distinct().ToArray();
            var bar = new EChartsBar();
            bar.Legend = legends;
            bar.Sources = barData;
            return bar;
        }

        public EChartsBar GetBarsForProcess(string filter, string subFilter, DateType dateType)
        {
            var barData = new List<EChartsBarItem>();
            string[] legends = null;
            var alarms = CurrDb.FindList<MTBFFromDb>(CreatePCodeMTBASql(filter, subFilter, StandardCode.CEID_AlarmOccurred, dateType));
            var runs = CurrDb.FindList<MTBFFromDb>(CreatePCodeMTBASql(filter, subFilter, StandardCode.CEID_Run, dateType));
            var list = new List<MTBFDto>();
            IList<BarItemData> barItemDatas = null;
            var dateGroups = runs.GroupBy(c => c.Date).OrderBy(c => c.Key.Length).ThenBy(c => c.Key); ;
            foreach (var dateItem in dateGroups)
            {
                barItemDatas = new List<BarItemData>();
                foreach (var item in dateItem)
                {
                    var count = 0;
                    var currAlarms = alarms.Where(c => c.PCode == item.PCode && c.Date == item.Date);
                    if (currAlarms == null || currAlarms.Count() <= 0)
                    {
                        count = 1;
                    }
                    else
                    {
                        count = currAlarms.Sum(c => c.Count);
                    }
                    var mtbf = new MTBFDto()
                    {
                        name = item.PCode,
                        count = count,
                        code = item.PCode,
                        pcode = item.PCode,
                        minutes = item.TimeLen / 60f
                    };
                    mtbf.MTBF = mtbf.minutes / (mtbf.count == 0 ? 1 : mtbf.count);
                    list.Add(mtbf);
                    barItemDatas.Add(new BarItemData { Code = item.PCode, Value = mtbf.MTBF.Value });
                }
                barData.Add(new EChartsBarItem { Title = dateItem.Key, Data = barItemDatas });
            }
            legends = runs.Select(c => c.PCode).Distinct().ToArray();
            var bar = new EChartsBar();
            bar.Legend = legends;
            bar.Sources = barData;
            return bar;
        }

        /// <summary>
        /// 组装返回客户端的最终数据
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public LineDateDto GetAlarmTimeDtos(string filter, string subFilter, string type)
        {
            var dtos = GetDatasFromDb(filter, subFilter, 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.AddDays(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;
        }

        public LineDateDtoForDisplay GetAlarmTimeDtosForScreenDisplay(string filter)
        {
            var sql = "select c.PCode as name,SUM(1) as count,SUBSTRING(CONVERT(varchar(50),StartTime,24),0,3) time from McaEventStatisticByDay a " +
                      "left join machine b on a.mcacode=b.Fcode " +
                      "left join mactprocess c on b.id=c.macid "
                       + "where  1=1 and b.isanalysis<>-1 "
                       + $" {filter} "
                       + "group by c.PCode,SUBSTRING(CONVERT(varchar(50), StartTime, 24), 0, 3)  order by c.PCode,SUBSTRING(CONVERT(varchar(50), StartTime, 24), 0, 3)";
            var dtos = CurrDb.FindList<AlarmTimeDtoForScreenDisplay>(sql);
            if (dtos == null || dtos.Count() == 0)
                return null;
            var dateStart = dtos.Select(c => Convert.ToInt32(c.Time)).Min(c => c);    //统计开始时间
            var dateEnd = dtos.Select(c => Convert.ToInt32(c.Time)).Max(c => c);      //统计结束时间
            int timeSpan = dateEnd - dateStart;
            var hours = timeSpan;
            var groups = dtos.GroupBy(c => c.Name);
            var series = new List<LineSeriesForDisplay>();
            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 <= hours; i++)
                    {
                        if (temps.FirstOrDefault(c => Convert.ToInt32(c.Time) == (dateStart + i)) == null)
                        {
                            temps.Add(new AlarmTimeDtoForScreenDisplay()
                            {
                                Name = item.Key,
                                Count = 0,
                                Time = (dateStart + i).ToString()
                            });
                        }
                    }
                    series.Add(new LineSeriesForDisplay()
                    {
                        Name = item.Key,
                        AlarmTimeDtos = temps.OrderBy(c => c.Time)
                    });
                }
            }
            var dates = new List<string>();
            for (var j = 0; j <= hours; j++)
            {
                dates.Add((dateStart + j).ToString());
            }
            var lineDateDto = new LineDateDtoForDisplay()
            {
                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 subFilter, string type)
        {
            string sql = string.Empty;
            switch (type)
            {
                case "machine":
                    sql = $@"select t.name,t.count,t.time from
                             (select a.mcacode as name,SUM(1) as count,substring(DATE_FORMAT(a.StartTime,'%Y-%m-%d'),1,11) time 
                             from McaEventStatisticByDay a 
                             where 1=1 {filter}
                             group by a.mcaCode,substring(DATE_FORMAT(a.StartTime,'%Y-%m-%d'),1,11)) t
                             left join machine b on t.name=b.FCode 
                             where 1=1  and b.isanalysis<>-1 {subFilter}  
                             order by t.name,t.time ";
                    break;
                case "macmodel":
                    sql = $@"select c.FCode name,sum(t.count) as count,t.time from (
                             select a.mcacode,SUM(1) as count,substring(DATE_FORMAT(a.StartTime,'%Y-%m-%d'),1,11) time 
                             from McaEventStatisticByDay a 
                             where 1=1 {filter}
                             group by a.mcacode,substring(DATE_FORMAT(a.StartTime,'%Y-%m-%d'),1,11)  
                             ) t 
                             left join Machine b on t.mcacode=b.FCode
                             left join MacModel c on b.MModeId=c.id 
                             where b.isanalysis<>-1 {subFilter}
                             group by c.FCode,t.time
                             order by name,time";
                    break;
                case "process":
                    sql = $@"select c.pcode name,sum(t.count) as count,t.time from (
                             select a.mcacode,SUM(1) as count,substring(DATE_FORMAT(a.StartTime,'%Y-%m-%d'),1,11) time 
                             from McaEventStatisticByDay a 
                             where 1=1 {filter}
                             group by a.mcacode,substring(DATE_FORMAT(a.StartTime,'%Y-%m-%d'),1,11)
                             ) t
                             left join Machine b on t.mcacode=b.FCode 
                             left join mactprocess c on b.id=c.macid 
                             where 1=1 and b.isanalysis<>-1 {subFilter}
                             group by c.pcode,t.time
                             order by c.pcode,time";
                    break;
            }
            IEnumerable<AlarmTimeDto> dtos = CurrDb.FindList<AlarmTimeDto>(sql); ;
            return dtos;
        }

        /// <summary>
        /// 获取数据库中所有的机台的运行开始结束时间
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<McaEventStatisticByDay> GetEffiencyEntities(string filter, string subFilter)
        {
            var mcaEvents = this.GetStatisticDetails($"and EventCode='{DllHsms.StandardCode.CEID_Run}' {filter}", subFilter);
            return mcaEvents;
        }

        public IEnumerable<McaEventStatisticByDay> GetAlarms(string filter, string subFilter)
        {
            var alarms = this.GetStatisticDetails($"and EventCode='{DllHsms.StandardCode.CEID_AlarmOccurred}' {filter}", subFilter);
            return alarms;
        }

        public IEnumerable<StatusCountDto> GetStatusCounts(DateTime? dateStart, DateTime? dateEnd)
        {
            var filter = $" and a.etime>='{dateStart}' and a.etime<='{dateEnd}'";
            string sql = $"select a.maccode,c.fcode as macmodel,d.pcode,count(1) as count1 from macstatus a " +
                $"left join machine b on a.maccode=b.fcode " +
                $" left join macmodel c on b.mmodeid=c.id " +
                $" left join mactprocess d on b.id=d.macid where 1=1 {filter} and b.isanalysis<>-1 " +
               $"and statusid<>'{MacStatusVal.Run}' group by maccode,c.fcode,d.pcode";
            var stopDtos = CurrDb.FindList<StatusCountDto>(sql);
            return stopDtos;
        }

        /// <summary>
        /// 生成获取效率基础信息的SQL语句
        /// </summary>
        /// <param name="type"></param>
        /// <param name="filter"></param>
        /// <param name="subFilter"></param>
        /// <returns></returns>
        private string CreateEnffiencySql(string type, string filter, string subFilter, string macModel, string pCode)
        {
            string sql = string.Empty;
            if (!string.IsNullOrEmpty(macModel) && macModel != "null")
            {
                subFilter += $" and e.fcode='{macModel}'";
            }
            if (!string.IsNullOrEmpty(pCode) && pCode != "null")
            {
                subFilter += $" and d.fcode='{pCode}'";
            }
            switch (type)
            {
                case "pcode":
                    sql = $@"select count(1) count,sum(tt.timelength),c.pcode,tt.eventcode from (select sum(timespan) as timelength,mcacode,eventCode eventCode
                           from mcaeventstatisticbyday a 
                           where 1=1 {filter} 
                           group by mcacode,eventCode
                           )tt
                           left join Machine b on tt.McaCode=b.FCode 
                           left join MacTProcess c on b.id=c.MacID 
                           left join TProcess d on c.PCode=d.FCode  
                           left join MacModel e on b.MModeId=e.id 
                           left join Supplier f on e.SupplierId=f.id 
                           left join FactoryRegion g on b.FactoryId=g.id 
                           where 1=1 and b.isanalysis<>-1 and c.pcode is not null {subFilter}
                           group by c.pcode,tt.EventCode";
                    break;
                case "macmodel":
                    sql = $@"select count(1) count,sum(tt.timelength),c.pcode,e.fCode macmodel,tt.eventcode from (select sum(timespan) as timelength,mcacode,eventCode eventCode
                           from mcaeventstatisticbyday a 
                           where 1=1 {filter}
                           group by mcacode,eventCode
                           )tt
                           left join Machine b on tt.McaCode=b.FCode 
                           left join MacTProcess c on b.id=c.MacID 
                           left join TProcess d on c.PCode=d.FCode  
                           left join MacModel e on b.MModeId=e.id 
                           left join Supplier f on e.SupplierId=f.id 
                           left join FactoryRegion g on b.FactoryId=g.id 
                           where 1=1 and b.isanalysis<>-1 and c.pcode is not null {subFilter}
                           group by c.pcode,tt.EventCode,e.fcode";
                    break;
                case "machine":
                    sql = $@"select sum(tt.timelength) timeLength,c.pcode,e.fCode macmodel,mcacode,tt.eventcode from (select sum(timespan) as timelength,mcacode,eventCode eventCode
                           from mcaeventstatisticbyday a 
                           where 1=1 {filter}
                           group by mcacode,eventCode
                           )tt
                           left join Machine b on tt.McaCode=b.FCode 
                           left join MacTProcess c on b.id=c.MacID 
                           left join TProcess d on c.PCode=d.FCode  
                           left join MacModel e on b.MModeId=e.id 
                           left join Supplier f on e.SupplierId=f.id 
                           left join FactoryRegion g on b.FactoryId=g.id
                           where 1=1 and b.isanalysis<>-1 and c.pcode is not null {subFilter}
                           group by c.pcode,tt.EventCode,e.fcode,tt.mcacode";
                    break;
            }
            return sql;
        }

        private string CreateEnffiencyMachineSql(string filter, string subFilter, int start, int pageSize, string sortField, string sortOrder, double se)
        {
            string sql = string.Empty;
            sql = $@"SELECT
	FactoryName,
	FloorName,
	PlantName,
	PCode,
	modelName,
	MacCode,
    macnumber,
	SUM( runTime ) runTime,
	SUM( idleTime ) idleTime,
	SUM( errorTime ) errorTime,
	SUM( allTime ) allTime,
	ROUND(SUM( runTime )/SUM( allTime )* 100, 1 ) runrRate,
	ROUND(SUM( idleTime )/SUM( allTime )* 100, 1 ) idleRate,
	ROUND( sum( errorTime) / SUM( allTime ) * 100, 1 ) 	 errorRate,
	IFNULL( ROUND( (sum( CASE  WHEN faulttype=1 THEN errorTime WHEN faulttype='' THEN  errorTime WHEN faulttype IS NULL THEN errorTime ELSE 0 END ) ) / SUM( allTime ) * 100, 1 ), 0 ) errorRateOne,
	IFNULL( ROUND( (sum( CASE faulttype WHEN 2 THEN errorTime ELSE 0 END ) ) / SUM( allTime ) * 100, 1 ), 0 ) errorRateTwo,
	sum( CASE  WHEN faulttype=1 THEN errorTime WHEN faulttype='' THEN  errorTime WHEN faulttype IS NULL THEN errorTime ELSE 0 END ) errorTimeOne,
	sum( CASE faulttype WHEN 2 THEN errorTime ELSE 0 END ) errorTimeTwo 
FROM
	(
	SELECT
		p.faulttype,
		i.FName FactoryName,
		k.FName FloorName,
		l.Fname PlantName,
		f.PCode,
		d.FName modelName,
		a.FCode MacCode,
        machinenumber.macnumber macnumber,
		tt.AlarmCode,
		IFNULL( ROUND( tt.RUN / 60 / {se} * 100, 1 ), 0 ) runrRate,
		IFNULL( ROUND( tt.IDLE / 60 / {se} * 100, 1 ), 0 ) idleRate,
		IFNULL( ROUND( tt.ERROR / 60 / {se} * 100, 1 ), 0 ) errorRate,
		IFNULL( ROUND( tt.ERROR / 60 + tt.IDLE / 60 + tt.RUN/60 ), 0 ) allTime,
		IFNULL( ROUND( tt.RUN / 60 ), 0 ) runTime,
		IFNULL( ROUND( tt.IDLE / 60 ), 0 ) idleTime,
		IFNULL( ROUND( tt.ERROR / 60 ), 0 ) errorTime 
	FROM
		Machine a
		LEFT JOIN (
		SELECT
			a.MacCode,
			AlarmCode,
			sum( CASE a.StatusID WHEN 3 THEN ifnull( FLen, 0 ) ELSE 0 END ) IDLE,
			sum( CASE a.StatusID WHEN 4 THEN ifnull( FLen, 0 ) ELSE 0 END ) RUN,
			sum( CASE a.StatusID WHEN 6 THEN ifnull( FLen, 0 ) ELSE 0 END ) ERROR 
		FROM
			macstatus a 
		WHERE
			StatusID IN ( 3, 4, 6 ) 
			{filter}
		GROUP BY
			a.MacCode,
			a.AlarmCode 
		) tt ON tt.MacCode = a.FCode
		LEFT OUTER JOIN macstatus01 m ON a.fcode = m.maccode
		LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
		LEFT JOIN mtbacode p ON p.MModeID = d.id 
		AND p.AlarmCode = tt.AlarmCode
		LEFT OUTER JOIN mactprocess f ON a.id = f.macid
		LEFT OUTER JOIN tprocess g ON f.PCode = g.FCode
		LEFT OUTER JOIN factoryregion i ON a.factoryid = i.id
		LEFT OUTER JOIN factoryregion j ON a.regionid = j.id
		LEFT OUTER JOIN factoryregion k ON j.parentid = k.id
		LEFT OUTER JOIN factoryregion l ON k.parentid = l.id 
        left join machinenumber  on a.id=machinenumber.macid
	WHERE
		1 = 1  {subFilter}
-- 		AND a.FCode = 'AA00041' 
	ORDER BY
		tt.MacCode 
	) hh 
GROUP BY
	FactoryName,
	FloorName,
	PlantName,
	PCode,
	modelName,
	MacCode,
    macnumber
ORDER BY MacCode
	LIMIT {start - 1},{pageSize}";
            #region 原始
            //sql = $@"SELECT
            //                 i.FName FactoryName,
            //                 k.FName FloorName,
            //                    l.Fname PlantName,
            //                 f.PCode,
            //                 d.FName modelName,
            //                 a.FCode MacCode,
            //                 IFNULL(ROUND(tt.RUN /60/ {se}*100,1),0)  runrRate,
            //                 IFNULL(ROUND(tt.IDLE /60/ {se}*100,1),0) idleRate,
            //                 IFNULL(ROUND(tt.ERROR /60/ {se}*100,1),0) errorRate,
            //                 IFNULL(ROUND(tt.ERROR/ 60 + tt.IDLE/ 60 + tt.RUN),0) allTime,
            //                 IFNULL(ROUND(tt.RUN/ 60),0) runTime,
            //                 IFNULL(ROUND(tt.IDLE/ 60),0) idleTime 
            //                FROM Machine a
            //                LEFT JOIN (
            //                 SELECT
            //                  a.MacCode,
            //                  sum( CASE a.StatusID WHEN 3 THEN ifnull( FLen, 0 ) ELSE 0 END ) IDLE,
            //                  sum( CASE a.StatusID WHEN 4 THEN ifnull( FLen, 0 ) ELSE 0 END ) RUN,
            //                  sum( CASE a.StatusID WHEN 6 THEN ifnull( FLen, 0 ) ELSE 0 END ) ERROR 
            //                 FROM
            //                  macstatus a 
            //                 WHERE
            //                  StatusID in(3,4,6)
            //                  {filter}
            //                 GROUP BY
            //                  a.MacCode 

            //                 ) tt
            //                   ON tt.MacCode = a.FCode
            //                    LEFT OUTER JOIN macstatus01 m ON a.fcode = m.maccode
            //                 LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
            //                 LEFT OUTER JOIN mactprocess f ON a.id = f.macid
            //                 LEFT OUTER JOIN tprocess g ON f.PCode = g.FCode
            //                 LEFT OUTER JOIN factoryregion i ON a.factoryid = i.id
            //                 LEFT OUTER JOIN factoryregion j ON a.regionid = j.id
            //                 LEFT OUTER JOIN factoryregion k ON j.parentid = k.id 
            //                    LEFT OUTER JOIN factoryregion l ON k.parentid = l.id
            //                    where 1=1  {subFilter}
            //                 ORDER BY tt.MacCode
            //                    LIMIT {start - 1},{pageSize}	";
            #endregion
            return sql;
        }
        private string CreateEnffiencyMachineTotalSql(string filter, string subFilter, int start, int pageSize, string sortField, string sortOrder, double se)
        {
            string sql = string.Empty;
            sql = $@"SELECT
	                            
	                            ROUND(sum(tt.RUN) /60/ (sum(tt.errorTime)/ 60 + sum(tt.IDLE)/ 60 + sum(tt.RUN)/60) *100,1) runrRate,
	                            ROUND(sum(tt.IDLE) /60/ (sum(tt.errorTime)/ 60 + sum(tt.IDLE)/ 60 + sum(tt.RUN)/60) *100,1) idleRate,
	                            ROUND(sum(tt.errorTime) /60/ (sum(tt.errorTime)/ 60 + sum(tt.IDLE)/ 60 + sum(tt.RUN)/60)*100,1) errorRate,
	                            ROUND(sum(tt.errorTime)/ 60 + sum(tt.IDLE)/ 60 + sum(tt.RUN)/60) allTime,
	                            ROUND(sum(tt.RUN)/ 60) runTime,
	                            ROUND(sum(tt.IDLE)/ 60) idleTime,
 ROUND(SUM( errorTime/60 )) errorTime,
IFNULL( ROUND( (sum( CASE  WHEN faulttype=1 THEN errorTime WHEN faulttype='' THEN  errorTime WHEN faulttype IS NULL THEN errorTime ELSE 0 END ) )/60 / (sum(tt.errorTime)/ 60 + sum(tt.IDLE)/ 60 + sum(tt.RUN)/60) * 100, 1 ), 0 ) errorRateOne,
 	IFNULL( ROUND( (sum( CASE faulttype WHEN 2 THEN errorTime ELSE 0 END ) )/60 / (sum(tt.errorTime)/ 60 + sum(tt.IDLE)/ 60 + sum(tt.RUN)/60) * 100, 1 ), 0 ) errorRateTwo,
 	ROUND(sum( CASE  WHEN faulttype=1 THEN errorTime WHEN faulttype='' THEN  errorTime WHEN faulttype IS NULL THEN errorTime ELSE 0 END )/60)   errorTimeOne,
	ROUND(sum( CASE faulttype WHEN 2 THEN errorTime ELSE 0 END )/60) errorTimeTwo 
                            FROM Machine a
	                             LEFT OUTER JOIN (
	                            SELECT
		                            a.MacCode,
	                                AlarmCode,
		                            sum( CASE a.StatusID WHEN 3 THEN ifnull( FLen, 0 ) ELSE 0 END ) IDLE,
		                            sum( CASE a.StatusID WHEN 4 THEN ifnull( FLen, 0 ) ELSE 0 END ) RUN,
		                            sum( CASE a.StatusID WHEN 6 THEN ifnull( FLen, 0 ) ELSE 0 END ) errorTime 
	                            FROM
		                            macstatus a 
	                            WHERE
		                            StatusID in(3,4,6)
		                            {filter}
	                            GROUP BY
		                            a.MacCode ,
                                    a.AlarmCode 
 		                              
	                            ) tt ON tt.MacCode = a.FCode
                                LEFT OUTER JOIN macstatus01 m ON a.fcode = m.maccode 
	                            LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
                               LEFT JOIN mtbacode p ON p.MModeID = d.id 
								AND p.AlarmCode = tt.AlarmCode
	                            LEFT OUTER JOIN mactprocess f ON a.id = f.macid
	                            LEFT OUTER JOIN tprocess g ON f.PCode = g.FCode
	                            LEFT OUTER JOIN factoryregion i ON a.factoryid = i.id
	                            LEFT OUTER JOIN factoryregion j ON a.regionid = j.id
	                            LEFT OUTER JOIN factoryregion k ON j.parentid = k.id 
                                LEFT OUTER JOIN factoryregion l ON k.parentid = l.id
                                left join machinenumber  on a.id=machinenumber.macid
                                where 1=1  {subFilter} and l.fname not like '%试产%'
                                	";
            #region
            //    sql = $@"SELECT

            //                     ROUND(sum(tt.RUN) /60/ {se}*100,1) runrRate,
            //                     ROUND(sum(tt.IDLE) /60/ {se}*100,1) idleRate,
            //                     ROUND(sum(tt.ERROR) /60/ {se}*100,1) errorRate,
            //                     ROUND(sum(tt.ERROR)/ 60 + sum(tt.IDLE)/ 60 + sum(tt.RUN)/60) allTime,
            //                     ROUND(sum(tt.RUN)/ 60/Count(tt.MacCode)) runTime,
            //                     ROUND(sum(tt.IDLE)/ 60/Count(tt.MacCode)) idleTime
            //                    FROM Machine a
            //                      LEFT OUTER JOIN (
            //                     SELECT
            //                      a.MacCode,
            //                         AlarmCode,
            //                      sum( CASE a.StatusID WHEN 3 THEN ifnull( FLen, 0 ) ELSE 0 END ) IDLE,
            //                      sum( CASE a.StatusID WHEN 4 THEN ifnull( FLen, 0 ) ELSE 0 END ) RUN,
            //                      sum( CASE a.StatusID WHEN 6 THEN ifnull( FLen, 0 ) ELSE 0 END ) ERROR 
            //                     FROM
            //                      macstatus a 
            //                     WHERE
            //                      StatusID in(3,4,6)
            //                      {filter}
            //                     GROUP BY
            //                      a.MacCode ,
            //                            a.AlarmCode 

            //                     ) tt ON tt.MacCode = a.FCode
            //                        LEFT OUTER JOIN macstatus01 m ON a.fcode = m.maccode 
            //                     LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
            //                       LEFT JOIN mtbacode p ON p.MModeID = d.id 
            //AND p.AlarmCode = tt.AlarmCode
            //                     LEFT OUTER JOIN mactprocess f ON a.id = f.macid
            //                     LEFT OUTER JOIN tprocess g ON f.PCode = g.FCode
            //                     LEFT OUTER JOIN factoryregion i ON a.factoryid = i.id
            //                     LEFT OUTER JOIN factoryregion j ON a.regionid = j.id
            //                     LEFT OUTER JOIN factoryregion k ON j.parentid = k.id 
            //                        LEFT OUTER JOIN factoryregion l ON k.parentid = l.id 
            //                        where 1=1  {subFilter} and l.fname not like '%试产%'
            //                        	";
            #endregion

            return sql;
        }

        private string CreateEnffiencyFactorySql(string filter, double se)
        {
            string sql = string.Empty;
            sql = $@"select * from 
(
SELECT
	                    concat( i.FName,l.FName, k.FName ) FactoryName,
	                    i.id FactoryID,
	                    k.id FloorID,
	                    ROUND( sum( tt.RUN ) / 60 / {se}/count(tt.MacCode) * 100, 1 ) runrRate,
	                    ROUND( sum( tt.IDLE ) / 60 / {se}/count(tt.MacCode) * 100, 1 ) idleRate,
	                    ROUND( sum( tt.ERROR ) / 60 / {se}/count(tt.MacCode) * 100, 1 ) errorRate,
	                    count( aa.FCode)  allTime 
                    FROM
	                    (
	                    SELECT
		                    a.MacCode,
		                    sum( CASE a.StatusID WHEN 3 THEN ifnull( FLen, 0 ) ELSE 0 END ) IDLE,
		                    sum( CASE a.StatusID WHEN 4 THEN ifnull( FLen, 0 ) ELSE 0 END ) RUN,
		                    sum( CASE a.StatusID WHEN 6 THEN ifnull( FLen, 0 ) ELSE 0 END ) ERROR 
	                    FROM
		                    macstatus a 
	                    WHERE
		                    a.StatusID IN ( 3, 4, 6 ) 
		                   {filter} 
	                    GROUP BY
		                    a.MacCode 
	                    ) tt
	                    LEFT OUTER JOIN Machine aa ON tt.MacCode = aa.FCode
	                    LEFT OUTER JOIN factoryregion i ON aa.factoryid = i.id
	                    LEFT OUTER JOIN factoryregion j ON aa.regionid = j.id
	                    LEFT OUTER JOIN factoryregion k ON j.parentid = k.id 
                        LEFT OUTER JOIN factoryregion l ON k.parentid = l.id 
                    GROUP BY
	                    i.FName,
	                    i.id,
	                    k.FName,
                        l.FName,
	                    k.id) t
											WHERE t.FactoryName is not null";
            return sql;
        }
        private string CreateEnffiencyMachineCountSql(string filter, string subFilter, string sortField, string sortOrder)
        {
            string sql = string.Empty;
            sql = $@"SELECT
	                           count(*)
FROM
	(
	SELECT
		FCode 
	FROM
		(
		SELECT
			a.FCode 
                            FROM Machine a
	                           LEFT OUTER JOIN (
	                            SELECT
		                            a.MacCode,AlarmCode,
		                            sum( CASE a.StatusID WHEN 3 THEN ifnull( FLen, 0 ) ELSE 0 END ) IDLE,
		                            sum( CASE a.StatusID WHEN 4 THEN ifnull( FLen, 0 ) ELSE 0 END ) RUN,
		                            sum( CASE a.StatusID WHEN 6 THEN ifnull( FLen, 0 ) ELSE 0 END ) ERROR 
	                            FROM
		                            macstatus a 
	                            WHERE
		                            StatusID in(3,4,6)
		                            {filter}
	                            GROUP BY
		                            a.MacCode ,a.AlarmCode 
 		                             
	                            ) tt
	                              ON tt.MacCode = a.FCode 
                                LEFT OUTER JOIN macstatus01 m on a.FCode=m.MacCode 
	                            LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
LEFT JOIN mtbacode p ON p.MModeID = d.id 
																												AND p.AlarmCode = tt.AlarmCode
	                            LEFT OUTER JOIN mactprocess f ON a.id = f.macid
	                            LEFT OUTER JOIN tprocess g ON f.PCode = g.FCode
	                            LEFT OUTER JOIN factoryregion i ON a.factoryid = i.id
	                            LEFT OUTER JOIN factoryregion j ON a.regionid = j.id
	                            LEFT OUTER JOIN factoryregion k ON j.parentid = k.id 
                                LEFT OUTER JOIN factoryregion l ON k.parentid = l.id
                                left join machinenumber  on a.id=machinenumber.macid
                                where 1=1  {subFilter}
	                            ORDER BY tt.MacCode
) t 
	GROUP BY
	FCode 
	) l";
            return sql;
        }
        private string CreateEnffiencyMachineSqlExport(string filter, string subFilter, string sortField, string sortOrder, double se)
        {
            string sql = string.Empty;
            sql = $@"SELECT
	FactoryName,
	FloorName,
	PlantName,
	PCode,
	modelName,
	MacCode,
	SUM( runTime ) runTime,
	SUM( idleTime ) idleTime,
	SUM( errorTime ) errorTime,
	SUM( allTime ) allTime,
	SUM( runrRate ) runrRate,
	SUM( idleRate ) idleRate,
	ROUND( sum( errorTime) / {se} * 100, 1 ) 	 errorRate,
	IFNULL( ROUND( (sum( CASE  WHEN faulttype=1 THEN errorTime WHEN faulttype='' THEN  errorTime WHEN faulttype IS NULL THEN errorTime ELSE 0 END ) ) / {se} * 100, 1 ), 0 ) errorRateOne,
	IFNULL( ROUND( (sum( CASE faulttype WHEN 2 THEN errorTime ELSE 0 END ) ) / {se} * 100, 1 ), 0 ) errorRateTwo,
	sum( CASE  WHEN faulttype=1 THEN errorTime WHEN faulttype='' THEN  errorTime WHEN faulttype IS NULL THEN errorTime ELSE 0 END ) errorTimeOne,
	sum( CASE faulttype WHEN 2 THEN errorTime ELSE 0 END ) errorTimeTwo 
FROM
	(
	SELECT
		p.faulttype,
		i.FName FactoryName,
		k.FName FloorName,
		l.Fname PlantName,
		f.PCode,
		d.FName modelName,
		a.FCode MacCode,
		tt.AlarmCode,
		IFNULL( ROUND( tt.RUN / 60 / {se} * 100, 1 ), 0 ) runrRate,
		IFNULL( ROUND( tt.IDLE / 60 / {se} * 100, 1 ), 0 ) idleRate,
		IFNULL( ROUND( tt.ERROR / 60 / {se} * 100, 1 ), 0 ) errorRate,
		IFNULL( ROUND( tt.ERROR / 60 + tt.IDLE / 60 + tt.RUN/60 ), 0 ) allTime,
		IFNULL( ROUND( tt.RUN / 60 ), 0 ) runTime,
		IFNULL( ROUND( tt.IDLE / 60 ), 0 ) idleTime,
		IFNULL( ROUND( tt.ERROR / 60 ), 0 ) errorTime 
	FROM
		Machine a
		LEFT JOIN (
		SELECT
			a.MacCode,
			AlarmCode,
			sum( CASE a.StatusID WHEN 3 THEN ifnull( FLen, 0 ) ELSE 0 END ) IDLE,
			sum( CASE a.StatusID WHEN 4 THEN ifnull( FLen, 0 ) ELSE 0 END ) RUN,
			sum( CASE a.StatusID WHEN 6 THEN ifnull( FLen, 0 ) ELSE 0 END ) ERROR 
		FROM
			macstatus a 
		WHERE
			StatusID IN ( 3, 4, 6 ) 
			{filter}
		GROUP BY
			a.MacCode,
			a.AlarmCode 
		) tt ON tt.MacCode = a.FCode
		LEFT OUTER JOIN macstatus01 m ON a.fcode = m.maccode
		LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
		LEFT JOIN mtbacode p ON p.MModeID = d.id 
		AND p.AlarmCode = tt.AlarmCode
		LEFT OUTER JOIN mactprocess f ON a.id = f.macid
		LEFT OUTER JOIN tprocess g ON f.PCode = g.FCode
		LEFT OUTER JOIN factoryregion i ON a.factoryid = i.id
		LEFT OUTER JOIN factoryregion j ON a.regionid = j.id
		LEFT OUTER JOIN factoryregion k ON j.parentid = k.id
		LEFT OUTER JOIN factoryregion l ON k.parentid = l.id 
	WHERE
		1 = 1  {subFilter}
	ORDER BY
		tt.MacCode 
	) hh 
GROUP BY
	FactoryName,
	FloorName,
	PlantName,
	PCode,
	modelName,
	MacCode 
ORDER BY MacCode";
            return sql;
        }
        public IEnumerable<MachineEfficiency> GetMachineEfficy(string type, string filter, string subFilter, string macmodel, string pcode, int? factoryId, DateTime? dateStart = null, DateTime? dateEnd = null)
        {
            var list = new List<MachineEfficiency>();
            var regionDal = new FactoryRegionDal(CurrDb);
            IEnumerable<MachineEffiencyDto> dtos = CurrDb.FindList<MachineEffiencyDto>(CreateEnffiencySql("machine", filter, subFilter, macmodel, pcode));
            if (dtos == null || dtos.Count() <= 0)
                return null;
            var macFilter = $" and b.isAnalysis=1 ";
            if (factoryId != null && factoryId != 0)
            {
                macFilter += $" and b.factoryId={factoryId}";
            }
            var machines = GetAllMachines(macFilter);
            if (type == "pcode")
            {
                int j = 0;
                var runs = dtos.Where(c => c.EventCode == StandardCode.CEID_Run);
                var alarms = dtos.Where(c => c.EventCode == StandardCode.CEID_AlarmOccurred);
                var gruop = runs.GroupBy(c => c.PCode);
                foreach (var item in gruop)
                {
                    var entity = new MachineEfficiency
                    {
                        Count = machines.Where(c => c.ProcessCode == item.Key).Count(),
                        Type = "pcode",
                        Index = ++j,
                        Name = item.Key,
                        Key = item.Key,
                        HasChildren = true,
                        TotalTime = (decimal)(dateEnd - dateStart).Value.TotalSeconds,
                        PCode = item.Key
                    };
                    var runTime = item.Sum(c => c.TimeLength);
                    var alarmTime = alarms.Where(c => c.PCode == item.Key)?.Sum(c => c.TimeLength);
                    entity.RunTime = runTime / entity.Count;
                    entity.RunRate = entity.RunTime / entity.TotalTime;   // 稼动率
                    entity.AlarmRate = 0;
                    entity.AlarmRate = (alarmTime == null ? 0 : alarmTime.Value) / item.Count() / entity.TotalTime;   // 故障率
                    entity.IdleRate = 1 - entity.RunRate - entity.AlarmRate;    // 闲置率
                    entity.LossTime = entity.TotalTime - entity.RunTime;
                    // var inProducingTime = item.Where(c => c.IsInProducing == 1 && c.EventCode == DllHsms.StandardCode.CEID_Run)?.Sum(c => c.TimeSpan.Value) / count;     // 机台中有料的生产时间                                                                                                                                                                //entity.LoadRate = (decimal)inProducingTime / entity.TotalTime;                                                                                                                                                                 //entity.RDRate = (1 - entity.LoadRate)                                                                                                                                                       //为数据显示美观 后期计算方式会变化
                    entity.LoadRate = entity.RunRate + entity.AlarmRate;
                    entity.RDRate = 1 - entity.LoadRate;
                    list.Add(entity);
                }
            }
            else if (type == "macmodel")
            {
                int j = 0;
                var runs = dtos.Where(c => c.EventCode == StandardCode.CEID_Run);
                var alarms = dtos.Where(c => c.EventCode == StandardCode.CEID_AlarmOccurred);
                var gruop = runs.GroupBy(c => c.MacModel);
                foreach (var item in gruop)
                {
                    var entity = new MachineEfficiency
                    {
                        Count = machines.Where(c => c.ProcessCode == pcode && c.MacModel == item.Key).Count(),
                        Type = "macmodel",
                        Index = ++j,
                        Name = item.Key,
                        Key = item.Key,
                        HasChildren = true,
                        TotalTime = (decimal)(dateEnd - dateStart).Value.TotalSeconds,
                        PCode = item.First().PCode
                    };
                    var runTime = item.Sum(c => c.TimeLength);
                    var alarmTime = alarms.Where(c => c.MacModel == item.Key && c.PCode == pcode)?.Sum(c => c.TimeLength);
                    entity.RunTime = runTime / entity.Count;
                    entity.RunRate = entity.RunTime / entity.TotalTime;   // 稼动率
                    entity.AlarmRate = 0;
                    entity.AlarmRate = (alarmTime == null ? 0 : alarmTime.Value) / item.Count() / entity.TotalTime;   // 故障率
                    entity.IdleRate = 1 - entity.RunRate - entity.AlarmRate;    // 闲置率
                    entity.LossTime = entity.TotalTime - entity.RunTime;
                    // var inProducingTime = item.Where(c => c.IsInProducing == 1 && c.EventCode == DllHsms.StandardCode.CEID_Run)?.Sum(c => c.TimeSpan.Value) / count;     // 机台中有料的生产时间                                                                                                                                                                //entity.LoadRate = (decimal)inProducingTime / entity.TotalTime;                                                                                                                                                                 //entity.RDRate = (1 - entity.LoadRate)                                                                                                                                                       //为数据显示美观 后期计算方式会变化
                    entity.LoadRate = entity.RunRate + entity.AlarmRate;
                    entity.RDRate = 1 - entity.LoadRate;
                    list.Add(entity);
                }
            }
            else
            {
                int j = 0;
                var runs = dtos.Where(c => c.EventCode == StandardCode.CEID_Run);
                var alarms = dtos.Where(c => c.EventCode == StandardCode.CEID_AlarmOccurred);
                var gruop = runs.GroupBy(c => c.McaCode);
                foreach (var item in gruop)
                {
                    var mac = machines.FirstOrDefault(c => c.MacCode == item.Key);
                    var entity = new MachineEfficiency
                    {
                        Count = 1,
                        Type = "machine",
                        Index = ++j,
                        Name = item.Key,
                        Key = item.Key,
                        HasChildren = false,
                        TotalTime = (decimal)(dateEnd - dateStart).Value.TotalSeconds,
                        PCode = item.First().PCode,
                        RegionName = mac.RegionName
                    };
                    var runTime = item.Sum(c => c.TimeLength);
                    var alarmTime = alarms.Where(c => c.McaCode == item.Key)?.Sum(c => c.TimeLength);
                    entity.RunTime = runTime / entity.Count;
                    entity.RunRate = entity.RunTime / entity.TotalTime;   // 稼动率
                    entity.AlarmRate = 0;
                    entity.AlarmRate = (alarmTime == null ? 0 : alarmTime.Value) / item.Count() / entity.TotalTime;   // 故障率
                    entity.IdleRate = 1 - entity.RunRate - entity.AlarmRate;    // 闲置率
                    entity.LossTime = entity.TotalTime - entity.RunTime;
                    // var inProducingTime = item.Where(c => c.IsInProducing == 1 && c.EventCode == DllHsms.StandardCode.CEID_Run)?.Sum(c => c.TimeSpan.Value) / count;     // 机台中有料的生产时间                                                                                                                                                                //entity.LoadRate = (decimal)inProducingTime / entity.TotalTime;                                                                                                                                                                 //entity.RDRate = (1 - entity.LoadRate)                                                                                                                                                       //为数据显示美观 后期计算方式会变化
                    entity.LoadRate = entity.RunRate + entity.AlarmRate;
                    entity.RDRate = 1 - entity.LoadRate;
                    list.Add(entity);
                }
                var disconns = machines.Except(machines.Where(c => gruop.Select(t => t.Key).Contains(c.MacCode)))
                    .Where(c => c.PCode == pcode && c.MacModel == macmodel);
                foreach (var d in disconns)
                {
                    list.Add(new MachineEfficiency
                    {
                        Count = 1,
                        Type = "machine",
                        Index = ++j,
                        Name = d.MacCode,
                        Key = d.MacCode,
                        HasChildren = false,
                        TotalTime = (decimal)(dateEnd - dateStart).Value.TotalSeconds,
                        PCode = d.PCode,
                        RegionName = d.RegionName
                    });
                }
                list = list.OrderBy(c => c.Key).ToList();
            }
            return list;
        }
        public IEnumerable<MachineEfDto> GetMachineEfficyMachine(string filter, string subFilter, int start, int pageSize, string sortField, string sortOrder, out int total, double se)
        {
            var list = new List<MachineEfDto>();
            var regionDal = new FactoryRegionDal(CurrDb);
            string errorinfo = "";
            string sql = $@"SELECT a.MacCode,b.FName StatusName from macstatus01 a 
 INNER JOIN  standardstatus b on a.StatusID = b.StatusVal ";
            var macstatus01 = CurrDb.FindList<StatusDto>(sql).Select(c => new { MacCode = c.MacCode, StatusName = c.StatusName });
            IEnumerable<MachineEfDto> dtos = CurrDb.FindList<MachineEfDto>(CreateEnffiencyMachineSql(filter, subFilter, start, pageSize, sortField, sortOrder, se));
            foreach (var item in dtos)
            {
                var dt = new MachineEfDto
                {
                    FactoryName = item.FactoryName,
                    PlantName = item.PlantName,
                    FloorName = item.FloorName,
                    PCode = item.PCode,
                    ModelName = item.ModelName,
                    MacCode = item.MacCode,
                    macnumber = item.macnumber,
                    RunrRate = (string.IsNullOrEmpty(item.RunrRate) ? "0.00" : item.RunrRate) + "%",
                    IdleRate = (string.IsNullOrEmpty(item.IdleRate) ? "0.00" : item.IdleRate) + "%",
                    ErrorRate = (string.IsNullOrEmpty(item.ErrorRate) ? "0.00" : item.ErrorRate) + "%",
                    AllTime = item.AllTime,
                    RunTime = item.RunTime,
                    IdleTime = item.IdleTime,
                    ErrorTime = item.ErrorTime,
                    ErrorTimeOne = item.ErrorTimeOne,
                    ErrorTimeTwo = item.ErrorTimeTwo,
                    ErrorRateOne = (string.IsNullOrEmpty(item.ErrorRateOne) ? "0.00" : item.ErrorRateOne) + "%",
                    ErrorRateTwo = (string.IsNullOrEmpty(item.ErrorRateTwo) ? "0.00" : item.ErrorRateTwo) + "%",
                    StatusName = macstatus01.Where(l => l.MacCode == item.MacCode).Select(l => l.StatusName).FirstOrDefault()
                };
                list.Add(dt);
            }
            total = Convert.ToInt32(CurrDb.FindList<string>(CreateEnffiencyMachineCountSql(filter, subFilter, sortField, sortOrder)).FirstOrDefault() ?? "0");
            if (dtos == null || dtos.Count() <= 0)
                return null;

            return list;
        }

        public IEnumerable<MachineEfDto> GetMachineEfficyMachineTotal(string filter, string subFilter, int start, int pageSize, string sortField, string sortOrder, double se)
        {
            var list = new List<MachineEfDto>();
            IEnumerable<MachineEfDto> dtos = CurrDb.FindList<MachineEfDto>(CreateEnffiencyMachineTotalSql(filter, subFilter, start, pageSize, sortField, sortOrder, se));
            foreach (var item in dtos)
            {
                var dt = new MachineEfDto
                {
                    RunrRate = (string.IsNullOrEmpty(item.RunrRate) ? "0.00" : item.RunrRate) + "%",
                    IdleRate = (string.IsNullOrEmpty(item.IdleRate) ? "0.00" : item.IdleRate) + "%",
                    ErrorRate = (string.IsNullOrEmpty(item.ErrorRate) ? "0.00" : item.ErrorRate) + "%",
                    AllTime = item.AllTime < 0 ? 0 : item.AllTime,
                    RunTime = item.RunTime < 0 ? 0 : item.RunTime,
                    IdleTime = item.IdleTime < 0 ? 0 : item.IdleTime,
                    ErrorTime = item.ErrorTime < 0 ? 0 : item.ErrorTime,
                    ErrorTimeOne = item.ErrorTimeOne < 0 ? 0 : item.ErrorTimeOne,
                    ErrorTimeTwo = item.ErrorTimeTwo < 0 ? 0 : item.ErrorTimeTwo,
                    ErrorRateOne = (string.IsNullOrEmpty(item.ErrorRateOne) ? "0.00" : item.ErrorRateOne) + "%",
                    ErrorRateTwo = (string.IsNullOrEmpty(item.ErrorRateTwo) ? "0.00" : item.ErrorRateTwo) + "%",
                };
                list.Add(dt);
            }

            if (dtos == null || dtos.Count() <= 0)
                return null;

            return list;
        }


        public IEnumerable<MachineEfFactoryDto> GetMachineEfficyFactory(string filter, double se)
        {
            IEnumerable<MachineEfFactoryDto> dtos = CurrDb.FindList<MachineEfFactoryDto>(CreateEnffiencyFactorySql(filter, se));
            if (dtos == null || dtos.Count() <= 0)
                return null;
            return dtos;
        }
        public IEnumerable<MachineEfDto> GetMachineEfficyMachineExprort(string filter, string subFilter, string sortField, string sortOrder, int take, double se)
        {
            var list = new List<MachineEfDto>();
            var regionDal = new FactoryRegionDal(CurrDb);
            IEnumerable<MachineEfDto> dtos = CurrDb.FindList<MachineEfDto>(CreateEnffiencyMachineSqlExport(filter, subFilter, sortField, sortOrder, se)).Take(take);
            if (dtos == null || dtos.Count() <= 0)
                return null;
            string sql = $@"SELECT a.MacCode,b.FName StatusName from macstatus01 a 
 INNER JOIN  standardstatus b on a.StatusID = b.StatusVal";
            var macstatus01 = CurrDb.FindList<StatusDto>(sql).Select(c => new { MacCode = c.MacCode, StatusName = c.StatusName });
            Parallel.ForEach(dtos, item =>
            {
                var dt = new MachineEfDto
                {
                    FactoryName = item.FactoryName,
                    PlantName = item.PlantName,
                    FloorName = item.FloorName,
                    PCode = item.PCode,
                    ModelName = item.ModelName,
                    MacCode = item.MacCode,
                    RunrRate = item.RunrRate,
                    IdleRate = item.IdleRate,
                    ErrorRate = item.ErrorRate,
                    AllTime = item.AllTime,
                    RunTime = item.RunTime,
                    IdleTime = item.IdleTime,
                    StatusName = macstatus01.Where(l => l.MacCode == item.MacCode).Select(l => l.StatusName).FirstOrDefault()
                };
                list.Add(dt);
            });
            return list.Where(c => !string.IsNullOrEmpty(c.FactoryName)).OrderBy(c => c.MacCode);
        }

        /// <summary>
        /// 获取以机台为基准的效率统计列表
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<MachineEfficiency> GetMachineEfficienciesGroupByMcaCode(string filter, string macModel, string pCode, DateTime? dateStart = null, DateTime? dateEnd = null)
        {
            var datas = GetEffiencyEntities(filter, string.Empty).Where(c => c.PCode == pCode && c.MacModel == macModel).ToList();
            var alarms = GetAlarms(filter, string.Empty).Where(c => c.PCode == pCode && c.MacModel == macModel).ToList();
            //  var stops = GetStatusCounts(dateStart, dateEnd);
            // var oees = GetOeeEntities($" and b.FDate>='{dateStart}' and c.FDate<='{dateEnd}' ", dateStart.Value, dateEnd.Value);
            //if (datas == null)
            //{
            //    datas = new List<McaEventStatisticByDay>();
            //}
            datas.AddRange(alarms);
            var list = new List<MachineEfficiency>();
            var groups = datas.GroupBy(c => new { c.McaCode, c.McaName });
            int j = 0;
            if (dateStart == null && dateEnd == null)
            {
                dateStart = datas.OrderBy(c => c.StartTime).FirstOrDefault()?.StartTime;
                // dateEnd = datas.OrderBy(c => c.EndTime).LastOrDefault()?.EndTime;
            }
            dateEnd = datas.Max(c => c.EndTime);
            var regionDal = new FactoryRegionDal(CurrDb);
            foreach (var item in groups)
            {
                var regionId = item.FirstOrDefault().RegionId;
                var regionName = string.Empty;
                string errorinfo = string.Empty;
                var allRegions = regionDal.Get(1, 10000, "ID", "asc", string.Empty, errorinfo);
                if (regionId != null)
                {
                    regionName = regionDal.GetFullRegionName(regionId.Value, allRegions);
                }
                var entity = new MachineEfficiency
                {
                    Index = ++j,
                    Count = 1,
                    Name = item.Key.McaCode,
                    RunTime = item.Where(c => c.EventCode == DllHsms.StandardCode.CEID_Run).Sum(c => c.TimeSpan.Value) / 1,
                    Key = item.Key.McaCode,
                    HasChildren = false,
                    Type = "machine",
                    TotalTime = (decimal)(dateEnd - dateStart).Value.TotalSeconds,
                    PCode = item.FirstOrDefault().PCode,
                    Factory = item.FirstOrDefault().Factory,
                    RegionName = regionName
                    //    StopCount = stops.Where(c => c.MacCode == item.Key.McaCode)?.Sum(c => c.Count1)
                };
                var alarmTime = item.Where(c => c.EventCode == DllHsms.StandardCode.CEID_AlarmOccurred)?.Sum(c => c.TimeSpan);
                //  var alramscount = alarms.Where(c => c.MacModel == item.Key.McaCode).Select(c => c.McaCode).Distinct().Count();
                entity.RunRate = entity.RunTime / entity.TotalTime;   // 稼动率
                entity.AlarmRate = (decimal)alarms.Where(c => c.McaCode == item.Key.McaCode && c.EventCode == DllHsms.StandardCode.CEID_AlarmOccurred)?.Sum(c => c.TimeSpan) / 1 / entity.TotalTime;   // 故障率
                entity.IdleRate = 1 - entity.RunRate - entity.AlarmRate;    // 闲置率
                entity.LossTime = entity.TotalTime - entity.RunTime;
                var inProducingTime = item.Where(c => c.IsInProducing == 1 && c.EventCode == DllHsms.StandardCode.CEID_Run)?.Sum(c => c.TimeSpan.Value) / 1;     // 机台中有料的生产时间
                                                                                                                                                                 //entity.LoadRate = (decimal)inProducingTime / entity.TotalTime;
                                                                                                                                                                 //entity.RDRate = (1 - entity.LoadRate);
                                                                                                                                                                 //为数据显示美观 后期计算方式会变化
                entity.LoadRate = entity.RunRate + entity.AlarmRate;
                entity.RDRate = 1 - entity.LoadRate;
                //if (oees != null && oees.Count() > 0 && oees.Where(c => c.MacCode == entity.Name).Count() > 0)
                //{
                //    entity.PerformanceEfficiency = oees.Where(c => c.MacCode == entity.Name)?.Average(c => c.PerformanceEffi) ?? 0;
                //    entity.TimeEfficiency = oees.Where(c => c.MacCode == entity.Name)?.Average(c => c.TimeEffi) ?? 0;
                //    entity.OverallEfficiency = oees.Where(c => c.MacCode == entity.Key)?.Average(c => c.Oee) ?? 0;
                //}

                list.Add(entity);
            }
            list = list.OrderBy(c => c.Name).ToList();
            var machines = this.GetAllMachines($" and d.FCode='{macModel}' and a.PCode='{pCode}'");
            var notContainedEffis = new List<MachineEfficiency>();
            foreach (var mac in machines)
            {
                var item = list.FirstOrDefault(c => c.Key == mac.MacCode);
                if (item == null)
                {
                    list.Add(new MachineEfficiency { PCode = pCode, Key = mac.MacCode, Name = mac.MacCode, Count = 1, HasChildren = false, Type = "machine" });
                }
            }
            return list;
        }

        /// <summary>
        /// 以机型为基准的效率统计列表
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<MachineEfficiency> GetMachineEfficienciesGroupByMacModel(string filter, string pCode, DateTime? dateStart = null, DateTime? dateEnd = null)
        {
            var datas = GetEffiencyEntities(filter, string.Empty).Where(c => c.PCode == pCode).ToList();
            var alarms = GetAlarms(filter, string.Empty).Where(c => c.PCode == pCode).ToList();
            //  var stops = GetStatusCounts(dateStart,dateEnd);
            var machines = GetAllMachines(string.Empty);
            datas.AddRange(alarms);
            // var oees = GetOeeEntities($" and b.FDate>='{dateStart}' and c.FDate<='{dateEnd}'", dateStart.Value, dateEnd.Value);
            var list = new List<MachineEfficiency>();
            var groups = datas.GroupBy(c => new { c.MacModel }); ;
            int j = 0;
            if (dateStart == null && dateEnd == null)
            {
                dateStart = datas.OrderBy(c => c.StartTime).FirstOrDefault()?.StartTime;
            }
            dateEnd = datas.Max(c => c.EndTime);
            foreach (var item in groups)
            {
                var count = item.Select(c => c.McaCode).Distinct().Count();
                var entity = new MachineEfficiency
                {
                    Index = ++j,
                    Count = machines.Where(c => c.MacModel == item.Key.MacModel && c.PCode == item.FirstOrDefault().PCode).Distinct().Count(),
                    Name = item.Key.MacModel,
                    RunTime = item.Where(c => c.EventCode == DllHsms.StandardCode.CEID_Run).Sum(c => c.TimeSpan.Value) / count,
                    Key = item.Key.MacModel,
                    HasChildren = true,
                    Type = "macmodel",
                    TotalTime = (decimal)(dateEnd - dateStart).Value.TotalSeconds,
                    PCode = pCode,
                    //   StopCount = stops.Where(c => c.MacModel == item.Key.MacModel)?.Sum(c => c.Count1)
                };
                entity.RunRate = entity.RunTime / entity.TotalTime;   // 稼动率
                entity.AlarmRate = 0;

                entity.AlarmRate = (decimal)item.Where(c => c.EventCode == DllHsms.StandardCode.CEID_AlarmOccurred)?.Sum(c => c.TimeSpan) / count / entity.TotalTime;   // 故障率

                entity.IdleRate = 1 - entity.RunRate - entity.AlarmRate;    // 闲置率
                entity.LossTime = entity.TotalTime - entity.RunTime;

                var inProducingTime = item.Where(c => c.IsInProducing == 1 && c.EventCode == DllHsms.StandardCode.CEID_Run)?.Sum(c => c.TimeSpan.Value) / count;     // 机台中有料的生产时间
                                                                                                                                                                     //entity.LoadRate = (decimal)inProducingTime / entity.TotalTime;
                                                                                                                                                                     //entity.RDRate = (1 - entity.LoadRate);                                                                                                                                                        //为数据显示美观 后期计算方式会变化
                entity.LoadRate = entity.RunRate + entity.AlarmRate;
                entity.RDRate = 1 - entity.LoadRate;
                //if (oees != null && oees.Count() > 0 && oees.Where(c => c.MacModelCode == entity.Key && c.PCode == pCode).Count() > 0)
                //{
                //    entity.PerformanceEfficiency = oees.Where(c => c.MacModelCode == entity.Key && c.PCode == pCode)?.Average(c => c.PerformanceEffi) ?? 0;
                //    entity.TimeEfficiency = oees.Where(c => c.MacModelCode == entity.Key && c.PCode == pCode)?.Average(c => c.TimeEffi) ?? 0;
                //    entity.OverallEfficiency = oees.Where(c => c.MacModelCode == entity.Key && c.PCode == pCode)?.Average(c => c.Oee) ?? 0;
                //}
                list.Add(entity);
            }
            list = list.OrderBy(c => c.Name).ToList();
            var groupedMachines = machines.Where(c => c.PCode == datas[0].PCode).GroupBy(c => c.MacModel);
            foreach (var mac in groupedMachines)
            {
                var item = list.FirstOrDefault(c => c.Key == mac.Key);
                if (item == null)
                {
                    list.Add(new MachineEfficiency { PCode = pCode, Key = mac.Key, Name = mac.Key, Count = mac.Count(), HasChildren = true, Type = "macmodel" });
                }
            }
            return list;
        }

        /// <summary>
        /// 以制程为基准的效率统计列表
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<MachineEfficiency> GetMachineEfficienciesGroupByPcode(string filter, string subFilter, DateTime? dateStart = null, DateTime? dateEnd = null)
        {
            var datas = GetEffiencyEntities(filter, subFilter).ToList();
            var alarms = GetAlarms(filter, subFilter).ToList();
            //  var stops = GetStatusCounts(dateStart,dateEnd);
            var machines = GetAllMachines(string.Empty);
            datas.AddRange(alarms);
            // var oees = GetOeeEntities($" and b.FDate>='{dateStart}' and c.FDate<='{dateEnd}'", dateStart.Value, dateEnd.Value);
            var list = new List<MachineEfficiency>();
            var groups = datas.GroupBy(c => new { c.PCode, c.PName });
            int j = 0;
            if (dateStart == null && dateEnd == null)
            {
                dateStart = datas.OrderBy(c => c.StartTime).FirstOrDefault()?.StartTime;
            }
            dateEnd = datas.Max(c => c.EndTime);
            foreach (var item in groups)
            {
                var count = item.Select(c => c.McaCode).Distinct().Count();
                var entity = new MachineEfficiency
                {
                    Index = ++j,
                    Count = machines.Where(c => c.PCode == item.Key.PCode).Select(c => c.MacCode).Distinct().Count(),
                    Name = item.Key.PCode,
                    RunTime = item.Where(c => c.EventCode == DllHsms.StandardCode.CEID_Run).Sum(c => c.TimeSpan.Value) / count,
                    Key = item.Key.PCode,
                    HasChildren = true,
                    Type = "pcode",
                    TotalTime = (decimal)(dateEnd - dateStart).Value.TotalSeconds,
                    PCode = item.Key.PCode,
                    //  StopCount = stops.Where(c => c.PCode == item.Key.PCode)?.Sum(c => c.Count1)
                };
                entity.RunRate = entity.RunTime / entity.TotalTime;   // 稼动率
                entity.AlarmRate = 0;

                entity.AlarmRate = (decimal)item.Where(c => c.EventCode == DllHsms.StandardCode.CEID_AlarmOccurred)?
               .Sum(c => c.TimeSpan) / count / entity.TotalTime;   // 故障率

                entity.IdleRate = 1 - entity.RunRate - entity.AlarmRate;    // 闲置率
                entity.LossTime = entity.TotalTime - entity.RunTime;
                var inProducingTime = item.Where(c => c.IsInProducing == 1 && c.EventCode == DllHsms.StandardCode.CEID_Run)?.Sum(c => c.TimeSpan.Value) / count;     // 机台中有料的生产时间
                                                                                                                                                                     //entity.LoadRate = (decimal)inProducingTime / entity.TotalTime;
                                                                                                                                                                     //entity.RDRate = (1 - entity.LoadRate);
                                                                                                                                                                     //为数据显示美观 后期计算方式会变化
                entity.LoadRate = entity.RunRate + entity.AlarmRate;
                entity.RDRate = 1 - entity.LoadRate;
                //if (oees != null && oees.Count() > 0 && oees.Where(c => c.PCode == entity.Key).Count() > 0)
                //{
                //    entity.PerformanceEfficiency = oees.Where(c => c.PCode == entity.Key)?.Average(c => c.PerformanceEffi) ?? 0;
                //    entity.TimeEfficiency = oees.Where(c => c.PCode == entity.Key)?.Average(c => c.TimeEffi) ?? 0;
                //    entity.OverallEfficiency = oees.Where(c => c.PCode == entity.Key)?.Average(c => c.Oee) ?? 0;
                //}
                list.Add(entity);
            }
            return list;
        }

        /// <summary>
        /// 获取机台产量信息
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<OutputDto> GetMachineOutput(string filter, out int total, out int outputTotal, ref string errorinfo, int pageIndex = 1, int size = 10)
        {
            using (IDatabase eapdb = DbFactory.Base("eap"))
            {
                var outputs = new List<OutputDto>();
                var maccounts = eapdb.FindListForCondition<MacCount02>(filter, ref errorinfo);
                var groups = maccounts?.GroupBy(c => c.MacCode);
                if (groups != null && groups.Count() > 0)
                {
                    foreach (var item in groups)
                    {
                        var singles = item.OrderBy(c => c.FDate);
                        var output = 0;
                        for (var i = 0; i < singles.Count() - 1; i++)
                        {
                            var minus = singles.ElementAt(i + 1).FCount - singles.ElementAt(i).FCount;
                            if (minus < 0)
                            {
                                minus = 0;
                            }
                            output += minus;
                        }

                        outputs.Add(new OutputDto
                        {
                            MacCode = item.Key,
                            PCode = item.FirstOrDefault().PCode,
                            PName = item.FirstOrDefault().PName,
                            MacModel = item.FirstOrDefault().MacModelCode,
                            Supplier = item.FirstOrDefault().SupplierCode,
                            Count = output
                        });
                    }
                }
                total = outputs.Count;
                outputTotal = outputs.Sum(c => c.Count);
                return outputs.Skip((pageIndex - 1) * size).Take(size);
            }
        }

        /// <summary>
        /// 已制程分组获取产量信息
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<OutputTree> GetOutputs(string filter, string subFilter, ref string errorinfo)
        {
            var dtos = this.GetOutputsByMachine(filter, subFilter, ref errorinfo);
            var trees = new List<OutputTree>();
            if (dtos != null && dtos.Count() > 0)
            {
                var regionDal = new FactoryRegionDal(CurrDb);
                var allRegions = regionDal.Get(1, 10000, "ID", "asc", string.Empty, errorinfo);
                var facItem = new OutputTree
                {
                    Key = "全厂",
                    Code = "全厂",
                    Name = "全厂",
                    Count = dtos.Sum(c => c.Count),
                    TimeLen = dtos.Sum(c => c.TimeLen)
                };
                if (facItem.TimeLen == 0)
                {
                    facItem.UPH = 0;
                }
                else
                {
                    facItem.UPH = Convert.ToInt32(Math.Round(Convert.ToDecimal(facItem.Count) / (decimal)facItem.TimeLen * 60 * 60));
                }
                var pcodeGroups = dtos.GroupBy(c => new { c.PCode, c.PName });
                var pcodeTrees = new List<OutputTree>();
                if (pcodeGroups != null && pcodeGroups.Count() > 0)
                {
                    foreach (var item in pcodeGroups)
                    {
                        var root = new OutputTree
                        {
                            Key = item.Key.PCode,
                            Code = item.Key.PCode,
                            Name = item.Key.PName,
                            Level = 1,
                            Expand = false,
                            MacCode = "--",
                            MacModel = "--",
                            MacName = "--",
                            MacModelName = "--",
                            Count = item.Sum(c => c.Count),
                            TimeLen = item.Sum(c => c.TimeLen)
                        };
                        if (root.TimeLen == 0)
                        {
                            root.UPH = 0;
                        }
                        else
                        {
                            root.UPH = Convert.ToInt32(Math.Round(root.Count / (decimal)root.TimeLen * 60 * 60));
                        }
                        var macmodelGroups = item.GroupBy(e => new { e.MacModel, e.MacModelName });
                        if (macmodelGroups != null && macmodelGroups.Count() > 0)
                        {
                            var modelTrees = new List<OutputTree>();
                            foreach (var modelItem in macmodelGroups)
                            {
                                var macmodel = new OutputTree
                                {
                                    Key = item.Key.PName + modelItem.Key.MacModel,
                                    Code = modelItem.Key.MacModel,
                                    Name = modelItem.Key.MacModelName,
                                    Level = 2,
                                    Expand = false,
                                    Count = modelItem.Sum(c => c.Count),
                                    TimeLen = modelItem.Sum(c => c.TimeLen)
                                };
                                if (macmodel.TimeLen == 0)
                                {
                                    macmodel.UPH = 0;
                                }
                                else
                                {
                                    macmodel.UPH = Convert.ToInt32(Math.Round(macmodel.Count / (decimal)macmodel.TimeLen * 60 * 60));
                                }
                                var macTrees = new List<OutputTree>();
                                foreach (var mac in modelItem)
                                {
                                    var regionName = string.Empty;
                                    if (mac.RegionId != null)
                                    {
                                        regionName = regionDal.GetFullRegionName(mac.RegionId.Value, allRegions);
                                    }
                                    var macItem = new OutputTree
                                    {
                                        Key = mac.Code,
                                        Code = mac.Code,
                                        Name = mac.Name,
                                        RegionName = regionName,
                                        Level = 3,
                                        Expand = false,
                                        Count = mac.Count,
                                        Children = null,
                                        TimeLen = mac.TimeLen
                                    };
                                    if (mac.TimeLen == 0)
                                    {
                                        macItem.UPH = 0;
                                    }
                                    else
                                    {
                                        macItem.UPH = Convert.ToInt32(Math.Round(macItem.Count / (decimal)macItem.TimeLen * 60 * 60));
                                    }
                                    macTrees.Add(macItem);
                                }
                                macmodel.Children = macTrees;
                                modelTrees.Add(macmodel);
                            }
                            root.Children = modelTrees;
                        }
                        pcodeTrees.Add(root);
                    }
                }
                facItem.Children = pcodeTrees;
                trees.Add(facItem);
            }
            return trees;
        }

        private IEnumerable<OutputDto> GetOutputsByMachine(string filter, string subFilter, ref string errorinfo)
        {
            string sql = $@"select count,e.fcode as pcode,e.fname as pname,f.fcode as macmodel,
                            f.fname as macmodelname,c.fcode as code,c.fname as name,TimeLen,
                            c.RegionId from 
                            (select a.macid,sum(b.fcount) count,sum(a.timeLen) timeLen from maccountmst a 
                            left join maccountdetail b on a.id=b.mstid 
                            where b.paramcode='S00026' and b.typeid=0 {filter}
                            group by a.macid) t 
                            left join machine c on t.macid=c.id
                            left join mactprocess d on c.id=d.macid
                            left join tprocess e on d.pcode=e.fcode
                            left join macmodel f on c.mmodeid=f.id
                            where 1=1 {subFilter}";
            var dtos = CurrDb.FindList<OutputDto>(sql);
            return dtos;
        }

        public IEnumerable<OutputDto> GetOutputsByProgram(string filter, string subFilter, int skip, int take, string sort, string order, ref string errorinfo)
        {
            var sql = $@"select count,timelen,round(count/timelen*3600) uph,name,pcode from (select sum(cc.count) as count,sum(cc.timelen) as timelen,cc.pname as name,
                         c.pcode from 
                         (select tt.macid,tt.pname,sum(tt.timelen) timelen,sum(a.fcount) count from 
                         (select b.id,b.macid,b.timelen,b.pname from maccountmst b where 1=1
                         {filter} and b.timelen<>0 
                          and b.pname is not null) tt 
                         left join maccountdetail a on tt.id=a.mstid 
                         where 1=1
                         and a.typeid=0 and a.paramcode='S00026' group by tt.macid,tt.pname) cc
                         left join mactprocess c on cc.macid=c.macid
                         left join machine d on cc.macid = d.id
                         where 1=1 {subFilter}
                         group by cc.pname,c.pcode) t order by {sort} {order} limit {skip - 1},{take}";
            return CurrDb.FindList<OutputDto>(sql);
        }

        public int GetProgramCountFromMacCount(string filter, string subFilter)
        {
            var sql = $@"select count(1) from(select sum(count),pname from (
                         select count(1) count ,pname,macid from maccountmst b where 1 = 1 {filter}
                         and b.timelen <> 0 and b.pname is not null  
                         group by pname,macid) tt
                         left join mactprocess c on tt.macid = c.macid
                         left join machine d on tt.macid = d.id 
                         where 1=1 {subFilter}
                         group by pname)t";
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
        }

        public int GetExtra(string filter, string subFilter)
        {
            var sql = $@"select sum(cc.count) as count from 
                         (select tt.macid,sum(a.fcount) count from 
                         (select b.id,b.macid,b.timelen from maccountmst b where 1=1
                          and b.timelen<>0 {filter}
                          and b.pname is not null) tt 
                         left join maccountdetail a on tt.id=a.mstid 
                         where 1=1
                         and a.typeid=0 and a.paramcode='S00026' group by tt.macid) cc
                         left join mactprocess c on cc.macid=c.macid
                         left join machine d on cc.macid = d.id
                         where 1=1 {subFilter}";
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
        }

        public IEnumerable<OeeEntity> GetOeeEntities(string filter, DateTime dateStart, DateTime dateEnd)
        {
            string sql = $@"select b.FDate as TrackInTime,c.FDate as TrackOutTime,a.PCode,a.FTotal,a.FGoods,a.Rejects,a.Losts,a.MacCode,d.FModel as MacModelCode,
                          e.FName as MacModelName,g.PartCode as ProductCode from JobBooking a 
                          left join JbTrack b on a.ID=b.JbID and b.FType=1
                          left join JbTrack c on a.ID=c.JbID and c.FType=-1
                          left join Machine d on a.MacCode=d.FCode
                          left join MacModel e on d.FModel=e.FCode
                          left join LotMst f on a.LotNo=f.LotNo
                          left join ProductOrder g on f.PoCode=g.FCode
                          where a.StatusID=2 and a.IsAuto=1 {filter}";
            var jobs = CurrDb.FindList<JobbookingDto>(sql);
            var list = new List<OeeEntity>();
            var idealDal = new ProductOutputConfigDal(CurrDb);
            var runs = this.GetEffiencyEntities($"and a.StartTime>='{dateStart}' and a.StartTime<='{dateEnd}'", string.Empty);
            foreach (var item in jobs)
            {
                item.RunTime = runs.Where(c => c.McaCode == item.MacCode && c.StartTime >= item.TrackInTime && c.EndTime <= item.TrackOutTime)
                    ?.Sum(c => c.TimeSpan) ?? 0;
            }
            string errorinfo = string.Empty;
            var configs = idealDal.Get(1, 10000, "asc", "a.id", string.Empty, errorinfo);
            if (jobs != null && jobs.Count() > 0)
            {
                var groups = jobs.GroupBy(c => new { c.PCode, c.MacModelCode, c.MacCode, c.ProductCode });
                foreach (var item in groups)
                {
                    var entity = new OeeEntity
                    {
                        MacCode = item.Key.MacCode,
                        PCode = item.FirstOrDefault().PCode,
                        ProductCode = item.Key.ProductCode,
                        FGoods = item.Sum(c => c.FGoods),
                        Rejects = item.Sum(c => c.Rejects),
                        Losts = item.Sum(c => c.Losts),
                        Output = item.Sum(c => c.FTotal),
                        MacModelCode = item.FirstOrDefault().MacModelCode,
                        MacModelName = item.FirstOrDefault().MacModelName
                    };
                    var config = configs.FirstOrDefault(c => c.MacCode == entity.MacCode && c.ProductCode == entity.ProductCode);
                    if (config == null)
                    {
                        entity.TimeEffi = 0;
                        entity.PerformanceEffi = 0;
                        entity.Oee = 0;
                    }
                    else
                    {
                        var idealTs = 60 * 60 / config.FLen;   // 理想加工周期
                        entity.RunTime = item.Sum(c => c.RunTime);  // 机台在这段时间内的运行时长
                        entity.LoadTime = (decimal)item.Sum(c => (c.TrackOutTime - c.TrackInTime).TotalSeconds);   // 负荷时间  当前时间段内所有trackin到trackout的时间和
                        entity.TimeEffi = entity.RunTime / entity.LoadTime;
                        if (entity.RunTime > 0)
                        {
                            entity.PerformanceEffi = (decimal)idealTs * entity.Output / entity.RunTime;
                        }
                        entity.Oee = (decimal)idealTs * entity.FGoods / entity.LoadTime;
                        list.Add(entity);
                    }
                }
            }
            return list;
        }
        #endregion

        #region  定时汇总相关

        /// <summary>
        ///  从Eap数据库中获取
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public IEnumerable<MacStatus> GetMacStatusesFromMySql(string filter)
        {
            return CurrDb.FindList<MacStatus>($@"select a.* from macstatus a  
                    where a.ishandeled=0 {filter}");
        }

        /// <summary>
        /// 获取上次汇总报警的时间
        /// </summary>
        /// <returns></returns>
        public DateTime GetLastAsyncDate()
        {
            string errorinfo = string.Empty;
            var macstaus = CurrDb.FindListForCondition<MacStatus>($" and a.ishandled=1 " +
                $"order by id desc limit 0,1", ref errorinfo).FirstOrDefault();
            if (macstaus == null)
                return DateTime.Now.AddDays(-2);
            return macstaus.ETime;
        }

        public int Async(DateTime dateNow, ref string errorinfo)
        {
            // using(IDatabase db = DbFactory.Base())
            var details = CurrDb.FindList<AlarmCode>(CreateDetailSql(dateNow)).ToList();
            LogHelper<McaEventStatisticByDayDal>.LogFatal($"开始同步报警数据", "EapAsync", string.Empty);
            if (details == null || details.Count() <= 0)
            {
                errorinfo = "暂无需要汇总的数据";
                // LogHelper<McaEventStatisticByDayDal>.LogFatal($"暂无需要汇总的数据", "EapAsync", string.Empty);
                return -1;
            }
            var minumnDate = details.Min(c => c.ptime);
            IList<int> alarmHandledIds = new List<int>();
            string runHandledIds = string.Empty;
            var entities = AppendAlarmEndTime(details, StandardCode.CEID_AlarmOccurred, DllHsms.StandardCode.CEID_AlarmRelease, dateNow, out alarmHandledIds).ToList();
            var runs = AppendRunTime(details, dateNow, out runHandledIds);
            entities.AddRange(runs);
            var aoiAlarmIds = string.Empty;
            entities = entities.Where(c => c.IsHandled == 1).Distinct(new AlarmByDayComparer()).ToList();
            var aoiAlarms = AppendAoiAlarms(dateNow, out aoiAlarmIds);
            entities.AddRange(aoiAlarms);
            if (CurrDb.Insert<McaEventStatisticByDay>(entities) < 0)
            {
                errorinfo = "插入目标表失败";
                return -1;
            }
            var sql = string.Empty;
            #region sql批量插入
            //DataTable dt = new DataTable();
            //var sql = "select * from McaEventStatisticByDay where 1<>1";
            //Console.WriteLine("开始插入目标表");
            //dt = CurrDb.FindTable(sql);
            //SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, trans);
            //bulkCopy.DestinationTableName = "McaEventStatisticByDay";
            //foreach (var item in entities)
            //{
            //    var rows = dt.Select($" McaCode ='{item.McaCode}' and AlarmCode ='{item.AlarmCode}' " +
            //        $"and EventCode='{item.EventCode}' and StartTime='{item.StartTime}'" +
            //        $"and EndTime='{item.EndTime}'");
            //    if (rows != null && rows.Count() > 0)
            //    {
            //        continue;
            //    }
            //    DataRow row = dt.NewRow();
            //    row["ProductName"] = item.ProductName;
            //    row["Manufacturer"] = item.Manufacturer;
            //    row["AlarmCode"] = item.AlarmCode;
            //    row["AlarmDesc"] = item.AlarmDesc;
            //    row["EventCode"] = item.EventCode;
            //    row["McaCode"] = item.McaCode;
            //    row["McaName"] = item.McaName;
            //    row["MacModel"] = item.MacModel;
            //    row["MacModelName"] = item.MacModelName;
            //    row["PCode"] = item.PCode;
            //    row["PName"] = item.PName;
            //    row["ProgramName"] = item.ProgramName;
            //    row["StartTime"] = item.StartTime;
            //    row["IsInProducing"] = item.IsInProducing;
            //    var endTime = DBNull.Value;
            //    if (item.EndTime == null)
            //    {
            //        row["EndTime"] = endTime;
            //    }
            //    else
            //    {
            //        row["EndTime"] = item.EndTime;
            //    }
            //    if (item.TimeSpan == null)
            //    {
            //        row["TimeSpan"] = DBNull.Value;
            //    }
            //    else
            //    {
            //        row["TimeSpan"] = item.TimeSpan;
            //    }
            //    row["IsHandled"] = item.IsHandled;
            //    row["InsertTime"] = DateTime.Now;
            //    dt.Rows.Add(row);
            //}
            //bulkCopy.BatchSize = dt.Rows.Count;
            //bulkCopy.WriteToServer(dt);
            //bulkCopy.Close();
            #endregion
            var updateSql = "delete from McaEventStatisticByDay where id not in (select max(id) from McaEventStatisticByDay " +
                "group by McaCode,EventCode,AlarmCode,StartTime,EndTime) ";
            if (alarmHandledIds != null && alarmHandledIds.Count > 0)
            {
                updateSql = $"update McaSecTime set IsHandled=1 where id in({string.Join(",", alarmHandledIds.Select(c => $"'{c}'"))})";
                var res = CurrDb.ExecuteBySql(updateSql);
                if (res < 0)
                {
                    errorinfo = "更新数据源表失败";
                    //  LogHelper<McaEventStatisticByDayDal>.LogError($"更新数据源表失败", "EapAsync", string.Empty);
                    return -1;
                }
            }
            if (!string.IsNullOrEmpty(runHandledIds))
            {
                updateSql = $"update MacStatus set ishandeled=1 where id in ({runHandledIds})";
                if (CurrDb.ExecuteBySql(updateSql) < 0)
                {
                    //   LogHelper<McaEventStatisticByDayDal>.LogError($"更新MYSQL中运行状态表失败", "EapAsync", string.Empty);
                    errorinfo = "更新MYSQL中运行状态表失败";
                    return -1;
                }
            }
            if (!string.IsNullOrEmpty(aoiAlarmIds))
            {
                updateSql = $"update MacStatus set ishandeled=1 where id in ({aoiAlarmIds})";
                if (CurrDb.ExecuteBySql(updateSql) < 0)
                {
                    //   LogHelper<McaEventStatisticByDayDal>.LogError($"更新MYSQL中运行状态表失败", "EapAsync", string.Empty);
                    errorinfo = "更新MYSQL中AOI报警状态表失败";
                    return -1;
                }
            }
            return 1;
        }

        public void AsyncFails(string connStr, DatabaseType dbType, ref string errorinfo)
        {
            IDatabase db = null;
            try
            {
                db = CurrDb;
                var fails = CurrDb.FindListForCondition<EapAsyncHistory>($" and a.IsSuccess=-1", ref errorinfo)
                    ?.Where(c => (DateTime.Now - c.AsyncTime).TotalMinutes > 62);
                if (fails != null && fails.Count() > 0)
                {
                    foreach (var item in fails)
                    {
                        using (IDatabase tempDb = DbFactory.Base(connStr, dbType))
                        {
                            if (this.Async(item.AsyncTime, ref errorinfo) < 0)
                            {
                                tempDb.Rollback();
                                continue;
                            }
                            item.IsSuccess = 1;
                            if (CurrDb.UpdateFor(item, string.Empty) < 0)
                            {
                                tempDb.Rollback();
                                LogHelper<McaEventStatisticByDay>.LogFatal($"更新汇总记录[{item.AsyncTime.ToString("yyyy-MM-dd HH:mm:ss")}]失败", "EapAsync", string.Empty);
                                continue;
                            }
                            tempDb.Commit();
                        }

                    }
                }
                else
                {
                    LogHelper<McaEventStatisticByDay>.LogFatal("暂无需要处理的记录", "EapAsync", string.Empty);
                }
            }
            catch (Exception e)
            {
                if (db != null)
                    db.Rollback();
                LogHelper<McaEventStatisticByDayDal>.LogError(e.Message, "EapAsync", string.Empty);
                LogHelper<McaEventStatisticByDayDal>.LogError(e.StackTrace, "EapAsync", string.Empty);
            }
            finally
            {
                if (db != null)
                {
                    db.Close();
                    db.Dispose();
                }
            }
        }

        private object obj = new object();

        /// <summary>
        /// 加入报警结束时间
        /// </summary>
        /// <param name="details"></param>
        /// <param name="startCode"></param>
        /// <param name="endCode"></param>
        /// <returns></returns>
        public IEnumerable<McaEventStatisticByDay> AppendAlarmEndTime(IEnumerable<AlarmCode> details, string startCode, string endCode, DateTime date, out IList<int> ids)
        {
            LogHelper<McaEventStatisticByDayDal>.LogFatal($"开始同步报警,总数：{details.Count()}", "EapAsync", string.Empty);
            if (string.IsNullOrEmpty(endCode))
            {
                endCode = startCode;
            }
            IList<AlarmCode> occcurrs, releases;
            occcurrs = details.Where(c => c.eventCode == startCode).OrderBy(c => c.ptime).ToList();  //所有报警开始的数据
            LogHelper<McaEventStatisticByDayDal>.LogFatal($"报警开始数量：{occcurrs.Count()}", "EapAsync", string.Empty);
            releases = details.Where(c => c.eventCode == endCode).OrderBy(c => c.ptime).ToList();   //所有报警结束的数据
            LogHelper<McaEventStatisticByDayDal>.LogFatal($"报警解除数量：{releases.Count()}", "EapAsync", string.Empty);
            var entities = new ConcurrentBag<McaEventStatisticByDay>();
            var handledIds = new List<int>();
            //Parallel.For(0, occcurrs.Count, i =>
            //{
            for (var i = 0; i < occcurrs.Count; i++)
            {
                var element = occcurrs[i];
                var entity = new McaEventStatisticByDay()
                {
                    AlarmCode = element.name,
                    AlarmDesc = element.description,
                    StartTime = element.ptime,
                    MacModel = element.macmodel,
                    McaCode = element.maccode,
                    McaName = element.macname,
                    MacModelName = element.macmodelname,
                    PCode = element.pcode,
                    PName = element.pname,
                    EventCode = DllHsms.StandardCode.CEID_AlarmOccurred,
                    Manufacturer = element.supplier
                };

                AlarmCode releaseItem = null;
                //获取当前报警的机台的下一次报警，如果此次报警不是当前机台的最后一次报警，
                //则限定结束报警的时间要在当前报警的后面且在下一次报警之前
                var nextItem = occcurrs.FirstOrDefault(c => c.maccode == element.maccode && c.ptime > element.ptime);
                if (nextItem != null)
                {
                    releaseItem = releases.Where(c => c.ptime >= occcurrs[i].ptime
                    && c.maccode == occcurrs[i].maccode && c.ptime < nextItem.ptime
                    && c.name == occcurrs[i].name).OrderBy(c => c.ptime).FirstOrDefault();
                }
                else
                {
                    releaseItem = releases.Where(c => c.ptime >= occcurrs[i].ptime
                    && c.maccode == occcurrs[i].maccode && c.name == occcurrs[i].name)
                    .OrderBy(c => c.ptime).FirstOrDefault();
                }
                //如果有报警有结束的记录，则取结束的记录，如果没有，判断该报警距离今天是不是超过24小时，
                //未超过则跳过该记录，将该记录置为未处理，等下次汇总时处理，如果超过一天，则取
                //该机台下一次报警的开始时间作为此次报警的结束时间，如果没有报警，则取下一次状态改变的时间
                //
                if (releaseItem != null)
                {
                    entity.EndTime = releaseItem.ptime;
                    entity.TimeSpan = (decimal)(entity.EndTime - entity.StartTime).Value.TotalSeconds;
                    entity.IsHandled = 1;
                    handledIds.Add(element.Id);
                    handledIds.Add(releaseItem.Id);
                }
                else
                {
                    TimeSpan ts = date - element.ptime.Value;
                    if (ts.TotalDays > 1)
                    {
                        entity.EndTime = entity.StartTime.Value.AddMinutes(10);
                        entity.TimeSpan = (decimal)(entity.EndTime - entity.StartTime).Value.TotalSeconds;
                        //releaseItem = details.Where(c => c.ptime > element.ptime && c.maccode == element.maccode).FirstOrDefault();
                        //if (releaseItem == null)
                        //{
                        //    var nextEvent = details.FirstOrDefault(c => c.ptime > element.ptime);
                        //    if (nextEvent == null)
                        //    {
                        //        entity.EndTime = entity.StartTime;
                        //    }
                        //    else
                        //    {
                        //        entity.EndTime = nextEvent.ptime;
                        //    }
                        //}
                        //else if (nextItem == null)
                        //{
                        //    entity.EndTime = releaseItem.ptime;
                        //}
                        //else
                        //{
                        //    if (nextItem.ptime < releaseItem.ptime)
                        //    {
                        //        entity.EndTime = nextItem.ptime;
                        //    }
                        //    else
                        //    {
                        //        entity.EndTime = releaseItem.ptime;
                        //    }
                        //}
                        //entity.TimeSpan = (decimal)(entity.EndTime - entity.StartTime).Value.TotalSeconds;
                        entity.IsHandled = 1;
                        //if (releaseItem != null)
                        //{
                        //    handledIds.Add(releaseItem.Id);
                        //}
                        handledIds.Add(element.Id);
                    }
                    else
                    {
                        entity.IsHandled = -1;
                    }
                }
                if (entity.IsHandled == 1)
                {
                    entities.Add(entity);
                }
                Console.WriteLine($"========插入机台[{entity.McaCode}]报警代码为{entity.AlarmCode}的记录成功==========={i + 1}");
            }
            //});
            ids = handledIds;
            LogHelper<McaEventStatisticByDayDal>.LogFatal($"提取报警结束时间成功", "EapAsync", string.Empty);
            return entities;
        }

        /// <summary>
        /// 加入设备运行时间
        /// </summary>
        /// <param name="startCode"></param>
        /// <returns></returns>
        public IEnumerable<McaEventStatisticByDay> AppendRunTime(IEnumerable<AlarmCode> details, DateTime date, out string ids)
        {
            var macStatuses = this.GetMacStatusesFromMySql($" and a.statusid=4 and " +
                $"a.stime>'{date.AddMinutes(-62).ToString("yyyy-MM-dd HH:mm:ss")}' " +
                $"and a.stime<='{date.ToString("yyyy-MM-dd HH:mm:ss")}' " +
                $" and a.etime<>'0001-01-01 00:00:00' " +
                $"" +
                $"order by a.stime asc").ToList();  // 设备运行事件
            LogHelper<McaEventStatisticByDayDal>.LogFatal($"机台运行记录总数：{macStatuses.Count()}", "EapAsync", string.Empty);
            var entities = new ConcurrentBag<McaEventStatisticByDay>();
            var macProcesses = GetMacTProcesses();
            var handledIds = string.Empty;
            if (macStatuses != null && macStatuses.Count > 0)
            {
                var index = 0;
                //Parallel.ForEach<MacStatus>(macStatuses, item =>
                //{
                foreach (var item in macStatuses)
                {
                    DateTime? endTime = null;
                    if (item.ETime.ToString("yyyy-MM-dd") != "0001-01-01")
                    {
                        endTime = item.ETime;
                    }
                    var element = new McaEventStatisticByDay
                    {
                        AlarmCode = string.Empty,
                        AlarmDesc = string.Empty,
                        StartTime = item.STime,
                        EndTime = endTime,
                        TimeSpan = item.FLen,
                        EventCode = DllHsms.StandardCode.CEID_Run,
                        McaCode = item.MacCode,
                        Manufacturer = item.Supplier
                    };

                    var macP = macProcesses.FirstOrDefault(c => c.MacCode == element.McaCode);
                    if (macP != null)
                    {
                        element.MacModel = macP.MacModel;
                        element.MacModelName = macP.MacModelName;
                        // element.Manufacturer = string.Empty;
                        element.McaName = macP.MacName;
                        element.PCode = macP.ProcessCode;
                        element.PName = macP.ProcessName;
                    }

                    if (element.EndTime != null)
                    {
                        element.IsHandled = 1;
                        handledIds += $"{item.ID},";
                        if (element.TimeSpan != null)
                        {
                            if (entities.FirstOrDefault(c => c.McaCode == element.McaCode
                             && c.StartTime == element.StartTime && c.EndTime == element.EndTime
                             && c.EventCode == element.EventCode) == null)
                                entities.Add(element);
                        }
                        index++;
                        Console.WriteLine($"========插入机台为{element.McaCode}的运行记录成功==========={index}");
                    }
                }
                //});
            }
            ids = string.IsNullOrEmpty(handledIds) ? handledIds : handledIds.Substring(0, handledIds.Length - 1);
            LogHelper<McaEventStatisticByDayDal>.LogFatal($"提取机台运行数据成功", "EapAsync", string.Empty);
            return entities;
        }

        /// <summary>
        /// 处理AOI报警
        /// </summary>
        /// <param name="date"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public IEnumerable<McaEventStatisticByDay> AppendAoiAlarms(DateTime date, out string ids)
        {
            var entities = new ConcurrentBag<McaEventStatisticByDay>();
            var handledIds = string.Empty;
            var alarms = GetAOIAlarms(date);
            if (alarms != null && alarms.Count() > 0)
            {
                var index = 0;
                //Parallel.ForEach<MacStatus>(macStatuses, item =>
                //{
                foreach (var item in alarms)
                {
                    DateTime? endTime = null;
                    if (item.ETime.ToString("yyyy-MM-dd") != "0001-01-01")
                    {
                        endTime = item.ETime;
                    }
                    var element = new McaEventStatisticByDay
                    {
                        AlarmCode = string.Empty,
                        AlarmDesc = string.Empty,
                        StartTime = item.STime,
                        EndTime = endTime,
                        TimeSpan = item.FLen,
                        EventCode = DllHsms.StandardCode.CEID_AlarmOccurred,
                        McaCode = item.MacCode
                    };

                    if (element.EndTime != null)
                    {
                        element.IsHandled = 1;
                        handledIds += $"{item.ID},";
                        if (element.TimeSpan != null)
                        {
                            if (entities.FirstOrDefault(c => c.McaCode == element.McaCode
                             && c.StartTime == element.StartTime && c.EndTime == element.EndTime
                             && c.EventCode == element.EventCode) == null)
                                entities.Add(element);
                        }
                        index++;
                        Console.WriteLine($"========插入机台为{element.McaCode}的报警记录成功==========={index}");
                    }
                }
                //});
            }
            ids = string.IsNullOrEmpty(handledIds) ? handledIds : handledIds.Substring(0, handledIds.Length - 1);
            return entities;
        }

        /// <summary>
        /// 获取所有机型制程对应关系
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MacTProcess> GetMacTProcesses()
        {
            string sql = "select a.*,b.FCode as MacCode,b.FName as MacName,c.FCode as ProcessCode,c.FName as ProcessName,d.FCode as macmodel," +
                "d.fname as MacModelName "
                + "from MacTProcess a " +
                "inner join Machine b on a.MacId=b.id " +
                "inner join TProcess c on a.PCode=c.FCode " +
                "inner join MacModel d on b.MModeId = d.id";
            return CurrDb.FindList<MacTProcess>(sql);
        }

        public string CreateDetailSql(DateTime date)
        {
            var sql = $@"select 
                      a.Id,b.McaCode as maccode,b.eventcode,c.FVal as name,
                      d.FVal as description,a.ptime
                      from (select * from McaSecTime temp where 
                      temp.ptime>'{date.AddMinutes(-62).ToString("yyyy-MM-dd HH:mm:ss")}' 
                      and temp.ptime<='{date.ToString("yyyy-MM-dd HH:mm:ss")}') a 
                      inner join McaSecVMst b on a.PreID = b.ID
                      left outer join McaSecVDetail c on b.ID = c.PreID and c.FNum = 2
                      left outer join McaSecVDetail d on b.ID = d.PreID and d.FNum = 3
                      "
                    + $"where 1=1 and (b.eventcode ='{DllHsms.StandardCode.CEID_AlarmOccurred}' " +
                    $"or b.eventcode='{DllHsms.StandardCode.CEID_AlarmRelease}') " +
                    $" and (a.ishandled is null or a.ishandled=0) and left(b.McaCode,4)<>'LXJC' ";
            return sql;
        }

        /// <summary>
        /// 获取AOI报警
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        private IEnumerable<MacStatus> GetAOIAlarms(DateTime date)
        {
            var sql = "select a.id,a.maccode,a.alarmcode,a.alarmdescribe,a.stime,a.etime,a.flen from macstatus where left(maccode,4)='LXJC' and " +
                $"stime>'{date.AddMinutes(-62).ToString("yyyy-MM-dd HH:mm:ss")}' " +
                $"and stime<'{date.ToString("yyyy-MM-dd HH:mm:ss")}' and ishandeled=0 " +
                $"and a.etime<>'0001-01-01 00:00:00' ";
            return CurrDb.FindList<MacStatus>(sql);
        }

        #endregion

        #region 大屏展示
        public IEnumerable<OrderProgressDto> GetOrderProgress(string filter)
        {
            string sql = @"select top 12 b.PoCode as OrderNo,Sum(c.FTotal) as CompleteCount from JobBooking a 
                           left join LotMst b on a.LotNo = b.LotNo 
                           left join JbStaff c on a.ID=c.JbID "
                          + $" where a.StatusID = 2 {filter} group by b.PoCode order by sum(b.FCount) desc";
            var progresses = CurrDb.FindList<OrderProgressDto>(sql);
            sql = "select sum(FCount) as Count ,pocode as OrderNo from LotMst where 1=1 "
                + $" and pocode in ({string.Join(",", progresses.Select(c => $"'{c.OrderNo}'"))}) group by PoCode";
            var orders = CurrDb.FindList<OrderCountDto>(sql);
            foreach (var item in progresses)
            {
                item.TotalCount = orders.FirstOrDefault(c => c.OrderNo == item.OrderNo).Count;
            }
            return progresses;
        }
        #endregion
    }
}