using HanHe.Utility.Data;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Entitys.MoveStock;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Data.OracleClient;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.MoveStock
|
{
|
public class TN_WM_B_MOVE_RESULTDAL : DapperBaseDAL
|
{
|
#region 获取分页列表
|
/// <summary>
|
/// 获取分页列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/21</history>
|
public DataTable GetDataTable(dynamic obj)
|
{
|
DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
|
this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, "TN_WM_B_MOVE_RESULT");
|
this.DataAccess.AddInParameter(cmd, "WhereStr", ComDbType.CHAR, obj.sqlWhere);
|
this.DataAccess.AddInParameter(cmd, "OrderByStr", ComDbType.CHAR, obj.OrdeBy);
|
this.DataAccess.AddInParameter(cmd, "PageSize", ComDbType.INT, obj.pageSize);
|
this.DataAccess.AddInParameter(cmd, "PageIndex", ComDbType.INT, obj.pageIndex);
|
this.DataAccess.AddOutParameter(cmd, "TotalPage", ComDbType.INT, 4);
|
this.DataAccess.AddOutParameter(cmd, "TotalRecord", ComDbType.INT, 4);
|
|
//如果是oracle 增加特性
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
|
{
|
//处理游标类型 因DbType无游标类型,游标类型单独处理
|
ComDbType.AddOrcOutParameter(cmd, "v_cur", OracleType.Cursor);
|
}
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
obj.PageCount = Convert.ToInt32(this.DataAccess.GetParameterValue(cmd, "TotalRecord"));
|
return dt;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
|
|
/// <summary>
|
/// 获取分页列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/11/5</history>
|
public DataTable GetDataTableGroup(dynamic obj)
|
{
|
//获取策略 判断是否要审核
|
//string sysConfig = SysConfig.GetValue(Constants.CL_IsAllowMixing);
|
string appendlot = "";
|
//if (sysConfig.Length > 0)
|
//{
|
// if (sysConfig.Equals("N"))
|
// {
|
// appendlot = ",CN_S_LOT_CODE";
|
// }
|
//}
|
|
string sql = @"(SELECT CN_S_OP_NO,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_SERIAL_NO,CN_S_MEASURE_UNIT,CN_S_MODEL,CN_S_ITEM_STATE,CN_S_FIGURE_NO" + appendlot + ",MAX(CN_F_QUANTITY) AS CN_F_QUANTITY,SUM(CN_F_OUT_QUANTITY) AS CN_F_OUT_QUANTITY,SUM(CN_F_IN_QUANTITY) AS CN_F_IN_QUANTITY,CN_S_TRAY_CODE,CN_S_START_BIT,CN_S_END_BIT,CN_S_NOTE FROM tn_wm_b_move_result GROUP BY CN_S_OP_NO,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_SERIAL_NO,CN_S_MEASURE_UNIT,CN_S_MODEL,CN_S_ITEM_STATE,CN_S_FIGURE_NO" + appendlot + ",CN_S_TRAY_CODE,CN_S_START_BIT,CN_S_END_BIT,CN_S_NOTE) as a";
|
DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
|
this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, sql);
|
this.DataAccess.AddInParameter(cmd, "WhereStr", ComDbType.CHAR, obj.sqlWhere);
|
this.DataAccess.AddInParameter(cmd, "OrderByStr", ComDbType.CHAR, obj.OrdeBy);
|
this.DataAccess.AddInParameter(cmd, "PageSize", ComDbType.INT, obj.pageSize);
|
this.DataAccess.AddInParameter(cmd, "PageIndex", ComDbType.INT, obj.pageIndex);
|
this.DataAccess.AddOutParameter(cmd, "TotalPage", ComDbType.INT, 4);
|
this.DataAccess.AddOutParameter(cmd, "TotalRecord", ComDbType.INT, 4);
|
|
//如果是oracle 增加特性
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
|
{
|
//处理游标类型 因DbType无游标类型,游标类型单独处理
|
ComDbType.AddOrcOutParameter(cmd, "v_cur", OracleType.Cursor);
|
}
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
obj.PageCount = Convert.ToInt32(this.DataAccess.GetParameterValue(cmd, "TotalRecord"));
|
return dt;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
|
|
/// <summary>
|
/// 获取移库明细分页列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/11/5</history>
|
public DataTable GetMoveResultList(string sqlwhere)
|
{
|
string sql = "select * from (SELECT CN_S_OP_NO,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_SERIAL_NO,CN_S_MEASURE_UNIT,CN_S_MODEL,CN_S_ITEM_STATE,CN_S_FIGURE_NO,'' as CN_S_LOT_CODE,MAX(CN_F_QUANTITY) AS CN_F_QUANTITY,SUM(CN_F_OUT_QUANTITY) AS CN_F_OUT_QUANTITY,SUM(CN_F_IN_QUANTITY) AS CN_F_IN_QUANTITY,CN_S_TRAY_CODE,CN_S_START_BIT,CN_S_END_BIT,CN_S_NOTE FROM tn_wm_b_move_result GROUP BY CN_S_OP_NO,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_SERIAL_NO,CN_S_MEASURE_UNIT,CN_S_MODEL,CN_S_ITEM_STATE,CN_S_FIGURE_NO,CN_S_TRAY_CODE,CN_S_START_BIT,CN_S_END_BIT,CN_S_NOTE) as a {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
|
/// <summary>
|
/// 获取分页列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/3</history>
|
public DataTable GetDataTableGroup(string sqlWhere, string OrdeBy, int pageSize, int pageIndex)
|
{
|
string sql = @"TN_WM_B_MOVE_RESULT";
|
DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
|
this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, sql);
|
this.DataAccess.AddInParameter(cmd, "WhereStr", ComDbType.CHAR, sqlWhere);
|
this.DataAccess.AddInParameter(cmd, "OrderByStr", ComDbType.CHAR, OrdeBy);
|
this.DataAccess.AddInParameter(cmd, "PageSize", ComDbType.INT, pageSize);
|
this.DataAccess.AddInParameter(cmd, "PageIndex", ComDbType.INT, pageIndex);
|
this.DataAccess.AddOutParameter(cmd, "TotalPage", ComDbType.INT, 4);
|
this.DataAccess.AddOutParameter(cmd, "TotalRecord", ComDbType.INT, 4);
|
|
//如果是oracle 增加特性
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
|
{
|
//处理游标类型 因DbType无游标类型,游标类型单独处理
|
ComDbType.AddOrcOutParameter(cmd, "v_cur", OracleType.Cursor);
|
}
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
//obj.PageCount = Convert.ToInt32(this.DataAccess.GetParameterValue(cmd, "TotalRecord"));
|
return dt;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
#endregion
|
|
#region 获取移库明细查询
|
/// <summary>
|
/// 获取盘点结果查询
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/9/11</history>
|
public DataTable GetDataTable(string sqlwhere)
|
{
|
string sql = "select * from TN_WM_B_MOVE_RESULT {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
#endregion
|
|
#region 新增
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="entity">实体类</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public SqlExecuteResult Add(TN_WM_B_MOVE_RESULTEntity entity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into tn_wm_b_move_result(");
|
strSql.Append("CN_S_OP_NO,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_SERIAL_NO,CN_S_MEASURE_UNIT,CN_S_MODEL,CN_S_ITEM_STATE,CN_S_FIGURE_NO,CN_S_LOT_CODE,CN_F_QUANTITY,CN_F_OUT_QUANTITY,CN_F_IN_QUANTITY,CN_S_TRAY_CODE,CN_S_START_BIT,CN_S_END_BIT,CN_S_NOTE)");
|
strSql.Append(" values (");
|
strSql.Append(":CN_S_OP_NO,:CN_S_OWNER,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_SERIAL_NO,:CN_S_MEASURE_UNIT,:CN_S_MODEL,:CN_S_ITEM_STATE,:CN_S_FIGURE_NO,:CN_S_LOT_CODE,:CN_F_QUANTITY,:CN_F_OUT_QUANTITY,:CN_F_IN_QUANTITY,:CN_S_TRAY_CODE,:CN_S_START_BIT,:CN_S_END_BIT,:CN_S_NOTE)");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, entity.CN_S_OP_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.STRING, entity.CN_S_SERIAL_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_CODE", ComDbType.STRING, entity.CN_S_LOT_CODE);
|
DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_OUT_QUANTITY", ComDbType.INT32, entity.CN_F_OUT_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_IN_QUANTITY", ComDbType.INT32, entity.CN_F_IN_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_START_BIT", ComDbType.STRING, entity.CN_S_START_BIT);
|
DataAccess.AddInParameter(cmd, "CN_S_END_BIT", ComDbType.STRING, entity.CN_S_END_BIT);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
|
return ExecuteCommand(cmd, trans);
|
}
|
|
/// <summary>
|
/// 批量新增
|
/// </summary>
|
/// <param name="entity">实体类</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public SqlExecuteResult Addlist(List<TN_WM_B_MOVE_RESULTEntity> listentity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into tn_wm_b_move_result(");
|
strSql.Append("CN_S_OP_NO,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_SERIAL_NO,CN_S_MEASURE_UNIT,CN_S_MODEL,CN_S_ITEM_STATE,CN_S_FIGURE_NO,CN_S_LOT_CODE,CN_F_QUANTITY,CN_F_OUT_QUANTITY,CN_F_IN_QUANTITY,CN_S_TRAY_CODE,CN_S_START_BIT,CN_S_END_BIT,CN_S_NOTE,CN_S_AREA_CODE)");
|
strSql.Append(" values (");
|
strSql.Append(":CN_S_OP_NO,:CN_S_OWNER,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_SERIAL_NO,:CN_S_MEASURE_UNIT,:CN_S_MODEL,:CN_S_ITEM_STATE,:CN_S_FIGURE_NO,:CN_S_LOT_CODE,:CN_F_QUANTITY,:CN_F_OUT_QUANTITY,:CN_F_IN_QUANTITY,:CN_S_TRAY_CODE,:CN_S_START_BIT,:CN_S_END_BIT,:CN_S_NOTE,:CN_S_AREA_CODE)");
|
List<DbCommand> listcmd = new List<DbCommand>();
|
foreach (TN_WM_B_MOVE_RESULTEntity entity in listentity)
|
{
|
;
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, entity.CN_S_OP_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.STRING, entity.CN_S_SERIAL_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_CODE", ComDbType.STRING, entity.CN_S_LOT_CODE);
|
DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_OUT_QUANTITY", ComDbType.INT32, entity.CN_F_OUT_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_IN_QUANTITY", ComDbType.INT32, entity.CN_F_IN_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_START_BIT", ComDbType.STRING, entity.CN_S_START_BIT);
|
DataAccess.AddInParameter(cmd, "CN_S_END_BIT", ComDbType.STRING, entity.CN_S_END_BIT);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
DataAccess.AddInParameter(cmd, "CN_S_AREA_CODE", ComDbType.STRING, entity.CN_S_AREA_CODE);
|
listcmd.Add(cmd);
|
}
|
|
|
return ExecuteCommands(listcmd, trans);
|
}
|
#endregion
|
|
#region 更新
|
/// <summary>
|
/// 更新
|
/// </summary>
|
/// <param name="entity">实体类</param>
|
/// <param name="trans">事务</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public SqlExecuteResult Update(TN_WM_B_MOVE_RESULTEntity entity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("update tn_wm_b_move_result set ");
|
strSql.Append("CN_S_OWNER=:CN_S_OWNER,");
|
strSql.Append("CN_S_ITEM_CODE=:CN_S_ITEM_CODE,");
|
strSql.Append("CN_S_ITEM_NAME=:CN_S_ITEM_NAME,");
|
strSql.Append("CN_S_SERIAL_NO=:CN_S_SERIAL_NO,");
|
strSql.Append("CN_S_MEASURE_UNIT=:CN_S_MEASURE_UNIT,");
|
strSql.Append("CN_S_MODEL=:CN_S_MODEL,");
|
strSql.Append("CN_S_ITEM_STATE=:CN_S_ITEM_STATE,");
|
strSql.Append("CN_S_FIGURE_NO=:CN_S_FIGURE_NO,");
|
strSql.Append("CN_S_LOT_CODE=:CN_S_LOT_CODE,");
|
strSql.Append("CN_F_QUANTITY=:CN_F_QUANTITY,");
|
strSql.Append("CN_F_OUT_QUANTITY=:CN_F_OUT_QUANTITY,");
|
strSql.Append("CN_F_IN_QUANTITY=:CN_F_IN_QUANTITY,");
|
strSql.Append("CN_S_TRAY_CODE=:CN_S_TRAY_CODE,");
|
strSql.Append("CN_S_START_BIT=:CN_S_START_BIT,");
|
strSql.Append("CN_S_END_BIT=:CN_S_END_BIT,");
|
strSql.Append("CN_S_NOTE=:CN_S_NOTE");
|
strSql.Append(" where CN_S_OP_NO=:CN_S_OP_NO ");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
DataAccess.AddInParameter(cmd, "CN_S_OP_NO", ComDbType.STRING, entity.CN_S_OP_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
|
DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.STRING, entity.CN_S_SERIAL_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
|
DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
|
DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_LOT_CODE", ComDbType.STRING, entity.CN_S_LOT_CODE);
|
DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_OUT_QUANTITY", ComDbType.INT32, entity.CN_F_OUT_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_F_IN_QUANTITY", ComDbType.INT32, entity.CN_F_IN_QUANTITY);
|
DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_START_BIT", ComDbType.STRING, entity.CN_S_START_BIT);
|
DataAccess.AddInParameter(cmd, "CN_S_END_BIT", ComDbType.STRING, entity.CN_S_END_BIT);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 删除
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="sqlwhere">条件</param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/8/23</history>
|
public SqlExecuteResult Delete(string sqlwhere, DbTransaction trans)
|
{
|
string sql = "DELETE TN_WM_B_MOVE_RESULT{0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
|
#region 根据移库单号查询托盘与物料表
|
/// <summary>
|
/// 根据移库单号查询托盘与物料表
|
/// </summary>
|
/// <param name="sqlwhere"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/9/13</history>
|
public List<TN_WM_B_MOVE_RESULTEntity> GetTrayitemByOpno(string sqlwhere, string type)
|
{
|
string sql = "";
|
if (type == "货位移库")
|
{
|
sql = @"SELECT c.CN_S_OP_NO,c.CN_N_ROW_NO AS CN_S_DTL_ROW,d.CN_S_STOCK_CODE,c.CN_S_STOCK_AREA,c.CN_S_LOCATION_CODE,a.CN_S_TRAY_CODE,a.CN_S_TRAY_GRID,a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_ITEM_STATE,a.CN_S_OWNER,a.CN_S_LOT_NO,a.CN_S_MODEL,a.CN_S_FIGURE_NO,a.CN_F_PACKING_QTY AS CN_F_QUANTITY,0 AS CN_F_CHECK_QTY,c.CN_S_MEASURE_UNIT,D.CN_S_NOTE,'N' AS CN_S_ISCHECK,'' AS CN_S_ISWIN,d.CN_S_CREATOR AS CN_S_OPERATOR,GETDATE() AS CN_T_OPERATE,a.CN_S_PACKING_CODE AS CN_S_PACK_CODE FROM dbo.tn_wm_b_tray_item_rel AS a
|
INNER JOIN tn_wm_b_tray_location AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE
|
INNER JOIN TN_WM_MOVE_DTL AS c ON
|
a.CN_S_ITEM_CODE=c.CN_S_ITEM_CODE AND a.CN_S_ITEM_STATE=c.CN_S_ITEM_STATE AND a.CN_S_LOT_NO=c.CN_S_LOT_CODE AND a.CN_S_OWNER=c.CN_S_OWNER AND a.CN_S_MODEL = c.CN_S_MODEL AND a.CN_S_FIGURE_NO=c.CN_S_FIGURE_NO AND b.CN_S_LOCATION_CODE=c.CN_S_LOCATION_CODE INNER JOIN dbo.tn_wm_check_mst AS d ON c.CN_S_OP_NO=d.CN_S_OP_NO {0}";
|
}
|
else
|
{
|
sql = @"SELECT c.CN_S_OP_NO,b.CN_S_STOCK_CODE,d.CN_S_START_AREA,a.CN_S_OWNER,a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MEASURE_UNIT,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_FIGURE_NO,a.CN_S_LOT_NO AS CN_S_LOT_CODE,c.CN_F_QUANTITY AS CN_F_QUANTITY,a.CN_F_QUANTITY AS CN_F_OUT_QUANTITY,a.CN_F_QUANTITY AS CN_F_IN_QUANTITY,a.CN_S_TRAY_CODE,b.CN_S_LOCATION_CODE AS CN_S_START_BIT,c.CN_S_END_BIT,'' AS CN_S_NOTE,a.CN_S_PRODUCTION_BATCH
|
FROM --码盘汇总开始
|
(SELECT CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_FIGURE_NO,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_MEASURE_UNIT,b.CN_S_UNIQUE_CODE AS CN_S_PACK_CODE,b.CN_S_LOT_NO,SUM(b.CN_F_QUANTITY) AS CN_F_QUANTITY,b.CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_S_PRODUCTION_BATCH FROM TN_WM_B_TRAY_ITEM_MST AS a
|
INNER JOIN dbo.TN_WM_B_TRAY_ITEM_DTL AS b ON a.CN_GUID=b.CN_PARENT_GUID
|
GROUP BY CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_FIGURE_NO,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_MEASURE_UNIT,b.CN_S_UNIQUE_CODE,b.CN_S_LOT_NO,b.CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_S_PRODUCTION_BATCH) AS a---汇总结束
|
INNER JOIN tn_wm_b_tray_location AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE --关联货位
|
INNER JOIN dbo.TN_WM_MOVE_DTL AS c ON
|
--查询出对应码盘数据
|
a.CN_S_ITEM_CODE=c.CN_S_ITEM_CODE AND a.CN_S_ITEM_STATE=c.CN_S_ITEM_STATE AND ISNULL(a.CN_S_LOT_NO,'')=ISNULL(c.CN_S_LOT_CODE,'') AND ISNULL(a.CN_S_OWNER,'')=ISNULL(c.CN_S_OWNER,'') AND a.CN_S_MODEL = c.CN_S_MODEL AND a.CN_S_FIGURE_NO=c.CN_S_FIGURE_NO INNER JOIN dbo.TN_WM_MOVE_MST AS d ON c.CN_S_OP_NO=d.CN_S_OP_NO {0}";
|
}
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_B_MOVE_RESULTEntity>(this.DataAccess, cmd, SetEntity);
|
}
|
|
|
/// <summary>
|
/// 设置实体的各列
|
/// </summary>
|
private void SetEntity(TN_WM_B_MOVE_RESULTEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_S_OP_NO", "CN_S_OP_NO", reader);
|
SetEntityUti(entity, "CN_S_OWNER", "CN_S_OWNER", 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_SERIAL_NO", "CN_S_SERIAL_NO", reader);
|
SetEntityUti(entity, "CN_S_MEASURE_UNIT", "CN_S_MEASURE_UNIT", reader);
|
SetEntityUti(entity, "CN_S_MODEL", "CN_S_MODEL", reader);
|
SetEntityUti(entity, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", reader);
|
SetEntityUti(entity, "CN_S_FIGURE_NO", "CN_S_FIGURE_NO", reader);
|
SetEntityUti(entity, "CN_S_LOT_CODE", "CN_S_LOT_CODE", reader);
|
SetEntityUti(entity, "CN_F_QUANTITY", "CN_F_QUANTITY", reader);
|
SetEntityUti(entity, "CN_F_OUT_QUANTITY", "CN_F_OUT_QUANTITY", reader);
|
SetEntityUti(entity, "CN_F_IN_QUANTITY", "CN_F_IN_QUANTITY", reader);
|
SetEntityUti(entity, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", reader);
|
SetEntityUti(entity, "CN_S_START_BIT", "CN_S_START_BIT", reader);
|
SetEntityUti(entity, "CN_S_END_BIT", "CN_S_END_BIT", reader);
|
SetEntityUti(entity, "CN_S_NOTE", "CN_S_NOTE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
|
SetEntityUti(entity, "CN_S_START_AREA", "CN_S_START_AREA", reader);
|
SetEntityUti(entity, "CN_S_PRODUCTION_BATCH", "CN_S_PRODUCTION_BATCH", reader);
|
}
|
#endregion
|
}
|
}
|
