select 

( case 
when tbl_sale_invoice.status in ('5')  then concat(tbl_sale_invoice.code,'<br>Cancelled')
else  tbl_sale_invoice.code  end ) as  invoice_No_style_10_left

,tbl_sale_invoice.created_on as INV_date_style_15_left
,c.customer_name  AS Customer_style_15_left

 ,( case 
when tbl_sale_invoice.status in ('5')  then '0.00'
else  (case when date(tbl_sale_invoice.created_on ) =  date('{filterDate}') then tbl_sale_invoice.net_total  else '0.00' end )  end ) as INV_amount_sum_2_style_8_right

,( case 
when tbl_sale_invoice.status in ('5')  then '0.00'
else  ifnull(get_invoicePayments(ifnull(tbl_payment_summary_invoices.ps_id,0),tbl_payment_summary_invoices.sale_invoice_id,'CASH','{filterDate}'),0)
end ) as Cash_Payments_sum_2_style_8_right

,( case 
when tbl_sale_invoice.status in ('5')  then '0.00'
else ifnull(get_invoicePayments(ifnull(tbl_payment_summary_invoices.ps_id,0),tbl_payment_summary_invoices.sale_invoice_id,'CHEQUE','{filterDate}'),0)
end ) as Cheque_Payments_sum_2_style_8_right

,( case 
when tbl_sale_invoice.status in ('5')  then '0.00'
else ifnull(get_invoicePayments(ifnull(tbl_payment_summary_invoices.ps_id,0),tbl_payment_summary_invoices.sale_invoice_id,'BANK','{filterDate}'),0)
end ) as Bank_Payments_sum_2_style_7_right

,( case 
when tbl_sale_invoice.status in ('5')  then '0.00'
else ifnull(get_invoicePayments(ifnull(tbl_payment_summary_invoices.ps_id,0),tbl_payment_summary_invoices.sale_invoice_id,'CREDIT_CARD','{filterDate}'),0)
end ) as Credit_cards_sum_2_style_7_right

,( case 
when tbl_sale_invoice.status in ('5')  then '0.00'
else ifnull(get_invoicePayments(ifnull(tbl_payment_summary_invoices.ps_id,0),tbl_payment_summary_invoices.sale_invoice_id,'OUTSTANDING','{filterDate}'),0)
end ) as Assets_sum_2_style_7_right

,( case 
when tbl_sale_invoice.status in ('5')  then '0.00'
else (tbl_sale_invoice.net_total + tbl_sale_invoice.rpt_cash_returns - tbl_sale_invoice.rpt_settled_amount - tbl_sale_invoice.rpt_returns_amount)
end ) as Outstanding_sum_2_style_5_right

,( case 
when tbl_sale_invoice.status in ('5')  then '0.00'
else (ifnull((select 

case 
when tbl_customer_returns.return_type = 'CASH_RETURNS' then 
sum(tbl_customer_returns.amount) *-1 else sum(tbl_customer_returns.amount) end

from tbl_customer_returns
where date(tbl_customer_returns.created_on) = date('{filterDate}')
and tbl_customer_returns.sale_invoice_id = tbl_sale_invoice.id
),0))
end ) as Rtns_sum_2_style_5_right

,( case 
when tbl_sale_invoice.status in ('5')  then '0.00'
else ifnull(tbl_comisions.comis_amount,0)
end ) as Comm_sum_2_style_5_right
 
from tbl_sale_invoice
inner join tbl_customers c on c.customer_id = tbl_sale_invoice.customer_id
left join tbl_comisions on tbl_comisions.sale_invoice_id =  tbl_sale_invoice.id
left join tbl_payment_summary_invoices on tbl_payment_summary_invoices.sale_invoice_id = tbl_sale_invoice.id
where (

date(tbl_sale_invoice.created_on) = date('{filterDate}') or 
tbl_sale_invoice.id in (
select tbl_payment_summary_invoices.sale_invoice_id from tbl_payment_summary_invoices
where date(tbl_payment_summary_invoices.created_on) = date('{filterDate}')
group by tbl_payment_summary_invoices.sale_invoice_id) or

tbl_sale_invoice.id in ( select tbl_customer_returns.sale_invoice_id
from tbl_customer_returns
where date(tbl_customer_returns.created_on) = date('{filterDate}')
)

)
and tbl_sale_invoice.status in ('4','102','5')
group by tbl_sale_invoice.id
-- order by tbl_sale_invoice.id
order by SUBSTRING(tbl_sale_invoice.code, -2),tbl_sale_invoice.id