using SqlSugar;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using WIDESEA_Core;
|
using WIDESEA_Core.BaseRepository;
|
using WIDESEA_DTO.Stock;
|
using WIDESEA_IStockService;
|
using WIDESEA_Model.Models;
|
using WIDESEA_Core.Helper;
|
using WIDESEA_Core.Utilities;
|
using System.Reflection;
|
using System.Dynamic;
|
using Magicodes.ExporterAndImporter.Excel;
|
using Magicodes.ExporterAndImporter.Core;
|
using WIDESEA_Core.HostedService;
|
using WIDESEA_Core.DB.Models;
|
using WIDESEA_IOutboundRepository;
|
using WIDESEA_IStockRepository;
|
using WIDESEA_Common.StockEnum;
|
using WIDESEA_IBasicRepository;
|
using WIDESEA_Common.WareHouseEnum;
|
|
namespace WIDESEA_StockService
|
{
|
public partial class ProStockViewService : IProStockViewService
|
{
|
private readonly IUnitOfWorkManage _unitOfWorkManage;
|
private readonly SqlSugarClient _dbBase;
|
private readonly IOutboundRepository _outboundRepository;
|
private readonly IStockRepository _stockRepository;
|
private readonly IBasicRepository _basicRepository;
|
|
public ProStockViewService(IUnitOfWorkManage unitOfWorkManage, IOutboundRepository outboundRepository, IStockRepository stockRepository,IBasicRepository basicRepository)
|
{
|
_unitOfWorkManage = unitOfWorkManage;
|
_dbBase = unitOfWorkManage.GetDbClient();
|
_outboundRepository = outboundRepository;
|
_stockRepository = stockRepository;
|
_basicRepository = basicRepository;
|
}
|
|
public virtual PageGridData<ProStockViewDTO> GetPageData(PageDataOptions options)
|
{
|
try
|
{
|
List<ProStockViewDTO> stockViewDTOs = new List<ProStockViewDTO>();
|
|
string where = string.Empty;
|
ISugarQueryable<Dt_ProStockInfo> sugarQueryable1 = _dbBase.Queryable<Dt_ProStockInfo>().Includes(x => x.proStockInfoDetails);
|
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_ProStockInfoDetail.ProductCode).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.proStockInfoDetails.Any(v => v.ProductCode.Contains(searchParameters.Value)));
|
}
|
}
|
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_ProStockInfoDetail.ProductVersion).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.proStockInfoDetails.Any(v => v.ProductVersion.Contains(searchParameters.Value)));
|
}
|
}
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_ProStockInfoDetail.SpecifyVer).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.proStockInfoDetails.Any(v => v.SpecifyVer.Contains(searchParameters.Value)));
|
}
|
}
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_ProStockInfoDetail.LotNumber).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.proStockInfoDetails.Any(v => v.LotNumber.Contains(searchParameters.Value)));
|
}
|
}
|
|
|
}
|
|
}
|
catch { }
|
}
|
string whereSto = GetDataRole(typeof(Dt_ProStockInfo));
|
sugarQueryable1 = sugarQueryable1.Where(whereSto);
|
EntityProperties.ValidatePageOptions(options, ref sugarQueryable1);
|
|
string whereLo = GetDataRole(typeof(Dt_LocationInfo));
|
ISugarQueryable<Dt_LocationInfo> sugarQueryable = _dbBase.Queryable<Dt_LocationInfo>().Where(whereLo);
|
|
EntityProperties.ValidatePageOptions(options, ref sugarQueryable);
|
|
ISugarQueryable<ProStockViewDTO> list = sugarQueryable1.InnerJoin(sugarQueryable, (b, a) => a.LocationCode == b.LocationCode).Select((b, a)
|
=> new ProStockViewDTO
|
{
|
ProStockAttribute=b.ProStockAttribute,
|
LocationCode = b.LocationCode,
|
Column = a.Column,
|
WarehouseId = b.WarehouseId,
|
CreateDate = b.CreateDate,
|
Creater = b.Creater,
|
EnalbeStatus = a.EnableStatus,
|
Layer = a.Layer,
|
LocationName = a.LocationName,
|
LocationStatus = a.LocationStatus,
|
Modifier = b.Modifier,
|
ModifyDate = b.ModifyDate,
|
PalletCode = b.PalletCode,
|
StockRemark = b.Remark,
|
Row = a.Row,
|
ProStockId = b.Id,
|
StockStatus = b.StockStatus,
|
Details = b.proStockInfoDetails,
|
SumStocks=b.proStockInfoDetails.Sum(x=>x.StockPcsQty)
|
});
|
|
var pklist = sugarQueryable1.Where(b => b.LocationCode == "成品待发货区" || b.LocationCode == "成品包装拣货区").Select(b => new ProStockViewDTO
|
{
|
ProStockAttribute = b.ProStockAttribute,
|
LocationCode = b.LocationCode,
|
WarehouseId = b.WarehouseId,
|
CreateDate = b.CreateDate,
|
Creater = b.Creater,
|
LocationName = b.LocationCode,
|
Modifier = b.Modifier,
|
ModifyDate = b.ModifyDate,
|
PalletCode = b.PalletCode,
|
StockRemark = b.Remark,
|
ProStockId = b.Id,
|
StockStatus = b.StockStatus,
|
Details = b.proStockInfoDetails,
|
SumStocks = b.proStockInfoDetails.Sum(x => x.StockPcsQty)
|
});
|
|
int totalCount = 0;
|
int pktotalCount = 0;
|
|
stockViewDTOs = list.ToPageList(options.Page, options.Rows, ref totalCount);
|
stockViewDTOs.AddRange(pklist.ToPageList(options.Page, options.Rows, ref pktotalCount));
|
|
stockViewDTOs.ForEach(x =>
|
{
|
x.ProductCode = string.Join(",", x.Details.Select(x => x.ProductCode).Distinct());
|
x.ProductVersion = string.Join(",", x.Details.Select(x => x.ProductVersion).Distinct());
|
x.SpecifyVer = string.Join(",", x.Details.Select(x => x.SpecifyVer).Distinct());
|
x.DateCode = x.Details.FirstOrDefault()?.DateCode ?? "";
|
x.LotNumber = x.Details.FirstOrDefault()?.LotNumber ?? "";
|
x.ERPOrder = x.Details.FirstOrDefault()?.ERPOrder ?? "";
|
x.SaleOrder= x.Details.FirstOrDefault()?.SaleOrder ?? "";
|
x.MoNumber = x.Details.FirstOrDefault()?.MoNumber ?? "";
|
}
|
);
|
return new PageGridData<ProStockViewDTO>(totalCount + pktotalCount, stockViewDTOs.OrderByDescending(x=>x.CreateDate).ToList());
|
}
|
catch (Exception ex)
|
{
|
|
}
|
return new PageGridData<ProStockViewDTO>();
|
}
|
|
public virtual object GetDetailPage(PageDataOptions pageData)
|
{
|
Type t = typeof(ProStockViewDTO);
|
|
if (pageData.Value == null) return new PageGridData<object>(total: 0, null);
|
string keyName = t.GetKeyName();
|
////生成查询条件
|
//Expression<Func<TEntity, bool>> whereExpression = keyName.CreateExpression<TEntity>(pageData.Value, LinqExpressionType.Equal);
|
int totalCount = 0;
|
PropertyInfo? propertyInfo = t.GetProperties().FirstOrDefault(x => x.GetCustomAttribute<Navigate>() != null);
|
if (propertyInfo != null)
|
{
|
Type detailType = propertyInfo.PropertyType.GetGenericArguments()[0];
|
Navigate? navigate = propertyInfo.GetCustomAttribute<Navigate>();
|
if (navigate != null)
|
{
|
List<ExpandoObject> list = _dbBase.Queryable(detailType.Name, "detail").Where(navigate.GetName(), "=", pageData.Value).ToPageList(pageData.Page, pageData.Rows, ref totalCount);
|
return new PageGridData<ExpandoObject>(totalCount, list);
|
}
|
}
|
return new PageGridData<object>(total: 0, null);
|
}
|
public virtual WebResponseContent Export(PageDataOptions options)
|
{
|
WebResponseContent content = new WebResponseContent();
|
try
|
{
|
Type t = typeof(ProStockViewDTO);
|
|
string savePath = AppDomain.CurrentDomain.BaseDirectory + $"ExcelExport";
|
IExporter exporter = new ExcelExporter();
|
options.Page = 1;
|
options.Rows = 30;
|
options.Order = "desc";
|
options.Sort = "proStockId";
|
List<ProStockViewDTO> stockViewDTOs = new List<ProStockViewDTO>();
|
List<Dt_Warehouse> warehouses = _basicRepository.WarehouseRepository.QueryData(x => x.WarehouseType == WarehouseTypEnum.成品.ToString());
|
string where = string.Empty;
|
ISugarQueryable<Dt_ProStockInfo> sugarQueryable1 = _dbBase.Queryable<Dt_ProStockInfo>().Includes(x => x.proStockInfoDetails);
|
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_ProStockInfoDetail.ProductCode).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.proStockInfoDetails.Any(v => v.ProductCode.Contains(searchParameters.Value)));
|
}
|
}
|
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_ProStockInfoDetail.ProductVersion).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.proStockInfoDetails.Any(v => v.ProductVersion.Contains(searchParameters.Value)));
|
}
|
}
|
|
}
|
|
}
|
catch { }
|
}
|
string whereSto = GetDataRole(typeof(Dt_ProStockInfo));
|
sugarQueryable1 = sugarQueryable1.Where(whereSto);
|
EntityProperties.ValidatePageOptions(options, ref sugarQueryable1);
|
|
string whereLo = GetDataRole(typeof(Dt_LocationInfo));
|
ISugarQueryable<Dt_LocationInfo> sugarQueryable = _dbBase.Queryable<Dt_LocationInfo>().Where(whereLo);
|
|
EntityProperties.ValidatePageOptions(options, ref sugarQueryable);
|
|
ISugarQueryable<ProStockViewDTO> list = sugarQueryable1.InnerJoin(sugarQueryable, (b, a) => a.LocationCode == b.LocationCode).Select((b, a)
|
=> new ProStockViewDTO
|
{
|
ProStockAttribute = b.ProStockAttribute,
|
LocationCode = b.LocationCode,
|
Column = a.Column,
|
WarehouseId = b.WarehouseId,
|
CreateDate = b.CreateDate,
|
Creater = b.Creater,
|
EnalbeStatus = a.EnableStatus,
|
Layer = a.Layer,
|
LocationName = a.LocationName,
|
LocationStatus = a.LocationStatus,
|
Modifier = b.Modifier,
|
ModifyDate = b.ModifyDate,
|
PalletCode = b.PalletCode,
|
StockRemark = b.Remark,
|
Row = a.Row,
|
ProStockId = b.Id,
|
StockStatus = b.StockStatus,
|
Details = b.proStockInfoDetails,
|
SumStocks = b.proStockInfoDetails.Sum(x => x.StockPcsQty),
|
|
});
|
|
//var pklist = sugarQueryable1.Where(b => b.LocationCode == "平库位").Select(b => new StockViewDTO
|
//{
|
// LocationCode = b.LocationCode,
|
// Column = 0,
|
// WarehouseId = b.WarehouseId,
|
// CreateDate = b.CreateDate,
|
// Creater = b.Creater,
|
// 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 = "平库",
|
// Row = 0,
|
// StockId = b.Id,
|
// StockStatus = b.StockStatus,
|
// Details = b.Details,
|
//});
|
stockViewDTOs = list.ToList();
|
//stockViewDTOs.AddRange(pklist.ToList());
|
stockViewDTOs.ForEach(x =>
|
{
|
x.ProductCode = string.Join(",", x.Details.Select(x => x.ProductCode).Distinct());
|
x.ProductVersion = string.Join(",", x.Details.Select(x => x.ProductVersion).Distinct());
|
x.SpecifyVer = string.Join(",", x.Details.Select(x => x.SpecifyVer).Distinct());
|
x.DateCode = x.Details.FirstOrDefault()?.DateCode ?? "";
|
x.LotNumber = x.Details.FirstOrDefault()?.LotNumber ?? "";
|
x.ERPOrder = x.Details.FirstOrDefault()?.ERPOrder ?? "";
|
x.SaleOrder = x.Details.FirstOrDefault()?.SaleOrder ?? "";
|
x.MoNumber = x.Details.FirstOrDefault()?.MoNumber ?? "";
|
x.StockRemark = warehouses.FirstOrDefault(v => v.WarehouseId == x.WarehouseId).WarehouseName;
|
}
|
);
|
|
byte[] data = exporter.ExportAsByteArray(stockViewDTOs).Result;
|
|
string fileName = "成品库存视图.xlsx";
|
|
FileHelper.WriteFile(savePath, fileName, data);
|
|
content = WebResponseContent.Instance.OK(data: savePath + "\\" + fileName);
|
}
|
catch (Exception ex)
|
{
|
content = WebResponseContent.Instance.Error(ex.Message);
|
}
|
return content;
|
}
|
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}");
|
}
|
}
|
public List<ProStockViewDTO> GetProPKSelectStocks(int orderId)
|
{
|
try
|
{
|
Dt_ProOutOrderDetail outOrderDetail = _outboundRepository.ProOutOrderDetailRepository.QueryFirst(x => x.Id == orderId);
|
if (outOrderDetail == null)
|
{
|
throw new Exception($"未找到出库单明细");
|
}
|
List<ProStockViewDTO> stockViewDTOs = new List<ProStockViewDTO>();
|
ISugarQueryable<Dt_ProStockInfo> sugarQueryable1 = _dbBase.Queryable<Dt_ProStockInfo>().Includes(x => x.proStockInfoDetails);
|
var pklist = sugarQueryable1.Where(b => b.LocationCode == "成品待发货区" && b.StockStatus==StockStatusEmun.平库入库完成.ObjToInt() && (b.ShipmentOrder==null|| b.ShipmentOrder =="")).Where(x=>x.proStockInfoDetails.Any(v=>v.ProductCode== outOrderDetail.PCode)).Select(b => new ProStockViewDTO
|
{
|
ProStockAttribute = b.ProStockAttribute,
|
LocationCode = b.LocationCode,
|
WarehouseId = b.WarehouseId,
|
CreateDate = b.CreateDate,
|
Creater = b.Creater,
|
LocationName = b.LocationCode,
|
Modifier = b.Modifier,
|
ModifyDate = b.ModifyDate,
|
PalletCode = b.PalletCode,
|
StockRemark = b.Remark,
|
ProStockId = b.Id,
|
StockStatus = b.StockStatus,
|
Details = b.proStockInfoDetails,
|
});
|
stockViewDTOs.AddRange(pklist.ToList());
|
stockViewDTOs.ForEach(x =>
|
{
|
x.ProductCode = string.Join(",", x.Details.Select(x => x.ProductCode).Distinct());
|
x.ProductVersion = string.Join(",", x.Details.Select(x => x.ProductVersion).Distinct());
|
x.SpecifyVer = string.Join(",", x.Details.Select(x => x.SpecifyVer).Distinct());
|
x.DateCode = x.Details.FirstOrDefault()?.DateCode ?? "";
|
x.LotNumber = x.Details.FirstOrDefault()?.LotNumber ?? "";
|
x.ERPOrder = x.Details.FirstOrDefault()?.ERPOrder ?? "";
|
x.SaleOrder = x.Details.FirstOrDefault()?.SaleOrder ?? "";
|
x.MoNumber = x.Details.FirstOrDefault()?.MoNumber ?? "";
|
x.SumStocks = x.Details.Sum(x => x.StockPcsQty);
|
}
|
);
|
return stockViewDTOs.OrderBy(x => x.DateCode).ThenBy(x => x.CreateDate).ToList();
|
//return _stockRepository.ProStockInfoRepository.QueryTabs<Dt_ProStockInfo, Dt_ProStockInfoDetail, ProStockViewDTO>((a, b) => a.Id == b.ProStockId, (a, b) => new ProStockViewDTO
|
//{
|
// LocationCode = a.LocationCode,
|
// ProductCode = b.ProductCode,
|
// ProStockAttribute = a.ProStockAttribute,
|
// ProductVersion=b.ProductVersion,
|
// DateCode=b.DateCode,
|
// ProStockId=a.Id,
|
// SumStocks=b.StockPcsQty,
|
// SaleOrder=b.SaleOrder,
|
// PalletCode = a.PalletCode,
|
// CreateDate=a.CreateDate,
|
// ERPOrder=b.ERPOrder,
|
// MoNumber=b.MoNumber
|
//}, a => a.LocationCode == "成品待发货区", b => b.ProductCode == outOrderDetail.PCode, x => true).GroupBy(x => x.PalletCode).Select(x => new ProStockViewDTO
|
//{
|
// LocationCode = x.FirstOrDefault()?.LocationCode ?? "",
|
// ProductCode = x.FirstOrDefault()?.ProductCode ?? "",
|
// ProductVersion = x.FirstOrDefault()?.ProductVersion ?? "",
|
// DateCode = x.FirstOrDefault()?.DateCode ?? "",
|
// ProStockId = x.FirstOrDefault().ProStockId,
|
// SumStocks=x.FirstOrDefault().SumStocks,
|
// SaleOrder = x.FirstOrDefault()?.SaleOrder ?? "",
|
// ProStockAttribute=x.FirstOrDefault()?.ProStockAttribute ?? 0,
|
// PalletCode = x.Key,
|
// ERPOrder=x.FirstOrDefault()?.ERPOrder ?? "",
|
// MoNumber=x.FirstOrDefault()?.MoNumber ?? "",
|
// CreateDate=x.FirstOrDefault()?.CreateDate ?? new DateTime()
|
//}).ToList();
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
}
|
}
|
}
|
