企业级软件研发团队绩效考核系统开发（持续更新 Day 6）

呱牛doit
0 阅读15分钟

作者：呱牛

发布日期：2026年3月29日

标签：FastAPI、绩效考核

🔥 今日亮点

2026年3月29日 - 指标计算完成 & 分类汇总得分

  • ✅ 实现分类汇总得分计算：需求、项目、自主研发、运维工作汇总
  • ✅ 解决Cxxx得分计算错误：修复字段解析索引错误，移除按指标编号去重
  • ✅ 修复员工信息插入问题：处理重复记录，避免事务关闭
  • ✅ 优化数据处理逻辑：支持NULL值处理，确保无数据时按0存入
  • ✅ 完善指标配置：扩展到Cxxx指标，支持灵活配置

📋 文章目录

🎯 Cxxx指标计算完成

1.1 功能需求

扩展指标计算功能，支持Cxxx等多个指标的计算：

  • 统一使用通用处理方法处理不同指标
  • 确保所有指标得分正确计算和汇总
  • 实现分类汇总得分计算

1.2 实现步骤

步骤1：指标配置

# 指标配置列表
indicator_configs = [
    {'table': '*_pa_kpi_c102', 'field': 'demand_score_c102'},
    {'table': '*_pa_kpi_c103', 'field': 'demand_score_c103'},
    {'table': '*_pa_kpi_c105', 'field': 'demand_score_c105'},
    {'table': '*_pa_kpi_c106', 'field': 'demand_score_c106'},
    ...
    {'table': '*_pa_kpi_c401', 'field': 'ops_score_c401'},
    {'table': '*_pa_kpi_c402', 'field': 'ops_score_c402'},
    {'table': '*_pa_kpi_c403', 'field': 'ops_score_c403'},
]

步骤2：通用指标处理方法

@classmethod
async def _process_indicator_data(
    cls,
    auth: AuthSchema,
    table_name: str,
    field_name: str,
    assessment_period: str,
    data_date: str,
    data_timepoint_date: str,
    LOG_DIR: str,
    staff_infos: list
) -> None:
    """
    处理指标数据，生成汇总得分和UPDATE SQL
    
    参数:
    - auth: AuthSchema - 认证信息
    - table_name: str - 指标表名
    - field_name: str - 目标字段名
    - assessment_period: str - 考核期次
    - data_date: str - 数据时点
    - data_timepoint_date: str - 数据时点（date格式）
    - LOG_DIR: str - 日志目录
    - staff_infos: list - 员工信息列表
    """
    from datetime import datetime
    from collections import defaultdict
    from sqlalchemy import select
    
    # 动态导入模型
    model_name = f"*PaKpi{table_name.split('_')[-1].title()}Model"
    module_path = f"app.plugin.module_gencode.{table_name}.model"
    
    try:
        import importlib
        module = importlib.import_module(module_path)
        model = getattr(module, model_name)
    except Exception as e:
        log.error(f"导入 {table_name} 模型失败: {str(e)}")
        return
    
    # 构建查询，动态检查字段是否存在
    select_columns = []
    column_names = []
    
    # 基础字段（所有指标表都有）
    base_fields = ['staff_no', 'real_name', 'position', 'assessment_period', 'data_date']
    for field in base_fields:
        if hasattr(model, field):
            select_columns.append(getattr(model, field))
            column_names.append(field)
    
    # 检查可选字段是否存在
    optional_fields = ['indicator_no', 'indicator_content', 'work_days', 'outsourcer_name', 'judgment_condition', 'indicator_score']
    for field in optional_fields:
        if hasattr(model, field):
            select_columns.append(getattr(model, field))
            column_names.append(field)
    
    # 构建查询语句
    stmt = select(*select_columns).where(
        model.assessment_period == assessment_period,
        model.data_date == data_date
    )
    
    # 执行查询
    result = await auth.db.execute(stmt)
    data = result.fetchall()
    
    # 写入数据到日志文件
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    log_path = LOG_DIR / f"{table_name}数据{datetime.now().strftime('%Y%m%d%H%M%S')}.log"
    
    # 按工号聚合得分（累加所有记录的得分）
    staff_scores = defaultdict(float)
    from decimal import Decimal
    
    # 初始化所有员工的得分为0
    for staff in staff_infos:
        staff_no = staff[0]
        staff_scores[staff_no] = 0.0
    
    with open(log_path, 'w', encoding='utf-8') as f:
        f.write(f"查询时间: {current_time}\n")
        f.write(f"考核期次: {assessment_period}\n")
        f.write(f"数据时点: {data_date}\n")
        f.write(f"查询到{table_name}指标数据数量: {len(data)}\n\n")
        
        f.write("=" * 80 + "\n")
        f.write(f"{table_name}指标数据:\n")
        f.write("=" * 80 + "\n")
        
        for idx, row in enumerate(data, 1):
            # 使用column_names将行数据映射为字典
            row_dict = dict(zip(column_names, row))
            
            # 解析行数据
            staff_no = row_dict.get('staff_no')
            real_name = row_dict.get('real_name')
            position = row_dict.get('position')
            ass_period = row_dict.get('assessment_period')
            data_date_val = row_dict.get('data_date')
            
            # 处理可选字段
            indicator_no = row_dict.get('indicator_no')
            indicator_content = row_dict.get('indicator_content')
            outsourcer_name = row_dict.get('outsourcer_name')
            judgment_condition = row_dict.get('judgment_condition')
            indicator_score = row_dict.get('indicator_score')
            
            # 写入日志
            f.write(f"记录 {idx}:\n")
            f.write(f"  工号: {staff_no}\n")
            f.write(f"  姓名: {real_name}\n")
            f.write(f"  岗位: {position}\n")
            f.write(f"  考核期次: {ass_period}\n")
            f.write(f"  数据时点: {data_date_val}\n")
            if indicator_no:
                f.write(f"  指标编号: {indicator_no}\n")
            if indicator_content:
                f.write(f"  指标内容: {indicator_content}\n")
            if outsourcer_name:
                f.write(f"  外包姓名: {outsourcer_name}\n")
            if judgment_condition:
                f.write(f"  判定条件: {judgment_condition}\n")
            if indicator_score:
                f.write(f"  指标得分: {indicator_score}\n")
            f.write("-" * 80 + "\n")
            
            # 聚合得分（累加所有记录的得分，不按指标编号去重）
            if indicator_score:
                # 将Decimal类型转换为float类型
                score_value = float(indicator_score) if isinstance(indicator_score, Decimal) else float(indicator_score)
                staff_scores[staff_no] += score_value
    
    # 执行UPDATE语句
    from sqlalchemy import text
    update_count = 0
    for staff_no, total_score in staff_scores.items():
        update_sql = f"""
UPDATE `yurdmc_pa_performance_assessment` 
SET `{field_name}` = {total_score:.2f}, 
    `updated_time` = NOW() 
WHERE `staff_no` = '{staff_no}' 
  AND `assessment_period` = '{assessment_period}' 
  AND `data_timepoint` = '{data_timepoint_date}';
"""
        try:
            await auth.db.execute(text(update_sql))
            update_count += 1
        except Exception as e:
            log.error(f"更新员工 {staff_no} 的{field_name}得分失败: {str(e)}")
    
    # 使用flush而不是commit，让上下文管理器自动处理事务提交
    await auth.db.flush()
    log.info(f"{table_name}指标更新完成，共更新 {update_count} 条记录")
    
    # 将UPDATE SQL写入日志文件
    with open(log_path, 'a', encoding='utf-8') as f:
        f.write("\n" + "=" * 80 + "\n")
        f.write(f"{table_name}指标汇总得分与UPDATE SQL:\n")
        f.write("=" * 80 + "\n")
        
        for staff_no, total_score in staff_scores.items():
            f.write(f"工号: {staff_no}, 汇总得分: {total_score:.2f}\n")
        
        f.write("\n" + "=" * 80 + "\n")
        f.write("UPDATE SQL语句:\n")
        f.write("=" * 80 + "\n")
        for staff_no, total_score in staff_scores.items():
            update_sql = f"""
UPDATE `yurdmc_pa_performance_assessment` 
SET `{field_name}` = {total_score:.2f}, 
    `updated_time` = NOW() 
WHERE `staff_no` = '{staff_no}' 
  AND `assessment_period` = '{assessment_period}' 
  AND `data_timepoint` = '{data_timepoint_date}';
"""
            f.write(update_sql + "\n")
    
    log.info(f"{table_name}指标汇总完成，共汇总 {len(staff_scores)} 个员工的得分")

步骤3：分类汇总得分计算

@classmethod
async def _calculate_category_totals(
    cls,
    auth: AuthSchema,
    assessment_period: str,
    data_timepoint_date: str,
    staff_infos: list
) -> None:
    """
    计算分类汇总得分
    
    参数:
    - auth: AuthSchema - 认证信息
    - assessment_period: str - 考核期次
    - data_timepoint_date: str - 数据时点（date格式）
    - staff_infos: list - 员工信息列表
    """
    from sqlalchemy import select, text
    from ..yurdmc_pa_performance_assessment.model import YurdmcPaPerformanceAssessmentModel
    
    # 获取所有员工记录
    stmt = select(
        *PaPerformanceAssessmentModel.id,
        *PaPerformanceAssessmentModel.staff_no,
        *PaPerformanceAssessmentModel.demand_score_c101,
        ...
        *PaPerformanceAssessmentModel.self_dev_score_c301,
        *PaPerformanceAssessmentModel.ops_score_c401,
        *PaPerformanceAssessmentModel.ops_score_c402,
        *PaPerformanceAssessmentModel.ops_score_c403
    ).where(
        *PaPerformanceAssessmentModel.assessment_period == assessment_period,
        *PaPerformanceAssessmentModel.data_timepoint == data_timepoint_date
    )
    
    result = await auth.db.execute(stmt)
    records = result.fetchall()
    
    # 计算分类汇总得分
    update_count = 0
    for record in records:
        (record_id, staff_no, 
         demand_c101, demand_c102, demand_c103, demand_c105, demand_c106, demand_c107,
         project_c201, project_c202, project_c203, project_c204, project_c205, project_c206,
         project_c207, project_c208, project_c209, project_c210, project_c211, project_c212,
         project_c213, project_c214,
         self_dev_c301,
         ops_c401, ops_c402, ops_c403) = record
        
        # 1. 需求工作汇总得分
        demand_total = (demand_c101 or 0) + (demand_c102 or 0) + (demand_c103 or 0) + \
                      (demand_c105 or 0) + (demand_c106 or 0) + (demand_c107 or 0)
        
        # 2. 项目工作汇总得分
        project_total = (project_c201 or 0) + (project_c202 or 0) + (project_c203 or 0) + \
                       (project_c204 or 0) + (project_c205 or 0) + (project_c206 or 0) + \
                       (project_c207 or 0) + (project_c208 or 0) + (project_c209 or 0) + \
                       (project_c210 or 0) + (project_c211 or 0) + (project_c212 or 0) + \
                       (project_c213 or 0) + (project_c214 or 0)
        
        # 3. 自主研发汇总得分
        self_dev_total = self_dev_c301 or 0
        
        # 4. 运维工作汇总得分
        ops_total = (ops_c401 or 0) + (ops_c402 or 0) + (ops_c403 or 0)
        
        # 5. 质量工作汇总得分、安全工作汇总得分、OKR目标汇总得分（默认0）
        quality_total = 0
        security_total = 0
        okr_total = 0
        
        # 6. 汇总得分
        total_score = demand_total + project_total + self_dev_total + \
                     ops_total + quality_total + security_total + okr_total
        
        # 更新数据库
        update_sql = f"""
UPDATE `*_pa_performance_assessment` 
SET `demand_total_score` = {demand_total:.2f},
    `project_total_score` = {project_total:.2f},
    `self_dev_total_score` = {self_dev_total:.2f},
    `ops_total_score` = {ops_total:.2f},
    `quality_total_score` = {quality_total:.2f},
    `security_total_score` = {security_total:.2f},
    `okr_total_score` = {okr_total:.2f},
    `total_score` = {total_score:.2f},
    `updated_time` = NOW()
WHERE `id` = {record_id};
"""
        try:
            await auth.db.execute(text(update_sql))
            update_count += 1
        except Exception as e:
            log.error(f"更新员工 {staff_no} 的分类汇总得分失败: {str(e)}")
    
    # 使用flush而不是commit，让上下文管理器自动处理事务提交
    await auth.db.flush()
    log.info(f"分类汇总得分计算完成，共更新 {update_count} 条记录")

步骤4：调用分类汇总方法

# 处理其他指标
for config in indicator_configs:
    await cls._process_indicator_data(
        auth=auth,
        table_name=config['table'],
        field_name=config['field'],
        assessment_period=assessment_period,
        data_date=data_date,
        data_timepoint_date=data_timepoint_date,
        LOG_DIR=LOG_DIR,
        staff_infos=staff_infos
    )
    # 记录指标处理结果
    indicator_key = config['table'].split('_')[-1]  # 提取指标代码，如c102
    indicator_results[f'{indicator_key}_result'] = {'success_count': 1, 'message': '计算完成'}

# 计算分类汇总得分
await cls._calculate_category_totals(
    auth=auth,
    assessment_period=assessment_period,
    data_timepoint_date=data_timepoint_date,
    staff_infos=staff_infos
)

# 更新计算状态
update_data = {
    'calculation_status': '已生成'
}
await YurdmcPaExcelUploadRecordCRUD(auth).update_yurdmc_pa_excel_upload_record_crud(id=id, data=update_data)

🐛 问题排查与修复

2.1 C102指标得分计算错误

问题描述

  • xx：预期得分26，实际计算23
  • xx：预期得分686，实际计算500

问题分析

问题根源：
1. 字段解析错误：C102模型有work_days字段，但查询时未包含
2. 按指标编号去重：相同指标编号的不同判定条件得分被过滤
3. 固定索引解析：当模型字段顺序变化时导致解析错误

解决方案

# 1. 动态检查字段是否存在，包括work_days
optional_fields = ['indicator_no', 'indicator_content', 'work_days', 'outsourcer_name', 'judgment_condition', 'indicator_score']
for field in optional_fields:
    if hasattr(model, field):
        select_columns.append(getattr(model, field))
        column_names.append(field)

# 2. 使用字典映射方式解析字段值
row_dict = dict(zip(column_names, row))
staff_no = row_dict.get('staff_no')
indicator_score = row_dict.get('indicator_score')

# 3. 移除按指标编号去重，直接累加所有记录的得分
if indicator_score:
    score_value = float(indicator_score) if isinstance(indicator_score, Decimal) else float(indicator_score)
    staff_scores[staff_no] += score_value

修复效果

  • xx：10 + 3 + 13 = 26（正确）
  • xx：50 + 30 + 20 + 20 + 60 + 70 + 40 + 110 + 96 + 100 + 90 = 686（正确）

2.2 员工信息插入问题

问题描述

插入员工 xx 到绩效考核结果表失败: 创建失败: (asyncmy.errors.IntegrityError) (1062, "Duplicate entry '2985' for key 'yurdmc_pa_performance_assessment.uk_staff_no'")

问题分析

  • 员工记录已存在，插入时触发唯一键约束
  • 第一个员工插入失败后，事务被关闭，导致后续所有员工插入都失败

解决方案

# 检查记录是否已存在
from sqlalchemy import select
from ..*_pa_performance_assessment.model import YurdmcPaPerformanceAssessmentModel

check_stmt = select(*PaPerformanceAssessmentModel.id).where(
    *PaPerformanceAssessmentModel.staff_no == staff_no,
    *PaPerformanceAssessmentModel.assessment_period == assessment_period,
    *PaPerformanceAssessmentModel.data_timepoint == (datetime.strptime(data_timepoint, '%Y-%m-%d').date() if isinstance(data_timepoint, str) else data_timepoint)
)

check_result = await auth.db.execute(check_stmt)
existing_record = check_result.scalar_one_or_none()

if existing_record:
    log.info(f"员工 {staff_no} - {real_name} 已存在于绩效考核结果表中，跳过插入")
    continue

2.3 无数据时得分处理

问题描述

当指标没有数据时，得分应该按0存入，而不是NULL。

解决方案

# 初始化所有员工的得分为0
for staff in staff_infos:
    staff_no = staff[0]
    staff_scores[staff_no] = 0.0

# 处理NULL值
score_value = float(indicator_score) if isinstance(indicator_score, Decimal) else float(indicator_score)

# 分类汇总时处理NULL值
demand_total = (demand_c101 or 0) + (demand_c102 or 0) + (demand_c103 or 0) + \
              (demand_c105 or 0) + (demand_c106 or 0) + (demand_c107 or 0)

🔧 代码优化

3.1 通用指标处理方法

优化前

# 为每个指标编写单独的处理逻辑
def process_c101():
    # C101处理逻辑
    pass

def process_c102():
    # C102处理逻辑
    pass

# 重复代码多，维护成本高

优化后

# 通用处理方法，支持任意指标
@classmethod
async def _process_indicator_data(
    cls,
    auth: AuthSchema,
    table_name: str,
    field_name: str,
    assessment_period: str,
    data_date: str,
    data_timepoint_date: str,
    LOG_DIR: str,
    staff_infos: list
) -> None:
    # 动态导入模型
    # 动态构建查询
    # 统一处理逻辑
    pass

# 配置化管理指标
indicator_configs = [
    {'table': 'yurdmc_pa_kpi_c102', 'field': 'demand_score_c102'},
    {'table': 'yurdmc_pa_kpi_c103', 'field': 'demand_score_c103'},
    # 其他指标...
]

3.2 字段解析优化

优化前

# 固定索引解析，容易出错
staff_no = row[0]
indicator_no = row[5]
indicator_score = row[8]

# 当字段顺序变化时，解析错误

优化后

# 字典映射方式解析，不依赖字段顺序
row_dict = dict(zip(column_names, row))
staff_no = row_dict.get('staff_no')
indicator_no = row_dict.get('indicator_no')
indicator_score = row_dict.get('indicator_score')

# 更加健壮，支持不同表结构

3.3 去重逻辑优化

优化前

# 按指标编号去重，可能过滤有效得分
if indicator_no not in staff_indicators[staff_no]:
    staff_scores[staff_no] += score_value
    staff_indicators[staff_no].add(indicator_no)

# 相同指标编号但不同判定条件的得分被过滤

优化后

# 直接累加所有记录的得分，不移除重复
if indicator_score:
    score_value = float(indicator_score) if isinstance(indicator_score, Decimal) else float(indicator_score)
    staff_scores[staff_no] += score_value

# 确保所有得分都被正确计算

📊 完整流程

4.1 指标计算与分类汇总流程图

开始
  ↓
点击"计算考核期次得分"按钮
  ↓
查询员工基本信息（*_user_staff_info）
  ↓
写入员工信息日志
  ↓
查询C101指标数据（*_pa_kpi_c101）
  ↓
写入C101指标数据日志
  ↓
按工号聚合C101得分
  ↓
插入员工信息到绩效考核结果表
  ↓
执行UPDATE语句更新C101得分
  ↓
循环处理C102-C403指标
  ↓
  ↓  动态导入指标模型
  ↓  动态构建查询
  ↓  执行查询获取数据
  ↓  按工号聚合得分
  ↓  执行UPDATE语句更新得分
  ↓
计算分类汇总得分
  ↓
  ↓  需求工作汇总得分
  ↓  项目工作汇总得分
  ↓  自主研发汇总得分
  ↓  运维工作汇总得分
  ↓  汇总总得分
  ↓
更新计算状态为"已生成"
  ↓
返回计算结果
  ↓
结束

4.2 数据流转图

┌─────────────────────────────────────────────────────────────┐
│  数据源：Excel文件上传                                    │
│  ┌───────────────────────────────────────────────────────┐ │
│  │  *_pa_excel_upload_record                    │ │
│  │  - assessment_period: 202603                   │ │
│  │  - data_date: 2026-03-23                      │ │
│  │  - calculation_status: 未计算 → 已计算              │ │
│  └───────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
                                │
                                ▼
┌─────────────────────────────────────────────────────────────┐
│  员工信息表：*_user_staff_info                      │
│  ┌───────────────────────────────────────────────────────┐ │
│  │  staff_no, real_name, position, dept_level1_name,  │ │
│  │  dept_level2_name, team_name                     │ │
│  │  data_status = 1 (在职）                          │ │
│  └───────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
                                │
                                ▼
┌─────────────────────────────────────────────────────────────┐
│  指标数据表：*_pa_kpi_*                           │
│  ┌───────────────────────────────────────────────────────┐ │
│  │  C101, C102, C103, C105, C106, C107             │ │
│  │  C201-C214, C301, C401-C403                     │ │
│  │  staff_no, indicator_score, assessment_period, data_date │ │
│  └───────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
                                │
                                ▼
┌─────────────────────────────────────────────────────────────┐
│  绩效考核结果表：*_pa_performance_assessment        │
│  ┌───────────────────────────────────────────────────────┐ │
│  │  员工基本信息 + 指标得分                          │ │
│  │  demand_score_c101-c107 = 汇总得分               │ │
│  │  project_score_c201-c214 = 汇总得分              │ │
│  │  self_dev_score_c301 = 汇总得分                  │ │
│  │  ops_score_c401-c403 = 汇总得分                  │ │
│  │  demand_total_score = 需求工作汇总               │ │
│  │  project_total_score = 项目工作汇总               │ │
│  │  self_dev_total_score = 自主研发汇总              │ │
│  │  ops_total_score = 运维工作汇总                 │ │
│  │  total_score = 总得分                          │ │
│  └───────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘

🚀 技术要点

5.1 动态模型导入

# 动态导入模型
model_name = f*PaKpi{table_name.split('_')[-1].title()}Model"
module_path = f"app.plugin.module_gencode.{table_name}.model"

try:
    import importlib
    module = importlib.import_module(module_path)
    model = getattr(module, model_name)
except Exception as e:
    log.error(f"导入 {table_name} 模型失败: {str(e)}")
    return

优势：

  • 支持任意指标表，无需为每个指标编写单独的处理逻辑
  • 提高代码的通用性和可维护性
  • 便于扩展新的指标类型

5.2 动态字段检查

# 动态检查字段是否存在
base_fields = ['staff_no', 'real_name', 'position', 'assessment_period', 'data_date']
for field in base_fields:
    if hasattr(model, field):
        select_columns.append(getattr(model, field))
        column_names.append(field)

# 检查可选字段
optional_fields = ['indicator_no', 'indicator_content', 'work_days', 'outsourcer_name', 'judgment_condition', 'indicator_score']
for field in optional_fields:
    if hasattr(model, field):
        select_columns.append(getattr(model, field))
        column_names.append(field)

优势：

  • 支持不同结构的指标表
  • 避免字段不存在导致的错误
  • 提高代码的健壮性

5.3 字典映射解析

# 使用字典映射方式解析行数据
row_dict = dict(zip(column_names, row))

# 解析字段值
staff_no = row_dict.get('staff_no')
real_name = row_dict.get('real_name')
indicator_score = row_dict.get('indicator_score')

优势：

  • 不依赖字段顺序，更加健壮
  • 代码可读性更高
  • 便于处理不同表结构

5.4 分类汇总计算

# 计算需求工作汇总得分
demand_total = (demand_c101 or 0) + (demand_c102 or 0) + (demand_c103 or 0) + \
              (demand_c105 or 0) + (demand_c106 or 0) + (demand_c107 or 0)

# 计算项目工作汇总得分
project_total = (project_c201 or 0) + (project_c202 or 0) + (project_c203 or 0) + \
               (project_c204 or 0) + (project_c205 or 0) + (project_c206 or 0) + \
               (project_c207 or 0) + (project_c208 or 0) + (project_c209 or 0) + \
               (project_c210 or 0) + (project_c211 or 0) + (project_c212 or 0) + \
               (project_c213 or 0) + (project_c214 or 0)

# 计算总得分
total_score = demand_total + project_total + self_dev_total + \
             ops_total + quality_total + security_total + okr_total

优势：

  • 清晰的分类逻辑
  • 支持NULL值处理
  • 易于理解和维护

📈 待实现功能

6.1 质量和安全指标

6.2 OKR目标指标

6.3 性能优化

6.4 功能完善

  • 数据验证和异常处理
  • 单元测试和集成测试
  • 用户界面优化
  • 导出报表功能

​编辑

​

avatar
文章
阅读
粉丝