MES / IOT · Files

using Hh.Mes.Common.DataTableTo;
using Hh.Mes.Common.log;
using Hh.Mes.Pojo.System;
using Hh.Mes.POJO.ApiEntity;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.Response;
using Hh.Mes.Service.Repository;
using Newtonsoft.Json;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Formula.Functions;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Security.Claims;
using System.Text;

namespace Hh.Mes.Service
{
    public class BulletinBoardService : RepositorySqlSugar<sys_user>
    {
        /// <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,

                    projectAlarmSum = dss.Item1.Tables[6],
                    projectAlarmSumInfo = dss.Item1.Tables[7]
                };
                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";
            var sqlAlarm40 = "";
            if (!string.IsNullOrEmpty(projectCode))
            {
                var whereSql = $" WHERE t.projectCode='{projectCode}'";
                sqlMonth += whereSql;
                sqlWeek += whereSql;
                sqlAlarm40 = $" and t.projectCode='{projectCode}'";
            }

            var groupBySql = " group by projectCode,projectName order by sumTime desc";
            sqlMonth += groupBySql;
            sqlWeek += groupBySql;
            var projectKeys = base.GetProjectInfoKeys(base.sysUserApiAccount);
            var sql = @$"  -----------------本月公共数据源 ErrorDuration 已转化分）--------------------------------------------
                         IF OBJECT_ID('Tempdb..#listMonth') IS NOT NULL
                         DROP TABLE #listMonth;
                         SELECT * INTO #listMonth FROM (
                                     SELECT rownumber=ROW_NUMBER() OVER (PARTITION BY EquipmentCode, CONVERT(VARCHAR(16), t1.CreateTime, 120), CONVERT(VARCHAR(16), t1.UpdateTime, 120)
                                                      ORDER BY EquipmentCode), 
                                            t1.CreateTime AS Created,
                                            t1.UpdateTime AS Updated,
                                            ErrorDuration= CAST(t1.ErrorDuration/60.0 as DECIMAL(10, 2)) ,
                                            t1.EquipmentCode,
                                            t1.AlarmMessage + ';' + ISNULL(t1.Remark,'') AS Alarm,
                                            t1.EquipmentName,
                                            t1.EquipmentPropName,
                                            t1.EquipmentTypeCode,
                                            t1.ProjectCode,
                                            p.projectName AS ProjectName
                                    FROM dbo.daq_equipment_alarm_record t1 with(nolock)
                                    LEFT JOIN dbo.base_project p  ON p.projectCode = t1.ProjectCode
							       {sqlWhereMonth}
                                   and p.keys in('{string.Join("','", projectKeys)}')
                          )t;
                         ----------------<#listMonth 处理相同设备在同年月日时分数据>----------------
                         DELETE FROM #listMonth   WHERE rownumber > 1;

                         -----------------本周公共数据源  #listWeek  ErrorDuration 已转化分--------------------------------------------
                        IF OBJECT_ID('Tempdb..#listWeek') IS NOT NULL
                        DROP TABLE #listWeek;
                        SELECT *
                        INTO #listWeek
                        FROM
                        (
                            SELECT rownumber=ROW_NUMBER() OVER (PARTITION BY EquipmentCode, CONVERT(VARCHAR(16), t1.CreateTime, 120), CONVERT(VARCHAR(16), t1.UpdateTime, 120)
                                                          ORDER BY EquipmentCode), 
                                   t1.CreateTime AS created,
                                   t1.UpdateTime AS updated,
                                   ErrorDuration= CAST(t1.ErrorDuration/60.0 as DECIMAL(10, 2)),
                                   t1.EquipmentCode,
                                   t1.EquipmentName,
                                   t1.EquipmentPropName,
                                   t1.Id AS EquipmentAlarmId,
                                   t1.AlarmMessage+ ';'  + ISNULL(t1.Remark,'') AS Alarm,
                                   t1.ProjectCode,
                                   t1.EquipmentTypeCode,
                                   p.projectName AS ProjectName
                            FROM dbo.daq_equipment_alarm_record t1 with(nolock)
                            LEFT JOIN dbo.base_project p  ON p.projectCode = t1.ProjectCode
                           {sqlWhereWeek} 
                           and p.keys in('{string.Join("','", projectKeys)}')
                        ) t;
                        
                        ----------------listWeek 处理相同设备在同年月日时分数据>----------------
                       DELETE FROM #listWeek  WHERE rownumber > 1;
 
                        ----------------故障時間本月开始listMonth0 表格1-------------------------------
                        {sqlMonth} 
                        ----------------故障時間 月初、月末结束-------------------------------

                        ----------------故障時間本周开始listWeek1 表格1-------------------------------
                        {sqlWeek}
                         ----------------故障時間本周结束-------------------------------

                        --------------本周故障時間列表超過40分开始 ----------------
                        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 > 40  {sqlAlarm40}
                        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'
					  
                        --项目设备报警统计
					   select   t.ProjectCode, xAxis =convert(varchar(10),created,120),sumVal= SUM(t.ErrorDuration)  from #listWeek t
					   group by  t.ProjectCode,convert(varchar(10),created,120) 

					   --项目设备报警统计明细信息
					   select xAxis =convert(varchar(10),created,120),timeVal=ErrorDuration,*  from #listWeek ";
            return sql;
        }

        /// <summary>
        /// 故障統計分析
        /// </summary>
        /// <returns></returns>
        public dynamic FaultStatistics(string begin, string end, string projectCode, string equipmentCode)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var whereSql = "";
                if (!string.IsNullOrEmpty(projectCode)) whereSql = $"and t1.projectCode='{projectCode}'";
                if (!string.IsNullOrEmpty(equipmentCode)) whereSql = $" and t1.equipmentCode='{equipmentCode}'";

                var projectKeys = base.GetProjectInfoKeys(base.sysUserApiAccount);
                var sql = GetAlarmSql(whereSql, begin, end, projectKeys);
                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 equipmentCode)
        {
            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(equipmentCode))
                {
                    whereSql = $"and t.projectCode='{projectCode}' and t.EquipmentCode='{equipmentCode}'";
                }
                if (!string.IsNullOrEmpty(projectCode) && string.IsNullOrEmpty(equipmentCode))
                {
                    whereSql = $"and t.projectCode='{projectCode}'";
                }
                if (string.IsNullOrEmpty(projectCode) && !string.IsNullOrEmpty(equipmentCode))
                {
                    whereSql = $"and t.EquipmentCode='{equipmentCode}'";
                }

                var startDate = startTime + " 00:00:00";
                var endDate = endTime + " 23:59:59";

                var sql = GetAlarmSql("", startDate, endDate) + GetRateSql(timeFlag, whereSql);
                var ds = Context.Ado.GetDataSetAll(sql);

                var resDt = ds.Tables[1];

                //查询项目所有设备
                if (string.IsNullOrEmpty(equipmentCode))
                {
                    resDt = HandleDt(resDt, projectCode, startDate);
                }

                response.data = resDt;
                response.count = ds.Tables[1].Rows.Count;
                return response;
            }, catchRetrunValue: "list");
        }

        public string GetRateSql(string timeFlag, string whereSql)
        {
            var sql = @"select {0} as date,
                                sumTime = sum(ErrorDuration),
                                ProjectName,EquipmentName
                            from  #tempdbRate t 
                            where  1=1 {1}
	                        group by {0},
                                     ProjectName,EquipmentCode,EquipmentName
                            order by EquipmentCode";
            if (timeFlag == "year")
            {
                sql = string.Format(sql, "CONVERT(varchar(7),created,120)", whereSql);//年月分组
            }
            else
            {
                sql = string.Format(sql, "CONVERT(varchar,created,23)", whereSql);//年月日分组
            }
            return sql;
        }


        /// <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 = GetAlarmSql(whereSql, startTime.ToString(), endTime.ToString());
                sql = sql + @"
                                SELECT   Alarm=t1.AlarmMessage ,
                                            SumTime=SUM(t1.ErrorDuration) ,
	                                        YearMonth=CONVERT(CHAR(7), t1.CreateTime, 120) 
                                FROM   #tempdbRate t1
                                GROUP BY  CONVERT(CHAR(7), t1.CreateTime, 120),  t1.AlarmMessage
                                ORDER BY CONVERT(CHAR(7), t1.CreateTime,120);";
                DataSet ds = Context.Ado.GetDataSetAll(sql);
                //处理没有数据的赋值0
                var dt = ds.Tables[1];
                var alarmDt = dt.AsEnumerable().GroupBy(x => x["Alarm"]).Distinct().Select(x => x.Key).ToList();
                var sumTime = dt.GetColumnValues<decimal>("SumTime").Sum();
                var sumMsgInfo = "故障月份对比 ";
                for (int i = 0; i < 3; i++)
                {
                    DateTime t = new DateTime(year, month, 1).AddMonths(-i);
                    string currentTime = t.ToString("yyyy-MM");
                    var totalSumTime = dt.CalculateSumTime("YearMonth", "SumTime", currentTime);
                    sumMsgInfo += currentTime + "故障分钟:" + totalSumTime + "，";
                    foreach (string alarm in alarmDt)
                    {
                        var isHave = dt.AsEnumerable().Any(x => x["YearMonth"].ToString() == currentTime && x["Alarm"].ToString().TrimEnd() == alarm);
                        if (!isHave)
                        {
                            dt.Rows.Add(alarm, 0, currentTime);
                        }
                    }
                }

                response.code = 200;
                response.data = new
                {
                    data = dt,
                    allAlarm = ds.Tables[0],
                    sumMsgInfo = sumMsgInfo + " 总故障时间:" + sumTime + "(单位分钟)"
                };
                response.count = dt.Rows.Count;
                return response;
            }, catchRetrunValue: "list");
        }

        /// <summary>
        /// 项目月 四周 故障次数&时间
        /// </summary>
        /// <param name="projectCode">项目号</param>
        /// <param name="currentDate">时间日期</param>
        /// <returns></returns>
        public dynamic GetFailureMonthWeekCountAndTime(string projectCode, DateTime currentDate)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                List<EquipmentAlarmCountTimeEntity> list = new List<EquipmentAlarmCountTimeEntity>();
                //数据源查出来
                var whereSql = "";
                if (!string.IsNullOrEmpty(projectCode))
                {
                    whereSql = $"and t1.projectCode='{projectCode}'";
                }
                var start = currentDate.AddDays(-28).ToString();
                var end = currentDate.ToString();
                var sql = GetAlarmSql(whereSql, start, end);
                DataTable allAlarmDt = Context.Ado.GetDataTable(sql);
                //近四周
                for (int i = 0; i < 4; i++)
                {
                    EquipmentAlarmCountTimeEntity entity = new EquipmentAlarmCountTimeEntity();
                    DateTime startTime = currentDate.AddDays(-(7 * (i + 1)));
                    DateTime endTime = currentDate.AddDays(-(7 * i));
                    var dt = allAlarmDt.AsEnumerable()
                                             .Where(i => i.Field<DateTime>("CreateTime") >= startTime && i.Field<DateTime>("CreateTime") < endTime)
                                             .ToList();

                    entity.WeekNo = $"第{4 - i}周";
                    entity.TimePeriod = $"{startTime.ToString().Replace(" 0:00:00", "")}~{endTime.ToString().Replace(" 0:00:00", "")}";
                    entity.AlarmSumTime = (decimal)dt.Sum(i => i.Field<decimal>("ErrorDuration"));
                    entity.AlarmCount = dt.Count;
                    entity.AlarmListData = dt.Count > 0 ? dt.CopyToDataTable() : null;
                    list.Add(entity);
                }
                response.count = allAlarmDt.Rows.Count;
                response.data = list;
                return response;
            });
        }

        /// <summary>
        /// 项目设备故障一天每小时故障次数
        /// </summary>
        /// <param name="projectCode"></param>
        /// <param name="equipmentCode"></param>
        /// <param name="currentDate"></param>
        /// <returns></returns>
        public dynamic GetFailureEveryHourCount(string projectCode, string equipmentCode, DateTime currentDate)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                string startTime = currentDate.ToString("yyyy-MM-dd") + " 00:00:00";
                string endTime = currentDate.ToString("yyyy-MM-dd") + " 23:59:59";
                var sqlWhere = "";
                if (!string.IsNullOrEmpty(projectCode)) sqlWhere = $"and t1.projectCode='{projectCode}'";
                var sql = GetAlarmSql(sqlWhere, startTime, endTime);
                var whereSql = "";
                if (!string.IsNullOrEmpty(equipmentCode))
                {
                    whereSql = $@"where t1.EquipmentCode = '{equipmentCode}'";
                }
                sql = sql + @"
                            select COUNT(*) as alarmCount, CAST(FORMAT(t1.CreateTime , 'HH') AS INT) as day_hour 
                            from #tempdbRate t1 {0}
                            group by CAST(FORMAT(t1.CreateTime , 'HH') AS INT)
                            order by CAST(FORMAT(t1.CreateTime , 'HH') AS INT);
                               
                            select * from #tempdbRate t1 {0}

                            select sumVal= sum(ErrorDuration) from  #tempdbRate t1 {0} ";
                sql = string.Format(sql, whereSql);
                DataSet ds = Context.Ado.GetDataSetAll(sql);

                List<EquipmentAlarmCountTimeEntity> list = new List<EquipmentAlarmCountTimeEntity>();
                var result = ds.Tables[1];
                foreach (DataRow dr in result.Rows)
                {
                    int hour = (int)dr["day_hour"];

                    EquipmentAlarmCountTimeEntity entity = new EquipmentAlarmCountTimeEntity();
                    entity.AlarmCount = (int)dr["alarmCount"];
                    entity.TimePeriod = hour.ToString();
                    entity.AlarmListData = ds.Tables[2].AsEnumerable().Where(x => x.Field<DateTime>("CreateTime").Hour == hour).CopyToDataTable();
                    list.Add(entity);
                }
                response.data = list;
                response.count = ds.Tables[2].Rows.Count;
                response.message = list.Count == 0 ? " " : "故障次数天每小时对比，总次数：" + ds.Tables[2].Rows.Count + "，总故障分钟数：" + ds.Tables[3].Rows[0]["sumVal"].ToString();
                return response;
            });
        }

        /// <summary>
        /// 故障次数
        /// </summary>
        /// <param name="projectCode"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public dynamic GetFailureAlarmCount(string projectCode, DateTime startTime, DateTime endTime)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var begin = startTime;
                var end = DateTime.Parse(endTime.ToString("yyyy-MM-dd") + " 23:59:59");
                var data = Context.Queryable<daq_equipment_alarm_record, base_equipment_type>((x, y) => x.EquipmentTypeCode == y.code)
                                  .Where((x, y) => (!SystemVariable.IotNotContainDevice.Contains(x.EquipmentTypeCode)) && x.ProjectCode == projectCode && x.createTime >= begin && x.createTime <= end)
                                  .Select((x, y) => new
                                  {
                                      x.equipmentCode,
                                      x.equipmentName,
                                      //errorduration = x.errorduration > 0 ? Math.Round(x.errorduration / 60.0, 2) : 0,
                                      x.EquipmentTypeCode,
                                      EquipmentTypeName = y.name,
                                      x.alarmMessage,
                                      x.remark,
                                      x.createTime,
                                      x.updateTime
                                  })
                                  .ToList();
                //设备故障分组
                var res = data.GroupBy(x => new { x.equipmentCode, x.equipmentName, x.EquipmentTypeName, x.alarmMessage })
                              .Select(x => new
                              {
                                  equipmentCode = x.Key.equipmentCode.ToString(),
                                  equipmentName = x.Key.equipmentName.ToString(),
                                  equipmentTypeName = x.Key.EquipmentTypeName,
                                  alarmMessage = x.Key.alarmMessage,
                                  alarmCount = x.Count()
                              })
                              .OrderBy(x => x.equipmentCode)
                              .ToList();
                //故障次数分组
                var alarmRes = data.GroupBy(x => new { x.alarmMessage })
                              .Select(x => new
                              {
                                  alarmMessage = x.Key.alarmMessage,
                                  alarmCount = x.Count()
                              })
                              .OrderByDescending(x => x.alarmCount)
                              .ToList();
                response.data = new
                {
                    res = res,
                    alarmRes = alarmRes
                };
                response.count = res.Count;
                return response;
            });
        }

        /// <summary>
        /// 查询项目列表
        /// </summary>
        /// <param name="agvTag"></param>
        /// <returns></returns>
        public dynamic GetProjects(bool agvTag)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var expression = GetProjectsWhere();
                var result = Context.Queryable<base_project>()
                                    .Where(expression)
                                    .OrderBy(x => x.createTime)
                                    .Select(x => new { x.projectCode, x.projectName, x.createTime })
                                    .ToList();


                //agvTag标记true，只查询含agv设备的项目
                if (agvTag)
                {
                    var expressionAgv = GetProjectsAgvWhere();
                    result = Context.Queryable<base_project, base_equipment>((x, y) => x.keys == y.projectKeys)
                                    .Where(expressionAgv)
                                    .OrderBy((x, y) => x.createTime)
                                    .Select((x, y) => new { x.projectCode, x.projectName, x.createTime })
                                    .Distinct()
                                    .ToList();
                }

                response.data = result;
                response.count = result.Count;
                return response;
            });
        }

        public Expression<Func<base_project, bool>> GetProjectsWhere()
        {
            try
            {
                var exp = Expressionable.Create<base_project>();
                //非管理员，查询登陆用户绑定客户管理的项目
                string currentUser = sysUserApiAccount;
                if (currentUser != SystemVariable.DefaultCreated)
                {
                    var projectRoleKeys = GetProjectRoleKeys(currentUser);
                    exp.And(x => SqlFunc.Subqueryable<sys_role_projects_rel>().Where(c => projectRoleKeys.Contains(c.project_roles_key) && c.project_key == x.keys).Any());
                }
                return exp.ToExpression();//拼接表达式
            }
            catch (Exception ex)
            {
                throw new Exception($"{ex.Message}");
            }
        }

        public Expression<Func<base_project, base_equipment, bool>> GetProjectsAgvWhere()
        {
            try
            {
                var exp = Expressionable.Create<base_project, base_equipment>();
                //非管理员，查询登陆用户绑定客户管理的项目
                string currentUser = sysUserApiAccount;
                if (currentUser != SystemVariable.DefaultCreated)
                {
                    var projectRoleKeys = GetProjectRoleKeys(currentUser);
                    exp.And((x,y)=> SqlFunc.Subqueryable<sys_role_projects_rel>().Where(c => projectRoleKeys.Contains(c.project_roles_key) && c.project_key == x.keys).Any());
                }
                exp.And((x, y) => SystemVariable.IotAGVContainDevice.Contains(y.equipmentTypeCode));
                return exp.ToExpression();//拼接表达式
            }
            catch (Exception ex)
            {
                throw new Exception($"{ex.Message}");
            }
        }

        /// <summary>
        ///公共方法： 项目查询设备
        /// </summary>
        /// <param name="projectCode"></param>
        /// <param name="agvTag"></param>
        /// <returns></returns>
        public dynamic GetEquipmentsByProject(string projectCode, bool agvTag)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = Context.Queryable<base_equipment, base_project>((x, y) => x.projectKeys == y.keys)
                                    .Where((x, y) => y.projectCode == projectCode
                                                       && !SystemVariable.IotNotContainDevice.Contains(x.equipmentTypeCode))
                                    .OrderBy((x, y) => x.equipmentCode)
                                    .Select((x, y) => new { x.equipmentCode, x.equipmentName, x.equipmentTypeCode })
                                    .ToList();
                //agvTag标记true，只查询agv设备
                if (agvTag)
                {
                    result = result.AsEnumerable().Where(x => SystemVariable.IotAGVContainDevice.Contains(x.equipmentTypeCode)).ToList();
                }

                response.data = result;
                response.count = result.Count;
                return response;
            });
        }

        /// <summary>
        /// 公共方法：AGV报警列表
        /// </summary>
        /// <returns></returns>
        public dynamic GetAGVAlarmList(string projectCode, string equipmentCode)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = Context.Queryable<daq_equipment_alarm_record>()
                                  .Where(x => SystemVariable.IotAGVContainDevice.Contains(x.EquipmentTypeCode))
                                  .Where(x => string.IsNullOrEmpty(projectCode) || x.ProjectCode == projectCode)
                                  .Where(x => string.IsNullOrEmpty(equipmentCode) || x.equipmentCode == equipmentCode)
                                  .Select(x => new
                                  {
                                      key = x.alarmMessage,
                                      value = x.alarmMessage
                                  })
                                  .Distinct().ToList();
                response.data = result;
                response.count = result.Count;
                return response;
            });
        }

        /// <summary>
        /// AGV设备异常分析（点位维度）
        /// </summary>
        /// <returns></returns>
        public dynamic GetAGVCodePointAlarmRecord(string startTime, string endTime, string projectCode, string equipmentCode, string alarmMsg)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                DataSet ds = new DataSet();
                var startDate = startTime + " 00:00:00";
                var endDate = endTime + " 23:59:59";
                var sqlWhere = $" and t1.EquipmentTypeCode in ('{SystemVariable.IotAGVContainDevice}')";
                if (!string.IsNullOrEmpty(projectCode)) sqlWhere += $"  and t1.ProjectCode = '{projectCode}' ";
                if (!string.IsNullOrEmpty(equipmentCode)) sqlWhere += $"  and t1.EquipmentCode = '{equipmentCode}' ";
                if (!string.IsNullOrEmpty(alarmMsg)) sqlWhere += $"  and t1.AlarmMessage = '{alarmMsg}' ";
                var sql = GetAlarmSql(sqlWhere, startDate, endDate) + GetAGVCodePointAlarmRecordSql(projectCode, alarmMsg);
                ds = Context.Ado.GetDataSetAll(sql);

                var resDt = ds.Tables[2];
                //查询项目所有设备
                if (!string.IsNullOrEmpty(projectCode) && string.IsNullOrEmpty(equipmentCode))
                {
                    var equipmentList = Context.Queryable<base_equipment, base_project>((x, y) => x.projectKeys == y.keys)
                                          .Where((x, y) => y.projectCode == projectCode && x.equipmentTypeCode.Contains("AGV"))
                                          .Select((x, y) => new { x.equipmentCode, x.equipmentName, y.projectName })
                                          .ToList();
                    if(resDt!=null && resDt.Rows.Count > 0)
                    {
                        foreach (var i in equipmentList)
                        {
                            bool exists = resDt.AsEnumerable().Any(r => r.Field<string>("EquipmentName") == i.equipmentName);
                            if (!exists)
                            {
                                resDt.Rows.Add(0, i.equipmentCode, i.equipmentName, resDt.Rows[0]["CodePoint"]);
                            }
                        }
                    }
                    else
                    {
                        foreach(var i in equipmentList)
                        {
                            resDt.Rows.Add(0, i.equipmentCode, i.equipmentName, ds.Tables[1].Rows[0]["BarCode"]);
                        }
                    }
                    
                }

                response.data = new
                {
                    codepoints = ds.Tables[1],//该项目所有点位
                    result = resDt,//有报警的点位以及信息
                };
                response.count = resDt.Rows.Count;
                return response;
            });
        }

        /// <summary>
        /// AGV点位维度异常分析
        /// </summary>
        /// <param name="alarmMsg"></param>
        /// <returns></returns>
        public string GetAGVCodePointAlarmRecordSql(string projectCode, string alarmMsg)
        {
            var sql = $"select distinct ProjectCode,BarCode from daq_agv_point where ProjectCode='{projectCode}' order by BarCode;";
            if (!string.IsNullOrEmpty(alarmMsg))
            {
                sql += @"select COUNT(*) as count,
                                EquipmentCode,EquipmentName,
                                CodePoint,
                                AlarmMessage
                            from
	                            #tempdbRate t
                            group by
	                            EquipmentCode,EquipmentName,
                                CodePoint,
                                AlarmMessage";
            }
            else
            {
                sql += @"select COUNT(*) as count,
                                EquipmentCode,EquipmentName,
                                CodePoint
                            from
	                            #tempdbRate t
                            group by
	                            EquipmentCode,EquipmentName,
                                CodePoint";

            }
            return sql;
        }

        /// <summary>
        /// AGV设备异常分析（时间维度）
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="projectCode"></param>
        /// <param name="equipmentCode"></param>
        /// <param name="alarmMsg"></param>
        /// <returns></returns>
        public dynamic GetAGVAbnormalRecord(string startTime, string endTime, string projectCode, string equipmentCode, string alarmMsg)
        {
            var response = new ResponseNew();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                DataSet ds = new DataSet();
                var startDate = startTime + " 00:00:00";
                var endDate = endTime + " 23:59:59";
                var sqlWhere = $" and t1.EquipmentTypeCode in ('{SystemVariable.IotAGVContainDevice}')";
                if (!string.IsNullOrEmpty(projectCode)) sqlWhere += $"  and t1.ProjectCode = '{projectCode}' ";
                if (!string.IsNullOrEmpty(equipmentCode)) sqlWhere += $"  and t1.EquipmentCode = '{equipmentCode}' ";
                if (!string.IsNullOrEmpty(alarmMsg)) sqlWhere += $"  and t1.AlarmMessage = '{alarmMsg}' ";
                var sql = GetAlarmSql(sqlWhere, startDate, endDate) + GetAGVAbnormalRecordSql(alarmMsg);
                ds = Context.Ado.GetDataSetAll(sql);

                var resDt = ds.Tables[1];
                //查询项目所有设备
                if (!string.IsNullOrEmpty(projectCode) && string.IsNullOrEmpty(equipmentCode))
                {
                    var equipmentList = Context.Queryable<base_equipment, base_project>((x, y) => x.projectKeys == y.keys)
                                          .Where((x, y) => y.projectCode == projectCode && x.equipmentTypeCode.Contains("AGV"))
                                          .Select((x, y) => new { x.equipmentCode, x.equipmentName, y.projectName })
                                          .ToList();
                    foreach (var i in equipmentList)
                    {
                        bool exists = resDt.AsEnumerable().Any(r => r.Field<string>("EquipmentName") == i.equipmentName);
                        if (!exists)
                        {
                            resDt.Rows.Add(0, i.equipmentCode, i.equipmentName, startTime);
                        }
                    }
                }

                response.data = resDt;
                response.count = ds.Tables[1].Rows.Count;
                return response;
            });
        }

        public string GetAGVAbnormalRecordSql(string alarmMsg)
        {
            var sql = "";
            if (!string.IsNullOrEmpty(alarmMsg))
            {
                sql += @"select COUNT(*) as count,
                                EquipmentCode,EquipmentName,
                                CONVERT(varchar,created,23) as date,
                                AlarmMessage
                            from
	                            #tempdbRate t
                            group by
	                            EquipmentCode,EquipmentName,
                                CONVERT(varchar,created,23),
                                AlarmMessage";
            }
            else
            {
                sql += @"select COUNT(*) as count,
                                EquipmentCode,EquipmentName,
                                CONVERT(varchar,created,23) as date
                            from
	                            #tempdbRate t
                            group by
	                            EquipmentCode,EquipmentName,
                                CONVERT(varchar,created,23)";
            }
            return sql;
        }

        /// <summary>
        /// AGV故障任务妥善率
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="yearOrdaySelectVal"></param>
        /// <param name="timeFlag"></param>
        /// <param name="projectCode"></param>
        /// <param name="equipmentCode"></param>
        /// <returns></returns>
        public dynamic GetAGVProperRate(string startTime, string endTime, string yearOrdaySelectVal, string timeFlag, string projectCode, string equipmentCode)
        {
            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;
                }
                string startDate = startTime;
                startTime = startTime + " 00:00:00";
                endTime = endTime + " 23:59:59";
                //故障次数
                var sqlWhere = $" and t1.EquipmentTypeCode in ('{SystemVariable.IotAGVContainDevice}')";
                if (!string.IsNullOrEmpty(projectCode)) sqlWhere += $"  and t1.ProjectCode = '{projectCode}' ";
                if (!string.IsNullOrEmpty(equipmentCode)) sqlWhere += $"  and t1.EquipmentCode = '{equipmentCode}' ";
                var sql = GetAlarmSql(sqlWhere, startTime, endTime) + GetAGVProperRateSql(timeFlag, startTime, endTime, projectCode);
                var ds = Context.Ado.GetDataSetAll(sql);
                var resDt = ds.Tables[1];

                //查询项目所有设备
                if (!string.IsNullOrEmpty(projectCode) && string.IsNullOrEmpty(equipmentCode))
                {
                    var equipmentList = Context.Queryable<base_equipment, base_project>((x, y) => x.projectKeys == y.keys)
                                          .Where((x, y) => y.projectCode == projectCode && x.equipmentTypeCode.Contains("AGV"))
                                          .Select((x, y) => new { x.equipmentCode, x.equipmentName, y.projectName })
                                          .ToList();
                    foreach (var i in equipmentList)
                    {
                        bool exists = resDt.AsEnumerable().Any(r => r.Field<string>("EquipmentName") == i.equipmentName);
                        if (!exists)
                        {
                            resDt.Rows.Add(i.equipmentCode, i.equipmentName, startDate, 0, 0);
                        }
                    }
                }

                resDt.Columns.Add("Rate", typeof(string));
                foreach (DataRow row in resDt.Rows)
                {
                    if ((decimal)row["TotalTask"] == 0)
                    {
                        row["Rate"] = "100%";
                    }
                    else
                    {
                        decimal result = Math.Round((int)row["counts"] / (decimal)row["TotalTask"], 2);
                        var rate = (1 - result).ToString("P2");
                        row["Rate"] = rate;
                    }
                }

                

                response.data = resDt;
                response.count = resDt.Rows.Count;
                return response;
            });
        }

        public string GetAGVProperRateSql(string timeFlag, string startTime, string endTime, string projectCode)
        {
            var sql = @"IF OBJECT_ID('Tempdb..#tempdbAlarmCount') IS NOT NULL
                        DROP TABLE #tempdbAlarmCount;
                        SELECT *
                        INTO #tempdbAlarmCount
                        FROM
                        (
						    select COUNT(*) as counts,
                                EquipmentCode,EquipmentName,
                                {0} as dateVal
                            from #tempdbRate
                            group by {0},
	                                 EquipmentCode,EquipmentName
						)t;	

						select
                          task.*,
                          temp.counts
                        from
                          #tempdbAlarmCount temp join (
                        select
                          t.EquipmentCode,t.EquipmentName,
                          t.dateVal,
                          sum(d.TotalTask) as TotalTask
                        from
                          #tempdbAlarmCount t join daq_equipment_task d
                          on t.EquipmentCode = d.EquipmentCode
                          and t.dateVal = {0}
                        group by
                          t.EquipmentCode,t.EquipmentName,
                          t.dateVal
                        ) as task on task.EquipmentCode = temp.EquipmentCode
                        and task.dateVal = temp.dateVal
                        order by task.EquipmentCode";
            if (timeFlag == "year")
            {
                sql = string.Format(sql, "CONVERT(varchar(7),created,120)");
            }
            else
            {
                sql = string.Format(sql, "CONVERT(varchar,created,23)");
            }
            return sql;
        }

        /// <summary>
        /// 公共方法：查询对应条件的故障（过滤同设备同年月日时分的数据） 临时表 #tempdbRate
        /// </summary>
        /// <param name="sqlWhere"></param>
        /// <returns></returns>
        public string GetAlarmSql(string sqlWhere, string begin, string end,Guid[] projectKeys=null)
        {
            string sql = @" -----------------数据源--------------------------------------------
                       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.CreateTime,t1.UpdateTime,
                                   ErrorDuration = CAST(t1.ErrorDuration/60.0 as DECIMAL(10, 2)),
                                   t1.EquipmentCode,t1.EquipmentName,t1.EquipmentPropName,t1.Id AS EquipmentAlarmId,
                                   t1.AlarmMessage,t1.AlarmMessage +';'+ ISNULL(t1.Remark,'') AS Alarm, ISNULL(t1.Remark,'') AS Remark,
                                   t1.ProjectCode,t1.EquipmentTypeCode,p.projectName AS ProjectName,
                                   t.name AS EquipmentTypeName,t1.Points as CodePoint,
                                   h.HandleInfo,h.HandleUser,h.Reason, t1.HandleTime,t1.UserHandleTime
                            FROM dbo.daq_equipment_alarm_record t1 with(nolock)
                            LEFT JOIN dbo.bus_equipment_alarm_handle h
                                ON h.EquipmentAlarmRecordId=t1.Id
                            LEFT JOIN dbo.base_equipment_type t
                                ON t.code = t1.EquipmentTypeCode
                            LEFT JOIN dbo.base_project p
                                ON p.projectCode = t1.ProjectCode
                            WHERE 1=1 {0}
                                    AND t1.CreateTime >= '{1}'
                                    AND t1.CreateTime <= '{2}' 
                                    AND t1.EquipmentTypeCode not in ('{3}')
                                    {4}
                        ) t;
                        
                        ----------------tempdbRate 处理相同设备在同年月日时分数据>----------------
                        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;
                        SELECT*FROM #tempdbRate ORDER BY CreateTime desc;";
            
            var wh = "";
            if (projectKeys != null)
            {
                wh = $" and p.keys in('{string.Join("','", projectKeys)}')";
            }
            sql = string.Format(sql, sqlWhere, begin, end, SystemVariable.IotNotContainDevice, wh);
            return sql;
        }

        //通用处理无数据返回设备信息
        private DataTable HandleDt(DataTable resDt,string projectCode,string startDate)
        {
            if (!string.IsNullOrEmpty(projectCode))
            {
                var equipmentList = Context.Queryable<base_equipment, base_project>((x, y) => x.projectKeys == y.keys)
                                           .Where((x, y) => y.projectCode == projectCode)
                                           .Select((x, y) => new { x.equipmentCode, x.equipmentName, y.projectName })
                                           .ToList();
                foreach (var i in equipmentList)
                {
                    bool exists = resDt.AsEnumerable().Any(r => r.Field<string>("EquipmentName") == i.equipmentName);
                    if (!exists)
                    {
                        resDt.Rows.Add(startDate, 0, i.projectName, i.equipmentName);
                    }
                }
            }
            return resDt;
        }
    }
}