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.

Migrating a Site to Silverstripe /

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

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

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


Reply


4 Posts   662 Views

Avatar
MJA

Community Member, 16 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, 1132 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, 16 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));
}