Wangdiantong Transfer Order Sync to MySQL: A Single-Strategy Deep Dive
What This Strategy Solves
Transfer orders document stock movement between warehouses. They feed both finance reconciliation and in-transit analytics. In one real project, a retail enterprise handled over a thousand transfer orders per day. Pulling everything via a full-volume API was slow and risked overwhelming the upstream; missed orders were even worse. This strategy uses a time-windowed incremental query plus REPLACE writes to land Wangdiantong transfer orders reliably into a MySQL staging database: fast, replayable, and ready for downstream consumers.
Data Flow and Field Mapping
Data flows: Wangdiantong (source) → Qeasy Data Integration Platform (middle layer) → MySQL (target).
The source calls wdt.stock.transfer.query, pulling incrementally by start_time / end_time. The target uses execute SQL: a main table write REPLACE INTO tp_wdt_stock_transfer and a detail table write REPLACE INTO tp_wdt_stock_transfer_details_list. Primary-key collisions overwrite, so the operation is naturally idempotent.
Key field mapping:
| Business meaning | Source (Wangdiantong) | Middle layer | Target (MySQL) | Notes |
|---|---|---|---|---|
| Transfer order number | transfer_no | Pass-through | transfer_no | Document unique ID |
| Order status | status | Pass-through | status | 10/20/30/40/42/44/46/50/60/62/64/66/70/80/90 |
| Source warehouse code | from_warehouse_no | Pass-through | from_warehouse_no | Warehouse unique code |
| Target warehouse code | to_warehouse_no | Pass-through | to_warehouse_no | Warehouse unique code |
| Created at | created | Pass-through | created | datetime |
| Modified at | modified | Pass-through | modified | Incremental cursor |
| Detail line ID | rec_id | Pass-through | rec_id | 1:N detail identifier |
| Product code | spec_no / goods_no | Pass-through | spec_no / goods_no | Many-to-one mapping |
Configuring on Qeasy
On the Qeasy Data Integration Platform's strategy canvas, this strategy is essentially two steps: a source Query and a target SQL Execute.
- Source: Pick API
wdt.stock.transfer.query, POST method.start_timeuses{{LAST_SYNC_TIME|datetime}}andend_timeuses{{CURRENT_TIME|datetime}}; the scheduler maintains the incremental cursor automatically.statusexplicitly passes50,60,70,80,90, filtering to statuses meaningful to downstream consumers (partial out, fully out, partial in, pending settlement, transfer completed). - Target: SQL type Execute. Main statement:
REPLACE INTO tp_wdt_stock_transfer (...) VALUES (...). The detail table usesextend_sql_2writing totp_wdt_stock_transfer_details_listwithextend_params_2set todetails_list.idCheckoff andbuildModeloff avoid misjudgment. - Code mapping: Warehouse codes, product codes, and supplier codes all live in a centralized code-mapping registry rather than scattered across strategies. New strategies reference this registry directly; this is one of the typical Qeasy customer patterns.
Implementation Steps
Phase the schedule to stay safe:
- Incremental anchor: Run a one-time full-volume backfill first to populate historical transfer orders and record
LAST_SYNC_TIME. Then run every 11 minutes (*/11 * * * *). Offset the source and target by 3 minutes (3-59/11 * * * *) so they don't hit MySQL simultaneously. - Full-volume trigger: Run a full-volume replay during the daily low-peak window. With
REPLACEidempotency, drifted rows self-correct without a complex reconciliation job. - Schedule frequency: Bump to every 5 minutes during peak hours, every 30 minutes during off-peak. The source polling interval must always exceed the target write duration to avoid backlog.
- Validation: Use Qeasy's built-in data-validation module to check
transfer_noagainstgoods_count / goods_in_count / goods_out_count; anomalies go into an alert queue.
Pitfalls and Lessons
- Pitfall 1: Leaving
statusempty. Without a value, the source returns every status, including massive numbers of cancelled orders that confuse downstream logic. The safe approach is to pass50,60,70,80,90explicitly and sync only what matters. - Pitfall 2: Not maintaining the incremental cursor.
LAST_SYNC_TIMEmust advance only after a successful write; otherwise retries replay the same batch. AlthoughREPLACEis idempotent, logs and alerts still flood. - Pitfall 3: Wrong 1:N extend parameter.
extend_params_2must equal the source response array name (for example,details_list). A mismatch silently drops details while the main table still lands — extremely hard to debug. The Qeasy pattern of staged header/detail writes is recommended: get the main table working first, then enable the detail table. - Pitfall 4: Misusing
REPLACE.REPLACEdeletes then inserts, so auto-increment IDs change and triggers fire twice. The safe approach is a composite unique index ontransfer_id + rec_id, keeping the business key stable. - Pitfall 5: Warehouse code mismatch. Wangdiantong uses string codes while the business system uses numeric IDs. Passing them through directly causes collisions. The common Qeasy customer pattern here is centralized code mapping: a single mapping table in the platform translates source codes before writing.
When to Use and When Not
Use when transfer volume is high, near-real-time landing is required, the source exposes only a Query interface, and header/detail must land in the same database. Do not use when sub-second push is required, event-driven callbacks are needed, or the source has no time field to drive an incremental cursor — in those cases switch to an instant-inventory sync or message-push strategy.