Django 统计报表查询并分页

需求:根据时间,人员,仓库信息进行统计汇总

原型效果图:

一.定义视图

class ReturnDemoViewSet(viewsets.ModelViewSet):
    """统计汇总视图"""

    @action(methods=['get'], detail=False)
    def pick_return_count(self, request, *args, **kwargs):
        """统计汇总订单统计报表"""
        request_params = request.query_params
        limit = request_params.get('limit', '2')
        page = request_params.get('page', '1')
        if not (isinstance(eval(limit), int) and isinstance(eval(page), int)):
            return returnResponse({'code': 400, 'msg': '参数错误！'})
        from .models.pickingDemo import PickingDemoManageBase
        result_data, dict_total = PickingDemoManageBase().pick_return_count(request_params)
        start_page, total_page, _start, _end = handler_page(page=eval(page), count=len(result_data),
                                                            one_page_max_number=eval(limit))
        page_data = result_data[_start: _end]
        result = {
            'page': start_page,
            'total_page': total_page,
            'count': len(result_data),
            'data': page_data,
            'dict_total': dict_total
        }
        return returnResponse(result)

二.定义分页功能

def handler_page(page, count, one_page_max_number):
    """
    取得页数的方法
    :param page: 页数
    :param count: 总数
    :param one_page_max_number: 每页数量
    :return: page: 当前页
            total_page_number: 总页数
            _start: 开始位置
            _end: 结尾位置
    """
    total_page_number = None
    try:
        # 计算最大页数
        if count % one_page_max_number == 0:
            total_page_number = count / one_page_max_number
        else:
            total_page_number = int(count / one_page_max_number) + 1

        if total_page_number == 0:
            total_page_number = 1
        page = int(page)
        if page < 1:
            # 当页数<1的时候，设为1
            page = 1
        elif page > total_page_number:
            # 当页数>总页数的时候，设为总页数
            page = total_page_number
    except (ValueError, TypeError):
        # 当发生转换错误时，设为第1页
        page = 1
        if total_page_number is None:
            total_page_number = page
    _start = (page - 1) * one_page_max_number
    _end = page * one_page_max_number

    return page, total_page_number, _start, _end

三.具体逻辑获取数据

import datetime

from django.db.models import Count
from django.utils import timezone
from .models import PickingDemo,StockDemo
from .utls import get_name_id

def pick_return_count(self, request_params):
    """订单统计报表"""
    location_id = request_params.get('location', None)
    operator_id = request_params.get('operator_id', None)
    start_date = request_params.get('start_date', None)
    end_date = request_params.get('end_date', None)
    pick_queryset = None
    import time
    if start_date and end_date:
        # 将请求的时间转换成数据库的时区时间
        start_date = timezone.make_aware(
            datetime.datetime.strptime(start_date, '%Y-%m-%d %H:%M:%S'))
        end_date = timezone.make_aware(
            datetime.datetime.strptime(end_date, '%Y-%m-%d %H:%M:%S'))

        pick_queryset = PickingDemo.objects.filter(type='in', create_date__gte=start_date,
                                                    create_date__lte=end_date, sale_id__isnull=False)
    if location_id:
        if pick_queryset and location_id:
            pick_queryset = pick_queryset.filter(location_id=location_id)
        elif location_id:
            pick_queryset = PickingDemo.objects.filter(type='in', location_id=location_id, sale_id__isnull=False)
        else:
            pick_queryset = PickingDemo.objects.filter(type='in', sale_id__isnull=False)
    if operator_id:
        user_id = get_name_id('name', operator_id.strip())
        if user_id and pick_queryset:
            pick_queryset = pick_queryset.filter(create_uid=user_id)
        elif user_id:
            pick_queryset = PickingDemo.objects.filter(type='in', create_uid=user_id, sale_id__isnull=False)
        else:
            pick_queryset = PickingDemo.objects.filter(type='in', sale_id__isnull=False)
    #  获取人员信息
    user_dict = {}
    user_list = set(pick_queryset.values('location_id', 'create_uid').values_list('location_id', 'create_uid'))
    user_id_list = []
    for user_info in user_list:
        user_id_list.append(user_info[1])
        user_dict['%s_%s' % (user_info[0], user_info[1])] = {'user_id': user_info[1], 'order_qty': 0,
                                                             'sku_qty_total': 0, 'user_name': '', 'sku_qty': 0,

                                                             }
    #  获取订单数量
    order_qty_dict = pick_queryset.values('location_id', 'create_uid').annotate(
        total=Count('id')).values('location_id', 'create_uid',
                                  'total')
    #  获取SKU种类
    stock_move_queryset = StockDemo.objects.filter(picking_id__in=pick_queryset.values_list('id'))

    sku_qty_list = stock_move_queryset.values('location_dest_id', 'create_uid').annotate(
        total=Count('product_id', distinct=True)).order_by('-total').values('location_dest_id', 'create_uid',
                                                                            'total')

    #  获取SKU总数
    sku_qty_total_list = stock_move_queryset.values('location_dest_id', 'create_uid').annotate(
        total=Count('product_qty')).order_by('-total').values('location_dest_id', 'create_uid',
                                                              'total')

    # 合并数据
    dict_total = {'total_order_qty': 0, 'total_sku_qty': 0, 'total_sku_qty_total': 0}
    for order_qty in order_qty_dict:
        location_user = '%s_%s' % (order_qty['location_id'], order_qty['create_uid'])
        if location_user in user_dict:
            user_dict[location_user]['order_qty'] += order_qty['total']
        dict_total['total_order_qty'] += order_qty['total']
    for sku_qty in sku_qty_list:
        location_user = '%s_%s' % (sku_qty['location_dest_id'], sku_qty['create_uid'])
        if location_user in user_dict:
            user_dict[location_user]['sku_qty'] += sku_qty['total']
        dict_total['total_sku_qty'] += sku_qty['total']

    for sku_qty_total in sku_qty_total_list:
        location_user = '%s_%s' % (sku_qty_total['location_dest_id'], sku_qty_total['create_uid'])
        if location_user in user_dict:
            user_dict[location_user]['sku_qty_total'] += sku_qty_total['total']
        dict_total['total_sku_qty_total'] += sku_qty_total['total']

    result_list = list(user_dict.values())

    return result_list, dict_total

四.响应结果:

get  http://0.0.0.0:8000/api_count/?imit=2&page=2&start_date=2020-04-01 00:00:00&end_date=2020-04-28 00:00:00
{
    "total_page": 2.0,
    "data": [
        {
            "order_qty": 2,
            "user_name": "管理员",
            "location": "义乌分仓",
            "sku_qty_total": 2,
            "user_id": 1,
            "sku_qty": 2
        },
        {
            "order_qty": 3,
            "user_name": "梅亚男",
            "location": "义乌分仓",
            "sku_qty_total": 3,
            "user_id": 2476,
            "sku_qty": 3
        }
    ],
    "page": 2,
    "count": 4,
    "dict_total": {
        "total_order_qty": 21,
        "total_sku_qty": 21,
        "total_sku_qty_total": 21
    }
}

tips:根据不同的page返回不同的数据.