/// <summary> /// Update record by using the table's primary key. /// </summary> /// <param name="database"></param> /// <param name="data">Data which to update.</param> /// <param name="oldKEY1">Old Key #1</param> /// <param name="oldKEY2">Old Key #2</param> /// <returns></returns> public int UpdateWithPK(Database database, TransactionLockDTO data, NZString oldKEY1, NZString oldKEY2) { Database db = UseDatabase(database); StringBuilder sb = new StringBuilder(); #region SQL Statement sb.AppendLine(" UPDATE " + data.TableName); sb.AppendLine(" SET "); sb.AppendLine(" " + TransactionLockDTO.eColumns.KEY1 + "=:KEY1"); sb.AppendLine(" ," + TransactionLockDTO.eColumns.KEY2 + "=:KEY2"); sb.AppendLine(" ," + TransactionLockDTO.eColumns.KEY3 + "=:KEY3"); sb.AppendLine(" ," + TransactionLockDTO.eColumns.KEY4 + "=:KEY4"); sb.AppendLine(" ," + TransactionLockDTO.eColumns.KEY5 + "=:KEY5"); sb.AppendLine(" WHERE "); sb.AppendLine(" " + TransactionLockDTO.eColumns.KEY1 + "=:oldKEY1"); sb.AppendLine(" AND " + TransactionLockDTO.eColumns.KEY2 + "=:oldTransactionLock"); #endregion DataRequest req = new DataRequest(sb.ToString()); #region Parameters req.Parameters.Add("KEY1", DataType.NVarChar, data.KEY1.Value); req.Parameters.Add("KEY2", DataType.NVarChar, data.KEY2.Value); req.Parameters.Add("KEY3", DataType.NVarChar, data.KEY3.Value); req.Parameters.Add("KEY4", DataType.NVarChar, data.KEY4.Value); req.Parameters.Add("KEY5", DataType.NVarChar, data.KEY5.Value); req.Parameters.Add("oldKEY1", DataType.NVarChar, oldKEY1.Value); req.Parameters.Add("oldKEY2", DataType.NVarChar, oldKEY2.Value); #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, TransactionLockDTO data) { Database db = UseDatabase(database); if (Exist(database, data.KEY1, data.KEY2)) { return(UpdateWithoutPK(db, data)); } return(AddNew(db, data)); }
public NZString GeneratePackNo() { CommonLib.Common.CurrentDatabase.KeepConnection = true; CommonLib.Common.CurrentDatabase.BeginTransaction(IsolationLevel.Serializable); NZString PackNo = new NZString(); try { #region Lock Running Number gen. TransactionLockDAO daoTrans = new TransactionLockDAO(CommonLib.Common.CurrentDatabase); TransactionLockDTO dtoTrans = new TransactionLockDTO(); NZString key1 = new NZString(null, "PACK_NO_AUTO_GEN"); NZString key2 = new NZString(null, "INV_TRANS_TR"); if (!daoTrans.Exist(null, key1, key2)) { dtoTrans.KEY1 = key1; dtoTrans.KEY2 = key2; dtoTrans.CRT_BY = CommonLib.Common.CurrentUserInfomation.UserCD; dtoTrans.CRT_DATE.Value = DateTime.Now; dtoTrans.CRT_MACHINE = CommonLib.Common.CurrentUserInfomation.Machine; daoTrans.AddNew(null, dtoTrans); } // lock transaction daoTrans.SelectWithKeys(null, key1, key2); // end of lock transaction #endregion List<InventoryTransactionDTO> dtoList = new List<InventoryTransactionDTO>(); RunningNumberBIZ bizRunning = new RunningNumberBIZ(); PackNo = bizRunning.GetCompleteRunningNo(new NZString(null, "PACK_NO_AUTO_GEN"), new NZString(null, "TB_INV_TRANS_TR")); daoTrans.DeleteWithKeys(null, key1, key2); CommonLib.Common.CurrentDatabase.Commit(); } catch (BusinessException) { CommonLib.Common.CurrentDatabase.Rollback(); throw; } catch (Exception) { CommonLib.Common.CurrentDatabase.Rollback(); throw; } return PackNo; }
/// <summary> /// Insert new record into database. /// </summary> /// <param name="database"></param> /// <param name="data"></param> /// <returns></returns> public int AddNew(Database database, TransactionLockDTO data) { Database db = UseDatabase(database); StringBuilder sb = new StringBuilder(); #region SQL Statement sb.AppendLine(" INSERT INTO " + data.TableName + "("); sb.AppendLine(" " + TransactionLockDTO.eColumns.KEY1); sb.AppendLine(" ," + TransactionLockDTO.eColumns.KEY2); sb.AppendLine(" ," + TransactionLockDTO.eColumns.KEY3); sb.AppendLine(" ," + TransactionLockDTO.eColumns.KEY4); sb.AppendLine(" ," + TransactionLockDTO.eColumns.KEY5); sb.AppendLine(" ," + TransactionLockDTO.eColumns.CRT_BY); sb.AppendLine(" ," + TransactionLockDTO.eColumns.CRT_DATE); sb.AppendLine(" ," + TransactionLockDTO.eColumns.CRT_MACHINE); sb.AppendLine(") VALUES("); sb.AppendLine(" :KEY1"); sb.AppendLine(" ,:KEY2"); sb.AppendLine(" ,:KEY3"); sb.AppendLine(" ,:KEY4"); sb.AppendLine(" ,:KEY5"); sb.AppendLine(" ,:CRT_BY"); sb.AppendLine(" ,GETDATE()"); sb.AppendLine(" ,:CRT_MACHINE"); sb.AppendLine(" )"); #endregion DataRequest req = new DataRequest(sb.ToString()); #region Parameters req.Parameters.Add("KEY1", DataType.NVarChar, data.KEY1.Value); req.Parameters.Add("KEY2", DataType.NVarChar, data.KEY2.Value); req.Parameters.Add("KEY3", DataType.NVarChar, data.KEY3.Value); req.Parameters.Add("KEY4", DataType.NVarChar, data.KEY4.Value); req.Parameters.Add("KEY5", DataType.NVarChar, data.KEY5.Value); req.Parameters.Add("CRT_BY", DataType.NVarChar, data.CRT_BY.Value); req.Parameters.Add("CRT_MACHINE", DataType.NVarChar, data.CRT_MACHINE.Value); #endregion return(db.ExecuteNonQuery(req)); }
/// <summary> /// Save new data. /// </summary> /// <param name="model"></param> public void SaveAdd(AdjustmentEntryUIDM model) { try { CommonLib.Common.CurrentDatabase.KeepConnection = true; CommonLib.Common.CurrentDatabase.BeginTransaction(System.Data.IsolationLevel.Serializable); AdjustmentValidator adjustmentValidator = new AdjustmentValidator(); ItemValidator itemValidator = new ItemValidator(); DealingValidator locationValidator = new DealingValidator(); TransactionValidator valTran = new TransactionValidator(); CommonBizValidator commonVal = new CommonBizValidator(); //ValidateException.ThrowErrorItem(adjustmentValidator.CheckEmptyAdjustDate(model.AdjustDate)); //ValidateException.ThrowErrorItem(adjustmentValidator.CheckEmptyReasonCode(model.ReasonCode)); //ValidateException.ThrowErrorItem(valTran.DateIsInCurrentPeriod(model.AdjustDate)); //ValidateException.ThrowErrorItem(itemValidator.CheckEmptyItemCode(model.ItemCode)); BusinessException businessException = itemValidator.CheckItemNotExist(model.ItemCode); if (businessException != null) { ValidateException.ThrowErrorItem(businessException.Error); } //ValidateException.ThrowErrorItem(locationValidator.CheckEmptyLocationCode(model.StoredLoc)); ValidateException.ThrowErrorItem(locationValidator.CheckNotExistsLocationCode(model.StoredLoc)); ValidateException.ThrowErrorItem(commonVal.CheckInputLot(model.ItemCode, new NZString(), model.LotNo, false)); //ValidateException.ThrowErrorItem(adjustmentValidator.CheckEmptyAdjustQty(model.AdjustQty)); //ValidateException.ThrowErrorItem(adjustmentValidator.CheckIsZeroAdjustQty(model.AdjustQty)); #region Lock Running Number gen. TransactionLockDAO daoTrans = new TransactionLockDAO(CommonLib.Common.CurrentDatabase); TransactionLockDTO dtoTrans = new TransactionLockDTO(); NZString key1 = new NZString(null, "ADJUST_NO"); NZString key2 = new NZString(null, "INV_TRANS_TR"); if (!daoTrans.Exist(null, key1, key2)) { dtoTrans.KEY1 = key1; dtoTrans.KEY2 = key2; dtoTrans.CRT_BY = CommonLib.Common.CurrentUserInfomation.UserCD; dtoTrans.CRT_DATE.Value = DateTime.Now; dtoTrans.CRT_MACHINE = CommonLib.Common.CurrentUserInfomation.Machine; daoTrans.AddNew(null, dtoTrans); } // lock transaction daoTrans.SelectWithKeys(null, key1, key2); // end of lock transaction #endregion RunningNumberBIZ bizRunning = new RunningNumberBIZ(); NZString SlipNo = bizRunning.GetCompleteRunningNo(new NZString(null, "ADJUST_NO"), new NZString(null, "TB_INV_TRANS_TR")); model.AdjustNo = SlipNo; NZString GroupTransID = bizRunning.GetCompleteRunningNo(new NZString(null, "TRAN_GROUP_ID"), new NZString(null, "TB_INV_TRANS_TR")); model.GroupTransID = GroupTransID; #region Generate pack_no if it is empty DealingConstraintBIZ bizConstraint = new DealingConstraintBIZ(); string strProcess = model.StoredLoc.StrongValue; DealingConstraintDTO constriant = bizConstraint.LoadDealingConstraint(strProcess.ToNZString()); if (constriant != null && constriant.ENABLE_PACK_FLAG.StrongValue == 1 && model.PackNo.IsNull) { NZString PackNo = bizRunning.GetCompleteRunningNo(new NZString(null, "PACK_NO_AUTO_GEN"), new NZString(null, "TB_INV_TRANS_TR")); model.PackNo = PackNo; } #endregion InventoryBIZ inventoryBIZ = new InventoryBIZ(); InventoryTransactionDTO dto = ConvertUIDMToDTO(model); inventoryBIZ.AddInventoryTransaction(CommonLib.Common.CurrentDatabase, dto, true); CommonLib.Common.CurrentDatabase.Commit(); } catch (System.Exception) { CommonLib.Common.CurrentDatabase.Rollback(); throw; } }
public int AddIssueConsumption(List <InventoryTransactionDTO> dtoInvTrnsList) { try { #region Validate Mandatory IssueEntryValidator valIssue = new IssueEntryValidator(); InventoryOnhandValidator valINV = new InventoryOnhandValidator(); ErrorItem errorItem = null; if (dtoInvTrnsList.Count > 0) { errorItem = valIssue.CheckIssueDate(dtoInvTrnsList[0].TRANS_DATE); if (null != errorItem) { ValidateException.ThrowErrorItem(errorItem); } } // CHECK EXIST INVENTORY ONHAND //for (int i = 0; i < dtoInvTrnsList.Count; i++) //{ // if (dtoInvTrnsList[i].IN_OUT_CLS.StrongValue == DataDefine.Convert2ClassCode(DataDefine.eIN_OUT_CLASS.Out)) // { // // NZString YearMonth = new NZString(null, dtoInvTrnsList[i].TRANS_DATE.StrongValue.ToString("yyyyMM")); // errorItem = valINV.CheckOnhandQty(dtoInvTrnsList[i].IN_OUT_CLS.StrongValue, dtoInvTrnsList[i].QTY, dtoInvTrnsList[i].ITEM_CD, // dtoInvTrnsList[i].LOC_CD, dtoInvTrnsList[i].LOT_NO); // if (null != errorItem) ValidateException.ThrowErrorItem(errorItem); // } //} #endregion // lock transaction TransactionLockDAO daoTrans = new TransactionLockDAO(CommonLib.Common.CurrentDatabase); TransactionLockDTO dtoTrans = new TransactionLockDTO(); NZString key1 = new NZString(null, "ISSUE_TRANS_ID"); NZString key2 = new NZString(null, "INV_TRANS_TR"); if (!daoTrans.Exist(null, key1, key2)) { dtoTrans.KEY1 = key1; dtoTrans.KEY2 = key2; dtoTrans.CRT_BY = CommonLib.Common.CurrentUserInfomation.UserCD; dtoTrans.CRT_DATE.Value = DateTime.Now; dtoTrans.CRT_MACHINE = CommonLib.Common.CurrentUserInfomation.Machine; daoTrans.AddNew(null, dtoTrans); } // end of lock transaction InventoryTransactionDAO InventoryTransaction = new InventoryTransactionDAO(CommonLib.Common.CurrentDatabase); RunningNumberBIZ bizRunning = new RunningNumberBIZ(); NZString TransID = bizRunning.GetCompleteRunningNo(new NZString(null, "TRAN_ID"), new NZString(null, "TB_INV_TRANS_TR")); NZString RefID = bizRunning.GetCompleteRunningNo(new NZString(null, "TRAN_ID"), new NZString(null, "TB_INV_TRANS_TR")); NZString ConsumptionID = bizRunning.GetCompleteRunningNo(new NZString(null, "TRAN_ID"), new NZString(null, "TB_INV_TRANS_TR")); // if load success then lock transaction daoTrans.SelectWithKeys(null, key1, key2); // end of lock transaction dtoInvTrnsList[0].TRANS_ID = TransID; dtoInvTrnsList[0].REF_NO = RefID; dtoInvTrnsList[1].TRANS_ID = RefID; dtoInvTrnsList[1].REF_NO = TransID; dtoInvTrnsList[2].TRANS_ID = ConsumptionID; dtoInvTrnsList[2].REF_NO = RefID; for (int i = 0; i < dtoInvTrnsList.Count; i++) { InventoryTransaction.AddNew(null, dtoInvTrnsList[i]); } daoTrans.DeleteWithKeys(null, key1, key2); CommonLib.Common.CurrentDatabase.Commit(); } catch (ValidateException) { CommonLib.Common.CurrentDatabase.Rollback(); throw; } catch (BusinessException) { CommonLib.Common.CurrentDatabase.Rollback(); throw; } catch (Exception) { CommonLib.Common.CurrentDatabase.Rollback(); throw; } finally { if (CommonLib.Common.CurrentDatabase.DBConnectionState == ConnectionState.Open) { CommonLib.Common.CurrentDatabase.Close(); } } return(1); }