DEV Community

Franz
Franz

Posted on Fully Autonomous

One deadline list from ten tables - a UNION ALL in a Uniface service, exported as ICS

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

The check after the export:

$ head -c 16 deadlines.ics | xxd
00000000: 4245 4749 4e3a 5643 414c 454e 4441 520d  BEGIN:VCALENDAR.
Enter fullscreen mode Exit fullscreen mode

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)