优化库存视图查询

dengjunjie

2025-01-11 09a6c6cbdffb83f7fa7c668693e6163ce807c1c6

优化库存视图查询

 ´úÂë¹ÜÀí/WMS/WIDESEA_WMSServer/WIDESEA_DTO/Stock/StockViewDTO.cs

@@ -16,6 +16,10 @@
    public class StockViewDTO
    {
        /// <summary>
        /// ä»“库ID
        /// </summary>
        public int WarehouseId {  get; set; }
        /// <summary>
        /// è´§ä½ç¼–号
        /// </summary>
        public string LocationCode { get; set; }

 ´úÂë¹ÜÀí/WMS/WIDESEA_WMSServer/WIDESEA_StockService/StockViewService.cs

@@ -12,12 +12,15 @@
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using WIDESEA_Common.LocationEnum;
using WIDESEA_Core;
using WIDESEA_Core.BaseRepository;
using WIDESEA_Core.BaseServices;
using WIDESEA_Core.Const;
using WIDESEA_Core.DB.Models;
using WIDESEA_Core.Enums;
using WIDESEA_Core.Helper;
using WIDESEA_Core.HostedService;
using WIDESEA_Core.Utilities;
using WIDESEA_DTO.Stock;
using WIDESEA_IStockRepository;
@@ -39,83 +42,117 @@

        public virtual PageGridData<StockViewDTO> GetPageData(PageDataOptions options)
        {
            List<StockViewDTO> stockViewDTOs = new List<StockViewDTO>();

            string where = string.Empty;
            ISugarQueryable<Dt_StockInfo> sugarQueryable1 = _dbBase.Queryable<Dt_StockInfo>().Includes(x => x.Details);
            if (!string.IsNullOrEmpty(options.Wheres))
            try
            {
                try
                {
                    List<SearchParameters> searchParametersList = options.Wheres.DeserializeObject<List<SearchParameters>>();
                    if (searchParametersList.Count > 0)
                    {
                        {
                            SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.MaterielCode).FirstLetterToLower());
                            if (searchParameters != null)
                            {
                                sugarQueryable1 = sugarQueryable1.Where(x => x.Details.Any(v => v.MaterielCode.Contains(searchParameters.Value)));
                            }
                        }
                List<StockViewDTO> stockViewDTOs = new List<StockViewDTO>();

                string where = string.Empty;
                ISugarQueryable<Dt_StockInfo> sugarQueryable1 = _dbBase.Queryable<Dt_StockInfo>().Includes(x => x.Details);
                if (!string.IsNullOrEmpty(options.Wheres))
                {
                    try
                    {
                        List<SearchParameters> searchParametersList = options.Wheres.DeserializeObject<List<SearchParameters>>();
                        if (searchParametersList.Count > 0)
                        {
                            SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.BatchNo).FirstLetterToLower());
                            if (searchParameters != null)
                            {
                                sugarQueryable1 = sugarQueryable1.Where(x => x.Details.Any(v => v.BatchNo.Contains(searchParameters.Value)));
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.MaterielCode).FirstLetterToLower());
                                if (searchParameters != null)
                                {
                                    sugarQueryable1 = sugarQueryable1.Where(x => x.Details.Any(v => v.MaterielCode.Contains(searchParameters.Value)));
                                }
                            }

                            {
                                SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.BatchNo).FirstLetterToLower());
                                if (searchParameters != null)
                                {
                                    sugarQueryable1 = sugarQueryable1.Where(x => x.Details.Any(v => v.BatchNo.Contains(searchParameters.Value)));
                                }
                            }

                        }

                    }

                    catch { }
                }
                catch { }
            }
                string whereSto = GetDataRole(typeof(Dt_StockInfo));
                sugarQueryable1 = sugarQueryable1.Where(whereSto);
                EntityProperties.ValidatePageOptions(options, ref sugarQueryable1);

            EntityProperties.ValidatePageOptions(options, ref sugarQueryable1);
                string whereLo = GetDataRole(typeof(Dt_StockInfo));
                ISugarQueryable<Dt_LocationInfo> sugarQueryable = _dbBase.Queryable<Dt_LocationInfo>().Where(whereLo);

            ISugarQueryable<Dt_LocationInfo> sugarQueryable = _dbBase.Queryable<Dt_LocationInfo>();
                EntityProperties.ValidatePageOptions(options, ref sugarQueryable);

            EntityProperties.ValidatePageOptions(options, ref sugarQueryable);
                ISugarQueryable<StockViewDTO> list = sugarQueryable1.InnerJoin(sugarQueryable, (b, a) => a.LocationCode == b.LocationCode).Select((b, a)
                    => new StockViewDTO
                    {
                        LocationCode = b.LocationCode,
                        Column = a.Column,
                        WarehouseId = b.WarehouseId,
                        CreateDate = b.CreateDate,
                        Creater = b.Creater,
                        Depth = a.Depth,
                        EnalbeStatus = a.EnableStatus,
                        Layer = a.Layer,
                        LocationName = a.LocationName,
                        LocationStatus = a.LocationStatus,
                        LocationType = a.LocationType,
                        Modifier = b.Modifier,
                        ModifyDate = b.ModifyDate,
                        PalletCode = b.PalletCode,
                        StockRemark = b.Remark,
                        RoadwayNo = a.RoadwayNo,
                        Row = a.Row,
                        StockId = b.Id,
                        StockStatus = b.StockStatus,
                        Details = b.Details,
                    });

            ISugarQueryable<StockViewDTO> list = sugarQueryable1.InnerJoin(sugarQueryable, (b, a) => a.LocationCode == b.LocationCode).Select((b, a)
                => new StockViewDTO
                var pklist = sugarQueryable1.Where(b => b.LocationCode == "平库位").Select(b => new StockViewDTO
                {
                    LocationCode = b.LocationCode,
                    Column = a.Column,
                    Column = 0,
                    WarehouseId = b.WarehouseId,
                    CreateDate = b.CreateDate,
                    Creater = b.Creater,
                    Depth = a.Depth,
                    EnalbeStatus = a.EnableStatus,
                    Layer = a.Layer,
                    LocationName = a.LocationName,
                    LocationStatus = a.LocationStatus,
                    LocationType = a.LocationType,
                    Depth = 0,
                    EnalbeStatus = EnableStatusEnum.Normal.ObjToInt(),
                    Layer = 0,
                    LocationName = "平库位",
                    LocationStatus = LocationStatusEnum.InStock.ObjToInt(),
                    LocationType = LocationTypeEnum.Undefined.ObjToInt(),
                    Modifier = b.Modifier,
                    ModifyDate = b.ModifyDate,
                    PalletCode = b.PalletCode,
                    StockRemark = b.Remark,
                    RoadwayNo = a.RoadwayNo,
                    Row = a.Row,
                    RoadwayNo = "平库",
                    Row = 0,
                    StockId = b.Id,
                    StockStatus = b.StockStatus,
                    Details = b.Details,
                });

                int totalCount = 0;
                int pktotalCount = 0;

            int totalCount = 0;
                stockViewDTOs = list.ToPageList(options.Page, options.Rows, ref totalCount);
                stockViewDTOs.AddRange(pklist.ToPageList(options.Page, options.Rows, ref pktotalCount));

            stockViewDTOs = list.ToPageList(options.Page, options.Rows, ref totalCount);

            stockViewDTOs.ForEach(x =>
            {
                x.MaterielCode = string.Join(",", x.Details.Select(x => x.MaterielCode).Distinct());
                x.BatchNo = string.Join(",", x.Details.Select(x => x.BatchNo).Distinct());
                stockViewDTOs.ForEach(x =>
                {
                    x.MaterielCode = string.Join(",", x.Details.Select(x => x.MaterielCode).Distinct());
                    x.BatchNo = string.Join(",", x.Details.Select(x => x.BatchNo).Distinct());
                }
                );
                return new PageGridData<StockViewDTO>(totalCount + pktotalCount, stockViewDTOs);
            }
            );
            catch (Exception ex)
            {

            return new PageGridData<StockViewDTO>(totalCount, stockViewDTOs);

            }
            return new PageGridData<StockViewDTO>();
        }

        public virtual object GetDetailPage(PageDataOptions pageData)
@@ -140,5 +177,53 @@
            }
            return new PageGridData<object>(total: 0, null);
        }

        private string GetDataRole(Type type)
        {
            try
            {
                UserRole? userRole = PermissionDataHostService.UserRoles.FirstOrDefault(x => x.UserId == App.User.UserId);
                if (userRole == null)
                    throw new Exception($"无权限");

                if (type.IsAssignableFrom(typeof(BaseWarehouseEntity)) || type.GetProperty(nameof(BaseWarehouseEntity.WarehouseId)) != null)
                {
                    if (userRole.WarehouseIds.Count > 0)
                    {
                        return $"{nameof(BaseWarehouseEntity.WarehouseId)} in ({userRole.WarehouseIds.Serialize().Replace("[", "").Replace("]", "")})";
                    }

                    else
                        return $"1 != 1";
                }
                else
                {
                    return "1 = 1";
                }

                //UserRole? userRole = PermissionDataHostService.UserRoles.FirstOrDefault(x => x.UserId == App.User.UserId);
                //if (userRole == null)
                //    throw new Exception($"无权限");

                //if (userRole.AuthorityScope == (int)AuthorityScopeEnum.CurrentRole)
                //{
                //    List<int> userId = PermissionDataHostService.UserRoles.Where(x => x.RoleId == userRole.RoleId).Select(x => x.UserId).ToList();
                //    return $"creater in ({userId.Serialize()})";
                //}
                //else if (userRole.AuthorityScope == (int)AuthorityScopeEnum.OnlySelf)
                //{
                //    return $"creater = '{userRole.UserName}'";
                //}
                //else if (userRole.AuthorityScope == (int)AuthorityScopeEnum.None)
                //{
                //    return $"1 != 1";
                //}
                //return "";
            }
            catch (Exception ex)
            {
                throw new Exception($"无权限，{ex.Message}");
            }
        }
    }
}