using HH.WCS.Mobox3.SXJK.models;
|
using HH.WCS.Mobox3.SXJK.models.other;
|
using HH.WCS.Mobox3.SXJK.util;
|
using Newtonsoft.Json;
|
using NLog.Fluent;
|
using SqlSugar;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Linq.Expressions;
|
using System.Security.Cryptography;
|
using System.Threading.Tasks;
|
using static HH.WCS.Mobox3.SXJK.api.ApiModel;
|
|
namespace HH.WCS.Mobox3.SXJK.wms {
|
/// <summary>
|
/// wms管到作业
|
/// </summary>
|
internal class WMSHelper {
|
internal static string GenerateTaskNo() {
|
var id = SYSHelper.GetSerialNumber("作业号", "OP");
|
var date = DateTime.Now.ToString("yyMMdd");
|
return $"OP{date}{id.ToString().PadLeft(4, '0')}";
|
}
|
internal static string GenerateSortingNo() {
|
var id = SYSHelper.GetSerialNumber("分拣单", "SO");
|
var date = DateTime.Now.ToString("yyMMdd");
|
return $"SO{date}{id.ToString().PadLeft(4, '0')}";
|
}
|
internal static List<WMSTask> GetOperationListByState(string state) {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<WMSTask>().Where(a => a.S_B_STATE == state && a.S_FACTORY == Settings.FacCode).ToList();
|
}
|
internal static List<WMSTask> GetOperationListByState(int state) {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<WMSTask>().Where(a => a.N_B_STATE == state && a.S_FACTORY == Settings.FacCode).ToList();
|
}
|
internal static List<WMSTask> GetWaitingOperationList() {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<WMSTask>().Where(a => (a.N_B_STATE == 0 || a.N_B_STATE == 3) && a.S_FACTORY == Settings.FacCode).ToList();
|
}
|
internal static InStockList GetInStockList(string ASNNo) {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<InStockList>().Where(a => a.S_ASN_NO == ASNNo).First();
|
}
|
internal static bool CreateInStockList(InStockList model) {
|
var result = false;
|
var db = new SqlHelper<object>().GetInstance();
|
try
|
{
|
db.BeginTran();
|
result = db.Insertable<InStockList>(model).ExecuteCommand() > 0;
|
model.Details.ForEach(a =>
|
{
|
result = db.Insertable<InStockListChild>(a).ExecuteCommand() > 0;
|
});
|
db.CommitTran();
|
}
|
catch (Exception e) {
|
db.RollbackTran();
|
LogHelper.Info("CreateInStockList errorInfo：" + e.Message, "Mobox");
|
}
|
return result;
|
}
|
|
internal static PutawayDetail GetPutawayOrderDetail(string no, string item_code) {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<PutawayDetail>().Where(a => a.S_PUTAWAY_NO == no && a.S_ITEM_CODE == item_code).First();
|
}
|
internal static PutawayDetail GetPutawayOrderDetail(string item_code) {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<PutawayDetail>().Where(a => a.S_ITEM_CODE == item_code && a.F_QTY - a.F_ACC_B_QTY > 0).OrderByDescending(a => a.T_CREATE).First();
|
}
|
|
internal static void UpdatePutawayOrderDetailQty(PutawayDetail model) {
|
var db = new SqlHelper<object>().GetInstance();
|
db.Updateable(model).UpdateColumns(it => new { it.F_ACC_B_QTY }).ExecuteCommand();
|
}
|
|
|
internal static OutStockList GetOutStockList(string orderNo) {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<OutStockList>().Includes(a => a.Details).Where(a => a.S_ORDER_NO == orderNo).First();
|
}
|
|
internal static bool CreateOutStockList(OutStockList model) {
|
var result = false;
|
var db = new SqlHelper<object>().GetInstance();
|
try
|
{
|
db.BeginTran();
|
result = db.Insertable<OutStockList>(model).ExecuteCommand() > 0;
|
model.Details.ForEach(a =>
|
{
|
result = db.Insertable<OutStockListChild>(a).ExecuteCommand() > 0;
|
});
|
db.CommitTran();
|
}
|
catch (Exception e)
|
{
|
db.RollbackTran();
|
LogHelper.Info("CreateOutStockList errorInfo：" + e.Message, "Mobox");
|
}
|
return result;
|
}
|
internal static TransferList GetTransferList(string tdocNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<TransferList>().Includes(a => a.Details).Where(a => a.S_TDOC_NO == tdocNo).First();
|
}
|
|
internal static bool CreateTransferList(TransferList model)
|
{
|
var result = false;
|
var db = new SqlHelper<object>().GetInstance();
|
try
|
{
|
db.BeginTran();
|
result = db.Insertable<TransferList>(model).ExecuteCommand() > 0;
|
model.Details.ForEach(a =>
|
{
|
result = db.Insertable<TransferListChild>(a).ExecuteCommand() > 0;
|
});
|
db.CommitTran();
|
}
|
catch (Exception e)
|
{
|
db.RollbackTran();
|
LogHelper.Info("CreateTransferList errorInfo：" + e.Message, "Mobox");
|
}
|
return result;
|
}
|
|
internal static bool CreateFreezeList(FreezeList model)
|
{
|
var result = false;
|
var db = new SqlHelper<object>().GetInstance();
|
try
|
{
|
db.BeginTran();
|
result = db.Insertable<FreezeList>(model).ExecuteCommand() > 0;
|
model.Details.ForEach(a =>
|
{
|
result = db.Insertable<FreezeListChild>(a).ExecuteCommand() > 0;
|
});
|
db.CommitTran();
|
}
|
catch (Exception e)
|
{
|
db.RollbackTran();
|
LogHelper.Info("CreateFreezeList errorInfo：" + e.Message, "Mobox");
|
}
|
return result;
|
}
|
|
internal static bool CreateShiftStockList(ShiftStockList model)
|
{
|
var result = false;
|
var db = new SqlHelper<object>().GetInstance();
|
try
|
{
|
db.BeginTran();
|
result = db.Insertable<ShiftStockList>(model).ExecuteCommand() > 0;
|
model.Details.ForEach(a =>
|
{
|
result = db.Insertable<ShiftStockListChild>(a).ExecuteCommand() > 0;
|
});
|
db.CommitTran();
|
}
|
catch (Exception e)
|
{
|
db.RollbackTran();
|
LogHelper.Info("CreateShiftStockList errorInfo：" + e.Message, "Mobox");
|
}
|
return result;
|
}
|
|
|
|
internal static FreezeList GetFreezeList(string holdNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<FreezeList>().Includes(a => a.Details).Where(a => a.S_HOLD_NO == holdNo).First();
|
}
|
|
|
internal static ShiftStockList GetShiftingStockList(string mdocNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<ShiftStockList>().Includes(a => a.Details).Where(a => a.S_MDOC_NO == mdocNo).First();
|
}
|
|
internal static bool CreateSortingOrder(List<string> list) {
|
var res = false;
|
//遍历获取发货单，然后判断库存，如果全都没库存，则不生成分拣单，如果有生成分拣单
|
//更新波次单，即使只有一个发货单也更新波次单
|
var db = new SqlHelper<object>().GetInstance();
|
var sortingOrderNo = "";
|
SortingOrder sortingOrder = null;
|
try {
|
db.BeginTran();
|
list.ForEach(a => {
|
var so = db.Queryable<ShippingOrder>().Includes(s => s.Details).Where(s => s.S_NO == a).First();
|
//判断库存，只有已经入库的才可以计算，码盘的不算，入库后出库途中的也要计算，码盘后默认叫待入库，入库后叫正常
|
//生成出库任务，只有托盘位置在立库才需要创建出库任务（先写死立库）
|
//只有全部分拣完成才允许回库（一个托盘可能对应多个分拣明细）
|
|
//查找仓库量表和库区量表，出库单需要定位仓库和库区了，出库定位物理库区就行，入库可能物料库区还要区分逻辑库区
|
|
//暂时只查仓库量表（量表如何重置，查找所有查找C_ENABLE=N的，判断如果在仓库中，改成Y，然后统计）
|
|
//生成分拣单时候增加仓库量表分配量，生成分拣单明细时候，增加库区量表分配量
|
if (so != null && so.Details.Count > 0) {
|
var fail = true;
|
for (int i = 0; i < so.Details.Count; i++) {
|
var whi = db.Queryable<WHInventory>().Where(w => w.S_ITEM_CODE == so.Details[i].S_ITEM_CODE).First();
|
if (whi != null && whi.F_QTY - whi.F_ALLOC_QTY > 0) {
|
//有货就出
|
float qty = whi.F_QTY - whi.F_ALLOC_QTY > so.Details[i].F_QTY ? so.Details[i].F_QTY : whi.F_QTY - whi.F_ALLOC_QTY;
|
fail = false;
|
//有可用库存，生成分拣单
|
if (sortingOrderNo == "") {
|
sortingOrderNo = GenerateSortingNo();
|
sortingOrder = new SortingOrder
|
{
|
S_NO = sortingOrderNo,
|
S_SHIPPING_NO = so.S_NO,
|
Composes = new List<SortingCompose>()
|
};
|
//创建分拣单
|
db.Insertable(sortingOrder).ExecuteCommand();
|
}
|
else {
|
//获取最新分拣单
|
sortingOrder = db.Queryable<SortingOrder>().Includes(s => s.Composes).Where(s => s.S_NO == sortingOrderNo).First();
|
//更新分拣单中的发货单单号
|
sortingOrder.S_SHIPPING_NO = sortingOrder.S_SHIPPING_NO + ";" + so.S_NO;
|
sortingOrder.T_MODIFY = DateTime.Now;
|
db.Updateable(sortingOrder).UpdateColumns(it => new { it.S_SHIPPING_NO, it.T_MODIFY }).ExecuteCommand();
|
}
|
//查询分拣单子表（正常情况还需要增加批次判断）
|
var soc = db.Queryable<SortingCompose>().Where(s => s.S_ITEM_CODE == so.Details[i].S_ITEM_CODE && s.S_SORTING_NO == sortingOrder.S_NO).First();
|
if (soc == null) {
|
soc = new SortingCompose
|
{
|
S_ITEM_CODE = so.Details[i].S_ITEM_CODE,
|
S_SORTING_NO = sortingOrder.S_NO,
|
F_QTY = qty,
|
N_ROW_NO = sortingOrder.Composes.Count() + 1,
|
S_BATCH_NO = so.Details[i].S_BATCH_NO
|
};
|
|
//创建分拣单子表
|
db.Insertable(soc).ExecuteCommand();
|
}
|
else {
|
soc.F_QTY += qty;
|
soc.T_MODIFY = DateTime.Now;
|
//更新分拣单子表
|
db.Updateable(soc).UpdateColumns(it => new { it.F_QTY, it.T_MODIFY }).ExecuteCommand();
|
}
|
//更新仓库量表分配量
|
whi.F_ALLOC_QTY += qty;
|
whi.T_MODIFY = DateTime.Now;
|
db.Updateable(whi).UpdateColumns(it => new { it.F_ALLOC_QTY, it.T_MODIFY }).ExecuteCommand();
|
//跟新发货单子表配货数量
|
so.Details[i].F_ACC_D_QTY += qty;
|
so.Details[i].T_MODIFY = DateTime.Now;
|
db.Updateable(so.Details[i]).UpdateColumns(it => new { it.F_ACC_D_QTY, it.T_MODIFY }).ExecuteCommand();
|
}
|
|
}
|
//更新发货单状态
|
so.N_B_STATE = fail ? 5 : 1;
|
if (fail) {
|
so.S_NOTE = "没有库存";
|
}
|
so.S_WAVE_CODE = sortingOrderNo;
|
so.T_MODIFY = DateTime.Now;
|
db.Updateable(so).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY, it.S_WAVE_CODE, it.S_NOTE }).ExecuteCommand();
|
|
}
|
|
|
//查找库区内托盘生成托盘明细（包括分拣中）怎么判断库内？查找货位绑定？还是托盘上加标识比较好，如果是整托出的，分拣确认后去掉分拣标识。
|
//1、只有码盘入库后的才需要加标识，分拣回的不用，分拣回的标识还在，没有变。写死码盘入的完成后加标识，整托分拣的去掉标识，防止后面重新码盘脏数据
|
//2、或者关联查询库内的，只给分拣出的加标识，每次分拣回再把标识清除了（如果不清除带标识的会很多，还不如全部加标识），整托的清除。
|
//综合选择还是方案1，这样创建分拣明细，只需要托盘物料两表查询就能定位托盘（如果信息不准，可以重置），方案2需要货位、托盘、物料三表联查
|
|
//暂时不计库区，标识用容器表 C_ENABLE 来判断，表示能不能出库
|
|
//执行分拣创建任务，遍历分拣明细中的托盘，如果在仓库就出库，如果不在就不用出库
|
|
|
});
|
//全部分拣单生成之后将分拣单状态设置为开始配货,波次号为发货单
|
sortingOrder = db.Queryable<SortingOrder>().Where(s => s.S_NO == sortingOrderNo).First();
|
sortingOrder.N_B_STATE = 1;
|
sortingOrder.T_MODIFY = DateTime.Now;
|
db.Updateable(sortingOrder).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
|
db.CommitTran();
|
res = true;
|
}
|
catch (Exception ex) {
|
Console.WriteLine(ex.Message);
|
db.RollbackTran();
|
}
|
return res;
|
|
}
|
|
internal static List<SortingDetail> GetSortingDetailByCntr(string cntr) {
|
var db = new SqlHelper<object>().GetInstance();
|
var result = db.Queryable<SortingDetail>().Where(a => a.S_CNTR_CODE == cntr && a.N_B_STATE != 2).ToList();
|
return result;
|
}
|
|
internal static bool CreateWmsTask(WMSTask wmsTask) {
|
try {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Insertable<WMSTask>(wmsTask).ExecuteCommand() > 0;
|
}
|
catch (Exception ex) {
|
Console.WriteLine(ex.Message);
|
throw;
|
}
|
}
|
|
internal static WMSTask GetWmsTaskByCntr(string cntr, bool active = true) {
|
WMSTask result = null;
|
var db = new SqlHelper<object>().GetInstance();
|
if (active) {
|
result = db.Queryable<WMSTask>().Where(a => a.S_CNTR_CODE.Contains(cntr) && a.N_B_STATE < 2).First();
|
}
|
else {
|
result = db.Queryable<WMSTask>().Where(a => a.S_CNTR_CODE.Contains(cntr)).First();
|
}
|
return result;
|
}
|
|
internal static WMSTask GetWmsTaskByAsnNo(string asnNo)
|
{
|
WMSTask result = null;
|
var db = new SqlHelper<object>().GetInstance();
|
result = db.Queryable<WMSTask>().Where(a => a.S_OP_DEF_CODE == asnNo && a.N_B_STATE < 2).First();
|
return result;
|
}
|
|
internal static Location GetEnd(WMSTask a) {
|
throw new NotImplementedException();
|
}
|
|
internal static Location GetStart(WMSTask a) {
|
throw new NotImplementedException();
|
}
|
|
internal static void UpdateTaskState(WMSTask task) {
|
var db = new SqlHelper<object>().GetInstance();
|
task.T_MODIFY = DateTime.Now;
|
task.S_B_STATE = WMSTask.GetStateStr(task.N_B_STATE);
|
db.Updateable<WMSTask>(task).UpdateColumns(a => new { a.N_B_STATE, a.S_B_STATE,a.T_END_TIME, a.T_MODIFY }).ExecuteCommand();
|
}
|
|
internal static bool UpdateTaskEnd(WMSTask task) {
|
var db = new SqlHelper<object>().GetInstance();
|
task.T_MODIFY = DateTime.Now;
|
task.S_B_STATE = WMSTask.GetStateStr(task.N_B_STATE);
|
return db.Updateable<WMSTask>(task).UpdateColumns(it => new { it.N_B_STATE, it.S_B_STATE ,it.S_END_LOC,it.S_END_AREA, it.T_MODIFY }).ExecuteCommand() > 0;
|
}
|
|
internal static WMSTask GetWmsTask(string code) {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<WMSTask>().Where(a => a.S_CODE == code).First();
|
}
|
|
internal static List<WMSTask> GetActionWmsTaskList()
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<WMSTask>().Where(a => a.N_B_STATE < 2).ToList();
|
}
|
|
internal static void CreateSortingOrderDetail(string so_no) {
|
//分拣单配货
|
var db = new SqlHelper<object>().GetInstance();
|
var so = db.Queryable<SortingOrder>().Includes(a => a.Composes).Where(a => a.S_NO == so_no && (a.N_B_STATE == 1 || a.N_B_STATE == 20)).First();//
|
if (so != null && so.Composes.Count > 0) {
|
db.BeginTran();
|
try {
|
int rowNo = 1;
|
so.Composes.ForEach(a => {
|
//按分拣单子表去配货，查找可用托盘(先查所有符合的，后面再优化)
|
|
//expression.
|
|
|
var cirList = db.Queryable<CntrItemRel>().Includes(c => c.Cntr).Where(c => c.Cntr.C_ENABLE == "Y" && c.S_ITEM_CODE == a.S_ITEM_CODE && (c.F_QTY - c.F_ALLOC_QTY) > 0).OrderBy(c => c.T_CREATE).ToList();
|
for (int i = 0; i < cirList.Count; i++) {
|
var cir = cirList[i];
|
var sd = new SortingDetail
|
{
|
N_ROW_NO = rowNo,
|
S_BATCH_NO = a.S_BATCH_NO,
|
S_ITEM_CODE = a.S_ITEM_CODE,
|
S_CNTR_CODE = cir.S_CNTR_CODE,
|
S_SORTING_NO = a.S_SORTING_NO
|
};
|
bool needBreak = false;
|
if (cir.F_QTY - cir.F_ALLOC_QTY >= (a.F_QTY - a.F_ACC_S_QTY)) {
|
//容器可用数量大于分拣单子表要求的数量，生成分拣明细，然后跳出循环
|
sd.F_QTY = a.F_QTY - a.F_ACC_S_QTY;
|
needBreak = true;
|
}
|
else {
|
//生成分拣明细，继续创建
|
sd.F_QTY = cir.F_QTY - cir.F_ALLOC_QTY;
|
}
|
db.Insertable(sd).ExecuteCommand();
|
rowNo++;
|
//更新容器货品表分配量
|
cir.F_ALLOC_QTY += sd.F_QTY;
|
cir.T_MODIFY = DateTime.Now;
|
db.Updateable(cir).UpdateColumns(it => new { it.F_ALLOC_QTY, it.T_MODIFY }).ExecuteCommand();
|
//更新分拣单子表的量
|
a.F_ACC_S_QTY += sd.F_QTY;
|
a.T_MODIFY = DateTime.Now;
|
db.Updateable(cir).UpdateColumns(it => new { it.F_ALLOC_QTY, it.T_MODIFY }).ExecuteCommand();
|
if (needBreak) {
|
break;
|
}
|
}
|
});
|
//全部分拣单子表生成分拣明细，修改分拣单状态为配货完成(反正仓库的货能配的都配完了，除非数据有异常)
|
so.N_B_STATE = 2;
|
so.T_MODIFY = DateTime.Now;
|
db.Updateable(so).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
|
db.CommitTran();
|
}
|
catch (Exception ex) {
|
Console.WriteLine(ex.Message);
|
db.RollbackTran();
|
}
|
}
|
}
|
|
/// <summary>
|
/// 获取开始配货的分拣单，一次性生成分拣明细，避免生成一半再生成，所以创建分拣明细的时候加上事务
|
/// </summary>
|
/// <returns></returns>
|
internal static List<SortingOrder> GetWaitingSortingOrderList() {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<SortingOrder>().Includes(a => a.Composes).Where(a => a.N_B_STATE == 1 || a.N_B_STATE == 20).ToList();
|
}
|
/// <summary>
|
/// 获取配货完成的分拣单，每个分拣单单独创建分拣作业
|
/// </summary>
|
/// <returns></returns>
|
internal static List<SortingOrder> GetWaitingSortingOperationList() {
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<SortingOrder>().Includes(a => a.Details).Where(a => a.N_B_STATE == 2 || a.N_B_STATE == 3).ToList();
|
}
|
|
/// <summary>
|
///单个分拣单的分拣明细创建作业
|
/// </summary>
|
internal static void CreateSortingOperation(SortingOrder so) {
|
var list = so.Details.Where(a => a.N_B_STATE == 0).ToList();
|
var db = new SqlHelper<object>().GetInstance();
|
if (list.Count > 0) {
|
try {
|
db.BeginTran();
|
list.GroupBy(g => g.S_CNTR_CODE).ToList().ForEach(g => {
|
var cntr = g.Key;
|
var sdList = g.ToList();
|
//查询托盘货位，查到了创建任务，查不到说明在别的分拣作业中，不创建任务，需要回库后重新创建
|
var lcr = db.Queryable<LocCntrRel>().Where(c => c.S_CNTR_CODE == cntr).First();
|
if (lcr != null) {
|
//判断托盘是否已经创建任务了，可能多个分拣明细是同一个托盘，如果创建任务了，其它分拣的要稍后，只出一次人工会搞不清楚是哪个分拣单的
|
var wmsTask = db.Queryable<WMSTask>().Where(op => op.S_CNTR_CODE.Contains(cntr) && op.N_B_STATE < 2).First();
|
if (wmsTask == null) {
|
wmsTask = new WMSTask
|
{
|
S_CNTR_CODE = cntr,
|
S_CODE = WMSHelper.GenerateTaskNo(),
|
S_START_LOC = lcr.S_LOC_CODE,
|
S_END_LOC = "",
|
N_TYPE = 2,
|
S_TYPE = WMSTask.GetTypeStr(2),
|
S_OP_DEF_CODE = "",
|
S_OP_DEF_NAME = "分拣出立库"
|
};
|
if (db.Insertable(wmsTask).ExecuteCommand() > 0) {
|
LocationHelper.LockLoc(lcr.S_LOC_CODE, 2);
|
}
|
|
sdList.ForEach(a => {
|
a.N_B_STATE = 1;
|
a.T_MODIFY = DateTime.Now;
|
db.Updateable(a).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
|
});
|
}
|
}
|
//修改分拣单状态为开始作业（不代表作业全部创建了，因为有的托盘可能已经被占用了）
|
so.N_B_STATE = 3;
|
//如果所有的作业都已经创建了，就设置为作业已经创建
|
if (so.Details.Count(s => s.N_B_STATE == 0) == 0) {
|
so.N_B_STATE = 4;
|
}
|
so.T_MODIFY = DateTime.Now;
|
db.Updateable(so).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
|
|
});
|
db.CommitTran();
|
}
|
catch (Exception ex) {
|
Console.WriteLine(ex.Message);
|
db.RollbackTran();
|
}
|
|
}
|
}
|
internal static void SortingConfrim(List<SortingResultCheck> models) {
|
if (models.Count > 0) {
|
var db = new SqlHelper<object>().GetInstance();
|
try {
|
db.BeginTran();
|
//先查寻该分拣单所有执行中的分拣明细
|
var sdList = db.Queryable<SortingDetail>().Where(a => a.S_SORTING_NO == models[0].sortNo).ToList();
|
var soList = db.Queryable<ShippingOrder>().Includes(a => a.Details).Where(a => a.S_WAVE_CODE == models[0].sortNo).ToList();
|
//查出所有关联的发货单
|
models.ForEach(a => {
|
//根据条目修改累计分拣数量，添加分拣结果表，同时批分到发货单中
|
var sd = sdList.Where(b => b.S_ITEM_CODE == a.itemCode).First();
|
//简单一点，要求人工必须一次性分拣完，分拣数量和分拣明细中一致才创建分拣结果，不支持一条分拣明细多次分拣，多条分拣结果
|
//(sd.F_QTY - sd.F_ACC_SR_QTY) == a.qty
|
if (sd != null && sd.F_QTY == a.qty) {
|
//查询分拣结果是否已经存在，存在就累加
|
//分拣单结果
|
var sdr = new SortingResult
|
{
|
S_SORTING_NO = models[0].sortNo,
|
F_QTY = a.qty,
|
N_ROW_NO = sd.N_ROW_NO,
|
S_ITEM_CODE = sd.S_ITEM_CODE,
|
S_ITEM_NAME = "",
|
S_BATCH_NO = sd.S_BATCH_NO,
|
S_CNTR_CODE = a.cntrCode
|
};
|
db.Insertable(sdr).ExecuteCommand();
|
//sd.F_ACC_SR_QTY += a.qty;
|
sd.N_B_STATE = 2;
|
sd.T_MODIFY = DateTime.Now;
|
db.Updateable(sd).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
|
//批分到发货单中
|
var dList = soList.SelectMany(s => s.Details).Where(d => d.S_ITEM_CODE == a.itemCode && (d.F_QTY - d.F_ACC_S_QTY) > 0).ToList();
|
var qty = a.qty;
|
for (int i = 0; i < dList.Count; i++) {
|
var d = dList[i];
|
//发货信息中间表
|
var dyo = new DayuanOut
|
{
|
CN_S_BATCH_NO = sd.S_BATCH_NO,
|
CN_S_ITEM_CODE = sd.S_ITEM_CODE,
|
CN_S_OUT_NO = d.S_SHIPPING_NO,
|
|
};
|
if (d.F_QTY - d.F_ACC_S_QTY >= a.qty) {
|
//发货单数量超过托盘分拣数量，批分qty
|
d.F_ACC_S_QTY += a.qty;
|
dyo.CN_F_QUANTITY = a.qty;
|
qty = 0;
|
}
|
else {
|
// 发货单数量小于托盘分拣数量
|
d.F_ACC_S_QTY = d.F_QTY;
|
qty -= (d.F_QTY - d.F_ACC_S_QTY);
|
dyo.CN_F_QUANTITY = d.F_QTY - d.F_ACC_S_QTY;
|
}
|
d.T_MODIFY = DateTime.Now;
|
db.Updateable(d).UpdateColumns(it => new { it.F_ACC_S_QTY, it.T_MODIFY }).ExecuteCommand();
|
//插入到大元出库中间表
|
db.Insertable(dyo).ExecuteCommand();
|
if (qty == 0) {
|
break;
|
}
|
|
}
|
//更新托盘明细表
|
var cir = db.Queryable<CntrItemRel>().Where(c => c.S_CNTR_CODE == a.cntrCode && c.S_ITEM_CODE == a.itemCode).First();
|
cir.F_QTY -= a.qty;
|
cir.F_ALLOC_QTY -= a.qty;
|
cir.T_MODIFY = DateTime.Now;
|
db.Updateable(cir).UpdateColumns(it => new { it.F_QTY, it.F_ALLOC_QTY, it.T_MODIFY }).ExecuteCommand();
|
//更新仓库量表和库区量表
|
//查询仓库量表
|
var wi = db.Queryable<WHInventory>().Where(i => i.S_ITEM_CODE == a.itemCode).First();
|
if (wi != null) {
|
wi.F_QTY -= a.qty;
|
wi.F_ALLOC_QTY -= a.qty;
|
wi.T_MODIFY = DateTime.Now;
|
db.Updateable(wi).UpdateColumns(it => new { it.F_QTY, it.F_ALLOC_QTY, it.T_MODIFY }).ExecuteCommand();
|
}
|
//麻烦了，分拣明细只记托盘，不记住起点，如果要统计库区量表，必须对托盘加标记，记录托盘的来源库区，托盘清空后再清除标记
|
|
//逻辑库区更不需要量表了，因为从一个逻辑库区出来，可能回其它逻辑库区，那就应该直接删除逻辑库区量表，后面再加到别的逻辑库区中
|
//但是会带来新的问题，就是分拣回的托盘不属于任何逻辑库区的量，只有回库了才能计算到
|
|
//var aziList = db.Queryable<AZInventory>().Where(i => i.S_ITEM_CODE == a.itemCode).ToList();
|
}
|
|
//
|
});
|
//判断分拣单是否全部完成
|
if (sdList.Count(sd => sd.N_B_STATE != 2) == 0) {
|
var spo = db.Queryable<SortingOrder>().Where(s => s.S_NO == models[0].sortNo).First();
|
if (spo != null) {
|
spo.N_B_STATE = 10;
|
spo.T_MODIFY = DateTime.Now;
|
db.Updateable(spo).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
|
}
|
}
|
//判断发货单配货是否分拣完成
|
soList.ForEach(s => {
|
if (s.Details.Count > 0 && s.Details.Count(sd => sd.F_ACC_D_QTY != sd.F_ACC_S_QTY) == 0) {
|
//配货单的数量等于分拣的数量，分拣完成
|
s.N_B_STATE = 3;
|
s.T_MODIFY = DateTime.Now;
|
db.Updateable(s).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
|
}
|
});
|
db.CommitTran();
|
}
|
catch (Exception ex) {
|
Console.WriteLine(ex.Message);
|
db.RollbackTran();
|
}
|
}
|
}
|
internal static bool CheckSortingWholeCntr(string cntr, bool autoSort) {
|
var result = false;
|
//判断分拣明细是不是只有一个托盘，如果是，判断容器货品明细是不是和分拣明细完全一样
|
var db = new SqlHelper<object>().GetInstance();
|
var sdList = db.Queryable<SortingDetail>().Where(a => a.S_CNTR_CODE == cntr).ToList();
|
if (sdList.Count > 0) {
|
var groups = sdList.GroupBy(a => a.S_SORTING_NO).ToList();
|
if (groups.Count == 1) {
|
var list = groups.ToList();
|
//只有一个分拣单的情况下,分配量和托盘量都相同，表示是整托
|
var cirList = db.Queryable<CntrItemRel>().Where(a => a.S_CNTR_CODE == cntr).ToList();
|
if (list.Count == cirList.Count && cirList.Count(c => c.F_QTY != c.F_ALLOC_QTY) == 0) {
|
result = true;
|
if (autoSort) {
|
var data = sdList.Select(s => new SortingResultCheck { cntrCode = s.S_CNTR_CODE, itemCode = s.S_ITEM_CODE, qty = s.F_QTY, sortNo = s.S_SORTING_NO }).ToList();
|
SortingConfrim(data);
|
|
}
|
}
|
}
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 根据托盘查询入库单详情
|
/// </summary>
|
/// <param name="asnNo"></param>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
internal static List<InStockListChild> getInstockListChild( string trayCode , string asnNo = null)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var query = db.Queryable<InStockListChild>()
|
.LeftJoin<InStockList>((a,b) => a.S_ASN_NO == b.S_ASN_NO)
|
.Where((a, b) => a.S_TRACE_ID.Trim() == trayCode.Trim() && a.COMPLETED_FLAG.Trim() == "N" && b.S_ASN_STATUS.Trim() == "00");
|
if (asnNo != null) {
|
query = query.Where(a => a.S_ASN_NO.Trim() == asnNo.Trim());
|
}
|
return query.OrderByDescending(a => a.T_CREATE).ToList();
|
}
|
|
/// <summary>
|
/// 查询入库单的完成情况
|
/// </summary>
|
/// <param name="asnNo"></param>
|
/// <returns></returns>
|
internal static bool isCompletedInstockOrder(string asnNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<InStockListChild>().Where(a => a.S_ASN_NO == asnNo && a.COMPLETED_FLAG == "N").Count() == 0;
|
}
|
|
/// <summary>
|
/// 入库完成
|
/// </summary>
|
/// <param name="inStockListChilds"></param>
|
/// <returns></returns>
|
internal static bool instockCompleted(List<InStockListChild> inStockListChilds)
|
{
|
bool result = false;
|
var db = new SqlHelper<object>().GetInstance();
|
foreach (var item in inStockListChilds)
|
{
|
item.COMPLETED_FLAG = "Y";
|
}
|
|
result = db.Updateable(inStockListChilds).ExecuteCommand() > 0;
|
if (result) {
|
bool isFinish = isCompletedInstockOrder(inStockListChilds[0].S_ASN_NO);
|
if (isFinish)
|
{
|
result = db.Updateable<InStockList>().SetColumns(it => it.S_ASN_STATUS == "90").Where(it => it.S_ASN_NO == inStockListChilds[0].S_ASN_NO).ExecuteCommand() > 0;
|
}
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 查询新建出库单
|
/// </summary>
|
/// <param name="status"></param>
|
/// <returns></returns>
|
internal static List<OutStockList> getOutstockListByStatus(string status)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<OutStockList>().Where(a => a.S_SO_STATUS == status).ToList();
|
}
|
|
/// <summary>
|
/// 查询未完成出库单详情列表
|
/// </summary>
|
/// <param name="orderNo"></param>
|
/// <returns></returns>
|
internal static List<OutStockListChild> getOutstockListChilds(string orderNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<OutStockListChild>().Where(a => a.S_ORDER_NO == orderNo && a.COMPLETED_FLAG == "N").ToList();
|
}
|
|
/// <summary>
|
/// 查询未完成出库单详情列表
|
/// </summary>
|
/// <param name="orderNo"></param>
|
/// <param name="seqNo"></param>
|
/// <returns></returns>
|
internal static OutStockListChild getOutstockListChild(string orderNo ,string seqNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<OutStockListChild>().Where(a => a.S_ORDER_NO == orderNo && a.S_SEQ_NO == seqNo).First();
|
}
|
|
/// <summary>
|
/// 查询未完成出库单详情
|
/// </summary>
|
/// <param name="orderNo"></param>
|
/// <param name="seqNo"></param>
|
/// <returns></returns>
|
internal static OutStockListChild getOutstockListChildBySeqNo(string orderNo , string seqNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<OutStockListChild>().Where(a => a.S_ORDER_NO == orderNo && a.S_SEQ_NO == seqNo ).First();
|
}
|
|
/// <summary>
|
/// 更新出库单子表
|
/// </summary>
|
/// <param name="outStockListChild"></param>
|
/// <returns></returns>
|
internal static bool updateOutStockListChild(OutStockListChild outStockListChild)
|
{
|
bool result = false;
|
var db = new SqlHelper<object>().GetInstance();
|
result = db.Updateable(outStockListChild).ExecuteCommand() > 0;
|
return result;
|
}
|
|
/// <summary>
|
/// 是否完成出库单
|
/// </summary>
|
/// <param name="orderNo"></param>
|
/// <returns></returns>
|
internal static bool isCompletedOutstockOrder(string orderNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var childResult = db.Queryable<OutStockListChild>().Where(a => a.S_ORDER_NO == orderNo && a.COMPLETED_FLAG.Trim() == "N").Count() == 0;
|
|
// 1.查询集货区是否有空货位，没有则出库单完成
|
string areaCode = null;
|
bool isEmptyLoc = false;
|
var areaList = LocationHelper.GetAreaByCode(Settings.WHCode, 7);
|
if (areaList.Count > 0)
|
{
|
areaCode = areaList[0].S_CODE;
|
var rows = db.Queryable<Location>()
|
.Where(a => a.S_AREA_CODE == areaCode)
|
.GroupBy(a => a.N_ROW)
|
.Select(a => a.N_ROW)
|
.ToList();
|
|
if (rows.Count > 0)
|
{
|
isEmptyLoc = true;
|
foreach (var item in rows)
|
{
|
// 有其他锁的排，排除
|
var count = db.Queryable<Location>().Where(a => a.S_AREA_CODE == areaCode && a.N_LOCK_STATE == 3 && a.N_ROW == item).Count();
|
if (count > 0)
|
{
|
continue;
|
}
|
|
var locCount = db.Queryable<Location>().Where(a => a.S_AREA_CODE == areaCode && a.N_LOCK_STATE < 3 && a.N_ROW == item && a.N_CURRENT_NUM == 0 && a.C_ENABLE == "Y").Count();
|
if (locCount > 0)
|
{
|
isEmptyLoc = false;
|
break;
|
}
|
LogHelper.Info($"isEmptyLoc:{isEmptyLoc},row:{item},area:{areaCode}", "WMS");
|
}
|
}
|
}
|
LogHelper.Info("isEmptyLoc:" + isEmptyLoc, "WMS");
|
LogHelper.Info("childResult:" + childResult, "WMS");
|
if (childResult || isEmptyLoc)
|
{
|
return true;
|
}
|
return false;
|
}
|
|
/// <summary>
|
/// 完成出库任务
|
/// </summary>
|
/// <param name="task"></param>
|
/// <param name="orderNo"></param>
|
/// <returns></returns>
|
internal static bool completedOutstockTask(WCSTask cst, string orderNo)
|
{
|
bool result = false;
|
var db = new SqlHelper<object>().GetInstance();
|
var child = WMSHelper.getOutstockListChild(orderNo, cst.S_DATA);
|
|
var cntrCodeList = cst.S_CNTR_CODE.Split(',').ToList();
|
// 更新出库数量
|
float qty = 0;
|
for (int i = 0; i < cntrCodeList.Count; i++)
|
{
|
var cntrItemRels = ContainerHelper.GetCntrItemRel(cntrCodeList[i]);
|
foreach (var cntrItemRel in cntrItemRels)
|
{
|
qty += cntrItemRel.F_QTY;
|
}
|
}
|
child.COMPLETED_QTY = child.COMPLETED_QTY + qty;
|
WMSHelper.updateOutStockListChild(child);
|
|
// 判断出库单是否完成
|
if (child != null) {
|
if (child.COMPLETED_QTY >= child.N_QTY)
|
{
|
child.COMPLETED_FLAG = "Y";
|
}
|
result = WMSHelper.updateOutStockListChild(child);
|
|
if (result)
|
{
|
bool isFinish = isCompletedOutstockOrder(child.S_ORDER_NO);
|
if (isFinish)
|
{
|
result = db.Updateable<OutStockList>().SetColumns(it => it.S_SO_STATUS == "完成").Where(it => it.S_ORDER_NO == child.S_ORDER_NO).ExecuteCommand() > 0;
|
}
|
}
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 根据状态查询移库同步单
|
/// </summary>
|
/// <param name="status"></param>
|
/// <returns></returns>
|
internal static List<ShiftStockList> getShiftstockListByStatus(string status)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<ShiftStockList>().Where(a => a.S_STATUS == status).OrderBy(a => a.T_CREATE).ToList();
|
}
|
|
/// <summary>
|
/// 查询移库单子表
|
/// </summary>
|
/// <param name="mdocNo"></param>
|
/// <returns></returns>
|
internal static List<ShiftStockListChild> getShiftstockListChilds(string mdocNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<ShiftStockListChild>().Where(a => a.S_MDOC_NO == mdocNo && a.COMPLETED_FLAG == "N").ToList();
|
}
|
|
/// <summary>
|
/// 查询移库单子表
|
/// </summary>
|
/// <param name="mdocNo"></param>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
internal static ShiftStockListChild getShiftstockListChild(string mdocNo ,string trayCode)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<ShiftStockListChild>().Where(a => a.S_MDOC_NO == mdocNo && a.S_TRACE_ID == trayCode).First();
|
}
|
|
/// <summary>
|
/// 移库单完成
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
internal static bool shiftstockListCompleted(ShiftStockListChild model)
|
{
|
bool result = false;
|
var db = new SqlHelper<object>().GetInstance();
|
model.COMPLETED_FLAG = "Y";
|
result = db.Updateable(model).ExecuteCommand() > 0;
|
if (result)
|
{
|
bool isFinish = db.Queryable<ShiftStockListChild>().Where(a => a.S_MDOC_NO == model.S_MDOC_NO && a.COMPLETED_FLAG == "N").Count() == 0;
|
if (isFinish)
|
{
|
result = db.Updateable<ShiftStockList>().SetColumns(it => it.S_STATUS == "完成").Where(it => it.S_MDOC_NO == model.S_MDOC_NO).ExecuteCommand() > 0;
|
}
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 查询完成抽检的批次列表
|
/// </summary>
|
/// <returns></returns>
|
internal static List<string> getFinishSpotCheckList()
|
{
|
List<string> asnNoList = new List<string>();
|
var db = new SqlHelper<object>().GetInstance();
|
var list = db.Queryable<SpotCheckList>()
|
.GroupBy(a => a.S_ASN_NO)
|
.Select(a => new {
|
ASN_NO = a.S_ASN_NO ,
|
finishNum = SqlFunc.AggregateSum(a.N_STATUS == 3 ? 1:0),
|
unfinishNum = SqlFunc.AggregateSum(a.N_STATUS < 3 ? 1 : 0),
|
iscontinue = SqlFunc.AggregateSum(a.S_ISCONTINUE == "否" ? 1:0)
|
})
|
.ToList();
|
foreach (var item in list)
|
{
|
if (item.finishNum > 0 && item.unfinishNum == 0 && item.iscontinue > 0) {
|
asnNoList.Add(item.ASN_NO);
|
}
|
}
|
return asnNoList;
|
}
|
|
/// <summary>
|
/// 查询完成回库的批次列表
|
/// </summary>
|
/// <returns></returns>
|
internal static List<string> getBackStockSpotCheckList()
|
{
|
List<string> asnNoList = new List<string>();
|
var db = new SqlHelper<object>().GetInstance();
|
var list = db.Queryable<SpotCheckList>()
|
.GroupBy(a => a.S_ASN_NO)
|
.Select(a => new {
|
ASN_NO = a.S_ASN_NO.Trim(),
|
count = SqlFunc.AggregateCount(a.S_TRACE_ID),
|
finishNum = SqlFunc.AggregateSum(a.N_STATUS == 4 ? 1 : 0),
|
unfinishNum = SqlFunc.AggregateSum(a.N_STATUS < 4 ? 1 : 0)
|
})
|
.ToList();
|
foreach (var item in list)
|
{
|
if (item.finishNum == item.count && item.unfinishNum == 0)
|
{
|
asnNoList.Add(item.ASN_NO);
|
}
|
}
|
return asnNoList;
|
}
|
|
/// <summary>
|
/// 查询完成检验的抽检单列表
|
/// </summary>
|
/// <returns></returns>
|
internal static bool addSpotCheckList(SpotCheckList spotCheckList)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Insertable(spotCheckList).ExecuteCommand() > 0;
|
}
|
|
/// <summary>
|
/// 更新抽检单状态
|
/// </summary>
|
/// <returns></returns>
|
internal static bool updateSpotCheckList(SpotCheckList spotCheckList)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Updateable(spotCheckList).ExecuteCommand() > 0;
|
}
|
|
/// <summary>
|
/// 查询抽检单
|
/// </summary>
|
/// <returns></returns>
|
internal static SpotCheckList querySpotCheckList(string asnNo ,string traceId)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<SpotCheckList>().Where(a => a.S_ASN_NO == asnNo && a.S_TRACE_ID == traceId).First();
|
}
|
|
/// <summary>
|
/// 更新物料信息的质量状态
|
/// </summary>
|
/// <returns></returns>
|
internal static bool updateCntrItemStatusByAsnNO(string asnNo ,string status)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var cntrItems = db.Queryable<CntrItemRel>().Where(a => a.S_ASN_NO == asnNo).ToList();
|
if (cntrItems != null && cntrItems.Count > 0) {
|
return db.Updateable<CntrItemRel>().SetColumns(a => a.LOTATT08 == status).Where(a => a.S_ASN_NO == asnNo).ExecuteCommand() > 0;
|
}
|
return false;
|
}
|
|
/// <summary>
|
/// 查询存储库区所有不合格货品的货位
|
/// </summary>
|
/// <returns></returns>
|
internal static List<Location> getUnacceptedLocList()
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
List<string> areaCodes = LocationHelper.GetAreaByCode(Settings.WHCode, 1).Select(a => a.S_CODE).ToList();
|
var locList = db.Queryable<Location>()
|
.LeftJoin<LocCntrRel>((a,b) => a.S_CODE == b.S_LOC_CODE)
|
.LeftJoin<CntrItemRel>((a,b,c) => b.S_CNTR_CODE == c.S_CNTR_CODE)
|
.Where((a, b, c) => c.LOTATT08 == "Y" && areaCodes.Contains(a.S_AREA_CODE) && a.N_LOCK_STATE == 0)
|
.OrderByDescending((a, b, c) => a.N_COL)
|
.ToList();
|
return locList;
|
}
|
|
/// <summary>
|
/// 查询需要抽检的物料信息
|
/// </summary>
|
/// <returns></returns>
|
internal static List<SpotCheckList> GetShopCheckListByStatus(string status)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var list = db.Queryable<SpotCheckList>()
|
.LeftJoin<LocCntrRel>((a,b)=> a.S_TRACE_ID == b.S_CNTR_CODE)
|
.Where((a, b) => a.S_STATUS == status )
|
.OrderByDescending((a, b) => new { b.N_BIND_ORDER })
|
.ToList();
|
return list;
|
}
|
|
/// <summary>
|
/// 查询已抽检的物料信息
|
/// </summary>
|
/// <returns></returns>
|
internal static List<SpotCheckList> GetShopCheckListByAsnNo(string asnNo)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var list = db.Queryable<SpotCheckList>().Where(a => a.S_ASN_NO == asnNo && a.N_STATUS == 3).OrderByDescending(a => a.T_CREATE).ToList();
|
return list;
|
}
|
|
/// <summary>
|
/// 查询抽检单
|
/// </summary>
|
/// <returns></returns>
|
internal static SpotCheckList GetShopCheckList(string asnNo , string trayCode)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var list = db.Queryable<SpotCheckList>().Where(a => a.S_ASN_NO == asnNo && a.S_TRACE_ID == trayCode).First();
|
return list;
|
}
|
|
/// <summary>
|
/// 查询开始货位
|
/// </summary>
|
/// <param name="order"></param>
|
/// <returns></returns>
|
public static Location getStartLoc(OutStockListChild order) {
|
string asnNo = null;
|
// 查询发货区的物料的入库单号，优先出库相同的入库单物料
|
var areaList = LocationHelper.GetAreaByCode(Settings.WHCode, 7);
|
if (areaList.Count > 0)
|
{
|
string endAreaCode = areaList[0].S_CODE;
|
var db = new SqlHelper<object>().GetInstance();
|
var maxColLocs = db.Queryable<Location>()
|
.Where(a => a.S_AREA_CODE == endAreaCode && a.N_CURRENT_NUM > 0)
|
.OrderByDescending(a => a.N_COL)
|
.Take(1)
|
.PartitionBy(a => new { a.S_AREA_CODE, a.N_ROW })
|
.ToList();
|
|
if (maxColLocs.Count > 0)
|
{
|
foreach(Location loc in maxColLocs)
|
{
|
var cntrItemRel = db.Queryable<CntrItemRel>()
|
.LeftJoin<LocCntrRel>((a, b) => a.S_CNTR_CODE == b.S_CNTR_CODE)
|
.Where((a, b) => b.S_LOC_CODE == loc.S_CODE && a.S_ITEM_CODE == order.S_SKU).First();
|
if (cntrItemRel != null)
|
{
|
asnNo = cntrItemRel.S_ASN_NO;
|
break;
|
}
|
}
|
}
|
}
|
return getCntrItemLoc(order, asnNo);
|
}
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <param name="order"></param>
|
/// <param name="priorityAsnNo"></param>
|
/// <returns></returns>
|
public static Location getCntrItemLoc(OutStockListChild order, string priorityAsnNo = null) {
|
var db = new SqlHelper<object>().GetInstance();
|
List<string> areaCodes = LocationHelper.GetAreaByCode(Settings.WHCode, 1).Select(a => a.S_CODE).ToList();
|
|
var query = db.Queryable<Location>()
|
.LeftJoin<LocCntrRel>((a, b) => a.S_CODE == b.S_LOC_CODE)
|
.LeftJoin<CntrItemRel>((a, b, c) => b.S_CNTR_CODE == c.S_CNTR_CODE)
|
.Where((a, b, c) => a.N_CURRENT_NUM > 0 && a.N_LOCK_STATE == 0 && areaCodes.Contains(a.S_AREA_CODE));
|
|
// 查询条件
|
if (order.S_SKU != null && order.S_SKU != "")
|
{
|
query = query.Where((a, b, c) => c.S_ITEM_CODE == order.S_SKU);
|
}
|
if (order.LOTATT01 != null && order.LOTATT01 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT01 == order.LOTATT01);
|
}
|
if (order.LOTATT02 != null && order.LOTATT02 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT02 == order.LOTATT02);
|
}
|
if (order.LOTATT03 != null && order.LOTATT03 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT03 == order.LOTATT03);
|
}
|
if (order.LOTATT04 != null && order.LOTATT04 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT04 == order.LOTATT04);
|
}
|
if (order.LOTATT05 != null && order.LOTATT05 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT05 == order.LOTATT05);
|
}
|
if (order.LOTATT06 != null && order.LOTATT06 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT06 == order.LOTATT06);
|
}
|
if (order.LOTATT07 != null && order.LOTATT07 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT07 == order.LOTATT07);
|
}
|
if (order.LOTATT08 != null && order.LOTATT08 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT08 == order.LOTATT08);
|
}
|
if (order.LOTATT09 != null && order.LOTATT09 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT09 == order.LOTATT09);
|
}
|
if (order.LOTATT10 != null && order.LOTATT10 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT10 == order.LOTATT10);
|
}
|
if (order.LOTATT11 != null && order.LOTATT11 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT11 == order.LOTATT11);
|
}
|
if (order.LOTATT12 != null && order.LOTATT12 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT12 == order.LOTATT12);
|
}
|
if (order.LOTATT13 != null && order.LOTATT13 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT13 == order.LOTATT13);
|
}
|
if (order.LOTATT14 != null && order.LOTATT14 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT14 == order.LOTATT14);
|
}
|
if (order.LOTATT15 != null && order.LOTATT15 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT15 == order.LOTATT15);
|
}
|
if (order.LOTATT16 != null && order.LOTATT16 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT16 == order.LOTATT16);
|
}
|
if (order.LOTATT17 != null && order.LOTATT17 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT17 == order.LOTATT17);
|
}
|
if (order.LOTATT19 != null && order.LOTATT19 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT19 == order.LOTATT19);
|
}
|
if (order.LOTATT20 != null && order.LOTATT20 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT20 == order.LOTATT20);
|
}
|
if (order.LOTATT21 != null && order.LOTATT21 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT21 == order.LOTATT21);
|
}
|
if (order.LOTATT22 != null && order.LOTATT22 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT22 == order.LOTATT22);
|
}
|
if (order.LOTATT23 != null && order.LOTATT23 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT23 == order.LOTATT23);
|
}
|
if (order.LOTATT24 != null && order.LOTATT24 != "")
|
{
|
query = query.Where((a, b, c) => c.LOTATT24 == order.LOTATT24);
|
}
|
if (order.LOTATT18 != null && order.LOTATT18 == "JD")
|
{
|
query = query.Where((a, b, c) => c.LOTATT18 == order.LOTATT18);
|
}
|
if (priorityAsnNo != null)
|
{
|
query = query.Where((a, b, c) => c.S_ASN_NO == priorityAsnNo);
|
}
|
|
var locList = query.OrderByDescending((a, b, c) => a.N_COL)
|
.Take(1)
|
.PartitionBy((a, b, c) => new { a.S_AREA_CODE, a.N_ROW })
|
.OrderBy((a, b, c) => c.T_CREATE)
|
.ToList();
|
|
if (locList.Count > 0)
|
{
|
foreach (var loc in locList)
|
{
|
// 排除排有锁的
|
var result = db.Queryable<Location>().Where(a => a.N_ROW == loc.N_ROW && a.N_LOCK_STATE != 0 && loc.S_AREA_CODE == a.S_AREA_CODE).Count() > 0;
|
// 排除外侧有物料
|
var result1 = db.Queryable<Location>().Where(a => a.N_ROW == loc.N_ROW && loc.S_AREA_CODE == a.S_AREA_CODE && a.N_COL > loc.N_COL && a.N_CURRENT_NUM > 0).Count() > 0;
|
if (result || result1)
|
{
|
continue;
|
}
|
return loc;
|
}
|
}
|
return null;
|
}
|
|
/// <summary>
|
/// 查询可入库的货位
|
/// 1. 优先入 同 定向标识 、入库时间 的排
|
/// 2. 没有则 优先入空排
|
/// 3. 没有空排则 优先入 同 定向标识 的排
|
/// </summary>
|
/// <param name="orientSign"></param>
|
/// <param name="asnNo"></param>
|
/// <param name="cntrCount"></param>
|
/// <param name="areaCode"></param>
|
/// <param name="excludeRow"></param>
|
/// <returns></returns>
|
public static Location getEndLocation(string orientSign,string asnNo,int cntrCount, string areaCode = null ,int excludeRow = 0)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
Location end = null;
|
|
if (areaCode == null)
|
{
|
var areaList = LocationHelper.GetAreaByCode(Settings.WHCode,1);
|
var areaCodeList = areaList.OrderBy(a => a.S_GROUP).Select(a => a.S_CODE).ToList();
|
end = GetLocation4InExistRow(orientSign, asnNo, areaCodeList, cntrCount , excludeRow);
|
}
|
else
|
{
|
var area = db.Queryable<Area>().Where(a => a.S_CODE == areaCode).First();
|
if (area.N_TYPE == 1 || area.N_TYPE == 5 || area.N_TYPE == 7)
|
{
|
end = GetLocation4InExistRow(orientSign, asnNo, new List<string> { areaCode }, cntrCount , excludeRow);
|
}
|
else
|
{
|
end = db.Queryable<Location>().Where(a => a.S_AREA_CODE == areaCode && a.N_LOCK_STATE == 0 && a.N_CURRENT_NUM == 0).OrderBy(a => new { a.N_ROW, a.N_COL }).First();
|
}
|
}
|
return end;
|
}
|
|
public static Location GetLocation4InExistRow(string orientSign, string asnNo, List<string> areaCodeList ,int cntrCount ,int excludeRow)
|
{
|
Location end = null;
|
if (orientSign == "") {
|
orientSign = "F";
|
}
|
var db = new SqlHelper<object>().GetInstance();
|
LogHelper.Info("asnNo：" + asnNo, "Mobox");
|
|
// JD 定向标识 + 预期入库通知单号 入同一排
|
var listMaxCol = db.Queryable<Location>()
|
.LeftJoin<LocCntrRel>((l, lc) => l.S_CODE == lc.S_LOC_CODE)
|
.LeftJoin<CntrItemRel>((l, lc, ci) => lc.S_CNTR_CODE == ci.S_CNTR_CODE)
|
.Where((l, lc, ci) => l.N_CURRENT_NUM > 0
|
&& areaCodeList.Contains(l.S_AREA_CODE)
|
&& ci.S_ASN_NO == asnNo
|
&& l.N_ROW != excludeRow
|
).OrderByDescending((l, lc, ci) => l.N_COL)
|
.Take(1)
|
.PartitionBy((l, lc, ci) => new { l.S_AREA_CODE, l.N_ROW })
|
.ToList();
|
|
var rowNum = listMaxCol.Count;
|
LogHelper.Info("listMaxCol数量：" + rowNum, "Mobox");
|
|
if (listMaxCol != null)
|
{
|
foreach (var loc in listMaxCol.ToList())
|
{
|
//排有锁也排除
|
var other = db.Queryable<Location>().Where(a => a.S_AREA_CODE == loc.S_AREA_CODE && a.N_ROW == loc.N_ROW && a.N_LOCK_STATE != 0).First();
|
if (other != null)
|
{
|
LogHelper.Info($"{loc.N_ROW}排有锁排除", "WMS");
|
continue;
|
}
|
var locCntrRels = LocationHelper.GetLocCntr(loc.S_CODE);
|
var cntrItemRels = ContainerHelper.GetCntrItemRel(locCntrRels[0].S_CNTR_CODE);
|
if (orientSign != null && orientSign != "")
|
{
|
if (cntrItemRels[0].LOTATT18 == orientSign)
|
{
|
end = db.Queryable<Location>().OrderBy(a => a.N_COL).Where(a => a.S_AREA_CODE == loc.S_AREA_CODE && a.N_ROW == loc.N_ROW && a.N_CURRENT_NUM + cntrCount <= a.N_CAPACITY && (string.IsNullOrEmpty(a.C_ENABLE) || (a.C_ENABLE != "禁用" && a.C_ENABLE != "N")) && a.N_COL >= loc.N_COL).First();
|
if (end != null)
|
{
|
return end;
|
}
|
else
|
{
|
LogHelper.Info($"{loc.N_ROW}排没有空货位", "WMS");
|
}
|
}
|
else
|
{
|
LogHelper.Info($"定向标识:{orientSign}与{loc.N_ROW}排的定向标识:{cntrItemRels[0].LOTATT18}不相等", "WMS");
|
}
|
}
|
}
|
|
// 查询同批次入库锁数量，如果锁数量 >= 2 ,则不再从【空排】入库
|
var lockRow = db.Queryable<Location>().Where(a => a.N_LOCK_STATE == 1 && a.S_LOCK_OP.Trim() == asnNo).Take(1).PartitionBy(l => new { l.S_AREA_CODE, l.N_ROW }).ToList();
|
LogHelper.Info("同批次入库锁数量：" + lockRow.Count, "Mobox");
|
if (end == null && rowNum < 2 && lockRow.Count < 2 )
|
{
|
string fullerCode = null;
|
if (lockRow.Count > 0)
|
{
|
var maxLoc = lockRow[0];
|
var fuller = db.Queryable<TN_Fuller>().Where(a => a.S_AREA_CODE == maxLoc.S_AREA_CODE && a.N_ROW == maxLoc.N_ROW).First();
|
if (fuller != null)
|
{
|
fullerCode = fuller.FULLER_CODE;
|
}
|
}
|
|
if (fullerCode == null && rowNum > 0 )
|
{
|
var maxLoc = listMaxCol[0];
|
var fuller = db.Queryable<TN_Fuller>().Where(a => a.S_AREA_CODE == maxLoc.S_AREA_CODE && a.N_ROW == maxLoc.N_ROW).First();
|
if (fuller != null)
|
{
|
fullerCode = fuller.FULLER_CODE;
|
}
|
}
|
end = GetLocation4InEmptyRow(areaCodeList, asnNo, fullerCode);
|
}
|
}
|
|
|
if (end == null)
|
{
|
if (listMaxCol != null)
|
{
|
foreach (var loc in listMaxCol)
|
{
|
LogHelper.Info("locCode：" + loc.S_CODE, "Mobox");
|
//排有锁也排除
|
var other = db.Queryable<Location>().Where(a => a.S_AREA_CODE == loc.S_AREA_CODE && a.N_ROW == loc.N_ROW && a.N_LOCK_STATE != 0).First();
|
if (other != null)
|
{
|
continue;
|
}
|
end = db.Queryable<Location>().OrderBy(a => a.N_COL).Where(a => a.S_AREA_CODE == loc.S_AREA_CODE && a.N_ROW == loc.N_ROW && a.N_CURRENT_NUM + cntrCount <= a.N_CAPACITY && (string.IsNullOrEmpty(a.C_ENABLE) || (a.C_ENABLE != "禁用" && a.C_ENABLE != "N")) && a.N_COL >= loc.N_COL).First();
|
if (end != null)
|
{
|
return end;
|
}
|
}
|
}
|
}
|
return end;
|
}
|
|
public static Location GetLocation4InEmptyRow(List<string> areaCodes ,string asnNo ,string fullerCode = null)
|
{
|
Location result = null;
|
try
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
|
#region 查找所有数量是空的排
|
// 优先入同富乐库区
|
var query = db.Queryable<Location>()
|
.LeftJoin<LocCntrRel>((l, lc) => l.S_CODE == lc.S_LOC_CODE)
|
.LeftJoin<CntrItemRel>((l, lc, ci) => lc.S_CNTR_CODE == ci.S_CNTR_CODE)
|
.LeftJoin<TN_Fuller>((l, lc, ci, fu) => l.S_AREA_CODE == fu.S_AREA_CODE && l.N_ROW == fu.N_ROW)
|
.Where((l, lc, ci, fu) => l.N_CURRENT_NUM > 0
|
&& ci.S_ASN_NO == asnNo
|
&& areaCodes.Contains(l.S_AREA_CODE)
|
);
|
if (fullerCode != null)
|
{
|
query = query.Where((l, lc, ci, fu) => fu.FULLER_CODE == fullerCode);
|
}
|
var fullerCodeList = query.Select((l, lc, ci, fu) => fu.FULLER_CODE).Distinct().ToList();
|
|
//Console.WriteLine("查找所有数量是空的排");
|
//2.0 简化查询只查每一排第一列
|
var list = db.Queryable<Location, TN_Fuller>((a, b) => a.S_AREA_CODE == b.S_AREA_CODE && a.N_ROW == b.N_ROW)
|
.Where((a, b) => areaCodes.Contains(a.S_AREA_CODE) && a.N_CURRENT_NUM == 0)
|
.PartitionBy((a, b) => new { a.S_AREA_CODE, a.N_ROW })
|
.OrderBy((a,b)=>a.S_AREA_CODE)
|
.OrderBy((a, b) => a.N_COL)
|
.Select((a, b) => new { a, IsFullerCodeInList = fullerCodeList.Contains(b.FULLER_CODE) })
|
.ToList()
|
.OrderBy(x => !x.IsFullerCodeInList) // 优先显示fullerCodeList中的记录
|
.Select(x => x.a)
|
.ToList();
|
|
//2.1 选一个空排
|
for (int i = 0; i < list.Count; i++)
|
{
|
var other = db.Queryable<Location>().Where(a => a.S_AREA_CODE == list[i].S_AREA_CODE && a.N_ROW == list[i].N_ROW && a.N_LOCK_STATE != 0 ).First();
|
if (list[i].N_LOCK_STATE == 0 && other == null)
|
{
|
//二次校验当前排所有货位都是空的，防止系统数据错乱
|
var rowSumInfo = db.Queryable<Location>().Where(l => l.S_AREA_CODE == list[i].S_AREA_CODE && l.N_ROW == list[i].N_ROW && l.N_CURRENT_NUM != 0).Count();
|
if (rowSumInfo == 0)
|
{
|
result = list[i];
|
break;
|
}
|
}
|
}
|
#endregion
|
|
if (result != null && (!string.IsNullOrEmpty(result.C_ENABLE) && (result.C_ENABLE == "禁用" || result.C_ENABLE == "N")))
|
{
|
//禁用了选择后面一个货位
|
Console.WriteLine("禁用了选择后面一个货位");
|
result = db.Queryable<Location>().OrderBy(a => a.N_COL).Where(a => a.S_AREA_CODE == result.S_AREA_CODE && a.N_ROW == result.N_ROW && (string.IsNullOrEmpty(a.C_ENABLE) || (a.C_ENABLE != "禁用" && a.C_ENABLE != "N")) && a.N_COL > result.N_COL).First();
|
}
|
}
|
catch (Exception ex)
|
{
|
|
LogHelper.Error("GetLocation4InEmptyRow:" + ex.Message, ex);
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 查询是否有需要移库的货位
|
/// </summary>
|
/// <returns></returns>
|
public static Location getShiftLocation(Location loc)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<Location>().Where(a => a.S_AREA_CODE == loc.S_AREA_CODE
|
&& a.N_ROW == loc.N_ROW
|
&& a.N_COL > loc.N_COL
|
&& a.N_CURRENT_NUM > 0
|
&& a.N_LOCK_STATE == 0
|
)
|
.OrderByDescending(a => a.N_COL)
|
.First();
|
}
|
|
/// <summary>
|
/// 查询是否有需要移库的货位
|
/// </summary>
|
/// <returns></returns>
|
public static float getFurWeight(string VENDOR)
|
{
|
float weight = 0;
|
var db = new SqlHelper<object>().GetInstance();
|
var verdor = db.Queryable<TN_VendorList>().Where(a => a.VENDOR == VENDOR).First();
|
if (verdor != null)
|
{
|
weight = verdor.FURWEIGHT;
|
}
|
return weight;
|
}
|
|
/// <summary>
|
/// 查询是否有需要移库的货位
|
/// </summary>
|
/// <returns></returns>
|
public static float getFurHeight(string VENDOR)
|
{
|
float height = 800;
|
var db = new SqlHelper<object>().GetInstance();
|
var verdor = db.Queryable<TN_VendorList>().Where(a => a.VENDOR == VENDOR).First();
|
if (verdor != null)
|
{
|
height = verdor.FURHEIGHT;
|
}
|
return height;
|
}
|
}
|
}
|
