[도서관 관리 사이트] 6. 마이페이지, 모든서적 페이지, 인기차트 페이지

1. 마이페이지

이전 글에서 만들었던 관리자 페이지보다 요구되는 기능이 적기에 쉽게 만들 수 있다.

1-1. templates/mypage/mypage.html

이 페이지에서는 관리자페이지에서 처럼 table을 사용했다.
하지만 관리자 페이지는 옆으로 테이블 두 개를 배치했고, 이 페이지는 위 아래로 배치했다.

대여기록 부분은 대여일을 기준으로 정렬해 보여주게끔 했고, 따라서 연체 등의 특별한 경우가 아니라면 일반적으로 아직 대여중인 책이 위에 있게 된다.
그래서 반납할 때 밑으로 스크롤을 할 필요 없게 편의성을 고려했다.

 1-2. mypage/routes.py

이 페이지는 routes.py에서 크게 추가로 해야하는 동작은 없다. 간단히 쿼리 함수를 호출해주기만 하면 된다.

@mypage_bp.route('/', methods=['GET', 'POST'])
def mypage():
    # 로그인하지 않은 사용자는 접근할 수 없도록 합니다.
    if 'user_id' not in session:
        flash("로그인이 필요한 서비스입니다.")
        return redirect(url_for('auth.login'))

    user_id = session['user_id']

    # '반납하기' 버튼을 눌렀을 때 (POST 요청)
    if request.method == 'POST':
        rent_id_to_return = request.form.get('rent_id')
        if rent_id_to_return:
            db.return_book(rent_id_to_return)
            flash("책이 성공적으로 반납되었습니다.")
        return redirect(url_for('mypage.mypage'))

    # 페이지를 처음 로드할 때 (GET 요청)
    reservations = db.get_user_reservations(user_id)
    rentals = db.get_user_rentals(user_id)

    return render_template('mypage/mypage.html', reservations=reservations, rentals=rentals)

1-3. 관련된 sql쿼리들 (db.py)

총 세 가지의 쿼리함수가 사용된다.
get_user_reservations에서는 해당 유저의 아이디를 통해 이 유저가 예약을 걸어놓은 책의 정보를 select한다.

get_user_rentals에서는 해당 유저의 아이디를 통해 이 유저의 대여기록을 가져온다.

return_book은 책을 반납할 때 바뀌는 내용들을 table에 수정한다.

def get_user_reservations(user_id):
    print('get_user_reservations 호출:', user_id)
    query = """
        SELECT bd.title, bd.author, r.reservation_date
        FROM reservation AS r
        JOIN book_data AS bd ON r.book_code = bd.book_code
        WHERE r.user_id = %s
        ORDER BY r.reservation_date DESC;
    """
    return execute_query(query, params=(user_id,), fetch='all')

def get_user_rentals(user_id):
    print('get_user_rentals 호출:', user_id)
    query = """
        SELECT r.rent_id, bd.title, bd.author, r.rent_date, r.return_date, r.book_id
        FROM rent AS r
        JOIN book_status AS bs ON r.book_id = bs.book_id
        JOIN book_data AS bd ON bs.book_code = bd.book_code
        WHERE r.user_id = %s
        ORDER BY r.rent_date DESC;
    """
    return execute_query(query, params=(user_id,), fetch='all')

def return_book(rent_id):
    print('return_book 호출:', rent_id)
    
    update_rent_query = "UPDATE rent SET return_date = NOW() WHERE rent_id = %s;"
    execute_query(update_rent_query, params=(rent_id,))

    update_status_query = """
        UPDATE book_status 
        SET is_rent = FALSE 
        WHERE book_id = (SELECT book_id FROM rent WHERE rent_id = %s);
    """
    execute_query(update_status_query, params=(rent_id,))

 

2. 모든서적 페이지 (allbooks.html)

2-1. templates/main/allbooks.html

모든 서적을 볼 수 있고, 검색과 대출까지 가능한 기능이 많은 페이지이다.

검색은 포함되는 글자로 검색이 가능하고, 제목/저자/카테고리에 따라 오름차순/내림차순

정렬이 가능하다.

옆의 대출 칸에는 현 상황에 맞는 문구와 함께 활성화/비활성화된 버튼이 있다.

나올 수 있는 글씨의 종류는 아래와 같다.

• 이용불가 : 연체 중일 때 표시된다. 버튼이 비활성화 된다.
• 대출 중 : 현재 해당 책을 이미 대출 중일 때 표시된다. 버튼이 비활성화 된다.
• 대출 불가 : 이미 3 권을 빌린 상태일 때 표시된다. 버튼이 비활성화 된다.
• 대출 : 대출이 가능한 경우 표시된다. 버튼이 활성화 된다.
• 예약됨 : 이미 본인이 해당 책을 예약을 했을 경우 표시된다. 버튼이 비활성화 된다.
• 예약 : 예약을 할 수 있는 버튼이다. 버튼이 활성화 된다.
• 로그인 필요 : 로그인이 되지않은 상태에서 모든서적페이지를 볼 때 표시된다. 버튼이 비활성화 된다.

2-2. main/routes.py

기본적으로 모든 책의 리스트를 get_all_books()를 통해 받아온다.

또 로그인 되어있을 경우 get_user_borrowing_status()와, get_user_reservations()를 통해

관련된 이용자의 정보도 받아온다.

@main_bp.route('/allbooks')
def allbookspage():
    search_term = request.args.get('search')
    sort_column = request.args.get('sort_by', 'title') 
    sort_direction = request.args.get('sort_order', 'asc') 

    all_book_list = db.get_all_books(
        search_query=search_term, 
        sort_by=sort_column,
        sort_order=sort_direction
    )

    user_status = None
    user_reservations = [] 
    if 'user_id' in session:
        user_id = session['user_id']
        user_status = db.get_user_borrowing_status(user_id)
        reservations_raw = db.get_user_reservations(user_id) 
        user_reservations = [
            r['book_code'] for r in reservations_raw
            if isinstance(r, dict) and 'book_code' in r and r['book_code']
        ]
        
    return render_template('main/allbooks.html', books=all_book_list, user_status=user_status, user_reservations=user_reservations)

대출 버튼이 눌렸을 경우, 로그인 여부를 확인하고 연체/최대대출권수/동일종류 책 대출 중 여부를 확인한다.

Find_available_copy()를 사용해 대출 가능한 책을 찾는다.

만약 예약자가 있을 경우 본인이 그 예약자가 아니라면 자신보다 먼저 예약한 사람이

있는 것이기 때문에 대출이 불가능하다.

Get_oldest_reservation_user()를 사용한다.

예약자가 없다면 바로 대출 가능하다.

대출은 borrow_book()으로 진행되고, 본인이 예약자였다면 delete_reservation()을 통해

예약 내역을 삭제한다.

@main_bp.route('/borrow/<int:book_code>', methods=['POST'])
def borrow(book_code):

    if 'user_id' not in session:
        flash("로그인이 필요한 서비스입니다.", "error")
        return redirect(url_for('auth.login'))
        
    user_id = session['user_id']
    user_status = db.get_user_borrowing_status(user_id)

    
    if user_status['is_overdue']:
        flash("연체 중인 도서가 있어 대출할 수 없습니다. 먼저 반납해주세요.", "error")
        return redirect(request.referrer or url_for('main.homepage'))
    if user_status['current_loan_count'] >= 3:
        flash("최대 3권까지만 대출할 수 있습니다.", "error")
        return redirect(request.referrer or url_for('main.homepage'))
    if book_code in user_status['borrowed_book_codes']:
        flash("이미 동일한 종류의 책을 대출 중입니다.", "error")
        return redirect(request.referrer or url_for('main.homepage'))

    
    available_copy_id = db.find_available_copy(book_code)
    
    oldest_reserver_id = db.get_oldest_reservation_user(book_code)

    #예약자가 있을 떄?
    if oldest_reserver_id is not None and available_copy_id is not None:
        # 본인이 젤 빠른 예약 아니면 안돼
        if oldest_reserver_id != user_id:
            flash("해당 도서는 다른 사용자가 먼저 예약하여 대출할 수 없습니다.", "warning")
            return redirect(request.referrer or url_for('main.all_books'))
        
        
    # 아니면 대출 가능
    elif available_copy_id is None:
        flash("해당 도서의 대출 가능한 재고가 없습니다.", "error")
        return redirect(request.referrer or url_for('main.all_books'))

#대출
    try:
        db.borrow_book(user_id, available_copy_id)
        
        # 필요하면 예약 기록 삭제
        if oldest_reserver_id == user_id:
            db.delete_reservation(user_id, book_code)
            flash("예약하신 도서 대출에 성공했습니다!", "success")
        else:
            flash("도서 대출에 성공했습니다!", "success")
            
    except Exception as e:
        flash(f"대출 처리 중 오류가 발생했습니다: {e}", "error")


    return redirect(request.referrer or url_for('main.all_books'))

예약버튼이 눌렸을 때도 비슷하게 동작한다.

@main_bp.route('/reserve/<int:book_code>', methods=['POST'])
def reserve(book_code):

    if 'user_id' not in session:
        flash("로그인이 필요한 서비스입니다.", "error")
        return redirect(url_for('auth.login'))
        
    user_id = session['user_id']
    user_status = db.get_user_borrowing_status(user_id)


    if user_status['is_overdue']:
        flash("연체 중인 도서가 있어 예약할 수 없습니다.", "error")
        return redirect(request.referrer or url_for('main.allbooks'))


    available_copy_id = db.find_available_copy(book_code)
    if available_copy_id:
        flash("현재 대출 가능한 재고가 있어 예약할 수 없습니다. 바로 대출해주세요.", "warning")
        return redirect(request.referrer or url_for('main.allbooks'))
        
    # 이미 예약?
    if db.check_existing_reservation(user_id, book_code):
        flash("이미 해당 도서를 예약하셨습니다.", "warning")
        return redirect(request.referrer or url_for('main.allbooks'))
            
    try:
        db.add_reservation(user_id, book_code)
        flash("도서 예약에 성공했습니다!", "success")
    except Exception as e:
        flash(f"예약 처리 중 오류가 발생했습니다: {e}", "error")

    return redirect(request.referrer or url_for('main.allbooks'))

2-3. 관련된 sql 쿼리들 (db.py)

Get_all_books()은 join 과 select 를 기본 쿼리로 두고, 뒤에 where,orderby str 을 추가로

붙이는 방식으로 검색 조건을 반영하고 정렬한다.

#모든 책 받아오기
def get_all_books(search_query=None, sort_by='title', sort_order='asc'):
    # 기본
    base_query = """
        SELECT
            bd.book_code, bd.title, bd.author,
            GROUP_CONCAT(DISTINCT c.category_name ORDER BY c.category_name SEPARATOR ', ') AS categories,
            COUNT(DISTINCT bs.book_id) AS total_quantity,
            COALESCE(avail_counts.available_count, 0) AS available_quantity 
        FROM book_data AS bd
        LEFT JOIN book_status AS bs ON bd.book_code = bs.book_code 
        LEFT JOIN book_category AS bc ON bd.book_code = bc.book_code
        LEFT JOIN category AS c ON bc.category_id = c.category_id
        LEFT JOIN (
            SELECT book_code, COUNT(book_id) AS available_count
            FROM book_status
            WHERE is_rent = FALSE
            GROUP BY book_code
        ) AS avail_counts ON bd.book_code = avail_counts.book_code
    """
    
    params = []
    where_sql = ""

    # 검색
    if search_query:
        search_pattern = f"%{search_query}%"
        where_sql = " WHERE (bd.title LIKE %s OR bd.author LIKE %s OR c.category_name LIKE %s)"
        params.extend([search_pattern, search_pattern, search_pattern])

    # 최종
    final_query = base_query + where_sql
    final_query += " GROUP BY bd.book_code"

    # 정렬
    allowed_sort_columns = {'title': 'bd.title', 'author': 'bd.author', 'category': 'categories'}
    sort_column = allowed_sort_columns.get(sort_by, 'bd.title')
    order = 'DESC' if sort_order.lower() == 'desc' else 'ASC'
    final_query += f" ORDER BY {sort_column} {order}"

    return execute_query(final_query, params=tuple(params), fetch='all')

Get_user_borrowing_status()는 user_id 를 받아 대출 중인 책과 연체 여부를 반환한다.

#유저 대출 상태 확인
def get_user_borrowing_status(user_id):
    
    query = """
        SELECT r.rent_date, bs.book_code
        FROM rent AS r
        JOIN book_status AS bs ON r.book_id = bs.book_id
        WHERE r.user_id = %s AND r.return_date IS NULL;
    """
    current_rentals = execute_query(query, params=(user_id,), fetch='all')

    current_loan_count = len(current_rentals)
    is_overdue = False
    borrowed_book_codes = set() 

    seven_days_ago = datetime.now() - timedelta(days=7)
    for rental in current_rentals:
        borrowed_book_codes.add(rental['book_code'])
        # rent_date가 7일보다 오래되면
        if rental['rent_date'] < seven_days_ago:
            is_overdue = True
            break 

    return {
        'current_loan_count': current_loan_count,
        'is_overdue': is_overdue,
        'borrowed_book_codes': list(borrowed_book_codes) 
    }

Get_user_reservations()는 단순히 조인을 한 후 유저아이디에 맞는 튜플만 반환한다.

Find_available_copy()는 book_code 를 입력받아 대출 가능한 book_id 를 반환한다.

Get_oldest_resercation_user()는 특정 도서를 가장먼저 예약한 사람의 id 를 반환한다.

Borrow_book()은 rent 와 book_status 에 각각 대출내역을 적용시켜주는 두 줄짜리 함수이다.

Delete_reservation()은 reservation 테이블에서 delete 를 한다.

#유저 예약 기록 받아오기
def get_user_reservations(user_id):
    print('get_user_reservations 호출:', user_id)
    query = """
        SELECT bd.title, bd.author, r.reservation_date, r.book_code
        FROM reservation AS r
        JOIN book_data AS bd ON r.book_code = bd.book_code
        WHERE r.user_id = %s
        ORDER BY r.reservation_date DESC;
    """
    return execute_query(query, params=(user_id,), fetch='all')
    
#대출 가능한 책 찾기
def find_available_copy(book_code):
    query = """
        SELECT book_id FROM book_status
        WHERE book_code = %s AND is_rent = FALSE
        LIMIT 1; 
    """
    result = execute_query(query, params=(book_code,), fetch='one')
    return result['book_id'] if result else None
    
#가장 먼저 예약한 사용자 찾기
def get_oldest_reservation_user(book_code):
    query = """
        SELECT user_id FROM reservation 
        WHERE book_code = %s
        ORDER BY reservation_date ASC 
        LIMIT 1;
    """
    result = execute_query(query, params=(book_code,), fetch='one')
    return result['user_id'] if result else None
    
#책빌리기
def borrow_book(user_id, book_id):
    # 대여 기록 추가
    add_rent_query = "INSERT INTO rent (user_id, book_id, rent_date) VALUES (%s, %s, NOW());"
    execute_query(add_rent_query, params=(user_id, book_id))

    # 책 상태 대여중
    update_status_query = "UPDATE book_status SET is_rent = TRUE WHERE book_id = %s;"
    execute_query(update_status_query, params=(book_id,))
    
#예약 삭제하기
def delete_reservation(user_id, book_code):
    query = "DELETE FROM reservation WHERE user_id = %s AND book_code = %s;"
    execute_query(query, params=(user_id, book_code))

 

3. 인기서적 페이지 (topbooks.html)

3-1. templates/main/topbooks.html

이 페이지는 별도의 조작가능한 버튼들은 없다.

도서 개별 순위와 카테고리별 순위를 보여주고, 각각 10 권, 5 개의 순위까지 보여준다.

3-2. main/routes.py

단순히 쿼리 함수의 실행값을 전달한다.

3-3. 관련된 sql 쿼리들 (db.py)

Get_popular_categories_last_3_months 와 get_popular_books_last_3_months 는

3 달 전의 날짜를 계산한 후 where 을 통해 rent_date 가 해당 날짜보다 최근인 데이터에 대해 통계를 낸다.

#최근 3개월간 가장 인기있는 책 상위 10권
def get_popular_books_last_3_months(limit=10):
    """최근 3개월간 가장 많이 대출된 책 (종류별) 순위를 반환합니다."""
    # 3개월 전 날짜 계산
    three_months_ago = datetime.now() - timedelta(days=90)
    
    query = """
        SELECT bd.title, bd.author, COUNT(r.rent_id) AS rental_count
        FROM rent AS r
        JOIN book_status AS bs ON r.book_id = bs.book_id
        JOIN book_data AS bd ON bs.book_code = bd.book_code
        WHERE r.rent_date >= %s 
        GROUP BY bd.book_code
        ORDER BY rental_count DESC
        LIMIT %s;
    """
    
    return execute_query(query, params=(three_months_ago, limit), fetch='all')

# 가장 인기있는 카테고리 상위 5개
def get_popular_categories_last_3_months(limit=5):
    three_months_ago = datetime.now() - timedelta(days=90)
    
    query = """
        SELECT c.category_name, COUNT(r.rent_id) AS rental_count
        FROM rent AS r
        JOIN book_status AS bs ON r.book_id = bs.book_id
        JOIN book_category AS bc ON bs.book_code = bc.book_code
        JOIN category AS c ON bc.category_id = c.category_id
        WHERE r.rent_date >= %s
        GROUP BY c.category_id
        ORDER BY rental_count DESC
        LIMIT %s;
    """
    return execute_query(query, params=(three_months_ago, limit), fetch='all')

 

이렇게 모든 페이지를 만들었다.

다음 글에서는 실제 웹에 배포하는 내용을 설명할 예정이다.

 

이 글을 쓰는 시점에서는 이미 배포가 되었고, 그 과정중에 자잘한 코드 수정들이 있었다.

따라서 최종적으로 수정된 코드를 보고 싶다면 아래의 깃헙에서 확인가능하다.

https://github.com/april2901/library-web-app

GitHub - april2901/library-web-app: library website using flask, mysql