review-automation-server/analytics/utils.py

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


def last_data(date, location_id):
    res = Review.objects.filter(create_time__gte=date, location_id=location_id).values('star_rating').annotate(total=Count('star_rating')).order_by('star_rating')
    ratings = [0] * 5
    for r in res:
        ratings[r.get('star_rating')-1] = r.get('total')
    return ratings


def get_review_count_by_month(location_id):
    now = timezone.now()
    day = 31 - now.day
    first_day_month = now - timezone.timedelta(days=now.day)
    this_month = last_data(first_day_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(DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL -1 YEAR), INTERVAL {day} DAY) <= create_time and location_id={location_id}
    GROUP BY MONTH(create_time)
    ORDER BY DATE(create_time)
    '''
    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(DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL -1 YEAR), INTERVAL {day} DAY) <= create_time
            and location_id={location_id} and star_rating={i}
            GROUP BY MONTH(create_time)
            ORDER BY DATE(create_time)
            '''
        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 = {
        'label': 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],
        'this': this_month
    }
    return response


def get_review_count_by_week(location_id):
    now = timezone.now()
    day = 6 - now.weekday()
    first_day_week = now - timezone.timedelta(days=now.weekday()+1)
    this_week = last_data(first_day_week, location_id)
    sql = f'''
    SELECT review_id, WEEK(create_time) as week, COUNT(review_id) as total_review
    FROM review_review
    WHERE DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL -1 YEAR), INTERVAL {day} DAY) <= create_time and location_id={location_id}
    GROUP BY WEEK(create_time)
    ORDER BY DATE(create_time)
    '''
    qs = Review.objects.raw(sql)
    label = [q.week 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, WEEK(create_time) as week, COUNT(review_id) as total_ratings
            FROM review_review
            WHERE DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),INTERVAL -1 YEAR), INTERVAL {day} DAY) <= create_time
            and location_id={location_id} and star_rating={i}
            GROUP BY WEEK(create_time)
            ORDER BY DATE(create_time)
            '''
        qs = Review.objects.raw(sql)
        ratings = [0]*len(label)
        for q in qs:
            ratings[label.index(q.week)] = q.total_ratings
        star_ratings.append(ratings)
    response = {
        'label': 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],
        'this': this_week
    }
    return response