using SqlSugar;
|
using System;
|
using System.Collections.Generic;
|
using System.Text;
|
using System.Threading.Tasks;
|
using WIDESEA_Core;
|
using WIDESEA_Core.BaseRepository;
|
using WIDESEA_Core.DB.Models;
|
using WIDESEA_Core.Helper;
|
using WIDESEA_Core.HostedService;
|
using WIDESEA_DTO.Stock;
|
using WIDESEA_IStockService;
|
using WIDESEA_Model.Models;
|
using System.Linq;
|
using WIDESEA_Common.WareHouseEnum;
|
using Magicodes.ExporterAndImporter.Core;
|
using Magicodes.ExporterAndImporter.Excel;
|
using WIDESEA_Common.LocationEnum;
|
using WIDESEA_Core.Utilities;
|
|
namespace WIDESEA_StockService
|
{
|
public partial class MaterielnfoStatisticsService : IMaterielnfoStatisticsService
|
{
|
public readonly IUnitOfWorkManage _unitOfWorkManage;
|
public readonly SqlSugarClient _dbBase;
|
|
public MaterielnfoStatisticsService(IUnitOfWorkManage unitOfWorkManage) {
|
_unitOfWorkManage = unitOfWorkManage;
|
_dbBase = unitOfWorkManage.GetDbClient();
|
}
|
|
public virtual PageGridData<MaterielnfoStatisticsDTO> GetPageGridData(PageDataOptions options)
|
{
|
try
|
{
|
|
List<MaterielnfoStatisticsDTO> materielnfoStatistics = new List<MaterielnfoStatisticsDTO>();
|
ISugarQueryable<Dt_StockInfoDetail> sugarQueryable1 = _dbBase.Queryable<Dt_StockInfoDetail>();
|
|
string whereStoDe = GetDataRole(typeof(Dt_StockInfoDetail));
|
sugarQueryable1 = sugarQueryable1.Where(whereStoDe);
|
|
|
string whereSto = GetDataRole(typeof(Dt_StockInfo));
|
ISugarQueryable<Dt_StockInfo> sugarQueryable = _dbBase.Queryable<Dt_StockInfo>().Where(whereSto);
|
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.MaterielCode).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.MaterielCode.Contains(searchParameters.Value));
|
}
|
}
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfo.WarehouseId).FirstLetterToLower());
|
if (searchParameters != null) {
|
sugarQueryable = sugarQueryable.Where(x => x.WarehouseId.Equals(searchParameters.Value));
|
}
|
}
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfo.StockStatus).FirstLetterToLower());
|
if(searchParameters!= null)
|
{
|
sugarQueryable = sugarQueryable.Where(x => x.StockStatus.Equals(searchParameters.Value));
|
}
|
}
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.MaterielName).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.MaterielName.Contains(searchParameters.Value));
|
}
|
}
|
|
}
|
|
}
|
catch(Exception ex)
|
{
|
|
}
|
}
|
|
|
ISugarQueryable<MaterielnfoStatisticsDTO> sugarQueryable2 = sugarQueryable1.InnerJoin(sugarQueryable, (b, a) => b.StockId == a.Id).Where((b,a)=>a.LocationCode!="" && a.LocationCode!=null).Select((b, a)
|
=> new MaterielnfoStatisticsDTO
|
{
|
WarehouseId = a.WarehouseId,
|
MaterielCode = b.MaterielCode,
|
MaterielName = b.MaterielName,
|
StockQuantity =(decimal) b.StockQuantity,
|
OutboundQuantity =(decimal) b.OutboundQuantity,
|
StockStatus = a.StockStatus,
|
Unit = b.Unit,
|
Remark = a.Remark,
|
|
});
|
// 获取所有数据
|
var allData = sugarQueryable2.ToList();
|
|
// 使用字典进行分组汇总
|
var groupedData = new Dictionary<string, MaterielnfoStatisticsDTO>();
|
foreach (var item in allData)
|
{
|
// 确保初始值为decimal类型
|
if (groupedData.ContainsKey(item.MaterielCode))
|
{
|
groupedData[item.MaterielCode].StockQuantity += Convert.ToDecimal(item.StockQuantity);
|
groupedData[item.MaterielCode].OutboundQuantity += Convert.ToDecimal(item.OutboundQuantity);
|
}
|
else
|
{
|
groupedData[item.MaterielCode] = new MaterielnfoStatisticsDTO
|
{
|
WarehouseId = item.WarehouseId,
|
MaterielCode = item.MaterielCode,
|
MaterielName = item.MaterielName,
|
StockQuantity = Convert.ToDecimal(item.StockQuantity),
|
OutboundQuantity = Convert.ToDecimal(item.OutboundQuantity),
|
StockStatus = item.StockStatus,
|
Unit = item.Unit,
|
Remark = item.Remark
|
};
|
}
|
}
|
|
// 转换为列表
|
materielnfoStatistics = groupedData.Values.ToList();
|
|
// 分页处理
|
int startIndex = (options.Page - 1) * options.Rows;
|
int endIndex = Math.Min(startIndex + options.Rows, materielnfoStatistics.Count);
|
materielnfoStatistics = materielnfoStatistics.GetRange(startIndex, endIndex - startIndex);
|
|
int count = groupedData.Count;
|
|
return new PageGridData<MaterielnfoStatisticsDTO>(count, materielnfoStatistics);
|
|
}
|
catch(Exception ex)
|
{
|
|
}
|
return new PageGridData<MaterielnfoStatisticsDTO>();
|
}
|
|
public virtual WebResponseContent Export(PageDataOptions options)
|
{
|
WebResponseContent content = new WebResponseContent();
|
try
|
{
|
Type t = typeof(MaterielnfoStatisticsDTO);
|
|
string savePath = AppDomain.CurrentDomain.BaseDirectory + $"ExcelExport";
|
IExporter exporter = new ExcelExporter();
|
options.Page = 1;
|
options.Rows = 30;
|
options.Order = "desc";
|
options.Sort = "MaterielCode";
|
List<MaterielnfoStatisticsDTO> materielnfoStatistics = new List<MaterielnfoStatisticsDTO>();
|
ISugarQueryable<Dt_StockInfoDetail> sugarQueryable1 = _dbBase.Queryable<Dt_StockInfoDetail>();
|
|
string whereStoDe = GetDataRole(typeof(Dt_StockInfoDetail));
|
sugarQueryable1 = sugarQueryable1.Where(whereStoDe);
|
|
|
string whereSto = GetDataRole(typeof(Dt_StockInfo));
|
ISugarQueryable<Dt_StockInfo> sugarQueryable = _dbBase.Queryable<Dt_StockInfo>().Where(whereSto);
|
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.MaterielCode).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.MaterielCode.Contains(searchParameters.Value));
|
}
|
}
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfo.WarehouseId).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable = sugarQueryable.Where(x => x.WarehouseId.Equals(searchParameters.Value));
|
}
|
}
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfo.StockStatus).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable = sugarQueryable.Where(x => x.StockStatus.Equals(searchParameters.Value));
|
}
|
}
|
{
|
SearchParameters? searchParameters = searchParametersList.FirstOrDefault(x => x.Name == nameof(Dt_StockInfoDetail.MaterielName).FirstLetterToLower());
|
if (searchParameters != null)
|
{
|
sugarQueryable1 = sugarQueryable1.Where(x => x.MaterielName.Contains(searchParameters.Value));
|
}
|
}
|
|
}
|
|
}
|
catch (Exception ex)
|
{
|
|
}
|
}
|
|
|
ISugarQueryable<MaterielnfoStatisticsDTO> sugarQueryable2 = sugarQueryable1.InnerJoin(sugarQueryable, (b, a) => b.StockId == a.Id).Where((b, a) => a.LocationCode != "" && a.LocationCode != null).Select((b, a)
|
=> new MaterielnfoStatisticsDTO
|
{
|
WarehouseId = a.WarehouseId,
|
MaterielCode = b.MaterielCode,
|
MaterielName = b.MaterielName,
|
StockQuantity = (decimal)b.StockQuantity,
|
OutboundQuantity =(decimal) b.OutboundQuantity,
|
StockStatus = a.StockStatus,
|
Unit = b.Unit,
|
Remark = a.Remark,
|
|
});
|
// 获取所有数据
|
var allData = sugarQueryable2.ToList();
|
|
// 使用字典进行分组汇总
|
var groupedData = new Dictionary<string, MaterielnfoStatisticsDTO>();
|
foreach (var item in allData)
|
{
|
if (groupedData.ContainsKey(item.MaterielCode))
|
{
|
groupedData[item.MaterielCode].StockQuantity += item.StockQuantity;
|
groupedData[item.MaterielCode].OutboundQuantity += item.OutboundQuantity;
|
}
|
else
|
{
|
groupedData[item.MaterielCode] = new MaterielnfoStatisticsDTO
|
{
|
WarehouseId = item.WarehouseId,
|
MaterielCode = item.MaterielCode,
|
MaterielName = item.MaterielName,
|
StockQuantity = item.StockQuantity,
|
OutboundQuantity = item.OutboundQuantity,
|
StockStatus = item.StockStatus,
|
Unit = item.Unit,
|
Remark = item.Remark
|
};
|
}
|
}
|
|
// 转换为列表
|
materielnfoStatistics = groupedData.Values.ToList();
|
|
byte[] data = exporter.ExportAsByteArray(materielnfoStatistics).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";
|
}
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"无权限，{ex.Message}");
|
}
|
}
|
}
|
}
|
