What Problem Does This API Solve

The Jushuitan after-sales order query (non-Qimen channel) pulls refund/return orders from the SaaS platform into a local SQL Server table J_shtk. It powers after-sales analytics, return audits, financial reconciliation, and inventory backflush. In real scenarios, a retail enterprise can generate thousands of after-sales orders daily; exporting by hand is neither timely nor reliable. A scheduled incremental pull keeps after-sales data aligned with sales orders and inventory in near real-time.

API Capability Overview

Endpoint: /open/refund/single/query (non-Qimen channel; fields are largely the same as the Qimen variant, but auth and rate limiting differ).
Authentication: Jushuitan Open Platform AppKey signature; request body carries partnerid and timestamp. In QeasyCloud, we wrap this into a "Jushuitan adapter" so credentials are configured once and reused.
Request structure: JSON body with page_index, page_size (default 50, API max 50), modified_begin/modified_end, and date_type (0 = modified, 1 = created, 2 = confirm_date).
Pagination and incremental: You must page by time windows; a single window cannot exceed 7 days. In QeasyCloud scheduler we typically configure crontab: 1 0,1,2 * * * to run daily at 0, 1, 2 a.m., staggering load while providing redundancy.
Response structure: JSON with datas (array of after-sales orders) and has_next (whether more pages exist). Each order carries header fields plus nested items and batchs arrays.

Typical Field Mapping

Field	Type	Meaning	Field Notes
as_id	string	Jushuitan internal after-sales order number, primary key	`number` in metadata maps here; `id` is often `{{as_id}}{{modified}}` for incremental dedup
outer_as_id	string	Platform-side refund/return order number	Key for reconciliation with Taobao/JD
so_id / o_id	string	Online order number / internal order number	Links to sales order for traceability and inventory backflush
shop_id / wh_id / wms_co_id	string	Shop / warehouse / sub-warehouse code	Used for routing and grouping in multi-shop / multi-warehouse scenarios
shop_status	string	Platform-side after-sales flow status	See enum below; SUCCESS / CLOSED are terminal states
type	string	After-sales type	Normal return, refused return, refund-only, complaint, resend, exchange, etc.
good_status	string	Goods status	BUYER_NOT_RECEIVED / BUYER_RECEIVED / BUYER_RETURNED_GOODS / SELLER_RECEIVED
modified / created / confirm_date	string/datetime	Modified / created / confirmed time	Drives incremental pull; `confirm_date` marks true completion
refund / payment / freight	string	Refund / compensation / freight refund	All strings; cast to decimal to avoid precision loss
items	array	Line items	Must be flattened into items_sku_id, items_qty, items_amount, etc.
order_labels	string (array)	After-sales labels	Returned by the API but not declared in metadata; needs script to join with comma into order_label

How to Configure in QeasyCloud

QeasyCloud offers an out-of-the-box adapter for Jushuitan:

Data source: Pick the "Jushuitan · Non-Qimen" channel and fill in AppKey and partnerid. The platform handles signing and rate limiting automatically.
Field mapper: Map header fields via drag-and-drop; use the "Array Exploder" node for items to replicate parent keys (as_id, shop_id, wh_id) and map SKU/qty/amount/receive date to items_* columns.
Incremental strategy: In the scheduler, set modified_begin to the last successful end time, date_type=0, window 7 days, with automatic retry on failure.
Target write: Bulk upsert into SQL Server J_shtk; on primary-key conflict, overwrite by modified for idempotency.
Script hook: AfterSourceInvoke joins the order_labels array into a string, patching fields not declared in metadata.

Cross-Scenario Best Practices

Time window must not exceed 7 days: This is a hard API constraint; exceeding it returns an error. The safe approach is to add a validation check in the scheduler.
modified vs confirm_date: Use modified for incremental sync, but use confirm_date for "truly completed" after-sales — never mix the two.
Items must be flattened: The target is a relational schema; nested arrays must be flattened, otherwise downstream BI and reconciliation scripts will break.
Primary key dedup needs modified: Relying on as_id alone causes duplicates when the order is updated. The safe approach is a composite unique key on as_id+modified.
Amount fields are strings: After JSON parsing, cast explicitly to decimal(18,2) to avoid floating-point drift like 1.00000000001.
Three-level shop/warehouse/sub-warehouse coding: In multi-warehouse / multi-shop scenarios, carry shop_id, wh_id, and wms_co_id together; otherwise inventory backflush will misalign.

Lessons Learned

Pitfall 1: Window > 7 days returns HTTP 400. We once tried a one-shot monthly pull — it failed. The safe approach is to slice into 7-day chunks and call serially.
Pitfall 2: Wrong incremental key. Using created missed updates when customer service modified the after-sales status. Switching to modified stabilized it.
Pitfall 3: items not flattened, target table ended up with only garbage rows. The Array Exploder node must be configured with parent-key propagation, or detail lines will be lost.
Pitfall 4: order_labels array fails to land. The field is returned by the API but absent from metadata; a script fallback is required.
Pitfall 5: Amount precision drift. refund is "30.00" as a string; writing it directly causes 30.00000000001. Explicitly cast to decimal.

When to Use It

This API fits "e-commerce after-sales data localization": multi-platform, multi-shop, high after-sales volume, where refund/return orders must flow into your own ERP/data warehouse for reconciliation and analytics. If you only need light sync via the Qimen channel, or your after-sales volume is trivial, export from the platform backend directly — no integration pipeline is needed.

Jushuitan After-Sales Order Query API Field Handbook: An Authoritative Tutorial from the Non-Qimen Channel to SQL Server