using HH.WMS.Entitys;
|
using HH.WMS.Entitys.Entitys;
|
using HH.WMS.Entitys.External;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.InStock
|
{
|
public class TN_WM_B_TRAY_LOCATIONDAL : DapperBaseDAL
|
{
|
#region 根据货位找托盘及其物料
|
/// <summary>
|
/// 根据货位获取货位上托盘物料信息
|
/// </summary>
|
/// <param name="CN_S_LOCATION_CODE">货位编码</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-13</History>
|
public DataTable GetTrayByLocationCode(string CN_S_LOCATION_CODE)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT TI.CN_S_TRAY_CODE,CN_S_LOT_NO,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,TI.CN_F_QUANTITY ,TIDTL.CN_S_PRODUCTION_BATCH,TI.CN_S_MEASURE_UNIT,ti.CN_S_MODEL ");
|
sbStr.Append("FROM TN_WM_B_TRAY_ITEM_MST TI LEFT JOIN TN_WM_B_TRAY_LOCATION TL ON TI.CN_S_TRAY_CODE=TL.CN_S_TRAY_CODE ");
|
sbStr.Append(" LEFT JOIN TN_WM_B_TRAY_ITEM_DTL TIDTL ON TI.CN_GUID=TIDTL.CN_PARENT_GUID ");
|
if (!string.IsNullOrEmpty(CN_S_LOCATION_CODE))
|
{
|
sbStr.Append("WHERE CN_S_LOCATION_CODE='" + CN_S_LOCATION_CODE.Trim() + "' ");
|
}
|
return ExecuteDataTable(sbStr.ToString());
|
}
|
#endregion
|
|
#region 获取货位列表
|
/// <summary>
|
/// 根据托盘编码获取托盘货位关联列表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public List<TN_WM_B_TRAY_LOCATIONEntity> GetListByTrayCode(string trayCode, string locationCode = "")
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT * FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE IN(SELECT CN_S_LOCATION_CODE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_TRAY_CODE='" + trayCode + "')");
|
if (!string.IsNullOrEmpty(locationCode))
|
{
|
sbStr.Append(" AND CN_S_LOCATION_CODE='" + locationCode + "' ");
|
}
|
return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sbStr.ToString());
|
}
|
#endregion
|
|
#region 获取货位列表
|
/// <summary>
|
/// 根据托盘编码获取托盘货位关联列表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public List<TN_WM_B_TRAY_LOCATIONEntity> GetListByLocationCode(string locationCode)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT * FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE='" + locationCode + "' ");
|
|
return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sbStr.ToString());
|
}
|
#endregion
|
#region 根据货位列表查询托盘信息
|
/// <summary>
|
/// 根据托盘编码获取托盘货位关联列表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public List<TN_WM_B_TRAY_LOCATIONEntity> GetListByLocationCodeList(List<string> locationCode)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
string locations = string.Join(",", locationCode).Replace(",", "','");
|
sbStr.Append("SELECT * FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE in ('" + locations + "') ");
|
return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sbStr.ToString());
|
}
|
#endregion
|
|
#region 获取虚拟货位列表（根据库区码查询）
|
/// <summary>
|
/// 根据托盘编码获取托盘货位关联列表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public List<TN_WM_B_TRAY_LOCATIONEntity> GetlistXnAreaCode(string areaCode)
|
{
|
string sql = @"
|
SELECT b.CN_GUID,b.CN_S_ITEM_CODE,a.*FROM dbo.tn_wm_b_tray_location AS a
|
LEFT JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS b ON b.CN_S_TRAY_CODE = a.CN_S_TRAY_CODE WHERE CN_S_LOCATION_CODE IN (SELECT CN_S_LOCATION_CODE from dbo.tn_wm_b_location_ext where CN_S_AREA_CODE='" + areaCode + "' )";
|
return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sql);
|
}
|
#endregion
|
|
#region 新增货位拓展表
|
/// <summary>
|
/// 新增货位拓展表
|
/// </summary>
|
/// <param name="list"></param>
|
/// <param name="trans"></param>
|
/// <History>[HANHE(LT)] CREATED BY 2018-12-26</History>
|
///
|
public OperateResult addlist(List<TN_WM_B_TRAY_LOCATIONEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (TN_WM_B_TRAY_LOCATIONEntity model in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" if not exists(select * from TN_WM_B_TRAY_LOCATION " + model.sqlWhere + " )");
|
strSql.Append("INSERT INTO TN_WM_B_TRAY_LOCATION (CN_S_TRAY_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE)");
|
strSql.Append(" values (");
|
strSql.Append("@CN_S_TRAY_CODE,@CN_S_STOCK_CODE,@CN_S_STOCK_AREA,@CN_S_LOCATION_CODE,@CN_N_INDEX,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE)");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_S_TRAY_CODE = model.CN_S_TRAY_CODE,
|
CN_S_STOCK_CODE = model.CN_S_STOCK_CODE,
|
CN_S_STOCK_AREA = model.CN_S_STOCK_AREA,
|
CN_S_LOCATION_CODE = model.CN_S_LOCATION_CODE,
|
CN_N_INDEX = model.CN_N_INDEX,
|
CN_S_CREATOR = model.CN_S_CREATOR,
|
CN_S_CREATOR_BY = model.CN_S_CREATOR_BY,
|
CN_T_CREATE = model.CN_T_CREATE,
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
|
}
|
|
public OperateResult Add(TN_WM_B_TRAY_LOCATIONEntity trayLocation, IDbTransaction trans)
|
{
|
return DALCreator.Create<DapperDAL<TN_WM_B_TRAY_LOCATIONEntity>>().Add(trayLocation, trans);
|
}
|
|
//public OperateResult Add(TN_WM_B_TRAY_LOCATIONEntity trayLocation, IDbTransaction trans)
|
//{
|
// var strSql = new StringBuilder();
|
// strSql.Append(" if not exists(select * from TN_WM_B_TRAY_LOCATION " + trayLocation.sqlWhere + " )");
|
// strSql.Append("INSERT INTO TN_WM_B_TRAY_LOCATION (CN_S_TRAY_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE)");
|
// strSql.Append(" values (");
|
// strSql.Append("@CN_S_TRAY_CODE,@CN_S_STOCK_CODE,@CN_S_STOCK_AREA,@CN_S_LOCATION_CODE,@CN_N_INDEX,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE)");
|
|
// var result = ExecuteTranSql(strSql.ToString(), new
|
// {
|
// CN_S_TRAY_CODE = trayLocation.CN_S_TRAY_CODE,
|
// CN_S_STOCK_CODE = trayLocation.CN_S_STOCK_CODE,
|
// CN_S_STOCK_AREA = trayLocation.CN_S_STOCK_AREA,
|
// CN_S_LOCATION_CODE = trayLocation.CN_S_LOCATION_CODE,
|
// CN_N_INDEX = trayLocation.CN_N_INDEX,
|
// CN_S_CREATOR = trayLocation.CN_S_CREATOR,
|
// CN_S_CREATOR_BY = trayLocation.CN_S_CREATOR_BY,
|
// CN_T_CREATE = trayLocation.CN_T_CREATE,
|
// }, trans);
|
// if (result.Status == ResultStatus.Error)
|
// return result;
|
// return result;
|
|
//}
|
#endregion
|
|
#region 根据货位查询托盘与货位关联表
|
/// <summary>
|
/// 根据托盘编码获取托盘货位关联列表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public List<TN_WM_B_TRAY_LOCATIONEntity> GetTrayLocationBylocationCode(string locationCodes)
|
{
|
string sql = "SELECT a.*,b.CN_S_OWNER,b.CN_S_ITEM_CODE,b.CN_S_FIGURE_NO,b.CN_S_MODEL,b.CN_S_ITEM_STATE,b.CN_S_MEASURE_UNIT,b.CN_GUID FROM dbo.tn_wm_b_tray_location AS a LEFT JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS b ON b.CN_S_TRAY_CODE = a.CN_S_TRAY_CODE where a.CN_S_LOCATION_CODE IN ('" + locationCodes + "')";
|
return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sql);
|
}
|
#endregion
|
|
#region 入库单反审时获取数据
|
/// <summary>
|
/// 入库单反审时获取数据
|
/// </summary>
|
/// <param name="opNoList"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_LOCATIONEntity> GetTrayCodeByIncrease(string opNoList)
|
{
|
string sql = "SELECT CN_S_TRAY_CODE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE in( select CN_S_PUT_POSITION from TN_WM_ARRIVAL_DTL where CN_S_ARRIVAL_NO IN(SELECT CN_S_FROM_NO FROM TN_WM_INCREASE_INVENTORY_MST WHERE CN_S_OP_NO in (" + opNoList + ") and (CN_S_FROM_NO!='' and CN_S_FROM_NO is not null)))";
|
return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sql);
|
}
|
|
public OperateResult DeleteByTrayCode(string opNoList, IDbTransaction trans)
|
{
|
string sql = "DELETE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_TRAY_CODE IN(" + opNoList + ")";
|
return ExecuteTranSql(sql, null, trans);
|
}
|
#endregion
|
|
#region 查询托盘是否绑定货位
|
/// <summary>
|
/// 根据托盘编码获取托盘货位关联列表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public bool CheckTrayBindLoc(string trayCode)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT CN_S_TRAY_CODE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_TRAY_CODE=@CN_S_TRAY_CODE ");
|
|
DataTable dt = ExecuteDataTable(sbStr.ToString(), new { CN_S_TRAY_CODE = trayCode });
|
if (dt.Rows.Count > 0)
|
{
|
return true;
|
}
|
else
|
{
|
return false;
|
}
|
}
|
#endregion
|
|
#region 查询托盘是否绑定货位
|
/// <summary>
|
/// 根据托盘编码获取托盘货位关联列表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public bool CheckTrayBindLocGroup(string mstGuid)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT A.CN_S_TRAY_CODE FROM TN_WM_B_TRAY_LOCATION A INNER JOIN TN_WM_B_TRAY_ITEM_MST B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE WHERE B.CN_GUID=@CN_GUID ");
|
|
DataTable dt = ExecuteDataTable(sbStr.ToString(), new { CN_GUID = mstGuid });
|
if (dt.Rows.Count > 0)
|
{
|
return true;
|
}
|
else
|
{
|
return false;
|
}
|
}
|
#endregion
|
|
#region AGV可用货位查询
|
/// <summary>
|
/// 根据托盘编码获取托盘货位关联列表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public List<AgvLocationQuery> GetAGVLocationStatus(string stockCode,string strLocation)
|
{
|
StringBuilder sbSql = new StringBuilder();
|
sbSql.Append("SELECT b.CN_S_LOCATION_CODE,a.CN_S_TRAY_CODE,b.CN_S_LOCATION_STATE,b.CN_S_USE_STATE FROM tn_wm_b_tray_location a RIGHT JOIN tn_wm_b_location_ext b ON a.CN_S_LOCATION_CODE = b.CN_S_LOCATION_CODE ");
|
if (!string.IsNullOrEmpty(strLocation))
|
{
|
sbSql.Append("where b.CN_S_LOCATION_CODE = '" + strLocation + "'");
|
}
|
if (!string.IsNullOrEmpty(stockCode))
|
{
|
sbSql.Append("where b.CN_S_STOCK_CODE like '%" + stockCode + "%'");
|
}
|
return ExecuteQuery<AgvLocationQuery>(sbSql.ToString()); ;
|
}
|
#endregion
|
}
|
}
|
