121 lines
4.7 KiB
Python
121 lines
4.7 KiB
Python
#!/usr/bin/env python3
|
||
"""导入遗漏的2026-05-27库存文件（02, 03, 08, 09）"""
|
||
import sys, os, csv, json, subprocess, pymysql
|
||
from datetime import datetime
|
||
|
||
# 从 config.py 导入配置
|
||
import importlib.util
|
||
spec = importlib.util.spec_from_file_location("config", "config.py")
|
||
config = importlib.util.module_from_spec(spec)
|
||
spec.loader.exec_module(config)
|
||
DB_CONFIG = config.DB_CONFIG
|
||
|
||
TMP_DIR = "tmp_csv"
|
||
|
||
def parse_int(val):
|
||
if val is None or val == '' or str(val).upper() == 'NA': return 0
|
||
try: return int(float(val))
|
||
except: return 0
|
||
|
||
def parse_float(val):
|
||
if val is None or val == '' or str(val).upper() == 'NA': return 0.0
|
||
try: return float(val)
|
||
except: return 0.0
|
||
|
||
def parse_datetime(val):
|
||
if not val or val == '' or str(val).upper() == 'NA': return None
|
||
for fmt in ('%Y-%m-%d %H:%M:%S', '%Y-%m-%d'):
|
||
try: return datetime.strptime(val.strip(), fmt)
|
||
except: pass
|
||
return None
|
||
|
||
os.makedirs(TMP_DIR, exist_ok=True)
|
||
|
||
MISSING_FILES = [
|
||
("20260527-wdt-kcmx-02", "2026-05-27", "02", "pYLaezmVNe3rBDrNCGGE2y5bWrMqPxX6"),
|
||
("20260527-wdt-kcmx-03", "2026-05-27", "03", "MyQA2dXW7eKDkAD2C55OaK5lJzlwrZgb"),
|
||
("20260527-wdt-kcmx-08", "2026-05-27", "08", "P0MALyR8klXxPgxAtKKAvB3YW3bzYmDO"),
|
||
("20260527-wdt-kcmx-09", "2026-05-27", "09", "1DKw2zgV2PdeDAe0HPPaevPr8B5r9YAn"),
|
||
]
|
||
|
||
total_imported = 0
|
||
for name, data_date, wh_code, node_id in MISSING_FILES:
|
||
print(f"\n📥 {name} (仓库{wh_code})")
|
||
|
||
# 下载
|
||
result = subprocess.run(
|
||
["dws", "doc", "download", "--node", node_id, "--output", "/dev/stdout", "--format", "json"],
|
||
capture_output=True, text=True, timeout=30
|
||
)
|
||
try:
|
||
url = json.loads(result.stdout)['resourceUrl']
|
||
except:
|
||
print(f" ❌ 获取下载链接失败")
|
||
continue
|
||
|
||
local_path = os.path.join(TMP_DIR, f"{name}.csv")
|
||
subprocess.run(["curl", "-sL", url, "-o", local_path], capture_output=True, timeout=30)
|
||
|
||
if not os.path.exists(local_path) or os.path.getsize(local_path) < 50:
|
||
print(f" ❌ 下载失败")
|
||
continue
|
||
|
||
fsize = os.path.getsize(local_path)
|
||
|
||
# 编码转换（如果需要）
|
||
r = subprocess.run(["file", local_path], capture_output=True, text=True, timeout=5)
|
||
if 'ISO' in r.stdout or '8859' in r.stdout:
|
||
utf8_path = local_path.replace('.csv', '_utf8.csv')
|
||
subprocess.run(["iconv", "-f", "GBK", "-t", "UTF-8", local_path, "-o", utf8_path], capture_output=True, timeout=10)
|
||
os.replace(utf8_path, local_path)
|
||
|
||
# 导入
|
||
conn = pymysql.connect(
|
||
host=DB_CONFIG["host"], port=DB_CONFIG["port"],
|
||
user=DB_CONFIG["user"], password=DB_CONFIG["password"],
|
||
database=DB_CONFIG["database"], charset="utf8mb4",
|
||
cursorclass=pymysql.cursors.DictCursor
|
||
)
|
||
try:
|
||
with open(local_path, 'r', encoding='utf-8-sig') as f:
|
||
rows = list(csv.DictReader(f))
|
||
print(f" 下载完成 ({fsize} bytes), 读取到 {len(rows)} 条")
|
||
|
||
inserted = 0
|
||
with conn.cursor() as cur:
|
||
for row in rows:
|
||
try:
|
||
cur.execute("""
|
||
INSERT INTO inventory_detail
|
||
(data_date, source, warehouse_code, goods_code, product_code, product_name,
|
||
brand, cost_price, available_qty, production_in_transit, purchase_in_transit,
|
||
category1, category2, category3, created_time)
|
||
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
||
""", (
|
||
data_date, 'wdt', wh_code,
|
||
row.get('商家编码', '').strip(),
|
||
row.get('货品编号', '').strip(),
|
||
row.get('货品名称', '').strip(),
|
||
row.get('品牌', '').strip(),
|
||
parse_float(row.get('成本价', 0)),
|
||
parse_int(row.get('可用库存', 0)),
|
||
parse_int(row.get('生产在途量', 0)),
|
||
parse_int(row.get('采购在途', 0)),
|
||
row.get('一级类目', '').strip(),
|
||
row.get('二级类目', '').strip(),
|
||
row.get('三级类目', '').strip(),
|
||
parse_datetime(row.get('单品创建时间', ''))
|
||
))
|
||
inserted += 1
|
||
except Exception as e:
|
||
print(f" 插入失败: {e}")
|
||
conn.commit()
|
||
print(f" ✅ 导入 {inserted} 条")
|
||
total_imported += inserted
|
||
finally:
|
||
conn.close()
|
||
|
||
print(f"\n{'='*40}")
|
||
print(f"补导入完成！共 {total_imported} 条")
|
||
print(f"{'='*40}")
|