using HanHe.Utility.Data;
using HH.WMS.Entitys;
using HH.WMS.Entitys.Check;
using HH.WMS.Entitys.Common;
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.Check
{
    public class TN_WM_CHECK_MSTDAL : DapperBaseDAL
    {
        #region 获取分页列表
        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/7/3</history>
        public DataTable GetDataTable(dynamic obj)
        {
            var isFirstLevelOrg = obj.Value<bool>("isFirstLevelOrg");
            var currentOrgCode = obj.Value<string>("currentOrgCode");
            string sqlWhere = obj.sqlWhere;
            if (!isFirstLevelOrg)
            {
                //sqlWhere += " AND CN_S_NEXT_ORG_CODE='" + currentOrgCode + "' ";
                sqlWhere += " AND ((CN_S_CURR_ORG_CODE='" + currentOrgCode + "' or CN_S_NEXT_ORG_CODE='" + currentOrgCode + "' ) OR CN_S_NEXT_ORG_CODE='') ";
            }

            DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
            this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, "TN_WM_CHECK_MST");
            this.DataAccess.AddInParameter(cmd, "WhereStr", ComDbType.CHAR, 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/7/3</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_CHECK_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>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/7/3</history>
        public DataTable Getentity(string sqlwhere)
        {
            string sql = "SELECT * FROM TN_WM_CHECK_MST WHERE 1=1 {0}";
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
        }
        #endregion

        /// <summary>
        /// 获取货位物料信息
        /// </summary>
        /// <param name="sqlwhere">查询条件</param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/7/12</history>
        public List<TN_WM_CHECK_DTLEntity> Getlist(string sqlwhere, string type)
        {
            var sql = "";
            if (type == "物料盘点")
            {
                sql = @"select * from (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,'' AS CN_S_LOCATION_CODE,'' as CN_S_LOT_NO,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY, CN_S_MEASURE_UNIT FROM dbo.tn_wm_b_tray_location AS a LEFT JOIN TN_WM_B_TRAY_ITEM_MST AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE
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) b {0} ";
            }
            else
            {

                sql = @"select * from (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,'' as CN_S_LOT_NO,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,ISNULL(CN_F_QUANTITY,0) AS CN_F_QUANTITY, CN_S_MEASURE_UNIT FROM
 dbo.tn_wm_b_tray_location AS a
LEFT JOIN TN_WM_B_TRAY_ITEM_MST AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE 
) b {0} ";
            }
            sql = string.Format(sql, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return DataAccessExtensive.ExecuteListEntity<TN_WM_CHECK_DTLEntity>(this.DataAccess, cmd, SetEntity);

        }

        /// <summary>
        /// 设置实体的各列
        /// </summary>
        private void SetEntity(TN_WM_CHECK_DTLEntity entity, IDataReader reader)
        {
            SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
            SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", 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_ITEM_STATE", "CN_S_ITEM_STATE", reader);
            SetEntityUti(entity, "CN_S_LOT_NO", "CN_S_LOT_NO", reader);
            SetEntityUti(entity, "CN_S_OWNER", "CN_S_OWNER", reader);
            SetEntityUti(entity, "CN_S_MODEL", "CN_S_MODEL", reader);
            SetEntityUti(entity, "CN_S_FIGURE_NO", "CN_S_FIGURE_NO", reader);
            SetEntityUti(entity, "CN_F_QUANTITY", "CN_F_QUANTITY", reader);
            SetEntityUti(entity, "CN_S_MEASURE_UNIT", "CN_S_MEASURE_UNIT", reader);
            SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);

        }

        #region 根据盘点单号修改状态
        /// <summary>
        /// 根据盘点单号修改状态
        /// </summary>
        /// <param name="CN_S_OP_NO"></param>
        /// <param name="CN_S_STATE"></param>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/7/12</history>
        public SqlExecuteResult UpdateStateByNo(string CN_S_OP_NO, string CN_S_STATE, DbTransaction trans)
        {
            string sql = "UPDATE dbo.tn_wm_check_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);
        }
        #endregion

        
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <history>[HanHe(lt)] CREATED 2018/7/3</history>
        public OperateResult UpdateMstByOpNo(string state, string sqlwhere, IDbTransaction trans)
        {
            string sql = "update tn_wm_check_mst set CN_S_STATE='{0}' {1}";
            sql = string.Format(sql, state, sqlwhere);
            DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
            return ExecuteTranSql(sql, null, trans);
        }

        #region 空货位 更新货位拓展表
        /// <summary>
        /// 更新货位拓展表
        /// </summary>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/12/20</history>
        public OperateResult UpdateLocationExt(string locationCodes, IDbTransaction trans)
        {
            string sql = @" UPDATE tn_wm_b_location_ext SET CN_S_USE_STATE='空' FROM tn_wm_b_location_ext AS a WITH (NOLOCK)
LEFT JOIN dbo.tn_wm_b_tray_location AS b WITH (NOLOCK)  ON a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE
WHERE a.CN_S_LOCATION_CODE IN ('" + locationCodes + "') AND b.CN_S_LOCATION_CODE IS NULL";
            return ExecuteTranSql(sql, null, trans);
        }
        #endregion


        #region 空货位 更新货位拓展表
        /// <summary>
        /// 更新货位拓展表
        /// </summary>
        /// <returns></returns>
        /// <history>[HanHe(lt)] CREATED 2018/12/20</history>
        public OperateResult DeleteTrayLocation(string trayCodes, IDbTransaction trans)
        {
            string sql = @" DELETE tn_wm_b_tray_location FROM tn_wm_b_tray_location AS a WITH (NOLOCK)
LEFT JOIN TN_WM_B_TRAY_ITEM_MST AS b WITH (NOLOCK) ON b.CN_S_TRAY_CODE = a.CN_S_TRAY_CODE
INNER JOIN dbo.tn_wm_tray_info AS c WITH (NOLOCK) ON c.CN_S_TRAY_CODE = a.CN_S_TRAY_CODE
WHERE CN_S_TRAY_TYPE='虚拟托盘' AND  a.CN_S_TRAY_CODE IN ('" + trayCodes + "') AND b.CN_S_TRAY_CODE IS NULL";
            return ExecuteTranSql(sql, null, trans);
        }
        #endregion


        /// <summary>
        /// 根据盘点单号获取盘点明细
        /// </summary>
        /// <param name="opNo"></param>
        /// <returns></returns>
        /// <history>[HanHe(dbs)] CREATED 2019/3/28</history>
        public List<TN_WM_CHECK_LISTEntity> GetCheckList(string opNo,string type)
        {
            string sql = string.Empty;
            if (type.Equals("货位盘点"))
            {
                sql = @"select b.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,c.CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,
                                c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_S_ITEM_STATE,c.CN_S_MEASURE_UNIT,c.CN_S_OWNER,sum(d.CN_F_QUANTITY) CN_F_QUANTITY, 
                                d.CN_S_PACKING_UNIT,d.CN_F_PACKING_QTY
                                from tn_wm_check_dtl a 
                                join tn_wm_b_tray_location b on a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE 
                                left join  TN_WM_B_TRAY_ITEM_MST c on b.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE
                                join TN_WM_B_TRAY_ITEM_DTL d on  c.CN_GUID=d.CN_PARENT_GUID
                                where CN_S_OP_NO=@CN_S_OP_NO
                                group by b.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,c.CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,
                                c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_S_ITEM_STATE,c.CN_S_MEASURE_UNIT,c.CN_S_OWNER,
                                d.CN_S_PACKING_UNIT,d.CN_F_PACKING_QTY";
            }
            else if (type.Equals("物料盘点"))
            {
                sql = @"select 
                                d.CN_S_STOCK_AREA,d.CN_S_LOCATION_CODE,d.CN_S_TRAY_CODE,b.CN_S_TRAY_GRID,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,b.CN_S_MODEL,
                                b.CN_S_FIGURE_NO,b.CN_S_ITEM_STATE,b.CN_S_MEASURE_UNIT,b.CN_S_OWNER,sum(c.CN_F_QUANTITY) CN_F_QUANTITY,c.CN_S_PACKING_UNIT,c.CN_F_PACKING_QTY
                                from tn_wm_check_dtl a join TN_WM_B_TRAY_ITEM_MST b on a.CN_S_ITEM_CODE=b.CN_S_ITEM_CODE 
                                and a.CN_S_ITEM_STATE=b.CN_S_ITEM_STATE and a.CN_S_OWNER=b.CN_S_OWNER
                                join TN_WM_B_TRAY_ITEM_DTL c on b.CN_GUID=c.CN_PARENT_GUID
                                join tn_wm_b_tray_location d on b.CN_S_TRAY_CODE=d.CN_S_TRAY_CODE where CN_S_OP_NO=@CN_S_OP_NO
                                group by  d.CN_S_STOCK_AREA,d.CN_S_LOCATION_CODE,d.CN_S_TRAY_CODE,b.CN_S_TRAY_GRID,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,b.CN_S_MODEL,
                                b.CN_S_FIGURE_NO,b.CN_S_ITEM_STATE,b.CN_S_MEASURE_UNIT,b.CN_S_OWNER,c.CN_S_PACKING_UNIT,c.CN_F_PACKING_QTY";
            }
            return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, new { CN_S_OP_NO = opNo });
        }
    }
}