using HH.WMS.Common;
|
using HH.WMS.Common.Algorithm.Out;
|
using HH.WMS.Common.External;
|
using HH.WMS.DAL.Basic;
|
using HH.WMS.Entitys;
|
using HH.WMS.Entitys.Basic;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Entitys.Entitys;
|
using System;
|
using System.Collections.Generic;
|
using System.Configuration;
|
using System.Data;
|
using System.Data.Common;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.InStock
|
{
|
|
public class TN_WM_B_TRAY_ITEM_RELDAL : DapperBaseDAL
|
{
|
public string DataType = ConfigurationManager.ConnectionStrings["DataType"].ToString();
|
|
#region 货位内托盘本物料汇总
|
/// <summary>
|
/// 货位内托盘本物料汇总
|
/// </summary>
|
/// <param name="itemCode">物料编码</param>
|
/// <param name="strWhere">条件</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-16</History>
|
public TN_WM_B_TRAY_ITEM_MSTEntity GetInStockQty(string itemCode, string strWhere)
|
{
|
TN_WM_B_TRAY_ITEM_MSTEntity areaQtyEntity = new TN_WM_B_TRAY_ITEM_MSTEntity();
|
|
string str1 = "";
|
string str2 = "";
|
|
switch (DataType)
|
{
|
case "MSSQL":
|
str1 = " ISNULL(SUM(CN_F_QUANTITY),0) CN_F_QUANTITY ";
|
str2 = " AND ISNULL(TL.CN_S_LOCATION_CODE,'')!='' ";
|
break;
|
case "MYSQL":
|
str1 = " IFNULL(SUM(CN_F_QUANTITY),0) CN_F_QUANTITY ";
|
str2 = " AND IFNULL(TL.CN_S_LOCATION_CODE,'')!='' ";
|
break;
|
case "ORACLE":
|
str1 = " IFNULL(SUM(CN_F_QUANTITY),0) CN_F_QUANTITY ";
|
str2 = " AND IFNULL(TL.CN_S_LOCATION_CODE,'')!='' ";
|
break;
|
default:
|
break;
|
}
|
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append("SELECT ");
|
sbStr.Append(str1);
|
sbStr.Append(" FROM TN_WM_B_TRAY_ITEM_MST TIR LEFT JOIN TN_WM_B_TRAY_LOCATION TL ON TIR.CN_S_TRAY_CODE=TL.CN_S_TRAY_CODE WHERE TIR.CN_S_ITEM_CODE='" + itemCode + "' ");
|
sbStr.Append(str2);
|
sbStr.Append(" AND TIR.CN_C_ISIN='Y' ");
|
if (!string.IsNullOrEmpty(strWhere))
|
{
|
sbStr.Append(strWhere);
|
}
|
|
return ExecuteSingleEntity<TN_WM_B_TRAY_ITEM_MSTEntity>(sbStr.ToString());
|
}
|
#endregion
|
|
#region 根据货位获取托盘信息
|
/// <summary>
|
/// 根据货位获取托盘信息
|
/// </summary>
|
/// <param name="lstQueryUpdate"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(lt)] CREATED BY 2018-12-5</History>
|
public List<TN_WM_B_TRAY_ITEM_MSTEntity> GetTrayLocation(string sqlwhere)
|
{
|
string sql = @"SELECT * FROM dbo.tn_wm_b_tray_location AS a
|
LEFT JOIN TN_WM_B_TRAY_ITEM_MST AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(sql, null);
|
}
|
|
public List<TN_WM_B_TRAY_ITEM_MSTEntity> GetTrayLocationAndNullLocation(string sqlwhere)
|
{
|
string sql = @"SELECT b.CN_S_TRAY_CODE,a.CN_S_STOCK_CODE,a.CN_S_AREA_CODE AS CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,CN_N_INDEX,c.* FROM dbo.tn_wm_b_location_ext AS a
|
LEFT JOIN dbo.tn_wm_b_tray_location AS b ON a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE
|
LEFT JOIN dbo.TN_WM_B_TRAY_ITEM_MST AS c ON b.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE
|
LEFT JOIN (SELECT TOP 1 CN_S_ITEM_CODE,CN_F_PACKING_QTY,CN_S_PACKING_UNIT FROM dbo.TN_WM_B_UNIQUE_BARCODE WHERE CN_S_PARENT_CODE='' GROUP BY CN_S_ITEM_CODE,CN_F_PACKING_QTY,CN_S_PACKING_UNIT) AS d ON c.CN_S_ITEM_CODE=d.CN_S_ITEM_CODE {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(sql, null);
|
}
|
#endregion
|
|
#region 获取主表
|
|
public List<TN_WM_B_TRAY_ITEM_MSTEntity> GetTrayItemMst(string sqlWhere)
|
{
|
string sql = " SELECT * FROM TN_WM_B_TRAY_ITEM_MST WHERE 1=1 " + sqlWhere;
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(sql, null);
|
}
|
|
#endregion
|
|
public List<TN_WM_B_TRAY_ITEM_DTLEntity> GetDtl(string sqlWhere)
|
{
|
string sql = " SELECT * FROM TN_WM_B_TRAY_ITEM_DTL WHERE 1=1 " + sqlWhere;
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_DTLEntity>(sql, null);
|
}
|
|
public List<TN_WM_B_TRAY_ITEM_DTLEntity> GetDtlByUniques(string uniqueCodes)
|
{
|
uniqueCodes = uniqueCodes.Replace(",", "','");
|
string sql = " SELECT * FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_S_UNIQUE_CODE IN ('" + uniqueCodes + "') ";
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_DTLEntity>(sql, null);
|
}
|
|
#region 通过托盘码和排除已在的料箱码之后改托盘码是否还有其他料箱码
|
/// <summary>
|
/// 通过托盘码和排除已在的料箱码之后改托盘码是否还有其他料箱码
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <param name="UniqueCodes"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_ITEM_DTLEntity> GetTrayUniqueCode(string trayCode, string UniqueCodes)
|
{
|
string UniqueCode = UniqueCodes.Substring(0, UniqueCodes.Length - 1);
|
string sql = @" SELECT B.CN_S_UNIQUE_CODE FROM TN_WM_B_TRAY_ITEM_MST A,TN_WM_B_TRAY_ITEM_DTL B
|
WHERE A.CN_GUID=B.CN_PARENT_GUID AND A.CN_S_TRAY_CODE='" + trayCode + @"'
|
AND B.CN_S_UNIQUE_CODE NOT IN (" + UniqueCode + ")";
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_DTLEntity>(sql, null);
|
}
|
#endregion
|
|
#region 获取托盘中物料的信息，按批次汇总
|
/// <summary>
|
/// 获取托盘中物料的信息，按批次汇总
|
/// </summary>
|
/// <param name="trayCode">托盘号</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2018/12/8</History>
|
public DataTable GetTrayItem(string locationCode)
|
{
|
string sql = @"SELECT
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_QUANTITY) CN_F_QUANTITY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH,
|
B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM tn_wm_b_tray_location C
|
LEFT JOIN
|
tn_wm_tray_info D ON C.CN_S_TRAY_CODE=D.CN_S_TRAY_CODE
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID
|
WHERE C.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE
|
GROUP BY
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO";
|
|
return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode });
|
}
|
public DataTable GetTrayItemYS(string locationCode)
|
{
|
string sql = @"SELECT
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_PACKING_QTY) CN_F_PACKING_QTY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH,
|
B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM tn_wm_b_tray_location C
|
LEFT JOIN
|
tn_wm_tray_info D ON C.CN_S_TRAY_CODE=D.CN_S_TRAY_CODE
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID
|
WHERE C.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE
|
GROUP BY
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO";
|
return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode });
|
}
|
public DataTable GetTrayItemJx(string locationCode)
|
{
|
string sql = @"SELECT
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_PACKING_QTY) CN_F_PACKING_QTY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH,
|
B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM tn_wm_b_tray_location C
|
LEFT JOIN
|
tn_wm_tray_info D ON C.CN_S_TRAY_CODE=D.CN_S_TRAY_CODE
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID
|
WHERE C.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE
|
GROUP BY
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO";
|
|
return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode });
|
}
|
public DataTable GetTrayItemJxInLock(string locationCode)
|
{
|
string sql = @"SELECT
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_PACKING_QTY) CN_F_PACKING_QTY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH,
|
B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,'' AS CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM TN_WM_TASK C
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID
|
WHERE C.CN_S_END_BIT=@CN_S_LOCATION_CODE AND C.CN_S_STATE IN ('未执行','执行中','取货完成','卸货完成')
|
GROUP BY
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO";
|
|
return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode });
|
}
|
public DataTable GetTrayItemJxOutLock(string locationCode)
|
{
|
string sql = @"SELECT
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_PACKING_QTY) CN_F_PACKING_QTY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH,
|
B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,'' AS CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO,B.CN_S_PRODUCTION_BATCH FROM TN_WM_TASK C
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE
|
LEFT JOIN
|
TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID
|
WHERE C.CN_S_START_BIT=@CN_S_LOCATION_CODE AND C.CN_S_STATE IN ('未执行','执行中','取货完成','卸货完成')
|
GROUP BY
|
CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO";
|
|
return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode });
|
}
|
#endregion
|
|
#region 获取托盘中物料的信息，按批次汇总 隆基
|
/// <summary>
|
/// 获取托盘中物料的信息，按批次汇总
|
/// </summary>
|
/// <param name="trayCode">托盘号</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2018/12/8</History>
|
public DataTable GetTrayItemLJ(string locationCode)
|
{
|
// string sql = @"select CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,sum(b.CN_F_QUANTITY) CN_F_QUANTITY,a.CN_S_MEASURE_UNIT,b.CN_S_PRODUCTION_BATCH,b.CN_S_UNIQUE_CODE,a.CN_S_TRAY_CODE,d.CN_S_USE_STATE
|
// from TN_WM_B_TRAY_ITEM_MST a join
|
// TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID
|
// join tn_wm_b_tray_location c on a.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE inner join tn_wm_tray_info d on a.CN_S_TRAY_CODE=d.CN_S_TRAY_CODE
|
// where c.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE
|
// group by CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,b.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,b.CN_S_UNIQUE_CODE,a.CN_S_TRAY_CODE,d.CN_S_USE_STATE ";
|
|
// string sql = @"SELECT
|
// CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,SUM(B.CN_F_QUANTITY) CN_F_QUANTITY,A.CN_S_MEASURE_UNIT,B.CN_S_PRODUCTION_BATCH,
|
// B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO FROM tn_wm_b_tray_location C
|
// INNER JOIN
|
// tn_wm_tray_info D ON C.CN_S_TRAY_CODE=D.CN_S_TRAY_CODE
|
// LEFT JOIN
|
// TN_WM_B_TRAY_ITEM_MST A ON A.CN_S_TRAY_CODE=C.CN_S_TRAY_CODE
|
// LEFT JOIN
|
// TN_WM_B_TRAY_ITEM_DTL B ON A.CN_GUID=B.CN_PARENT_GUID
|
// WHERE C.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE
|
// GROUP BY
|
// CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,B.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,B.CN_S_UNIQUE_CODE,C.CN_S_TRAY_CODE,D.CN_S_USE_STATE,B.CN_S_SERIAL_NO,B.CN_S_LOT_NO";
|
|
string sql = @" select CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,sum(b.CN_F_QUANTITY) CN_F_QUANTITY,a.CN_S_MEASURE_UNIT,b.CN_S_PRODUCTION_BATCH,b.CN_S_UNIQUE_CODE,a.CN_S_TRAY_CODE ,
|
'' as CN_S_USE_STATE,b.CN_S_SERIAL_NO,b.CN_S_LOT_NO from TN_WM_B_TRAY_ITEM_MST a join
|
TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID=b.CN_PARENT_GUID
|
join tn_wm_b_tray_location c on a.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE
|
where c.CN_S_LOCATION_CODE=@CN_S_LOCATION_CODE
|
group by CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,b.CN_S_PRODUCTION_BATCH,CN_S_MEASURE_UNIT,b.CN_S_UNIQUE_CODE,a.CN_S_TRAY_CODE,CN_S_SERIAL_NO,CN_S_LOT_NO";
|
return ExecuteDataTable(sql, new { CN_S_LOCATION_CODE = locationCode });
|
}
|
#endregion
|
|
#region 获取托盘中物料的信息，按批次汇总
|
/// <summary>
|
/// 获取托盘中物料的信息，按批次汇总
|
/// </summary>
|
/// <param name="trayCode">托盘号</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2018/12/8</History>
|
public DataTable GetTrayDetail(string trayCode)
|
{
|
string sql = @" select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_OWNER,a.CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT,a.CN_S_IN_AREA_CODE,a.CN_S_MEASURE_UNIT,b.CN_S_LOT_NO,b.CN_S_UNIQUE_CODE,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,b.CN_F_PACKING_QTY,b.CN_S_VENDOR_NO,b.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,b.CN_S_PRODUCTION_BATCH
|
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
|
where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE ";
|
|
return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode });
|
}
|
public DataTable GetTrayDetailManual(string trayCode,string itemCode,string lotNo)
|
{
|
string sql = @" select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_OWNER,a.CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT,a.CN_S_IN_AREA_CODE,a.CN_S_MEASURE_UNIT,b.CN_S_LOT_NO,b.CN_S_UNIQUE_CODE,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,b.CN_F_PACKING_QTY,b.CN_S_VENDOR_NO,b.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,b.CN_S_PRODUCTION_BATCH
|
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
|
where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE and a.CN_S_ITEM_CODE=@CN_S_ITEM_CODE and b.CN_S_LOT_NO=@CN_S_LOT_NO ";
|
|
return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode, CN_S_ITEM_CODE = itemCode, CN_S_LOT_NO = lotNo });
|
}
|
public DataTable GetTrayDetailCheck(string trayCode)
|
{
|
string sql = @" select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_OWNER,b.CN_S_EXT1 AS CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT,a.CN_S_IN_AREA_CODE,a.CN_S_MEASURE_UNIT,b.CN_S_LOT_NO,b.CN_S_UNIQUE_CODE,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,b.CN_F_PACKING_QTY,b.CN_S_VENDOR_NO,b.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,b.CN_S_PRODUCTION_BATCH
|
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
|
where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE ";
|
return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode });
|
}
|
#endregion
|
|
#region 获取托盘中物料的信息，按物料汇总
|
/// <summary>
|
/// 获取托盘中物料的信息，按批次汇总
|
/// </summary>
|
/// <param name="trayCode">托盘号</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2018/12/8</History>
|
public DataTable GetTrayItemDetail(string trayCode)
|
{
|
string sql = @" select CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MODEL,CN_S_ITEM_STATE,CN_S_OWNER,CN_S_MEASURE_UNIT,CN_F_QUANTITY
|
from TN_WM_B_TRAY_ITEM_MST
|
where CN_S_TRAY_CODE=@CN_S_TRAY_CODE ";
|
|
return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode });
|
}
|
#endregion
|
|
#region 获取托盘中物料的信息，按批次汇总
|
/// <summary>
|
/// 获取托盘中物料的信息，按批次汇总
|
/// </summary>
|
/// <param name="trayCode">托盘号</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2018/12/8</History>
|
public DataTable GetTrayDetailYS(string trayCode)
|
{
|
string sql = @" select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,a.CN_S_MODEL,a.CN_S_ITEM_STATE,a.CN_S_OWNER,a.CN_S_FIGURE_NO,a.CN_S_MEASURE_UNIT,b.CN_S_LOT_NO,b.CN_S_UNIQUE_CODE,b.CN_T_PRODUCTION,b.CN_T_EXPIRATION,b.CN_S_SERIAL_NO,b.CN_S_VENDOR_NO,b.CN_S_VENDOR_NAME, b.CN_F_QUANTITY,b.CN_S_PRODUCTION_BATCH ,
|
c.CN_N_GOODUPLINE,b.CN_F_PACKING_QTY,b.CN_S_PACKING_UNIT 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 inner join tn_wm_tray_info c
|
on c.CN_S_TRAY_CODE = A.CN_S_TRAY_CODE
|
where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE ";
|
|
return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode });
|
}
|
public int GetGoodTypeInTray(string trayCode)
|
{
|
string sql = @" select COUNT(1) as GOOD_TYPE_NUM 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
|
where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE
|
group by a.CN_S_ITEM_CODE,b.CN_S_LOT_NO ";
|
DataTable dt = ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode });
|
int goodType = 0;
|
if(dt.Rows.Count >0)
|
{
|
goodType = dt.Rows.Count;
|
}
|
return goodType;
|
}
|
public DataTable GetTrayInfoYS(string trayCode)
|
{
|
string sql = @" select CN_N_GOODUPLINE from tn_wm_tray_info
|
where CN_S_TRAY_CODE=@CN_S_TRAY_CODE ";
|
|
return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode });
|
}
|
#endregion
|
|
#region 获取托盘内物料及明细（多行）
|
/// <summary>
|
/// 获取托盘内物料及明细
|
/// </summary>
|
/// <param name="trayCode">托盘编码</param>
|
/// <returns></returns>
|
public List<TrayItemMstAndDtlEntity> TrayItemMstAndDtl(string trayCode)
|
{
|
string sql = @"SELECT CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL
|
,CN_S_ITEM_STATE,CN_S_MEASURE_UNIT,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_S_PACKING_UNIT,
|
CN_F_PACKING_QTY,DTL.CN_F_QUANTITY,DTL.CN_S_PRODUCTION_BATCH FROM TN_WM_B_TRAY_ITEM_MST MST LEFT JOIN TN_WM_B_TRAY_ITEM_DTL DTL
|
ON MST.CN_GUID=DTL.CN_PARENT_GUID WHERE MST.CN_S_TRAY_CODE='" + trayCode + "'";
|
|
return ExecuteQuery<TrayItemMstAndDtlEntity>(sql);
|
}
|
#endregion
|
|
#region 根据条件查询托盘数量
|
/// <summary>
|
///根据条件查询托盘数量
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(lt)] CREATED BY 2019/1/17</History>
|
public List<TN_WM_B_TRAY_ITEM_MSTEntity> GetTrayItemMstList(string sqlwhere)
|
{
|
string sql = @"SELECT * FROM TN_WM_B_TRAY_ITEM_MST AS a INNER JOIN
|
tn_wm_b_tray_location AS b
|
ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(sql, null);
|
}
|
#endregion
|
|
/// <summary>
|
/// 根据托盘码获取主表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_ITEM_MSTEntity> GetMstByTrayCode(string trayCode)
|
{
|
string sql = string.Format(@"SELECT * FROM TN_WM_B_TRAY_ITEM_MST WHERE CN_S_TRAY_CODE='{0}'", trayCode);
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(sql, null);
|
}
|
|
/// <summary>
|
/// 根据托盘码获取子表
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_ITEM_DTLEntity> GetDtlByTrayCode(string trayCode)
|
{
|
string sql = string.Format(@"SELECT B.*
|
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
|
WHERE A.CN_S_TRAY_CODE='{0}'", trayCode);
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_DTLEntity>(sql, null);
|
}
|
|
public decimal GetWeightByTrayCode(string trayCode)
|
{
|
var trayDtls = GetDtlByTrayCode(trayCode);
|
decimal weight = 0;
|
if (trayDtls != null)
|
{
|
trayDtls.ForEach(e => {
|
weight += Convert.ToDecimal(e.CN_S_SERIAL_NO);
|
});
|
}
|
return weight;
|
}
|
|
public decimal GetItemQtyByTrayCode(string trayCode, LogPara logPara = null)
|
{
|
if(logPara == null) logPara = LogType.LogPara("获取托盘数量");
|
decimal qty = 0;
|
try
|
{
|
var trayMsts = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_MSTEntity>>().GetList(new { CN_S_TRAY_CODE = trayCode });
|
if (trayMsts.Count() > 0)
|
{
|
foreach (var trayMst in trayMsts)
|
{
|
// var item = DALCreator.Create<TN_WMS_ITEMDAL>().GetItemEntity(trayMst.CN_S_ITEM_CODE);
|
trayMst.TrayItemDtlList = DALCreator.Create<DapperDAL<TN_WM_B_TRAY_ITEM_DTLEntity>>().GetList(new { CN_PARENT_GUID = trayMst.CN_GUID });
|
foreach (var trayDtl in trayMst.TrayItemDtlList)
|
{
|
qty += Convert.ToDecimal(trayDtl.CN_F_PACKING_QTY);
|
}
|
}
|
}
|
Log.Detail(logPara, "获取托盘数量为：" + qty);
|
}
|
catch (Exception ex)
|
{
|
Log.Detail(logPara, "获取出错，原因：" + ex.Message);
|
}
|
return qty;
|
}
|
|
public decimal GetQtyByTrayCode(string trayCode)
|
{
|
decimal qty = 0;
|
var trayMsts = GetMstByTrayCode(trayCode);
|
foreach (var trayMst in trayMsts)
|
{
|
qty += trayMst.CN_F_QUANTITY;
|
}
|
return qty;
|
}
|
|
#region 判断数据库是否存在某个条件下的数据
|
/// <summary>
|
/// 判断数据库是否存在某个条件下的数据
|
/// </summary>
|
/// <param name="entity">条件实体</param>
|
/// <returns></returns>
|
public List<TN_WM_B_TRAY_ITEM_MSTEntity> CheckExists(TN_WM_B_TRAY_ITEM_MSTEntity entity)
|
{
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select * from TN_WM_B_TRAY_ITEM_MST ");
|
strSql.Append(" WHERE CN_S_TRAY_CODE='" + entity.CN_S_TRAY_CODE + "' AND CN_S_TRAY_GRID='" + entity.CN_S_TRAY_GRID +
|
"' AND CN_S_OWNER='" + entity.CN_S_OWNER + "' AND CN_S_ITEM_CODE='" + entity.CN_S_ITEM_CODE +// "' AND CN_S_FIGURE_NO='" + entity.CN_S_FIGURE_NO +
|
"' AND CN_S_ITEM_STATE='" + entity.CN_S_ITEM_STATE + "' ");
|
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(strSql.ToString());
|
}
|
public List<TN_WM_B_TRAY_ITEM_MSTEntity> CheckExistMsts(TN_WM_B_TRAY_ITEM_MSTEntity entity, string packingCode)
|
{
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select * from TN_WM_B_TRAY_ITEM_MST ");
|
strSql.Append(" WHERE CN_S_TRAY_CODE='" + entity.CN_S_TRAY_CODE + "' AND CN_S_TRAY_GRID='" + entity.CN_S_TRAY_GRID +
|
"' AND CN_S_OWNER='" + entity.CN_S_OWNER + "' AND CN_S_ITEM_CODE='" + entity.CN_S_ITEM_CODE + //"' AND CN_S_FIGURE_NO='" + entity.CN_S_FIGURE_NO +
|
"' AND CN_S_ITEM_STATE='" + entity.CN_S_ITEM_STATE + "' and CN_GUID in(select CN_PARENT_GUID from TN_WM_B_TRAY_ITEM_DTL where CN_S_UNIQUE_CODE='" + packingCode + "')");
|
|
return ExecuteQuery<TN_WM_B_TRAY_ITEM_MSTEntity>(strSql.ToString());
|
}
|
#endregion
|
|
|
/// <summary>
|
/// 根据条件更新关联表数量
|
/// </summary>
|
/// <param name="lstQueryUpdate"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-17</History>
|
public OperateResult UpdateByCondition(TN_WM_B_TRAY_ITEM_DTLEntity model, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" UPDATE TN_WM_B_TRAY_ITEM_DTL SET CN_F_QUANTITY=CN_F_QUANTITY+@CN_F_QUANTITY ");
|
strSql.Append(" WHERE CN_PARENT_GUID=@CN_PARENT_GUID AND CN_S_UNIQUE_CODE=@CN_S_UNIQUE_CODE ");
|
strSql.Append(" AND CN_S_LOT_NO=@CN_S_LOT_NO AND CN_S_SERIAL_NO=@CN_S_SERIAL_NO AND CN_S_PACKING_UNIT=@CN_S_PACKING_UNIT ");
|
strSql.Append(" AND CN_F_PACKING_QTY=@CN_F_PACKING_QTY AND CN_F_PURCHASE_PRICE=@CN_F_PURCHASE_PRICE ");
|
strSql.Append(" AND CN_F_RETAIL_PRICE=@CN_F_RETAIL_PRICE AND CN_S_PRODUCTION_BATCH=@CN_S_PRODUCTION_BATCH ");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_F_QUANTITY = model.CN_F_QUANTITY,
|
CN_PARENT_GUID = model.CN_PARENT_GUID,
|
CN_S_UNIQUE_CODE = string.IsNullOrEmpty(model.CN_S_UNIQUE_CODE) ? "" : model.CN_S_UNIQUE_CODE,
|
CN_S_LOT_NO = string.IsNullOrEmpty(model.CN_S_LOT_NO) ? "" : model.CN_S_LOT_NO,
|
CN_S_SERIAL_NO = string.IsNullOrEmpty(model.CN_S_SERIAL_NO) ? "" : model.CN_S_SERIAL_NO,
|
CN_S_PACKING_UNIT = string.IsNullOrEmpty(model.CN_S_PACKING_UNIT) ? "" : model.CN_S_PACKING_UNIT,
|
CN_F_PACKING_QTY = model.CN_F_PACKING_QTY,
|
CN_F_PURCHASE_PRICE = model.CN_F_PURCHASE_PRICE,
|
CN_F_RETAIL_PRICE = model.CN_F_RETAIL_PRICE,
|
CN_S_PRODUCTION_BATCH = string.IsNullOrEmpty(model.CN_S_PRODUCTION_BATCH) ? "" : model.CN_S_PRODUCTION_BATCH
|
}, trans);
|
|
return result;
|
}
|
|
|
/// <summary>
|
/// 码盘修改数量,根据Guid
|
/// </summary>
|
/// <param name="TrayItemMstlst"></param>
|
/// <param name="trans"></param>
|
public OperateResult UpdateMstQty(List<TN_WM_B_TRAY_ITEM_MSTEntity> TrayItemMstlst, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in TrayItemMstlst)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=ISNULL(CN_F_QUANTITY,0)+@CN_F_QUANTITY,CN_F_INQTY=ISNULL(CN_F_INQTY,0)+@CN_F_INQTY ");
|
strSql.Append(" WHERE CN_GUID=@CN_GUID ");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_F_QUANTITY = model.CN_F_QUANTITY,
|
CN_F_INQTY = model.CN_F_INQTY,
|
CN_GUID = model.CN_GUID
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 码盘修改分配数量,托盘信息
|
/// </summary>
|
/// <param name="TrayItemMstlst"></param>
|
/// <param name="trans"></param>
|
public OperateResult UpdateMstAllocQty(List<TN_WM_B_TRAY_ITEM_MSTEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_ALLOC_QTY=ISNULL(CN_F_ALLOC_QTY,0)+ " + model.CN_F_ALLOC_QTY + "" + model.sqlwhere + "");
|
|
result = ExecuteTranSql(strSql.ToString(), null, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
#region 修改货位
|
/// <summary>
|
/// 修改货位
|
/// </summary>
|
/// <param name="startLoca">起点货位</param>
|
/// <param name="endLoca">终点货位</param>
|
/// <returns></returns>
|
/// <History>[HANHE(lt)] CREATED BY 2018-12-5</History>
|
public SqlExecuteResult UpdateLocation(string startLoca, string endLoca, DbTransaction trans)
|
{
|
string sql = @"UPDATE dbo.tn_wm_b_tray_location SET CN_S_LOCATION_CODE='" + endLoca + "' WHERE CN_S_LOCATION_CODE='" + startLoca + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 修改货位拓展表 修改终点直接带入起点状态
|
/// <summary>
|
/// 修改货位拓展表
|
/// </summary>
|
/// <param name="startLoca">起点货位</param>
|
/// <param name="endLoca">终点货位</param>
|
/// <returns></returns>
|
/// <History>[HANHE(lt)] CREATED BY 2018-12-5</History>
|
public SqlExecuteResult UpdateLocationExt(string startLoca, string endLoca, DbTransaction trans)
|
{
|
string sql = @"UPDATE dbo.tn_wm_b_location_ext SET CN_S_USE_STATE=(SELECT CN_S_USE_STATE FROM tn_wm_b_location_ext WHERE CN_S_LOCATION_CODE='" + startLoca + "') WHERE CN_S_LOCATION_CODE='" + endLoca + "';UPDATE dbo.tn_wm_b_location_ext SET CN_S_USE_STATE='空' WHERE CN_S_LOCATION_CODE='" + startLoca + "'";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
#region 修改托盘物料分配量
|
/// <summary>
|
/// 修改托盘物料分配量
|
/// </summary>
|
/// <param name="trayItemList"></param>
|
/// <param name="trans"></param>
|
/// <param name="timeStamp"></param>
|
/// <returns></returns>
|
public OperateResult UpdateTrayItemAllocQty(List<TN_WM_B_TRAY_ITEM_MSTEntity> trayItemList, IDbTransaction trans, string timeStamp = "")
|
{
|
string sql = "UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_ALLOC_QTY=@CN_F_ALLOC_QTY,CN_S_TIMESTAMP='{0}' WHERE CN_GUID=@CN_GUID AND (CN_S_TIMESTAMP = @CN_S_TIMESTAMP OR CN_S_TIMESTAMP IS NULL)";
|
sql = string.Format(sql, string.IsNullOrEmpty(timeStamp) ? DateTime.Now.ToString("yyyyMMddHHmmssffff") : timeStamp);
|
return ExecuteTranSql(sql, trayItemList, trans);
|
}
|
#endregion
|
|
#region 新增主表
|
/// <summary>
|
///新增主表数据
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2019/4/15</History>
|
public OperateResult AddMst(TN_WM_B_TRAY_ITEM_MSTEntity entity, IDbTransaction trans)
|
{
|
string sql = @"IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_MST
|
where CN_S_TRAY_CODE=@CN_S_TRAY_CODE and CN_S_TRAY_GRID=@CN_S_TRAY_GRID
|
and CN_S_ITEM_CODE=@CN_S_ITEM_CODE and CN_S_ITEM_STATE=@CN_S_ITEM_STATE and CN_S_OWNER=@CN_S_OWNER)
|
UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY FROM TN_WM_B_TRAY_ITEM_MST
|
where CN_S_TRAY_CODE=@CN_S_TRAY_CODE and CN_S_TRAY_GRID=@CN_S_TRAY_GRID
|
and CN_S_ITEM_CODE=@CN_S_ITEM_CODE and CN_S_ITEM_STATE=@CN_S_ITEM_STATE and CN_S_OWNER=@CN_S_OWNER
|
else
|
insert into TN_WM_B_TRAY_ITEM_MST(CN_GUID,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_IN_AREA_CODE,CN_S_ITEM_STATE,CN_F_QUANTITY,CN_S_MEASURE_UNIT)
|
values (@CN_GUID,@CN_S_TRAY_CODE,@CN_S_TRAY_GRID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_FIGURE_NO,@CN_S_MODEL,@CN_S_IN_AREA_CODE,@CN_S_ITEM_STATE,@CN_F_QUANTITY,@CN_S_MEASURE_UNIT)";
|
|
return ExecuteTranSql(sql, new
|
{
|
CN_GUID = entity.CN_GUID,
|
CN_S_TRAY_CODE = entity.CN_S_TRAY_CODE,
|
CN_S_TRAY_GRID = entity.CN_S_TRAY_GRID,
|
CN_S_OWNER = entity.CN_S_OWNER,
|
CN_S_ITEM_CODE = entity.CN_S_ITEM_CODE,
|
CN_S_ITEM_NAME = entity.CN_S_ITEM_NAME,
|
CN_S_FIGURE_NO = entity.CN_S_FIGURE_NO,
|
CN_S_MODEL = entity.CN_S_MODEL,
|
CN_S_ITEM_STATE = entity.CN_S_ITEM_STATE,
|
CN_S_IN_AREA_CODE = entity.CN_S_IN_AREA_CODE,
|
CN_F_QUANTITY = entity.CN_F_QUANTITY,
|
CN_S_MEASURE_UNIT = entity.CN_S_MEASURE_UNIT
|
}, trans);
|
}
|
#endregion
|
|
#region 新增子表
|
/// <summary>
|
///新增子表
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(lt)] CREATED BY 2018/12/14</History>
|
public SqlExecuteResult AddDtl(List<TN_WM_B_TRAY_ITEM_DTLEntity> list, IDbTransaction trans)
|
{
|
List<DbCommand> cmdlist = new List<DbCommand>();
|
foreach (TN_WM_B_TRAY_ITEM_DTLEntity entity in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
//判断是否存在数据
|
strSql.Append("IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_DTl " + entity.sqlwhere + ")");
|
//修改子表
|
strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY+:CN_F_QUANTITY " + entity.sqlwhere + "");
|
strSql.Append(" else ");
|
strSql.Append("insert into TN_WM_B_TRAY_ITEM_DTL(");
|
strSql.Append("CN_PARENT_GUID,CN_S_UNIQUE_CODE,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_F_QUANTITY,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,CN_T_EXPIRATION,CN_S_PRODUCTION_BATCH,CN_S_NOTE,CN_S_EXT1,CN_S_EXT2,CN_S_EXT3,CN_S_EXT4,CN_S_EXT5,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY,CN_GUID)");
|
strSql.Append(" values (");
|
strSql.Append(":CN_PARENT_GUID,:CN_S_UNIQUE_CODE,:CN_S_LOT_NO,:CN_S_SERIAL_NO,:CN_S_PACKING_UNIT,:CN_F_PACKING_QTY,:CN_F_QUANTITY,:CN_S_VENDOR_NO,:CN_S_VENDOR_NAME,:CN_F_PURCHASE_PRICE,:CN_F_RETAIL_PRICE,:CN_T_PRODUCTION,:CN_T_EXPIRATION,:CN_S_PRODUCTION_BATCH,:CN_S_NOTE,:CN_S_EXT1,:CN_S_EXT2,:CN_S_EXT3,:CN_S_EXT4,:CN_S_EXT5,:CN_S_CREATOR,:CN_S_CREATOR_BY,:CN_T_CREATE,:CN_S_MODIFY,:CN_S_MODIFY_BY,:CN_T_MODIFY,:CN_GUID)");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
this.DataAccess.AddInParameter(cmd, "CN_PARENT_GUID", ComDbType.STRING, entity.CN_PARENT_GUID);
|
this.DataAccess.AddInParameter(cmd, "CN_S_UNIQUE_CODE", ComDbType.STRING, entity.CN_S_UNIQUE_CODE);
|
this.DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
|
this.DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.STRING, entity.CN_S_SERIAL_NO);
|
this.DataAccess.AddInParameter(cmd, "CN_S_PACKING_UNIT", ComDbType.STRING, entity.CN_S_PACKING_UNIT);
|
this.DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.DECIMAL, entity.CN_F_PACKING_QTY);
|
this.DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
|
this.DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NO", ComDbType.STRING, entity.CN_S_VENDOR_NO);
|
this.DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NAME", ComDbType.STRING, entity.CN_S_VENDOR_NAME);
|
this.DataAccess.AddInParameter(cmd, "CN_F_PURCHASE_PRICE", ComDbType.DECIMAL, entity.CN_F_PURCHASE_PRICE);
|
this.DataAccess.AddInParameter(cmd, "CN_F_RETAIL_PRICE", ComDbType.DECIMAL, entity.CN_F_RETAIL_PRICE);
|
this.DataAccess.AddInParameter(cmd, "CN_T_PRODUCTION", ComDbType.DATE, entity.CN_T_PRODUCTION);
|
this.DataAccess.AddInParameter(cmd, "CN_T_EXPIRATION", ComDbType.DATE, entity.CN_T_EXPIRATION);
|
this.DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.CN_S_PRODUCTION_BATCH);
|
this.DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT1", ComDbType.STRING, entity.CN_S_EXT1);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT2", ComDbType.STRING, entity.CN_S_EXT2);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT3", ComDbType.STRING, entity.CN_S_EXT3);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT4", ComDbType.STRING, entity.CN_S_EXT3);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT5", ComDbType.STRING, entity.CN_S_EXT3);
|
this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR);
|
this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY);
|
this.DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
|
this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
|
this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
|
this.DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
|
this.DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, System.Guid.NewGuid().ToString());
|
cmdlist.Add(cmd);
|
}
|
|
return ExecuteCommands(cmdlist, null);
|
}
|
#endregion
|
|
#region 删除等于数量为0的码数量
|
/// <summary>
|
///删除等于数量为0的码数量
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(lt)] CREATED BY 2018/12/14</History>
|
public SqlExecuteResult DeleteMstDtl(DbTransaction trans)
|
{
|
string sql = "DELETE TN_WM_B_TRAY_ITEM_MST WHERE CN_F_QUANTITY <= 0;DELETE TN_WM_B_TRAY_ITEM_DTL WHERE CN_F_QUANTITY <= 0";
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
|
public OperateResult DeleteMstDtl(string trayCodes, IDbTransaction trans)
|
{
|
string sql = "DELETE TN_WM_B_TRAY_ITEM_MST WHERE CN_F_QUANTITY <= 0 AND CN_S_TRAY_CODE in ('" + trayCodes + "');DELETE TN_WM_B_TRAY_ITEM_DTL WHERE CN_F_QUANTITY <= 0;";
|
return ExecuteTranSql(sql, null, trans);
|
}
|
#endregion
|
|
#region 修改主表
|
/// <summary>
|
///新增主表数据
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(lt)] CREATED BY 2018/12/14</History>
|
public SqlExecuteResult UpdateMst(List<TN_WM_B_TRAY_ITEM_MSTEntity> list, DbTransaction trans)
|
{
|
List<DbCommand> cmdlist = new List<DbCommand>();
|
foreach (TN_WM_B_TRAY_ITEM_MSTEntity entity in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
//strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY - :CN_F_QUANTITY,CN_F_ALLOC_QTY=CN_F_ALLOC_QTY - :CN_F_QUANTITY " + entity.sqlwhere + "");
|
strSql.Append(" IF EXISTS(SELECT * FROM dbo.TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere1 + ") ");
|
//修改主子表
|
strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY + :CN_F_QUANTITY,CN_F_INQTY=CN_F_INQTY + :CN_F_QUANTITY FROM TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere1 + "");
|
strSql.Append(" else ");
|
strSql.Append("insert into TN_WM_B_TRAY_ITEM_MST(");
|
strSql.Append("CN_GUID,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_ITEM_STATE,CN_F_QUANTITY,CN_S_MEASURE_UNIT,CN_S_IN_AREA_CODE,CN_F_INQTY,CN_F_ALLOC_QTY,CN_S_TIMESTAMP)");
|
strSql.Append(" values (");
|
strSql.Append(":CN_GUID,:CN_S_TRAY_CODE,:CN_S_TRAY_GRID,:CN_S_OWNER,:CN_S_ITEM_CODE,:CN_S_ITEM_NAME,:CN_S_FIGURE_NO,:CN_S_MODEL,:CN_S_ITEM_STATE,:CN_F_QUANTITY,:CN_S_MEASURE_UNIT,:CN_S_IN_AREA_CODE,:CN_F_INQTY,:CN_F_ALLOC_QTY,:CN_S_TIMESTAMP)");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
this.DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
|
this.DataAccess.AddInParameter(cmd, "CN_S_TRAY_CODE", ComDbType.STRING, entity.CN_S_TRAY_CODE);
|
this.DataAccess.AddInParameter(cmd, "CN_S_TRAY_GRID", ComDbType.STRING, entity.CN_S_TRAY_GRID);
|
this.DataAccess.AddInParameter(cmd, "CN_S_OWNER", ComDbType.STRING, entity.CN_S_OWNER);
|
this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_CODE", ComDbType.STRING, entity.CN_S_ITEM_CODE);
|
this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_NAME", ComDbType.STRING, entity.CN_S_ITEM_NAME);
|
this.DataAccess.AddInParameter(cmd, "CN_S_FIGURE_NO", ComDbType.STRING, entity.CN_S_FIGURE_NO);
|
this.DataAccess.AddInParameter(cmd, "CN_S_MODEL", ComDbType.STRING, entity.CN_S_MODEL);
|
this.DataAccess.AddInParameter(cmd, "CN_S_ITEM_STATE", ComDbType.STRING, entity.CN_S_ITEM_STATE);
|
this.DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
|
this.DataAccess.AddInParameter(cmd, "CN_S_MEASURE_UNIT", ComDbType.STRING, entity.CN_S_MEASURE_UNIT);
|
this.DataAccess.AddInParameter(cmd, "CN_S_IN_AREA_CODE", ComDbType.STRING, entity.CN_S_IN_AREA_CODE);
|
this.DataAccess.AddInParameter(cmd, "CN_F_INQTY", ComDbType.DECIMAL, entity.CN_F_INQTY);
|
this.DataAccess.AddInParameter(cmd, "CN_F_ALLOC_QTY", ComDbType.DECIMAL, entity.CN_F_ALLOC_QTY);
|
this.DataAccess.AddInParameter(cmd, "CN_S_TIMESTAMP", ComDbType.STRING, entity.CN_S_TIMESTAMP);
|
cmdlist.Add(cmd);
|
}
|
return ExecuteCommands(cmdlist, trans);
|
}
|
#endregion
|
|
#region 修改子表
|
/// <summary>
|
///修改子表
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(lt)] CREATED BY 2018/12/14</History>
|
public SqlExecuteResult UpdateDtl(List<TN_WM_B_TRAY_ITEM_DTLEntity> list, DbTransaction trans)
|
{
|
List<DbCommand> cmdlist = new List<DbCommand>();
|
foreach (TN_WM_B_TRAY_ITEM_DTLEntity entity in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
//strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY - :CN_F_QUANTITY " + entity.sqlwhere + "");
|
//判断是否存在数据
|
strSql.Append(" IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_DTl " + entity.sqlwhere + ")");
|
//修改子表
|
strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY + :CN_F_QUANTITY " + entity.sqlwhere + "");
|
strSql.Append(" else ");
|
strSql.Append("insert into TN_WM_B_TRAY_ITEM_DTL(");
|
strSql.Append("CN_PARENT_GUID,CN_S_UNIQUE_CODE,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_F_QUANTITY,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,CN_T_EXPIRATION,CN_S_PRODUCTION_BATCH,CN_S_NOTE,CN_S_EXT1,CN_S_EXT2,CN_S_EXT3,CN_S_EXT4,CN_S_EXT5,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY,CN_GUID)");
|
strSql.Append(" values (");
|
strSql.Append(":CN_PARENT_GUID,:CN_S_UNIQUE_CODE,:CN_S_LOT_NO,:CN_S_SERIAL_NO,:CN_S_PACKING_UNIT,:CN_F_PACKING_QTY,:CN_F_QUANTITY,:CN_S_VENDOR_NO,:CN_S_VENDOR_NAME,:CN_F_PURCHASE_PRICE,:CN_F_RETAIL_PRICE,:CN_T_PRODUCTION,:CN_T_EXPIRATION,:CN_S_PRODUCTION_BATCH,:CN_S_NOTE,:CN_S_EXT1,:CN_S_EXT2,:CN_S_EXT3,:CN_S_EXT4,:CN_S_EXT5,:CN_S_CREATOR,:CN_S_CREATOR_BY,:CN_T_CREATE,:CN_S_MODIFY,:CN_S_MODIFY_BY,:CN_T_MODIFY,:CN_GUID)");
|
DbCommand cmd = DataAccess.GetSqlStringCommand(strSql.ToString());
|
this.DataAccess.AddInParameter(cmd, "CN_PARENT_GUID", ComDbType.STRING, entity.CN_PARENT_GUID);
|
this.DataAccess.AddInParameter(cmd, "CN_S_UNIQUE_CODE", ComDbType.STRING, entity.CN_S_UNIQUE_CODE);
|
this.DataAccess.AddInParameter(cmd, "CN_S_LOT_NO", ComDbType.STRING, entity.CN_S_LOT_NO);
|
this.DataAccess.AddInParameter(cmd, "CN_S_SERIAL_NO", ComDbType.STRING, entity.CN_S_SERIAL_NO);
|
this.DataAccess.AddInParameter(cmd, "CN_S_PACKING_UNIT", ComDbType.STRING, entity.CN_S_PACKING_UNIT);
|
this.DataAccess.AddInParameter(cmd, "CN_F_PACKING_QTY", ComDbType.DECIMAL, entity.CN_F_PACKING_QTY == null ? 0 : entity.CN_F_PACKING_QTY);
|
this.DataAccess.AddInParameter(cmd, "CN_F_QUANTITY", ComDbType.DECIMAL, entity.CN_F_QUANTITY);
|
this.DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NO", ComDbType.STRING, entity.CN_S_VENDOR_NO);
|
this.DataAccess.AddInParameter(cmd, "CN_S_VENDOR_NAME", ComDbType.STRING, entity.CN_S_VENDOR_NAME);
|
this.DataAccess.AddInParameter(cmd, "CN_F_PURCHASE_PRICE", ComDbType.DECIMAL, entity.CN_F_PURCHASE_PRICE);
|
this.DataAccess.AddInParameter(cmd, "CN_F_RETAIL_PRICE", ComDbType.DECIMAL, entity.CN_F_RETAIL_PRICE);
|
this.DataAccess.AddInParameter(cmd, "CN_T_PRODUCTION", ComDbType.DATE, entity.CN_T_PRODUCTION);
|
this.DataAccess.AddInParameter(cmd, "CN_T_EXPIRATION", ComDbType.DATE, entity.CN_T_EXPIRATION);
|
this.DataAccess.AddInParameter(cmd, "CN_S_PRODUCTION_BATCH", ComDbType.STRING, entity.CN_S_PRODUCTION_BATCH);
|
this.DataAccess.AddInParameter(cmd, "CN_S_NOTE", ComDbType.STRING, entity.CN_S_NOTE);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT1", ComDbType.STRING, entity.CN_S_EXT1);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT2", ComDbType.STRING, entity.CN_S_EXT2);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT3", ComDbType.STRING, entity.CN_S_EXT3);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT4", ComDbType.STRING, entity.CN_S_EXT3);
|
this.DataAccess.AddInParameter(cmd, "CN_S_EXT5", ComDbType.STRING, entity.CN_S_EXT3);
|
this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR", ComDbType.STRING, entity.CN_S_CREATOR);
|
this.DataAccess.AddInParameter(cmd, "CN_S_CREATOR_BY", ComDbType.STRING, entity.CN_S_CREATOR_BY);
|
this.DataAccess.AddInParameter(cmd, "CN_T_CREATE", ComDbType.DATE, entity.CN_T_CREATE);
|
this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY", ComDbType.STRING, entity.CN_S_MODIFY);
|
this.DataAccess.AddInParameter(cmd, "CN_S_MODIFY_BY", ComDbType.STRING, entity.CN_S_MODIFY_BY);
|
this.DataAccess.AddInParameter(cmd, "CN_T_MODIFY", ComDbType.DATE, entity.CN_T_MODIFY);
|
this.DataAccess.AddInParameter(cmd, "CN_GUID", ComDbType.STRING, entity.CN_GUID);
|
cmdlist.Add(cmd);
|
}
|
|
return ExecuteCommands(cmdlist, trans);
|
}
|
#endregion
|
|
#region 修改主表
|
/// <summary>
|
///新增主表数据
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(lt)] CREATED BY 2019/1/7</History>
|
public OperateResult UpdateTrayItemMst(List<TN_WM_B_TRAY_ITEM_MSTEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (TN_WM_B_TRAY_ITEM_MSTEntity entity in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY - @CN_F_QUANTITY,CN_F_INQTY=CN_F_INQTY - @CN_F_QUANTITY " + entity.sqlwhere + "");
|
strSql.Append(" IF EXISTS(SELECT * FROM dbo.TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere1 + ") ");
|
//修改主子表
|
strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY ,CN_F_INQTY=CN_F_INQTY + @CN_F_QUANTITY FROM TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere1 + "");
|
strSql.Append(" else ");
|
strSql.Append("insert into TN_WM_B_TRAY_ITEM_MST(");
|
strSql.Append("CN_GUID,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_ITEM_STATE,CN_F_QUANTITY,CN_S_MEASURE_UNIT,CN_S_IN_AREA_CODE,CN_F_INQTY,CN_F_ALLOC_QTY,CN_S_TIMESTAMP)");
|
strSql.Append(" values (");
|
strSql.Append("@CN_GUID,@CN_S_TRAY_CODE,@CN_S_TRAY_GRID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_FIGURE_NO,@CN_S_MODEL,@CN_S_ITEM_STATE,@CN_F_QUANTITY,@CN_S_MEASURE_UNIT,@CN_S_IN_AREA_CODE,@CN_F_INQTY,@CN_F_ALLOC_QTY,@CN_S_TIMESTAMP)");
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_GUID = entity.CN_GUID,
|
CN_S_TRAY_CODE = entity.CN_S_TRAY_CODE,
|
CN_S_TRAY_GRID = entity.CN_S_TRAY_GRID,
|
CN_S_OWNER = entity.CN_S_OWNER,
|
CN_S_ITEM_CODE = entity.CN_S_ITEM_CODE,
|
CN_S_ITEM_NAME = entity.CN_S_ITEM_NAME,
|
CN_S_FIGURE_NO = entity.CN_S_FIGURE_NO,
|
CN_S_MODEL = entity.CN_S_MODEL,
|
CN_S_ITEM_STATE = entity.CN_S_ITEM_STATE,
|
CN_F_QUANTITY = entity.CN_F_QUANTITY,
|
CN_S_MEASURE_UNIT = entity.CN_S_MEASURE_UNIT,
|
CN_S_IN_AREA_CODE = entity.CN_S_IN_AREA_CODE,
|
CN_F_INQTY = entity.CN_F_INQTY,
|
CN_F_ALLOC_QTY = entity.CN_F_ALLOC_QTY,
|
CN_S_TIMESTAMP = entity.CN_S_TIMESTAMP,
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
#endregion
|
|
#region 修改子表
|
/// <summary>
|
///修改子表
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(lt)] CREATED BY 2018/12/14</History>
|
public OperateResult UpdateTrayItemDtl(List<TN_WM_B_TRAY_ITEM_DTLEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (TN_WM_B_TRAY_ITEM_DTLEntity entity in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY - @CN_F_QUANTITY " + entity.sqlwhere + "");
|
//判断是否存在数据
|
strSql.Append(" IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_DTl " + entity.sqlwhere1 + ")");
|
//修改子表
|
strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY " + entity.sqlwhere1 + "");
|
strSql.Append(" else ");
|
strSql.Append("insert into TN_WM_B_TRAY_ITEM_DTL(");
|
strSql.Append("CN_PARENT_GUID,CN_S_UNIQUE_CODE,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_F_QUANTITY,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,CN_T_EXPIRATION,CN_S_PRODUCTION_BATCH,CN_S_NOTE,CN_S_EXT1,CN_S_EXT2,CN_S_EXT3,CN_S_EXT4,CN_S_EXT5,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY,CN_GUID)");
|
strSql.Append(" values (");
|
strSql.Append("@CN_PARENT_GUID,@CN_S_UNIQUE_CODE,@CN_S_LOT_NO,@CN_S_SERIAL_NO,@CN_S_PACKING_UNIT,@CN_F_PACKING_QTY,@CN_F_QUANTITY,@CN_S_VENDOR_NO,@CN_S_VENDOR_NAME,@CN_F_PURCHASE_PRICE,@CN_F_RETAIL_PRICE,@CN_T_PRODUCTION,@CN_T_EXPIRATION,@CN_S_PRODUCTION_BATCH,@CN_S_NOTE,@CN_S_EXT1,@CN_S_EXT2,@CN_S_EXT3,@CN_S_EXT4,@CN_S_EXT5,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_T_MODIFY,@CN_GUID)");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_PARENT_GUID = entity.CN_PARENT_GUID,
|
CN_S_UNIQUE_CODE = entity.CN_S_UNIQUE_CODE,
|
CN_S_LOT_NO = entity.CN_S_LOT_NO,
|
CN_S_SERIAL_NO = entity.CN_S_SERIAL_NO,
|
CN_S_PACKING_UNIT = entity.CN_S_PACKING_UNIT,
|
CN_F_PACKING_QTY = entity.CN_F_PACKING_QTY == null ? 0 : entity.CN_F_PACKING_QTY,
|
CN_F_QUANTITY = entity.CN_F_QUANTITY,
|
CN_S_VENDOR_NO = entity.CN_S_VENDOR_NO,
|
CN_S_VENDOR_NAME = entity.CN_S_VENDOR_NAME,
|
CN_F_PURCHASE_PRICE = entity.CN_F_PURCHASE_PRICE,
|
CN_F_RETAIL_PRICE = entity.CN_F_RETAIL_PRICE,
|
CN_T_PRODUCTION = entity.CN_T_PRODUCTION,
|
CN_T_EXPIRATION = entity.CN_T_EXPIRATION,
|
CN_S_PRODUCTION_BATCH = entity.CN_S_PRODUCTION_BATCH,
|
CN_S_NOTE = entity.CN_S_NOTE,
|
CN_S_EXT1 = entity.CN_S_EXT1,
|
CN_S_EXT2 = entity.CN_S_EXT2,
|
CN_S_EXT3 = entity.CN_S_EXT3,
|
CN_S_EXT4 = entity.CN_S_EXT3,
|
CN_S_EXT5 = entity.CN_S_EXT3,
|
CN_S_CREATOR = entity.CN_S_CREATOR,
|
CN_S_CREATOR_BY = entity.CN_S_CREATOR_BY,
|
CN_T_CREATE = entity.CN_T_CREATE,
|
CN_S_MODIFY = entity.CN_S_MODIFY,
|
CN_S_MODIFY_BY = entity.CN_S_MODIFY_BY,
|
CN_T_MODIFY = entity.CN_T_MODIFY,
|
CN_GUID = entity.CN_GUID,
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
|
return result;
|
}
|
#endregion
|
|
#region 新增码盘主子表
|
/// <summary>
|
///新增码盘主子表
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(lt)] CREATED BY 2019/1/7</History>
|
public OperateResult AddORUpdateTrayItemMst(List<TN_WM_B_TRAY_ITEM_MSTEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (TN_WM_B_TRAY_ITEM_MSTEntity entity in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" IF EXISTS(SELECT * FROM dbo.TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere + ") ");
|
//修改主子表
|
strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY ,CN_F_INQTY=CN_F_INQTY + @CN_F_QUANTITY FROM TN_WM_B_TRAY_ITEM_MST " + entity.sqlwhere + "");
|
strSql.Append(" else ");
|
strSql.Append("insert into TN_WM_B_TRAY_ITEM_MST(");
|
strSql.Append("CN_GUID,CN_S_TRAY_CODE,CN_S_TRAY_GRID,CN_S_OWNER,CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_FIGURE_NO,CN_S_MODEL,CN_S_ITEM_STATE,CN_F_QUANTITY,CN_S_MEASURE_UNIT,CN_S_IN_AREA_CODE,CN_F_INQTY,CN_F_ALLOC_QTY,CN_S_TIMESTAMP)");
|
strSql.Append(" values (");
|
strSql.Append("@CN_GUID,@CN_S_TRAY_CODE,@CN_S_TRAY_GRID,@CN_S_OWNER,@CN_S_ITEM_CODE,@CN_S_ITEM_NAME,@CN_S_FIGURE_NO,@CN_S_MODEL,@CN_S_ITEM_STATE,@CN_F_QUANTITY,@CN_S_MEASURE_UNIT,@CN_S_IN_AREA_CODE,@CN_F_INQTY,@CN_F_ALLOC_QTY,@CN_S_TIMESTAMP)");
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_GUID = entity.CN_GUID,
|
CN_S_TRAY_CODE = entity.CN_S_TRAY_CODE,
|
CN_S_TRAY_GRID = entity.CN_S_TRAY_GRID,
|
CN_S_OWNER = entity.CN_S_OWNER,
|
CN_S_ITEM_CODE = entity.CN_S_ITEM_CODE,
|
CN_S_ITEM_NAME = entity.CN_S_ITEM_NAME,
|
CN_S_FIGURE_NO = entity.CN_S_FIGURE_NO,
|
CN_S_MODEL = entity.CN_S_MODEL,
|
CN_S_ITEM_STATE = entity.CN_S_ITEM_STATE,
|
CN_F_QUANTITY = entity.CN_F_QUANTITY,
|
CN_S_MEASURE_UNIT = entity.CN_S_MEASURE_UNIT,
|
CN_S_IN_AREA_CODE = entity.CN_S_IN_AREA_CODE,
|
CN_F_INQTY = entity.CN_F_INQTY,
|
CN_F_ALLOC_QTY = entity.CN_F_ALLOC_QTY,
|
CN_S_TIMESTAMP = entity.CN_S_TIMESTAMP,
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
/// <summary>
|
///修改子表
|
/// </summary>
|
/// <returns></returns>
|
/// <History>[Hanhe(lt)] CREATED BY 2018/12/14</History>
|
public OperateResult AddORUpdateTrayItemDtl(List<TN_WM_B_TRAY_ITEM_DTLEntity> list, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (TN_WM_B_TRAY_ITEM_DTLEntity entity in list)
|
{
|
StringBuilder strSql = new StringBuilder();
|
//判断是否存在数据
|
strSql.Append(" IF EXISTS(SELECT * FROM TN_WM_B_TRAY_ITEM_DTl " + entity.sqlwhere + ")");
|
//修改子表
|
strSql.Append(@"UPDATE TN_WM_B_TRAY_ITEM_DTl SET CN_F_QUANTITY=CN_F_QUANTITY + @CN_F_QUANTITY " + entity.sqlwhere + "");
|
strSql.Append(" else ");
|
strSql.Append("insert into TN_WM_B_TRAY_ITEM_DTL(");
|
strSql.Append("CN_PARENT_GUID,CN_S_UNIQUE_CODE,CN_S_LOT_NO,CN_S_SERIAL_NO,CN_S_PACKING_UNIT,CN_F_PACKING_QTY,CN_F_QUANTITY,CN_S_VENDOR_NO,CN_S_VENDOR_NAME,CN_F_PURCHASE_PRICE,CN_F_RETAIL_PRICE,CN_T_PRODUCTION,CN_T_EXPIRATION,CN_S_PRODUCTION_BATCH,CN_S_NOTE,CN_S_EXT1,CN_S_EXT2,CN_S_EXT3,CN_S_EXT4,CN_S_EXT5,CN_S_CREATOR,CN_S_CREATOR_BY,CN_T_CREATE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_T_MODIFY,CN_GUID)");
|
strSql.Append(" values (");
|
strSql.Append("@CN_PARENT_GUID,@CN_S_UNIQUE_CODE,@CN_S_LOT_NO,@CN_S_SERIAL_NO,@CN_S_PACKING_UNIT,@CN_F_PACKING_QTY,@CN_F_QUANTITY,@CN_S_VENDOR_NO,@CN_S_VENDOR_NAME,@CN_F_PURCHASE_PRICE,@CN_F_RETAIL_PRICE,@CN_T_PRODUCTION,@CN_T_EXPIRATION,@CN_S_PRODUCTION_BATCH,@CN_S_NOTE,@CN_S_EXT1,@CN_S_EXT2,@CN_S_EXT3,@CN_S_EXT4,@CN_S_EXT5,@CN_S_CREATOR,@CN_S_CREATOR_BY,@CN_T_CREATE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_T_MODIFY,@CN_GUID)");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_PARENT_GUID = entity.CN_PARENT_GUID,
|
CN_S_UNIQUE_CODE = entity.CN_S_UNIQUE_CODE,
|
CN_S_LOT_NO = entity.CN_S_LOT_NO,
|
CN_S_SERIAL_NO = entity.CN_S_SERIAL_NO,
|
CN_S_PACKING_UNIT = entity.CN_S_PACKING_UNIT,
|
CN_F_PACKING_QTY = entity.CN_F_PACKING_QTY == null ? 0 : entity.CN_F_PACKING_QTY,
|
CN_F_QUANTITY = entity.CN_F_QUANTITY,
|
CN_S_VENDOR_NO = entity.CN_S_VENDOR_NO,
|
CN_S_VENDOR_NAME = entity.CN_S_VENDOR_NAME,
|
CN_F_PURCHASE_PRICE = entity.CN_F_PURCHASE_PRICE,
|
CN_F_RETAIL_PRICE = entity.CN_F_RETAIL_PRICE,
|
CN_T_PRODUCTION = entity.CN_T_PRODUCTION,
|
CN_T_EXPIRATION = entity.CN_T_EXPIRATION,
|
CN_S_PRODUCTION_BATCH = entity.CN_S_PRODUCTION_BATCH,
|
CN_S_NOTE = entity.CN_S_NOTE,
|
CN_S_EXT1 = entity.CN_S_EXT1,
|
CN_S_EXT2 = entity.CN_S_EXT2,
|
CN_S_EXT3 = entity.CN_S_EXT3,
|
CN_S_EXT4 = entity.CN_S_EXT3,
|
CN_S_EXT5 = entity.CN_S_EXT3,
|
CN_S_CREATOR = entity.CN_S_CREATOR,
|
CN_S_CREATOR_BY = entity.CN_S_CREATOR_BY,
|
CN_T_CREATE = entity.CN_T_CREATE,
|
CN_S_MODIFY = entity.CN_S_MODIFY,
|
CN_S_MODIFY_BY = entity.CN_S_MODIFY_BY,
|
CN_T_MODIFY = entity.CN_T_MODIFY,
|
CN_GUID = entity.CN_GUID,
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
|
return result;
|
}
|
#endregion
|
|
#region 删除托盘物料关联表
|
/// <summary>
|
/// 删除托盘物料关联表
|
/// </summary>
|
/// <param name="opTrayCodeList">托盘编码集合</param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult DeleteByFromNo(string opTrayCodeList, IDbTransaction trans)
|
{
|
string sql = "DELETE FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_PARENT_GUID in (select CN_GUID from TN_WM_B_TRAY_ITEM_MST WHERE CN_S_TRAY_CODE IN (" + opTrayCodeList + "))";
|
|
OperateResult res = ExecuteTranSql(sql, null, trans);
|
if (res.Success)
|
{
|
string sqlmst = "DELETE FROM TN_WM_B_TRAY_ITEM_MST WHERE CN_S_TRAY_CODE IN (" + opTrayCodeList + ")";
|
res = ExecuteTranSql(sqlmst, null, trans);
|
return res;
|
}
|
return res;
|
}
|
#endregion
|
|
#region 获取未分配量=总量-分配量(运动中托盘的可用量=上架量 ，库区中静态的可用量=库存可用量)
|
/// <summary>
|
/// 获取未分配量=总量-分配量(运动中托盘的可用量=上架量 ，库区中静态的可用量=库存可用量)
|
/// </summary>
|
/// <param name="trayCode">托盘</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(dbs)] CREATED BY 2019/3/7</History>
|
public DataTable GetNoAllocQty(string trayCode)
|
{
|
string sql = @"select a.CN_S_ITEM_CODE,a.CN_F_QUANTITY,a.CN_S_ITEM_STATE,a.CN_S_OWNER,b.CN_T_PRODUCTION,b.CN_S_LOT_NO,
|
a.CN_F_QUANTITY-a.CN_F_ALLOC_QTY as UsableQty
|
from TN_WM_B_TRAY_ITEM_MST a join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID = b.CN_PARENT_GUID
|
where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
|
|
return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode });
|
}
|
#endregion
|
|
#region 获取托盘中库存信息
|
/// <summary>
|
/// 获取托盘中库存信息
|
/// </summary>
|
/// <param name="trayCode"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(dbs)] CREATED BY 2019/3/7</History>
|
public DataTable GetTrayInventoryQty(string trayCode)
|
{
|
string sql = @"select a.CN_S_ITEM_CODE,a.CN_S_ITEM_NAME,b.CN_F_QUANTITY,a.CN_S_ITEM_STATE,a.CN_S_OWNER,b.CN_T_PRODUCTION,b.CN_S_LOT_NO,b.CN_S_PRODUCTION_BATCH
|
from TN_WM_B_TRAY_ITEM_MST a join TN_WM_B_TRAY_ITEM_DTL b on a.CN_GUID = b.CN_PARENT_GUID
|
where a.CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
|
|
return ExecuteDataTable(sql, new { CN_S_TRAY_CODE = trayCode });
|
}
|
#endregion
|
|
#region 减少托盘与物料关联量
|
public OperateResult ReduceMstQty(TN_WM_B_TRAY_ITEM_MSTEntity mst, IDbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY-@CN_F_QUANTITY where CN_GUID=@CN_GUID ";
|
return ExecuteTranSql(sql, new { CN_F_QUANTITY = mst.CN_F_QUANTITY, CN_GUID = mst.CN_GUID }, trans);
|
}
|
|
public OperateResult ReduceDtlQty(TN_WM_B_TRAY_ITEM_DTLEntity dtl, IDbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_TRAY_ITEM_DTL SET CN_F_QUANTITY=CN_F_QUANTITY-@CN_F_QUANTITY where CN_GUID=@CN_GUID ";
|
|
return ExecuteTranSql(sql, new { CN_F_QUANTITY = dtl.CN_F_QUANTITY, CN_GUID = dtl.CN_GUID }, trans);
|
}
|
#endregion
|
|
public OperateResult AddDtlQty(TN_WM_B_TRAY_ITEM_DTLEntity dtl, IDbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_TRAY_ITEM_DTL SET CN_F_QUANTITY=CN_F_QUANTITY+@CN_F_QUANTITY where CN_GUID=@CN_GUID ";
|
|
return ExecuteTranSql(sql, new { CN_F_QUANTITY = dtl.CN_F_QUANTITY, CN_GUID = dtl.CN_GUID }, trans);
|
}
|
|
public OperateResult AddMstQty(TN_WM_B_TRAY_ITEM_MSTEntity mst, IDbTransaction trans)
|
{
|
string sql = @"UPDATE TN_WM_B_TRAY_ITEM_MST SET CN_F_QUANTITY=CN_F_QUANTITY+@CN_F_QUANTITY where CN_GUID=@CN_GUID ";
|
return ExecuteTranSql(sql, new { CN_F_QUANTITY = mst.CN_F_QUANTITY, CN_GUID = mst.CN_GUID }, trans);
|
}
|
|
#region 删除托盘物料的主子表
|
|
public OperateResult DeleteTrayItem(string sqlWhere, IDbTransaction trans)
|
{
|
var sql = string.Format(@"DELETE FROM TN_WM_B_TRAY_ITEM_DTL WHERE CN_PARENT_GUID IN (SELECT CN_GUID FROM TN_WM_B_TRAY_ITEM_MST WHERE {0}) ", sqlWhere);
|
if (ExecuteTranSql(sql, null, null).Success)
|
{
|
sql = string.Format("DELETE FROM TN_WM_B_TRAY_ITEM_MST WHERE {0}", sqlWhere);
|
return ExecuteTranSql(sql, null, trans);
|
}
|
|
return ExecuteTranSql(sql, null, trans);
|
}
|
|
#endregion
|
|
#region 验证料箱码是否属于当前托盘
|
public bool CheckUniqueIsCurTray(string tryaCode, string uniqueCode)
|
{
|
var sql = string.Format(@"SELECT COUNT(1)
|
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
|
WHERE A.CN_S_TRAY_CODE='{0}' AND B.CN_S_UNIQUE_CODE='{1}'", tryaCode, uniqueCode);
|
var dt = ExecuteDataTable(sql, null);
|
return Convert.ToInt32(dt.Rows[0][0]) > 0;
|
}
|
#endregion
|
|
#region 验证料箱码是否属于当前托盘
|
public bool CheckCurTrayIsHasItem(string tryaCode)
|
{
|
var sql = string.Format(@"SELECT COUNT(1)
|
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
|
WHERE A.CN_S_TRAY_CODE='{0}' ", tryaCode);
|
var dt = ExecuteDataTable(sql, null);
|
return Convert.ToInt32(dt.Rows[0][0]) > 0;
|
}
|
#endregion
|
|
|
|
}
|
}
|
