Calculated Fields in a "phantom" table

[rugcutter]

I'd like to make a tab that is a read-only view of a "phantom" table that simply exposes calculated fields.

My attempt is to make a folder named "calculated_fields" underneath the "tables" folder. The fields.ini would contain a custom SQL that would calculate the values for given columns, and may join to other tables as a source of for those calculations. That fields.ini might look something like this:

Code: Select all

__sql__ = "select
(1+1) as cf_days_used_to_date,
(2+4) as cf_days_remaining,
(3+5) as cf_budget_used_to_date,
(4+2) as cf_budget_remaining"

[cf_project_details_id]
widget:label = "Project Details ID"
order=1

[cf_days_used_to_date]
widget:label = "Days Used to Date"
order=2

[cf_days_remaining]
widget:label = "Days Remaining"
order=3

[cf_budget_used_to_date]
widget:label = "Budget Used to Date"
order=4

[cf_budget_remaining]
widget:label = "Budget Remaining"
order=5


In theory I would think this would work but Xataface expects an actual database table underneath it, whereas my configuration is simply a "phantom" table. I get this error:

Code: Select all

Fatal error: Error performing mysql query to get column information from table 'cf_calculated_fields'. The mysql error returned was : 'Table 'gs_project_dashboard_dev.cf_calculated_fields' doesn't exist'.
On line 477 of file C:\public_html\xataface-1.2.2\Dataface\Table.php in function printStackTrace()
On line 2348 of file C:\public_html\xataface-1.2.2\Dataface\Table.php in function Dataface_Table(cf_calculated_fields,Resource id #19,)
On line 413 of file C:\public_html\xataface-1.2.2\Dataface\Relationship.php in function loadTable(cf_calculated_fields,Resource id #19)
On line 370 of file C:\public_html\xataface-1.2.2\Dataface\Relationship.php in function _normalizeColumns()
On line 105 of file C:\public_html\xataface-1.2.2\Dataface\Relationship.php in function _init(array($pt_project_details_id,1,0,0))
On line 1559 of file C:\public_html\xataface-1.2.2\Dataface\Table.php in function Dataface_Relationship(project_details,CalculatedFields,array($pt_project_details_id,1,0,0))
On line 3097 of fil in C:\public_html\xataface-1.2.2\Dataface\Table.php on line 477


I would prefer to keep the logic in Xataface (in fields.ini) vs. creating a view in the database.

Is there a way around this, to do what I am trying to accomplish?

[shannah]

Currently the Xataface SQL parser doesn't support arithmetic (i..e 1+2 1*2). I'm not sure when I'll be able to get around to adding this. Using a view is one workaround.

[cantlep]

Hi,

Arithmetic sort of works, depending on how it's done. I've used it fine in table delegate classes (the webserver logs *will* error) but the sums are still calculated correctly. An example from my BillData/BillData.php

Code: Select all

// Allow the StandingChargeUnits column to estimate days - Column does NOT exist in DB
// Allow the VAT to be calculated - As above
//Allow Bill Total to be calculated - And again

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";
}


Maybe that will help?