using HanHe.Utility.Data;
using HH.WMS.Common;
using HH.WMS.Entitys;
using HH.WMS.Entitys.Basic;
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.Basic
{
    public class TN_WM_B_TRAY_INFODAL : DapperBaseDAL
    {
        #region GET

        public OperateResult GetForPrint(string trayCode, string trayType, string spec, int pageIndex, int pageSize)
        {
            string sqlWhere = " where 1=1 ";
            if (!string.IsNullOrEmpty(trayCode))
            {
                sqlWhere += " and CN_S_TRAY_CODE like '%" + trayCode + "%' ";
            }
            if (!string.IsNullOrEmpty(trayType))
            {
                sqlWhere += " and CN_S_TRAY_TYPE like '%" + trayType + "%'";
            }
            if (!string.IsNullOrEmpty(spec))
            {
                sqlWhere += " and CN_S_SPEC like '%" + spec + "%'";
            }
            sqlWhere += " and CN_S_TRAY_CODE not like 'VWT%' ";
            string sql = @"(select * from TN_WM_TRAY_INFO " + sqlWhere + ") AS AA ";
            return ExecutePagingResult(sql, pageIndex, pageSize);
        }

        #endregion

        #region 更新托盘实际重量
        /// <summary>
        /// 更新托盘实际重量
        /// </summary>
        /// <param name="trayCode">托盘 编码</param>
        /// <param name="weight">本次入托实际重量</param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult updateTrayCodeWeight(string trayCode, decimal weight, IDbTransaction trans)
        {
            string sq1 = "UPDATE TN_WM_TRAY_INFO SET CN_S_STATE=@CN_S_STATE,CN_F_WEIGHT=CN_F_WEIGHT+@CN_F_WEIGHT,CN_S_USE_STATE=case when CN_F_WEIGHT+@CN_F_WEIGHT>=CN_F_LOADBEARING then '满' else '不满' end  WHERE CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
            return ExecuteTranSql(sq1, new
            {
                CN_S_STATE = Constants.TrayState_InUse,
                CN_F_WEIGHT = weight,
                CN_S_TRAY_CODE = trayCode
            }, trans);
        }
        #endregion

        #region 查询托盘是否已使用
        /// <summary>
        /// 查询托盘是否已使用
        /// </summary>
        /// <param name="trayCode">托盘号</param>
        /// <returns></returns>
        /// <History>[HANHE(DBS)] CREATED BY 2018-12-5</History>
        public bool GetUseState(string trayCode)
        {
            string sql = "select count(1) from tn_wm_b_tray_location where CN_S_TRAY_CODE=@CN_S_TRAY_CODE";

            DataTable dt = ExecuteDataTable(sql, new
            {
                CN_S_TRAY_CODE = trayCode
            });
            if (Convert.ToInt16(dt.Rows[0][0]) == 0)
            {
                sql = "select COUNT(1) from TN_WM_B_TRAY_ITEM_MST where CN_S_TRAY_CODE=@CN_S_TRAY_CODE"; //tn_wm_b_tray_item_rel
                dt = ExecuteDataTable(sql, new
                {
                    CN_S_TRAY_CODE = trayCode
                });
            }
            return Convert.ToInt16(dt.Rows[0][0]) > 0;
        }
        #endregion

        #region  新增容器表
        /// <summary>
        /// 新增容器表
        /// </summary>
        /// <returns></returns>
        /// <History>[HANHE(LT)] CREATED BY 2018-12-27</History>
        public OperateResult Add(List<TN_WM_B_TRAY_INFOEntity> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (TN_WM_B_TRAY_INFOEntity model in list)
            {
                StringBuilder strSql = new StringBuilder();
                //strSql.Append(" if not exists(select * from tn_wm_tray_info  " + model.sqlWhere + " )");

                strSql.Append(" IF NOT EXISTS(SELECT * FROM dbo.TN_WM_B_TRAY_ITEM_MST  " + model.sqlWhere + ") ");
                strSql.Append("insert into tn_wm_tray_info(");
                strSql.Append("CN_S_TRAY_CODE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_S_TRAY_CODE_EXT,CN_S_TRAY_TYPE,CN_T_MODIFY,CN_S_CREATOR,CN_S_TRAY_DIMENSION,CN_S_CREATOR_BY,CN_S_TRAY_COLOR,CN_S_DESC,CN_T_CREATE,CN_S_STATE,CN_S_SPEC,CN_S_SORTING_CODE,CN_S_COLLECT_CODE,CN_S_COLLECT_LOCATION,CN_N_LATTICE_NUM,CN_S_USE_STATE,CN_N_ROW_NUM,CN_N_COLUMN_NUM,CN_F_LOADBEARING,CN_F_GROSSWEIGHT,CN_F_WEIGHT)");
                strSql.Append(" values (");
                strSql.Append("@CN_S_TRAY_CODE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_S_TRAY_CODE_EXT,@CN_S_TRAY_TYPE,@CN_T_MODIFY,@CN_S_CREATOR,@CN_S_TRAY_DIMENSION,@CN_S_CREATOR_BY,@CN_S_TRAY_COLOR,@CN_S_DESC,@CN_T_CREATE,@CN_S_STATE,@CN_S_SPEC,@CN_S_SORTING_CODE,@CN_S_COLLECT_CODE,@CN_S_COLLECT_LOCATION,@CN_N_LATTICE_NUM,@CN_S_USE_STATE,@CN_N_ROW_NUM,@CN_N_COLUMN_NUM,@CN_F_LOADBEARING,@CN_F_GROSSWEIGHT,@CN_F_WEIGHT)");

                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_S_TRAY_CODE = model.CN_S_TRAY_CODE,
                    CN_S_MODIFY = model.CN_S_MODIFY,
                    CN_S_MODIFY_BY = model.CN_S_MODIFY_BY,
                    CN_S_TRAY_CODE_EXT = model.CN_S_TRAY_CODE_EXT,
                    CN_S_TRAY_TYPE = model.CN_S_TRAY_TYPE,
                    CN_T_MODIFY = model.CN_T_MODIFY,
                    CN_S_CREATOR = model.CN_S_CREATOR,
                    CN_S_TRAY_DIMENSION = model.CN_S_TRAY_DIMENSION,
                    CN_S_CREATOR_BY = model.CN_S_CREATOR_BY,
                    CN_S_TRAY_COLOR = model.CN_S_TRAY_COLOR,
                    CN_S_DESC = model.CN_S_DESC,
                    CN_T_CREATE = model.CN_T_CREATE,
                    CN_S_STATE = model.CN_S_STATE,
                    CN_S_SPEC = model.CN_S_SPEC,
                    CN_S_SORTING_CODE = model.CN_S_SORTING_CODE,
                    CN_S_COLLECT_CODE = model.CN_S_COLLECT_CODE,
                    CN_S_COLLECT_LOCATION = model.CN_S_COLLECT_LOCATION,
                    CN_N_LATTICE_NUM = model.CN_N_LATTICE_NUM,
                    CN_S_USE_STATE = model.CN_S_USE_STATE,
                    CN_N_ROW_NUM = model.CN_N_ROW_NUM,
                    CN_N_COLUMN_NUM = model.CN_N_COLUMN_NUM,
                    CN_F_LOADBEARING = model.CN_F_LOADBEARING,
                    CN_F_GROSSWEIGHT = model.CN_F_GROSSWEIGHT,
                    CN_F_WEIGHT = model.CN_F_WEIGHT,
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;

        }
        #endregion

        #region  新增容器表 单个
        /// <summary>
        /// 新增容器表
        /// </summary>
        /// <returns></returns>
        /// <History>[HANHE(LT)] CREATED BY 2018-12-27</History>
        public OperateResult Add(TN_WM_B_TRAY_INFOEntity model, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();

            StringBuilder strSql = new StringBuilder();
            //strSql.Append(" if not exists(select * from tn_wm_tray_info  " + model.sqlWhere + " )");
            strSql.Append("insert into tn_wm_tray_info(");
            strSql.Append("CN_S_TRAY_CODE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_S_TRAY_CODE_EXT,CN_S_TRAY_TYPE,CN_T_MODIFY,CN_S_CREATOR,CN_S_TRAY_DIMENSION,CN_S_CREATOR_BY,CN_S_TRAY_COLOR,CN_S_DESC,CN_T_CREATE,CN_S_STATE,CN_S_SPEC,CN_S_SORTING_CODE,CN_S_COLLECT_CODE,CN_S_COLLECT_LOCATION,CN_N_LATTICE_NUM,CN_S_USE_STATE,CN_N_ROW_NUM,CN_N_COLUMN_NUM,CN_F_LOADBEARING,CN_F_GROSSWEIGHT,CN_F_WEIGHT)");
            strSql.Append(" values (");
            strSql.Append("@CN_S_TRAY_CODE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_S_TRAY_CODE_EXT,@CN_S_TRAY_TYPE,@CN_T_MODIFY,@CN_S_CREATOR,@CN_S_TRAY_DIMENSION,@CN_S_CREATOR_BY,@CN_S_TRAY_COLOR,@CN_S_DESC,@CN_T_CREATE,@CN_S_STATE,@CN_S_SPEC,@CN_S_SORTING_CODE,@CN_S_COLLECT_CODE,@CN_S_COLLECT_LOCATION,@CN_N_LATTICE_NUM,@CN_S_USE_STATE,@CN_N_ROW_NUM,@CN_N_COLUMN_NUM,@CN_F_LOADBEARING,@CN_F_GROSSWEIGHT,@CN_F_WEIGHT)");

            result = ExecuteTranSql(strSql.ToString(), new
            {
                CN_S_TRAY_CODE = model.CN_S_TRAY_CODE,
                CN_S_MODIFY = model.CN_S_MODIFY,
                CN_S_MODIFY_BY = model.CN_S_MODIFY_BY,
                CN_S_TRAY_CODE_EXT = model.CN_S_TRAY_CODE_EXT,
                CN_S_TRAY_TYPE = model.CN_S_TRAY_TYPE,
                CN_T_MODIFY = model.CN_T_MODIFY,
                CN_S_CREATOR = model.CN_S_CREATOR,
                CN_S_TRAY_DIMENSION = model.CN_S_TRAY_DIMENSION,
                CN_S_CREATOR_BY = model.CN_S_CREATOR_BY,
                CN_S_TRAY_COLOR = model.CN_S_TRAY_COLOR,
                CN_S_DESC = model.CN_S_DESC,
                CN_T_CREATE = model.CN_T_CREATE,
                CN_S_STATE = model.CN_S_STATE,
                CN_S_SPEC = model.CN_S_SPEC,
                CN_S_SORTING_CODE = model.CN_S_SORTING_CODE,
                CN_S_COLLECT_CODE = model.CN_S_COLLECT_CODE,
                CN_S_COLLECT_LOCATION = model.CN_S_COLLECT_LOCATION,
                CN_N_LATTICE_NUM = model.CN_N_LATTICE_NUM,
                CN_S_USE_STATE = model.CN_S_USE_STATE,
                CN_N_ROW_NUM = model.CN_N_ROW_NUM,
                CN_N_COLUMN_NUM = model.CN_N_COLUMN_NUM,
                CN_F_LOADBEARING = model.CN_F_LOADBEARING,
                CN_F_GROSSWEIGHT = model.CN_F_GROSSWEIGHT,
                CN_F_WEIGHT = model.CN_F_WEIGHT,
            }, trans);
            if (result.Status == ResultStatus.Error)
                return result;

            return result;

        }
        #endregion

        #region 更新托盘实际重量及容器规格
        /// <summary>
        /// 更新托盘实际重量
        /// </summary>
        /// <param name="trayCode">托盘 编码</param>
        /// <param name="weight">本次入托实际重量</param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult updateTrayInfo(string trayCode, decimal? weight, string useState, string traySpec, IDbTransaction trans)
        {
            string sq1 = "UPDATE TN_WM_TRAY_INFO SET CN_S_SPEC=@CN_S_SPEC,CN_F_GROSSWEIGHT=@CN_F_GROSSWEIGHT,CN_S_USE_STATE=@CN_S_USE_STATE WHERE CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
            return ExecuteTranSql(sq1, new
            {
                CN_F_GROSSWEIGHT = weight,
                CN_S_SPEC = traySpec,
                CN_S_USE_STATE = useState,
                CN_S_TRAY_CODE = trayCode
            }, trans);
        }
        #endregion
        #region 根据托盘号获取托盘信息
        public TN_WM_B_TRAY_INFOEntity GetModel(string trayCode)
        {
            string sql = @"SELECT * FROM tn_wm_tray_info WHERE CN_S_TRAY_CODE='" + trayCode + "'";

            return ExecuteSingleEntity<TN_WM_B_TRAY_INFOEntity>(sql, null);
        }
        /// <summary>
        /// 设置实体的各列
        /// <summary>
        private void SetEntity(TN_WM_B_TRAY_INFOEntity entity, IDataReader reader)
        {
            SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
            SetEntityUti(entity, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", reader);
            SetEntityUti(entity, "CN_S_TRAY_TYPE", "CN_S_TRAY_TYPE", reader);
            SetEntityUti(entity, "CN_S_USE_STATE", "CN_S_USE_STATE", reader);
            SetEntityUti(entity, "CN_S_SPEC", "CN_S_SPEC", reader);
        }
        #endregion
    }
}