using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Linq.Expressions;
|
using System.Text;
|
using Model.ModelDto.BllSoDto;
|
using SqlSugar;
|
using WMS.DAL;
|
using WMS.Entity.BllSoEntity;
|
using WMS.Entity.Context;
|
using WMS.Entity.SysEntity;
|
using WMS.IBLL.IBllSoServer;
|
|
namespace WMS.BLL.BllSoServer
|
{
|
public class CompleteDetailServer: DbHelper<BllCompleteDetail>,ICompleteDetailServer
|
{
|
private static readonly SqlSugarScope Db = DataContext.Db;
|
public CompleteDetailServer():base(Db)
|
{
|
}
|
|
//查询拣货明细箱码信息（分组后）
|
public List<CompleteDetailDto> GetCompleteDetailList(int id, int page, int limit, out int count)
|
{
|
try
|
{
|
var total = 0;
|
var detailInfo = Db.Queryable<BllCompleteDetail>().First(w => string.IsNullOrEmpty(w.BoxNo) || string.IsNullOrEmpty(w.BoxNo3));
|
if (detailInfo == null)
|
{
|
Expression<Func<BllCompleteDetail, bool>> item = Expressionable.Create<BllCompleteDetail>()
|
.And(it => it.ExportAllotId == id)
|
.And(it => it.IsDel == "0")
|
.ToExpression();//注意 这一句 不能少
|
var data = GetAllWhereAsync(item)
|
.GroupBy(m => new
|
{
|
m.SONo,
|
m.SODetailNo,
|
m.StockId,
|
m.ExportAllotId,
|
|
m.BoxNo,
|
m.LotNo,
|
m.LotText,
|
m.SupplierLot,
|
m.SkuNo,
|
m.SkuName,
|
m.Standard,
|
m.PalletNo,
|
m.NowPalletNo,
|
})
|
.Select(a => new CompleteDetailDto()
|
{
|
SONo = a.SONo,
|
SODetailNo = a.SODetailNo,
|
StockId = a.StockId,
|
ExportAllotId = a.ExportAllotId,
|
|
BoxNo = a.BoxNo,
|
LotNo = a.LotNo,
|
LotText = a.LotText,
|
SupplierLot = a.SupplierLot,
|
SkuNo = a.SkuNo,
|
SkuName = a.SkuName,
|
Standard = a.Standard,
|
PalletNo = a.PalletNo,
|
CompleteQty = SqlFunc.AggregateSum(a.CompleteQty),
|
NowPalletNo = a.NowPalletNo,
|
//CreateTime = a.CreateTime
|
}).ToOffsetPage(page, limit, ref total);
|
count = total;
|
return data;
|
}
|
else //箱码或支码为空说明是按数量拣货，增加 CreateTime 分组条件
|
{
|
Expression<Func<BllCompleteDetail, bool>> item = Expressionable.Create<BllCompleteDetail>()
|
.And(it => it.ExportAllotId == id)
|
.And(it => it.IsDel == "0")
|
.ToExpression();//注意 这一句 不能少
|
var data = GetAllWhereAsync(item)
|
.GroupBy(m => new
|
{
|
m.SONo,
|
m.SODetailNo,
|
m.StockId,
|
m.ExportAllotId,
|
|
m.BoxNo,
|
m.LotNo,
|
m.LotText,
|
m.SupplierLot,
|
m.SkuNo,
|
m.SkuName,
|
m.Standard,
|
m.PalletNo,
|
m.NowPalletNo,
|
m.CreateTime
|
})
|
.Select(a => new CompleteDetailDto()
|
{
|
SONo = a.SONo,
|
SODetailNo = a.SODetailNo,
|
StockId = a.StockId,
|
ExportAllotId = a.ExportAllotId,
|
|
BoxNo = a.BoxNo,
|
LotNo = a.LotNo,
|
LotText = a.LotText,
|
SupplierLot = a.SupplierLot,
|
SkuNo = a.SkuNo,
|
SkuName = a.SkuName,
|
Standard = a.Standard,
|
PalletNo = a.PalletNo,
|
CompleteQty = SqlFunc.AggregateSum(a.CompleteQty),
|
NowPalletNo = a.NowPalletNo,
|
//CreateTime = a.CreateTime
|
}).ToOffsetPage(page, limit, ref total);
|
count = total;
|
return data;
|
}
|
}
|
catch (Exception e)
|
{
|
throw new Exception(e.Message);
|
}
|
}
|
|
//查询拣货明细支码详细信息
|
public List<CompleteDetailDto> GetCompBoxInfoList(string boxNo, string boxNo3)
|
{
|
try
|
{
|
Expression<Func<BllCompleteDetail, bool>> item = Expressionable.Create<BllCompleteDetail>()
|
.AndIF(!string.IsNullOrWhiteSpace(boxNo), it => it.BoxNo == boxNo.Trim())
|
.AndIF(!string.IsNullOrWhiteSpace(boxNo3), it => it.BoxNo3.Contains(boxNo3.Trim()))
|
.ToExpression();
|
var data = GetAllWhereAsync(item)
|
.LeftJoin<SysUserInfor>((a, b) => a.CreateUser == b.Id)
|
.LeftJoin<SysUserInfor>((a, b, c) => a.UpdateUser == c.Id)
|
.Select((a, b, c) => new CompleteDetailDto()
|
{
|
Id = a.Id,
|
SONo = a.SONo,
|
SODetailNo = a.SODetailNo,
|
StockId = a.StockId,
|
ExportAllotId = a.ExportAllotId,
|
|
BoxNo = a.BoxNo,
|
BoxNo3 = a.BoxNo3,
|
LotNo = a.LotNo,
|
LotText = a.LotText,
|
SupplierLot = a.SupplierLot,
|
SkuNo = a.SkuNo,
|
SkuName = a.SkuName,
|
Standard = a.Standard,
|
PalletNo = a.PalletNo,
|
CompleteQty = a.CompleteQty,
|
NowPalletNo = a.NowPalletNo,
|
|
CreateUserName = b.RealName,
|
UpdateUserName = c.RealName,
|
CreateTime = a.CreateTime,
|
UpdateTime = a.UpdateTime,
|
}).ToList();
|
return data;
|
}
|
catch (Exception e)
|
{
|
throw new Exception(e.Message);
|
}
|
}
|
|
#region 归档数据
|
//查询拣货明细箱码信息（分组后）- 归档数据
|
public List<CompleteDetailDto> GetArchivingCompleteDetailList(int id, int page, int limit, out int count)
|
{
|
try
|
{
|
var detailInfo = Db.Queryable<BllCompleteDetail>().First(w => string.IsNullOrEmpty(w.BoxNo) || string.IsNullOrEmpty(w.BoxNo3));
|
if (detailInfo == null)
|
{
|
string sqlString = string.Empty;
|
string sqlCount = string.Empty;
|
string sqlPub = string.Empty;
|
|
sqlCount += "SELECT COUNT(SONo) FROM ArchivingCompleteDetail ";
|
sqlString += "SELECT SONo,SODetailNo,StockId,ExportAllotId,BoxNo,LotNo,LotText,sum(CompleteQty) as CompleteQty," +
|
"SupplierLot,SkuNo,SkuName,Standard,PalletNo,NowPalletNo ";
|
sqlString += " FROM ArchivingCompleteDetail ";
|
|
|
sqlPub += $"WHERE ExportAllotId = '{id}' and isDel = '0' ";
|
sqlPub += $" group by SONo,SODetailNo,StockId,ExportAllotId,BoxNo,LotNo,LotText,SupplierLot,SkuNo,SkuName,Standard," +
|
$"PalletNo,NowPalletNo ";
|
|
sqlCount += sqlPub;
|
|
if (page == 0)
|
{
|
page = 1;
|
}
|
|
sqlString += sqlPub + $" order by 1 offset {((page - 1) * limit)} rows fetch next {limit} rows only;";
|
|
var com = new Common();
|
count = com.GetRowCount(sqlCount);
|
|
var modelList = Db.Ado.SqlQuery<CompleteDetailDto>(sqlString);
|
return modelList;
|
}
|
else
|
{
|
string sqlString = string.Empty;
|
string sqlCount = string.Empty;
|
string sqlPub = string.Empty;
|
|
sqlCount += "SELECT COUNT(SONo) FROM ArchivingCompleteDetail ";
|
sqlString += "SELECT SONo,SODetailNo,StockId,ExportAllotId,BoxNo,LotNo,LotText,sum(CompleteQty) as CompleteQty," +
|
"SupplierLot,SkuNo,SkuName,Standard,PalletNo,NowPalletNo ";
|
sqlString += " FROM ArchivingCompleteDetail ";
|
|
sqlPub += $"WHERE ExportAllotId = '{id}' and isDel = '0' ";
|
sqlPub += $" group by SONo,SODetailNo,StockId,ExportAllotId,BoxNo,LotNo,LotText,SupplierLot,SkuNo,SkuName,Standard," +
|
$"PalletNo,NowPalletNo,CreateTime ";
|
|
sqlCount += sqlPub;
|
|
if (page == 0)
|
{
|
page = 1;
|
}
|
sqlString += sqlPub + $" order by 1 offset {((page - 1) * limit)} rows fetch next {limit} rows only;";
|
|
var com = new Common();
|
count = com.GetRowCount(sqlCount);
|
|
var modelList = Db.Ado.SqlQuery<CompleteDetailDto>(sqlString);
|
return modelList;
|
}
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
}
|
|
|
//查询拣货明细支码详细信息-数据归档
|
public List<CompleteDetailDto> GetArchivingCompBoxInfoList(string boxNo, string boxNo3)
|
{
|
try
|
{
|
string sqlString = string.Empty;
|
string sqlPub = string.Empty;
|
|
sqlString += "SELECT tb1.Id,tb1.SONo,tb1.SODetailNo,tb1.StockId,tb1.ExportAllotId,tb1.BoxNo,tb1.BoxNo3" +
|
",tb1.LotNo,tb1.LotText,tb1.CompleteQty,tb1.SupplierLot,tb1.SkuNo,tb1.SkuName,tb1.Standard,tb1.PalletNo,tb1.NowPalletNo, ";
|
sqlString += "tb3.RealName as CreateUserName,tb4.RealName as UpdateUserName,tb1.CreateTime,tb1.UpdateTime ";
|
sqlString += " FROM ArchivingCompleteDetail AS tb1 ";
|
sqlString += "LEFT JOIN SysUserInfor AS tb3 ON tb1.CreateUser = tb3.Id ";
|
sqlString += "LEFT JOIN SysUserInfor AS tb4 ON tb1.UpdateUser = tb4.Id ";
|
|
|
sqlString += $"WHERE tb1.isDel = '0' ";
|
|
if (!string.IsNullOrWhiteSpace(boxNo))
|
{
|
sqlString += $"AND tb1.BoxNo = '{boxNo.Trim()}' ";
|
}
|
if (!string.IsNullOrWhiteSpace(boxNo3))
|
{
|
sqlString += $"AND tb1.BoxNo3 like '%{boxNo3.Trim()}%' ";
|
}
|
|
var modelList = Db.Ado.SqlQuery<CompleteDetailDto>(sqlString);
|
return modelList;
|
}
|
catch (Exception e)
|
{
|
throw new Exception(e.Message);
|
}
|
}
|
#endregion
|
}
|
}
|
