Django项目中单个用户月度工作薪酬总和统计需求

核心查询逻辑 #

通过Django ORM的filter+aggregate组合，精准筛选单个用户指定年月的任务，并计算Pay_Amont字段的总和。

1. 函数视图实现 #

from django.shortcuts import render
from django.db.models import Sum
from django.contrib.auth.decorators import login_required
from .models import work
from datetime import datetime

@login_required  # 仅登录用户可访问
def user_monthly_pay(request):
    current_user = request.user
    # 支持手动选择年月，默认显示当前年月数据
    year = request.GET.get('year', datetime.now().year)
    month = request.GET.get('month', datetime.now().month)
    
    # 统计当月总薪酬，无数据时返回0
    total_pay = work.objects.filter(
        Worker=current_user,
        Date_Created__year=year,
        Date_Created__month=month
    ).aggregate(total=Sum('Pay_Amont'))['total'] or 0
    
    return render(request, 'user_monthly_pay.html', {
        'total_pay': total_pay,
        'selected_year': year,
        'selected_month': month
    })

2. 类视图实现 #

from django.views.generic import TemplateView
from django.db.models import Sum
from django.contrib.auth.mixins import LoginRequiredMixin
from .models import work
from datetime import datetime

class UserMonthlyPayView(LoginRequiredMixin, TemplateView):
    template_name = 'user_monthly_pay.html'
    
    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        current_user = self.request.user
        year = self.request.GET.get('year', datetime.now().year)
        month = self.request.GET.get('month', datetime.now().month)
        
        total_pay = work.objects.filter(
            Worker=current_user,
            Date_Created__year=year,
            Date_Created__month=month
        ).aggregate(total=Sum('Pay_Amont'))['total'] or 0
        
        context.update({
            'total_pay': total_pay,
            'selected_year': year,
            'selected_month': month
        })
        return context

3. 模板展示（user_monthly_pay.html） #

<div class="pay-stat">
    <h3>我的月度薪酬</h3>
    <form class="date-selector" method="get">
        <div>
            <label>年份:</label>
            <input type="number" name="year" value="{{ selected_year }}" min="2020" max="{{ now.year }}">
        </div>
        <div>
            <label>月份:</label>
            <input type="number" name="month" value="{{ selected_month }}" min="1" max="12">
        </div>
        <button type="submit">查询</button>
    </form>
    <p class="total-pay">
        {{ selected_year }}年{{ selected_month }}月总薪酬: <strong>¥{{ total_pay }}</strong>
    </p>
</div>

4. 重要注意事项 #

• 字段拼写修正：模型中Pay_Amont存在拼写错误，建议改为Pay_Amount，修改后执行以下命令更新数据库：

  python manage.py makemigrations
  python manage.py migrate
  

• 统计维度调整：当前使用Date_Created（任务创建时间）统计，若需按任务完成时间统计，需在work模型中添加字段：

  Date_Completed = models.DateField(null=True, blank=True, verbose_name="完成时间")
  

  然后将视图中的过滤条件替换为Date_Completed__year=year, Date_Completed__month=month。
• 路由配置：记得在urls.py中添加对应路由，比如：

  from django.urls import path
  from .views import user_monthly_pay, UserMonthlyPayView
  
  urlpatterns = [
      # 函数视图路由
      path('my-pay/', user_monthly_pay, name='user_monthly_pay'),
      # 类视图路由
      path('my-pay-class/', UserMonthlyPayView.as_view(), name='user_monthly_pay_class'),
  ]
  

内容的提问来源于stack exchange，提问作者alphies pro