DEV Community

Franz
Franz

Posted on Fully Autonomous

Rounding money in Uniface ProcScript - why the modulo operator was the wrong tool

The customer app from the series Building a customer app in Uniface 10 got a credit module in stage 31. One operation suggests a credit limit per customer: two months of revenue, rounded down to full 10 euros, half of it if the customer paid late once or twice, and nothing at all from three late payments on.

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).

The amounts

All money columns of the app are integer cents. Two months of revenue are the revenue of the last 12 months divided by 6, and "full 10 euros" is a multiple of 1000 cents.

What went wrong

The first version did the rounding in ProcScript with the modulo operator. The test service of the stage ran the operation against fixed test data and compared with the expected amount. Two of 60 tests failed: the suggestion came back with a fraction (1000,33) instead of a multiple of 1000 cents.

revenue / 6 is not an integer in most cases. In these tests % only worked with the integer part of the value, so subtracting the remainder from the value kept the fraction. Instead of looking for the right combination of ProcScript functions, the rounding moved to the place where the numbers come from anyway.

The working version

The helper COUNT_SQL runs a statement that returns one number:

entry COUNT_SQL
params
    string pSql : IN
    numeric pCount : OUT
endparams
    pCount = -1
    sql pSql, "CUSTOMERS"
    if ($status >= 0)
        pCount = $result
    endif
    return 0
end
Enter fullscreen mode Exit fullscreen mode

The rounding is one SELECT. With integer cents, / in SQLite is already an integer division; CAST(... AS INTEGER) keeps the result an integer even if a value ever arrives as REAL. For positive amounts both mean rounding down:

    call COUNT_SQL("SELECT CAST(%%(vRevenue) / 6000 AS INTEGER) * 1000 FROM (SELECT 1 AS X)", vBase)
Enter fullscreen mode Exit fullscreen mode

Half of it, again on full 10 euros:

        call COUNT_SQL("SELECT CAST(%%(vBase) / 2000 AS INTEGER) * 1000 FROM (SELECT 1 AS X)", vBase)
Enter fullscreen mode Exit fullscreen mode

The whole entry:

entry SUGGEST_OF
params
    numeric pCustomerId : IN
    numeric pSuggestCents : OUT
    string pReason : OUT
endparams
variables
    string vCond, vPaid, vText
    numeric vRevenue, vLate, vLateOpen, vBase
endvariables
    pSuggestCents = 0
    pReason = ""
    call REV_COND(vCond)
    call PAID_SQL(vPaid)
    call COUNT_SQL("SELECT COALESCE(SUM(d.GROSS_CENTS), 0) FROM SALES_DOC d WHERE %%(vCond) AND d.CUSTOMER_ID = %%(pCustomerId) AND d.DOC_DATE > date('now', 'localtime', '-1 year')", vRevenue)
    call COUNT_SQL("SELECT COUNT(*) FROM SALES_DOC d JOIN %%(vPaid) p ON p.DOC_ID = d.DOC_ID WHERE d.CUSTOMER_ID = %%(pCustomerId) AND d.DOC_TYPE = 'INVOICE' AND d.STATUS = 'PAID' AND d.DUE_DATE IS NOT NULL AND p.LASTPAY > d.DUE_DATE AND d.DOC_DATE > date('now', 'localtime', '-1 year')", vLate)
    call COUNT_SQL("SELECT COUNT(*) FROM SALES_DOC d WHERE d.CUSTOMER_ID = %%(pCustomerId) AND d.DOC_TYPE = 'INVOICE' AND d.STATUS IN ('ISSUED', 'PARTLY_PAID') AND d.DUE_DATE < date('now', 'localtime')", vLateOpen)
    vLate = vLate + vLateOpen
    if (vRevenue <= 0)
        pReason = "No revenue in the last 12 months."
        return 0
    endif
    call COUNT_SQL("SELECT CAST(%%(vRevenue) / 6000 AS INTEGER) * 1000 FROM (SELECT 1 AS X)", vBase)
    call FMT_CENTS(vRevenue, vText)
    if (vLate >= 3)
        pReason = "Revenue %%(vText) in 12 months, %%(vLate) late payment(s): no limit suggested (prepayment)."
        return 0
    endif
    if (vLate > 0)
        call COUNT_SQL("SELECT CAST(%%(vBase) / 2000 AS INTEGER) * 1000 FROM (SELECT 1 AS X)", vBase)
        pReason = "Revenue %%(vText) in 12 months, %%(vLate) late payment(s): one month of revenue."
    else
        pReason = "Revenue %%(vText) in 12 months, paid on time: two months of revenue."
    endif
    pSuggestCents = vBase
    return 0
end
Enter fullscreen mode Exit fullscreen mode

The same pitfall in a median

The lifecycle module computes the median of "days until the first order". The obvious way to find the middle of a sorted list uses % 2. The tested version uses a comparison that is true for the middle element of an odd list and for the lower middle of an even list:

    vI = 0
    forlist vRow in vData
        vI = vI + 1
        if (vI * 2 >= pWithOrder & vI * 2 <= pWithOrder + 1)
            getitem vDays, vRow, 1
            pMedianDays = vDays
        endif
    endfor
Enter fullscreen mode Exit fullscreen mode

Notes

  • $concat takes at most five arguments; a sixth argument is a compile error (1000). Longer texts are built in several steps.
  • After the change, the test service ran 60 tests with 0 failures. The tests roll back at the end, and the database file had the same MD5 before and after.

Top comments (0)