rymghosn opened a new pull request, #6535: URL: https://github.com/apache/fineract/pull/6535
## Description JIRA: https://issues.apache.org/jira/browse/FINERACT-2871 On PostgreSQL, generating a collection sheet (`POST /v1/collectionsheet?command=generateCollectionSheet`, and the group/center `generateCollectionSheet` commands) returns HTTP 500. The SQL in `CollectionSheetReadPlatformServiceImpl` relies on three things MySQL/MariaDB tolerate and PostgreSQL rejects: - `:dueDate` is bound as a formatted `String` and compared with `date` columns (`operator does not exist: date <= character varying`). The `LocalDate` is now bound directly. - `completed_derived` (boolean) is compared with `0` (`operator does not exist: boolean = integer`). It is now compared with `false`. - The queries select columns of joined lookup tables (`m_product_loan`, `m_savings_product`, `m_currency`, `m_staff`, `m_group_level`) that aren't functionally dependent on the `GROUP BY`, and the two loan queries re-group the derived table `loandata` to sum loan charges. The lookup tables' primary keys are added to the inner `GROUP BY`, and `chargesDue` is computed with a correlated subquery, so no outer `GROUP BY` is needed. The result rows and values are unchanged, and the SQL remains valid on MySQL/MariaDB. ### Tests New `CollectionSheetIntegrationTest`: disburses a loan with a due installment and a due charge, generates the individual collection sheet, and checks that the loan is on it with a positive `principalDue` and `chargesDue` = 10. Verified locally against PostgreSQL: - `develop`: the generate request returns 500 (`operator does not exist: boolean = integer`) and the new test fails. - With this change: the request returns 200 and the test passes. The center collection sheet (`POST /v1/centers/{centerId}?command=generateCollectionSheet`) also returns 200. ## Checklist - [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 (no API change) - [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]
