public int ThemPhong(PhongDTO p)
{
int nRow = 0;
Provider provider = new Provider();
try
{
string SqlPhong = "INSERT INTO PHONG(MaPhong,TenPhong,MaLoaiPhong,MaTinhTrang) VALUES (@MaPhong,@TenPhong,@MaLoaiPhong,@MaTinhTrang)";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, SqlPhong,
new SqlParameter {
ParameterName = "@MaPhong", Value = p.MaPhong
},
new SqlParameter {
ParameterName = "@TenPhong", Value = p.TenPhong
},
new SqlParameter {
ParameterName = "@MaLoaiPhong", Value = p.MaLoaiPhong
},
new SqlParameter {
ParameterName = "@MaTinhTrang", Value = p.MaLoaiTinhTrang
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public DataTable loadLoaiTinhTrang()
{
Provider provider = new Provider();
provider.Connect();
DataTable dt = new DataTable();
string sqlQuery = "SELECT * FROM LOAITINHTRANG";
dt = provider.Select(CommandType.Text, sqlQuery);
provider.DisConnect();
return(dt);
}
public DataTable DuLieuMCuoiDanhSach()
{
Provider provider = new Provider();
provider.Connect();
DataTable dt = new DataTable();
string sqlQuery = "select max(p.MaPhong) from PHONG p";
dt = provider.Select(CommandType.Text, sqlQuery);
provider.DisConnect();
return(dt);
}
public DataTable loadLoaiPhong()
{
Provider provider = new Provider();
provider.Connect();
DataTable dt = new DataTable();
string sqlQuery = "SELECT MaLoaiPhong, TenLoaiPhong FROM LOAIPHONG";
dt = provider.Select(CommandType.Text, sqlQuery);
provider.DisConnect();
return(dt);
}
public DataTable DanhSachLoaiTinhTrang()
{
Provider provider = new Provider();
provider.Connect();
DataTable dt = new DataTable();
string sqlQuery = "SELECT ltt.MaTinhTrang,ltt.TenTinhTrang FROM LOAITINHTRANG ltt";
dt = provider.Select(CommandType.Text, sqlQuery);
provider.DisConnect();
return(dt);
}
public DataTable DanhSachLoaiKhachHang()
{
Provider provider = new Provider();
provider.Connect();
DataTable dt = new DataTable();
string sqlQuery = "select lkh.MaLoaiKhachHang,lkh.TenLoaiKhach from LOAIKHACHHANG lkh";
dt = provider.Select(CommandType.Text, sqlQuery);
provider.DisConnect();
return(dt);
}
public DataTable DuLieuMCuoiDanhSach()
{
Provider provider = new Provider();
provider.Connect();
DataTable dt = new DataTable();
string sqlQuery = "select max(kh.MaKhachHang) from KHACHHANG kh";
dt = provider.Select(CommandType.Text, sqlQuery);
provider.DisConnect();
return(dt);
}
public DataTable LayMaPhieuBaoCaoMatDOCuoiCung()
{
Provider provider = new Provider();
try
{
string strSql = "SELECT MAX(BC.MaPhieuBaoCaoMatDoSuDungPhong) FROM PHIEUBAOCAOMATDOSUDUNGPHONG BC";
provider.Connect();;
DataTable dt = provider.Select(CommandType.Text, strSql);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable DanhSachLoaiPhong()
{
Provider provider = new Provider();
try
{
string strSql = "SELECT LP.MaLoaiPhong,LP.TenLoaiPhong FROM LOAIPHONG LP";
provider.Connect();;
DataTable dt = provider.Select(CommandType.Text, strSql);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable LayMaPhieuBaoCaoCuoiCung()
{
Provider provider = new Provider();
try
{
string strSql = "SELECT MAX(BC.MaPhieuBaoCaoDoanhThu) FROM PHIEUBAOCAODOANHTHU BC";
provider.Connect();;
DataTable dt = provider.Select(CommandType.Text, strSql);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable LoadKhachHang()
{
Provider provider = new Provider();
try
{
string strSql = "select kh.MaKhachHang, kh.TenKhachHang, kh.CMND, kh.DiaChi, lkh.TenLoaiKhach FROM KHACHHANG kh join LOAIKHACHHANG lkh on kh.MaLoaiKhachHang = lkh.MaLoaiKhachHang";
provider.Connect();;
DataTable dt = provider.Select(CommandType.Text, strSql);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable getRevenue(int month)
{
Provider provider = new Provider();
try
{
provider.Connect();
List <SqlParameter> pars = new List <SqlParameter> {
new SqlParameter("@month", month)
};
return(provider.Select(CommandType.StoredProcedure, "getRevenue", pars.ToArray()));
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public int ThemDataVaoPhieuBaoCaoMatDo(PhieuCaoBaoMatDo bc)
{
int n = 0;
Provider provider = new Provider();
try
{
string strSql = "INSERT INTO PHIEUBAOCAOMATDOSUDUNGPHONG(MaPhieuBaoCaoMatDoSuDungPhong,MaLoaiPhong,MatDo,TiLe,ThangBaoCaoMatDo) VALUES (@MaPhieuBaoCaoMatDoSuDungPhong,@MaLoaiPhong,@MatDo,@TiLe,@ThangBaoCaoMatDo)";
provider.Connect();;
n = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaPhieuBaoCaoMatDoSuDungPhong", Value = bc.MaPhieuMatDo
},
new SqlParameter {
ParameterName = "@MaLoaiPhong", Value = bc.MaLoaiPhong
},
new SqlParameter {
ParameterName = "@MatDo", Value = bc.MatDo
},
new SqlParameter {
ParameterName = "@TiLe", Value = bc.TiLe
},
new SqlParameter {
ParameterName = "@ThangBaoCaoMatDo", Value = bc.Thang
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(n);
}
public int ThemDataVaoPhieuBaoCao(PhieuCaoBaoDanhThu bc)
{
int n = 0;
Provider provider = new Provider();
try
{
string strSql = "INSERT INTO PHIEUBAOCAODOANHTHU(MaPhieuBaoCaoDoanhThu,ThangBaoCaoDoanhThu,DoanhThu,TiLe,MaLoaiPhong) VALUES (@MaPhieu,@Thang,@DoanhThu,@TiLe,@MaLoaiPhong)";
provider.Connect();;
n = provider.ExecuteNonQuery(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@MaPhieu", Value = bc.MaPhieuBaoCao
},
new SqlParameter {
ParameterName = "@Thang", Value = bc.Thang
},
new SqlParameter {
ParameterName = "@DoanhThu", Value = bc.DoanhThu
},
new SqlParameter {
ParameterName = "@TiLe", Value = bc.TiLe
},
new SqlParameter {
ParameterName = "@MaLoaiPhong", Value = bc.MaLoaiPhong
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(n);
}
public DataTable PhieuCaoBaoMatDo(string Thang)
{
Provider provider = new Provider();
try
{
string strSql = "SELECT MD.ThangBaoCaoMatDo,MD.MaLoaiPhong,MD.MatDo,MD.TiLe FROM PHIEUBAOCAOMATDOSUDUNGPHONG MD WHERE MD.ThangBaoCaoMatDo=@Thang";
provider.Connect();;
DataTable dt = provider.Select(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@Thang", Value = Thang
});
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable PhieuCaoBaoDanhThu(string Thang)
{
Provider provider = new Provider();
try
{
string strSql = "SELECT PBC.MaLoaiPhong,PBC.DoanhThu,PBC.TiLe,PBC.ThangBaoCaoDoanhThu FROM PHIEUBAOCAODOANHTHU PBC WHERE PBC.ThangBaoCaoDoanhThu=@Thang";
provider.Connect();;
DataTable dt = provider.Select(CommandType.Text, strSql,
new SqlParameter {
ParameterName = "@Thang", Value = Thang
});
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public int ThemKhachHnag(KhachHangDTO kh)
{
int nRow = 0;
Provider provider = new Provider();
try
{
string SqlPhong = "INSERT INTO KHACHHANG(MaKhachHang,TenKhachHang,CMND,DiaChi,MaLoaiKhachHang) VALUES (@MaKhachHang,@TenKhachHang,@CMND,@DiaChi,@MaLoaiKhachHang)";
provider.Connect();
nRow = provider.ExecuteNonQuery(CommandType.Text, SqlPhong,
new SqlParameter {
ParameterName = "@MaKhachHang", Value = kh.MaKhachHang
},
new SqlParameter {
ParameterName = "@TenKhachHang", Value = kh.TenKhachHang
},
new SqlParameter {
ParameterName = "@CMND", Value = kh.CMND
},
new SqlParameter {
ParameterName = "@DiaChi", Value = kh.DiaChi
},
new SqlParameter {
ParameterName = "@MaLoaiKhachHang", Value = kh.MaLoaiKhachHang
}
);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
return(nRow);
}
public int ThemThanhToan(HoaDonDTO hoaDonDTO, List <ChiTietHoaDonDTO> dSChiTietHoaDonDTO)
{
Provider provider = new Provider();
try
{
DataTable dt = new DataTable();
provider.Connect();
// tạo ra mã hóa đơn và ghi vào trong database
string sqlQueryHD = "SELECT MaHoaDon FROM HOADON";
dt = provider.Select(CommandType.Text, sqlQueryHD);
string MaHoaDon;
if (dt.Rows.Count == 0)
{
MaHoaDon = "HD001";
}
else
{
string so0 = "000";
string increment = (int.Parse(dt.Rows[dt.Rows.Count - 1].Field <string>(0).Substring(2)) + 1).ToString();
MaHoaDon = "HD" + so0.Substring(increment.Length) + increment;
}
string sqlQuery = "INSERT INTO HOADON(MaHoaDon, NgayThanhToan, TongThanhTien, MaKhachHang) VALUES(@MaHoaDon, @NgayThanhToan, @TongThanhTien, @MaKhachHang)";
provider.ExecuteNonQuery(CommandType.Text, sqlQuery,
new SqlParameter {
ParameterName = "@MaHoaDon", Value = MaHoaDon
},
new SqlParameter {
ParameterName = "@NgayThanhToan", Value = hoaDonDTO.NgayThanhToan
},
new SqlParameter {
ParameterName = "@TongThanhTien", Value = hoaDonDTO.TriGia
},
new SqlParameter {
ParameterName = "@MaKhachHang", Value = hoaDonDTO.MaKhachHang
}
);
// tạo ra mã chi tiết hóa đơn và ghi vào trong database
for (int i = 0; i < dSChiTietHoaDonDTO.Count; i++)
{
string sqlQueryMCTHD = "SELECT MaChiTietHoaDon FROM CHITIETHOADON";
DataTable dt_2 = new DataTable();
dt_2 = provider.Select(CommandType.Text, sqlQueryMCTHD);
string MaChiTietHoaDon;
if (dt_2.Rows.Count == 0)
{
MaChiTietHoaDon = "CTHD001";
}
else
{
string so0 = "000";
string increment = (int.Parse(dt_2.Rows[dt_2.Rows.Count - 1].Field <string>(0).Substring(4)) + 1).ToString();
MaChiTietHoaDon = "CTHD" + so0.Substring(increment.Length) + increment;
}
string sqlQuery_2 = "INSERT INTO CHITIETHOADON(MaChiTietHoaDon, SoNgayThue, DonGia, ThanhTien, MaPhong, MaHoaDon) VALUES(@MaChiTietHoaDon, @SoNgayThue, @DonGia, @ThanhTien, @MaPhong, @MaHoaDon)";
provider.ExecuteNonQuery(CommandType.Text, sqlQuery_2,
new SqlParameter {
ParameterName = "@MaChiTietHoaDon", Value = MaChiTietHoaDon
},
new SqlParameter {
ParameterName = "@SoNgayThue", Value = dSChiTietHoaDonDTO[i].SoNgayThue
},
new SqlParameter {
ParameterName = "@DonGia", Value = dSChiTietHoaDonDTO[i].DonGia
},
new SqlParameter {
ParameterName = "@ThanhTien", Value = dSChiTietHoaDonDTO[i].ThanhTien
},
new SqlParameter {
ParameterName = "@MaPhong", Value = dSChiTietHoaDonDTO[i].MaPhong
},
new SqlParameter {
ParameterName = "@MaHoaDon", Value = MaHoaDon
}
);
// cập nhật tình trạng phòng
string sqlQuery_3 = "UPDATE PHONG SET MaTinhTrang = 'TT001' WHERE MaPhong = @MaPhong";
provider.ExecuteNonQuery(CommandType.Text, sqlQuery_3,
new SqlParameter {
ParameterName = "@MaPhong", Value = dSChiTietHoaDonDTO[i].MaPhong
}
);
}
provider.DisConnect();
return(1);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}