<?php

$sql = 'SELECT  week_day, dayname, (gross_output/weeks) gross, (net_output/weeks) net, (payable/weeks) tax, weeks '
        .'FROM (SELECT weekday(trans_date) week_day,'
                .'date_format(trans_date, \'\%W\') dayname,'
                .'sum(gross_output) gross_output,'
                .'sum(net_output) net_output,'
                .'sum(payable) payable,'
                .'count(*) weeks '
            .'FROM (SELECT bt.trans_date trans_date,'
                    .'sum((ttd.net_amount+ttd.amount)*ex_rate) gross_output,'
                    .'sum(ttd.net_amount*ex_rate) net_output,'
                    .'sum(ttd.amount*ex_rate) payable '
                .'FROM 0_bank_trans bt '
                .'INNER JOIN 0_cust_allocations ca '
                    .'ON bt.type = ca.trans_type_from '
                    .'AND bt.trans_no = ca.trans_no_from '
                .'INNER JOIN 0_debtor_trans dt '
                    .'ON dt.type = ca.trans_type_from '
                    .'AND dt.trans_no = ca.trans_no_from '
                .'INNER JOIN 0_trans_tax_details ttd '
                    .'ON ttd.trans_type = ca.trans_type_to '
                    .'AND ttd.trans_no = ca.trans_no_to '
                .'INNER JOIN 0_tax_types tt '
                    .'ON tt.id = ttd.tax_type_id '
                .'GROUP BY bt.trans_date '
                .') days  '
                .'GROUP BY weekday(trans_date), date_format(trans_date, \'\%W\') '
            .') items order by gross_output desc';

