using HanHe.Utility.Data;
|
using HH.WMS.Common;
|
using HH.WMS.DAL.Check;
|
using HH.WMS.Entitys;
|
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_MOVE_MSTDAL : DapperBaseDAL
|
{
|
#region 获取分页列表
|
/// <summary>
|
/// 获取分页列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/9/6</history>
|
public DataTable GetDataTable(dynamic obj)
|
{
|
DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
|
this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, "TN_WM_MOVE_MST");
|
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/9/6</history>
|
public DataTable GetDataTable(string sqlWhere, string OrdeBy, int pageSize, int pageIndex)
|
{
|
DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
|
this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, "TN_WM_MOVE_MST");
|
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>
|
/// <history>[HanHe(lt)] CREATED 2018/9/6</history>
|
public SqlExecuteResult Add(TN_WM_MOVE_MSTEntity entity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into TN_WM_MOVE_MST(");
|
strSql.Append("CN_S_OP_NO,CN_S_OP_MODE,CN_S_STOCK_CODE,CN_S_START_AREA,CN_S_END_AREA,CN_S_OP_TYPE,CN_T_OP_DATE,CN_S_STATE,CN_S_NOTE,CN_S_OPREATOR,CN_S_OP_FROM,CN_S_FROM_NO,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY)");
|
strSql.Append(" values (");
|
strSql.Append(":CN_S_OP_NO,:CN_S_OP_MODE,:CN_S_STOCK_CODE,:CN_S_START_AREA,:CN_S_END_AREA,:CN_S_OP_TYPE,:CN_T_OP_DATE,:CN_S_STATE,:CN_S_NOTE,:CN_S_OPREATOR,:CN_S_OP_FROM,:CN_S_FROM_NO,:CN_S_CREATOR,:CN_S_CREATOR_BY,:CN_T_CREATE,:CN_S_MODIFY,:CN_S_MODIFY_BY,:CN_T_MODIFY)");
|
|
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_OP_MODE", ComDbType.STRING, entity.CN_S_OP_MODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_START_AREA", ComDbType.STRING, entity.CN_S_START_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_END_AREA", ComDbType.STRING, entity.CN_S_END_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_OP_TYPE", ComDbType.STRING, entity.CN_S_OP_TYPE);
|
DataAccess.AddInParameter(cmd, "CN_T_OP_DATE", ComDbType.DATE, entity.CN_T_OP_DATE);
|
DataAccess.AddInParameter(cmd, "CN_S_OPREATOR", ComDbType.STRING, entity.CN_S_OPREATOR);
|
DataAccess.AddInParameter(cmd, "CN_S_OP_FROM", ComDbType.STRING, entity.CN_S_OP_FROM);
|
DataAccess.AddInParameter(cmd, "CN_S_FROM_NO", ComDbType.STRING, entity.CN_S_FROM_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_STATE", ComDbType.STRING, entity.CN_S_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
|
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 修改
|
/// <summary>
|
/// 更新一条数据
|
/// </summary>
|
/// <history>[HanHe(lt)] CREATED 2018/9/6</history>
|
public SqlExecuteResult Update(TN_WM_MOVE_MSTEntity entity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("update TN_WM_MOVE_MST set ");
|
strSql.Append("CN_S_OP_MODE=:CN_S_OP_MODE,");
|
strSql.Append("CN_S_STOCK_CODE=:CN_S_STOCK_CODE,");
|
strSql.Append("CN_S_START_AREA=:CN_S_START_AREA,");
|
strSql.Append("CN_S_END_AREA=:CN_S_END_AREA,");
|
strSql.Append("CN_S_OP_TYPE=:CN_S_OP_TYPE,");
|
strSql.Append("CN_T_OP_DATE=:CN_T_OP_DATE,");
|
strSql.Append("CN_S_STATE=:CN_S_STATE,");
|
strSql.Append("CN_S_NOTE=:CN_S_NOTE,");
|
strSql.Append("CN_S_OPREATOR=:CN_S_OPREATOR,");
|
strSql.Append("CN_S_OP_FROM=:CN_S_OP_FROM,");
|
strSql.Append("CN_S_FROM_NO=:CN_S_FROM_NO,");
|
strSql.Append("CN_S_CREATOR=:CN_S_CREATOR,");
|
strSql.Append("CN_S_CREATOR_BY=:CN_S_CREATOR_BY,");
|
strSql.Append("CN_T_CREATE=:CN_T_CREATE,");
|
strSql.Append("CN_S_MODIFY=:CN_S_MODIFY,");
|
strSql.Append("CN_S_MODIFY_BY=:CN_S_MODIFY_BY,");
|
strSql.Append("CN_T_MODIFY=:CN_T_MODIFY");
|
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_OP_MODE", ComDbType.STRING, entity.CN_S_OP_MODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_START_AREA", ComDbType.STRING, entity.CN_S_START_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_END_AREA", ComDbType.STRING, entity.CN_S_END_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_OP_TYPE", ComDbType.STRING, entity.CN_S_OP_TYPE);
|
DataAccess.AddInParameter(cmd, "CN_T_OP_DATE", ComDbType.DATE, entity.CN_T_OP_DATE);
|
DataAccess.AddInParameter(cmd, "CN_S_OPREATOR", ComDbType.STRING, entity.CN_S_OPREATOR);
|
DataAccess.AddInParameter(cmd, "CN_S_OP_FROM", ComDbType.STRING, entity.CN_S_OP_FROM);
|
DataAccess.AddInParameter(cmd, "CN_S_FROM_NO", ComDbType.STRING, entity.CN_S_FROM_NO);
|
DataAccess.AddInParameter(cmd, "CN_S_STATE", ComDbType.STRING, entity.CN_S_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR);
|
DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
|
|
return ExecuteCommand(cmd, trans);
|
|
}
|
|
/// <summary>
|
/// 更新一条数据
|
/// </summary>
|
/// <history>[HanHe(lt)] CREATED 2018/9/6</history>
|
public SqlExecuteResult UpdateDtl(TN_WM_MOVE_MSTEntity entity, DbTransaction trans)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("update TN_WM_MOVE_MST set ");
|
strSql.Append("CN_S_OP_MODE=:CN_S_OP_MODE,");
|
strSql.Append("CN_S_STOCK_CODE=:CN_S_STOCK_CODE,");
|
strSql.Append("CN_S_START_AREA=:CN_S_START_AREA,");
|
strSql.Append("CN_S_END_AREA=:CN_S_END_AREA,");
|
strSql.Append("CN_S_OP_TYPE=:CN_S_OP_TYPE,"); ;
|
strSql.Append("CN_S_STATE=:CN_S_STATE,");
|
strSql.Append("CN_S_NOTE=:CN_S_NOTE,");
|
strSql.Append("CN_S_MODIFY=:CN_S_MODIFY,");
|
strSql.Append("CN_S_MODIFY_BY=:CN_S_MODIFY_BY,");
|
strSql.Append("CN_T_MODIFY=:CN_T_MODIFY");
|
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_OP_MODE", ComDbType.STRING, entity.CN_S_OP_MODE);
|
DataAccess.AddInParameter(cmd, "CN_S_STOCK_CODE", ComDbType.STRING, entity.CN_S_STOCK_CODE);
|
DataAccess.AddInParameter(cmd, "CN_S_START_AREA", ComDbType.STRING, entity.CN_S_START_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_END_AREA", ComDbType.STRING, entity.CN_S_END_AREA);
|
DataAccess.AddInParameter(cmd, "CN_S_OP_TYPE", ComDbType.STRING, entity.CN_S_OP_TYPE);
|
DataAccess.AddInParameter(cmd, "CN_S_STATE", ComDbType.STRING, entity.CN_S_STATE);
|
DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
|
DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
|
DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
|
|
return ExecuteCommand(cmd, trans);
|
}
|
|
#endregion
|
|
#region 删除
|
/// <summary>
|
/// 删除一条数据
|
/// </summary>
|
/// <history>[HanHe(lt)] CREATED 2018/9/6</history>
|
public SqlExecuteResult Delete(string sqlwhere, DbTransaction trans)
|
{
|
|
string sql = "DELETE FROM TN_WM_MOVE_MST {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/6</history>
|
public DataTable Getentity(string sqlwhere)
|
{
|
string sql = "SELECT * FROM TN_WM_MOVE_MST WHERE 1=1 {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
#endregion
|
|
#region 获取货位物料信息
|
/// <summary>
|
/// 获取货位物料信息
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/12</history>
|
public DataTable GetTrayLocation(dynamic obj)
|
{
|
var type = obj.type;
|
var sql = "";
|
|
|
////获取策略 判断是否要审核
|
//string sysConfig = SysConfig.GetValue(Constants.CL_IsAllowMixing);
|
|
|
if (type == "货位盘点")
|
{
|
sql = @"(SELECT a.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,CN_S_ITEM_STATE,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,ISNULL(CAST(SUM(c.CN_F_QUANTITY) AS INT),0) AS CN_F_QUANTITY, CN_S_MEASURE_UNIT,c.CN_S_LOT_NO AS CN_S_LOT_CODE FROM dbo.tn_wm_b_tray_location AS a INNER JOIN TN_WM_B_TRAY_ITEM_MST AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE
|
INNER JOIN dbo.TN_WM_B_TRAY_ITEM_DTL AS c ON b.CN_GUID=c.CN_PARENT_GUID GROUP BY a.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,CN_S_ITEM_STATE,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,c.CN_S_LOT_NO) b";
|
}
|
else
|
{
|
sql = @"( SELECT a.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,CN_S_ITEM_STATE,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,ISNULL(SUM(c.CN_F_QUANTITY),0)-MAX(b.CN_F_ALLOC_QTY) AS CN_F_QUANTITY,CN_S_MEASURE_UNIT,'' AS CN_S_END_BIT,'' AS CN_S_LOT_CODE,c.CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_b_tray_location AS a INNER JOIN TN_WM_B_TRAY_ITEM_MST AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE
|
INNER JOIN dbo.TN_WM_B_TRAY_ITEM_DTL AS c ON b.CN_GUID=c.CN_PARENT_GUID
|
GROUP BY a.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,CN_S_ITEM_STATE,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,CN_S_MEASURE_UNIT,c.CN_S_PRODUCTION_BATCH) b";
|
}
|
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;
|
}
|
}
|
#endregion
|
|
#region 根据移库单号修改状态
|
/// <summary>
|
/// 根据盘点单号修改状态
|
/// </summary>
|
/// <param name="CN_S_OP_NO"></param>
|
/// <param name="CN_S_STATE"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/9/13</history>
|
public SqlExecuteResult UpdateStateByNo(string CN_S_OP_NO, string CN_S_STATE, DbTransaction trans)
|
{
|
string sql = "UPDATE dbo.tn_wm_move_mst SET CN_S_STATE='{0}' WHERE CN_S_OP_NO IN ('{1}')";
|
sql = string.Format(sql, CN_S_STATE, CN_S_OP_NO);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
|
public OperateResult UpdateMoveMstStateByNo(string CN_S_OP_NO, string CN_S_STATE, IDbTransaction trans)
|
{
|
string sql = "UPDATE dbo.tn_wm_move_mst SET CN_S_STATE='{0}' WHERE CN_S_OP_NO IN ('{1}')";
|
sql = string.Format(sql, CN_S_STATE, CN_S_OP_NO);
|
return ExecuteTranSql(sql,null, trans);
|
}
|
#endregion
|
|
#region 获取移库主表与明细表关联数据
|
/// <summary>
|
/// 获取移库主表与明细表关联数据
|
/// </summary>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/12/20</history>
|
public DataTable GetMstResult(string sqlwhere)
|
{
|
string sql = @"SELECT * FROM dbo.TN_WM_MOVE_MST AS a
|
INNER JOIN dbo.TN_WM_MOVE_DTL AS b ON b.CN_S_OP_NO = a.CN_S_OP_NO
|
INNER JOIN dbo.tn_wm_b_move_result AS c ON c.CN_S_OP_NO = a.CN_S_OP_NO AND c.CN_S_OWNER = b.CN_S_OWNER AND c.CN_S_ITEM_CODE = b.CN_S_ITEM_CODE AND ISNULL(c.CN_S_SERIAL_NO,'') = ISNULL(b.CN_S_SERIAL_NO,'') AND c.CN_S_MEASURE_UNIT = b.CN_S_MEASURE_UNIT AND c.CN_S_MODEL = b.CN_S_MODEL AND c.CN_S_ITEM_STATE = b.CN_S_ITEM_STATE AND c.CN_S_FIGURE_NO = b.CN_S_FIGURE_NO {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region 综合查询
|
/// <summary>
|
/// 综合查询
|
/// </summary>
|
/// <param name="tableName">主表字段</param>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="total"></param>
|
/// <param name="orderBy"></param>
|
/// <param name="param"></param>
|
/// <returns></returns>
|
public DataTable GetDataList(string tableName, int pageIndex, int pageSize, out long total, object where = null, string orderBy = "")
|
{
|
tableName = "(" + tableName + ") t";
|
return ExecutePagingData(tableName, pageIndex, pageSize, out total, orderBy);
|
}
|
#endregion
|
|
#region 获取移库主表与子表关联数据
|
/// <summary>
|
/// 获取移库主表与明细表关联数据
|
/// </summary>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/12/20</history>
|
public DataTable GetMstJoinDtl(string sqlwhere)
|
{
|
string sql = @" SELECT c.CN_F_QUANTITY - c.CN_F_ALLOC_QTY AS CN_F_QUANTITY_STOCK ,* FROM dbo.TN_WM_MOVE_MST AS a INNER JOIN dbo.tn_wm_move_dtl AS b ON a.CN_S_OP_NO=b.CN_S_OP_NO
|
LEFT JOIN (SELECT SUM(CN_F_QUANTITY) AS CN_F_QUANTITY ,SUM(CN_F_ALLOC_QTY) AS CN_F_ALLOC_QTY,CN_S_OWNER,CN_S_STOCK_CODE,CN_S_ITEM_CODE,CN_S_ITEM_STATE,ISNULL(CN_S_PRODUCTION_BATCH,'') AS CN_S_PRODUCTION_BATCH FROM dbo.tn_wm_b_stock_qty GROUP BY CN_S_OWNER,CN_S_STOCK_CODE,CN_S_ITEM_CODE,CN_S_ITEM_STATE,CN_S_PRODUCTION_BATCH)
|
AS c ON c.CN_S_OWNER = b.CN_S_OWNER AND a.CN_S_STOCK_CODE=a.CN_S_STOCK_CODE AND c.CN_S_ITEM_CODE = b.CN_S_ITEM_CODE AND c.CN_S_ITEM_STATE = b.CN_S_ITEM_STATE {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region 获取分拣单
|
/// <summary>
|
/// 获取分拣单
|
/// </summary>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/12/25</history>
|
public List<TN_WM_SORTING_RESULTEntity> GetSortingResult(string sqlwhere)
|
{
|
// AND a.CN_S_LOT_NO=c.CN_S_LOT_CODE
|
string sql = @" SELECT c.CN_S_OP_NO,A.*,c.CN_S_END_BIT,c.CN_S_AREA_CODE,d.CN_S_STOCK_CODE,d.CN_S_END_AREA FROM tn_wm_sorting_result AS a
|
LEFT JOIN tn_wm_sorting_list AS b ON a.CN_S_SORTING_NO=b.CN_S_SORTING_NO
|
LEFT JOIN dbo.tn_wm_b_move_result AS c ON b.CN_S_FROM_NO=c.CN_S_OP_NO AND c.CN_S_ITEM_CODE = a.CN_S_ITEM_CODE AND ISNULL(c. CN_S_SERIAL_NO,'') =ISNULL(a.CN_S_SERIAL_NO,'') AND c.CN_S_MEASURE_UNIT = a.CN_S_MEASURE_UNIT AND c.CN_S_MODEL = a.CN_S_MODEL AND c.CN_S_ITEM_STATE = a.CN_S_ITEM_STATE AND c.CN_S_TRAY_CODE = a.CN_S_TRAY_CODE
|
LEFT 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);
|
return ExecuteQuery<TN_WM_SORTING_RESULTEntity>(sql);
|
}
|
#endregion
|
}
|
}
|
