/// <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)); }