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_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 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


def monthly_review_count(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 15 DAY) <= create_time and location_id={location_id}
            GROUP BY MONTH(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 'September_5' means 5 star ratings in September month and value is the number of star count.
        row_dict = {row[0] + '_' + str(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])
        row_lists = []
        for i in range(1, 6):
            row = [row_dict.get(m+'_'+str(i), 0) for m in labels]
            row_lists.append(row)
    return labels, row_lists


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 (('September', 5, 26), ('October', 3, 21), ...)
        # to a dict like this {'September_5': 26, 'October_3', 21), ...}
        # Here key 'September_5' means 5 star ratings in September month and value is the number of star count.
        row_dict = {str(row[0]) + '_' + str(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(str(w) + '_' + str(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 'September_5' means 5 star ratings in September month and value is the number of star count.
        row_dict = {str(row[0]) + '_' + str(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(str(m) + '_' + str(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