/// <summary>
/// Update record by using primary key value on the given data variable
/// </summary>
/// <param name="database"></param>
/// <param name="data">Data with primary key value.</param>
/// <returns></returns>
public int UpdateWithoutPK(Database database, InventoryOnhandDTO data)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" UPDATE " + data.TableName);
sb.AppendLine(" SET ");
//sb.AppendLine(" " + InventoryOnhandDTO.eColumns.YEAR_MONTH + "=:YEAR_MONTH");
//sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_BEGIN_DATE + "=:PERIOD_BEGIN_DATE");
//sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_END_DATE + "=:PERIOD_END_DATE");
//sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ITEM_CD + "=:ITEM_CD");
//sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOC_CD + "=:LOC_CD");
//sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOT_NO + "=:LOT_NO");
sb.AppendLine(" " + InventoryOnhandDTO.eColumns.OPEN_QTY + "=:OPEN_QTY");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.IN_QTY + "=:IN_QTY");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OUT_QTY + "=:OUT_QTY");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ADJUST_QTY + "=:ADJUST_QTY");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.STOCK_TAKE_QTY + "=:STOCK_TAKE_QTY");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ON_HAND_QTY + "=:ON_HAND_QTY");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_BY + "=:UPD_BY");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_DATE + "=GETDATE()");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_MACHINE + "=:UPD_MACHINE");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LAST_RECEIVE_DATE + "=:LAST_RECEIVE_DATE");
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OLD_DATA + "=:OLD_DATA");
sb.AppendLine(" WHERE ");
//sb.AppendLine(" " + InventoryOnhandDTO.eColumns.ID + "=:ID");
sb.AppendLine(" " + InventoryOnhandDTO.eColumns.YEAR_MONTH + "=:YEAR_MONTH");
sb.AppendLine(" AND " + InventoryOnhandDTO.eColumns.PERIOD_BEGIN_DATE + "=:PERIOD_BEGIN_DATE");
sb.AppendLine(" AND " + InventoryOnhandDTO.eColumns.PERIOD_END_DATE + "=:PERIOD_END_DATE");
sb.AppendLine(" AND " + InventoryOnhandDTO.eColumns.ITEM_CD + "=:ITEM_CD");
sb.AppendLine(" AND " + InventoryOnhandDTO.eColumns.LOC_CD + "=:LOC_CD");
sb.AppendLine(" AND ((LOT_NO IS NULL) OR (LOT_NO =:LOT_NO))");
#endregion
DataRequest req = new DataRequest(sb.ToString());
#region Parameters
req.Parameters.Add("ID", DataType.Default, data.ID.Value);
req.Parameters.Add("YEAR_MONTH", DataType.NVarChar, data.YEAR_MONTH.Value);
req.Parameters.Add("PERIOD_BEGIN_DATE", DataType.DateTime, data.PERIOD_BEGIN_DATE.Value);
req.Parameters.Add("PERIOD_END_DATE", DataType.DateTime, data.PERIOD_END_DATE.Value);
req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("LOC_CD", DataType.NVarChar, data.LOC_CD.Value);
req.Parameters.Add("LOT_NO", DataType.NVarChar, data.LOT_NO.Value);
req.Parameters.Add("OPEN_QTY", DataType.Number, data.OPEN_QTY.Value);
req.Parameters.Add("IN_QTY", DataType.Number, data.IN_QTY.Value);
req.Parameters.Add("OUT_QTY", DataType.Number, data.OUT_QTY.Value);
req.Parameters.Add("ADJUST_QTY", DataType.Number, data.ADJUST_QTY.Value);
req.Parameters.Add("STOCK_TAKE_QTY", DataType.Number, data.STOCK_TAKE_QTY.Value);
req.Parameters.Add("ON_HAND_QTY", DataType.Number, data.ON_HAND_QTY.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("LAST_RECEIVE_DATE", DataType.DateTime, data.LAST_RECEIVE_DATE.Value);
req.Parameters.Add("OLD_DATA", DataType.Number, 0);
#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, InventoryOnhandDTO data)
{
Database db = UseDatabase(database);
if (Exist(database, data.ID))
{
return(UpdateWithoutPK(db, data));
}
return(AddNew(db, data));
}
public List <InventoryMovementInqUIDM> LoadDataInventorymovementInquiryByLotNo(NZString YEAR_MONTH, NZDateTime PERIOD_BEGIN_DATE, NZDateTime PERIOD_END_DATE
, NZString ITEM_CD
, NZString LOC_CD
, NZString LOT_NO
, NZString PACK_NO
)
{
InventoryBIZ biz = new InventoryBIZ();
List <InventoryTransactionDTO> listData = biz.LoadInventoryMovementByLotNo(PERIOD_BEGIN_DATE, PERIOD_END_DATE, ITEM_CD, LOC_CD, LOT_NO);
InventoryOnhandDTO dtoInvOnHand = biz.LoadInventoryOnHandByYearMonth(YEAR_MONTH, ITEM_CD, LOC_CD, LOT_NO, PACK_NO);
return(GenerateLines(dtoInvOnHand, listData));
}
public AdjustmentEntryUIDM ConvertDTOToUIDM(InventoryTransactionDTO dto)
{
AdjustmentEntryUIDM model = new AdjustmentEntryUIDM();
model.TransactionID = dto.TRANS_ID;
model.AdjustNo = dto.SLIP_NO;
model.AdjustDate = dto.TRANS_DATE;
model.AdjustType = dto.IN_OUT_CLS;
model.ItemCode = dto.ITEM_CD;
model.StoredLoc = dto.LOC_CD;
model.LotNo = dto.LOT_NO;
model.PackNo = dto.PACK_NO;
model.FGNo = dto.FG_NO;
model.ExternalLotNo = dto.EXTERNAL_LOT_NO;
model.AdjustWeight = dto.WEIGHT;
model.AdjustQty = dto.QTY;
model.Remark = dto.REMARK;
model.ReasonCode = dto.TRAN_SUB_CLS;
//== Get Item Description.
ItemBIZ biz = new ItemBIZ();
ItemDTO itemDTO = biz.LoadItem(dto.ITEM_CD);
model.ItemDesc = itemDTO.SHORT_NAME;
//-- Get Customer Name
DealingBIZ bizCust = new DealingBIZ();
DealingDTO dtoCust = bizCust.LoadLocation(itemDTO.CUSTOMER_CD);
model.CustomerName = dtoCust.LOC_DESC;
//== Get OnHand.
InventoryBIZ inventoryBIZ = new InventoryBIZ();
InventoryPeriodBIZ inventoryPeriodBIZ = new InventoryPeriodBIZ();
InventoryPeriodDTO inventoryPeriodDTO = inventoryPeriodBIZ.LoadCurrentPeriod();
InventoryOnhandDTO inventoryOnhandDTO = inventoryBIZ.LoadInventoryOnHandByDate(inventoryPeriodDTO.YEAR_MONTH, dto.TRANS_DATE, dto.ITEM_CD, dto.LOC_CD, dto.LOT_NO, dto.PACK_NO);
if (inventoryOnhandDTO == null)
{
model.OnHandQty.Value = 0;
}
else
{
model.OnHandQty = inventoryOnhandDTO.ON_HAND_QTY;
}
return(model);
}
private List <InventoryMovementInqUIDM> GenerateLines(InventoryOnhandDTO dtoInvOnHand, List <InventoryTransactionDTO> listData)
{
//== Generate line of inquiry item.
List <InventoryMovementInqUIDM> listModel = new List <InventoryMovementInqUIDM>();
InventoryMovementInqUIDM model = null;
// first line.
model = new InventoryMovementInqUIDM();
if (dtoInvOnHand == null)
{
model.BALANCE.Value = 0;
}
else
{
model.BALANCE.Value = dtoInvOnHand.OPEN_QTY.Value;
model.TRANS_DATE.Value = dtoInvOnHand.PERIOD_BEGIN_DATE.Value;
}
model.TRANS_INFO.Value = "Open Bal";
listModel.Add(model);
decimal dOpenQty = model.BALANCE.NVL(0);
// add each transaction line.
for (int i = 0; i < listData.Count; i++)
{
InventoryTransactionDTO dto = listData[i];
model = new InventoryMovementInqUIDM();
model.TRANS_DATE = dto.TRANS_DATE;
model.TRANS_ID = dto.TRANS_ID;
model.TRANS_INFO = dto.TRANS_CLS;
model.REF_SLIP_NO = dto.REF_SLIP_NO;
model.REF_TYPE = dto.REF_SLIP_CLS;
model.LOT_NO = dto.LOT_NO;
model.IN_QTY.Value = 0;
model.OUT_QTY.Value = 0;
model.BALANCE.Value = 0;
model.REF_NO = dto.REF_NO;
model.REMARK = dto.REMARK;
if (dto.IN_OUT_CLS.StrongValue == "01")
{
model.IN_QTY = dto.QTY;
dOpenQty += dto.QTY.NVL(0);
}
else if (dto.IN_OUT_CLS.StrongValue == "02")
{
model.OUT_QTY = dto.QTY;
dOpenQty -= dto.QTY.NVL(0);
}
else
{
model.NG_CRITERIA = dto.TRAN_SUB_CLS;
model.NG_QTY = dto.QTY;
}
model.BALANCE.Value = dOpenQty; //calculate previous balance.
listModel.Add(model);
}
return(listModel);
}
/// <summary>
/// Insert new record into database.
/// </summary>
/// <param name="database"></param>
/// <param name="data"></param>
/// <returns></returns>
public int AddNew(Database database, InventoryOnhandDTO data)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" INSERT INTO " + data.TableName + "(");
//sb.AppendLine(" " + InventoryOnhandDTO.eColumns.ID);
sb.AppendLine(" " + InventoryOnhandDTO.eColumns.YEAR_MONTH);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_BEGIN_DATE);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.PERIOD_END_DATE);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ITEM_CD);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOC_CD);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LOT_NO);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OPEN_QTY);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.IN_QTY);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OUT_QTY);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ADJUST_QTY);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.STOCK_TAKE_QTY);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.ON_HAND_QTY);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.CRT_BY);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.CRT_DATE);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.CRT_MACHINE);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_BY);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_DATE);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.UPD_MACHINE);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.LAST_RECEIVE_DATE);
sb.AppendLine(" ," + InventoryOnhandDTO.eColumns.OLD_DATA);
sb.AppendLine(") VALUES(");
// sb.AppendLine(" :ID");
sb.AppendLine(" :YEAR_MONTH");
sb.AppendLine(" ,:PERIOD_BEGIN_DATE");
sb.AppendLine(" ,:PERIOD_END_DATE");
sb.AppendLine(" ,:ITEM_CD");
sb.AppendLine(" ,:LOC_CD");
sb.AppendLine(" ,:LOT_NO");
sb.AppendLine(" ,:OPEN_QTY");
sb.AppendLine(" ,:IN_QTY");
sb.AppendLine(" ,:OUT_QTY");
sb.AppendLine(" ,:ADJUST_QTY");
sb.AppendLine(" ,:STOCK_TAKE_QTY");
sb.AppendLine(" ,:ON_HAND_QTY");
sb.AppendLine(" ,:CRT_BY");
sb.AppendLine(" ,GETDATE()");
sb.AppendLine(" ,:CRT_MACHINE");
sb.AppendLine(" ,:UPD_BY");
sb.AppendLine(" ,GETDATE()");
sb.AppendLine(" ,:UPD_MACHINE");
sb.AppendLine(" ,:LAST_RECEIVE_DATE");
sb.AppendLine(" ,:OLD_DATA");
sb.AppendLine(" )");
#endregion
DataRequest req = new DataRequest(sb.ToString());
#region Parameters
//req.Parameters.Add("ID", DataType.Default, data.ID.Value);
req.Parameters.Add("YEAR_MONTH", DataType.NVarChar, data.YEAR_MONTH.Value);
req.Parameters.Add("PERIOD_BEGIN_DATE", DataType.DateTime, data.PERIOD_BEGIN_DATE.Value);
req.Parameters.Add("PERIOD_END_DATE", DataType.DateTime, data.PERIOD_END_DATE.Value);
req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("LOC_CD", DataType.NVarChar, data.LOC_CD.Value);
req.Parameters.Add("LOT_NO", DataType.NVarChar, data.LOT_NO.Value);
req.Parameters.Add("OPEN_QTY", DataType.Number, data.OPEN_QTY.Value);
req.Parameters.Add("IN_QTY", DataType.Number, data.IN_QTY.Value);
req.Parameters.Add("OUT_QTY", DataType.Number, data.OUT_QTY.Value);
req.Parameters.Add("ADJUST_QTY", DataType.Number, data.ADJUST_QTY.Value);
req.Parameters.Add("STOCK_TAKE_QTY", DataType.Number, data.STOCK_TAKE_QTY.Value);
req.Parameters.Add("ON_HAND_QTY", DataType.Number, data.ON_HAND_QTY.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);
req.Parameters.Add("LAST_RECEIVE_DATE", DataType.DateTime, data.LAST_RECEIVE_DATE.Value);
req.Parameters.Add("OLD_DATA", DataType.Int16, 0);
#endregion
try
{
return(db.ExecuteNonQuery(req));
}
catch (Exception err)
{
throw;
}
}