MES / IOT · Files

using Hh.Mes.Common.log;
using Hh.Mes.Common.Request;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.Response;
using Hh.Mes.POJO.WMSEntity;
using Hh.Mes.Service.Repository;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Text;
using System.Linq;
using NPOI.SS.Formula.Functions;
using System.Data;
using Hh.Mes.POJO.ApiEntity;
using Microsoft.IdentityModel.Tokens;

namespace Hh.Mes.Service.WmsService
{
    public class WmsSnPartService : RepositorySqlSugar<Sn>
    {
        #region SN进度追溯
        public Response<List<SnPartDetailHistory>> GetSnPartDetailHistory(string snCode)
        {
            var result = new Response<List<SnPartDetailHistory>>();
            try
            {
                result.Status = true;
                result.Result = ContextWms.Queryable<SnPartDetailHistory>().Where(x => x.snCode == snCode).OrderBy(x => x.created).ToList();
            }
            catch (Exception ex)
            {
                result.Code = 500;
                result.Status = false;
                result.Message = ex.Message;
            }
            return result;
        }

        public dynamic GetTreeList()
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var stringBuilder = new StringBuilder();
                var nodes = new List<dynamic>();
                var rootNode = new
                {
                    id = Guid.NewGuid(),
                    name = "根节点",
                    keys = "r-1",
                    parentId = "0",
                    isok = false,
                    projectKeys = Guid.Empty,
                };

                var codes = ContextWms.Queryable<Sn>().Where(LinqWhere(new Sn())).OrderBy(x => x.correlatedCode).Select(x => x.correlatedCode).Distinct().ToList();

                var projectCodes = Context.Queryable<base_project>().Where(x => codes.Contains(x.projectCode)).Select(x => x.projectCode).Distinct().ToList();

                var tempNode1 = new
                {
                    id = Guid.NewGuid().ToString("N"),
                    name = "IOT已使用",
                    keys = "r-2",
                    parentId = rootNode.keys,
                    isok = false,
                };

                //已使用
                var temp1 = codes.Where(code => projectCodes.Contains(code)).Select(code => new
                {
                    id = Guid.NewGuid().ToString("N"),
                    name = code,
                    keys = code,
                    parentId = tempNode1.keys,
                    isok = true,
                });

                var tempNode2 = new
                {
                    id = Guid.NewGuid().ToString("N"),
                    name = "IOT未使用",
                    keys = "r-3",
                    parentId = rootNode.keys,
                    isok = false,
                };
                //未使用
                var temp2 = codes.Where(code => !projectCodes.Contains(code)).Select(code => new
                {
                    id = Guid.NewGuid().ToString("N"),
                    name = code,
                    keys = code,
                    parentId = tempNode2.keys,
                    isok = true,
                    //projectKeys = x.keys
                }).ToList();

                nodes.Add(rootNode);
                nodes.Add(tempNode1);
                nodes.Add(tempNode2);
                nodes.AddRange(temp1);
                nodes.AddRange(temp2);
                return nodes;
            });
        }

        public dynamic Load(PageReq pageReq, Sn entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = new Response();
                var expression = LinqWhere(entity);
                //先组合查询表达式(多表查询查看IOT 设备列表案例)
                var query = ContextWms.Queryable<Sn>().Where(expression).Select(x => new
                {
                    x.id,
                    x.code,
                    x.correlatedCode,
                    operation = SqlFunc.Subqueryable<SnPartDetailHistory>().Where(s => s.snCode == x.code).OrderByDesc(s => s.created).Select(s => s.operation),
                    progress = SqlFunc.Subqueryable<SnPartDetailHistory>().Where(s => s.snCode == x.code).DistinctCount(s => s.operation) * 100 / 8,
                    x.created,
                    x.createdBy,
                });

                int total = 0;
                var data = query.ToOffsetPage(pageReq.page, pageReq.limit, ref total);
                var equipmentCodes = Context.Queryable<base_equipment>().Select(x => x.equipmentCode).Distinct().ToList();

                var createds = data.Select(x => x.createdBy).Distinct().ToList();
                var users = Context.Queryable<sys_user>().Where(x => createds.Contains(x.account)).Select(x => new sys_user
                {
                    id = x.id,
                    account = x.account,
                    name = x.name
                }).ToList();

                result.Result = data.Select(x => new
                {
                    x.id,
                    x.code,
                    x.correlatedCode,
                    snStatus = equipmentCodes.Contains(x.code),//SN使用状态
                    x.operation,
                    x.progress,
                    x.created,
                    x.createdBy,
                    userName = users.Where(s => s.account == x.createdBy).Select(s => s.name).FirstOrDefault(),
                }).ToList();
                result.Count = total;
                return result;
            }, catchRetrunValue: "list");
        }

        public dynamic LoadDesc(PageReq pageReq, SnPartDetail entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = new Response();
                var expression = LinqWhereDetail(entity);
                //先组合查询表达式(多表查询查看IOT 设备列表案例)
                var query = ContextWms.Queryable<SnPartDetail>().Where(expression);
                int total = 0;
                result.Result = query.ToOffsetPage(pageReq.page, pageReq.limit, ref total);
                result.Count = total;
                return result;
            }, catchRetrunValue: "list");
        }

        private Expression<Func<Sn, bool>> LinqWhere(Sn entity)
        {
            var exp = Expressionable.Create<Sn>();
            exp.And(x => x.created >= new DateTime(2024, 11, 1));
            exp.And(x => !string.IsNullOrEmpty(x.correlatedCode));
            var filterCodes = new List<string>
            {
                "/",
                "测试",
                "test",
                "ceshi",
            };
            //过滤掉以数字开头的数据
            for (int i = 0; i < 10; i++)
            {
                filterCodes.Add($"{i}");
            }

            foreach (var item in filterCodes)
            {
                exp.And(x => !x.correlatedCode.StartsWith(item));
            }

            if (!string.IsNullOrWhiteSpace(entity.code))
            {
                exp.And(x => x.code.Contains(entity.code));
            }
            if (!string.IsNullOrWhiteSpace(entity.correlatedCode))
            {
                exp.And(x => x.correlatedCode.Contains(entity.correlatedCode));
            }
            if (entity.syncIot >= 0)
            {
                exp.And(x => x.syncIot == entity.syncIot);
            }

            return exp.ToExpression();//拼接表达式
        }

        private Expression<Func<SnPartDetail, bool>> LinqWhereDetail(SnPartDetail entity)
        {
            var exp = Expressionable.Create<SnPartDetail>();
            if (entity.snId != default)
            {
                exp.And(x => x.snId == entity.snId);
            }
            if (!string.IsNullOrWhiteSpace(entity.snCode))
            {
                exp.And(x => x.snCode.Contains(entity.snCode));
            }
            return exp.ToExpression();//拼接表达式
        }
        #endregion


        #region SN查询 阶段状态
        public dynamic LoadSnState(PageReq pageReq, string sn, DateTime start, DateTime end,bool Exel=false)
        {
            var response = new Response();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var lincenseWherePageSql = "";
                if (!Exel)
                {
                    var offset = (pageReq.page - 1) * pageReq.limit;
                    lincenseWherePageSql = $"limit {pageReq.limit} offset {offset}";
                }
                var lincenseWhereSql = "";
                if (!string.IsNullOrEmpty(sn)) lincenseWhereSql += $" and s.sn like '%{sn}%' ";
                if (start!= DateTime.MinValue&& end!=DateTime.MinValue) lincenseWhereSql += $" and s.Created >='{start}' and s.Created <='{end}' ";

                // SN申请、授权统计 mysql数据库
                var lincenseSql = $@"select  s.sn,
                                             s.CreatedBy as register_by, 
                                             s.Created as register_time,
                                             case 
                                                 when li.sn is not null then 'SN授权'
                                                 else null 
                                             end as authorization,
                                             li.expiration_time,  
                                             li.Created as authorize_time   
                                     from   sninfo s
                                     left join ( select sn, max(ExpiringDate) as expiration_time , max(Created) as Created 
                                                 from licenseinfo  group by sn ) li on s.sn = li.sn  
                                     where 1=1 {lincenseWhereSql}
                                     order by s.Created desc
                                     {lincenseWherePageSql} ; 
                                     
                                     select count(*) as rowTotal  from  sninfo s   where 1=1 {lincenseWhereSql} ; ";
                var licenseDs = ContextLicense.Ado.GetDataSetAll(lincenseSql);

                // 动态扩展主表字段（包含默认值）
                licenseDs.Tables[0].Columns.AddRange(new[] {
                    new DataColumn("inbound_operator", typeof(string)) { DefaultValue = "未入库" },
                    new DataColumn("inbound_time", typeof(string)) { DefaultValue = "未入库" },
                    new DataColumn("outbound_operator", typeof(string)) { DefaultValue = "未出库" },
                    new DataColumn("outbound_time", typeof(string)) { DefaultValue = "未出库" },
                    new DataColumn("last_push_time", typeof(string)) { DefaultValue = "未推送" },
                    new DataColumn("last_push_ordeCode", typeof(string)) { DefaultValue = "未推送" },
                    new DataColumn("last_push_userBy", typeof(string)) { DefaultValue = "未推送" },

                    new DataColumn("bind_time", typeof(string)) { DefaultValue = "未绑定" },
                    new DataColumn("bind_project", typeof(string)) { DefaultValue = "" },
                    new DataColumn("has_alarm", typeof(string)) { DefaultValue = "否" }
                });
                if (licenseDs.Tables[0].Rows.Count==0)
                {
                    response.Result = null;
                    response.Count = 0;
                    return response;
                }
                // 生成SN列表（带引号）
                var snList = licenseDs.Tables[0].AsEnumerable()
                                    .Select(r => $"'{r["sn"].ToString()}'")
                                    .Distinct();
                var snInClause = string.Join(",", snList);

                // WMS出入库批量查询 mysql数据库
                var wmsSql = $@" select sncode as sn,
                                        max(case when operation='sn入库' then createdby end) as inbound_operator,
                                        max(case when operation='sn入库' then created end) as inbound_time,

                                        max(case when operation='sn出库' then createdby end) as outbound_operator,
                                        max(case when operation='sn出库' then created end) as outbound_time
                                    from sn_part_detail_history 
                                    where sncode in ({string.Join(",", snList)})
                                          and operation in ('sn入库','sn出库')
                                    group by sncode;";

                var wmsResults = ContextWms.Ado.GetDataTable(wmsSql)
                                        .AsEnumerable()
                                        .ToDictionary(
                                            row => row["sn"].ToString(),
                                            row => new {
                                                inbound_operator = string.IsNullOrEmpty(row["inbound_operator"].ToString()) ? "未入库" : row["inbound_operator"].ToString(),
                                                inbound_time = row["inbound_time"]?.ToString() ?? "未入库",
                                                outbound_operator = string.IsNullOrEmpty(row["outbound_operator"].ToString())?"未出库": row["outbound_operator"].ToString(),
                                                outbound_time = row["outbound_time"]?.ToString() ?? "未出库"  
                                            });

                // IOT设备信息批量查询 微软数据库 daq_equipment_status_record_history 表数据库大需要注意
                var iotSql = $@"select b.sn,
                                       convert(varchar(10), p.last_push_time, 120) as last_push_time,p.ordeCode,p.userBy,
                                       convert(varchar(10), b.bind_time, 120) as bind_time,b.projectName,
                                       a.sn as has_alarm
                                from (    select equipmentCode as sn,t1.createtime as bind_time,t2.projectName
                                          from base_equipment t1 
                                          left join base_project t2 on t1.projectKeys=t2.keys
                                          where t1.equipmentCode in ({snInClause})
                                         ) b 
                                     
                                left join (
                                        select sn, max(createtime) as last_push_time, max(ordeCode) as ordeCode, max(userBy) as userBy 
                                        from bus_sn_project_info_wms_head 
                                        where sn in ({snInClause})
                                        group by sn ) p
                                        on p.sn = b.sn
                                left join 
                                       (select distinct EquipmentCode as sn 
                                        from daq_equipment_alarm_record 
                                        where EquipmentCode in ({snInClause})) a 
                                       on b.sn = a.sn";

                var iotResults = Context.Ado.GetDataTable(iotSql)
                                        .AsEnumerable()
                                        .ToDictionary(
                                            row => row["sn"].ToString(),
                                            row => new {
                                                last_push_time =string.IsNullOrEmpty(row["last_push_time"].ToString()) ? "未推送" : row["last_push_time"].ToString(),
                                                last_push_ordeCode = row["ordeCode"].ToString(),
                                                last_push_userBy = row["userBy"].ToString(),
                                                bind_time = string.IsNullOrEmpty(row["bind_time"].ToString()) ? "未绑定" : row["bind_time"].ToString(),
                                                bind_project = string.IsNullOrEmpty(row["projectName"].ToString()) ? "" : row["projectName"].ToString(),

                                                has_alarm = string.IsNullOrEmpty(row["has_alarm"].ToString()) ? "否" : "是",
                                            });

                // 填充所有数据
                foreach (DataRow row in licenseDs.Tables[0].Rows)
                {
                    var sn = row["sn"].ToString();

                    // 填充WMS数据
                    if (wmsResults.TryGetValue(sn, out var wmsRecord))
                    {
                        row["inbound_operator"] = wmsRecord.inbound_operator;
                        row["inbound_time"] = wmsRecord.inbound_time;
                        row["outbound_operator"] = wmsRecord.outbound_operator;
                        row["outbound_time"] = wmsRecord.outbound_time;
                    }

                    // 填充IOT数据
                    if (iotResults.TryGetValue(sn, out var iotRecord))
                    {
                        row["last_push_time"] = iotRecord.last_push_time;
                        row["last_push_ordeCode"] = iotRecord.last_push_ordeCode;
                        row["last_push_userBy"] = iotRecord.last_push_userBy;

                        row["bind_time"] = iotRecord.bind_time;
                        row["bind_project"] = iotRecord.bind_project;

                        
                        row["has_alarm"] = iotRecord.has_alarm ;
                    }
                }

                response.Result = licenseDs.Tables[0];
                response.Count = int.Parse(licenseDs.Tables[1].Rows[0]["rowTotal"].ToString());
                return response;
            });
        }

        #endregion
    }
}