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

using Cksoft.Data;
using Cksoft.Data.Repository;
using Cksoft.Unity;
using DllEapEntity;
using DllEapEntity.Enums;
using DllEapEntity.OFILM;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DllEapDal.OFILM
{
    public class MacLastStatusDal
    {
        private readonly IDatabase CurrDb = null;

        public MacLastStatusDal()
        {

        }

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

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="db"></param>
        /// <returns></returns>
        public async Task Insert(MacLastStatus entity, IDatabase db = null)
        {
            if (db == null)
            {
                CurrDb.InsertFor(entity, string.Empty);
            }
            else
            {
                db.InsertFor(entity, string.Empty);
            }
            await Task.CompletedTask;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="shift"></param>
        /// <returns></returns>
        public async Task<IEnumerable<MacLastStatus>> GetLastStatus(DateTime date, EnumShift shift)
        {
            string errorinfo = string.Empty;
            return await CurrDb.FindListForConditionAsync<MacLastStatus>($" and a.fdate='{date.ToString("yyyy-MM-dd 00:00:00")}' " +
                $"and a.shift={(int)shift}",
                ref errorinfo);
        }

        /// <summary>
        /// 汇总机台最后状态
        /// </summary>
        /// <returns></returns>
        public async Task SyncLastStatus()
        {
            var logger = AppConfigurtaionServices.MyLog;
            try
            {
                logger.LogError("开始计算机台最后状态");
                var dateNow = DateTime.Now;
                string errorinfo = string.Empty;
                var (shift, date) = GetShift(dateNow);
                var dateStart = Convert.ToDateTime(dateNow.ToString("yyyy-MM-dd 08:30:00"));
                if (shift == EnumShift.Day)
                {
                    dateStart = Convert.ToDateTime(dateNow.ToString("yyyy-MM-dd 08:30:00"));
                }
                else if (shift == EnumShift.Night)
                {
                    dateStart = Convert.ToDateTime(dateNow.AddDays(-1).ToString("yyyy-MM-dd 20:30:00"));
                }
                var runRates = await GetRunRates(dateStart);  // 稼动率
                var machines = await GetAllMachines();
                using (IDatabase db = DbFactory.Base("eap"))
                {
                    db.BeginTrans();
                    // var 
                    var lastStatus = await db.FindListForConditionAsync<MacStatus01>(string.Empty, ref errorinfo);
                    foreach (var item in lastStatus)
                    {
                        var runTime = runRates.FirstOrDefault(c => c.Name == item.MacCode);
                        decimal runRate = 0;
                        if (runTime != null)
                        {
                            runRate = (decimal)runTime.Value / (12 * 60 * 60);
                        }
                        var tmp = new MacLastStatus
                        {
                            MacCode = item.MacCode,
                            Flen = Convert.ToInt32((dateNow - item.STime).TotalSeconds),
                            Shift = (int)shift,
                            StatusId = item.StatusID,
                            RecTime = Convert.ToDateTime(dateNow.ToString("yyyy-MM-dd HH:mm:ss")),
                            FDate = Convert.ToDateTime(date),
                            STime = item.STime,
                            RunRate = runRate,
                            Remark = item.Remark?.Replace(" ", "-").Replace("_", "-").Replace("|", "-")
                            .Replace(".", "-").Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries)
                            .FirstOrDefault() ,
                            FactoryId = machines.FirstOrDefault(c => c.FCode == item.MacCode)?.FactoryId,
                            RegionId = machines.FirstOrDefault(c => c.FCode == item.MacCode)?.RegionId
                        };
                        db.InsertFor(tmp, string.Empty);
                    }
                    db.Commit();
                }
                logger.LogError("计算机台最后状态完成");
            }
            catch (Exception ex)
            {
                logger.LogError("计算机台最后状态错误，" + ex.ToString());
                throw;
            }
        }

        /// <summary>
        /// 获取所有机台
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<Machine>> GetAllMachines()
        {
            using (IDatabase db = DbFactory.Base("eap"))
            {
                var sql = "select * from machine";
                return await db.FindListAsync<Machine>(sql, null);
            }
        }

        /// <summary>
        /// 获取机台稼动率
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<RunRateDto>> GetRunRates(DateTime dateStart)
        {
            var dateEnd = dateStart.AddHours(12);
            string timeFormat = "yyyy-MM-dd HH:mm:ss";
            using (IDatabase db = DbFactory.Base("eap"))
            {
                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 
                   group by tt.maccode";
                return await db.FindListAsync<RunRateDto>(sql, null);
            }
        }

        /// <summary>
        /// 获取班别
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        private (EnumShift, string) GetShift(DateTime date)
        {
            if (date.Hour > 9 && date.Hour < 21)
            {
                return (EnumShift.Day, date.ToString("yyyy-MM-dd"));
            }
            return (EnumShift.Night, date.AddDays(-1).ToString("yyyy-MM-dd"));
        }
    }
}