void GetData_PV(string idPDP)
{
lvService.Items.Clear();
lvFood.Items.Clear();
string where = "IDPhieuDatPhong = " + idPDP + "";
DataTable dt = SQLConnection.SelectWhere("*", "Add_DichVu", where);
foreach (DataRow dr in dt.Rows)
{
string ten = SQLConnection.SelectOne("TenDichVu", "DichVu", "IDDichVu = " + dr["IDDichVu"].ToString());
ListViewItem i = new ListViewItem(ten);
i.SubItems.Add(dr["SoLuong"].ToString());
i.SubItems.Add(dr["ThanhTien"].ToString());
i.SubItems.Add("DV");
i.SubItems.Add(dr["IDDatDV"].ToString());
lvService.Items.Add(i);
}
dt = SQLConnection.SelectWhere("*", "Add_MonAn", where);
foreach (DataRow dr in dt.Rows)
{
string ten = SQLConnection.SelectOne("TenMonAn", "MonAn", "IDMonAn = " + dr["IDMonAn"].ToString());
ListViewItem i = new ListViewItem(ten);
i.SubItems.Add(dr["SoLuong"].ToString());
i.SubItems.Add(dr["ThanhTien"].ToString());
i.SubItems.Add("MA");
i.SubItems.Add(dr["IDDatMA"].ToString());
lvFood.Items.Add(i);
}
}
void LoadCBPhong(string soPhong)
{
string where = "TinhTrang = 0";
DataTable dt = SQLConnection.SelectWhere("*", "Phong", where);
cbSoPhong.DataSource = dt;
cbSoPhong.DisplayMember = "IDPhong";
cbSoPhong.ValueMember = "IDPhong";
try
{
int i = 0;
foreach (DataRow dr in dt.Rows)
{
if (dr["IDPhong"].ToString() == soPhong)
{
break;
}
i++;
}
cbSoPhong.SelectedIndex = i;
}
catch
{
MessageBox.Show("Hết phòng trống");
foreach (Control c in this.Controls)
{
c.Enabled = false;
}
}
LoadPhong(soPhong);
}
void GetData_KH(string idKH)
{
string where = "IDKhachHang LIKE '" + idKH + "'";
DataTable dt = SQLConnection.SelectWhere("*", "KhachHang", where);
DataRow dr = dt.Rows[0];
lblTenKH.Text = "Tên Khách Hàng: " + dr["TenKhachHang"].ToString();
lblCMND.Text = "CMND: " + dr["CMND"].ToString();
}
void LoadData()
{
string date = dtpThang.Value.ToString("MM/yyyy");
try
{
int sum = 0;
string where = "ThoiGian LIKE '" + date + "'";
DataTable dt = SQLConnection.SelectWhere("*", "DOANHTHU_Thang", where);
lvThongKe.Items.Clear();
foreach (DataRow dr in dt.Rows)
{
string _ID_DTNgay = dr["ID_DTNGay"].ToString();
int tongPhong = 0;
int tongDV = 0;
int tongCong = 0;
string wherePDP = "";
string whereNgay = "ID_DTNGay = " + _ID_DTNgay;
DataTable dtNgay = SQLConnection.SelectWhere("*", "DOANHTHU_Ngay", whereNgay);
foreach (DataRow drN in dtNgay.Rows)
{
wherePDP = "IDPhieuDatPhong = " + drN["IDPhieuDatPhong"].ToString();
int tienPhong = int.Parse(SQLConnection.SelectOne("TongCong", "PhieuDatPhong", wherePDP))
+ int.Parse(SQLConnection.SelectOne("PhiDoiPhong", "PhieuDatPhong", wherePDP));
int tienDV = int.Parse(SQLConnection.SelectOne("Phi_MonAn", "PhieuDatPhong", wherePDP))
+ int.Parse(SQLConnection.SelectOne("Phi_DichVu", "PhieuDatPhong", wherePDP));
int valCoupon;
if (SQLConnection.isExist("MaGiamGia", "IDMaGiamGia", SQLConnection.SelectOne("IDMaGiamGia", "PhieuDatPhong", wherePDP)))
{
valCoupon = int.Parse(SQLConnection.SelectOne("HieuQua", "MaGiamGia", "IDMaGiamGia = N'" + dr["IDMaGiamGia"].ToString() + "'"));
}
else
{
valCoupon = 0;
}
int discount = (tienPhong + tienDV) * valCoupon / 100;
int tongTien = tienDV + tienPhong - discount;
tongPhong += tienPhong;
tongDV += tienDV;
tongCong += tongTien;
sum += tongCong;
}
ListViewItem i = new ListViewItem(SQLConnection.SelectOne("ThoiGian", "DOANHTHU_Ngay", whereNgay));
i.SubItems.Add(tongPhong.ToString());
i.SubItems.Add(tongDV.ToString());
i.SubItems.Add(tongCong.ToString());
lvThongKe.Items.Add(i);
}
lblTongCong.Text = "Tổng cộng: " + sum;
}
catch
{
}
}
void FillData(string soPhong)
{
soPhongDat = soPhong;
string where = "IDPHONG LIKE '" + soPhong + "'";
DataTable dt = SQLConnection.SelectWhere("*", "PhieuDatPhong", where);
DataRow dr = dt.Rows[0];
GetData_KH(dr["IDKhachHang"].ToString());
_IDKhachHang = dr["IDKhachHang"].ToString();
lblSoPhong.Text = "Số phòng: " + dr["IDPhong"].ToString();
lblNgayDi.Text = "Ngày đi: " + dr["NgayDi"].ToString();
lblNgayDen.Text = "Ngày đến: " + dr["NgayDen"].ToString();
lblTienPhong.Text = "Tổng tiền phòng: " + dr["TongCong"].ToString();
if (int.Parse(dr["PhiDoiPhong"].ToString()) > 0)
{
lblTienPhong.Text += " + " + dr["PhiDoiPhong"].ToString();
}
tongCong += int.Parse(dr["TongCong"].ToString()) + int.Parse(dr["PhiDoiPhong"].ToString());
lblTienService.Text = "Tổng tiền dịch vụ: " + dr["Phi_DichVu"].ToString();
tongCong += int.Parse(dr["Phi_DichVu"].ToString());
lblTienFood.Text = "Tổng tiền món ăn: " + dr["Phi_MonAn"].ToString();
tongCong += int.Parse(dr["Phi_MonAn"].ToString());
doanhthu += tongCong;
lblDatCoc.Text = "Đặt cọc: " + dr["SoTienCoc"].ToString();
giamGia += int.Parse(dr["SoTienCoc"].ToString());
int valCoupon;
if (SQLConnection.isExist("MaGiamGia", "IDMaGiamGia", dr["IDMaGiamGia"].ToString()))
{
valCoupon = int.Parse(SQLConnection.SelectOne("HieuQua", "MaGiamGia", "IDMaGiamGia = N'" + dr["IDMaGiamGia"].ToString() + "'"));
}
else
{
valCoupon = 0;
}
int discount = tongCong * valCoupon / 100;
lblGiamGia.Text = "Giảm giá: " + discount.ToString();
giamGia += discount;
lblThanhTien.Text = (tongCong - giamGia).ToString("N0",
System.Globalization.CultureInfo.GetCultureInfo("de"));
doanhthu -= discount;
_IDPhieuDatPhong = dr["IDPhieuDatPhong"].ToString();
GetData_PV(dr["IDPhieuDatPhong"].ToString());
}
void LoadCBCoupon()
{
string today = DateTime.Now.ToString();
DataTable dt = SQLConnection.SelectWhere("IDMaGiamGia", "MaGiamGia", "HanSuDung >= '" + today + "'");
DataRow dr = dt.NewRow();
dr["IDMaGiamGia"] = "Không có";
dt.Rows.InsertAt(dr, 0);
cbCoupon.DataSource = dt;
cbCoupon.DisplayMember = "IDMaGiamGia";
cbCoupon.ValueMember = "IDMaGiamGia";
cbCoupon.SelectedIndex = 0;
}
void LoadCBCMND()
{
string where = "IDPhieuDatPhong IS NULL";
DataTable dt = SQLConnection.SelectWhere("*", "KhachHang", where);
DataRow dr = dt.NewRow();
dr["CMND"] = "Thêm mới";
dt.Rows.InsertAt(dr, 0);
cbCMND.DataSource = dt;
cbCMND.DisplayMember = "CMND";
cbCMND.ValueMember = "IDKhachHang";
cbCMND.SelectedIndex = 0;
}
void LoadPhong(string soPhong)
{
string where = "IDPhong LIKE N'" + soPhong + "'";
DataTable dt = SQLConnection.SelectWhere("*", "Phong", where);
foreach (DataRow dr in dt.Rows)
{
lblLoaiPhong.Text = "Loại Phòng: " + dr["IDPhong"].ToString().Remove(1);
dtpNgayDi.Value = DateTime.Now.AddDays(1);
lblDonGia.Text = "Đơn Giá: " + dr["GiaPhong"].ToString();
lblTongTien.Text = TongTien(dtpNgayDi.Value).ToString();
}
}
void LoadData()
{
string date = dtpNgay.Value.ToString("MM/dd/yyyy");
try
{
int sum = 0;
string where = "ThoiGian = '" + date + "'";
DataTable dt = SQLConnection.SelectWhere("*", "DOANHTHU_NGAY", where);
lvThongKe.Items.Clear();
foreach (DataRow dr in dt.Rows)
{
string _IDPhieuDatPhong = dr["IDPhieuDatPhong"].ToString();
string wherePDP = "IDPhieuDatPhong = " + _IDPhieuDatPhong;
string idPhong = SQLConnection.SelectOne("IDPhong", "PhieuDatPhong", wherePDP);
string ngayDen = SQLConnection.SelectOne("NgayDen", "PhieuDatPhong", wherePDP);
int tienPhong = int.Parse(SQLConnection.SelectOne("TongCong", "PhieuDatPhong", wherePDP))
+ int.Parse(SQLConnection.SelectOne("PhiDoiPhong", "PhieuDatPhong", wherePDP));
int tienDV = int.Parse(SQLConnection.SelectOne("Phi_MonAn", "PhieuDatPhong", wherePDP))
+ int.Parse(SQLConnection.SelectOne("Phi_DichVu", "PhieuDatPhong", wherePDP));
int valCoupon;
if (SQLConnection.isExist("MaGiamGia", "IDMaGiamGia", SQLConnection.SelectOne("IDMaGiamGia", "PhieuDatPhong", wherePDP)))
{
valCoupon = int.Parse(SQLConnection.SelectOne("HieuQua", "MaGiamGia", "IDMaGiamGia = N'" + dr["IDMaGiamGia"].ToString() + "'"));
}
else
{
valCoupon = 0;
}
int discount = (tienPhong + tienDV) * valCoupon / 100;
int tongCong = tienDV + tienPhong - discount;
sum += tongCong;
ListViewItem i = new ListViewItem(idPhong);
i.SubItems.Add(ngayDen);
i.SubItems.Add(tienPhong.ToString());
i.SubItems.Add(tienDV.ToString());
i.SubItems.Add(tongCong.ToString());
lvThongKe.Items.Add(i);
}
lblTongCong.Text = "Tổng cộng: " + sum;
}
catch
{
}
}
void LoadCBPhong()
{
string where = "TinhTrang = 0";
DataTable dt = SQLConnection.SelectWhere("*", "Phong", where);
cbPhongMoi.DataSource = dt;
cbPhongMoi.DisplayMember = "IDPhong";
cbPhongMoi.ValueMember = "IDPhong";
try
{
cbPhongMoi.SelectedIndex = 0;
}
catch
{
MessageBox.Show("Hết phòng trống", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
LockControl();
}
}
void LoadData()
{
try
{
int sum = 0;
string where = "DaThanhToan = 0";
DataTable dt = SQLConnection.SelectWhere("*", "PhieuDatPhong", where);
lvThongKe.Items.Clear();
foreach (DataRow dr in dt.Rows)
{
string _IDPhieuDatPhong = dr["IDPhieuDatPhong"].ToString();
string wherePDP = "IDPhieuDatPhong = " + _IDPhieuDatPhong;
string tenKH = SQLConnection.SelectOne("TenKhachHang", "KhachHang", wherePDP);
int tienPhong = int.Parse(dr["TongCong"].ToString()) + int.Parse(dr["PhiDoiPhong"].ToString());
int tienDV = int.Parse(dr["Phi_DichVu"].ToString()) + int.Parse(dr["Phi_MonAn"].ToString());
int valCoupon;
if (SQLConnection.isExist("MaGiamGia", "IDMaGiamGia", SQLConnection.SelectOne("IDMaGiamGia", "PhieuDatPhong", wherePDP)))
{
valCoupon = int.Parse(SQLConnection.SelectOne("HieuQua", "MaGiamGia", "IDMaGiamGia = N'" + dr["IDMaGiamGia"].ToString() + "'"));
}
else
{
valCoupon = 0;
}
int discount = (tienPhong + tienDV) * valCoupon / 100;
int tongCong = (tienDV + tienPhong - discount) - int.Parse(dr["SoTienCoc"].ToString());
sum += tongCong;
ListViewItem i = new ListViewItem(dr["NgayDi"].ToString());
i.SubItems.Add(dr["IDPhong"].ToString());
i.SubItems.Add(tenKH);
i.SubItems.Add(dr["SoTienCoc"].ToString());
i.SubItems.Add(tongCong.ToString());
lvThongKe.Items.Add(i);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void lvDSKH_SelectedIndexChanged(object sender, EventArgs e)
{
if (lvDSKH.SelectedItems.Count == 0)
{
return;
}
ListViewItem item = lvDSKH.SelectedItems[0];
string where = "IDKhachHang LIKE '" + item.SubItems[2].Text.ToString() + "'";
DataTable dt = SQLConnection.SelectWhere("*", "KhachHang", where);
Customer customer;
foreach (DataRow dr in dt.Rows)
{
bool type = dr["LoaiKhachHang"].ToString() == "False" ? false : true;
customer = new Customer(dr["TenKhachHang"].ToString(),
dr["CMND"].ToString(), dr["SDT"].ToString(), dr["Mail"].ToString(), type, dr["IDKhachHang"].ToString());
pageCustomerInfo.Customer = customer;
}
}