all-project/学籍号刷新/process_student_id_card_refresh.sh

#!/bin/bash

# ===========================================
# 学生学籍号刷新脚本 v1.0.2
# 处理类型: ID_CARD_REFRESH(学籍号刷新)
# 刷新规则: ext_field2 = 'G' + id_card
# 优化: 直接UPDATE + ROW_COUNT()，无需预查询
# 新增: 支持run_refresh.sh bg模式后台执行
# ===========================================

set -euo pipefail

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

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

# Lock file to prevent multiple instances
LOCK_FILE="${SCRIPT_DIR}/process_student_id_card_refresh.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
}

# 检查脚本是否已在运行
if [[ -f "$LOCK_FILE" ]]; then
    if kill -0 $(cat "$LOCK_FILE") 2>/dev/null; then
        log "INFO" "脚本已在运行 (PID: $(cat "$LOCK_FILE"))"
        exit 1
    else
        log "WARN" "删除过期的锁定文件"
        rm -f "$LOCK_FILE"
    fi
fi

# Create lock file
echo $$ > "$LOCK_FILE"

# 清理函数
cleanup() {
    [[ -f "$LOCK_FILE" ]] && rm -f "$LOCK_FILE"
    log "INFO" "脚本执行完成"
}

trap cleanup EXIT

# Database functions
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"
}

# Process student ID card refresh in batch
process_id_card_refresh_batch() {
    local batch_offset="$1"
    local batch_size="$2"

    log "INFO" "开始处理学籍号刷新批次 - 偏移量: $batch_offset, 批次大小: $batch_size"

    # 查找需要刷新的学生记录（ext_field2为空或空字符串，且id_card不为空）
    local find_records_sql="
    SELECT id, student_no, student_name, id_card, ext_field2
    FROM xg_student_msg
    WHERE (ext_field2 IS NULL OR ext_field2 = '')
      AND id_card IS NOT NULL
      AND id_card != ''
      AND deleted = 0
    ORDER BY id ASC
    LIMIT $batch_offset, $batch_size;
    "

    local records
    records=$(execute_sql "$find_records_sql")

    # 检查是否有记录需要处理
    local data_records
    data_records=$(echo "$records" | grep -E '^[0-9]' || true)

    if [[ -z "$data_records" ]]; then
        log "INFO" "未找到需要刷新的学生记录"
        return 0
    fi

    log "INFO" "找到需要刷新的学生记录，开始处理..."

    local processed_count=0
    local failed_count=0
    local student_ids=""

    # 收集需要更新的学生ID
    while IFS=$'\t' read -r student_id student_no student_name id_card current_ext_field2; do
        # Skip empty lines
        [[ -z "$student_id" ]] && continue

        # 生成新的学籍号：'G' + id_card
        local new_id_card="'G$id_card'"

        # 构建学生ID列表，用于批量更新
        if [[ -z "$student_ids" ]]; then
            student_ids="$student_id"
        else
            student_ids="$student_ids,$student_id"
        fi

        log "INFO" "准备刷新学籍号 - 学生ID: $student_id, 学号: $student_no, 姓名: $student_name, 身份证: $id_card, 新学籍号: G$id_card"

        ((processed_count++))

        # 单个更新（为了记录详细日志）
        local update_sql="
        UPDATE xg_student_msg SET
            ext_field2 = 'G$id_card',
            update_by = 1,
            update_time = NOW()
        WHERE id = $student_id AND deleted = 0;
        "

        if execute_sql "$update_sql"; then
            log "INFO" "成功刷新学籍号 - 学生ID: $student_id, 学号: $student_no, 新学籍号: G$id_card"
        else
            log "ERROR" "刷新学籍号失败 - 学生ID: $student_id, 学号: $student_no"
            ((failed_count++))
        fi

    done <<< "$data_records"

    log "INFO" "批次处理完成 - 找到记录: $processed_count, 失败: $failed_count"

    # 返回成功处理的记录数
    echo $((processed_count - failed_count))
}

# 一次性刷新所有符合条件的学籍号
process_all_id_card_refresh() {
    log "INFO" "开始一次性刷新所有学籍号"

    # 直接执行批量更新，并获取受影响的行数
    local update_sql="
    UPDATE xg_student_msg SET
        ext_field2 = CONCAT('G', id_card),
        update_by = 1,
        update_time = NOW()
    WHERE (ext_field2 IS NULL OR ext_field2 = '')
      AND id_card IS NOT NULL
      AND id_card != ''
      AND deleted = 0;

    SELECT ROW_COUNT() as affected_rows;
    "

    local update_result
    update_result=$(execute_sql "$update_sql")

    if [[ $? -eq 0 ]]; then
        # 从结果中提取受影响的行数
        local affected_rows
        affected_rows=$(echo "$update_result" | grep -E '^[0-9]' | awk '{print $1}' | tail -1)

        if [[ "$affected_rows" -eq 0 ]]; then
            log "INFO" "没有需要刷新的学籍号记录"
        else
            log "INFO" "学籍号刷新完成 - 成功更新: $affected_rows 条记录"
        fi
        return 0
    else
        log "ERROR" "学籍号刷新失败"
        return 1
    fi
}

# Main function
main() {
    log "INFO" "启动学生学籍号刷新处理脚本"
    log "INFO" "处理类型: ID_CARD_REFRESH(学籍号刷新)"
    log "INFO" "刷新规则: ext_field2 = 'G' + id_card"

    # 测试数据库连接
    log "INFO" "正在测试数据库连接..."
    if ! execute_sql "SELECT 1;" >/dev/null 2>&1; then
        error_exit "数据库连接失败"
    fi
    log "INFO" "数据库连接成功"

    # 检查命令行参数
    if [[ $# -gt 0 && "$1" == "once" ]]; then
        log "INFO" "执行一次性刷新模式"
        if process_all_id_card_refresh; then
            log "INFO" "一次性刷新完成"
        else
            error_exit "一次性刷新失败"
        fi
    else
        log "INFO" "执行循环监控模式，间隔10分钟"

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

            # 直接执行批量刷新，如果有记录需要处理就会更新，没有则不影响
            if process_all_id_card_refresh; then
                log "INFO" "本轮处理周期完成"
            else
                log "WARN" "本轮处理周期出现错误，将继续下一轮"
            fi

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

# Run main function
main "$@"