def export_to_xls_user_poll(self, request, queryset):
uf = queryset[0]
users = uf.get_filtered_user_queryset()
if not uf.polls:
uf.polls = Poll.objects.all()
polls = UsersPoll.objects.filter(user__pk__in=users.values_list('pk'),
poll__pk__in=uf.polls.values_list('pk'), )
if uf.date_from:
polls = polls.filter(date_passed__lte=uf.date_from)
if uf.date_to:
polls = polls.filter(date_passed__gte=uf.date_to)
polls = Poll.objects.filter(pk__in=polls.values_list('poll'))
answers = UserAnswer.objects.filter(user__pk__in=users.values_list('pk'),
question__poll__pk__in=polls.values_list('pk'))
answers = answers.order_by(uf.order, set(ORDERS - set(uf.order)).pop(), 'question__id', 'answer__id')
df = read_frame(answers,
verbose=True,
fieldnames=(
'user__user__id', 'user__user__username',
'question__poll__id', 'question__poll__name',
'question__id', 'question__question_text',
'answer__id', 'answer__answer_text',
'other_answer',
))
df = translate_column_names(df)
return export_to_xls(df, translit(uf.group.name) + '.xls', engine='openpyxl')
def poll_to_xls(request, queryset):
qs = None
for poll in queryset:
if qs:
qs |= Answer.objects.filter(question__poll=poll.id)
else:
qs = Answer.objects.filter(question__poll=poll.id)
qs = qs.annotate(useranswers=Count('useranswer'))
qs = qs.order_by('-question__poll__id', 'question__id', 'id')
df = read_frame(qs,
verbose=True,
fieldnames=('question__poll__id',
'question__poll__name',
'question__id',
'question__question_text',
'id',
'answer_text',
'useranswers'
))
for i, row in df.iterrows():
question = df.loc[i, 'question__id']
answers = df.loc[i, 'useranswers']
total_counts = sum(df[df['question__id'] == question]['useranswers'])
if total_counts:
percent = answers / total_counts * 100
else:
percent = 0
df.ix[i, 'percent'] = str(percent) + '%'
i = dt.datetime.now()
path = 'oprosy' + "_%s_%s_%s" % (i.day, i.month, i.year) + '.xls'
df = translate_column_names(df)
return export_to_xls(df, path)
def export_to_xls_users(self, request, queryset):
df = pd.DataFrame()
for el in queryset:
users = el.get_filtered_user_queryset()
users = users.annotate(userlogins=Count('usersession'))
users = users.annotate(usertime=Sum('usersession__duration'))
df = pd.concat([df, read_frame(users,
index_col='user__id',
verbose=True,
fieldnames=(
'user__id', 'user__username', 'user__first_name', 'user__last_name',
'patronimyc',
'user__email', 'user__last_login', 'userlogins', 'usertime',
'date_of_birth', 'age', 'gender', 'city__name', 'area__name',
'score', 'total_score',
'total_exchange',
'speciality__name',
'work__name', 'curing_form', 'position__name', 'category',
'bed_quantity',
'patient_quantity',
'main_phone', 'secondary_phone', 'work_phone',
'district', 'house', 'flat', 'index',))])
df = translate_column_names(df)
return export_to_xls(df, translit(el.name) + '.xls', engine='openpyxl')
def export_to_xls_search_history(self, request, queryset):
df = read_frame(queryset,
verbose=True,
fieldnames=(
'user__user__id', 'user__user__username',
'date',
'name',
))
df = translate_column_names(df)
return export_to_xls(df,
'search_history' +
request.GET.get('drf__day__gte ', '') +
request.GET.get('drf__day__lte', '') + '.xls',
engine='openpyxl')
def export_to_xls_exchange(self, request, queryset, ):
df = read_frame(queryset,
index_col='date',
verbose=True,
fieldnames=(
'date',
'user__user__id', 'user__user__username',
'user__user__email',
'exchange',
'user__score', 'user__total_score',
'user__total_exchange',
))
df = translate_column_names(df)
return export_to_xls(df,
'exchange' + request.GET.get('drf__day__gte ', '') + '_' +
request.GET.get('drf__day__lte', '') + '.xls',
engine='openpyxl')
