using HanHe.Utility.Data;
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_DTLDAL : DapperBaseDAL
    {
        #region 获取分页列表
        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/9/10</history>
        public DataTable GetDataTable(dynamic obj)
        {
            DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
            this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, "TN_WM_MOVE_DTL");
            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/10/9</history>
        public DataTable getInfo(string sqlWhere)
        {
            string sql = "SELECT * FROM TN_WM_MOVE_DTL where {0}";
            sql = string.Format(sql, sqlWhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }
        #endregion

        #region 新增
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <history>[HanHe(lt)] CREATED 2018/9/10</history>
        public SqlExecuteResult Add(TN_WM_MOVE_DTLEntity entity, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TN_WM_MOVE_DTL(");
            strSql.Append("CN_S_OP_NO,CN_N_ROW_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_S_TRAY_CODE,CN_S_START_BIT,CN_S_END_BIT,CN_S_NOTE)");
            strSql.Append(" values (");
            strSql.Append(":CN_S_OP_NO,:CN_N_ROW_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_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_N_ROW_NO", ComDbType.INT32, entity.CN_N_ROW_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_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
            DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.INT32, entity.CN_F_QUANTITY);
            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>
        /// <history>[HanHe(lt)] CREATED 2018/7/3</history>
        public SqlExecuteResult Addlist(List<TN_WM_MOVE_DTLEntity> listentity, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TN_WM_MOVE_DTL(");
            strSql.Append("CN_S_OP_NO,CN_N_ROW_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_S_TRAY_CODE,CN_S_START_BIT,CN_S_END_BIT,CN_S_NOTE)");
            strSql.Append(" values (");
            strSql.Append(":CN_S_OP_NO,:CN_N_ROW_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_S_TRAY_CODE,:CN_S_START_BIT,:CN_S_END_BIT,:CN_S_NOTE)");
            List<DbCommand> listcmd = new List<DbCommand>();

            foreach (TN_WM_MOVE_DTLEntity 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_N_ROW_NO", ComDbType.INT32, entity.CN_N_ROW_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_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
                DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.INT32, entity.CN_F_QUANTITY);
                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);
                listcmd.Add(cmd);
            }


            return ExecuteCommands(listcmd, trans);
        }


        /// <summary>
        /// 增加多条数据
        /// </summary>
        /// <history>[HanHe(lt)] CREATED 2018/7/3</history>
        public OperateResult Addlist(List<TN_WM_MOVE_DTLEntity> listentity, string CN_S_OP_NO, IDbTransaction trans)
        {
            var i = 1;
            OperateResult result = new OperateResult();
            foreach (TN_WM_MOVE_DTLEntity entity in listentity)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into TN_WM_MOVE_DTL(");
                strSql.Append("CN_S_OP_NO,CN_N_ROW_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_S_TRAY_CODE,CN_S_START_BIT,CN_S_END_BIT,CN_S_NOTE,CN_S_PRODUCTION_BATCH)");
                strSql.Append(" values (");
                strSql.Append("@CN_S_OP_NO,@CN_N_ROW_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_S_TRAY_CODE,@CN_S_START_BIT,@CN_S_END_BIT,@CN_S_NOTE,@CN_S_PRODUCTION_BATCH)");

                result = ExecuteTranSql(strSql.ToString(), new {
                     CN_S_OP_NO = CN_S_OP_NO,
                     CN_N_ROW_NO=i++,
                     CN_S_OWNER = entity.CN_S_OWNER,
                     CN_S_ITEM_CODE = entity.CN_S_ITEM_CODE,
                     CN_S_ITEM_NAME = entity.CN_S_ITEM_NAME,
                     CN_S_SERIAL_NO = entity.CN_S_SERIAL_NO,
                     CN_S_MEASURE_UNIT = entity.CN_S_MEASURE_UNIT,
                     CN_S_MODEL = entity.CN_S_MODEL,
                     CN_S_ITEM_STATE = entity.CN_S_ITEM_STATE,
                     CN_S_FIGURE_NO = entity.CN_S_FIGURE_NO,
                     CN_S_LOT_CODE = entity.CN_S_LOT_CODE,
                     CN_F_QUANTITY = entity.CN_F_QUANTITY,
                     CN_S_TRAY_CODE = entity.CN_S_TRAY_CODE,
                     CN_S_START_BIT = entity.CN_S_START_BIT,
                     CN_S_END_BIT = entity.CN_S_END_BIT,
                     CN_S_NOTE = entity.CN_S_NOTE,
                     CN_S_PRODUCTION_BATCH = entity.CN_S_PRODUCTION_BATCH,
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
        }

        #endregion

        #region 修改
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <history>[HanHe(lt)] CREATED 2018/9/10</history>
        public SqlExecuteResult Update(TN_WM_MOVE_DTLEntity entity, DbTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TN_WM_MOVE_DTL 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_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 and CN_N_ROW_NO=:CN_N_ROW_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_N_ROW_NO", ComDbType.INT32, entity.CN_N_ROW_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_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
            DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.INT32, entity.CN_F_QUANTITY);
            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>
        /// <history>[HanHe(lt)] CREATED 2018/9/10</history>
        public SqlExecuteResult Delete(string sqlwhere, DbTransaction trans)
        {

            string sql = "DELETE FROM TN_WM_MOVE_DTL {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return ExecuteCommand(cmd, trans);
        }
        #endregion


        #region 修改查询移库明细
        /// <summary>
        /// 修改查询移库明细
        /// </summary>
        /// <history>[HanHe(lt)] CREATED 2018/9/19</history>
        public DataTable GetDtl(string sqlwhere)
        {
            string sql = @"SELECT ''  AS ROWID,CN_S_STOCK_CODE,a.CN_S_START_AREA AS CN_S_STOCK_AREA,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,b.CN_S_ITEM_STATE,CN_S_LOT_CODE,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,CAST(b.CN_F_QUANTITY AS INT)  AS  CN_F_QUANTITY,CAST(c.CN_F_QUANTITY AS INT) AS CN_F_QUANTITY_IN,b.CN_S_MEASURE_UNIT,CN_S_END_BIT 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 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 c
 on b.CN_S_ITEM_CODE=c.CN_S_ITEM_CODE AND b.CN_S_ITEM_STATE=c.CN_S_ITEM_STATE AND ISNULL(b.CN_S_LOT_CODE,'')=ISNULL(c.CN_S_LOT_NO,'') AND ISNULL(b.CN_S_OWNER,'')=ISNULL(c.CN_S_OWNER,'') AND b.CN_S_MODEL = c.CN_S_MODEL AND b.CN_S_FIGURE_NO=c.CN_S_FIGURE_NO where {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }
        #endregion


        #region 修改查询移库明细
        /// <summary>
        /// 修改查询移库明细
        /// </summary>
        /// <history>[HanHe(lt)] CREATED 2018/9/19</history>
        public DataTable GetMstJoinDtl(string sqlwhere)
        {

            string sql = "SELECT * 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 where {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }
        #endregion
    }
}