Qeasy Cloud
Get Started

BDS Reconciliation Banniu After-sales Claim Order Sync Strategy Tutorial

· 王浩宇· Integration Solutions· 19 views· 4 min read
MySQL班牛售后理赔单班牛集成MySQL轻易云Data Integration对账

What This Strategy Solves

In one retail engagement, after-sales claim orders lived inside a third-party work order platform (Banniu), while the finance and reconciliation teams needed the same data inside their in-house MySQL data warehouse—rebuilt per bill number, with cost allocation and supplier settlement logic. With no automated channel in place, customer service exported CSVs by hand and handed them to the data team, which meant a typical 3-day delay and painful reverse-tracing whenever an attachment was missing. We used the Qeasy Data Integration Platform to wire a single strategy: periodically pull Banniu work orders tagged as "claim," then write them into MySQL's claim header table and detail table, aligning the two systems on the same reconciliation view.

Data Flow and Field Mapping

The flow is one-way: Banniu (source) → Qeasy middleware → MySQL (target). The middleware handles pagination aggregation, field normalization, and primary-key deduplication. On landing, a main SQL writes the header row and an extended SQL uses lastInsertId to write the detail rows.

DomainSource (Banniu work order)Middleware mappingTarget (MySQL header / detail)
Bill numbertask.id / custom numberbill_nosupplier_return_change.bill_no
Related trade notrade_nopassthroughtrade_no
Statusstatus enumstatus dictionarystatus
Responsible partyresponsible_partycentralized code mappingresponsible_party, responsible_party_detail
Cost partycost_partypassthroughcost_party
Warehousewarehouse_code/namecode alignmentwarehouse_code, warehouse_name
Logistics providerlogistic_namepassthroughlogistic_name
Suppliersuppiler_code/namecode mappingsuppiler_code, suppiler_name
Amountsclaim_amount / confirm_*numeric normalizationclaim_amount, confirm_claim_amount, etc.
Create timecreate_timetimezone unificationcreate_time

The detail table (supplier_return_change_detail) is written through extend_sql_1, with the header's lastInsertId injected as order_id to tie header and lines together.

Configuring This Strategy in Qeasy

In Qeasy, this strategy is decomposed into a source operator and a target operator:

  1. Source operator: platform "Banniu", API task.list (GET, RESTful, QUERY). Configure request parameters: project_id (the group ID where claim work orders live), page_size=50, page_num=1, plus star_modified / end_modified as the incremental window. Set idCheck=true and autoFillResponse=true so Qeasy takes over pagination.
  2. Target operator: platform "MySQL", API executeReturn (POST, EXECUTE). Set buildModel=true. Put header fields into main_params and the detail array into extend_params_1. The main_sql and extend_sql_1 are written as SQL templates with <{field: }> placeholders. The detail row's foreign key uses <{lastInsertId: }> so Qeasy auto-injects the header's generated ID.
  3. Scheduling: crontab is */30 7-23 * * *, covering business hours of the after-sales flow.

A common pattern among Qeasy customers is centralized code mapping: high-churn dictionaries (responsible party, supplier, warehouse) are kept in a dedicated mapping table that the strategy consults at runtime, so business teams change mappings, not the strategy itself.

Implementation Steps

We staged the rollout into three phases:

  • Phase 1: Align the incremental starting point. Pick a historical "waterline" in Banniu, set star_modified to that point, run a single sample with page_size=10 and page_num=1, and manually verify that header-to-detail rows are truly 1:N. Only widen the scope once it checks out.
  • Phase 2: Trigger the full backfill. Bump page_size to 50 and push star_modified to the earliest meaningful timestamp, then run one full historical backfill. Pause business-side writes during the backfill, or enable idempotency protection.
  • Phase 3: Daily scheduling. Switch to the */30 7-23 * * * incremental cadence. The source side uses a sliding window of star_modified = last success time and end_modified = current run time. The target side relies on a unique index on bill_no for idempotency, so network jitter or reruns will not cause duplicate writes.

The dual-track (full + incremental) pattern is widely adopted by Qeasy customers: full backfills run as ad-hoc plans, while daily traffic runs on incremental schedules, with a safe rerun path whenever something goes wrong.

Pitfalls and Lessons Learned

  1. Detail rows silently dropped. The first attempt only configured main_params and forgot to enable extend_sql_1, so the entire detail array was discarded. The safe rule: any 1:N source must wire the main SQL and the extended SQL together, with <{lastInsertId: }> carrying the foreign key.
  2. Timezone drift on star_modified. Banniu's default timezone differs from the MySQL warehouse's, so passing the value through shifts the incremental window by 8 hours and causes the same bill to be written twice. Normalize the timezone inside the Qeasy middleware.
  3. Hard-coded dictionaries. Encoding responsible-party values directly into the SQL means every dictionary change requires a redeployment. Move the dictionary into Qeasy's centralized code-mapping table instead.
  4. Detail column order swapped. The source fields claim_amount_detail and confirm_claim_amount_detail are easy to mix up. MySQL's VARCHAR will accept the swap silently, but the reconciliation numbers will be wrong. Enable Qeasy's field length and type checks.
  5. Schedule colliding with rate limits. */30 looks sparse, but Banniu's interface has QPS thresholds and parallel runs will trigger 429s. Add Qeasy's rate-limiting backoff and a retry window.

When to Use and When Not

Use it when: Banniu work orders and a MySQL reconciliation/settlement warehouse need 1:N synchronization, the business side wants bill_no-based idempotency, and dictionaries change frequently. Avoid it when: real-time second-level push is required (this strategy runs at a 30-minute minimum), the source is a direct database connection rather than a work-order system, or the target is a non-relational store.

Original content. Please credit the source when reposting: https://www.qeasy.cloud/insights/solutions/strat-mysql-p887249-0089-bds-8840f06c

Comments