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