shop-java/tools/test_order_writes.py

# -*- coding: utf-8 -*-
"""订单链路写接口端到端自测（订单事件 / 发货 / 售后）。

策略：本地库现状 + 现场造订单/售后单 → 调 Java 接口 → 直接查库断言落库结果 → 清理恢复。
写接口无法用线上 PHP 读回校验（本地库与线上是 PHP→本地单向同步），因此以库内断言为准。

用法：python test_order_writes.py
前置：python tools/backup_order_tables.py
"""
import json
import re
import time
from pathlib import Path

import pymysql
import requests
from _root import ROOT

JAVA_BASE = "http://127.0.0.1:18080"
TOKEN = "testsession123"
STORE_ID = 10001
YML = Path(str(ROOT / "yoshop-java/src/main/resources/application.yml"))

SESSION = requests.Session()
SESSION.trust_env = False

passed = failed = 0
created = {"order_ids": [], "delivery_ids": [], "refund_ids": []}


def check(name, cond, extra=""):
    global passed, failed
    if cond:
        passed += 1
        print(f"  [ok] {name}")
    else:
        failed += 1
        print(f"  [FAIL] {name} {extra}")


def db():
    pwd = re.findall(r"password:\s*(.+)", YML.read_text(encoding="utf-8"))[0].strip().strip('"')
    return pymysql.connect(host="192.168.1.177", port=3306, user="root", password=pwd,
                           database="shop", charset="utf8mb4", autocommit=True,
                           cursorclass=pymysql.cursors.DictCursor)


def java(path, method="get", params=None, body=None):
    url = JAVA_BASE + path
    headers = {"Access-Token": TOKEN}
    if method == "get":
        r = SESSION.get(url, headers=headers, params=params or {}, timeout=60)
    else:
        r = SESSION.post(url, headers=headers, json=body, timeout=60)
    try:
        return r.json()
    except Exception:
        return {"status": -1, "message": r.text[:200], "data": None}


def one(cur, sql, args=None):
    cur.execute(sql, args or ())
    return cur.fetchone()


def dec(value):
    return float(value) if value is not None else None


def now():
    return int(time.time())


# ---------------------------------------------------------------- 造数 / 清理

def create_order(cur, items):
    """items: [(goods_sku_id, total_num, goods_price)]，返回 (order_id, [order_goods_id...])"""
    order_no = "TESTJ%d" % int(time.time() * 1000)
    total = sum(round(p * n, 2) for _, n, p in items)
    cur.execute(
        "insert into yoshop_order (order_no,order_type,total_price,order_price,coupon_id,coupon_money,"
        "points_money,points_num,pay_price,update_price,buyer_remark,pay_method,pay_status,pay_time,"
        "trade_id,delivery_type,express_price,delivery_status,delivery_time,sync_weixin_shipping,"
        "receipt_status,receipt_time,order_status,points_bonus,merchant_remark,is_settled,settled_time,"
        "is_comment,order_source,order_source_id,order_source_data,platform,user_id,is_delete,store_id,"
        "create_time,update_time) values "
        "(%s,10,%s,%s,0,0,0,0,%s,0,'','balance',20,%s,0,10,0,10,0,0,10,0,10,0,'',0,0,0,10,0,'',"
        "'H5',10001,0,10001,%s,%s)",
        (order_no, total, total, total, now() - 60, now() - 60, now() - 60))
    order_id = cur.lastrowid
    created["order_ids"].append(order_id)
    goods_ids = []
    for sku_id, num, price in items:
        cur.execute(
            "insert into yoshop_order_goods (goods_id,goods_type,goods_name,image_id,deduct_stock_type,"
            "spec_type,goods_sku_id,goods_props,content,goods_no,goods_price,line_price,goods_weight,"
            "is_user_grade,grade_ratio,grade_goods_price,grade_total_money,coupon_money,points_money,"
            "points_num,points_bonus,total_num,total_price,total_pay_price,delivery_status,delivery_num,"
            "is_comment,order_id,user_id,goods_source_id,store_id,create_time) values "
            "(10004,10,'测试商品-百香果',10012,10,20,%s,'','','',%s,%s,1.5,0,0,0,0,0,0,0,0,%s,%s,%s,"
            "10,0,0,%s,10001,0,10001,%s)",
            (sku_id, price, price, num, round(price * num, 2), round(price * num, 2), order_id, now() - 60))
        goods_ids.append(cur.lastrowid)
    # 收货地址
    cur.execute(
        "insert into yoshop_order_address (name,phone,province_id,city_id,region_id,detail,order_id,"
        "user_id,store_id,create_time) values ('测试收货人','13700000000',1,2,3,'测试路1号',%s,10001,10001,%s)",
        (order_id, now() - 60))
    return order_id, goods_ids


def create_refund(cur, order_id, order_goods_id, refund_type=10):
    cur.execute(
        "insert into yoshop_order_refund (order_id,order_goods_id,user_id,type,apply_desc,audit_status,"
        "refuse_desc,refund_money,is_user_send,send_time,express_id,express_no,is_receipt,status,"
        "store_id,create_time,update_time) values "
        "(%s,%s,10001,%s,'测试售后原因',0,'',0,0,0,'','',0,0,10001,%s,%s)",
        (order_id, order_goods_id, refund_type, now() - 30, now() - 30))
    refund_id = cur.lastrowid
    created["refund_ids"].append(refund_id)
    return refund_id


def cleanup(cur, base_balance):
    for refund_id in created["refund_ids"]:
        cur.execute("delete from yoshop_order_refund where order_refund_id=%s", (refund_id,))
        cur.execute("delete from yoshop_order_refund_address where order_refund_id=%s", (refund_id,))
    for order_id in created["order_ids"]:
        cur.execute("delete from yoshop_order_delivery where order_id=%s", (order_id,))
        cur.execute("delete from yoshop_order_delivery_goods where order_id=%s", (order_id,))
        cur.execute("delete from yoshop_order_goods where order_id=%s", (order_id,))
        cur.execute("delete from yoshop_order_address where order_id=%s", (order_id,))
        cur.execute("delete from yoshop_order where order_id=%s", (order_id,))
    # 清理测试产生的余额流水（describe 带 TESTJ 订单号或标记）
    cur.execute("delete from yoshop_user_balance_log where `describe` like '%%TESTJ%%'")
    cur.execute("delete from yoshop_user_points_log where `describe` like '%%TESTJ%%'")
    cur.execute("update yoshop_user set balance=%s where user_id=10001", (base_balance,))


# ---------------------------------------------------------------- 用例

def case_update_price(cur):
    print("\n== 改价 / 备注 / 改地址 / 删除（订单 10002）")
    before = one(cur, "select * from yoshop_order where order_id=10002")
    base_price = dec(before["order_price"])

    r = java("/store/order.event/updatePrice", "post",
             body={"orderId": 10002, "form": {"order_price": 100, "express_price": 5}})
    check("改价返回成功", r.get("status") == 200 and r.get("message") == "操作成功", r)

    row = one(cur, "select * from yoshop_order where order_id=10002")
    check("order_price=100.00", dec(row["order_price"]) == 100.0, row["order_price"])
    check("express_price=5.00", dec(row["express_price"]) == 5.0, row["express_price"])
    check("pay_price=105.00", dec(row["pay_price"]) == 105.0, row["pay_price"])
    check("update_price=100-原价", round(dec(row["update_price"]), 2) == round(100 - base_price, 2),
          f'{row["update_price"]} 期望 {round(100 - base_price, 2)}')

    r = java("/store/order.event/updatePrice", "post",
             body={"orderId": 10001, "form": {"order_price": 10, "express_price": 0}})
    check("已支付订单改价被拒", r.get("status") == 500 and r.get("message") == "该订单不合法", r)

    r = java("/store/order.event/updatePrice", "post",
             body={"orderId": 10002, "form": {"order_price": 0, "express_price": 0}})
    check("实付款为 0 被拒", r.get("status") == 500 and r.get("message") == "订单实付款价格不能为0.00元", r)

    # 备注
    r = java("/store/order.event/updateRemark", "post",
             body={"orderId": 10002, "form": {"content": "测试商家备注888"}})
    check("备注返回成功", r.get("status") == 200, r)
    row = one(cur, "select merchant_remark from yoshop_order where order_id=10002")
    check("merchant_remark 落库", row["merchant_remark"] == "测试商家备注888", row)

    r = java("/store/order.event/updateRemark", "post", body={"orderId": 10002, "form": {}})
    row = one(cur, "select merchant_remark from yoshop_order where order_id=10002")
    check("空备注写为空串", row["merchant_remark"] == "", row)

    # 改地址
    r = java("/store/order.event/updateAddress", "post",
             body={"orderId": 10002,
                   "form": {"name": "新收货人", "phone": "13611112222",
                            "cascader": [310000, 310100, 310101], "detail": "新地址88号"}})
    check("改地址返回成功", r.get("status") == 200, r)
    addr = one(cur, "select * from yoshop_order_address where order_id=10002")
    check("收货人已更新", addr["name"] == "新收货人", addr)
    check("省市区已更新", (addr["province_id"], addr["city_id"], addr["region_id"]) == (310000, 310100, 310101), addr)
    check("详细地址已更新", addr["detail"] == "新地址88号", addr)

    # 恢复订单 10002 原值
    cur.execute("update yoshop_order set order_price=%s,pay_price=%s,update_price=%s,express_price=%s,"
                "merchant_remark=%s where order_id=10002",
                (before["order_price"], before["pay_price"], before["update_price"],
                 before["express_price"], before["merchant_remark"]))
    cur.execute("update yoshop_order_address set name=%s,phone=%s,province_id=%s,city_id=%s,region_id=%s,"
                "detail=%s where order_id=10002",
                ("檀先生", "18587721340", 1, 2, 3, "长安大街111号"))


def case_delete(cur):
    print("\n== 软删除（订单 10002）")
    r = java("/store/order.event/delete", "post", body={"orderId": 10002})
    check("删除返回成功", r.get("status") == 200 and r.get("message") == "删除成功", r)
    row = one(cur, "select is_delete from yoshop_order where order_id=10002")
    check("is_delete=1", row["is_delete"] == 1, row)

    r = java("/store/order/detail", "get", params={"orderId": 10002})
    check("删除后详情不可见", r.get("status") == 500 and r.get("message") == "未找到该订单记录", r)

    r = java("/store/order/list", "get", params={"dataType": "all", "page": 1})
    ids = [o["order_id"] for o in r["data"]["list"]["data"]]
    check("删除后列表不含该订单", 10002 not in ids, ids)

    cur.execute("update yoshop_order set is_delete=0 where order_id=10002")
    row = one(cur, "select is_delete from yoshop_order where order_id=10002")
    check("已恢复未删除状态", row["is_delete"] == 0, row)


def case_confirm_cancel(cur):
    print("\n== 审核取消（订单 10001，余额支付）")
    base_user = one(cur, "select balance,points from yoshop_user where user_id=10001")
    base_stock = one(cur, "select stock_total from yoshop_goods where goods_id=10004")["stock_total"]
    base_sku = one(cur, "select stock_num from yoshop_goods_sku where goods_id=10004 and goods_sku_id='10001'")["stock_num"]

    # 拒绝取消：订单回到进行中，不退款
    cur.execute("update yoshop_order set order_status=21 where order_id=10001")
    r = java("/store/order.event/confirmCancel", "post",
             body={"orderId": 10001, "form": {"status": 0}})
    check("拒绝取消返回成功", r.get("status") == 200, r)
    row = one(cur, "select order_status from yoshop_order where order_id=10001")
    check("拒绝后 order_status=10", row["order_status"] == 10, row)
    user = one(cur, "select balance from yoshop_user where user_id=10001")
    check("拒绝取消不动余额", dec(user["balance"]) == dec(base_user["balance"]), user)

    # 同意取消：退款 + 取消事件
    cur.execute("update yoshop_order set order_status=21 where order_id=10001")
    r = java("/store/order.event/confirmCancel", "post",
             body={"orderId": 10001, "form": {"status": 1}})
    check("同意取消返回成功", r.get("status") == 200, r)
    row = one(cur, "select order_status from yoshop_order where order_id=10001")
    check("同意后 order_status=20", row["order_status"] == 20, row)
    user = one(cur, "select balance from yoshop_user where user_id=10001")
    check("余额已退回 31 元", round(dec(user["balance"]) - dec(base_user["balance"]), 2) == 31.0,
          f'{base_user["balance"]} -> {user["balance"]}')
    log = one(cur, "select * from yoshop_user_balance_log where scene=40 order by log_id desc limit 1")
    check("写入退款流水 scene=40", log is not None and dec(log["money"]) == 31.0, log)
    check("流水描述含订单号", log is not None and "369583349062174103" in (log["describe"] or ""), log)
    stock = one(cur, "select stock_total from yoshop_goods where goods_id=10004")["stock_total"]
    sku = one(cur, "select stock_num from yoshop_goods_sku where goods_id=10004 and goods_sku_id='10001'")["stock_num"]
    check("商品总库存已回退 +1", stock == base_stock + 1, f"{base_stock} -> {stock}")
    check("SKU 库存已回退 +1", sku == base_sku + 1, f"{base_sku} -> {sku}")

    # 恢复
    cur.execute("update yoshop_order set order_status=10 where order_id=10001")
    cur.execute("update yoshop_user set balance=%s where user_id=10001", (base_user["balance"],))
    cur.execute("update yoshop_goods set stock_total=%s where goods_id=10004", (base_stock,))
    cur.execute("update yoshop_goods_sku set stock_num=%s where goods_id=10004 and goods_sku_id='10001'", (base_sku,))
    cur.execute("delete from yoshop_user_balance_log where scene=40")


def case_delivery(cur):
    print("\n== 发货（新建订单）")
    order_id, goods_ids = create_order(cur, [("10001", 2, 28.00), ("10002", 1, 28.00)])
    print(f"  测试订单 order_id={order_id} goods={goods_ids}")

    # 部分发货
    r = java("/store/order.delivery/delivery", "post",
             body={"orderId": order_id,
                   "form": {"deliveryMethod": 10, "expressId": 10003, "expressNo": "TEST123456",
                            "isAllPack": False,
                            "packGoodsData": [{"orderGoodsId": goods_ids[0], "deliveryNum": 1}]}})
    check("部分发货返回成功", r.get("status") == 200 and r.get("message") == "发货成功", r)

    dl = one(cur, "select * from yoshop_order_delivery where order_id=%s order by delivery_id desc limit 1", (order_id,))
    check("发货单已写入", dl is not None and dl["express_no"] == "TEST123456", dl)
    check("发货方式=10", dl is not None and dl["delivery_method"] == 10, dl)
    check("发货单 store_id=10001", dl is not None and dl["store_id"] == STORE_ID, dl)
    dg = one(cur, "select * from yoshop_order_delivery_goods where delivery_id=%s", (dl["delivery_id"],))
    check("发货商品写入", dg is not None and dg["delivery_num"] == 1 and dg["goods_id"] == 10004, dg)
    g1 = one(cur, "select delivery_num,delivery_status from yoshop_order_goods where order_goods_id=%s", (goods_ids[0],))
    check("订单商品已发 1 件/部分发货", (g1["delivery_num"], g1["delivery_status"]) == (1, 30), g1)
    g2 = one(cur, "select delivery_num,delivery_status from yoshop_order_goods where order_goods_id=%s", (goods_ids[1],))
    check("未发商品状态不变", (g2["delivery_num"], g2["delivery_status"]) == (0, 10), g2)
    order = one(cur, "select delivery_status,delivery_time from yoshop_order where order_id=%s", (order_id,))
    check("订单=部分发货(30)", order["delivery_status"] == 30, order)
    check("订单发货时间已写", order["delivery_time"] > 0, order)

    # 整单发货 + 无需物流
    r = java("/store/order.delivery/delivery", "post",
             body={"orderId": order_id,
                   "form": {"deliveryMethod": 20, "isAllPack": True}})
    check("整单发货返回成功", r.get("status") == 200, r)
    dl2 = one(cur, "select * from yoshop_order_delivery where order_id=%s order by delivery_id desc limit 1", (order_id,))
    check("无需物流时单号为空", dl2["express_no"] == "", dl2)
    check("发货方式=20", dl2["delivery_method"] == 20, dl2)
    g1 = one(cur, "select delivery_num,delivery_status from yoshop_order_goods where order_goods_id=%s", (goods_ids[0],))
    check("商品1全部发完", (g1["delivery_num"], g1["delivery_status"]) == (2, 20), g1)
    g2 = one(cur, "select delivery_num,delivery_status from yoshop_order_goods where order_goods_id=%s", (goods_ids[1],))
    check("商品2整单发完", (g2["delivery_num"], g2["delivery_status"]) == (1, 20), g2)
    order = one(cur, "select delivery_status from yoshop_order where order_id=%s", (order_id,))
    check("订单=已发货(20)", order["delivery_status"] == 20, order)

    # 重复发货
    r = java("/store/order.delivery/delivery", "post",
             body={"orderId": order_id, "form": {"deliveryMethod": 10, "isAllPack": True}})
    check("已发货订单再次发货被拒", r.get("status") == 500 and "不满足发货条件" in (r.get("message") or ""), r)

    # 未支付订单发货
    r = java("/store/order.delivery/delivery", "post",
             body={"orderId": 10002, "form": {"deliveryMethod": 10, "isAllPack": True}})
    check("未支付订单发货被拒", r.get("status") == 500 and "不满足发货条件" in (r.get("message") or ""), r)

    # 发货记录列表能查到新发货单
    r = java("/store/order.delivery/list", "get", params={"page": 1})
    found = [d for d in r["data"]["list"]["data"] if d["order_id"] == order_id]
    check("发货列表含新发货单", len(found) >= 1, r["data"]["list"]["total"])
    if found:
        item = found[0]
        check("列表含 orderData/orderData.user", "orderData" in item and item["orderData"].get("user") is not None, list(item.keys()))
        check("列表 goods 含订单商品", item["goods"] and item["goods"][0].get("goods") is not None, item.get("goods"))
        check("列表含物流公司", item.get("express") is not None and item["express"]["express_name"] == "圆通速递", item.get("express"))
        check("列表不含 eorder_html", "eorder_html" not in item, list(item.keys()))

    # 详情
    dl_id = found[0]["delivery_id"] if found else dl2["delivery_id"]
    r = java("/store/order.delivery/detail", "get", params={"deliveryId": dl_id})
    det = r.get("data", {}).get("detail") or {}
    check("发货单详情含 eorder_html", "eorder_html" in det, list(det.keys()))
    check("发货单详情无关联键", "goods" not in det and "orderData" not in det, list(det.keys()))
    return order_id, goods_ids


def case_refund(cur, order_id, goods_ids):
    print("\n== 售后（审核 / 确认收货退款）")
    base_user = one(cur, "select balance from yoshop_user where user_id=10001")
    refund_id = create_refund(cur, order_id, goods_ids[0], refund_type=10)

    # 拒绝无原因
    r = java("/store/order.refund/audit", "post",
             body={"orderRefundId": refund_id, "form": {"audit_status": 20}})
    check("拒绝未填原因被拦", r.get("status") == 500 and r.get("message") == "请输入拒绝原因", r)

    # 同意无地址
    r = java("/store/order.refund/audit", "post",
             body={"orderRefundId": refund_id, "form": {"audit_status": 10}})
    check("同意未选地址被拦", r.get("status") == 500 and r.get("message") == "请选择退货地址", r)

    # 同意 + 退货地址
    r = java("/store/order.refund/audit", "post",
             body={"orderRefundId": refund_id, "form": {"audit_status": 10, "address_id": 10001}})
    check("同意售后返回成功", r.get("status") == 200, r)
    row = one(cur, "select * from yoshop_order_refund where order_refund_id=%s", (refund_id,))
    check("audit_status=10", row["audit_status"] == 10, row)
    check("退货退款单仍为进行中", row["status"] == 0, row)
    addr = one(cur, "select * from yoshop_order_refund_address where order_refund_id=%s", (refund_id,))
    check("退货地址已写入", addr is not None and addr["name"] == "仓库", addr)
    check("退货地址 region_id 来自商城地址", addr is not None and addr["region_id"] == 5, addr)

    # 退款金额超限
    r = java("/store/order.refund/receipt", "post",
             body={"orderRefundId": refund_id, "form": {"refund_money": 9999}})
    check("超额退款被拦", r.get("status") == 500 and r.get("message") == "退款金额不能大于商品实付款金额", r)

    # 正常收货退款
    r = java("/store/order.refund/receipt", "post",
             body={"orderRefundId": refund_id, "form": {"refund_money": 10}})
    check("确认收货退款成功", r.get("status") == 200, r)
    row = one(cur, "select * from yoshop_order_refund where order_refund_id=%s", (refund_id,))
    check("refund_money=10.00", dec(row["refund_money"]) == 10.0, row)
    check("is_receipt=1", row["is_receipt"] == 1, row)
    check("status=20 已完成", row["status"] == 20, row)
    user = one(cur, "select balance from yoshop_user where user_id=10001")
    check("余额增加 10 元", round(dec(user["balance"]) - dec(base_user["balance"]), 2) == 10.0,
          f'{base_user["balance"]} -> {user["balance"]}')
    log = one(cur, "select * from yoshop_user_balance_log where scene=40 order by log_id desc limit 1")
    check("退款流水已写入", log is not None and dec(log["money"]) == 10.0, log)

    # 换货：同意即完成
    exch = create_refund(cur, order_id, goods_ids[1], refund_type=20)
    r = java("/store/order.refund/audit", "post",
             body={"orderRefundId": exch, "form": {"audit_status": 10, "address_id": 10001}})
    row = one(cur, "select * from yoshop_order_refund where order_refund_id=%s", (exch,))
    check("换货同意后直接完成", r.get("status") == 200 and row["status"] == 20, row)

    # 拒绝
    rej = create_refund(cur, order_id, goods_ids[1], refund_type=10)
    r = java("/store/order.refund/audit", "post",
             body={"orderRefundId": rej, "form": {"audit_status": 20, "refuse_desc": "不符合退货条件"}})
    row = one(cur, "select * from yoshop_order_refund where order_refund_id=%s", (rej,))
    check("拒绝售后 status=10", r.get("status") == 200 and row["status"] == 10, row)
    check("拒绝原因已落库", row["refuse_desc"] == "不符合退货条件", row)
    check("拒绝不写退货地址",
          one(cur, "select id from yoshop_order_refund_address where order_refund_id=%s", (rej,)) is None)

    # 列表 / 详情读回
    r = java("/store/order.refund/list", "get", params={"page": 1})
    rows = [x for x in r["data"]["list"]["data"] if x["order_refund_id"] == refund_id]
    check("售后列表能查到该单", len(rows) == 1, r["data"]["list"]["total"])
    if rows:
        item = rows[0]
        check("列表含订单号", item.get("order_no") is not None, item.get("order_no"))
        check("列表含 orderGoods.orderData.user",
              item.get("orderGoods") is not None and item.get("orderData") is not None and item.get("user") is not None,
              list(item.keys()))

    r = java("/store/order.refund/detail", "get", params={"orderRefundId": refund_id})
    det = r.get("data", {}).get("detail") or {}
    check("详情含 images/address/orderGoods",
          all(k in det for k in ("images", "address", "orderGoods", "orderData", "user", "express")), list(det.keys()))
    check("详情退货地址含 region", det.get("address") and "region" in det["address"], det.get("address"))
    check("详情不含 order_no", "order_no" not in det, list(det.keys()))

    cur.execute("update yoshop_user set balance=%s where user_id=10001", (base_user["balance"],))


def main():
    cur = db().cursor()
    base_balance = one(cur, "select balance from yoshop_user where user_id=10001")["balance"]
    try:
        case_update_price(cur)
        case_delete(cur)
        case_confirm_cancel(cur)
        order_id, goods_ids = case_delivery(cur)
        case_refund(cur, order_id, goods_ids)
    finally:
        cleanup(cur, base_balance)
        print("\n已清理测试数据")
    print(f"\n通过 {passed} / 失败 {failed}")


if __name__ == "__main__":
    main()