using HH.MData; using HH.WMS.Common; using HH.WMS.Entitys.Basic; using MongoDB.Bson; using MongoDB.Driver.Builders; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; namespace HH.WMS.DAL.Pda { public class YsApiDAL : DapperBaseDAL { #region 人工搬运查询库区中符合条件的货位 public DataTable GetManualHandDetail(string itemCode, string lotNo, string areaCode) { StringBuilder sbStr = new StringBuilder(); sbStr.Append(" select a.CN_S_TRAY_CODE,d.CN_S_LOCATION_CODE,SUM(CAST(b.CN_S_SERIAL_NO AS DECIMAL(18,4))) as CN_S_SERIAL_NO ,SUM(b.CN_F_QUANTITY) as CN_F_QUANTITY ,SUM(b.CN_F_PACKING_QTY) as CN_F_PACKING_QTY "); sbStr.Append(" from dbo.TN_WM_B_TRAY_ITEM_MST a inner join dbo.TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID = b.CN_PARENT_GUID"); sbStr.Append(" inner join dbo.tn_wm_b_tray_location c on a.CN_S_TRAY_CODE = c.CN_S_TRAY_CODE inner join dbo.tn_wm_b_location_ext d on c.CN_S_LOCATION_CODE = d.CN_S_LOCATION_CODE"); sbStr.Append(" where d.CN_S_LOCATION_STATE = '正常' and CN_S_USE_STATE = '满'"); if (!string.IsNullOrEmpty(itemCode)) { sbStr.Append(" and a.CN_S_ITEM_CODE = '" + itemCode + "'"); } if (!string.IsNullOrEmpty(lotNo)) { sbStr.Append(" and b.CN_S_LOT_NO = '" + lotNo + "'"); } if (!string.IsNullOrEmpty(areaCode)) { sbStr.Append(" and c.CN_S_STOCK_AREA = '" + areaCode + "'"); } sbStr.Append(" group by a.CN_S_TRAY_CODE,d.CN_S_LOCATION_CODE "); return ExecuteDataTable(sbStr.ToString()); } #endregion #region 查询库区库存 public DataTable GetAreaQty(string itemCode, string lotNo, string areaCode) { StringBuilder sbStr = new StringBuilder(); sbStr.Append(" select CN_S_ITEM_CODE,CN_S_LOT_NO,(CN_F_QUANTITY-CN_F_ALLOC_QTY) as CN_F_QUANTITY, CN_F_PLANNED_QTY,CN_S_MEASURE_UNIT from dbo.tn_wm_b_area_qty where 1= 1 "); if (!string.IsNullOrEmpty(itemCode)) { sbStr.Append(" and CN_S_ITEM_CODE = '" + itemCode + "'"); } if (!string.IsNullOrEmpty(lotNo)) { sbStr.Append(" and CN_S_LOT_NO = '" + lotNo + "'"); } if (!string.IsNullOrEmpty(areaCode)) { sbStr.Append(" and CN_S_STOCK_AREA = '" + areaCode + "'"); } sbStr.Append(" order by CN_S_LOT_NO asc "); DataTable dt = ExecuteDataTable(sbStr.ToString()); return dt; } #endregion #region 查询库区中的物料批次并按从小到大顺序排序 public List GetLotList(string itemCode, string areaCode) { StringBuilder sbStr = new StringBuilder(); sbStr.Append(" select CN_S_LOT_NO from dbo.tn_wm_b_area_qty where 1= 1 "); if (!string.IsNullOrEmpty(itemCode)) { sbStr.Append(" and CN_S_ITEM_CODE = '" + itemCode + "'"); } if (!string.IsNullOrEmpty(areaCode)) { sbStr.Append(" and CN_S_STOCK_AREA = '" + areaCode + "'"); } sbStr.Append(" order by CN_S_LOT_NO asc "); DataTable dt = ExecuteDataTable(sbStr.ToString()); List lstLotNo = dt.AsEnumerable().Select(x => x.Field("CN_S_LOT_NO")).ToList(); return lstLotNo; } #endregion } }