Напредни извештаи од базата (SQL и складирани процедури)

Извештај за членови со најмногу позајмувања по месец

SELECT 
    users.FirstName,
    users.LastName,
    EXTRACT(MONTH FROM loan.LoanDate) AS month,
    COUNT(loan.LoanID) AS loan_count
FROM users
INNER JOIN loan ON users.UserID = loan.MemberID
WHERE EXTRACT(MONTH FROM loan.LoanDate) = 4  -- Change this number for different months (1-12)
GROUP BY 
    users.UserID, 
    users.FirstName, 
    users.LastName,
    EXTRACT(MONTH FROM loan.LoanDate)
ORDER BY loan_count DESC
LIMIT 5;

Извештај за најпозајмени книги по тримесечие

SELECT 
    book.CoverImage,
    book.Title,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate) AS quarter,
    COUNT(loan.LoanID) AS loan_count
FROM book
INNER JOIN book_copies ON book.BookID = book_copies.BookID
LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID
INNER JOIN book_author ON book.BookID = book_author.BookID
INNER JOIN author ON book_author.AuthorID = author.AuthorID
WHERE EXTRACT(QUARTER FROM loan.LoanDate) = 4  -- Change this number (1-4) for different quarters
GROUP BY 
    book.BookID,
    book.Title,
    book.CoverImage,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate)
ORDER BY loan_count DESC
LIMIT 5;

Извештај за најмалку позајмувани книги по тримесечие

SELECT 
    book.CoverImage,
    book.Title,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate) AS quarter,
    COUNT(loan.LoanID) AS loan_count
FROM book
INNER JOIN book_copies ON book.BookID = book_copies.BookID
LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID
INNER JOIN book_author ON book.BookID = book_author.BookID
INNER JOIN author ON book_author.AuthorID = author.AuthorID
WHERE EXTRACT(QUARTER FROM loan.LoanDate) = 4  -- Change this number (1-4) for different quarters
GROUP BY 
    book.BookID,
    book.Title,
    book.CoverImage,
    book.Genre,
    author.FirstName,
    author.LastName,
    EXTRACT(QUARTER FROM loan.LoanDate)
ORDER BY loan_count ASC
LIMIT 5;

Извештај за сума на платени и неплатени казни во текот на годината

SELECT 
    EXTRACT(YEAR FROM finedate) AS fine_year,
    SUM(CASE WHEN status = 'Paid' THEN fineamount ELSE 0 END) AS total_paid,
    SUM(CASE WHEN status = 'Unpaid' THEN fineamount ELSE 0 END) AS total_unpaid,
    SUM(fineamount) AS total_fines
FROM fine
WHERE EXTRACT(YEAR FROM finedate) = 2024  -- Change year as needed
GROUP BY EXTRACT(YEAR FROM finedate)
ORDER BY fine_year;

Извештај за задоцнети враќања и просечно доцнење по месец

SELECT 
    EXTRACT(YEAR FROM LoanDate) as year,
    EXTRACT(MONTH FROM LoanDate) as month,
    COUNT(*) as total_late_returns,
    AVG(ReturnDate - LoanDate) as avg_days_late
FROM Loan
WHERE ReturnDate > LoanDate + INTERVAL '14 days'
GROUP BY 
    EXTRACT(YEAR FROM LoanDate),
    EXTRACT(MONTH FROM LoanDate)
ORDER BY year, month;

Извештај за кондиција на книга

SELECT 
    b.Title,
    bc.Condition,
    COUNT(*) as copy_count
FROM Book b
JOIN Book_Copies bc ON b.BookID = bc.BookID
GROUP BY b.Title, bc.Condition
ORDER BY b.Title;

Извештај за членовите

SELECT 
    u.FirstName,
    u.LastName,
    COUNT(l.LoanID) as total_loans,
    COUNT(f.FineID) as total_fines,
    SUM(f.FineAmount) as total_fine_amount
FROM Users u
JOIN Member m ON u.UserID = m.UserID
LEFT JOIN Loan l ON m.MemberID = l.MemberID
LEFT JOIN Fine f ON l.LoanID = f.LoanID
GROUP BY u.UserID, u.FirstName, u.LastName
ORDER BY total_loans DESC;

Извештај за анализа на времето за враќање

SELECT 
    b.Title,
    AVG(l.ReturnDate - l.LoanDate) as AvgDaysToReturn,
    MIN(l.ReturnDate - l.LoanDate) as MinDaysToReturn,
    MAX(l.ReturnDate - l.LoanDate) as MaxDaysToReturn,
    COUNT(*) as TotalLoans,
    COUNT(CASE WHEN f.FineID IS NOT NULL THEN 1 END) as LoansWithFines
FROM 
    Loan l
    JOIN Book_Copies bc ON l.BookCopyID = bc.CopyID
    JOIN Book b ON bc.BookID = b.BookID
    LEFT JOIN Fine f ON l.LoanID = f.LoanID
WHERE 
    l.ReturnDate IS NOT NULL
GROUP BY 
    b.BookID, b.Title
HAVING 
    COUNT(*) >= 5  -- Only show books with at least 5 loans
ORDER BY 
    AVG(l.ReturnDate - l.LoanDate) DESC;