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

namespace HH.WMS.DAL.InStock
{
    public class TN_WM_B_TRAY_LOCATIONDAL : DapperBaseDAL
    {
        #region 根据货位找托盘及其物料
        /// <summary>
        /// 根据货位获取货位上托盘物料信息
        /// </summary>
        /// <param name="CN_S_LOCATION_CODE">货位编码</param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-13</History>
        public DataTable GetTrayByLocationCode(string CN_S_LOCATION_CODE)
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT TI.CN_S_TRAY_CODE,CN_S_LOT_NO,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_ITEM_STATE,TI.CN_F_QUANTITY  ,TIDTL.CN_S_PRODUCTION_BATCH,TI.CN_S_MEASURE_UNIT,ti.CN_S_MODEL ");
            sbStr.Append("FROM TN_WM_B_TRAY_ITEM_MST TI LEFT JOIN TN_WM_B_TRAY_LOCATION TL ON TI.CN_S_TRAY_CODE=TL.CN_S_TRAY_CODE ");
            sbStr.Append(" LEFT JOIN TN_WM_B_TRAY_ITEM_DTL TIDTL ON TI.CN_GUID=TIDTL.CN_PARENT_GUID ");
            if (!string.IsNullOrEmpty(CN_S_LOCATION_CODE))
            {
                sbStr.Append("WHERE CN_S_LOCATION_CODE='" + CN_S_LOCATION_CODE.Trim() + "' ");
            }
            return ExecuteDataTable(sbStr.ToString());
        }
        #endregion

        #region 获取货位列表
        /// <summary>
        /// 根据托盘编码获取托盘货位关联列表
        /// </summary>
        /// <param name="trayCode"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public List<TN_WM_B_TRAY_LOCATIONEntity> GetListByTrayCode(string trayCode, string locationCode = "")
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT * FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE IN(SELECT CN_S_LOCATION_CODE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_TRAY_CODE='" + trayCode + "')");
            if (!string.IsNullOrEmpty(locationCode))
            {
                sbStr.Append(" AND CN_S_LOCATION_CODE='" + locationCode + "' ");
            }
            return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sbStr.ToString());
        }
        #endregion

        #region 获取货位列表
        /// <summary>
        /// 根据托盘编码获取托盘货位关联列表
        /// </summary>
        /// <param name="trayCode"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public List<TN_WM_B_TRAY_LOCATIONEntity> GetListByLocationCode(string locationCode)
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT * FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE='" + locationCode + "' ");

            return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sbStr.ToString());
        }
        #endregion
        #region 根据货位列表查询托盘信息
        /// <summary>
        /// 根据托盘编码获取托盘货位关联列表
        /// </summary>
        /// <param name="trayCode"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public List<TN_WM_B_TRAY_LOCATIONEntity> GetListByLocationCodeList(List<string> locationCode)
        {
            StringBuilder sbStr = new StringBuilder();
            string locations = string.Join(",", locationCode).Replace(",", "','");
            sbStr.Append("SELECT * FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE in ('" + locations + "') ");
            return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sbStr.ToString());
        }
        #endregion

        #region 获取虚拟货位列表（根据库区码查询）
        /// <summary>
        /// 根据托盘编码获取托盘货位关联列表
        /// </summary>
        /// <param name="trayCode"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public List<TN_WM_B_TRAY_LOCATIONEntity> GetlistXnAreaCode(string areaCode)
        {
            string sql = @"
SELECT b.CN_GUID,b.CN_S_ITEM_CODE,a.*FROM dbo.tn_wm_b_tray_location AS a
LEFT JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS b ON b.CN_S_TRAY_CODE = a.CN_S_TRAY_CODE WHERE CN_S_LOCATION_CODE IN  (SELECT CN_S_LOCATION_CODE from dbo.tn_wm_b_location_ext where CN_S_AREA_CODE='" + areaCode + "' )";
            return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sql);
        }
        #endregion

        #region 新增货位拓展表
        /// <summary>
        /// 新增货位拓展表
        /// </summary>
        /// <param name="list"></param>
        /// <param name="trans"></param>
        /// <History>[HANHE(LT)] CREATED BY 2018-12-26</History>
        /// 
        public OperateResult addlist(List<TN_WM_B_TRAY_LOCATIONEntity> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (TN_WM_B_TRAY_LOCATIONEntity model in list)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" if not exists(select * from TN_WM_B_TRAY_LOCATION  " + model.sqlWhere + " )");
                strSql.Append("INSERT INTO TN_WM_B_TRAY_LOCATION    (CN_S_TRAY_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE)");
                strSql.Append(" values (");
                strSql.Append("@CN_S_TRAY_CODE,@CN_S_STOCK_CODE,@CN_S_STOCK_AREA,@CN_S_LOCATION_CODE,@CN_N_INDEX,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE)");

                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_S_TRAY_CODE = model.CN_S_TRAY_CODE,
                    CN_S_STOCK_CODE = model.CN_S_STOCK_CODE,
                    CN_S_STOCK_AREA = model.CN_S_STOCK_AREA,
                    CN_S_LOCATION_CODE = model.CN_S_LOCATION_CODE,
                    CN_N_INDEX = model.CN_N_INDEX,
                    CN_S_CREATOR = model.CN_S_CREATOR,
                    CN_S_CREATOR_BY = model.CN_S_CREATOR_BY,
                    CN_T_CREATE = model.CN_T_CREATE,
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;

        }

        public OperateResult Add(TN_WM_B_TRAY_LOCATIONEntity trayLocation, IDbTransaction trans)
        {
            return DALCreator.Create<DapperDAL<TN_WM_B_TRAY_LOCATIONEntity>>().Add(trayLocation, trans);
        }

        //public OperateResult Add(TN_WM_B_TRAY_LOCATIONEntity trayLocation, IDbTransaction trans)
        //{
        //    var strSql = new StringBuilder();
        //    strSql.Append(" if not exists(select * from TN_WM_B_TRAY_LOCATION  " + trayLocation.sqlWhere + " )");
        //    strSql.Append("INSERT INTO TN_WM_B_TRAY_LOCATION    (CN_S_TRAY_CODE,CN_S_STOCK_CODE,CN_S_STOCK_AREA,CN_S_LOCATION_CODE,CN_N_INDEX,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE)");
        //    strSql.Append(" values (");
        //    strSql.Append("@CN_S_TRAY_CODE,@CN_S_STOCK_CODE,@CN_S_STOCK_AREA,@CN_S_LOCATION_CODE,@CN_N_INDEX,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE)");

        //    var result = ExecuteTranSql(strSql.ToString(), new
        //    {
        //        CN_S_TRAY_CODE = trayLocation.CN_S_TRAY_CODE,
        //        CN_S_STOCK_CODE = trayLocation.CN_S_STOCK_CODE,
        //        CN_S_STOCK_AREA = trayLocation.CN_S_STOCK_AREA,
        //        CN_S_LOCATION_CODE = trayLocation.CN_S_LOCATION_CODE,
        //        CN_N_INDEX = trayLocation.CN_N_INDEX,
        //        CN_S_CREATOR = trayLocation.CN_S_CREATOR,
        //        CN_S_CREATOR_BY = trayLocation.CN_S_CREATOR_BY,
        //        CN_T_CREATE = trayLocation.CN_T_CREATE,
        //    }, trans);
        //    if (result.Status == ResultStatus.Error)
        //        return result;
        //    return result;

        //}
        #endregion

        #region 根据货位查询托盘与货位关联表
        /// <summary>
        /// 根据托盘编码获取托盘货位关联列表
        /// </summary>
        /// <param name="trayCode"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public List<TN_WM_B_TRAY_LOCATIONEntity> GetTrayLocationBylocationCode(string locationCodes)
        {
            string sql = "SELECT a.*,b.CN_S_OWNER,b.CN_S_ITEM_CODE,b.CN_S_FIGURE_NO,b.CN_S_MODEL,b.CN_S_ITEM_STATE,b.CN_S_MEASURE_UNIT,b.CN_GUID FROM dbo.tn_wm_b_tray_location AS a LEFT JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS b ON b.CN_S_TRAY_CODE = a.CN_S_TRAY_CODE where a.CN_S_LOCATION_CODE IN  ('" + locationCodes + "')";
            return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sql);
        }
        #endregion

        #region 入库单反审时获取数据
        /// <summary>
        /// 入库单反审时获取数据
        /// </summary>
        /// <param name="opNoList"></param>
        /// <returns></returns>
        public List<TN_WM_B_TRAY_LOCATIONEntity> GetTrayCodeByIncrease(string opNoList)
        {
            string sql = "SELECT CN_S_TRAY_CODE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_LOCATION_CODE in( select CN_S_PUT_POSITION from TN_WM_ARRIVAL_DTL where CN_S_ARRIVAL_NO IN(SELECT CN_S_FROM_NO FROM TN_WM_INCREASE_INVENTORY_MST WHERE CN_S_OP_NO in (" + opNoList + ") and (CN_S_FROM_NO!='' and CN_S_FROM_NO is not null)))";
            return ExecuteQuery<TN_WM_B_TRAY_LOCATIONEntity>(sql);
        }

        public OperateResult DeleteByTrayCode(string opNoList, IDbTransaction trans)
        {
            string sql = "DELETE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_TRAY_CODE IN(" + opNoList + ")";
            return ExecuteTranSql(sql, null, trans);
        }
        #endregion

        #region 查询托盘是否绑定货位
        /// <summary>
        /// 根据托盘编码获取托盘货位关联列表
        /// </summary>
        /// <param name="trayCode"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public bool CheckTrayBindLoc(string trayCode)
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT CN_S_TRAY_CODE FROM TN_WM_B_TRAY_LOCATION WHERE CN_S_TRAY_CODE=@CN_S_TRAY_CODE ");

            DataTable dt = ExecuteDataTable(sbStr.ToString(), new { CN_S_TRAY_CODE = trayCode });
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 查询托盘是否绑定货位
        /// <summary>
        /// 根据托盘编码获取托盘货位关联列表
        /// </summary>
        /// <param name="trayCode"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public bool CheckTrayBindLocGroup(string mstGuid)
        {
            StringBuilder sbStr = new StringBuilder();

            sbStr.Append("SELECT A.CN_S_TRAY_CODE FROM TN_WM_B_TRAY_LOCATION A INNER JOIN TN_WM_B_TRAY_ITEM_MST B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE WHERE B.CN_GUID=@CN_GUID ");

            DataTable dt = ExecuteDataTable(sbStr.ToString(), new { CN_GUID = mstGuid });
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region AGV可用货位查询
        /// <summary>
        /// 根据托盘编码获取托盘货位关联列表
        /// </summary>
        /// <param name="trayCode"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
        public List<AgvLocationQuery> GetAGVLocationStatus(string stockCode,string strLocation)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT b.CN_S_LOCATION_CODE,a.CN_S_TRAY_CODE,b.CN_S_LOCATION_STATE,b.CN_S_USE_STATE FROM tn_wm_b_tray_location  a RIGHT JOIN tn_wm_b_location_ext b ON a.CN_S_LOCATION_CODE = b.CN_S_LOCATION_CODE ");
            if (!string.IsNullOrEmpty(strLocation))
            {
                sbSql.Append("where b.CN_S_LOCATION_CODE = '" + strLocation + "'");
            }
            if (!string.IsNullOrEmpty(stockCode))
            {
                sbSql.Append("where b.CN_S_STOCK_CODE like '%" + stockCode + "%'");
            }
            return ExecuteQuery<AgvLocationQuery>(sbSql.ToString()); ;
        }
        #endregion
    }
}