public int AddNewD(Database database, CustomerOrderDDTO data)
{
Database db = UseDatabase(database);
CustomerOrderDDAO daoCustomerOrderD = new CustomerOrderDDAO();
return(daoCustomerOrderD.AddNew(database, data));
}
/// <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, CustomerOrderDDTO data)
{
Database db = UseDatabase(database);
if (Exist(database, data.ORDER_NO, data.ORDER_DETAIL_NO))
{
return(UpdateWithoutPK(db, data));
}
return(AddNew(db, data));
}
/// <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="oldORDER_NO">Old Key #1</param>
/// <param name="oldRUN_NO">Old Key #2</param>
/// <returns></returns>
public int UpdateWithPK(Database database, CustomerOrderDDTO data, String oldORDER_NO, NZString oldORDER_DETAIL_NO)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" UPDATE " + data.TableName);
sb.AppendLine(" SET ");
sb.AppendLine(" " + CustomerOrderDDTO.eColumns.UPD_BY + "=@UPD_BY");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.UPD_DATE + "=GETDATE()");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.UPD_MACHINE + "=@UPD_MACHINE");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.ORDER_NO + "=@ORDER_NO");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.ORDER_NO + "=@ORDER_DETAIL_NO");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.RUN_NO + "=@RUN_NO");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.ITEM_CD + "=@ITEM_CD");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.ITEM_DELIVERY_DATE + "=@ITEM_DELIVERY_DATE");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.QTY + "=@QTY");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.PRICE + "=@PRICE");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.AMOUNT + "=@AMOUNT");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.AMOUNT_THB + "=@AMOUNT_THB");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.SHIP_QTY + "=@SHIP_QTY");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.COMPLETE_FLAG + "=@COMPLETE_FLAG");
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.OLD_DATA + "=@OLD_DATA");
sb.AppendLine(" WHERE ");
sb.AppendLine(" " + CustomerOrderDDTO.eColumns.ORDER_NO + "=@oldORDER_NO");
sb.AppendLine(" AND " + CustomerOrderDDTO.eColumns.ORDER_DETAIL_NO + "=@oldORDER_DATAIL_NO");
#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("@ORDER_NO", DataType.NVarChar, data.ORDER_NO.Value);
req.Parameters.Add("@ORDER_DETAIL_NO", DataType.NVarChar, data.ORDER_DETAIL_NO.Value);
req.Parameters.Add("@RUN_NO", DataType.Default, data.RUN_NO.Value);
req.Parameters.Add("@ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("@ITEM_DELIVERY_DATE", DataType.DateTime, data.ITEM_DELIVERY_DATE.Value);
req.Parameters.Add("@QTY", DataType.Number, data.QTY.Value);
req.Parameters.Add("@PRICE", DataType.Number, data.PRICE.Value);
req.Parameters.Add("@AMOUNT", DataType.Number, data.AMOUNT.Value);
req.Parameters.Add("@AMOUNT_THB", DataType.Number, data.AMOUNT_THB.Value);
req.Parameters.Add("@SHIP_QTY", DataType.Number, data.SHIP_QTY.Value);
req.Parameters.Add("@COMPLETE_FLAG", DataType.Default, data.COMPLETE_FLAG.Value);
req.Parameters.Add("@OLD_DATA", DataType.Default, 0);
req.Parameters.Add("@oldORDER_NO", DataType.NVarChar, oldORDER_NO);
req.Parameters.Add("@oldORDER_DATAIL_NO", DataType.Default, oldORDER_DETAIL_NO);
#endregion
return(db.ExecuteNonQuery(req));
}
public CustomerOrderViewDTO CustomerOrderDDTO2CustomerOrderViewDTO(CustomerOrderDDTO dtoCustomerOrderDDTO)
{
CustomerOrderViewDTO dtoCustomerOrderView = new CustomerOrderViewDTO();
dtoCustomerOrderView.CRT_BY = dtoCustomerOrderDDTO.CRT_BY;
dtoCustomerOrderView.CRT_DATE = dtoCustomerOrderDDTO.CRT_DATE;
dtoCustomerOrderView.CRT_MACHINE = dtoCustomerOrderDDTO.CRT_MACHINE;
dtoCustomerOrderView.UPD_BY = dtoCustomerOrderDDTO.UPD_BY;
dtoCustomerOrderView.UPD_DATE = dtoCustomerOrderDDTO.UPD_DATE;
dtoCustomerOrderView.UPD_MACHINE = dtoCustomerOrderDDTO.UPD_MACHINE;
dtoCustomerOrderView.ORDER_NO = dtoCustomerOrderDDTO.ORDER_NO;
dtoCustomerOrderView.RUN_NO = dtoCustomerOrderDDTO.RUN_NO;
dtoCustomerOrderView.ITEM_CD = dtoCustomerOrderDDTO.ITEM_CD;
dtoCustomerOrderView.ITEM_DELIVERY_DATE = dtoCustomerOrderDDTO.ITEM_DELIVERY_DATE;
dtoCustomerOrderView.QTY = dtoCustomerOrderDDTO.QTY;
dtoCustomerOrderView.PRICE = dtoCustomerOrderDDTO.PRICE;
dtoCustomerOrderView.AMOUNT = dtoCustomerOrderDDTO.AMOUNT;
dtoCustomerOrderView.AMOUNT_THB = dtoCustomerOrderDDTO.AMOUNT_THB;
dtoCustomerOrderView.SHIP_QTY = dtoCustomerOrderDDTO.SHIP_QTY;
dtoCustomerOrderView.COMPLETE_FLAG = dtoCustomerOrderDDTO.COMPLETE_FLAG;
dtoCustomerOrderView.OLD_DATA = dtoCustomerOrderDDTO.OLD_DATA;
return(dtoCustomerOrderView);
}
//public CustomerOrderEntryUIDM LoadData(NZString orderNo)
//{
// CustomerOrderBIZ biz = new CustomerOrderBIZ();
// List<CustomerOrderViewDTO> listViewDTO = DTOUtility.ConvertDataTableToList<CustomerOrderViewDTO>(biz.LoadCustomerOrderEntry(orderNo, false));
// if (listViewDTO != null)
// {
// if (listViewDTO.Count > 0)
// {
// CustomerOrderEntryUIDM model = MapDTOToUIDM(listViewDTO[0]);
// model.DATA_VIEW = DTOUtility.ConvertListToDataTable(listViewDTO);
// //== Ensure that data has not modified.
// model.DATA_VIEW.AcceptChanges();
// return model;
// }
// }
// return new CustomerOrderEntryUIDM();
//}
#region SaveData
public void Save(CustomerOrderEntryUIDM model, Common.eScreenMode Mode)
{
try
{
Common.CurrentDatabase.KeepConnection = true;
Common.CurrentDatabase.BeginTransaction(IsolationLevel.Serializable);
CustomerOrderBIZ biz = new CustomerOrderBIZ();
#region Validate Data
//== If data not has to processing.
if (model.DATA_VIEW == null || model.DATA_VIEW.Rows.Count == 0)
{
ValidateException.ThrowErrorItem(new ErrorItem(null, TKPMessages.eValidate.VLM0055.ToString()));
}
DataTable dtNonDelete = model.DATA_VIEW.GetChanges(DataRowState.Unchanged | DataRowState.Added | DataRowState.Modified);
List <CustomerOrderViewDTO> listData = DTOUtility.ConvertDataTableToList <CustomerOrderViewDTO>(dtNonDelete);
#endregion
DataTable dtData = model.DATA_VIEW.Copy();
DataTable dtAdd = dtData.GetChanges(DataRowState.Added);
DataTable dtModify = dtData.GetChanges(DataRowState.Modified);
DataTable dtDelete = dtData.GetChanges(DataRowState.Deleted);
//ORDER_DETAIL_NO,
//ITEM_CD,
//ITEM_CD_BTN,
//PART_NO,
//ITEM_DELIVERY_DATE,
//OLD_ITEM_DELIVERY_DATE,
//QTY,
//OLD_QTY,
//PRICE,
//AMOUNT,
//PRICE_THB,
//AMOUNT_THB
// แก้บั๊ก GetChange แล้ว Data ไม่ถูกต้อง
dtData.AcceptChanges();
if (dtModify != null && dtModify.Rows.Count > 0)
{
for (int i = 0; i < dtModify.Rows.Count; i++)
{
for (int j = 0; j < dtData.Rows.Count; j++)
{
if (Convert.ToString(dtModify.Rows[i]["ORDER_DETAIL_NO"]) == Convert.ToString(dtData.Rows[j]["ORDER_DETAIL_NO"]))
{
dtModify.Rows[i]["ITEM_CD"] = dtData.Rows[j]["ITEM_CD"];
dtModify.Rows[i]["PART_NO"] = dtData.Rows[j]["PART_NO"];
dtModify.Rows[i]["ITEM_DELIVERY_DATE"] = dtData.Rows[j]["ITEM_DELIVERY_DATE"];
dtModify.Rows[i]["QTY"] = dtData.Rows[j]["QTY"];
dtModify.Rows[i]["PRICE"] = dtData.Rows[j]["PRICE"];
dtModify.Rows[i]["AMOUNT"] = dtData.Rows[j]["AMOUNT"];
dtModify.Rows[i]["PRICE_THB"] = dtData.Rows[j]["PRICE_THB"];
dtModify.Rows[i]["AMOUNT_THB"] = dtData.Rows[j]["AMOUNT_THB"];
}
}
}
}
List <CustomerOrderViewDTO> listAdd = new List <CustomerOrderViewDTO>();
List <CustomerOrderViewDTO> listUpdate = new List <CustomerOrderViewDTO>();
List <CustomerOrderViewDTO> listDelete = new List <CustomerOrderViewDTO>();
//มีการปรับให้ทำคำสั่งทีละ set ของ add , update , delete เลย
//ที่เริ่มจาก delete ก่อนเพราะ consumption จะได้มีการเอาไปใช้สำหรับตัวที่ add ได้เลย ไม่ต้องค้างไว้ lot หน้า
//== Delete process.
if (dtDelete != null && dtDelete.Rows.Count > 0)
{
listDelete = DTOUtility.ConvertDataTableToList <CustomerOrderViewDTO>(dtDelete);
for (int i = 0; i < listDelete.Count; i++)
{
AssignHeaderToDTO(model, listDelete[i], Mode);
}
biz.SaveCustomerOrder(Common.CurrentDatabase, null, null, listDelete, Mode);
// When detail row <= 0 then delete header row
if ((dtAdd == null || dtAdd.Rows.Count == 0) &&
(dtModify == null || dtModify.Rows.Count == 0) &&
biz.LoadCustomerOrderEntry(listDelete[0].ORDER_NO, false).Count <= 0)
{
biz.DeleteCustomerOrderHeader(Common.CurrentDatabase, listDelete);
}
}
//== Insert process.
if (dtAdd != null && dtAdd.Rows.Count > 0)
{
listAdd = DTOUtility.ConvertDataTableToList <CustomerOrderViewDTO>(dtAdd);
List <CustomerOrderViewDTO> listAddEachDetail = null;
NZString runningNo = null;
RunningNumberBIZ runningNumberBIZ = new RunningNumberBIZ();
if (model.ORDER_NO.IsNull)
{
runningNo = runningNumberBIZ.GetCompleteRunningNo((NZString)"CUSTOMER_ORDER_NO", (NZString)"TB_CUSTOMER_ORDERH_TR");
model.ORDER_NO = runningNo;
// Insert Header
AssignHeaderToDTO(model, listAdd[0], Mode);
biz.AddCustomerOrderHeader(Common.CurrentDatabase, listAdd[0]);
}
else
{
runningNo = model.ORDER_NO;
}
listAddEachDetail = new List <CustomerOrderViewDTO>();
for (int i = 0; i < listAdd.Count; i++)
{
CustomerOrderViewDTO dto = listAdd[i];
// Copy ค่า Header จาก Model ไปยัง DTO แต่ละตัว
AssignHeaderToDTO(model, dto, Mode);
//dto.TRANS_ID = runningNumberBIZ.GetCompleteRunningNo(DataDefine.TRANS_ID.ToNZString(), DataDefine.TRANSACTION_TABLE_NAME.ToNZString());
//add header ใส่ list
dto.CRT_BY = Common.CurrentUserInfomation.UserCD;
dto.CRT_MACHINE = Common.CurrentUserInfomation.Machine;
dto.UPD_BY = Common.CurrentUserInfomation.UserCD;
dto.UPD_MACHINE = Common.CurrentUserInfomation.Machine;
// Check all lines.
listAddEachDetail.Add(dto);
}
biz.SaveCustomerOrder(Common.CurrentDatabase, listAddEachDetail, null, null, Mode);
}
//== Update process.
if (dtModify != null && dtModify.Rows.Count > 0)
{
listUpdate = DTOUtility.ConvertDataTableToList <CustomerOrderViewDTO>(dtModify);
List <CustomerOrderViewDTO> listUpdateDetail = new List <CustomerOrderViewDTO>();
for (int i = 0; i < listUpdate.Count; i++)
{
CustomerOrderViewDTO dto = listUpdate[i];
CustomerOrderBIZ bizCustomer = new CustomerOrderBIZ();
CustomerOrderDDTO dtoDDTO = bizCustomer.LoadCustomerOrderDetail(dto.ORDER_NO.StrongValue, dto.ORDER_DETAIL_NO);
// Copy ค่า Header จาก Model ไปยัง DTO แต่ละตัว
AssignHeaderToDTO(model, dto, Mode);
dto.CRT_BY = Common.CurrentUserInfomation.UserCD;
dto.CRT_MACHINE = Common.CurrentUserInfomation.Machine;
dto.UPD_BY = Common.CurrentUserInfomation.UserCD;
dto.UPD_MACHINE = Common.CurrentUserInfomation.Machine;
//add header ใส่ list
listUpdateDetail.Add(dto);
}
biz.SaveCustomerOrder(Common.CurrentDatabase, null, listUpdateDetail, null, Mode);
}
//Update Header for Edit Case
//if (Mode == Common.eScreenMode.EDIT && dtData != null && dtData.Rows.Count > 0)
//{
// listUpdate = DTOUtility.ConvertDataTableToList<CustomerOrderViewDTO>(dtData);
// for (int i = 0; i < listUpdate.Count; i++)
// {
// CustomerOrderViewDTO dto = listUpdate[i];
// // Copy ค่า Header จาก Model ไปยัง DTO แต่ละตัว
// AssignHeaderToDTO(model, dto);
// dto.CRT_BY = Common.CurrentUserInfomation.UserCD;
// dto.CRT_MACHINE = Common.CurrentUserInfomation.Machine;
// dto.UPD_BY = Common.CurrentUserInfomation.UserCD;
// dto.UPD_MACHINE = Common.CurrentUserInfomation.Machine;
// biz.UpdateCustomerOrderHeader(Common.CurrentDatabase, dto);
// }
//}
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, CustomerOrderDDTO data)
{
Database db = UseDatabase(database);
StringBuilder sb = new StringBuilder();
#region SQL Statement
sb.AppendLine(" INSERT INTO " + data.TableName + "(");
sb.AppendLine(" " + CustomerOrderDDTO.eColumns.CRT_BY);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.CRT_DATE);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.CRT_MACHINE);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.UPD_BY);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.UPD_DATE);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.UPD_MACHINE);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.ORDER_NO);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.ORDER_DETAIL_NO);
//sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.RUN_NO);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.ITEM_CD);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.ITEM_DELIVERY_DATE);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.QTY);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.PRICE);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.AMOUNT);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.AMOUNT_THB);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.SHIP_QTY);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.COMPLETE_FLAG);
sb.AppendLine(" ," + CustomerOrderDDTO.eColumns.OLD_DATA);
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(" ,@ORDER_NO");
sb.AppendLine(" ,@ORDER_DETAIL_NO");
//sb.AppendLine(" ,@RUN_NO");
sb.AppendLine(" ,@ITEM_CD");
sb.AppendLine(" ,@ITEM_DELIVERY_DATE");
sb.AppendLine(" ,@QTY");
sb.AppendLine(" ,@PRICE");
sb.AppendLine(" ,@AMOUNT");
sb.AppendLine(" ,@AMOUNT_THB");
sb.AppendLine(" ,@SHIP_QTY");
sb.AppendLine(" ,@COMPLETE_FLAG");
sb.AppendLine(" ,@OLD_DATA");
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("@ORDER_NO", DataType.NVarChar, data.ORDER_NO.Value);
req.Parameters.Add("@ORDER_DETAIL_NO", DataType.NVarChar, data.ORDER_DETAIL_NO.Value);
//req.Parameters.Add("@RUN_NO", DataType.Default, data.RUN_NO.Value);
req.Parameters.Add("@ITEM_CD", DataType.NVarChar, data.ITEM_CD.Value);
req.Parameters.Add("@ITEM_DELIVERY_DATE", DataType.DateTime, data.ITEM_DELIVERY_DATE.Value);
req.Parameters.Add("@QTY", DataType.Number, data.QTY.Value);
req.Parameters.Add("@PRICE", DataType.Number, data.PRICE.Value);
req.Parameters.Add("@AMOUNT", DataType.Number, data.AMOUNT.Value);
req.Parameters.Add("@AMOUNT_THB", DataType.Number, data.AMOUNT_THB.Value);
req.Parameters.Add("@SHIP_QTY", DataType.Number, data.SHIP_QTY.Value);
req.Parameters.Add("@COMPLETE_FLAG", DataType.Default, data.COMPLETE_FLAG.Value);
req.Parameters.Add("@OLD_DATA", DataType.Default, 0);
#endregion
return(db.ExecuteNonQuery(req));
}
