Integration Overview: SQL Server Expense Requests with Qikaidesheng Attendance System

Scenario and Value

In a real engagement with a manufacturing enterprise, HR repeatedly ran into an awkward situation: an employee filled out a travel request in the morning, arrived at the customer site in the afternoon, and discovered the attendance system had no record of that out-of-office trip. When payroll rolled around, they were stuck matching single-document numbers back and forth in group chats. The root cause was not a wrong table copy on either side, but the lack of an automated closed loop between travel approval and attendance recording. The records simply sat in the OA/expense system view, and no one pushed them to the attendance side.

This solution closes that gap: it takes the travel request view (vw_cus_ExpenseRequest) in SQL Server, splits the data by region and operation type, and reliably synchronizes it to the out-of-office / business-trip interface of the Qikaidesheng attendance system, giving out-of-office attendance, work-hour accounting, and payroll calculation a consistent source.

Integration Architecture and Data Flow

The overall link is a one-way "pull-transform-write" three-stage pipeline:

┌─────────────────────┐    pull    ┌──────────────────┐    write    ┌─────────────────────────┐
│   SQL Server        │  ──────►  │  iPaaS / ETL     │  ──────►  │  Qikaidesheng Attendance│
│  (vw_cus_ExpenseReq)│            │  (sched/transform)│           │           │  (Out-work API)        │
└─────────────────────┘            └──────────────────┘           └─────────────────────────┘
        │                                  │                              │
        │ Incremental: fcreatedate/         │ Region filter S%/W%          │ RESTful API
        │ fmodifydate                       │ Date format conversion       │ /api/attendance/open/batch/att-out-work
        │ New/Modify type filter            │ leaveHourAmount calc         │
        │ Full / Incremental switch         │ Retry on errors              │
        ▼                                  │                              ▼

When we delivered this link on the Qeasy (轻易云) data integration platform, we split it into 4 independent strategies, orthogonally partitioned by "region × new/modify": Shenzhen-new, Shenzhen-modify, Wenjiang-new, Wenjiang-modify. There are no hard dependencies between the four, so in theory they can run in parallel; in practice, to dodge rate limiting on the attendance interface, we typically stagger them by 1–2 minutes.

The data flow runs in four steps:

1. Extract: Apply row-level filters on the source view by region prefix (S%, W%) and operation type (新增 / 修改).
2. Incremental cursor: New records use fcreatedate, modifications use fmodifydate, each maintaining its own watermark.
3. Field transformation: Normalize date formats, compute the duration between start and end times into leaveHourAmount (default to 0.1 hours when uncomputable), and map the work-number region code to the target field.
4. Write: Call the Qikaidesheng out-of-office batch API, using source FBillNO as target approvalSerialNum for natural idempotency.

Interface List

Implementation Notes

Phased scheduling. The 4 strategies are scheduled independently. We recommend a 5-minute cycle with a 2-minute start delay; if the attendance API has QPS limits, stagger Shenzhen and Wenjiang by another 1–2 minutes — that consistently outperforms running everything in parallel.

Incremental fields and full backfill. New and modify must use separate watermarks. Mixing them under fcreatedate will silently lose modify records, leaving the attendance side undercounting out-of-office work. Also keep manual or scheduled full-trigger (commonly every Sunday at dawn) to repair any gaps after an incremental miss.

Centralized encoding mapping. Mappings for the S/W work-number prefix and FBillNO → approvalSerialNum are typically placed in a single mapping table on Qeasy rather than scattered across each strategy, so later changes to region or field semantics only touch configuration, not scripts.

Retry and idempotency. Source FBillNO is written into target approvalSerialNum, combined with the target-side dedup logic, so repeat pushes never produce dirty data. For network jitter or throttling, the recommended backoff is 30s → 2min → 10min, capped at 3 rounds.

Privacy and credentials. Database connection strings and attendance API auth credentials all live in platform environment variables — they never appear in solution documents or script bodies. This is a hard constraint on our on-premise deliveries.

Best Practices and Pitfalls

1. Do not mix fcreatedate and fmodifydate on the source side. A typical mistake is using fcreatedate > :t to grab both new and modify records, which makes modify entries lag forever and the attendance side undercount out-of-office work. The safe pattern is to split new and modify into separate strategies with separate watermarks.

2. Do not throw when leaveHourAmount cannot be computed. When start/end times cross multiple days or span midnight, the duration calculation occasionally returns 0 or invalid values. Our convention is to default to 0.1 hours when it cannot be derived; the target side corrects per its own rules rather than failing the whole batch.

3. Know the batch ceiling. The Qikaidesheng /api/attendance/open/batch/att-out-work endpoint has a per-call batch ceiling; exceeding it silently truncates. In our Qeasy strategy we default to 200 records per chunk for large tables, so a single chunk failure does not force the entire segment to rerun.

4. Staged write for header and line items. For two-layer structures like "request header + companion list", the safe approach is to write the header first and obtain the returned approvalSerialNum, then use that ID to write the companion lines. On Qeasy's orchestration canvas this is just two chained nodes.

5. Qimen / non-Qimen dual-channel split. If the attendance side accepts both the Qimen channel and the direct open-platform channel, you need to pre-split by store-code prefix into different adapters. Keeping that rule in a mapping table is far easier to maintain than hardcoding it into scripts.

When to Use Qeasy

When data must flow continuously from a traditional relational database like SQL Server into a business system like Qikaidesheng that exposes RESTful open APIs, and the work involves multi-region, multi-operation-type scheduling, the Qeasy data integration platform provides visual strategy management, centralized encoding mappings, built-in incremental watermarks, and automatic retry. It compresses delivery from "write scripts + watch logs" to "configure strategies + read dashboards", and runs fully on premise.