/// <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, MRPHDTO data)
{
Database db = UseDatabase(database);
if (Exist(database, data.MRP_NO, data.ITEM_CD, data.ORDER_LOC_CD))
{
return(UpdateWithoutPK(db, data));
}
return(AddNew(db, data));
}
public void UpdateBalanceStock(Database database, MRPHDTO dto)
{
Database db = UseDatabase(database);
DataRequest req = new DataRequest();
req.CommandText = "S_MRP020_UPDATE_BALANCE_ITEM";
req.CommandType = CommandType.StoredProcedure;
req.Parameters.Add("@pMRP_NO", DataType.VarChar, dto.MRP_NO.Value);
req.Parameters.Add("@pITEM_CD", DataType.VarChar, dto.ITEM_CD.Value);
req.Parameters.Add("@pORDER_LOC_CD", DataType.VarChar, dto.ORDER_LOC_CD.Value);
//req.Parameters.Add("@pVar_UPD_BY", DataType.VarChar, dto.UPD_BY.Value);
//req.Parameters.Add("@pVar_UPD_MACHINE", DataType.VarChar, dto.UPD_MACHINE.Value);
db.ExecuteNonQuery(req);
}
public void SaveData(MRPHDTO dtoHeader, List <MRPDDTO> dtoInsert, List <MRPDDTO> dtoUpdate, List <MRPDDTO> dtoDelete)
{
try {
CheckBeforeSave();
Common.CurrentDatabase.KeepConnection = true;
Common.CurrentDatabase.BeginTransaction(IsolationLevel.Serializable);
//Update Header
MRPHDAO daoMRPH = new MRPHDAO(Common.CurrentDatabase);
daoMRPH.UpdateMRPRevision(null, dtoHeader);
MRPDDAO daoMRPD = new MRPDDAO(Common.CurrentDatabase);
if (dtoDelete != null && dtoDelete.Count > 0)
{
foreach (MRPDDTO dto in dtoDelete)
{
dto.CRT_BY = Common.CurrentUserInfomation.UserCD;
dto.CRT_DATE = DateTime.Now.ToNZDateTime();
dto.CRT_MACHINE = Common.CurrentUserInfomation.Machine;
dto.UPD_BY = Common.CurrentUserInfomation.UserCD;
dto.UPD_DATE = DateTime.Now.ToNZDateTime();
dto.UPD_MACHINE = Common.CurrentUserInfomation.Machine;
daoMRPD.Delete(null, dto.MRP_NO, dto.ITEM_CD, dto.ORDER_LOC_CD, dto.AT_DATE);
}
}
if (dtoInsert != null && dtoInsert.Count > 0)
{
foreach (MRPDDTO dto in dtoInsert)
{
dto.CRT_BY = Common.CurrentUserInfomation.UserCD;
dto.CRT_DATE = DateTime.Now.ToNZDateTime();
dto.CRT_MACHINE = Common.CurrentUserInfomation.Machine;
dto.UPD_BY = Common.CurrentUserInfomation.UserCD;
dto.UPD_DATE = DateTime.Now.ToNZDateTime();
dto.UPD_MACHINE = Common.CurrentUserInfomation.Machine;
daoMRPD.AddNewOrUpdate(null, dto);
}
}
if (dtoUpdate != null && dtoUpdate.Count > 0)
{
foreach (MRPDDTO dto in dtoUpdate)
{
dto.CRT_BY = Common.CurrentUserInfomation.UserCD;
dto.CRT_DATE = DateTime.Now.ToNZDateTime();
dto.CRT_MACHINE = Common.CurrentUserInfomation.Machine;
dto.UPD_BY = Common.CurrentUserInfomation.UserCD;
dto.UPD_DATE = DateTime.Now.ToNZDateTime();
dto.UPD_MACHINE = Common.CurrentUserInfomation.Machine;
daoMRPD.AddNewOrUpdate(null, dto);
}
}
//Call Update Balance Stock
daoMRPD.UpdateBalanceStock(null, dtoHeader);
Common.CurrentDatabase.Commit();
}
catch (Exception) {
Common.CurrentDatabase.Rollback();
throw;
}
}
/// <summary>
/// Insert new record into database.
/// </summary>
/// <param name="database"></param>
/// <param name="data"></param>
/// <returns></returns>
public int AddNew(Database database, MRPHDTO data)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" INSERT INTO " + data.TableName + "(");
sb.AppendLine(" " + MRPHDTO.eColumns.CRT_BY);
sb.AppendLine(" ," + MRPHDTO.eColumns.CRT_DATE);
sb.AppendLine(" ," + MRPHDTO.eColumns.CRT_MACHINE);
sb.AppendLine(" ," + MRPHDTO.eColumns.UPD_BY);
sb.AppendLine(" ," + MRPHDTO.eColumns.UPD_DATE);
sb.AppendLine(" ," + MRPHDTO.eColumns.UPD_MACHINE);
sb.AppendLine(" ," + MRPHDTO.eColumns.IS_ACTIVE);
sb.AppendLine(" ," + MRPHDTO.eColumns.MRP_NO);
sb.AppendLine(" ," + MRPHDTO.eColumns.ITEM_CD);
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_LOC_CD);
sb.AppendLine(" ," + MRPHDTO.eColumns.REVISION_NO);
sb.AppendLine(" ," + MRPHDTO.eColumns.ITEM_CLS);
sb.AppendLine(" ," + MRPHDTO.eColumns.ITEM_DESC);
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_PROCESS_CLS);
sb.AppendLine(" ," + MRPHDTO.eColumns.LOT_SIZE);
sb.AppendLine(" ," + MRPHDTO.eColumns.REORDER_POINT);
sb.AppendLine(" ," + MRPHDTO.eColumns.SAFETY_STOCK);
sb.AppendLine(" ," + MRPHDTO.eColumns.MINIMUM_ORDER);
sb.AppendLine(" ," + MRPHDTO.eColumns.INV_UM_CLS);
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_UM_CLS);
sb.AppendLine(" ," + MRPHDTO.eColumns.INV_UM_RATE);
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_UM_RATE);
sb.AppendLine(" ," + MRPHDTO.eColumns.MAX_CAPACITY);
sb.AppendLine(" ," + MRPHDTO.eColumns.LEADTIME);
sb.AppendLine(" ," + MRPHDTO.eColumns.SAFETY_LEADTIME);
sb.AppendLine(" ," + MRPHDTO.eColumns.MRP_FLAG);
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_CONDITION);
sb.AppendLine(") VALUES(");
sb.AppendLine(" :CRT_BY");
sb.AppendLine(" ,GETDATE()");
sb.AppendLine(" ,:CRT_MACHINE");
sb.AppendLine(" ,:UPD_BY");
sb.AppendLine(" ,GETDATE()");
sb.AppendLine(" ,:UPD_MACHINE");
sb.AppendLine(" ,:IS_ACTIVE");
sb.AppendLine(" ,:MRP_NO");
sb.AppendLine(" ,:ITEM_CD");
sb.AppendLine(" ,:ORDER_LOC_CD");
sb.AppendLine(" ,:REVISION_NO");
sb.AppendLine(" ,:ITEM_CLS");
sb.AppendLine(" ,:ITEM_DESC");
sb.AppendLine(" ,:ORDER_PROCESS_CLS");
sb.AppendLine(" ,:LOT_SIZE");
sb.AppendLine(" ,:REORDER_POINT");
sb.AppendLine(" ,:SAFETY_STOCK");
sb.AppendLine(" ,:MINIMUM_ORDER");
sb.AppendLine(" ,:INV_UM_CLS");
sb.AppendLine(" ,:ORDER_UM_CLS");
sb.AppendLine(" ,:INV_UM_RATE");
sb.AppendLine(" ,:ORDER_UM_RATE");
sb.AppendLine(" ,:MAX_CAPACITY");
sb.AppendLine(" ,:LEADTIME");
sb.AppendLine(" ,:SAFETY_LEADTIME");
sb.AppendLine(" ,:MRP_FLAG");
sb.AppendLine(" ,:ORDER_CONDITION");
sb.AppendLine(" )");
#endregion
DataRequest req = new DataRequest(sb.ToString());
#region Parameters
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("IS_ACTIVE", DataType.Number, data.IS_ACTIVE.Value);
req.Parameters.Add("MRP_NO", DataType.NVarChar, data.MRP_NO.Value);
req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("ORDER_LOC_CD", DataType.NVarChar, data.ORDER_LOC_CD.Value);
req.Parameters.Add("REVISION_NO", DataType.Number, data.REVISION_NO.Value);
req.Parameters.Add("ITEM_CLS", DataType.NVarChar, data.ITEM_CLS.Value);
req.Parameters.Add("ITEM_DESC", DataType.NVarChar, data.ITEM_DESC.Value);
req.Parameters.Add("ORDER_PROCESS_CLS", DataType.NVarChar, data.ORDER_PROCESS_CLS.Value);
req.Parameters.Add("LOT_SIZE", DataType.Number, data.LOT_SIZE.Value);
req.Parameters.Add("REORDER_POINT", DataType.Number, data.REORDER_POINT.Value);
req.Parameters.Add("SAFETY_STOCK", DataType.Number, data.SAFETY_STOCK.Value);
req.Parameters.Add("MINIMUM_ORDER", DataType.Number, data.MINIMUM_ORDER.Value);
req.Parameters.Add("INV_UM_CLS", DataType.NVarChar, data.INV_UM_CLS.Value);
req.Parameters.Add("ORDER_UM_CLS", DataType.NVarChar, data.ORDER_UM_CLS.Value);
req.Parameters.Add("INV_UM_RATE", DataType.Number, data.INV_UM_RATE.Value);
req.Parameters.Add("ORDER_UM_RATE", DataType.Number, data.ORDER_UM_RATE.Value);
req.Parameters.Add("MAX_CAPACITY", DataType.Number, data.MAX_CAPACITY.Value);
req.Parameters.Add("LEADTIME", DataType.Number, data.LEADTIME.Value);
req.Parameters.Add("SAFETY_LEADTIME", DataType.Number, data.SAFETY_LEADTIME.Value);
req.Parameters.Add("MRP_FLAG", DataType.NVarChar, data.MRP_FLAG.Value);
req.Parameters.Add("ORDER_CONDITION", DataType.NVarChar, data.ORDER_CONDITION.Value);
#endregion
return(db.ExecuteNonQuery(req));
}
/// <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="oldMRP_NO">Old Key #1</param>
/// <param name="oldITEM_CD">Old Key #2</param>
/// <param name="oldORDER_LOC_CD">Old Key #3</param>
/// <returns></returns>
public int UpdateWithPK(Database database, MRPHDTO data, String oldMRP_NO, String oldITEM_CD, String oldORDER_LOC_CD)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" UPDATE " + data.TableName);
sb.AppendLine(" SET ");
sb.AppendLine(" " + MRPHDTO.eColumns.UPD_BY + "=:UPD_BY");
sb.AppendLine(" ," + MRPHDTO.eColumns.UPD_DATE + "=GETDATE()");
sb.AppendLine(" ," + MRPHDTO.eColumns.UPD_MACHINE + "=:UPD_MACHINE");
sb.AppendLine(" ," + MRPHDTO.eColumns.IS_ACTIVE + "=:IS_ACTIVE");
sb.AppendLine(" ," + MRPHDTO.eColumns.MRP_NO + "=:MRP_NO");
sb.AppendLine(" ," + MRPHDTO.eColumns.ITEM_CD + "=:ITEM_CD");
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_LOC_CD + "=:ORDER_LOC_CD");
sb.AppendLine(" ," + MRPHDTO.eColumns.REVISION_NO + "=:REVISION_NO");
sb.AppendLine(" ," + MRPHDTO.eColumns.ITEM_CLS + "=:ITEM_CLS");
sb.AppendLine(" ," + MRPHDTO.eColumns.ITEM_DESC + "=:ITEM_DESC");
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_PROCESS_CLS + "=:ORDER_PROCESS_CLS");
sb.AppendLine(" ," + MRPHDTO.eColumns.LOT_SIZE + "=:LOT_SIZE");
sb.AppendLine(" ," + MRPHDTO.eColumns.REORDER_POINT + "=:REORDER_POINT");
sb.AppendLine(" ," + MRPHDTO.eColumns.SAFETY_STOCK + "=:SAFETY_STOCK");
sb.AppendLine(" ," + MRPHDTO.eColumns.MINIMUM_ORDER + "=:MINIMUM_ORDER");
sb.AppendLine(" ," + MRPHDTO.eColumns.INV_UM_CLS + "=:INV_UM_CLS");
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_UM_CLS + "=:ORDER_UM_CLS");
sb.AppendLine(" ," + MRPHDTO.eColumns.INV_UM_RATE + "=:INV_UM_RATE");
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_UM_RATE + "=:ORDER_UM_RATE");
sb.AppendLine(" ," + MRPHDTO.eColumns.MAX_CAPACITY + "=:MAX_CAPACITY");
sb.AppendLine(" ," + MRPHDTO.eColumns.LEADTIME + "=:LEADTIME");
sb.AppendLine(" ," + MRPHDTO.eColumns.SAFETY_LEADTIME + "=:SAFETY_LEADTIME");
sb.AppendLine(" ," + MRPHDTO.eColumns.MRP_FLAG + "=:MRP_FLAG");
sb.AppendLine(" ," + MRPHDTO.eColumns.ORDER_CONDITION + "=:ORDER_CONDITION");
sb.AppendLine(" WHERE ");
sb.AppendLine(" " + MRPHDTO.eColumns.MRP_NO + "=:oldMRP_NO");
sb.AppendLine(" AND " + MRPHDTO.eColumns.ITEM_CD + "=:oldMRPH");
sb.AppendLine(" AND " + MRPHDTO.eColumns.ORDER_LOC_CD + "=:oldMRPH");
#endregion
DataRequest req = new DataRequest(sb.ToString());
#region Parameters
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("IS_ACTIVE", DataType.Number, data.IS_ACTIVE.Value);
req.Parameters.Add("MRP_NO", DataType.NVarChar, data.MRP_NO.Value);
req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("ORDER_LOC_CD", DataType.NVarChar, data.ORDER_LOC_CD.Value);
req.Parameters.Add("REVISION_NO", DataType.Number, data.REVISION_NO.Value);
req.Parameters.Add("ITEM_CLS", DataType.NVarChar, data.ITEM_CLS.Value);
req.Parameters.Add("ITEM_DESC", DataType.NVarChar, data.ITEM_DESC.Value);
req.Parameters.Add("ORDER_PROCESS_CLS", DataType.NVarChar, data.ORDER_PROCESS_CLS.Value);
req.Parameters.Add("LOT_SIZE", DataType.Number, data.LOT_SIZE.Value);
req.Parameters.Add("REORDER_POINT", DataType.Number, data.REORDER_POINT.Value);
req.Parameters.Add("SAFETY_STOCK", DataType.Number, data.SAFETY_STOCK.Value);
req.Parameters.Add("MINIMUM_ORDER", DataType.Number, data.MINIMUM_ORDER.Value);
req.Parameters.Add("INV_UM_CLS", DataType.NVarChar, data.INV_UM_CLS.Value);
req.Parameters.Add("ORDER_UM_CLS", DataType.NVarChar, data.ORDER_UM_CLS.Value);
req.Parameters.Add("INV_UM_RATE", DataType.Number, data.INV_UM_RATE.Value);
req.Parameters.Add("ORDER_UM_RATE", DataType.Number, data.ORDER_UM_RATE.Value);
req.Parameters.Add("MAX_CAPACITY", DataType.Number, data.MAX_CAPACITY.Value);
req.Parameters.Add("LEADTIME", DataType.Number, data.LEADTIME.Value);
req.Parameters.Add("SAFETY_LEADTIME", DataType.Number, data.SAFETY_LEADTIME.Value);
req.Parameters.Add("MRP_FLAG", DataType.NVarChar, data.MRP_FLAG.Value);
req.Parameters.Add("ORDER_CONDITION", DataType.NVarChar, data.ORDER_CONDITION.Value);
req.Parameters.Add("oldMRP_NO", DataType.NVarChar, oldMRP_NO);
req.Parameters.Add("oldITEM_CD", DataType.NVarChar, oldITEM_CD);
req.Parameters.Add("oldORDER_LOC_CD", DataType.NVarChar, oldORDER_LOC_CD);
#endregion
return(db.ExecuteNonQuery(req));
}
