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