/// <summary> /// Update record by using primary key value on the given data variable /// </summary> /// <param name="database"></param> /// <param name="data">Data with primary key value.</param> /// <returns></returns> public int UpdateWithoutPK(Database database, InventoryOnhandDTO data) { Database db = UseDatabase(database); StringBuilder sb = new StringBuilder(); #region SQL Statement sb.AppendLine(" UPDATE " + data.TableName); sb.AppendLine(" SET "); //sb.AppendLine(" " + InventoryOnhandDTO.eColumns.YEAR_MONTH + "=:YEAR_MONTH"); //sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_BEGIN_DATE + "=:PERIOD_BEGIN_DATE"); //sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_END_DATE + "=:PERIOD_END_DATE"); //sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ITEM_CD + "=:ITEM_CD"); //sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOC_CD + "=:LOC_CD"); //sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOT_NO + "=:LOT_NO"); sb.AppendLine(" " + InventoryOnhandDTO.eColumns.OPEN_QTY + "=:OPEN_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.IN_QTY + "=:IN_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OUT_QTY + "=:OUT_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ADJUST_QTY + "=:ADJUST_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.STOCK_TAKE_QTY + "=:STOCK_TAKE_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ON_HAND_QTY + "=:ON_HAND_QTY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_BY + "=:UPD_BY"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_DATE + "=GETDATE()"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_MACHINE + "=:UPD_MACHINE"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LAST_RECEIVE_DATE + "=:LAST_RECEIVE_DATE"); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OLD_DATA + "=:OLD_DATA"); sb.AppendLine(" WHERE "); //sb.AppendLine(" " + InventoryOnhandDTO.eColumns.ID + "=:ID"); sb.AppendLine(" " + InventoryOnhandDTO.eColumns.YEAR_MONTH + "=:YEAR_MONTH"); sb.AppendLine(" AND " + InventoryOnhandDTO.eColumns.PERIOD_BEGIN_DATE + "=:PERIOD_BEGIN_DATE"); sb.AppendLine(" AND " + InventoryOnhandDTO.eColumns.PERIOD_END_DATE + "=:PERIOD_END_DATE"); sb.AppendLine(" AND " + InventoryOnhandDTO.eColumns.ITEM_CD + "=:ITEM_CD"); sb.AppendLine(" AND " + InventoryOnhandDTO.eColumns.LOC_CD + "=:LOC_CD"); sb.AppendLine(" AND ((LOT_NO IS NULL) OR (LOT_NO =:LOT_NO))"); #endregion DataRequest req = new DataRequest(sb.ToString()); #region Parameters req.Parameters.Add("ID", DataType.Default, data.ID.Value); req.Parameters.Add("YEAR_MONTH", DataType.NVarChar, data.YEAR_MONTH.Value); req.Parameters.Add("PERIOD_BEGIN_DATE", DataType.DateTime, data.PERIOD_BEGIN_DATE.Value); req.Parameters.Add("PERIOD_END_DATE", DataType.DateTime, data.PERIOD_END_DATE.Value); req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value); req.Parameters.Add("LOC_CD", DataType.NVarChar, data.LOC_CD.Value); req.Parameters.Add("LOT_NO", DataType.NVarChar, data.LOT_NO.Value); req.Parameters.Add("OPEN_QTY", DataType.Number, data.OPEN_QTY.Value); req.Parameters.Add("IN_QTY", DataType.Number, data.IN_QTY.Value); req.Parameters.Add("OUT_QTY", DataType.Number, data.OUT_QTY.Value); req.Parameters.Add("ADJUST_QTY", DataType.Number, data.ADJUST_QTY.Value); req.Parameters.Add("STOCK_TAKE_QTY", DataType.Number, data.STOCK_TAKE_QTY.Value); req.Parameters.Add("ON_HAND_QTY", DataType.Number, data.ON_HAND_QTY.Value); req.Parameters.Add("UPD_BY", DataType.NVarChar, data.UPD_BY.Value); req.Parameters.Add("UPD_MACHINE", DataType.NVarChar, data.UPD_MACHINE.Value); req.Parameters.Add("LAST_RECEIVE_DATE", DataType.DateTime, data.LAST_RECEIVE_DATE.Value); req.Parameters.Add("OLD_DATA", DataType.Number, 0); #endregion return(db.ExecuteNonQuery(req)); }
/// <summary> /// Check exist before manipulate data. If found record will update data. Otherwise insert new data. /// </summary> /// <param name="database"></param> /// <param name="data"></param> /// <returns></returns> public int AddNewOrUpdate(Database database, InventoryOnhandDTO data) { Database db = UseDatabase(database); if (Exist(database, data.ID)) { return(UpdateWithoutPK(db, data)); } return(AddNew(db, data)); }
public List <InventoryMovementInqUIDM> LoadDataInventorymovementInquiryByLotNo(NZString YEAR_MONTH, NZDateTime PERIOD_BEGIN_DATE, NZDateTime PERIOD_END_DATE , NZString ITEM_CD , NZString LOC_CD , NZString LOT_NO , NZString PACK_NO ) { InventoryBIZ biz = new InventoryBIZ(); List <InventoryTransactionDTO> listData = biz.LoadInventoryMovementByLotNo(PERIOD_BEGIN_DATE, PERIOD_END_DATE, ITEM_CD, LOC_CD, LOT_NO); InventoryOnhandDTO dtoInvOnHand = biz.LoadInventoryOnHandByYearMonth(YEAR_MONTH, ITEM_CD, LOC_CD, LOT_NO, PACK_NO); return(GenerateLines(dtoInvOnHand, listData)); }
public AdjustmentEntryUIDM ConvertDTOToUIDM(InventoryTransactionDTO dto) { AdjustmentEntryUIDM model = new AdjustmentEntryUIDM(); model.TransactionID = dto.TRANS_ID; model.AdjustNo = dto.SLIP_NO; model.AdjustDate = dto.TRANS_DATE; model.AdjustType = dto.IN_OUT_CLS; model.ItemCode = dto.ITEM_CD; model.StoredLoc = dto.LOC_CD; model.LotNo = dto.LOT_NO; model.PackNo = dto.PACK_NO; model.FGNo = dto.FG_NO; model.ExternalLotNo = dto.EXTERNAL_LOT_NO; model.AdjustWeight = dto.WEIGHT; model.AdjustQty = dto.QTY; model.Remark = dto.REMARK; model.ReasonCode = dto.TRAN_SUB_CLS; //== Get Item Description. ItemBIZ biz = new ItemBIZ(); ItemDTO itemDTO = biz.LoadItem(dto.ITEM_CD); model.ItemDesc = itemDTO.SHORT_NAME; //-- Get Customer Name DealingBIZ bizCust = new DealingBIZ(); DealingDTO dtoCust = bizCust.LoadLocation(itemDTO.CUSTOMER_CD); model.CustomerName = dtoCust.LOC_DESC; //== Get OnHand. InventoryBIZ inventoryBIZ = new InventoryBIZ(); InventoryPeriodBIZ inventoryPeriodBIZ = new InventoryPeriodBIZ(); InventoryPeriodDTO inventoryPeriodDTO = inventoryPeriodBIZ.LoadCurrentPeriod(); InventoryOnhandDTO inventoryOnhandDTO = inventoryBIZ.LoadInventoryOnHandByDate(inventoryPeriodDTO.YEAR_MONTH, dto.TRANS_DATE, dto.ITEM_CD, dto.LOC_CD, dto.LOT_NO, dto.PACK_NO); if (inventoryOnhandDTO == null) { model.OnHandQty.Value = 0; } else { model.OnHandQty = inventoryOnhandDTO.ON_HAND_QTY; } return(model); }
private List <InventoryMovementInqUIDM> GenerateLines(InventoryOnhandDTO dtoInvOnHand, List <InventoryTransactionDTO> listData) { //== Generate line of inquiry item. List <InventoryMovementInqUIDM> listModel = new List <InventoryMovementInqUIDM>(); InventoryMovementInqUIDM model = null; // first line. model = new InventoryMovementInqUIDM(); if (dtoInvOnHand == null) { model.BALANCE.Value = 0; } else { model.BALANCE.Value = dtoInvOnHand.OPEN_QTY.Value; model.TRANS_DATE.Value = dtoInvOnHand.PERIOD_BEGIN_DATE.Value; } model.TRANS_INFO.Value = "Open Bal"; listModel.Add(model); decimal dOpenQty = model.BALANCE.NVL(0); // add each transaction line. for (int i = 0; i < listData.Count; i++) { InventoryTransactionDTO dto = listData[i]; model = new InventoryMovementInqUIDM(); model.TRANS_DATE = dto.TRANS_DATE; model.TRANS_ID = dto.TRANS_ID; model.TRANS_INFO = dto.TRANS_CLS; model.REF_SLIP_NO = dto.REF_SLIP_NO; model.REF_TYPE = dto.REF_SLIP_CLS; model.LOT_NO = dto.LOT_NO; model.IN_QTY.Value = 0; model.OUT_QTY.Value = 0; model.BALANCE.Value = 0; model.REF_NO = dto.REF_NO; model.REMARK = dto.REMARK; if (dto.IN_OUT_CLS.StrongValue == "01") { model.IN_QTY = dto.QTY; dOpenQty += dto.QTY.NVL(0); } else if (dto.IN_OUT_CLS.StrongValue == "02") { model.OUT_QTY = dto.QTY; dOpenQty -= dto.QTY.NVL(0); } else { model.NG_CRITERIA = dto.TRAN_SUB_CLS; model.NG_QTY = dto.QTY; } model.BALANCE.Value = dOpenQty; //calculate previous balance. listModel.Add(model); } return(listModel); }
/// <summary> /// Insert new record into database. /// </summary> /// <param name="database"></param> /// <param name="data"></param> /// <returns></returns> public int AddNew(Database database, InventoryOnhandDTO data) { Database db = UseDatabase(database); StringBuilder sb = new StringBuilder(); #region SQL Statement sb.AppendLine(" INSERT INTO " + data.TableName + "("); //sb.AppendLine(" " + InventoryOnhandDTO.eColumns.ID); sb.AppendLine(" " + InventoryOnhandDTO.eColumns.YEAR_MONTH); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_BEGIN_DATE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_END_DATE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ITEM_CD); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOC_CD); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOT_NO); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OPEN_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.IN_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OUT_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ADJUST_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.STOCK_TAKE_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ON_HAND_QTY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.CRT_BY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.CRT_DATE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.CRT_MACHINE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_BY); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_DATE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_MACHINE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LAST_RECEIVE_DATE); sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OLD_DATA); sb.AppendLine(") VALUES("); // sb.AppendLine(" :ID"); sb.AppendLine(" :YEAR_MONTH"); sb.AppendLine(" ,:PERIOD_BEGIN_DATE"); sb.AppendLine(" ,:PERIOD_END_DATE"); sb.AppendLine(" ,:ITEM_CD"); sb.AppendLine(" ,:LOC_CD"); sb.AppendLine(" ,:LOT_NO"); sb.AppendLine(" ,:OPEN_QTY"); sb.AppendLine(" ,:IN_QTY"); sb.AppendLine(" ,:OUT_QTY"); sb.AppendLine(" ,:ADJUST_QTY"); sb.AppendLine(" ,:STOCK_TAKE_QTY"); sb.AppendLine(" ,:ON_HAND_QTY"); sb.AppendLine(" ,:CRT_BY"); sb.AppendLine(" ,GETDATE()"); sb.AppendLine(" ,:CRT_MACHINE"); sb.AppendLine(" ,:UPD_BY"); sb.AppendLine(" ,GETDATE()"); sb.AppendLine(" ,:UPD_MACHINE"); sb.AppendLine(" ,:LAST_RECEIVE_DATE"); sb.AppendLine(" ,:OLD_DATA"); sb.AppendLine(" )"); #endregion DataRequest req = new DataRequest(sb.ToString()); #region Parameters //req.Parameters.Add("ID", DataType.Default, data.ID.Value); req.Parameters.Add("YEAR_MONTH", DataType.NVarChar, data.YEAR_MONTH.Value); req.Parameters.Add("PERIOD_BEGIN_DATE", DataType.DateTime, data.PERIOD_BEGIN_DATE.Value); req.Parameters.Add("PERIOD_END_DATE", DataType.DateTime, data.PERIOD_END_DATE.Value); req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value); req.Parameters.Add("LOC_CD", DataType.NVarChar, data.LOC_CD.Value); req.Parameters.Add("LOT_NO", DataType.NVarChar, data.LOT_NO.Value); req.Parameters.Add("OPEN_QTY", DataType.Number, data.OPEN_QTY.Value); req.Parameters.Add("IN_QTY", DataType.Number, data.IN_QTY.Value); req.Parameters.Add("OUT_QTY", DataType.Number, data.OUT_QTY.Value); req.Parameters.Add("ADJUST_QTY", DataType.Number, data.ADJUST_QTY.Value); req.Parameters.Add("STOCK_TAKE_QTY", DataType.Number, data.STOCK_TAKE_QTY.Value); req.Parameters.Add("ON_HAND_QTY", DataType.Number, data.ON_HAND_QTY.Value); req.Parameters.Add("CRT_BY", DataType.NVarChar, data.CRT_BY.Value); req.Parameters.Add("CRT_MACHINE", DataType.NVarChar, data.CRT_MACHINE.Value); req.Parameters.Add("UPD_BY", DataType.NVarChar, data.UPD_BY.Value); req.Parameters.Add("UPD_MACHINE", DataType.NVarChar, data.UPD_MACHINE.Value); req.Parameters.Add("LAST_RECEIVE_DATE", DataType.DateTime, data.LAST_RECEIVE_DATE.Value); req.Parameters.Add("OLD_DATA", DataType.Int16, 0); #endregion try { return(db.ExecuteNonQuery(req)); } catch (Exception err) { throw; } }