everlight.eap/backend/DllEapDal/OFILM/MacStatusTotalDal.cs

using Cksoft.Data;
using Cksoft.Data.Repository;
using Cksoft.Unity;
using DllEapEntity;
using DllEapEntity.Dtos;
using DllEapEntity.Enums;
using DllEapEntity.OFILM;
using DllHsms;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace DllEapDal.OFILM
{
    /// <summary>
    /// WEB版机台状态看板数据处理
    /// </summary>
    public class MacStatusTotalDal
    {
        string timeFormat = "yyyy-MM-dd HH:mm:ss";
        private IEnumerable<StandardStatus> standardStatuses;
        private IEnumerable<Machine> machines;

        private IDatabase CurrDb = null;

        private readonly IConfiguration configuration;

        private string svidFilter = " and a.AlarmCode<>'SVID'";

        public MacStatusTotalDal()
        {

        }

        public MacStatusTotalDal(IDatabase db)
        {
            CurrDb = db;
        }

        public MacStatusTotalDal(IDatabase db, IConfiguration configuration)
        {
            CurrDb = db;
            this.configuration = configuration;
        }

        public MacStatusTotalDal(IDatabase db, IConfiguration configuration,
            IEnumerable<StandardStatus> standardStatuses, IEnumerable<Machine> machines)
        {
            CurrDb = db;
            this.configuration = configuration;
            this.machines = machines;
            this.standardStatuses = standardStatuses;
        }

        /// <summary>
        /// 获取所有机台的状态
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<MacStatusForDashboardDto> Get(string filter, string date, string type, ref string errorinfo)
        {
            var dateNow = DateTime.Now;
            DateTime dateStart;
            DateTime dateEnd;
            EnumShift enumShift = EnumShift.Day;
            if (type == "1")
            {
                enumShift = EnumShift.Day;
            }
            else if (type == "2")
            {
                enumShift = EnumShift.Night;
            }
            else
            {
                enumShift = EnumShift.Full;
            }
            if (string.IsNullOrEmpty(date) || Convert.ToDateTime(date).ToString("yyyy-MM-dd") == dateNow.ToString("yyyy-MM-dd"))
            {
                if (dateNow <= new DateTime(dateNow.Year, dateNow.Month, dateNow.Day, 20, 30, 0)
                && dateNow > new DateTime(dateNow.Year, dateNow.Month, dateNow.Day, 8, 30, 0))
                {
                    dateStart = new DateTime(dateNow.Year, dateNow.Month, dateNow.Day, 8, 30, 0);
                }
                else
                {
                    var yesterday = dateNow.AddDays(-1);
                    dateStart = new DateTime(yesterday.Year, yesterday.Month, yesterday.Day, 20, 30, 0);
                }
                dateEnd = dateNow;
            }
            else
            {
                dateNow = Convert.ToDateTime(date);
                if (type == "1")//白班
                {
                    dateStart = new DateTime(dateNow.Year, dateNow.Month, dateNow.Day, 8, 30, 0);
                    dateEnd = new DateTime(dateNow.Year, dateNow.Month, dateNow.Day, 20, 30, 0);
                }
                else if (type == "2")//夜班
                {
                    var tomorrow = dateNow.AddDays(1);
                    dateStart = new DateTime(dateNow.Year, dateNow.Month, dateNow.Day, 20, 30, 0);
                    dateEnd = new DateTime(tomorrow.Year, tomorrow.Month, tomorrow.Day, 8, 30, 0);
                }
                else//整天
                {
                    var tomorrow = dateNow.AddDays(1);
                    dateStart = new DateTime(dateNow.Year, dateNow.Month, dateNow.Day, 8, 30, 0);
                    dateEnd = new DateTime(tomorrow.Year, tomorrow.Month, tomorrow.Day, 8, 30, 0);
                }
            }

            var status = new List<MacStatusForDashboardDto>();
            standardStatuses = CurrDb.FindListForCondition<StandardStatus>(string.Empty, ref errorinfo);
            if (standardStatuses == null || standardStatuses.Count() <= 0)
            {
                errorinfo = "机台标准状态为空";
                return null;
            }
            var idle = standardStatuses.FirstOrDefault(c => c.FCode.ToUpper() == "IDLE");
            var disconn = standardStatuses.FirstOrDefault(t => t.FCode.ToUpper() == "DISCONN");
            var sql = $@"select a.FCode maccode,c.pcode processCode,{OfilmRecipeProvider.GenRecipeString("b")} recipe,
                       b.StatusID status,b.STime,b.ETime,b.alarmCode,
                       ifnull(d.FName,'离线') statusName,ifnull(d.FCode,'DISCONN') statusCode,f.FName factory,e.FCode macmodel,
                       ifnull(d.red,178) red ,ifnull(d.green,178) green,ifnull(d.blue,178) blue,
                       ifnull(d.alpha,178) alpha,i.FName floor,h.FName line,i.Rank floorRank,
	                   h.Rank lineRank,
                       l.fname plant,g.rank,g.UseImage  
                       from machine a 
                       left join macstatus01 b on a.FCode=b.maccode
                       left join mactprocess c on a.id=c.macid
                       left join standardstatus d on b.statusid=d.StatusVal
                       left join macmodel e on a.MModeId=e.id
                       left join factoryregion f on a.factoryId = f.id
                       left join tprocess g on c.pcode=g.fcode 
                       left join factoryregion h on a.regionid=h.id
                       left join factoryregion i on h.parentid=i.id
                       left join factoryregion l on i.parentid=l.id 
                       where 1=1 and b.STime is not null {filter} order by i.Rank,h.Rank,macCode asc";

            var macLastHistories = new List<MacLastStatus>();

            var allMachines = CurrDb.FindList<MacStatusForDashboardDto>(sql);
            var statusNames = _getTableNames("macstatus", dateStart, dateEnd);

            var viewLastStausName = string.Empty;
            var temporaryName = string.Empty;

            List<MacStatusForDashboardDto> lastStatuses = new List<MacStatusForDashboardDto>(); ;

            // 当前日期不是选中的日期
            if (!(dateStart.ToString("yyyy-MM-dd") == DateTime.Now.ToString("yyyy-MM-dd")
                || (dateStart.ToString("yyyy-MM-dd") == DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")
                && DateTime.Now < Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 08:30")))))
            {
                // 历史数据查询历史稼动率数据表
                if (enumShift == EnumShift.Full)
                {
                    sql = $@"select a.FCode maccode,c.pcode processCode,b.Remark recipe,
                       b.StatusID status,b.STime,
                       ifnull(d.FName, '离线') statusName,ifnull(d.FCode, 'DISCONN') statusCode,f.FName factory,e.FCode macmodel,
                       ifnull(d.red, 178) red ,ifnull(d.green, 178) green,ifnull(d.blue, 178) blue,
                       ifnull(d.alpha, 178) alpha,i.FName floor, h.FName line, i.Rank floorRank,
                       h.Rank lineRank,l.fname plant, g.rank,g.UseImage
                       from machine a
                       left join maclaststatus b on a.FCode = b.maccode
                       left join mactprocess c on a.id = c.macid
                       left join standardstatus d on b.statusid = d.StatusVal
                       left join macmodel e on a.MModeId = e.id
                       left join factoryregion f on a.factoryId = f.id
                       left join tprocess g on c.pcode = g.fcode
                       left join factoryregion h on a.regionid = h.id
                       left join factoryregion i on h.parentid = i.id
                       left join factoryregion l on i.parentid = l.id
                       where 1 = 1 and b.FDate='{dateStart.ToString("yyyy-MM-dd")}' 
                       and shift = {(int)EnumShift.Night} { filter}
                       order by i.Rank,h.Rank,macCode asc";
                    lastStatuses = CurrDb.FindList<MacStatusForDashboardDto>(sql).ToList();
                    sql = $@"select sum(runrate*100)/2 as value,maccode as name from maclaststatus 
                          where fdate='{dateStart.ToString("yyyy-MM-dd 00:00:00")}' 
                          group by maccode";
                    var historyRunrates = CurrDb.FindList<RunRateDto>(sql);
                    if (historyRunrates.Any())
                    {
                        Parallel.ForEach(lastStatuses, item =>
                        {
                            item.RunRate = historyRunrates.FirstOrDefault(c => c.Name == item.MacCode)?.Value ?? 0;
                        });
                    }
                }
                else
                {
                    sql = $@"select a.FCode maccode,c.pcode processCode,b.Remark recipe,
                       b.StatusID status,b.STime,
                       ifnull(d.FName, '离线') statusName,ifnull(d.FCode, 'DISCONN') statusCode,f.FName factory, e.FCode macmodel,
                       ifnull(d.red, 178) red ,ifnull(d.green, 178) green,ifnull(d.blue, 178) blue,
                       ifnull(d.alpha, 178) alpha,i.FName floor, h.FName line, i.Rank floorRank,
                       h.Rank lineRank,l.fname plant, g.rank,g.UseImage,b.runrate*100 
                       from machine a
                       left join maclaststatus b on a.FCode = b.maccode
                       left join mactprocess c on a.id = c.macid
                       left join standardstatus d on b.statusid = d.StatusVal
                       left join macmodel e on a.MModeId = e.id
                       left join factoryregion f on a.factoryId = f.id
                       left join tprocess g on c.pcode = g.fcode
                       left join factoryregion h on a.regionid = h.id
                       left join factoryregion i on h.parentid = i.id
                       left join factoryregion l on i.parentid = l.id
                       where 1 = 1 and b.FDate='{dateStart.ToString("yyyy-MM-dd")}' 
                       and shift = {(int)enumShift} { filter}
                order by i.Rank,h.Rank,macCode asc";
                    lastStatuses = CurrDb.FindList<MacStatusForDashboardDto>(sql).ToList();
                }

                if (!lastStatuses.Any() || lastStatuses.All(c => c.RunRate == 0))
                {
                    // 查询结束时间前2个小时的状态存入临时视图，减小数据量，用新生成的视图查询最后的状态
                    // 并关联其他表取基础信息
                    var tempStart = dateEnd.AddHours(-2);
                    viewLastStausName = $"v_temp_last_status_{DateTime.Now.Ticks}";
                    var createLastStatusSql = $"CREATE OR REPLACE VIEW {viewLastStausName} AS ";
                    createLastStatusSql += $" SELECT * FROM {statusNames.Last()} " +
                        $"WHERE STime>'{tempStart.ToString("yyyy-MM-dd HH:mm:ss")}' " +
                        $"AND STime<'{dateEnd.ToString("yyyy-MM-dd HH:mm:ss")}';";
                    CurrDb.ExecuteBySql(createLastStatusSql);
                    temporaryName = $"macstatus_last_temp_{DateTime.Now.Ticks}";
                    // 将最后的记录放到临时表里
                    createLastStatusSql = $@"CREATE TEMPORARY TABLE {temporaryName} 
                       select * from {viewLastStausName} where id in (select max(id) from 
                       {viewLastStausName} group by maccode);";
                    CurrDb.ExecuteBySql(createLastStatusSql);
                    // 查询历史数据
                    sql = $@"select a.FCode maccode,c.pcode processCode,{OfilmRecipeProvider.GenRecipeString("b")} recipe,
                       b.StatusID status, b.STime,b.ETime,b.alarmCode,
                       ifnull(d.FName, '离线') statusName,ifnull(d.FCode, 'DISCONN') statusCode,f.FName factory, e.FCode macmodel,
                       ifnull(d.red, 178) red ,ifnull(d.green, 178) green,ifnull(d.blue, 178) blue,
                       ifnull(d.alpha, 178) alpha,i.FName floor, h.FName line, i.Rank floorRank,
                       h.Rank lineRank,l.fname plant, g.rank,g.UseImage 
                       from machine a
                       left join {temporaryName} b on a.FCode = b.maccode
                       left join mactprocess c on a.id = c.macid
                       left join standardstatus d on b.statusid = d.StatusVal
                       left join macmodel e on a.MModeId = e.id
                       left join factoryregion f on a.factoryId = f.id
                       left join tprocess g on c.pcode = g.fcode
                       left join factoryregion h on a.regionid = h.id
                       left join factoryregion i on h.parentid = i.id
                       left join factoryregion l on i.parentid = l.id
                       where 1 = 1 and b.STime is not null { filter}
                order by i.Rank,h.Rank,macCode asc";
                    var lastDal = new MacLastStatusDal(CurrDb);
                    macLastHistories = lastDal.GetLastStatus(dateStart, enumShift).Result.ToList();
                    lastStatuses = CurrDb.FindList<MacStatusForDashboardDto>(sql).ToList();
                }
            }
            else
            {
                // 查询最新数据
                sql = $@"select a.FCode maccode,c.pcode processCode,{OfilmRecipeProvider.GenRecipeString("b")} recipe,
                       b.StatusID status, b.STime,b.ETime,b.alarmCode,
                       ifnull(d.FName, '离线') statusName,ifnull(d.FCode, 'DISCONN') statusCode,f.FName factory, e.FCode macmodel,
                       ifnull(d.red, 178) red ,ifnull(d.green, 178) green,ifnull(d.blue, 178) blue,
                       ifnull(d.alpha, 178) alpha,i.FName floor, h.FName line, i.Rank floorRank,
                       h.Rank lineRank,l.fname plant, g.rank,g.UseImage 
                       from machine a
                       left join macstatus01 b on a.FCode = b.maccode
                       left join mactprocess c on a.id = c.macid
                       left join standardstatus d on b.statusid = d.StatusVal
                       left join macmodel e on a.MModeId = e.id
                       left join factoryregion f on a.factoryId = f.id
                       left join tprocess g on c.pcode = g.fcode
                       left join factoryregion h on a.regionid = h.id
                       left join factoryregion i on h.parentid = i.id
                       left join factoryregion l on i.parentid = l.id
                       where 1 = 1 and b.STime is not null { filter}
                order by i.Rank,h.Rank,macCode asc";
                lastStatuses = CurrDb.FindList<MacStatusForDashboardDto>(sql).ToList();
            }

            var excepts = new List<MacStatusForDashboardDto>();
            if (lastStatuses.Count() < allMachines.Count())
            {
                excepts = allMachines.Where(c => lastStatuses.All(t => t.MacCode != c.MacCode)).ToList();
                lastStatuses.AddRange(excepts.Select(c =>
                {
                    c.RunRate = 0;
                    return c;
                }));
            }
            // 删除生成的视图和临时表
            if (!string.IsNullOrEmpty(viewLastStausName))
            {
                var dropSql = $"DROP VIEW IF EXISTS {viewLastStausName};";
                CurrDb.ExecuteBySql(dropSql);
                dropSql = $"DROP TABLE IF EXISTS {temporaryName};";
                CurrDb.ExecuteBySql(dropSql);
            }

            foreach (var item in lastStatuses)
            {
                var temp = item;
                temp.Color = new RgbColor(temp.Red.Value, temp.Green.Value, temp.Blue.Value,
                    temp.Alpha.Value);
                if (temp.Status == 5 || (temp.Status == 6 && temp.AlarmCode == "SVID"))
                {
                    temp.Status = idle.ID;
                    temp.StatusCode = idle.FCode;
                    temp.StatusName = idle.FName;
                    temp.Color = new RgbColor(idle.Red, idle.Green, idle.Blue, idle.Alpha);
                }
                if (string.IsNullOrEmpty(temp.Recipe) || string.IsNullOrWhiteSpace(temp.Recipe)
                    || temp.Recipe == "\0")
                {
                    temp.Recipe = "暂无";
                }
                decimal timeLen = 0;
                if (temp.STime == null)
                {
                    timeLen = 0;
                }
                else
                {
                    timeLen = (decimal)(dateEnd - temp.STime.Value).TotalSeconds;
                    timeLen = timeLen < 0 ? 0 : timeLen;
                    if (macLastHistories.Count > 0)
                    {
                        var lastHistory = macLastHistories.FirstOrDefault(c => c.MacCode == temp.MacCode);
                        if (lastHistory != null && lastHistory.StatusId == temp.Status)
                        {
                            timeLen = (decimal)lastHistory.Flen;
                        }
                    }
                }
                temp.TimeLen = timeLen;
                status.Add(temp);
            }

            IEnumerable<RunRateDto> runTimes = null;
            var totalTimeLen = (dateEnd - dateStart).TotalSeconds;

            if (dateStart.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd"))
            {
                if (status.All(c => c.RunRate == 0))
                {
                    if (dateStart < DateTime.Now.AddDays(-30))
                    {
                        var viewName = $"V_MacStatus_Dashboard_{DateTime.Now.Ticks}";

                        var createSql = $"CREATE TEMPORARY TABLE {viewName} ";

                        for (int i = 0; i < statusNames.Count(); i++)
                        {
                            createSql += $@"select sum(tt.flen) as Value,tt.maccode as Name from (
                   select sum(flen) flen,maccode from {statusNames.ElementAt(i)} where stime>'{dateStart.ToString(timeFormat)}' and stime<'{dateEnd.ToString(timeFormat)}' and statusid=4
                   group by maccode 
                   union all 
                   select time_to_sec(timediff('{dateEnd.ToString(timeFormat)}',stime)) as flen,maccode from {statusNames.ElementAt(i)} where
                   statusid=4 and etime='0001-01-01 00:00:00' and flen=0 and stime>'{dateStart.ToString(timeFormat)}' and stime<'{dateEnd.ToString(timeFormat)}'
                   union all 
                   select time_to_sec(timediff(etime, '{dateStart.ToString(timeFormat)}')) as flen,
                   maccode from 
                   {statusNames.ElementAt(i)} where statusid=4 and stime<='{dateStart.ToString(timeFormat)}' and etime>'{dateStart.ToString(timeFormat)}'
                   ) tt 
                   left join machine a on tt.maccode=a.fcode
                   left join macstatus01 b on a.FCode=b.maccode
                   left join mactprocess c on a.id=c.macid
                   left join standardstatus d on b.statusid=d.StatusVal
                   left join macmodel e on a.MModeId=e.id
                   left join factoryregion f on a.factoryId = f.id
                   left join tprocess g on c.pcode=g.fcode 
                   left join factoryregion h on a.regionid=h.id
                   left join factoryregion i on h.parentid=i.id 
                   left join factoryregion l on i.parentid=l.id 
                   where 1=1 {filter} and h.FName not like '%试产%'  group by tt.maccode";
                        }
                        createSql += " union all ";

                        createSql = createSql.Substring(0, createSql.Length - 11);
                        CurrDb.ExecuteBySql(createSql);
                        sql = $"select sum(value) value,name from {viewName} group by name";
                        runTimes = CurrDb.FindList<RunRateDto>(sql);
                        sql = $"DROP TABLE IF EXISTS {viewName}";
                        CurrDb.ExecuteBySql(sql);
                    }
                    else
                    {
                        runTimes = FindMacRunRates(dateStart, dateEnd, filter);
                    }
                    status = AppendMacRunRate(status, runTimes, totalTimeLen).ToList();
                }
            }
            else
            {
                runTimes = FindMacRunRates(dateStart, dateEnd, filter);
                status = AppendMacRunRate(status, runTimes, totalTimeLen).ToList();
            }

            return status;
        }

        /// <summary>
        /// 加上稼动率
        /// </summary>
        /// <param name="status"></param>
        /// <param name="runTimes"></param>
        /// <param name="totalTimeLen"></param>
        /// <returns></returns>
        private IEnumerable<MacStatusForDashboardDto> AppendMacRunRate(IEnumerable<MacStatusForDashboardDto> status,
            IEnumerable<RunRateDto> runTimes, double totalTimeLen)
        {
            Parallel.ForEach(status, item =>
            {
                var runTime = runTimes.FirstOrDefault(c => c.Name == item.MacCode);
                if (runTime == null)
                {
                    item.RunRate = 0;
                }
                else
                {
                    item.RunRate = runTime.Value / totalTimeLen * 100;
                }
            });
            return status;
        }

        /// <summary>
        /// 查询稼动率
        /// </summary>
        /// <param name="dateStart"></param>
        /// <param name="dateEnd"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        private IEnumerable<RunRateDto> FindMacRunRates(DateTime dateStart, DateTime dateEnd, string filter)
        {
            var sql = $@"select sum(tt.flen) as Value,tt.maccode as Name from (
                   select sum(flen) flen,maccode from macstatus where stime>'{dateStart.ToString(timeFormat)}' and stime<'{dateEnd.ToString(timeFormat)}' and statusid=4
                   group by maccode 
                   union all 
                   select time_to_sec(timediff('{dateEnd.ToString(timeFormat)}',stime)) as flen,maccode from macstatus where
                   statusid=4 and etime='0001-01-01 00:00:00' and flen=0 and stime>'{dateStart.ToString(timeFormat)}' and stime<'{dateEnd.ToString(timeFormat)}'
                   union all 
                   select time_to_sec(timediff(etime, '{dateStart.ToString(timeFormat)}')) as flen,
                   maccode from 
                   macstatus where statusid=4 and stime<='{dateStart.ToString(timeFormat)}' and etime>'{dateStart.ToString(timeFormat)}'
                   ) tt 
                   left join machine a on tt.maccode=a.fcode
                   left join macstatus01 b on a.FCode=b.maccode
                   left join mactprocess c on a.id=c.macid
                   left join standardstatus d on b.statusid=d.StatusVal
                   left join macmodel e on a.MModeId=e.id
                   left join factoryregion f on a.factoryId = f.id
                   left join tprocess g on c.pcode=g.fcode 
                   left join factoryregion h on a.regionid=h.id
                   left join factoryregion i on h.parentid=i.id 
                   left join factoryregion l on i.parentid=l.id 
                   where 1=1 {filter} and h.FName not like '%试产%'  group by tt.maccode";
            return CurrDb.FindList<RunRateDto>(sql);
        }

        /// <summary>
        /// 获取机台状态统计数据
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public MacStatusTotalInfo GetTotalInfo(string filter, string recipeName, string date, string type, ref string errorinfo)
        {
            var datas = this.Get(filter, date, type, ref errorinfo).Where(c => !string.IsNullOrEmpty(c.ProcessCode));
            if (!string.IsNullOrEmpty(recipeName))
            {
                datas = datas.Where(c => c.Recipe.Contains(recipeName));
            }
            if (datas == null || datas.Count() <= 0)
            {
                return null;
            }
            var factory = datas.FirstOrDefault(c => !string.IsNullOrEmpty(c.Factory)).Factory;
            var plant = datas.FirstOrDefault(c => !string.IsNullOrEmpty(c.Plant)).Plant;
            var total = datas.Count();    // 机台总数量
            var statusGroups = datas.Where(c => !string.IsNullOrEmpty(c.StatusCode)).GroupBy(c => c.StatusName);
            IList<Status> dic = null;
            if (statusGroups != null && statusGroups.Count() > 0)
            {
                dic = new List<Status>();
                foreach (var item in statusGroups)
                {
                    var standard = standardStatuses.FirstOrDefault(s => s.FName == item.Key);
                    var temp = new Status
                    {
                        StatusCode = standard.FCode,
                        StatusName = standard.FName,
                        Count = item.Count(),
                        Color = new RgbColor(standard.Red, standard.Green, standard.Blue)
                    };
                    dic.Add(temp);
                }
            }
            var model = new MacStatusTotalInfo();
            model.StatusCount = dic;
            model.Total = total;
            model.Factory = factory;
            model.Plant = plant;

            var lineGroups = datas.GroupBy(c => new { c.Floor, c.FloorRank, c.Line, c.LineRank })
                .OrderBy(c => c.Key.FloorRank).ThenBy(c => c.Key.LineRank);

            var lines = new List<Line>();
            foreach (var item in lineGroups)
            {
                string recipe = null;

                var modelDic = new Dictionary<string, IEnumerable<MacStatusForDashboardDto>>();

                var pcodeGroups = item.GroupBy(c => new { c.ProcessCode, c.Rank }).OrderBy(c => c.Key.Rank);
                if (pcodeGroups != null && pcodeGroups.Count() > 0)
                {
                    foreach (var group in pcodeGroups)
                    {
                        modelDic.Add(group.Key.ProcessCode.ToLower(), group.AsEnumerable());
                    }
                }

                lines.Add(new Line
                {
                    Recipe = recipe,
                    FloorName = item.Key.Floor,
                    LineName = item.Key.Line,
                    ModelMachines = modelDic
                });
            }
            model.Lines = lines;
            var processRates = this.GetRunRates(datas);
            var chartDto = new ChartDto2
            {
                text = "各个工序稼动率",
                legend = new string[] { "稼动率" },
                xdata = processRates.Select(c => c.Name).ToArray(),
                ydata = processRates.Select(c => c.Value).ToArray()
            };
            model.ProcessRunRate = chartDto;
            return model;
        }

        /// <summary>
        /// 获取各个工序稼动率
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="take"></param>
        /// <returns></returns>
        public ChartDto2 GetProcessRunRate(string filter, string recipe, DateTime dateStart, DateTime dateEnd, ref string errorinfo)
        {
            var dto = this.GetRunRates(filter, recipe, dateStart, dateEnd, ref errorinfo);
            var chartDto = new ChartDto2
            {
                text = "各个工序稼动率",
                legend = new string[] { "稼动率" },
                xdata = dto.Select(c => c.Name).ToArray(),
                ydata = dto.Select(c => c.Value).ToArray()
                //ydata = new double[] { 0.9, 0.1, 0.5, 1.3 }
            };
            return chartDto;
        }

        public ChartDto2 GetProcessRunRate(string filter, string date, string type, string recipe, ref string errorinfo)
        {
            var dto = this.GetRunRates2(filter, date, type, recipe, ref errorinfo);
            var chartDto = new ChartDto2
            {
                text = "各个工序稼动率",
                legend = new string[] { "稼动率" },
                xdata = dto.Select(c => c.Name).ToArray(),
                ydata = dto.Select(c => c.Value).ToArray()
                //ydata = new double[] { 0.9, 0.1, 0.5, 1.3 }
            };
            return chartDto;
        }

        private IEnumerable<RunRateDto> GetRunRates(string filter, string recipe, DateTime dateStart,
            DateTime dateEnd, ref string errorinfo)
        {
            var regex = new Regex($"and b.statusId in (.*\\))");
            var statusFilter = filter;
            var match = regex.Match(statusFilter);
            if (match != null && !string.IsNullOrEmpty(match.Value))
            {
                statusFilter = statusFilter.Replace(match.Value, "");
            }

            var recipeFilter = "";
            if (!string.IsNullOrEmpty(recipe))
            {
                recipeFilter += $" and b.remark like '%{recipe}%'";
            }
            var machines = GetDisconnMacs(dateStart, statusFilter, ref errorinfo);
            var macFilter = string.Empty;
            if (machines != null && machines.Count() > 0)
            {
                macFilter = $" and a.FCode in ({string.Join(",", machines.Select(c => $"'{c.FCode}'"))})";
            }
            string sql = "";
            if (dateStart.ToString("yyyy-MM-dd") == DateTime.Now.ToString("yyyy-MM-dd"))
            {
                sql = $@"select sum(flen) as TimeLen,c.pcode as ProcessCode from 
                        (select sum(flen) flen,b.maccode from macstatus b 
                       where b.statusid=4 and b.stime>=STR_TO_DATE('{dateStart.ToString("yyyy-MM-dd HH:mm:ss")}', '%Y-%m-%d %H:%i:%s') and 
                       b.etime<=STR_TO_DATE( '{dateEnd.ToString("yyyy-MM-dd HH:mm:ss")}', '%Y-%m-%d %H:%i:%s') {recipeFilter} group by maccode 
union all 
                   select time_to_sec(sum(timediff('{dateEnd.ToString(timeFormat)}',stime))) as flen,maccode from macstatus   where
                   statusid=4 and etime='0001-01-01 00:00:00' and flen=0 and stime>'{dateStart.ToString(timeFormat)}' group by maccode
                   union all 
                   select time_to_sec(sum(timediff(etime, '{dateStart.ToString(timeFormat)}'))) as flen,
                   maccode from macstatus   where statusid=4 and stime<='{dateStart.ToString(timeFormat)}' and etime>'{dateStart.ToString(timeFormat)}' group by maccode
                        ) as t
                       left join machine a on t.maccode=a.fcode
                       left join mactprocess c on a.id=c.macid
                       left join tprocess g on c.pcode=g.FCode 
                       left join factoryregion h on a.regionid=h.id 
                       left join factoryregion i on h.parentid=i.id
                       left join factoryregion l on i.parentid=l.id
                       where 1=1 and h.FName not like '%试产%' {statusFilter} {macFilter}
                       group by c.pcode";
            }
            else
            {
                sql = $@"select sum(flen) as TimeLen,c.pcode as ProcessCode from (select b.maccode,sum(flen) flen from macstatus b 
                       where b.statusid=4 and b.stime>=STR_TO_DATE('{dateStart.ToString("yyyy-MM-dd HH:mm:ss")}', '%Y-%m-%d %H:%i:%s') and 
                       b.etime<=STR_TO_DATE( '{dateEnd.ToString("yyyy-MM-dd HH:mm:ss")}', '%Y-%m-%d %H:%i:%s') {recipeFilter} group by maccode  
union all 
                   select time_to_sec(sum(timediff('{dateEnd.ToString(timeFormat)}',stime))) as flen,maccode from macstatus   where
                   statusid=4 and etime='0001-01-01 00:00:00' and flen=0 and stime>'{dateStart.ToString(timeFormat)}' and stime<'{dateEnd.ToString(timeFormat)}' group by maccode
                   union all 
                   select time_to_sec(sum(timediff(etime, '{dateStart.ToString(timeFormat)}'))) as flen,
                   maccode from macstatus   where statusid=4 and stime<='{dateStart.ToString(timeFormat)}' and etime>'{dateStart.ToString(timeFormat)}' group by maccode
) as t
                       left join machine a on t.maccode=a.fcode
                       left join mactprocess c on a.id=c.macid
                       left join tprocess g on c.pcode=g.FCode 
                       left join factoryregion h on a.regionid=h.id 
                       left join factoryregion i on h.parentid=i.id
                       left join factoryregion l on i.parentid=l.id
                       where 1=1 and h.FName not like '%试产%' {statusFilter} {macFilter}
                       group by c.pcode";
            }

            var runTimes = CurrDb.FindList<RunTime>(sql);

            var rates = new List<RunRateDto>();
            var currTimeLen = (dateEnd - dateStart).TotalSeconds;
            var codes = CurrDb.FindListForCondition<TProcess>($" and a.ShowChart=1", ref errorinfo).OrderBy(c => c.Rank)
                .Select(c => c.FCode);
            //var macRunrates = 
            //foreach (var item in runTimes)
            //{

            //}
            foreach (var item in codes)
            {
                var count = machines.Count(c => c.PCode == item);
                var runTime = runTimes.FirstOrDefault(c => c.ProcessCode == item);
                double rate = 0;
                if (runTime != null && count != 0)
                {
                    rate = Convert.ToDouble(runTime.TimeLen) / Convert.ToDouble(count) / currTimeLen;
                }
                rates.Add(new RunRateDto
                {
                    Name = item,
                    Value = rate
                });
            }
            return rates;
        }

        private IEnumerable<RunRateDto> GetRunRates2(string filter, string date, string type, string recipe, ref string errorinfo)
        {
            var rates = new List<RunRateDto>();
            var codes = CurrDb.FindListForCondition<TProcess>($" and a.ShowChart=1", ref errorinfo).OrderBy(c => c.Rank)
                .Select(c => c.FCode);
            if (!string.IsNullOrEmpty(recipe))
            {
                filter += $" and b.remark like '%{recipe}%'";
            }
            var macStatuses = this.Get(filter, date, type, ref errorinfo);
            // var machines = GetDisconnMacs(Convert.ToDateTime(date), filter, ref errorinfo);
            if (macStatuses != null && macStatuses.Count() > 0)
            {
                foreach (var item in codes)
                {
                    var count = macStatuses.Where(c => c.UseImage == item && c.Status != 1 && c.Status != 204906).Count();
                    double rate = 0;
                    if (count > 0)
                    {
                        //var runTime = runTimes.FirstOrDefault(c => c.ProcessCode == item);
                        rate = macStatuses.Where(c => c.UseImage == item && c.Status != 1 && c.Status != 204906).Sum(t => t.RunRate) / count / 100;
                    }
                    else
                    {
                        rate = 0;
                    }
                    rates.Add(new RunRateDto
                    {
                        Name = item,
                        Value = rate
                    });
                }
            }
            return rates;
        }

        /// <summary>
        /// 根据机台状态计算工序汇总的稼动率
        /// </summary>
        /// <param name="macStatuses"></param>
        /// <returns></returns>
        private IEnumerable<RunRateDto> GetRunRates(IEnumerable<MacStatusForDashboardDto> macStatuses)
        {
            string errorinfo = string.Empty;
            var rates = new List<RunRateDto>();
            var codes = CurrDb.FindListForCondition<TProcess>($" and a.ShowChart=1", ref errorinfo).OrderBy(c => c.Rank)
                .Select(c => c.FCode);
            if (macStatuses != null && macStatuses.Count() > 0)
            {
                foreach (var item in codes)
                {
                    var count = macStatuses.Where(c => c.UseImage == item && c.Status != 1 && c.Status != 204906).Count();
                    double rate = 0;
                    if (count > 0)
                    {
                        rate = macStatuses.Where(c => c.UseImage == item && c.Status != 1 && c.Status != 204906).Sum(t => t.RunRate) / count / 100;
                    }
                    else
                    {
                        rate = 0;
                    }
                    rates.Add(new RunRateDto
                    {
                        Name = item,
                        Value = rate
                    });
                }
            }
            return rates;
        }

        /// <summary>
        /// 获取未断线及关机的机台
        /// </summary>
        /// <returns></returns>
        public IEnumerable<Machine> GetDisconnMacs(DateTime dateStart, string filter, ref string errorinfo)
        {
            var sql = $"SELECT maccode FROM eap.macstatus01 where stime<'{dateStart.ToString("yyyy-MM-dd HH:mm:ss")}' " +
                $"and etime='0001-01-01 00:00:00' and (StatusID=1 or StatusID=204906)";
            var macs = CurrDb.FindListForCondition<Machine>($" and a.FCode in ({sql}) ", ref errorinfo);
            return macs;
        }

        public MacStatusForDashboardDetailDto GetDetail(string maccode, string starttime, string endtime)
        {
            string errorinfo = string.Empty;
            var st = Convert.ToDateTime(starttime);
            string startMonthTime = Convert.ToDateTime(starttime).ToString("yyyy-MM-") + "01 00:00";
            string endMonthTime = Convert.ToDateTime(startMonthTime).AddMonths(1).ToString("yyyy-MM-01 00:00:00");
            double timediff = Convert.ToDateTime(endtime).Subtract(Convert.ToDateTime(starttime)).TotalHours;
            MacStatusForDashboardDetailDto macStatusForDashboardDetailDto = new MacStatusForDashboardDetailDto();
            string sqlstr = $" and a.FCode='{maccode}'";
            MacStatusForDashboardDto entity = Get(sqlstr, "", "", ref errorinfo)?.FirstOrDefault();//运行状态
                                                                                                   //var sql = $@" CALL GetTotalInfoDeatil ('{maccode}','{starttime}','{endtime}') ";
            #region sql
            var statusNames = _getTableNames("macstatus", st, st.AddDays(1));
            var countmstNames = _getTableNames("maccountmst", st, st.AddDays(1));
            var countDetailNames = _getTableNames("maccountdetail", st, st.AddDays(1));

            var viewName = $"V_MacStatus_Detail_{DateTime.Now.Ticks}";
            var createSql = $"CREATE OR REPLACE VIEW {viewName} AS ";

            for (int i = 0; i < statusNames.Count(); i++)
            {
                createSql += $@" select  (SELECT
	                                    COUNT(*)
                                    FROM
	                                    {statusNames.ElementAt(i)} as A
                                    WHERE
	                                    MacCode = '{maccode}'
	                                    AND STime >  '{starttime}'
	                                    AND STime < '{endtime}'
	                                    AND StatusID=6 AND AlarmCode<>'SVID') AlarmCount
                                ,(SELECT
	                                ifnull(ROUND(SUM(FLen)/60),0)
                                FROM
	                                macstatus 
                                WHERE
	                                MacCode = '{maccode}'
	                                AND STime >  '{starttime}'
	                                AND STime < '{endtime}'
	                                AND StatusID=4) RunTime
                            ,(SELECT  ifnull(sum(c.FCount),0)
	                            FROM {countDetailNames.ElementAt(i)} as C
	                            LEFT JOIN {countmstNames.ElementAt(i)} as A ON A.ID=C.MstID
	                            LEFT JOIN machine as B on A.MacID=b.ID
	                            WHERE B.FCode='{maccode}'
	                            AND A.EDate>'{starttime}'
	                            AND A.EDate < '{endtime}'
                                AND C.ParamCode='{StandardCode.SVID_FinishUnit}'
		                            AND C.TypeID=0) TodayCount
                            ,(	SELECT  ifnull(sum(c.FCount),0)
	                            FROM {countDetailNames.ElementAt(i)} as C
	                            LEFT JOIN {countmstNames.ElementAt(i)} as A ON A.ID=C.MstID
	                            LEFT JOIN machine as B on A.MacID=b.ID
	                            WHERE B.FCode='{maccode}'
		                            AND A.SDate>'{startMonthTime}'
		                            AND A.SDate<'{endMonthTime}'
                                    AND C.ParamCode='{StandardCode.SVID_FinishUnit}'
	                            AND C.TypeID=0) MonthCount
";
                createSql += " union all ";
            }
            createSql = createSql.Substring(0, createSql.Length - 11);
            CurrDb.ExecuteBySql(createSql);

            var sql = $"select sum(AlarmCount) AlarmCount,sum(RunTime) RunTime," +
                $"sum(TodayCount) TodayCount,sum(MonthCount) MonthCount from {viewName} ";

            #endregion

            var status = CurrDb.FindList<MacStatusForDashboardDetailDto>(sql).FirstOrDefault();
            status.RunTime = string.IsNullOrEmpty(status.RunTime) ? "0" : status.RunTime;

            sql = $"DROP VIEW IF EXISTS {viewName}";
            CurrDb.ExecuteBySql(sql);

            // 获取小于StartTime的最后一条状态，如果该状态结束时间大于StartTime,
            // 将该状态的结束时间-StartTime的时长计入对应状态
            var first = CurrDb.FindListForCondition<MacStatus>($" and a.maccode='{maccode}' and a.stime<'{starttime}'" +
                $" and a.statusid=4 order by a.id desc limit 0,1", ref errorinfo)
                .FirstOrDefault();
            if (first != null && first.ETime > Convert.ToDateTime(starttime))
            {
                status.RunTime = (Convert.ToInt32(status.RunTime) + Math.Round((first.ETime - Convert.ToDateTime(starttime)).TotalMinutes)).ToString();
            }
            status.RunRate = Math.Round((Convert.ToDouble(status.RunTime) / timediff * 100), 2).ToString();
            status.RunRate = (!string.IsNullOrEmpty(status.RunRate)) ? status.RunRate : "0.00";
            status.AlarmCount = string.IsNullOrEmpty(status.AlarmCount) ? "0" : status.AlarmCount;
            status.TodayCount = string.IsNullOrEmpty(status.TodayCount) ? "0" : status.TodayCount;
            status.MonthCount = Math.Round((Convert.ToDouble(status.TodayCount) / timediff), 2).ToString();
            status.Color = entity?.Color;
            macStatusForDashboardDetailDto = status;
            macStatusForDashboardDetailDto.Recipe = entity?.Recipe;//机种
            macStatusForDashboardDetailDto.StatusName = entity?.StatusName;//当前状态
            macStatusForDashboardDetailDto.StatusCode = entity?.StatusCode;
            macStatusForDashboardDetailDto.Factory = entity?.Factory;
            macStatusForDashboardDetailDto.RegionName = entity?.RegionName;
            return macStatusForDashboardDetailDto;
        }

        /// <summary>
        /// 获取报警信息
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public IOrderedEnumerable<MacStatusForAnalysis> GetDetailForAnalysis(string maccode, string starttime, string endtime)
        {
            string errorinfo = string.Empty;
            string st = Convert.ToDateTime(starttime).ToString("yyyy-MM-dd") + " 00:00:00";
            string endtt = Convert.ToDateTime(st).AddHours(24).ToString();
            var tbNames = this._getTableNames("macstatus", Convert.ToDateTime(st), Convert.ToDateTime(endtt));
            var alarms = new List<MacStatusForAnalysis>();
            string sql = string.Empty;
            var viewName = $"v_temp_alarm_{DateTime.Now.Ticks}";
            for (int i = 0; i < tbNames.Count(); i++)
            {
                sql += $@"SELECT count(*) AlarmCount,AlarmCode,AlarmDescribe
                            FROM {tbNames.ElementAt(i)} as A
                            where A.MacCode = '{maccode}'
	                            AND A.STime > '{starttime}'
	                            AND A.STime < '{endtime}'     
                                AND StatusID=6 
                                AND a.AlarmCode <> 'SVID'
                            GROUP BY A.AlarmCode ,AlarmDescribe ";
                sql += " union all ";
            }
            sql = sql.Substring(0, sql.Length - 11);
            var createSql = $"CREATE OR REPLACE VIEW AS {sql}";
            CurrDb.ExecuteBySql(createSql);
            sql = "select * from " + viewName;
            var dtos = CurrDb.FindList<MacStatusForAnalysis>(sql).OrderByDescending(c => c.AlarmCount);
            createSql = "DROP VIEW IF EXISTS " + viewName;
            CurrDb.ExecuteBySql(createSql);
            return dtos;
        }
        /// <summary>
        /// 稼动详情饼图数据
        /// </summary>
        /// <param name="maccode"></param>
        /// <returns></returns>
        public IEnumerable<RunRateDto> GetRunRatesDetail(string maccode, string startTime, string endTime)
        {
            string errorinfo = string.Empty;
            var standards = CurrDb.FindListForCondition<StandardStatus>(string.Empty, ref errorinfo);
            var disconn = standards.FirstOrDefault(c => c.FCode.ToUpper() == "DISCONN");
            var idle = standards.FirstOrDefault(c => c.FCode.ToUpper() == "IDLE");
            string st = startTime;
            string endtt = endTime;
            var createSql = string.Empty;
            var statusNames = this._getTableNames("macstatus", Convert.ToDateTime(st), Convert.ToDateTime(endtt));
            var viewName = $"V_Status_Mac_{DateTime.Now.Ticks}";
            createSql += $"CREATE OR REPLACE VIEW {viewName} AS ";
            for (int i = 0; i < statusNames.Count(); i++)
            {
                createSql += $@"SELECT
                                SUM(FLen) AS value,StatusID,B.FCode AS Code,B.FName as Name
                            FROM
                                {statusNames.ElementAt(i)} as A
                                left join standardstatus as B on A.StatusID = B.StatusVal
                            WHERE
                                A.MacCode = '{maccode}'
                                AND A.STime >= '{startTime}'
                                AND A.ETime < '{endTime}'
                                AND (A.Alarmcode is null or a.alarmcode != 'SVID')
                                GROUP BY StatusID,B.FName,B.FCode ";
                createSql += " union all ";
            }
            createSql = createSql.Substring(0, createSql.Length - 11);
            CurrDb.ExecuteBySql(createSql);

            var sql = $"select sum(value) value,statusID,Code,Name from {viewName} " +
                $"group by statusID,Code,Name";

            IList<RunRateDto> runRateDtos = CurrDb.FindList<RunRateDto>(sql).ToList();

            var dropSql = $"DROP VIEW IF EXISTS {viewName}";
            CurrDb.ExecuteBySql(dropSql);

            Parallel.ForEach(runRateDtos, item =>
            {
                item.Value = item.Value / 60;
            });
            // 获取小于StartTime的最后一条状态，如果该状态结束时间大于StartTime,
            // 将该状态的结束时间-StartTime的时长计入对应状态
            var statusSql = new MacStatus().GetSelectSql();
            var first = CurrDb.FindList<MacStatus>($"{statusSql.Replace("MacStatus", statusNames.First())} where 1=1 and a.maccode='{maccode}' and a.stime<'{startTime}'" +
                $" order by a.id desc limit 0,1")
                .FirstOrDefault();
            if (first != null && first.ETime > Convert.ToDateTime(startTime))
            {
                if (first.StatusID == 6 && first.AlarmCode == "SVID")
                {
                    first.StatusID = 3;
                    first.FCode = idle.FCode;
                    first.StatusFName = idle.FName;
                }
                if (runRateDtos.Any(t => t.Code == first.FCode))
                {
                    runRateDtos.FirstOrDefault(c => c.Code == first.FCode).Value += (first.ETime - Convert.ToDateTime(startTime)).TotalMinutes;
                }
                else
                {
                    runRateDtos.Add(new RunRateDto
                    {
                        Code = first.FCode,
                        Name = first.StatusFName,
                        Value = (first.ETime - Convert.ToDateTime(startTime)).TotalMinutes,
                    });
                }
            }
            var last = CurrDb.FindList<MacStatus>($" {statusSql.Replace("MacStatus", statusNames.Last())} where 1=1 and a.maccode='{maccode}' and a.stime<='{endTime}'" +
                $"and a.stime>='{startTime}' " +
                $" order by a.id desc limit 0,1").FirstOrDefault();
            if (last != null && (last.ETime.ToString("yyyy-MM-dd HH:mm:ss") == "0001-01-01 00:00:00"
                || last.ETime > Convert.ToDateTime(endTime)))
            {
                if (last.StatusID == 6 && last.AlarmCode == "SVID")
                {
                    last.StatusID = 3;
                    last.FCode = idle.FCode;
                    last.StatusFName = idle.FName;
                }
                if (runRateDtos.Any(t => t.Code == last.FCode))
                {
                    runRateDtos.FirstOrDefault(c => c.Code == last.FCode).Value += (Convert.ToDateTime(endTime) - last.STime).TotalMinutes;
                }
                else
                {
                    runRateDtos.Add(new RunRateDto
                    {
                        Code = last.FCode,
                        Name = last.StatusFName,
                        Value = Math.Round((Convert.ToDateTime(endTime) - last.STime).TotalMinutes, 2)
                    });
                }
            }
            if (first != null && first.ETime.ToString("yyyy-MM-dd HH:mm:ss") == "0001-01-01 00:00:00" && runRateDtos.Count == 0)
            {
                //if (first.FCode != idle.FCode)
                //{
                runRateDtos.Add(new RunRateDto
                {
                    Code = first.FCode,
                    Name = first.StatusFName,
                    Value = Math.Round((Convert.ToDateTime(endTime) - Convert.ToDateTime(startTime)).TotalMinutes, 2),
                    // Color = new RgbColor(disconn.Red, disconn.Green, disconn.Blue)
                });
                // }
                //else
                //{
                //    runRateDtos.FirstOrDefault().Value += Math.Round((Convert.ToDateTime(endTime) - Convert.ToDateTime(startTime)).TotalMinutes, 2);
                //}
            }
            var totalMinutes = runRateDtos.Sum(c => c.Value);
            var dayTimeSpan = (Convert.ToDateTime(endtt) - Convert.ToDateTime(st)).TotalMinutes;
            if (totalMinutes != dayTimeSpan)
            {
                // 当总时间与状态的时间和不等时，判断该时间段内状态，如果状态只有一个，
                // 将差值加到该状态下,如果有多个，优先加到稼动时间上，其次闲置，再次时间最长的状态
                if (runRateDtos.Count == 1)
                {
                    runRateDtos.First().Value += dayTimeSpan - totalMinutes;
                }
                else
                {
                    var totalRunTime = runRateDtos.Where(c => c.Code == "RUN").Sum(c => c.Value);
                    var totalIdleTime = runRateDtos.Where(c => c.Code == "IDLE").Sum(c => c.Value);

                    if (runRateDtos.FirstOrDefault(c => c.Code == "IDLE") != null && totalIdleTime > 0)
                    {
                        runRateDtos.FirstOrDefault(c => c.Code == "IDLE").Value += (dayTimeSpan - totalMinutes);
                    }
                    else if (runRateDtos.FirstOrDefault(c => c.Code == "RUN") != null && totalRunTime > 0)
                    {
                        runRateDtos.FirstOrDefault(c => c.Code == "RUN").Value += (dayTimeSpan - totalMinutes);
                    }
                    else
                    {
                        var maxItem = runRateDtos.FirstOrDefault(t => t.Value == runRateDtos.Max(c => c.Value));
                        if (maxItem != null)
                        {
                            maxItem.Value += (dayTimeSpan - totalMinutes);
                        }
                    }
                }
                //if (runRateDtos.FirstOrDefault(c => c.Code == "IDLE") == null)
                //{
                //    runRateDtos.Add(new RunRateDto
                //    {
                //        Code = "IDLE",
                //        Color = new RgbColor(idle.Red, idle.Green, idle.Blue),
                //        Name = idle.FName,
                //        Value = Math.Round(dayTimeSpan - totalMinutes, 2)
                //    });
                //}
                //else
                //{
                //    runRateDtos.FirstOrDefault(c => c.Code == idle.FCode).Value += Math.Round(dayTimeSpan - totalMinutes, 2);
                //}
            }
            // var next = CurrDb.FindListForCondition<MacStatus>($" and a.maccode='{maccode}' and a.eTime>''")
            if (runRateDtos.Count() == 0)
            {
                //return new List<RunRateDto>()
                //{
                //    new RunRateDto()
                //    {
                //        Code=disconn.FCode,
                //        Name=disconn.FName,
                //        Value=Math.Round((Convert.ToDateTime(endTime)-Convert.ToDateTime(startTime)).TotalMinutes,2),
                //        Color=new RgbColor(disconn.Red,disconn.Green,disconn.Blue)
                //    }
                //};
                return null;
            }
            else
            {
                Parallel.ForEach<RunRateDto>(runRateDtos, item =>
                {
                    var stand = standards.FirstOrDefault(c => c.FCode.ToUpper() == item.Code.ToUpper());
                    // if(stand.FCode=="")
                    item.Color = new RgbColor(stand.Red, stand.Green, stand.Blue);
                    item.Value = Math.Round(item.Value, 2);
                });
                runRateDtos = runRateDtos.Where(c => c.Value > 0).ToList();
                return runRateDtos;
            }
        }
        /// <summary>
        /// 获取机种产量详情白夜班信息
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public IEnumerable<DayCount> GetDetailForCountDay(string maccode, string startTime)
        {
            string errorinfo = string.Empty;
            DateTime st = Convert.ToDateTime(Convert.ToDateTime(startTime).ToString("yyyy-MM-dd") + " 00:30");
            DateTime yestertodayShiftStart = st.AddHours(-16);//昨天白班开始
            DateTime yestertodayShiftEnd = st.AddHours(-4);//昨天白班结束
            DateTime yestertodayNightStart = yestertodayShiftEnd;//昨天夜班开始
            DateTime yestertodayNightEnd = st.AddHours(8);//昨天夜班结束
            DateTime TodayShiftStart = yestertodayNightEnd;//今天白班开始
            DateTime TodayShiftEnd = TodayShiftStart.AddHours(12);//今天白班结束
            DateTime TodayNightStart = TodayShiftEnd;//今天夜班开始
            DateTime TodayNightEnd = TodayNightStart.AddHours(12);//今天夜班结束
            string FinishUnit = DllHsms.StandardCode.SVID_FinishUnit;
            string toDay = st.ToString("yyyy-MM-dd");
            string ysday = st.AddDays(-1).ToString("yyyy-MM-dd");
            IEnumerable<DayCount> dtos = null;
            #region
            if (yestertodayNightStart < DateTime.Now.AddDays(-30))
            {
                var createSql = string.Empty;
                var tbDetailsNames = this._getTableNames("maccountdetail", st, st.AddDays(1));
                var tbMstNames = this._getTableNames("maccountmst", st, st.AddDays(1));
                var viewName = $"V_Output_Day_{DateTime.Now.Ticks}";
                createSql += $"CREATE OR REPLACE VIEW {viewName} AS ";
                for (int i = 0; i < tbMstNames.Count(); i++)
                {
                    createSql += $@"SELECT
                             IFNULL(sum( a.FCount ),0) Count,
                             '{ysday}' DAY,
                             '{yestertodayShiftStart}' DayDate,
                             '白班' class 
                            FROM
                             {tbDetailsNames.ElementAt(i)} a
                             LEFT JOIN {tbMstNames.ElementAt(i)} b ON a.mstid = b.id
                             LEFT JOIN machine c ON b.macid = c.id 
                            WHERE
                             a.typeID = 0 
                             AND a.ParamCode = '{FinishUnit}' 
                             AND c.FCode = '{maccode}'
                             AND b.EDate BETWEEN '{yestertodayShiftStart.ToString(timeFormat)}' 
                             AND '{yestertodayShiftEnd.ToString(timeFormat)}' 
                            UNION ALL
                            SELECT
                             IFNULL(sum( a.FCount ),0) Count,
                             '{ysday}' DAY,
                             '{yestertodayNightStart}' DayDate,
                             '夜班' class 
                            FROM
                             {tbDetailsNames.ElementAt(i)} a
                             LEFT JOIN {tbMstNames.ElementAt(i)} b ON a.mstid = b.id
                             LEFT JOIN machine c ON b.macid = c.id 
                            WHERE
                             a.typeID = 0 
                             AND a.ParamCode = '{FinishUnit}'  
                             AND c.FCode =  '{maccode}'
                             AND b.EDate BETWEEN '{yestertodayNightStart.ToString(timeFormat) }'
                             AND '{yestertodayNightEnd.ToString(timeFormat)}' UNION ALL
                            SELECT
                             IFNULL(sum( a.FCount ),0) Count,
                             '{toDay}' DAY,
                             '{TodayShiftStart}' DayDate,
                             '白班' class 
                            FROM
                              {tbDetailsNames.ElementAt(i)} a
                             LEFT JOIN {tbMstNames.ElementAt(i)} b ON a.mstid = b.id
                             LEFT JOIN machine c ON b.macid = c.id 
                            WHERE
                             a.typeID = 0 
                             AND a.ParamCode = '{FinishUnit}'  
                             AND c.FCode =  '{maccode}'
                             AND b.EDate BETWEEN '{TodayShiftStart.ToString(timeFormat) }'
                             AND '{TodayShiftEnd.ToString(timeFormat)}' UNION ALL
                            SELECT
                             IFNULL(sum( a.FCount ),0) Count,
                             '{toDay}' DAY,
                             '{TodayNightStart}' DayDate,
                            '夜班' class 
                            FROM
                              {tbDetailsNames.ElementAt(i)} a
                             LEFT JOIN {tbMstNames.ElementAt(i)} b ON a.mstid = b.id
                             LEFT JOIN machine c ON b.macid = c.id 
                            WHERE
                             a.typeID = 0 
                             AND a.ParamCode = '{FinishUnit}'  
                             AND c.FCode =  '{maccode}'
                             AND b.EDate BETWEEN '{TodayNightStart.ToString(timeFormat)}' 
                             AND '{TodayNightEnd.ToString(timeFormat)}' ";
                    createSql += " union all ";
                }
                createSql = createSql.Substring(0, createSql.Length - 11);
                CurrDb.ExecuteBySql(createSql);

                var sql = $"select sum(count) count,day,class,dayDate EDate from {viewName} " +
                    $"group by daydate,day,class";

                dtos = CurrDb.FindList<DayCount>(sql).OrderBy(c => c.EDate);

                var dropSql = $"DROP VIEW IF EXISTS {viewName}";
                CurrDb.ExecuteBySql(dropSql);
            }
            else
            {
                var sql = $@"SELECT
                             IFNULL(sum( a.FCount ),0) Count,
                             '{ysday}' DAY,
                             '{yestertodayShiftStart}' DayDate,
                             '白班' class 
                            FROM
                             maccountdetail a
                             LEFT JOIN maccountmst b ON a.mstid = b.id
                             LEFT JOIN machine c ON b.macid = c.id 
                            WHERE
                             a.typeID = 0 
                             AND a.ParamCode = '{FinishUnit}' 
                             AND c.FCode = '{maccode}'
                             AND b.EDate BETWEEN '{yestertodayShiftStart.ToString(timeFormat)}' 
                             AND '{yestertodayShiftEnd.ToString(timeFormat)}' 
                            UNION ALL
                            SELECT
                             IFNULL(sum( a.FCount ),0) Count,
                             '{ysday}' DAY,
                             '{yestertodayNightStart}' DayDate,
                             '夜班' class 
                            FROM
                             maccountdetail a
                             LEFT JOIN maccountmst b ON a.mstid = b.id
                             LEFT JOIN machine c ON b.macid = c.id 
                            WHERE
                             a.typeID = 0 
                             AND a.ParamCode = '{FinishUnit}'  
                             AND c.FCode =  '{maccode}'
                             AND b.EDate BETWEEN '{yestertodayNightStart.ToString(timeFormat) }'
                             AND '{yestertodayNightEnd.ToString(timeFormat)}' UNION ALL
                            SELECT
                             IFNULL(sum( a.FCount ),0) Count,
                             '{toDay}' DAY,
                             '{TodayShiftStart}' DayDate,
                             '白班' class 
                            FROM
                              maccountdetail a
                             LEFT JOIN maccountmst b ON a.mstid = b.id
                             LEFT JOIN machine c ON b.macid = c.id 
                            WHERE
                             a.typeID = 0 
                             AND a.ParamCode = '{FinishUnit}'  
                             AND c.FCode =  '{maccode}'
                             AND b.EDate BETWEEN '{TodayShiftStart.ToString(timeFormat) }'
                             AND '{TodayShiftEnd.ToString(timeFormat)}' UNION ALL
                            SELECT
                             IFNULL(sum( a.FCount ),0) Count,
                             '{toDay}' DAY,
                             '{TodayNightStart}' DayDate,
                            '夜班' class 
                            FROM
                              maccountdetail a
                             LEFT JOIN maccountmst b ON a.mstid = b.id
                             LEFT JOIN machine c ON b.macid = c.id 
                            WHERE
                             a.typeID = 0 
                             AND a.ParamCode = '{FinishUnit}'  
                             AND c.FCode =  '{maccode}'
                             AND b.EDate BETWEEN '{TodayNightStart.ToString(timeFormat)}' 
                             AND '{TodayNightEnd.ToString(timeFormat)}' ";
                dtos = CurrDb.FindList<DayCount>(sql).OrderBy(c => c.EDate);
            }

            #endregion

            return dtos;
        }
        /// <summary>
        /// 获取机种产量详情信息
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="starttime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public IEnumerable<HourCount> GetDetailForCountHour(string maccode, string startTime)
        {
            var sql = string.Empty;
            var dateStart = Convert.ToDateTime(startTime);
            DateTime st = Convert.ToDateTime(Convert.ToDateTime(startTime).ToString("yyyy-MM-dd HH:mm"));
            IList<HourCount> dtos = null;
            // 当月数据
            if ((DateTime.Now - dateStart).TotalDays < 30)
            {
                sql = $@"(SELECT 
						IFNULL( sum( a.FCount ),0) Count, DAY(b.edate) DAY,HOUR(b.EDate)  Hour
						FROM
							maccountdetail a
							LEFT JOIN maccountmst b ON a.mstid = b.id
							LEFT JOIN eap.machine c ON b.macid = c.id 
						WHERE
							a.typeID = 0 
							AND a.ParamCode = 'S00026' 
							AND c.FCode = '{maccode}' 
							AND b.EDate BETWEEN '{st.ToString(timeFormat)}' 
							AND '{st.AddHours(24).ToString(timeFormat)}'
                            GROUP BY DAY(b.Edate),HOUR(b.EDate))";
                dtos = CurrDb.FindList<HourCount>(sql).ToList();
            }
            else  // 历史数据
            {
                var countMsts = this._getTableNames("maccountmst", st, st.AddDays(1));
                var countDetails = this._getTableNames("maccountdetail", st, st.AddDays(1));
                var viewName = $"V_Output_{DateTime.Now.Ticks}";
                var createSql = $"CREATE TEMPORARY TABLE {viewName} ";
                for (int i = 0; i < countMsts.Count(); i++)
                {
                    createSql += $@" (SELECT 
						IFNULL( sum( a.FCount ),0) Count, DAY(b.edate) DAY,HOUR(b.EDate)  Hour
						FROM
							{countDetails.ElementAt(i)} a
							LEFT JOIN {countMsts.ElementAt(i)} b ON a.mstid = b.id
							LEFT JOIN eap.machine c ON b.macid = c.id 
						WHERE
							a.typeID = 0 
							AND a.ParamCode = 'S00026' 
							AND c.FCode = '{maccode}' 
							AND b.EDate BETWEEN '{st.ToString(timeFormat)}' 
							AND '{st.AddHours(24).ToString(timeFormat)}'
                            GROUP BY DAY(b.Edate),HOUR(b.EDate))";
                    createSql += " union all ";
                }
                createSql = createSql.Substring(0, createSql.Length - 11);

                CurrDb.ExecuteBySql(createSql);

                sql = $"select sum(Count) count,DAY,HOUR from {viewName} group by DAY,HOUR ";

                dtos = CurrDb.FindList<HourCount>(sql).ToList();

                sql = $"DROP TABLE IF EXISTS {viewName}";

                CurrDb.ExecuteBySql(sql);
            }

            var hours = 24;
            if (st.ToString("yyyy-MM-dd") == DateTime.Now.ToString("yyyy-MM-dd"))
            {
                hours = Convert.ToInt32(Math.Ceiling((DateTime.Now.AddMinutes(30) - st).TotalHours));
            }
            var newDtos = new List<HourCount>();
            for (int i = 0; i < hours; i++)
            {
                var hour = st.AddHours(i).Hour;
                var day = st.AddHours(i).Day;
                if (!dtos.Any(c => c.Hour == hour && c.Day == day))
                {
                    newDtos.Add(new HourCount
                    {
                        Hour = hour,
                        Day = day,
                        Count = "0"
                    });
                }
                else
                {
                    newDtos.Add(new HourCount
                    {
                        Hour = hour,
                        Day = day,
                        Count = dtos.FirstOrDefault(c => c.Day == day && c.Hour == hour).Count
                    });
                }
            }
            return newDtos;
        }

        /// <summary>
        /// 获取机台时间段内运行历史
        /// </summary>
        /// <param name="macCode"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<RunHistroyDto> GetRunHistory(string macCode, DateTime startTime, DateTime endTime, ref string errorinfo)
        {
            var standards = CurrDb.FindListForCondition<StandardStatus>(string.Empty, ref errorinfo);
            var disconn = standards.FirstOrDefault(t => t.FCode.ToUpper() == "DISCONN");
            var idle = standards.FirstOrDefault(t => t.FCode == "IDLE");
            var dateFormatter = "yyyy-MM-dd H:mm:ss";
            var runHistories = new List<RunHistroyDto>();
            var sql = new MacStatus().GetSelectSql();
            var tbNames = this._getTableNames("MacStatus", startTime, startTime.AddDays(1));
            var statuses = new List<MacStatus>();
            foreach (var item in tbNames)
            {
                var tempSql = sql.Replace("MacStatus", item);
                tempSql += $" where 1=1 and a.macCode='{macCode}' " +
                $"and a.stime>='{startTime.ToString(timeFormat)}' and " +
                $"a.stime<='{endTime.ToString(timeFormat)}' and a.etime<>'0001-01-01 00:00:00'";
                var partStatuses = CurrDb.FindList<MacStatus>(tempSql).ToList();
                statuses.AddRange(partStatuses);
            }

            //var statuses = CurrDb.FindListForCondition<MacStatus>($" and a.macCode='{macCode}' " +
            //    $"and a.stime>='{startTime.ToString(timeFormat)}' and " +
            //    $"a.stime<='{endTime.ToString(timeFormat)}' and a.etime<>'0001-01-01 00:00:00' ", ref errorinfo)
            //    .ToList();

            //var lastStatus = CurrDb.FindListForCondition<MacStatus>($" and a.macCode='{macCode}' and a.stime<'{startTime.ToString(timeFormat)}' " +
            //       $"order by id desc limit 0,1", ref errorinfo).FirstOrDefault();
            sql = sql.Replace("MacStatus", tbNames.Last());
            sql += $" where a.macCode='{macCode}' and a.stime<'{startTime.ToString(timeFormat)}' " +
                  $"order by id desc limit 0,1";
            var lastStatus = CurrDb.FindList<MacStatus>(sql).FirstOrDefault();
            if (lastStatus != null)
            {
                statuses.Insert(0, lastStatus);
            }
            var currState = CurrDb.FindListForCondition<MacStatus>($" and a.macCode='{macCode}' and a.etime='0001-01-01 00:00:00' order by a.stime desc limit 0,1",
                ref errorinfo).FirstOrDefault();
            if (currState != null)
            {
                statuses.Add(currState);
            }
            // 当前时间段内没数据，查询开始时间的前的最后一条数据
            if (statuses == null || statuses.Count() <= 0)
            {
                var status = disconn.FCode.ToUpper();
                var statusName = disconn.FName;
                var timeLen = (endTime - startTime).TotalMinutes;
                var history = new RunHistroyDto
                {
                    StartTime = startTime.ToString(dateFormatter),
                    EndTime = endTime.ToString(dateFormatter),
                    Start = startTime.Hour * 100 + startTime.Minute,
                    End = endTime.Hour * 100 + endTime.Minute,
                    MacCode = macCode.ToUpper(),
                    Status = status,
                    StatusName = statusName,
                    TimeLen = timeLen,
                    Duration = AppendDuration(endTime - startTime),
                    Color = new RgbColor(disconn.Red, disconn.Green, disconn.Blue),
                    AlarmCode = string.Empty
                };
                runHistories.Add(history);
            }
            else
            {
                statuses = statuses.OrderBy(c => c.ID).ToList();
                foreach (var item in statuses)
                {
                    var end = Convert.ToDouble(endTime.Hour * 100 + endTime.Minute);
                    var endStr = endTime.ToString("HH:mm:ss");
                    var startStr = startTime.ToString("HH:mm:ss");
                    var dateStart = startTime.ToString(dateFormatter);
                    var dateEnd = endTime.ToString(dateFormatter);
                    var start = 0d;
                    var minutes = 0d;
                    var duration = string.Empty;
                    if (item.STime < startTime && item.ETime > endTime)
                    {
                        start = 0;
                        end = Convert.ToDouble(AppendTime(endTime.Hour, endTime.Minute, endTime.Second));
                        dateEnd = endTime.ToString(dateFormatter);
                        minutes = (endTime - startTime).TotalMinutes;
                        duration = AppendDuration(endTime - startTime);
                    }
                    if (item.ETime.ToString(timeFormat) != "0001-01-01 00:00:00")
                    {
                        end = Convert.ToDouble(AppendTime(item.ETime.Hour, item.ETime.Minute, item.ETime.Second));
                        endStr = item.ETime.ToString("HH:mm:ss");
                        dateEnd = item.ETime.ToString(dateFormatter);
                        minutes = (item.ETime - item.STime).TotalMinutes;
                        duration = AppendDuration(item.ETime - item.STime);
                        if (item.STime < startTime)
                        {
                            minutes = (item.ETime - startTime).TotalMinutes;
                            duration = AppendDuration(item.ETime - startTime);
                        }
                    }
                    else
                    {
                        minutes = (endTime - item.STime).TotalMinutes;
                        duration = AppendDuration(endTime - item.STime);
                        if (item.STime < startTime)
                        {
                            minutes = (endTime - startTime).TotalMinutes;
                            duration = AppendDuration(endTime - startTime);
                        }
                        item.ETime = endTime;
                    }
                    start = Convert.ToDouble(AppendTime(item.STime.Hour, item.STime.Minute, item.STime.Second));
                    startStr = item.STime.ToString("HH:mm:ss");
                    dateStart = item.STime.ToString(dateFormatter);
                    if (item.STime < startTime)
                    {
                        start = 0;
                        startStr = startTime.ToString("HH:mm:ss");
                        dateStart = startTime.ToString(dateFormatter);
                    }

                    // 获取列表最后一条记录，判断当前状态是否与其一样，一样就加上当前状态的持续时间
                    var lastHistory = runHistories.LastOrDefault();
                    if (item.StatusID == 6 && item.AlarmCode == "SVID")
                    {
                        item.StatusID = 3;
                        item.StatusFName = idle.FName;
                    }
                    var currStandard = standards.FirstOrDefault(c => c.StatusVal == item.StatusID);
                    if (lastHistory != null && lastHistory.StatusID == item.StatusID)
                    {
                        lastHistory.Duration = AppendDuration(item.ETime - Convert.ToDateTime(lastHistory.StartTime));
                        lastHistory.End = end;
                        lastHistory.TimeLen += minutes;
                        lastHistory.EndString = endStr;
                        lastHistory.EndTime = dateEnd;
                        lastHistory.AlarmCode = item.AlarmCode;
                    }
                    else
                    {
                        runHistories.Add(new RunHistroyDto
                        {
                            MacCode = macCode,
                            Start = start,
                            End = end,
                            Status = item.FCode,
                            StatusName = item.StatusFName,
                            TimeLen = minutes,
                            StartString = startStr,
                            EndString = endStr,
                            StartTime = dateStart,
                            EndTime = dateEnd,
                            Duration = duration,
                            StatusID = item.StatusID,
                            Color = new RgbColor(currStandard.Red, currStandard.Green, currStandard.Blue),
                            AlarmCode = item.AlarmCode
                        });
                    }
                }
            }

            return runHistories;
        }

        /// <summary>
        /// 根据日期获取表名
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="startTime"></param>
        /// <returns></returns>
        private IEnumerable<string> _getTableNames(string tableName, DateTime startTime, DateTime endTime)
        {
            var newNames = new List<string>();
            var now = DateTime.Now;
            if (now.AddMonths(-1) <= startTime)
            {
                newNames.Add(tableName);
                return newNames;
            }

            var months = ((endTime.Year - startTime.Year) * 12) + endTime.Month - startTime.Month + 1;

            if (months == 1)
            {
                if (endTime.Year == now.Year && endTime.Month == now.Month)
                {
                    newNames.Add(tableName);
                }
                else
                {
                    newNames.Add($"eap{endTime.Year}.{tableName}{endTime.ToString("MM")}");
                }
            }
            else
            {
                for (int i = 0; i < months; i++)
                {
                    var currDate = startTime.AddMonths(i);
                    if (currDate.Month == now.Month)
                    {
                        newNames.Add(tableName);
                    }
                    else
                    {
                        newNames.Add($"eap{currDate.Year}.{tableName}{currDate.ToString("MM")}");
                    }
                }
            }

            return newNames;
        }

        /// <summary>
        /// 拼接时间字符串
        /// </summary>
        /// <param name="hour">小时</param>
        /// <param name="minute">分钟</param>
        /// <returns></returns>
        private string AppendTime(int hour, int minute, int second)
        {
            var sb = new StringBuilder();
            if (hour > 0)
            {
                sb.Append(hour);
            }

            double minutes = minute + ((double)second / 60);
            if (minutes < 10)
            {
                sb.Append("0");
            }
            sb.Append(minutes.ToString("f2"));
            return sb.ToString();
        }

        private string AppendDuration(TimeSpan timeSpan)
        {
            var days = timeSpan.Days;
            var hours = 0;
            if (days > 0)
            {
                hours = days * 24;
            }
            hours += timeSpan.Hours;
            var hourPart = hours + "小时";
            var minutePart = timeSpan.Minutes + "分";
            var secondPart = timeSpan.Seconds + "秒";
            if (timeSpan.TotalSeconds < 60)
                return secondPart;
            else if (timeSpan.TotalSeconds < 3600)
            {
                return minutePart + secondPart;
            }
            else
            {
                return hourPart + minutePart + secondPart;
            }
        }

        /// <summary>
        /// 获取报警明细
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="filter"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <param name="errorinfo"></param>
        /// <returns></returns>
        public IEnumerable<MacStatus> GetAlarmDetail(int start, int length, string filter, string sort, string order, out int total, ref string errorinfo)
        {
            var sql = $"select count(1) from macstatus a where 1=1 and statusid={MacStatusVal.Error} " +
                $"and AlarmCode<>'SVID' " +
                $"{filter} ";
            total = Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
            return CurrDb.FindListForCondition<MacStatus>($" and a.statusid={MacStatusVal.Error} and AlarmCode<>'SVID' {filter}  " +
                $" order by {sort} {order} limit {start - 1},{length}", ref errorinfo);
        }

        //public IEnumerable<MacStatus> GetAlarmDetail(int start, int end, string filter, string sort, string order)
        //{
        //    string sql = string.Format($"SELECT * FROM (SELECT a.*,ROW_NUMBER() OVER(ORDER BY a.{sort} {order}) AS RowNum FROM macstatus AS a "
        //       + $"WHERE 1=1 {filter}) AS t WHERE "
        //       + $"t.RowNum>={start} AND t.RowNum<{end}");
        //        return CurrDb.FindList<MacStatus>(sql);

        //}
        public int GetCount(string filter)
        {
            string sql = string.Format($"SELECT COUNT(1) FROM macstatus a WHERE 1=1 {filter}");
            return Convert.ToInt32(CurrDb.FindList<string>(sql).FirstOrDefault() ?? "0");
        }

        /// <summary>
        /// 获取当天和前一天的白班及晚班报警数量
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="macCode"></param>
        /// <returns></returns>
        public IEnumerable<DayCount> GetAlarmCountByShift(DateTime startTime, string macCode)
        {
            string errorinfo = string.Empty;
            DateTime st = Convert.ToDateTime(Convert.ToDateTime(startTime).ToString("yyyy-MM-dd") + " 00:30");
            DateTime yestertodayShiftStart = st.AddHours(-16);//昨天白班开始
            DateTime yestertodayShiftEnd = st.AddHours(-4);//昨天白班结束
            DateTime yestertodayNightStart = yestertodayShiftEnd;//昨天夜班开始
            DateTime yestertodayNightEnd = st.AddHours(8);//昨天夜班结束
            DateTime TodayShiftStart = yestertodayNightEnd;//今天白班开始
            DateTime TodayShiftEnd = TodayShiftStart.AddHours(12);//今天白班结束
            DateTime TodayNightStart = TodayShiftEnd;//今天夜班开始
            DateTime TodayNightEnd = TodayNightStart.AddHours(12);//今天夜班结束
            string toDay = st.ToString("yyyy-MM-dd");
            string ysday = st.AddDays(-1).ToString("yyyy-MM-dd");
            #region

            var viewName = $"V_Mac_Alarm_Detail_{DateTime.Now.Ticks}";
            var statusNames = _getTableNames("macstatus", yestertodayShiftStart, TodayNightEnd);
            // string sql = $@" call GetDeatilForDayCount ('{maccode}','{DllHsms.StandardCode.SVID_FinishUnit}','{startTime}')";
            string sql = $@"select count(1) count,'{ysday}' day,'白班' class from macstatus a 
                            where a.statusid=6 {svidFilter} and a.maccode='{macCode}' and a.STime>'{yestertodayShiftStart.ToString(timeFormat)}' and a.sTime<='{yestertodayShiftEnd.ToString(timeFormat)}' 
                          
                            union all 
                            select count(1) count,'{ysday}' day,'夜班' class from macstatus a  
                            where a.statusid=6 {svidFilter} and a.maccode='{macCode}' and a.STime>'{yestertodayNightStart.ToString(timeFormat)}' and a.sTime<='{yestertodayNightEnd.ToString(timeFormat)}' 
                           
                            union all 
                            select count(1) count,'{toDay}' day,'白班' class from macstatus a 
                            where a.statusid=6 {svidFilter} and a.maccode='{macCode}' and a.STime>'{TodayShiftStart.ToString(timeFormat)}' and a.sTime<='{TodayShiftEnd.ToString(timeFormat)}' 
                           
                            union all 
                            select count(1) count,'{toDay}' day,'夜班' class from macstatus a 
                            where a.statusid=6 {svidFilter} and a.maccode='{macCode}' and a.STime>'{TodayNightStart.ToString(timeFormat)}' and a.sTime<='{TodayNightEnd.ToString(timeFormat)}' 
                            ";
            #endregion
            return CurrDb.FindList<DayCount>(sql);
        }

        public MacDto GetDetail(List<string> maccodes, string Time)
        {
            string errorinfo = string.Empty;
            string startTime = GetStartTime(Convert.ToDateTime(Time));
            string FinishUnit = DllHsms.StandardCode.SVID_FinishUnit;
            DateTime e = Convert.ToDateTime(Time);
            DateTime s = Convert.ToDateTime(startTime);
            double se = 1;
            se = e.Subtract(s).TotalMinutes;// 时间差 分钟


            StringBuilder sb = new StringBuilder();
            sb.Append(" ( ");
            for (int i = 0; i < maccodes.Count; i++)
            {
                //主要判断是否是最后一个条件
                if (maccodes.Count - 1 == i)
                {
                    sb.Append(" '" + maccodes[i] + "' ");
                }
                else
                {
                    sb.Append(" '" + maccodes[i] + "'" + " ,");
                }
            }
            sb.Append(" )");

            StringBuilder sbs = new StringBuilder();
            sbs.Append(" ( ");
            for (int i = 0; i < maccodes.Count; i++)
            {
                //主要判断是否是最后一个条件
                if (maccodes.Count - 1 == i)
                {
                    sbs.Append(" '" + maccodes[i].Replace("0", "") + "' ");
                }
                else
                {
                    sbs.Append(" '" + maccodes[i].Replace("0", "") + "'" + " ,");
                }
            }
            sbs.Append(" )");
            List<MacInfoShowLineDto> dtos = new List<MacInfoShowLineDto>();
            #region
            //产量
            string sql = $@" SELECT
                             IFNULL(sum( a.FCount ),0) Count,c.FCode MacCode
                            FROM
                             maccountdetail a
                             LEFT JOIN maccountmst b ON a.mstid = b.id
                             LEFT JOIN machine c ON b.macid = c.id 
                            WHERE
                             a.typeID = 0 
                             AND a.ParamCode = '{FinishUnit}' 
                             AND c.FCode in {sb.ToString()}
                             AND b.EDate BETWEEN '{startTime}' 
                             AND '{Time}'
	                         GROUP BY c.FCode 
                            ";
            var count = CurrDb.FindList<MacInfoShowLineDto>(sql);
            //稼动率,故障率,功能开启
            string sqlrate = $@"SELECT
	                            a.FCode MacCode,a.IsControl,f.PCode,c.FCode statusName,
                         {OfilmRecipeProvider.GenRecipeString("m")} recipe,
	                            IFNULL( ROUND( tt.RUN / 60 / {se} * 100, 1 ), 0 ) runRate,
	                            IFNULL( ROUND( tt.ERROR / 60 / {se} * 100, 1 ), 0 ) errorRate 
                            FROM
	                            Machine a
	                            LEFT JOIN (
	                            -- 	SELECT
-- 		a.MacCode,
-- 		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 ( 4, 6 ) 
-- 		AND a.RecTime > '2021-06-01 08:30' 
-- 		AND a.MacCode IN ( 'DA00027', 'WB00151', 'WB00152', 'WB00153', 'WB00154', 'WB00155', 'WB00156', 'LHA0029', 'AA00058' ) 
-- 	GROUP BY
-- 		a.MacCode 
		SELECT
		sum( t.RUN ) AS RUN,
		sum( t.ERROR ) AS ERROR,
		t.maccode  
	FROM
		(
		SELECT
			sum( CASE StatusID WHEN 4 THEN ifnull( FLen, 0 ) ELSE 0 END ) RUN,
			sum( CASE StatusID WHEN 6 THEN ifnull( FLen, 0 ) ELSE 0 END ) ERROR,
			maccode 
		FROM
			macstatus 
		WHERE
			stime > '{startTime}' 
			AND StatusID IN ( 4, 6 ) 
			AND MacCode IN {sb.ToString()} 
		GROUP BY
			maccode
			UNION ALL
		SELECT
			sum( CASE StatusID WHEN 4 THEN ifnull( time_to_sec(timediff('{Time}',stime)), 0 ) ELSE 0 END ) RUN,
			sum( CASE StatusID WHEN 6 THEN ifnull( time_to_sec(timediff('{Time}',stime)), 0 ) ELSE 0 END ) ERROR,
			maccode 
		FROM
			macstatus 
		WHERE
			StatusID IN ( 4, 6 ) 
			AND MacCode IN {sb.ToString()}  
			AND etime = '0001-01-01 00:00:00' 
			AND flen = 0 
			AND stime > '{startTime}' 
            AND stime <= '{Time}' 
			GROUP BY
			maccode
			UNION ALL
		SELECT
			sum( CASE StatusID WHEN 4 THEN ifnull( time_to_sec(timediff(etime, '{startTime}')), 0 ) ELSE 0 END ) RUN,
			sum( CASE StatusID WHEN 6 THEN ifnull( time_to_sec(timediff(etime, '{startTime}')), 0 ) ELSE 0 END ) ERROR,
			maccode 
		FROM
			macstatus 
		WHERE
			StatusID IN ( 4, 6 ) 
			AND MacCode IN {sb.ToString()}  
			AND stime <= '{startTime}' AND etime > '{startTime}' 
			GROUP BY
			maccode
		) t 
	GROUP BY
		t.maccode 
	                            ) tt ON tt.MacCode = a.FCode
	                            LEFT OUTER JOIN macstatus01 m ON a.fcode = m.maccode 
                                LEFT JOIN standardstatus c on m.StatusID=c.id
	                            LEFT OUTER JOIN macmodel d ON a.MModeID = d.id
	                            LEFT OUTER JOIN mactprocess f ON a.id = f.macid
                            WHERE
	                            1 = 1 
	                            AND a.FCode in {sb.ToString()}";
            var rate = CurrDb.FindList<MacInfoShowLineDto>(sqlrate);
            //报警top1、报警top2、报警top3
            string sqlalarmcode = $@"SELECT NAME,
	                                        description,mcacode,
	                                        sum( count ) AS AlarmCount 
                                        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 
			                                        AND AlarmCode != '' 
			                                        AND a.startTime >= '{startTime}' 
			                                        AND a.startTime <= '{Time}' 
		                                        GROUP BY
			                                        alarmcode,
			                                        alarmdesc,
			                                        mcacode 
		                                        ) t
		                                        LEFT JOIN Machine b ON t.mcacode = b.FCode 
	                                        WHERE
		                                        1 = 1 
		                                        AND b.FCode in {sb.ToString()}
	                                        ) aa 
                                        GROUP BY
	                                        NAME,
	                                        description ,mcacode
                                        ORDER BY
	                                        mcacode,sum( count ) DESC 
	                                       ";
            var alarm = CurrDb.FindList<MacAlarm>(sqlalarmcode);
            //MTBA
            string sqlmtba = $@"SELECT
	                                a.FCode AS MacCode,
	                                a.FName AS MacName,
	                                d.FCode AS ModelCode,
	                                me.EventCode,
	                                me.timelen AS TimeSpan,
	                                me.count AS Count 
                                FROM
	                                (
	                                SELECT
		                                sum( timespan ) AS timelen,
		                                count( 1 ) AS count,
		                                mcacode,
		                                eventcode
		
	                                FROM
		                                mcaeventstatisticbyday e 
	                                WHERE
		                                StartTime >= '{startTime}' 
		                                AND StartTime < '{Time}' 
		                                and McaCode IN {sb.ToString()}
	                                GROUP BY
		                                mcacode,
		                                eventcode
	                                ) me
	                                INNER JOIN machine a ON a.FCode = me.McaCode
	                                INNER JOIN macmodel d ON d.ID = a.MModeID
                                ORDER BY
	                                me.count DESC";
            var mtbaList = CurrDb.FindList<MacMTBAEntity>(sqlmtba);

            IEnumerable<MacInfoShowLineDto> shujian = null;
            IEnumerable<FixedPersonPost> fix = null;
            IEnumerable<MacInfoShowLineDto> falResult = null;
            using (IDatabase db = DbFactory.Base("ccmrpt"))
            {
                string sqlFal = $@"	SELECT
	                                to_char(CREATED,'yyyy-mm-dd hh24:mi:ss') AS FalTime,
	                                EQUIPMENT_ID AS MacCode 
                                FROM
	                                ccmrpt.CCMMES_IPQC_PROTUCT_TEST_V t 
                                WHERE
	                                CREATED > to_date( '{startTime}', 'yyyy-mm-dd hh24:mi:ss' ) 
                                    AND FIRST_TYPE='首件'
	                                AND EQUIPMENT_ID IN {sb.ToString()}  ";
                shujian = db.FindList<MacInfoShowLineDto>(sqlFal);

                string sqlFix = $@"SELECT
                                    max(	work_num) as WorkNm,max(Name) as Name,EQUIPMENT_ID as MacCode
                                    FROM
	                                    ccmrpt.EES_EQUIPMENT_OPER_LOG_v 
                                    WHERE EQUIPMENT_ID IN {sb.ToString()} 
	                                    GROUP BY EQUIPMENT_ID";
                fix = db.FindList<FixedPersonPost>(sqlFix);
            }

            using (IDatabase db = DbFactory.Base("CCMQIS"))
            {
                string sqlFal = $@"	select inspection_conclusion as FalResult,equipment_no as MacCode from ofg_qis.MFG_INSPECTION_REPORT_VIEW
                                    where CREATED_TIME > '{startTime}' 
                                    
	                                 ";
                //AND equipment_no IN { sbs.ToString()}
                falResult = db.FindList<MacInfoShowLineDto>(sqlFal);
            }
            dtos = count.ToList();
            foreach (MacInfoShowLineDto item in dtos)
            {
                item.IsControl = rate.Where(l => l.MacCode == item.MacCode).Select(c => c.IsControl).FirstOrDefault();
                item.RunRate = rate.Where(l => l.MacCode == item.MacCode).Select(c => c.RunRate).FirstOrDefault();
                item.ErrorRate = rate.Where(l => l.MacCode == item.MacCode).Select(c => c.ErrorRate).FirstOrDefault();
                item.Recipe = rate.Where(l => l.MacCode == item.MacCode).Select(c => c.Recipe).FirstOrDefault();
                item.PCode = rate.Where(l => l.MacCode == item.MacCode).Select(c => c.PCode).FirstOrDefault();
                item.StatusName = rate.Where(l => l.MacCode == item.MacCode).Select(c => c.StatusName).FirstOrDefault();
                item.MacAlarms = alarm.Where(l => l.Mcacode == item.MacCode).Take(3).ToList();
                item.FalTime = shujian.Where(l => l.MacCode == item.MacCode).Select(c => c.FalTime).FirstOrDefault();
                item.FalResult = falResult != null && falResult.Any() ? falResult.Where(l => l.MacCode == item.MacCode.Replace("0", "")).Select(c => c.FalResult).FirstOrDefault() : "No";
                item.FixedPersonPosts = fix.Where(l => l.MacCode == item.MacCode).FirstOrDefault();
                //MTBA
                var macMtbaList = mtbaList.Where(m => m.MacCode == item.MacCode).ToList();
                var runList = macMtbaList.Any() ? macMtbaList.Where(m => m.EventCode == "C00012").ToList() : null;
                var alarmList = macMtbaList.Any() ? macMtbaList.Where(m => m.EventCode == "C00007").ToList() : null;
                var runTimeSpan = runList != null && runList.Any() ? runList.Sum(m => m.TimeSpan) : 0;
                var alarmCount = alarmList != null && alarmList.Any() ? alarmList.Sum(m => m.Count) : 1;
                item.MTBA = (runTimeSpan / alarmCount / 60).ToString("f2");
            }
            List<MacInfoShowLineDto> dtoss = new List<MacInfoShowLineDto>();
            dtoss.AddRange(dtos.Where(l => l.MacCode.StartsWith("DA")));
            dtoss.AddRange(dtos.Where(l => l.MacCode.StartsWith("WB")));
            dtoss.AddRange(dtos.Where(l => l.MacCode.StartsWith("LHA")));
            dtoss.AddRange(dtos.Where(l => l.MacCode.StartsWith("AA")));
            MacDto macDto = new MacDto();
            macDto.macInfoShowLineDtos = dtoss;
            macDto.TotalCount = dtoss.Where(l => l.MacCode.StartsWith("DA")).Sum(c => c.Count);
            macDto.OutCount = dtoss.Where(l => l.MacCode.StartsWith("AA")).Sum(c => c.Count);
            macDto.Recipe = dtoss.Select(c => c.Recipe).FirstOrDefault();
            #endregion

            return macDto;
        }
        /// <summary>
        /// 根据当前时间获取班别开始时间
        /// </summary>
        /// <param name="now"></param>
        /// <returns></returns>
        private static string GetStartTime(DateTime now)
        {
            try
            {
                //DateTime now = DateTime.Now;
                string starttime = "";
                //00:00 08:30  20:30 23:59 
                DateTime nowstr = Convert.ToDateTime(now.ToString("HH:mm"));
                DateTime dt1 = Convert.ToDateTime("00:00");
                DateTime dt2 = Convert.ToDateTime("08:30");
                DateTime dt3 = Convert.ToDateTime("20:30");
                DateTime dt4 = Convert.ToDateTime("23:59");
                if (DateTime.Compare(nowstr, dt1) > 0 && DateTime.Compare(nowstr, dt2) < 0)
                {
                    starttime = now.AddDays(-1).ToString("yyyy-MM-dd") + " 20:30";
                }
                else if (DateTime.Compare(nowstr, dt2) > 0 && DateTime.Compare(nowstr, dt3) < 0)
                {
                    starttime = now.ToString("yyyy-MM-dd") + " 08:30";
                }
                else if (DateTime.Compare(nowstr, dt3) > 0 && DateTime.Compare(nowstr, dt4) < 0)
                {
                    starttime = now.ToString("yyyy-MM-dd") + " 20:30";
                }
                return starttime;
            }
            catch (Exception xe)
            {
                return "";
            }
        }

        /// <summary>
        /// 获取每个机台的最后一个
        /// </summary>
        /// <returns></returns>
        public async Task StoreLastStatus()
        {

        }

        /// <summary>
        /// 删除查询时创建的临时视图
        /// </summary>
        /// <returns></returns>
        public async Task ClearTempViews()
        {
            using (IDatabase db = DbFactory.Base("eapslave"))
            {
                var viewPrefixs = new string[] {
                "v_temp_last_status_",
                "V_MacStatus_Detail_",
                "v_temp_alarm_",
                "V_Status_Mac_",
                "V_Output_Day_"
                };
                var ticks = DateTime.Now.AddMinutes(-5).Ticks;
                foreach (var item in viewPrefixs)
                {
                    var sql = $@"select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where TABLE_NAME like 
                            '%{item}%' and TABLE_SCHEMA='eap' ";
                    var views = await db.FindListAsync<string>(sql, null);
                    if (views != null && views.Count() > 0)
                    {
                        foreach (var viewName in views)
                        {
                            var viewTicks = Convert.ToInt64(viewName.Split('_').Last());
                            if (viewTicks < ticks)
                            {
                                var drop = $"DROP VIEW IF EXISTS {viewName};";
                                db.ExecuteBySql(drop);
                            }
                        }
                    }
                }
            }
        }
    }
}