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); } }