Syncing Business Trip Requests to Attendance Out-Work Records: A Practical Walkthrough from SQL View to REST API
What This Strategy Solves
In a private-deployment environment at a manufacturing enterprise, business trip requests live in a business system backed by SQL Server, while attendance is managed in a third-party attendance platform. Every time an employee modifies a trip request, HR has to re-enter the out-work record manually, which often leads to missed updates and silent discrepancies. This strategy only syncs modify-type requests for staff numbers starting with W, using Qeasy to perform incremental transport—push once per modification, and the attendance side automatically records the out-work entry.
Data Flow and Field Mapping (Source → Middle Layer → Target)
The source is a SQL Server view, and the target is a RESTful endpoint /api/attendance/open/batch/att-out-work. The full chain is: SQL view → Qeasy collector (POST query) → Qeasy transformer → target POST.
Key field mapping:
| Business Meaning | Source (SQL Server view) | Target (Attendance REST) | Notes |
|---|---|---|---|
| Document number | FBillNO | approvalSerialNum | Idempotency key for deduplication |
| Staff number | FAccompany | staffNum | Drives both incremental and full sync |
| Trip start | FTravelStartDate | startTime | String, keep format consistent |
| Trip end | FTravelEndDate | endTime | String |
| Trip reason | freason | leaveReason | |
| Sync type | ftype='修改' | leaveType=businessTrip | Constant value |
| Watermark | fmodifydate | No matching field | Source-only, drives polling |
FAccompanyName, FTravelStartSite, FTravelEndSite, and fcreatedate are not pushed to the target in this strategy but can be kept in the middle layer for troubleshooting logs.
How to Configure It in Qeasy
Source configuration: Pick API type select (POST query), execution type QUERY. The crucial part is adding two time windows in main_sql—the watermark fmodifydate > :fmodifydate plus an absolute floor fcreatedate >= '2026-01-09 00:00:00.000' to avoid one-shot dumps of legacy dirty data. Then apply ftype='修改' and FAccompany like 'W%' as the whitelist. This is the typical "centralized code mapping + incremental & full dual-track" approach—all conditions are pushed down to SQL, so the front end only cares about the result set.
Target configuration: method=POST, idCheck=true (the target system deduplicates by document number), leaveType as a constant businessTrip, and the remaining fields reference the source via {{field name}}. The batch endpoint accepts multiple records at once; failed rows can be inspected in Qeasy logs to see exactly which approvalSerialNum did not land.
Scheduling for both platforms: 2-59/5 * * * * and 5-59/5 * * * * are staggered—source runs first, target runs 3 minutes later, giving the collector time to land results.
Implementation Steps
Step 1: Set the incremental baseline. Record the current maximum value of fmodifydate in the view as the watermark. First do an empty run for full data (keep fcreatedate >= '2026-01-09 00:00:00.000') and confirm the target endpoint can receive data normally.
Step 2: Switch to incremental. Add fmodifydate > :fmodifydate; the parameter :fmodifydate is maintained automatically by Qeasy (the maximum value from the last successful push). Set the source crontab to 2-59/5 * * * *.
Step 3: Target integration. First hit /api/attendance/open/batch/att-out-work with Postman to confirm the response structure aligns with Qeasy logs; then switch to Qeasy, enable batch pushing, and set the target crontab to 5-59/5 * * * *, 3 minutes apart from the source.
Step 4: Verify and freeze. Run for 3 consecutive business days. Each day, sample 5 records and compare with the attendance out-work list to confirm approvalSerialNum is not duplicated and startTime/endTime match the request, then publish officially.
Lessons Learned from the Field
-
Wrong watermark field. The first attempt used
fcreatedateas the watermark, which meant historical modifications were never picked up. The safe approach: iffmodifydateexists, use it; otherwise considerfupdatedate/fmodifytime. -
Missing whitelist in the view condition.
FAccompany like 'W%'is a specific staff number prefix. Forgetting it will push staff numbers from other bases, producing unexpected out-work entries. Centralized management of code segments became a hard requirement after this incident. -
Did not limit
ftypeto "modify". The same view contains both new and modified records; new ones are handled by a separate strategy. This strategy only filtersftype='修改'—otherwise modifications would also trigger the new-type strategy, producing two attendance records. -
Inconsistent time formats. Source
FTravelStartDatemay beyyyy-MM-dd HH:mm:ss, while the target expects an ISO string with timezone. Direct mapping will return HTTP 400. The safe approach is to add aDate.parsenormalization step in the Qeasy transformer and align both sides to the same timezone (UTC+8 in this design).
When to Use and When Not to Use
Use when: the document database is SQL Server, attendance is a third-party SaaS/HTTP endpoint, you need to incrementally sync out-work records on modification events, and the source has reliable timestamp fields.
Do not use when: the source has no modification timestamp (full sync only), the target endpoint lacks an idempotency key (may produce duplicates), or the same staff number spans multiple bases requiring complex routing—in those cases, split strategies by staff number segment.