void CreateDTThang()
{
string dateDTNgay = Convert.ToDateTime(SQLConnection.SelectOne("ThoiGian", "DoanhThu_Ngay", "IDPhieuDatPhong = " + _IDPhieuDatPhong))
.ToString("MM/yyyy");
string _ID_DTNgay = SQLConnection.SelectOne("ID_DTNgay", "DoanhThu_Ngay", "IDPhieuDatPhong = " + _IDPhieuDatPhong);
SqlConnection con = SQLConnection.GetConnection();
using (con)
{
con.Open();
SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) FROM DoanhThu_Thang WHERE ThoiGian LIKE '" + dateDTNgay + "'", con);
int result = int.Parse(sqlCommand.ExecuteScalar().ToString());
if (result == 0)
{
int id = SQLConnection.IncrID("DoanhThu_Thang", "ID_DTThang");
string insCol = "ID_DTThang, ThoiGian, ID_DTNgay";
string insVal = id + ", '" + dateDTNgay + "', " + _ID_DTNgay;
SQLConnection.Insert("DoanhThu_Thang", insCol, insVal);
}
else
{
sqlCommand = new SqlCommand("SELECT COUNT(*) FROM DoanhThu_Thang WHERE ID_DTNgay = " + _ID_DTNgay, con);
result = int.Parse(sqlCommand.ExecuteScalar().ToString());
if (result == 0)
{
int id = int.Parse(SQLConnection.SelectOne("ID_DTThang", "DoanhThu_Thang", "ThoiGian LIKE '" + dateDTNgay + "'"));
string insCol = "ID_DTThang, ThoiGian, ID_DTNgay";
string insVal = id + ", '" + dateDTNgay + "', " + _ID_DTNgay;
SQLConnection.Insert("DoanhThu_Thang", insCol, insVal);
}
}
}
}
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 InsertDTNgay()
{
string ngayDi = Convert.ToDateTime(SQLConnection.SelectOne("NgayDi", "PhieuDatPhong", "IDPhieuDatPhong = " + _IDPhieuDatPhong))
.ToString("MM/dd/yyyy");
SqlConnection con = SQLConnection.GetConnection();
using (con)
{
con.Open();
SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) FROM DoanhThu_Ngay WHERE ThoiGian = '" + ngayDi + "'", con);
int result = int.Parse(sqlCommand.ExecuteScalar().ToString());
int id;
if (result == 0)
{
id = SQLConnection.IncrID("DoanhThu_Ngay", "ID_DTNgay");
}
else
{
id = int.Parse(SQLConnection.SelectOne("ID_DTNgay", "DoanhThu_Ngay", "ThoiGian = '" + ngayDi + "'"));
}
string insCol = "ID_DTNgay, ThoiGian, IDPhieuDatPhong";
string insVal = id + ", '" + ngayDi + "', " + _IDPhieuDatPhong;
SQLConnection.Insert("DoanhThu_Ngay", insCol, insVal);
}
}
private void btnAccept_Click(object sender, EventArgs e)
{
try
{
string where1 = "IDPhong = N'" + soPhongCu + "'";
DateTime checkIn = Convert.ToDateTime(SQLConnection.SelectOne("NgayDen", "PhieuDatPhong", where1));
DateTime checkOut = Convert.ToDateTime(SQLConnection.SelectOne("NgayDi", "PhieuDatPhong", where1));
int diffDays = SoNgayO(checkIn, checkOut);
string where2 = "IDPhong = N'" + cbPhongMoi.SelectedValue.ToString() + "'";
int tongPhongMoi = diffDays * Convert.ToInt32(SQLConnection.SelectOne("GiaPhong", "Phong", where2));
string _IDPhieuDatPhong = SQLConnection.SelectOne("IDPhieuDatPhong", "PhieuDatPhong", where1);
string where3 = "IDPhieuDatPhong = " + _IDPhieuDatPhong;
int phatDoiPhong = int.Parse(SQLConnection.SelectOne("PhiDoiPhong", "PhieuDatPhong", where1)) + int.Parse(SQLConnection.SelectTS("TS_DoiPhongPhat"));
int soLanDoiPhong = int.Parse(SQLConnection.SelectOne("SoLanDoiPhong", "PhieuDatPhong", where1)) + 1;
string val = "PhiDoiPhong = " + phatDoiPhong + ", SoLanDoiPhong = " + soLanDoiPhong +
", IDPhong = N'" + cbPhongMoi.SelectedValue.ToString() + "'" +
", TongCong = " + tongPhongMoi;
SQLConnection.Update("PhieuDatPhong", val, where3);
string valTrong = "TinhTrang = 0";
string valCoNguoi = "TinhTrang = 1";
SQLConnection.Update("Phong", valTrong, where1);
SQLConnection.Update("Phong", valCoNguoi, where2);
MessageBox.Show("Thành Công!", "Đổi phòng", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Lỗi!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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());
}
public DoiPhong(string soPhong)
{
InitializeComponent();
lblPhong.Text = "Phòng cũ: " + soPhong;
soPhongCu = soPhong;
string where1 = "IDPhong = N'" + soPhongCu + "'";
if (int.Parse(SQLConnection.SelectOne("SoLanDoiPhong", "PhieuDatPhong", where1)) >= int.Parse(SQLConnection.SelectTS("TS_SoLanDoiPhong")))
{
MessageBox.Show("Vượt quá số lần đổi cho phép", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
LockControl();
}
}
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 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);
}
}