Every module of the customer app from the series Building a customer app in Uniface 10 has its own due date: invoices are due, quotes expire, supplier bills must be paid, subscriptions bill again or end, follow-ups and todos wait, appointments and opportunities have a date. Stage 31 added one list for all of them, without a schema change.
Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, October 2026. The app uses SQLite through the SLE driver (SQLite 3.50.2).
Same columns for every source
Each source becomes one SELECT with the same eight columns: date, kind, document number, id, customer id, name, text and the responsible user. The first SELECT names the columns, the others only have to keep the order. The ten statements are joined with UNION ALL into a subquery:
entry ALL_SQL
params
string pSql : OUT
endparams
variables
string vCust, vI, vQ, vB, vP, vS1, vS2, vF, vT, vA, vO
endvariables
vCust = "c.LAST_NAME || ', ' || c.FIRST_NAME"
vI = "SELECT d.DUE_DATE AS DUE, 'INVOICE' AS KIND, COALESCE(d.DOC_NO, '') AS REF_NO, d.DOC_ID AS REF_ID, d.CUSTOMER_ID AS CID, %%(vCust) AS NAME, 'Invoice due' AS TXT, COALESCE(w.OWNER, '') AS OWNER FROM SALES_DOC d JOIN CUSTOMER c ON c.CUSTOMER_ID = d.CUSTOMER_ID LEFT JOIN CUSTOMER_OWNER w ON w.CUSTOMER_ID = d.CUSTOMER_ID WHERE d.DOC_TYPE = 'INVOICE' AND d.STATUS IN ('ISSUED', 'PARTLY_PAID') AND d.DUE_DATE IS NOT NULL"
vQ = "SELECT d.VALID_UNTIL, 'QUOTE', COALESCE(d.DOC_NO, ''), d.DOC_ID, d.CUSTOMER_ID, %%(vCust), 'Quote valid until', COALESCE(w.OWNER, '') FROM SALES_DOC d JOIN CUSTOMER c ON c.CUSTOMER_ID = d.CUSTOMER_ID LEFT JOIN CUSTOMER_OWNER w ON w.CUSTOMER_ID = d.CUSTOMER_ID WHERE d.DOC_TYPE = 'QUOTE' AND d.STATUS = 'SENT' AND d.VALID_UNTIL IS NOT NULL"
vB = "SELECT b.DUE_DATE, 'BILL', COALESCE(b.BILL_NO, ''), b.BILL_ID, 0, s.SUPPLIER_NAME, 'Supplier bill due', '' FROM SUPPLIER_BILL b JOIN SUPPLIER s ON s.SUPPLIER_ID = b.SUPPLIER_ID WHERE b.STATUS = 'OPEN' AND b.DUE_DATE IS NOT NULL"
vP = "SELECT p.DUE_DATE, 'PURCHASE', COALESCE(p.PO_NO, ''), p.PO_ID, 0, s.SUPPLIER_NAME, 'Delivery expected', '' FROM PURCHASE_DOC p JOIN SUPPLIER s ON s.SUPPLIER_ID = p.SUPPLIER_ID WHERE p.STATUS IN ('SENT', 'PARTLY_RECEIVED') AND p.DUE_DATE IS NOT NULL"
vS1 = "SELECT u.NEXT_DATE, 'SUBSCRIPTION', '', u.SUB_ID, u.CUSTOMER_ID, %%(vCust), 'Next billing: ' || u.SUB_NAME, COALESCE(w.OWNER, '') FROM SUBSCRIPTION u JOIN CUSTOMER c ON c.CUSTOMER_ID = u.CUSTOMER_ID LEFT JOIN CUSTOMER_OWNER w ON w.CUSTOMER_ID = u.CUSTOMER_ID WHERE u.STATUS = 'ACTIVE' AND u.NEXT_DATE IS NOT NULL"
vS2 = "SELECT u.END_DATE, 'SUB_END', '', u.SUB_ID, u.CUSTOMER_ID, %%(vCust), 'Subscription ends: ' || u.SUB_NAME, COALESCE(w.OWNER, '') FROM SUBSCRIPTION u JOIN CUSTOMER c ON c.CUSTOMER_ID = u.CUSTOMER_ID LEFT JOIN CUSTOMER_OWNER w ON w.CUSTOMER_ID = u.CUSTOMER_ID WHERE u.STATUS IN ('ACTIVE', 'PAUSED') AND u.END_DATE < '9999-12-31'"
vF = "SELECT f.DUE_DATE, 'FOLLOWUP', '', f.FOLLOWUP_ID, f.CUSTOMER_ID, %%(vCust), COALESCE(f.FOLLOWUP_TEXT, ''), COALESCE(f.ASSIGNED_TO, '') FROM CUSTOMER_FOLLOWUP f JOIN CUSTOMER c ON c.CUSTOMER_ID = f.CUSTOMER_ID WHERE f.IS_DONE = 0 AND f.DUE_DATE IS NOT NULL"
vT = "SELECT t.DUE_DATE, 'TODO', '', t.TODO_ID, 0, '', COALESCE(t.TODO_TEXT, ''), t.USER_NAME FROM APP_TODO t WHERE t.IS_DONE = 0 AND t.DUE_DATE IS NOT NULL"
vA = "SELECT date(a.START_AT), 'APPOINTMENT', '', a.APPT_ID, COALESCE(a.CUSTOMER_ID, 0), COALESCE(%%(vCust), ''), a.SUBJECT, COALESCE(m.U, '') FROM APPOINTMENT a LEFT JOIN CUSTOMER c ON c.CUSTOMER_ID = a.CUSTOMER_ID LEFT JOIN (SELECT APPT_ID, MIN(USER_NAME) AS U FROM APPOINTMENT_USER GROUP BY APPT_ID) m ON m.APPT_ID = a.APPT_ID WHERE a.STATUS = 'PLANNED'"
vO = "SELECT o.CLOSE_DATE, 'OPPORTUNITY', '', o.OPP_ID, o.CUSTOMER_ID, %%(vCust), o.TITLE, COALESCE(o.OWNER, '') FROM OPPORTUNITY o JOIN CUSTOMER c ON c.CUSTOMER_ID = o.CUSTOMER_ID WHERE o.PHASE IN ('QUALIFY', 'PROPOSAL', 'NEGOTIATE') AND o.CLOSE_DATE IS NOT NULL"
pSql = "(%%(vI) UNION ALL %%(vQ) UNION ALL %%(vB) UNION ALL %%(vP) UNION ALL %%(vS1) UNION ALL %%(vS2) UNION ALL %%(vF) UNION ALL %%(vT) UNION ALL %%(vA) UNION ALL %%(vO))"
return 0
end
Some details:
- Supplier bills and purchase orders have no customer; they get 0 and the supplier name.
- A subscription appears twice: next billing date and end date. The end date is only a deadline if it is not the "open end" value
9999-12-31. - Appointments can have several users;
MIN(USER_NAME)picks one in a stable way. - The customer name is put together in SQL once (
vCust) and reused in every SELECT.
All operations of the service use this subquery and only add WHERE and ORDER BY, for example for the calendar file:
call READ_ROWS("SELECT REPLACE(DUE, '-', '') AS D1, REPLACE(date(DUE, '+1 days'), '-', '') AS D2, KIND, REF_NO, REF_ID, NAME, TXT FROM %%(vAll) x WHERE DUE >= '%%(vFrom)' AND DUE <= '%%(vTo)' ORDER BY DUE, KIND, REF_ID", vData, vN, pError)
SQLite computes the end date (date(DUE, '+1 days')) because an all-day event in ICS ends on the next day.
The ICS file
public operation DEADLINE_ICS
params
string pFrom : IN
string pTo : IN
string pFileName : IN
numeric pCount : OUT
string pError : OUT
endparams
variables
string vFrom, vTo, vAll, vData, vRow, vNl, vText, vStamp, vDay, vNext, vKind, vRefNo, vRefId, vName, vTxt, vSummary, vLogErr
numeric vN
endvariables
pCount = 0
pError = ""
activate "RIGHTS_SVC".REQUIRE("EDIT", pError)
if ($status < 0)
return -1
endif
call RANGE(pFrom, pTo, vFrom, vTo, pError)
if ($status < 0)
return -1
endif
call ALL_SQL(vAll)
call READ_ROWS("SELECT REPLACE(DUE, '-', '') AS D1, REPLACE(date(DUE, '+1 days'), '-', '') AS D2, KIND, REF_NO, REF_ID, NAME, TXT FROM %%(vAll) x WHERE DUE >= '%%(vFrom)' AND DUE <= '%%(vTo)' ORDER BY DUE, KIND, REF_ID", vData, vN, pError)
if ($status < 0)
return -1
endif
vNl = "%%^"
call GET_TEXT("SELECT strftime('%Y%m%dT%H%M%S', 'now', 'localtime') FROM (SELECT 1 AS X)", vStamp)
vText = $concat("BEGIN:VCALENDAR", vNl, "VERSION:2.0", vNl, "PRODID:-//Customer Management//Deadlines//EN")
forlist vRow in vData
getitem vDay, vRow, 1
getitem vNext, vRow, 2
getitem vKind, vRow, 3
getitem vRefNo, vRow, 4
getitem vRefId, vRow, 5
getitem vName, vRow, 6
getitem vTxt, vRow, 7
if (vRefNo = "")
vRefNo = "#%%(vRefId)"
endif
vSummary = "%%(vKind) %%(vRefNo): %%(vTxt)"
if (vName != "")
vSummary = "%%(vSummary) (%%(vName))"
endif
call ICS_ESC(vSummary)
vText = $concat(vText, vNl, "BEGIN:VEVENT", vNl, "UID:deadline-%%(vKind)-%%(vRefId)-%%(vDay)@customers")
vText = $concat(vText, vNl, "DTSTAMP:%%(vStamp)", vNl, "DTSTART;VALUE=DATE:%%(vDay)")
vText = $concat(vText, vNl, "DTEND;VALUE=DATE:%%(vNext)", vNl, "SUMMARY:%%(vSummary)")
vText = $concat(vText, vNl, "END:VEVENT")
pCount = pCount + 1
endfor
vText = $concat(vText, vNl, "END:VCALENDAR", vNl)
call WRITE_FILE_NOBOM(vText, pFileName, pError)
if ($status < 0)
return -1
endif
activate "EXPORTLOG_SVC".LOG_EXPORT("DEADLINE_ICS", pCount, pFileName, vLogErr)
if ($status < 0)
pError = $concat("The calendar was written, but the export could not be logged: ", vLogErr)
return -1
endif
return 0
end
Commas and semicolons in a text are escaped:
entry ICS_ESC
params
string pText : INOUT
endparams
if ($scan(pText, ",") > 0)
pText = $replace(pText, 1, ",", "\,", -1)
endif
if ($scan(pText, ";") > 0)
pText = $replace(pText, 1, ";", "\;", -1)
endif
return 0
end
Without BOM
The first version wrote the file with lfiledump and the character set "UTF-8". The file started with the bytes EF BB BF before BEGIN:VCALENDAR. The tested version writes it with /nobom:
entry WRITE_FILE_NOBOM
params
string pText : IN
string pFileName : IN
string pError : OUT
endparams
pError = ""
lfiledump/nobom pText, pFileName, "UTF-8"
if ($status < 0)
pError = $concat("The file ", pFileName, " could not be written (error ", $procerror, ").")
return -1
endif
return 0
end
The check after the export:
$ head -c 16 deadlines.ics | xxd
00000000: 4245 4749 4e3a 5643 414c 454e 4441 520d BEGIN:VCALENDAR.
Each line ends with CR LF (%%^), which is what RFC 5545 asks for.
Notes
- RFC 5545 says lines should not be longer than 75 octets. The test file had no long lines, but the service does not fold lines yet; a long subject would need it.
- Writing the calendar needs the role EDIT and is written to the export log, like the other exports of the app.
- The UID is built from kind, id and date, so the same deadline gets the same UID on the next export.
Top comments (0)