Jump to:

5521 Posts in 1734 Topics by 1223 members

Customising the CMS

SilverStripe Forums » Customising the CMS » Please help -- SS 3.1 -- export to csv in ReportAdmin

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

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

    Please help -- SS 3.1 -- export to csv in ReportAdmin Link to this post

    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
    }
    ?>

  • MJA
    Avatar
    Community Member
    15 Posts

    Re: Please help -- SS 3.1 -- export to csv in ReportAdmin Link to this post

    Hi people,

    Still not getting anywhere with this.
    Anyone got any ideas?

    Mike Armstrong
    Silvertoad Ltd

  • Willr
    Avatar
    Forum Moderator
    5490 Posts

    Re: Please help -- SS 3.1 -- export to csv in ReportAdmin Link to this post

    ReportAdmin in 3.1 uses a GridField so you should be able to add a GridFieldExportButton component to the grid field to allow the data to be exported. There is a hook to allow you to edit that ReportAdmin interface - https://github.com/silverstripe-labs/silverstripe-reports/blob/master/code/ReportAdmin.php#L180

  • MJA
    Avatar
    Community Member
    15 Posts

    Re: Please help -- SS 3.1 -- export to csv in ReportAdmin Link to this post

    Thanks for the reply Willr, but I have already solved this one myself

    In case anybode else has a similar problem, here is my final solution

    ----------------------

    It turns out that the problem was in the record selection for the export button (core function GridFieldExportButton->generateExportFileData())

    This calls GridField->getManipulatedList(), which returns the full manipulated list (including pagination)

    The trick is to call GridField->getList() instea, which applies the search critria but not the pagination selection

    So, override GridFieldExportButton like this:

    <?php
    if (!class_exists('GridFieldExportAllButton')) {

    class GridFieldExportAllButton extends GridFieldExportButton {
    /**
    * Generate export fields for CSV.
    *
    * @param GridField $gridField
    * @return array
    *
    * ----------
    * replaces definition in GridFieldExportButton
    * same as original except sources data from $gridField->getList() instead of $gridField->getManipulatedList();
    * ----------
    *
    */
    public function generateExportFileData($gridField) {
    $separator = $this->csvSeparator;
    $csvColumns = ($this->exportColumns)
    ? $this->exportColumns
    : singleton($gridField->getModelClass())->summaryFields();
    $fileData = '';
    $columnData = array();
    $fieldItems = new ArrayList();

    if($this->csvHasHeader) {
    $headers = array();

    // determine the CSV headers. If a field is callable (e.g. anonymous function) then use the
    // source name as the header instead
    foreach($csvColumns as $columnSource => $columnHeader) {
    $headers[] = (!is_string($columnHeader) && is_callable($columnHeader)) ? $columnSource : $columnHeader;
    }

    $fileData .= "\"" . implode("\"{$separator}\"", array_values($headers)) . "\"";
    $fileData .= "\n";
    }

    // -----------------------
    //$items = $gridField->getManipulatedList();
    $items = $gridField->getList();
    // -----------------------

    // @todo should GridFieldComponents change behaviour based on whether others are available in the config?
    foreach($gridField->getConfig()->getComponents() as $component){
    if($component instanceof GridFieldFilterHeader || $component instanceof GridFieldSortableHeader) {
    $items = $component->getManipulatedData($gridField, $items);
    }
    }

    foreach($items->limit(null) as $item) {
    $columnData = array();

    foreach($csvColumns as $columnSource => $columnHeader) {
    if(!is_string($columnHeader) && is_callable($columnHeader)) {
    if($item->hasMethod($columnSource)) {
    $relObj = $item->{$columnSource}();
    } else {
    $relObj = $item->relObject($columnSource);
    }

    $value = $columnHeader($relObj);
    } else {
    $value = $gridField->getDataFieldValue($item, $columnSource);
    }

    $value = str_replace(array("\r", "\n"), "\n", $value);
    $columnData[] = '"' . str_replace('"', '\"', $value) . '"';
    }
    $fileData .= implode($separator, $columnData);
    $fileData .= "\n";

    $item->destroy();
    }

    return $fileData;
    }

    } // end class GridFieldExportAllButton

    }
    ?>

    Then, in the custom report class (the one extending SS_Report):L
    Override getReportField()
    Remove componant GridFieldExportButton from the config
    And add componant GridFieldExportAllButton

          public function getReportField() {
             $gridField = parent::getReportField();
             
             $gridField->setModelClass('reportTotalBookings');
             
             $gridConfig = $gridField->getConfig();
             
             $gridConfig->removeComponentsByType('GridFieldPrintButton');
             $gridConfig->removeComponentsByType('GridFieldExportButton');
             
             $gridConfig->addComponents (
                new GridFieldPrintAllButton('buttons-after-left'),
                new GridFieldExportAllButton('buttons-after-left')
             );
             
             return $gridField;
          }

    Also do the same thing with GridFieldPrintButton as this has the same problem

    <?php
    if (!class_exists('GridFieldPrintAllButton')) {

    class GridFieldPrintAllButton extends GridFieldPrintButton {

    /**
    * Export core.
    *
    * @param GridField
    *
    * ----------
    * replaces definition in GridFieldPrintButton
    * same as original except sources data from $gridField->getList() instead of $gridField->getManipulatedList();
    * ----------
    *
    */
    public function generatePrintData(GridField $gridField) {
    $printColumns = $this->getPrintColumnsForGridField($gridField);

    $header = null;

    if($this->printHasHeader) {
    $header = new ArrayList();

    foreach($printColumns as $field => $label){
    $header->push(new ArrayData(array(
    "CellString" => $label,
    )));
    }
    }

    // -----------------------
    //$items = $gridField->getManipulatedList();
    $items = $gridField->getList();
    // -----------------------

    $itemRows = new ArrayList();

    foreach($items as $item) {
    $itemRow = new ArrayList();

    foreach($printColumns as $field => $label) {
    $value = $gridField->getDataFieldValue($item, $field);

    $itemRow->push(new ArrayData(array(
    "CellString" => $value,
    )));
    }

    $itemRows->push(new ArrayData(array(
    "ItemRow" => $itemRow
    )));

    $item->destroy();
    }

    $ret = new ArrayData(array(
    "Title" => $this->getTitle($gridField),
    "Header" => $header,
    "ItemRows" => $itemRows,
    "Datetime" => SS_Datetime::now(),
    "Member" => Member::currentUser(),
    ));

    return $ret;

    }
    } // end class GridFieldPrintAllButton

    }

    ?>

    Finally, to get the correct fields on the report

    These are generated directly from the $summary_fields array on the DataObject

    So, override the DataObject class with a new set of summary fields:

    if (!class_exists('reportTotalBookings')) {
       class reportTotalBookings extends stVehicleBooking {
          public static $summary_fields = 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'
          );
       }   // end class reportTotalBookings
    }

    Then use this class when genrating the the record list

    In sourceRecords() replace:

             // and return the resuts
             return Report_List::makeList('stVehicleBooking', $sql->execute());


    with:

             // and return the resuts
             return Report_List::makeList('reportTotalBookings', $sql->execute());

    384 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.