/****** Object:  StoredProcedure  [sp_getStatisticRentbookInMonth]  Script Date: 8/13/2019 ******/
--Trả về thông tin sách thuê theo tháng
CREATE PROCEDURE sp_getStatisticRentbookInMonth (@MONTH SMALLINT)
AS
BEGIN
    DECLARE @book_id VARCHAR(50)
    DECLARE @book_title NVARCHAR(256)
    DECLARE @amount_total INT
    DECLARE @amount_returned INT
    DECLARE @amount_renting INT
    DECLARE @amount_expiration INT

    DECLARE @tblStats TABLE
    (
        book_id VARCHAR(50),
        book_title NVARCHAR(256),
        amount_total INT,
        amount_returned INT,
        amount_renting INT,
        amount_expiration INT
    )
    --Tạo con trỏ duyệt từng sách có trong phần thuê sách
    IF (@MONTH != 0) -- Nếu @month khác 0 thì duyệt lấy mã sách thuê theo tháng @month
        DECLARE cs  CURSOR FOR SELECT DISTINCT b.id
        FROM dbo.BOOK b, dbo.RENTBOOK rb, dbo.RENTBOOK_DETAIL dt
        WHERE b.id = dt.book_id AND rb.id = dt.rentbook_id
        AND YEAR(rb.created_date) = YEAR(GETDATE()) AND MONTH(rb.created_date) = @MONTH
    ELSE-- Nếu @month = 0 thì duyệt lấy mã sách thuê trong cả năm
        DECLARE cs  CURSOR FOR SELECT DISTINCT b.id
        FROM dbo.BOOK b, dbo.RENTBOOK rb, dbo.RENTBOOK_DETAIL dt
        WHERE b.id = dt.book_id AND rb.id = dt.rentbook_id
        AND YEAR(rb.created_date) = YEAR(GETDATE())

    OPEN cs
    FETCH NEXT FROM cs INTO @book_id

    --Tiến hành lặp danh sách sách thuê và insert dữ liệu thống kê vào tblStats
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --Tên sách
        SELECT @book_title = title FROM dbo.BOOK WHERE id = @book_id

        --Tổng số lượng đã thuê
        SELECT @amount_total = SUM(dt.amount)
        FROM dbo.BOOK b, dbo.RENTBOOK_DETAIL dt
        WHERE b.id = dt.book_id AND dt.book_id = @book_id
        GROUP BY b.id

        --Tổng số sách đã trả
        SELECT @amount_returned = SUM(dt.amount)
        FROM dbo.BOOK b, dbo.RENTBOOK_DETAIL dt, dbo.RENTBOOK rb
        WHERE b.id = dt.book_id
        AND dt.rentbook_id = rb.id
        AND dt.book_id = @book_id
        AND rb.STATUS = 1
        GROUP BY b.id

        --Tổng số sách đang còn thuê
        SELECT @amount_renting = SUM(dt.amount)
        FROM dbo.BOOK b, dbo.RENTBOOK_DETAIL dt, dbo.RENTBOOK rb
        WHERE b.id = dt.book_id
        AND dt.rentbook_id = rb.id
        AND dt.book_id = @book_id
        AND rb.STATUS = 0
        GROUP BY b.id

        --Tổng số sách đang quá hạn
        SELECT @amount_expiration = SUM(dt.amount)
        FROM dbo.BOOK b, dbo.RENTBOOK_DETAIL dt, dbo.RENTBOOK rb
        WHERE b.id = dt.book_id
        AND dt.rentbook_id = rb.id
        AND dt.book_id = @book_id
        AND rb.STATUS = 0
        AND DATEDIFF(DAY, rb.created_date, GETDATE()) > rb.expiration_day
        GROUP BY b.id

        --Thêm các dữ liệu đã thống kê vào bảng tblStats
        INSERT @tblStats( book_id, book_title, amount_total, amount_returned, amount_renting, amount_expiration)
        VALUES  (@book_id, @book_title, @amount_total, @amount_returned, @amount_renting, @amount_expiration)

        -- Đi đến dòng tiếp theo
        FETCH NEXT FROM cs INTO @book_id
    END


    --Đóng con trỏ
    CLOSE cs
    DEALLOCATE cs

    SELECT * FROM @tblStats
END
GO