all-project/学生换班级/process_student_class_change_fixed.sh

#!/bin/bash

# ===========================================
# 学生换班级处理脚本 (修复版本)
# 处理类型: CLASS_CHANGE(换班级)
# ===========================================

# set -euo pipefail  # Temporarily disable to prevent script exit on errors

# Force C locale to ensure consistent behavior across systems
export LANG=C
export LC_ALL=C

# Load configuration
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
CONFIG_FILE="${SCRIPT_DIR}/data.conf"

if [[ ! -f "$CONFIG_FILE" ]]; then
    echo "错误：配置文件 $CONFIG_FILE 未找到！"
    exit 1
fi

source "$CONFIG_FILE"

# Create log directory
mkdir -p "$LOG_DIR"

# Log file
LOG_FILE="${LOG_DIR}/process_student_class_change_fixed_$(date +%Y%m%d_%H%M%S).log"

# Lock file to prevent multiple instances
LOCK_FILE="${SCRIPT_DIR}/process_student_class_change_fixed.lock"

# Functions
log() {
    local level="$1"
    local message="$2"
    local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    echo "[$timestamp] [$level] $message" | tee -a "$LOG_FILE"
}

error_exit() {
    local message="$1"
    log "ERROR" "$message"
    # Remove lock file
    [[ -f "$LOCK_FILE" ]] && rm -f "$LOCK_FILE"
    exit 1
}

# Database functions - Fixed version
execute_sql() {
    local sql="$1"
    local result

    result=$(mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "$sql" 2>&1)
    local exit_code=$?

    if [[ $exit_code -ne 0 ]]; then
        log "ERROR" "SQL执行失败 (退出码: $exit_code)"
        log "ERROR" "数据库连接: $DB_USER@$DB_HOST:$DB_PORT/$DB_NAME"
        log "ERROR" "失败的SQL: $sql"
        log "ERROR" "MySQL错误详情: $result"
        return 1
    fi

    echo "$result"
}

# Database query function - Fixed version for cross-platform compatibility
execute_query() {
    local sql="$1"
    local result

    # Use -ss for ultra-silent mode to ensure clean tab-separated output
    result=$(mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASS" -ss "$DB_NAME" -e "$sql" 2>&1)
    local exit_code=$?

    if [[ $exit_code -ne 0 ]]; then
        log "ERROR" "数据库查询执行失败 (退出码: $exit_code)"
        log "ERROR" "数据库连接: $DB_USER@$DB_HOST:$DB_PORT/$DB_NAME"
        log "ERROR" "失败的SQL: $sql"
        log "ERROR" "MySQL错误详情: $result"
        return 1
    fi

    echo "$result"
}

# Process student class change
process_student_class_change() {
    local record_id="$1"
    local student_id="$2"
    local student_no="$3"
    local new_class_id="$4"
    local operator_id="${5:-1}"

    # Validate input parameters
    if [[ -z "$record_id" || -z "$student_id" || -z "$student_no" || -z "$new_class_id" ]]; then
        log "ERROR" "参数不完整 - record_id: '$record_id', student_id: '$student_id', student_no: '$student_no', new_class_id: '$new_class_id'"
        return 1
    fi

    log "INFO" "Processing student class change - ID: $record_id, Student: $student_no ($student_id), New Class ID: $new_class_id"

    # Get new class information
    local class_info_sql="
    SELECT
        c.college_id, c.college_name, c.major_id, c.major_name,
        c.class_code, c.class_name, c.class_type, c.grade,
        c.training_type, c.counselor_id, c.counselor_name,
        c.class_teacher_id, c.class_teacher_name, c.school_system
    FROM xg_class_msg c
    WHERE c.id = $new_class_id AND c.deleted = 0
    "

    local class_info
    class_info=$(execute_query "$class_info_sql")

    if [[ -z "$class_info" || "$class_info" == *"Empty set"* ]]; then
        log "ERROR" "获取班级ID为 $new_class_id 的班级信息失败"
        return 1
    fi

    # Parse class information - Fixed parsing logic
    local class_data
    class_data=$(echo "$class_info" | grep -v "^mysql:" | grep -v "^$" | head -n 1)

    if [[ -z "$class_data" ]]; then
        log "ERROR" "解析班级信息失败，无法获取数据行"
        return 1
    fi

    # Use cut to extract specific fields reliably
    local college_id=$(echo "$class_data" | cut -f1)
    local college_name=$(echo "$class_data" | cut -f2)
    local major_id=$(echo "$class_data" | cut -f3)
    local major_name=$(echo "$class_data" | cut -f4)
    local class_code=$(echo "$class_data" | cut -f5)
    local class_name=$(echo "$class_data" | cut -f6)
    local class_type=$(echo "$class_data" | cut -f7)
    local grade=$(echo "$class_data" | cut -f8)
    local training_type=$(echo "$class_data" | cut -f9)
    local counselor_id=$(echo "$class_data" | cut -f10)
    local counselor_name=$(echo "$class_data" | cut -f11)
    local class_teacher_id=$(echo "$class_data" | cut -f12)
    local class_teacher_name=$(echo "$class_data" | cut -f13)
    local school_system=$(echo "$class_data" | cut -f14)

    # Handle NULL values for IDs
    local counselor_id_value class_teacher_id_value
    if [[ "$counselor_id" == "NULL" || -z "$counselor_id" ]]; then
        counselor_id_value="NULL"
    else
        counselor_id_value="'$counselor_id'"
    fi

    if [[ "$class_teacher_id" == "NULL" || -z "$class_teacher_id" ]]; then
        class_teacher_id_value="NULL"
    else
        class_teacher_id_value="'$class_teacher_id'"
    fi

    # Start transaction
    local transaction_sql="
    START TRANSACTION;

    -- 1. Backup original data to his tables (ignore if already exists)
    INSERT IGNORE INTO xg_student_msg_his SELECT * FROM xg_student_msg WHERE id = $student_id;
    INSERT IGNORE INTO xg_class_pos_msg_his SELECT * FROM xg_class_pos_msg WHERE student_id = $student_id;

    -- 2. Terminate cadre positions (干部信息终止)
    UPDATE xg_class_pos_msg SET
        is_current = 0,
        cadre_status = '4',  -- 免职
        dismissal_date = CURDATE(),
        dismissal_reason = '学生换班级',
        term_end_date = CURDATE(),
        term_duration = TIMESTAMPDIFF(MONTH, term_start_date, CURDATE()),
        update_by = $operator_id,
        update_time = NOW()
    WHERE student_id = $student_id AND is_current = 1;

    -- 3. Update student information with new class data
    UPDATE xg_student_msg SET
        -- 基础班级信息
        college_id = '$college_id',
        college_name = '$college_name',
        major_id = '$major_id',
        major_name = '$major_name',
        class_id = '$new_class_id',
        class_name = '$class_name',
        grade = '$grade',

        -- 预留字段从班级表关联获取的数据 (7182-7195行)
        school_system = '$school_system',
        academic_level = '$class_type',  -- class_type 对应 academic_level
        training_type = '$training_type',
        counselor_id = $counselor_id_value,
        counselor_name = '$counselor_name',
        class_teacher_id = $class_teacher_id_value,
        class_teacher_name = '$class_teacher_name',

        -- 更新信息
        update_by = $operator_id,
        update_time = NOW()
    WHERE id = $student_id;

    -- 4. Update class change record status
    UPDATE xg_student_class_change SET
        status = '1',  -- 已处理
        update_by = $operator_id,
        update_time = NOW()
    WHERE id = $record_id;

    COMMIT;
    "

    # 执行事务
    if execute_sql "$transaction_sql"; then
        log "INFO" "成功处理学生换班级 - ID: $record_id, 学生: $student_no"
        return 0
    else
        log "ERROR" "处理学生换班级失败 - ID: $record_id, 学生: $student_no"
        return 1
    fi
}

# Main function
main() {
    log "INFO" "启动学生换班级处理守护进程 (修复版本)"
    log "INFO" "处理类型: CLASS_CHANGE(换班级)"
    log "INFO" "以连续模式运行，间隔10分钟"

    # 测试数据库连接
    log "INFO" "正在测试数据库连接..."
    log "INFO" "连接信息: $DB_USER@$DB_HOST:$DB_PORT/$DB_NAME"
    if ! execute_sql "SELECT 1;" >/dev/null 2>&1; then
        error_exit "数据库连接失败 - 请检查连接参数和网络连接"
    fi
    log "INFO" "数据库连接测试成功"

    # Main processing loop
    while true; do
        log "INFO" "开始新的处理周期"

        # 获取未处理的记录
        local query_sql="
        SELECT id, student_id, student_no, new_class_id, update_by
        FROM xg_student_class_change
        WHERE status = '0'
          AND deleted = 0
          AND effective_time <= NOW()
        ORDER BY create_time ASC
        LIMIT $BATCH_SIZE;
        "

        local records
        if ! records=$(execute_query "$query_sql"); then
            log "ERROR" "获取待处理记录失败，${CYCLE_INTERVAL}秒后重试"
            sleep $CYCLE_INTERVAL
            continue
        fi

        # 检查是否有记录需要处理（过滤掉空行和MySQL警告信息）
        local clean_records
        clean_records=$(echo "$records" | grep -v "^mysql:" | grep -v "^$" | wc -l)

        # 去除可能的空白字符
        clean_records=$(echo "$clean_records" | tr -d ' ')

        if [[ $clean_records -le 1 ]]; then
            log "INFO" "未找到未处理的记录，正在等待..."
        else
            # 排除标题行后的实际记录数
            local actual_records=$((clean_records - 1))
            log "INFO" "找到 $actual_records 条待处理记录"

            # 调试：显示前几条记录的信息
            if [[ $actual_records -gt 0 ]]; then
                log "DEBUG" "将处理 $actual_records 条记录"
            fi

            local processed_count=0
            local failed_count=0

            # Process records line by line using a simple loop
            local filtered_data
            filtered_data=$(echo "$records" | grep -v "^mysql:" | grep -v "^$")

            log "DEBUG" "过滤后数据行数: $(echo "$filtered_data" | wc -l)"
            log "DEBUG" "过滤后数据内容预览:"
            echo "$filtered_data" | head -3 | while read -r preview_line; do
                log "DEBUG" "  DATA: $preview_line"
            done
            # Create temporary file for processing to avoid subshell variable scope issues
            local temp_file="/tmp/student_class_change_process_$$.txt"
            echo "$filtered_data" > "$temp_file"

            # Process records from temporary file (variables will be in same shell)
            local is_first_line=true
            while IFS= read -r line; do
                # Parse the tab-separated values for processing
                IFS=$'\t' read -r record_id student_id student_no new_class_id operator_id <<< "$line"

                # Skip completely empty lines
                if [[ -z "$record_id$student_id$student_no$new_class_id$operator_id" ]]; then
                    continue
                fi

                # Skip header line (first non-empty line that looks like column names)
                if [[ $is_first_line == true ]]; then
                    is_first_line=false
                    # Check if this looks like a header line
                    if [[ "$record_id" == "id" || "$record_id" =~ ^[[:space:]]*id[[:space:]]*$ ]]; then
                        log "INFO" "跳过标题行"
                        continue
                    fi
                fi

                # Validate required fields
                if [[ -z "$record_id" || -z "$student_id" || -z "$student_no" || -z "$new_class_id" ]]; then
                    log "WARN" "跳过无效记录 - ID: '$record_id'"
                    continue
                fi

                # Log the record being processed
                log "INFO" "处理记录 - ID: $record_id, 学号: $student_no"

                local retry_count=0
                local success=false

                while [[ $retry_count -lt $MAX_RETRY && $success == false ]]; do
                    log "DEBUG" "开始调用process_student_class_change函数，参数: $record_id, $student_id, $student_no, $new_class_id, $operator_id"
                    if process_student_class_change "$record_id" "$student_id" "$student_no" "$new_class_id" "$operator_id"; then
                        success=true
                        ((processed_count++))
                        log "INFO" "成功处理记录 $record_id (总计: $processed_count)"
                        log "DEBUG" "函数调用成功，success=$success, processed_count=$processed_count"
                    else
                        ((retry_count++))
                        log "DEBUG" "函数调用失败，retry_count=$retry_count, MAX_RETRY=$MAX_RETRY"
                        if [[ $retry_count -lt $MAX_RETRY ]]; then
                            log "WARN" "处理失败，重试中..."
                            sleep $RETRY_DELAY
                        fi
                    fi
                done

                if [[ $success == false ]]; then
                    ((failed_count++))
                    log "ERROR" "记录 $record_id 处理失败"
                fi
                ((line_count++))
                echo "DEBUG: 处理第 $line_count 行: $line" >&2
                log "DEBUG" "处理第 $line_count 行: $line"
                # Parse the tab-separated values for processing
                IFS=$'\t' read -r record_id student_id student_no new_class_id operator_id <<< "$line"

                # Skip completely empty lines
                if [[ -z "$record_id$student_id$student_no$new_class_id$operator_id" ]]; then
                    continue
                fi

                # Skip header line (first non-empty line that looks like column names)
                if [[ $is_first_line == true ]]; then
                    is_first_line=false
                    if [[ "$record_id" == "id" || "$record_id" =~ ^[[:space:]]*id[[:space:]]*$ ]]; then
                        continue
                    fi
                fi

                # Validate required fields
                if [[ -z "$record_id" || -z "$student_id" || -z "$student_no" || -z "$new_class_id" ]]; then
                    continue
                fi

                # Log the record being processed
                log "INFO" "处理记录 - ID: $record_id, 学号: $student_no"

                local retry_count=0
                local success=false

                while [[ $retry_count -lt $MAX_RETRY && $success == false ]]; do
                    log "DEBUG" "开始调用process_student_class_change函数，参数: $record_id, $student_id, $student_no, $new_class_id, $operator_id"
                    if process_student_class_change "$record_id" "$student_id" "$student_no" "$new_class_id" "$operator_id"; then
                        success=true
                        ((processed_count++))
                        log "INFO" "成功处理记录 $record_id (总计: $processed_count)"
                        log "DEBUG" "函数调用成功，success=$success, processed_count=$processed_count"
                    else
                        ((retry_count++))
                        log "DEBUG" "函数调用失败，retry_count=$retry_count, MAX_RETRY=$MAX_RETRY"
                        if [[ $retry_count -lt $MAX_RETRY ]]; then
                            log "WARN" "处理失败，重试中..."
                            sleep $RETRY_DELAY
                        fi
                    fi
                done

                if [[ $success == false ]]; then
                    ((failed_count++))
                    log "ERROR" "记录 $record_id 处理失败"
                fi

                log "DEBUG" "完成处理记录 $record_id，继续下一条"

            done < "$temp_file"

            log "DEBUG" "所有记录处理完成，processed_count=$processed_count, failed_count=$failed_count"

            # Clean up temporary file
            rm -f "$temp_file"

            log "INFO" "处理周期完成 - 已处理: $processed_count, 失败: $failed_count"

            log "INFO" "处理周期完成 - 已处理: $processed_count, 失败: $failed_count"
        fi

        # 等待配置的间隔时间后开始下一个周期
        log "INFO" "等待 $CYCLE_INTERVAL 秒后开始下一个处理周期..."
        sleep $CYCLE_INTERVAL
    done
}

# Run main function
main "$@"