using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using Model.ModelDto.DataDto;
|
using SqlSugar;
|
using WMS.BLL.LogServer;
|
using WMS.DAL;
|
using WMS.Entity.BllQualityEntity;
|
using WMS.Entity.Context;
|
using WMS.Entity.DataEntity;
|
using WMS.IBLL.IDataServer;
|
|
namespace WMS.BLL.DataServer
|
{
|
public class StockDetailServer:DbHelper<DataStockDetail>,IStockDetailServer
|
{
|
private static readonly SqlSugarScope Db = DataContext.Db;
|
|
public StockDetailServer():base(Db)
|
{
|
}
|
|
#region 托盘明细
|
|
/// <summary>
|
/// 获取托盘明细
|
/// </summary>
|
/// <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="bitPalletMark">零托标记</param>
|
/// <param name="bitBoxMark">零箱标记</param>
|
/// <param name="inspectStatus">质量状态</param>
|
/// <returns></returns>
|
public List<StockDetailDto> GetBindList(string skuNo, string skuName, string palletNo, string lotNo, string boxNo, string status,
|
string inspectMark, string bitPalletMark, string bitBoxMark, 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(palletNo))
|
{
|
str += " and detail.PalletNo like @palletno";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and detail.LotNo like @lotno";
|
}
|
//判断零托标记是否为空
|
if (!string.IsNullOrEmpty(bitPalletMark))
|
{
|
str += " and detail.BitPalletMark = @bitpalletmark";
|
}
|
//判断货主编码是否为空
|
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}'";
|
}
|
|
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 detail.InspectMark = @inspectmark";
|
i = 1;
|
}
|
//判断零箱标记是否为空
|
if (!string.IsNullOrEmpty(bitBoxMark))
|
{
|
boxstr += " and BitBoxMark = @bitboxmark";
|
i = 1;
|
}
|
//判断质量状态是否为空
|
if (!string.IsNullOrEmpty(inspectStatus))
|
{
|
boxstr += " and InspectStatus = @inspectstatus";
|
str += " and detail.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 //质量状态
|
});
|
//去重
|
// List<int> newArr = null;
|
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 PalletNo,SkuNo,LotNo";
|
List<StockDetailDto> boxInforList = Db.Ado.SqlQuery<StockDetailDto>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
palletno = "%" + palletNo + "%", //托盘
|
lotno = "%" + lotNo + "%", //批次号
|
bitpalletmark = bitPalletMark, //零托标记
|
inspectmark = inspectMark, //检验标记
|
inspectstatus = inspectStatus, //质量状态
|
ownerNo = "%" + ownerNo + "%", //货主编码
|
ownerName = "%" + ownerName + "%" //货主名称
|
//stockdetailid = "(" + arr + ")" //库存明细id
|
});
|
|
return boxInforList;
|
}
|
|
|
#endregion
|
|
#region 导出托盘明细
|
/// <summary>
|
/// 获取托盘明细
|
/// </summary>
|
/// <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="bitPalletMark">零托标记</param>
|
/// <param name="bitBoxMark">零箱标记</param>
|
/// <param name="inspectStatus">质量状态</param>
|
/// <returns></returns>
|
public List<DataStockDetail> GetBindListDaoChu(string skuNo, string skuName, 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 from DataStockDetail where IsDel = @isdel";
|
//判断物料号是否为空
|
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(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 //质量状态
|
});
|
//去重
|
// List<int> newArr = null;
|
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 LotNo,SkuNo,PalletNo";
|
List<DataStockDetail> boxInforList = Db.Ado.SqlQuery<DataStockDetail>(str, new
|
{
|
isdel = "0", //是否删除
|
skuno = "%" + skuNo + "%", //物料号
|
skuname = "%" + skuName + "%", //物料名称
|
palletno = "%" + palletNo + "%", //托盘
|
lotno = "%" + lotNo + "%", //批次号
|
bitpalletmark = bitPalletMark, //零托标记
|
inspectmark = inspectMark, //检验标记
|
inspectstatus = inspectStatus, //质量状态
|
//stockdetailid = "(" + arr + ")" //库存明细id
|
});
|
foreach (var item in boxInforList)
|
{
|
//储位地址，加上单引号是防止导出到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}";
|
}
|
}
|
return boxInforList;
|
}
|
#endregion
|
|
#region 定时任务
|
/// <summary>
|
/// 超期物料转换为不合格 进入复检期物料转换为未检
|
/// </summary>
|
public void StockCheck()
|
{
|
try
|
{
|
//开启事务
|
Db.BeginTran();
|
var dateTime = DateTime.Now;//当前时间
|
var stock = Db.Queryable<DataBoxInfo>().Where(w => w.IsDel == "0" && w.InspectStatus == "1" && (w.ExpirationTime <= dateTime || w.InspectTime <= dateTime));
|
var stockList = stock.GroupBy(it => new { it.PalletNo, it.SkuNo, it.SkuName, it.LotNo, it.Standard }).
|
Select(s => new { s.PalletNo, s.SkuNo, s.SkuName, s.LotNo, s.Standard }).ToList();
|
foreach (var item in stockList)
|
{
|
//将过期的物料质量状态改为不合格
|
string sqlStr = $@"update DataBoxInfo set InspectStatus='2' where IsDel=0 and InspectStatus=1 and PalletNo='{item.PalletNo}' and SkuNo='{item.SkuNo}' and LotNo='{item.LotNo}'
|
and ExpirationTime<SYSDATETIME()";
|
int rowCount = Db.Ado.ExecuteCommand(sqlStr);
|
if (rowCount > 0)
|
{
|
//添加变更记录
|
var quality = new BllQualityInspect()
|
{
|
LotNo = item.LotNo,
|
SkuNo = item.SkuNo,
|
SkuName = item.SkuName,
|
Standard = item.Standard,
|
FailQty = rowCount,
|
IsQualified = "0",
|
Origin = "Time",
|
Demo = $"将托盘号：{item.PalletNo}上已过期的物料质量状态改为不合格，执行条数为：{rowCount}"
|
};
|
Db.Insertable(quality).ExecuteCommand();
|
}
|
//将复检的物料质量状态改为不合格
|
string sqlStr2 = $@"update DataBoxInfo set InspectStatus='0' where IsDel=0 and InspectStatus=1 and PalletNo='{item.PalletNo}' and SkuNo='{item.SkuNo}' and LotNo='{item.LotNo}'
|
and InspectTime<SYSDATETIME() and ExpirationTime>SYSDATETIME()";
|
int rowCount2 = Db.Ado.ExecuteCommand(sqlStr2);
|
if (rowCount2 > 0)
|
{
|
//添加变更记录
|
var quality2 = new BllQualityInspect()
|
{
|
LotNo = item.LotNo,
|
SkuNo = item.SkuNo,
|
SkuName = item.SkuName,
|
Standard = item.Standard,
|
FailQty = rowCount,
|
IsQualified = "0",
|
Origin = "Time",
|
Demo = $"将托盘号：{item.PalletNo}上到复检期的物料质量状态改为待检验，执行条数为：{rowCount}"
|
};
|
Db.Insertable(quality2).ExecuteCommand();
|
}
|
}
|
//提交事务
|
Db.CommitTran();
|
}
|
catch (Exception e)
|
{
|
//回滚事务
|
Db.RollbackTran();
|
throw new Exception(e.Message);
|
}
|
}
|
#endregion
|
}
|
}
|
