MES / IOT · Files

using Hh.Mes.Common.log;
using Hh.Mes.Common.Request;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.LicenseEntity;
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.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Text.RegularExpressions;

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 iotProjects = Context.Queryable<base_project>().Where(x => codes.Contains(x.projectCode))
                .Select(x => new base_project
                {
                    id = x.id,
                    projectCode = x.projectCode,
                    projectName = x.projectName
                }).ToList();

                var projectCodes = iotProjects.Select(x => x.projectCode).Distinct().ToList();

                var wmsProjects = Context.Queryable<bus_sn_project_info_wms_head>().Where(x => codes.Contains(x.correlatedCode) && !string.IsNullOrEmpty(x.ordeName)).ToList();

                var pattern = @"^[A-Za-z]{3}\d{6}";
                var regex = new Regex(pattern);

                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) && regex.IsMatch(code)).Select(code =>
                {
                    var txt = string.Empty;
                    var iotProject = iotProjects.Where(x => x.projectCode == code).FirstOrDefault();
                    if (iotProject != null)
                    {
                        txt = $"[{iotProject.projectName}]";
                    }
                    return new
                    {
                        id = Guid.NewGuid().ToString("N"),
                        name = code[..9] + txt,
                        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) && regex.IsMatch(code)).Select(code =>
                {
                    var txt = string.Empty;
                    var wmsTemp = wmsProjects.Where(x => x.correlatedCode == code).FirstOrDefault();
                    if (wmsTemp != null)
                    {
                        txt = $"[{wmsTemp.ordeName}]";
                    }
                    return new
                    {
                        id = Guid.NewGuid().ToString("N"),
                        name = code[..9] + txt,
                        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();

                var correlatedCodes = data.Select(x => x.correlatedCode).Distinct().ToList();
                var projectInfo = Context.Queryable<base_project>().Where(x => correlatedCodes.Contains(x.projectCode)).Select(x => new base_project
                {
                    projectCode = x.projectCode,
                    projectName = x.projectName
                }).ToList();

                result.Result = data.Select(x => new
                {
                    x.id,
                    x.code,
                    x.correlatedCode,
                    prpjectName = projectInfo.Where(s => s.projectCode == x.correlatedCode).Select(s => s.projectName),
                    snStatus = equipmentCodes.Contains(x.code),//SN使用状态
                    x.operation,
                    x.progress,
                    model = GetModelInfo(x.code),
                    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");
        }

        private static string GetModelInfo(string snCode)
        {
            if (string.IsNullOrWhiteSpace(snCode))
            {
                return string.Empty;
            }
            if (snCode.Length < 2)
            {
                return string.Empty;
            }
            var v1 = snCode[..1];
            var v2 = snCode.Substring(1, 1);

            return v1 switch
            {
                "1" => "管焊",
                "2" => "专机",
                "3" => "管道",
                "4" => "物流",
                "5" => "激光",
                "6" => "减速机",
                "7" => "切割机",
                "8" => "机器人",
                //软件部分
                "9" => v2 switch
                {
                    "1" => "WMS",
                    "2" => "WCS",
                    "3" => "MES",
                    "4" => "RCS",
                    "5" => "MTS",
                    "6" => "ECS",
                    "7" => "EMS",
                    "8" => "IOT",
                    "9" => "DT",
                    "A" => "参数化编程",
                    "B" => "离线软件",
                    //"C" => string.Empty,
                    //"D" => string.Empty,
                    //"E" => string.Empty,
                    //"F" => string.Empty,
                    _ => "软件",
                },
                "A" => string.Empty,
                "B" => string.Empty,
                "C" => string.Empty,
                "D" => string.Empty,
                "E" => string.Empty,
                "F" => string.Empty,
                _ => string.Empty,
            };
        }

        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",
                "1"
            };
            //过滤掉以数字开头的数据
            //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.Length == 12);
                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, string snStatus, DateTime start, DateTime end, bool Exel = false)
        {
            var response = new Response();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                //registered,已注册,不考虑
                //authorization,已授权
                //inbound,已入库
                //outbound,已出库
                //iot,已推送IOT
                //project,已绑定项目
                //errorInfo,报警信息已上传

                var lincenseQuery = ContextLicense.Queryable<SnInfo>()
                                        .LeftJoin<LicenseInfo>((s, li) => s.Sn == li.Sn)
                                        .WhereIF(!string.IsNullOrEmpty(sn), (s, li) => s.Sn.Contains(sn))
                                        .WhereIF(snStatus == "authorization", (s, li) => !string.IsNullOrEmpty(li.Sn))
                                        .WhereIF(start != default, (s, li) => s.Created >= start)
                                        .WhereIF(end != default, (s, li) => s.Created <= end);

                var lincenseData = lincenseQuery.OrderBy((s, li) => s.Created, OrderByType.Desc).Select((s, li) => new
                {
                    sn = s.Sn,
                    register_by = s.CreatedBy,
                    register_time = s.Created,
                    authorization = string.IsNullOrEmpty(li.Sn) ? null : "SN授权",
                }).ToList();

                var snCodes = lincenseData.Select(ld => ld.sn).Distinct().ToList();
                var wmsData = ContextWms.Queryable<SnPartDetailHistory>()
                                    .Where(pd => snCodes.Contains(pd.snCode))
                                    .Where(pd => pd.operation == "sn入库" || pd.operation == "sn出库").ToList();
                //iot
                var busSnProjectInfoWmsHeads = Context.Queryable<bus_sn_project_info_wms_head>().Where(x => snCodes.Contains(x.sn)).ToList();

                var sn_Alarm_Project = Context.Queryable<base_equipment>()
                .LeftJoin<daq_equipment_alarm_record>((equipment, alarm) => equipment.otherCode == alarm.equipmentCode)
                .LeftJoin<base_project>((equipment, alarm, project) => equipment.projectKeys == project.keys)
                .Where((equipment, alarm, project) => snCodes.Contains(equipment.equipmentCode))
                        .Select((equipment, alarm, project) => new
                        {
                            equipment.equipmentCode,
                            haveAlarm = !string.IsNullOrEmpty(alarm.alarmMessage),
                            projectName = project.projectName,
                            bindTime = equipment.createTime
                        }).ToList();

                var dataTemp = lincenseData.Where(lincense =>
                {
                    //已入库
                    if (snStatus == "inbound")
                    {
                        return wmsData.Where(w => w.snCode == lincense.sn && w.operation.Equals("sn入库", StringComparison.OrdinalIgnoreCase)).Any();
                    }

                    //已出库
                    if (snStatus == "outbound")
                    {
                        return wmsData.Where(w => w.snCode == lincense.sn && w.operation.Equals("sn出库", StringComparison.OrdinalIgnoreCase)).Any();
                    }

                    //已推送IOT
                    if (snStatus == "iot")
                    {
                        return busSnProjectInfoWmsHeads.Where(x => x.sn == lincense.sn).Any();
                    }

                    //已绑定项目
                    if (snStatus == "project")
                    {
                        return sn_Alarm_Project.Where(x => x.equipmentCode == lincense.sn && !string.IsNullOrEmpty(x.projectName)).Any();
                    }

                    //报警信息已上传
                    if (snStatus == "errorInfo")
                    {
                        return sn_Alarm_Project.Where(x => x.equipmentCode == lincense.sn && x.haveAlarm).Any();
                    }
                    return true;
                }).ToList();

                var total = dataTemp.Count;
                if (total == 0)
                {
                    response.Result = null;
                    response.Count = 0;
                    return response;
                }

                var offset = 0;
                var limit = total;
                if (!Exel)
                {
                    offset = (pageReq.page - 1) * pageReq.limit;
                    limit = pageReq.limit;
                }

                var data = dataTemp.Skip(offset).Take(limit).Select(lincense =>
                 {
                     //wms
                     var wmsInRecord = wmsData.Where(w => w.snCode == lincense.sn && w.operation.Equals("sn入库", StringComparison.OrdinalIgnoreCase)).OrderByDescending(x => x.created).FirstOrDefault();

                     var wmsOutRecord = wmsData.Where(w => w.snCode == lincense.sn && w.operation.Equals("sn出库", StringComparison.OrdinalIgnoreCase)).OrderByDescending(x => x.created).FirstOrDefault();

                     //iot
                     var busSnProjectInfoWmsHead = busSnProjectInfoWmsHeads.Where(x => x.sn == lincense.sn).OrderByDescending(x => x.createTime).FirstOrDefault();
                     var project = sn_Alarm_Project.Where(x => x.equipmentCode == lincense.sn && !string.IsNullOrEmpty(x.projectName)).FirstOrDefault();

                     return new
                     {
                         lincense.sn,
                         lincense.register_by,
                         lincense.register_time,
                         lincense.authorization,
                         authorize_time = ContextLicense.Queryable<LicenseInfo>()
                                         .Where(x => x.Sn == lincense.sn)
                                         .OrderByDescending(x => x.ExpiringDate)
                                         .Select(x => x.ExpiringDate).First(),
                         expiration_time = ContextLicense.Queryable<LicenseInfo>()
                                         .Where(x => x.Sn == lincense.sn)
                                         .OrderByDescending(x => x.Created)
                                         .Select(x => x.Created).First(),

                         //wms
                         inbound_operator = wmsInRecord?.operationUser ?? "未入库",
                         inbound_time = wmsInRecord?.operationTime,
                         outbound_operator = wmsOutRecord?.operationUser ?? "未出库",
                         outbound_time = wmsOutRecord?.operationTime,

                         //iot
                         last_push_time = busSnProjectInfoWmsHead?.createTime.ToString() ?? "未推送",
                         last_push_ordeCode = busSnProjectInfoWmsHead?.ordeCode,
                         last_push_userBy = busSnProjectInfoWmsHead?.userBy,
                         bind_time = project?.bindTime.ToString() ?? "未绑定",
                         bind_project = project?.projectName,
                         has_alarm = sn_Alarm_Project.Where(x => x.equipmentCode == lincense.sn && x.haveAlarm).Any() ? "是" : "否"
                     };
                 }).ToList();

                response.Result = data;
                response.Count = total;
                return response;
            });
        }

        #endregion
    }
}