prepare("SELECT role FROM users WHERE userid = :userid");
$stmt->bindParam(':userid', $_SESSION['userid'], PDO::PARAM_INT);
$stmt->execute();
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$user || $user['role'] !== 'Admin') {
header("Location: ./Sign&Log.php");
exit();
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
exit();
}
// Query the users table
//Indexed
$query = "
SELECT users.userid, users.username, users.email, member.membership_status, member.memberid
FROM users
LEFT JOIN member ON users.userid = member.userid
";
$res = $conn->query($query);
$users = [];
while($row = $res->fetch(PDO::FETCH_ASSOC)) {
$users[] = $row;
}
// SQL query to get loan, book, book_copies, and member information
$query = "
SELECT loan.loanid, loan.memberid, book.bookid, book_copies.copyid, loan.status
FROM loan
INNER JOIN book_copies ON loan.bookcopyid = book_copies.copyid
INNER JOIN book ON book_copies.bookid = book.bookid
INNER JOIN member ON loan.memberid = member.memberid
";
$res = $conn->query($query);
$loans = [];
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
$loans[] = $row;
}
// SQL Query to get Fine
$query = " SELECT * FROM Fine";
$res = $conn->query($query);
$fines = [];
while($row = $res->fetch(PDO::FETCH_ASSOC)){
$fines[] = $row;
}
// SQL Query to get Author
$query = "SELECT * FROM Author ORDER BY authorid";
$res = $conn->query($query);
$authors = [];
while($row = $res->fetch(PDO::FETCH_ASSOC)){
$authors[] = $row;
}
// SQL Query to get Book
$query = "
SELECT b.*, a.FirstName, a.LastName
FROM Book b
JOIN Book_Author ba ON b.BookID = ba.BookID
JOIN Author a ON ba.AuthorID = a.AuthorID";
$res = $conn->query($query);
$books = [];
while($row = $res->fetch(PDO::FETCH_ASSOC)){
$books[] = $row;
}
// SQL Query for Dashboiard
$totalBooksQuery = "SELECT COUNT(*) AS total_books FROM Book";
$borrowedBooksQuery = "SELECT COUNT(*) AS borrowed_books FROM Loan WHERE status != 'Returned'";
$totalUsersQuery = "SELECT COUNT(*) AS total_users FROM Users";
$unpaidFinesQuery = "SELECT SUM(FineAmount) AS unpaid_fines FROM Fine WHERE Status = 'Unpaid'";
// Execute queries
$totalBooksResult = $conn->query($totalBooksQuery)->fetch(PDO::FETCH_ASSOC);
$borrowedBooksResult = $conn->query($borrowedBooksQuery)->fetch(PDO::FETCH_ASSOC);
$totalUsersResult = $conn->query($totalUsersQuery)->fetch(PDO::FETCH_ASSOC);
$unpaidFinesResult = $conn->query($unpaidFinesQuery)->fetch(PDO::FETCH_ASSOC);
// Data for the dashboard
$totalBooks = $totalBooksResult['total_books'];
$borrowedBooks = $borrowedBooksResult['borrowed_books'];
$totalUsers = $totalUsersResult['total_users'];
$unpaidFines = $unpaidFinesResult['unpaid_fines'];
?>
Librarian Dashboard