sys/Hh.Mes.Service/ApiService/DashboardService.cs

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using SqlSugar;
using Hh.Mes.Service.Repository;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.ApiEntity;
using Microsoft.AspNetCore.Mvc;
using NPOI.SS.Formula.Functions;
using System.Data.SqlTypes;
using System.Linq;
using Hh.Mes.Common.log;
using Hh.Mes.POJO.Response;
using Hh.Mes.Pojo.System;
namespace Hh.Mes.Service.ApiService
{
    public class DashboardService : RepositorySqlSugar<sys_user_online>
    {
        /// <summary>
        /// 故障报告
        /// </summary>
        /// <param name="yyyyMonth">日期</param>
        /// <param name="project">项目</param>
        /// <param name="equipmentType">设备类型</param>
        /// <returns></returns>
        public dynamic GetFaultReport(string yyyyMonth, string projectCode, string equipmentTypeCode)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var month = Convert.ToDateTime(yyyyMonth);
                var whereSql = @" where  t1.CreateTime>='{0}' and t1.CreateTime<='{1}' and t1.EquipmentTypeCode not in ('{2}')";
                //月初、月末
                var monthStart = month.AddDays(1 - month.Day);
                var monthEnd = month.AddDays(1 - month.Day).Date.AddMonths(1).AddSeconds(-1);
                var sqlWhereMonth = string.Format(whereSql, monthStart, monthEnd, SystemVariable.IotNotContainDevice);//月数据源查询条件
                //周一、周末
                var weekOne = month.AddDays(0 - (Convert.ToInt16(month.DayOfWeek) == 0 ? 7 : Convert.ToInt16(month.DayOfWeek)) + 1).ToString("yyyy-MM-dd 00:00:00");
                var weekend = month.AddDays(6 - (Convert.ToInt16(month.DayOfWeek) == 0 ? 7 : Convert.ToInt16(month.DayOfWeek)) + 1).ToString("yyyy-MM-dd 23:59:59");
                var sqlWhereWeek = string.Format(whereSql, weekOne, weekend, SystemVariable.IotNotContainDevice);
                var sql = GetSql(sqlWhereMonth, sqlWhereWeek, projectCode, equipmentTypeCode);
                var ds = Context.Ado.GetDataSetAll(sql);
                var diffDay = DateTime.DaysInMonth(month.Year, month.Month);
                var monthStr = monthStart.ToString("MM-dd") + "~" + monthEnd.ToString("MM-dd");
                var weekStr = DateTime.Parse(weekOne).ToString("MM-dd") + "~" + DateTime.Parse(weekend).ToString("MM-dd");
                Tuple<DataSet, int, string, string> dss = new Tuple<DataSet, int, string, string>(ds, diffDay, monthStr, weekStr);
                //当天是否在本周内
                var nowDay = DateTime.Now;
                var selectDay = Convert.ToDateTime(yyyyMonth);
                var startWeekDay = nowDay.AddDays(-(int)nowDay.DayOfWeek + 1).ToString("yyyy-MM-dd 00:00:00");//当前周的周一日期
                var weekOnes = selectDay.AddDays(0 - (Convert.ToInt16(selectDay.DayOfWeek) == 0 ? 7 : Convert.ToInt16(selectDay.DayOfWeek)) + 1).ToString("yyyy-MM-dd 00:00:00");//当前所选周的周一日期
                var isInterval = false;
                if (startWeekDay == weekOnes) isInterval = true;
                response.data = new
                {
                    listMonth = dss.Item1.Tables[0],
                    listWeek = dss.Item1.Tables[1],
                    listAlarmSum40 = dss.Item1.Tables[2],
                    diffDay = dss.Item2,
                    monthStr = dss.Item3,
                    weekStr = dss.Item4,
                    weekDefault = dss.Item1.Tables[3],
                    properlyTarget = dss.Item1.Tables[4],
                    factoryTarget = dss.Item1.Tables[5],
                    isInterval = isInterval
                };
                return response;
            });
        }
        public string GetSql(string sqlWhereMonth, string sqlWhereWeek, string projectCode, string equipmentTypeCode)
        {
            var selectSql = "SELECT ProjectCode,projectName,sumTime=SUM(ErrorDuration) ";
            var sqlMonth = selectSql + "FROM #listMonth t";
            var sqlWeek = selectSql + "FROM #listWeek t";
            if (!string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(equipmentTypeCode))
            {
                var whereSql = $"WHERE t.projectCode='{projectCode}' and t.equipmentTypeCode='{equipmentTypeCode}'";
                sqlMonth += whereSql;
                sqlWeek += whereSql;
            }
            if (!string.IsNullOrEmpty(projectCode) && string.IsNullOrEmpty(equipmentTypeCode))
            {
                var whereSql = $"WHERE t.projectCode='{projectCode}'";
                sqlMonth += whereSql;
                sqlWeek += whereSql;
            }
            if (string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(equipmentTypeCode))
            {
                var whereSql = $"WHERE t.equipmentTypeCode='{equipmentTypeCode}'";
                sqlMonth += whereSql;
                sqlWeek += whereSql;
            }
            var groupBySql = " group by projectCode,projectName order by sumTime desc";
            sqlMonth += groupBySql;
            sqlWeek += groupBySql;
            var sql = @$"  -----------------本月公共数据源--------------------------------------------
                         IF OBJECT_ID('Tempdb..#listMonth') IS NOT NULL
                         DROP TABLE #listMonth;
                         SELECT * INTO #listMonth FROM (
                                     SELECT t1.CreateTime AS Created,
                                            t1.UpdateTime AS Updated,
                                            t1.ErrorDuration,
                                            t1.EquipmentCode,
                                            t1.AlarmMessage + t1.Remark AS Alarm,
                                            t1.EquipmentName,
                                            t1.EquipmentPropName,
                                            t1.EquipmentTypeCode,
                                            t1.ProjectCode,
                                            p.projectName AS ProjectName,
                                            t.name AS EquipmentTypeName
                                    FROM dbo.daq_equipment_alarm_record t1
                                        LEFT JOIN dbo.base_project p
                                            ON p.projectCode = t1.ProjectCode
                                        LEFT JOIN dbo.base_equipment_type t
                                            ON t.code = t1.EquipmentTypeCode
							       {sqlWhereMonth} AND t1.ErrorDuration>=60
                          )t;
                         ----------------<#listMonth 处理相同设备在同年月日时分数据>----------------
                        WITH CTE
                        AS (SELECT ROW_NUMBER() OVER (PARTITION BY e.EquipmentCode,
                                                                   SUBSTRING(CONVERT(VARCHAR(50), e.Created, 120), 1, 16),
                                                                   SUBSTRING(CONVERT(VARCHAR(50), e.updated, 120), 1, 16)
                                                      ORDER BY EquipmentCode
                                                     ) AS rownumber,
                                   *
                            FROM #listMonth e)
                        DELETE FROM CTE
                        WHERE rownumber > 1;
                         -----------------本周公共数据源  #listWeek--------------------------------------------
                        IF OBJECT_ID('Tempdb..#listWeek') IS NOT NULL
                        DROP TABLE #listWeek;
                        SELECT *
                        INTO #listWeek
                        FROM
                        (
                            SELECT t1.CreateTime AS created,
                                   t1.UpdateTime AS updated,
                                   t1.ErrorDuration,
                                   t1.EquipmentCode,
                                   t1.EquipmentName,
                                   t1.EquipmentPropName,
                                   t1.Id AS EquipmentAlarmId,
                                   t1.AlarmMessage + t1.Remark AS Alarm,
                                   t1.ProjectCode,
                                   t1.EquipmentTypeCode,
                                   t.name AS EquipmentTypeName,
                                   p.projectName AS ProjectName
                            FROM dbo.daq_equipment_alarm_record t1
                                LEFT JOIN dbo.base_equipment_type t
                                    ON t.code = t1.EquipmentTypeCode
                                LEFT JOIN dbo.base_project p
                                    ON p.projectCode = t1.ProjectCode
                           {sqlWhereWeek} AND t1.ErrorDuration>=60
                        ) t;
                        ----------------listWeek 处理相同设备在同年月日时分数据>----------------
                        WITH CTE
                        AS (SELECT ROW_NUMBER() OVER (PARTITION BY e.EquipmentCode,
                                                                   SUBSTRING(CONVERT(VARCHAR(50), e.Created, 120), 1, 16),
                                                                   SUBSTRING(CONVERT(VARCHAR(50), e.updated, 120), 1, 16)
                                                      ORDER BY EquipmentCode
                                                     ) AS rownumber,
                                   *
                            FROM #listWeek e)
                        DELETE FROM CTE
                        WHERE rownumber > 1;
                        ----------------故障時間本月开始listMonth0 表格1-------------------------------
                        {sqlMonth} 
                        ----------------故障時間 月初、月末结束-------------------------------
                        ----------------故障時間本周开始listWeek1 表格1-------------------------------
                        {sqlWeek}
                         ----------------故障時間本周结束-------------------------------
                        --------------本周故障時間列表超過40分开始 （数据表ErrorDuration字段是记录以秒为单位，所以换算一下 40*60）----------------
                        SELECT t.*,
                               eh.HandleInfo,
                               eh.HandleUser,
                               eh.Reason
                        FROM #listweek t
                            LEFT JOIN bus_equipment_alarm_handle eh
                                ON eh.EquipmentAlarmRecordId = t.EquipmentAlarmId
                        WHERE t.ErrorDuration > 2400
                        ORDER BY t.ErrorDuration DESC;
                        -------------本周故障時間列表超過40分结束-------------------------------------
                       ---weekDefault  
                       SELECT dictValue FROM dbo.sys_dict_data WHERE dictLabel='weekDefault'
                       --妥善率目标 
                       select dictValue FROM dbo.sys_dict_data  WHERE dictLabel='properlyTarget'
                       --仓库可设定 
                       select dictValue FROM dbo.sys_dict_data  WHERE dictLabel='factoryTarget'
                      ";
            return sql;
        }
        /// <summary>
        /// 本周每天故障数据
        /// </summary>
        /// <param name="startDay"></param>
        /// <param name="endDay"></param>
        /// <returns></returns>
        public dynamic GetEquipmentWeekDayFaultInfo(string startDay, string endDay, string projectCode)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                DataSet ds;
                string startTime = startDay.TrimEnd() + " 00:00:00";
                string endTime = endDay.TrimEnd() + " 23:59:59";
                string sqlWhereWeek = @$" where  t1.CreateTime>='{startTime}' and t1.CreateTime<='{endTime}' and t1.EquipmentTypeCode not in ('{SystemVariable.IotNotContainDevice}')";
                //按故障内容汇总
                var sqlWeek = @"select name = t.ProjectName,  --项目
                                alarm = t.Alarm,                --故障内容
                                sumTime = sum(t.ErrorDuration) --故障:秒
                          from  #listWeekFaultInfo t 
                          where  
                               t.ProjectCode='{0}' and
                               t.created>='{1}' and t.created<='{2}' 
	                      group by t.ProjectName,t.Alarm";
                sqlWeek = string.Format(sqlWeek, projectCode, startTime, endTime);
                string sql = @$"    -----------------本周公共数据源--------------------------------------------
                        IF OBJECT_ID('Tempdb..#listWeekFaultInfo') IS NOT NULL
                        DROP TABLE #listWeekFaultInfo;
                        SELECT *
                        INTO #listWeekFaultInfo
                        FROM
                        (
                            SELECT t1.CreateTime AS created,
                                   t1.UpdateTime AS updated,
                                   t1.ErrorDuration,
                                   t1.EquipmentCode,
                                   t1.EquipmentName,
                                   t1.EquipmentPropName,
                                   t1.Id,
                                   t1.AlarmMessage + t1.Remark AS Alarm,
                                   t1.ProjectCode,
                                   t1.EquipmentTypeCode,
                                   t.name AS EquipmentTypeName,
                                   p.projectName AS ProjectName
                            FROM dbo.daq_equipment_alarm_record t1
                                LEFT JOIN dbo.base_equipment_type t
                                    ON t.code = t1.EquipmentTypeCode
                                LEFT JOIN dbo.base_project p
                                    ON p.projectCode = t1.ProjectCode
                           {sqlWhereWeek} AND t1.ErrorDuration>=60
                        ) t;
                         ---------------- #listWeekFaultInfo处理相同设备在同年月日时分数据>----------------
                        WITH CTE
                        AS (SELECT ROW_NUMBER() OVER (PARTITION BY e.EquipmentCode,
                                                                   SUBSTRING(CONVERT(VARCHAR(50), e.Created, 120), 1, 16),
                                                                   SUBSTRING(CONVERT(VARCHAR(50), e.updated, 120), 1, 16)
                                                      ORDER BY EquipmentCode
                                                     ) AS rownumber,
                                   *
                            FROM #listWeekFaultInfo e)
                        DELETE FROM CTE
                        WHERE rownumber > 1;  
                      -------本周所选设备每天故障分柱状图数据来源
                      {sqlWeek}
                      -----------本周每天设备故障详情-------------
                        SELECT t.Id,
                                t.ProjectName,
                                t.EquipmentTypeName,
                                t.EquipmentCode,
                                t.EquipmentName,
                                t.Alarm,
                                sumtime = t.ErrorDuration,
                                t.Created,
                                t.Updated,
                                h.Reason,
                                h.HandleInfo,
                                h.HandleUser
                        FROM #listWeekFaultInfo t
                            LEFT JOIN dbo.bus_equipment_alarm_handle h
                                ON h.EquipmentAlarmRecordId = t.Id
                        ORDER BY sumtime DESC;
                        DROP TABLE #listWeekFaultInfo; ";
                ds = Context.Ado.GetDataSetAll(sql);
                response.data = new
                {
                    top = ds.Tables[0],
                    table = ds.Tables[1],
                };
                response.count = ds.Tables[1].Rows.Count;
                return response;
            });
        }
        /// <summary>
        /// 故障統計分析
        /// </summary>
        /// <returns></returns>
        public dynamic FaultStatistics(string begin, string end, string projectCode, string equipmentTypeCode)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var sql = @"   IF OBJECT_ID('tempdb..#equipmentTempAlarm') IS NOT NULL
                            DROP TABLE #equipmentTempAlarm;
                            WITH CTE
                            AS (SELECT ROW_NUMBER() OVER (PARTITION BY EquipmentCode,
                                                                       CONVERT(VARCHAR(16), t1.CreateTime, 120),
                                                                       CONVERT(VARCHAR(16), t1.UpdateTime, 120)
                                                          ORDER BY EquipmentCode
                                                         ) AS rownumber,
                                       t1.*,
                                       p.projectName,
                                       t.name AS equipmentTypeName
                                FROM dbo.daq_equipment_alarm_record t1
                                    LEFT JOIN dbo.base_project p
                                        ON p.projectCode = t1.ProjectCode
                                    LEFT JOIN dbo.base_equipment_type t
                                        ON t.code = t1.EquipmentTypeCode
                                WHERE 1 = 1 {0}
                                      AND t1.CreateTime >= '{1}'
                                      AND t1.CreateTime <= '{2}' 
                                      AND t1.EquipmentTypeCode not in ('{3}')
                                )
                            SELECT *
                            INTO #equipmentTempAlarm
                            FROM CTE;
                            DELETE FROM #equipmentTempAlarm
                            WHERE rownumber > 1;
                            SELECT *
                            FROM
                            (
                                SELECT t1.EquipmentCode,
                                       t1.EquipmentName,
                                       Alarm =  t1.AlarmMessage+','+ t1.Remark,
                                       AlarmStart = t1.CreateTime,
                                       AlarmEnd = t1.UpdateTime,
                                       sumTimeAlarm = (t1.ErrorDuration),
                                       t1.projectName,
                                       t1.equipmentTypeName,
                                       t2.HandleInfo,
                                       HandleUser = ISNULL(t2.HandleUser, ''),
                                       HandleTime = t2.CreateTime,
                                       Reason = t2.Reason
                                FROM #equipmentTempAlarm t1
                                    LEFT JOIN dbo.bus_equipment_alarm_handle t2
                                        ON t1.Id = t2.EquipmentAlarmRecordId
                            ";
                sql += " )tt where sumTimeAlarm >= 60 ";
                sql += "order by  tt.projectName,tt.EquipmentCode, tt.AlarmStart DESC ";
                var whereSql = "";
                if (!string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(equipmentTypeCode))
                {
                    whereSql = $"and t1.projectCode='{projectCode}' and t1.equipmentTypeCode='{equipmentTypeCode}'";
                }
                if (!string.IsNullOrEmpty(projectCode) && string.IsNullOrEmpty(equipmentTypeCode))
                {
                    whereSql = $"and t1.projectCode='{projectCode}'";
                }
                if (string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(equipmentTypeCode))
                {
                    whereSql = $"and t1.equipmentTypeCode='{equipmentTypeCode}'";
                }
                sql = string.Format(sql, whereSql, begin, end, SystemVariable.IotNotContainDevice);
                var dt = Context.Ado.GetDataTable(sql);
                response.data = dt;
                response.count = dt.Rows.Count;
                response.code = 200;
                return response;
            }, catchRetrunValue: "list");
        }
        /// <summary>
        /// 妥善率报表查询
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="eqCode"></param>
        /// <param name="yearOrdaySelectVal">传年或者日</param>
        /// <param name="timeFlag">值有year、month、day</param>
        /// <returns></returns>
        public dynamic GetProperRate(string startTime, string endTime, string yearOrdaySelectVal, string timeFlag, string projectCode, string equipmentTypeCode)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                if (timeFlag == "year")
                {
                    startTime = yearOrdaySelectVal + "-01-01";
                    endTime = yearOrdaySelectVal + "-12-31";
                }
                else if (timeFlag == "day")
                {
                    startTime = yearOrdaySelectVal;
                    endTime = yearOrdaySelectVal;
                }
                var whereSql = "";
                if (!string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(equipmentTypeCode))
                {
                    whereSql = $"and t1.projectCode='{projectCode}' and t1.equipmentTypeCode='{equipmentTypeCode}'";
                }
                if (!string.IsNullOrEmpty(projectCode) && string.IsNullOrEmpty(equipmentTypeCode))
                {
                    whereSql = $"and t1.projectCode='{projectCode}'";
                }
                if (string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(equipmentTypeCode))
                {
                    whereSql = $"and t1.equipmentTypeCode='{equipmentTypeCode}'";
                }
                var startDate = startTime + " 00:00:00";
                var endDate = endTime + " 23:59:59";
                var sqlWhere = $" where  t1.CreateTime>='{startDate}' and t1.CreateTime<='{endDate}' and t1.EquipmentTypeCode not in ('{SystemVariable.IotNotContainDevice}')";
                var sql = GetRateSql(sqlWhere, timeFlag, whereSql);
                var dt = Context.Ado.GetDataTable(sql);
                response.data = dt;
                response.count = dt.Rows.Count;
                return response;
            }, catchRetrunValue: "list");
        }
        public string GetRateSql(string sqlWhere, string timeFlag, string whereSql)
        {
            var sql = "";
            if (!string.IsNullOrEmpty(sql))
            {
                sql += " union all ";
            }
            if (timeFlag == "year")
            {
                sql += @"select CONVERT(varchar(7),created,120) as date,
                                sumTime = sum(ErrorDuration),
                                ProjectName
                            from  #tempdbRate t 
                            where  1=1 {0}
	                        group by CONVERT(varchar(7),created,120),
                                ProjectName";
            }
            else
            {
                sql += @"select CONVERT(varchar,created,23) as date,
                                sumTime = sum(ErrorDuration),
                                ProjectName
                            from  #tempdbRate t 
                            where  1=1 {0}
	                        group by CONVERT(varchar,created,23) ,
                                ProjectName";
            }
            sql = string.Format(sql, whereSql);
            var resSql = @$"     
                        -----------------数据源--------------------------------------------
                        IF OBJECT_ID('Tempdb..#tempdbRate') IS NOT NULL
                        DROP TABLE #tempdbRate;
                        SELECT *
                        INTO #tempdbRate
                        FROM
                        (
                            SELECT t1.CreateTime AS created,
                                   t1.UpdateTime AS updated,
                                   t1.ErrorDuration,
                                   t1.EquipmentCode,
                                   t1.EquipmentName,
                                   t1.EquipmentPropName,
                                   t1.Id AS EquipmentAlarmId,
                                   t1.AlarmMessage + t1.Remark AS Alarm,
                                   t1.ProjectCode,
                                   t1.EquipmentTypeCode,
                                   t.name AS EquipmentTypeName,
                                   p.projectName AS ProjectName
                            FROM dbo.daq_equipment_alarm_record t1
                                LEFT JOIN dbo.base_equipment_type t
                                    ON t.code = t1.EquipmentTypeCode
                                LEFT JOIN dbo.base_project p
                                    ON p.projectCode = t1.ProjectCode
                           {sqlWhere} AND t1.ErrorDuration>=60
                        ) t;
                        ----------------listWeek 处理相同设备在同年月日时分数据>----------------
                        WITH CTE
                        AS (SELECT ROW_NUMBER() OVER (PARTITION BY e.EquipmentCode,
                                                                   SUBSTRING(CONVERT(VARCHAR(50), e.Created, 120), 1, 16),
                                                                   SUBSTRING(CONVERT(VARCHAR(50), e.updated, 120), 1, 16)
                                                      ORDER BY EquipmentCode
                                                     ) AS rownumber,
                                   *
                            FROM #tempdbRate e)
                        DELETE FROM CTE
                        WHERE rownumber > 1;
                         -----------------时间段故障总结--------------------------------------------
                        {sql}
                      ";
            return resSql;
        }
        /// <summary>
        /// 查询故障月份对比数据（只查三个月）
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="projectCode">项目</param>
        /// <param name="equipmentTypeCode">设备类型</param>
        /// <returns></returns>
        public dynamic GetFailureMonthCompare(int year, int month, string projectCode, string equipmentTypeCode)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                if (string.IsNullOrEmpty(projectCode))
                {
                    response.code = 500;
                    response.status = false;
                    response.message = "请选择一个项目";
                    return response;
                }
                DateTime startTime = new DateTime(year, month, 1).AddMonths(-2);//所选日期近三月的第一天
                DateTime endTime = new DateTime(year, month, 1, 23, 59, 59).AddMonths(1).AddDays(-1);//得出最后一天
                var whereSql = "";
                if (!string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(equipmentTypeCode))
                {
                    whereSql = $"and t1.projectCode='{projectCode}' and t1.equipmentTypeCode='{equipmentTypeCode}'";
                }
                if (!string.IsNullOrEmpty(projectCode) && string.IsNullOrEmpty(equipmentTypeCode))
                {
                    whereSql = $"and t1.projectCode='{projectCode}'";
                }
                var sql = @"SELECT t1.AlarmMessage + t1.Remark AS Alarm,
                               SUM(t1.ErrorDuration) AS SumTime,
                               CONVERT(CHAR(7), t1.CreateTime, 120) AS YearMonth
                        FROM dbo.daq_equipment_alarm_record t1
                        WHERE t1.CreateTime >= '{0}'
                              AND t1.CreateTime <= '{1}'
                              AND t1.EquipmentTypeCode not in ('{2}')
                              {3}
                        GROUP BY CONVERT(CHAR(7), t1.CreateTime, 120),
                                 t1.AlarmMessage + t1.Remark
                        ORDER BY CONVERT(CHAR(7), t1.CreateTime, 120);";
                sql = string.Format(sql, startTime, endTime,SystemVariable.IotNotContainDevice, whereSql);
                DataTable dt = Context.Ado.GetDataTable(sql);
                //处理没有数据的赋值0
                var alarmDt = dt.AsEnumerable().GroupBy(x => x["Alarm"]).Distinct().Select(x => x.Key).ToList();
                for (int i = 0; i < 3; i++)
                {
                    DateTime t = new DateTime(year, month, 1).AddMonths(-i);
                    string currentTime = t.ToString("yyyy-MM");
                    foreach (string alarm in alarmDt)
                    {
                        var isHave = dt.AsEnumerable().Any(x => x["YearMonth"].ToString() == currentTime && x["Alarm"].ToString().TrimEnd() == alarm);
                        if (!isHave)
                        {
                            //DataRow newRow = dt.NewRow();
                            //newRow["Alarm"] = alarm;
                            //newRow["SumTime"] = 0;
                            //newRow["YearMonth"] = currentTime;
                            //dt.Rows.Add(newRow);
                            dt.Rows.Add(alarm, 0, currentTime);
                        }
                    }
                }
                response.code = 200;
                response.data = dt;
                response.count = dt.Rows.Count;
                return response;
            }, catchRetrunValue: "list");
        }
    }
}