review-automation-server/user/utils.py

from django.utils import timezone
from django.db.models import Count
from review.models import Review
from facebook_app.models import FacebookReview
from yelp.models import 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
    }