private void GenerateTmpDemandOrderDTOList(List <TmpDemandOrderDTO> dtoDemandOrderList)
{
DateTime dtmSelect = Convert.ToDateTime(dtpDemand.Value);
for (int iRow = 0; iRow < shtView.Rows.Count; iRow++)
{
#region validation
ValidateException.ThrowErrorItem(
PurchaseOrderEntryValidation.CheckEmptyString((NZString)Convert.ToString(shtView.Cells[iRow, (int)eColumn.ITEM_DESC].Value), Messages.eValidate.VLM0129));
#endregion
for (int iColumn = (int)eColumn.Day1; iColumn < DateTime.DaysInMonth(dtmSelect.Year, dtmSelect.Month) + (int)eColumn.Day1; iColumn++)
{
TmpDemandOrderDTO dtoTmpDemandOrder = new TmpDemandOrderDTO
{
CRT_BY = CommonLib.Common.CurrentUserInfomation.UserCD,
CRT_MACHINE = CommonLib.Common.CurrentUserInfomation.Machine,
CRT_DATE = (NZDateTime)DateTime.UtcNow,
CUSTOMER_CD = (NZString)txtCusCD.Text,
DUE_DATE = (NZDateTime)(new DateTime(dtmSelect.Year, dtmSelect.Month, (iColumn - 2))),
ITEM_CD = (NZString)Convert.ToString(shtView.Cells[iRow, (int)eColumn.ITEM_CD].Value.ToString()),
ITEM_DESC = (NZString)Convert.ToString(shtView.Cells[iRow, (int)eColumn.ITEM_DESC].Value),
ORDER_QTY = (NZDecimal)(Convert.ToDecimal(shtView.Cells[iRow, iColumn].Value)),
ORDER_TYPE = null,
YEAR_MONTH = (NZDateTime)dtpDemand.Value,
};
if (dtoTmpDemandOrder.ORDER_QTY > 0)
{
dtoDemandOrderList.Add(dtoTmpDemandOrder);
}
}
}
}
/// <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, TmpDemandOrderDTO data)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" UPDATE " + data.TableName);
sb.AppendLine(" SET ");
sb.AppendLine(" " + TmpDemandOrderDTO.eColumns.YEAR_MONTH + "=:YEAR_MONTH");
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.ORDER_QTY + "=:ORDER_QTY");
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.ORDER_TYPE + "=:ORDER_TYPE");
sb.AppendLine(" WHERE ");
sb.AppendLine(" " + TmpDemandOrderDTO.eColumns.CUSTOMER_CD + "=:CUSTOMER_CD");
sb.AppendLine(" AND " + TmpDemandOrderDTO.eColumns.DUE_DATE + "=:DUE_DATE");
sb.AppendLine(" AND " + TmpDemandOrderDTO.eColumns.ITEM_CD + "=:ITEM_CD");
#endregion
DataRequest req = new DataRequest(sb.ToString());
#region Parameters
req.Parameters.Add("YEAR_MONTH", DataType.Default, data.YEAR_MONTH.Value);
req.Parameters.Add("CUSTOMER_CD", DataType.NVarChar, data.CUSTOMER_CD.Value);
req.Parameters.Add("DUE_DATE", DataType.DateTime, data.DUE_DATE.Value);
req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("ORDER_QTY", DataType.Number, data.ORDER_QTY.Value);
req.Parameters.Add("ORDER_TYPE", DataType.NVarChar, data.ORDER_TYPE.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, TmpDemandOrderDTO data)
{
Database db = UseDatabase(database);
if (Exist(database, data.CUSTOMER_CD, data.DUE_DATE, data.ITEM_CD))
{
return(UpdateWithoutPK(db, data));
}
return(AddNew(db, data));
}
/// <summary>
/// Insert new record into database.
/// </summary>
/// <param name="database"></param>
/// <param name="data"></param>
/// <returns></returns>
public int AddNew(Database database, TmpDemandOrderDTO data)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" INSERT INTO " + data.TableName + "(");
sb.AppendLine(" " + TmpDemandOrderDTO.eColumns.CRT_BY);
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.CRT_DATE);
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.CRT_MACHINE);
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.YEAR_MONTH);
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.CUSTOMER_CD);
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.DUE_DATE);
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.ITEM_CD);
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.ORDER_QTY);
sb.AppendLine(" ," + TmpDemandOrderDTO.eColumns.ORDER_TYPE);
sb.AppendLine(") VALUES(");
sb.AppendLine(" :CRT_BY");
sb.AppendLine(" ,GETDATE()");
sb.AppendLine(" ,:CRT_MACHINE");
sb.AppendLine(" ,:YEAR_MONTH");
sb.AppendLine(" ,:CUSTOMER_CD");
sb.AppendLine(" ,:DUE_DATE");
sb.AppendLine(" ,:ITEM_CD");
sb.AppendLine(" ,:ORDER_QTY");
sb.AppendLine(" ,:ORDER_TYPE");
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("YEAR_MONTH", DataType.Default, data.YEAR_MONTH.Value);
req.Parameters.Add("CUSTOMER_CD", DataType.NVarChar, data.CUSTOMER_CD.Value);
req.Parameters.Add("DUE_DATE", DataType.DateTime, data.DUE_DATE.Value);
req.Parameters.Add("ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("ORDER_QTY", DataType.Number, data.ORDER_QTY.Value);
req.Parameters.Add("ORDER_TYPE", DataType.NVarChar, data.ORDER_TYPE.Value);
#endregion
return(db.ExecuteNonQuery(req));
}
public int InsertTempDemandOrder(Database database, TmpDemandOrderDTO dto)
{
Database db = UseDatabase(database);
DataRequest req = new DataRequest();
req.CommandText = "S_MRP020_InsertTmpDemandOrder";
req.CommandType = CommandType.StoredProcedure;
req.Parameters.Add("@pVar_CRT_BY", DataType.NVarChar, dto.CRT_BY.Value);
req.Parameters.Add("@pDtm_CRT_DATE", DataType.DateTime, dto.CRT_DATE.Value);
req.Parameters.Add("@pVar_CRT_MACHINE", DataType.NVarChar, dto.CRT_MACHINE.Value);
req.Parameters.Add("@pDtm_YEAR_MONTH", DataType.DateTime, dto.YEAR_MONTH.Value);
req.Parameters.Add("@pVar_CUSTOMER_CD", DataType.NVarChar, dto.CUSTOMER_CD.Value);
req.Parameters.Add("@pDtm_DUE_DATE", DataType.DateTime, dto.DUE_DATE.Value);
req.Parameters.Add("@pVar_ITEM_CD", DataType.NVarChar, dto.ITEM_CD.Value);
req.Parameters.Add("@pVar_ITEM_DESC", DataType.NVarChar, dto.ITEM_DESC.Value);
req.Parameters.Add("@pNum_ORDER_QTY", DataType.Decimal, dto.ORDER_QTY.Value);
req.Parameters.Add("@pVar_ORDER_TYPE", DataType.NVarChar, dto.ORDER_TYPE.Value);
return(db.ExecuteNonQuery(req));
}
