private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
if (checkValidDatHang())
{
int selectedRow = this.dataGridViewSanPham.SelectedRows[0].Index;
DonDatHangSPDto orderSP = this.orderDTO.listSanPham[selectedRow];
orderSP.idOrder = orderDTO.id;
orderSP.tenSanPham = cbbLoaiSanPham.Text;
orderSP.kichThuoc = cbbSize.Text;
orderSP.loaiBia = cbbLoaiBia.Text;
orderSP.loaiGiay = cbbLoaiGiay.Text;
orderSP.soTrang = (int)txtSoTo.Value;
orderSP.donGia = double.Parse(txtDonGia.Text);
orderSP.soluong = (int)txtSoLuong.Value;
orderSP.chietKhau = (int)txtChietKhau.Value;
orderSP.thanhTien = orderSP.donGia * orderSP.soluong;
orderSP.thanhTien = orderSP.thanhTien - (orderSP.chietKhau / 100 * orderSP.thanhTien);
orderSP.cdcr = txtTenCDCR.Text;
//orderDTO.listSanPham.Add(orderSP);
updateUI();
setSetForm();
this.btnUpdate.Enabled = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ERROR!!!");
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
if (checkValidDatHang())
{
DonDatHangSPDto orderSP = new DonDatHangSPDto();
orderSP.idOrder = orderDTO.id;
orderSP.tenSanPham = cbbLoaiSanPham.Text;
orderSP.kichThuoc = cbbSize.Text;
orderSP.loaiBia = cbbLoaiBia.Text;
orderSP.loaiGiay = cbbLoaiGiay.Text;
orderSP.soTrang = (int)txtSoTo.Value;
orderSP.donGia = double.Parse(txtDonGia.Text);
orderSP.soluong = (int)txtSoLuong.Value;
orderSP.chietKhau = (int)txtChietKhau.Value;
orderSP.thanhTien = orderSP.donGia * orderSP.soluong;
orderSP.thanhTien = orderSP.thanhTien - (orderSP.chietKhau / 100 * orderSP.thanhTien);
orderSP.cdcr = txtTenCDCR.Text;
orderDTO.listSanPham.Add(orderSP);
updateUI();
setSetForm();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ERROR!!!");
}
}
protected void contextItemClick(object sender, EventArgs e)
{
MenuItem item = (MenuItem)sender;
if (item.Text == "Edit")
{
int selectedRow = this.dataGridViewSanPham.SelectedRows[0].Index;
DonDatHangSPDto orderSP = this.orderDTO.listSanPham[selectedRow];
cbbLoaiSanPham.Text = orderSP.tenSanPham;
cbbSize.Text = orderSP.kichThuoc;
cbbLoaiBia.Text = orderSP.loaiBia;
cbbLoaiGiay.Text = orderSP.loaiGiay;
txtSoTo.Value = orderSP.soTrang;
txtDonGia.Text = orderSP.donGia.ToString();
txtSoLuong.Value = orderSP.soluong;
txtChietKhau.Value = (decimal)orderSP.chietKhau;
txtTenCDCR.Text = orderSP.cdcr;
this.btnUpdate.Enabled = true;
}
if (item.Text == "Delete")
{
DialogResult result = MessageBox.Show("Bạn có chắc muốn xóa?", "Confirmation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
int selectedRow = this.dataGridViewSanPham.SelectedRows[0].Index;
this.orderDTO.listSanPham.RemoveAt(selectedRow);
updateUI();
}
}
}
public static void exportOrder(String idOrder, OrderDto order, String path)
{
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Chưa cài đặt excel, thao tác bị lỗi", "ERROR");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Open(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + "\\Template\\OrderTemplate.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[4, 1] = "Tên khách hàng : " + order.tenKhachHang;
xlWorkSheet.Cells[4, 5] = "Số Order : " + idOrder;
xlWorkSheet.Cells[6, 3] = "Địa chỉ : " + order.diaDiemGiaoHang;
xlWorkSheet.Cells[7, 1] = "Ngày đặt : " + order.ngayDat.ToString("dd/MM/yyyy");
xlWorkSheet.Cells[7, 4] = "Ngày đặt : " + order.ngayGiao.ToString("dd/MM/yyyy");
List <DonDatHangSPDto> dtProductDetail = OrderDao.getOderDetailByOrderID(idOrder);
for (int i = 0; i < dtProductDetail.Count; i++)
{
var r = xlWorkSheet.get_Range(string.Format("{0}:{0}", 10, Type.Missing));
var range = xlWorkSheet.get_Range(string.Format("{0}:{0}", 9, Type.Missing));
range.Select();
range.Copy();
r.Insert();
DonDatHangSPDto orderDetail = dtProductDetail[i];
xlWorkSheet.Cells[9, 1] = i + 1;
xlWorkSheet.Cells[9, 2] = orderDetail.tenSanPham;
xlWorkSheet.Cells[9, 3] = orderDetail.soluong;
xlWorkSheet.Cells[9, 4] = orderDetail.kichThuoc;
xlWorkSheet.Cells[9, 5] = orderDetail.soTrang;
xlWorkSheet.Cells[9, 6] = orderDetail.loaiBia;
xlWorkSheet.Cells[9, 7] = orderDetail.loaiGiay;
xlWorkSheet.Cells[9, 8] = orderDetail.donGia;
xlWorkSheet.Cells[9, 9] = orderDetail.chietKhau + "%";
xlWorkSheet.Cells[9, 10] = orderDetail.thanhTien;
}
DateTime now = DateTime.Now;
xlWorkSheet.Cells[10 + dtProductDetail.Count, 10] = order.tongCong;
xlWorkSheet.Cells[11 + dtProductDetail.Count, 10] = order.vat;
xlWorkSheet.Cells[12 + dtProductDetail.Count, 10] = order.tongTien;
xlWorkBook.CheckCompatibility = false;
xlWorkBook.SaveAs(path + "\\Order_" + idOrder + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
private void loadData()
{
try
{
if (order != null)
{
// fill Data
this.lblSoOrder.Text = this.idOrder;
this.lblTenKhachHang.Text = order.tenKhachHang;
this.lblNgayGiao.Text = order.ngayGiao.ToString("dd/MM/yyyy");
this.lblNgayDat.Text = order.ngayDat.ToString("dd/MM/yyyy");
this.lblSDT.Text = order.dienThoai;
this.lblDiaChi.Text = order.diaDiemGiaoHang;
this.lblCong.Text = order.tongCong.ToString("#,### VND");
this.lblThueVAT.Text = order.vat.ToString("#,### VND");
this.lblTongTien.Text = order.tongTien.ToString("#,### VND");
this.ckbThanhToan.Checked = order.thanhToan;
this.ckbGiaoHang.Checked = order.xuatKho;
this.lblGhiChu.Text = order.notes;
List <DonDatHangSPDto> dtProductDetail = OrderDao.getOderDetailByOrderID(this.idOrder);
lvProductDetail.Items.Clear();
for (int i = 0; i < dtProductDetail.Count; i++)
{
DonDatHangSPDto orderDetail = dtProductDetail[i];
ListViewItem listitem = new ListViewItem((i + 1).ToString());
listitem.SubItems.Add(orderDetail.tenSanPham);
listitem.SubItems.Add(orderDetail.soluong.ToString());
listitem.SubItems.Add(orderDetail.kichThuoc);
listitem.SubItems.Add(orderDetail.soTrang.ToString());
listitem.SubItems.Add(orderDetail.loaiBia);
listitem.SubItems.Add(orderDetail.loaiGiay);
listitem.SubItems.Add(orderDetail.donGia.ToString("#,###"));
listitem.SubItems.Add(orderDetail.chietKhau.ToString() + " %");
listitem.SubItems.Add(orderDetail.thanhTien.ToString("#,###"));
lvProductDetail.Items.Add(listitem);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error!");
}
}
private DonDatHangSPDto createDonDathangSPDto(SubFormProductObj obj, DateTime systemTime)
{
DonDatHangSPDto dto = new DonDatHangSPDto();
dto.idOrder = obj.idOrder;
dto.tenSanPham = obj.tenSanPham;
dto.kichThuoc = obj.kichThuoc;
dto.loaiBia = obj.loaiBia;
dto.soTrang = obj.soTrang;
dto.loaiGiay = obj.loaiGiay;
dto.soluong = obj.soluong;
dto.thanhTien = obj.thanhTien;
dto.donVi = obj.donVi;
dto.donGia = obj.donGia;
dto.cdcr = obj.cdcr;
dto.updateBy = obj.user;
dto.createBy = obj.user;
dto.createTime = systemTime;
return(dto);
}
public void updateOrder(OrderDto orderDto)
{
SqlConnection sqlConnection = Connection.getConnection();
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
try
{
String strQuery = ""
+ " UPDATE DON_DAT_HANG SET"
+ " ID_KHACH_HANG = @idKhachHang"
+ ", NGAY_DAT = @ngayDat"
+ ", NGAY_GIAO = @ngayGiao"
+ ", TONG_CONG = @tongCong"
+ ", VAT = @vat"
+ ", TONG_TIEN = @tongTien"
+ ", PHI_VAN_CHUYEN = @phiVanChuyen"
+ ", NOTES = @notes"
+ ", LIEN_HE = @lienHe"
+ ", SDT = @sdt"
+ ", DIA_DIEM_GIAO_HANG = @diaDiemGiaoHang"
+ " WHERE id = @id";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
cmd.Transaction = sqlTransaction;
cmd.Parameters.AddWithValue("@id", orderDto.id);
cmd.Parameters.AddWithValue("@idKhachHang", orderDto.idKhachHang);
cmd.Parameters.AddWithValue("@ngayDat", new DateTime(orderDto.ngayDat.Year, orderDto.ngayDat.Month, orderDto.ngayDat.Day));
cmd.Parameters.AddWithValue("@ngayGiao", new DateTime(orderDto.ngayGiao.Year, orderDto.ngayGiao.Month, orderDto.ngayGiao.Day));
cmd.Parameters.AddWithValue("@tongCong", orderDto.tongCong);
cmd.Parameters.AddWithValue("@vat", orderDto.vat);
cmd.Parameters.AddWithValue("@tongTien", orderDto.tongTien);
cmd.Parameters.AddWithValue("@phiVanChuyen", orderDto.phiVanChuyen);
cmd.Parameters.AddWithValue("@notes", orderDto.notes);
cmd.Parameters.AddWithValue("@lienHe", orderDto.lienHe);
cmd.Parameters.AddWithValue("@sdt", orderDto.dienThoai);
cmd.Parameters.AddWithValue("@diaDiemGiaoHang", orderDto.diaDiemGiaoHang);
cmd.ExecuteNonQuery();
// delete Detail
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
cmd.Transaction = sqlTransaction;
String sqlDelete = "DELETE DON_DAT_HANG_SP WHERE ID_DON_DAT_HANG = @idDonHang";
cmd.CommandText = sqlDelete;
cmd.Parameters.AddWithValue("@idDonHang", orderDto.id);
cmd.ExecuteNonQuery();
// save Detail
cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection;
cmd.Transaction = sqlTransaction;
String sql = "INSERT INTO DON_DAT_HANG_SP ("
+ " ID_DON_DAT_HANG"
+ ", TEN_SAN_PHAM"
+ ", SO_LUONG"
+ ", KICH_THUOC"
+ ", DON_VI"
+ ", SO_TRANG"
+ ", LOAI_BIA"
+ ", LOAI_GIAY"
+ ", DON_GIA"
+ ", CHIET_KHAU"
+ ", THANH_TIEN"
+ ", CD_CR"
+ ", CREATE_BY"
+ ", CREATE_TIME"
+ " )"
+ " VALUES ";
for (int i = 0; i < orderDto.listSanPham.Count; i++)
{
sql += "("
+ " @idDonDatHang" + i
+ " , @tenSanPham" + i
+ " , @soLuong" + i
+ " , @kichThuoc" + i
+ " , @donVi" + i
+ " , @soTrang" + i
+ " , @loaiBia" + i
+ " , @loaiGiay" + i
+ " , @donGia" + i
+ " , @chietKhau" + i
+ " , @thanhTien" + i
+ " , @cdcr" + i
+ " , @createBy" + i
+ " , @createTime" + i
+ " ) ";
if (i < orderDto.listSanPham.Count - 1)
{
sql += ", ";
}
DonDatHangSPDto dto = orderDto.listSanPham[i];
cmd.Parameters.AddWithValue("@idDonDatHang" + i, dto.idOrder);
cmd.Parameters.AddWithValue("@tenSanPham" + i, dto.tenSanPham);
cmd.Parameters.AddWithValue("@soLuong" + i, dto.soluong);
cmd.Parameters.AddWithValue("@kichThuoc" + i, dto.kichThuoc);
cmd.Parameters.AddWithValue("@donVi" + i, dto.donVi);
cmd.Parameters.AddWithValue("@soTrang" + i, dto.soTrang);
cmd.Parameters.AddWithValue("@loaiBia" + i, dto.loaiBia);
cmd.Parameters.AddWithValue("@loaiGiay" + i, dto.loaiGiay);
cmd.Parameters.AddWithValue("@donGia" + i, dto.donGia);
cmd.Parameters.AddWithValue("@chietKhau" + i, dto.chietKhau);
cmd.Parameters.AddWithValue("@thanhTien" + i, dto.thanhTien);
cmd.Parameters.AddWithValue("@cdcr" + i, dto.cdcr);
cmd.Parameters.AddWithValue("@createBy" + i, dto.createBy);
cmd.Parameters.AddWithValue("@createTime" + i, dto.createTime);
}
cmd.CommandText = sql;
int numRowExcute = cmd.ExecuteNonQuery();
if (numRowExcute != orderDto.listSanPham.Count)
{
throw new Exception("update ĐƠN ĐẶT HÀNG fail!");
}
sqlTransaction.Commit();
}
catch (Exception ex)
{
sqlTransaction.Rollback();
throw new Exception(ex.Message);
}
}
public static List <DonDatHangSPDto> getOderDetailByOrderID(String orderID)
{
String strQuery = "SELECT ID "
+ ", TEN_SAN_PHAM "
+ ", KICH_THUOC "
+ ", DON_VI "
+ ", SO_TRANG "
+ ", LOAI_BIA "
+ ", LOAI_GIAY "
+ ", DON_GIA "
+ ", SO_LUONG "
+ ", CHIET_KHAU "
+ ", THANH_TIEN "
+ ", CD_CR "
+ " FROM DON_DAT_HANG_SP "
+ " WHERE ID_DON_DAT_HANG = @orderID";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.CommandType = CommandType.Text;
cmd.Connection = Connection.getConnection();
cmd.Parameters.AddWithValue("@orderID", orderID);
SqlDataReader reader = cmd.ExecuteReader();
List <DonDatHangSPDto> listDetail = new List <DonDatHangSPDto>();
while (reader.Read())
{
DonDatHangSPDto donHangDetail = new DonDatHangSPDto();
if (!reader.IsDBNull(0))
{
donHangDetail.id = reader.GetInt32(0);
}
if (!reader.IsDBNull(1))
{
donHangDetail.tenSanPham = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
donHangDetail.kichThuoc = reader.GetString(2);
}
if (!reader.IsDBNull(3))
{
donHangDetail.donVi = reader.GetString(3);
}
if (!reader.IsDBNull(4))
{
donHangDetail.soTrang = reader.GetInt32(4);
}
if (!reader.IsDBNull(5))
{
donHangDetail.loaiBia = reader.GetString(5);
}
if (!reader.IsDBNull(6))
{
donHangDetail.loaiGiay = reader.GetString(6);
}
if (!reader.IsDBNull(7))
{
donHangDetail.donGia = (double)reader.GetDecimal(7);
}
if (!reader.IsDBNull(8))
{
donHangDetail.soluong = reader.GetInt32(8);
}
if (!reader.IsDBNull(9))
{
donHangDetail.chietKhau = (double)reader.GetDecimal(9);
}
if (!reader.IsDBNull(10))
{
donHangDetail.thanhTien = (double)reader.GetDecimal(10);
}
if (!reader.IsDBNull(11))
{
donHangDetail.cdcr = reader.GetString(11);
}
donHangDetail.idOrder = orderID;
listDetail.Add(donHangDetail);
}
reader.Close();
return(listDetail);
}
