import os import pymysql import requests import json import re import threading import urllib3 from flask import Flask, render_template, request, redirect, url_for, session, flash, jsonify, Response, stream_with_context from werkzeug.utils import secure_filename from oss_utils import upload_to_oss from ocr_utils import extract_page_number import time from datetime import datetime # Suppress InsecureRequestWarning urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning) app = Flask(__name__) app.secret_key = 'genealogy_secret_key' app.config['UPLOAD_FOLDER'] = 'uploads' os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True) # 数据库配置 DB_CONFIG = { "host": "rm-f8ze60yirdj8786u2.mysql.rds.aliyuncs.com", "port": 3306, "user": "root", "password": "csqz@20255", "db": "csqz-client", "charset": "utf8mb4", "cursorclass": pymysql.cursors.DictCursor } def get_db_connection(): return pymysql.connect(**DB_CONFIG) def format_timestamp(ts): if not ts: return '未知' try: # 兼容秒和毫秒 if ts > 10000000000: # 超过2286年的秒数，通常认为是毫秒 ts = ts / 1000 return time.strftime('%Y-%m-%d', time.localtime(ts)) except: return '未知' def manual_simplify(text): """ Simple fallback for common Traditional to Simplified conversion if AI fails to convert specific characters. """ if not text: return text mapping = { '學': '学', '國': '国', '萬': '万', '寶': '宝', '興': '兴', '華': '华', '會': '会', '葉': '叶', '藝': '艺', '號': '号', '處': '处', '見': '见', '視': '视', '言': '言', '語': '语', '貝': '贝', '車': '车', '長': '长', '門': '门', '韋': '韦', '頁': '页', '風': '风', '飛': '飞', '食': '食', '馬': '马', '魚': '鱼', '鳥': '鸟', '麥': '麦', '黃': '黄', '齊': '齐', '齒': '齿', '龍': '龙', '龜': '龟', '壽': '寿', '榮': '荣', '愛': '爱', '慶': '庆', '衛': '卫', '賢': '贤', '義': '义', '禮': '礼', '樂': '乐', '靈': '灵', '滅': '灭', '氣': '气', '智': '智', '信': '信', '仁': '仁', '勇': '勇', '嚴': '严', '銳': '锐', '優': '优', '楊': '杨', '吳': '吴', '銀': '银' } result = "" for char in text: result += mapping.get(char, char) return result def clean_name(name): """ Clean name according to Liu family genealogy rules: 1. If name is '学公' or '留学公', keep 'Gong' (exception). 2. Otherwise, if name ends with '公', remove '公'. 3. If name does not start with '留', prepend '留'. """ if not name: return name name = name.strip() # Pre-process: Ensure Simplified Chinese for specific chars name = manual_simplify(name) # 1. Check exceptions (names that SHOULD keep 'Gong') exceptions = ['学公', '留学公'] if name in exceptions: if not name.startswith('留'): name = '留' + name return name # 2. General Rule: Remove 'Gong' suffix if name.endswith('公'): name = name[:-1] # 3. Ensure 'Liu' surname if not name.startswith('留'): name = '留' + name return name def process_ai_task(record_id, image_url): """Background task to process image with AI and store result.""" print(f"[AI Task] Starting task for record {record_id}...") conn = get_db_connection() try: with conn.cursor() as cursor: cursor.execute("UPDATE genealogy_records SET ai_status = 1 WHERE id = %s", (record_id,)) conn.commit() print(f"[AI Task] Status updated to 'Processing' for record {record_id}") api_key = "a1800657-9212-4afe-9b7c-b49f015c54d3" api_url = "https://ark.cn-beijing.volces.com/api/v3/responses" prompt = """ 请分析这张家谱图片，提取其中关于人物的信息。 请务必将繁体字转换为简体字（original_name 字段除外）。 特别注意：'name' 字段必须是纯简体中文，不能包含繁体字（例如：'學'应转换为'学'，'劉'应转换为'刘'，'萬'应转换为'万'）。 请提取以下字段（如果存在）： - original_name: 原始姓名（严格保持图片上的繁体字，不做任何修改或转换） - name: 简体姓名（必须转换为简体中文，去除不需要的敬称） - sex: 性别（男/女） - birthday: 出生日期（尝试转换为YYYY-MM-DD格式，如果无法确定年份可只填月日） - father_name: 父亲姓名 - spouse_name: 配偶姓名 - generation: 第几世/代数 - name_word: 字辈（例如名字为“学勤公”，“学”为字辈；提取名字中的字辈信息） - education: 学历/功名 - title: 官职/称号 请严格以JSON列表格式返回，不要包含Markdown代码块标记（如 ```json ... ```），直接返回JSON数组。 如果包含多个人物，请都提取出来。 Do not output any reasoning or explanation, just the JSON. """ payload = { "model": "doubao-seed-1-8-251228", "stream": True, # Streaming for robust handling "input": [ { "role": "user", "content": [ {"type": "input_image", "image_url": image_url}, {"type": "input_text", "text": prompt} ] } ] } headers = { "Authorization": f"Bearer {api_key}", "Content-Type": "application/json" } max_retries = 3 last_exception = None for attempt in range(max_retries): try: print(f"[AI Task] Attempt {attempt+1}/{max_retries} connecting to API for record {record_id}...") response = requests.post( api_url, json=payload, headers=headers, timeout=1200, stream=True, verify=False, proxies={"http": None, "https": None} ) if response.status_code == 200: print(f"[AI Task] Connection established for record {record_id}, receiving stream...") full_content = "" for line in response.iter_lines(): if not line: continue line_str = line.decode('utf-8') # Debug: Print full line to understand event flow print(f"[AI Task Debug] Raw Line: {line_str[:500]}") # Truncate very long lines if line_str.startswith('data: '): json_str = line_str[6:] if json_str.strip() == '[DONE]': print("[AI Task Debug] Received [DONE]") break try: chunk = json.loads(json_str) chunk_type = chunk.get('type') # Standard OpenAI format (choices) if 'choices' in chunk and len(chunk['choices']) > 0: delta = chunk['choices'][0].get('delta', {}) if 'content' in delta: full_content += delta['content'] # Doubao/Volcengine specific formats (delta) elif chunk_type == 'response.text.delta': full_content += chunk.get('delta', '') # Check response.completed if empty elif chunk_type == 'response.completed' and not full_content: output = chunk.get('response', {}).get('output', []) for item in output: # Also extract from reasoning if it contains JSON-like text if item.get('type') == 'reasoning': summary = item.get('summary', []) for sum_item in summary: if sum_item.get('type') == 'summary_text': full_content += sum_item.get('text', '') elif item.get('type') == 'message': content = item.get('content') if isinstance(content, str): full_content += content elif isinstance(content, list): for part in content: if isinstance(part, dict) and part.get('type') == 'text': full_content += part.get('text', '') # Fallback: output_item.added elif chunk_type == 'response.output_item.added': item = chunk.get('item', {}) if item.get('role') == 'assistant': content_field = item.get('content', []) if isinstance(content_field, str): full_content += content_field elif isinstance(content_field, list): for part in content_field: if isinstance(part, dict) and part.get('type') == 'text': full_content += part.get('text', '') except Exception as e: print(f"[AI Task] Chunk parse error: {e}") else: # Fallback for non-SSE try: chunk = json.loads(line_str) if 'choices' in chunk and len(chunk['choices']) > 0: content = chunk['choices'][0]['message']['content'] full_content += content except: pass print(f"[AI Task] Stream finished. Content length: {len(full_content)}") if len(full_content) == 0: print(f"[AI Task] WARNING: No content received from AI stream.") # Continue to JSON parse to fail gracefully # Clean JSON try: # 1. Try finding [...] array start = full_content.find('[') end = full_content.rfind(']') # 2. If not found, try finding {...} object and wrap it is_single_object = False if start == -1 or end == -1 or end <= start: start = full_content.find('{') end = full_content.rfind('}') is_single_object = True if start != -1 and end != -1 and end > start: content_clean = full_content[start:end+1] else: # Fallback to regex or raw content_clean = re.sub(r'^```json\s*', '', full_content) content_clean = re.sub(r'```$', '', content_clean) parsed = json.loads(content_clean) # Normalize single object to list if is_single_object and isinstance(parsed, dict): parsed = [parsed] content_clean = json.dumps(parsed, ensure_ascii=False) elif isinstance(parsed, dict) and not isinstance(parsed, list): # Just in case json.loads parsed a dict even if we looked for [] parsed = [parsed] content_clean = json.dumps(parsed, ensure_ascii=False) # Clean names in parsed content if isinstance(parsed, list): for person in parsed: # Process Name: 'name' is Simplified from AI, 'original_name' is Traditional/Raw from AI simplified_name = person.get('name', '') original_name = person.get('original_name', '') # Apply clean logic to simplified name（本人生效：拼接“留”姓、处理“公”） cleaned_simplified = clean_name(simplified_name) person['simplified_name'] = cleaned_simplified # Store raw name in 'name' field (as requested) if original_name: person['name'] = original_name else: # Fallback: if no original_name returned, use the uncleaned name as 'name' # or keep existing logic. But user wants raw in 'name'. # If AI didn't return original_name, 'name' is likely simplified. pass # Keep 'name' as is (which is Simplified) if original_name missing # Father name：同族，需要按“留”姓规则清洗 if 'father_name' in person and person['father_name']: person['father_name'] = clean_name(person['father_name']) # Spouse name：只做繁转简，不拼接“留”姓，也不去“公” if 'spouse_name' in person and person['spouse_name']: person['spouse_name'] = manual_simplify(person['spouse_name']) # Re-serialize content_clean = json.dumps(parsed, ensure_ascii=False) with conn.cursor() as cursor: cursor.execute("UPDATE genealogy_records SET ai_status = 2, ai_content = %s WHERE id = %s", (content_clean, record_id)) conn.commit() print(f"[AI Task] SUCCESS: Record {record_id} processed and saved.") return # Success except json.JSONDecodeError: print(f"[AI Task] WARNING: JSON Parse Error for record {record_id}. Saving raw content.") with conn.cursor() as cursor: cursor.execute("UPDATE genealogy_records SET ai_status = 3, ai_content = %s WHERE id = %s", (f"JSON Parse Error. Raw: {full_content}", record_id)) conn.commit() return # Success (technically API worked, just bad content) else: print(f"[AI Task] API Error {response.status_code} for record {record_id}: {response.text[:100]}...") if response.status_code >= 500: raise Exception(f"Server Error {response.status_code}") with conn.cursor() as cursor: cursor.execute("UPDATE genealogy_records SET ai_status = 3, ai_content = %s WHERE id = %s", (f"API Error: {response.text}", record_id)) conn.commit() return except Exception as e: print(f"[AI Task] Attempt {attempt+1} failed for record {record_id}: {e}") last_exception = e if attempt < max_retries - 1: wait_time = 2 * (attempt + 1) print(f"[AI Task] Waiting {wait_time}s before retry...") time.sleep(wait_time) raise last_exception or Exception("Unknown error") except Exception as e: print(f"[AI Task] FINAL FAILURE for record {record_id}: {e}") try: with conn.cursor() as cursor: cursor.execute("UPDATE genealogy_records SET ai_status = 3, ai_content = %s WHERE id = %s", (f"Max Retries Exceeded. Error: {str(e)}", record_id)) conn.commit() except: pass finally: conn.close() print(f"[AI Task] Task finished for record {record_id}") @app.route('/manager/') def index(): if 'user_id' not in session: return redirect(url_for('login')) conn = get_db_connection() try: with conn.cursor() as cursor: # 获取家谱图片记录 (上传管理) cursor.execute("SELECT * FROM genealogy_records ORDER BY upload_time DESC") records = cursor.fetchall() finally: conn.close() return render_template('index.html', records=records) @app.route('/manager/members') def members(): if 'user_id' not in session: return redirect(url_for('login')) search_name = request.args.get('name', '').strip() page = request.args.get('page', 1, type=int) per_page = 10 offset = (page - 1) * per_page conn = get_db_connection() try: with conn.cursor() as cursor: # 1. Get total count if search_name: cursor.execute("SELECT COUNT(*) as count FROM family_member_info WHERE name LIKE %s", (f"%{search_name}%",)) else: cursor.execute("SELECT COUNT(*) as count FROM family_member_info") result = cursor.fetchone() total = result['count'] if result else 0 total_pages = (total + per_page - 1) // per_page # 2. Get paginated results, ordered by modified_time DESC (or create_time if modified is null/same) # Using COALESCE to ensure sort works even if modified_time is NULL order_clause = "ORDER BY COALESCE(modified_time, create_time) DESC" if search_name: sql = f"SELECT * FROM family_member_info WHERE name LIKE %s {order_clause} LIMIT %s OFFSET %s" cursor.execute(sql, (f"%{search_name}%", per_page, offset)) else: sql = f"SELECT * FROM family_member_info {order_clause} LIMIT %s OFFSET %s" cursor.execute(sql, (per_page, offset)) members = cursor.fetchall() # 格式化日期 for m in members: m['birthday_str'] = format_timestamp(m.get('birthday')) # 格式化创建时间 (针对 TIMESTAMP 字段) if m.get('create_time'): m['create_time_str'] = m['create_time'].strftime('%Y-%m-%d') if m.get('modified_time'): m['modified_time_str'] = m['modified_time'].strftime('%Y-%m-%d %H:%M') finally: conn.close() return render_template('members.html', members=members, search_name=search_name, page=page, total_pages=total_pages, total=total) @app.route('/manager/tree') def tree(): if 'user_id' not in session: return redirect(url_for('login')) return render_template('tree.html') @app.route('/manager/api/tree_data') def tree_data(): if 'user_id' not in session: return jsonify({"error": "Unauthorized"}), 401 conn = get_db_connection() try: with conn.cursor() as cursor: # 获取所有成员 cursor.execute("SELECT id, name, simplified_name, sex FROM family_member_info") members = cursor.fetchall() # 获取所有关系 (1:父子 2:母子 10:夫妻 11:兄弟 12:姐妹) cursor.execute("SELECT parent_mid, child_mid, relation_type FROM family_relation_info") relations = cursor.fetchall() return jsonify({"members": members, "relations": relations}) finally: conn.close() @app.route('/manager/api/save_relation', methods=['POST']) def save_relation(): if 'user_id' not in session: return jsonify({"success": False, "message": "Unauthorized"}), 401 data = request.json source_mid = data.get('source_mid') # The member being dragged target_mid = data.get('target_mid') # The member being dropped onto rel_type = int(data.get('relation_type')) sub_rel_type = int(data.get('sub_relation_type', 0)) if not source_mid or not target_mid or not rel_type: return jsonify({"success": False, "message": "参数不完整"}), 400 conn = get_db_connection() try: with conn.cursor() as cursor: # 简单处理：如果是父子/母子关系 # target_mid 是父辈，source_mid 是子辈 parent_mid = target_mid child_mid = source_mid gen_diff = 1 if rel_type == 10: # 夫妻 # 夫妻关系中，我们通常把关联人设为 parent_mid parent_mid = target_mid child_mid = source_mid gen_diff = 0 elif rel_type in [11, 12]: # 兄弟姐妹 # 这里逻辑上比较复杂，通常兄弟姐妹有共同父母。 # 简化处理：暂时存为同级关系 (gen_diff=0) parent_mid = target_mid child_mid = source_mid gen_diff = 0 # 删除旧关系 cursor.execute("DELETE FROM family_relation_info WHERE source_mid = %s", (source_mid,)) # 插入新关系 sql = """ INSERT INTO family_relation_info (parent_mid, child_mid, relation_type, sub_relation_type, source_mid, generation_diff) VALUES (%s, %s, %s, %s, %s, %s) """ cursor.execute(sql, (parent_mid, child_mid, rel_type, sub_rel_type, source_mid, gen_diff)) conn.commit() return jsonify({"success": True, "message": "关系已保存"}) except Exception as e: return jsonify({"success": False, "message": str(e)}), 500 finally: conn.close() @app.route('/manager/api/check_relations', methods=['POST']) def check_relations(): if 'user_id' not in session: return jsonify({"success": False, "message": "Unauthorized"}), 401 data = request.json people = data.get('people', []) if not people: return jsonify({"success": False, "matches": {}}) conn = get_db_connection() matches = {} try: with conn.cursor() as cursor: # Collect all father names and spouse names to query names_to_check = set() for p in people: if p.get('father_name'): names_to_check.add(p['father_name']) if p.get('spouse_name'): names_to_check.add(p['spouse_name']) if not names_to_check: return jsonify({"success": True, "matches": {}}) # Query DB format_strings = ','.join(['%s'] * len(names_to_check)) if names_to_check: sql = "SELECT id, name, simplified_name, sex, birthday FROM family_member_info WHERE name IN (%s) OR simplified_name IN (%s)" % (format_strings, format_strings) cursor.execute(sql, tuple(names_to_check) * 2) results = cursor.fetchall() else: results = [] # Organize by name db_map = {} # name -> [list of members] for r in results: # Add under 'name' (Traditional/Old Simplified) if r['name'] not in db_map: db_map[r['name']] = [] db_map[r['name']].append(r) # Add under 'simplified_name' if exists if r.get('simplified_name'): sname = r['simplified_name'] if sname not in db_map: db_map[sname] = [] # Avoid duplicates if simplified_name is same as name? # The list might contain same object reference, which is fine. if sname != r['name']: db_map[sname].append(r) # Build matches for each input person for index, p in enumerate(people): p_match = {} # Check Father fname = p.get('father_name') if fname and fname in db_map: candidates = db_map[fname] # Filter: Father should be Male usually, and older than child (if birthday available) valid_fathers = [c for c in candidates if c['sex'] == 1] if valid_fathers: p_match['father'] = valid_fathers # Return all candidates # Check Spouse sname = p.get('spouse_name') if sname and sname in db_map: candidates = db_map[sname] # Filter: Spouse usually opposite sex target_sex = 1 if p.get('sex') == '女' else 2 valid_spouses = [c for c in candidates if c['sex'] == target_sex] if valid_spouses: p_match['spouse'] = valid_spouses if p_match: matches[index] = p_match return jsonify({"success": True, "matches": matches}) finally: conn.close() @app.route('/manager/add_member', methods=['GET', 'POST']) def add_member(): if 'user_id' not in session: return redirect(url_for('login')) conn = get_db_connection() try: # Check for source_record_id (from GET or POST) source_record_id = request.args.get('record_id') or request.form.get('source_record_id') prefilled_content = None source_oss_url = None if source_record_id: with conn.cursor() as cursor: cursor.execute("SELECT oss_url, ai_content, ai_status FROM genealogy_records WHERE id = %s", (source_record_id,)) rec = cursor.fetchone() if rec: source_oss_url = rec['oss_url'] # Check ai_status (2 = success) if rec['ai_status'] == 2 and rec['ai_content']: prefilled_content = rec['ai_content'] if request.method == 'POST': # 处理生日转换为 Unix 时间戳 birthday_str = request.form.get('birthday') birthday_ts = 0 if birthday_str: try: birthday_ts = int(datetime.strptime(birthday_str, '%Y-%m-%d').timestamp()) except ValueError: birthday_ts = 0 # 关系数据 related_mid = request.form.get('related_mid') relation_type = request.form.get('relation_type') sub_relation_type = request.form.get('sub_relation_type', 0) # 年龄校验逻辑 if related_mid and relation_type in ['1', '2']: # 1:父子 2:母子 with conn.cursor() as cursor: cursor.execute("SELECT name, birthday FROM family_member_info WHERE id = %s", (related_mid,)) parent = cursor.fetchone() if parent and parent['birthday'] > 0 and birthday_ts > 0: if birthday_ts < parent['birthday']: error_msg = f"数据冲突：成员年龄不能比其父亲/母亲（{parent['name']}）大，请检查并修正出生日期。" flash(error_msg) # Re-fetch data for rendering cursor.execute("SELECT id, name FROM family_member_info ORDER BY name") all_members = cursor.fetchall() cursor.execute("SELECT * FROM genealogy_records ORDER BY page_number ASC") images = cursor.fetchall() if request.headers.get('X-Requested-With') == 'XMLHttpRequest' or request.is_json: return jsonify({ "success": False, "message": error_msg }), 400 return render_template('add_member.html', all_members=all_members, images=images, prefilled_content=prefilled_content, source_oss_url=source_oss_url, source_record_id=source_record_id) # 获取表单数据 data = { 'name': request.form['name'], 'simplified_name': request.form.get('simplified_name'), 'former_name': request.form.get('former_name'), 'childhood_name': request.form.get('childhood_name'), 'name_word': request.form.get('name_word'), 'name_word_generation': request.form.get('name_word_generation'), 'name_title': request.form.get('name_title'), 'sex': request.form['sex'], 'birthday': birthday_ts, 'is_pass_away': request.form.get('is_pass_away', 0), 'marital_status': request.form.get('marital_status', 0), 'birth_place': request.form.get('birth_place'), 'branch_family_hall': request.form.get('branch_family_hall'), 'cluster_place': request.form.get('cluster_place'), 'nation': request.form.get('nation'), 'residential_address': request.form.get('residential_address'), 'phone': request.form.get('phone'), 'mail': request.form.get('mail'), 'wechat_account': request.form.get('wechat_account'), 'id_number': request.form.get('id_number'), 'occupation': request.form.get('occupation'), 'educational': request.form.get('educational'), 'blood_type': request.form.get('blood_type'), 'religion': request.form.get('religion'), 'hobbies': request.form.get('hobbies'), 'personal_achievements': request.form.get('personal_achievements'), 'family_rank': request.form.get('family_rank'), 'tags': request.form.get('tags'), 'notes': request.form.get('notes'), 'source_record_id': request.form.get('source_record_id') or None # Save source record ID } # ... (rest of logic) ... with conn.cursor() as cursor: fields = ", ".join(data.keys()) placeholders = ", ".join(["%s"] * len(data)) sql = f"INSERT INTO family_member_info ({fields}) VALUES ({placeholders})" cursor.execute(sql, list(data.values())) member_id = cursor.lastrowid # 录入关系 if related_mid and relation_type: rel_type = int(relation_type) parent_mid = int(related_mid) child_mid = member_id gen_diff = 1 if rel_type in [1, 2] else 0 sql_relation = """ INSERT INTO family_relation_info (parent_mid, child_mid, relation_type, sub_relation_type, source_mid, generation_diff) VALUES (%s, %s, %s, %s, %s, %s) """ cursor.execute(sql_relation, (parent_mid, child_mid, rel_type, sub_relation_type, member_id, gen_diff)) # Update AI Record Status if applicable source_record_id = data.get('source_record_id') source_index = request.form.get('source_index') if source_record_id and source_index and source_index.isdigit(): try: idx = int(source_index) cursor.execute("SELECT ai_content FROM genealogy_records WHERE id = %s FOR UPDATE", (source_record_id,)) rec = cursor.fetchone() if rec and rec['ai_content']: import json content = json.loads(rec['ai_content']) # Ensure content is a list (it might be a dict if single object, though we try to normalize) if isinstance(content, dict): content = [content] if isinstance(content, list): updated = False if 0 <= idx < len(content): if not content[idx].get('is_imported'): # Avoid redundant updates content[idx]['is_imported'] = True content[idx]['imported_member_id'] = member_id updated = True if updated: new_content = json.dumps(content, ensure_ascii=False) cursor.execute("UPDATE genealogy_records SET ai_content = %s WHERE id = %s", (new_content, source_record_id)) except Exception as e: print(f"Error updating AI content status: {e}") conn.commit() if request.headers.get('X-Requested-With') == 'XMLHttpRequest' or request.is_json: return jsonify({"success": True, "message": "成员录入成功", "member_id": member_id}) flash('成员录入成功') return redirect(url_for('members')) with conn.cursor() as cursor: cursor.execute("SELECT id, name FROM family_member_info ORDER BY name") all_members = cursor.fetchall() cursor.execute("SELECT * FROM genealogy_records ORDER BY page_number ASC") images = cursor.fetchall() except Exception as e: flash(f'发生错误: {e}') all_members = [] images = [] finally: conn.close() return render_template('add_member.html', all_members=all_members, images=images, prefilled_content=prefilled_content, source_oss_url=source_oss_url, source_record_id=source_record_id) @app.route('/manager/edit_member/', methods=['GET', 'POST']) def edit_member(member_id): if 'user_id' not in session: return redirect(url_for('login')) conn = get_db_connection() try: if request.method == 'POST': birthday_str = request.form.get('birthday') birthday_ts = 0 if birthday_str: try: birthday_ts = int(datetime.strptime(birthday_str, '%Y-%m-%d').timestamp()) except ValueError: birthday_ts = 0 # 关系数据 related_mid = request.form.get('related_mid') relation_type = request.form.get('relation_type') sub_relation_type = request.form.get('sub_relation_type', 0) # 年龄校验逻辑 if related_mid and relation_type in ['1', '2']: with conn.cursor() as cursor: cursor.execute("SELECT name, birthday FROM family_member_info WHERE id = %s", (related_mid,)) parent = cursor.fetchone() if parent and parent['birthday'] > 0 and birthday_ts > 0: if birthday_ts < parent['birthday']: flash(f"数据冲突：成员年龄不能比其父亲/母亲（{parent['name']}）大，请检查并修正出生日期。") # 重新加载编辑页所需数据 cursor.execute("SELECT * FROM family_member_info WHERE id = %s", (member_id,)) member = cursor.fetchone() member['birthday_date'] = birthday_str # 保持用户输入 cursor.execute("SELECT id, name FROM family_member_info WHERE id != %s ORDER BY name", (member_id,)) all_members = cursor.fetchall() cursor.execute("SELECT * FROM genealogy_records ORDER BY page_number ASC") images = cursor.fetchall() if request.headers.get('X-Requested-With') == 'XMLHttpRequest' or request.is_json: return jsonify({ "success": False, "message": f"数据冲突：成员年龄不能比其父亲/母亲（{parent['name']}）大，请检查并修正出生日期。" }), 400 return render_template('add_member.html', member=member, images=images, all_members=all_members) data = { 'name': request.form['name'], 'simplified_name': request.form.get('simplified_name'), 'former_name': request.form.get('former_name'), 'childhood_name': request.form.get('childhood_name'), 'name_word': request.form.get('name_word'), 'name_word_generation': request.form.get('name_word_generation'), 'name_title': request.form.get('name_title'), 'sex': request.form['sex'], 'birthday': birthday_ts, 'is_pass_away': request.form.get('is_pass_away', 0), 'marital_status': request.form.get('marital_status', 0), 'birth_place': request.form.get('birth_place'), 'branch_family_hall': request.form.get('branch_family_hall'), 'cluster_place': request.form.get('cluster_place'), 'nation': request.form.get('nation'), 'residential_address': request.form.get('residential_address'), 'phone': request.form.get('phone'), 'mail': request.form.get('mail'), 'wechat_account': request.form.get('wechat_account'), 'id_number': request.form.get('id_number'), 'occupation': request.form.get('occupation'), 'educational': request.form.get('educational'), 'blood_type': request.form.get('blood_type'), 'religion': request.form.get('religion'), 'hobbies': request.form.get('hobbies'), 'personal_achievements': request.form.get('personal_achievements'), 'family_rank': request.form.get('family_rank'), 'tags': request.form.get('tags'), 'notes': request.form.get('notes'), 'source_record_id': request.form.get('source_record_id') or None } # 关系数据 related_mid = request.form.get('related_mid') relation_type = request.form.get('relation_type') sub_relation_type = request.form.get('sub_relation_type', 0) with conn.cursor() as cursor: update_parts = [f"{k} = %s" for k in data.keys()] sql = f"UPDATE family_member_info SET {', '.join(update_parts)} WHERE id = %s" cursor.execute(sql, list(data.values()) + [member_id]) # 更新关系 if related_mid and relation_type: rel_type = int(relation_type) cursor.execute("DELETE FROM family_relation_info WHERE source_mid = %s", (member_id,)) parent_mid = int(related_mid) child_mid = member_id gen_diff = 1 if rel_type in [1, 2] else 0 sql_relation = """ INSERT INTO family_relation_info (parent_mid, child_mid, relation_type, sub_relation_type, source_mid, generation_diff) VALUES (%s, %s, %s, %s, %s, %s) """ cursor.execute(sql_relation, (parent_mid, child_mid, rel_type, sub_relation_type, member_id, gen_diff)) # Update AI Record Status if applicable source_record_id = data.get('source_record_id') source_index = request.form.get('source_index') if source_record_id and source_index and source_index.isdigit(): try: idx = int(source_index) cursor.execute("SELECT ai_content FROM genealogy_records WHERE id = %s FOR UPDATE", (source_record_id,)) rec = cursor.fetchone() if rec and rec['ai_content']: import json content = json.loads(rec['ai_content']) if isinstance(content, dict): content = [content] if isinstance(content, list): updated = False if 0 <= idx < len(content): if not content[idx].get('is_imported'): # Avoid redundant updates content[idx]['is_imported'] = True content[idx]['imported_member_id'] = member_id updated = True if updated: new_content = json.dumps(content, ensure_ascii=False) cursor.execute("UPDATE genealogy_records SET ai_content = %s WHERE id = %s", (new_content, source_record_id)) except Exception as e: print(f"Error updating AI content status: {e}") conn.commit() if request.headers.get('X-Requested-With') == 'XMLHttpRequest' or request.is_json: return jsonify({"success": True, "message": "成员信息更新成功"}) flash('成员信息更新成功') return redirect(url_for('members')) with conn.cursor() as cursor: cursor.execute("SELECT * FROM family_member_info WHERE id = %s", (member_id,)) member = cursor.fetchone() if not member: flash('成员不存在') return redirect(url_for('members')) # 格式化日期供显示 if member.get('birthday'): member['birthday_date'] = format_timestamp(member['birthday']) # 获取现有关系 cursor.execute("SELECT * FROM family_relation_info WHERE source_mid = %s LIMIT 1", (member_id,)) current_relation = cursor.fetchone() cursor.execute("SELECT id, name FROM family_member_info WHERE id != %s ORDER BY name", (member_id,)) all_members = cursor.fetchall() cursor.execute("SELECT * FROM genealogy_records ORDER BY page_number ASC") images = cursor.fetchall() finally: conn.close() return render_template('add_member.html', member=member, images=images, all_members=all_members, current_relation=current_relation) @app.route('/manager/member_detail/') def member_detail(member_id): if 'user_id' not in session: return redirect(url_for('login')) conn = get_db_connection() try: with conn.cursor() as cursor: # Join with genealogy_records to get source image info sql = """ SELECT m.*, r.oss_url as source_image_url, r.page_number as source_page FROM family_member_info m LEFT JOIN genealogy_records r ON m.source_record_id = r.id WHERE m.id = %s """ cursor.execute(sql, (member_id,)) member = cursor.fetchone() if not member: flash('成员不存在') return redirect(url_for('members')) member['birthday_str'] = format_timestamp(member.get('birthday')) # 获取关系 cursor.execute(""" SELECT m.id, m.name, r.relation_type FROM family_relation_info r JOIN family_member_info m ON r.parent_mid = m.id WHERE r.child_mid = %s """, (member_id,)) parents = cursor.fetchall() cursor.execute(""" SELECT m.id, m.name, r.relation_type FROM family_relation_info r JOIN family_member_info m ON r.child_mid = m.id WHERE r.parent_mid = %s """, (member_id,)) children = cursor.fetchall() finally: conn.close() return render_template('member_detail.html', member=member, parents=parents, children=children) @app.route('/manager/delete_member/', methods=['POST']) def delete_member(member_id): if 'user_id' not in session: return jsonify({"success": False, "message": "Unauthorized"}), 401 conn = get_db_connection() try: with conn.cursor() as cursor: # 1. 删除关系表中关联该成员的所有记录 cursor.execute("DELETE FROM family_relation_info WHERE parent_mid = %s OR child_mid = %s OR source_mid = %s", (member_id, member_id, member_id)) # 2. 删除成员本身 cursor.execute("DELETE FROM family_member_info WHERE id = %s", (member_id,)) conn.commit() flash('成员及其关系已成功删除') return redirect(url_for('members')) except Exception as e: conn.rollback() flash(f'删除失败: {e}') return redirect(url_for('members')) finally: conn.close() @app.route('/manager/login', methods=['GET', 'POST']) def login(): if request.method == 'POST': username = request.form['username'] password = request.form['password'] try: conn = get_db_connection() try: with conn.cursor() as cursor: cursor.execute("SELECT * FROM users WHERE username=%s AND password=%s", (username, password)) user = cursor.fetchone() if user: session['user_id'] = user['id'] session['username'] = user['username'] return redirect(url_for('index')) else: flash('用户名或密码错误') finally: conn.close() except Exception as e: flash(f'数据库连接错误: {str(e)}') print(f'Login error: {str(e)}') return render_template('login.html') @app.route('/manager/logout') def logout(): session.clear() return redirect(url_for('login')) import requests import json import re @app.route('/manager/api/recognize_image', methods=['POST']) def recognize_image(): if 'user_id' not in session: return jsonify({"success": False, "message": "Unauthorized"}), 401 data = request.json image_url = data.get('image_url') if not image_url: return jsonify({"success": False, "message": "No image URL provided"}), 400 api_key = "a1800657-9212-4afe-9b7c-b49f015c54d3" api_url = "https://ark.cn-beijing.volces.com/api/v3/responses" prompt = """ 请分析这张家谱图片，提取其中关于人物的信息。 请务必将繁体字转换为简体字（original_name 字段除外）。 特别注意：'name' 字段必须是纯简体中文，不能包含繁体字（例如：'學'应转换为'学'，'劉'应转换为'刘'，'萬'应转换为'万'）。 请提取以下字段（如果存在）： - original_name: 原始姓名（严格保持图片上的繁体字，不做任何修改或转换） - name: 简体姓名（必须转换为简体中文，去除不需要的敬称） - sex: 性别（男/女） - birthday: 出生日期（尝试转换为YYYY-MM-DD格式，如果无法确定年份可只填月日） - father_name: 父亲姓名 - spouse_name: 配偶姓名 - generation: 第几世/代数 - name_word: 字辈（例如名字为“学勤公”，“学”为字辈；提取名字中的字辈信息） - education: 学历/功名 - title: 官职/称号 请严格以JSON列表格式返回，不要包含Markdown代码块标记（如 ```json ... ```），直接返回JSON数组。 如果包含多个人物，请都提取出来。 """ payload = { "model": "doubao-seed-1-8-251228", "stream": True, "input": [ { "role": "user", "content": [ { "type": "input_image", "image_url": image_url }, { "type": "input_text", "text": prompt } ] } ] } headers = { "Authorization": f"Bearer {api_key}", "Content-Type": "application/json" } def generate(): yield "正在连接 AI 服务...\n" try: # 使用 stream=True, timeout=120 # 增加 verify=False 以防 SSL 问题（开发环境） # 增加 proxies=None 以防本地代理干扰 with requests.post( api_url, json=payload, headers=headers, stream=True, timeout=1200, verify=False, proxies={"http": None, "https": None} ) as r: if r.status_code != 200: yield f"Error: API returned status code {r.status_code}. Response: {r.text}" return yield "连接成功，正在等待 AI 响应...\n" full_reasoning = "" json_started = False for line in r.iter_lines(): if line: line_str = line.decode('utf-8') if line_str.startswith('data: '): json_str = line_str[6:] if json_str.strip() == '[DONE]': break try: chunk = json.loads(json_str) # 处理 standard OpenAI choices format (content) if 'choices' in chunk and len(chunk['choices']) > 0: delta = chunk['choices'][0].get('delta', {}) if 'content' in delta: if not json_started: yield "|||JSON_START|||" json_started = True yield delta['content'] # 处理 standard OpenAI choices format (reasoning_content) if any if 'reasoning_content' in delta: yield f"\n[推理]: {delta['reasoning_content']}" # 处理 Doubao/Volcano specific formats # Type: response.reasoning_summary_text.delta if chunk.get('type') == 'response.reasoning_summary_text.delta': if 'delta' in chunk: yield chunk['delta'] # Type: response.text.delta if chunk.get('type') == 'response.text.delta': if 'delta' in chunk: if not json_started: yield "|||JSON_START|||" json_started = True yield chunk['delta'] # Type: response.output_item.added (May contain initial content or status) # Type: response.reasoning_summary_part.added except Exception as e: print(f"Chunk parse error: {e}") else: # 尝试直接解析非 data: 开头的行 try: chunk = json.loads(line_str) if 'choices' in chunk and len(chunk['choices']) > 0: content = chunk['choices'][0]['message']['content'] yield content except: pass except Exception as e: yield f"\n[Error: {str(e)}]" return Response(stream_with_context(generate()), mimetype='text/plain') @app.route('/manager/api/start_analysis/', methods=['POST']) def start_analysis(record_id): if 'user_id' not in session: return jsonify({"success": False, "message": "Unauthorized"}), 401 conn = get_db_connection() try: with conn.cursor() as cursor: # Check if record exists cursor.execute("SELECT oss_url, ai_status FROM genealogy_records WHERE id = %s", (record_id,)) record = cursor.fetchone() if not record: return jsonify({"success": False, "message": "Record not found"}), 404 # Update status to processing (1) cursor.execute("UPDATE genealogy_records SET ai_status = 1 WHERE id = %s", (record_id,)) conn.commit() # Start background task threading.Thread(target=process_ai_task, args=(record_id, record['oss_url'])).start() return jsonify({"success": True, "message": "Analysis started"}) except Exception as e: return jsonify({"success": False, "message": str(e)}), 500 finally: conn.close() @app.route('/manager/upload', methods=['GET', 'POST']) def upload(): if 'user_id' not in session: return redirect(url_for('login')) # 获取建议页码 (当前最大页码 + 1) conn = get_db_connection() suggested_page = 1 try: with conn.cursor() as cursor: cursor.execute("SELECT MAX(page_number) as max_p FROM genealogy_records") result = cursor.fetchone() if result and result['max_p']: suggested_page = result['max_p'] + 1 finally: conn.close() if request.method == 'POST': if 'file' not in request.files: flash('未选择文件') return redirect(request.url) file = request.files['file'] if file.filename == '': flash('未选择文件') return redirect(request.url) # 允许用户在上传时直接指定页码，或者由 OCR 识别 manual_page = request.form.get('manual_page') if file: filename = secure_filename(file.filename) file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename) file.save(file_path) # 1. 尝试 OCR 提取页码 page_num = extract_page_number(file_path) # 如果 OCR 没识别到，且用户也没手动输入，则跳转到“补录页码”页面 if not page_num and not manual_page: # 先把文件传到 OSS，拿到 URL，方便后续补录 oss_url = upload_to_oss(file_path) if oss_url: # 暂时存入 session 或者跳转带参数 return render_template('confirm_page.html', filename=filename, oss_url=oss_url, suggested_page=suggested_page) else: flash('上传 OSS 失败') return redirect(url_for('upload')) # 使用识别到的或手动输入的页码 final_page = manual_page if manual_page else page_num oss_url = upload_to_oss(file_path) if oss_url: conn = get_db_connection() try: with conn.cursor() as cursor: sql = "INSERT INTO genealogy_records (file_name, oss_url, page_number, ai_status) VALUES (%s, %s, %s, 1)" cursor.execute(sql, (filename, oss_url, final_page)) record_id = cursor.lastrowid conn.commit() # Start AI Task threading.Thread(target=process_ai_task, args=(record_id, oss_url)).start() flash(f'上传成功！页码：{final_page}，已加入解析队列') except Exception as e: flash(f'数据库错误：{e}') finally: conn.close() return redirect(url_for('index')) else: flash('上传 OSS 失败') return render_template('upload.html', suggested_page=suggested_page) @app.route('/manager/save_upload', methods=['POST']) def save_upload(): if 'user_id' not in session: return redirect(url_for('login')) filename = request.form.get('filename') oss_url = request.form.get('oss_url') page_number = request.form.get('page_number') if not oss_url or not page_number: flash('页码不能为空') return redirect(url_for('upload')) conn = get_db_connection() try: with conn.cursor() as cursor: sql = "INSERT INTO genealogy_records (file_name, oss_url, page_number, ai_status) VALUES (%s, %s, %s, 1)" cursor.execute(sql, (filename, oss_url, page_number)) record_id = cursor.lastrowid conn.commit() # Start AI Task threading.Thread(target=process_ai_task, args=(record_id, oss_url)).start() flash(f'记录已保存，页码：{page_number}，已加入解析队列') except Exception as e: flash(f'保存失败: {e}') finally: conn.close() return redirect(url_for('index')) @app.route('/manager/delete_upload/', methods=['POST']) def delete_upload(record_id): if 'user_id' not in session: return jsonify({"success": False, "message": "Unauthorized"}), 401 conn = get_db_connection() try: with conn.cursor() as cursor: # 删除记录 cursor.execute("DELETE FROM genealogy_records WHERE id = %s", (record_id,)) conn.commit() flash('文件记录已成功删除') return redirect(url_for('index')) except Exception as e: conn.rollback() flash(f'删除失败: {e}') return redirect(url_for('index')) finally: conn.close() if __name__ == '__main__': app.run(debug=True, port=5001)