using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Linq.Expressions;
|
using System.Net.WebSockets;
|
using System.Reflection;
|
using System.Security.Claims;
|
using System.Text;
|
using System.Threading.Tasks;
|
using Model.ModelDto;
|
using Model.ModelDto.DataDto;
|
using Model.ModelVm.DataVm;
|
using Model.ModelVm.SysVm;
|
using SqlSugar;
|
using WMS.DAL;
|
using WMS.Entity.BllAsnEntity;
|
using WMS.Entity.BllSoEntity;
|
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="skuNo">物料编码</param>
|
/// <returns></returns>
|
public List<MateDataStockDto> GetDataStockList(string skuNo)
|
{
|
string str = "select stock.SkuNo,stock.SkuName,stock.Standard,stock.Qty,stock.LockQty,stock.FrozenQty,CONVERT(decimal(18,4),(mate.Weight * stock.Qty)) WeightSum from DataStock stock left join SysMaterials mate on stock.SkuNo = mate.SkuNo Where stock.IsDel = 0 and mate.IsDel = 0";
|
//判断物料编码是否为空
|
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)).ToString();
|
dto.FrozenQty = (Convert.ToInt32(dto.FrozenQty) + Convert.ToInt32(item.FrozenQty)).ToString();
|
dto.LockQty = (Convert.ToInt32(dto.LockQty) + Convert.ToInt32(item.LockQty)).ToString();
|
dto.ResidueQty = (Convert.ToInt32(dto.ResidueQty) + Convert.ToInt32(item.ResidueQty)).ToString();
|
dto.WeightSum = Convert.ToDecimal(dto.WeightSum) + Convert.ToDecimal(item.WeightSum);
|
break;
|
}
|
i += 1;
|
//判断已有相同物料
|
if (i == StockListDto.Count)
|
{
|
StockListDto.Add(item);
|
break;
|
}
|
}
|
}
|
else
|
{
|
StockListDto.Add(item);
|
continue;
|
}
|
}
|
|
return StockListDto;
|
}
|
|
/// <summary>
|
/// 获取库存明细
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="type">类型</param>
|
/// <param name="locatNo">储位地址</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="status">库存状态</param>
|
/// <param name="inspectStatus">质检状态</param>
|
/// <param name="PalletType">是否字母拖</param>
|
/// <param name="IsBale">是否缠膜</param>
|
/// <param name="IsBelt">是否打包</param>
|
/// <returns></returns>
|
public List<MateDataStockDto> GetInventoryList1(string skuNo, string lotNo, string type, string locatNo, string palletNo, string status, string inspectStatus, string PalletType, string IsBale, string IsBelt)
|
{
|
string str = "select detail.Id,detail.PalletNo,detail.LocatNo,roadway.RoadwayName RoadwayNo,detail.SkuNo,detail.SkuName,detail.Standard,detail.LotNo,detail.LotText,detail.Qty,(mate.Weight * detail.Qty) WeightSum,detail.LockQty,detail.Status,detail.InspectStatus,detail.CompleteTime,detail.IsBale,detail.IsBelt,detail.PalletType,detail.Demo from DataStockDetail detail left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo left join SysMaterials mate on detail.SkuNo = mate.SkuNo 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(type))
|
{
|
str += " and mate.Type = @type";
|
}
|
//判断储位地址是否为空
|
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.IsNullOrWhiteSpace(PalletType))
|
{
|
//判断零托标记是否为1
|
if (PalletType == "1")
|
{
|
str += " and detail.PalletType != '0'";
|
}
|
else
|
{
|
str += " and detail.PalletType = '0'";
|
}
|
}
|
//判断缠膜状态是否为空
|
if (!string.IsNullOrWhiteSpace(IsBale))
|
{
|
if (IsBale == "1")
|
{
|
str += " and detail.IsBale = '1'";
|
}
|
else
|
{
|
str += " and detail.IsBale != '1'";
|
}
|
}
|
//判断打包状态是否为空
|
if (!string.IsNullOrWhiteSpace(IsBelt))
|
{
|
if (IsBelt == "1")
|
{
|
str += " and detail.IsBelt = '1'";
|
}
|
else
|
{
|
str += " and detail.IsBelt != '1'";
|
}
|
}
|
//排序
|
str += " order by detail.SkuNo,detail.PalletNo,detail.LotNo";
|
List<MateDataStockDto> stockDetailsList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = skuNo, //物料编码
|
//skuname = skuName, //物料名称
|
lotno = lotNo, //批次
|
type, //物料类型
|
locatno = locatNo, //储位地址
|
palletno = palletNo, //托盘条码
|
status, //库存状态
|
inspectstatus = inspectStatus //质检状态
|
});
|
return stockDetailsList;
|
}
|
|
/// <summary>
|
/// 获取库存明细
|
/// </summary>
|
/// <param name="Id">Id</param>
|
/// <returns></returns>
|
public MateDataStockDto GetInventoryListById(string Id)
|
{
|
string str = "select Id,PalletNo,SkuNo,SkuName,Qty,Demo from DataStockDetail detail where IsDel = @isdel and Id = @id";
|
|
MateDataStockDto stockDetailsList = Db.Ado.SqlQuerySingle<MateDataStockDto>(str, new
|
{
|
isdel = "0", //是否删除
|
id = Id, //Id
|
});
|
return stockDetailsList;
|
}
|
|
/// <summary>
|
/// 增加库存备注
|
/// </summary>
|
/// <param name="model">库存model</param>
|
/// <returns></returns>
|
public async Task<int> EditDetailDemo(DataStockDetail model)
|
{
|
string str = "update DataStockDetail set Demo = @demo,UpdateTime = @utime,UpdateUser = @uuser where Id = @id";
|
int i = await Db.Ado.ExecuteCommandAsync(str, new
|
{
|
demo = model.Demo, //备注
|
utime = Db.GetDate(), //修改时间
|
uuser = model.UpdateUser, //修改人
|
id = model.Id, //库存id
|
});
|
return i;
|
}
|
|
/// <summary>
|
/// 获取库存明细
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="status">库存状态</param>
|
/// <param name="page"></param>
|
/// <param name="limit"></param>
|
/// <param name="count"></param>
|
/// <returns></returns>
|
public List<DataStockDetail> GetOutSideStockList(string skuNo, string skuName, string lotNo, string palletNo, string status, int page, int limit, out int count)
|
{
|
try
|
{
|
Expression<Func<DataStockDetail, bool>> item = Expressionable.Create<DataStockDetail>()
|
.AndIF(!string.IsNullOrWhiteSpace(skuNo), it => it.SkuNo.Contains(skuNo.Trim()))
|
.AndIF(!string.IsNullOrWhiteSpace(skuName), it => it.SkuName.Contains(skuName.Trim()))
|
.AndIF(!string.IsNullOrWhiteSpace(status), it => it.Status == status)
|
.AndIF(!string.IsNullOrWhiteSpace(lotNo), it => it.LotNo.Contains(lotNo.Trim()))
|
.AndIF(!string.IsNullOrWhiteSpace(palletNo), it => it.PalletNo.Contains(palletNo.Trim()))
|
.And(it => string.IsNullOrWhiteSpace(it.LocatNo))
|
.ToExpression();//注意 这一句 不能少
|
var total = 0;
|
var data = Db.Queryable<DataStockDetail>().Where(item).ToOffsetPage(page, limit, ref total);
|
|
count = total;
|
|
return data;
|
}
|
catch (Exception e)
|
{
|
throw new Exception(e.Message);
|
}
|
}
|
|
|
#endregion
|
|
#region 托盘明细
|
|
/// <summary>
|
/// 获取托盘明细
|
/// </summary>
|
/// <param name="skuNo">物料号</param>
|
/// <param name="palletNo">托盘号</param>
|
/// <param name="lotNo">批次号</param>
|
/// <param name="boxNo">箱码</param>
|
/// <param name="status">箱支状态</param>
|
/// <param name="inspectMark">检验标记</param>
|
/// <param name="bitPalletMark">零托标记</param>
|
/// <param name="bitBoxMark">零箱标记</param>
|
/// <param name="inspectStatus">质量状态</param>
|
/// <returns></returns>
|
public List<DataStockDetail> GetBindList(string skuNo, string palletNo, string lotNo, string boxNo, string status, string inspectMark, string bitPalletMark, string bitBoxMark, string inspectStatus)
|
{
|
string str = "select Id,LotNo,LotText,SupplierLot,SkuNo,SkuName,Qty,LockQty,FrozenQty,AreaNo,LocatNo,PalletNo,PalletNo2,PalletNo3,InspectMark,BitPalletMark,Status from DataStockDetail where IsDel = @isdel";
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and SkuNo like @skuno";
|
}
|
//判断托盘号是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
str += " and PalletNo like @palletno";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and LotNo like @lotno";
|
}
|
//判断零托标记是否为空
|
if (!string.IsNullOrEmpty(bitPalletMark))
|
{
|
str += " and BitPalletMark = @bitpalletmark";
|
}
|
|
int i = 0;
|
//在箱码明细中获取相应数据
|
string boxstr = "select StockDetailId from DataBoxInfo Where IsDel = @isdel";
|
//判断箱码是否为空
|
if (!string.IsNullOrEmpty(boxNo))
|
{
|
boxstr += " and BoxNo like @boxno";
|
i = 1;
|
}
|
//判断箱支状态是否为空
|
if (!string.IsNullOrEmpty(status))
|
{
|
boxstr += " and Status = @status";
|
i = 1;
|
}
|
//判断检验标记是否为空
|
if (!string.IsNullOrEmpty(inspectMark))
|
{
|
//boxstr += " and InspectMark = @inspectmark";
|
str += " and InspectMark = @inspectmark";
|
i = 1;
|
}
|
//判断零箱标记是否为空
|
if (!string.IsNullOrEmpty(bitBoxMark))
|
{
|
boxstr += " and BitBoxMark = @bitboxmark";
|
i = 1;
|
}
|
//判断质量状态是否为空
|
if (!string.IsNullOrEmpty(inspectStatus))
|
{
|
boxstr += " and InspectStatus = @inspectstatus";
|
str += " and InspectStatus = @inspectstatus";
|
i = 1;
|
}
|
//将箱码数据进行排序`
|
boxstr += " order by StockDetailId";
|
//查出箱码明细中信息
|
List<int> stockDetailId = Db.Ado.SqlQuery<int>(boxstr, new
|
{
|
isdel = "0", //是否删除
|
boxno = "%" + boxNo + "%", //箱码
|
status, //箱支状态
|
inspectmark = inspectMark, //检验标记
|
bitboxmark = bitBoxMark, //零箱标记
|
inspectstatus = inspectStatus //质量状态
|
});
|
string arr = "";
|
//判断是否有查箱码信息
|
if (i == 1)
|
{
|
if (stockDetailId.Count > 0)
|
{
|
//去重
|
arr += stockDetailId[0].ToString();
|
|
for (int a = 1; a < stockDetailId.Count; a++)
|
{
|
if (stockDetailId[a] != stockDetailId[a - 1])
|
{
|
arr += ',' + stockDetailId[a].ToString();
|
}
|
}
|
}
|
if (!string.IsNullOrEmpty(arr))
|
{
|
str += $" and Id in ({arr})";
|
}
|
else
|
{
|
str += " and Id = ''";
|
}
|
}
|
|
//排序
|
str += " order by LocatNo,LotNo,SkuNo,PalletNo";
|
List<DataStockDetail> boxInforList = Db.Ado.SqlQuery<DataStockDetail>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
palletno = "%" + palletNo + "%", //托盘
|
lotno = "%" + lotNo + "%", //批次号
|
bitpalletmark = bitPalletMark, //零托标记
|
inspectmark = inspectMark, //检验标记
|
inspectstatus = inspectStatus, //质量状态
|
//stockdetailid = "(" + arr + ")" //库存明细id
|
});
|
|
return boxInforList;
|
}
|
|
/// <summary>
|
/// 获取箱码明细
|
/// </summary>
|
/// <param name="id">id</param>
|
/// <param name="skuNo">物料号</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="palletNo">托盘号</param>
|
/// <param name="lotNo">批次号</param>
|
/// <param name="boxNo">箱码</param>
|
/// <param name="status">箱支状态</param>
|
/// <param name="inspectMark">检验标记</param>
|
/// <param name="bitBoxMark">零箱标记</param>
|
/// <param name="inspectStatus">质量状态</param>
|
/// <returns></returns>
|
public List<DataBoxInfo> GetBoxInfor(string id, string skuNo, string skuName, string palletNo, string lotNo, string boxNo, string status, string inspectMark, string bitBoxMark, string inspectStatus)
|
{
|
string str = "select PalletNo,PalletNo2,PalletNo3,BoxNo,BoxNo2,BoxNo3,Status,LotNo,Qty,FullQty,SkuNo,SkuName,LotText,ProductionTime,InspectMark,BitBoxMark,InspectStatus,InspectTime from DataBoxInfo Where IsDel = @isdel";
|
//判断id是否为空
|
if (!string.IsNullOrEmpty(id))
|
{
|
str += " and StockDetailId = @id";
|
}
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and SkuName like @skuname";
|
}
|
//判断托盘号是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
str += " and PalletNo like @palletno";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and LotNo like @lotno";
|
}
|
//判断箱码是否为空
|
if (!string.IsNullOrEmpty(boxNo))
|
{
|
str += " and BoxNo like @boxno";
|
}
|
//判断箱支状态是否为空
|
if (!string.IsNullOrEmpty(status))
|
{
|
str += " and Status = @status";
|
}
|
//判断检验标记是否为空
|
if (!string.IsNullOrEmpty(inspectMark))
|
{
|
str += " and InspectMark = @inspectmark";
|
}
|
//判断零箱标记是否为空
|
if (!string.IsNullOrEmpty(bitBoxMark))
|
{
|
str += " and BitBoxMark = @bitboxmark";
|
}
|
//判断质量状态是否为空
|
if (!string.IsNullOrEmpty(inspectStatus))
|
{
|
str += " and InspectStatus = @inspectstatus";
|
}
|
//排序
|
str += " order by LotNo,PalletNo,SkuNo";
|
List<DataBoxInfo> boxInfor = Db.Ado.SqlQuery<DataBoxInfo>(str, new
|
{
|
isdel = "0", //是否删除
|
id, //id
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
palletno = "%" + palletNo + "%", //托盘
|
lotno = "%" + lotNo + "%", //批次号
|
boxno = "%" + boxNo + "%", //箱码
|
status, //箱支状态
|
inspectmark = inspectMark, //检验标记
|
bitboxmark = bitBoxMark, //零箱标记
|
inspectstatus = inspectStatus //质量状态
|
});
|
return boxInfor;
|
}
|
|
/// <summary>
|
/// 根据箱码获取箱支信息
|
/// </summary>
|
/// <param name="boxNo">箱码</param>
|
/// <param name="boxNo3">支码</param>
|
/// <returns></returns>
|
public List<DataBoxInfo> GetBoxInforByBoxNo(string boxNo, string boxNo3)
|
{
|
string str = "select PalletNo,PalletNo2,PalletNo3,BoxNo,BoxNo2,BoxNo3,Status,LotNo,Qty,FullQty,SkuNo,SkuName,LotText,ProductionTime,InspectMark,BitBoxMark,InspectStatus,InspectTime from DataBoxInfo Where IsDel = @isdel";
|
//判断箱码是否为空
|
if (!string.IsNullOrEmpty(boxNo))
|
{
|
str += " and BoxNo = @boxno";
|
}
|
//判断支码是否为空
|
if (!string.IsNullOrEmpty(boxNo3))
|
{
|
str += " and BoxNo3 like @boxno3";
|
}
|
//排序
|
str += " order by LotNo,PalletNo,SkuNo";
|
List<DataBoxInfo> boxInfor = Db.Ado.SqlQuery<DataBoxInfo>(str, new
|
{
|
isdel = "0", //是否删除
|
boxno = boxNo, //箱码
|
boxno3 = "%" + boxNo3 + "%", //支码
|
});
|
return boxInfor;
|
}
|
|
#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="palletNo">托盘号</param>
|
/// <param name="status">状态</param>
|
/// <param name="inspectMark">检验标记</param>
|
/// <param name="inspectStatus">质量状态</param>
|
/// <param name="isBale">是否裹包</param>
|
/// <param name="isBelt">是否打带</param>
|
/// <returns></returns>
|
public List<MateDataStockDto> GetEmergencyWarning(string skuNo, string skuName, string lotNo, string palletNo, string status, string inspectMark, string inspectStatus, string isBale, string isBelt)
|
{
|
string str = "select stock.Id,stock.LotNo,stock.LotText,stock.SupplierLot,stock.SkuNo,stock.SkuName,stock.Standard,stock.Qty,stock.LockQty,stock.FrozenQty,stock.LocatNo,stock.PalletNo,stock.ProductionTime,stock.ExpirationTime,stock.Status,stock.InspectMark,stock.InspectStatus,stock.IsBale,stock.IsBelt from DataStockDetail stock left join SysMaterials mate on stock.SkuNo = mate.SkuNo where stock.ExpirationTime-mate.AdventTime <= GETDATE()";
|
//判断物料号是否为空
|
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(palletNo))
|
{
|
str += " and stock.PalletNo like @palletno";
|
}
|
//判断状态是否为空
|
if (!string.IsNullOrEmpty(status))
|
{
|
str += " and stock.Status = @status";
|
}
|
//判断检验标记是否为空
|
if (!string.IsNullOrEmpty(inspectMark))
|
{
|
str += " and stock.InspectMark = @inspectmark";
|
}
|
//判断质量状态是否为空
|
if (!string.IsNullOrEmpty(inspectStatus))
|
{
|
str += " and stock.InspectStatus = @inspectstatus";
|
}
|
//判断裹包是否为空
|
if (!string.IsNullOrEmpty(isBale))
|
{
|
str += " and stock.IsBale = @isbale";
|
}
|
//判断打带是否为空
|
if (!string.IsNullOrEmpty(isBelt))
|
{
|
str += " and stock.IsBelt = @isbelt";
|
}
|
//排序
|
str += " order by stock.SkuNo,stock.LotNo";
|
List<MateDataStockDto> stockList = Db.Ado.SqlQuery<MateDataStockDto>(str, new
|
{
|
skuno = skuNo, //物料号
|
skuname = skuName, //物料名称
|
lotno = lotNo, //批次
|
palletno = palletNo, //托盘号
|
status, //状态
|
inspectmark = inspectMark, //检验标记
|
inspectstatus = inspectStatus, //质量状态
|
isbale = isBale, //是否裹包
|
isbel = isBelt //是否打带
|
});
|
return stockList;
|
}
|
|
#endregion
|
|
#region 出入库报表
|
|
/// <summary>
|
/// 获取出入库总量
|
/// </summary>
|
/// <param name="skuNo">物料号</param>
|
/// <param name="skuName">物料名称</param>
|
/// <param name="lotNo">批次</param>
|
/// <returns></returns>
|
public List<TotalRecordDto> GetTotalRecord(string skuNo, string skuName, string lotNo)
|
{
|
//入库
|
string rstr = "select LotNo,SkuNo,SkuName,SUM(Qty) RQty,SUM(FactQty) RFactQty,SUM(CompleteQty) RCompleteQty from BllArrivalNoticeDetail where IsDel = '0' and Id in (select ASNDetailNo from DataStockDetail where IsDel = '0' group by ASNDetailNo) ";
|
//出库
|
string cstr = "select LotNo,SkuNo,SkuName,SUM(Qty) CQty,SUM(FactQty) CFactQty,SUM(CompleteQty) CompleteQty,SUM(AllotQty) CAllotQty from BllExportNoticeDetail where IsDel = '0' and LotNo in (select LotNo from DataStock 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";
|
}
|
rstr += " group by LotNo,SkuNo,SkuName";
|
cstr += " group by LotNo,SkuNo,SkuName";
|
//入库
|
List<TotalRecordDto> totalHListData = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
});
|
List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
});
|
|
//合并数据
|
int a = 0;
|
foreach (var h in totalHListData)
|
{
|
if (a >= totalCListData.Count)
|
{
|
continue;
|
}
|
foreach (var c in totalCListData)
|
{
|
//判断出库是否拥有相同批次 且物料相同
|
if (h.LotNo == c.LotNo)
|
{
|
if (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>
|
/// <returns></returns>
|
public List<TotalRecordDto> GetDetailedRecord(string skuNo, string skuName, string lotNo, string palletNo)
|
{
|
//入库
|
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' and bind.LotNo in (select LotNo from DataStock where IsDel = '0') ";
|
//出库
|
string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo,allot.CreateTime 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' and notice.LotNo in (select LotNo from DataStock where 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";
|
}
|
|
//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 + "%", //托盘号
|
});
|
//出库
|
List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
lotno = "%" + lotNo + "%", //批次号
|
palletno = "%" + palletNo + "%", //托盘号
|
});
|
|
//合并
|
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 周期报表
|
|
#region 周期
|
/// <summary>
|
/// 获取周期报表
|
/// </summary>
|
/// <param name="TaskNo">任务号</param>
|
/// <param name="DataStatus">时间状态</param>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="type">出、入库</param>
|
/// <param name="page">页</param>
|
/// <param name="limit">每页多少数</param>
|
/// <param name="count">库存数量</param>
|
/// <returns></returns>
|
public List<SysMaterialsDto> GetToCycleDataStockDetails(string TaskNo, string DataStatus, string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
|
{
|
//获取当前日期
|
var data = Db.GetDate();
|
//判断时间
|
if (DataStatus == "1")
|
{
|
//日
|
data = data.AddDays(-1);
|
|
}
|
else if (DataStatus == "2")
|
{
|
//周
|
data = data.AddDays(-7);
|
}
|
else if (DataStatus == "3")
|
{
|
//月
|
data = data.AddMonths(-1);
|
}
|
else if (DataStatus == "4")
|
{
|
//年
|
data = data.AddYears(-1);
|
}
|
|
//入库
|
if (type == "1")
|
{
|
string str = $"SELECT * FROM SysMaterials WHERE IsDel = '0' "; //物料信息
|
string stra = $" and SkuNo IN (SELECT SkuNo FROM BllBoxInfo where IsDel = '0' and Status = '2' and CompleteTime >= '{data}' "; //物料信息子条件
|
string str1 = $"select SkuNo,SkuName,sum(Qty) Qty from BllBoxInfo where IsDel = '0' and CompleteTime >= '{data}' and Status = '2' "; //物料入库数量
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
stra += $" and SkuNo = '{skuNo}'";
|
str1 += $" and SkuNo = '{skuNo}'";
|
}
|
|
stra += " group by SkuNo,SkuName);";
|
str += stra;
|
str1 += " group by SkuNo,SkuName ";
|
|
var total = 0;
|
var skuinfo = Db.SqlQueryable<SysMaterialsDto>(str).ToList();
|
var qtyList = Db.SqlQueryable<BllBoxInfo>(str1).ToList();
|
foreach (var sku in skuinfo)
|
{
|
foreach (var qty in qtyList)
|
{
|
if (sku.SkuNo == qty.SkuNo)
|
{
|
sku.Qty += qty.Qty;
|
}
|
}
|
}
|
count = total;
|
|
return skuinfo;
|
}
|
|
//出库
|
else if (type == "2")
|
{
|
string str = $"SELECT * FROM SysMaterials WHERE IsDel = '0' "; //物料信息
|
string stra = $" and SkuNo IN (SELECT SkuNo FROM BllExportAllot where IsDel = '0' and Status = '5' and CreateTime >= '{data}' "; //物料信息子条件
|
string str1 = $"select SkuNo,SkuName,sum(Qty) Qty from BllExportAllot where IsDel = '0' and CreateTime >= '{data}' and Status = '5' "; //物料出库数量
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
stra += $" and SkuNo = '{skuNo}'";
|
str1 += $" and SkuNo = '{skuNo}'";
|
}
|
|
stra += " group by SkuNo,SkuName);";
|
str += stra;
|
str1 += " group by SkuNo,SkuName ";
|
|
var total = 0;
|
var skuinfo = Db.SqlQueryable<SysMaterialsDto>(str).ToList();
|
var qtyList = Db.SqlQueryable<BllBoxInfo>(str1).ToList();
|
foreach (var sku in skuinfo)
|
{
|
foreach (var qty in qtyList)
|
{
|
if (sku.SkuNo == qty.SkuNo)
|
{
|
sku.Qty += qty.Qty;
|
}
|
}
|
}
|
count = total;
|
|
return skuinfo;
|
}
|
//盘库
|
else if (type == "3")
|
{
|
string str = $"SELECT * FROM SysMaterials WHERE IsDel = '0' "; //物料信息
|
string stra = $" and SkuNo IN (SELECT SkuNo FROM BllStockCheckDetail where IsDel = '0' and Status = '4' and CreateTime >= '{data}' "; //物料信息子条件
|
string str1 = $"select SkuNo,SkuName,sum(Qty) Qty from BllStockCheckDetail where IsDel = '0' and CreateTime >= '{data}' and Status = '4' "; //物料出库数量
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
stra += $" and SkuNo = '{skuNo}'";
|
str1 += $" and SkuNo = '{skuNo}'";
|
}
|
|
stra += " group by SkuNo,SkuName);";
|
str += stra;
|
str1 += " group by SkuNo,SkuName ";
|
|
var total = 0;
|
var skuinfo = Db.SqlQueryable<SysMaterialsDto>(str).ToList();
|
var qtyList = Db.SqlQueryable<BllBoxInfo>(str1).ToList();
|
foreach (var sku in skuinfo)
|
{
|
foreach (var qty in qtyList)
|
{
|
if (sku.SkuNo == qty.SkuNo)
|
{
|
sku.Qty += qty.Qty;
|
}
|
}
|
}
|
count = total;
|
|
return skuinfo;
|
}
|
else
|
{
|
throw new Exception("异常！");
|
}
|
}
|
|
|
/// <summary>
|
/// 获取周期报表
|
/// </summary>
|
/// <param name="StartTime"></param>
|
/// <param name="EndTime"></param>
|
/// <returns></returns>
|
public List<DataStockInfoVM> GetDataStockInfo(string StartTime, string EndTime)
|
{
|
try
|
{
|
//当日信息
|
string str = "select a.*,b.Weight from DataStockInfo a left join SysMaterials b on a.SkuNo = b.SkuNo where a.IsDel = '0' ";
|
//判断开始时间是否为空
|
if (!string.IsNullOrWhiteSpace(StartTime))
|
{
|
str += $" and CONVERT(date,a.CreateTime) = '{StartTime}'";
|
}
|
else if (string.IsNullOrWhiteSpace(StartTime))
|
{
|
str += " and DateDiff(dd,a.CreateTime,getdate())=0";
|
}
|
//实例化库存信息
|
var stockinfo = Db.Ado.SqlQuery<DataStockInfoVM>(str);
|
return stockinfo;
|
|
|
#region 废弃代码
|
/*
|
//实例化库存信息
|
List<DataStockInfo> stockinfo = new List<DataStockInfo>();
|
//实例化库存期初信息
|
List<DataStockInfo> qiStockinfo = new List<DataStockInfo>();
|
//实例化库存结存信息
|
List<DataStockInfo> jieStockinfo = new List<DataStockInfo>();
|
//开始为空期初集合
|
List<DataStockInfo> startnullinfo = new List<DataStockInfo>();
|
//结束为空结存数量
|
List<DataStockInfo> endnullinfo = new List<DataStockInfo>();
|
|
//今天的年月日
|
string year = DateTime.Now.ToString("yyyy"); //年
|
string month = DateTime.Now.ToString("MM"); //月
|
string day = DateTime.Now.ToString("dd"); //日
|
//当天时间查询条件
|
string datastr = $" and (DATEPART(YEAR, CreateTime)= '{year}') and (DATEPART(MONTH, CreateTime)= '{month}') and (DATEPART(DAY, CreateTime)= '{day}') ";
|
|
#region 获取数据
|
|
//判断开始或结束时间是否为空
|
if (string.IsNullOrWhiteSpace(StartTime) && string.IsNullOrWhiteSpace(EndTime))
|
{
|
string str = "select * from DataStockInfo where IsDel = '0' " + datastr + " order by Id";
|
stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
|
}
|
//判断开始时间与结束时间是否相等
|
else if (StartTime == EndTime)
|
{
|
string str = "select * from DataStockInfo where IsDel = '0' " + datastr + " order by Id";
|
stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
|
}
|
else if (!string.IsNullOrWhiteSpace(StartTime) || !string.IsNullOrWhiteSpace(EndTime))
|
{
|
//开始时间划分
|
var start = StartTime.Split('-');
|
//结束时间划分
|
var end = EndTime.Split('-');
|
|
//获取库存信息sql
|
string str = " select SkuNo,SUM(ArrivalQty) as ArrivalQty,SUM(ExportQty) as ExportQty from DataStockInfo where IsDel = '0'";
|
//获取期初数量sql 开始时间当天
|
string qstr = "select SkuNo,SkuName,Standard,Type,ExpectedQty from DataStockInfo where IsDel = '0'";
|
//获取结存数量sql 结束时间当天
|
string jstr = "select SkuNo,SkuName,Standard,Type,BalanceQty from DataStockInfo where IsDel = '0'";
|
|
//判断开始时间不为空 结束时间为空
|
if (!string.IsNullOrWhiteSpace(StartTime) && string.IsNullOrWhiteSpace(EndTime))
|
{
|
//获取库存信息
|
str += $" and CreateTime >= '{StartTime}' group by SkuNo ";
|
stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
|
//结束为空结存数量
|
string jieqtystr = $"select SkuNo,SkuName,Standard,Type,BalanceQty from DataStockInfo where Id in (select MAX(Id) as Id from DataStockInfo where IsDel = '0' group by SkuNo)";
|
endnullinfo = Db.Ado.SqlQuery<DataStockInfo>(jieqtystr);
|
//库存信息与结存数量合并
|
foreach (var item in stockinfo)
|
{
|
//结存数量
|
foreach (var enditem in endnullinfo)
|
{
|
//判断物料编码是否相同
|
if (item.SkuNo == enditem.SkuNo)
|
{
|
item.SkuName = enditem.SkuName; //物料名称
|
item.Standard = enditem.Standard; //规格
|
item.Type = enditem.Type; //类型
|
item.BalanceQty = enditem.BalanceQty; //结存数量
|
break;
|
}
|
}
|
}
|
//获取期初信息
|
year = start[0];
|
month = start[1];
|
day = start[2];
|
qstr += datastr;
|
qiStockinfo = Db.Ado.SqlQuery<DataStockInfo>(qstr);
|
}
|
//判断开始时间为空 结束时间不为空
|
else if (string.IsNullOrWhiteSpace(StartTime) && !string.IsNullOrWhiteSpace(EndTime))
|
{
|
EndTime = end[0] + "-" + end[1] + "-" + (int.Parse(end[2]) + 1).ToString();
|
//获取库存信息
|
str += $" and CreateTime < '{EndTime}' group by SkuNo ";
|
stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
|
//结束为空期初数量
|
string qiqtystr = $"select SkuNo,SkuName,Standard,Type,ExpectedQty from DataStockInfo where Id in (select Min(Id) as Id from DataStockInfo where IsDel = '0' group by SkuNo)";
|
startnullinfo = Db.Ado.SqlQuery<DataStockInfo>(qiqtystr);
|
//库存信息与期初数量合并
|
foreach (var item in stockinfo)
|
{
|
//期初数量
|
foreach (var enditem in startnullinfo)
|
{
|
//判断物料编码是否相同
|
if (item.SkuNo == enditem.SkuNo)
|
{
|
item.SkuName = enditem.SkuName; //物料名称
|
item.Standard = enditem.Standard; //规格
|
item.Type = enditem.Type; //类型
|
item.ExpectedQty = enditem.ExpectedQty; //期初数量
|
break;
|
}
|
}
|
}
|
//获取结存信息
|
year = end[0];
|
month = end[1];
|
day = end[2];
|
jstr += datastr;
|
jieStockinfo = Db.Ado.SqlQuery<DataStockInfo>(jstr);
|
}
|
//判断开始和结束时间不为空
|
else if (!string.IsNullOrWhiteSpace(StartTime) && !string.IsNullOrWhiteSpace(EndTime))
|
{
|
EndTime = end[0] + "-" + end[1] + "-" + (int.Parse(end[2]) + 1).ToString();
|
//获取库存信息
|
str += $" and CreateTime >= '{StartTime}' and CreateTime < '{EndTime}' group by SkuNo ";
|
stockinfo = Db.Ado.SqlQuery<DataStockInfo>(str);
|
//获取期初信息
|
year = start[0];
|
month = start[1];
|
day = start[2];
|
qstr += datastr;
|
qiStockinfo = Db.Ado.SqlQuery<DataStockInfo>(qstr);
|
//获取结存信息
|
year = end[0];
|
month = end[1];
|
day = end[2];
|
jstr += datastr;
|
jieStockinfo = Db.Ado.SqlQuery<DataStockInfo>(jstr);
|
}
|
|
}
|
|
#endregion
|
|
#region 合并
|
|
//判断期初或结存集合是否为空
|
if (qiStockinfo.Count > 0 || jieStockinfo.Count > 0)
|
{
|
//库存信息
|
foreach (var item in stockinfo)
|
{
|
//判断期初是否为空
|
if (qiStockinfo.Count > 0)
|
{
|
//期初信息
|
foreach (var qitem in qiStockinfo)
|
{
|
//判断物料编码是否一致
|
if (item.SkuNo == qitem.SkuNo)
|
{
|
item.SkuName = qitem.SkuName; //物料名称
|
item.Standard = qitem.Standard; //规格
|
item.Type = qitem.Type; //类型
|
item.ExpectedQty = qitem.ExpectedQty; //期初数量
|
break;
|
}
|
}
|
}
|
//判断结存是否为空
|
if (jieStockinfo.Count > 0)
|
{
|
//结存信息
|
foreach (var jitem in jieStockinfo)
|
{
|
//判断物料编码是否一致
|
if (item.SkuNo == jitem.SkuNo)
|
{
|
item.SkuName = jitem.SkuName; //物料名称
|
item.Standard = jitem.Standard; //规格
|
item.Type = jitem.Type; //类型
|
item.BalanceQty = jitem.BalanceQty; //结存数量
|
break;
|
}
|
}
|
}
|
|
}
|
}
|
*/
|
|
#endregion
|
|
|
}
|
catch (Exception ex)
|
{
|
|
throw new Exception(ex.Message);
|
}
|
}
|
|
|
/// <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 inner 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();
|
}
|
int 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);
|
|
}
|
|
}
|
int yeara = 0; //年入库
|
int years = 0; //年出库
|
int montha = 0; //月入库
|
int 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 (linshiru.Count > 0)
|
{
|
//入库
|
foreach (var item in linshiru)
|
{
|
item.NoticeDateTime = Convert.ToDateTime(item.NoticeDateTime).ToString("yyyy-MM-dd");
|
countjie += item.ASNQty; //总计结存数量
|
item.ASNSOQty = countjie; //结存数量
|
item.Weight = weight; //重量
|
infolist.Add(item);
|
montha += item.ASNQty; //月入库
|
yeara += item.ASNQty; //年入库
|
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="lotNo">批次</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="type">出、入库</param>
|
/// <param name="page">页</param>
|
/// <param name="limit">每页多少数</param>
|
/// <param name="count">库存数量</param>
|
/// <returns></returns>
|
public List<PalletBindDto> GetToDayDataStockDetails(string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
|
{
|
//获取前一天现在的日期
|
var data = Db.GetDate().AddDays(-1);
|
|
//入库
|
if (type == "1")
|
{
|
string str = "select bind.TaskNo,bind.PalletNo,bind.Qty,box.LotNo,bind.LotText,bind.IsBale,bind.IsBelt,box.SkuNo,box.SkuName from bllPalletBind bind left join BllBoxInfo box on bind.Id = box.BindNo where bind.IsDel = '0' and box.IsDel = '0' and bind.Status = '2' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and box.SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and box.LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and bind.PalletNo = '{palletNo}'";
|
}
|
|
str += $" and bind.CompleteTime >= '{data}'";
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
count = total;
|
|
return palletBindDto;
|
}
|
//出库
|
else if (type == "2")
|
{
|
string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,IsBale,IsBelt,Qty from BllExportAllot where IsDel = '0' and Status = '5' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and PalletNo = '{palletNo}'";
|
}
|
|
str += $" and CreateTime >= '{data}'";
|
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
count = total;
|
|
return palletBindDto;
|
}
|
//盘库
|
else if (type == "3")
|
{
|
string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,Qty from BllStockCheckDetail where IsDel = '0' and TaskNo != '' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and PalletNo = '{palletNo}'";
|
}
|
|
str += $" and CreateTime >= '{data}'";
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
foreach (var item in palletBindDto)
|
{
|
item.IsBale = "0";
|
item.IsBelt = "0";
|
}
|
count = total;
|
|
return palletBindDto;
|
}
|
else
|
{
|
throw new Exception("异常！");
|
}
|
|
|
|
}
|
|
#endregion
|
|
#region 周
|
|
/// <summary>
|
/// 每周报表
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="type">出、入库</param>
|
/// <param name="page">页</param>
|
/// <param name="limit">每页多少数</param>
|
/// <param name="count">库存数量</param>
|
/// <returns></returns>
|
public List<PalletBindDto> GetToWeekDataStockDetails(string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
|
{
|
//获取前一年现在的日期
|
var data = Db.GetDate().AddDays(-7);
|
|
//入库
|
if (type == "1")
|
{
|
string str = "select bind.TaskNo,bind.PalletNo,bind.Qty,box.LotNo,bind.LotText,bind.IsBale,bind.IsBelt,box.SkuNo,box.SkuName from bllPalletBind bind left join BllBoxInfo box on bind.Id = box.BindNo where bind.IsDel = '0' and box.IsDel = '0' and bind.Status = '2' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and box.SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and box.LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and bind.PalletNo = '{palletNo}'";
|
}
|
|
str += $" and bind.CompleteTime >= '{data}'";
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
count = total;
|
|
return palletBindDto;
|
}
|
//出库
|
else if (type == "2")
|
{
|
string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,IsBale,IsBelt,Qty from BllExportAllot where IsDel = '0' and Status = '5' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and PalletNo = '{palletNo}'";
|
}
|
|
str += $" and CreateTime >= '{data}'";
|
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
count = total;
|
|
return palletBindDto;
|
}
|
//盘库
|
else if (type == "3")
|
{
|
string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,Qty from BllStockCheckDetail where IsDel = '0' and TaskNo != '' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and PalletNo = '{palletNo}'";
|
}
|
|
str += $" and CreateTime >= '{data}'";
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
foreach (var item in palletBindDto)
|
{
|
item.IsBale = "0";
|
item.IsBelt = "0";
|
}
|
count = total;
|
|
return palletBindDto;
|
}
|
else
|
{
|
throw new Exception("异常！");
|
}
|
}
|
|
#endregion
|
|
#region 月
|
|
/// <summary>
|
/// 每月报表
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="type">出、入库</param>
|
/// <param name="page">页</param>
|
/// <param name="limit">每页多少数</param>
|
/// <param name="count">库存数量</param>
|
/// <returns></returns>
|
public List<PalletBindDto> GetToMonthsDataStockDetails(string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
|
{
|
//获取前一年现在的日期
|
var data = Db.GetDate().AddMonths(-1);
|
|
//入库
|
if (type == "1")
|
{
|
string str = "select bind.TaskNo,bind.PalletNo,bind.Qty,box.LotNo,bind.LotText,bind.IsBale,bind.IsBelt,box.SkuNo,box.SkuName from bllPalletBind bind left join BllBoxInfo box on bind.Id = box.BindNo where bind.IsDel = '0' and box.IsDel = '0' and bind.Status = '2' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and box.SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and box.LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and bind.PalletNo = '{palletNo}'";
|
}
|
|
str += $" and bind.CompleteTime >= '{data}'";
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
count = total;
|
|
return palletBindDto;
|
}
|
//出库
|
else if (type == "2")
|
{
|
string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,IsBale,IsBelt,Qty from BllExportAllot where IsDel = '0' and Status = '5' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and PalletNo = '{palletNo}'";
|
}
|
|
str += $" and CreateTime >= '{data}'";
|
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
count = total;
|
|
return palletBindDto;
|
}
|
//盘库
|
else if (type == "3")
|
{
|
string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,Qty from BllStockCheckDetail where IsDel = '0' and TaskNo != '' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and PalletNo = '{palletNo}'";
|
}
|
|
str += $" and CreateTime >= '{data}'";
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
foreach (var item in palletBindDto)
|
{
|
item.IsBale = "0";
|
item.IsBelt = "0";
|
}
|
count = total;
|
|
return palletBindDto;
|
}
|
else
|
{
|
throw new Exception("异常！");
|
}
|
}
|
|
#endregion
|
|
#region 年
|
|
/// <summary>
|
/// 每年报表
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="palletNo">托盘条码</param>
|
/// <param name="type">出、入库</param>
|
/// <param name="page">页</param>
|
/// <param name="limit">每页多少数</param>
|
/// <param name="count">库存数量</param>
|
/// <returns></returns>
|
public List<PalletBindDto> GetToYearDataStockDetails(string skuNo, string lotNo, string palletNo, string type, int page, int limit, out int count)
|
{
|
//获取前一年现在的日期
|
var data = Db.GetDate().AddYears(-1);
|
|
//入库
|
if (type == "1")
|
{
|
string str = "select bind.TaskNo,bind.PalletNo,bind.Qty,box.LotNo,bind.LotText,bind.IsBale,bind.IsBelt,box.SkuNo,box.SkuName from bllPalletBind bind left join BllBoxInfo box on bind.Id = box.BindNo where bind.IsDel = '0' and box.IsDel = '0' and bind.Status = '2' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and box.SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and box.LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and bind.PalletNo = '{palletNo}'";
|
}
|
|
str += $" and bind.CompleteTime >= '{data}'";
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
count = total;
|
|
return palletBindDto;
|
}
|
//出库
|
else if (type == "2")
|
{
|
string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,IsBale,IsBelt,Qty from BllExportAllot where IsDel = '0' and Status = '5' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and PalletNo = '{palletNo}'";
|
}
|
|
str += $" and CreateTime >= '{data}'";
|
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
count = total;
|
|
return palletBindDto;
|
}
|
//盘库
|
else if (type == "3")
|
{
|
string str = "select TaskNo,SkuNo,SkuName,PalletNo,LotNo,LotText,Qty from BllStockCheckDetail where IsDel = '0' and TaskNo != '' ";
|
if (!string.IsNullOrWhiteSpace(skuNo))
|
{
|
str += $" and SkuNo = '{skuNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(lotNo))
|
{
|
str += $" and LotNo = '{lotNo}'";
|
}
|
if (!string.IsNullOrWhiteSpace(palletNo))
|
{
|
str += $" and PalletNo = '{palletNo}'";
|
}
|
|
str += $" and CreateTime >= '{data}'";
|
|
var total = 0;
|
//var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToOffsetPage(page, limit, ref total).ToList();
|
var palletBindDto = Db.SqlQueryable<PalletBindDto>(str).ToList();
|
foreach (var item in palletBindDto)
|
{
|
item.IsBale = "0";
|
item.IsBelt = "0";
|
}
|
count = total;
|
|
return palletBindDto;
|
}
|
else
|
{
|
throw new Exception("异常！");
|
}
|
}
|
|
#endregion
|
*/
|
#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)).ToString();
|
dto.FrozenQty = (Convert.ToInt32(dto.FrozenQty) + Convert.ToInt32(item.FrozenQty)).ToString();
|
dto.LockQty = (Convert.ToInt32(dto.LockQty) + Convert.ToInt32(item.LockQty)).ToString();
|
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>
|
/// <returns></returns>
|
public List<StockDetailDto> GetInventoryList1DaoChu(string skuNo, string skuName, string lotNo, string locatNo, string palletNo, string status, string inspectStatus)
|
{
|
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";
|
}
|
//排序
|
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
|
|
#region 导出出入库报表
|
/// <summary>
|
/// 导出库存总量
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="palletNo">托盘号</param>
|
/// <returns></returns>
|
public List<TotalRecordDto> GetTotalRecordDaoChu(string skuNo, string lotNo, string palletNo)
|
{
|
//入库
|
string rstr = "select LotNo,SkuNo,SkuName,SUM(Qty) RQty,SUM(FactQty) RFactQty,SUM(CompleteQty) RCompleteQty from BllArrivalNoticeDetail where IsDel = '0' and Id in (select ASNDetailNo from DataStockDetail where IsDel = '0' group by ASNDetailNo) ";
|
//出库
|
string cstr = "select LotNo,SkuNo,SkuName,SUM(Qty) CQty,SUM(FactQty) CFactQty,SUM(CompleteQty) CompleteQty,SUM(AllotQty) CAllotQty from BllExportNoticeDetail where IsDel = '0' and LotNo in (select LotNo from DataStock where IsDel = '0') ";
|
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
rstr += " and SkuNo like @skuno";
|
cstr += " and SkuNo like @skuno";
|
}
|
//判断批次号是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
rstr += " and LotNo like @lotno";
|
cstr += " and LotNo like @lotno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
rstr += " and PalletNo like @palletno";
|
cstr += " and PalletNo like @palletno";
|
}
|
rstr += " group by LotNo,SkuNo,SkuName";
|
cstr += " group by LotNo,SkuNo,SkuName";
|
//入库
|
List<TotalRecordDto> totalHListData = Db.Ado.SqlQuery<TotalRecordDto>(rstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
lotno = "%" + lotNo + "%", //批次号
|
palletno = "%" + palletNo + "%", //托盘号
|
});
|
List<TotalRecordDto> totalCListData = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
lotno = "%" + lotNo + "%", //批次号
|
palletno = "%" + palletNo + "%", //托盘号
|
});
|
|
//合并数据
|
int a = 0;
|
foreach (var h in totalHListData)
|
{
|
if (a >= totalCListData.Count)
|
{
|
continue;
|
}
|
foreach (var c in totalCListData)
|
{
|
//判断出库是否拥有相同批次 且物料相同
|
if (h.LotNo == c.LotNo)
|
{
|
if (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;
|
}
|
}
|
}
|
}
|
|
foreach (var item in totalHListData)
|
{
|
//物料编码，加上单引号是防止导出到excel自动把前面的0给去掉
|
if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
|
{
|
item.SkuNo = $"'{item.SkuNo}";
|
}
|
}
|
|
return totalHListData;
|
}
|
|
/// <summary>
|
/// 导出出入库报表记录
|
/// </summary>
|
/// <param name="skuNo">物料编码</param>
|
/// <param name="lotNo">批次</param>
|
/// <param name="palletNo">托盘号</param>
|
/// <returns></returns>
|
public List<TotalRecordDto> GetDetailedRecordDaoChu(string skuNo, string lotNo, string palletNo)
|
{
|
//入库
|
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' and bind.LotNo in (select LotNo from DataStock where IsDel = '0') ";
|
//出库
|
string cstr = "select notice.SONo,allot.LotNo,allot.TaskNo CTaskNo,allot.SkuNo,allot.SkuName,allot.PalletNo,allot.CreateTime 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' and notice.LotNo in (select LotNo from DataStock where IsDel = '0') ";
|
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
rstr += " and notice.SkuNo like @skuno";
|
cstr += " and allot.SkuNo like @skuno";
|
}
|
//判断批次号是否为空
|
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";
|
}
|
|
//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 + "%", //物料号
|
lotno = "%" + lotNo + "%", //批次号
|
palletno = "%" + palletNo + "%", //托盘号
|
});
|
//出库
|
List<TotalRecordDto> totalCList = Db.Ado.SqlQuery<TotalRecordDto>(cstr, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
lotno = "%" + lotNo + "%", //批次号
|
palletno = "%" + palletNo + "%", //托盘号
|
});
|
|
//合并
|
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;
|
}
|
}
|
}
|
|
|
foreach (var item in totalCList)
|
{
|
//储位地址，加上单引号是防止导出到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 totalRList;
|
}
|
|
List<BllPalletBind> IStockServer.GetOutSideStockList(string skuNo, string skuName, string lotNo, string palletNo, string status, int page, int limit, out int count)
|
{
|
throw new NotImplementedException();
|
}
|
#endregion
|
|
}
|
}
|
