using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Linq.Expressions;
|
using System.Runtime.Intrinsics.X86;
|
using System.Security.Claims;
|
using System.Text;
|
using System.Threading.Tasks;
|
using Model.ModelDto;
|
using Model.ModelDto.DataDto;
|
using Model.ModelDto.SysDto;
|
using Model.ModelVm.DataVm;
|
using Model.ModelVm.SysVm;
|
using SqlSugar;
|
using WMS.DAL;
|
using WMS.Entity.BllAsnEntity;
|
using WMS.Entity.Context;
|
using WMS.Entity.DataEntity;
|
using WMS.Entity.SysEntity;
|
using WMS.IBLL.IDataServer;
|
|
namespace WMS.BLL.DataServer
|
{
|
public class StockServer : DbHelper<DataStock>, IStockServer
|
{
|
private static readonly SqlSugarScope Db = DataContext.Db;
|
public StockServer() : base(Db)
|
{
|
|
}
|
|
|
#region 库存明细
|
|
/// <summary>
|
/// 查询库存统计
|
/// </summary>
|
/// <param name="selectType">查询类型 0：物料信息 1：批次信息 2：质检信息 3：货主信息</param>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="ownerNo">货主编码</param>
|
/// <param name="ownerName">货主名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="status">库存状态</param>
|
/// <param name="inspectStatus">质检状态</param>
|
/// <returns></returns>
|
public async Task<List<MateDataStockDto>> GetDataStockList(string selectType, string skuNo, string skuName, string ownerNo,
|
string ownerName, string lotNo, string status, string inspectStatus)
|
{
|
Expression<Func<DataStockDetail, bool>> item = Expressionable.Create<DataStockDetail>()
|
.AndIF(!string.IsNullOrEmpty(skuNo), a => a.SkuNo.Contains(skuNo))
|
.AndIF(!string.IsNullOrEmpty(skuName), a => a.SkuName.Contains(skuName))
|
.AndIF(!string.IsNullOrEmpty(ownerNo), a => a.OwnerNo.Contains(ownerNo))
|
.AndIF(!string.IsNullOrEmpty(ownerName), a => a.OwnerName.Contains(ownerName))
|
.AndIF(!string.IsNullOrEmpty(lotNo), a => a.LotNo.Contains(lotNo))
|
.AndIF(!string.IsNullOrEmpty(status), a => a.Status == status)
|
.AndIF(!string.IsNullOrEmpty(inspectStatus), a => a.InspectStatus == inspectStatus)
|
.And(a => a.IsDel == "0")
|
.ToExpression();//注意 这一句 不能少
|
|
|
var data = Db.Queryable<DataStockDetail>().Where(item);
|
|
List<MateDataStockDto> data2;
|
switch (selectType)
|
{
|
// 按物料统计
|
case "0":
|
data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard })
|
.Select(a => new MateDataStockDto()
|
{
|
SkuNo = a.SkuNo, //物料编码
|
SkuName = a.SkuName, //物料名称
|
Standard = a.Standard, //规格
|
//LotNo = a.LotNo, //批次
|
//LotText = a.LotText, //批次描述
|
//OwnerNo = a.OwnerNo, //货主编码
|
//OwnerName = a.OwnerName, //货主名称
|
Status = status, //库存状态
|
InspectStatus = inspectStatus, //质检状态
|
Qty = SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
|
LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
|
FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
|
InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty)
|
}).ToListAsync();
|
break;
|
|
// 按批次统计
|
case "1":
|
data2 = await data.GroupBy(a => new { a.LotNo, a.LotText, a.SkuNo, a.SkuName, a.Standard })
|
.Select(a => new MateDataStockDto()
|
{
|
SkuNo = a.SkuNo, //物料编码
|
SkuName = a.SkuName, //物料名称
|
Standard = a.Standard, //规格
|
LotNo = a.LotNo, //批次
|
LotText = a.LotText, //批次描述
|
//OwnerNo = a.OwnerNo, //货主编码
|
//OwnerName = a.OwnerName, //货主名称
|
Status = status, //库存状态
|
InspectStatus = inspectStatus, //质检状态
|
Qty = SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
|
LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
|
FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
|
InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty)
|
}).ToListAsync();
|
break;
|
|
// 按质检状态统计
|
case "2":
|
data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard,a.InspectStatus})
|
.Select(a => new MateDataStockDto()
|
{
|
SkuNo = a.SkuNo, //物料编码
|
SkuName = a.SkuName, //物料名称
|
Standard = a.Standard, //规格
|
//LotNo = a.LotNo, //批次
|
//LotText = a.LotText, //批次描述
|
//OwnerNo = a.OwnerNo, //货主编码
|
//OwnerName = a.OwnerName, //货主名称
|
Status = status, //库存状态
|
InspectStatus = a.InspectStatus, //质检状态
|
Qty = SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
|
LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
|
FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
|
InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
|
}).ToListAsync();
|
break;
|
|
// 按货主统计
|
case "3":
|
data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard, a.OwnerNo, a.OwnerName })
|
.Select(a => new MateDataStockDto()
|
{
|
SkuNo = a.SkuNo, //物料编码
|
SkuName = a.SkuName, //物料名称
|
Standard = a.Standard, //规格
|
//LotNo = a.LotNo, //批次
|
//LotText = a.LotText, //批次描述
|
OwnerNo = a.OwnerNo, //货主编码
|
OwnerName = a.OwnerName, //货主名称
|
Status = status, //库存状态
|
InspectStatus = inspectStatus, //质检状态
|
Qty = SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
|
LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
|
FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
|
InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
|
}).ToListAsync();
|
break;
|
|
// 默认统计（物料 批次 质检 库存状态 货主)
|
default:
|
data2 = await data.GroupBy(a => new { a.SkuNo, a.SkuName, a.Standard ,a.LotNo, a.LotText, a.OwnerNo, a.OwnerName , a.InspectStatus })
|
.Select(a => new MateDataStockDto()
|
{
|
SkuNo = a.SkuNo, //物料编码
|
SkuName = a.SkuName, //物料名称
|
Standard = a.Standard, //规格
|
LotNo = a.LotNo, //批次
|
LotText = a.LotText, //批次描述
|
OwnerNo = a.OwnerNo, //货主编码
|
OwnerName = a.OwnerName, //货主名称
|
Status = status, //库存状态
|
InspectStatus = a.InspectStatus, //质检状态
|
Qty = SqlFunc.AggregateSumNoNull(a.Qty), //库存数量
|
LockQty = SqlFunc.AggregateSumNoNull(a.LockQty), //锁定数量
|
FrozenQty = SqlFunc.AggregateSumNoNull(a.LockQty),
|
InspectQty = SqlFunc.AggregateSumNoNull(a.InspectQty),
|
}).ToListAsync();
|
break;
|
}
|
|
return data2;
|
}
|
|
/// <summary>
|
/// 获取库存明细
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="locatNo">储位地址</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="status">库存状态</param>
|
/// <param name="inspectStatus">质检状态</param>
|
/// <returns></returns>
|
public List<StockDetailDto> GetInventoryList1(string skuNo, string skuName, string lotNo, string locatNo, string palletNo,
|
string status, string inspectStatus, string ownerNo, string ownerName, string startTime, string endTime)
|
{
|
string str = "select detail.*,house.WareHouseNo + '-' + house.WareHouseName as WareHouseName," +
|
"roadway.RoadwayNo + '-' + roadway.RoadwayName as RoadwayName,area.AreaNo + '-' + area.AreaName as AreaName " +
|
"from DataStockDetail detail " +
|
"left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo " +
|
"left join SysWareHouse house on detail.WareHouseNo = house.WareHouseNo " +
|
"left join SysStorageArea area on detail.AreaNo = area.AreaNo " +
|
"Where detail.IsDel = @isdel";
|
//判断物料编码是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and detail.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and detail.SkuName like @skuname";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and detail.LotNo like @lotno";
|
}
|
//判断储位地址是否为空
|
if (!string.IsNullOrEmpty(locatNo))
|
{
|
str += " and detail.LocatNo like @locatno";
|
}
|
//判断托盘条码是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
str += " and detail.PalletNo like @palletno";
|
}
|
//判断库存状态是否为空
|
if (!string.IsNullOrEmpty(status))
|
{
|
str += " and detail.Status = @status";
|
}
|
//判断质检状态是否为空
|
if (!string.IsNullOrEmpty(inspectStatus))
|
{
|
str += " and detail.InspectStatus = @inspectstatus";
|
}
|
//判断货主编码是否为空
|
if (!string.IsNullOrEmpty(ownerNo))
|
{
|
str += " and detail.OwnerNo like @ownerNo";
|
}
|
//判断货主名称是否为空
|
if (!string.IsNullOrEmpty(ownerName))
|
{
|
str += " and detail.OwnerName like @ownerName";
|
}
|
if (!string.IsNullOrEmpty(startTime))
|
{
|
str += $" and detail.CompleteTime >= '{startTime}'";
|
}
|
if (!string.IsNullOrEmpty(endTime))
|
{
|
endTime = Convert.ToDateTime(endTime).AddDays(1).ToString();
|
str += $" and detail.CompleteTime < '{endTime}'";
|
}
|
//排序
|
str += " order by detail.SkuNo,detail.PalletNo,detail.LotNo";
|
List<StockDetailDto> stockDetailsList = Db.Ado.SqlQuery<StockDetailDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料编码
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次
|
locatno = "%" + locatNo + "%", //储位地址
|
palletno = "%" + palletNo + "%", //托盘条码
|
status = status, //库存状态
|
inspectstatus = inspectStatus, //质检状态
|
ownerNo = "%" + ownerNo + "%", //货主编码
|
ownerName = "%" + ownerName + "%" //货主名称
|
});
|
return stockDetailsList;
|
}
|
|
#endregion
|
|
#region 低库存预警
|
|
/// <summary>
|
/// 低库存预警
|
/// </summary>
|
/// <param name="skuNo">物料号</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="type">物料类型</param>
|
/// <returns></returns>
|
public List<MateDataStockDto> GetInventoryWarning(string skuNo, string skuName, string lotNo, string type)
|
{
|
string str = "select mate.Id,mate.SkuNo,mate.SkuName,mate.Standard,mate.Type,stock.Qty,stock.LockQty,stock.FrozenQty,stock.Qty - stock.LockQty - Stock.FrozenQty ResidueQty,stock.LotNo from SysMaterials mate right join DataStock stock on mate.SkuNo = stock.SkuNo Where stock.IsDel = @isdel and stock.Qty - stock.LockQty - Stock.FrozenQty <= mate.LowInventory";
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and stock.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and stock.SkuName like @skuname";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and stock.LotNo like @lotno";
|
}
|
//判断物料类型是否为空
|
if (!string.IsNullOrEmpty(type))
|
{
|
str += " and mate.Type = @type";
|
}
|
//排序
|
str += " order by stock.SkuNo";
|
List<MateDataStockDto> stockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次
|
type, //物料类型
|
});
|
return stockList;
|
}
|
|
#endregion
|
|
#region 出入库报表
|
|
/// <summary>
|
/// 获取出入库总量
|
/// </summary>
|
/// <param name="skuNo">物料号</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="startTime">开始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns></returns>
|
public List<TotalRecordDto> GetTotalRecord(string skuNo, string skuName, string lotNo, string startTime, string endTime)
|
{
|
//入库
|
string rstr = "select LotNo,SkuNo,SkuName,SUM(Qty) RQty,SUM(FactQty) RFactQty,SUM(CompleteQty) RCompleteQty from BllArrivalNoticeDetail where IsDel = '0' ";
|
//出库
|
string cstr = "select LotNo,SkuNo,SkuName,SUM(Qty) CQty,SUM(FactQty) CFactQty,SUM(CompleteQty) CompleteQty,SUM(AllotQty) CAllotQty from BllExportNoticeDetail where IsDel = '0' ";
|
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
rstr += " and SkuNo like @skuno";
|
cstr += " and SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
rstr += " and SkuName like @skuname";
|
cstr += " and SkuName like @skuname";
|
}
|
//判断批次号是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
rstr += " and LotNo like @lotno";
|
cstr += " and LotNo like @lotno";
|
}
|
// liudl 时间不准确 暂时注释。
|
if (!string.IsNullOrWhiteSpace(startTime))
|
{
|
rstr += " and UpdateTime >= @startTime";
|
cstr += " and UpdateTime >= @startTime";
|
}
|
if (!string.IsNullOrWhiteSpace(endTime))
|
{
|
endTime = DateTime.Parse(endTime).AddDays(1).ToString();
|
rstr += " and UpdateTime < @endTime";
|
cstr += " and UpdateTime < @endTime";
|
}
|
|
rstr += " group by LotNo,SkuNo,SkuName";
|
cstr += " group by LotNo,SkuNo,SkuName";
|
//入库
|
List<TotalRecordDto> totalHListData = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
|
{
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
startTime, //开始时间
|
endTime //结束时间
|
|
});
|
List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
|
{
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
startTime, //开始时间
|
endTime //结束时间
|
});
|
|
//合并数据
|
int a = 0;
|
foreach (var h in totalHListData)
|
{
|
if (a >= totalCListData.Count)
|
{
|
continue;
|
}
|
foreach (var c in totalCListData)
|
{
|
//判断出库是否拥有相同批次 且物料相同
|
if (h.LotNo == c.LotNo || h.LotNo.Contains(c.LotNo) && h.SkuNo == c.SkuNo && h.SkuName == c.SkuName)
|
{
|
h.CQty = c.CQty; //出库数量
|
h.CAllotQty = c.CAllotQty; //分配数量
|
h.CFactQty = c.CFactQty; //下架数量
|
h.CompleteQty = c.CompleteQty; //拣货数量
|
//h.SONo = c.SONo; //出库单号
|
|
a += 1;
|
|
break;
|
}
|
}
|
}
|
|
return totalHListData;
|
}
|
|
/// <summary>
|
/// 获取出入库记录
|
/// </summary>
|
/// <param name="skuNo">物料号</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="palletNo">托盘</param>
|
/// <param name="startTime">开始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns></returns>
|
public List<TotalRecordDto> GetDetailedRecord(string skuNo, string skuName, string lotNo, string palletNo,
|
string startTime, string endTime)
|
{
|
//入库
|
string rstr = "select notice.ASNNo,bind.LotNo,notice.SkuNo,notice.SkuName,bind.PalletNo,bind.Qty RQty,bind.Qty RFactQty,bind.Qty RCompleteQty,bind.CompleteTime,notice.LotText,notice.SupplierLot,bind.CompleteTime from BllPalletBind bind left join BllArrivalNoticeDetail notice on bind.ASNDetailNo = notice.Id where bind.IsDel = '0' and notice.IsDel = '0' ";
|
//出库
|
string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo," +
|
"allot.UpdateTime CCreateTime,allot.Qty CQty,allot.Qty CAllotQty,Allot.Qty CFactQty,Allot.CompleteQty CompleteQty," +
|
"allot.LotText,allot.SupplierLot " +
|
"from BllExportAllot allot left join BllExportNoticeDetail notice on allot.SODetailNo = notice.Id " +
|
"where allot.IsDel = '0' and notice.IsDel = '0' ";
|
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
rstr += " and notice.SkuNo like @skuno";
|
cstr += " and allot.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
rstr += " and notice.SkuName like @skuname";
|
cstr += " and allot.SkuName like @skuname";
|
}
|
//判断批次号是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
rstr += " and notice.LotNo like @lotno";
|
cstr += " and allot.LotNo like @lotno";
|
}
|
//判断托盘是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
rstr += " and bind.PalletNo like @palletno";
|
cstr += " and allot.PalletNo like @palletno";
|
}
|
if (!string.IsNullOrWhiteSpace(startTime))
|
{
|
rstr += " and bind.CompleteTime >= @startTime";
|
cstr += " and allot.UpdateTime >= @startTime";
|
}
|
if (!string.IsNullOrWhiteSpace(endTime))
|
{
|
endTime = DateTime.Parse(endTime).AddDays(1).ToString();
|
rstr += " and bind.CompleteTime < @endTime";
|
cstr += " and allot.UpdateTime < @endTime";
|
}
|
|
//rstr += " group by bind.LotNo,bind.SkuNo,bind.SkuName";
|
//cstr += " group by LotNo,SkuNo,SkuName";
|
//入库
|
List<TotalRecordDto> totalRList = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
palletno = "%" + palletNo + "%", //托盘号
|
startTime = startTime,
|
endTime = endTime
|
});
|
//出库
|
List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
palletno = "%" + palletNo + "%", //托盘号
|
startTime = startTime,
|
endTime = endTime
|
});
|
|
//合并
|
foreach (var c in totalCList)
|
{
|
int i = 0;
|
foreach (var r in totalRList)
|
{
|
i += 1;
|
//判断入库中是否存在出库中的批次物料托盘
|
if (r.PalletNo == c.PalletNo && r.LotNo == c.LotNo && r.SkuNo == c.SkuNo && r.SkuName == c.SkuName)
|
{
|
if (r.SONo != null)
|
{
|
if (r.SONo != c.SONo)
|
{
|
totalRList.Add(c);
|
}
|
}
|
if (c.CTaskNo != "" && r.SONo == null || r.SONo == c.SONo)
|
{
|
r.SONo = c.SONo; //出库单号
|
r.CQty += c.CQty; //出库数量
|
r.CAllotQty += c.CAllotQty; //分配数量
|
r.CFactQty += c.CFactQty; //下架数量
|
r.CompleteQty += c.CompleteQty; //拣货数量
|
r.CCreateTime = c.CCreateTime; //拣货时间
|
}
|
if (r.CompleteTime == null)
|
{
|
r.RCompleteQty = 0;
|
}
|
break;
|
}
|
if (i == totalRList.Count)
|
{
|
totalRList.Add(c);
|
break;
|
}
|
}
|
}
|
if (totalCList.Count == 0)
|
{
|
foreach (var r in totalRList)
|
{
|
if (r.CompleteTime == null)
|
{
|
r.RCompleteQty = 0;
|
continue;
|
}
|
}
|
}
|
|
return totalRList;
|
}
|
|
#endregion
|
|
#region 年度报表
|
|
/// <summary>
|
/// 获取年报表总表
|
/// </summary>
|
/// <param name="StartTime"></param>
|
/// <param name="EndTime"></param>
|
/// <returns></returns>
|
public List<SysMaterials> GetAssSoInfo(string StartTime, string EndTime)
|
{
|
try
|
{
|
string str = "select* from SysMaterials where SkuNo in (select a.SkuNo from BllArrivalNoticeDetail a " +
|
"left join BllExportNoticeDetail b on a.SkuNo = b.SkuNo";
|
|
//判断开始时间是否为空
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
str += $" and a.CompleteTime >= '{StartTime}' and b.UpdateTime >= '{StartTime}'";
|
}
|
//判断结束时间是否为空
|
if (!string.IsNullOrWhiteSpace(EndTime))
|
{
|
EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
|
str += $" and a.CompleteTime < '{EndTime}' and b.UpdateTime < '{EndTime}'";
|
}
|
|
str += " group by a.SkuNo,b.SkuNo)";
|
|
var sku = Db.Ado.SqlQuery<SysMaterials>(str);
|
return sku;
|
}
|
catch (Exception ex)
|
{
|
|
throw new Exception(ex.Message);
|
}
|
}
|
|
/// <summary>
|
/// 获取年报表明细
|
/// </summary>
|
/// <param name="skuNo"></param>
|
/// <param name="StartTime"></param>
|
/// <param name="EndTime"></param>
|
/// <returns></returns>
|
public List<AssSoDateStockInfoDto> GetAssSoInfoDetail(string skuNo, string StartTime, string EndTime)
|
{
|
try
|
{
|
//用于合并数据
|
List<AssSoDateStockInfoDto> infolist = new List<AssSoDateStockInfoDto>();
|
//获取开始时间当天的结存数量
|
//DataStockInfo stockInfo = new DataStockInfo();
|
//获取物料理论重量
|
string weightstr = $"select Weight from SysMaterials where SkuNo = '{skuNo}'";
|
var weight = Db.Ado.SqlQuerySingle<decimal>(weightstr);
|
|
//判断开始时间是否为空
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
//string str = $"select * from DataStockInfo where IsDel = '0' and CONVERT(date,CreateTime) = '{StartTime}'";
|
//获取当天库存信息
|
//stockInfo = Db.Ado.SqlQuerySingle<DataStockInfo>(str);
|
}
|
//判断结束时间是否为空
|
if (!string.IsNullOrWhiteSpace(EndTime))
|
{
|
EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
|
}
|
decimal countjie = 0; //统计结存数量
|
////判断库存信息是否为空
|
//if (stockInfo != null)
|
//{
|
// countjie = stockInfo.BalanceQty; //统计结存数量
|
//}
|
//else if (stockInfo == null)
|
//{
|
//判断开始时间是否为空
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
//string StartTimelin = (Convert.ToDateTime(StartTime).AddDays(1)).ToString();
|
//获取出库数量
|
string str = $"select SUM(CompleteQty) as CompleteQtyc from BllExportNoticeDetail where IsDel = '0' and SkuNo = '{skuNo}' and Status != '0' and CreateTime < '{StartTime}'";
|
string CompleteQtyc = Db.Ado.SqlQuerySingle<string>(str);
|
//获取入库数量
|
str = $"select SUM(CompleteQty) as CompleteQtyr from BllArrivalNoticeDetail where IsDel = '0' and SkuNo = '{skuNo}' and Status != '0' and CreateTime < '{StartTime}'";
|
string CompleteQtyr = Db.Ado.SqlQuerySingle<string>(str);
|
|
//计算结存数量
|
countjie = int.Parse(CompleteQtyr) - int.Parse(CompleteQtyc);
|
|
}
|
|
//}
|
decimal yeara = 0; //年入库
|
decimal years = 0; //年出库
|
decimal montha = 0; //月入库
|
decimal months = 0; //月出库
|
DateTime createTime = DateTime.Now.AddYears(-1000); //时间
|
|
//获取有多少年
|
string yearstr = "select DATEPART(YEAR, CreateTime) SkuName from BllArrivalNoticeDetail where IsDel = '0' and Status = '2' ";
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
yearstr += $" and CreateTime >= '{StartTime}'";
|
}
|
if (!string.IsNullOrWhiteSpace(EndTime))
|
{
|
|
yearstr += $" and CreateTime < '{EndTime}'";
|
}
|
yearstr += "group by DATEPART(YEAR, CreateTime)";
|
var yearlist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(yearstr);
|
if (!string.IsNullOrWhiteSpace(StartTime) && !string.IsNullOrWhiteSpace(EndTime))
|
{
|
//判断开始时间年与结束时间年是否相等
|
var startyear = StartTime.Split('-');
|
var endyear = EndTime.Split("-");
|
}
|
foreach (var yearcount in yearlist)
|
{
|
//获取当前物料每年入库单据明细
|
string ruyearstr = $"select ASNNo,SkuNo,SkuName,CompleteQty as ASNQty,0 as ASNSOQty,CONVERT(date,CreateTime) as NoticeDateTime,DATEPART(MONTH,CreateTime) as month,CreateTime from BllArrivalNoticeDetail where IsDel = '0' and Status != '0' and SkuNo = '{skuNo}' and DATEPART(YEAR, CreateTime) = '{yearcount.SkuName}'";
|
//获取当前物料每年出库单据明细
|
string chuyearstr = $"select SONo,SkuNo,SkuName,CompleteQty as SOQty,0 as ASNSOQty,CONVERT(date,CreateTime) as NoticeDateTime,DATEPART(MONTH,CreateTime) as month,CreateTime from BllExportNoticeDetail where IsDel = '0' and Status != '0' and SkuNo = '{skuNo}' and DATEPART(YEAR, CreateTime) = '{yearcount.SkuName}'";
|
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
ruyearstr += $" and CreateTime >= '{StartTime}'";
|
chuyearstr += $" and CreateTime >= '{StartTime}'";
|
}
|
if (!string.IsNullOrWhiteSpace(EndTime))
|
{
|
//EndTime = (Convert.ToDateTime(EndTime).AddDays(1)).ToString();
|
ruyearstr += $" and CreateTime < '{EndTime}'";
|
chuyearstr += $" and CreateTime < '{EndTime}'";
|
}
|
|
ruyearstr += " order by CreateTime";
|
chuyearstr += " order by CreateTime";
|
//每年入库
|
List<AssSoDateStockInfoDto> ruinfolist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(ruyearstr);
|
//每年出库
|
List<AssSoDateStockInfoDto> chuinfolist = Db.Ado.SqlQuery<AssSoDateStockInfoDto>(chuyearstr);
|
|
//循环12个月
|
for (int i = 1; i <= 12; i++)
|
{
|
//临时入
|
List<AssSoDateStockInfoDto> linshiru = new List<AssSoDateStockInfoDto>();
|
//临时出
|
List<AssSoDateStockInfoDto> linshichu = new List<AssSoDateStockInfoDto>();
|
|
if (ruinfolist.Count > 0)
|
{
|
linshiru = ruinfolist.Where(a => a.month == i.ToString()).ToList(); //获取当月入库信息
|
}
|
if (chuinfolist.Count > 0)
|
{
|
linshichu = chuinfolist.Where(a => a.month == i.ToString()).ToList(); //获取当月出库信息
|
}
|
if (linshichu.Count > 0)
|
{
|
foreach (var h in linshichu)
|
{
|
linshiru.Add(h);
|
}
|
}
|
var data = linshiru.OrderBy(a => a.CreateTime).ToList();
|
|
//判断是否存在当月入库信息
|
if (linshiru.Count > 0)
|
{
|
|
foreach (var item in data)
|
{
|
item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
|
//总计结存数量
|
if (item.ASNNo != null)
|
{
|
//入库
|
countjie += item.ASNQty;
|
montha += item.ASNQty; //月入库
|
yeara += item.ASNQty; //年入库
|
}
|
else if (item.SONo != null)
|
{
|
//出库
|
countjie -= item.SOQty;
|
months += item.SOQty; //月出库
|
years += item.SOQty; //年出库
|
}
|
item.ASNSOQty = countjie; //结存数量
|
item.Weight = weight; //重量
|
infolist.Add(item);
|
|
if (createTime < item.CreateTime)
|
{
|
createTime = item.CreateTime; //时间
|
}
|
}
|
}
|
//判断是否存在当月出库信息
|
//if (linshichu.Count > 0)
|
//{
|
// //出库
|
// foreach (var item in linshichu)
|
// {
|
// item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
|
// countjie -= item.SOQty; //总计结存数量
|
// item.ASNSOQty = countjie; //结存数量
|
// item.Weight = weight; //重量
|
// infolist.Add(item);
|
// months += item.SOQty; //月出库
|
// years += item.SOQty; //年出库
|
// if (createTime < item.CreateTime)
|
// {
|
// createTime = item.CreateTime; //时间
|
// }
|
// }
|
//}
|
|
//判断是否有月出入库
|
if (linshiru.Count > 0 || linshichu.Count > 0)
|
{
|
//月信息
|
var addmonthinfo = new AssSoDateStockInfoDto()
|
{
|
ASNNo = "", // 入库单据
|
SONo = "", // 出库单据
|
|
SkuNo = "", // 物料编码
|
SkuName = "", // 物料名称
|
|
NoticeType = "本月累计", //统计类别
|
|
ASNQty = montha, // 入库数量
|
SOQty = months, // 出库数量
|
ASNSOQty = countjie, // 结存数量
|
|
Weight = weight, // 理论重量
|
|
NoticeDateTime = yearcount.SkuName + "年" + i + "月", // 单据时间
|
CreateTime = createTime, //时间
|
};
|
infolist.Add(addmonthinfo);
|
}
|
montha = 0; //月入库
|
months = 0; //月出库
|
}
|
//判断是否有年出入库
|
if (ruinfolist.Count > 0 || chuinfolist.Count > 0)
|
{
|
//年信息
|
var addyearinfo = new AssSoDateStockInfoDto()
|
{
|
ASNNo = "", // 入库单据
|
SONo = "", // 出库单据
|
|
SkuNo = "", // 物料编码
|
SkuName = "", // 物料名称
|
|
NoticeType = "本年累计", //统计类别
|
|
ASNQty = yeara, // 入库数量
|
SOQty = years, // 出库数量
|
ASNSOQty = countjie, // 结存数量
|
|
Weight = weight, // 理论重量
|
|
NoticeDateTime = yearcount.SkuName + "年", // 单据时间
|
CreateTime = createTime, //时间
|
};
|
infolist.Add(addyearinfo);
|
}
|
yeara = 0; //年入库
|
years = 0; //年出库
|
}
|
|
infolist = infolist.OrderBy(x => x.CreateTime).ToList();
|
return infolist;
|
|
}
|
catch (Exception ex)
|
{
|
|
throw new Exception(ex.Message);
|
}
|
}
|
|
|
#endregion
|
|
#region 导出库存
|
/// <summary>
|
/// 导出库存总量
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <returns></returns>
|
public List<MateDataStockDto> GetDataStockListDaoChu(string skuNo, string skuName)
|
{
|
string str = "select stock.SkuNo,stock.SkuName,stock.Standard,stock.Qty,stock.LockQty,stock.FrozenQty,(mate.Weight * stock.Qty) WeightSum from DataStock stock left join SysMaterials mate on stock.SkuNo = mate.SkuNo Where stock.IsDel = @isdel";
|
//判断物料编码是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and stock.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and stock.SkuName like @skuname";
|
}
|
//排序
|
str += " order by stock.SkuNo";
|
List<MateDataStockDto> StockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料编码
|
skuname = "%" + skuName + "%" //物料名称
|
});
|
|
//库存总量
|
List<MateDataStockDto> StockListDto = new List<MateDataStockDto>();
|
|
foreach (var item in StockList)
|
{
|
//判断库存总量是否拥有物料
|
if (StockListDto.Count > 0)
|
{
|
int i = 0;
|
//foreach循环库存总量
|
foreach (var dto in StockListDto)
|
{
|
//判断物料是否相同
|
if (dto.SkuNo == item.SkuNo)
|
{
|
dto.Qty = Convert.ToInt32(dto.Qty) + Convert.ToInt32(item.Qty);
|
dto.FrozenQty = Convert.ToInt32(dto.FrozenQty) + Convert.ToInt32(item.FrozenQty);
|
dto.LockQty = Convert.ToInt32(dto.LockQty) + Convert.ToInt32(item.LockQty);
|
dto.ResidueQty = (Convert.ToInt32(dto.ResidueQty) + Convert.ToInt32(item.ResidueQty)).ToString();
|
break;
|
}
|
i += 1;
|
//判断已有相同物料
|
if (i == StockListDto.Count)
|
{
|
StockListDto.Add(item);
|
break;
|
}
|
}
|
}
|
else
|
{
|
StockListDto.Add(item);
|
continue;
|
}
|
}
|
foreach (var item in StockListDto)
|
{
|
//物料编码，加上单引号是防止导出到excel自动把前面的0给去掉
|
if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
|
{
|
item.SkuNo = $"'{item.SkuNo}";
|
}
|
}
|
|
return StockListDto;
|
}
|
|
/// <summary>
|
/// 导出库存明细
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="locatNo">储位地址</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="status">库存状态</param>
|
/// <param name="inspectStatus">质检状态</param>
|
/// <param name="startTime">开始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns></returns>
|
public List<StockDetailDto> GetInventoryList1DaoChu(string skuNo, string skuName, string lotNo, string locatNo, string palletNo, string status, string inspectStatus, string startTime, string endTime)
|
{
|
string str = "select detail.*,house.WareHouseName as WareHouseName,roadway.RoadwayName as RoadwayName from DataStockDetail detail left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo left join SysWareHouse house on detail.WareHouseNo = house.WareHouseNo Where detail.IsDel = @isdel";
|
//判断物料编码是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and detail.SkuNo = @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and detail.SkuName = @skuname";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and detail.LotNo = @lotno";
|
}
|
//判断储位地址是否为空
|
if (!string.IsNullOrEmpty(locatNo))
|
{
|
str += " and detail.LocatNo = @locatno";
|
}
|
//判断托盘条码是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
str += " and detail.PalletNo = @palletno";
|
}
|
//判断库存状态是否为空
|
if (!string.IsNullOrEmpty(status))
|
{
|
str += " and detail.Status = @status";
|
}
|
//判断质检状态是否为空
|
if (!string.IsNullOrEmpty(inspectStatus))
|
{
|
str += " and detail.InspectStatus = @inspectstatus";
|
}
|
if (!string.IsNullOrEmpty(startTime))
|
{
|
str += $" and detail.CompleteTime >= '{startTime}'";
|
}
|
if (!string.IsNullOrEmpty(endTime))
|
{
|
endTime = Convert.ToDateTime(endTime).AddDays(1).ToString();
|
str += $" and detail.CompleteTime < '{endTime}'";
|
}
|
//排序
|
str += " order by detail.SkuNo,detail.PalletNo,detail.LotNo";
|
List<StockDetailDto> stockDetailsList = Db.Ado.SqlQuery<StockDetailDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = skuNo, //物料编码
|
skuname = skuName, //物料名称
|
lotno = lotNo, //批次
|
locatno = locatNo, //储位地址
|
palletno = palletNo, //托盘条码
|
status = status, //库存状态
|
inspectstatus = inspectStatus //质检状态
|
});
|
foreach (var item in stockDetailsList)
|
{
|
//储位地址，加上单引号是防止导出到excel自动把前面的0给去掉
|
if (!string.IsNullOrEmpty(item.LocatNo) && item.LocatNo.Substring(0, 1) == "0")
|
{
|
item.LocatNo = $"'{item.LocatNo}";
|
}
|
//物料编码
|
if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
|
{
|
item.SkuNo = $"'{item.SkuNo}";
|
}
|
//库存状态
|
switch (item.Status)
|
{
|
case "0":
|
item.Status = "待分配";
|
break;
|
case "1":
|
item.Status = "部分分配";
|
break;
|
case "2":
|
item.Status = "已分配";
|
break;
|
case "3":
|
item.Status = "盘点锁定";
|
break;
|
case "4":
|
item.Status = "移库锁定";
|
break;
|
default:
|
break;
|
}
|
//质检状态
|
switch (item.InspectStatus)
|
{
|
case "0":
|
item.InspectStatus = "待检验";
|
break;
|
case "1":
|
item.InspectStatus = "检验合格";
|
break;
|
case "2":
|
item.InspectStatus = "不合格";
|
break;
|
case "4":
|
item.InspectStatus = "放置期";
|
break;
|
default:
|
break;
|
}
|
|
}
|
return stockDetailsList;
|
}
|
#endregion
|
}
|
}
|
