/// <summary>
/// Explosion BOM for retrieve list child items of specific ITEM_CD on level one.
/// </summary>
/// <param name="ITEM_CD">Parent Item Code which want to explosion child BOM.</param>
/// <param name="ORDER_LOC">Location of order.</param>
/// <param name="QTY">Quantity of work result.</param>
/// <returns>List of WorkResultEntryViewDTO</returns>
public List <WorkResultEntryViewDTO> LoadConsumptionListByExplosionBOM(Database database, NZString ITEM_CD, NZString ORDER_LOC, NZDecimal QTY)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
InventoryPeriodDAO periodDao = new InventoryPeriodDAO(db);
InventoryPeriodDTO periodDto = periodDao.LoadCurrentYearMonth(null);
#region SQL Statement
//sb.AppendLine(" SELECT T.*, T.REQUEST_QTY " + WorkResultEntryViewDTO.eColumns.CONSUMPTION_QTY);
//sb.AppendLine(" FROM ( ");
//sb.AppendLine(" SELECT ");
//sb.AppendLine(" T_BOM.LOWER_ITEM_CD " + WorkResultEntryViewDTO.eColumns.ITEM_CD);
//sb.AppendLine(" ,L_ITM.LOT_CONTROL_CLS " + WorkResultEntryViewDTO.eColumns.LOT_CONTROL_CLS);
//sb.AppendLine(" ,:ORDER_LOC LOC_CD ");
//sb.AppendLine(" ,NULL " + WorkResultEntryViewDTO.eColumns.LOT_NO);
//sb.AppendLine(" ,ISNULL(STK.ON_HAND_QTY, 0) " + WorkResultEntryViewDTO.eColumns.ON_HAND_QTY);
//sb.AppendLine(" ,(T_BOM.LOWER_QTY / T_BOM.UPPER_QTY) * :WO_QTY " + WorkResultEntryViewDTO.eColumns.REQUEST_QTY);
////sb.AppendLine(" ,T_BOM.LOWER_QTY " + WorkResultEntryViewDTO.eColumns.LOWER_QTY);
////sb.AppendLine(" ,T_BOM.UPPER_QTY " + WorkResultEntryViewDTO.eColumns.UPPER_QTY);
//sb.AppendLine(" ,L_ITM.INV_UM_CLS INV_UM_CLS");
//sb.AppendLine(" ,T_BOM." + WorkResultEntryViewDTO.eColumns.CHILD_ORDER_LOC_CD.ToString() + " " + WorkResultEntryViewDTO.eColumns.CHILD_ORDER_LOC_CD);
//sb.AppendLine(" FROM ");
//sb.AppendLine(" FNC_GET_BOM_EXPLOSION( :ITEM_CD, 0, '', '~') T_BOM");
//sb.AppendLine(" INNER JOIN TB_ITEM_MS L_ITM");
//sb.AppendLine(" ON L_ITM.ITEM_CD = T_BOM.LOWER_ITEM_CD");
//sb.AppendLine(" LEFT JOIN TB_INV_ONHAND_TR STK");
//sb.AppendLine(" ON STK.ITEM_CD = T_BOM.LOWER_ITEM_CD");
//sb.AppendLine(" AND STK.LOC_CD = :ORDER_LOC");
//sb.AppendLine(" AND ISNULL(STK.LOT_NO, '') = '' ");
//sb.AppendLine(" AND STK.YEAR_MONTH=:YEAR_MONTH ");
//sb.AppendLine(" WHERE T_BOM.LEVEL = 1");
//sb.AppendLine(" ) T ");
//sb.AppendLine(" ORDER BY T.ITEM_CD");
#endregion
DataRequest req = new DataRequest(sb.ToString());
req.Parameters.Add("ITEM_CD", DataType.NVarChar, ITEM_CD.Value);
req.Parameters.Add("ORDER_LOC", DataType.NVarChar, ORDER_LOC.Value);
req.Parameters.Add("WO_QTY", DataType.Number, QTY.Value);
req.Parameters.Add("YEAR_MONTH", DataType.NVarChar, periodDto.YEAR_MONTH.Value);
return(db.ExecuteForList <WorkResultEntryViewDTO>(req));
}
public List <MultiWorkResultEntryViewDTO> LoadChildItemToInputMultiWorkResult(Database database, NZString ITEM_CD, NZString ORDER_LOC, NZString LOT_NO, DataDefine.eTRAN_SUB_CLS workResultType)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
InventoryPeriodDAO periodDao = new InventoryPeriodDAO(db);
InventoryPeriodDTO periodDto = periodDao.LoadCurrentYearMonth(null);
#region SQL Statement
sb.AppendLine(@"select stk.LOT_NO, stk.ON_HAND_QTY ");
sb.AppendLine(@"from TB_INV_ONHAND_TR stk");
sb.AppendLine(@"where (STK.LOC_CD = :ORDER_LOC)");
sb.AppendLine(@"and (STK.YEAR_MONTH = :YEAR_MONTH)");
sb.AppendLine(@"and (STK.ITEM_CD = :ITEM_CD)");
sb.AppendLine(@"and (STK.ON_HAND_QTY > 0)");
sb.AppendLine(@"and (STK.LOT_NO like :LOT_NO + '%' or :LOT_NO is null)");
if (workResultType == DataDefine.eTRAN_SUB_CLS.WR)
{
sb.AppendLine(@"and (STK.LOT_NO not like '%#R')"); //work result หา lot ปกติ
}
else if (workResultType == DataDefine.eTRAN_SUB_CLS.RW)
{
sb.AppendLine(@"and (STK.LOT_NO like '%#R')"); //rework หา lot ที่เป็น reserve
}
else
{
throw new NotSupportedException();
}
#endregion
DataRequest req = new DataRequest(sb.ToString());
req.Parameters.Add("ITEM_CD", DataType.NVarChar, ITEM_CD.Value);
req.Parameters.Add("ORDER_LOC", DataType.NVarChar, ORDER_LOC.Value);
req.Parameters.Add("YEAR_MONTH", DataType.NVarChar, periodDto.YEAR_MONTH.Value);
req.Parameters.Add("LOT_NO", DataType.VarChar, LOT_NO.Value);
return(db.ExecuteForList <MultiWorkResultEntryViewDTO>(req));
}
public List <LotMaintenanceDTO> LoadInventory(Database database, LotMaintenanceDTO data)
{
Database db = UseDatabase(database);
InventoryPeriodDAO periodDao = new InventoryPeriodDAO(db);
InventoryPeriodDTO periodDto = periodDao.LoadCurrentYearMonth(null);
StringBuilder sb = new StringBuilder();
string tableName = EVOFramework.Data.DTOUtility.ReadTableName(typeof(InventoryOnhandDTO));
sb.AppendLine(" SELECT ");
sb.AppendLine(" " + InventoryOnhandDTO.eColumns.LOT_NO);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ON_HAND_QTY);
sb.AppendLine(" FROM " + tableName);
sb.AppendLine("where (LOC_CD = :ORDER_LOC)");
sb.AppendLine("and (YEAR_MONTH = :YEAR_MONTH)");
sb.AppendLine("and (ITEM_CD = :ITEM_CD)");
sb.AppendLine("and (ON_HAND_QTY > 0)");
sb.AppendLine("and (LOT_NO like :LOT_NO + '%' or :LOT_NO is null)");
if (!data.ShowReserveLot.NVL(false))
{
sb.AppendLine("and (LOT_NO not like '%#R')");
}
DataRequest req = new DataRequest(sb.ToString());
req.Parameters.Add("ITEM_CD", data.Item_NO.Value);
req.Parameters.Add("ORDER_LOC", data.Location.Value);
req.Parameters.Add("LOT_NO", data.LOT_NO.Value);
req.Parameters.Add("YEAR_MONTH", periodDto.YEAR_MONTH.Value);
return(db.ExecuteForList <LotMaintenanceDTO>(req));
}
