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