import pymysql
import pandas as pd
import numpy as np
import json
import os
DB_HOST = 'localhost'
DB_USER = 'root'
DB_PASSWORD = '585418'
DB_NAME = 'platform'
DB_PORT = 3306
def get_user_from_db(username, password):
connection = None
try:
# 建立数据库连接
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
port=DB_PORT,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor # 返回字典格式
)
with connection.cursor() as cursor:
# 查询用户名密码匹配的用户
sql = "SELECT id, num_id, emp_id, username, password, role, dept FROM `user` WHERE username = %s AND password = %s"
cursor.execute(sql, (username, password))
result = cursor.fetchone()
return result # 返回匹配的用户 dict 或 None
except Exception as e:
print(f"数据库错误: {e}")
return None
finally:
if connection:
connection.close()
def add_activity_to_db(title, description, date, tags, images):
"""
将活动插入数据库
:param title: 标题
:param description: 描述
:param date: 日期时间字符串 (YYYY-MM-DD HH:MM:SS)
:param tags: 标签列表 -> 转为 JSON
:param images: 图片路径列表 -> 转为 JSON
:return: 成功返回新记录 ID，失败返回 None
"""
connection = None
try:
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
port=DB_PORT,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
with connection.cursor() as cursor:
# 构造 SQL 插入语句
sql = """
INSERT INTO activities (title, description, date, tags, images)
VALUES (%s, %s, %s, %s, %s)
"""
# 转为 JSON 字符串
tags_json = json.dumps(tags, ensure_ascii=False)
images_json = json.dumps(images, ensure_ascii=False)
cursor.execute(sql, (title, description, date, tags_json, images_json))
connection.commit()
# 返回最后插入的 ID
return cursor.lastrowid
except Exception as e:
print(f"插入活动失败: {e}")
if connection:
connection.rollback()
return None
finally:
if connection:
connection.close()
def get_all_activities_from_db():
"""
从数据库获取所有活动，按时间倒序排列
:return: 活动列表（字典格式），每个包含解析后的 tags 和 images 列表
"""
connection = None
try:
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
port=DB_PORT,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
with connection.cursor() as cursor:
sql = "SELECT * FROM activities ORDER BY date DESC"
cursor.execute(sql)
results = cursor.fetchall()
# 处理 JSON 字段反序列化
for row in results:
try:
row['tags'] = json.loads(row['tags']) if row['tags'] else []
except (json.JSONDecodeError, TypeError):
row['tags'] = []
try:
row['images'] = json.loads(row['images']) if row['images'] else []
except (json.JSONDecodeError, TypeError):
row['images'] = []
# 确保 id 是整数
row['id'] = int(row['id'])
return results
except Exception as e:
print(f"查询活动失败: {e}")
return []
finally:
if connection:
connection.close()
def delete_activity_from_db(activity_id):
"""
根据 ID 删除活动
:param activity_id: 活动 ID
:return: 成功返回 True，否则 False
"""
connection = None
try:
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
port=DB_PORT,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
with connection.cursor() as cursor:
# 先查出图片路径用于后续删除文件
sql_select = "SELECT images FROM activities WHERE id = %s"
cursor.execute(sql_select, (activity_id,))
result = cursor.fetchone()
if not result:
return {"success": False, "message": "活动不存在"}
# 删除数据库记录
sql_delete = "DELETE FROM activities WHERE id = %s"
cursor.execute(sql_delete, (activity_id,))
connection.commit()
# 解析图片路径并返回（供 Flask 删除本地文件）
try:
image_paths = json.loads(result['images']) if result['images'] else []
except:
image_paths = []
return {"success": True, "image_paths": image_paths}
except Exception as e:
print(f"删除活动失败: {e}")
if connection:
connection.rollback()
return {"success": False, "message": "数据库错误"}
finally:
if connection:
connection.close()
def bpdata_to_sql():
main_folder = 'bpdata'
column_mapping = {
'工号': 'emp_id',
'姓名': 'name',
'部门': 'dept',
'身高(cm)': 'stature',
'体重(kg)': 'weight',
'BMI': 'bmi',
'收缩压(mmHg)': 'systolic',
'舒张压(mmHg)': 'diastolic',
'心率(次/分钟)': 'heart_rate',
'测量时间': 'measuring_time'
}
required_physical_columns_target = ['stature', 'weight', 'bmi', 'systolic', 'diastolic', 'heart_rate']
connection = None
inserted_count = 0
try:
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
port=DB_PORT,
charset='utf8mb4'
)
for root, dirs, files in os.walk(main_folder):
for file in files:
if file.lower().endswith(('.xlsx', '.xls')):
file_path = os.path.join(root, file)
try:
print(f"正在处理: {file_path}")
df = pd.read_excel(file_path, sheet_name=0, dtype=str)
available_cols = [col for col in column_mapping.keys() if col in df.columns]
if len(available_cols) == 0:
print(f"文件无可用列，跳过: {file}")
continue
df = df[available_cols]
df.rename(columns=column_mapping, inplace=True)
phys_cols_in_df = [col for col in required_physical_columns_target if col in df.columns]
if len(phys_cols_in_df) == 0:
print("没有找到任何生理指标列，跳过文件")
continue
for col in phys_cols_in_df:
df[col] = pd.to_numeric(df[col], errors='coerce')
df[col].replace([np.inf, -np.inf], np.nan, inplace=True)
df['has_valid_phys'] = ~df[phys_cols_in_df].isna().all(axis=1)
df = df[df['has_valid_phys']].drop(columns=['has_valid_phys'])
if df.empty:
print("处理后无有效记录，跳过")
continue
optional_cols = ['name', 'dept', 'measuring_time']
for col in optional_cols:
if col in df.columns and col not in phys_cols_in_df:
df[col] = df[col].astype('string').replace('nan', pd.NA)
if 'measuring_time' in df.columns:
df['measuring_time'] = pd.to_datetime(df['measuring_time'], errors='coerce')
# Step 6: emp_id 必须存在且有效
if 'emp_id' not in df.columns:
print(f"文件缺少 '工号' 列，跳过: {file}")
continue
df['emp_id'] = pd.to_numeric(df['emp_id'], errors='coerce')
df['emp_id'].replace([np.inf, -np.inf], np.nan, inplace=True)
df = df.dropna(subset=['emp_id'])
df['emp_id'] = df['emp_id'].astype('Int64')
def nan_to_none(x):
return None if pd.isna(x) else x
records = []
for _, row in df.iterrows():
record = tuple(nan_to_none(row[col]) for col in [
'emp_id', 'name', 'dept', 'stature', 'weight', 'bmi',
'systolic', 'diastolic', 'heart_rate', 'measuring_time'
])
records.append(record)
if not records:
print("处理后无有效记录，跳过")
continue
with connection.cursor() as cursor:
sql = """
INSERT INTO bpdata
(emp_id, name, dept, stature, weight, bmi, systolic, diastolic, heart_rate, measuring_time)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
cursor.executemany(sql, records)
inserted_count += len(records)
print(f"成功导入 {len(records)} 条记录: {file}")
except Exception as e:
print(f"处理文件失败 {file_path}: {str(e)}")
connection.commit()
print(f"\n总共成功导入 {inserted_count} 条血压数据")
return True
except Exception as e:
print(f"批量导入过程出错: {e}")
if connection:
connection.rollback()
return False
finally:
if connection:
connection.close()
if __name__ == "__main__":
bpdata_to_sql()from datetime import datetime
import time
from flask import Flask, request, jsonify, session, render_template, redirect, url_for
from perc import all_data_json, merged_df_json, final_json
from bp import blood1
from pe import frontend_data, pedata, disease_data
from hrc import hr_data
import os
from werkzeug.utils import secure_filename
from functools import wraps
from mysql import get_user_from_db, add_activity_to_db, get_all_activities_from_db, delete_activity_from_db
from ids_email import send_mail_soap
from bmi_warning_email import bmi_email
app = Flask(__name__)
app.secret_key = 'your_secret_key_here'
UPLOAD_FOLDER = 'static/uploads'
os.makedirs(UPLOAD_FOLDER, exist_ok=True)
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif'}
def allowed_file(filename):
return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS
def login_required(f):
@wraps(f)
def decorated_function(*args, **kwargs):
if 'user' not in session:
session['login_required'] = True # 设置标志
return redirect(url_for('home'))
return f(*args, **kwargs)
return decorated_function
def get_current_user():
return session.get('user')
@app.route('/')
def home():
return render_template('index.html')
@app.route('/blood')
@login_required
def blood():
return render_template('blood.html')
@app.route('/bmi')
@login_required
def bmi():
return render_template('bmi.html')
@app.route('/physical')
@login_required
def physical():
return render_template(
'physical.html',
analysis_options=pedata.get("analysis_options", []),
employees_by_analysis=pedata.get("employees_by_analysis", {})
)
@app.route('/clf')
@login_required
def clf():
return render_template('clf.html')
@app.route('/general')
@login_required
def general():
return render_template('general.html')
@app.route('/hpa')
@login_required
def hpa():
return render_template('hpa.html')
@app.route('/perfile')
@login_required
def per():
return render_template('personal_file.html')
@app.route('/send-bmi-warning', methods=['POST'])
def bmi_warning():
try:
data = request.get_json()
if not data:
return jsonify({'success': False, 'message': '请求体为空'}), 400
employees = data.get('employees')
if not employees:
return jsonify({'success': False, 'message': '缺少 employees 数据'}), 400
if not isinstance(employees, list):
return jsonify({'success': False, 'message': 'employees 必须是数组'}), 400
# print("\n" + "=" * 60)
# print("收到 BMI 健康提醒请求")
# print("=" * 60)
results = []
success_count = 0
for emp in employees:
# 发送邮件
mail_result = bmi_email(emp)
# 添加员工信息到结果
mail_result['employee_id'] = emp.get('id', '')
mail_result['name'] = emp.get('name', '未知')
if mail_result.get('success'):
success_count += 1
results.append(mail_result)
# print(f"共处理 {len(employees)} 名员工，成功发送 {success_count} 封邮件")
# print("=" * 60)
return jsonify({
'success': True,
'sentCount': success_count,
'total': len(employees),
'results': results,
'message': f'成功发送 {success_count} 封邮件（共 {len(employees)} 名员工）'
})
except Exception as e:
print(f"服务器错误: {e}")
return jsonify({
'success': False,
'message': '内部错误',
'error': str(e)
}), 500
# @app.route('/send_email')
# @login_required
# def send():
# return render_template('send_email.html')
@app.route('/data/first', methods=['GET'])
def all_data():
return all_data_json
@app.route('/data/second', methods=['GET'])
def second_data():
return frontend_data
@app.route('/data/blood1', methods=['GET'])
def blood1_data():
return blood1
@app.route('/data/pedata', methods=['GET'])
def pe_data():
return pedata
@app.route('/data/risk', methods=['GET'])
def risk_data():
return merged_df_json
@app.route('/data/risk_dept', methods=['GET'])
def risk_dept():
return final_json
@app.route('/data/hrdata', methods=['GET'])
def hrdata():
return hr_data
@app.route('/data/diseasedata', methods=['GET'])
def disease():
return disease_data
# activities = [
# {
# 'title': '健康讲座',
# 'description': '健康讲座',
# 'date': '2025-07-29T14:30',
# 'tags': ['讲座'],
# 'image': ''
# },
# {
# 'title': '社区义诊',
# 'description': '社区义诊',
# 'date': '2025-08-06T10:00',
# 'tags': ['义诊'],
# 'image': ''
# },
# {
# 'title': '免费体检',
# 'description': '免费体检',
# 'date': '2025-08-06T09:00',
# 'tags': ['其他相关'],
# 'image': ''
# },
# {
# 'title': '健康饮食讲座',
# 'description': '健康饮食讲座',
# 'date': '2025-09-06T09:00',
# 'tags': ['讲座'],
# 'image': ''
# },
# {
# 'title': '图片',
# 'description': '健康饮食讲座',
# 'date': '2025-09-11T09:00',
# 'tags': ['讲座'],
# 'image': 'static/uploads/2025-09-10_164723.png'
# }
# ]
@app.route('/add_activity', methods=['POST'])
def add_activity():
try:
title = request.form.get('title', '').strip()
description = request.form.get('description', '').strip()
date = request.form.get('date', '').strip()
tags = request.form.getlist('tags') # 多个 checkbox
# ✅ 获取多个图片文件
files = request.files.getlist('images') # 注意字段名必须匹配前端的 name="images"
image_urls = []
for file in files:
if file and allowed_file(file.filename):
filename = secure_filename(f"{int(time.time())}_{file.filename}")
filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
file.save(filepath)
# 统一使用正斜杠
image_url = f'/static/uploads/{filename}'
image_urls.append(image_url)
# 写入数据库
new_id = add_activity_to_db(title, description, date, tags, image_urls)
if not new_id:
return jsonify({'error': '保存到数据库失败'}), 500
return jsonify({
'id': new_id,
'title': title,
'description': description,
'date': date,
'tags': tags,
'images': image_urls # 返回数组
}), 201
except Exception as e:
print(f"Error in /add_activity: {e}")
return jsonify({'error': str(e)}), 500
@app.route('/get_activities', methods=['GET'])
def get_activities():
try:
activities = get_all_activities_from_db()
result = []
for activity in activities:
item = dict(activity)
if 'date' in item and item['date']:
dt = item['date']
if isinstance(dt, datetime):
item['date'] = dt.isoformat()
elif isinstance(dt, str):
pass
else:
item['date'] = None
else:
item['date'] = None
# # 处理 tags：从逗号分隔字符串转为数组
# if 'tags' in item and isinstance(item['tags'], str):
# item['tags'] = [tag.strip() for tag in item['tags'].split(',') if tag.strip()]
# else:
# item['tags'] = []
#
# # 处理 images 字段（如果存在）
# if 'images' in item and isinstance(item['images'], str):
# try:
# import json
# item['images'] = json.loads(item['images']) # 如果存的是 JSON 字符串
# except:
# item['images'] = [item['images']] if item['images'] else []
# elif 'images' not in item:
# item['images'] = []
result.append(item)
return jsonify(result)
except Exception as e:
print(f"Error in /get_activities: {e}")
return jsonify({'error': str(e)}), 500
@app.route('/delete_activity/<int:activity_id>', methods=['DELETE'])
def delete_activity(activity_id):
user = get_current_user()
if not user or user['role'] != 'admin' or user['dept'] != 'all':
return jsonify({'success': False, 'message': '权限不足'}), 403
try:
result = delete_activity_from_db(activity_id)
if not result['success']:
return jsonify(result), 404
# 删除本地图片文件
for img_path in result.get('image_paths', []):
real_path = '.' + img_path # 转为相对路径
if os.path.exists(real_path):
try:
os.remove(real_path)
except Exception as e:
print(f"无法删除图片文件 {real_path}: {e}")
return jsonify({'success': True, 'message': '删除成功'})
except Exception as e:
print(f"删除失败: {e}")
return jsonify({'success': False, 'message': '服务器错误'}), 500
# users = [
# {"id": 'all', "username": "admin", "password": "123456", "role": "admin", "dept": "all"},
# {"id": '22527715', "username": "user1", "password": "123456", "role": "admin", "dept": "HR"},
# {"id": 3, "username": "user2", "password": "123456", "role": "admin", "dept": "S17"},
# {"id": 4, "username": "user3", "password": "123456", "role": "user", "dept": "S17"},
# {"id": "22527715", "username": "user4", "password": "123456", "role": "user", "dept": "HR"},
# ]
# 登录接口
@app.route('/login', methods=['POST'])
def login():
data = request.get_json()
username = data.get('username')
password = data.get('password')
if not username or not password:
return jsonify({"success": False, "message": "用户名或密码不能为空"}), 400
# 从数据库查询用户
user = get_user_from_db(username, password)
if user:
session['user'] = {
'id': user['num_id'],
'username': user['username'],
'role': user['role'],
'dept': user['dept']
}
return jsonify({"success": True, "message": "登录成功"})
else:
return jsonify({"success": False, "message": "用户名或密码错误"}), 401
@app.route('/user')
def get_user():
if 'user' in session:
user = session['user']
return jsonify({
'user': {
'id': user.get('id'),
'username': user.get('username'),
'role': user.get('role', 'user'),
'dept': user.get('dept', 'all')
},
'is_authenticated': True
})
else:
show_login_toast = session.pop('login_required', False)
return jsonify({
'user': None,
'is_authenticated': False,
'show_login_toast': show_login_toast
})
@app.route('/logout', methods=['POST'])
def logout():
session.pop('user', None)
return jsonify({"success": True, "message": "退出成功"})
@app.route('/send_email', methods=['POST'])
def handle_send_email():
"""接收前端请求并调用邮件函数"""
try:
data = request.get_json()
print(data)
sjr = data.get('sjr', '').strip()
csr = data.get('csr', '').strip()
tbbt = data.get('tbbt', '').strip()
tbnr = data.get('tbnr', '').strip()
fjr = data.get('fjr', 'IDS').strip()
if not sjr or not tbbt or not tbnr:
return jsonify({"success": False, "message": "缺少必要参数"}), 400
result = send_mail_soap(sjr=sjr, csr=csr, tbbt=tbbt, tbnr=tbnr, fjr=fjr)
return jsonify(result)
except Exception as e:
return jsonify({"success": False, "message": f"服务器错误: {str(e)}"}), 500
if __name__ == '__main__':
app.run(debug=True)如何实现bpdata的自动增量更新
最新发布
本文详细介绍了Log4j的日志配置方法，包括设置根logger级别为debug、开启debug模式、配置控制台输出适配器及输出格式、以及针对HTTP客户端的日志级别调整等。对于希望深入了解Log4j配置细节的读者来说，本文提供了实用的指导。
扫一扫
1806
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
