using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Linq.Expressions;
|
using System.Text;
|
using Model.ModelDto;
|
using Model.ModelDto.DataDto;
|
using Model.ModelDto.SysDto;
|
using SqlSugar;
|
using Utility.Tools;
|
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 DataBoxInfoServer: DbHelper<DataBoxInfo>,IDataBoxInfoServer
|
{
|
private static readonly SqlSugarScope Db = DataContext.Db;
|
public DataBoxInfoServer():base(Db)
|
{
|
}
|
|
#region 托盘明细
|
|
/// <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> GetDataBoxInfo(string id, string skuNo, string skuName, string palletNo, string lotNo,
|
string boxNo, string status, string inspectMark, string bitBoxMark, string inspectStatus, string WareHouseNo, string AreaNo)
|
{
|
string str = "select PalletNo,PalletNo2,PalletNo3,BoxNo,BoxNo2,BoxNo3,Status,LotNo,Qty,FullQty,SkuNo,SkuName," +
|
"LotText,ProductionTime,ExpirationTime,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 //质量状态
|
});
|
|
List<DataBoxInfo> box = new List<DataBoxInfo>();
|
foreach (var b1 in boxInfor)
|
{
|
//判断box是否为空
|
if (box.Count <= 0)
|
{
|
//无数据获取第一条
|
box.Add(b1);
|
continue;
|
}
|
int i = 0;
|
foreach (var b2 in box)
|
{
|
if (b2.BoxNo == b1.BoxNo)
|
{
|
b2.Qty += b1.Qty;
|
break;
|
}
|
i += 1;
|
}
|
if (i == box.Count)
|
{
|
box.Add(b1);
|
continue;
|
}
|
}
|
|
return box;
|
}
|
|
/// <summary>
|
/// 根据箱码获取箱支信息
|
/// </summary>
|
/// <param name="boxNo">箱码</param>
|
/// <param name="boxNo3">支码</param>
|
/// <returns></returns>
|
public List<DataBoxInfo> GetBoxInfoByBoxNo(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="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<AdventBoxInfoDto> GetEmergencyWarning(string skuNo, string skuName, string lotNo, string palletNo, string status, string inspectMark, string inspectStatus)
|
{
|
string str = @" select d.WareHouseName,d.WareHouseNo,e.RoadwayName,e.RoadwayNo,f.AreaName,f.AreaNo,c.LocatNo,a.PalletNo,
|
a.SkuNo,a.SkuName,SUM(a.Qty) as Qty,a.Standard,a.LotNo,a.SupplierLot,a.ProductionTime,a.Status,c.ExpirationTime
|
from DataBoxInfo a
|
left join SysMaterials b on a.SkuNo = b.SkuNo
|
left join DataStockDetail c on a.StockDetailId = c.Id
|
left join SysWareHouse d on d.WareHouseNo = c.WareHouseNo
|
left join SysStorageRoadway e on e.RoadwayNo = c.RoadwayNo
|
left join SysStorageArea f on f.AreaNo = c.AreaNo
|
where a.ProductionTime+b.Warranty-b.AdventTime <= GETDATE() ";
|
//判断物料号是否为空
|
if (!string.IsNullOrEmpty(skuNo))
|
{
|
str += " and a.SkuNo like @skuno";
|
}
|
//判断物料名称是否为空
|
if (!string.IsNullOrEmpty(skuName))
|
{
|
str += " and a.SkuName like @skuname";
|
}
|
//判断批次是否为空
|
if (!string.IsNullOrEmpty(lotNo))
|
{
|
str += " and a.LotNo like @lotno";
|
}
|
//判断托盘号是否为空
|
if (!string.IsNullOrEmpty(palletNo))
|
{
|
str += " and a.PalletNo like @palletno";
|
}
|
//判断状态是否为空
|
if (!string.IsNullOrEmpty(status))
|
{
|
str += " and a.Status = @status";
|
}
|
//判断检验标记是否为空
|
if (!string.IsNullOrEmpty(inspectMark))
|
{
|
str += " and a.InspectMark = @inspectmark";
|
}
|
//判断质量状态是否为空
|
if (!string.IsNullOrEmpty(inspectStatus))
|
{
|
str += " and a.InspectStatus = @inspectstatus";
|
}
|
//排序
|
str += @" group by d.WareHouseName,d.WareHouseNo,e.RoadwayName,e.RoadwayNo,f.AreaName,f.AreaNo,c.LocatNo,a.PalletNo,a.SkuNo,a.SkuName,a.Standard,a.LotNo,a.SupplierLot,a.ProductionTime,a.Status,c.ExpirationTime order by a.SkuNo,a.LotNo ";
|
List<AdventBoxInfoDto> stockList = Db.Ado.SqlQuery<AdventBoxInfoDto>(str, new
|
{
|
skuno = skuNo, //物料号
|
skuname = skuName, //物料名称
|
lotno = lotNo, //批次
|
palletno = palletNo, //托盘号
|
status, //状态
|
inspectmark = inspectMark, //检验标记
|
inspectstatus = inspectStatus, //质量状态
|
});
|
return stockList;
|
}
|
|
#endregion
|
|
#region 导出箱码明细
|
/// <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> GetDataBoxInfoDaoChu(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 //质量状态
|
});
|
|
List<DataBoxInfo> box = new List<DataBoxInfo>();
|
foreach (var b1 in boxInfor)
|
{
|
//判断box是否为空
|
if (box.Count <= 0)
|
{
|
//无数据获取第一条
|
box.Add(b1);
|
continue;
|
}
|
int i = 0;
|
foreach (var b2 in box)
|
{
|
if (b2.BoxNo == b1.BoxNo)
|
{
|
b2.Qty += b1.Qty;
|
break;
|
}
|
i += 1;
|
}
|
if (i == box.Count)
|
{
|
box.Add(b1);
|
continue;
|
}
|
}
|
|
foreach (var item in box)
|
{
|
//物料编码，加上单引号是防止导出到excel自动把前面的0给去掉
|
if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
|
{
|
item.SkuNo = $"'{item.SkuNo}";
|
}
|
//箱码，加上单引号是防止导出到excel纯数字太长
|
if (!string.IsNullOrEmpty(item.BoxNo))
|
{
|
item.BoxNo = $"'{item.BoxNo}";
|
}
|
//抽检标记
|
switch (item.InspectMark)
|
{
|
case "0":
|
item.InspectMark = "否";
|
break;
|
case "1":
|
item.InspectMark = "是";
|
break;
|
default:
|
break;
|
}
|
//零箱标记
|
switch (item.BitBoxMark)
|
{
|
case "0":
|
item.BitBoxMark = "否";
|
break;
|
case "1":
|
item.BitBoxMark = "是";
|
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 box;
|
}
|
#endregion
|
|
#region#补打标签
|
/// <summary>
|
/// 补打库存明细箱码
|
/// </summary>
|
/// <param name="boxNo"></param>
|
/// <returns></returns>
|
/// <exception cref="Exception"></exception>
|
public List<BoxInfoDto> GetBuDaLabelList(string boxNo)
|
{
|
try
|
{
|
if (string.IsNullOrWhiteSpace(boxNo))
|
{
|
throw new Exception($"箱码不能为空");
|
}
|
|
//打印数据集合
|
var printList = new List<BoxInfoDto>();
|
|
List<BoxInfoDto> printModelList = new List<BoxInfoDto>();
|
|
|
var funSet = Db.Queryable<SysFunSetting>().First(m => m.IsDel == "0" && m.FunSetName == "物料码类型" && m.IsEnable == "NO");
|
var allotSet = 1;//分配规则设定
|
|
if (funSet != null)
|
{
|
switch (funSet.SetValue)
|
{
|
case "One":
|
allotSet = 1;//一维条形码
|
break;
|
case "Two":
|
allotSet = 2;//二维码
|
break;
|
default:
|
allotSet = 1;// 默认：一维条形码、
|
break;
|
}
|
}
|
#region 单个箱码标签
|
|
|
var dataList = Db.Queryable<DataBoxInfo>().Where(m => m.IsDel == "0" && m.BoxNo == boxNo).ToList();
|
var data = dataList.FirstOrDefault();
|
if (data == null)
|
{
|
throw new Exception($"未查询到{boxNo}信息");
|
}
|
//物料信息
|
var skuInfo = Db.Queryable<SysMaterials>().First(w => w.IsDel == "0" && w.SkuNo == data.SkuNo);
|
if (skuInfo == null)
|
{
|
throw new Exception($"未查询到编码为{data.SkuNo}的物料信息");
|
}
|
//包装信息
|
var pack = Db.Queryable<SysPackag>().First(m => m.IsDel == "0" && m.PackagNo == skuInfo.PackagNo);
|
if (pack == null)
|
{
|
throw new Exception("未查询到当前单据中物料的包装信息");
|
}
|
|
var packStr = "";
|
|
if (!string.IsNullOrWhiteSpace(pack.L1Name))
|
{
|
packStr += pack.L1Num + "/" + pack.L1Name;
|
}
|
if (!string.IsNullOrWhiteSpace(pack.L2Name))
|
{
|
packStr += "-" + pack.L2Num + "/" + pack.L2Name;
|
}
|
if (!string.IsNullOrWhiteSpace(pack.L3Name))
|
{
|
packStr += "-" + pack.L3Num + "/" + pack.L3Name;
|
}
|
if (!string.IsNullOrWhiteSpace(pack.L4Name))
|
{
|
packStr += "-" + pack.L4Num + "/" + pack.L4Name;
|
}
|
if (!string.IsNullOrWhiteSpace(pack.L5Name))
|
{
|
packStr += "-" + pack.L5Num + "/" + pack.L5Name;
|
}
|
// 物料条码信息赋值
|
BoxInfoDto model = new BoxInfoDto();
|
model.SkuNo = data.SkuNo;
|
model.SkuName = data.SkuName;
|
model.LotNo = data.LotNo; //批次
|
model.SupplierLot = data.SupplierLot; // 原厂批号
|
model.Standard = data.Standard; // 规格/型号
|
model.PackageStandard = packStr; // 包装规格
|
|
model.ExpirationTime = data.ExpirationTime; // 有效期至
|
model.StoreTime = null; // 储存期至
|
model.ProductionTime = data.ProductionTime;//生产日期
|
|
model.Qty = dataList.Sum(m => m.Qty);// 数量
|
|
model.BoxNo = data.BoxNo; // 箱号
|
|
model.ImgStr = allotSet == 1 ? BarcodeHelper.GetCodeBarBase64(model.BoxNo, 160, 40, false) : BarcodeHelper.GetQrCodeBase64(model.BoxNo, 85, 85);
|
|
// 添加到list集合
|
printModelList.Add(model);
|
|
//添加打印记录
|
//WmsLabelPrintLog log = new WmsLabelPrintLog();
|
//log.BoxCode = data.LabelStream;
|
//log.CreateTime = DateTime.Now;
|
//log.Demo = Demo;
|
//log.Isdel = 0;
|
//log.CreateUser = userId;
|
//printlog.Add(log);
|
#endregion
|
|
return printModelList;
|
}
|
catch (Exception e)
|
{
|
throw new Exception(e.Message);
|
}
|
}
|
|
#endregion
|
|
#region 废代码 仅供参考
|
|
////获取分组后的箱码明细
|
//public List<DataBoxInfo> GetDataBoxInfoGourp(string id, string skuNo, string skuName, string palletNo, string lotNo, string boxNo,
|
// string status, string inspectMark, string bitBoxMark, string inspectStatus, int page, int limit, out int count)
|
//{
|
|
// try
|
// {
|
// Expression<Func<DataBoxInfo, bool>> item = Expressionable.Create<DataBoxInfo>()
|
// .AndIF(!string.IsNullOrWhiteSpace(id), it => it.StockDetailId == int.Parse(id))
|
// .AndIF(!string.IsNullOrWhiteSpace(skuNo), it => it.SkuNo.Contains(skuNo.Trim()))
|
// .AndIF(!string.IsNullOrWhiteSpace(skuName), it => it.SkuName.Contains(skuName.Trim()))
|
// .AndIF(!string.IsNullOrWhiteSpace(palletNo), it => it.PalletNo.Contains(palletNo.Trim()))
|
// .AndIF(!string.IsNullOrWhiteSpace(lotNo), it => it.LotNo.Contains(lotNo.Trim()))
|
// .AndIF(!string.IsNullOrWhiteSpace(boxNo), it=>it.BoxNo.Contains(boxNo.Trim()))
|
// .AndIF(!string.IsNullOrWhiteSpace(status), it => it.Status == status)
|
// .AndIF(!string.IsNullOrWhiteSpace(inspectMark), it => it.InspectMark == inspectMark)
|
// .AndIF(!string.IsNullOrWhiteSpace(bitBoxMark), it => it.BitBoxMark == bitBoxMark)
|
// .AndIF(!string.IsNullOrWhiteSpace(inspectStatus), it => it.InspectStatus == inspectStatus)
|
// .ToExpression();//注意 这一句 不能少
|
// var total = 0;
|
// var list = GetAllWhereAsync(item).GroupBy(m=>new {m.PalletNo,m.BoxNo,m.SkuNo,m.SkuName,m.LotNo});//.ToOffsetPage(page, limit, ref total);
|
// var data = list.Select(m=>new
|
// {
|
// a=m.PalletNo,
|
// b=m.BoxNo,
|
// c=m.SkuNo,
|
// d=m.SkuName,
|
// e=m.LotNo,
|
// f=SqlFunc.AggregateSum(m.Qty),
|
|
// }).ToOffsetPage(page, limit, ref total);
|
// count = total;
|
|
// return null;
|
// }
|
// catch (Exception e)
|
// {
|
// throw new Exception(e.Message);
|
// }
|
//}
|
|
////根据箱码获取箱支信息
|
//public List<DataBoxInfo> GetBoxInfoByBoxNo(string boxNo, string boxNo3)
|
//{
|
// try
|
// {
|
// Expression<Func<DataBoxInfo, bool>> item = Expressionable.Create<DataBoxInfo>()
|
// .AndIF(!string.IsNullOrWhiteSpace(boxNo), it => it.BoxNo == boxNo)
|
// .AndIF(!string.IsNullOrWhiteSpace(boxNo3), it => it.BoxNo3.Contains(boxNo3.Trim()))
|
// .ToExpression();//注意 这一句 不能少
|
|
// var data = GetAllWhereAsync(item).ToList();//.ToOffsetPage(page, limit, ref total);
|
// return data;
|
// }
|
// catch (Exception e)
|
// {
|
// throw new Exception(e.Message);
|
// }
|
//}
|
|
#endregion
|
|
}
|
}
|
