135 lines
4.7 KiB
Python
135 lines
4.7 KiB
Python
#!/usr/bin/env python3
|
||
"""
|
||
企业微信通讯录 → CMA 系统同步脚本
|
||
功能：
|
||
1. 从企业微信通讯录获取真实员工数据（姓名、手机、岗位）
|
||
2. 同步到 CMA 的 users 表（扩展手机号字段）
|
||
3. 同步到 CMA 的 org_nodes 表（更新真实组织架构）
|
||
"""
|
||
|
||
import json
|
||
import urllib.request
|
||
import pymysql
|
||
import hashlib
|
||
|
||
# ==== 配置 ====
|
||
MCP_URL = "http://127.0.0.1:18900/call"
|
||
DB_HOST = "127.0.0.1"
|
||
DB_PORT = 3306
|
||
DB_USER = "cma_user"
|
||
DB_PASS = "cma_pass_2026"
|
||
DB_NAME = "cma"
|
||
DEFAULT_PASSWORD = "admin123"
|
||
|
||
# ==== 工具函数 ====
|
||
def mcp_call(name, arguments={}):
|
||
req = urllib.request.Request(
|
||
MCP_URL,
|
||
data=json.dumps({"name": name, "arguments": arguments}).encode(),
|
||
headers={"Content-Type": "application/json"}
|
||
)
|
||
with urllib.request.urlopen(req, timeout=10) as resp:
|
||
return json.loads(resp.read())
|
||
|
||
def get_db():
|
||
return pymysql.connect(
|
||
host=DB_HOST, port=DB_PORT,
|
||
user=DB_USER, password=DB_PASS,
|
||
database=DB_NAME, charset="utf8mb4"
|
||
)
|
||
|
||
# ==== 主流程 ====
|
||
def sync_users():
|
||
"""同步通讯录成员到 CMA users 表"""
|
||
print("=" * 50)
|
||
print("企业微信通讯录 → CMA 用户同步")
|
||
print("=" * 50)
|
||
|
||
# 1. 获取企微通讯录
|
||
result = mcp_call("wecom_get_userlist", {"department_id": 1})
|
||
wecom_users = result.get("users", [])
|
||
print(f"\n📋 企微通讯录共 {len(wecom_users)} 人")
|
||
|
||
# 2. 连接 CMA 数据库
|
||
db = get_db()
|
||
cursor = db.cursor()
|
||
|
||
# 先检查 users 表是否有 phone 字段
|
||
cursor.execute("SHOW COLUMNS FROM users LIKE 'phone'")
|
||
if not cursor.fetchone():
|
||
print("⚠️ users 表缺少 phone 字段，准备添加...")
|
||
cursor.execute("ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT '手机号'")
|
||
db.commit()
|
||
print("✅ phone 字段已添加")
|
||
|
||
# 检查是否有 openid 字段
|
||
cursor.execute("SHOW COLUMNS FROM users LIKE 'wecom_userid'")
|
||
if not cursor.fetchone():
|
||
cursor.execute("ALTER TABLE users ADD COLUMN wecom_userid VARCHAR(100) DEFAULT NULL COMMENT '企微UserID'")
|
||
db.commit()
|
||
print("✅ wecom_userid 字段已添加")
|
||
|
||
# 检查是否有 position 字段
|
||
cursor.execute("SHOW COLUMNS FROM users LIKE 'position'")
|
||
if not cursor.fetchone():
|
||
cursor.execute("ALTER TABLE users ADD COLUMN position VARCHAR(100) DEFAULT NULL COMMENT '岗位'")
|
||
db.commit()
|
||
print("✅ position 字段已添加")
|
||
|
||
# 3. 同步用户
|
||
password_hash = hashlib.sha256(DEFAULT_PASSWORD.encode()).hexdigest()
|
||
synced = 0
|
||
skipped = 0
|
||
|
||
for u in wecom_users:
|
||
name = u.get("name", "")
|
||
mobile = u.get("mobile", "")
|
||
userid = u.get("userid", "")
|
||
position = u.get("position", "")
|
||
|
||
if not name:
|
||
skipped += 1
|
||
continue
|
||
|
||
# 检查是否已存在（按手机号或用户名匹配）
|
||
cursor.execute("SELECT id, username FROM users WHERE phone = %s OR username = %s", (mobile, name))
|
||
existing = cursor.fetchone()
|
||
|
||
if existing:
|
||
# 更新已有用户
|
||
cursor.execute(
|
||
"UPDATE users SET phone = %s, wecom_userid = %s, position = %s WHERE id = %s",
|
||
(mobile, userid, position, existing[0])
|
||
)
|
||
print(f" 🔄 更新: {name:6s} → id={existing[0]}")
|
||
else:
|
||
# 插入新用户
|
||
username = userid if userid else name
|
||
cursor.execute(
|
||
"INSERT INTO users (username, password_hash, name, role, phone, wecom_userid, position) VALUES (%s, %s, %s, %s, %s, %s, %s)",
|
||
(username, password_hash, name, "business", mobile, userid, position)
|
||
)
|
||
new_id = cursor.lastrowid
|
||
print(f" ➕ 新增: {name:6s} → id={new_id} (role=business)")
|
||
synced += 1
|
||
|
||
db.commit()
|
||
print(f"\n📊 同步完成: 新增 {synced} 人, 更新 {len(wecom_users) - synced - skipped} 人, 跳过 {skipped} 人")
|
||
|
||
# 4. 显示同步后的用户列表
|
||
cursor.execute("SELECT id, username, name, role, phone, position FROM users ORDER BY id")
|
||
all_users = cursor.fetchall()
|
||
print(f"\n📋 CMA 用户表（共 {len(all_users)} 人）:")
|
||
print(f" {'ID':>3} {'用户名':16s} {'姓名':8s} {'角色':10s} {'手机号':12s} {'岗位':10s}")
|
||
print(f" {'-'*65}")
|
||
for row in all_users:
|
||
print(f" {row[0]:>3} {row[1]:16s} {(row[2] or ''):8s} {(row[3] or ''):10s} {(row[4] or ''):12s} {(row[5] or ''):10s}")
|
||
|
||
cursor.close()
|
||
db.close()
|
||
return len(wecom_users)
|
||
|
||
|
||
if __name__ == "__main__":
|
||
sync_users()
|