review-automation-server/analytics/utils.py

from django.db.models import Count
from review.models import Review


def get_review_count_by_month(location_id):
    sql = f'''
    SELECT review_id, MONTHNAME(create_time) as month, COUNT(review_id) as total_review
    FROM review_review
    WHERE DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL -1 YEAR) <= create_time and location_id={location_id}
    GROUP BY MONTH(create_time)
    ORDER BY DATE(create_time) DESC
    '''
    qs = Review.objects.raw(sql)
    label = [q.month for q in qs]
    total_review = [q.total_review for q in qs]
    star_ratings = []
    for i in range(1, 6):
        sql = f'''
            SELECT review_id, MONTHNAME(create_time) as month, COUNT(review_id) as total_ratings
            FROM review_review
            WHERE DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL -1 YEAR) <= create_time
            and location_id={location_id} and star_rating={i}
            GROUP BY MONTH(create_time)
            ORDER BY DATE(create_time) DESC
            '''
        qs = Review.objects.raw(sql)
        ratings = [0]*len(label)
        for q in qs:
            ratings[label.index(q.month)] = q.total_ratings
        star_ratings.append(ratings)
    response = {
        'month': label,
        'total_reviews': total_review,
        'one_star': star_ratings[0],
        'two_star': star_ratings[1],
        'three_star': star_ratings[2],
        'four_star': star_ratings[3],
        'five_star': star_ratings[4]
    }
    return response


def get_review_count_by_month_all():
    sql = f'''
    SELECT review_id, MONTHNAME(create_time) as month, COUNT(review_id) as total_review
    FROM review_review
    GROUP BY MONTH(create_time)
    ORDER BY DATE(create_time) DESC
    '''
    qs = Review.objects.raw(sql)
    label = [q.month for q in qs]
    total_review = [q.total_review for q in qs]
    star_ratings = []
    for i in range(1, 6):
        sql = f'''
            SELECT review_id, MONTHNAME(create_time) as month, COUNT(review_id) as total_ratings
            FROM review_review
            WHERE star_rating={i}
            GROUP BY MONTH(create_time)
            ORDER BY DATE(create_time) DESC
            '''
        qs = Review.objects.raw(sql)
        ratings = [0]*len(label)
        for q in qs:
            ratings[label.index(q.month)] = q.total_ratings
        star_ratings.append(ratings)
    response = {
        'month': label,
        'total_reviews': total_review,
        'one_star': star_ratings[0],
        'two_star': star_ratings[1],
        'three_star': star_ratings[2],
        'four_star': star_ratings[3],
        'five_star': star_ratings[4]
    }
    return response