Simply put, I cannot seem to get my custom reports to to create a full export to a csv file.
They will only export the rows shown in the currently displayed grid (i.e. the full manipulated date, including pagination), not the the entire dataset resulting from the sql query.
Furthermore they export the summery fields from the source table (stVehicleBooking) rather than the fields from the sql query.
The dataset is being correctly generated and displayed in the on-screen display grid, it is only the export and print buttons that are incorrect.
I feel sure there is a simple solution to this, but I simply cannot work it out.
Thank you all in advance for any advice you could offer, it would be greatly appreciated.
My current code :-
In TotalBookingsReport.php
<?php
if (!class_exists('stTotalBookingsReport')) {
class stTotalBookingsReport extends SS_Report {
//protected $description = 'Displays the total number of bookings grouped by year';
public function title() {
return 'Total Booking Report - Displays the total number of bookings grouped by year';
}
public function columns() {
return array (
'StartYear' => 'Period',
'NumberOfBookings' => 'Number of Bookings',
'TotalFullPrice' => 'Total Price',
'TotalPaid' => 'Total Amount Paid',
'TotalOwed' => 'Total Owed',
'TotalPrice' => 'Total Base Price (excl Treats)',
'TotalTreats' => 'Total Value of Treats',
'TotalInboundTransfers' => 'Total Inbound Xfers',
'TotalOutboundTransfers' => 'Total Outbound Xfers',
'TotalOutdoorChairs' => 'Total Outdoor Chairs',
'TotalSnowChains' => 'Total Snow Chains',
'TotalGenerator' => 'Total Generator',
'TotalSecondGasBottle' => 'Total Second Gas Bottle',
'TotalStarterPack' => 'Total Starter Pack',
'TotalCaravanClub' => 'Total Caravan Club',
'TotalGasEasyRefill' => 'Total Gas Easy Refill',
'TotalFuelEasyRefill' => 'Total Fuel Easy Refill',
'TotalSatNav' => 'Total Sat Nav'
);
}
public function parameterFields() {
$fields = new FieldList ();
$fields->push (
new DropdownField (
'ReportGrouping',
'Calculate totals for:',
array (
'' => '[ no grouping ]',
'Year' => 'Year',
'YearAndMonth' => 'Year & Month',
'YearAndQtr' => 'Year & Quarter'
)
)
);
$fields->push ($start = new DateField ('StartDate', 'Start Date'));
$fields->push ($end = new DateField ('EndDate', 'End Date'));
$start->setConfig ('showcalendar', true);
$end->setConfig ('showcalendar', true);
return $fields;
}
public function sourceRecords($params, $sort, $limit) {
// define the fields to be included and the sql fragments to build them
$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)'
);
// define a group field
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';
$where = 'Product.ID is not null';
// define the period for the report
if (isset($params['StartDate']) && !empty($params['StartDate'])) {
$where = " and StartDate >= '" . date('Y-m-d', strtotime($params['StartDate'])) . "'";
}
if (isset($params['EndDate']) && !empty($params['EndDate'])) {
$where .= " and EndDate <= '" . date('Y-m-d', strtotime($params['EndDate'])) . "'";
}
// build an sql object
$sql = new SQLQuery();
foreach ($fields as $name => $field)
$sql->selectField ($field, $name);
$sql->setFrom ('stVehicleBooking')
->addLeftJoin ('stQuote', 'stQuote.ID = stVehicleBooking.QuoteID')
->addLeftJoin ('stVehicle', 'stVehicle.ID = stVehicleBooking.VehicleID')
->addLeftJoin ('Product', 'Product.ID = stVehicle.ModelID')
->addWhere ($where)
->setGroupBy ('StartYear')
->setOrderBy ('stVehicleBooking.StartDate');
// and return the resuts
return Report_List::makeList('stVehicleBooking', $sql->execute());
}
} // end class stTotalBookingsReport
}
?>
And in Report_List.php
<?php
if (!class_exists('Report_List')) {
class Report_List {
public static function makeList($dataObject, $results) {
// generate an ArrayList of the SQL results array (from $sqlQuery->execute()) for output
$list = array();
if ($results->numRecords() > 0)
foreach ($results as $item)
$list[] = new $dataObject($item);
return new ArrayList($list);
}
} // end class Report_List
}
?>