Lots of sums (SOLVED)

[cantlep]

Hiya,

In my "BillData" table, I'm trying to do a few calculations based on data inputed. At the moment, I'm using "real" columns in the database but eventually I'd like these to be auto calculated and not actually appear in any other view other than "list". However, for now, just to get the logic right, I'm doing it with "real" columns. The situation is this:

I have a site (i.e. shop 1) and that site can be charged for a product (let's say the charge is £1000). 50% of that site might have to pay 5% VAT and the other 50% would have to pay 17.5% VAT (or any combination thereof - i.e. 100% of the site might need to pay 17.5% VAT or there might be a 35% / 65% split).

Here's what I have so far.

TotalCostExVAT = The charged amount (in this case £1000)
MPAN1LowerVAT% = the percentage of the site that will be charged the lower VAT rate
MPAN1HigherVAT% = the percentage of the site that will be charged the Higher VAT rate
LowBillDataVATRates = 5%
HighBillDataVATRates = 17.5%

In my BillData.php, I have this:

Code: Select all

function SUBANS1__display(&$record){
        return money_format('%i',$record->val('TotalCostExVAT')*$record->val('MPAN1LowerVAT%')/(100)*$record->val('LowBillDataVATRates'));
}

function SUBANS2__display(&$record){
        return money_format('%i',$record->val('TotalCostExVAT')*$record->val('MPAN1HigherVAT%')/(100)*$record->val('HighBillDataVATRates'));
}

function VAT__display(&$record){
        return money_format('%i',$record->val('SUBANS1')+$record->val('SUBANS2'));
}

function BILLTOTAL__display(&$record){
        return money_format('%i',$record->val('TotalCostExVAT')+$record->val('VAT'));
}


The first issue is that becuase SUBANS1 and SUBANS2 are technically "NULL" in the database (despite me overriding what's displayed with the fieldname__display function) the VAT field always displays as 0.00. Is there a way I can get this to display properly? Either by inserting data into the DB on the fly so that SUBANS1 and 2 have "real" values and therefore the VAT sum works or by doing this with some other method.

As I said at the start, what I'd really like is to not have the SUBANS1, 2, VAT, stuff displayed as "real" columns but instead be generated on the fly with some sort of __sql__ function.

I'd really appreciated some help.

Thanks

Paul

[shannah]

Since you're doing the calculation the display method, the Val method won't include the calculation. Better to provide separate methods to do the calculations and then call those methods from each of the display methods.

[cantlep]

Cheers...any chance you could provide an example I can follow?

Thanks

[cantlep]

Hiya,

Did it this way instead.

Code: Select all

function __sql__(){
return "select t.*, datediff(t.SupplyPeriodEnd,t.SupplyPeriodStart) as StandingChargeUnits, t.TotalCostExVAT*t.MPAN1LowerVATPerc/100*t.LowBillDataVATRates+t.TotalCostExVAT*t.MPAN1HigherVATPerc/100*t.HighBillDataVATRates as VAT, t.TotalCostExVAT*t.MPAN1LowerVATPerc/100*t.LowBillDataVATRates+t.TotalCostExVAT*t.MPAN1HigherVATPerc/100*t.HighBillDataVATRates + t.TotalCostExVAT AS BillTotal from BillData t";
}