XH_Digital_Management/application/exec_tbl/models.py

from datetime import datetime, timedelta
from decimal import Decimal

from django.db import models
from django.db.models import Sum, ExpressionWrapper, F, fields, Q
from django.utils import timezone

from application.busi_tbl.models import DepartmentAnnualPerformance, UpdateLog, DepartmentMonthlyIncome
from application.fac_mgnt.models import InvoiceRecord, EmployeeCommissionDetail, BonusAllocation
from application.hrm_mgnt.models import EmployeeInformation, EmployeeAttendanceRecord, OtherLeaveDetails
from application.perf_mgnt.models import GroupBusinessTarget, EmployeePerformanceTarget
from application.pjt_mgnt.models import EmployeeProjectIncomeSettlement


class FinancialData(models.Model):
    year = models.IntegerField(verbose_name="年份", help_text="财务数据对应的年份")
    annual_revenue = models.FloatField(verbose_name="年度营业收入", help_text="当年总收入", null=True, blank=True)
    annual_new_revenue = models.FloatField(verbose_name="年度新增收入", help_text="当年新增的收入", null=True, blank=True)
    annual_existing_revenue = models.FloatField(verbose_name="年度存量收入", help_text="当年开始就已存在的持续收入", null=True, blank=True)
    annual_operating_costs = models.FloatField(verbose_name="年度营业成本", help_text="当年的总营业成本", null=True, blank=True)
    annual_expenses = models.FloatField(verbose_name="年度费用开销", help_text="当年的总费用", null=True, blank=True)
    operating_profit = models.FloatField(verbose_name="营业利润", help_text="当年的营业利润（营业收入 - 营业成本 - 费用开销）", null=True, blank=True)
    unfinished_annual_revenue = models.FloatField(verbose_name="年未完成收入", help_text="当年尚未完成的收入目标", null=True, blank=True)
    daily_income_target_remaining = models.FloatField(verbose_name="剩余日均收入目标", help_text="当年剩余天数内每天需要完成的收入目标", null=True, blank=True)

    class Meta:
        verbose_name = "公司整体经营情况"
        verbose_name_plural = "公司整体经营情况"

    def __str__(self):
        return f"{self.year} 年的经营情况"

    def to_dict(self):
        return {
            'year': self.year,
            'annual_revenue': self.annual_revenue,
            'annual_new_revenue': self.annual_new_revenue,
            'annual_existing_revenue': self.annual_existing_revenue,
            'annual_operating_costs': self.annual_operating_costs,
            'annual_expenses': self.annual_expenses,
            'operating_profit': self.operating_profit,
            'unfinished_annual_revenue': self.unfinished_annual_revenue,
            'daily_income_target_remaining': self.daily_income_target_remaining
        }

    @staticmethod
    def get_field_labels():
        return {
            'year': '年份',
            'annual_revenue': '年度营业收入',
            'annual_new_revenue': '年度新增收入',
            'annual_existing_revenue': '年度存量收入',
            'annual_operating_costs': '年度营业成本',
            'annual_expenses': '年度费用开销',
            'operating_profit': '营业利润',
            'unfinished_annual_revenue': '年未完成收入',
            'daily_income_target_remaining': '剩余日均收入目标'
        }

    def get_field_label(self, field_name):
        field_labels = self.get_field_labels()
        return field_labels.get(field_name, field_name)

    @staticmethod
    def calculate_and_update_for_year(year):
        # 聚合 DepartmentAnnualPerformance 的数据
        aggregated_data = DepartmentAnnualPerformance.objects.filter(year=year).aggregate(
            total_annual_revenue=Sum('annual_revenue'),
            total_annual_new_revenue=Sum('annual_new_revenue'),
            total_annual_existing_revenue=Sum('annual_existing_revenue'),
            total_annual_operating_costs=Sum('annual_operating_costs'),
            total_annual_expenses=Sum('annual_expenses'),
            total_operating_profit=Sum('operating_profit')
        )

        # 从 EmployeePerformanceTarget 获取收入总目标
        total_revenue_target = GroupBusinessTarget.objects.filter(year=year).aggregate(
            total=Sum('total_revenue_target')
        )['total'] or Decimal('1')  # 避免除以零

        total_revenue_target = float(total_revenue_target)

        # 计算年未完成收入
        unfinished_annual_revenue = total_revenue_target - (aggregated_data['total_annual_revenue'] or 0)

        # 计算当年剩余天数
        today = datetime.today()
        remaining_days = (datetime(year, 12, 31) - today).days

        # 计算剩余日均收入目标
        daily_income_target_remaining = unfinished_annual_revenue / remaining_days if remaining_days > 0 else 0

        # 更新或创建 FinancialData 实例
        FinancialData.objects.update_or_create(
            year=year,
            defaults={
                'annual_revenue': round(float(aggregated_data['total_annual_revenue'] or 0), 2),
                'annual_new_revenue': round(float(aggregated_data['total_annual_new_revenue'] or 0), 2),
                'annual_existing_revenue': round(float(aggregated_data['total_annual_existing_revenue'] or 0), 2),
                'annual_operating_costs': round(float(aggregated_data['total_annual_operating_costs'] or 0), 2),
                'annual_expenses': round(float(aggregated_data['total_annual_expenses'] or 0), 2),
                'operating_profit': round(float(aggregated_data['total_operating_profit'] or 0), 2),
                'unfinished_annual_revenue': round(float(unfinished_annual_revenue), 2),
                'daily_income_target_remaining': round(float(daily_income_target_remaining), 2)
            }
        )

        UpdateLog.objects.update_or_create(
            table_name='FinancialData',
            defaults={'last_update': timezone.now().date()}
        )

    @classmethod
    def update_financial_data_for_year(cls, year):
        cls.calculate_and_update_for_year(year)


class MonthlyFinancialData(models.Model):
    year_month = models.CharField(max_length=7, verbose_name="年月", help_text="格式为'YYYY-MM'，表示统计数据的年月")
    current_month_income = models.FloatField(verbose_name="当月收入", help_text="该月的总收入", null=True, blank=True)
    monthly_income_target = models.FloatField(verbose_name="月收入目标", help_text="该月的收入目标", null=True, blank=True)
    monthly_target_completion_rate = models.FloatField(verbose_name="月目标完成率", help_text="该月收入完成目标的百分比", null=True, blank=True)
    current_month_new_income = models.FloatField(verbose_name="当月新增收入", help_text="当月新增的收入", null=True, blank=True)
    monthly_new_income_target = models.FloatField(verbose_name="月新增收入目标", help_text="当月新增收入的目标", null=True, blank=True)
    monthly_new_income_completion_rate = models.FloatField(verbose_name="月新增收入完成率", help_text="当月新增收入完成目标的百分比", null=True, blank=True)
    current_month_existing_income = models.FloatField(verbose_name="当月存量收入", help_text="当月存量收入", null=True, blank=True)
    monthly_existing_income_target = models.FloatField(verbose_name="月存量收入目标", help_text="当月存量收入的目标", null=True, blank=True)
    monthly_existing_income_completion_rate = models.FloatField(verbose_name="月存量收入完成率", help_text="当月存量收入完成目标的百分比", null=True, blank=True)
    annual_accumulated_income = models.FloatField(verbose_name="年累计收入", help_text="年度累计收入", null=True, blank=True)
    accumulated_target_completion_rate = models.FloatField(verbose_name="累计目标完成率", help_text="年度收入目标完成率的百分比", null=True, blank=True)

    class Meta:
        verbose_name = "公司整体收入情况"
        verbose_name_plural = "公司整体收入情况"
        db_table_comment = '存储公司整体收入情况，包括收入、目标和完成率等'

    def __str__(self):
        return f"{self.year_month} 的收入情况"

    @staticmethod
    def get_field_labels():
        return {
            'year_month': '年月',
            'current_month_income': '当月收入',
            'monthly_income_target': '月收入目标',
            'monthly_target_completion_rate': '月目标完成率',
            'current_month_new_income': '当月新增收入',
            'monthly_new_income_target': '月新增收入目标',
            'monthly_new_income_completion_rate': '月新增收入完成率',
            'current_month_existing_income': '当月存量收入',
            'monthly_existing_income_target': '月存量收入目标',
            'monthly_existing_income_completion_rate': '月存量收入完成率',
            'annual_accumulated_income': '年累计收入',
            'accumulated_target_completion_rate': '累计目标完成率',
        }

    def get_field_label(self, field_name):
        field_labels = self.get_field_labels()
        return field_labels.get(field_name, field_name)

    @staticmethod
    def calculate_and_update_for_month(year_month):
        year, month = map(int, year_month.split('-'))

        # 聚合 DepartmentMonthlyIncome 的数据
        aggregated_data = DepartmentMonthlyIncome.objects.filter(year_month=year_month).aggregate(
            total_current_month_income=Sum('current_month_income'),
            total_monthly_income_target=Sum('monthly_income_target'),
            total_current_month_new_income=Sum('current_month_new_income'),
            total_monthly_new_income_target=Sum('monthly_new_income_target'),
            total_current_month_existing_income=Sum('current_month_existing_income'),
            total_monthly_existing_income_target=Sum('monthly_existing_income_target'),
            total_annual_accumulated_income=Sum('annual_accumulated_income')
        )

        # 计算完成率
        monthly_target_completion_rate = (aggregated_data['total_current_month_income'] / aggregated_data['total_monthly_income_target'] * 100) if aggregated_data['total_monthly_income_target'] else 0
        monthly_new_income_completion_rate = (aggregated_data['total_current_month_new_income'] / aggregated_data['total_monthly_new_income_target'] * 100) if aggregated_data['total_monthly_new_income_target'] else 0
        monthly_existing_income_completion_rate = (aggregated_data['total_current_month_existing_income'] / aggregated_data['total_monthly_existing_income_target'] * 100) if aggregated_data['total_monthly_existing_income_target'] else 0
        accumulated_target_completion_rate = (aggregated_data['total_annual_accumulated_income'] / aggregated_data['total_monthly_income_target'] * 100) if aggregated_data['total_monthly_income_target'] else 0

        # 更新或创建 MonthlyFinancialData 实例
        MonthlyFinancialData.objects.update_or_create(
            year_month=year_month,
            defaults={
                'current_month_income': round(float(aggregated_data['total_current_month_income'] or 0), 2),
                'monthly_income_target': round(float(aggregated_data['total_monthly_income_target'] or 0), 2),
                'monthly_target_completion_rate': round(float(monthly_target_completion_rate), 2),
                'current_month_new_income': round(float(aggregated_data['total_current_month_new_income'] or 0), 2),
                'monthly_new_income_target': round(float(aggregated_data['total_monthly_new_income_target'] or 0), 2),
                'monthly_new_income_completion_rate': round(float(monthly_new_income_completion_rate), 2),
                'current_month_existing_income': round(float(aggregated_data['total_current_month_existing_income'] or 0), 2),
                'monthly_existing_income_target': round(float(aggregated_data['total_monthly_existing_income_target'] or 0), 2),
                'monthly_existing_income_completion_rate': round(float(monthly_existing_income_completion_rate), 2),
                'annual_accumulated_income': round(float(aggregated_data['total_annual_accumulated_income'] or 0), 2),
                'accumulated_target_completion_rate': round(float(accumulated_target_completion_rate), 2),
            }
        )

    @classmethod
    def update_all_months(cls):
        # 获取所有不同的 year_month
        year_months = DepartmentMonthlyIncome.objects.values_list('year_month', flat=True).distinct()
        for year_month in year_months:
            cls.calculate_and_update_for_month(year_month)


class ReceivablesData(models.Model):
    total_receivables = models.FloatField(verbose_name="应收账款总额", help_text="所有未结清的应收账款总额", null=True, blank=True)
    receivables_0_3_months = models.FloatField(verbose_name="0-3个月", help_text="应收账款中，开票日期距今0-3个月的金额总计", null=True, blank=True)
    receivables_3_6_months = models.FloatField(verbose_name="3-6个月", help_text="应收账款中，开票日期距今3-6个月的金额总计", null=True, blank=True)
    receivables_6_12_months = models.FloatField(verbose_name="6-12个月", help_text="应收账款中，开票日期距今6-12个月的金额总计", null=True, blank=True)
    receivables_1_2_years = models.FloatField(verbose_name="1-2年", help_text="应收账款中，开票日期距今1-2年的金额总计", null=True, blank=True)
    receivables_2_3_years = models.FloatField(verbose_name="2-3年", help_text="应收账款中，开票日期距今2-3年的金额总计", null=True, blank=True)
    receivables_3_5_years = models.FloatField(verbose_name="3-5年", help_text="应收账款中，开票日期距今3-5年的金额总计", null=True, blank=True)
    receivables_over_5_years = models.FloatField(verbose_name="5年以上", help_text="应收账款中，开票日期超过5年的金额总计", null=True, blank=True)

    class Meta:
        verbose_name = "公司应收账款情况"
        verbose_name_plural = "公司应收账款情况"
        db_table_comment = '存储公司应收账款数据，包括各时间段的应收账款金额总计'

    def __str__(self):
        return f"Receivables Data with total receivables: {self.total_receivables}"

    @staticmethod
    def get_field_labels():
        return {
            'total_receivables': '应收账款总额',
            'receivables_0_3_months': '0-3个月',
            'receivables_3_6_months': '3-6个月',
            'receivables_6_12_months': '6-12个月',
            'receivables_1_2_years': '1-2年',
            'receivables_2_3_years': '2-3年',
            'receivables_3_5_years': '3-5年',
            'receivables_over_5_years': '5年以上',
        }

    def get_field_label(self, field_name):
        field_labels = self.get_field_labels()
        return field_labels.get(field_name, field_name)

    @staticmethod
    def calculate_and_update_receivables():
        now = datetime.now().date()
        periods = [
            (0, 3, 'receivables_0_3_months'),
            (3, 6, 'receivables_3_6_months'),
            (6, 12, 'receivables_6_12_months'),
            (12, 24, 'receivables_1_2_years'),
            (24, 36, 'receivables_2_3_years'),
            (36, 60, 'receivables_3_5_years'),
            (60, None, 'receivables_over_5_years')
        ]

        invoices = InvoiceRecord.objects.annotate(
            receivable_amount=ExpressionWrapper(
                F('total_amount') - Sum('repaymentrecord__repayment_amount', default=0),
                output_field=fields.DecimalField()
            )
        )

        total_receivables = invoices.aggregate(total=Sum('receivable_amount'))['total'] or 0.0
        receivables_data = {'total_receivables': total_receivables}

        for start_month, end_month, field_name in periods:
            if end_month is not None:
                start_date = now - timedelta(days=end_month * 30)
                end_date = now - timedelta(days=start_month * 30)
                receivable_amount = invoices.filter(invoice_date__range=(start_date, end_date)).aggregate(
                    total=Sum('receivable_amount'))['total'] or 0.0
            else:
                start_date = now - timedelta(days=start_month * 30)
                receivable_amount = invoices.filter(invoice_date__lte=start_date).aggregate(
                    total=Sum('receivable_amount'))['total'] or 0.0

            receivables_data[field_name] = receivable_amount

        ReceivablesData.objects.update_or_create(
            id=1,
            defaults={field: round(value, 2) for field, value in receivables_data.items()}
        )


class ReceivablesDetail(models.Model):
    project_name = models.CharField(max_length=255, verbose_name="项目名称", help_text="非空，一致性验证", null=True, blank=True)
    primary_department = models.CharField(max_length=255, verbose_name="一级部门", help_text="非空，一致性验证", null=True, blank=True)
    project_manager = models.CharField(max_length=255, verbose_name="项目负责人", help_text="非空，一致性验证", null=True, blank=True)
    outstanding_amount = models.FloatField(verbose_name="待回款金额", help_text="当前仍待回款的金额（大于0）", null=True, blank=True)

    class Meta:
        verbose_name = "公司应收账款明细"
        verbose_name_plural = "公司应收账款明细"
        db_table_comment = '存储公司应收账款明细数据，包括项目名称、一级部门、项目负责人和待回款金额等'

    def __str__(self):
        return f"Receivables Detail for project: {self.project_name}"

    @staticmethod
    def get_field_labels():
        return {
            'project_name': '项目名称',
            'primary_department': '一级部门',
            'project_manager': '项目负责人',
            'outstanding_amount': '待回款金额',
        }

    def get_field_label(self, field_name):
        field_labels = self.get_field_labels()
        return field_labels.get(field_name, field_name)

    @staticmethod
    def calculate_and_update_details():
        # 计算每个项目的应收账款明细
        invoices = InvoiceRecord.objects.annotate(
            receivable_amount=ExpressionWrapper(
                F('total_amount') - Sum('repaymentrecord__repayment_amount', default=0),
                output_field=fields.DecimalField()
            )
        ).filter(receivable_amount__gt=0)

        receivables_details = []

        for invoice in invoices:
            project = invoice.project_name.project_name
            receivable_amount = round(float(invoice.receivable_amount), 2)
            project_ledger = invoice.project_name.project_leader

            if project_ledger:
                receivables_details.append(
                    ReceivablesDetail(
                        project_name=project,
                        primary_department=invoice.project_name.primary_department,
                        project_manager=project_ledger,
                        outstanding_amount=receivable_amount
                    )
                )

        # 批量更新或创建 ReceivablesDetail 实例
        ReceivablesDetail.objects.all().delete()  # 清空原有数据
        ReceivablesDetail.objects.bulk_create(receivables_details)


class EmployeeStatus(models.Model):
    total_employees = models.IntegerField(verbose_name="员工总数", help_text="全职和试用，状态为在职的员工总数", null=True, blank=True)
    new_employees_current_year = models.IntegerField(verbose_name="当年新入职员工数", help_text="当年新入职的员工数（全职和试用）", null=True, blank=True)
    regularized_employees = models.IntegerField(verbose_name="转正人数", help_text="当年转正的员工总数", null=True, blank=True)
    probation_failed = models.IntegerField(verbose_name="试用期未通过人数", help_text="当年试用期未通过的离职员工数", null=True, blank=True)
    employees_left_current_year = models.IntegerField(verbose_name="当年离职员工数", help_text="当年离职的员工总数", null=True, blank=True)
    intern_employees = models.IntegerField(verbose_name="实习人数", help_text="在职的实习生总数", null=True, blank=True)
    part_time_employees = models.IntegerField(verbose_name="兼职人数", help_text="在职的兼职员工总数", null=True, blank=True)

    class Meta:
        verbose_name = "公司人员情况"
        verbose_name_plural = "公司人员情况"
        db_table_comment = '存储公司人员情况数据，包括员工总数、当年新入职员工数、转正人数、试用期未通过人数、当年离职员工数、实习人数和兼职人数等'

    def __str__(self):
        return f"Employee Status for {self.total_employees} employees"

    @staticmethod
    def get_field_labels():
        return {
            'total_employees': '员工总数',
            'new_employees_current_year': '当年新入职员工数',
            'regularized_employees': '转正人数',
            'probation_failed': '试用期未通过人数',
            'employees_left_current_year': '当年离职员工数',
            'intern_employees': '实习人数',
            'part_time_employees': '兼职人数',
        }

    def get_field_label(self, field_name):
        field_labels = self.get_field_labels()
        return field_labels.get(field_name, field_name)

    @staticmethod
    def calculate_and_update_status():
        current_year = datetime.now().year

        total_employees = EmployeeInformation.objects.filter(
            Q(employment_type__in=['全职', '试用']) & Q(status='在职')
        ).count()

        new_employees_current_year = EmployeeInformation.objects.filter(
            Q(entry_date__year=current_year) & Q(employment_type__in=['全职', '试用'])
        ).count()

        regularized_employees = EmployeeInformation.objects.filter(
            regularization_date__year=current_year
        ).count()

        probation_failed = EmployeeInformation.objects.filter(
            Q(resignation_date__year=current_year) & Q(resignation_type='试用未通过')
        ).count()

        employees_left_current_year = EmployeeInformation.objects.filter(
            resignation_date__year=current_year
        ).count()

        intern_employees = EmployeeInformation.objects.filter(
            Q(employment_type='实习') & Q(status='在职')
        ).count()

        part_time_employees = EmployeeInformation.objects.filter(
            Q(employment_type='兼职') & Q(status='在职')
        ).count()

        # 更新或创建 EmployeeStatus 实例
        EmployeeStatus.objects.update_or_create(
            id=1,  # 假设只有一条记录
            defaults={
                'total_employees': total_employees,
                'new_employees_current_year': new_employees_current_year,
                'regularized_employees': regularized_employees,
                'probation_failed': probation_failed,
                'employees_left_current_year': employees_left_current_year,
                'intern_employees': intern_employees,
                'part_time_employees': part_time_employees
            }
        )


class EmployeePerformance(models.Model):
    year = models.IntegerField(verbose_name="年份", help_text="数据统计的年份", null=True, blank=True)
    name = models.CharField(max_length=255, verbose_name="姓名", help_text="员工姓名", null=True, blank=True)
    department = models.CharField(max_length=255, verbose_name="一级部门", help_text="员工所属的一级部门", null=True, blank=True)
    current_year_income = models.FloatField(verbose_name="当年收入", help_text="当年通过销售活动累计的收入", null=True, blank=True)
    income_target = models.FloatField(verbose_name="收入目标", help_text="当年为员工设定的销售收入目标", null=True, blank=True)
    income_target_completion_rate = models.FloatField(verbose_name="收入目标完成率", help_text="当年收入与收入目标的完成比率", null=True, blank=True)
    commission_paid = models.FloatField(verbose_name="已发放提成", help_text="当年已发放给员工的提成金额", null=True, blank=True)
    accrued_amount = models.FloatField(verbose_name="计提金额", help_text="当年为提成预留的金额，尚未发放", null=True, blank=True)
    other_bonuses = models.FloatField(verbose_name="其他奖金", help_text="当年员工通过其他奖励机制获得的奖金总额", null=True, blank=True)

    class Meta:
        verbose_name = "员工业绩及提成情况"
        verbose_name_plural = "员工业绩及提成情况"
        db_table_comment = '存储公司员工业绩及提成情况，包括年份、姓名、部门、收入、收入目标及完成率、已发放提成、计提金额和其他奖金等'

    def __str__(self):
        return f"Employee Performance for {self.name} in {self.year}"

    @staticmethod
    def get_field_labels():
        return {
            'year': '年份',
            'name': '姓名',
            'department': '一级部门',
            'current_year_income': '当年收入',
            'income_target': '收入目标',
            'income_target_completion_rate': '收入目标完成率',
            'commission_paid': '已发放提成',
            'accrued_amount': '计提金额',
            'other_bonuses': '其他奖金',
        }

    def get_field_label(self, field_name):
        field_labels = self.get_field_labels()
        return field_labels.get(field_name, field_name)

    @staticmethod
    def calculate_and_update_performance():
        current_year = datetime.now().year

        # 获取所有员工
        employees = EmployeeInformation.objects.all()

        for employee in employees:
            name = employee.name
            department = employee.primary_department

            # 当年收入
            current_year_income = EmployeeProjectIncomeSettlement.objects.filter(
                name=name,
                year_month__year=current_year
            ).aggregate(total=Sum(F('sales_income')))['total'] or 0.0

            # 收入目标
            income_target = EmployeePerformanceTarget.objects.filter(
                name=name,
                year=current_year
            ).aggregate(total=Sum(F('total_revenue_target')))['total'] or 0.0

            # 收入目标完成率
            income_target_completion_rate = (float(current_year_income) / float(income_target) * 100) if income_target > 0 else 0.0

            # 已发放提成
            commission_paid = EmployeeCommissionDetail.objects.filter(
                employee__name=name,
                year=current_year
            ).aggregate(total=Sum(F('amount_paid')))['total'] or 0.0

            # 计提金额
            accrued_amount = EmployeeCommissionDetail.objects.filter(
                employee__name=name,
                year=current_year
            ).aggregate(total=Sum(F('accrued_amount')))['total'] or 0.0

            # 其他奖金
            other_bonuses = BonusAllocation.objects.filter(
                name=name,
                year_month__year=current_year
            ).aggregate(total=Sum(F('award_amount')))['total'] or 0.0

            # 更新或创建 EmployeePerformance 实例
            EmployeePerformance.objects.update_or_create(
                year=current_year,
                name=name,
                defaults={
                    'department': department,
                    'current_year_income': round(current_year_income, 2),
                    'income_target': round(income_target, 2),
                    'income_target_completion_rate': round(income_target_completion_rate, 2),
                    'commission_paid': round(commission_paid, 2),
                    'accrued_amount': round(accrued_amount, 2),
                    'other_bonuses': round(other_bonuses, 2)
                }
            )


class EmployeeAttendance(models.Model):
    name = models.CharField(max_length=255, verbose_name="姓名", help_text="员工的姓名", null=True, blank=True)
    late = models.IntegerField(verbose_name="迟到", help_text="当年累计迟到的次数", null=True, blank=True)
    early_departure = models.IntegerField(verbose_name="早退", help_text="当年累计早退的次数", null=True, blank=True)
    absenteeism = models.IntegerField(verbose_name="旷工", help_text="当年累计旷工的天数", null=True, blank=True)
    annual_leave = models.IntegerField(verbose_name="年假", help_text="当年使用的年假天数", null=True, blank=True)
    personal_leave = models.IntegerField(verbose_name="事假", help_text="当年使用的事假天数", null=True, blank=True)
    sick_leave = models.IntegerField(verbose_name="病假", help_text="当年使用的病假天数", null=True, blank=True)
    other_leave = models.IntegerField(verbose_name="其他", help_text="当年使用的其他类型假期天数", null=True, blank=True)

    class Meta:
        verbose_name = "员工出勤情况"
        verbose_name_plural = "员工出勤情况"
        db_table_comment = '存储公司员工出勤情况数据，包括姓名、迟到、早退、旷工、年假、事假、病假和其他假期等'

    def __str__(self):
        return f"Employee Attendance for {self.name}"

    @staticmethod
    def get_field_labels():
        return {
            'name': '姓名',
            'late': '迟到',
            'early_departure': '早退',
            'absenteeism': '旷工',
            'annual_leave': '年假',
            'personal_leave': '事假',
            'sick_leave': '病假',
            'other_leave': '其他',
        }

    def get_field_label(self, field_name):
        field_labels = self.get_field_labels()
        return field_labels.get(field_name, field_name)

    @staticmethod
    def calculate_and_update_attendance(year):
        # 获取所有员工姓名
        employees = EmployeeInformation.objects.values_list('name', flat=True).distinct()

        for employee in employees:
            # 计算每个员工的出勤情况
            attendance_data = EmployeeAttendanceRecord.objects.filter(
                employee=employee,
                year_month__year=year
            ).aggregate(
                total_late=Sum('late'),
                total_early_departure=Sum('early_leave'),
                total_absenteeism=Sum('absenteeism'),
                total_annual_leave=Sum('annual_leave'),
                total_personal_leave=Sum('personal_leave'),
                total_sick_leave=Sum('sick_leave')
            )

            # 计算其他假期天数
            other_leave_data = OtherLeaveDetails.objects.filter(
                attendance_record__employee=employee,
                attendance_record__year_month__year=year
            ).aggregate(total_other_leave=Sum('days'))

            # 提取计算结果
            late = attendance_data['total_late'] or 0
            early_departure = attendance_data['total_early_departure'] or 0
            absenteeism = attendance_data['total_absenteeism'] or 0
            annual_leave = attendance_data['total_annual_leave'] or 0
            personal_leave = attendance_data['total_personal_leave'] or 0
            sick_leave = attendance_data['total_sick_leave'] or 0
            other_leave = other_leave_data['total_other_leave'] or 0

            # 更新或创建 EmployeeAttendance 实例
            EmployeeAttendance.objects.update_or_create(
                name=employee,
                defaults={
                    'late': late,
                    'early_departure': early_departure,
                    'absenteeism': absenteeism,
                    'annual_leave': annual_leave,
                    'personal_leave': personal_leave,
                    'sick_leave': sick_leave,
                    'other_leave': other_leave
                }
            )


class ProjectLedgerJr(models.Model):
    project_id = models.AutoField(primary_key=True, verbose_name="项目编号", help_text="主键，自增")
    project_name = models.CharField(max_length=255, verbose_name="项目名称", help_text="项目的名称")
    start_date = models.DateField(verbose_name="开始日期", help_text="项目创建日期")
    end_date = models.DateField(verbose_name="结束日期", help_text="项目完成日期")
    primary_department = models.CharField(max_length=255, verbose_name="一级部门", help_text="项目归属的一级部门")
    customer_name = models.CharField(max_length=255, verbose_name="客户名称", help_text="项目相关的客户名称")
    province = models.CharField(max_length=255, verbose_name="省", help_text="项目所在省份")
    city = models.CharField(max_length=255, verbose_name="市", help_text="项目所在城市")
    district = models.CharField(max_length=255, verbose_name="区县", help_text="项目所在区县")
    project_leader = models.CharField(max_length=255, verbose_name="负责人", help_text="项目负责人")
    project_members = models.CharField(max_length=255, verbose_name="项目组员", help_text="参与项目的团队成员")
    project_status = models.CharField(max_length=20, choices=[
        ('进行中', '进行中'),
        ('暂停', '暂停'),
        ('待收款', '待收款'),
        ('完成', '完成')
    ], verbose_name="项目状态", help_text="项目当前状态")
    resource_type = models.CharField(max_length=20, choices=[
        ('公司', '公司'),
        ('个人', '个人')
    ], verbose_name="资源类型", help_text="项目资源类型")
    project_nature = models.CharField(max_length=20, choices=[
        ('新增', '新增'),
        ('存量', '存量'),
        ('新增及存量', '新增及存量'),
        ('老客户新业务', '老客户新业务')
    ], verbose_name="项目性质", help_text="项目的性质")
    project_progress = models.CharField(max_length=255, verbose_name="项目进度", help_text="项目当前进度，可配置")
    contract_date = models.DateField(verbose_name="签约时间", help_text="项目签约日期")
    contract_amount = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="标的金额",
                                          help_text="合同金额，单位元")
    contract_rate = models.DecimalField(max_digits=5, decimal_places=2, verbose_name="合同费率",
                                        help_text="合同费率，单位为百分比")
    revenue = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="收入",
                                  help_text="非空，项目收入，单位元")
    cost_rate = models.DecimalField(max_digits=5, decimal_places=2, verbose_name="成本费率",
                                    help_text="成本费率，单位为百分比")
    cost = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="成本", help_text="非空，项目成本，单位元")
    net_income = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="净收入", help_text="净收入，单位元")
    total_amount_including_tax = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="价税合计金额",
                                                     help_text="从开票记录获取的总开票金额，单位元")
    repayment_amount = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="回款金额",
                                           help_text="从回款记录获取的总回款金额，单位元")
    receivable_net_income = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="应收净收入",
                                                help_text="应收净收入，单位元")
    actual_net_income = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="实收净收入",
                                            help_text="实收净收入，单位元")
    outstanding_net_income = models.DecimalField(max_digits=10, decimal_places=2, verbose_name="待收净收入",
                                                 help_text="待收净收入，单位元")
    notes = models.TextField(verbose_name="备注", help_text="附加信息")

    class Meta:
        verbose_name = "公司项目台账"
        verbose_name_plural = "公司项目台账"
        # 添加模型注释
        db_table_comment = '存储公司项目台账数据，包括项目编号、项目名称、开始日期、结束日期、一级部门、客户名称、省、市、区县、负责人、项目组员、项目状态、资源类型、项目性质、项目进度、签约时间、标的金额、合同费率、收入、成本费率、成本、净收入、价税合计金额、回款金额、应收净收入、实收净收入、待收净收入和备注等'

    def __str__(self):
        return f"Project Ledger: {self.project_name} ({self.project_id})"

    @staticmethod
    def get_field_labels():
        return {
            'project_id': '项目编号',
            'project_name': '项目名称',
            'start_date': '开始日期',
            'end_date': '结束日期',
            'primary_department': '一级部门',
            'customer_name': '客户名称',
            'province': '省',
            'city': '市',
            'district': '区县',
            'project_leader': '负责人',
            'project_members': '项目组员',
            'project_status': '项目状态',
            'resource_type': '资源类型',
            'project_nature': '项目性质',
            'project_progress': '项目进度',
            'contract_date': '签约时间',
            'contract_amount': '标的金额',
            'contract_rate': '合同费率',
            'revenue': '收入',
            'cost_rate': '成本费率',
            'cost': '成本',
            'net_income': '净收入',
            'total_amount_including_tax': '价税合计金额',
            'repayment_amount': '回款金额',
            'receivable_net_income': '应收净收入',
            'actual_net_income': '实收净收入',
            'outstanding_net_income': '待收净收入',
            'notes': '备注',
        }

    def get_field_label(self, field_name):
        field_labels = self.get_field_labels()
        return field_labels.get(field_name, field_name)