all-project/退学、休学、复学、毕业/process_student_changes_fixed.sh

#!/bin/bash

# ===========================================
# 学生状态变更处理脚本 - 毕业/退学/休学 (修复版本)
# 处理类型: XIU_XUE(休学), TUI_XUE(退学), BI_YE(毕业)
# ===========================================

# 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_changes_fixed_$(date +%Y%m%d_%H%M%S).log"

# Lock file to prevent multiple instances
LOCK_FILE="${SCRIPT_DIR}/process_student_changes_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执行失败: $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
    # Redirect stderr to /dev/null to discard warnings, only capture actual data
    result=$(mysql -h"$DB_HOST" -P"$DB_PORT" -u"$DB_USER" -p"$DB_PASS" -ss "$DB_NAME" -e "$sql" 2>/dev/null)
    local exit_code=$?

    if [[ $exit_code -ne 0 ]]; then
        log "ERROR" "查询执行失败: $sql"
        log "ERROR" "MySQL错误码: $exit_code"
        return 1
    fi

    echo "$result"
}

# Process student status change
process_student_change() {
    local record_id="$1"
    local student_id="$2"
    local student_no="$3"
    local change_type="$4"
    local operator_id="${5:-1}"

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

    log "INFO" "正在处理学生状态变更 - ID: $record_id, 学生: $student_no ($student_id), 类型: $change_type"

    # Get dormitory assignment info before transaction
    local dorm_query="
    SELECT GROUP_CONCAT(id), GROUP_CONCAT(bed_id)
    FROM xg_dorm_bed_student
    WHERE student_id = $student_id AND status = '0' AND deleted = 0;
    "

    local dorm_info=""
    local dorm_ids=""
    local bed_ids=""

    # Execute dormitory query with error handling
    if dorm_info=$(execute_query "$dorm_query" 2>/dev/null); then
        # Parse the result - handle NULL values properly
        if [[ -n "$dorm_info" && "$dorm_info" != "NULL" && "$dorm_info" != "NULL\tNULL" ]]; then
            # Split by tab and handle NULL values
            IFS=$'\t' read -r dorm_ids_raw bed_ids_raw <<< "$dorm_info"
            if [[ "$dorm_ids_raw" != "NULL" && -n "$dorm_ids_raw" ]]; then
                dorm_ids="$dorm_ids_raw"
            fi
            if [[ "$bed_ids_raw" != "NULL" && -n "$bed_ids_raw" ]]; then
                bed_ids="$bed_ids_raw"
            fi
        fi
    else
        log "WARN" "查询宿舍分配信息失败，将跳过宿舍处理 - 学生ID: $student_id"
    fi

    log "INFO" "找到宿舍分配 - ID: ${dorm_ids:-无}, 床位: ${bed_ids:-无}"

    # 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 sys_user_his SELECT * FROM sys_user WHERE user_id = $student_id;
    INSERT IGNORE INTO xg_class_pos_msg_his SELECT * FROM xg_class_pos_msg WHERE student_id = $student_id;
    INSERT IGNORE INTO club_member_his SELECT * FROM club_member WHERE user_id = $student_id;
    INSERT IGNORE INTO stgl_member_his SELECT * FROM stgl_member WHERE user_id = $student_id;
    INSERT IGNORE INTO xg_dorm_bed_student_his SELECT * FROM xg_dorm_bed_student WHERE student_id = $student_id;

    -- 2. Update student status
    UPDATE xg_student_msg SET
        student_status = CASE
            WHEN '$change_type' = 'XIU_XUE' THEN '2'
            WHEN '$change_type' = 'TUI_XUE' THEN '4'
            WHEN '$change_type' = 'BI_YE' THEN '5'
            ELSE student_status
        END,
        update_by = $operator_id,
        update_time = NOW()
    WHERE id = $student_id;

    -- 3. Disable user account
    UPDATE sys_user SET
        status = '1',
        update_by = $operator_id,
        update_time = NOW()
    WHERE user_id = $student_id AND account_type = '2';

    -- 4. Process class position (cadre) - only for XIU_XUE, TUI_XUE, BI_YE
    UPDATE xg_class_pos_msg SET
        is_current = 0,
        cadre_status = CASE
            WHEN '$change_type' = 'XIU_XUE' THEN '6'
            WHEN '$change_type' IN ('TUI_XUE', 'BI_YE') THEN '4'
            ELSE cadre_status
        END,
        dismissal_date = CURDATE(),
        dismissal_reason = CASE
            WHEN '$change_type' = 'XIU_XUE' THEN '学生休学'
            WHEN '$change_type' = 'TUI_XUE' THEN '学生退学'
            WHEN '$change_type' = 'BI_YE' THEN '学生毕业'
            ELSE dismissal_reason
        END,
        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;

    -- 5. Process club membership - only for XIU_XUE, TUI_XUE, BI_YE
    UPDATE club_member SET
        member_status = '4',
        update_by = $operator_id,
        update_time = NOW()
    WHERE user_id = $student_id AND member_status IN ('0', '1', '2');

    -- 6. Process volunteer membership - only for XIU_XUE, TUI_XUE, BI_YE
    UPDATE stgl_member SET
        status = 4,
        update_by = $operator_id,
        update_time = NOW()
    WHERE user_id = $student_id AND status IN (0, 1, 2);

    -- 7. Process dormitory - only for TUI_XUE, BI_YE
    -- First, clear dormitory info from student record
    UPDATE xg_student_msg SET
        dormitory_building = NULL,
        dormitory_room = NULL,
        ext_field1 = NULL,
        update_time = NOW(),
        update_by = $operator_id
    WHERE id = $student_id;"

    # Add dormitory processing only if assignments exist and are valid
    if [[ -n "$dorm_ids" && "$dorm_ids" != "NULL" && -n "$bed_ids" && "$bed_ids" != "NULL" ]]; then
        transaction_sql+="
    -- Process dormitory checkout for specific assignments
    UPDATE xg_dorm_bed_student SET
        status = '1',
        checkout_time = NOW(),
        update_time = NOW(),
        update_by = $operator_id
    WHERE FIND_IN_SET(id, '$dorm_ids') AND deleted = 0;

    -- Update bed status for specific beds
    UPDATE xg_dorm_bed SET
        bed_status = '0'
    WHERE FIND_IN_SET(id, '$bed_ids') AND deleted = 0;"
        log "INFO" "将处理宿舍退宿 - 分配ID: $dorm_ids, 床位ID: $bed_ids"
    else
        log "INFO" "无需处理宿舍退宿（无有效宿舍分配）"
    fi

    transaction_sql+="
    -- 8. Update change record status
    UPDATE xg_student_status_change SET
        process_status = 1,
        update_by = $operator_id,
        update_time = NOW()
    WHERE id = $record_id;

    COMMIT;
    "

    # Execute transaction (using execute_sql to allow error output for debugging)
    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" "处理类型: XIU_XUE(休学), TUI_XUE(退学), BI_YE(毕业)"
    log "INFO" "以连续模式运行，间隔10分钟"

    # 测试数据库连接
    log "INFO" "正在测试数据库连接..."
    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, change_type, operator_id
        FROM xg_student_status_change
        WHERE process_status = 0
          AND change_type IN ('XIU_XUE', 'TUI_XUE', 'BI_YE')
          AND deleted = 0
          AND effective_date <= NOW()
        ORDER BY create_time ASC
        LIMIT $BATCH_SIZE;
        "

        local records
        records=$(execute_query "$query_sql")

        if [[ $? -ne 0 ]]; then
            log "ERROR" "获取待处理记录失败"
            sleep $CYCLE_INTERVAL
            continue
        fi

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

        if [[ $clean_records -eq 0 ]]; then
            log "INFO" "未找到未处理的记录，正在等待..."
        else
            log "INFO" "找到 $clean_records 条待处理记录"

            local processed_count=0
            local failed_count=0

            # Process records directly from the query result, skip header and empty lines
            local is_first_line=true
            while IFS=$'\t' read -r record_id student_id student_no change_type operator_id; do
                # Skip completely empty lines
                [[ -z "$record_id$student_id$student_no$change_type$operator_id" ]] && continue

                # 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 "$change_type" ]]; then
                    log "WARN" "跳过无效记录 - ID: '$record_id', 学生ID: '$student_id', 学号: '$student_no', 类型: '$change_type'"
                    continue
                fi

                # Log the record being processed for debugging
                log "INFO" "读取到记录 - ID: $record_id, 学生ID: $student_id, 学号: $student_no, 类型: $change_type, 操作员: ${operator_id:-1}"

                local retry_count=0
                local success=false

                while [[ $retry_count -lt $MAX_RETRY && $success == false ]]; do
                    if process_student_change "$record_id" "$student_id" "$student_no" "$change_type" "$operator_id"; then
                        success=true
                        ((processed_count++))
                    else
                        ((retry_count++))
                        if [[ $retry_count -lt $MAX_RETRY ]]; then
                            log "WARN" "处理失败，$RETRY_DELAY 秒后重试... (尝试 $retry_count/$MAX_RETRY)"
                            sleep $RETRY_DELAY
                        fi
                    fi
                done

                if [[ $success == false ]]; then
                    ((failed_count++))
                    log "ERROR" "$MAX_RETRY 次尝试后仍处理失败 - ID: $record_id"
                fi

            done <<< "$(echo "$records" | grep -v "^mysql:" | grep -v "^$")"

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

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

# Run main function
main "$@"