Aarti-panchal01 opened a new pull request, #6706:
URL: https://github.com/apache/fineract/pull/6706
## Description
Several stretchy reports fail or return wrong values on PostgreSQL. This
fixes them in one tenant changelog part, `0261_fix_postgresql_report_sql.xml`,
with one changeset per fix so each can be reviewed (or dropped) on its own.
1. **Currency filter** (`report-parameter-currency-id-string-format`, all
databases). `currencyIdSelectAll` was registered as a `number` parameter, but
its values are currency codes and every report compares it with
`l.currency_code`. Since FINERACT-2624 binds `number` parameters as `Long`, the
default `R_currencyId=-1` fails on PostgreSQL with `operator does not exist:
character varying = bigint`, which breaks 22 reports in the Reports menu
(Portfolio at Risk, Active Loans, Aging, Loans Awaiting Disbursal, ...). On
every database a real code like `R_currencyId=USD` was rejected as not numeric.
The parameter is now `string`.
2. **`CAST(... AS CHAR)`** (`postgresql-reports-cast-as-varchar`, PostgreSQL
only). `CHAR` without a length is `CHAR(1)` on PostgreSQL, so "Portfolio at
Risk %" showed `5` instead of `5.88`. Replaced with `AS VARCHAR` in Portfolio
at Risk, Portfolio at Risk by Branch, Active Loans - Summary, the two maturity
summary reports and TxnRunningBalances.
3. **Happy Birthday** (`postgresql-happy-birthday-report`, PostgreSQL only).
`EXTRACT(DAY FROM date - date)` failed (date minus date is an integer), the
date comparison included the year so it only matched clients born today, and
the loan officer filter was hard-coded to `-1`. The query now compares month
and day, uses `${loanOfficerId}`, and quotes its aliases like the MySQL copy,
so the SMS campaign job finds the `mobileNo` column.
4. **TxnRunningBalances** (`postgresql-txn-running-balances-report`,
PostgreSQL only, hidden report). `DATE ${startDate}` became `DATE ?`, a syntax
error; now `CAST(${startDate} AS DATE)`. The loan officer was hard-coded to
`9`; now `${loanOfficerId}`. The opening balance row summed interest as
"Outstanding Principal"; now the same expression as the transaction rows and
the MySQL copy.
**Approach.** For the `AS CHAR` reports the changeset only swaps the cast
(`regexp_replace(report_sql, 'AS\s+CHAR\)', 'AS VARCHAR)', 'gi')` on the six
named reports) instead of repeating each full query, so the diff shows exactly
what changes and nothing else in those queries can drift. A plain `REPLACE`
would miss 13 of the 21 casts, because the spacing between `AS` and `CHAR`
varies and TxnRunningBalances uses lower case. Happy Birthday and
TxnRunningBalances get full `report_sql` rewrites, like FINERACT-2436 did,
because their logic changes. If you would rather have full rewrites for all of
them, or a single generic query instead of PostgreSQL-specific copies, it is a
mechanical change to the same file and I am happy to switch.
**Tests.** `StretchyReportsSmokeTest` runs every enabled table report with a
value for each of its parameters and checks that it returns column headers. It
also checks that "Portfolio at Risk %" keeps two decimals for every PAR type,
and that Happy Birthday runs and returns `mobileNo`. CI runs it on PostgreSQL,
MySQL and MariaDB, so the next report that breaks on one database should fail
there.
Checked on PostgreSQL 18.3 against a real tenant database (inside a
transaction, rolled back): before the change 22 of the 34 enabled table reports
failed with the currency error, Happy Birthday failed with the `extract` error
and TxnRunningBalances with the syntax error. After it, all 34 run, Portfolio
at Risk returns `5.88` / `5.25` (was `5`), Happy Birthday returns the clients
whose birthday is today whatever their birth year, and TxnRunningBalances
returns full transaction type names.
Not in this PR (noted on the ticket): the other PostgreSQL SMS report copies
have the same unquoted-alias problem as Happy Birthday, and the hidden staff
dashboard reports reference `m_loan_paid_in_advance`, which was dropped in 0014.
JIRA: https://issues.apache.org/jira/browse/FINERACT-2976
## Checklist
Please make sure these boxes are checked before submitting your pull request
- thanks!
- [x] Write the commit message as per [our
guidelines](https://github.com/apache/fineract/blob/develop/CONTRIBUTING.md#pull-requests)
- [x] Acknowledge that we will not review PRs that are not passing the build
_("green")_ - it is your responsibility to get a proposed PR to pass the build,
not primarily the project's maintainers.
- [x] Create/update [unit or integration
tests](https://fineract.apache.org/docs/current/#_testing) for verifying the
changes made.
- [x] Follow our [coding
conventions](https://cwiki.apache.org/confluence/display/FINERACT/Coding+Conventions).
- [ ] Add required Swagger annotation and update API documentation at
fineract-provider/src/main/resources/static/legacy-docs/apiLive.htm with
details of any API changes
- [x] [This PR must not be a "code
dump"](https://cwiki.apache.org/confluence/display/FINERACT/Pull+Request+Size+Limit).
Large changes can be made in a branch, with assistance. Ask for help on the
[developer mailing list](https://fineract.apache.org/#contribute).
- [x] If merging this PR resolves a JIRA issue, I will mark that issue as
resolved and set "Fix Version/s" appropriately.
- [x] I followed the [AI
Policy](https://github.com/apache/fineract/blob/develop/CONTRIBUTING.md#ai-policy).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
