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

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

namespace DllEapDal.OFILM
{
    /// <summary>
	/// 每小时汇总一次产量稼动率等指标
	/// </summary>
    public class MacOutPutTimeDal
    {
		private ILogger myloger = AppConfigurtaionServices.MyLog;
		/// <summary>
		/// 逻辑处理
		/// </summary>
		public void SetData()
        {
		
			var time = DateTime.Now;
            string start = time.AddHours(-1).ToString("yyyy-MM-dd HH:00:00");
            string end = time.ToString("yyyy-MM-dd HH:00:00");
			myloger.LogError($"{start}产量计算定时任务开始");
			string sql = GetSql(start, end);
			var data = GetData(sql).ToList();
            if (data!=null)
            {
				int count = Add(data);
				if (count > 0)
				{
					myloger.LogError($"{start}产量计算定时任务成功");
				}
				else
				{
					myloger.LogError($"{start}产量计算定时任务失败");
				}

			}
           
			myloger.LogError($"{start}产量计算定时任务结束");
		}
		/// <summary>
		/// 插入指标数据
		/// </summary>
		/// <param name="data"></param>
		/// <returns></returns>
		private int  Add(IEnumerable<OutPutTime> data)
        {
            using(IDatabase db = DbFactory.Base("eap"))
            {
                foreach (var item in data)
                {
                    if (item.Day==null)
                    {
						item.Day = DateTime.Now.ToString("yyyy-MM-dd");
						item.Hour =  DateTime.Now.AddHours(-1).ToString("HH");
                    }
                    else
                    {
						item.Day = DateTime.Parse(item.Day).ToString("yyyy-MM-dd");
					}
				}
				return db.InsertFor<OutPutTime>(data, "定时提交");
            }
        }
		/// <summary>
		/// 获取指标数据
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		private IEnumerable<OutPutTime> GetData(string sql)
        {
			using (IDatabase db = DbFactory.Base("eapslave"))
			{

				var data = db.FindList<OutPutTime>(sql);
				return data;
			}
		}
		/// <summary>
		/// 拼接sql语句
		/// </summary>
		/// <param name="start"></param>
		/// <param name="end"></param>
		/// <returns></returns>
		private string GetSql(string start, string end)
        {
            string count = $@"SELECT IFNULL( sum( a.FCount ), 0 ) Count,IFNULL( sum( a.FCount ), 0 ) IntCount,DATE( b.EDate ) date,HOUR ( b.EDate ) HOUR,b.MacID MacId ,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 = 'S00026' AND b.EDate>= '{start}' AND b.EDate<'{end}' 
                              GROUP BY b.MacID,c.FCode,DATE( b.EDate ),HOUR ( b.EDate ) ";
            string t3 = $@"SELECT IFNULL( sum( IntCount ), 0 ) IntCount,IFNULL( sum( TimeLen ), 0 ) TimeLen,maccode MacCode,StatusID 
		                   FROM
	                       (
			                SELECT IFNULL( count( 1 ), 0 ) IntCount,TIME_TO_SEC(timediff( t.ETime, '{start}' )) TimeLen,maccode MacCode,StatusID 
			                FROM macstatus t 
			                WHERE
				            1 = 1 AND t.stime < '{start}' AND t.etime > '{start}' AND StatusID = 3 
			                GROUP BY maccode,StatusID,t.ETime 
                            UNION ALL
			                SELECT IFNULL( count( 1 ), 0 ) IntCount,SUM( FLen ) TimeLen,maccode MacCode,StatusID 
			                FROM
				            macstatus t 
			                WHERE
				            1 = 1 AND t.stime >= '{start}' AND t.etime <= '{end}' AND StatusID = 3 
			                GROUP BY maccode,StatusID,t.sTime 
                            UNION ALL
			                SELECT IFNULL( count( 1 ), 0 ) IntCount,TIME_TO_SEC(timediff( '{end}', t.sTime )) TimeLen,maccode MacCode,StatusID 
			                FROM
				            macstatus t 
			                WHERE
				            1 = 1 AND t.stime < '{end}' AND t.etime >'{end}' AND StatusID = 3 
			                GROUP BY maccode,StatusID,t.sTime 
			                ) temp3 
		                    GROUP BY maccode,StatusID ";
            string t4 = $@"SELECT IFNULL( sum( IntCount ), 0 ) IntCount,IFNULL( sum( TimeLen ), 0 ) TimeLen,maccode MacCode,StatusID 
		                    FROM
			                (
			                  SELECT IFNULL( count( 1 ), 0 ) IntCount,TIME_TO_SEC(timediff( t.ETime, '{start}' )) TimeLen,maccode MacCode,StatusID 
			                  FROM
				              macstatus t 
			                  WHERE
				              1 = 1  AND t.stime < '{start}' AND t.etime >'{start}' AND StatusID = 4 
			                 GROUP BY
				             maccode,StatusID,t.ETime 
                             UNION ALL
			                 SELECT IFNULL( count( 1 ), 0 ) IntCount,SUM( FLen ) TimeLen,maccode MacCode,StatusID 
			                 FROM
				             macstatus t 
			                 WHERE
				             1 = 1 AND t.stime >= '{start}' AND t.etime <= '{end}' AND StatusID = 4 
			                 GROUP BY
				             maccode,StatusID,t.sTime 
                             UNION ALL
			                 SELECT IFNULL( count( 1 ), 0 ) IntCount,TIME_TO_SEC(timediff( '{end}', t.sTime )) TimeLen,maccode MacCode,StatusID 
			                 FROM
				             macstatus t 
			                 WHERE
				             1 = 1 AND t.stime < '{end}' AND t.etime > '{end}' AND StatusID = 4 
			                 GROUP BY
				             maccode,StatusID,t.sTime 
			                 ) temp4 
		                   GROUP BY maccode,StatusID ";
            string t6 = $@"SELECT IFNULL( sum( IntCount ), 0 ) IntCount,IFNULL( sum( TimeLen ), 0 ) TimeLen,maccode MacCode,StatusID 
		                   FROM
			              (
			               SELECT IFNULL( count( 1 ), 0 ) IntCount,IF(TIME_TO_SEC(timediff( t.ETime, '{start}' ))= 0,0.01,TIME_TO_SEC(timediff( t.ETime, '{start}' ))) TimeLen,
				                   maccode MacCode,StatusID 
			               FROM
				           macstatus t 
			               WHERE 
                           1 = 1 AND t.stime <= '{start}' AND t.etime > '{start}' AND StatusID = 6 
			               GROUP BY maccode,StatusID,t.ETime
                           UNION ALL
			               SELECT IFNULL( count( 1 ), 0 ) IntCount,SUM(IF( flen = 0, 0.01, FLen )) TimeLen,maccode MacCode,StatusID 
			               FROM
				           macstatus t 
			               WHERE
				           1 = 1 AND t.stime >= '{start}' AND t.etime <= '{end}' AND StatusID = 6 
			               GROUP BY maccode,StatusID,t.sTime 
                           UNION ALL
                           SELECT IFNULL( count( 1 ), 0 ) IntCount,IF(TIME_TO_SEC(timediff( '{end}', t.sTime ))= 0,0.01,TIME_TO_SEC(timediff( '{end}', t.sTime ))) TimeLen,
				                  maccode MacCode,StatusID 
			               FROM macstatus t 
                           WHERE
				           1 = 1 AND t.stime < '{end}' AND t.etime >'{end}' AND StatusID = 6 
			               GROUP BY
				           maccode,StatusID,t.sTime 
		                	) temp6 
		                   GROUP BY
			              maccode,StatusID";
            return $@"SELECT aa.Id macid, count.maccode maccode,count.date DAY,count.HOUR HOUR,IFNULL( count.count, 0 ) count,IFNULL( t4.TimeLen, 0 ) RunTime,
	                  IFNULL( ROUND( t4.TimeLen / 3600, 4 ), 0 ) Runrate,IFNULL( t3.TimeLen, 0 ) StandbyTime,IFNULL( ROUND( t3.TimeLen / 3600, 4 ), 0 ) StandbyRate,
	                  IFNULL( t6.TimeLen, 0 ) AlarmTime,IFNULL( ROUND( t6.TimeLen / 3600, 4 ), 0 ) AlarmRate,IFNULL( t6.IntCount, 0 ) AlarmCount 
                      FROM
                      (
						machine  aa left join 
                      ({count}) count on count.macid=aa.id
		              LEFT JOIN ({t6}) t6 ON t6.maccode = count.maccode
                      LEFT JOIN ({t4}) t4 ON t4.maccode = count.maccode
		              LEFT JOIN ({t3}) t3 ON t3.maccode = count.maccode
                      ) ORDER BY maccode";
        }
    }
}