private void TestForm_Load(object sender, EventArgs e)
{
Hoadon cthd = new Hoadon();
this.lblMaHoaDon.Text = MaHoaDon;
//label1.Text = objHoaDon.GetMaCTPDT(MaHoaDon).MaChiTietPhieuDatTiec;
tbHoaDon = objHoaDon.GetDSDatDichVu_MaHD(MaHoaDon);
dgvGetDSdichvu.DataSource = objHoaDon.GetDSDatDichVu_MaHD(MaHoaDon);
lbDonGia.Text = objHoaDon.GetThongTinBanAn_MaHD(MaHoaDon).DonGiaBan;
cthd = objHoaDon.getChiTietHoaDon(MaHoaDon);
lbTongTienDichVu.Text = cthd.TongTienDichVu;
lbTongTienHoaDon.Text = cthd.TongTienHoaDon;
lbTongTienBan.Text = cthd.TongTienBan;
lbSoLuongBan.Text = (int.Parse(cthd.TongTienBan) / int.Parse(objHoaDon.GetThongTinBanAn_MaHD(MaHoaDon).DonGiaBan)).ToString();
lbTienDatCoc.Text = objHoaDon.GetTienDatCoc_MaHD(MaHoaDon).TienDatCoc;
lbTenChuRe.Text = objHoaDon.GetTenCoDauChuRe_MaHD(MaHoaDon).TenChuRe;
lbTenCoDau.Text = objHoaDon.GetTenCoDauChuRe_MaHD(MaHoaDon).TenCoDau;
lbNgayThanhToan.Text = DateTime.Now.ToString("dd/MM/yyyy");
//lbTongTienHoaDon.Text = int.Parse(objHoaDon.GetThongTinBanAn_MaHD(MaHoaDon).TongTienBan) + int.Parse(objHoaDon.GetTongTienDichVu_MaHD(MaHoaDon).TongTienDichVu) + "";
//lbTongTienThanhToan.Text = int.Parse(objHoaDon.GetThongTinBanAn_MaHD(MaHoaDon).TongTienBan) + int.Parse(objHoaDon.GetTongTienDichVu_MaHD(MaHoaDon).TongTienDichVu) - int.Parse(objHoaDon.GetTienDatCoc_MaHD(MaHoaDon).TienDatCoc) + "";
lbTongTienThanhToan.Text = (int.Parse(cthd.TongTienHoaDon) - int.Parse(objHoaDon.GetTienDatCoc_MaHD(MaHoaDon).TienDatCoc)).ToString();
}
//cập nhật lại thông tin số lượng bàn sử dụng, tiền thanh toán, tiền bàn, tiền phạt, sau khi đã tiến hành lập hoá đơn
public Hoadon getChiTietHoaDon(string MaHD)
{
Hoadon cthd = new Hoadon();
try
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT CT_HD.TongTienDichVu, CT_HD.TongTienHoaDon, CT_HD.TongTienBan FROM CT_HD WHERE (((CT_HD.MaHoaDon)=" + MaHD + "));", conn);
OleDbDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
cthd.MaHoaDon = MaHD;
cthd.TongTienDichVu = rd["TongTienDichVu"].ToString();
cthd.TongTienBan = rd["TongTienBan"].ToString();
cthd.TongTienHoaDon = rd["TongTienHoaDon"].ToString();
rd.Close();
}
conn.Close();
return cthd;
}
catch (Exception)
{
conn.Close();
Console.WriteLine();
return null;
}
}
private void buttonThanhToan_Click(object sender, EventArgs e)
{
string ngaythanhtoan = DateTime.Now.ToString("d");
string makhachhang = objHoaDon.GetTenCoDauChuRe(MaCT_PDT).MaKhachHang;
string tongtienthanhtoan = lbTongTienThanhToan.Text;
string tongtiendichvu = TienDichVu.ToString();
string tongtienhoadon = TongTienHoaDon.ToString();
string tongtienban = lbTongTienBan.Text;
string maphieudattiec = MaCT_PDT;
//string mahoadon = objHoaDon.GetMaHoaDon(MaCT_PDT).MaHoaDon;
Hoadon hd = new Hoadon(makhachhang, ngaythanhtoan, tongtienthanhtoan, maphieudattiec, tongtiendichvu, tongtienhoadon, tongtienban);
//Hoadon hd1 = new Hoadon(mahoadon,maphieudattiec,tongtiendichvu, tongtienhoadon, tongtienban);
if (objHoaDon.AddHoaDon(hd))
{
//xoá luôn chi tiết phiếu đặt tiệc , giữ lại ct_hd để hoá đơn sau khi xoá không quay trở lại danh sách tiệc cưới =))
MessageBox.Show("Lập Hoá Đơn Thành Công");
this.Close();
}
else
{
MessageBox.Show("Thất bại");
}
f.updateDgvDSLapHoaDon();
}
// add hoá đơn và chi tiết hoá đơn
public bool AddHoaDon(Hoadon hd)
{
try
{
if (conn.State != ConnectionState.Open) conn.Open(); //add hoa don
OleDbCommand cmd = new OleDbCommand("INSERT INTO HOADON([MaKhachHang],[NgayThanhToan],[TongTienThanhToan]) VALUES (" + hd.MaKhachHang + ",#" + hd.NgayThanhToan + "#," + hd.TongTienThanhToan + ")", conn);
cmd.ExecuteNonQuery();
Hoadon hd1 = GetMaHD(hd.MaKhachHang, hd.NgayThanhToan); // get mahd, add chi tiet hoa don
OleDbCommand cmd1 = new OleDbCommand("INSERT INTO CT_HD([MaHoaDon],[MaPhieuDatTiec],[TongTienDichVu],[TongTienHoaDon],[TongTienBan]) VALUES (" + hd1.MaHoaDon + "," + hd.MaPhieuDatTiec + "," + hd.TongTienDichVu + "," + hd.TongTienHoaDon + "," + hd.TongTienBan + ")", conn);
conn.Open();
cmd1.ExecuteNonQuery();
conn.Close();
return true;
}
catch (Exception ex)
{
conn.Close();
Console.WriteLine(ex);
return false;
}
}
//btn xoá click
private void button1_Click(object sender, EventArgs e)
{
string mahoadon;
int Curr = dgvDanhSachHoaDon.CurrentCell.RowIndex;
mahoadon = dgvDanhSachHoaDon.Rows[Curr].Cells[0].Value.ToString();
Hoadon hd = new Hoadon(mahoadon);
if (objHoaDon.XoaHoaDon(hd) == true)
{
string maphieudattiec = objHoaDon.GetMaPDT(mahoadon);
objHoaDon.XoaCTPDT(new Hoadon(mahoadon, maphieudattiec));
dgvDanhSachHoaDon.DataSource = objHoaDon.GetDSHoaDonThanhToan();
if (dgvDanhSachHoaDon.Rows.Count == 0)
{
button1.Enabled = false; //btn xoa
buttonChiTietHoaDon.Enabled = false;
}
MessageBox.Show("Xoá Hoá Đơn Thành Công!");
}
else
{
MessageBox.Show("Xoá Hoá Đơn Thất Bại!");
}
}
//xoá hoá đơn bằng mã hoá đơn
public bool XoaHoaDon(Hoadon hd)
{
{
try
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand("DELETE FROM HOADON WHERE (((HOADON.[MaHoaDon])=" + hd.MaHoaDon + "));", conn);
cmd.ExecuteNonQuery();
//OleDbCommand cmd1 = new OleDbCommand("DELETE CT_HD.MaHoaDon FROM CT_HD WHERE (((CT_HD.MaHoaDon)=" + hd.MaHoaDon + "));", conn);
//cmd1.ExecuteNonQuery();
conn.Close();
return true;
}
catch (Exception)
{
conn.Close();
Console.WriteLine();
return false;
}
}
}
//tính tổng tiền dịch vụ theo mahoadon
public Hoadon GetTongTienDichVu_MaHD(string mahd)
{
Hoadon hd = null;
try
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT Sum(DICHVU.DonGiaDichVu*DATDICHVU.SoLuong) AS TongTienDichVu, HOADON.MaHoaDon FROM (DICHVU INNER JOIN (DATDICHVU INNER JOIN CT_PDT ON DATDICHVU.MaPhieuDatTiec = CT_PDT.MaPhieuDatTiec) ON DICHVU.MaDichVu = DATDICHVU.MaDichVu) INNER JOIN HOADON ON CT_PDT.MaKhachHang = HOADON.MaKhachHang GROUP BY CT_PDT.MaPhieuDatTiec, HOADON.MaHoaDon HAVING (((HOADON.MaHoaDon)=@mahoadon));", conn);
cmd.Parameters.Add("@mahoadon", OleDbType.BSTR).Value = mahd;
OleDbDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
hd = new Hoadon();
hd.TongTienDichVu = rd["TongTienDichVu"].ToString();
rd.Close();
}
}
catch (Exception e)
{
conn.Close();
Console.WriteLine(e.ToString());
}
return hd;
}
//tính tổng tiền dịch vụ theo mactpdt
public Hoadon GetTongTienDichVu(string mact_pdt)
{
Hoadon hd = null;
try
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT CT_PDT.MaPhieuDatTiec, Sum([DICHVU].[DonGiaDichVu]*[DATDICHVU].[SoLuong]) AS [TongTienDichVu] FROM DICHVU INNER JOIN (DATDICHVU INNER JOIN CT_PDT ON DATDICHVU.MaPhieuDatTiec = CT_PDT.MaPhieuDatTiec) ON DICHVU.MaDichVu = DATDICHVU.MaDichVu GROUP BY CT_PDT.MaPhieuDatTiec, CT_PDT.MaPhieuDatTiec HAVING (((CT_PDT.MaPhieuDatTiec)=@mactpdt));", conn);
cmd.Parameters.Add("@MaCT_PDT", OleDbType.BSTR).Value = mact_pdt;
OleDbDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
hd = new Hoadon();
hd.TongTienDichVu = rd["TongTienDichVu"].ToString();
rd.Close();
}
}
catch (Exception e)
{
conn.Close();
Console.WriteLine(e.ToString());
}
return hd;
}
//lấy thông tin tiền đặt cọc theo mahd
public Hoadon GetTienDatCoc_MaHD(string mahd)
{
Hoadon hd = null;
try
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT CT_PDT.TienDatCoc, HOADON.MaHoaDon FROM (KHACHHANG INNER JOIN CT_PDT ON KHACHHANG.MaKhachHang = CT_PDT.MaKhachHang) INNER JOIN HOADON ON KHACHHANG.MaKhachHang = HOADON.MaKhachHang WHERE (((HOADON.MaHoaDon)=@mahoadon));", conn);
cmd.Parameters.Add("@mahoadon", OleDbType.BSTR).Value = mahd;
OleDbDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
hd = new Hoadon();
hd.TienDatCoc = rd["TienDatCoc"].ToString();
rd.Close();
}
}
catch (Exception e)
{
conn.Close();
Console.WriteLine(e.ToString());
}
return hd;
}
//lấy thông tin tiền đặt cọc theo mapctdt
public Hoadon GetTienDatCoc(string mact_pdt)
{
Hoadon hd = null;
try
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT CT_PDT.TienDatCoc, CT_PDT.MaPhieuDatTiec FROM KHACHHANG INNER JOIN CT_PDT ON KHACHHANG.MaKhachHang = CT_PDT.MaKhachHang WHERE (((CT_PDT.MaPhieuDatTiec)=@mact_pdt));", conn);
cmd.Parameters.Add("@MaCT_PDT", OleDbType.BSTR).Value = mact_pdt;
OleDbDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
hd = new Hoadon();
hd.TienDatCoc = rd["TienDatCoc"].ToString();
rd.Close();
}
}
catch (Exception e)
{
conn.Close();
Console.WriteLine(e.ToString());
}
return hd;
}
// lấy thông tin về ca, sảnh, ngày tổ chức, số lượng bàn, số lượng bàn dự trữ theo mact_pdt
public Hoadon GetThongTinCaSanh(string mact_pdt)
{
Hoadon hd = null;
try
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT KHACHHANG.TenChuRe, KHACHHANG.TenCoDau, SANH.TenSanh, LOAICA.TenLoaiCa AS Ca, PHIEUDATTIEC.NgayToChuc, CT_PDT.SoLuongBan, CT_PDT.SoBanDuTru, KHACHHANG.DienThoai FROM (LOAISANH INNER JOIN SANH ON LOAISANH.MaLoaiSanh = SANH.MaLoaiSanh) INNER JOIN ((LOAICA INNER JOIN PHIEUDATTIEC ON LOAICA.MaLoaiCa = PHIEUDATTIEC.MaLoaiCa) INNER JOIN (KHACHHANG INNER JOIN CT_PDT ON KHACHHANG.MaKhachHang = CT_PDT.MaKhachHang) ON PHIEUDATTIEC.MaPhieuDatTiec = CT_PDT.MaPhieuDatTiec) ON SANH.MaSanh = PHIEUDATTIEC.MaSanh WHERE (((CT_PDT.MaPhieuDatTiec)=@MaCT_PDT));", conn);
cmd.Parameters.Add("@MaCT_PDT", OleDbType.BSTR).Value = mact_pdt;
OleDbDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
hd = new Hoadon();
hd.TenChuRe = rd["TenChuRe"].ToString();
hd.TenCoDau = rd["TenCoDau"].ToString();
hd.TenSanh = rd["TenSanh"].ToString();
hd.Ca = rd["Ca"].ToString();
hd.NgayToChuc = rd["NgayToChuc"].ToString();
hd.SoLuongBan = rd["SoLuongBan"].ToString();
hd.SoBanDuTru = rd["SoBanDuTru"].ToString();
hd.SoDienThoai = rd["DienThoai"].ToString();
rd.Close();
}
}
catch (Exception e)
{
conn.Close();
Console.WriteLine(e.ToString());
}
return hd;
}
//lấy thông tin về số lượng bàn, đơn giá bàn, tổng tiền bàn theo mahd
public Hoadon GetThongTinBanAn_MaHD(string mahd)
{
Hoadon hd = null;
try
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT Sum(MONAN.DonGiaMonAn) AS DonGiaBanAn, CT_PDT.SoLuongBan, [ct_PDT].[soLuongBan]*DonGiaBanAn AS TongTienBan FROM ((LOAISANH INNER JOIN SANH ON LOAISANH.MaLoaiSanh = SANH.MaLoaiSanh) INNER JOIN (PHIEUDATTIEC INNER JOIN (MONAN INNER JOIN (DATMONAN INNER JOIN CT_PDT ON DATMONAN.MaPhieuDatTiec = CT_PDT.MaPhieuDatTiec) ON MONAN.MaMonAn = DATMONAN.MaMonAn) ON PHIEUDATTIEC.MaPhieuDatTiec = CT_PDT.MaPhieuDatTiec) ON SANH.MaSanh = PHIEUDATTIEC.MaSanh) INNER JOIN HOADON ON CT_PDT.MaKhachHang = HOADON.MaKhachHang GROUP BY CT_PDT.SoLuongBan, CT_PDT.MaPhieuDatTiec, LOAISANH.DonGiaBanToiThieu, HOADON.MaHoaDon HAVING (((Sum(MONAN.DonGiaMonAn))>=([LOAISANH].[DonGiaBanToiThieu])) AND ((HOADON.MaHoaDon)=@mahoadon)); ", conn);
cmd.Parameters.Add("@mahoadon", OleDbType.BSTR).Value = mahd;
OleDbDataReader rd = cmd.ExecuteReader();
rd.Read();
hd = new Hoadon();
hd.DonGiaBan = rd["DonGiaBanAn"].ToString();
hd.SoLuongBan = rd["SoLuongBan"].ToString();
hd.TongTienBan = int.Parse(hd.DonGiaBan) * int.Parse(hd.SoLuongBan) + "";
rd.Close();
return hd;
}
catch (Exception e)
{
conn.Close();
Console.WriteLine(e.ToString());
MessageBox.Show(e.ToString());
}
return hd;
}
//get mãhd từ mãkh , support cho hàm addhoadon
public Hoadon GetMaHD(string makh, string ngaytt)
{
Hoadon hd = new Hoadon();
try
{
if (conn.State != ConnectionState.Open) conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT HOADON.MaHoaDon FROM HOADON WHERE (((HOADON.MaKhachHang)=" + makh + ") AND ((HOADON.NgayThanhToan)=#" + ngaytt + "#))", conn);
OleDbDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
hd.MaHoaDon = rd["MaHoaDon"].ToString();
rd.Close();
}
conn.Close();
return hd;
}
catch (Exception ex)
{
conn.Close();
Console.WriteLine(ex);
return null;
}
}
public bool AddHoaDon(Hoadon hd)
{
return objhd.AddHoaDon(hd);
}
public bool XoaHoaDon(Hoadon hd)
{
return objhd.XoaHoaDon(hd);
}
public bool XoaCTPDT(Hoadon hd)
{
return objhd.XoaCT_PDT(hd);
}
