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