|
|
import os
|
|
|
import uuid
|
|
|
import pandas as pd
|
|
|
import re
|
|
|
|
|
|
from flask import render_template, request, send_file
|
|
|
from common.logger import system_logger
|
|
|
|
|
|
|
|
|
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
|
|
|
|
|
|
UPLOAD_FOLDER = os.path.join(BASE_DIR, "uploads")
|
|
|
OUTPUT_FOLDER = os.path.join(BASE_DIR, "output")
|
|
|
|
|
|
os.makedirs(UPLOAD_FOLDER, exist_ok=True)
|
|
|
os.makedirs(OUTPUT_FOLDER, exist_ok=True)
|
|
|
|
|
|
|
|
|
# =========================
|
|
|
# 1. 智能读取（v2.3 FINAL）
|
|
|
# =========================
|
|
|
def smart_read(file_path):
|
|
|
|
|
|
ext = os.path.splitext(file_path)[-1].lower()
|
|
|
|
|
|
# =========================
|
|
|
# 1️⃣ CSV / Excel：强制 header
|
|
|
# =========================
|
|
|
if ext in [".csv", ".xlsx", ".xls"]:
|
|
|
|
|
|
try:
|
|
|
if ext == ".csv":
|
|
|
df = pd.read_csv(file_path, dtype=str)
|
|
|
else:
|
|
|
df = pd.read_excel(file_path, dtype=str)
|
|
|
|
|
|
except Exception as e:
|
|
|
raise Exception(f"读取失败: {e}")
|
|
|
|
|
|
has_header = True
|
|
|
|
|
|
# 清洗
|
|
|
df.columns = [str(c).strip() for c in df.columns]
|
|
|
|
|
|
for c in df.columns:
|
|
|
df[c] = df[c].astype(str).str.strip()
|
|
|
|
|
|
system_logger.info(
|
|
|
f"[SmartRead-v2.3] file={file_path} type={ext} header=FORCED shape={df.shape}"
|
|
|
)
|
|
|
|
|
|
return df, has_header
|
|
|
|
|
|
# =========================
|
|
|
# 2️⃣ TXT / 非结构文件：智能识别
|
|
|
# =========================
|
|
|
seps = [",", "|", "\t", r"\s+"]
|
|
|
|
|
|
df = None
|
|
|
|
|
|
for sep in seps:
|
|
|
try:
|
|
|
df = pd.read_csv(
|
|
|
file_path,
|
|
|
sep=sep,
|
|
|
engine="python",
|
|
|
header=None,
|
|
|
dtype=str
|
|
|
)
|
|
|
|
|
|
if df is not None and df.shape[1] >= 2:
|
|
|
break
|
|
|
|
|
|
except:
|
|
|
continue
|
|
|
|
|
|
if df is None:
|
|
|
raise Exception("无法解析文件")
|
|
|
|
|
|
# =========================
|
|
|
# 3️⃣ header 判断（仅 TXT 使用）
|
|
|
# =========================
|
|
|
def is_header_row(row):
|
|
|
|
|
|
row = [str(x).strip() for x in row]
|
|
|
|
|
|
keywords = ["id", "uid", "name", "code", "工号", "编号", "time", "date"]
|
|
|
|
|
|
text_score = 0
|
|
|
structure_score = 0
|
|
|
numeric_penalty = 0
|
|
|
|
|
|
for v in row:
|
|
|
|
|
|
v_low = v.lower()
|
|
|
|
|
|
if any(k in v_low for k in keywords):
|
|
|
text_score += 2
|
|
|
|
|
|
if not re.fullmatch(r"\d+", v):
|
|
|
structure_score += 1
|
|
|
|
|
|
if len(v) <= 20:
|
|
|
structure_score += 1
|
|
|
|
|
|
if re.fullmatch(r"\d{4,20}", v):
|
|
|
numeric_penalty += 1
|
|
|
|
|
|
score = text_score + structure_score - numeric_penalty
|
|
|
|
|
|
return score >= 3
|
|
|
|
|
|
has_header = False
|
|
|
|
|
|
if len(df) >= 2:
|
|
|
|
|
|
row0 = df.iloc[0].tolist()
|
|
|
row1 = df.iloc[1].tolist()
|
|
|
|
|
|
score0 = is_header_row(row0)
|
|
|
score1 = is_header_row(row1)
|
|
|
|
|
|
has_header = score0 and not score1
|
|
|
|
|
|
# =========================
|
|
|
# 4️⃣ 应用 schema
|
|
|
# =========================
|
|
|
if has_header:
|
|
|
df.columns = [str(x).strip() for x in df.iloc[0].tolist()]
|
|
|
df = df.iloc[1:].reset_index(drop=True)
|
|
|
else:
|
|
|
df.columns = [f"c{i}" for i in range(df.shape[1])]
|
|
|
|
|
|
# =========================
|
|
|
# 5️⃣ 清洗
|
|
|
# =========================
|
|
|
df.columns = [str(c).strip() for c in df.columns]
|
|
|
|
|
|
for c in df.columns:
|
|
|
df[c] = df[c].astype(str).str.strip()
|
|
|
|
|
|
system_logger.info(
|
|
|
f"[SmartRead-v2.3] file={file_path} type=txt header={has_header} shape={df.shape}"
|
|
|
)
|
|
|
|
|
|
return df, has_header
|
|
|
|
|
|
|
|
|
# =========================
|
|
|
# 2. 特征评分（不变）
|
|
|
# =========================
|
|
|
def score_column(series):
|
|
|
|
|
|
s = series.dropna().astype(str).head(30)
|
|
|
|
|
|
if len(s) == 0:
|
|
|
return 0
|
|
|
|
|
|
score = 0
|
|
|
|
|
|
for v in s:
|
|
|
|
|
|
if re.fullmatch(r"\d{6,12}", v):
|
|
|
score += 3
|
|
|
|
|
|
if v.isdigit():
|
|
|
score += 2
|
|
|
|
|
|
if v.startswith("0"):
|
|
|
score += 1
|
|
|
|
|
|
if len(v) in [6, 8, 10, 12]:
|
|
|
score += 1
|
|
|
|
|
|
return score / len(s)
|
|
|
|
|
|
|
|
|
# =========================
|
|
|
# 3. 自动 key（不变）
|
|
|
# =========================
|
|
|
def detect_key(df):
|
|
|
|
|
|
candidates = ["uid", "id", "user_id", "emp_id", "工号", "编号"]
|
|
|
|
|
|
for col in df.columns:
|
|
|
for c in candidates:
|
|
|
if c in str(col).lower():
|
|
|
return col
|
|
|
|
|
|
best_col = None
|
|
|
best_score = 0
|
|
|
|
|
|
for col in df.columns:
|
|
|
score = score_column(df[col])
|
|
|
|
|
|
if score > best_score:
|
|
|
best_score = score
|
|
|
best_col = col
|
|
|
|
|
|
if best_score > 0.4:
|
|
|
return best_col
|
|
|
|
|
|
return None
|
|
|
|
|
|
|
|
|
# =========================
|
|
|
# 4. join（不变）
|
|
|
# =========================
|
|
|
def auto_join(df1, df2):
|
|
|
|
|
|
key1 = detect_key(df1)
|
|
|
key2 = detect_key(df2)
|
|
|
|
|
|
if not key1 or not key2:
|
|
|
raise Exception("无法识别匹配字段")
|
|
|
|
|
|
result = pd.merge(
|
|
|
df1,
|
|
|
df2,
|
|
|
left_on=key1,
|
|
|
right_on=key2,
|
|
|
how="left",
|
|
|
indicator=True
|
|
|
)
|
|
|
|
|
|
result["match_status"] = result["_merge"].map({
|
|
|
"both": "matched",
|
|
|
"left_only": "unmatched",
|
|
|
"right_only": "orphan"
|
|
|
}).astype(str)
|
|
|
|
|
|
result.drop(columns=["_merge"], inplace=True)
|
|
|
|
|
|
matched_count = int((result["match_status"] == "matched").sum())
|
|
|
unmatched_count = int((result["match_status"] == "unmatched").sum())
|
|
|
total = len(result)
|
|
|
|
|
|
match_rate = round(matched_count / total * 100, 2) if total else 0
|
|
|
|
|
|
stats = {
|
|
|
"matched_count": matched_count,
|
|
|
"unmatched_count": unmatched_count,
|
|
|
"match_rate": match_rate
|
|
|
}
|
|
|
|
|
|
return result, key1, key2, stats
|
|
|
|
|
|
|
|
|
# =========================
|
|
|
# 5. 页面（不变）
|
|
|
# =========================
|
|
|
def smart_data_page_v2():
|
|
|
|
|
|
preview = None
|
|
|
download_file = None
|
|
|
message = None
|
|
|
match_info = ""
|
|
|
|
|
|
matched_count = 0
|
|
|
unmatched_count = 0
|
|
|
match_rate = 0
|
|
|
|
|
|
if request.method == "POST":
|
|
|
|
|
|
try:
|
|
|
file1 = request.files.get("file1")
|
|
|
file2 = request.files.get("file2")
|
|
|
|
|
|
p1 = os.path.join(UPLOAD_FOLDER, f"{uuid.uuid4()}_{file1.filename}")
|
|
|
p2 = os.path.join(UPLOAD_FOLDER, f"{uuid.uuid4()}_{file2.filename}")
|
|
|
|
|
|
file1.save(p1)
|
|
|
file2.save(p2)
|
|
|
|
|
|
df1, h1 = smart_read(p1)
|
|
|
df2, h2 = smart_read(p2)
|
|
|
|
|
|
result, k1, k2, stats = auto_join(df1, df2)
|
|
|
|
|
|
matched_count = stats["matched_count"]
|
|
|
unmatched_count = stats["unmatched_count"]
|
|
|
match_rate = stats["match_rate"]
|
|
|
|
|
|
match_info = (
|
|
|
f"匹配字段：{k1} ↔ {k2} ｜ "
|
|
|
f"匹配率：{match_rate}% ｜ "
|
|
|
f"来源：{'有表头' if h1 else '无表头'} / {'有表头' if h2 else '无表头'}"
|
|
|
)
|
|
|
|
|
|
out_name = f"v2_{uuid.uuid4().hex}.xlsx"
|
|
|
out_path = os.path.join(OUTPUT_FOLDER, out_name)
|
|
|
|
|
|
result.to_excel(out_path, index=False)
|
|
|
|
|
|
preview = result.head(100).fillna("").to_html(
|
|
|
classes="table table-dark table-hover",
|
|
|
index=False
|
|
|
)
|
|
|
|
|
|
download_file = out_name
|
|
|
message = f"匹配成功，共 {len(result)} 条记录"
|
|
|
|
|
|
system_logger.info(
|
|
|
f"[SmartETL-v2.3] {match_info} "
|
|
|
f"matched={matched_count}, unmatched={unmatched_count}"
|
|
|
)
|
|
|
|
|
|
except Exception as e:
|
|
|
message = str(e)
|
|
|
system_logger.exception("[SmartETL-v2 ERROR]")
|
|
|
|
|
|
return render_template(
|
|
|
"smart_data_v2.html",
|
|
|
preview=preview,
|
|
|
download_file=download_file,
|
|
|
message=message,
|
|
|
match_info=match_info,
|
|
|
matched_count=matched_count,
|
|
|
unmatched_count=unmatched_count,
|
|
|
match_rate=match_rate
|
|
|
)
|
|
|
|
|
|
|
|
|
# =========================
|
|
|
# download
|
|
|
# =========================
|
|
|
def smart_download_v2(filename):
|
|
|
|
|
|
path = os.path.join(OUTPUT_FOLDER, filename)
|
|
|
|
|
|
system_logger.info(f"[SmartDownload-v2] {filename}")
|
|
|
|
|
|
return send_file(path, as_attachment=True) |
