Hi Guy, this one has been giving me headaches since before Christmas -- hope somebody out there can point me in the right direction
We are upgrading a site we wrote a couple of years ago in SS2.4.7 to SS3.1
All is working fine except some of the more complex reports which, in ver2, I had written by overloading SS_Report::sourceQuery(). This returns the SQLQuery object used to define the report contents -- code below
SS2 code
public function sourceQuery($params) {
$sql = new SQLQuery();
$fields = array (
'stVehicleBooking.ClassName as ClassName', // must include the class name, even if we are not using it
'stVehicleBooking.Status',
//'year(stVehicleBooking.StartDate) as StartYear',
'count(stVehicleBooking.ClassName) as NumberOfBookings',
'sum(stQuote.TotalPrice) as TotalPrice',
'sum(stQuote.Price) as TotalOwed',
'sum(stQuote.AmountPaid) as TotalPaid',
'sum(stQuote.PriceAirportTransferInbound + stQuote.PriceAirportTransferOutbound + stQuote.PriceOutdoorChairs + stQuote.PriceSnowChains + stQuote.PriceGenerator + stQuote.PriceSecondGasBottle + stQuote.PriceStarterPack + stQuote.PriceCaravanClub + stQuote.PriceGasEasyRefill + stQuote.PriceFuelEasyRefill + stQuote.PriceSatNav) as TotalTreats',
'sum(stQuote.PriceAirportTransferInbound) as TotalInboundTransfers',
'sum(stQuote.PriceAirportTransferOutbound) as TotalOutboundTransfers',
'sum(stQuote.PriceOutdoorChairs) as TotalOutdoorChairs',
'sum(stQuote.PriceSnowChains) as TotalSnowChains',
'sum(stQuote.PriceGenerator) as TotalGenerator',
'sum(stQuote.PriceSecondGasBottle) as TotalSecondGasBottle',
'sum(stQuote.PriceStarterPack) as TotalStarterPack',
'sum(stQuote.PriceCaravanClub) as TotalCaravanClub',
'sum(stQuote.PriceGasEasyRefill) as TotalGasEasyRefill',
'sum(stQuote.PriceFuelEasyRefill) as TotalFuelEasyRefill',
'sum(stQuote.PriceSatNav) as TotalSatNav'
);
if (isset($params['ReportGrouping']) && !empty($params['ReportGrouping'])) {
switch ($params['ReportGrouping']) {
case 'Year':
$fields[] = 'year(stVehicleBooking.StartDate) as StartYear';
break;
case 'YearAndMonth':
$fields[] = "concat_ws('-', monthname(stVehicleBooking.StartDate), year(stVehicleBooking.StartDate)) as StartYear";
break;
case 'YearAndQtr':
$fields[] = "concat_ws('-', year(stVehicleBooking.StartDate), 'Qtr', quarter(stVehicleBooking.StartDate)) as StartYear";
break;
default:
$fields[] = 'stVehicleBooking.StartDate as StartYear';
}
} else
$fields[] = 'stVehicleBooking.StartDate as StartYear';
$sql->select = $fields;
$sql->from = array (
'stVehicleBooking',
'left join stQuote on stQuote.ID = stVehicleBooking.QuoteID'
);
//$sql->where = array ( 'stVehicleBooking.Status = "Booking"' );
$sql->groupby = array ( 'StartYear' );
$sql->orderby = 'stVehicleBooking.StartDate';
return $sql;
}
which all worked perfectly.
(please note the SQL sum expressions on the joined table)
Now, in SS3 reports are defined by overloading SS_Report::sourceRecords() which returns an SS_Sortable list -- seems simple enough.
For the simpler reports this can be handled using by using MyDataObject::get()
It's the more complex ones (like the above example) that are giving me problems. These need to to be selected using SQL because MyDataObject::get()->leftJoin() does not return data from the joined table
So try: build the SQLQuery object, run the query and return the resulting list
SS3 code
public function sourceRecords($params, $sort, $limit) {
$sql = new SQLQuery();
$fields = array (
'NumberOfBookings' => 'count(stVehicleBooking.ClassName)',
'TotalPrice' => 'sum(stQuote.TotalPrice)',
'TotalOwed' => 'sum(stQuote.Price)',
'TotalPaid' => 'sum(stQuote.AmountPaid)',
'TotalTreats' => 'sum(stQuote.PriceAirportTransferInbound + stQuote.PriceAirportTransferOutbound + stQuote.PriceOutdoorChairs + stQuote.PriceSnowChains + stQuote.PriceGenerator + stQuote.PriceSecondGasBottle + stQuote.PriceStarterPack + stQuote.PriceCaravanClub + stQuote.PriceGasEasyRefill + stQuote.PriceFuelEasyRefill + stQuote.PriceSatNav)',
'TotalInboundTransfers' => 'sum(stQuote.PriceAirportTransferInbound)',
'TotalOutboundTransfers' => 'sum(stQuote.PriceAirportTransferOutbound)',
'TotalOutdoorChairs' => 'sum(stQuote.PriceOutdoorChairs)',
'TotalSnowChains' => 'sum(stQuote.PriceSnowChains)',
'TotalGenerator' => 'sum(stQuote.PriceGenerator)',
'TotalSecondGasBottle' => 'sum(stQuote.PriceSecondGasBottle)',
'TotalStarterPack' => 'sum(stQuote.PriceStarterPack)',
'TotalCaravanClub' => 'sum(stQuote.PriceCaravanClub)',
'TotalGasEasyRefill' => 'sum(stQuote.PriceGasEasyRefill)',
'TotalFuelEasyRefill' => 'sum(stQuote.PriceFuelEasyRefill)',
'TotalSatNav' => 'sum(stQuote.PriceSatNav)'
);
if (isset($params['ReportGrouping']) && !empty($params['ReportGrouping'])) {
switch ($params['ReportGrouping']) {
case 'Year':
$fields['StartYear'] = 'year(stVehicleBooking.StartDate)';
break;
case 'YearAndMonth':
$fields['StartYear'] = "concat_ws('-', monthname(stVehicleBooking.StartDate), year(stVehicleBooking.StartDate))";
break;
case 'YearAndQtr':
$fields['StartYear'] = "concat_ws('-', year(stVehicleBooking.StartDate), 'Qtr', quarter(stVehicleBooking.StartDate))";
break;
default:
$fields['StartYear'] = 'stVehicleBooking.StartDate';
}
} else
$fields['StartYear'] = 'stVehicleBooking.StartDate';
foreach ($fields as $name => $field)
$sql->selectField ($field, $name);
$sql->setFrom ('stVehicleBooking')
->addLeftJoin ('stQuote', 'stQuote.ID = stVehicleBooking.QuoteID')
->setGroupBy ('StartYear')
->setOrderBy ('stVehicleBooking.StartDate');
$results = $sql->execute();
$list = ArrayList::create();
//$list = DataList::create('stVehicleBooking');
if ($results->numRecords() > 0)
foreach ($results as $item)
$list->add (new ArrayData ($item));
return $list;
}
This does not seem to work
Returning the results of the query directly (return $results) gives an error (Gridfield expects a SS_Sortable list) -- not particularly surprising but never hurts to try the obvious
However, the SQL does work correctly (I have confirmed this by looping through the $results record set and echoing a couple of the fields)
Creating a DataList and returning that simply produces a list of blank records
Creating an ArrayList returns the the correct dataset but SS throws an uncaught exception error (Object->__call(): the method 'canview' does not exist on 'ArrayData')
So, the big question is
What am I missing here?
Is there any way of converting the results of an SQLQuery() object into a data list that the report generator will understand?
OR
Is there a way of calling MyDataObject::get()->leftJoin() that will return the contents of the joined table?