Jump to:

325 Posts in 200 Topics by 157 members

Migrating a Site to Silverstripe

SilverStripe Forums » Migrating a Site to Silverstripe » Please help -- Having trouble upgrading complex reports from SS2.4 to SS3.1

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

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

Page: 1
Go to End
Author Topic: 426 Views
  • MJA
    Avatar
    Community Member
    15 Posts

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

    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?

  • martimiz
    Avatar
    Forum Moderator
    1067 Posts

    Re: Please help -- Having trouble upgrading complex reports from SS2.4 to SS3.1 Link to this post

    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

  • MJA
    Avatar
    Community Member
    15 Posts

    Re: Please help -- Having trouble upgrading complex reports from SS2.4 to SS3.1 Link to this post

    Thanks Martine, that worked great

  • keskik
    Avatar
    Community Member
    1 Post

    Re: Please help -- Having trouble upgrading complex reports from SS2.4 to SS3.1 Link to this post

    The following should work as well:

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

    426 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.