public DataTable LayMaPhieuTra()
{
try
{
string strSQL = "SELECT MaPT FROM PHIEUTRASACH ORDER BY MaPT";
provider.Connect();
DataTable dtTable = provider.SELECT(CommandType.Text, strSQL);
return(dtTable);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable LayDanhSachNhanVien()
{
try
{
provider.Connect();
string query = "SELECT * FROM NhanVien";
DataTable table = provider.SELECT(CommandType.Text, query);
return(table);
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public static bool WasExistBookId(string bookId)
{
provider.Connect();
try
{
string cmd = "SELECT * FROM SACH WHERE MaSach = '" + bookId + "'";
var dt = provider.SELECT(System.Data.CommandType.Text, cmd);
return(dt.Rows.Count != 0);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable MaGhiNhanSachMat()
{
DataTable dt = new DataTable();
try
{
pro.Connect();
string strSql = "SELECT MaGNMatSach FROM MATSACH ORDER BY MaGNMatSach DESC";
dt = pro.SELECT(CommandType.Text, strSql);
return(dt);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
pro.DisConnect();
}
}
public DataTable LayDSDocGiaNo()
{
DataTable dt = new DataTable();
try
{
pro.Connect();
string strSql = "SELECT pm.MaDG, pt.MaPT, pt.TienNo FROM PHIEUTRASACH pt INNER JOIN PHIEUMUONSACH pm ON pt.MaPM=pm.MaPM WHERE pt.TienNo > 0";
dt = pro.SELECT(CommandType.Text, strSql);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
pro.DisConnect();
}
return(dt);
}
public DataTable LaySachTrongPhieuMuon(string MaSach)
{
try
{
string strSQL = "SELECT S.TenSach, TG.TenTacGia, TL.TenTheLoai, S.TTS" +
" FROM SACH S, TACGIA TG, THELOAISACH TL" +
" WHERE S.MaSach = @MaSach AND S.MaTacGia = TG.MaTacGia AND S.MaTheLoai = TL.MaTheLoai";
provider.Connect();
DataTable dtTable = provider.SELECT(CommandType.Text, strSQL,
new SqlParameter {
ParameterName = "@MaSach", Value = MaSach
});
return(dtTable);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}
public DataTable LayThongTinSach(string MaSach)
{
try
{
string strSQL = "SELECT S.MaSach, S.TenSach, TG.TenTacGia, TL.TenTheLoai FROM TACGIA TG JOIN SACH S INNER JOIN THELOAISACH TL " +
"ON TL.MaTheLoai = S.MaTheLoai ON S.MaTacGia = TG.MaTacGia " +
"WHERE S.MaSach = @MaSach";
provider.Connect();
DataTable dtSach = provider.SELECT(CommandType.Text, strSQL,
new SqlParameter {
ParameterName = "@MaSach", Value = MaSach
});
return(dtSach);
}
catch (Exception ex)
{
throw ex;
}finally
{
provider.DisConnect();
}
}
public DataTable LayThongTinBaoCaoDocGiaNoTienPhat(string NgayBaoCao)
{
try
{
string strSQL = "SELECT DG.HoTenDG, PTTP.ConLai FROM PHIEUTHUTIENPHAT PTTP INNER JOIN PHIEUTRASACH PTS INNER JOIN PHIEUMUONSACH PMS JOIN DOCGIA DG ON DG.MaDocGia = PMS.MaDG ON PMS.MaPM = PTS.MaPM ON PTS.MaPT = PTTP.MaPT " +
"WHERE PTTP.ConLai > 0 AND PTTP.NgayThu <= @NgayBaoCao";
provider.Connect();
DataTable dtBaoCao = provider.SELECT(CommandType.Text, strSQL,
new SqlParameter {
ParameterName = "@NgayBaoCao", Value = NgayBaoCao
});
return(dtBaoCao);
}
catch (Exception ex)
{
throw ex;
}
finally
{
provider.DisConnect();
}
}