Напредни извештаи од базата (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;

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

WITH DateRanges AS (
    SELECT
        generate_series(
            date_trunc('year', CURRENT_DATE) - interval '5 year',
            date_trunc('year', CURRENT_DATE),
            interval '1 year'
        ) as start_date,
        generate_series(
            date_trunc('year', CURRENT_DATE) - interval '4 year',
            date_trunc('year', CURRENT_DATE),
            interval '1 year'
        ) as end_date
)
SELECT 
    u.FirstName || ' ' || u.LastName as MemberName,
    m.Membership_Status,
    d.start_date::date as PeriodStart,
    d.end_date::date as PeriodEnd,
    COUNT(DISTINCT l.LoanID) as TotalLoans,
    COUNT(DISTINCT CASE WHEN l.ReturnDate IS NULL THEN l.LoanID END) as ActiveLoans,
    COUNT(DISTINCT f.FineID) as TotalFines,
    COALESCE(SUM(f.FineAmount), 0) as TotalFineAmount,
    COALESCE(SUM(fp.PaymentAmount), 0) as TotalPaidAmount
FROM 
    DateRanges d
    CROSS JOIN Member m
    JOIN Users u ON m.UserID = u.UserID
    LEFT JOIN Loan l ON m.MemberID = l.MemberID 
        AND l.LoanDate >= d.start_date 
        AND l.LoanDate < d.end_date
    LEFT JOIN Fine f ON l.LoanID = f.LoanID
    LEFT JOIN FinePayment fp ON f.FineID = fp.FineID
GROUP BY 
    u.FirstName, u.LastName, m.Membership_Status, d.start_date, d.end_date
ORDER BY 
    d.start_date DESC, TotalLoans DESC;

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

WITH TimePeriodLoans AS (
    SELECT 
        b.Genre,
        l.LoanDate,
        DATE_TRUNC('month', l.LoanDate) as MonthPeriod,
        DATE_TRUNC('quarter', l.LoanDate) as QuarterPeriod,
        DATE_TRUNC('year', l.LoanDate) as YearPeriod
    FROM 
        Book b
        JOIN Book_Copies bc ON b.BookID = bc.BookID
        JOIN Loan l ON bc.CopyID = l.BookCopyID
)
SELECT 
    Genre,
    Period,
    PeriodType,
    LoanCount,
    ROUND(AVG(LoanCount) OVER (
        PARTITION BY Genre, PeriodType 
        ORDER BY Period 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) as MovingAverage
FROM (
    SELECT 
        Genre,
        MonthPeriod as Period,
        'Monthly' as PeriodType,
        COUNT(*) as LoanCount
    FROM TimePeriodLoans
    GROUP BY Genre, MonthPeriod
    UNION ALL
    SELECT 
        Genre,
        QuarterPeriod,
        'Quarterly',
        COUNT(*)
    FROM TimePeriodLoans
    GROUP BY Genre, QuarterPeriod
    UNION ALL
    SELECT 
        Genre,
        YearPeriod,
        'Yearly',
        COUNT(*)
    FROM TimePeriodLoans
    GROUP BY Genre, YearPeriod
) as CombinedPeriods
ORDER BY 
    PeriodType, Period DESC, LoanCount DESC;