adamsaghy commented on PR #4102:
URL: https://github.com/apache/fineract/pull/4102#issuecomment-2427143457
@Jaswanth-Sriram-Veturi Seems the query is not correct for postgres:
`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.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 (s
av_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_ty
pe_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: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.organisation.teller.service.TellerManagementReadPlatformServiceImpl.retrieveCashierTransactionsWithSummary(TellerManagementReadPlatformServiceImpl.java:415)`
`Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or
near "txn"
Position: 900`
Would you mind to take a look at this?
--
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]
