FINERACT-2079: Rectify query for cashier transactions

[wkigenyi]

Description

Describe the changes made and why they were made.

Ignore if these details are present on the associated Apache Fineract JIRA ticket.

Checklist

Please make sure these boxes are checked before submitting your pull request - thanks!

• [ ] Write the commit message as per https://github.com/apache/fineract/#pull-requests

• [ ] 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.

• [ ] Create/update unit or integration tests for verifying the changes made.

• [ ] Follow coding conventions at 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

• [ ] Submission is not a "code dump". (Large changes can be made "in repository" via a branch. Ask on the developer mailing list for guidance, if required.)

FYI our guidelines for code reviews are at https://cwiki.apache.org/confluence/display/FINERACT/Code+Review+Guide.

[adamsaghy]

@wkigenyi Can you please write an integration test which calls the cashier transactions API and calls the cashier transactions with summary API? This way we can be sure, your fix is working and avoid regression in the future?

[wkigenyi]

@adamsaghy this commit basically replaces the wrong table column in the sql query with the right one. Please guide me on the best way to write this this test.

[adamsaghy]

@adamsaghy this commit basically replaces the wrong table column in the sql query with the right one. Please guide me on the best way to write this this test.

First of all thank you for the fix. Like I said in my earlier comment you should write an integration test which call the mentioned APIs and they are not failing (response is HTTP 200). This way we can make sure in the future if the query or the data model got changed we will know about it.

You may find plenty example in the "integration-test" directory.

Please let me know if you are facing any trouble!

[wkigenyi]

Thanks Adam, I do understand the importance of the test. I will look into the mentioned directory for examples.

[adamsaghy]

Thanks Adam, I do understand the importance of the test. I will look into the mentioned directory for examples.

Hi

I believe we dont really have too many test cases for cashier transactions, so i will try to give you some help here:

• I would create a new Helper class which extends the IntegrationTest class which contains helper methods to call the "fineract-client" and via that we can call cashier transactions via type-safe way: Example: return ok(fineract().tellers.getTransactionsForCashier(tellerId, cashierId, currencyCode, ...))

You can see examples in the LoanTransactionHelper class, example in line 738

[wkigenyi]

Thanks Alot Adam. I will at this in next few days. I have a commitment away from my machine.

[wkigenyi]

Good Morning @adamsaghy. I tried to follow the example in LoanTransactionHelper, how to I run that test individually to see its results (either in intellij or gradlew)?

[bravosty]

could you help me on how to locate this directory, I am using mifos 23.12 and I have failed to locate the suggested file inorder to make the changes (appuser_id)

[wkigenyi]

@adamsaghy please have a look at this.

[bravosty]

hey @wkigenyi , I am using the mifos 23 not docker version, the one downloaded from sourceforge.net/projects/mifos/ it runs in tomcat, is there any way of fixing that error from this kind of installation? I noticed that the fixes apply to the cloned docker mifos version.

[wkigenyi]

@bravosty you can locate the file fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java and make the change made in this commit.

[bravosty]

[image: image.png] this is the directory of the fineract-provider file in apache tomcat directory

[image: image.png] and this is what I find inside the directory, so I can not see the path you told me to navigate to cause this is the tomcat, local machine installation

On Mon, May 20, 2024 at 10:24 AM Wilfred Kigenyi @.***> wrote:

@bravosty https://github.com/bravosty you can locate the file fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java and make the change made in this commit.

— Reply to this email directly, view it on GitHub https://github.com/apache/fineract/pull/3881#issuecomment-2119835440, or unsubscribe https://github.com/notifications/unsubscribe-auth/A3E5KA3GALQ4XMK7WFUFNGDZDGQJDAVCNFSM6AAAAABHIBJQ4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJZHAZTKNBUGA . You are receiving this because you were mentioned.Message ID: @.***>

[wkigenyi]

@bravosty this has to be done in the source code. https://github.com/apache/fineract

[bravosty]

how then will I add this change to the Mifos installation?

On Mon, May 20, 2024 at 10:43 AM Wilfred Kigenyi @.***> wrote:

@bravosty https://github.com/bravosty this has to be done in the source code. https://github.com/apache/fineract

— Reply to this email directly, view it on GitHub https://github.com/apache/fineract/pull/3881#issuecomment-2119864037, or unsubscribe https://github.com/notifications/unsubscribe-auth/A3E5KAZOBPAFWRXAMNKZM53ZDGSQ5AVCNFSM6AAAAABHIBJQ4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJZHA3DIMBTG4 . You are receiving this because you were mentioned.Message ID: @.***>

[wkigenyi]

@bravosty you would have to build your own jar/war. The alternative is to wait for for this PR to be merged and Fineract 1.10 released.

[bravosty]

Ok, thank you.

On Mon, 20 May 2024, 11:28 am Wilfred Kigenyi, @.***> wrote:

@bravosty https://github.com/bravosty you would have to build your own jar/war. The alternative is to wait for for this PR to be merged and Fineract 1.10 released.

— Reply to this email directly, view it on GitHub https://github.com/apache/fineract/pull/3881#issuecomment-2119941249, or unsubscribe https://github.com/notifications/unsubscribe-auth/A3E5KA5CZNOVJS3Z4ZXOB33ZDGX2VAVCNFSM6AAAAABHIBJQ4KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJZHE2DCMRUHE . You are receiving this because you were mentioned.Message ID: @.***>

[wkigenyi]

@adamsaghy license added

[adamsaghy]

@wkigenyi Please kindly check the failing test case.

If you want to test locally, you can run the fineract backend and then configure your intelliJ to run the test case with IntelliJ Runner: IntelliJ Settings -> Build, Execution, Deployment -> Build Tools -> Gradle -> Run tests using: Intellij IDEA

[wkigenyi]

@adamsaghy I tried to run integration-tests with gradlew :integration-test:test and I was getting this error The origin server did not find a current representation for the target resource or is not willing to disclose that one exists.

[wkigenyi]

@adamsaghy I improved the test

[bravosty]

well done @wkigenyi, I am excited about the final fineract-provider.war file, I love the developments I am seeing, keep it up!

[wkigenyi]

@adamsaghy I need some help here. getTransactionsForCashier(tellerId, cashierId, currencyCode, offset, limit, orderBy, sortOrder) returns an object (Page) and not a List. I have tried to change this but somewhere as the test is executed a List is expected. I am not sure where to fix.

[adamsaghy]

Sorry for causing so much trouble for you. In the swagger file you can change it and then the fineract-client will generate the proper response object. In the TellerApiResource line 305: @ApiResponse(responseCode = "200", description = "OK", content = @Content(array = @ArraySchema(schema = @Schema(implementation = TellerApiResourceSwagger.GetTellersTellerIdCashiersCashiersIdTransactionsResponse.class)))) })

You can give a try to remove these annotations, so there will be no defined response object from the TellerApiResourceSwagger class and hopefully it will generate automatically the proper Response object the fineract-client. Alternatively you can change the annotations to point not as an ArraySchema, rather an (regular) schema (just remove the array parts) and change the TellerApiResourceSwagger.GetTellersTellerIdCashiersCashiersIdTransactionsResponse structure.

But i would say it is just easier to remove those annotations and let Fineract automatically generate the proper Response object ;)

[wkigenyi]

@adamsaghy, done and with this PR I have discovered a lot about Fineract integration testing

[wkigenyi]

@adamsaghy I think I am very close

[wkigenyi]

@adamsaghy

[wkigenyi]

@adamsaghy why are the last two checks here taking more than 24 hours ? 1.Fineract Build & Test - PostgreSQL / build 2.Fineract Docker build - PostgreSQL / build

[adamsaghy]

@adamsaghy why are the last two checks here taking more than 24 hours ? 1.Fineract Build & Test - PostgreSQL / build 2.Fineract Docker build - PostgreSQL / build

Seems they stucked. I have restarted. I should be finished under 50 mins

[wkigenyi]

@adamsaghy I have 1/8 failing org.apache.fineract.client.util.CallFailedRuntimeException: HTTP failed: Request{method=GET, url=https://localhost:8443/fineract-provider/api/v1/tellers/1/cashiers/1/transactions?currencyCode=UGX&offset=0&limit=0, tags={class retrofit2.Invocation=org.apache.fineract.client.services.TellerCashManagementApi.getTransactionsForCashier() [1, 1, UGX, 0, 0, null, null]}}; Response{protocol=http/1.1, code=500, message=, url=https://localhost:8443/fineract-provider/api/v1/tellers/1/cashiers/1/transactions?currencyCode=UGX&offset=0&limit=0}; errorBody: <!doctype html>

[adamsaghy]

@wkigenyi Seems the cashier transaction API does not working with postgres database. Can you please take a look on it? It might requires different SQL for postgres database. You can find database dependent solution in the DatabaseSpecificSQLGenerator.

From the server logs it looks this exception:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from (select  txn.id as txn_id, txn.cashier_id as cashier_id,  txn.txn_type as txn_type,  txn.txn_amount as txn_amount, txn.txn_date as txn_date, txn.txn_note as txn_note,  txn.entity_type as entity_type, txn.entity_id as entity_id, txn.created_date as created_date,  o.id as office_id, o.name as office_name, t.id as teller_id, t.name as teller_name, s.display_name as cashier_name  from m_cashier_transactions txn  left join m_cashiers c on c.id = txn.cashier_id  left join m_tellers t on t.id = c.teller_id  left join m_office o on o.id = t.office_id  left join m_staff s on s.id = c.staff_id  where txn.cashier_id = ? and txn.currency_code = ? and o.hierarchy like ? AND ((case when c.full_day then Date(txn.created_date) between c.start_date AND c.end_date else ( Date(txn.created_date) between c.start_date AND c.end_date ) and ( TIME(txn.created_date) between TIME(c.start_time) AND TIME(c.end_time)) end) or txn.txn_type = 101))  cashier_txns  union (select  sav_txn.id as txn_id, null as cashier_id,  case      when renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge', 'Annual Fee')          then 103      when renum.enum_value in ('withdrawal', 'Waive Charge', 'Interest Posting', 'Overdraft Interest', '')          then 104      else          105  end as txn_type,  sav_txn.amount as txn_amount, sav_txn.transaction_date as txn_date,  concat (renum.enum_value, ', Sav:', sav.id, '-', sav.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note,  'savings' as entity_type, sav.id as entity_id, sav_txn.created_date as created_date,  o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name  from m_savings_account_transaction sav_txn  left join r_enum_value renum on sav_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'savings_transaction_type_enum'  left join m_savings_account sav on sav_txn.savings_account_id = sav.id  left join m_client cl on sav.client_id = cl.id  left join m_office o on cl.office_id = o.id  left join m_appuser user on sav_txn.created_by = user.id  left join m_staff staff on user.staff_id = staff.id  left join m_cashiers c on c.staff_id = staff.id  left join m_payment_detail payDetails on payDetails.id = sav_txn.payment_detail_id  left join m_payment_type payType on payType.id = payDetails.payment_type_id  left join m_account_transfer_transaction acnttrans  on (acnttrans.from_savings_transaction_id = sav_txn.id  or acnttrans.to_savings_transaction_id = sav_txn.id)  where sav_txn.is_reversed = false and c.id = ? and sav.currency_code = ? and o.hierarchy like ? and  sav_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day)  and renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge', 'withdrawal', 'Annual Fee', 'Waive Charge', 'Interest Posting', 'Overdraft Interest')  and (sav_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true)  AND acnttrans.id IS NULL )  union (select  loan_txn.id as txn_id, c.id as cashier_id,  case      when renum.enum_value in ('REPAYMENT_AT_DISBURSEMENT','REPAYMENT', 'RECOVERY_REPAYMENT', 'CHARGE_PAYMENT')          then 103      when renum.enum_value in ('DISBURSEMENT', 'WAIVE_INTEREST', 'WRITEOFF', 'WAIVE_CHARGES')          then 104      else          105  end as cash_txn_type,  loan_txn.amount as txn_amount, loan_txn.transaction_date as txn_date,  concat (renum.enum_value, ', Loan:', loan.id, '-', loan.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note,  'loans' as entity_type, loan.id as entity_id, loan_txn.created_date as created_date,  o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name  from m_loan_transaction loan_txn  left join r_enum_value renum on loan_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'loan_transaction_type_enum'  left join m_loan loan on loan_txn.loan_id = loan.id  left join m_client cl on loan.client_id = cl.id  left join m_office o on cl.office_id = o.id  left join m_appuser user on loan_txn.created_by = user.id  left join m_staff staff on user.staff_id = staff.id  left join m_cashiers c on c.staff_id = staff.id  left join m_payment_detail payDetails on payDetails.id = loan_txn.payment_detail_id  left join m_payment_type payType on payType.id = payDetails.payment_type_id  left join m_account_transfer_transaction acnttrans  on (acnttrans.from_loan_transaction_id = loan_txn.id  or acnttrans.to_loan_transaction_id = loan_txn.id)  where loan_txn.is_reversed = false and c.id = ? and loan.currency_code = ? and o.hierarchy like ? and  loan_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day)  and renum.enum_value in ('REPAYMENT_AT_DISBURSEMENT','REPAYMENT', 'RECOVERY_REPAYMENT','DISBURSEMENT', 'CHARGE_PAYMENT', 'WAIVE_CHARGES', 'WAIVE_INTEREST', 'WRITEOFF')  and (loan_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true)  AND acnttrans.id IS NULL )  union (select  cli_txn.id AS txn_id, c.id AS cashier_id,  case  when renum.enum_value in ('PAY_CHARGE')  then 103  when renum.enum_value in ('WAIVE_CHARGE')  then 104  else  105  end as cash_txn_type,  cli_txn.amount as txn_amount, cli_txn.transaction_date as txn_date,  concat (renum.enum_value, ', Client:', cl.id, '-', cl.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note,  'client' as entity_type, cl.id as entity_id, cli_txn.created_date as created_date,  o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name  from m_client_transaction cli_txn  left join r_enum_value renum on cli_txn.transaction_type_enum = renum.enum_id AND renum.enum_name = 'client_transaction_type_enum'  left join m_client cl on cli_txn.client_id = cl.id  left join m_office o on cl.office_id = o.id  left join m_appuser user on cli_txn.created_by = user.id  left join m_staff staff on user.staff_id = staff.id  left join m_cashiers c on c.staff_id = staff.id  left join m_payment_detail payDetails on payDetails.id = cli_txn.payment_detail_id  left join m_payment_type payType on payType.id = payDetails.payment_type_id  where cli_txn.is_reversed = false and c.id = ? and cli_txn.currency_code = ? and o.hierarchy like ? and cli_txn.transaction_date  between c.start_date and date_add(c.end_date, interval 1 day)  and renum.enum_value in ('PAY_CHARGE', 'WAIVE_CHARGE')  and (cli_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true) )  order by created_date ]
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:112)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1548)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:754)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:767)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:825)
	at org.apache.fineract.infrastructure.core.service.PaginationHelper.fetchPage(PaginationHelper.java:44)
	at org.apache.fineract.organisation.teller.service.TellerManagementReadPlatformServiceImpl.retrieveCashierTransactions(TellerManagementReadPlatformServiceImpl.java:504)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:351)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:713)
	at org.apache.fineract.organisation.teller.service.TellerManagementReadPlatformServiceImpl$$SpringCGLIB$$0.retrieveCashierTransactions(<generated>)
	at org.apache.fineract.organisation.teller.api.TellerApiResource.getTransactionsForCashier(TellerApiResource.java:324)