review-automation-server/analytics/utils.py

import numpy as np
from django.utils import timezone
from review.models import Review
from django.db.models import Count
from django.db import connection


def last_month_reviews(location_id):
    now = timezone.now()
    date = now.replace(day=1)
    day = (date - timezone.timedelta(days=1)).day + 1
    prev_date = date - timezone.timedelta(days=day)
    res = Review.objects.filter(create_time__range=(prev_date, 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 weekly_reviews_summary(location_id):
    now = timezone.now()
    date = now - timezone.timedelta(days=7)
    reviews = Review.objects.filter(
        create_time__gte=date,
        location_id=location_id
    )
    ratings = reviews.values('star_rating')\
        .annotate(total=Count('star_rating'))\
        .order_by('-star_rating')

    return reviews, ratings


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_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 WEEK(create_time) as month, star_rating, COALESCE(COUNT(*), 0) 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), star_rating
            ORDER BY create_time;
           '''
    with connection.cursor() as cursor:
        cursor.execute(sql)
        rows = cursor.fetchall()
        # It will transform a cursor like ((3, 5, 26), (43, 3, 21), ...)
        # to a dict like this {(3, 5): 26, (43, 3): 21), ...}
        # Here key tuple (3, 5) means 5 star ratings in 3rd week of the year and value is the number of star count.
        row_dict = {(row[0], row[1]): row[2] for row in rows}
        # Get all week names for graph label
        labels = []
        for row in rows:
            if row[0] not in labels:
                labels.append(row[0])
        star_ratings = []
        for i in range(1, 6):
            row = [row_dict.get((w, i), 0) for w in labels]
            star_ratings.append(row)
    total_review = list(np.sum(star_ratings, axis=0))

    response = {
        'label': labels,
        '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


def get_review_count_by_month(location_id):
    now = timezone.now()
    date = now.replace(day=1) - timezone.timedelta(days=1)
    day = date.day - now.day
    first_day_month = now - timezone.timedelta(days=now.day)
    this_month = last_data(first_day_month, location_id)
    sql = f'''
            SELECT MONTHNAME(create_time) as month, star_rating, COALESCE(COUNT(*), 0) 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 MONTHNAME(create_time), star_rating
            ORDER BY create_time;
           '''
    with connection.cursor() as cursor:
        cursor.execute(sql)
        rows = cursor.fetchall()
        # It will transform a cursor like (('September', 5, 26), ('October', 3, 21), ...)
        # to a dict like this {('September', 5): 26, ('October', 3): 21), ...}
        # Here key tuple ('September', 5) means 5 star ratings in September month and value is the number of star count.
        row_dict = {(row[0], row[1]): row[2] for row in rows}
        # Get all month names for graph label
        labels = []
        for row in rows:
            if row[0] not in labels:
                labels.append(row[0])
        star_ratings = []
        for i in range(1, 6):
            row = [row_dict.get((m, i), 0) for m in labels]
            star_ratings.append(row)
    total_review = list(np.sum(star_ratings, axis=0))

    response = {
        'label': labels,
        '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