def get_registrations(self, interval, days, graph_key, select_box_value):
""" Returns an array with new users count per interval."""
try:
conf_data = DashboardStats.objects.get(graph_key=graph_key)
model_name = apps.get_model(conf_data.model_app_name,
conf_data.model_name)
kwargs = {}
for i in conf_data.criteria.all():
# fixed mapping value passed info kwargs
if i.criteria_fix_mapping:
for key in i.criteria_fix_mapping:
# value => i.criteria_fix_mapping[key]
kwargs[key] = i.criteria_fix_mapping[key]
# dynamic mapping value passed info kwargs
if i.dynamic_criteria_field_name and select_box_value:
kwargs[i.dynamic_criteria_field_name] = select_box_value
aggregate = None
if conf_data.type_operation_field_name and conf_data.operation_field_name:
operation = {
'Count': Count(conf_data.operation_field_name),
'Sum': Sum(conf_data.operation_field_name),
'Avg': Avg(conf_data.operation_field_name),
'StdDev': StdDev(conf_data.operation_field_name),
'Max': Max(conf_data.operation_field_name),
'Min': Min(conf_data.operation_field_name),
'Variance': Variance(conf_data.operation_field_name),
}
aggregate = operation[conf_data.type_operation_field_name]
stats = QuerySetStats(model_name.objects.filter(**kwargs),
conf_data.date_field_name, aggregate)
# stats = QuerySetStats(User.objects.filter(is_active=True), 'date_joined')
today = now()
if days == 24:
begin = today - timedelta(hours=days - 1)
return stats.time_series(begin, today + timedelta(hours=1),
interval)
begin = today - timedelta(days=days - 1)
return stats.time_series(begin, today + timedelta(days=1),
interval)
except (LookupError, FieldError, TypeError) as e:
self.error_message = str(e)
User = get_user_model()
stats = QuerySetStats(User.objects.filter(is_active=True),
'date_joined')
today = now()
if days == 24:
begin = today - timedelta(hours=days - 1)
return stats.time_series(begin, today + timedelta(hours=1),
interval)
begin = today - timedelta(days=days - 1)
return stats.time_series(begin, today + timedelta(days=1),
interval)
def test_time_series_weeks(self):
day = datetime.date(year=2013, month=4, day=5)
u = User.objects.create_user('user', '*****@*****.**')
u.date_joined = day
u.save()
qs = User.objects.all()
qss = QuerySetStats(qs, 'date_joined')
qss.time_series(day - datetime.timedelta(days=30), day, interval='weeks')
def test_time_series_weeks(self):
day = datetime.date(year=2013, month=4, day=5)
u = User.objects.create_user('user', '*****@*****.**')
u.date_joined = day
u.save()
qs = User.objects.all()
qss = QuerySetStats(qs, 'date_joined')
qss.time_series(day - datetime.timedelta(days=30), day, interval='weeks')
def graph(request):
start = datetime.datetime(2013, 11, 21, 00, 00).date()
end = datetime.datetime.now().date()
users = User.objects.all()
qsstats = QuerySetStats(users, date_field='last_login', aggregate=Count('id'))
values = qsstats.time_series(start , end , interval='days' )
staff = User.objects.all().filter(is_staff = '1').count()
other = User.objects.all().count() - staff
type_of_user = [['Суперпользователи',staff],['Пользователи',other]]
start = datetime.datetime(2013, 12, 3, 00, 00).date()
answer = Answer.objects.all()
qsstats2 = QuerySetStats(answer, date_field='date', aggregate=Count('id'))
ans = qsstats2.time_series(start,end, interval="days")
return render(request,'graph.html', { 'ans':ans , 'users': values , 'type' : type_of_user })
def get_registrations(self, interval, days, graph_key, select_box_value):
""" Returns an array with new users count per interval."""
try:
conf_data = DashboardStats.objects.get(graph_key=graph_key)
model_name = get_model(conf_data.model_app_name, conf_data.model_name)
kwargs = {}
for i in conf_data.criteria.all():
# fixed mapping value passed info kwargs
if i.criteria_fix_mapping:
for key in i.criteria_fix_mapping:
# value => i.criteria_fix_mapping[key]
kwargs[key] = i.criteria_fix_mapping[key]
# dynamic mapping value passed info kwargs
if i.dynamic_criteria_field_name and select_box_value:
kwargs[i.dynamic_criteria_field_name] = select_box_value
aggregate = None
if conf_data.type_operation_field_name and conf_data.operation_field_name:
operation={'Sum': Sum(conf_data.operation_field_name),
'Avg':Avg(conf_data.operation_field_name),
'StdDev':StdDev(conf_data.operation_field_name),
'Max':Max(conf_data.operation_field_name),
'Min':Min(conf_data.operation_field_name),
'Variance':Variance(conf_data.operation_field_name),
}
aggregate=operation[conf_data.type_operation_field_name]
stats = QuerySetStats(model_name.objects.filter(**kwargs),
conf_data.date_field_name, aggregate)
#stats = QuerySetStats(User.objects.filter(is_active=True), 'date_joined')
today = now()
if days == 24:
begin = today - timedelta(hours=days - 1)
return stats.time_series(begin, today + timedelta(hours=1), interval)
begin = today - timedelta(days=days - 1)
return stats.time_series(begin, today + timedelta(days=1), interval)
except:
User = get_user_model()
stats = QuerySetStats(
User.objects.filter(is_active=True), 'date_joined')
today = now()
if days == 24:
begin = today - timedelta(hours=days - 1)
return stats.time_series(begin, today + timedelta(hours=1), interval)
begin = today - timedelta(days=days - 1)
return stats.time_series(begin, today + timedelta(days=1), interval)
def process_hourly_activity(user, start, end, modelclass):
if modelclass == AdRecord:
qs = modelclass.objects.filter(_user_id=user.id, date__range=(start, end))
else:
qs = modelclass.objects.filter(user=user, date__range=(start, end))
if not qs:
return {}
qss = QuerySetStats(qs, 'date')
start_date = start.date()
end_date = end.date()
if start_date == end_date:
end_date = start_date + timedelta(days=1)
timeseries = qss.time_series(
start_date, end=end_date, interval='hours', date_field='date')
activities = {}
for t in timeseries:
if t[1]:
d = t[0]
activity = get_hourly_activity(user, start_date, d.hour)
if modelclass == AdRecord:
for r in qs.filter(date__hour=d.hour):
activity.adrecords.add(r)
elif modelclass == FBSponsored:
for r in qs.filter(date__hour=d.hour):
activity.fbsponsored.add(r)
elif modelclass == FBAd:
for r in qs.filter(date__hour=d.hour):
activity.fbads.add(r)
activity.adtotal = activity.adrecords.count()
activity.fbadtotal = activity.fbads.count()
activity.fbsptotal = activity.fbsponsored.count()
activity.save()
activities[d] = activity
return activities
def get_scan_per_day(start_date, end_date):
scan_qsstats = QuerySetStats(
Scan.objects.all(),
date_field='start',
)
scan_values = scan_qsstats.time_series(start_date,
end_date,
interval='days')
return format_date(scan_values)
def get_registrations(self, interval, days, graph_key, select_box_value):
""" Returns an array with new users count per interval."""
try:
conf_data = DashboardStats.objects.get(graph_key=graph_key)
model_name = get_model(conf_data.model_app_name,
conf_data.model_name)
kwargs = {}
for i in conf_data.criteria.all():
# fixed mapping value passed info kwargs
if i.criteria_fix_mapping:
for key in i.criteria_fix_mapping:
# value => i.criteria_fix_mapping[key]
kwargs[key] = i.criteria_fix_mapping[key]
# dynamic mapping value passed info kwargs
if i.dynamic_criteria_field_name and select_box_value:
kwargs[i.dynamic_criteria_field_name] = select_box_value
stats = QuerySetStats(model_name.objects.filter(**kwargs),
conf_data.date_field_name)
#stats = QuerySetStats(User.objects.filter(is_active=True), 'date_joined')
today = now()
if days == 24:
begin = today - timedelta(hours=days - 1)
return stats.time_series(begin, today + timedelta(hours=1),
interval)
begin = today - timedelta(days=days - 1)
return stats.time_series(begin, today + timedelta(days=1),
interval)
except:
User = get_user_model()
stats = QuerySetStats(User.objects.filter(is_active=True),
'date_joined')
today = now()
if days == 24:
begin = today - timedelta(hours=days - 1)
return stats.time_series(begin, today + timedelta(hours=1),
interval)
begin = today - timedelta(days=days - 1)
return stats.time_series(begin, today + timedelta(days=1),
interval)
def assertTimeSeriesWorks(self, today):
seven_days_ago = today - datetime.timedelta(days=7)
for j in range(1,8):
for i in range(0,j):
u = User.objects.create_user('p-%s-%s' % (j, i), '*****@*****.**' % (j, i))
u.date_joined = today - datetime.timedelta(days=i)
u.save()
qs = User.objects.all()
qss = QuerySetStats(qs, 'date_joined')
time_series = qss.time_series(seven_days_ago, today)
self.assertEqual([t[1] for t in time_series], [0, 1, 2, 3, 4, 5, 6, 7])
def test(request):
"""Just test page."""
template_name = 'runstat/test.html'
#
start_date = datetime.strptime('2016-05-01', '%Y-%m-%d').date()
end_date = datetime.strptime('2016-05-31', '%Y-%m-%d').date()
qs = GroupPost.objects.all()
qss = QuerySetStats(qs, date_field='created_time')
values = qss.time_series(start_date, end_date, interval='days')
return render(request, template_name, {'values': values})
def assertTimeSeriesWorks(self, today):
seven_days_ago = today - datetime.timedelta(days=7)
for j in range(1,8):
for i in range(0,j):
u = User.objects.create_user('p-%s-%s' % (j, i), '*****@*****.**' % (j, i))
u.date_joined = today - datetime.timedelta(days=i)
u.save()
qs = User.objects.all()
qss = QuerySetStats(qs, 'date_joined')
time_series = qss.time_series(seven_days_ago, today)
self.assertEqual([t[1] for t in time_series], [0, 1, 2, 3, 4, 5, 6, 7])
def detail(request, user_id):
try:
user = User.objects.get(id=user_id)
statistic = Statistic.objects.filter(user=user_id)
if 'start' in request.GET and request.GET[
'start'] and 'end' in request.GET and request.GET['end']:
start_date = datetime.strptime(request.GET['start'],
'%Y-%m-%d').date()
end_date = datetime.strptime(request.GET['end'], '%Y-%m-%d').date()
else:
end_date = Statistic.get_end_date(user_id=user_id)
start_date = end_date - timedelta(days=6)
qsstats_page_views = QuerySetStats(statistic,
date_field='date',
aggregate=Sum('page_views'))
charts_page_views = qsstats_page_views.time_series(start_date,
end_date,
interval='days')
qsstats_clicks = QuerySetStats(statistic,
date_field='date',
aggregate=Sum('clicks'))
charts_clicks = qsstats_clicks.time_series(start_date,
end_date,
interval='days')
context = {
'user': user,
'statistic': statistic,
'start_date': start_date,
'end_date': end_date,
'charts_page_views': charts_page_views,
'charts_clicks': charts_clicks
}
except:
raise Http404('User is not found.')
return render(request, 'users/detail.html', context=context)
def get_registrations(self, interval, days, graph_key, select_box_value):
""" Returns an array with new users count per interval."""
try:
conf_data = DashboardStats.objects.get(graph_key=graph_key)
model_name = get_model(
conf_data.model_app_name, conf_data.model_name)
kwargs = {}
for i in conf_data.criteria.all():
# fixed mapping value passed info kwargs
if i.criteria_fix_mapping:
for key in i.criteria_fix_mapping:
# value => i.criteria_fix_mapping[key]
kwargs[key] = i.criteria_fix_mapping[key]
# dynamic mapping value passed info kwargs
if i.dynamic_criteria_field_name and select_box_value:
kwargs[i.dynamic_criteria_field_name] = select_box_value
stats = QuerySetStats(model_name.objects.filter(**kwargs),
conf_data.date_field_name)
#stats = QuerySetStats(User.objects.filter(is_active=True), 'date_joined')
today = now()
if days == 24:
begin = today - timedelta(hours=days - 1)
return stats.time_series(begin, today + timedelta(hours=1), interval)
begin = today - timedelta(days=days - 1)
return stats.time_series(begin, today + timedelta(days=1), interval)
except:
stats = QuerySetStats(
User.objects.filter(is_active=True), 'date_joined')
today = now()
if days == 24:
begin = today - timedelta(hours=days - 1)
return stats.time_series(begin, today + timedelta(hours=1), interval)
begin = today - timedelta(days=days - 1)
return stats.time_series(begin, today + timedelta(days=1), interval)
def get_models_data(self, querysets):
""" querysets = {model: (queryset, time_field)} """
for model_type, (queryset, time_field) in querysets.iteritems():
qss = QuerySetStats(queryset, time_field)
data = qss.time_series(self.startdate, self.enddate)
counts = [d[1] for d in data]
qss.last_week = sum(counts[-7:])
qss.last_month = sum(counts[-30:])
self.day_captions = [t[0].day for t in data]
setattr(self, model_type+'_qss', qss)
setattr(self, model_type+'_values', counts)
def time_series(queryset, date_field, interval, func=None, agg=None, annotate_field=None):
if agg in (None, "daily"):
qsstats = QuerySetStats(queryset, date_field, func)
return qsstats.time_series(*interval)
else:
# Custom aggregation was set (weekly/monthly/yearly)
agg_by = agg[:-2]
# We need to set the range dynamically
interval_filter = {date_field + "__gte": interval[0], date_field + "__lte": interval[1]}
# Slightly raw-ish SQL query using extra
truncate_date = connections[queryset.db].ops.date_trunc_sql(agg_by, date_field)
if not annotate_field:
# By default we always annotate by dates, counting all the
# records recovered for the given dates
annotate_field = date_field
result = (
queryset.extra(select={agg_by: truncate_date})
.values_list(agg_by)
.annotate(Count(annotate_field))
.filter(**interval_filter)
.order_by(agg_by)
)
else:
# We do a Sum by annotation field, we are assuming if the
# annotation is not by dates it will be a field which type can be
# added, like prices for example.
result = (
queryset.extra(select={agg_by: truncate_date})
.values_list(agg_by)
.annotate(Sum(annotate_field))
.filter(**interval_filter)
.order_by(agg_by)
)
return fill_missing_dates_with_zeros(result, agg_by, interval)
def changelist_view(self, request, **kwargs):
from qsstats import QuerySetStats
from django.db.models import Count, Min
from django.utils import timezone
today = timezone.now()
start_day = Visits.objects.all().aggregate(Min('datetime')).values()
#print(start_day[0])
#print(today)
qsstats = QuerySetStats(Visits.objects.all(),
date_field='datetime',
aggregate=Count('id'))
values = qsstats.time_series(start_day[0], today, interval='days')
args = {
'values': values,
'opts': Visits._meta,
'app_label': Visits._meta.app_label,
'site_header': 'Welcome page visits statistic',
'site_title': "Statistic"
}
from django.shortcuts import render_to_response
from django.template import RequestContext
return render_to_response('admin/app/visits/change_list.html',
args,
context_instance=RequestContext(request))
def get(self, request, format=None):
"""
return data for graph
"""
try:
obj_id = request.query_params.get('equipment', 0)
end_date = request.query_params.get('end_date', timezone.now())
start_date = request.query_params.get('start_date')
equip = Equipment.objects.filter(id=obj_id).first()
if isinstance(end_date, str):
end_date = parse_datetime(end_date) or parse_date(end_date)
if start_date is None:
start_date = end_date - datetime.timedelta(days=1)
qs = RawData.objects.filter(
mac_address=equip.xbee_mac,
channel=equip.main_channel).order_by('date')
qss = QuerySetStats(qs=qs,
date_field='date',
aggregate=Avg('value'))
time_series = qss.time_series(start_date,
end_date,
interval='minutes')
data = {
'equipment': str(equip),
'end_date': end_date,
'ts': time_series
}
except Exception as e:
raise Http404('Error in parameters')
return Response(data)
def response_timeseries(self):
"""
Returns a list of timeseries from the responses.
"""
qss = QuerySetStats(self.all(), "created")
return qss.time_series(timezone.now() - timedelta(days=7), timezone.now())
def time_series(queryset, date_field, interval, func=None):
qsstats = QuerySetStats(queryset, date_field, func)
return qsstats.time_series(*interval)
def get_data(self, interval, days):
""" Returns an array with new users count per interval """
stats = QuerySetStats(self.queryset, self.date_field, aggregate=self.aggregate)
today = now()
begin = today - timedelta(days=days - 1)
return stats.time_series(begin, today + timedelta(days=1), interval)
def valuebase(request):
# формирование данных (таблица + фильтр)
form = ValuebaseFilterForm(request.POST)
values = Valuebase.objects.all()
if form.is_valid():
if form.cleaned_data['min_value']:
values = values.filter(value__gte=form.cleaned_data['min_value'])
if form.cleaned_data['max_value']:
values = values.filter(value__lte=form.cleaned_data['max_value'])
if form.cleaned_data['min_date']:
values = values.filter(date__gte=form.cleaned_data['min_date'])
if form.cleaned_data['max_date']:
values = values.filter(date__lte=form.cleaned_data['max_date'])
if form.cleaned_data['bt']:
values = values.filter(id_bt__name=form.cleaned_data['bt'])
if form.cleaned_data['office']:
values = values.filter(
id_office__office=form.cleaned_data['office'])
if form.cleaned_data['city']:
values = values.filter(id_city__city=form.cleaned_data['city'])
table = ValuebaseTable(values)
RequestConfig(request).configure(table)
# формирование таблицы показателей
res_form = ResForm(request.POST)
res = Valuebase.objects.all()
min_date = date(2019, 1, 1)
max_date = date.today()
if res_form.is_valid():
if res_form.cleaned_data['bt_res']:
res = res.filter(id_bt__name=res_form.cleaned_data['bt_res'])
if res_form.cleaned_data['city_res']:
res = res.filter(id_city__city=res_form.cleaned_data['city_res'])
if res_form.cleaned_data['office_res']:
res = res.filter(
id_office__office=res_form.cleaned_data['office_res'])
if res_form.cleaned_data['min_date_res']:
res = res.filter(date__gte=res_form.cleaned_data['min_date_res'])
# проверка на существование записей с минимальной датой
if res_form.cleaned_data['min_date_res'] < values.aggregate(
Min('date')).get('date__min'):
min_date = values.aggregate(Min('date')).get('date__min')
else:
min_date = res_form.cleaned_data['min_date_res']
if res_form.cleaned_data['max_date_res']:
res = res.filter(date__lte=res_form.cleaned_data['max_date_res'])
# проверка на существование записей с максимальной датой
if res_form.cleaned_data['max_date_res'] > values.aggregate(
Max('date')).get('date__max'):
max_date = values.aggregate(Max('date')).get('date__max')
else:
max_date = res_form.cleaned_data['max_date_res']
# вычисляем отношение показателя "Активные клиенты"
act_clients = get_stat(res, 1, max_date, min_date)
# вычисляем отношение показателя "Кредитные карты"
credit_cards = get_stat(res, 2, max_date, min_date)
# вычисляем отношение показателя "Costs"
costs = get_stat(res, 3, max_date, min_date)
# вычисляем отношение показателя "NBI"
nbi = get_stat(res, 4, max_date, min_date)
else:
# задаем начальные значения для показателей, если форма не заполнена
act_clients = get_stat_def(res, 1)
credit_cards = get_stat_def(res, 2)
costs = get_stat_def(res, 3)
nbi = get_stat_def(res, 4)
# формирование диаграммы
start_date = min_date
end_date = max_date
queryset = Valuebase.objects.values_list('date', 'value')
qss = QuerySetStats(
queryset, date_field='date',
aggregate=Avg('value')) # вычисление среднего значения показателей
graph = qss.time_series(start_date, end_date,
interval='months') # за указанный период
return render(
request, 'valuebase.html', {
'valuebase': table,
'form': form,
'res_form': res_form,
'act_clients': round(act_clients * 100),
'credit_cards': round(credit_cards * 100),
'costs': round(costs * 100),
'nbi': round(nbi * 100),
'graph': graph,
'queryset': qss.__dict__
})
def get_registrations(self, interval, days):
""" Returns an array with new users count per interval """
stats = QuerySetStats(User.objects.filter(is_active=True), 'date_joined')
today = datetime.today()
begin = today - timedelta(days=days-1)
return stats.time_series(begin, today+timedelta(days=1), interval)
def data_for_period(self, qs, start, end, interval='days'):
"""Return a time series of data for the given period."""
qss = QuerySetStats(qs, self.datetime_field, aggregate=self.aggregate)
return qss.time_series(start, end, interval=interval)
def get_data(self, interval, days):
""" Returns an array with new users count per interval """
stats = QuerySetStats(self.queryset, self.date_field)
today = datetime.date.today()
begin = today - datetime.timedelta(days=days - 1)
return stats.time_series(begin, today, interval)
def get_scan_per_day(start_date, end_date):
scan_qsstats = QuerySetStats(Scan.objects.all(),
date_field='start',)
scan_values = scan_qsstats.time_series(start_date, end_date, interval='days')
return format_date(scan_values)
def get_data(self, interval, days):
""" Returns an array with new users count per interval """
stats = QuerySetStats(self.queryset, self.date_field)
today = datetime.date.today()
begin = today - datetime.timedelta(days=days-1)
return stats.time_series(begin, today, interval)
def time_series(queryset, date_field, interval, func=None):
qsstats = QuerySetStats(queryset, date_field, func)
return qsstats.time_series(*interval)
def data_for_period(self, qs, start, end, interval='days'):
"""Return a time series of data for the given period."""
qss = QuerySetStats(qs, self.datetime_field,
aggregate=self.aggregate)
return qss.time_series(start, end, interval=interval)
