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')