MES / IOT · Files

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;

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 Tuple<DataSet, int, string, string> GetFaultReport(string yyyyMonth, string projectCode, string equipmentTypeCode)
        {
            var month = Convert.ToDateTime(yyyyMonth);
            //月初、月末
            var monthStart = month.AddDays(1 - month.Day);
            var monthEnd = month.AddDays(1 - month.Day).Date.AddMonths(1).AddSeconds(-1);
            var sqlWhereMonth = $" where  t1.CreateTime>='{monthStart}' and t1.CreateTime<='{monthEnd}' ";

            //周一、周末
            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 = $" where  t1.CreateTime>='{weekOne}' and t1.CreateTime<='{weekend}' ";


            var sql = GetSql(sqlWhereWeek, sqlWhereMonth, 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");
            return new Tuple<DataSet, int, string, string>(ds, diffDay, monthStr, weekStr);
        }
        public string GetSql(string sqlWhereWeek, string sqlWhereMonth, string projectCode, string equipmentTypeCode)
        {
            var selectSql = "SELECT 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";
            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 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}
                          )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 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 DataSet GetEquipmentWeekDayFaultInfo(string startDay, string endDay, string projectCode)
        {
            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}' ";

            //for (DateTime date = Convert.ToDateTime(startDay); date <= Convert.ToDateTime(endDay); date = date.AddDays(1))
            //{
            //    if (!string.IsNullOrEmpty(sqlWeek))
            //    {
            //        sqlWeek += " union all ";
            //    }
            //    var day = date.ToString("yyyy-MM-dd");
            //    sqlWeek += @"select     name = t.ProjectName,  --设备类型
            //                            sumTime = sum(t.ErrorDuration), --故障分
            //                            today=MIN(CONVERT(VARCHAR(16),t.Created,23))       --日期
            //                    from  #listWeekFaultInfo t 
            //                    where  
            //                         t.ProjectCode='{0}' and t.EquipmentTypeCode='{1}' and
            //                         t.created>='{2} 00:00:00' and t.created<='{2} 23:59:59' 
            //                 group by t.ProjectName";
            //    sqlWeek = string.Format(sqlWeek,projectCode,equipmentTypeCode,day);
            //}
            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 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);
            return ds;
        }


        /// <summary>
        /// 故障統計分析
        /// </summary>
        /// <returns></returns>
        public Tuple<dynamic, int> FaultStatistics(string begin, string end, string projectCode,string equipmentTypeCode)
        {
            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}')
                            SELECT *
                            INTO #equipmentTempAlarm
                            FROM CTE;
                            DELETE FROM #equipmentTempAlarm
                            WHERE rownumber > 1;

                            SELECT *
                            FROM
                            (
                                SELECT t1.EquipmentCode,
                                       t1.EquipmentName,
                                       Alarm = t1.AlarmMessage,
                                       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);
            var dt = Context.Ado.GetDataTable(sql);
            return new Tuple<dynamic, int>(dt, dt.Rows.Count);

        }



        /// <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 DataTable GetProperRate(string startTime, string endTime, string yearOrdaySelectVal, string timeFlag,string projectCode,string equipmentTypeCode)
        {
            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}' ";

            var sql = GetRateSql(sqlWhere, timeFlag,whereSql);
            var dt = Context.Ado.GetDataTable(sql);
            return dt;
        }

        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 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;
        }

    }
}