Wangdiantong Stock-in Order Sync to MySQL: A Practical Guide to a Single Strategy in Qeasy

What This Strategy Solves

In the supply chain of a retail enterprise, the WMS (Wangdiantong) handles on-site execution of purchase stock-in, transfer stock-in, inventory profit stock-in, and similar transactions. The backend MySQL data warehouse needs the data for inbound analytics, reconciliation, and cost accounting. The challenge: stock-in records in the WMS are scattered across multiple warehouses and statuses, while the downstream analytics database cannot get a complete, consistent view.

This strategy does one thing only: incrementally pull header and detail records of stock-in orders from Wangdiantong into MySQL based on the last-modified timestamp, for downstream reports and reconciliation. On the customer site, we use the Qeasy Data Integration Platform to orchestrate and schedule this pipeline. The source and target systems only expose their interfaces; mapping, state tracking, and exception handling are all managed in Qeasy.

Data Flow and Field Mapping (Source → Middle Layer → Target)

The flow is unidirectional: Wangdiantong (source) → Qeasy middle layer → MySQL (target). The source API wdt.stockin.order.query returns a single stock-in order together with its details per call, so the middle layer has to split header fields from detail fields and write them to two separate target tables.

Key field mapping:

The source uses start_time and end_time as a time window; the target uses REPLACE INTO for the header table and another REPLACE INTO for the detail sub-table. This is a typical "staged header/detail" landing pattern — a very common approach among Qeasy customers.

How to Configure in Qeasy

The configuration has three parts: source platform, target platform, and strategy orchestration.

Source platform side: Platform type is WebAPI, API is wdt.stockin.order.query, method POST, with order_no as the business number and stockin_id as the primary key in the response. The incremental field is bound to modified, and autoFillResponse is enabled. The request parameters bind start_time to {{LAST_SYNC_TIME|datetime}} and end_time to {{CURRENT_TIME|datetime}}.

Target platform side: Platform type MySQL, execution mode SQL. Prepare two SQL statements: the main statement performs REPLACE INTO on jry_wdt_stockin_order with named placeholders like :order_no, :stockin_id; the extension sub-table statement performs REPLACE INTO on jry_wdt_stockin_order_details_list, with the extension parameter field set to details_list (1:N array). Enable idCheck so that repeated arrivals of the same stock-in order get deduplicated instead of piling up dirty data.

Strategy orchestration side: Schedule uses */11 * * * *, with the source running a few minutes before the target (a typical combination is */11 for source and 3-59/11 for target) to prevent the target writing before the source has stabilised. Warehouse codes, owner codes, and similar fields are maintained centrally in a mapping table. Centralised code mapping is one of the most reused capabilities in Qeasy for this kind of project.

Implementation Steps

Implementation generally runs in three phases.

Phase 1: Full initialisation. Move start_time back to the business go-live date, take end_time as the current moment, and load all historical stock-in orders into MySQL in one shot. After the run, validate the row counts of the header and detail tables, as well as the key monetary totals.

Phase 2: Switch to incremental. Change the schedule from manual triggering to */11 * * * *. From then on, each run only pulls changes between the previous end_time and the current moment. Qeasy has a built-in LAST_SYNC_TIME variable, so we don't need to maintain a separate cursor table.

Phase 3: Incremental + full reconciliation dual track. Periodically (e.g., weekly) run a full reconciliation during low-traffic hours to patch up differences. This is a very typical "incremental and full dual-track" pattern among Qeasy customers: incremental ensures timeliness, full ensures eventual consistency.

Pitfall Retrospective

Pitfall 1: Wrong incremental starting point causes data loss. On the first integration, we set start_time to the go-live date, which missed stock-in orders that already existed before go-live but were still changing status. The safe approach is to load a full baseline first, then switch to incremental.

Pitfall 2: status enum mismatch. The source uses 10/20/25/30/32…80, while the analytics database uses a different dictionary. Writing the raw values through breaks all downstream reports. The typical mistake is not preparing a mapping table in advance. The safe approach is to maintain a central status-code mapping inside Qeasy, so future changes only touch one place.

Pitfall 3: Header written but details dropped. The source returns header + details in one call. If the middle layer only loops on the header write, details are silently lost. You must explicitly configure the 1:N extension and pass details_list to the sub-table SQL.

Pitfall 4: Wrong primary key in REPLACE INTO. If id is used as the deduplication primary key but the detail table also has id, records can be overwritten in the wrong place. The safe approach is to use stockin_id for the header and (stockin_id, rec_id) as the unique identifier for details.

Pitfall 5: Source and target schedules overlap. Both ends set to */11 * * * * and run in the same minute, causing the target to write before the source has settled. Time-shifting the two ends is the lowest-cost fix.

When to Use and When Not to Use

Use when: the source is a WMS/ERP exposing a time-window query interface; the target is a relational database like MySQL needing detail-level reconciliation or analytics; the business volume is moderate and a ~10 minute sync delay is acceptable.

Do not use when: sub-second inventory visibility is required (use message push or CDC instead); the source does not support time-window incremental and only supports full pulls; the target is a NoSQL store needing complex aggregation (direct SQL landing is not cost-effective).