adamsaghy commented on PR #3881:
URL: https://github.com/apache/fineract/pull/3881#issuecomment-2137810696
@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 TIM
E(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 O
R 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 lo
an 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_detai
l_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)
--
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]
