using HanHe.Utility.Data;
|
using HH.MData;
|
using HH.WMS.Entitys.Algorithm;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Entitys.External;
|
using MongoDB.Driver.Builders;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.External
|
{
|
public class TN_WM_B_OBJ_LOCATIONDAL : BaseDAL
|
{
|
#region 新增关联
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="entity"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/03/24</history>
|
public SqlExecuteResult Add(TN_WM_B_OBJ_LOCATIONEntity entity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into TN_WM_B_OBJ_LOCATION( ");
|
strSql.Append("CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_T_OPERATE)");
|
strSql.Append("values (:CN_GUID,:CN_S_OBJ,:CN_S_OBJ_CODE,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_N_INDEX,:CN_T_OPERATE)");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, entity.CN_S_OBJ);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, entity.CN_S_OBJ_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, entity.CN_S_STOCK_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, entity.CN_S_LOCATION_CODE);
|
DataAccess.AddInParameter(cmd, "CN_N_INDEX", ComDbType.INT, entity.CN_N_INDEX);
|
DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, DateTime.Now.ToString());
|
return ExecuteCommand(cmd, trans);
|
}
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="entity"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/03/24</history>
|
public SqlExecuteResult AddList(List<TN_WM_B_OBJ_LOCATIONEntity> entityList, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into TN_WM_B_OBJ_LOCATION( ");
|
strSql.Append("CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_T_OPERATE)");
|
strSql.Append("values (:CN_GUID,:CN_S_OBJ,:CN_S_OBJ_CODE,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_N_INDEX,:CN_T_OPERATE)");
|
|
List<DbCommand> cmds = new List<DbCommand>();
|
foreach (TN_WM_B_OBJ_LOCATIONEntity entity in entityList)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, entity.CN_S_OBJ);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, entity.CN_S_OBJ_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, entity.CN_S_STOCK_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, entity.CN_S_LOCATION_CODE);
|
DataAccess.AddInParameter(cmd, "CN_N_INDEX", ComDbType.INT, entity.CN_N_INDEX);
|
DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, DateTime.Now.ToString());
|
cmds.Add(cmd);
|
}
|
return ExecuteCommands(cmds, trans);
|
}
|
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="entity"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/07/30</history>
|
public SqlExecuteResult AddLocationList(List<TN_WM_B_OBJ_LOCATIONEntity> entityList, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into TN_WM_B_OBJ_LOCATION( ");
|
strSql.Append("CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_T_OPERATE)");
|
strSql.Append("values (:CN_GUID,:CN_S_OBJ,:CN_S_OBJ_CODE,:CN_S_STOCK_CODE,:CN_S_STOCK_AREA,:CN_S_LOCATION_CODE,:CN_N_INDEX,:CN_T_OPERATE)");
|
|
List<DbCommand> cmds = new List<DbCommand>();
|
foreach (TN_WM_B_OBJ_LOCATIONEntity entity in entityList)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, entity.CN_S_OBJ);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, entity.CN_S_OBJ_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, entity.CN_S_STOCK_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, entity.CN_S_LOCATION_CODE);
|
DataAccess.AddInParameter(cmd, "CN_N_INDEX", ComDbType.INT, entity.CN_N_INDEX);
|
DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, entity.CN_T_OPERATE);
|
cmds.Add(cmd);
|
}
|
return ExecuteCommands(cmds, trans);
|
}
|
#endregion
|
|
#region 根据对象编码获取列表
|
/// <summary>
|
/// 根据对象编码获取
|
/// </summary>
|
/// <param name="objCode"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_OBJ_LOCATIONEntity> GetListByObjCode(string obj, string objCode)
|
{
|
string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ_CODE=:CN_S_OBJ_CODE AND CN_S_OBJ=:CN_S_OBJ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, obj);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
}
|
#endregion
|
|
/// <summary>
|
/// 设置实体的各列
|
/// <summary>
|
private void SetEntity(TN_WM_B_OBJ_LOCATIONEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
|
SetEntityUti(entity, "CN_S_OBJ", "CN_S_OBJ", reader);
|
SetEntityUti(entity, "CN_S_OBJ_CODE", "CN_S_OBJ_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
|
SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);
|
SetEntityUti(entity, "CN_N_INDEX", "CN_N_INDEX", reader);
|
SetEntityUti(entity, "CN_T_OPERATE", "CN_T_OPERATE", reader);
|
// SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader);
|
// SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
|
}
|
private void SetEntityItem(TN_WM_B_OBJ_LOCATIONEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
|
SetEntityUti(entity, "CN_S_OBJ", "CN_S_OBJ", reader);
|
SetEntityUti(entity, "CN_S_OBJ_CODE", "CN_S_OBJ_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
|
SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);
|
SetEntityUti(entity, "CN_N_INDEX", "CN_N_INDEX", reader);
|
SetEntityUti(entity, "CN_T_OPERATE", "CN_T_OPERATE", reader);
|
SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader);
|
SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
|
SetEntityUti(entity, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", reader);
|
}
|
private void SetEntityItemSxcc(TN_WM_B_OBJ_LOCATIONEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
|
SetEntityUti(entity, "CN_S_OBJ", "CN_S_OBJ", reader);
|
SetEntityUti(entity, "CN_S_OBJ_CODE", "CN_S_OBJ_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
|
SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);
|
SetEntityUti(entity, "CN_N_INDEX", "CN_N_INDEX", reader);
|
SetEntityUti(entity, "CN_T_OPERATE", "CN_T_OPERATE", reader);
|
SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader);
|
SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
|
SetEntityUti(entity, "CN_S_VENDOR_NO", "CN_S_VENDOR_NO", reader);
|
SetEntityUti(entity, "CN_S_VENDOR_NAME", "CN_S_VENDOR_NAME", reader);
|
SetEntityUti(entity, "CN_S_FROM_NO", "CN_S_FROM_NO", reader);
|
SetEntityUti(entity, "CN_S_FIGURE_NO", "CN_S_FIGURE_NO", reader);
|
}
|
private void SetEntityObj(TN_WM_B_OBJ_LOCATIONEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
|
SetEntityUti(entity, "CN_S_OBJ", "CN_S_OBJ", reader);
|
SetEntityUti(entity, "CN_S_OBJ_CODE", "CN_S_OBJ_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
|
SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);
|
SetEntityUti(entity, "CN_N_INDEX", "CN_N_INDEX", reader);
|
SetEntityUti(entity, "CN_T_OPERATE", "CN_T_OPERATE", reader);
|
}
|
|
#region 根据对象编码获取
|
/// <summary>
|
/// 根据对象编码获取
|
/// </summary>
|
/// <param name="objCode"></param>
|
/// <returns></returns>
|
public TN_WM_B_OBJ_LOCATIONEntity GetEntityByObjCode(string obj, string objCode)
|
{
|
string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ_CODE=:CN_S_OBJ_CODE AND CN_S_OBJ=:CN_S_OBJ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, obj);
|
return DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
}
|
#endregion
|
|
public DataTable GetLocationContent(string areaCode, string location)
|
{
|
string sql = @" select * from TN_WM_B_OBJ_LOCATION where 1=1 ";
|
if (!string.IsNullOrEmpty(areaCode))
|
sql += " and CN_S_STOCK_AREA ='" + areaCode + "'";
|
if (!string.IsNullOrEmpty(location))
|
sql += " and CN_S_LOCATION_CODE ='" + location + "'";
|
sql += " order by CN_S_LOCATION_CODE,CN_N_INDEX ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
|
#region 根据对象、 多个库区获取货位
|
/// <summary>
|
/// 根据对象、 多个库区获取货位
|
/// </summary>
|
/// <param name="outOrder"></param>
|
/// <param name="area"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(zh)] CREATED 2018/05/9</history>
|
public string GetLocationCode(string objCode, string obj)
|
{
|
string sql = @"SELECT CN_S_LOCATION_CODE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ=:CN_S_OBJ AND CN_S_OBJ_CODE=:CN_S_OBJ_CODE ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, obj);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
|
var entity = DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd,
|
(e, r) =>
|
{
|
SetEntityUti(e, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", r);
|
});
|
if (entity != null)
|
return entity.CN_S_LOCATION_CODE;
|
return string.Empty;
|
}
|
#endregion
|
|
#region 批量清空货位
|
/// <summary>
|
/// 批量清空货位
|
/// </summary>
|
/// <param name="pType"></param>
|
/// <param name="pCodes"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult Clean(string pType, string[] pCodes, DbTransaction trans)
|
{
|
List<DbCommand> cmds = new List<DbCommand>();
|
if (!string.IsNullOrEmpty(pType) && pCodes.Length > 0)
|
{
|
string sql = string.Empty;
|
switch (pType)
|
{
|
case "托盘":
|
foreach (string m in pCodes)
|
{
|
sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ='托盘' AND CN_S_OBJ_CODE='" + m + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
cmds.Add(cmd);
|
}
|
break;
|
case "货位":
|
foreach (string m in pCodes)
|
{
|
sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE='" + m + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
cmds.Add(cmd);
|
}
|
break;
|
}
|
return ExecuteCommands(cmds, trans);
|
}
|
return new SqlExecuteResult();
|
}
|
#endregion
|
|
#region 清空货位、托盘
|
/// <summary>
|
/// 清空货位、托盘
|
/// </summary>
|
/// <param name="pType"></param>
|
/// <param name="pCode"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(zh)] CREATED 2018/5/4</history>
|
public SqlExecuteResult Clean(string pType, string pCode, DbTransaction trans)
|
{
|
if (!string.IsNullOrEmpty(pType) && !string.IsNullOrEmpty(pCode))
|
{
|
string sql = string.Empty;
|
pCode = pCode.Replace("\'", "");
|
switch (pType)
|
{
|
case "托盘":
|
sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ='托盘' AND CN_S_OBJ_CODE='" + pCode + "'";
|
break;
|
case "货位":
|
sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE='" + pCode + "'";
|
break;
|
}
|
if (!string.IsNullOrEmpty(pCode))
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
}
|
return new SqlExecuteResult();
|
}
|
/// <summary>
|
///
|
/// </summary>
|
/// <param name="locaion"></param>
|
/// <param name="trayCode"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult UnBanding(string locaion, string trayCode, DbTransaction trans)
|
{
|
string sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE and CN_S_OBJ_CODE=:CN_S_OBJ_CODE";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
this.DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, locaion);
|
this.DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, trayCode);
|
return ExecuteCommand(cmd, trans);
|
}
|
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <param name="sqlwhere"></param>
|
/// <returns></returns>
|
public SqlExecuteResult DeleteObj(string sqlwhere, DbTransaction trans)
|
{
|
string sql = "DELETE FROM TN_WM_B_OBJ_LOCATION WHERE 1=1 {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 批量新增
|
/// <summary>
|
/// 批量新增
|
/// </summary>
|
/// <param name="objLocationRels"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
///<History>[Hanhe(DBS)] created 2018-5-8</History>
|
public SqlExecuteResult Add(List<TN_WM_B_OBJ_LOCATIONEntity> objLocationRels, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into TN_WM_B_OBJ_LOCATION( ");
|
strSql.Append("CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_T_OPERATE)");
|
strSql.Append("values ( @CN_GUID,@CN_S_OBJ,@CN_S_OBJ_CODE,@CN_S_STOCK_CODE,@CN_S_STOCK_AREA,@CN_S_LOCATION_CODE,@CN_N_INDEX,@CN_T_OPERATE)");
|
|
List<DbCommand> cmds = new List<DbCommand>();
|
foreach (TN_WM_B_OBJ_LOCATIONEntity entity in objLocationRels)
|
{
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_GUID", DbType.String, entity.CN_GUID);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ", DbType.String, entity.CN_S_OBJ);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", DbType.String, entity.CN_S_OBJ_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", DbType.String, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", DbType.String, entity.CN_S_STOCK_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", DbType.String, entity.CN_S_LOCATION_CODE);
|
DataAccess.AddInParameter(cmd, "CN_N_INDEX", ComDbType.INT, entity.CN_N_INDEX);
|
DataAccess.AddInParameter(cmd, "CN_T_OPERATE", ComDbType.DATE, DateTime.Now.ToString());
|
cmds.Add(cmd);
|
}
|
return ExecuteCommands(cmds, trans);
|
}
|
#endregion
|
|
#region 判断货位是否含有关联
|
/// <summary>
|
/// 判断货位是否含有关联
|
/// </summary>
|
/// <param name="locationCode"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_OBJ_LOCATIONEntity> GetListByLocationCode(string locationCode, DbTransaction trans, string objCode = "")
|
{
|
List<TN_WM_B_OBJ_LOCATIONEntity> list = new List<TN_WM_B_OBJ_LOCATIONEntity>();
|
StringBuilder strSql = new StringBuilder();
|
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("MYSQL"))
|
{
|
strSql.Append("SELECT CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,IFNULL(CN_N_INDEX,0) CN_N_INDEX,CN_T_OPERATE FROM TN_WM_B_OBJ_LOCATION");
|
}
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("MSSQL"))
|
{
|
strSql.Append("SELECT CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,ISNULL(CN_N_INDEX,0) CN_N_INDEX,CN_T_OPERATE FROM TN_WM_B_OBJ_LOCATION");
|
}
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
|
{
|
strSql.Append("SELECT CN_GUID,CN_S_OBJ,CN_S_OBJ_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,NVL(CN_N_INDEX,0) CN_N_INDEX,CN_T_OPERATE FROM TN_WM_B_OBJ_LOCATION");
|
}
|
|
if (!string.IsNullOrEmpty(locationCode))
|
{
|
strSql.Append(" WHERE CN_S_LOCATION_CODE='" + locationCode + "' ");
|
}
|
else
|
{
|
return list;
|
}
|
if (!string.IsNullOrEmpty(objCode))
|
{
|
strSql.Append(" AND CN_S_OBJ_CODE!='" + objCode + "' ");
|
}
|
strSql.Append(" ORDER BY CN_N_INDEX DESC ");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
|
list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
return list;
|
|
}
|
#endregion
|
|
public TN_WM_B_OBJ_LOCATIONEntity GetModel(string location, string objCode)
|
{
|
string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE and CN_S_OBJ_CODE=:CN_S_OBJ_CODE";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
|
return DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
}
|
|
public List<TN_WM_B_OBJ_LOCATIONEntity> GetModelByLocation(string location)
|
{
|
string sql = @" SELECT
|
A.*,B.CN_S_ITEM_CODE,CN_S_ITEM_NAME,B.CN_S_ITEM_STATE
|
FROM
|
TN_WM_B_OBJ_LOCATION A LEFT JOIN [dbo].[tn_wm_b_tray_item_rel] B
|
ON
|
A.CN_S_OBJ_CODE=B.CN_S_TRAY_CODE
|
WHERE
|
CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ORDER BY A.CN_T_OPERATE ASC ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntityItem);
|
}
|
|
|
public List<TN_WM_B_OBJ_LOCATIONEntity> GetModelByLocationSxcc(string location)
|
{
|
string sql = @" SELECT
|
A.*,B.CN_S_ITEM_CODE,CN_S_ITEM_NAME,B.CN_S_VENDOR_NO,B.CN_S_VENDOR_NAME,B.CN_S_FIGURE_NO,B.CN_S_FROM_NO
|
FROM
|
TN_WM_B_OBJ_LOCATION A LEFT JOIN [dbo].[tn_wm_b_tray_item_rel] B
|
ON
|
A.CN_S_OBJ_CODE=B.CN_S_TRAY_CODE
|
WHERE
|
CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ORDER BY A.CN_T_OPERATE ASC ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntityItemSxcc);
|
}
|
|
|
public List<TN_WM_B_OBJ_LOCATIONEntity> SxccGetModel(string qybw, string kh, string qzsd, string xh)
|
{
|
string sql = @" SELECT
|
DISTINCT A.*
|
FROM
|
TN_WM_B_OBJ_LOCATION A INNER JOIN [dbo].[tn_wm_b_tray_item_rel] B
|
ON
|
A.CN_S_OBJ_CODE=B.CN_S_TRAY_CODE
|
WHERE 1=1 ";
|
if (!string.IsNullOrEmpty(qybw))
|
sql += " and B.CN_S_VENDOR_NO like '%" + qybw + "%'";
|
if (!string.IsNullOrEmpty(kh))
|
sql += " and B.CN_S_VENDOR_NAME like '%" + kh + "%'";
|
if (!string.IsNullOrEmpty(qzsd))
|
sql += " and B.CN_S_FIGURE_NO like '%" + qzsd + "%'";
|
if (!string.IsNullOrEmpty(xh))
|
sql += " and B.CN_S_FROM_NO like '%" + xh + "%'";
|
|
|
sql += " ORDER BY A.CN_S_LOCATION_CODE,A.CN_N_INDEX ASC ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
}
|
|
#region 更新对象位置
|
/// <summary>
|
/// 更新对象位置
|
/// </summary>
|
/// <param name="objCode"></param>
|
/// <param name="area"></param>
|
/// <param name="location"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public SqlExecuteResult UpdateObjPosition(string objCode, string area, string location, DbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_OBJ_LOCATION SET CN_S_STOCK_AREA=:CN_S_STOCK_AREA , CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE WHERE CN_S_OBJ_CODE=:CN_S_OBJ_CODE ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_AREA", ComDbType.STRING, area);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 获取对象列表
|
public DataTable GetList(string objCode)
|
{
|
string sql = "select * from TN_WM_B_OBJ_LOCATION where CN_S_OBJ_CODE=:CN_S_OBJ_CODE";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
|
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
|
|
public DataTable GetList(string obj, string code)
|
{
|
string sql = "select * from TN_WM_B_OBJ_LOCATION CN_S_OBJ=:CN_S_OBJ and CN_S_OBJ_CODE=:CN_S_OBJ_CODE";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, code);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
|
public List<TN_WM_B_OBJ_LOCATIONEntity> GetExtendList(string objCode)
|
{
|
string sql = "select * from TN_WM_B_OBJ_LOCATION where CN_S_OBJ_CODE in(" + objCode + ")";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
List<TN_WM_B_OBJ_LOCATIONEntity> list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
return list;
|
}
|
|
public List<TN_WM_B_OBJ_LOCATIONEntity> GetExtendListByArea(string areaCode)
|
{
|
string sql = "select a.* from TN_WM_B_OBJ_LOCATION a inner join tn_wm_b_location_ext b on a.cn_s_location_code = b.cn_s_location_code where a.CN_S_STOCK_AREA = '" + areaCode + "' AND a.CN_S_OBJ = '托盘' and b.cn_s_location_state = '正常' ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
List<TN_WM_B_OBJ_LOCATIONEntity> list = DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
return list;
|
}
|
#endregion
|
|
#region 根据对象、 库区获取货位
|
/// <summary>
|
/// 根据对象、 库区获取货位
|
/// </summary>
|
/// <param name="outOrder"></param>
|
/// <param name="area"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(zh)] CREATED 2018/05/9</history>
|
public string GetLocationCode(string objCode, string area, string obj)
|
{
|
string sql = @"SELECT CN_S_LOCATION_CODE FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_OBJ=:CN_S_OBJ AND CN_S_OBJ_CODE=:CN_S_OBJ_CODE ";
|
if (!string.IsNullOrEmpty(area))
|
sql += " AND CN_S_STOCK_AREA='" + area + "' ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ", ComDbType.STRING, obj);
|
DataAccess.AddInParameter(cmd, "CN_S_OBJ_CODE", ComDbType.STRING, objCode);
|
var entity = DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd,
|
(e, r) =>
|
{
|
SetEntityUti(e, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", r);
|
});
|
if (entity != null)
|
return entity.CN_S_LOCATION_CODE;
|
return string.Empty;
|
}
|
#endregion
|
|
#region 根据仓库和库区编码获得库区结构
|
/// <summary>
|
/// 根据仓库和库区编码获得库区结构
|
/// </summary>
|
/// <param name="stockCode">仓库编号</param>
|
/// <param name="areaCode">库区编号</param>
|
/// <returns></returns>
|
/// <history>[HanHe(LT)] CREATED 2018/11/30</history>
|
public List<AutoBomStockStructreEntity> GetStructList(string stockCode, string areaCode)
|
{
|
var query = Query.And(Query.EQ("CN_S_STOCK_CODE", stockCode), Query.EQ("CN_S_AREA_CODE", areaCode));
|
//仓库结构
|
List<AutoBomStockStructreEntity> lstStruct = MongoDBSingleton.Instance.Find<AutoBomStockStructreEntity>(query, "TN_AB_B_STOCK_STRUCTURE");
|
|
return lstStruct;
|
}
|
#endregion
|
|
public List<TN_WM_B_OBJ_LOCATIONEntity> GetEntityList(string location)
|
{
|
string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ORDER BY CN_T_OPERATE ASC";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
}
|
public TN_WM_B_OBJ_LOCATIONEntity GetEntity(string location)
|
{
|
string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE=:CN_S_LOCATION_CODE ORDER BY CN_T_OPERATE ASC";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
DataAccess.AddInParameter(cmd, "CN_S_LOCATION_CODE", ComDbType.STRING, location);
|
return DataAccessExtensive.ExecuteSingleEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
}
|
public List<TN_WM_B_OBJ_LOCATIONEntity> GetObjLocationList(string locations)
|
{
|
string sql = @"SELECT * FROM TN_WM_B_OBJ_LOCATION WHERE CN_S_LOCATION_CODE in(" + locations + ") ";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_OBJ_LOCATIONEntity>(this.DataAccess, cmd, SetEntity);
|
}
|
}
|
}
|
