using HH.WMS.Entitys.Entitys;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace HH.WMS.DAL.SysMgr
{
    public class TN_WM_B_TRAY_CAPACITYDAL : DapperBaseDAL
    {
        #region 返回托盘物料容量关联实体
        /// <summary>
        /// 根据条件获取容量实体
        /// </summary>
        /// <param name="itemCode"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public TN_WM_B_TRAY_CAPACITYEntity GetEntityByItemCode(string itemCode)
        {
            TN_WM_B_TRAY_CAPACITYEntity areaQtyEntity = new TN_WM_B_TRAY_CAPACITYEntity();

            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT (CN_S_STORE_UNIT*CN_F_QUANTITY) CN_F_QUANTITY FROM TN_WM_B_TRAY_CAPACITY WHERE CN_S_ITEM_CODE='" + itemCode + "' ");
           
            return ExecuteSingleEntity<TN_WM_B_TRAY_CAPACITYEntity>(sbStr.ToString());
        }

        #endregion

        #region 根据物料编码集合获得对应的存储序列
        /// <summary>
        /// 根据物料编码集合获得对应的存储序列
        /// </summary>
        /// <param name="lstItem"></param>
        /// <returns></returns>
        public List<TN_WM_B_TRAY_CAPACITYEntity> GetListByItemCode(string itemCode)
        {
            string strSql = "SELECT * FROM TN_WM_B_TRAY_CAPACITY WHERE CN_S_ITEM_CODE IN('" + itemCode + "')";
            List<TN_WM_B_TRAY_CAPACITYEntity> list = ExecuteQuery<TN_WM_B_TRAY_CAPACITYEntity>(strSql.ToString());
            return list;
        }
        #endregion

        /// <summary>
        ///  PDA接口-根据托盘码获取存放容量
        /// </summary>
        /// <param name="trayCode">托盘码</param>
        /// <returns></returns>
        public DataTable GetCapacityinfoByTrayCode(string trayCode)
        {
            string sql = @"SELECT distinct A.CN_S_TRAY_CODE,A.CN_F_WEIGHT,A.CN_S_TRAY_TYPE,CN_S_STATE,A.CN_S_SPEC,CN_N_LATTICE_NUM,CN_S_USE_STATE,
                                CN_N_ROW_NUM,CN_N_COLUMN_NUM,CASE WHEN B.CN_S_TRAY_GRID IS NULL THEN '0' else B.CN_S_TRAY_GRID end AS CN_S_TRAY_GRID_ITEM, B.CN_S_LOT_NO,
                                B.CN_S_ITEM_CODE,B.CN_S_ITEM_NAME,ISNULL(CAST(B.CN_F_PACKING_QTY AS int), 0) AS CN_F_PACKING_QTY,
                                 C.CN_S_SPEC AS CN_S_SPEC_ITEM,C.CN_S_STORE_UNIT,CAST(C.CN_N_QUANTITY AS int) AS CN_N_QUANTITY,
                                 ISNULL(A.CN_F_LOADBEARING, 0) CN_F_LOADBEARING,B.CN_S_OWNER,B.CN_S_ITEM_STATE,B.CN_T_AUDIT_TIME,B.CN_S_PACKING_CODE FROM TN_WM_TRAY_INFO AS A
                                  LEFT JOIN
                                  (SELECT CN_S_OWNER, CN_S_ITEM_STATE, CN_S_TRAY_CODE, CN_S_TRAY_GRID, CN_S_LOT_NO, CN_S_ITEM_CODE, CN_S_ITEM_NAME,
                                  CN_F_PACKING_QTY AS CN_F_PACKING_QTY, x.CN_S_PACKING_CODE, y.CN_T_AUDIT_TIME  FROM TN_WM_B_TRAY_ITEM_REL x left join
  
                                  TN_WM_INCREASE_INVENTORY_MST y on x.CN_S_FROM_NO = y.CN_S_OP_NO
                                  ) AS B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE LEFT JOIN
                                DBO.TN_WM_B_TRAY_CAPACITY AS C ON B.CN_S_ITEM_CODE = C.CN_S_ITEM_CODE AND C.CN_S_SPEC = A.CN_S_SPEC
                                 where A.CN_S_TRAY_CODE ={0}";
            sql = string.Format(sql, trayCode);
            return ExecuteDataTable(sql);
        }
    }
}