import logging import pypika import pypika.functions from datetime import timedelta from django.utils.translation import gettext as _ from django.utils import timezone as django_timezone from django.db import connections from rest_framework.response import Response from rest_framework.permissions import IsAdminUser from rest_framework.exceptions import NotFound from pingcap_sso.api.base import AdminEndpoint from pingcap_sso.api.permissions import MethodPermission from pingcap_sso.blog.models import Post from pingcap_sso.models import ( Evangelist, UserProfileCollections, User ) from pingcap_sso.utils import time as ptime from pingcap_sso.forms.admin_evangelist import EvangelistDetailForm logger = logging.getLogger(__name__) class SummaryEndpoint(AdminEndpoint): permission_classes = (IsAdminUser, MethodPermission) perms_map = { 'GET': ['pingcap_sso.view_evangelist'] } def get(self, request, *args, **kwargs): """audit_log: 查看了布道师数据概览""" posts = Post.objects.filter( status=Post.STATUS_PUBLISHED, deleted_at__isnull=True ) all_evangelist_count = Evangelist.objects.count() all_blog_count = posts.count() this_month_first_day = django_timezone.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0) last_month_first_day = (this_month_first_day - timedelta(days=1)).replace(day=1) this_month_blog_count = posts.filter( created_at__gte=this_month_first_day ).count() last_month_blog_count = posts.filter( created_at__gte=last_month_first_day, created_at__lt=this_month_first_day ).count() return Response({ 'detail': _('success'), 'data': { 'all_evangelist_count': all_evangelist_count, 'all_blog_count': all_blog_count, 'this_month_blog_count': this_month_blog_count, 'last_month_blog_count': last_month_blog_count, } }) class ListEndpoint(AdminEndpoint): permission_classes = (IsAdminUser, MethodPermission) perms_map = { 'GET': ['pingcap_sso.view_evangelist'] } sorted_fields = ['blog_count', 'join_at'] def get(self, request, *args, **kwargs): """audit_log: 查看了布道师数据列表""" try: cur_page = int(request.GET.get('currentPage', 1)) per_page = int(request.GET.get('perPage', 10)) year = int(request.GET.get('year', '-1')) sortBy = request.GET.get('sortBy', 'experience') sortDesc = request.GET.get('sortDesc', 'true') if sortBy not in self.sorted_fields: sortBy = 'join_at' sortDesc = False if sortDesc == 'false' else True except Exception: return Response({ 'detail': _('wrong params') }, 400) blog_posts, blog_users = pypika.Tables( 'community_blog.posts', 'community_blog.users') pingcap_users, pingcap_evangelists = pypika.Tables( 'pingcap_account.auth_user', 'pingcap_account.pingcap_evangelists') sql_parameters = [] post_subquery = pypika.Query.from_(blog_posts).select( blog_users.openid, pypika.functions.Count('*').as_('blog_count') ).left_join( blog_users ).on( blog_posts.creator_id == blog_users.id ).where( (blog_posts.status == 'PUBLISHED') & (blog_posts.deleted_at.isnull()) ).groupby( blog_users.openid ) if year != -1: YEAR_FORMAT = pypika.CustomFunction('YEAR', ['datetime']) post_subquery = post_subquery.where(YEAR_FORMAT(blog_posts.published_at) == pypika.Parameter('%s')) sql_parameters.append(year) alias_t1 = pypika.AliasedQuery('t1') with connections['blog'].cursor() as cursor: cursor.execute( pypika.Query.with_( post_subquery, 't1' ).from_(alias_t1).select( pypika.functions.Count(alias_t1.openid).distinct() ).get_sql(quote_char=None), sql_parameters ) total_num = cursor.fetchone()[0] query = pypika.Query.with_(post_subquery, 't1').from_( pingcap_evangelists ).select( pingcap_evangelists.star, alias_t1.blog_count ).left_join( pingcap_users ).on( pingcap_evangelists.user_id == pingcap_users.id ).left_join( alias_t1 ).on( pingcap_users.openid == alias_t1.openid ).where(alias_t1.blog_count > 0)[(cur_page - 1) * per_page:cur_page * per_page] order = pypika.Order.desc if sortDesc else pypika.Order.asc if sortBy == 'join_at': query = query.orderby( pingcap_evangelists.join_at, order=order ) elif sortBy == 'blog_count': query = query.orderby(alias_t1.blog_count, order=order) qs = Evangelist.objects.raw( query.get_sql(quote_char=None), sql_parameters ) user_ids = [evangelist.user_id for evangelist in qs] profile_users = UserProfileCollections.objects.filter( is_primary=True, user_id__in=user_ids ).values('user_id', 'user__username', 'name', 'company', 'phone') user_profile_map = {p_u['user_id']: p_u for p_u in profile_users} data = list() for evangelist in qs: user_id = evangelist.user_id user_profile = user_profile_map.get(user_id, {}) data.append({ 'id': evangelist.id, 'username': user_profile.get('user__username', ''), 'name': user_profile.get('name', ''), 'company': user_profile.get('company', ''), 'phone': user_profile.get('phone', ''), 'blog_count': evangelist.blog_count, 'asktug_honor_badges': evangelist.asktug_honor_badges, 'join_at': ptime.local_time_format(evangelist.join_at, '%Y-%m-%d'), 'comment': evangelist.comment, }) return Response({ 'detail': _('success'), 'data': data, 'total_num': total_num, 'per_page': per_page, 'cur_page': cur_page, 'order_by': sortBy }) class BlogListEndpoint(AdminEndpoint): permission_classes = (IsAdminUser, MethodPermission) perms_map = { 'GET': ['pingcap_sso.view_evangelist'] } sorted_fields = ['blog_count', 'join_at'] def get(self, request, username, *args, **kwargs): """audit_log: 查看了布道师的文章列表""" try: user = User.objects.get(username=username) year = int(request.GET.get('year', '-1')) except User.DoesNotExist: raise NotFound except Exception: return Response({ 'detail': _('wrong params') }, 400) posts = Post.objects.filter( creator__openid=user.openid, status=Post.STATUS_PUBLISHED, ).order_by('-published_at') if year != -1: posts = posts.filter(published_at__year=year) data = posts.values('slug', 'title', 'published_at') return Response({ 'detail': _('success'), 'data': data }) class DetailEndpoint(AdminEndpoint): permission_classes = (IsAdminUser, MethodPermission) perms_map = { 'PUT': ['pingcap_sso.view_evangelist'] } def put(self, request, username, *args, **kwargs): """audit_log: 查看了布道师的文章列表""" try: user = User.objects.get(username=username) evangelist = Evangelist.objects.get(user=user) except (User.DoesNotExist, Evangelist.DoesNotExist): raise NotFound form = EvangelistDetailForm(data=request.data, evangelist=evangelist) if not form.is_valid(): return Response({ 'detail': _('params error'), 'errors': form.errors }, status=400) form.save() return Response({ 'detail': _('success') })