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
|
|
|
|
|
|
|
|
|
|
}
|
}
|
