using HH.WMS.Entitys.Func;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Dynamic;
using HH.WMS.Common.Algorithm;
using HH.WMS.Entitys.Common;
using System.Data.Common;
using HH.WMS.Common.Algorithm.Out;
using HH.WMS.Common;

namespace HH.WMS.DAL.Algorithm
{
    public class Out_AlgorDAL : DapperBaseDAL
    {
        /// <summary>
        /// 根据物料等获取作业区中匹配的可出库货位，作业区目前都是整托出货
        /// </summary>
        /// <param name="itemCodes">物料编码集合 以逗号分隔</param>
        /// <param name="code">仓库/库区编号</param>
        /// <param name="type">1：根据库区  2：根据仓库</param>
        /// <returns></returns>
        public List<outAssignLocation> GetAssignFlatItemQty(string itemCodes, string areaCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT TB.*,TB2.CN_S_USE_STATE  FROM ");
            strSql.AppendLine(" (SELECT  A.CN_S_TRAY_CODE as trayCode,D.CN_T_PRODUCTION as productDate,D.CN_S_LOT_NO as lotNo, ");
            strSql.AppendLine(" B.CN_S_LOCATION_CODE as locationCode,B.CN_S_STOCK_AREA as stockAreaCode,B.CN_T_CREATE as opTime FROM TN_WM_B_TRAY_ITEM_MST A ");
            strSql.AppendLine(" INNER JOIN dbo.tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE ");
            strSql.AppendLine(" INNER JOIN dbo.TN_WM_B_TRAY_ITEM_DTL D ON D.CN_PARENT_GUID = A.CN_GUID ");
            if (!string.IsNullOrEmpty(areaCodes))
            {
                strSql.AppendLine(" AND B.CN_S_STOCK_AREA IN ('" + areaCodes + "') ");
            }
            //追加查询条件
            strSql.AppendLine(" AND A.CN_S_ITEM_CODE = '" + itemCodes + "' ");
            strSql.AppendLine(" ) TB INNER JOIN tn_wm_b_location_ext TB2 on TB.locationCode=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" WHERE  TB2.CN_S_LOCATION_STATE='正常' AND TB2.CN_S_USE_STATE!='空' ");
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("outAssign-GetAssignFlatItemQty", "sql语句为：" + strSql.ToString());
            List<outAssignLocation> lstQty = ExecuteQuery<outAssignLocation>(strSql.ToString());
            return lstQty;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="itemCodes"></param>
        /// <param name="areaCodes"></param>
        /// <returns></returns>
        public List<outAssignLocation> GetAssignFlatItemQtyJX(string areaCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT A.CN_S_TRAY_CODE as trayCode,'' as productDate,'' as lotNo, A.CN_S_LOCATION_CODE as locationCode,A.CN_S_STOCK_AREA as stockAreaCode, ");
            strSql.AppendLine(" A.CN_T_CREATE as opTime,TB2.CN_S_USE_STATE   FROM tn_wm_b_tray_location A ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_location_ext TB2 on A.locationCode=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" WHERE  TB2.CN_S_LOCATION_STATE='正常' AND TB2.CN_S_USE_STATE!='空' ");
            if (!string.IsNullOrEmpty(areaCodes))
            {
                strSql.AppendLine(" AND A.CN_S_STOCK_AREA = '" + areaCodes + "' ");
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("outAssign-GetAssignFlatItemQty", "sql语句为：" + strSql.ToString());
            List<outAssignLocation> lstQty = ExecuteQuery<outAssignLocation>(strSql.ToString());
            return lstQty;
        }
        /// <summary>
        /// 根据物料等获取作业区中匹配的可出库货位，作业区目前都是整托出货
        /// </summary>
        /// <param name="itemCodes">物料编码集合 以逗号分隔</param>
        /// <param name="code">仓库/库区编号</param>
        /// <param name="type">1：根据库区  2：根据仓库</param>
        /// <returns></returns>
        public List<outAssignLocation> GetAssignFlatItemQtyNT(string itemCodes, string areaCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT TB.trayCode,TB.productDate,TB.locationCode,TB.stockAreaCode,TB.opTime,TB2.CN_S_USE_STATE,SUM(TB.CN_F_QUANTITY) AS totalQty  FROM ");
            strSql.AppendLine(" (SELECT  A.CN_S_TRAY_CODE as trayCode,D.CN_T_PRODUCTION as productDate,");
            strSql.AppendLine(" B.CN_S_LOCATION_CODE as locationCode,B.CN_S_STOCK_AREA as stockAreaCode,B.CN_T_CREATE as opTime,A.CN_F_QUANTITY FROM TN_WM_B_TRAY_ITEM_MST A ");
            strSql.AppendLine(" INNER JOIN dbo.tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE ");
            strSql.AppendLine(" INNER JOIN dbo.TN_WM_B_TRAY_ITEM_DTL D ON D.CN_PARENT_GUID = A.CN_GUID ");
            if (!string.IsNullOrEmpty(areaCodes))
            {
                strSql.AppendLine(" AND B.CN_S_STOCK_AREA IN ('" + areaCodes + "') ");
            }
            //追加查询条件
            strSql.AppendLine(" AND A.CN_S_ITEM_CODE = '" + itemCodes + "' ");
            strSql.AppendLine(" ) TB INNER JOIN tn_wm_b_location_ext TB2 on TB.locationCode=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" WHERE  TB2.CN_S_LOCATION_STATE='正常' AND TB2.CN_S_USE_STATE!='空' ");
            strSql.AppendLine(" GROUP BY  TB.trayCode,TB.productDate,TB.locationCode,TB.stockAreaCode,TB.opTime,TB2.CN_S_USE_STATE ");
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("outAssign-GetAssignFlatItemQty", "sql语句为：" + strSql.ToString());
            List<outAssignLocation> lstQty = ExecuteQuery<outAssignLocation>(strSql.ToString());
            return lstQty;
        }
        /// <summary>
        /// 根据物料等获取作业区中匹配的可出库货位，作业区目前都是整托出货
        /// </summary>
        /// <param name="itemCodes">物料编码集合 以逗号分隔</param>
        /// <param name="code">仓库/库区编号</param>
        /// <param name="type">1：根据库区  2：根据仓库</param>
        /// <returns></returns>
        public List<outAssignLocation> GetAssignFlatItemQtyYS(string itemCodes, string lotNo, string areaCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT TB.trayCode,TB.locationCode,TB.stockAreaCode,TB2.CN_S_USE_STATE as useState ,isnull(SUM(CN_F_PACKING_QTY),0) as totalWeight FROM ");
            strSql.AppendLine(" (SELECT  A.CN_S_TRAY_CODE as trayCode, ");
            strSql.AppendLine(" B.CN_S_LOCATION_CODE as locationCode,B.CN_S_STOCK_AREA as stockAreaCode,CN_S_SERIAL_NO,CN_F_PACKING_QTY FROM TN_WM_B_TRAY_ITEM_MST A ");
            strSql.AppendLine(" INNER JOIN dbo.tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE ");
            strSql.AppendLine(" INNER JOIN dbo.TN_WM_B_TRAY_ITEM_DTL D ON D.CN_PARENT_GUID = A.CN_GUID ");
            if (!string.IsNullOrEmpty(areaCodes))
            {
                strSql.AppendLine(" AND B.CN_S_STOCK_AREA IN ('" + areaCodes + "') ");
            }
            if (!string.IsNullOrEmpty(lotNo))
            {
                strSql.AppendLine(" AND D.CN_S_LOT_NO ='" + lotNo + "' ");
            }
            //追加查询条件
            strSql.AppendLine(" AND A.CN_S_ITEM_CODE = '" + itemCodes + "' ");
            strSql.AppendLine(" ) TB INNER JOIN tn_wm_b_location_ext TB2 on TB.locationCode=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" WHERE  TB2.CN_S_LOCATION_STATE='正常' AND TB2.CN_S_USE_STATE!='空' ");
            strSql.AppendLine(" GROUP BY  TB.trayCode,TB.locationCode,TB.stockAreaCode,TB2.CN_S_USE_STATE ");
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("outAssign-GetAssignFlatItemQtyYS", "sql语句为：" + strSql.ToString());
            List<outAssignLocation> lstQty = ExecuteQuery<outAssignLocation>(strSql.ToString());
            return lstQty;
        }

        /// <summary>
        /// 根据库区、托盘类型获取空托盘的匹配货位
        /// </summary>
        /// <param name="itemCode"></param>
        /// <param name="traySpec"></param>
        /// <param name="areaCodes"></param>
        /// <returns></returns>
        public List<outAssignLocation> GetAssignFlatEmptySpecTray(string traySpec, string areaCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT A.CN_S_TRAY_CODE as trayCode,A.CN_S_LOCATION_CODE as locationCode,A.CN_S_STOCK_AREA as stockAreaCode  FROM dbo.tn_wm_b_tray_location A ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_location_ext TB2 on A.CN_S_LOCATION_CODE=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" INNER JOIN tn_wm_tray_info B on A.CN_S_TRAY_CODE=B.CN_S_TRAY_CODE ");
            strSql.AppendLine(" LEFT JOIN TN_WM_B_TRAY_ITEM_MST C on A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE ");
            strSql.AppendLine(" WHERE C.CN_S_TRAY_CODE IS NULL AND TB2.CN_S_LOCATION_STATE='正常' AND TB2.CN_S_USE_STATE!='空' ");
            if (!string.IsNullOrEmpty(areaCodes))
            {
                strSql.AppendLine(" AND A.CN_S_STOCK_AREA = '" + areaCodes + "' ");
            }
            //追加查询条件
            //物料编码
            if (!string.IsNullOrEmpty(traySpec))
            {
                strSql.AppendLine(" AND B.CN_S_SPEC = '" + traySpec + "' ");
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("outAssign-GetAssignFlatEmptySpecTray", "sql语句为：" + strSql.ToString());
            List<outAssignLocation> lstQty = ExecuteQuery<outAssignLocation>(strSql.ToString());
          
            return lstQty;
        }

        /// <summary>
        /// 根据库区、托盘类型获取空托盘的匹配货位
        /// </summary>
        /// <param name="itemCode"></param>
        /// <param name="traySpec"></param>
        /// <param name="areaCodes"></param>
        /// <returns></returns>
        public List<outAssignLocation> GetAssignFlatEmptySpecTrayBH(string traySpec, string areaCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT A.CN_S_TRAY_CODE  as trayCode,B.CN_S_LOCATION_CODE as locationCode,B.CN_S_AREA_CODE as stockAreaCode FROM dbo.tn_wm_b_tray_location A  INNER JOIN  tn_wm_b_location_ext B ");
            strSql.AppendLine(" ON a.CN_S_LOCATION_CODE = B.CN_S_LOCATION_CODE WHERE B.CN_S_LOCATION_STATE='正常' AND B.CN_S_USE_STATE!='空' ");
            if (!string.IsNullOrEmpty(areaCodes))
            {
                strSql.AppendLine(" AND B.CN_S_AREA_CODE = '" + areaCodes + "' ");
            }

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("outAssign-GetAssignFlatEmptySpecTrayBH", "sql语句为：" + strSql.ToString());
            List<outAssignLocation> lstQty = ExecuteQuery<outAssignLocation>(strSql.ToString());

            return lstQty;
        }

        /// <summary>
        /// 根据库区、托盘类型获取空托盘的匹配货位
        /// </summary>
        /// <param name="itemCode"></param>
        /// <param name="traySpec"></param>
        /// <param name="areaCodes"></param>
        /// <returns></returns>
        public List<outAssignLocation> GetAssignFlatEmptySpecTrayLJ(string traySpec, string areaCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT A.CN_S_TRAY_CODE  as trayCode,B.CN_S_LOCATION_CODE as locationCode,B.CN_S_AREA_CODE as stockAreaCode FROM dbo.tn_wm_b_tray_location A  INNER JOIN  tn_wm_b_location_ext B ");
            strSql.AppendLine(" ON a.CN_S_LOCATION_CODE = B.CN_S_LOCATION_CODE WHERE B.CN_S_LOCATION_STATE='正常' AND B.CN_S_USE_STATE!='空' ");
            if (!string.IsNullOrEmpty(areaCodes))
            {
                strSql.AppendLine(" AND B.CN_S_AREA_CODE = '" + areaCodes + "' ");
            }

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("outAssign-GetAssignFlatEmptySpecTrayLJ", "sql语句为：" + strSql.ToString());
            List<outAssignLocation> lstQty = ExecuteQuery<outAssignLocation>(strSql.ToString());

            return lstQty;
        }
        /// <summary>
        /// 根据库区、托盘类型获取空托盘的匹配货位
        /// </summary>
        /// <param name="itemCode"></param>
        /// <param name="traySpec"></param>
        /// <param name="areaCodes"></param>
        /// <returns></returns>
        public List<outAssignLocation> GetAssignFlatEmptySpecTrayYM(string traySpec, string areaCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT A.CN_S_TRAY_CODE  as trayCode,B.CN_S_LOCATION_CODE as locationCode,B.CN_S_AREA_CODE as stockAreaCode FROM dbo.tn_wm_b_tray_location A  INNER JOIN  tn_wm_b_location_ext B ");
            strSql.AppendLine(" ON a.CN_S_LOCATION_CODE = B.CN_S_LOCATION_CODE WHERE B.CN_S_LOCATION_STATE='正常' AND B.CN_S_USE_STATE!='空' ");
            if (!string.IsNullOrEmpty(areaCodes))
            {
                strSql.AppendLine(" AND B.CN_S_AREA_CODE = '" + areaCodes + "' ");
            }

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            Log.AlgorInfo("outAssign-GetAssignFlatEmptySpecTrayYM", "sql语句为：" + strSql.ToString());
            List<outAssignLocation> lstQty = ExecuteQuery<outAssignLocation>(strSql.ToString());

            return lstQty;
        }

        #region 根据库区或者仓库获取对应物料的数量
        /// <summary>
        /// 根据库区或者仓库获取对应物料的数量
        /// </summary>
        /// <param name="itemCodes">物料编码集合 以逗号分隔</param>
        /// <param name="code">仓库/库区编号</param>
        /// <param name="type">1：根据库区  2：根据仓库</param>
        /// <returns></returns>
        public List<itemQty> GetItemQtyByAreaCode(string strWhere, string code, int type, string orderBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT TB.*,TB2.CN_S_USE_STATE FROM ");
            strSql.AppendLine(" (SELECT CN_F_PACKING_QTY AS itemQty,CN_S_ITEM_CODE,a.CN_S_TRAY_CODE,CN_S_TRAY_GRID, ");
            strSql.AppendLine(" B.CN_T_OPERATE,A.CN_S_LOT_NO,A.CN_T_EXPIRATION,B.CN_S_LOCATION_CODE,B.CN_S_STOCK_AREA,B.CN_N_INDEX FROM tn_wm_b_tray_item_rel A ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE ");
            if (type == 1)
            {
                strSql.AppendLine(" WHERE B.CN_S_STOCK_AREA='" + code + "' ");
            }
            else
            {
                strSql.AppendLine(" WHERE B.CN_S_STOCK_CODE='" + code + "' ");
            }
            //追加查询条件
            strSql.AppendLine(strWhere);
            strSql.AppendLine(" ) TB INNER JOIN tn_wm_b_location_ext TB2 on TB.CN_S_LOCATION_CODE=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" WHERE  TB2.CN_S_LOCATION_STATE='正常' AND TB2.CN_S_USE_STATE!='空' ");
            strSql.AppendLine(orderBy);

            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            List<itemQty> lstQty = ExecuteQuery<itemQty>(strSql.ToString());
            return lstQty;
        }

        #endregion


        #region 获取对应出库所需物料的数量-锁货位物料量不锁货位
        /// <summary>
        /// 获取对应出库所需物料的数量-锁货位物料量不锁货位-吉鑫祥
        /// </summary>
        /// <param name="itemCodes">物料编码集合 以逗号分隔</param>
        /// <param name="code">仓库/库区编号</param>
        /// <param name="type">1：根据库区  2：根据仓库</param>
        /// <returns></returns>
        public List<itemQty> GetItemQtyLockRel(string strWhere, string code, string orderBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT  (A.CN_F_QUANTITY-A.CN_F_ALLOC_QTY) AS Qty,A.CN_S_ITEM_CODE as itemCode,A.CN_S_TRAY_CODE as trayCode,A.CN_S_TRAY_GRID as trayGrid, ");
            strSql.AppendLine(" C.CN_T_EXPIRATION as ExpireDate ,C.CN_S_LOT_NO as lotNo,C.CN_S_PRODUCTION_BATCH as prodBatchCode,B.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" as locationCode,B.CN_S_STOCK_AREA as stockAreaCode FROM tn_wm_b_tray_item_mst A ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE INNER JOIN (select top 1 *   tn_wm_b_tray_item_dtl D where d.CN_PARENT_GUID = A.CN_GUID ORDER BY CN_S_PRODUCTION_BATCH ASC,CN_S_LOT_NO ASC)  C");
            strSql.AppendLine(" ON A.CN_GUID = C.CN_PARENT_GUID WHERE B.CN_S_STOCK_AREA='" + code + "' ");
            //追加查询条件
            strSql.AppendLine(strWhere);
            strSql.AppendLine(orderBy);
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            List<itemQty> lstQty = ExecuteQuery<itemQty>(strSql.ToString());
            return lstQty;
        }
        /// <summary>
        /// 获取对应出库所需物料的数量-锁货位物料量不锁货位-吉鑫祥-不管控批次
        /// </summary>
        /// <param name="itemCodes">物料编码集合 以逗号分隔</param>
        /// <param name="code">仓库/库区编号</param>
        /// <param name="type">1：根据库区  2：根据仓库</param>
        /// <returns></returns>
        public List<itemQty> GetItemQtyJxx(string strWhere, string code)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT C.CN_F_QUANTITY AS Qty,A.CN_F_ALLOC_QTY,A.CN_S_ITEM_CODE as itemCode,A.CN_S_OWNER,A.CN_S_ITEM_STATE,A.CN_S_TRAY_CODE as trayCode,A.CN_S_TRAY_GRID as trayGrid, ");
            strSql.AppendLine(" C.CN_T_EXPIRATION as ExpireDate ,C.CN_S_LOT_NO as lotNo,C.CN_S_PRODUCTION_BATCH as prodBatchCode,B.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" as locationCode,B.CN_S_STOCK_AREA as stockAreaCode FROM tn_wm_b_tray_item_mst A ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE INNER JOIN   tn_wm_b_tray_item_dtl  C");
            strSql.AppendLine(" ON A.CN_GUID = C.CN_PARENT_GUID WHERE B.CN_S_STOCK_AREA='" + code + "' ");         
            //追加查询条件
            strSql.AppendLine(strWhere);
            strSql.AppendLine(" ORDER BY   C.CN_S_LOT_NO ASC");
            HH.WMS.Common.Log.AlgorInfo("GetItemQtyJxxt", "strSql语句:" + strSql.ToString());
            //strSql.AppendLine(orderBy);
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            List<itemQty> lstQty = ExecuteQuery<itemQty>(strSql.ToString());
            return lstQty;
        }
        /// <summary>
        /// 获取对应出库所需物料的数量-锁货位物料量不锁货位-吉鑫祥-不管控批次
        /// </summary>
        /// <param name="itemCodes">物料编码集合 以逗号分隔</param>
        /// <param name="code">仓库/库区编号</param>
        /// <param name="type">1：根据库区  2：根据仓库</param>
        /// <returns></returns>
        public List<itemQty> GetOutItemQty(string itemCode,string itemState,string prodBatchCode,string batchCode,string ownerName, string areaCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT cast(newid() as varchar(50)) as ID, C.CN_F_QUANTITY AS Qty,case C.CN_F_PACKING_QTY when 0 then 1 else CN_F_PACKING_QTY end as PAKQty,C.CN_S_PACKING_UNIT as packUnit, A.CN_F_ALLOC_QTY,A.CN_S_ITEM_CODE as itemCode,A.CN_S_OWNER,A.CN_S_ITEM_STATE,A.CN_S_TRAY_CODE as trayCode,A.CN_S_TRAY_GRID as trayGrid, ");
            strSql.AppendLine(" C.CN_T_EXPIRATION as ExpireDate ,C.CN_T_PRODUCTION AS productDate,C.CN_S_LOT_NO as lotNo,C.CN_S_PRODUCTION_BATCH as prodBatchCode,B.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" as locationCode,B.CN_S_STOCK_AREA as stockAreaCode, A.CN_S_TIMESTAMP,TB2.CN_S_ROW,TB2.CN_S_COL  FROM tn_wm_b_tray_item_mst A ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE INNER JOIN   tn_wm_b_tray_item_dtl  C");
            strSql.AppendLine(" ON A.CN_GUID = C.CN_PARENT_GUID  ");
            strSql.AppendLine(" INNER JOIN  tn_wm_b_location_ext  TB2 on B.CN_S_LOCATION_CODE=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" WHERE  TB2.CN_S_LOCATION_STATE='正常' AND TB2.CN_S_USE_STATE!='空' ");
            strSql.AppendLine(" AND B.CN_S_STOCK_AREA='" + areaCode + "' ");
            //物料编码
            if (!string.IsNullOrEmpty(itemCode))
            {
                strSql.AppendLine(" AND A.CN_S_ITEM_CODE  ='" + itemCode + "' ");
            }
            //物料状态
            if (!string.IsNullOrEmpty(itemState))
            {
                strSql.AppendLine(" AND A.CN_S_ITEM_STATE = '" + itemState + "' ");
            }
            //批次号
            if (!string.IsNullOrEmpty(batchCode))
            {
                strSql.AppendLine(" AND C.CN_S_LOT_NO = '" + batchCode + "' ");
            }
            //生产批次号
            if (!string.IsNullOrEmpty(prodBatchCode))
            {
                strSql.AppendLine(" AND C.CN_S_PRODUCTION_BATCH = '" + prodBatchCode + "' ");
            }
            //货主
            if (!string.IsNullOrEmpty(ownerName))
            {
                strSql.AppendLine(" AND A.CN_S_OWNER = '" + ownerName + "' ");
            }
            //strSql.AppendLine(orderBy);
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            HH.WMS.Common.Log.AlgorInfo("GetOutItemQty", "获取库区中匹配物料的strSql语句:" + strSql.ToString());
            List<itemQty> lstQty = ExecuteQuery<itemQty>(strSql.ToString());
            return lstQty;
        }
        /// <summary>
        /// 获取对应出库所需物料的数量-锁货位物料量不锁货位-吉鑫祥-不管控批次
        /// </summary>
        /// <param name="itemCodes">物料编码集合 以逗号分隔</param>
        /// <param name="code">仓库/库区编号</param>
        /// <param name="type">1：根据库区  2：根据仓库</param>
        /// <returns></returns>
        public List<itemQty> GetOutItemAllocQty(string itemCode, string itemState,  string ownerName, string areaCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT A.CN_F_ALLOC_QTY,A.CN_S_ITEM_CODE as itemCode,A.CN_S_OWNER,A.CN_S_ITEM_STATE,B.CN_S_TRAY_CODE as trayCode,A.CN_S_TRAY_GRID as trayGrid ");
            strSql.AppendLine(" FROM tn_wm_b_tray_item_mst A INNER JOIN tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE WHERE B.CN_S_STOCK_AREA='" + areaCode + "'");
            //追加查询条件

            //物料编码
            if (!string.IsNullOrEmpty(itemCode))
            {
                strSql.AppendLine(" AND A.CN_S_ITEM_CODE  ='" + itemCode + "' ");
            }
            //物料状态
            if (!string.IsNullOrEmpty(itemState))
            {
                strSql.AppendLine(" AND A.CN_S_ITEM_STATE = '" + itemState + "' ");
            }
            //货主
            if (!string.IsNullOrEmpty(ownerName))
            {
                strSql.AppendLine(" AND A.CN_S_OWNER = '" + ownerName + "' ");
            }
            //strSql.AppendLine(orderBy);
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            HH.WMS.Common.Log.AlgorInfo("GetOutItemAllocQty", "获取库区中匹配物料的strSql语句:" + strSql.ToString());
            List<itemQty> lstQty = ExecuteQuery<itemQty>(strSql.ToString());
            return lstQty;
        }
        public List<itemQty> GetItemQtyLockRelSszy(string strWhere, string code)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT C.CN_F_QUANTITY AS Qty,A.CN_F_ALLOC_QTY,A.CN_S_ITEM_CODE as itemCode,A.CN_S_OWNER,A.CN_S_ITEM_STATE,A.CN_S_TRAY_CODE as trayCode,A.CN_S_TRAY_GRID as trayGrid, ");
            strSql.AppendLine(" C.CN_T_EXPIRATION as ExpireDate ,C.CN_S_LOT_NO as lotNo,C.CN_S_PRODUCTION_BATCH as prodBatchCode,B.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" as locationCode,B.CN_S_STOCK_AREA as stockAreaCode FROM tn_wm_b_tray_item_mst A ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_tray_location B ON A.CN_S_TRAY_CODE = B.CN_S_TRAY_CODE INNER JOIN   FROM tn_wm_b_tray_item_dtl  C");
            strSql.AppendLine(" ON A.CN_GUID = C.CN_PARENT_GUID WHERE B.CN_S_STOCK_AREA='" + code + "' ");
            strSql.AppendLine(" ORDER BY  C.prodBatchCode ASC, C.CN_S_LOT_NO ASC");
            //追加查询条件
            strSql.AppendLine(strWhere);
            //strSql.AppendLine(orderBy);
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            List<itemQty> lstQty = ExecuteQuery<itemQty>(strSql.ToString());
            return lstQty;
        }

        #endregion


        #region 补料  取空托盘
        /// <summary>
        /// 补料  取空托盘
        /// </summary>
        /// <param name="areaCode"></param>
        /// <returns></returns>
        public List<FeedEmptyTrayEntity> GetEmptyTrayList(string areaCode, string traySpec, string locationCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" select A.CN_S_LOCATION_CODE,A.CN_S_TRAY_CODE,C.CN_S_SPEC,C.CN_F_LOADBEARING ");
            strSql.AppendLine(" from tn_wm_b_tray_location A ");
            strSql.AppendLine(" LEFT JOIN  ");
            strSql.AppendLine(" ( ");
            strSql.AppendLine(" SELECT CN_S_TRAY_CODE FROM TN_WM_B_TRAY_ITEM_MST ");
            strSql.AppendLine(" GROUP BY CN_S_TRAY_CODE ");
            strSql.AppendLine(" ) B ");
            strSql.AppendLine(" ON A.CN_S_TRAY_CODE=B.CN_S_TRAY_CODE ");
            strSql.AppendLine(" INNER JOIN tn_wm_tray_info C ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_location_ext D ON A.CN_S_LOCATION_CODE=D.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" where B.CN_S_TRAY_CODE IS NULL AND D.CN_S_LOCATION_STATE = '正常'  AND A.CN_S_STOCK_AREA='" + areaCode + "' ");
            if (!string.IsNullOrEmpty(locationCodes))
            {
                strSql.AppendLine("  AND A.CN_S_LOCATION_CODE IN (" + locationCodes + ")");
            }
            if (!string.IsNullOrEmpty(traySpec))
            {
                strSql.AppendLine("  AND C.CN_S_SPEC = '" + traySpec + "'");
            }
            DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            HH.WMS.Common.Log.AlgorInfo("Feed按重量补料-GetEmptyTrayList", strSql.ToString());
            List<FeedEmptyTrayEntity> list = ExecuteQuery<FeedEmptyTrayEntity>(strSql.ToString());
            return list;
        }
        public List<FridTrayWeight> GetTrayWeightBySpec(string areaCode, string traySpec, string locationCodes)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT B.CN_S_TRAY_CODE as trayCode,CN_F_LOADBEARING as loadWeight,C.CN_F_WEIGHT as fridWeight,CN_F_GROSSWEIGHT as crossWeight,B.CN_S_LOCATION_CODE as locationCode,C.CN_S_SPEC as traySpec ");
            strSql.AppendLine(" FROM  tn_wm_b_tray_location B  ");
            strSql.AppendLine(" INNER JOIN tn_wm_tray_info  C ON B.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_location_ext TB2 ON B.CN_S_LOCATION_CODE=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" where C.CN_S_USE_STATE='不满'    AND TB2.CN_S_LOCATION_STATE='正常'  ");
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql.AppendLine(" and B.CN_S_STOCK_AREA='" + areaCode + "' ");
            }
            if (!string.IsNullOrEmpty(traySpec))
            {
                strSql.AppendLine(" and C.CN_S_SPEC = '" + traySpec + "' ");
            }
            if (!string.IsNullOrEmpty(locationCodes))
            {
                strSql.AppendLine(" and B.CN_S_LOCATION_CODE in( " + locationCodes + ") ");
            }
            HH.WMS.Common.Log.AlgorInfo("Feed按重量补料-GetTrayWeightBySpec", strSql.ToString());
           // DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
            List<FridTrayWeight> lstFrid = ExecuteQuery<FridTrayWeight>(strSql.ToString());
            return lstFrid;
        }
        public List<ItemInWeight> GetItemWeightByTray(string trayCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT A.CN_S_ITEM_CODE,B.CN_S_LOT_NO,SUM(CN_F_PACKING_QTY) AS  ITEMQTY");
            strSql.AppendLine(" FROM TN_WM_B_TRAY_ITEM_MST A INNER JOIN TN_WM_B_TRAY_ITEM_DTL  B ON  A.CN_GUID = B.CN_PARENT_GUID   ");
            strSql.AppendLine(" where CN_S_TRAY_CODE ='" + trayCode + "'");
            strSql.AppendLine(" GROUP BY  CN_S_ITEM_CODE,CN_S_LOT_NO");
            List<ItemInWeight> lstItem = ExecuteQuery<ItemInWeight>(strSql.ToString());
            return lstItem;
        }
        public List<ItemInTray> GetItemByTray(string trayCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT A.CN_S_ITEM_CODE,B.CN_S_LOT_NO");
            strSql.AppendLine(" FROM TN_WM_B_TRAY_ITEM_MST A INNER JOIN TN_WM_B_TRAY_ITEM_DTL  B ON  A.CN_GUID = B.CN_PARENT_GUID   ");
            strSql.AppendLine(" where CN_S_TRAY_CODE ='" + trayCode + "'");
            strSql.AppendLine(" GROUP BY  CN_S_ITEM_CODE,CN_S_LOT_NO");
            List<ItemInTray> lstItem = ExecuteQuery<ItemInTray>(strSql.ToString());
            return lstItem;
        }

        public List<FridTray> GetNotFullTrayBySpec(string areaCode, string itemCode, string traySpec)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendLine(" SELECT B.CN_S_TRAY_CODE as trayCode,B.CN_S_LOCATION_CODE as locationCode,C.CN_N_GOODUPLINE ");
            strSql.AppendLine(" FROM  tn_wm_b_tray_location B  ");
            strSql.AppendLine(" INNER JOIN tn_wm_tray_info  C ON B.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE ");
            strSql.AppendLine(" INNER JOIN SELECT TN_WM_B_TRAY_ITEM_MST  D ON B.CN_S_TRAY_CODE=D.CN_S_TRAY_CODE ");
            strSql.AppendLine(" INNER JOIN tn_wm_b_location_ext TB2 ON B.CN_S_LOCATION_CODE=TB2.CN_S_LOCATION_CODE ");
            strSql.AppendLine(" where C.CN_S_USE_STATE  in ('空','不满')    AND TB2.CN_S_LOCATION_STATE='正常'  ");
            if (!string.IsNullOrEmpty(areaCode))
            {
                strSql.AppendLine(" and B.CN_S_STOCK_AREA='" + areaCode + "' ");
            }
            if (!string.IsNullOrEmpty(traySpec))
            {
                strSql.AppendLine(" and C.CN_S_SPEC = '" + traySpec + "' ");
            }
            if (!string.IsNullOrEmpty(itemCode))
            {
                strSql.AppendLine(" and D.CN_S_ITEM_CODE = '" + itemCode + "' ");
            }
            HH.WMS.Common.Log.AlgorInfo("Feed未满托盘补料-GetNotFullTrayBySpec", strSql.ToString());
            List<FridTray> lstFrid = ExecuteQuery<FridTray>(strSql.ToString());
            return lstFrid;
        }
        #endregion









    }
}