/// <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, RunningNumberDTO data) { Database db = UseDatabase(database); if (Exist(database, data.ID_NAME, data.TB_NAME)) { return(UpdateWithoutPK(db, data)); } return(AddNew(db, data)); }
/// <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="oldID_NAME">Old Key #1</param> /// <param name="oldTB_NAME">Old Key #2</param> /// <returns></returns> public int UpdateWithPK(Database database, RunningNumberDTO data, NZString oldID_NAME, NZString oldTB_NAME) { Database db = UseDatabase(database); StringBuilder sb = new StringBuilder(); #region SQL Statement sb.AppendLine(" UPDATE " + data.TableName); sb.AppendLine(" SET "); sb.AppendLine(" " + RunningNumberDTO.eColumns.ID_NAME + "=:ID_NAME"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.TB_NAME + "=:TB_NAME"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.DESCRIPTION + "=:DESCRIPTION"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.FORMAT + "=:FORMAT"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.NEXTVALUE + "=:NEXTVALUE"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.LAST_RESET + "=:LAST_RESET"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.RESET_FLAG_DAY + "=:RESET_FLAG_DAY"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.RESET_FLAG_MONTH + "=:RESET_FLAG_MONTH"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.RESET_FLAG_YEAR + "=:RESET_FLAG_YEAR"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.UPD_BY + "=:UPD_BY"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.UPD_DATE + "=GETDATE()"); sb.AppendLine(" ," + RunningNumberDTO.eColumns.UPD_MACHINE + "=:UPD_MACHINE"); sb.AppendLine(" WHERE "); sb.AppendLine(" " + RunningNumberDTO.eColumns.ID_NAME + "=:oldID_NAME"); sb.AppendLine(" AND " + RunningNumberDTO.eColumns.TB_NAME + "=:oldRunningNumber"); #endregion DataRequest req = new DataRequest(sb.ToString()); #region Parameters req.Parameters.Add("ID_NAME", DataType.VarChar, data.ID_NAME.Value); req.Parameters.Add("TB_NAME", DataType.VarChar, data.TB_NAME.Value); req.Parameters.Add("DESCRIPTION", DataType.VarChar, data.DESCRIPTION.Value); req.Parameters.Add("FORMAT", DataType.VarChar, data.FORMAT.Value); req.Parameters.Add("NEXTVALUE", DataType.Number, data.NEXTVALUE.Value); req.Parameters.Add("LAST_RESET", DataType.DateTime, data.LAST_RESET.Value); req.Parameters.Add("RESET_FLAG_DAY", DataType.Default, data.RESET_FLAG_DAY.Value); req.Parameters.Add("RESET_FLAG_MONTH", DataType.Default, data.RESET_FLAG_MONTH.Value); req.Parameters.Add("RESET_FLAG_YEAR", DataType.Default, data.RESET_FLAG_YEAR.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("oldID_NAME", DataType.VarChar, oldID_NAME.Value); req.Parameters.Add("oldTB_NAME", DataType.VarChar, oldTB_NAME.Value); #endregion return(db.ExecuteNonQuery(req)); }
/// <summary> /// Get Complete Running No /// </summary> /// <param name="IDName"></param> /// <param name="TBName"></param> /// <returns></returns> public NZString GetCompleteRunningNo(NZString IDName, NZString TBName) { NZString CompleteRunning = new NZString(); // CommonLib.Common.CurrentDatabase.KeepConnection = true; // CommonLib.Common.CurrentDatabase.BeginTransaction(IsolationLevel.Serializable); //try //{ if (IDName.IsNull || TBName.IsNull) { return(CompleteRunning); } // get data first RunningNumberDAO dao = new RunningNumberDAO(CommonLib.Common.CurrentDatabase); RunningNumberDTO dto = dao.LoadByPK_UPDLock(null, IDName, TBName); if (dto == null) { return(CompleteRunning); } // if load success then 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 )) //{ // daoTrans.SelectWithKeys(null, key1, key2); //} //else //{ // 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); // daoTrans.SelectWithKeys(null, key1, key2); //} string FormatString = dto.FORMAT.StrongValue; int RunningLenght = FormatString.LastIndexOf("X") - FormatString.IndexOf("X") + 1; string RunningText = FormatString.Substring(FormatString.IndexOf("X"), RunningLenght); NZDecimal NextRunning = dto.NEXTVALUE; NZDateTime LastReset = dto.LAST_RESET; NZString ResetFlagByDay = dto.RESET_FLAG_DAY; NZString ResetFlagByMonth = dto.RESET_FLAG_MONTH; NZString ResetFlagByYear = dto.RESET_FLAG_YEAR; if (!ResetFlagByDay.IsNull && ResetFlagByDay.StrongValue.ToUpper() == "Y") { if (!LastReset.IsNull && LastReset.StrongValue.Date != DateTime.Now.Date) { NextRunning.Value = 1; dto.LAST_RESET.Value = DateTime.Now; } } else if (!ResetFlagByMonth.IsNull && ResetFlagByMonth.StrongValue.ToUpper() == "Y") { if (!LastReset.IsNull && LastReset.StrongValue.Month != DateTime.Now.Month) { NextRunning.Value = 1; dto.LAST_RESET.Value = DateTime.Now; } } else if (!ResetFlagByYear.IsNull && ResetFlagByYear.StrongValue.ToUpper() == "Y") { if (!LastReset.IsNull && LastReset.StrongValue.Year != DateTime.Now.Year) { NextRunning.Value = 1; dto.LAST_RESET.Value = DateTime.Now; } } string RunningFormat = "{0:"; for (int i = 0; i < RunningLenght; i++) { RunningFormat += "0"; } RunningFormat += "}"; string CompleteRunningNo = string.Format(RunningFormat, NextRunning.StrongValue); // replace DD if (FormatString.IndexOf("DD") != -1) { FormatString = FormatString.Replace("DD", DateTime.Now.ToString("dd")); } // replace MM if (FormatString.IndexOf("MM") != -1) { FormatString = FormatString.Replace("MM", DateTime.Now.ToString("MM")); } // replace YY if (FormatString.IndexOf("YYYY") != -1) { FormatString = FormatString.Replace("YYYY", DateTime.Now.ToString("yyyy")); } if (FormatString.IndexOf("YY") != -1) { FormatString = FormatString.Replace("YY", DateTime.Now.ToString("yy")); } CompleteRunning.Value = FormatString.Replace(RunningText, CompleteRunningNo); // after get running number completet then step up // next running value dto.NEXTVALUE.Value = dto.NEXTVALUE.StrongValue + 1; dto.UPD_BY = CommonLib.Common.CurrentUserInfomation.UserCD; dto.UPD_DATE.Value = DateTime.Now; dto.UPD_MACHINE = CommonLib.Common.CurrentUserInfomation.Machine; dao.SetNextRunningNoValue(null, dto); // daoTrans.DeleteWithKeys(null, key1, key2); // } return(CompleteRunning); }
/// <summary> /// Insert new record into database. /// </summary> /// <param name="database"></param> /// <param name="data"></param> /// <returns></returns> public int AddNew(Database database, RunningNumberDTO data) { Database db = UseDatabase(database); StringBuilder sb = new StringBuilder(); #region SQL Statement sb.AppendLine(" INSERT INTO " + data.TableName + "("); sb.AppendLine(" " + RunningNumberDTO.eColumns.ID_NAME); sb.AppendLine(" ," + RunningNumberDTO.eColumns.TB_NAME); sb.AppendLine(" ," + RunningNumberDTO.eColumns.DESCRIPTION); sb.AppendLine(" ," + RunningNumberDTO.eColumns.FORMAT); sb.AppendLine(" ," + RunningNumberDTO.eColumns.NEXTVALUE); sb.AppendLine(" ," + RunningNumberDTO.eColumns.LAST_RESET); sb.AppendLine(" ," + RunningNumberDTO.eColumns.RESET_FLAG_DAY); sb.AppendLine(" ," + RunningNumberDTO.eColumns.RESET_FLAG_MONTH); sb.AppendLine(" ," + RunningNumberDTO.eColumns.RESET_FLAG_YEAR); sb.AppendLine(" ," + RunningNumberDTO.eColumns.CRT_BY); sb.AppendLine(" ," + RunningNumberDTO.eColumns.CRT_DATE); sb.AppendLine(" ," + RunningNumberDTO.eColumns.CRT_MACHINE); sb.AppendLine(" ," + RunningNumberDTO.eColumns.UPD_BY); sb.AppendLine(" ," + RunningNumberDTO.eColumns.UPD_DATE); sb.AppendLine(" ," + RunningNumberDTO.eColumns.UPD_MACHINE); sb.AppendLine(") VALUES("); sb.AppendLine(" :ID_NAME"); sb.AppendLine(" ,:TB_NAME"); sb.AppendLine(" ,:DESCRIPTION"); sb.AppendLine(" ,:FORMAT"); sb.AppendLine(" ,:NEXTVALUE"); sb.AppendLine(" ,:LAST_RESET"); sb.AppendLine(" ,:RESET_FLAG_DAY"); sb.AppendLine(" ,:RESET_FLAG_MONTH"); sb.AppendLine(" ,:RESET_FLAG_YEAR"); sb.AppendLine(" ,:CRT_BY"); sb.AppendLine(" ,GETDATE()"); sb.AppendLine(" ,:CRT_MACHINE"); sb.AppendLine(" ,:UPD_BY"); sb.AppendLine(" ,GETDATE()"); sb.AppendLine(" ,:UPD_MACHINE"); sb.AppendLine(" )"); #endregion DataRequest req = new DataRequest(sb.ToString()); #region Parameters req.Parameters.Add("ID_NAME", DataType.VarChar, data.ID_NAME.Value); req.Parameters.Add("TB_NAME", DataType.VarChar, data.TB_NAME.Value); req.Parameters.Add("DESCRIPTION", DataType.VarChar, data.DESCRIPTION.Value); req.Parameters.Add("FORMAT", DataType.VarChar, data.FORMAT.Value); req.Parameters.Add("NEXTVALUE", DataType.Number, data.NEXTVALUE.Value); req.Parameters.Add("LAST_RESET", DataType.DateTime, data.LAST_RESET.Value); req.Parameters.Add("RESET_FLAG_DAY", DataType.Default, data.RESET_FLAG_DAY.Value); req.Parameters.Add("RESET_FLAG_MONTH", DataType.Default, data.RESET_FLAG_MONTH.Value); req.Parameters.Add("RESET_FLAG_YEAR", DataType.Default, data.RESET_FLAG_YEAR.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); #endregion return(db.ExecuteNonQuery(req)); }