utils.py 8.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257
  1. import numpy as np
  2. from django.utils import timezone
  3. from django.db import connection
  4. from django.db.models import Count
  5. from review.models import Review
  6. from facebook_app.models import FacebookReview, FacebookPage
  7. from yelp.models import YelpReview, YelpLocation
  8. DATE_IDENTIFIER = {
  9. 'google': 'create_time',
  10. 'yelp': 'date_posted',
  11. 'facebook': 'create_time'
  12. }
  13. REVIEW_IDENTIFIER = {
  14. 'google': 'star_rating',
  15. 'yelp': 'rating',
  16. 'facebook': 'recommendation_type'
  17. }
  18. TABLE_NAME = {
  19. 'google': 'review_review',
  20. 'facebook': 'facebook_app_facebookreview',
  21. 'yelp': 'yelp_yelpreview'
  22. }
  23. def get_google_review_report(location_id):
  24. now = timezone.now()
  25. beginning_of_month = now.replace(day=1, hour=0, minute=0, second=0)
  26. beginning_of_last_month = now - timezone.timedelta(days=now.day + 31)
  27. reviews = Review.objects.all()
  28. this_month = reviews.filter(
  29. location_id=location_id,
  30. create_time__range=(beginning_of_month, now)
  31. ).count()
  32. last_month = reviews.filter(
  33. location_id=location_id,
  34. create_time__range=(beginning_of_last_month, beginning_of_month)
  35. ).count()
  36. return {
  37. 'this_month': this_month,
  38. 'last_month': last_month
  39. }
  40. def get_facebook_report(location_id):
  41. now = timezone.now()
  42. beginning_of_month = now.replace(day=1, hour=0, minute=0, second=0)
  43. beginning_of_last_month = now - timezone.timedelta(days=now.day + 31)
  44. reviews = FacebookReview.objects.all()
  45. this_month = reviews.filter(
  46. page__location_id=location_id,
  47. create_time__range=(beginning_of_month, now)
  48. ).count()
  49. last_month = reviews.filter(
  50. page__location_id=location_id,
  51. create_time__range=(beginning_of_last_month, beginning_of_month)
  52. ).count()
  53. return {
  54. 'this_month': this_month,
  55. 'last_month': last_month
  56. }
  57. def get_yelp_review_report(location_id):
  58. now = timezone.now()
  59. beginning_of_month = now.replace(day=1, hour=0, minute=0, second=0)
  60. beginning_of_last_month = now - timezone.timedelta(days=now.day + 31)
  61. pos = YelpReview.objects.filter(
  62. location__location_id=location_id,
  63. date_posted__range=(beginning_of_month, now),
  64. rating__gte=3
  65. ).count()
  66. neg = YelpReview.objects.filter(
  67. location__location_id=location_id,
  68. date_posted__range=(beginning_of_month, now),
  69. rating__lte=2
  70. ).count()
  71. pos_last_month = YelpReview.objects.filter(
  72. location__location_id=location_id,
  73. date_posted__range=(beginning_of_last_month, beginning_of_month),
  74. rating__gte=3
  75. ).count()
  76. neg_last_month = YelpReview.objects.filter(
  77. location__location_id=location_id,
  78. date_posted__range=(beginning_of_last_month, beginning_of_month),
  79. rating__lte=2
  80. ).count()
  81. pos_growth = 'inf' if pos_last_month == 0 else round(((((pos / now.day) * 30) - pos_last_month) / pos_last_month) * 100, 2)
  82. neg_growth = 'inf' if neg_last_month == 0 else round(((((neg / now.day) * 30) - neg_last_month) / neg_last_month) * 100, 2)
  83. total = pos + neg
  84. last_month_total = pos_last_month + neg_last_month
  85. total_growth = 'inf' if last_month_total == 0 else round(((((total / now.day) * 30) - last_month_total) / last_month_total) * 100, 2)
  86. return {
  87. 'positive': pos,
  88. 'positive_growth': pos_growth,
  89. 'negative': neg,
  90. 'negative_growth': neg_growth,
  91. 'total': total,
  92. 'total_growth': total_growth
  93. }
  94. def get_this_month_analytics(location_id):
  95. now = timezone.now()
  96. beginning_of_month = now.replace(day=1, hour=0, minute=0, second=0)
  97. google_qs = Review.objects.filter(
  98. create_time__range=(beginning_of_month, now),
  99. location_id=location_id
  100. )\
  101. .values('create_time__day')\
  102. .annotate(total=Count('create_time__day'))
  103. google_qs_dict = {q['create_time__day']: q['total'] for q in google_qs}
  104. # yelp_qs = YelpReview.objects.filter(
  105. # location__location_id=location_id,
  106. # date_posted__range=(beginning_of_month, now),
  107. # )\
  108. # .values('date_posted__day')\
  109. # .annotate(total=Count('date_posted__day'))
  110. # yelp_qs_dict = {q['date_posted__day']: q['total'] for q in yelp_qs}
  111. facebook_qs = FacebookReview.objects.filter(
  112. page__location_id=location_id,
  113. create_time__range=(beginning_of_month, now),
  114. )\
  115. .values('create_time__day')\
  116. .annotate(total=Count('create_time__day'))
  117. facebook_qs_dict = {q['create_time__day']: q['total'] for q in facebook_qs}
  118. label = []
  119. facebook = []
  120. google = []
  121. for day in range(1, now.day+1):
  122. label.append(day)
  123. facebook.append(facebook_qs_dict.get(day, 0))
  124. # yelp.append(yelp_qs_dict.get(day, 0))
  125. google.append(google_qs_dict.get(day, 0))
  126. return {
  127. 'label': label,
  128. 'google': google,
  129. 'facebook': facebook,
  130. }
  131. def get_review_count_by_month(location_id, platform):
  132. now = timezone.now()
  133. date = now.replace(day=1) - timezone.timedelta(days=1)
  134. day = date.day - now.day
  135. curr_month = {
  136. }
  137. if platform == 'google':
  138. loc_id = location_id
  139. field_name = 'location_id'
  140. curr_month_data = Review.objects.filter(create_time__gte=date, location_id=location_id).values('star_rating')\
  141. .annotate(total=Count('star_rating')).order_by('star_rating')
  142. curr_month['label'] = [r.get('star_rating') for r in curr_month_data]
  143. curr_month['total'] = [r.get('total') for r in curr_month_data]
  144. elif platform == 'yelp':
  145. loc_id = YelpLocation.objects.get(location_id=location_id).id
  146. field_name = 'location_id'
  147. curr_month_data = YelpReview.objects.filter(date_posted__gte=date, location__location_id=location_id)\
  148. .values('rating').annotate(total=Count('rating')).order_by('rating')
  149. curr_month['label'] = [r.get('rating') for r in curr_month_data]
  150. curr_month['total'] = [r.get('total') for r in curr_month_data]
  151. elif platform == 'facebook':
  152. loc_id = FacebookPage.objects.get(location_id=location_id).id
  153. field_name = 'page_id'
  154. curr_month_data = FacebookReview.objects.filter(create_time__gte=date, page__location_id=location_id)\
  155. .values('recommendation_type').annotate(total=Count('recommendation_type'))
  156. curr_month['label'] = ['Recommended' if r.get('recommendation_type') else 'Not Recommended' for r in curr_month_data]
  157. curr_month['total'] = [r.get('total') for r in curr_month_data]
  158. else:
  159. raise ValueError(f'No platform name {platform}')
  160. sql = f'''
  161. SELECT MONTHNAME({DATE_IDENTIFIER[platform]}) as month, {REVIEW_IDENTIFIER[platform]}, COUNT(*) as total_review
  162. FROM {TABLE_NAME[platform]}
  163. WHERE DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),
  164. INTERVAL -1 YEAR), INTERVAL {day} DAY) <= {DATE_IDENTIFIER[platform]} and {field_name}={loc_id}
  165. GROUP BY MONTHNAME({DATE_IDENTIFIER[platform]}), {REVIEW_IDENTIFIER[platform]}
  166. ORDER BY {DATE_IDENTIFIER[platform]};
  167. '''
  168. with connection.cursor() as cursor:
  169. cursor.execute(sql)
  170. rows = cursor.fetchall()
  171. # It will transform a cursor like (('September', 5, 26), ('October', 3, 21), ...)
  172. # to a dict like this {('September', 5): 26, ('October', 3): 21), ...}
  173. # Here key tuple ('September', 5) means 5 star ratings in September month and value is the number of star count.
  174. row_dict = {(row[0], row[1]): row[2] for row in rows}
  175. # Get all month names for graph label
  176. labels = formatter_month(now.month)
  177. # labels = []
  178. # for row in rows:
  179. # if row[0] not in labels:
  180. # labels.append(row[0])
  181. # print(labels)
  182. star_ratings = []
  183. if platform == 'facebook':
  184. for i in range(2):
  185. row = [row_dict.get((m, i), 0) for m in labels]
  186. star_ratings.append(row)
  187. else:
  188. for i in range(1, 6):
  189. row = [row_dict.get((m, i), 0) for m in labels]
  190. star_ratings.append(row)
  191. total_review = list(np.sum(star_ratings, axis=0))
  192. response = {
  193. 'total_review': total_review,
  194. 'labels': labels,
  195. 'star_rating': star_ratings,
  196. 'curr_month': curr_month
  197. }
  198. return response
  199. def date_str2datetime(date):
  200. year, month, day = date.split('-')
  201. date = timezone.datetime(year=int(year), month=int(month), day=int(day))
  202. dt_aware = timezone.make_aware(date, timezone.get_current_timezone())
  203. return dt_aware
  204. def formatter_month(m):
  205. '''
  206. Make month this formate
  207. >>> formatter_month(5)
  208. [5, 4, 3, 2, 1, 12, 11, 10, 9, 8, 7, 6]
  209. >>> formatter_month(7)
  210. [7, 6, 5, 4, 3, 2, 1, 12, 11, 10, 9, 8]
  211. :param m: index of current month
  212. :return: formatter of mentioned formate
  213. '''
  214. month = ['January', 'February', 'March', 'April', 'May', 'June', 'July',
  215. 'August', 'September', 'October', 'November', 'December']
  216. return month[m:] + month[:m]