Internal Server error when viewing transactions of a cashier

[ahlbherto-gp]

Description

Internal Server error when viewing transactions of a cashier

Steps to Reproduce

Log into mifos Create chart of accounts and define mappings Create a teller Create a cashier Allocate cash View transaction

Expected Behaviour

Display transactions

Actual Behaviour

Pop-up displaying Internal server error

Settings

• Mifos X version: 240411
• Browser used: chrome

Screenshots, if any

[PC-11-00]

@ahlbherto-gp please share the screenshot

[ahlbherto-gp]

There is already a screenshot. Do you need a specific one?

[PC-11-00]

This is a 500 error. This error is coming from backend.

[PC-11-00]

https://github.com/openMF/web-app/assets/76156941/310f3454-5ba0-49ee-b43c-2bf612813560

[ahlbherto-gp]

Yes. When I check logs of the API. I get this exception:

An exception mapping did not successfully produce and processed a response. Logging the exception propagated to the default exception mapper. org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select cash_txn_type, sum(txn_amount) as txn_total from (select * from (select txn.id as txn_id, txn.cashier_id as cashier_id, txn.txn_type as cash_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 (( 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) and txn.currency_code = ? and o.hierarchy like ? ) cashier_txns UNION (select sav_txn.id as txn_id, c.id 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 cash_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.appuser_id = 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 (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 (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 (cli_txn.payment_detail_id IS NULL OR payType.is_cash_payment = true) ) ) txns group by cash_txn_type limit 1000] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:112) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

[ahlbherto-gp]

Please, is this issue being looked in to? There has been no response.

[PC-11-00]

Please make a issue jira ticket in Apache Fineract

[ahlbherto-gp]

Thanks