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
    }
}