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.EnumEntitys;

using Hh.Mes.POJO.Response;

using Hh.Mes.POJO.ViewModel.Equipment;

using Hh.Mes.Service.Repository;

using Hh.Mes.Service.WebService.Equipment;

using SqlSugar;

using System;
using System.Collections.Generic;

using System.Data;
using System.Linq;

using System.Linq.Expressions;

namespace Hh.Mes.Service
{
    public class BulletinBoardService : RepositorySqlSugar<sys_user>
    {

        #region 故障维护报表相关

        /// <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.ToString("yyyy-MM-dd HH:mm:ss"), monthEnd.ToString("yyyy-MM-dd HH:mm:ss"), 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, startTime);

                }

                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,EquipmentCode,EquipmentName,Sequence

                            from  #tempdbRate t 

                            where  1=1 {1}
	                        group by {0},

                                     ProjectName,EquipmentCode,EquipmentName,Sequence
                            order by Sequence";

            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)