|
using HH.WCS.HangYang.models.other;
|
using HH.WCS.HangYang.util;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using static HH.WCS.HangYang.dispatch.NDC;
|
|
namespace HH.WCS.HangYang.wms
|
{
|
/// <summary>
|
/// 容器帮助类
|
/// </summary>
|
internal class ContainerHelper
|
{
|
internal static string GenerateCntrNo()
|
{
|
var id = SYSHelper.GetSerialNumber("托盘号", "TP");
|
var date = DateTime.Now.ToString("yyMMdd");
|
return $"TP{date}{id.ToString().PadLeft(4, '0')}";
|
}
|
/// <summary>
|
/// 根据容器类型、目的地、状态查询容器
|
/// </summary>
|
/// <param name="dest"></param>
|
/// <param name="cntrType"></param>
|
/// <param name="state"></param>
|
/// <returns></returns>
|
internal static List<Container> GetCntrListByPurpose(string dest, string cntrType, int state)
|
{
|
//1.0 查货位容器表
|
var db = new SqlHelper<object>().GetInstance();
|
var list = db.Queryable<Container>().Where(a => a.S_DEST == dest && a.S_TYPE == cntrType && a.N_B_STATE == state).ToList();
|
return list;
|
|
}
|
/// <summary>
|
/// 创建托盘物料绑定关系
|
/// </summary>
|
/// <param name="loc"></param>
|
/// <param name="cntr"></param>
|
/// <param name="itemcode"></param>
|
/// <returns></returns>
|
internal static bool CreateCntrItem(string loc, string cntr, string itemcode, string itemname, string itemtype, string cntrtype, string batch, int weight, string unit)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
try
|
{
|
var location = db.Queryable<Location>().Where(a => a.S_CODE == loc).First();
|
if (location != null)
|
{
|
LogHelper.Info($"托盘{cntr}：添加货位{loc}绑定关系，添加容器货品表");
|
db.BeginTran();
|
var con = db.Queryable<Container>().Where(a => a.S_CODE.Trim() == cntr).First();
|
if (con == null)
|
{
|
var container = new Container { S_CODE = cntr, S_TYPE = cntrtype };
|
db.Insertable<Container>(container).ExecuteCommand();
|
}
|
|
var cntritem = db.Queryable<CntrItemDetail>().Where(a => a.S_CNTR_CODE.Trim() == cntr).First();
|
if (cntritem == null)
|
{
|
var cir = new CntrItemDetail { S_CNTR_CODE = cntr, S_ITEM_CODE = itemcode, S_ITEM_NAME = itemname, S_ITEM_SPEC = itemtype, S_WU = unit, F_NET_WEIGHT = weight, S_BATCH_NO = batch };
|
db.Insertable<CntrItemDetail>(cir).ExecuteCommand();
|
}
|
|
var loctp = new LocCntrRel { S_LOC_CODE = loc, S_CNTR_CODE = cntr };
|
|
|
db.Insertable<LocCntrRel>(loctp).ExecuteCommand();
|
location.N_CURRENT_NUM = location.N_CURRENT_NUM + 1;
|
|
|
db.Updateable(location).UpdateColumns(it => new { it.N_CURRENT_NUM }).ExecuteCommand();
|
db.Ado.CommitTran();
|
res = true;
|
}
|
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Info($"CreateCntrItem => erro：托盘{cntr}添加失败" + ex.Message.ToString());
|
db.Ado.RollbackTran();
|
}
|
|
return res;
|
}
|
|
/// <summary>
|
/// 创建托盘物料绑定关系
|
/// </summary>
|
/// <param name="loc"></param>
|
/// <param name="cntr"></param>
|
/// <param name="itemcode"></param>
|
/// <returns></returns>
|
internal static bool CreateCntrItem(string cntr, string itemcode, string itemname, string batch, string weight)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
try
|
{
|
LogHelper.Info($"托盘{cntr}：添加物料{itemcode}绑定关系");
|
db.BeginTran();
|
var cntritem = db.Queryable<CntrItemDetail>().Where(a => a.S_CNTR_CODE.Trim() == cntr).First();
|
if (cntritem == null)
|
{
|
var cir = new CntrItemDetail { S_CNTR_CODE = cntr, S_ITEM_CODE = itemcode, S_ITEM_NAME = itemname, F_NET_WEIGHT = float.Parse(weight), S_BATCH_NO = batch };
|
db.Insertable<CntrItemDetail>(cir).ExecuteCommand();
|
}
|
else
|
{
|
cntritem.S_ITEM_CODE = itemcode;
|
cntritem.S_ITEM_NAME = itemname;
|
cntritem.F_NET_WEIGHT = float.Parse(weight);
|
cntritem.S_BATCH_NO = batch;
|
db.Updateable<CntrItemDetail>(cntritem).ExecuteCommand();
|
}
|
var con = db.Queryable<Container>().Where(a => a.S_CODE.Trim() == cntr).First();
|
if (con == null)
|
{
|
var container = new Container { S_CODE = cntr };
|
db.Insertable<Container>(container).ExecuteCommand();
|
}
|
db.Ado.CommitTran();
|
res = true;
|
|
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Info($"CreateCntrItem => erro：托盘{cntr}添加失败" + ex.Message.ToString());
|
db.Ado.RollbackTran();
|
}
|
|
return res;
|
}
|
/// <summary>
|
/// TN_ContainerHelper.test("4L天然水箱装6入", 50, "335ml东方树叶15入", 30);
|
/// </summary>
|
/// <param name="item1"></param>
|
/// <param name="qty1"></param>
|
/// <param name="item2"></param>
|
/// <param name="qty2"></param>
|
//internal static void test(string item1, int qty1, string item2, int qty2) {
|
// var db = new SqlHelper<object>().GetInstance();
|
// var cntr = db.Queryable<TN_Container>().Includes(a => a.CntrItemRelList).
|
// Where(a => a.CntrItemRelList.Count(b => b.S_ITEM_CODE == item1 && int.Parse(b.F_QTY) > qty1) > 0
|
// && a.CntrItemRelList.Count(b => b.S_ITEM_CODE == item2 && int.Parse(b.F_QTY) > qty2) > 0).First();
|
//}
|
|
/// <summary>
|
/// 判断容器是否有物料信息
|
/// </summary>
|
/// <param name="cntr"></param>
|
/// <returns></returns>
|
internal static bool CheckEmpty(string cntr)
|
{
|
//1.0 查货位容器表
|
var db = new SqlHelper<object>().GetInstance();
|
return db.Queryable<LocCntrRel>().Count(a => a.S_LOC_CODE.Trim() == cntr) == 0;
|
|
}
|
|
internal static bool AddCntr(string cntrCode, string itemCode)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
var TN_Container = new Container { S_CODE = cntrCode };
|
var cntrItemRel = new CntrItemDetail { S_CNTR_CODE = cntrCode, S_ITEM_CODE = itemCode };
|
try
|
{
|
db.BeginTran();
|
|
db.Insertable<Container>(TN_Container).ExecuteCommand();
|
db.Insertable<CntrItemDetail>(cntrItemRel).ExecuteCommand();
|
|
db.CommitTran();
|
res = true;
|
}
|
catch (Exception ex)
|
{
|
db.RollbackTran();
|
|
}
|
return res;
|
}
|
|
/// <summary>
|
/// 根据容器号获取容器信息
|
/// </summary>
|
/// <param name="cntr"></param>
|
/// <returns></returns>
|
internal static Container GetCntr(string cntr, bool create = false)
|
{
|
//1.0 查货位容器表
|
var db = new SqlHelper<object>().GetInstance();
|
var old = db.Queryable<Container>().Where(a => a.S_CODE == cntr).First();
|
if (old == null && create)
|
{
|
var model = new Container { S_CODE = cntr };
|
if (db.Insertable(model).ExecuteCommand() > 0)
|
{
|
old = model;
|
}
|
}
|
return old;
|
|
}
|
|
/// <summary>
|
/// 根据容器获取物料信息
|
/// </summary>
|
/// <param name="cntr"></param>
|
/// <returns></returns>
|
internal static List<CntrItemDetail> GetCntrItemRel(string cntr)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var list = db.Queryable<CntrItemDetail>().Where(a => a.S_CNTR_CODE.Trim() == cntr).ToList();
|
return list;
|
}
|
|
/// <summary>
|
/// 根据物料获取容器信息
|
/// </summary>
|
/// <param name="cntr"></param>
|
/// <returns></returns>
|
internal static List<CntrItemDetail> GetItemCntrRel(string itemcode)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var list = db.Queryable<CntrItemDetail>().Where(a => a.S_ITEM_CODE.Trim() == itemcode.Trim()).ToList();
|
return list;
|
}
|
|
/// <summary>
|
/// 物料信息绑定到满容器上
|
/// </summary>
|
/// <param name="cntrCode"></param>
|
/// <param name="itemCode"></param>
|
/// <param name="batchNo"></param>
|
/// <param name="qty"></param>
|
/// <param name="purpose">容器用途，用于哪个线边还是目的点</param>
|
/// <returns></returns>
|
internal static bool BindCntrItemSingle(Container cntr, string itemCode, string batchNo, float qty)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
try
|
{
|
db.BeginTran();
|
db.Updateable(cntr).UpdateColumns(it => new { it.S_DEST }).ExecuteCommand();
|
//1.将原有容器物料信息删除
|
db.Deleteable<CntrItemDetail>().Where(it => it.S_CNTR_CODE == cntr.S_CODE.Trim()).ExecuteCommand();
|
//2.插入新的容器物料信息（容器号不变）
|
var cir = new CntrItemDetail { S_CNTR_CODE = cntr.S_CODE.Trim(), S_BATCH_NO = batchNo, F_QTY = qty, S_ITEM_CODE = itemCode };
|
db.Insertable<CntrItemDetail>(cir).ExecuteCommand();
|
|
db.CommitTran();
|
res = true;
|
}
|
catch (Exception ex)
|
{
|
db.RollbackTran();
|
}
|
|
return res;
|
}
|
/// <summary>
|
/// 根据容器来源和状态获取托盘
|
/// </summary>
|
/// <param name="src"></param>
|
/// <param name="state"></param>
|
/// <returns></returns>
|
internal static List<Container> GetCntr(string dest, int state, string cntrType = "")
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
if (cntrType == "")
|
{
|
return db.Queryable<Container>().Where(a => a.S_DEST == dest && a.N_B_STATE == state).ToList();
|
}
|
else
|
{
|
return db.Queryable<Container>().Where(a => a.S_DEST == dest && a.N_B_STATE == state && a.S_TYPE == cntrType).ToList();
|
}
|
}
|
internal static bool UpdateCntr(List<string> cntrs, string dest, int state)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
var models = db.Queryable<Container>().Where(a => cntrs.Contains(a.S_CODE)).ToList();
|
if (models.Count > 0)
|
{
|
models.ForEach(a =>
|
{
|
a.S_DEST = dest; a.N_B_STATE = state;
|
db.Updateable(a).UpdateColumns(it => new { it.S_DEST, it.N_B_STATE }).ExecuteCommand();
|
});
|
res = true;
|
}
|
return res;
|
}
|
internal static bool UpdateCntrDest(List<string> cntrs, string dest)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
var models = db.Queryable<Container>().Where(a => cntrs.Contains(a.S_CODE)).ToList();
|
if (models.Count > 0)
|
{
|
models.ForEach(a =>
|
{
|
a.S_DEST = dest;
|
db.Updateable(a).UpdateColumns(it => new { it.S_DEST }).ExecuteCommand();
|
});
|
res = true;
|
}
|
return res;
|
}
|
/// <summary>
|
/// 更新托盘来源
|
/// </summary>
|
/// <param name="cntr"></param>
|
/// <param name="src"></param>
|
/// <returns></returns>
|
internal static bool UpdateCntrSrc(string cntr, string src)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
var model = db.Queryable<Container>().Where(a => a.S_CODE == cntr).First();
|
if (model != null)
|
{
|
model.S_SRC = src;
|
model.T_MODIFY = DateTime.Now;
|
res = db.Updateable(model).UpdateColumns(it => new { it.S_SRC, it.T_MODIFY }).ExecuteCommand() > 0;
|
}
|
return res;
|
}
|
internal static bool UpdateCntrState(List<string> cntrs, int state)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
var models = db.Queryable<Container>().Where(a => cntrs.Contains(a.S_CODE)).ToList();
|
if (models.Count > 0)
|
{
|
models.ForEach(a =>
|
{
|
a.N_B_STATE = state;
|
db.Updateable(a).UpdateColumns(it => new { it.N_B_STATE }).ExecuteCommand();
|
});
|
res = true;
|
}
|
return res;
|
}
|
|
internal static bool UpdateCntr(List<string> cntrs, string src, string dest, int state)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
var models = db.Queryable<Container>().Where(a => cntrs.Contains(a.S_CODE)).ToList();
|
if (models.Count > 0)
|
{
|
models.ForEach(a =>
|
{
|
a.S_DEST = dest; a.N_B_STATE = state; a.S_SRC = src;
|
db.Updateable(a).UpdateColumns(it => new { it.S_DEST, it.N_B_STATE, it.S_SRC }).ExecuteCommand();
|
});
|
res = true;
|
}
|
return res;
|
}
|
public static bool ClearCntrInfo(string cntr)
|
{
|
|
var db = new SqlHelper<object>().GetInstance();
|
var model = db.Queryable<Container>().Where(a => a.S_CODE == cntr).First();
|
if (model != null)
|
{
|
model.S_SRC = "";
|
model.S_DEST = "";
|
model.N_B_STATE = 0;
|
model.T_MODIFY = DateTime.Now;
|
|
}
|
db.Updateable(model).UpdateColumns(it => new { it.S_SRC, it.S_DEST, it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
|
return db.Deleteable<CntrItemDetail>().Where(a => a.S_CNTR_CODE.Trim() == cntr.Trim()).ExecuteCommand() > 0;
|
}
|
internal static List<Container> GetCntrBySrc(string src, int state, string cntrType = "")
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
if (cntrType == "")
|
{
|
return db.Queryable<Container>().Where(a => a.S_SRC == src && a.N_B_STATE == state).ToList();
|
}
|
else
|
{
|
return db.Queryable<Container>().Where(a => a.S_SRC == src && a.N_B_STATE == state && a.S_TYPE == cntrType).ToList();
|
}
|
}
|
/// <summary>
|
/// enable 让托盘允许被出库计算到，同时增加量表数据
|
/// </summary>
|
/// <param name="cntr"></param>
|
internal static void Enable(string cntr, string loc)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var cntrInfo = db.Queryable<Container>().Where(a => a.S_CODE == cntr).First();
|
//获取仓库量表
|
//获取物理库区
|
//获取逻辑库区
|
if (cntrInfo != null)
|
{
|
try
|
{
|
db.BeginTran();
|
//防止接口重复调用，量表重复增加
|
if (cntrInfo.C_ENABLE == "N")
|
{
|
cntrInfo.C_ENABLE = "Y";
|
db.Updateable(cntrInfo).UpdateColumns(a => new { a.C_ENABLE, a.T_MODIFY }).ExecuteCommand();
|
var cirList = db.Queryable<CntrItemDetail>().Where(a => a.S_CNTR_CODE == cntr).ToList();
|
if (cirList.Count > 0)
|
{
|
cirList.ForEach(a =>
|
{
|
var wh = db.Queryable<WHInventory>().Where(b => b.S_ITEM_CODE == a.S_ITEM_CODE).First();
|
if (wh != null)
|
{
|
//更新仓库量表
|
wh.F_QTY += a.F_QTY;
|
wh.T_MODIFY = DateTime.Now;
|
db.Updateable(wh).UpdateColumns(it => new { it.F_QTY, it.T_MODIFY }).ExecuteCommand();
|
}
|
else
|
{
|
//新增仓库量表
|
wh = new WHInventory { F_QTY = a.F_QTY, S_ITEM_CODE = a.S_ITEM_CODE };
|
db.Insertable(wh).ExecuteCommand();
|
}
|
//写入第三方中间表
|
//如果要统计分拣中，分拣回的量，无法跟踪托盘，除非对托盘加标识，属于哪个库区。
|
//另外分拣回可能去别的巷道，别的逻辑库区，逻辑库区的量控制更复杂，不能计算分拣中和分拣回。所以库区量表本项目暂不考虑
|
/*
|
//获取货位的物理库区和所有逻辑库区
|
var location = db.Queryable<Location>().Where(l => l.S_CODE == loc).First();
|
if (location != null) {
|
var az = db.Queryable<AZInventory>().Where(b => b.S_ITEM_CODE == a.S_ITEM_CODE && b.S_AREA_CODE == location.S_AREA_CODE).First();
|
if (az != null) {
|
//更新库区量表
|
az.F_QTY += a.F_QTY;
|
az.T_MODIFY = DateTime.Now;
|
db.Updateable(az).UpdateColumns(it => new { it.F_QTY, it.T_MODIFY }).ExecuteCommand();
|
}
|
else {
|
//新增库区量表
|
az = new AZInventory { F_QTY = a.F_QTY, S_ITEM_CODE = a.S_ITEM_CODE, S_AREA_CODE = location.S_AREA_CODE };
|
db.Insertable(az).ExecuteCommand();
|
}
|
|
}
|
//逻辑库区的量表
|
var zoneList = db.Queryable<ZoneLoc>().Where(l => l.S_LOC_CODE == loc).ToList();
|
if (zoneList.Count > 0) {
|
zoneList.ForEach(z => {
|
var az = db.Queryable<AZInventory>().Where(b => b.S_ITEM_CODE == a.S_ITEM_CODE && b.S_AREA_CODE == z.S_ZONE_CODE).First();
|
if (az != null) {
|
//更新库区量表
|
az.F_QTY += a.F_QTY;
|
az.T_MODIFY = DateTime.Now;
|
db.Updateable(az).UpdateColumns(it => new { it.F_QTY, it.T_MODIFY }).ExecuteCommand();
|
}
|
else {
|
//新增库区量表
|
az = new AZInventory { F_QTY = a.F_QTY, S_ITEM_CODE = a.S_ITEM_CODE, S_AREA_CODE = z.S_ZONE_CODE, C_IS_LOGIC_AREA = "Y" };
|
db.Insertable(az).ExecuteCommand();
|
}
|
});
|
}
|
*/
|
|
});
|
}
|
}
|
db.CommitTran();
|
}
|
catch (Exception ex)
|
{
|
Console.WriteLine(ex.Message);
|
db.RollbackTran();
|
|
}
|
}
|
}
|
|
/// <summary>
|
/// 绑定货位容器表
|
/// </summary>
|
/// <param name="cntr"></param>
|
/// <returns></returns>
|
internal static bool BindLocCntrs(string loc, string cntr, string itemCode, string itemName)
|
{
|
bool result = true;
|
List<string> list = new List<string>(cntr.Split(','));
|
var db = new SqlHelper<object>().GetInstance();
|
for (int i = 0; i < list.Count; i++)
|
{
|
if (!string.IsNullOrEmpty(list[i]))
|
{
|
string cntrCode = list[i];
|
if (db.Queryable<LocCntrRel>().Count(a => a.S_CNTR_CODE.Trim() == cntrCode) == 0)
|
{
|
var cir = new LocCntrRel { S_LOC_CODE = loc, S_CNTR_CODE = cntrCode };
|
var con = new Container { S_CODE = cntrCode,N_DETAIL_COUNT = 1 };
|
db.Insertable<LocCntrRel>(cir).ExecuteCommand();
|
db.Insertable<Container>(con).ExecuteCommand();
|
if (!string.IsNullOrEmpty(itemCode))
|
{
|
ContainerHelper.BindCntrItem(cntrCode, itemCode, itemName);
|
}
|
}
|
}
|
}
|
//1.0 查货位容器表
|
return result;
|
}
|
|
/// <summary>
|
/// 绑定容器物料表
|
/// </summary>
|
/// <param name="itemCode"></param>
|
/// <param name="batchNo"></param>
|
/// <param name="qty"></param>
|
/// <returns></returns>
|
internal static bool BindCntrItem(string trayCode, string itemCode, string itemName)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
try
|
{
|
db.BeginTran();
|
var cir = new CntrItemDetail { S_CNTR_CODE = trayCode, S_ITEM_CODE = itemCode, S_ITEM_NAME = itemName };
|
db.Insertable<CntrItemDetail>(cir).ExecuteCommand();
|
|
db.Ado.CommitTran();
|
res = true;
|
}
|
catch (Exception ex)
|
{
|
db.Ado.RollbackTran();
|
}
|
return res;
|
}
|
|
#region 杭氧货位容器物料帮助方法
|
|
/// <summary>
|
/// 修改容器表容器去向字段
|
/// </summary>
|
/// <param name="trayCode">托盘号</param>
|
/// <param name="abouts">容器去向</param>
|
/// <returns></returns>
|
internal static bool UpdateCntrAbouts(string trayCode, string abouts)
|
{
|
var res = false;
|
var db = new SqlHelper<object>().GetInstance();
|
var model = db.Queryable<LocCntrRel>().Where(a => a.S_CNTR_CODE == trayCode).First();
|
if (model != null)
|
{
|
model.S_ACTION_SRC = abouts;
|
model.T_MODIFY = DateTime.Now;
|
res = db.Updateable(model).UpdateColumns(it => new { it.S_ACTION_SRC, it.T_MODIFY }).ExecuteCommand() > 0;
|
}
|
return res;
|
}
|
|
|
/// <summary>
|
/// 查询被标记的托盘
|
/// </summary>
|
/// <param name="areaCode">库区</param>
|
/// <param name="abouts">标记</param>
|
/// <returns></returns>
|
internal static List<LocCntrRel> GetCntrAbouts(string areaCode, string abouts)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var model = db.Queryable<Location>().LeftJoin<LocCntrRel>((p, m) => p.S_CODE == m.S_LOC_CODE)
|
.Where((p,m)=>p.S_AREA_CODE == areaCode && p.S_LOCK_STATE == "无")
|
.Where((p,m)=>m.S_ACTION_SRC == abouts)
|
.Select((p,m)=>m)
|
.ToList();
|
return model;
|
}
|
|
internal static LocCntrRel GetCntrLoc(string trayCode)
|
{
|
var db = new SqlHelper<object>().GetInstance();
|
var model = db.Queryable<LocCntrRel>().Where(s=>s.S_CNTR_CODE == trayCode).First();
|
return model;
|
}
|
|
#endregion
|
|
|
}
|
}
|
