835 lines
40 KiB
Python
Executable File
835 lines
40 KiB
Python
Executable File
#!/usr/bin/env python3
|
||
"""
|
||
AeroTrack LAN Sync Server (AeroTrack 局域网多设备同步服务端)
|
||
- 零第三方依赖,纯 Python 标准库 (Python 3.8+)
|
||
- 自动管理 SQLite 数据库,具备完整的物理药瓶生命周期 (Bottle Entity) 与事件账本溯源
|
||
- 支持高并发多线程与完整 CORS
|
||
- 一体化托管 Flutter Web 端应用与服务端监控看板
|
||
"""
|
||
|
||
import sys
|
||
import os
|
||
import json
|
||
import sqlite3
|
||
import datetime
|
||
from http.server import HTTPServer, SimpleHTTPRequestHandler
|
||
from socketserver import ThreadingMixIn
|
||
|
||
DB_FILE = os.environ.get(
|
||
'AEROTRACK_DB',
|
||
os.path.join(os.path.dirname(os.path.abspath(__file__)), 'aerotrack.db')
|
||
)
|
||
|
||
WEB_DIR = os.environ.get(
|
||
'AEROTRACK_WEB_DIR',
|
||
os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'build', 'web')
|
||
)
|
||
|
||
def get_db():
|
||
conn = sqlite3.connect(DB_FILE, check_same_thread=False)
|
||
conn.row_factory = sqlite3.Row
|
||
return conn
|
||
|
||
def init_db():
|
||
conn = get_db()
|
||
with conn:
|
||
# 1. 药品基础方案配置表
|
||
conn.execute('''
|
||
CREATE TABLE IF NOT EXISTS canisters (
|
||
id TEXT PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
total_doses INTEGER NOT NULL,
|
||
remaining_doses INTEGER NOT NULL,
|
||
opened_at TEXT,
|
||
is_enabled INTEGER DEFAULT 1,
|
||
puffs_per_session INTEGER DEFAULT 2,
|
||
inhalation_seconds INTEGER DEFAULT 15,
|
||
is_steroid INTEGER DEFAULT 1,
|
||
sort_order INTEGER DEFAULT 1,
|
||
note TEXT,
|
||
updated_at TEXT
|
||
)
|
||
''')
|
||
|
||
# 2. 物理药瓶全生命周期实体表 (支持换新瓶档案、多端独立计数)
|
||
conn.execute('''
|
||
CREATE TABLE IF NOT EXISTS bottles (
|
||
id TEXT PRIMARY KEY,
|
||
canister_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
sequence_number INTEGER DEFAULT 1,
|
||
total_doses INTEGER NOT NULL,
|
||
remaining_doses INTEGER NOT NULL,
|
||
opened_at TEXT NOT NULL,
|
||
finished_at TEXT,
|
||
is_active INTEGER DEFAULT 1,
|
||
opened_by_device TEXT,
|
||
updated_at TEXT NOT NULL
|
||
)
|
||
''')
|
||
|
||
# 3. 吸入流水日志表 (UUID 主键,天然支持并发防重与软删除墓碑)
|
||
conn.execute('''
|
||
CREATE TABLE IF NOT EXISTS dose_records (
|
||
id TEXT PRIMARY KEY,
|
||
start_time TEXT NOT NULL,
|
||
end_time TEXT NOT NULL,
|
||
flixotide_puffs INTEGER DEFAULT 0,
|
||
ventolin_puffs INTEGER DEFAULT 0,
|
||
flixotide_bottle_id TEXT,
|
||
ventolin_bottle_id TEXT,
|
||
rinsed_mouth INTEGER DEFAULT 1,
|
||
washed_face INTEGER DEFAULT 1,
|
||
is_prime INTEGER DEFAULT 0,
|
||
is_deleted INTEGER DEFAULT 0,
|
||
note TEXT,
|
||
operator_name TEXT,
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT
|
||
)
|
||
''')
|
||
|
||
# 检查并平滑升级 dose_records 缺失的列
|
||
cursor = conn.execute("PRAGMA table_info(dose_records)")
|
||
existing_cols = {row['name'] for row in cursor.fetchall()}
|
||
if 'flixotide_bottle_id' not in existing_cols:
|
||
conn.execute('ALTER TABLE dose_records ADD COLUMN flixotide_bottle_id TEXT')
|
||
if 'ventolin_bottle_id' not in existing_cols:
|
||
conn.execute('ALTER TABLE dose_records ADD COLUMN ventolin_bottle_id TEXT')
|
||
if 'is_deleted' not in existing_cols:
|
||
conn.execute('ALTER TABLE dose_records ADD COLUMN is_deleted INTEGER DEFAULT 0')
|
||
if 'updated_at' not in existing_cols:
|
||
conn.execute('ALTER TABLE dose_records ADD COLUMN updated_at TEXT')
|
||
|
||
# 4. 局域网同步设备列表
|
||
conn.execute('''
|
||
CREATE TABLE IF NOT EXISTS devices (
|
||
device_id TEXT PRIMARY KEY,
|
||
device_name TEXT NOT NULL,
|
||
last_seen TEXT NOT NULL,
|
||
ip_address TEXT
|
||
)
|
||
''')
|
||
|
||
# 5. 若 bottles 为空,初始化默认第 1 瓶并平滑绑定历史流水
|
||
bottle_count = conn.execute('SELECT COUNT(*) as c FROM bottles').fetchone()['c']
|
||
if bottle_count == 0:
|
||
now_iso = datetime.datetime.now(datetime.timezone.utc).isoformat()
|
||
earliest_time = '2020-01-01T00:00:00Z'
|
||
conn.execute('''
|
||
INSERT INTO bottles (id, canister_id, name, sequence_number, total_doses, remaining_doses, opened_at, is_active, opened_by_device, updated_at)
|
||
VALUES ('flix_b1_initial', 'flixotide_primary', '辅舒酮 (丙酸氟替卡松)', 1, 60, 60, ?, 1, '系统初始化', ?)
|
||
''', (earliest_time, now_iso))
|
||
conn.execute('''
|
||
INSERT INTO bottles (id, canister_id, name, sequence_number, total_doses, remaining_doses, opened_at, is_active, opened_by_device, updated_at)
|
||
VALUES ('vent_b1_initial', 'ventolin_primary', '万托林 (硫酸沙丁胺醇)', 1, 200, 200, ?, 1, '系统初始化', ?)
|
||
''', (earliest_time, now_iso))
|
||
|
||
conn.execute("UPDATE dose_records SET flixotide_bottle_id = 'flix_b1_initial' WHERE flixotide_puffs > 0 AND flixotide_bottle_id IS NULL")
|
||
conn.execute("UPDATE dose_records SET ventolin_bottle_id = 'vent_b1_initial' WHERE ventolin_puffs > 0 AND ventolin_bottle_id IS NULL")
|
||
conn.execute("UPDATE dose_records SET updated_at = ? WHERE updated_at IS NULL", (now_iso,))
|
||
|
||
# 6. 若 canisters 为空,初始化基础配置
|
||
canister_count = conn.execute('SELECT COUNT(*) as c FROM canisters').fetchone()['c']
|
||
if canister_count == 0:
|
||
now_iso = datetime.datetime.now(datetime.timezone.utc).isoformat()
|
||
conn.execute('''
|
||
INSERT INTO canisters (id, name, total_doses, remaining_doses, opened_at, is_enabled, puffs_per_session, inhalation_seconds, is_steroid, sort_order, note, updated_at)
|
||
VALUES ('ventolin_primary', '万托林 (硫酸沙丁胺醇)', 200, 200, '2020-01-01T00:00:00Z', 1, 2, 15, 0, 1, '支气管舒张剂', ?)
|
||
''', (now_iso,))
|
||
conn.execute('''
|
||
INSERT INTO canisters (id, name, total_doses, remaining_doses, opened_at, is_enabled, puffs_per_session, inhalation_seconds, is_steroid, sort_order, note, updated_at)
|
||
VALUES ('flixotide_primary', '辅舒酮 (丙酸氟替卡松)', 60, 60, '2020-01-01T00:00:00Z', 1, 2, 15, 1, 2, '糖皮质激素', ?)
|
||
''', (now_iso,))
|
||
|
||
# 7. 家庭智能药箱全量管理表
|
||
conn.execute('''
|
||
CREATE TABLE IF NOT EXISTS medicines (
|
||
id TEXT PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
category TEXT NOT NULL,
|
||
spec TEXT,
|
||
storage_location TEXT,
|
||
target_user TEXT,
|
||
barcode TEXT,
|
||
batch_number TEXT,
|
||
production_date TEXT,
|
||
expiry_date TEXT NOT NULL,
|
||
shelf_life_days_after_opening INTEGER,
|
||
is_opened INTEGER DEFAULT 0,
|
||
opened_at TEXT,
|
||
opened_by_device TEXT,
|
||
notes TEXT,
|
||
is_deleted INTEGER DEFAULT 0,
|
||
updated_at TEXT NOT NULL
|
||
)
|
||
''')
|
||
conn.close()
|
||
|
||
class ThreadedHTTPServer(ThreadingMixIn, HTTPServer):
|
||
daemon_threads = True
|
||
|
||
class SyncHandler(SimpleHTTPRequestHandler):
|
||
def __init__(self, *args, **kwargs):
|
||
super().__init__(*args, directory=WEB_DIR if os.path.isdir(WEB_DIR) else None, **kwargs)
|
||
|
||
def log_message(self, format, *args):
|
||
try:
|
||
sys.stderr.write("%s - - [%s] %s\n" %
|
||
(self.address_string(),
|
||
self.log_date_time_string(),
|
||
format % args))
|
||
sys.stderr.flush()
|
||
except Exception:
|
||
pass
|
||
|
||
def _send_cors_headers(self):
|
||
self.send_header('Access-Control-Allow-Origin', '*')
|
||
self.send_header('Access-Control-Allow-Methods', 'GET, POST, OPTIONS')
|
||
self.send_header('Access-Control-Allow-Headers', 'Content-Type, Authorization, X-Requested-With')
|
||
|
||
def end_headers(self):
|
||
self._send_cors_headers()
|
||
super().end_headers()
|
||
|
||
def do_OPTIONS(self):
|
||
self.send_response(200)
|
||
self.end_headers()
|
||
|
||
def _send_json(self, status_code, data):
|
||
payload = json.dumps(data, ensure_ascii=False).encode('utf-8')
|
||
self.send_response(status_code)
|
||
self.send_header('Content-Type', 'application/json; charset=utf-8')
|
||
self.send_header('Content-Length', str(len(payload)))
|
||
self.end_headers()
|
||
self.wfile.write(payload)
|
||
|
||
def do_GET(self):
|
||
# 1. Ping / 连通性测试
|
||
if self.path == '/api/v1/ping':
|
||
self._send_json(200, {
|
||
'status': 'ok',
|
||
'service': 'AeroTrack LAN Sync Server',
|
||
'timestamp': datetime.datetime.now(datetime.timezone.utc).isoformat()
|
||
})
|
||
return
|
||
|
||
# 2. 状态查询
|
||
if self.path == '/api/v1/sync/status':
|
||
conn = get_db()
|
||
total_records = conn.execute('SELECT COUNT(*) as c FROM dose_records WHERE is_deleted = 0').fetchone()['c']
|
||
devices = [dict(r) for r in conn.execute('SELECT * FROM devices ORDER BY last_seen DESC').fetchall()]
|
||
canisters = [dict(r) for r in conn.execute('SELECT * FROM canisters ORDER BY sort_order ASC').fetchall()]
|
||
bottles = [dict(r) for r in conn.execute('SELECT * FROM bottles ORDER BY sequence_number ASC').fetchall()]
|
||
conn.close()
|
||
|
||
self._send_json(200, {
|
||
'status': 'online',
|
||
'total_records': total_records,
|
||
'devices': devices,
|
||
'canisters': canisters,
|
||
'bottles': bottles,
|
||
'server_time': datetime.datetime.now(datetime.timezone.utc).isoformat()
|
||
})
|
||
return
|
||
|
||
# 3. 药品条码/追溯码信息查询接口
|
||
if self.path.startswith('/api/v1/medicine/lookup'):
|
||
from urllib.parse import urlparse, parse_qs
|
||
query = parse_qs(urlparse(self.path).query)
|
||
code = query.get('code', [''])[0].strip()
|
||
self._handle_medicine_lookup(code)
|
||
return
|
||
|
||
# 4. 监控看板
|
||
if self.path == '/dashboard' or self.path == '/dashboard/':
|
||
self._render_dashboard()
|
||
return
|
||
|
||
# 5. 静态 Web 端(Flutter Web 产物)
|
||
if os.path.isdir(WEB_DIR):
|
||
super().do_GET()
|
||
return
|
||
|
||
# 6. 若未编译 Flutter Web,则默认展示监控面板
|
||
if self.path == '/' or self.path == '/index.html':
|
||
self._render_dashboard()
|
||
return
|
||
|
||
self.send_error(404, "Not Found")
|
||
|
||
def do_POST(self):
|
||
if self.path == '/api/v1/sync':
|
||
content_length = int(self.headers.get('Content-Length', 0))
|
||
body = self.rfile.read(content_length)
|
||
try:
|
||
payload = json.loads(body.decode('utf-8'))
|
||
except Exception as e:
|
||
self._send_json(400, {'error': f'Invalid JSON: {str(e)}'})
|
||
return
|
||
|
||
self._handle_sync(payload)
|
||
return
|
||
|
||
self.send_error(404, "Not Found")
|
||
|
||
def _handle_sync(self, payload):
|
||
device_name = payload.get('device_name', '未知设备')
|
||
device_id = payload.get('device_id', 'unknown')
|
||
client_ip = self.client_address[0]
|
||
now_str = datetime.datetime.now(datetime.timezone.utc).isoformat()
|
||
|
||
conn = get_db()
|
||
with conn:
|
||
# 1. 记录设备心跳
|
||
conn.execute('''
|
||
INSERT INTO devices (device_id, device_name, last_seen, ip_address)
|
||
VALUES (?, ?, ?, ?)
|
||
ON CONFLICT(device_id) DO UPDATE SET
|
||
device_name = excluded.device_name,
|
||
last_seen = excluded.last_seen,
|
||
ip_address = excluded.ip_address
|
||
''', (device_id, device_name, now_str, client_ip))
|
||
|
||
# 2. 接收并合并药瓶实体列表 (Bottles)
|
||
incoming_bottles = payload.get('bottles', [])
|
||
for b in incoming_bottles:
|
||
bid = b.get('id')
|
||
if not bid:
|
||
continue
|
||
cid = b.get('canisterId') or b.get('canister_id', '')
|
||
bname = b.get('name', '')
|
||
seq = b.get('sequenceNumber') or b.get('sequence_number', 1)
|
||
td = b.get('totalDoses') or b.get('total_doses', 60)
|
||
op_at = b.get('openedAt') or b.get('opened_at', now_str)
|
||
fin_at = b.get('finishedAt') or b.get('finished_at')
|
||
is_act = 1 if (b.get('isActive') if b.get('isActive') is not None else b.get('is_active', 1)) else 0
|
||
dev = b.get('openedByDevice') or b.get('opened_by_device', device_name)
|
||
up_at = b.get('updatedAt') or b.get('updated_at', now_str)
|
||
|
||
existing_b = conn.execute('SELECT * FROM bottles WHERE id = ?', (bid,)).fetchone()
|
||
if not existing_b:
|
||
conn.execute('''
|
||
INSERT INTO bottles (id, canister_id, name, sequence_number, total_doses, remaining_doses, opened_at, finished_at, is_active, opened_by_device, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', (bid, cid, bname, seq, td, td, op_at, fin_at, is_act, dev, up_at))
|
||
else:
|
||
if up_at >= (existing_b['updated_at'] or '') or (not is_act and existing_b['is_active']):
|
||
conn.execute('''
|
||
UPDATE bottles SET
|
||
finished_at = COALESCE(?, finished_at),
|
||
is_active = ?,
|
||
updated_at = ?
|
||
WHERE id = ?
|
||
''', (fin_at, is_act, up_at, bid))
|
||
|
||
# 3. 接收客户端推上来的用药记录 (UUID 幂等入库 + 软删除墓碑同步)
|
||
incoming_records = payload.get('records', [])
|
||
for r in incoming_records:
|
||
rec_id = r.get('id')
|
||
if not rec_id:
|
||
continue
|
||
is_del = 1 if r.get('isDeleted') or r.get('is_deleted') else 0
|
||
up_at = r.get('updatedAt') or r.get('updated_at', now_str)
|
||
fb_id = r.get('flixotideBottleId') or r.get('flixotide_bottle_id')
|
||
vb_id = r.get('ventolinBottleId') or r.get('ventolin_bottle_id')
|
||
|
||
existing_r = conn.execute('SELECT * FROM dose_records WHERE id = ?', (rec_id,)).fetchone()
|
||
if not existing_r:
|
||
conn.execute('''
|
||
INSERT INTO dose_records
|
||
(id, start_time, end_time, flixotide_puffs, ventolin_puffs, flixotide_bottle_id, ventolin_bottle_id, rinsed_mouth, washed_face, is_prime, is_deleted, note, operator_name, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', (
|
||
rec_id,
|
||
r.get('startTime', now_str),
|
||
r.get('endTime', now_str),
|
||
r.get('flixotidePuffs', 0),
|
||
r.get('ventolinPuffs', 0),
|
||
fb_id,
|
||
vb_id,
|
||
1 if r.get('rinsedMouth', True) else 0,
|
||
1 if r.get('washedFace', True) else 0,
|
||
1 if r.get('isPrime', False) else 0,
|
||
is_del,
|
||
r.get('note', ''),
|
||
device_name,
|
||
now_str,
|
||
up_at
|
||
))
|
||
else:
|
||
if up_at >= (existing_r['updated_at'] or ''):
|
||
conn.execute('''
|
||
UPDATE dose_records SET
|
||
is_deleted = ?,
|
||
flixotide_bottle_id = COALESCE(?, flixotide_bottle_id),
|
||
ventolin_bottle_id = COALESCE(?, ventolin_bottle_id),
|
||
updated_at = ?
|
||
WHERE id = ?
|
||
''', (is_del, fb_id, vb_id, up_at, rec_id))
|
||
|
||
# 4. 接收客户端方案配置 (Canisters 元数据)
|
||
incoming_canisters = payload.get('canisters', [])
|
||
for c in incoming_canisters:
|
||
cid = c.get('id')
|
||
if not cid:
|
||
continue
|
||
conn.execute('''
|
||
INSERT INTO canisters
|
||
(id, name, total_doses, remaining_doses, opened_at, is_enabled, puffs_per_session, inhalation_seconds, is_steroid, sort_order, note, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(id) DO UPDATE SET
|
||
name = excluded.name,
|
||
is_enabled = excluded.is_enabled,
|
||
puffs_per_session = excluded.puffs_per_session,
|
||
inhalation_seconds = excluded.inhalation_seconds,
|
||
sort_order = excluded.sort_order,
|
||
note = excluded.note,
|
||
updated_at = excluded.updated_at
|
||
''', (
|
||
cid,
|
||
c.get('name', ''),
|
||
c.get('totalDoses', 60),
|
||
c.get('remainingDoses', 60),
|
||
c.get('openedAt', now_str),
|
||
1 if c.get('isEnabled', True) else 0,
|
||
c.get('puffsPerSession', 2),
|
||
c.get('inhalationSeconds', 15),
|
||
1 if c.get('isSteroid', True) else 0,
|
||
c.get('sortOrder', 1),
|
||
c.get('note', ''),
|
||
now_str
|
||
))
|
||
|
||
# 4.5. 接收客户端家庭药箱数据 (Medicines)
|
||
incoming_medicines = payload.get('medicines', [])
|
||
for m in incoming_medicines:
|
||
mid = m.get('id')
|
||
if not mid:
|
||
continue
|
||
up_at = m.get('updatedAt', now_str)
|
||
is_del = 1 if m.get('isDeleted', False) else 0
|
||
is_open = 1 if m.get('isOpened', False) else 0
|
||
|
||
existing_m = conn.execute('SELECT * FROM medicines WHERE id = ?', (mid,)).fetchone()
|
||
if not existing_m:
|
||
conn.execute('''
|
||
INSERT INTO medicines
|
||
(id, name, category, spec, storage_location, target_user, barcode, batch_number, production_date, expiry_date, shelf_life_days_after_opening, is_opened, opened_at, opened_by_device, notes, is_deleted, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
''', (
|
||
mid,
|
||
m.get('name', '未命名药品'),
|
||
m.get('category', 'other'),
|
||
m.get('spec', ''),
|
||
m.get('storageLocation', '常温药箱'),
|
||
m.get('targetUser', '全家'),
|
||
m.get('barcode'),
|
||
m.get('batchNumber'),
|
||
m.get('productionDate'),
|
||
m.get('expiryDate', now_str),
|
||
m.get('shelfLifeDaysAfterOpening'),
|
||
is_open,
|
||
m.get('openedAt'),
|
||
m.get('openedByDevice'),
|
||
m.get('notes', ''),
|
||
is_del,
|
||
up_at
|
||
))
|
||
else:
|
||
if up_at >= (existing_m['updated_at'] or ''):
|
||
conn.execute('''
|
||
UPDATE medicines SET
|
||
name = ?,
|
||
category = ?,
|
||
spec = ?,
|
||
storage_location = ?,
|
||
target_user = ?,
|
||
barcode = ?,
|
||
batch_number = ?,
|
||
production_date = ?,
|
||
expiry_date = ?,
|
||
shelf_life_days_after_opening = ?,
|
||
is_opened = ?,
|
||
opened_at = ?,
|
||
opened_by_device = ?,
|
||
notes = ?,
|
||
is_deleted = ?,
|
||
updated_at = ?
|
||
WHERE id = ?
|
||
''', (
|
||
m.get('name', existing_m['name']),
|
||
m.get('category', existing_m['category']),
|
||
m.get('spec', existing_m['spec']),
|
||
m.get('storageLocation', existing_m['storage_location']),
|
||
m.get('targetUser', existing_m['target_user']),
|
||
m.get('barcode', existing_m['barcode']),
|
||
m.get('batchNumber', existing_m['batch_number']),
|
||
m.get('productionDate', existing_m['production_date']),
|
||
m.get('expiryDate', existing_m['expiry_date']),
|
||
m.get('shelfLifeDaysAfterOpening', existing_m['shelf_life_days_after_opening']),
|
||
is_open,
|
||
m.get('openedAt', existing_m['opened_at']),
|
||
m.get('openedByDevice', existing_m['opened_by_device']),
|
||
m.get('notes', existing_m['notes']),
|
||
is_del,
|
||
up_at,
|
||
mid
|
||
))
|
||
|
||
# 5. 依据物理药瓶实体 (Bottle) 与流水绑定关系,严格确定性重新核算余量
|
||
for b in conn.execute('SELECT * FROM bottles').fetchall():
|
||
bid = b['id']
|
||
cid = b['canister_id']
|
||
td = b['total_doses']
|
||
is_steroid = 1 if 'flix' in cid else 0
|
||
|
||
if is_steroid:
|
||
res = conn.execute('''
|
||
SELECT SUM(flixotide_puffs) as s FROM dose_records
|
||
WHERE is_deleted = 0 AND (flixotide_bottle_id = ? OR (flixotide_bottle_id IS NULL AND start_time >= ?))
|
||
''', (bid, b['opened_at'])).fetchone()
|
||
else:
|
||
res = conn.execute('''
|
||
SELECT SUM(ventolin_puffs) as s FROM dose_records
|
||
WHERE is_deleted = 0 AND (ventolin_bottle_id = ? OR (ventolin_bottle_id IS NULL AND start_time >= ?))
|
||
''', (bid, b['opened_at'])).fetchone()
|
||
|
||
consumed = (res['s'] or 0) if res else 0
|
||
rem = max(0, td - consumed)
|
||
conn.execute('UPDATE bottles SET remaining_doses = ? WHERE id = ?', (rem, bid))
|
||
|
||
# 若是当前在用活跃瓶,同步确保并更新 canisters 方案余量
|
||
if b['is_active']:
|
||
conn.execute('''
|
||
INSERT INTO canisters (id, name, total_doses, remaining_doses, opened_at, is_enabled, puffs_per_session, inhalation_seconds, is_steroid, sort_order, note, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, 1, 2, 15, ?, 1, '', ?)
|
||
ON CONFLICT(id) DO UPDATE SET remaining_doses = excluded.remaining_doses, opened_at = excluded.opened_at
|
||
''', (cid, b['name'], td, rem, b['opened_at'], is_steroid, now_str))
|
||
|
||
# 6. 汇总全量数据响应客户端
|
||
all_records = []
|
||
for row in conn.execute('SELECT * FROM dose_records ORDER BY start_time DESC LIMIT 300'):
|
||
all_records.append({
|
||
'id': row['id'],
|
||
'startTime': row['start_time'],
|
||
'endTime': row['end_time'],
|
||
'flixotidePuffs': row['flixotide_puffs'],
|
||
'ventolinPuffs': row['ventolin_puffs'],
|
||
'flixotideBottleId': row['flixotide_bottle_id'],
|
||
'ventolinBottleId': row['ventolin_bottle_id'],
|
||
'rinsedMouth': bool(row['rinsed_mouth']),
|
||
'washedFace': bool(row['washed_face']),
|
||
'isPrime': bool(row['is_prime']),
|
||
'isDeleted': bool(row['is_deleted']),
|
||
'note': row['note'],
|
||
'operatorName': row['operator_name'],
|
||
'updatedAt': row['updated_at']
|
||
})
|
||
|
||
all_bottles = []
|
||
for row in conn.execute('SELECT * FROM bottles ORDER BY sequence_number ASC'):
|
||
all_bottles.append({
|
||
'id': row['id'],
|
||
'canisterId': row['canister_id'],
|
||
'name': row['name'],
|
||
'sequenceNumber': row['sequence_number'],
|
||
'totalDoses': row['total_doses'],
|
||
'remainingDoses': row['remaining_doses'],
|
||
'openedAt': row['opened_at'],
|
||
'finishedAt': row['finished_at'],
|
||
'isActive': bool(row['is_active']),
|
||
'openedByDevice': row['opened_by_device'],
|
||
'updatedAt': row['updated_at']
|
||
})
|
||
|
||
all_canisters = []
|
||
for row in conn.execute('SELECT * FROM canisters ORDER BY sort_order ASC'):
|
||
all_canisters.append({
|
||
'id': row['id'],
|
||
'name': row['name'],
|
||
'totalDoses': row['total_doses'],
|
||
'remainingDoses': row['remaining_doses'],
|
||
'openedAt': row['opened_at'],
|
||
'isEnabled': bool(row['is_enabled']),
|
||
'puffsPerSession': row['puffs_per_session'],
|
||
'inhalationSeconds': row['inhalation_seconds'],
|
||
'isSteroid': bool(row['is_steroid']),
|
||
'sortOrder': row['sort_order'],
|
||
'note': row['note']
|
||
})
|
||
|
||
all_medicines = []
|
||
for row in conn.execute('SELECT * FROM medicines ORDER BY expiry_date ASC').fetchall():
|
||
all_medicines.append({
|
||
'id': row['id'],
|
||
'name': row['name'],
|
||
'category': row['category'],
|
||
'spec': row['spec'] or '',
|
||
'storageLocation': row['storage_location'] or '常温药箱',
|
||
'targetUser': row['target_user'] or '全家',
|
||
'barcode': row['barcode'],
|
||
'batchNumber': row['batch_number'],
|
||
'productionDate': row['production_date'],
|
||
'expiryDate': row['expiry_date'],
|
||
'shelfLifeDaysAfterOpening': row['shelf_life_days_after_opening'],
|
||
'isOpened': bool(row['is_opened']),
|
||
'openedAt': row['opened_at'],
|
||
'openedByDevice': row['opened_by_device'],
|
||
'notes': row['notes'] or '',
|
||
'isDeleted': bool(row['is_deleted']),
|
||
'updatedAt': row['updated_at']
|
||
})
|
||
|
||
conn.close()
|
||
|
||
self._send_json(200, {
|
||
'success': True,
|
||
'server_time': now_str,
|
||
'bottles': all_bottles,
|
||
'records': all_records,
|
||
'canisters': all_canisters,
|
||
'medicines': all_medicines
|
||
})
|
||
|
||
def _handle_medicine_lookup(self, code):
|
||
catalog = {
|
||
'6921147600010': {'name': '辅舒酮 (丙酸氟替卡松吸入气雾剂)', 'spec': '50μg/喷 × 60喷', 'notes': '吸入用糖皮质激素'},
|
||
'6921147600027': {'name': '万托林 (硫酸沙丁胺醇吸入气雾剂)', 'spec': '100μg/喷 × 200喷', 'notes': '支气管舒张剂'},
|
||
'6925567800018': {'name': '美林 (布洛芬混悬液)', 'spec': '100ml:2g', 'notes': '退热抗炎镇痛'},
|
||
'6925567800025': {'name': '泰诺林 (对乙酰氨基酚混悬滴剂)', 'spec': '15ml:1.5g', 'notes': '儿童退热滴剂'},
|
||
'6936577800015': {'name': '托百士 (妥布霉素滴眼液)', 'spec': '5ml:15mg', 'notes': '抗生素眼药水,开封4周内使用'},
|
||
'6914567800012': {'name': '可乐必妥 (左氧氟沙星滴眼液)', 'spec': '5ml:24.45mg', 'notes': '抗菌滴眼液'},
|
||
'6901234560019': {'name': '百多邦 (莫匹罗星软膏)', 'spec': '5g:0.1g', 'notes': '外用抗菌软膏'},
|
||
}
|
||
if code in catalog:
|
||
item = catalog[code]
|
||
self._send_json(200, {
|
||
'status': 'ok',
|
||
'data': {
|
||
'barcode': code,
|
||
'name': item['name'],
|
||
'spec': item['spec'],
|
||
'notes': item['notes']
|
||
}
|
||
})
|
||
return
|
||
|
||
if len(code) == 20 and code.isdigit():
|
||
self._send_json(200, {
|
||
'status': 'ok',
|
||
'data': {
|
||
'barcode': code,
|
||
'name': f'追溯码药品 ({code[:6]}...)',
|
||
'notes': '由国家药品电子监管码录入'
|
||
}
|
||
})
|
||
return
|
||
|
||
self._send_json(200, {'status': 'not_found', 'message': '未在本地知识库匹配到该药品条码'})
|
||
|
||
def _render_dashboard(self):
|
||
conn = get_db()
|
||
records = conn.execute('SELECT * FROM dose_records WHERE is_deleted = 0 ORDER BY start_time DESC LIMIT 20').fetchall()
|
||
devices = conn.execute('SELECT * FROM devices ORDER BY last_seen DESC').fetchall()
|
||
canisters = conn.execute('SELECT * FROM canisters ORDER BY sort_order ASC').fetchall()
|
||
bottles = conn.execute('SELECT * FROM bottles ORDER BY sequence_number DESC').fetchall()
|
||
medicines = conn.execute('SELECT * FROM medicines WHERE is_deleted = 0 ORDER BY expiry_date ASC').fetchall()
|
||
total_count = conn.execute('SELECT COUNT(*) as c FROM dose_records WHERE is_deleted = 0').fetchone()['c']
|
||
conn.close()
|
||
|
||
med_list = []
|
||
now_dt = datetime.datetime.now(datetime.timezone.utc)
|
||
for m in medicines:
|
||
exp_str = m['expiry_date'] or ''
|
||
try:
|
||
exp_clean = exp_str.replace('Z', '+00:00')
|
||
exp_dt = datetime.datetime.fromisoformat(exp_clean)
|
||
days_left = (exp_dt.date() - now_dt.date()).days
|
||
except Exception:
|
||
days_left = 999
|
||
|
||
if days_left < 0:
|
||
badge = '<span style="background:#fee2e2;color:#b91c1c;padding:2px 8px;border-radius:6px;font-size:11px;font-weight:bold;">已过期</span>'
|
||
elif days_left <= 30:
|
||
badge = f'<span style="background:#fef3c7;color:#b45309;padding:2px 8px;border-radius:6px;font-size:11px;font-weight:bold;">临期 ({days_left}天)</span>'
|
||
elif m['is_opened']:
|
||
badge = '<span style="background:#dbeafe;color:#1d4ed8;padding:2px 8px;border-radius:6px;font-size:11px;font-weight:bold;">已开封使用</span>'
|
||
else:
|
||
badge = '<span style="background:#dcfce7;color:#15803d;padding:2px 8px;border-radius:6px;font-size:11px;font-weight:bold;">未开封正常</span>'
|
||
|
||
open_info = f"已开封 ({m['opened_at'][:10]})" if m['is_opened'] and m['opened_at'] else "未开封"
|
||
loc = m['storage_location'] or '常温药箱'
|
||
spec = f" <span style='color:#64748b;font-size:11px;'>({m['spec']})</span>" if m['spec'] else ""
|
||
med_list.append(
|
||
f"<tr style='border-bottom:1px solid #e2e8f0;'>"
|
||
f"<td style='padding:10px 14px;'><strong>{m['name']}</strong>{spec}</td>"
|
||
f"<td style='padding:10px 14px;'>{loc}</td>"
|
||
f"<td style='padding:10px 14px;color:#2563eb;'>{exp_str[:10]}</td>"
|
||
f"<td style='padding:10px 14px;'>{open_info}</td>"
|
||
f"<td style='padding:10px 14px;'>{badge}</td>"
|
||
f"</tr>"
|
||
)
|
||
med_rows = "".join(med_list) or "<tr><td colspan='5' style='padding:12px;color:#94a3b8;'>暂无家庭药箱数据,可在 App 内扫码或手动添加</td></tr>"
|
||
|
||
canister_list = []
|
||
for c in canisters:
|
||
status_badge = '<span style="color:#059669;font-weight:bold;">启用</span>' if c['is_enabled'] else '<span style="color:#94a3b8;">未启用</span>'
|
||
canister_list.append(
|
||
f"<tr style='border-bottom:1px solid #e2e8f0;'>"
|
||
f"<td style='padding:10px 14px;'><strong>{c['name']}</strong></td>"
|
||
f"<td style='padding:10px 14px;color:#2563eb;font-weight:bold;'>{c['remaining_doses']} / {c['total_doses']} 喷</td>"
|
||
f"<td style='padding:10px 14px;'>{c['puffs_per_session']} 喷</td>"
|
||
f"<td style='padding:10px 14px;'>{status_badge}</td>"
|
||
f"</tr>"
|
||
)
|
||
canister_rows = "".join(canister_list) or "<tr><td colspan='4' style='padding:12px;color:#94a3b8;'>暂无药瓶方案数据</td></tr>"
|
||
|
||
bottle_list = []
|
||
for b in bottles:
|
||
status_badge = '<span style="background:#dcfce7;color:#15803d;padding:2px 8px;border-radius:6px;font-size:11px;font-weight:bold;">在用活跃</span>' if b['is_active'] else '<span style="background:#f1f5f9;color:#64748b;padding:2px 8px;border-radius:6px;font-size:11px;">已归档</span>'
|
||
fin_time = b['finished_at'][:16].replace('T', ' ') if b['finished_at'] else '使用中'
|
||
bottle_list.append(
|
||
f"<tr style='border-bottom:1px solid #e2e8f0;'>"
|
||
f"<td style='padding:10px 14px;'><strong>第 {b['sequence_number']} 瓶</strong> ({b['name'].split(' ')[0]})</td>"
|
||
f"<td style='padding:10px 14px;color:#2563eb;font-weight:bold;'>{b['remaining_doses']} / {b['total_doses']} 喷</td>"
|
||
f"<td style='padding:10px 14px;color:#64748b;'>{b['opened_at'][:16].replace('T', ' ')}</td>"
|
||
f"<td style='padding:10px 14px;color:#64748b;'>{fin_time}</td>"
|
||
f"<td style='padding:10px 14px;'>{status_badge}</td>"
|
||
f"<td style='padding:10px 14px;color:#64748b;font-size:12px;'>{b['opened_by_device']}</td>"
|
||
f"</tr>"
|
||
)
|
||
bottle_rows = "".join(bottle_list) or "<tr><td colspan='6' style='padding:12px;color:#94a3b8;'>暂无药瓶实体数据</td></tr>"
|
||
|
||
device_list = []
|
||
for d in devices:
|
||
device_list.append(
|
||
f"<tr style='border-bottom:1px solid #e2e8f0;'>"
|
||
f"<td style='padding:10px 14px;'><strong>📱 {d['device_name']}</strong></td>"
|
||
f"<td style='padding:10px 14px;font-family:monospace;'>{d['ip_address']}</td>"
|
||
f"<td style='padding:10px 14px;color:#64748b;'>{d['last_seen'][:19].replace('T', ' ')}</td>"
|
||
f"</tr>"
|
||
)
|
||
device_rows = "".join(device_list) or "<tr><td colspan='3' style='padding:12px;color:#94a3b8;'>暂无在线设备连接</td></tr>"
|
||
|
||
record_list = []
|
||
for r in records:
|
||
detail = '试喷 -1' if r['is_prime'] else f"万{r['ventolin_puffs']}喷 + 辅{r['flixotide_puffs']}喷"
|
||
clean_status = '已漱口/洗脸' if r['rinsed_mouth'] and r['washed_face'] else '未清洁'
|
||
op_name = r['operator_name'] or '未知'
|
||
record_list.append(
|
||
f"<tr style='border-bottom:1px solid #f1f5f9;'>"
|
||
f"<td style='padding:8px 12px;color:#1e293b;'>{r['start_time'][:16].replace('T', ' ')}</td>"
|
||
f"<td style='padding:8px 12px;'><strong>{op_name}</strong></td>"
|
||
f"<td style='padding:8px 12px;color:#059669;'>{detail}</td>"
|
||
f"<td style='padding:8px 12px;color:#64748b;'>{clean_status}</td>"
|
||
f"</tr>"
|
||
)
|
||
record_rows = "".join(record_list) or "<tr><td colspan='4' style='padding:12px;color:#94a3b8;'>暂无吸入记录</td></tr>"
|
||
|
||
html = f"""<!DOCTYPE html>
|
||
<html>
|
||
<head>
|
||
<meta charset="utf-8">
|
||
<title>AeroTrack 局域网同步服务器</title>
|
||
<meta name="viewport" content="width=device-width, initial-scale=1">
|
||
<style>
|
||
body {{ font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif; background: #f8fafc; color: #1e293b; margin: 0; padding: 24px; }}
|
||
.container {{ max-width: 960px; margin: 0 auto; }}
|
||
.header {{ background: #2563eb; color: #fff; padding: 24px; border-radius: 16px; margin-bottom: 24px; display: flex; justify-content: space-between; align-items: center; }}
|
||
.card {{ background: #fff; border-radius: 14px; border: 1px solid #e2e8f0; padding: 20px; margin-bottom: 24px; box-shadow: 0 1px 3px rgba(0,0,0,0.05); }}
|
||
h2 {{ margin-top: 0; font-size: 16px; color: #0f172a; border-bottom: 2px solid #f1f5f9; padding-bottom: 10px; }}
|
||
table {{ width: 100%; border-collapse: collapse; font-size: 13px; }}
|
||
th {{ background: #f8fafc; text-align: left; padding: 10px 14px; font-weight: 600; color: #64748b; }}
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<div class="container">
|
||
<div class="header">
|
||
<div>
|
||
<h1 style="margin:0;font-size:22px;">AeroTrack 局域网多设备同步服务器</h1>
|
||
<p style="margin:4px 0 0;font-size:13px;opacity:0.85;">数据安全保存在局域网私有主机,已记录 {total_count} 条有效雾化流水</p>
|
||
</div>
|
||
<div style="display:flex;gap:10px;align-items:center;">
|
||
<a href="/" style="background:#fff;color:#2563eb;text-decoration:none;padding:8px 16px;border-radius:12px;font-size:12px;font-weight:bold;">📱 打开舒雾记 Web 端</a>
|
||
<div style="background:rgba(255,255,255,0.2);padding:8px 16px;border-radius:12px;font-size:12px;font-weight:bold;">
|
||
🟢 服务运行中
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="card">
|
||
<h2>🏠 家庭智能药箱健康与保质期监控 (全家药品有效期与开封管理)</h2>
|
||
<table>
|
||
<thead><tr><th>药品名称</th><th>存放位置</th><th>出厂有效期</th><th>开封追踪</th><th>健康状态</th></tr></thead>
|
||
<tbody>{med_rows}</tbody>
|
||
</table>
|
||
</div>
|
||
|
||
<div class="card">
|
||
<h2>当前在用药瓶余量监控 (各设备统一权威真值)</h2>
|
||
<table>
|
||
<thead><tr><th>药名</th><th>当前剩余喷数</th><th>每次用量</th><th>方案状态</th></tr></thead>
|
||
<tbody>{canister_rows}</tbody>
|
||
</table>
|
||
</div>
|
||
|
||
<div class="card">
|
||
<h2>物理药瓶开封与换瓶档案 (Bottle Lifecycle 实体生命周期)</h2>
|
||
<table>
|
||
<thead><tr><th>瓶次 / 药品</th><th>剩余喷数</th><th>开瓶时间 (UTC)</th><th>用尽归档</th><th>状态</th><th>开瓶设备</th></tr></thead>
|
||
<tbody>{bottle_rows}</tbody>
|
||
</table>
|
||
</div>
|
||
|
||
<div class="card">
|
||
<h2>已连接局域网设备</h2>
|
||
<table>
|
||
<thead><tr><th>设备名称</th><th>IP 地址</th><th>最后同步时间</th></tr></thead>
|
||
<tbody>{device_rows}</tbody>
|
||
</table>
|
||
</div>
|
||
|
||
<div class="card">
|
||
<h2>最近有效雾化时间流水 (前20条)</h2>
|
||
<table>
|
||
<thead><tr><th>时间</th><th>操作人</th><th>喷数明细</th><th>收尾防护</th></tr></thead>
|
||
<tbody>{record_rows}</tbody>
|
||
</table>
|
||
</div>
|
||
</div>
|
||
</body>
|
||
</html>"""
|
||
encoded = html.encode('utf-8')
|
||
self.send_response(200)
|
||
self.send_header('Content-Type', 'text/html; charset=utf-8')
|
||
self.send_header('Content-Length', str(len(encoded)))
|
||
self.end_headers()
|
||
self.wfile.write(encoded)
|
||
|
||
def main():
|
||
port = int(os.environ.get('PORT', 8090))
|
||
if len(sys.argv) > 1:
|
||
try:
|
||
port = int(sys.argv[1])
|
||
except ValueError:
|
||
pass
|
||
|
||
init_db()
|
||
server_address = ('0.0.0.0', port)
|
||
httpd = ThreadedHTTPServer(server_address, SyncHandler)
|
||
print(f"=====================================================")
|
||
print(f"🚀 AeroTrack 局域网同步服务器已启动!")
|
||
print(f"📡 监听端口: {port}")
|
||
print(f"🌐 网页版客户端: http://0.0.0.0:{port}/")
|
||
print(f"📊 监控看板大屏: http://0.0.0.0:{port}/dashboard")
|
||
print(f"💾 数据存储路径: {DB_FILE}")
|
||
print(f"=====================================================")
|
||
try:
|
||
httpd.serve_forever()
|
||
except KeyboardInterrupt:
|
||
print("\n服务已停止")
|
||
|
||
if __name__ == '__main__':
|
||
main()
|