Sales Return Inbound Order Sync from Wangdiantong to MySQL: A Single-Strategy Practical Tutorial

What This Strategy Solves

In retail and distribution scenarios, stores and e-commerce front-ends generate large volumes of return orders every day. These documents need to flow back into the ERP/data warehouse for financial reconciliation and inventory adjustment. In a real project for a retail company, the source system is Wangdiantong (an e-commerce ERP), the target system is a MySQL data warehouse, and Qeasy (the Qeasy Data Integration Platform) sits in between. The goal is to incrementally pull "completed sales return inbound orders" from Wangdiantong within a time window and persist them into a header table and a detail table. Although it looks like a simple document sync, the return scenario has tricky characteristics: a complex state machine (cancelled / editing / pending audit / completed), tight coupling between header and line items, and an incremental window that must not drop or duplicate rows. A small engineering oversight can easily break reconciliation.

Data Flow and Field Mapping

The overall flow is: Wangdiantong (QUERY) → Qeasy middleware → MySQL (EXECUTE). The source invokes wdt.stockin.order.query.refund and queries incrementally by start_time / end_time, defaulting to status=80 (completed). The target uses one REPLACE INTO for the header table and a 1:1 extended sub-statement for the detail table.

Key field mapping (excerpt):

Code mapping (shop number, item code, warehouse number) is the area most likely to go wrong in this integration. The reliable approach is to maintain a single mapping table inside Qeasy rather than scattering it across every strategy — "centralized code mapping" is one of the common patterns Qeasy customers adopt on site.

How to Configure on Qeasy

The source connector is Wangdiantong · Enterprise Qimen (query type). Pick the API wdt.stockin.order.query.refund. The request body uses macros to inject the time window: start_time from {{LAST_SYNC_TIME|datetime}}, end_time from {{CURRENT_TIME|datetime}}. Page size uses {{PAGINATION_PAGE_SIZE}} with a maximum of 50. The source number field maps to order_no, id maps to stockin_id, and idCheck is turned off (since the primary key is assigned on the database side).

The target connector is MySQL with api=execute, method SQL. Prepare two SQL statements:

• Main SQL main_sql: REPLACE INTO fky_wdt_stockin_order_query_refund (...) VALUES (...) with :field placeholders, bound to main_params;
• Sub-table SQL extend_sql_1: REPLACE INTO fky_wdt_stockin_order_query_refund_detail (...) VALUES (...) with parameters bound to extend_params_1 (an array mapped to details_list).

Use the id field as the idempotency key on the database side, with idCheck=true turned on; the platform will deduplicate by primary key. Note that REPLACE INTO is preferred over INSERT so that field changes on the source side overwrite previous values instead of accumulating as dirty data.

Implementation Steps

Phase 1: Incremental start point. Before going live, perform a historical full sync and initialize LAST_SYNC_TIME to a date earlier than the earliest audited document so nothing is missed. Configure cron as */11 * * * * (source) and 3-59/11 * * * * (target, offset by 3 minutes) to avoid hammering the source system simultaneously.

Phase 2: Full sync trigger and reconciliation. After the full sync, reconcile both sides using the stockin_id primary key — sample row counts and amounts. For the detail table, aggregate row counts by stockin_id and compare. The recommended approach on Qeasy is to create a separate "reconciliation strategy" that reuses the same metadata.

Phase 3: Scheduling frequency and stability. An 11-minute cycle is a friendly frequency for retail return documents: it does not overload the source while still ensuring data lands before the next business day. A common pattern Qeasy customers adopt is "dual-track incremental and full" — incremental during normal operation, with a fixed full sync every month as a fallback against time drift or abnormal rewrites.

Pitfalls and Lessons Learned

1. Always filter by status. The default status=80 (completed) is easy to forget; without it you will pull back drafts in "editing" state, which then get dirtied once edited by humans and flow back as bad data.
2. Never use a one-sided time window. Passing only start_time without end_time lets the source default to "now," which can drop trailing records across minute boundaries. Always fill both sides and align to seconds.
3. Always use extend_sql_1 for the detail table. Stuffing detail fields into a JSON column on the header table works short-term but causes serious pain later when doing OLAP analysis. "Phased header and detail" is one of the most common patterns among Qeasy customers.
4. REPLACE INTO is not magic. It depends on a unique key; without a unique index on the header table, duplicate rows will appear. Add a unique index on stockin_id.
5. Monitor clock drift. When the clocks of the source, Qeasy, and MySQL drift apart, the modified-based fallback ordering breaks. The reliable approach is to apply a server-side calibration on modified inside Qeasy before writing to the database.

When This Applies and When It Doesn't

Applies: return volumes ranging from a few thousand to tens of thousands of orders per day, with Wangdiantong as the source, where completed return orders need to flow back to MySQL for financial / inventory reconciliation. Does not apply: scenarios with sub-second real-time requirements, scenarios that require writing status back to the source, or scenarios where return orders trigger complex downstream procurement / transfer workflows — those are better served by an event bus than by scheduled polling.