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 货位内托盘本物料汇总
///
/// 货位内托盘本物料汇总
///
/// 物料编码
/// 条件
///
/// [HANHE(XDL)] CREATED BY 2018-11-16
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(sbStr.ToString());
}
#endregion
#region 根据货位获取托盘信息
///
/// 根据货位获取托盘信息
///
///
///
///
/// [HANHE(lt)] CREATED BY 2018-12-5
public List 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(sql, null);
}
public List 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(sql, null);
}
#endregion
#region 获取主表
public List GetTrayItemMst(string sqlWhere)
{
string sql = " SELECT * FROM TN_WM_B_TRAY_ITEM_MST WHERE 1=1 " + sqlWhere;
return ExecuteQuery(sql, null);
}
#endregion
public List GetDtl(string sqlWhere)
{
string sql = " SELECT * FROM TN_WM_B_TRAY_ITEM_DTL WHERE 1=1 " + sqlWhere;
return ExecuteQuery(sql, null);
}
public List 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(sql, null);
}
#region 通过托盘码和排除已在的料箱码之后改托盘码是否还有其他料箱码
///
/// 通过托盘码和排除已在的料箱码之后改托盘码是否还有其他料箱码
///
///
///
///
public List 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(sql, null);
}
#endregion
#region 获取托盘中物料的信息,按批次汇总
///
/// 获取托盘中物料的信息,按批次汇总
///
/// 托盘号
///
/// [Hanhe(DBS)] CREATED BY 2018/12/8
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 获取托盘中物料的信息,按批次汇总 隆基
///
/// 获取托盘中物料的信息,按批次汇总
///
/// 托盘号
///
/// [Hanhe(DBS)] CREATED BY 2018/12/8
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 获取托盘中物料的信息,按批次汇总
///
/// 获取托盘中物料的信息,按批次汇总
///
/// 托盘号
///
/// [Hanhe(DBS)] CREATED BY 2018/12/8
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 获取托盘中物料的信息,按物料汇总
///
/// 获取托盘中物料的信息,按批次汇总
///
/// 托盘号
///
/// [Hanhe(DBS)] CREATED BY 2018/12/8
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 获取托盘中物料的信息,按批次汇总
///
/// 获取托盘中物料的信息,按批次汇总
///
/// 托盘号
///
/// [Hanhe(DBS)] CREATED BY 2018/12/8
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 获取托盘内物料及明细(多行)
///
/// 获取托盘内物料及明细
///
/// 托盘编码
///
public List 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(sql);
}
#endregion
#region 根据条件查询托盘数量
///
///根据条件查询托盘数量
///
///
/// [Hanhe(lt)] CREATED BY 2019/1/17
public List 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(sql, null);
}
#endregion
///
/// 根据托盘码获取主表
///
///
///
public List GetMstByTrayCode(string trayCode)
{
string sql = string.Format(@"SELECT * FROM TN_WM_B_TRAY_ITEM_MST WHERE CN_S_TRAY_CODE='{0}'", trayCode);
return ExecuteQuery(sql, null);
}
///
/// 根据托盘码获取子表
///
///
///
public List 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(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>().GetList(new { CN_S_TRAY_CODE = trayCode });
if (trayMsts.Count() > 0)
{
foreach (var trayMst in trayMsts)
{
// var item = DALCreator.Create().GetItemEntity(trayMst.CN_S_ITEM_CODE);
trayMst.TrayItemDtlList = DALCreator.Create>().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 判断数据库是否存在某个条件下的数据
///
/// 判断数据库是否存在某个条件下的数据
///
/// 条件实体
///
public List 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(strSql.ToString());
}
public List 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(strSql.ToString());
}
#endregion
///
/// 根据条件更新关联表数量
///
///
///
///
/// [HANHE(XDL)] CREATED BY 2018-11-17
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;
}
///
/// 码盘修改数量,根据Guid
///
///
///
public OperateResult UpdateMstQty(List 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;
}
///
/// 码盘修改分配数量,托盘信息
///
///
///
public OperateResult UpdateMstAllocQty(List 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 修改货位
///
/// 修改货位
///
/// 起点货位
/// 终点货位
///
/// [HANHE(lt)] CREATED BY 2018-12-5
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 修改货位拓展表 修改终点直接带入起点状态
///
/// 修改货位拓展表
///
/// 起点货位
/// 终点货位
///
/// [HANHE(lt)] CREATED BY 2018-12-5
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 修改托盘物料分配量
///
/// 修改托盘物料分配量
///
///
///
///
///
public OperateResult UpdateTrayItemAllocQty(List 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 新增主表
///
///新增主表数据
///
///
/// [Hanhe(DBS)] CREATED BY 2019/4/15
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 新增子表
///
///新增子表
///
///
/// [Hanhe(lt)] CREATED BY 2018/12/14
public SqlExecuteResult AddDtl(List list, IDbTransaction trans)
{
List cmdlist = new List();
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的码数量
///
///删除等于数量为0的码数量
///
///
/// [Hanhe(lt)] CREATED BY 2018/12/14
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 修改主表
///
///新增主表数据
///
///
/// [Hanhe(lt)] CREATED BY 2018/12/14
public SqlExecuteResult UpdateMst(List list, DbTransaction trans)
{
List cmdlist = new List();
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 修改子表
///
///修改子表
///
///
/// [Hanhe(lt)] CREATED BY 2018/12/14
public SqlExecuteResult UpdateDtl(List list, DbTransaction trans)
{
List cmdlist = new List();
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 修改主表
///
///新增主表数据
///
///
/// [Hanhe(lt)] CREATED BY 2019/1/7
public OperateResult UpdateTrayItemMst(List 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 修改子表
///
///修改子表
///
///
/// [Hanhe(lt)] CREATED BY 2018/12/14
public OperateResult UpdateTrayItemDtl(List 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 新增码盘主子表
///
///新增码盘主子表
///
///
/// [Hanhe(lt)] CREATED BY 2019/1/7
public OperateResult AddORUpdateTrayItemMst(List 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;
}
///
///修改子表
///
///
/// [Hanhe(lt)] CREATED BY 2018/12/14
public OperateResult AddORUpdateTrayItemDtl(List 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 删除托盘物料关联表
///
/// 删除托盘物料关联表
///
/// 托盘编码集合
///
///
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 获取未分配量=总量-分配量(运动中托盘的可用量=上架量 ,库区中静态的可用量=库存可用量)
///
/// 获取未分配量=总量-分配量(运动中托盘的可用量=上架量 ,库区中静态的可用量=库存可用量)
///
/// 托盘
///
/// [Hanhe(dbs)] CREATED BY 2019/3/7
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 获取托盘中库存信息
///
/// 获取托盘中库存信息
///
///
///
/// [Hanhe(dbs)] CREATED BY 2019/3/7
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
}
}