/// <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="oldPO_NO">Old Key #1</param>
/// <param name="oldPO_LINE">Old Key #2</param>
/// <returns></returns>
public int UpdateWithPK(Database database, PurchaseOrderDDTO data, String oldPO_NO, Decimal oldPO_LINE)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" UPDATE " + data.TableName);
sb.AppendLine(" SET ");
sb.AppendLine(" " + PurchaseOrderDDTO.eColumns.UPD_BY + "=:UPD_BY");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.UPD_DATE + "=GETDATE()");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.UPD_MACHINE + "=:UPD_MACHINE");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.IS_ACTIVE + "=:IS_ACTIVE");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.PO_NO + "=:PO_NO");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.PO_LINE + "=:PO_LINE");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.ITEM_CD + "=:ITEM_CD");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.ITEM_DESC + "=:ITEM_DESC");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.DUE_DATE + "=:DUE_DATE");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.UNIT_PRICE + "=:UNIT_PRICE");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.PO_QTY + "=:PO_QTY");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.UNIT + "=:UNIT");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.AMOUNT + "=:AMOUNT");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.RECEIVE_QTY + "=:RECEIVE_QTY");
//sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.BACK_ORDER_QTY + "=:BACK_ORDER_QTY");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.LAST_RECEIVE_ID + "=:LAST_RECEIVE_ID");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.LAST_RECEIVE_DATE + "=:LAST_RECEIVE_DATE");
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.STATUS + "=:STATUS");
sb.AppendLine(" WHERE ");
sb.AppendLine(" " + PurchaseOrderDDTO.eColumns.PO_NO + "=:oldPO_NO");
sb.AppendLine(" AND " + PurchaseOrderDDTO.eColumns.PO_LINE + "=:oldPurchaseOrderD");
#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.Default, data.IS_ACTIVE.Value);
req.Parameters.Add("PO_NO", DataType.NVarChar, data.PO_NO.Value);
req.Parameters.Add("PO_LINE", DataType.Number, data.PO_LINE.Value);
req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("ITEM_DESC", DataType.NVarChar, data.ITEM_DESC.Value);
req.Parameters.Add("DUE_DATE", DataType.Default, data.DUE_DATE.Value);
req.Parameters.Add("UNIT_PRICE", DataType.Number, data.UNIT_PRICE.Value);
req.Parameters.Add("PO_QTY", DataType.Number, data.PO_QTY.Value);
req.Parameters.Add("UNIT", DataType.NVarChar, data.UNIT.Value);
req.Parameters.Add("AMOUNT", DataType.Number, data.AMOUNT.Value);
req.Parameters.Add("RECEIVE_QTY", DataType.Number, data.RECEIVE_QTY.Value);
//req.Parameters.Add("BACK_ORDER_QTY", DataType.Number, data.BACK_ORDER_QTY.Value);
req.Parameters.Add("LAST_RECEIVE_ID", DataType.NVarChar, data.LAST_RECEIVE_ID.Value);
req.Parameters.Add("LAST_RECEIVE_DATE", DataType.DateTime, data.LAST_RECEIVE_DATE.Value);
req.Parameters.Add("STATUS", DataType.NVarChar, data.STATUS.Value);
req.Parameters.Add("oldPO_NO", DataType.NVarChar, oldPO_NO);
req.Parameters.Add("oldPO_LINE", DataType.Number, oldPO_LINE);
#endregion
return(db.ExecuteNonQuery(req));
}
/// <summary>
/// Generate PO Detail List with selected status.
/// </summary>
/// <param name="strPONo">PO No of PO Details.</param>
/// <param name="modifyState">status that want to selected.</param>
/// <returns>List of PO Detail with selected status.</returns>
private List <PurchaseOrderDDTO> GeneratePurchaseOrderDDTO(NZString strPONo, eModifyState modifyState)
{
if (modifyState == eModifyState.Delete)
{
return(m_DDTOListForDelete);
}
else
{
List <PurchaseOrderDDTO> dDTOPurchaseOrderList = new List <PurchaseOrderDDTO>();
for (int iRow = 0; iRow < shtView.Rows.Count; iRow++)
{
if (Convert.ToInt32(shtView.Cells[iRow, (int)eColumn.ModifyState].Value) == (int)modifyState)
{
NZString strItemCD = new NZString(null, shtView.Cells[iRow, (int)eColumn.ITEM_CD].Value, null);
NZString strItemDesc = new NZString(null, shtView.Cells[iRow, (int)eColumn.ITEM_DESC].Value, null);
NZDateTime dtmRequireDate = new NZDateTime(null, shtView.Cells[iRow, (int)eColumn.DUE_DATE].Value, null);
NZDecimal decUnitPrice = new NZDecimal(null, shtView.Cells[iRow, (int)eColumn.UNIT_PRICE].Value, null);
NZDecimal decQty = new NZDecimal(null, shtView.Cells[iRow, (int)eColumn.PO_QTY].Value, null);
NZString strUnit = new NZString(null, shtView.Cells[iRow, (int)eColumn.UNIT].Value, null);
NZDecimal decAmount = new NZDecimal(null, shtView.Cells[iRow, (int)eColumn.AMOUNT].Value, null);
NZDecimal decReceiveQty = new NZDecimal(null, shtView.Cells[iRow, (int)eColumn.RECEIVE_QTY].Value, 0);
NZString strStatus = new NZString(null, shtView.Cells[iRow, (int)eColumn.STATUS].Value, null);
PurchaseOrderDDTO dDTO = new PurchaseOrderDDTO
{
#region tmp data
CRT_BY = CommonLib.Common.CurrentUserInfomation.UserCD,
CRT_DATE = (NZDateTime)DateTime.UtcNow,
CRT_MACHINE = CommonLib.Common.CurrentUserInfomation.Machine,
UPD_BY = CommonLib.Common.CurrentUserInfomation.UserCD,
UPD_DATE = (NZDateTime)DateTime.UtcNow,
UPD_MACHINE = CommonLib.Common.CurrentUserInfomation.Machine,
IS_ACTIVE = (NZBoolean)true,
PO_NO = strPONo,
#endregion
PO_LINE = new NZDecimal(null, shtView.Cells[iRow, (int)eColumn.PO_LINE].Value, 0),
ITEM_CD = strItemCD,
ITEM_DESC = strItemDesc,
DUE_DATE = dtmRequireDate,
UNIT_PRICE = decUnitPrice,
PO_QTY = decQty,
UNIT = strUnit,
AMOUNT = decAmount,
RECEIVE_QTY = (decReceiveQty.IsNull ? new NZDecimal(null, 0) : decReceiveQty),
//BACK_ORDER_QTY = (NZDecimal)0,
//LAST_RECEIVE_ID = (NZString)string.Empty,
//LAST_RECEIVE_DATE = (NZDateTime)DateTime.UtcNow,
STATUS = strStatus
};
dDTOPurchaseOrderList.Add(dDTO);
}
}
return(dDTOPurchaseOrderList);
}
}
/// <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, PurchaseOrderDDTO data)
{
Database db = UseDatabase(database);
if (Exist(database, data.PO_NO, data.PO_LINE))
{
return(UpdateWithoutPK(db, data));
}
return(AddNew(db, data));
}
public int DeleteDTO(Database database, PurchaseOrderDDTO dto)
{
Database db = UseDatabase(database);
DataRequest req = new DataRequest();
req.CommandText = "S_MRP040_DeletePurchaseOrderDByPOLine";
req.CommandType = CommandType.StoredProcedure;
req.Parameters.Add("@pVar_PO_NO", DataType.NVarChar, dto.PO_NO.Value);
req.Parameters.Add("@pVar_PO_LINE", DataType.Decimal, dto.PO_LINE.Value);
return(db.ExecuteNonQuery(req));
}
public static ErrorItem CheckDupItemByDTO(PurchaseOrderDDTO dDTOchk, List <PurchaseOrderDDTO> dDTOList)
{
foreach (PurchaseOrderDDTO dto in dDTOList)
{
bool b1 = dDTOchk.ITEM_CD.Value.Equals(dto.ITEM_CD.Value);
bool b2 = dDTOchk.DUE_DATE.Value.Equals(dto.DUE_DATE.Value);
bool b3 = dDTOchk.UNIT_PRICE.Value.Equals(dto.UNIT_PRICE.Value);
if (b1 && b2 && b3)
{
return(new ErrorItem(dDTOchk.ITEM_CD.Owner, Messages.eValidate.VLM0128.ToString()));
}
}
return(null);
}
/// <summary>
/// Check PO Details, there cannot has the same ItemCode, DueDate and UnitPrice.
/// </summary>
/// <returns>ErrorItem if Has Dupplication.</returns>
private ErrorItem ValidateDupplicateItem()
{
int iRow = shtView.ActiveRowIndex;
if (shtView.Cells[iRow, (int)eColumn.ITEM_CD].Value == null ||
shtView.Cells[iRow, (int)eColumn.DUE_DATE].Value == null ||
shtView.Cells[iRow, (int)eColumn.UNIT_PRICE].Value == null)
{
return(null);
}
PurchaseOrderDDTO dDTOChk = new PurchaseOrderDDTO
{
ITEM_CD = (NZString)Convert.ToString(shtView.Cells[iRow, (int)eColumn.ITEM_CD].Value),
DUE_DATE = (NZDateTime)Convert.ToDateTime(shtView.Cells[iRow, (int)eColumn.DUE_DATE].Value),
UNIT_PRICE = (NZDecimal)Convert.ToDecimal(shtView.Cells[iRow, (int)eColumn.UNIT_PRICE].Value)
};
List <PurchaseOrderDDTO> dDTOPOList = new List <PurchaseOrderDDTO>();
for (int i = 0; i < shtView.Rows.Count; i++)
{
if (shtView.Cells[i, (int)eColumn.ITEM_CD].Value == null ||
shtView.Cells[i, (int)eColumn.DUE_DATE].Value == null ||
shtView.Cells[i, (int)eColumn.UNIT_PRICE].Value == null)
{
continue;
}
if (i != iRow)
{
PurchaseOrderDDTO dDTOTmp = new PurchaseOrderDDTO
{
ITEM_CD = (NZString)Convert.ToString(shtView.Cells[i, (int)eColumn.ITEM_CD].Value),
DUE_DATE = (NZDateTime)Convert.ToDateTime(shtView.Cells[i, (int)eColumn.DUE_DATE].Value),
UNIT_PRICE = (NZDecimal)Convert.ToDecimal(shtView.Cells[i, (int)eColumn.UNIT_PRICE].Value)
};
dDTOPOList.Add(dDTOTmp);
}
}
ErrorItem errItem = PurchaseOrderEntryValidation.CheckDupItemByDTO(dDTOChk, dDTOPOList);
if (errItem != null)
{
shtView.Cells[shtView.ActiveRowIndex, shtView.ActiveColumnIndex].Value = null;
}
return(errItem);
}
public int UpdateBalance(Database database, PurchaseOrderHDTO dtoH, PurchaseOrderDDTO dtoD, decimal dChangeQty)
{
Database db = UseDatabase(database);
DataRequest req = new DataRequest();
req.CommandText = "S_PURP010_FIFOBalancePOProcess";
req.CommandType = CommandType.StoredProcedure;
req.Parameters.Add("@P_RECEIVING_ID", DBNull.Value);
req.Parameters.Add("@P_RECEIVING_DATE", DataType.DateTime, dtoD.DUE_DATE.Value);
req.Parameters.Add("@P_ITEM_CD", DataType.NVarChar, dtoD.ITEM_CD.Value);
req.Parameters.Add("@P_SUPPLIER_CD", DataType.NVarChar, dtoH.SUPPLIER_CD.Value);
req.Parameters.Add("@P_CHGQTY", DataType.Decimal, dChangeQty);
return(db.ExecuteNonQuery(req));
}
/// <summary>
/// Private method that use for delete.
/// </summary>
/// <param name="db"></param>
/// <param name="dtoH"></param>
/// <param name="dtoDs"></param>
private void _DeletePOLine(Database db, PurchaseOrderHDTO dtoH, List <PurchaseOrderDDTO> dtoDs)
{
CheckBeforeUpdate(dtoH, dtoDs);
PurchaseOrderHDAO daoH = new PurchaseOrderHDAO(db);
PurchaseOrderDDAO daoD = new PurchaseOrderDDAO(db);
if (dtoDs == null)
{
return;
}
foreach (PurchaseOrderDDTO dtoD in dtoDs)
{
PurchaseOrderDDTO dtoDTmp = dtoD;
dtoD.RECEIVE_QTY = (NZDecimal)daoD.DeleteWithReturnReceiveQTY(null, dtoD);
daoH.UpdateBalance(null, dtoH, dtoD, dtoD.RECEIVE_QTY);
}
}
public decimal DeleteWithReturnReceiveQTY(Database database, PurchaseOrderDDTO dto)
{
Database db = UseDatabase(database);
DataRequest req = new DataRequest();
req.CommandText = "S_MRP040_DeletePurchaseOrderDByPOLine";
req.CommandType = CommandType.StoredProcedure;
req.Parameters.Add("@pVar_PO_NO", DataType.NVarChar, dto.PO_NO.Value);
req.Parameters.Add("@pVar_PO_LINE", DataType.Decimal, dto.PO_LINE.Value);
object obj = db.ExecuteScalar(req);
if (obj == DBNull.Value || obj == null)
{
return(0);
}
else
{
return(Convert.ToDecimal(obj));
}
}
/// <summary>
/// Insert new record into database.
/// </summary>
/// <param name="database"></param>
/// <param name="data"></param>
/// <returns></returns>
public int AddNew(Database database, PurchaseOrderDDTO data)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" INSERT INTO " + data.TableName + "(");
sb.AppendLine(" " + PurchaseOrderDDTO.eColumns.CRT_BY);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.CRT_DATE);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.CRT_MACHINE);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.UPD_BY);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.UPD_DATE);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.UPD_MACHINE);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.IS_ACTIVE);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.PO_NO);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.PO_LINE);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.ITEM_CD);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.ITEM_DESC);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.DUE_DATE);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.UNIT_PRICE);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.PO_QTY);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.UNIT);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.AMOUNT);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.RECEIVE_QTY);
//sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.BACK_ORDER_QTY);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.LAST_RECEIVE_ID);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.LAST_RECEIVE_DATE);
sb.AppendLine(" ," + PurchaseOrderDDTO.eColumns.STATUS);
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(" ,:PO_NO");
sb.AppendLine(" ,:PO_LINE");
sb.AppendLine(" ,:ITEM_CD");
sb.AppendLine(" ,:ITEM_DESC");
sb.AppendLine(" ,:DUE_DATE");
sb.AppendLine(" ,:UNIT_PRICE");
sb.AppendLine(" ,:PO_QTY");
sb.AppendLine(" ,:UNIT");
sb.AppendLine(" ,:AMOUNT");
sb.AppendLine(" ,:RECEIVE_QTY");
//sb.AppendLine(" ,:BACK_ORDER_QTY");
sb.AppendLine(" ,:LAST_RECEIVE_ID");
sb.AppendLine(" ,:LAST_RECEIVE_DATE");
sb.AppendLine(" ,:STATUS");
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.Default, data.IS_ACTIVE.Value);
req.Parameters.Add("PO_NO", DataType.NVarChar, data.PO_NO.Value);
req.Parameters.Add("PO_LINE", DataType.Number, data.PO_LINE.Value);
req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("ITEM_DESC", DataType.NVarChar, data.ITEM_DESC.Value);
req.Parameters.Add("DUE_DATE", DataType.Default, data.DUE_DATE.Value);
req.Parameters.Add("UNIT_PRICE", DataType.Number, data.UNIT_PRICE.Value);
req.Parameters.Add("PO_QTY", DataType.Number, data.PO_QTY.Value);
req.Parameters.Add("UNIT", DataType.NVarChar, data.UNIT.Value);
req.Parameters.Add("AMOUNT", DataType.Number, data.AMOUNT.Value);
req.Parameters.Add("RECEIVE_QTY", DataType.Number, data.RECEIVE_QTY.Value);
//req.Parameters.Add("BACK_ORDER_QTY", DataType.Number, data.BACK_ORDER_QTY.Value);
req.Parameters.Add("LAST_RECEIVE_ID", DataType.NVarChar, data.LAST_RECEIVE_ID.Value);
req.Parameters.Add("LAST_RECEIVE_DATE", DataType.DateTime, data.LAST_RECEIVE_DATE.Value);
req.Parameters.Add("STATUS", DataType.NVarChar, data.STATUS.Value);
#endregion
return(db.ExecuteNonQuery(req));
}