def test_trunc_hour_func(self):
start_datetime = microsecond_support(datetime(2015, 6, 15, 14, 30, 50, 321))
end_datetime = truncate_to(microsecond_support(datetime(2016, 6, 15, 14, 10, 50, 123)), 'hour')
if settings.USE_TZ:
start_datetime = timezone.make_aware(start_datetime, is_dst=False)
end_datetime = timezone.make_aware(end_datetime, is_dst=False)
self.create_model(start_datetime, end_datetime)
self.create_model(end_datetime, start_datetime)
self.assertQuerysetEqual(
DTModel.objects.annotate(extracted=TruncHour('start_datetime')).order_by('start_datetime'),
[
(start_datetime, truncate_to(start_datetime, 'hour')),
(end_datetime, truncate_to(end_datetime, 'hour')),
],
lambda m: (m.start_datetime, m.extracted)
)
self.assertQuerysetEqual(
DTModel.objects.annotate(extracted=TruncHour('start_time')).order_by('start_datetime'),
[
(start_datetime, truncate_to(start_datetime.time(), 'hour')),
(end_datetime, truncate_to(end_datetime.time(), 'hour')),
],
lambda m: (m.start_datetime, m.extracted)
)
self.assertEqual(DTModel.objects.filter(start_datetime=TruncHour('start_datetime')).count(), 1)
with self.assertRaisesMessage(ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"):
list(DTModel.objects.annotate(truncated=TruncHour('start_date')))
with self.assertRaisesMessage(ValueError, "Cannot truncate DateField 'start_date' to DateTimeField"):
list(DTModel.objects.annotate(truncated=TruncHour('start_date', output_field=DateField())))
def hourly_baseline_comparison(curr_date):
today_min = datetime.datetime.combine(curr_date, datetime.time.min)
today_max = datetime.datetime.combine(today_min, datetime.time.max)
yesterday = curr_date-datetime.timedelta(days=1)
daily_count = {}
avg = [0]*24
date_now = timezone.now()
oldest_day = (date_now-Metric.objects.all().order_by('date').first().date).days
for i in range(1,oldest_day+1):
curr_day = curr_date-datetime.timedelta(days=i)
for city in settings.CITIES_ZIP_CODE:#count for each city daily count
code_from = settings.CITIES_ZIP_CODE[city][0]
code_to = settings.CITIES_ZIP_CODE[city][1]
result = Metric.objects.filter(date__day=curr_day.day,
date__month=curr_day.month,
date__year=curr_day.year,
postal_code__postal_code__range=(code_from,code_to))
if not city in daily_count:
daily_count[city]=[{'date':curr_day,'value':len(result)}]
else:
daily_count[city].append({'date':curr_day,'value':len(result)})
result = Metric.objects.filter(date__day=curr_day.day,
date__month=curr_day.month,
date__year=curr_day.year) \
.annotate(hour=TruncHour('date')) \
.values('hour') \
.annotate(hour_count=Count('hour')) \
.values('hour','hour_count')
for item in result:
# import pdb;pdb.set_trace()
avg[item['hour'].hour]+=item['hour_count']
avg = [item/(oldest_day) for item in avg]
today_records = Metric.objects.filter(date__range=(today_min, today_max)) \
.annotate(hour=TruncHour('date')) \
.values('hour') \
.annotate(hour_count=Count('hour')) \
.values('hour','hour_count')
# import pdb;pdb.set_trace()
result_list = [0]*24
for i in range(len(today_records)):
temp = {
'date':today_records[i]['hour'].strftime("%Y-%m-%dT%H:%M:%SZ"),
'value1':today_records[i]['hour_count'],
'value2':avg[today_records[i]['hour'].hour],
'previousDate':today_records[i]['hour'].strftime("%Y-%m-%dT%H:%M:%SZ")
}
result_list[today_records[i]['hour'].hour]=temp
# print(daily_count)
return result_list,daily_count
def plot(request):
"""Create json data for plot using Vega/Altair"""
filtered = RequestFilter(request.GET, queryset=Request.objects.all())
data = (filtered.qs.annotate(
hour=TruncHour("time")).values("hour").annotate(
count=Count("id")).order_by())
df = pandas.DataFrame.from_records(data)
def get_url(hour: pandas.Timestamp):
"""generate url to browse page"""
nexthour = hour + datetime.timedelta(hours=1)
return (
reverse("statistic_browse") +
f"?time_after={hour.strftime('%d.%m.%Y %H:%M')}&time_before={nexthour.strftime('%d.%m.%Y %H:%M')}"
)
df["url"] = df["hour"].apply(get_url)
chart = (altair.Chart(df).mark_bar(color="green").encode(
x=altair.X("hour:T", title="Zeitpunkt"),
y=altair.Y("count:Q", title="Aufrufe pro Stunde"),
href="url:N",
tooltip=[
altair.Tooltip("hour", format="%d.%m. %H Uhr", title="Zeitpunkt"),
altair.Tooltip("count", title="Aufrufe"),
],
).properties(width=1000))
return HttpResponse(chart.to_json(), content_type="application/json")
def groundhogs_by_hour_of_day():
result = RemovalsByLocation.objects.annotate(
# hour=TruncHour('removal_time')).values('hour', 'sex',) \
hour=TruncHour('removal_time')).values('hour', ) \
.annotate(kills_per_hour=Count('id')) \
.order_by('hour')
return result
def build_upcoming_srs_for_user(user):
start, finish = get_24_hour_time_span()
reviews = get_users_reviews(user).filter(
next_review_date__range=(start, finish)
)
reviews = (
reviews.annotate(
hour=TruncHour("next_review_date", tzinfo=timezone.utc)
)
.annotate(date=TruncDate("next_review_date", tzinfo=timezone.utc))
.values("date", "hour")
.annotate(review_count=Count("id"))
.order_by("date", "hour")
)
logger.debug(f"Building upcoming SRS details for {user.username}")
expected_hour = start.hour
hours = [hour % 24 for hour in range(expected_hour, expected_hour + 24)]
retval = OrderedDict.fromkeys(hours, 0)
for review in reviews:
found_hour = review["hour"].hour
while found_hour != expected_hour:
logger.debug(f"{found_hour} != {expected_hour}, skipping.")
expected_hour = (expected_hour + 1) % 24
retval[expected_hour] = review["review_count"]
logger.debug(f"Inserting reviews at hour {expected_hour}")
real_retval = [value for key, value in retval.items()]
return real_retval
def checkin_stats_api(request):
timeseries = CheckIn.objects.all().annotate(hour=TruncHour('update_time')) \
.values('hour').annotate(checkins=Count('hour'))
checkin_count = len(CheckIn.objects.all())
hacker_attrition_rate = {
'Attrition rate': attrition_rate(HackerApplication)
}
volunteer_attrition_rate = {
'Attrition rate': attrition_rate(VolunteerApplication)
}
mentor_attrition_rate = {
'Attrition rate': attrition_rate(MentorApplication)
}
sponsor_attrition_rate = {
'Attrition rate': attrition_rate(SponsorApplication)
}
return JsonResponse({
'update_time': timezone.now(),
'timeseries': list(timeseries),
'checkin_count': checkin_count,
'hacker_attrition_rate': hacker_attrition_rate,
'volunteer_attrition_rate': volunteer_attrition_rate,
'mentor_attrition_rate': mentor_attrition_rate,
'sponsor_attrition_rate': sponsor_attrition_rate,
})
def codes(self, request, server_pk=None, server_endpoint_pk=None):
grouping = request.GET.get('grouping', 'month')
qs_grouping = {'grouping': TruncMonth('date_added')}
limit = 14
if grouping == 'hour':
qs_grouping = {'grouping': TruncHour('date_added')}
limit = 24
elif grouping == 'day':
qs_grouping = {'grouping': TruncDay('date_added')}
limit = 24
pings_qs = models.Ping.objects.filter(endpoint__id=server_endpoint_pk).order_by('grouping').annotate(**qs_grouping).values('grouping','response_code').annotate(
Count('response_code')
).order_by("-grouping")[:24]
pings = {}
for ping in pings_qs:
date = ping['grouping'].__str__()
if pings.get(date) is None:
pings[date] = {
'date': date,
'codes': {}
}
pings.get(date)['codes'][ ping['response_code'] ] = ping['response_code__count']
return Response(pings)
def to_representation(self, user):
now = timezone.now()
one_day_from_now = now + datetime.timedelta(hours=24)
reviews = get_users_reviews(user).filter(next_review_date__range=(now, one_day_from_now)) \
.annotate(hour=TruncHour('next_review_date', tzinfo=timezone.utc)) \
.annotate(date=TruncDate('next_review_date', tzinfo=timezone.utc)) \
.values("streak", "date", "hour") \
.annotate(review_count=Count('id')).order_by("date", "hour")
expected_hour = now.hour
hours = [
hour % 24 for hour in range(expected_hour, expected_hour + 24)
]
retval = OrderedDict.fromkeys(hours)
for key in retval.keys():
retval[key] = OrderedDict.fromkeys(
[level.name for level in KwSrsLevel], 0)
for review in reviews:
found_hour = review['hour'].hour
while found_hour != expected_hour:
expected_hour = (expected_hour + 1) % 24
streak = review['streak']
srs_level = STREAK_TO_SRS_LEVEL_MAP_KW[streak].name
retval[expected_hour][srs_level] += review["review_count"]
real_retval = [[count for srs_level, count in hourly_count.items()]
for hour, hourly_count in retval.items()]
return real_retval
def build_upcoming_srs_for_user(user):
start, finish = get_24_hour_time_span()
reviews = get_users_reviews(user).filter(next_review_date__range=(start, finish))
for review in reviews:
logger.debug(review.next_review_date)
reviews = reviews \
.annotate(hour=TruncHour('next_review_date', tzinfo=timezone.utc)) \
.annotate(date=TruncDate('next_review_date', tzinfo=timezone.utc)) \
.values("date", "hour") \
.annotate(review_count=Count('id')).order_by("date", "hour")
expected_hour = start.hour
hours = [hour % 24 for hour in range(expected_hour, expected_hour + 24)]
retval = OrderedDict.fromkeys(hours, 0)
for review in reviews:
found_hour = review['hour'].hour
while found_hour != expected_hour:
logger.debug("{} != {}, skipping.".format(found_hour, expected_hour))
expected_hour = (expected_hour + 1) % 24
retval[expected_hour] = review["review_count"]
logger.debug("Inserting reviews at hour {}".format(expected_hour))
real_retval = [value for key, value in retval.items()]
return real_retval
def groundhogs_by_hour_of_day_by_sex():
result = RemovalsByLocation.objects.annotate(
# hour=TruncHour('removal_time')).values('hour', 'sex',) \
hour=TruncHour('removal_time')).values('hour', 'sex', ) \
.annotate(kills_per_hour=Count('id')) \
.order_by('hour')
# print("FUNCTION: {0}".format(result))
return result
def _group_by(queryset, group_field='winlogevent__created_on',
group_by=GroupBy.HOUR):
"""
group the rows in a :class:`django.db.models.query.QuerySet` by a time
sequence and `annotate` it with the time value
See `Trunc
<https://docs.djangoproject.com/en/2.2/ref/models/database-functions/"""\
"""#trunc>`__
and `TimeField truncation
<https://docs.djangoproject.com/en/2.2/ref/models/database-functions/"""\
""""#timefield-truncation>`__
in the `Django` docs.
The resulting `queryset` will look something like this:
==== ================== =========== ============= ======================
site host count fails count success hour
==== ================== =========== ============= ======================
LGH lgh01.healthbc.org 0 14 Aug. 1, 2019, midnight
LGH lgh01.healthbc.org 0 14 Aug. 1, 2019, 1 a.m.
==== ================== =========== ============= ======================
:arg queryset: the :class:`django.db.models.query.QuerySet`
:arg str group_field: the name of the :class:`django.db.models.Model`
field that contains time data; this field must be a
:class:`django.db.models.DateTimeField` or a
:class:`django.db.models.TimeField` field
Default is 'winlogevent__created_on'.
:arg group_by: group the data to be returned by a time
sequence; default is :attr:`GroupBy.HOUR`
:type group_by: :class:`GroupBy`
:returns: a :class:`django.db.models.query.QuerySet`
"""
if group_by == GroupBy.NONE:
return queryset
# NOTE: This could be handled with a dictionary mapping from GroupBy values
# to Trunc* classes. That was not pursued since there are only two
# cases
if group_by == GroupBy.MINUTE:
trunc_obj = TruncMinute(group_field)
elif group_by == GroupBy.HOUR:
trunc_obj = TruncHour(group_field)
else:
raise ValueError(f'_group_by argument group_by must be one of '
f'{[gb.value for gb in GroupBy]}. '
f'Received {group_by}.')
annotate_settings = {group_by.value: trunc_obj}
return queryset.annotate(**annotate_settings).values(group_by.value)
def counts_by_hour(self):
# Get list of dates
return Pulse.objects.annotate(
ts=TruncHour('created')).values('ts').distinct().annotate(
num_events=Coalesce(
models.Sum('count', filter=Q(host=self.ip)), 0),
num_bytes=Coalesce(models.Sum('bytes', filter=Q(host=self.ip)),
0)).values(
'ts', 'host', 'num_events',
'num_bytes').order_by('ts').iterator()
def home(request):
# COUNT MAIL
emails = Mail.external_objects.all()
email_count = emails.count()
suspicious = (emails.filter(
Q(tags__name__contains="suspicious")
| Q(urls__tags__name__contains="suspicious")
| Q(ips__tags__name__contains="suspicious")
| Q(urls__domain__tags__name__contains="suspicious")).distinct().count(
))
malicious = (emails.filter(
Q(tags__name__contains="malicious")
| Q(urls__tags__name__contains="malicious")
| Q(ips__tags__name__contains="malicious")
| Q(urls__domain__tags__name__contains="malicious")).distinct().count(
))
qs = (Mail.external_objects.filter(
submission_date__gte=timezone.now() - timedelta(days=10)).annotate(
thour=TruncHour("submission_date")).order_by())
record_by_time = pivot(
qs,
"thour",
"official_response",
"pk",
aggregation=Count,
display_transform=lambda x: x.lower().replace(" ", "_"),
).order_by("thour")
# PAGINATE LATEST EMAIL
table = LatestMailTable(
Mail.external_objects.prefetch_related(
"addresses",
"ips",
"urls",
"attachments",
"tags",
"addresses__tags",
"ips__tags",
"urls__tags",
"attachments__tags",
).order_by("-submission_date")[:250], )
table.paginate(page=request.GET.get("page", 1), per_page=25)
return render(
request,
"pages/main.html",
{
"table": table,
"email_count": email_count,
"suspicious": suspicious,
"malicious": malicious,
"groups": record_by_time,
},
)
def get_date(date=None, user=1):
if not date:
date = timezone.localtime(timezone.now()).date()
return functions.get_all_review(user=functions.get_user(user))\
.annotate(date=TruncDate('review_time'))\
.filter(date=date)\
.annotate(hour=TruncHour('review_time'))\
.values("hour")\
.annotate(count=Count('id'))\
.order_by("hour")
def votes_per_hour(cls, poll_id=None):
"""
Returns the quantity of votes by hour, if poll_id is set the return
will be filtered by poll
"""
qs = cls.objects.annotate(
hour=TruncHour('created_at')).values('hour').annotate(
votes=models.Count('id'))
if poll_id is not None:
qs = qs.filter(poll_id=poll_id)
return qs
def handle(self, *args, **options):
cities = list(
set(SensorLocation.objects.all().values_list(
"city", flat=True).order_by("city")))
for city in cities:
if not city:
continue
last_date_time = (SensorDataStat.objects.filter(
city_slug=slugify(city)).values_list(
"last_datetime", flat=True).order_by("-last_datetime")[:1])
if last_date_time:
queryset = SensorDataValue.objects.filter(
Q(sensordata__location__city__iexact=city),
# Get dates greater than last stat calculation
Q(created__gt=last_date_time),
# Ignore timestamp values
~Q(value_type="timestamp"),
# Match only valid float text
Q(value__regex=r"^\-?\d+(\.?\d+)?$"),
)
else:
queryset = SensorDataValue.objects.filter(
Q(sensordata__location__city__iexact=city),
# Ignore timestamp values
~Q(value_type="timestamp"),
# Match only valid float text
Q(value__regex=r"^\-?\d+(\.?\d+)?$"),
)
for stats in chunked_iterator(
queryset.annotate(timestamp=TruncHour("created")).values(
"timestamp",
"value_type",
"sensordata__sensor",
"sensordata__location",
"sensordata__sensor__node",
).order_by().annotate(
last_datetime=Max("created"),
average=Avg(Cast("value", FloatField())),
minimum=Min(Cast("value", FloatField())),
maximum=Max(Cast("value", FloatField())),
sample_size=Count("created", FloatField()),
).filter(
~Q(average=float("NaN")),
~Q(minimum=float("NaN")),
~Q(maximum=float("NaN")),
).order_by("timestamp")):
SensorDataStat.objects.bulk_create(
list(map(lambda stat: map_stat(stat, city), stats)))
def get(self, request, *args, **kwargs):
try:
url, clicks = get_click_set(request, kwargs)
except ShortenedUrl.DoesNotExist:
return HttpResponse("does not exist")
ts = clicks.annotate(t=TruncHour('timestamp')).values('t').annotate(
y=Count('id'))
# data = serializers.serialize("json", ts)
data = []
for x in ts:
data.append({"t": x["t"].isoformat(), "y": x["y"]})
logger.info(f"ts={ts} {data}")
return JsonResponse(data, safe=False)
def get_action_properties(context):
ads = Ad.objects.filter(approve=True)
for ad in ads:
context['actions'][ad] = {}
action = {}
clicks_count = 0
views_count = 0
clicks = Click.objects.filter(ad=ad).annotate(
hour=TruncHour('time')).values('hour').annotate(clicks=Count('id'))
views = View.objects.filter(ad=ad).annotate(
hour=TruncHour('time')).values('hour').annotate(views=Count('id'))
for v in views:
action[v['hour']] = [
v['hour'] + timedelta(hours=1), 0, v['views'], 0
]
views_count += v['views']
for c in clicks:
action[c['hour']][1] = c['clicks']
action[c['hour']][3] = c['clicks'] / action[c['hour']][2]
clicks_count += c['clicks']
context['actions'][ad]['action'] = sorted(action.items(),
reverse=True,
key=lambda t: t[1])
context['actions'][ad]['cpv'] = clicks_count / views_count
def trendline(self, *args, **kwargs):
# Get list of dates
qs = Pulse.objects.annotate(
ts=TruncHour('created')).values('ts').distinct().annotate(
num_events=Coalesce(models.Sum('count', filter=Q(token=self)),
0),
num_bytes=Coalesce(models.Sum('bytes', filter=Q(token=self)),
0)).values(
'ts', 'host', 'num_events',
'num_bytes').order_by('ts').iterator()
data = [x['num_events'] for x in qs]
if len(data) < 45:
data = [0 for x in range(45 - len(data))] + data
return ','.join([str(x) for x in data[-45:]])
def get_hourly_visits(self, start_date, end_date):
current_results = self.trackers \
.filter(timestamp__gte=start_date, timestamp__lte=end_date) \
.exclude(type_device=Tracker.BOT) \
.exclude(referrer_url__contains=self.website_url) \
.annotate(month=TruncHour('timestamp')) \
.values('month') \
.annotate(requests=Count('pk')).order_by('-month')
for item in current_results:
item['t'] = '{date}' \
.format(date=item.pop('month'))
item['y'] = item.pop('requests')
return list(current_results)
def _group_by(queryset,
group_field='winlogevent__created_on',
group_by=GroupBy.NONE):
"""
group the rows in a :class:`django.db.models.query.QuerySet` by a time
sequence and `annotate` it with the time value
See `Trunc
<https://docs.djangoproject.com/en/2.2/ref/models/database-functions/#trunc>`__
and `TimeField truncation
<https://docs.djangoproject.com/en/2.2/ref/models/database-functions/#timefield-truncation>`__
in the `Django` docs.
The resulting `queryset` will look something like this:
==== ================== =========== ============= ======================
site host count fails count success hour
==== ================== =========== ============= ======================
LGH lgh01.healthbc.org 0 14 Aug. 1, 2019, midnight
LGH lgh01.healthbc.org 0 14 Aug. 1, 2019, 1 a.m.
==== ================== =========== ============= ======================
:arg queryset: the :class:`django.db.models.query.QuerySet`
:arg str group_field: the name of the :class:`django.db.models.Model`
field that contains time data; this field must be a
:class:`django.db.models.DateTimeField` or a
:class:`django.db.models.TimeField` field
Default is 'winlogevent__created_on'.
:arg group_by: group the data to be returned by a time
sequence; default is :attr:`GroupBy.HOUR`
:type group_by: :class:`GroupBy`
:returns: a :class:`django.db.models.query.QuerySet`
"""
if group_by == GroupBy.HOUR:
return queryset.\
annotate(hour=TruncHour(group_field)).values('hour')
if group_by == GroupBy.MINUTE:
return queryset.\
annotate(minute=TruncMinute(group_field)).values('minute')
return queryset
def get_logins_by_event_state_borg_hour(now=None, time_delta=None):
"""
get the number of failed events and successful events during the interval
defined by the arguments for each monitoring site aggregated by hour
:arg datetime.datetime now: the initial moment
By default the initial moment is the value returned by
:meth:`django.utils.timezone.now`
:arg datetime.timedelta time_delta: the time interval to consider
By default, this will be retrieved from the dynamic preference
`Dead if not seen for more than
<../../../admin/dynamic_preferences/globalpreferencemodel/?q=dead_after>`__
:returns: a :class:`django.db.models.query.QuerySet` based on the
:class:`citrus_borg.models.WinlogbeatHost` model
"""
if now is None:
now = timezone.now()
if time_delta is None:
time_delta = get_preference('citrusborgcommon__dead_after')
if not isinstance(now, datetime.datetime):
raise TypeError('%s type invalid for %s' % (type(now), now))
if not isinstance(time_delta, datetime.timedelta):
raise TypeError('%s type invalid for %s' %
(type(time_delta), time_delta))
return WinlogbeatHost.objects.\
filter(winlogevent__created_on__gt=now - time_delta).\
annotate(hour=TruncHour('winlogevent__created_on')).values('hour').\
annotate(
failed_events=Count(
'winlogevent__event_state',
filter=Q(winlogevent__event_state__iexact='failed'))).\
annotate(
successful_events=Count(
'winlogevent__event_state',
filter=Q(winlogevent__event_state__iexact='successful'))).\
order_by('-hour', 'site__site')
def get(self, request):
results = request.query_params
if not isinstance(int(results['graph']), int):
return Response({
'err': '0',
'msg': 'Incorrect type for graph parameter'
})
try:
graph = Graph.objects.get(pk=int(results['graph']))
except Graph.DoesNotExist:
return Response({'err': '1', 'msg': 'No graph with given id'})
if not request.user.is_authenticated and not graph.public:
return Response({
'err':
'2',
'msg':
'This data cannot be viewed without authentication'
})
data = []
for s in graph.selector.all():
arr = []
entries = DataEntry.objects.annotate(
hour=TruncHour('timestamp')).filter(
type=s.type,
instance=s.instance).order_by('timestamp').all()
last_hour = entries[0].timestamp.hour
for e in entries:
if e.timestamp.hour != last_hour:
arr.append({
'date': e.timestamp,
'value': round(e.value, 2)
})
last_hour = e.timestamp.hour
data.append(arr)
return Response(data)
def get(self, request):
"""
Returns the average calls hour of day from the database.
"""
data = {"labels": [], "data": []}
# Get all calls grouped by day and hour, ordered by hour
day_calls = Call.objects.annotate(
hour=TruncHour('received_timestamp'),
day=TruncDay('received_timestamp'),
).values('day', 'hour').annotate(count=Count('pk')).order_by('hour')
# Creates a list to hold the total calls for each hour of the day
hours = [0] * 24
# Creates labels for each hour of the day
labels = []
for i in range(0, 24):
hour_label = str('0' + str(i) if i < 10 else i) + ':00'
labels.append(hour_label)
days = [] # The days that have been accounted for
total_days = 0 # The total number of days in the data set
# Iterate through each day-hour call total and add the count of calls
# to the hours list. When encountering a new day value, increment the
# total days (used for calculating the average)
for call in day_calls:
if call["day"] not in days:
days.append(call["day"])
total_days += 1
hours[call["hour"].hour] += call["count"]
# Calculate average over each day of results
hours = [round(hour_count / total_days, 2) for hour_count in hours]
# Add the data to the results list
data["labels"] = labels
data["data"] = hours
# Return the JSON response
return JsonResponse({"status": "true", "data": data})
def chart_tod(request):
h_list = Homicide.objects.annotate(
hour=TruncHour('time')).values('hour').annotate(
ct=Count('count')).values('hour', 'ct', 'date', 'gender')
data = alt.Data(values=list(h_list))
chart1 = alt.Chart(data, title='ToD (Time) vs Date Scatter').mark_circle(
size=100).encode(alt.X('date:T'),
alt.Y('hour:O'),
color='gender:N',
tooltip=['gender:N', 'ct:Q', 'hour:O',
'date:T']).interactive()
chart2 = alt.Chart(data, title='ToD Summary').mark_bar().encode(
x='count()', y=alt.Y('hour:O'), color='gender:N').properties(width=100)
chart = alt.hconcat(chart1, chart2)
return JsonResponse(chart.to_dict(), safe=False)
def measurements(request):
start = request.GET.get("start", None)
end = request.GET.get("end", None)
interval = request.GET.get("interval", "minute")
if start is not None:
start = parser.parse(start)
if end is not None:
end = parser.parse(end)
if start is None:
start = datetime.datetime(1970, 1, 1)
if end is None:
end = datetime.datetime.now()
measurements = Measurement.objects.filter(
Q(timestamp__gte=start, timestamp__lte=end))
if interval == "hour":
measurements = (measurements.annotate(
datetime=TruncHour("timestamp")).values("datetime").annotate(
pm25=Avg("pm25")).annotate(pm10=Avg("pm10")).annotate(
co2=Avg("co2")).annotate(
temperature=Avg("temperature")).annotate(
humidity=Avg("humidity")).values(
"datetime", "pm25", "pm10", "co2",
"temperature", "humidity"))
elif interval == "day":
measurements = (measurements.annotate(
datetime=TruncDay("timestamp")).values("datetime").annotate(
pm25=Avg("pm25")).annotate(pm10=Avg("pm10")).annotate(
co2=Avg("co2")).annotate(
temperature=Avg("temperature")).annotate(
humidity=Avg("humidity")).values(
"datetime", "pm25", "pm10", "co2",
"temperature", "humidity"))
else:
measurements = measurements.annotate(datetime=F("timestamp")).values(
"datetime", "pm25", "pm10", "co2", "temperature", "humidity")
return JsonResponse(list(measurements),
encoder=DjangoJSONEncoder,
safe=False)
def stats(self, request, server_pk=None, server_endpoint_pk=None):
grouping = request.GET.get('grouping', 'month')
qs_grouping = {'grouping': TruncMonth('date_added')}
limit = 14
if grouping == 'hour':
qs_grouping = {'grouping': TruncHour('date_added')}
limit = 24
elif grouping == 'day':
qs_grouping = {'grouping': TruncDay('date_added')}
limit = 24
pings = models.Ping.objects.filter(endpoint__id=server_endpoint_pk).order_by('grouping').annotate(**qs_grouping).values('grouping').annotate(
Max('response_time'),
Min('response_time'),
Avg('response_time'),
Count('response_time'),
Count('response_code')
).order_by("-grouping")[:24]
return Response(pings)
def get(self, request, format=None):
week_previous = timezone.now() - timedelta(days=7)
tweets_by_hour = Tweet.objects.filter(created_at__gte=week_previous) \
.annotate(hour=TruncHour('created_at')) \
.values('hour').annotate(count=Count('id')) \
.values('hour', 'count').order_by('hour')
most_retweeted = Tweet.objects.raw("""
select distinct on (date(created_at at time zone 'US/Central')) *
from tweets_tweet where created_at > NOW() - INTERVAL '7 days'
order by (date(created_at at time zone 'US/Central')), retweet_count desc;
""")
most_retweeted_by_day = []
for record in most_retweeted:
most_retweeted_by_day.append(TweetSerializer(record).data)
return Response({
'tweets_by_hour': tweets_by_hour,
'most_retweeted_by_day': most_retweeted_by_day
})
def checkin_stats_api(request):
timeseries = CheckIn.objects.all().annotate(hour=TruncHour('update_time')) \
.values('hour').annotate(checkins=Count('hour'))
checkin_count = len(CheckIn.objects.all())
applications = list(Application.objects.all())
attended = 0
confirmed = 0
for a in applications:
if a.status == APP_CONFIRMED:
confirmed += 1
if a.status == APP_ATTENDED:
attended += 1
attrition_rate = {
'Attrition rate': attended * 100 / (confirmed + attended)
}
return JsonResponse({
'update_time': timezone.now(),
'timeseries': list(timeseries),
'checkin_count': checkin_count,
'attrition_rate': attrition_rate
})
def stats(request, checkplace: int):
qs = Check.objects.filter(check_place=checkplace)
if request.method == 'GET':
qs = qs.filter(created_at__month=localtime().month)
form = FilterForm(
initial={'prec': request.META.get('HTTP_REFERER', '')}
) # quand on filtre, ça se rajoute à l'historique, donc c'est chiant pour les écrans de scan
elif request.method == 'POST':
form = FilterForm(request.POST)
form.is_valid()
qs = qs.filter(
created_at__gte=form.cleaned_data.get('start', debut_ce_mois()),
created_at__lte=form.cleaned_data.get('end', fin_ce_mois()))
return render(
request, 'checker/stats.html', {
'form':
form,
'place':
CheckPlace.objects.get(id=checkplace),
'checks':
qs.annotate(hour=TruncHour('created_at')).values('hour').annotate(
total=Count('id')).order_by('hour')
})
