review-automation-server/user/utils.py

import numpy as np
from django.utils import timezone
from django.db import connection
from django.db.models import Count
from review.models import Review
from facebook_app.models import FacebookReview, FacebookPage
from yelp.models import YelpReview, YelpLocation

DATE_IDENTIFIER = {
    'google': 'create_time',
    'yelp': 'date_posted',
    'facebook': 'create_time'
}

REVIEW_IDENTIFIER = {
    'google': 'star_rating',
    'yelp': 'rating',
    'facebook': 'recommendation_type'
}

TABLE_NAME = {
    'google': 'review_review',
    'facebook': 'facebook_app_facebookreview',
    'yelp': 'yelp_yelpreview'
}

now = timezone.now()
beginning_of_month = now.replace(day=1, hour=0, minute=0, second=0)
beginning_of_last_month = now - timezone.timedelta(days=now.day+31)


def get_google_review_report(location_id):
    pos = Review.objects.filter(
        location_id=location_id,
        create_time__range=(beginning_of_month, now),
        star_rating__gte=3
    ).count()
    neg = Review.objects.filter(
        location_id=location_id,
        create_time__range=(beginning_of_month, now),
        star_rating__lte=2
    ).count()
    pos_last_month = Review.objects.filter(
        location_id=location_id,
        create_time__range=(beginning_of_last_month, beginning_of_month),
        star_rating__gte=3
    ).count()
    neg_last_month = Review.objects.filter(
        location_id=location_id,
        create_time__range=(beginning_of_last_month, beginning_of_month),
        star_rating__lte=2
    ).count()
    pos_growth = 'inf' if pos_last_month == 0 else round(((((pos / now.day) * 30) - pos_last_month) / pos_last_month) * 100, 2)
    neg_growth = 'inf' if neg_last_month == 0 else round(((((neg / now.day) * 30) - neg_last_month) / neg_last_month) * 100, 2)
    total = pos + neg
    last_month_total = pos_last_month + neg_last_month
    total_growth = 'inf' if last_month_total == 0 else round(((((total / now.day) * 30) - last_month_total) / last_month_total) * 100, 2)

    return {
        'positive': pos,
        'positive_growth': pos_growth,
        'negative': neg,
        'negative_growth': neg_growth,
        'total': total,
        'total_growth': total_growth
    }


def get_facebook_report(location_id):
    pos = FacebookReview.objects.filter(
        page__location_id=location_id,
        create_time__range=(beginning_of_month, now),
        recommendation_type=True
    ).count()
    neg = FacebookReview.objects.filter(
        page__location_id=location_id,
        create_time__range=(beginning_of_month, now),
        recommendation_type=False
    ).count()
    pos_last_month = FacebookReview.objects.filter(
        page__location_id=location_id,
        create_time__range=(beginning_of_last_month, beginning_of_month),
        recommendation_type=True
    ).count()
    neg_last_month = FacebookReview.objects.filter(
        page__location_id=location_id,
        create_time__range=(beginning_of_last_month, beginning_of_month),
        recommendation_type=False
    ).count()
    pos_growth = 'inf' if pos_last_month == 0 else round(((((pos / now.day) * 30) - pos_last_month) / pos_last_month) * 100, 2)
    neg_growth = 'inf' if neg_last_month == 0 else round(((((neg / now.day) * 30) - neg_last_month) / neg_last_month) * 100, 2)
    total = pos + neg
    last_month_total = pos_last_month + neg_last_month
    total_growth = 'inf' if last_month_total == 0 else round(((((total / now.day) * 30) - last_month_total) / last_month_total) * 100, 2)

    return {
        'positive': pos,
        'positive_growth': pos_growth,
        'negative': neg,
        'negative_growth': neg_growth,
        'total': total,
        'total_growth': total_growth
    }


def get_yelp_review_report(location_id):
    pos = YelpReview.objects.filter(
        location__location_id=location_id,
        date_posted__range=(beginning_of_month, now),
        rating__gte=3
    ).count()
    neg = YelpReview.objects.filter(
        location__location_id=location_id,
        date_posted__range=(beginning_of_month, now),
        rating__lte=2
    ).count()
    pos_last_month = YelpReview.objects.filter(
        location__location_id=location_id,
        date_posted__range=(beginning_of_last_month, beginning_of_month),
        rating__gte=3
    ).count()
    neg_last_month = YelpReview.objects.filter(
        location__location_id=location_id,
        date_posted__range=(beginning_of_last_month, beginning_of_month),
        rating__lte=2
    ).count()
    pos_growth = 'inf' if pos_last_month == 0 else round(((((pos / now.day) * 30) - pos_last_month) / pos_last_month) * 100, 2)
    neg_growth = 'inf' if neg_last_month == 0 else round(((((neg / now.day) * 30) - neg_last_month) / neg_last_month) * 100, 2)
    total = pos + neg
    last_month_total = pos_last_month + neg_last_month
    total_growth = 'inf' if last_month_total == 0 else round(((((total / now.day) * 30) - last_month_total) / last_month_total) * 100, 2)

    return {
        'positive': pos,
        'positive_growth': pos_growth,
        'negative': neg,
        'negative_growth': neg_growth,
        'total': total,
        'total_growth': total_growth
    }


def get_this_month_analytics(location_id):
    google_qs = Review.objects.filter(
        create_time__range=(beginning_of_month, now),
        location_id=location_id
    )\
        .values('create_time__day')\
        .annotate(total=Count('create_time__day'))
    google_qs_dict = {q['create_time__day']: q['total'] for q in google_qs}

    yelp_qs = YelpReview.objects.filter(
        location__location_id=location_id,
        date_posted__range=(beginning_of_month, now),
    )\
        .values('date_posted__day')\
        .annotate(total=Count('date_posted__day'))
    yelp_qs_dict = {q['date_posted__day']: q['total'] for q in yelp_qs}

    facebook_qs = FacebookReview.objects.filter(
        page__location_id=location_id,
        create_time__range=(beginning_of_month, now),
    )\
        .values('create_time__day')\
        .annotate(total=Count('create_time__day'))
    facebook_qs_dict = {q['create_time__day']: q['total'] for q in facebook_qs}

    label = []
    facebook = []
    yelp = []
    google = []
    for day in range(1, now.day+1):
        label.append(day)
        facebook.append(facebook_qs_dict.get(day, 0))
        yelp.append(yelp_qs_dict.get(day, 0))
        google.append(google_qs_dict.get(day, 0))

    return {
        'label': label,
        'google': google,
        'facebook': facebook,
        'yelp': yelp
    }


def get_review_count_by_month(location_id, platform):
    now = timezone.now()
    date = now.replace(day=1) - timezone.timedelta(days=1)
    day = date.day - now.day
    curr_month = {

    }
    if platform == 'google':
        loc_id = location_id
        field_name = 'location_id'
        curr_month_data = Review.objects.filter(create_time__gte=date, location_id=location_id).values('star_rating')\
            .annotate(total=Count('star_rating')).order_by('star_rating')
        curr_month['label'] = [r.get('star_rating') for r in curr_month_data]
        curr_month['total'] = [r.get('total') for r in curr_month_data]

    elif platform == 'yelp':
        loc_id = YelpLocation.objects.get(location_id=location_id).id
        field_name = 'location_id'
        curr_month_data = YelpReview.objects.filter(date_posted__gte=date, location__location_id=location_id)\
            .values('rating').annotate(total=Count('rating')).order_by('rating')
        curr_month['label'] = [r.get('rating') for r in curr_month_data]
        curr_month['total'] = [r.get('total') for r in curr_month_data]

    elif platform == 'facebook':
        loc_id = FacebookPage.objects.get(location_id=location_id).id
        field_name = 'page_id'
        curr_month_data = FacebookReview.objects.filter(create_time__gte=date, page__location_id=location_id)\
            .values('recommendation_type').annotate(total=Count('recommendation_type'))
        curr_month['label'] = ['Recommended' if r.get('recommendation_type') else 'Not Recommended' for r in curr_month_data]
        curr_month['total'] = [r.get('total') for r in curr_month_data]

    else:
        raise ValueError(f'No platform name {platform}')

    sql = f'''
            SELECT MONTHNAME({DATE_IDENTIFIER[platform]}) as month, {REVIEW_IDENTIFIER[platform]}, COUNT(*) as total_review
            FROM {TABLE_NAME[platform]}
            WHERE DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),
            INTERVAL -1 YEAR), INTERVAL {day} DAY) <= {DATE_IDENTIFIER[platform]} and {field_name}={loc_id}
            GROUP BY MONTHNAME({DATE_IDENTIFIER[platform]}), {REVIEW_IDENTIFIER[platform]}
            ORDER BY {DATE_IDENTIFIER[platform]};
           '''
    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 = []
        if platform == 'facebook':
            for i in range(2):
                row = [row_dict.get((m, i), 0) for m in labels]
                star_ratings.append(row)
        else:
            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 = {
        'total_review': total_review,
        'labels': labels,
        'star_rating': star_ratings,
        'curr_month': curr_month
    }
    return response