Practical Tutorial: Sales Order Rejection DingTalk Notification Sync

What This Strategy Solves (Scenario and Value)

After a sales order is rejected in the approval workflow, the biggest pain point is that the order just sits there unattended. In one real project, a retail enterprise had no proactive notification after rejection. Salespeople only discovered the order had been rejected two days later when chasing the deal, which directly impacted fulfillment efficiency. This strategy does one focused thing: it finds orders in MySQL that are in rejected status and still unhandled after more than 10 minutes, then sends a DingTalk Markdown message row by row to the order creator and a fixed carbon-copy recipient, forcing "who, which order, when rejected" onto IM and shortening the delay from manual inspection.

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

The source side is the MySQL query result. The middle layer is handled by the Qeasy data integration platform, which performs querying and mapping. The target side is the DingTalk enterprise message API. Each row on the source side represents one rejected order, and each row on the target side triggers one message. There is no nested detail structure.

How to Configure on Qeasy

The source uses a WebAPI select type. The main SQL is written in otherRequest.main_sql, and pagination is done through :limit and :offset placeholders paired with main parameters to avoid pulling large tables all at once. The five LEFT JOINs must all be done at the source side: creator → job number → DingTalk userid, customer uuid → customer name, and category dictionary lookup. This way the middle layer gets a clean flat result, and Qeasy only needs to do row-level mapping without writing any join scripts.

The target is DingTalk's topapi/message/corpconversation/asyncsend_v2, with four request parameters: robotCode, userIds, msgKey, and msgParam. The msgParam uses _function CONCAT to assemble the fixed title and source fields into JSON. On the Qeasy platform, "centralized encoding mapping management" is the most common pattern for such multi-table join scenarios—all userid concatenation and dictionary translation is placed in the source SQL, and the middle layer is only responsible for transport. Later, when changing dictionaries or adding cc recipients, only one place needs to be modified.

Implementation Steps

Incremental Starting Point: For the first launch, set the create_time cursor to 0:00 on the deployment day and run a full pull of historical rejected orders to complete the initial reach; then switch to incremental mode.

Full Trigger: It does not rely on manual triggering. The scheduled task runs automatically every 30 minutes from 8:00 to 21:00, scanning the rejection pool every 30 minutes during working hours. Note that the source SQL already filters out orders that have been rejected for less than 10 minutes using TIMESTAMPDIFF(MINUTE, a.create_time, now()) > 10, avoiding conflict with instant notifications.

Schedule Frequency: Every 30 minutes from 8:00 to 21:00, off at night, covering working hours and reducing invalid messages. On Qeasy, write the crontab as */30 8-21 * * *. The source metadata's crontab is */29 8-21 * * * (deliberately offset by 1 minute to prevent both ends from competing for the same window). This is a common trick in "incremental and full dual-track" setups.

Pitfall Review

1. The biggest trap is msgParam template inconsistency with source fields. The original msgParam referenced real_name, create_time, business_type, json_result, Solution, but the source SQL actually outputs order_no, customer_name, dict_label, userid, time. When run, all messages show blanks or placeholders. The reliable approach is to rewrite CONCAT based on actual source fields, change the title to "Sales Order Approval Rejection Reminder", and only reference columns actually output by SQL in the body.
2. userid concatenation must be stable. Manually splicing JSON with CONCAT('["', user3.userid, '",','"064140631255283"]') will break the structure if userid contains special characters. It is recommended to use JSON_ARRAY() or encapsulate the entire logic into a view at the source side, so Qeasy only reads the final field.
3. The cc recipient is a hardcoded constant. This fixed-append cc recipient approach requires modifying SQL when personnel change. In production environments, it is more recommended to maintain cc recipients as a configuration table, with the source SQL doing a LEFT JOIN to pull them in. Qeasy can switch recipients without changing a single line of code.
4. Pagination parameters must be paired. :limit and :offset must be bound together in main_params. Only writing :limit will cause data duplication or loss after the second page.
5. "Header-body phased approach" should be avoided in this single-layer mapping. This strategy has no detail rows. Do not forcibly split into header + empty body "for the sake of looking standard", which will cause row count doubling and message resending.

Applicable and Non-applicable Scenarios

Applicable: scenarios where notification to the creator is needed after approval rejection, the recipient is a fixed role, the message body contains a small number of key fields, and real-time requirements are at the 10-minute level. Not applicable: scenarios requiring aggregated approver notifications (please use the P4-060 approval reminder strategy), complex order state machines requiring event-driven approaches, message bodies containing nested details or multi-language, or recipients needing dynamic routing based on amount or customer level.