public static DataTable LayTatCaChiTietDatPhong_DataTable(int maDatPhong)
{
DataTable res = DataProvider.ExecuseQuery("SELECT * FROM quanlykaraoke.chitietdatphong,quanlykaraoke.loaiphong,quanlykaraoke.phong WHERE chitietdatphong.MaPhong=phong.MaPhong AND phong.MaLoaiPhong = loaiphong.MaLP AND chitietdatphong.MaDatPhong= '" + maDatPhong + " ' ;");
res.TableName = "DsPhongDat";
return(res);
}
public static DataTable LayTatCaDichVuDatPhong_DichVu_LoaiDV(int maDatPhong)
{
DataTable res = DataProvider.ExecuseQuery("SELECT * FROM quanlykaraoke.dichvu,quanlykaraoke.dichvudatphong,quanlykaraoke.chitietdatphong WHERE quanlykaraoke.dichvudatphong.MaChiTietDatPhong =quanlykaraoke.chitietdatphong.MaChiTietDatPhong AND quanlykaraoke.dichvudatphong.MaDichVu = quanlykaraoke.dichvu.MaDV AND quanlykaraoke.chitietdatphong.MaDatPhong='" + maDatPhong + " '; ");
res.TableName = "dichvu";
return(res);
}
public static List <DatPhongDTO> LayTatCaCacDatPhong(int maPhong)
{
List <DatPhongDTO> listDatPhong = new List <DatPhongDTO>();
foreach (DataRow row in DataProvider.ExecuseQuery("SELECT * FROM datphong, chitietdatphong WHERE datphong.MaDatPhong = chitietdatphong.MaDatPhong AND chitietdatphong.MaPhong = '" + maPhong + "';").Rows)
{
listDatPhong.Add(new DatPhongDTO((int)row["MaDatPhong"], DateTime.Parse(row["ThoiGianDatPhong"].ToString()), (double)row["SoTienDatTruoc"], (int)row["MaNhanVien"], (int)row["MaKH"], row["GhiChu"].ToString(), (int)row["MaTinhTrangDatPhong"], DateTime.Parse(row["NgayLapPhieuDat"].ToString())));
}
return(listDatPhong);
}
public static int PhatSinhMaHoaDon()
{
DataTable dt = DataProvider.ExecuseQuery("SELECT quanlykaraoke.hoadon.MaHoaDon FROM quanlykaraoke.hoadon ORDER BY quanlykaraoke.hoadon.MaHoaDon DESC LIMIT 1;");
if (dt.Rows.Count == 0)
{
return(1);
}
else
{
return(Convert.ToInt32(dt.Rows[0][0]) + 1);
}
}
public static int PhatSinhMaTienGio_ThuePhong()
{
DataTable dt = DataProvider.ExecuseQuery("SELECT quanlykaraoke.tiengio_thuephong.MaTienGio_ThuePhong FROM quanlykaraoke.tiengio_thuephong ORDER BY quanlykaraoke.tiengio_thuephong.MaTienGio_ThuePhong DESC LIMIT 1;");
if (dt.Rows.Count == 0)
{
return(1);
}
else
{
return(Convert.ToInt32(dt.Rows[0][0]) + 1);
}
}
public static int PhatSinhMaDichVu()
{
DataTable dt = DataProvider.ExecuseQuery("SELECT quanlykaraoke.dichvu.MaDV FROM quanlykaraoke.dichvu ORDER BY quanlykaraoke.dichvu.MaDV DESC LIMIT 1;");
if (dt.Rows.Count == 0)
{
return(1);
}
else
{
return(Convert.ToInt32(dt.Rows[0][0]) + 1);
}
}
public static int PhatSinhMaKH()
{
DataTable dt = DataProvider.ExecuseQuery("SELECT quanlykaraoke.khachhang.MaKH FROM quanlykaraoke.khachhang ORDER BY quanlykaraoke.khachhang.MaKH DESC LIMIT 1;");
if (dt.Rows.Count == 0)
{
return(1);
}
else
{
return(Convert.ToInt32(dt.Rows[0][0]) + 1);
}
}
public static DateTime LayThoiDiemDatPhongGanNhat_Truoc(int maPhong, DateTime thoiDiem)
{
DataTable dt = DataProvider.ExecuseQuery("SELECT ThoiGianDatPhong FROM datphong, chitietdatphong WHERE datphong.MaDatPhong = chitietdatphong.MaDatPhong AND MaTinhTrangDatPhong = '1' AND ThoiGianDatPhong < '" + thoiDiem.ToString("yyyy-MM-dd HH:mm:ss.fff") + "' AND MaPhong = '" + maPhong + "' ORDER BY ThoiGianDatPhong DESC LIMIT 1 ;");
if (dt.Rows.Count == 0)
{
return(DateTime.MinValue);
}
else
{
return(DateTime.Parse(dt.Rows[0]["ThoiGianDatPhong"].ToString()));
}
}
public static Dictionary <string, DonGiaTheoNgay> LayDonGia(LoaiPhongDTO loaiPhongDTO)
{
DataTable dt = DataProvider.ExecuseQuery("SELECT * FROM quanlykaraoke.dongia_loaiphong WHERE quanlykaraoke.dongia_loaiphong.MaLoaiPhong = '" + loaiPhongDTO.MaLoaiPhong + "'");
Dictionary <string, DonGiaTheoNgay> res = new Dictionary <string, DonGiaTheoNgay>();
foreach (DataRow row in dt.Rows)
{
if (!res.Keys.Contains(row["Thu"]))
{
res.Add(row["Thu"].ToString(), new DonGiaTheoNgay(row["Thu"].ToString()));
}
res[row["Thu"].ToString()].listDonGiaTheoKhoangThoiGian.Add(new DonGiaTheoKhoangThoiGian(new ThongTinThanhToanTheoNgay.Gio((TimeSpan)row["GioBatDau"]), new ThongTinThanhToanTheoNgay.Gio((TimeSpan)row["GioKetThuc"]), (double)row["DonGia"]));
}
return(res);
}
public static int LayKhoangThoiGianToiThieuGiuaHaiLanThue()
{
return(int.Parse(DataProvider.ExecuseQuery("SELECT * FROM quanlykaraoke.thamso WHERE MaThamSo = '1';").Rows[0]["GiaTri"].ToString()));
}
public static int GetNamThanhToanDauTien()
{
return(Convert.ToInt32(DataProvider.ExecuseQuery("SELECT Min(Year(NgayThanhToan)) FROM hoadon WHERE NgayThanhToan is not nULL GROUP BY Year(NgayThanhToan) ;").Rows[0][0]));
}
public static DatPhongDTO LayThongTinDatPhong(int maDatPhong)
{
DataTable dt = DataProvider.ExecuseQuery("SELECT * FROM datphong WHERE datphong.MaDatPhong = '" + maDatPhong + "';");
return(new DatPhongDTO((int)dt.Rows[0]["MaDatPhong"], DateTime.Parse(dt.Rows[0]["ThoiGianDatPhong"].ToString()), (double)dt.Rows[0]["SoTienDatTruoc"], (int)dt.Rows[0]["MaNhanVien"], (int)dt.Rows[0]["MaKH"], dt.Rows[0]["GhiChu"].ToString(), (int)dt.Rows[0]["MaTinhTrangDatPhong"], DateTime.Parse(dt.Rows[0]["NgayLapPhieuDat"].ToString())));
}
public static int DemSoLuongDichVu(int maLoaiDV)
{
return(Convert.ToInt32(DataProvider.ExecuseQuery("SELECT Count(*) FROM quanlykaraoke.dichvu WHERE quanlykaraoke.dichvu.MaLDV = '" + maLoaiDV.ToString() + "';").Rows[0][0]));
}
public static bool LayChuyenSangChoDonDepSauKhiThanhToan()
{
return(Convert.ToBoolean(DataProvider.ExecuseQuery("SELECT * FROM quanlykaraoke.thamso WHERE MaThamSo = '3';").Rows[0]["GiaTri"].ToString()));
}
public static DataTable LayTatCaCacChiTietDatPhong_table(int maDatPhong)
{
return(DataProvider.ExecuseQuery("SELECT * FROM chitietdatphong WHERE MaDatPhong = '" + maDatPhong + "';"));
}
public static DataTable LayTatCaDonGia_DataTable()
{
return(DataProvider.ExecuseQuery("SELECT * FROM quanlykaraoke.dongia_loaiphong "));
}
public static DataTable LayTienGio_ThuePhong_DataTable(int maThuePhong)
{
return(DataProvider.ExecuseQuery("SELECT * FROM tiengio_thuephong WHERE tiengio_thuephong.MaThuePhong = '" + maThuePhong + "';"));
}
public static int PhatSinhMaPhong()
{
return(Convert.ToInt32(DataProvider.ExecuseQuery("SELECT Count(*) + 1 FROM quanlykaraoke.phong;").Rows[0][0]));
}
public static int DemSoLuongThuePhong(int maHoaDon)
{
return(Convert.ToInt32(DataProvider.ExecuseQuery("SELECT COUNT(*) FROM quanlykaraoke.hoadon, quanlykaraoke.thuephong WHERE hoadon.MaHoaDon = thuephong.MaHoaDon AND thuephong.MaHoaDon = '" + maHoaDon + "' ;").Rows[0][0]));
}
public static int DemSoLuongChiTietDatPhong(int maDatPhong)
{
return(Convert.ToInt32(DataProvider.ExecuseQuery("SELECT Count(*) FROM chitietdatphong WHERE MaDatPhong = '" + maDatPhong + "' ;").Rows[0][0]));
}
public static TinhTrangDatPhongDTO LayTinhTrangPhong(int maTinhTrangPhong)
{
DataRow row = DataProvider.ExecuseQuery("SELECT * FROM tinhtrangdatphong WHERE tinhtrangdatphong.MaTinhTrangDatPhong = '" + maTinhTrangPhong + "';").Rows[0];
return(new TinhTrangDatPhongDTO((int)row["MaTinhTrangDatPhong"], row["TinhTrangDatPhong"].ToString()));
}
public static DataTable LayTatCaCacDatPhongTrongTuongLai(int maPhong)
{
return(DataProvider.ExecuseQuery("SELECT datphong.MaDatPhong, datphong.ThoiGianDatPhong, datphong.MaTinhTrangDatPhong FROM quanlykaraoke.datphong, quanlykaraoke.chitietdatphong WHERE quanlykaraoke.datphong.MaDatPhong = quanlykaraoke.chitietdatphong.MaDatPhong AND quanlykaraoke.datphong.ThoiGianDatPhong > now() AND datphong.MaTinhTrangDatPhong = '1' AND MaPhong = '" + maPhong + "';"));
}
public static DataTable LayTatCaChiTietDatPhong_DataTable()
{
return(DataProvider.ExecuseQuery("SELECT * FROM chitietdatphong, phong WHERE chitietdatphong.MaPhong = phong.MaPhong;"));
}
public static DataTable LayThongTinDatPhong_table(int maDatPhong)
{
return(DataProvider.ExecuseQuery("SELECT * FROM datphong WHERE datphong.MaDatPhong = '" + maDatPhong + "';"));
}
public static double GetTyLeQuyDoiDiemTien()
{
return(Double.Parse(DataProvider.ExecuseQuery("SELECT * FROM quanlykaraoke.thamso WHERE MaThamSo = '0';").Rows[0]["GiaTri"].ToString()));
}
public static DataTable LayTatCaDichVu_Phong_DichVuPhong_DaThanhToan(DateTime dateTime)
{
return(DataProvider.ExecuseQuery("SELECT thuephong.MaHoaDon, dichvu.TenDV, dichvuphong.ThoiGian, dichvuphong.SoLuong, dichvuphong.Gia, phong.TenPhong, dichvu.MaLDV FROM thuephong, dichvuphong, phong, dichvu WHERE thuephong.MaThuePhong = dichvuphong.MaThuePhong AND thuephong.MaPhong = phong.MaPhong AND dichvuphong.MaDV = dichvu.MaDV AND MONTH(NgayThanhToan) = '6' AND YEAR(NgayThanhToan) = '2018' ;"));
}
public static DataTable LayTatCaDatPhong_TinhTrangPhong_KhachHang_NhanVien_DataTable()
{
return(DataProvider.ExecuseQuery("SELECT MaDatPhong, ThoiGianDatPhong,NgayLapPhieuDat, datphong.SoTienDatTruoc,khachhang.TenKH,khachhang.SDT,TenNhanVien,GhiChu,TinhTrangDatPhong FROM datphong, tinhtrangdatphong, khachhang, nhanvien WHERE datphong.MaTinhTrangDatPhong = tinhtrangdatphong.MaTinhTrangDatPhong AND datphong.MaNhanVien = nhanvien.MaNhanVien AND datphong.MaKH = khachhang.MaKH ;"));
}
public static int LayKhoangThoiGianTuDongHuyDatPhong()
{
return(int.Parse(DataProvider.ExecuseQuery("SELECT * FROM quanlykaraoke.thamso WHERE MaThamSo = '4';").Rows[0]["GiaTri"].ToString()));
}
public static int DemSoLuongPhong(int maTinhTrangPhong)
{
return(Convert.ToInt32(DataProvider.ExecuseQuery("SELECT Count(*) FROM quanlykaraoke.phong WHERE phong.MaTinhTrangPhong = '" + maTinhTrangPhong + "';").Rows[0][0]));
}
public static DataTable LayCacPhongConTrongTrongThoiGian(DateTime time, int khoangThoiGianToiThieu)
{
return(DataProvider.ExecuseQuery("SELECT * FROM loaiphong, phong WHERE loaiphong.MaLP = phong.MaLoaiPhong AND phong.MaPhong NOT IN (SELECT MaPhong FROM datphong, chitietdatphong WHERE MaTinhTrangDatPhong = 1 AND datphong.MaDatPhong = chitietdatphong.MaDatPhong AND ThoiGianDatPhong BETWEEN '" + time.AddMinutes(-khoangThoiGianToiThieu).ToString("yyyy-MM-dd HH:mm:ss.fff") + "' AND '" + time.AddMinutes(khoangThoiGianToiThieu).ToString("yyyy-MM-dd HH:mm:ss.fff") + "') AND MaTinhTrangPhong <> 2 AND MaTinhTrangPhong <> 3;"));
}