Topic: ERROR IN MYSQL WHEN A CUSTOMER PAYMENT IS VOID
I found a problem with error message after I voided a customer payment. This problem has been encountered by other users:
Ref: https://frontaccounting.com/punbb/viewtopic.php?id=4892
When attempt to open a voided customer payment by clicking on the Customer Payment #, the following error message appeared:
DATABASE ERROR : no debtor trans found for given params
sql that failed was : SELECT trans.*,ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,cust.name AS DebtorName, cust.address, cust.curr_code, cust.tax_id, com.memo_,bank_act,0_bank_accounts.bank_name, 0_bank_accounts.bank_account_name, 0_bank_accounts.account_type AS BankTransType, 0_bank_accounts.bank_curr_code, 0_bank_trans.amount as bank_amount FROM 0_debtor_trans trans LEFT JOIN 0_comments com ON trans.type=com.type AND trans.trans_no=com.id LEFT JOIN 0_shippers ON 0_shippers.shipper_id=trans.ship_via, 0_debtors_master cust, 0_bank_trans, 0_bank_accounts WHERE trans.trans_no='22' AND trans.type='12' AND trans.debtor_no=cust.debtor_no AND 0_bank_trans.trans_no ='22' AND 0_bank_trans.type=12 AND 0_bank_trans.amount != 0 AND 0_bank_accounts.id=0_bank_trans.bank_act
I managed to find a line in sales/includes/db/cust_trans_db.inc that somehow caused the database error.
if ($trans_type == ST_CUSTPAYMENT || $trans_type == ST_BANKDEPOSIT) {
// it's a payment so also get the bank account
$sql .= " AND ".TB_PREF."bank_trans.trans_no =".db_escape($trans_id)."
AND ".TB_PREF."bank_trans.type=$trans_type
AND ".TB_PREF."bank_trans.amount != 0 <<== the "!=" is causing the issue
AND ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act ";
As stated above, this != 0 is causing the problem. If I remove "!", then the voided payment will appear normal. But the normal customer payment will have the same error.
Due to my limited php knowledge, I am not able to fix it.
I hope this can be fixed as soon as possible or it looks ugly when the user click on the transaction.
Thanks!
/Mark
