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'
}


def get_google_review_report(location_id):
    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)

    reviews = Review.objects.all()

    this_month_pos = reviews.filter(
        location_id=location_id,
        create_time__range=(beginning_of_month, now),
        star_rating__gte=4
    ).count()
    this_month_neg = reviews.filter(
        location_id=location_id,
        create_time__range=(beginning_of_month, now),
        star_rating__lte=3
    ).count()

    last_month_pos = reviews.filter(
        location_id=location_id,
        create_time__range=(beginning_of_last_month, beginning_of_month),
        star_rating__gte=4
    ).count()
    last_month_neg = reviews.filter(
        location_id=location_id,
        create_time__range=(beginning_of_last_month, beginning_of_month),
        star_rating__lt=4
    ).count()

    return {
        'this_month_pos': this_month_pos,
        'last_month_pos': last_month_pos,
        'this_month_neg': this_month_neg,
        'last_month_neg': last_month_neg
    }


def get_facebook_report(location_id):
    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)

    reviews = FacebookReview.objects.all()

    this_month_pos = reviews.filter(
        page__location_id=location_id,
        create_time__range=(beginning_of_month, now),
        recommendation_type=True
    ).count()

    last_month_pos = reviews.filter(
        page__location_id=location_id,
        create_time__range=(beginning_of_last_month, beginning_of_month),
        recommendation_type=True
    ).count()

    this_month_neg = reviews.filter(
        page__location_id=location_id,
        create_time__range=(beginning_of_month, now),
        recommendation_type=False
    ).count()

    last_month_neg = reviews.filter(
        page__location_id=location_id,
        create_time__range=(beginning_of_last_month, beginning_of_month),
        recommendation_type=False
    ).count()

    return {
        'this_month_pos': this_month_pos,
        'last_month_pos': last_month_pos,
        'this_month_neg': this_month_neg,
        'last_month_neg': last_month_neg
    }


def get_yelp_review_report(location_id):
    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)

    reviews = YelpReview.objects.all()

    this_month_pos = reviews.filter(
        location__location_id=location_id,
        date_posted__range=(beginning_of_month, now),
        rating__gte=4
    ).count()
    this_month_neg = reviews.filter(
        location__location_id=location_id,
        date_posted__range=(beginning_of_month, now),
        rating__lte=3
    ).count()

    last_month_pos = reviews.filter(
        location__location_id=location_id,
        date_posted__range=(beginning_of_last_month, beginning_of_month),
        rating__gte=4
    ).count()
    last_month_neg = reviews.filter(
        location__location_id=location_id,
        date_posted__range=(beginning_of_last_month, beginning_of_month),
        rating__lt=4
    ).count()

    return {
        'this_month_pos': this_month_pos,
        'last_month_pos': last_month_pos,
        'this_month_neg': this_month_neg,
        'last_month_neg': last_month_neg
    }


def get_this_month_analytics(location_id):
    now = timezone.now()
    beginning_of_month = now.replace(day=1, hour=0, minute=0, second=0)

    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 = []
    google = []
    yelp = []
    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 = formatter_month(now.month)

        # labels = []
        # for row in rows:
        #     if row[0] not in labels:
        #         labels.append(row[0])
        # print(labels)

        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


def date_str2datetime(date):
    year, month, day = date.split('-')
    date = timezone.datetime(year=int(year), month=int(month), day=int(day))
    dt_aware = timezone.make_aware(date, timezone.get_current_timezone())
    return dt_aware


def formatter_month(m):
    '''
    Make month this formate
    >>> formatter_month(5)
    [5, 4, 3, 2, 1, 12, 11, 10, 9, 8, 7, 6]
    >>> formatter_month(7)
    [7, 6, 5, 4, 3, 2, 1, 12, 11, 10, 9, 8]

    :param m: index of current month
    :return: formatter of mentioned formate
    '''
    month = ['January', 'February', 'March', 'April', 'May', 'June', 'July',
             'August', 'September', 'October', 'November', 'December']
    return month[m:] + month[:m]