SQL 查詢審查：錯誤、關聯與慢速之處

What the code does

1. Reads orders.csv from the shop export.
2. For each order line, finds the product by SKU in stock.xlsx and subtracts the quantity.
3. Writes the updated stock back and logs totals to import_log.txt.

The structure is clear and functions are well named. Most issues are about input that does not look the way the script expects.

Bugs and weak spots

#	Line	Severity	Issue
1	48	High	The same CSV can be imported twice; there is no check for already processed order IDs. This is the most likely cause of negative stock.
2	72	High	int(row["qty"]) crashes on empty or "2.0" values from refunded orders.
3	95	Medium	SKU lookup is case-sensitive: "gt-101" and "GT-101" are treated as different products.
4	130	Medium	The file is opened without encoding="utf-8", so product names with accents break on Windows.
5	201	Low	Bare except: hides real errors and makes the log useless.

Corrected fragments

Issue 1: skip orders already imported

processed = load_processed_ids("processed_orders.txt")
for row in reader:
    if row["order_id"] in processed:
        continue
    # ... existing logic ...
    processed.add(row["order_id"])
save_processed_ids("processed_orders.txt", processed)

Why: each order is applied once, even if you run the script on the same file again.

Issue 2: read quantity safely

raw = (row.get("qty") or "").strip()
try:
    qty = int(float(raw))
except ValueError:
    log.warning("Order %s: bad quantity %r, skipped", row["order_id"], raw)
    continue

Why: refunded or partial lines are skipped with a clear note instead of stopping the whole import.

Issue 3: normalise SKUs

sku = row["sku"].strip().upper()

Apply the same .strip().upper() when building the stock dictionary.

Style and readability

• process() is 90 lines long; split it into read_orders, apply_to_stock, write_report.
• Replace magic column numbers (row[7]) with header names.

Please test each fix on a copy of your files: we read the code but did not run it.