90 lines
3.3 KiB
Bash
Executable File
90 lines
3.3 KiB
Bash
Executable File
#!/bin/bash
|
||
# 用户购课完课统计定时任务脚本
|
||
# 执行时间：每天9:00
|
||
# 输出：Excel报表，发送到指定群
|
||
|
||
# 配置参数
|
||
DB_HOST="bj-postgres-16pob4sg.sql.tencentcdb.com"
|
||
DB_PORT="28591"
|
||
DB_NAME="vala_bi"
|
||
DB_USER="ai_member"
|
||
DB_PASS="LdfjdjL83h3h3^$&**YGG*"
|
||
SQL_PATH="/root/.openclaw/workspace/scripts/用户购课完课统计.sql"
|
||
OUTPUT_CSV="/tmp/user_course_stat_$(date +%Y%m%d).csv"
|
||
OUTPUT_EXCEL="/root/.openclaw/workspace/用户购课完课统计_$(date +%Y%m%d).xlsx"
|
||
TARGET_CHAT="chat:oc_af81515caefe26918736ad1941286224"
|
||
LOG_FILE="/var/log/user_course_stat.log"
|
||
|
||
# 记录日志
|
||
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 开始执行用户购课完课统计任务" >> $LOG_FILE
|
||
|
||
# 1. 运行SQL导出CSV
|
||
export PGPASSWORD=$DB_PASS
|
||
psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME --csv -f $SQL_PATH > $OUTPUT_CSV 2>> $LOG_FILE
|
||
|
||
if [ $? -ne 0 ]; then
|
||
echo "[$(date +'%Y-%m-%d %H:%M:%S')] SQL执行失败" >> $LOG_FILE
|
||
exit 1
|
||
fi
|
||
|
||
# 2. CSV转Excel
|
||
python3 - << EOF
|
||
import pandas as pd
|
||
import numpy as np
|
||
try:
|
||
np._get_promotion_state = lambda *args, **kwargs: 0
|
||
except:
|
||
pass
|
||
df = pd.read_csv('$OUTPUT_CSV', low_memory=False)
|
||
with pd.ExcelWriter('$OUTPUT_EXCEL') as writer:
|
||
df.to_excel(writer, sheet_name='用户统计数据', index=False)
|
||
EOF
|
||
|
||
if [ $? -ne 0 ]; then
|
||
echo "[$(date +'%Y-%m-%d %H:%M:%S')] Excel生成失败" >> $LOG_FILE
|
||
exit 1
|
||
fi
|
||
|
||
# 3. 发送到指定群（curl两步：上传文件 + 发送文件消息）
|
||
APP_ID=$(jq -r '.apps[0].appId' /root/.openclaw/credentials/xiaoxi/config.json)
|
||
APP_SECRET=$(jq -r '.apps[0].appSecret' /root/.openclaw/credentials/xiaoxi/config.json)
|
||
TOKEN=$(curl -s -X POST "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal" \
|
||
-H "Content-Type: application/json" \
|
||
-d "{\"app_id\":\"$APP_ID\",\"app_secret\":\"$APP_SECRET\"}" \
|
||
| jq -r '.tenant_access_token')
|
||
|
||
# 先发文本消息
|
||
curl -s -X POST "https://open.feishu.cn/open-apis/im/v1/messages?receive_id_type=chat_id" \
|
||
-H "Authorization: Bearer $TOKEN" \
|
||
-H "Content-Type: application/json" \
|
||
-d "{\"receive_id\":\"oc_af81515caefe26918736ad1941286224\",\"msg_type\":\"text\",\"content\":\"{\\\"text\\\":\\\"【每周定时统计】$(date +%Y年%m月%d日)用户购课与完课情况统计，请查收附件。\\\"}\"}" >> $LOG_FILE 2>&1
|
||
|
||
# 上传Excel文件
|
||
FILE_KEY=$(curl -s -X POST "https://open.feishu.cn/open-apis/im/v1/files" \
|
||
-H "Authorization: Bearer $TOKEN" \
|
||
-F "file_type=xls" \
|
||
-F "file_name=用户购课完课统计_$(date +%Y%m%d).xlsx" \
|
||
-F "file=@$OUTPUT_EXCEL" | jq -r '.data.file_key')
|
||
|
||
if [ -z "$FILE_KEY" ] || [ "$FILE_KEY" = "null" ]; then
|
||
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 文件上传失败" >> $LOG_FILE
|
||
exit 1
|
||
fi
|
||
|
||
# 发送文件消息到群
|
||
SEND_RESP=$(curl -s -X POST "https://open.feishu.cn/open-apis/im/v1/messages?receive_id_type=chat_id" \
|
||
-H "Authorization: Bearer $TOKEN" \
|
||
-H "Content-Type: application/json" \
|
||
-d "{\"receive_id\":\"oc_af81515caefe26918736ad1941286224\",\"msg_type\":\"file\",\"content\":\"{\\\"file_key\\\":\\\"${FILE_KEY}\\\"}\"}")
|
||
SEND_CODE=$(echo "$SEND_RESP" | jq -r '.code')
|
||
|
||
if [ "$SEND_CODE" != "0" ]; then
|
||
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 消息发送失败: $SEND_RESP" >> $LOG_FILE
|
||
exit 1
|
||
fi
|
||
|
||
# 清理临时文件
|
||
rm -f $OUTPUT_CSV
|
||
echo "[$(date +'%Y-%m-%d %H:%M:%S')] 任务执行完成，文件已发送到群" >> $LOG_FILE
|
||
exit 0
|