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


def get_list_of_reviews(google_reviews, facebook_reviews, yelp_reviews):
    google_review_list = list(google_reviews.exclude(star_rating__range=(3, 4)).
                              values('comment', 'star_rating', 'create_time', 'reviewer_name'))
    facebook_review_list = list(facebook_reviews.
                                values('id', 'recommendation_type', 'review_text', 'create_time'))

    yelp_review_list = list(yelp_reviews.exclude(rating__range=(3, 4)).
                            values('comment', 'reviewer_name', 'rating', 'date_posted'))
    all_revs = []

    for rev in google_review_list:
        if not rev['comment']:
            continue
        # if rev['star_rating'] == 5 and len(rev['comment']) < 100:
        #     continue
        rev['create_time'] = str(rev['create_time']).split()[0]
        rev['platform'] = 'Google'
        if rev['star_rating'] < 3:
            rev['positive'] = False
        else:
            rev['positive'] = True
        all_revs.append(rev)

    # Adding the facebook reviews
    for rev in facebook_review_list:
        if not rev['review_text']:
            continue
        rev['platform'] = 'Facebook'
        rev['create_time'] = str(rev['create_time']).split()[0]
        recommendation_type = rev.pop('recommendation_type')
        comment = rev.pop('review_text')
        reviewer = rev.pop('id')
        if recommendation_type:
            rev['positive'] = True
        else:
            rev['positive'] = False
        rev['comment'] = comment
        rev['reviewer_name'] = reviewer
        all_revs.append(rev)

    # Adding the yelp reviews
    for rev in yelp_review_list:
        if not rev['comment']:
            continue
        rev['platform'] = 'Yelp'
        date_posted = rev.pop('date_posted')
        rev['create_time'] = str(date_posted).split()[0]
        if rev['rating'] < 3:
            rev['positive'] = False
        else:
            rev['positive'] = True
        all_revs.append(rev)
    return all_revs