Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We're retiring the forums!

The SilverStripe forums have passed their heyday. They'll stick around, but will be read only. We'd encourage you to get involved in the community via the following channels instead:

Migrating a Site to Silverstripe /

What you need to know when migrating your existing site to SilverStripe.

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

Please help -- Having trouble upgrading complex reports from SS2.4 to SS3.1


Go to End


4 Posts   2161 Views

Avatar
MJA

Community Member, 21 Posts

7 January 2014 at 5:32am

Edited: 07/01/2014 5:47am

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?

Avatar
martimiz

Forum Moderator, 1391 Posts

7 January 2014 at 8:38am

It looks like the GridField just really wants to work with DataObjects - hence the canView problem. I've been able to work around it by creating an ArrayData extension like this:

class Report_ArrayData extends ArrayData {
	public function canView($member = null) {
		return true;           //<-- hmm, check some permission
	}
} 

And then in sourceRecords:

		...
		$sqlResult = $sqlQuery->execute();
		$returnSet = new ArrayList();
		foreach($sqlResult as $result) {
			$returnSet->push(new Report_ArrayData($result));
		}
		return $returnSet;

OK, it feels like a hack, but... the ArrayData extension can be made as safe as any DataObject. You could have it call the canView() method of the main DataObject (by using a singleton). So if that really is the only problem (I've found no other yet), this might not be such a bad hack :)

Martine

Avatar
MJA

Community Member, 21 Posts

7 January 2014 at 10:09pm

Thanks Martine, that worked great

Avatar
keskik

Community Member, 1 Post

30 January 2014 at 4:30am

The following should work as well:

foreach($sqlResult as $result) {
    $returnSet->push(new DataObject($result)); 
}