Hierarchical Report Generator for CakePHP

This is a CakePHP component to create hierarchical reports.

It's not really canonical Cake, because it only works with MySQL.

It basically works, but is rough. It can be used in a non-Cake context, to some extent.

A hierarchical report is just a report with several reports in it, and they are arranged hierarchically. For example, this reports on attendance at an event, for several events, on several different dates. Attendance is grouped by organization, event, and date.

hierarchical_report.php

class HierarchicalReportComponent
{
    /*  sample report
     *  10/10/02
     *     poker-game
     *       moe's - 2
     *       joe's - 3
     *       union - 1
     *     bake-sale
     *       moe's - 1
     *       union - 2
     *  10/11/02
     *     auto-race
     *       union - 3
     *       school - 1
     */
    function report( $spec=Null, $next_selector=Null )
    {
        switch($spec['display_as'])
    {
        case 'section':
            return $this->section_report( $spec, $next_selector ? $next_selector:Null );
        case 'table':
            return $this->table_report( $spec, $next_selector ? $next_selector:Null );
    }
    }

    var $section = 0;
    function section_report( $s, $next_selector = Null )
    {
        $this->section++;
        $sql = $s['sql'];
        if ($next_selector) $sql = preg_replace( '/__next_selector__/', $next_selector, $sql );
        ( $r = mysql_query( $sql ) ) || die( "FAILED: $sql" );
    $o = '';
        while( $d = mysql_fetch_array( $r ) )
        {
            if (isset($s['title'])) 
        $o .= "<h{$this->section}>{$d[$s['title']]}</h{$this->section}>";
            if (isset($s['subreport']))
                $o .= $this->report( $s['subreport'], $d[$s['next_selector']] );
        }
        $this->section--;
        return $o;
    }
    function table_report( $s, $next_selector = Null )
    {
        $hidden_columns = array();
        if (isset($s['hidden_columns'])) $hidden_columns = $s['hidden_columns'];

        $column_headings = array();
        if (isset($s['column_headings'])) $column_headings = $s['column_headings'];

        $links = array();
        if (isset($s['links'])) $links = $s['links'];

        $sorting_columns = array();
        if (isset($s['sorting_columns'])) $sorting_columns = $s['sorting_columns'];

        $sql = $s['sql'];
        if ($next_selector) $sql = preg_replace( '/__next_selector__/', $next_selector, $sql );
        ( $r = mysql_query( $sql ) ) || die( "FAILED: $sql" );
        $first_row = true;
        $o = '<table border=1 cellspacing=0 cellpadding=2>';
        while( $d = mysql_fetch_assoc( $r ) )
        {
            if ($first_row==true )
            {
                $o .= '<tr>';
                foreach($d as $key=>$value) 
                {
                    if (in_array($key, $hidden_columns)) continue;

                    $title = $this->_subst($key,$column_headings);

                    if (in_array($key, $sorting_columns))
                    {
                        $url = $s['sorting_url'];
                        if ($s['sorting_on']==$key)
                        {
                            $url = $this->_merge( $url, array('order_by'=>$key, 'asc_desc'=>($s['sorting_direction']=='DESC'?'ASC':'DESC') ) );
                            $title = "<a href='$url'>$title</a>";
                        }
                        else
                        {
                            $url = $this->_merge( $url, array('order_by'=>$key, 'asc_desc'=>'ASC') );
                            $title = "<a href='$url'>$title</a>";
                        }
                    }
                    $o .= '<th>'.$title.'</th>';
                }
                $o .= '</tr>';
                $first_row = false;
                reset($d);
            }
            $o .= '<tr>';
            foreach($d as $key=>$value)
            {
                if (isset($links[$key]))
                {
                    $link = $this->_merge( $links[$key], $d );
                    $value = '<a href="'.$link.'">'.$value.'</a>';
                }
                if (! in_array($key, $hidden_columns)) $o .= '<td>'.$value.'</td>';
            }
            $o .= '</tr>';
        }
        if (isset($s['summation_sql']))  /* applies only to tables */
        {
            // summation code
            // insert the sql statement right into the query
            $summation_sql = preg_replace( '/__sql__/', $sql, $s['summation_sql'] );
            if (!preg_match('/as .+$/', $summation_sql))
            $summation_sql .= ' AS abcdefghi';
            ( $res = mysql_query( $summation_sql ) ) || die("FAILED: $summation_sql");
            $row = mysql_fetch_assoc( $res );
            $o .= '<tr>';
            foreach($row as $key=>$value) $o .= '<td><b> '.$value.'</b></td>';
            $o .= '</tr>';
        }
        $o .= '</table>';
        return $o;
    }

    function _subst( $key, $substs )
    {
        if (isset($substs[$key])) return $substs[$key];
        return $key;
    }
    function _merge( $template, $substs )
    {
        $keys = array_keys($substs);
        for($i=count($keys)-1;$i>=0;$i--) $keys[$i]='__'.$keys[$i].'__';
        return str_replace( $keys, array_values($substs), $template );
    }

}

</code>
</pre>

Then, into the controller,  you add a report spec. This is the heart of the report.  There are three here.
<pre>
<code>
    function report_orgs()
    {
        list($campaign_id,$event_id) = $this->_get_event_context();

        $spec = array(
            'display_as' => 'table',
            'sql' => 'SELECT distinct a.ORGID,OrgName FROM tblorganizations AS org JOIN attendances AS a ON a.ORGID=org.ORGID ORDER BY OrgName ASC',
            'title' => 'Select an organization',
            'links' => array( 'OrgName'  => '/attendance/report_org_participation/__ORGID__' ),
            'hidden_columns' => array( 'ORGID' ),
            'column_headings' => array( 'OrgName' => 'Organization' ),
        );
        $hrc = new HierarchicalReportComponent();
        $this->set('out',$hrc->report( $spec ));
    }

    function report_org_participation( $ORGID, $order_by='Fname', $asc_desc='ASC' )
    {
        $spec = array(
            'display_as' => 'table',
            'sql' => "SELECT act.Fname, act.Lname, e.Name AS EventName, e.Date FROM attendances AS a JOIN events AS e ON e.CEventID=a.CEventID JOIN tblactivists AS act ON a.FEDID=act.FEDID WHERE a.ORGID=$ORGID ORDER BY $order_by $asc_desc",
            'title' => 'Participation',
            'sorting_columns' => array('Fname','Lname','EventName','Date'),
            'sorting_url' => "/attendance/report_org_participation/$ORGID/__order_by__/__asc_desc__",
            'sorting_on' => $order_by,
            'sorting_direction' => $asc_desc,
        );
        $hrc = new HierarchicalReportComponent();
        $this->set('out',$hrc->report( $spec ));
    }


    function report()
    {
      $spec=array(
          'display_as' => 'section',
          'sql' => 'SELECT distinct `Date` FROM `events` order by `Date`',
          'title' => 'Date',
          'next_selector' => 'Date',
          'subreport' => array(
              'display_as' => 'section',
              'sql' => "SELECT CEventID,`Name`,`Date` FROM `events` WHERE `Date`='__next_selector__'",
              'title' => 'Name',
              'next_selector' => 'CEventID',
              'subreport' => array(
                  'display_as' => 'table',
                  'sql' => 'SELECT att.ORGID,OrgName,COUNT(FEDID) AS persons FROM `attendances` AS att JOIN tblorganizations AS org ON att.ORGID=org.ORGID WHERE CEventID=__next_selector__ GROUP BY att.ORGID',
                  'summation_sql' => "select 'TOTAL', SUM(persons) FROM ( __sql__ )",
                  'next_selector' => 'ORGID',
                  'links' => array( 'persons'  => '/path/to/other/report?id=__ORGID__' ),
                  'hidden_columns' => array( 'ORGID' ),
                  'subreport_column' => 'OrgName',
                  'subreportx' => array(
                          'display_as' => 'table',
                          'indent' => 15
                      )
                  )
              )
          );
          /* feature to add - the next_selectors should accumulate, so any of them can be used in queries and urls... */
      $hrc = new HierarchicalReportComponent();
      $this->set('out',$hrc->report( $spec ));

And last, a view. This just happens to be the one I used, but yours will differ.

<tr><td colspan=2 align=left>
<?php echo $out ?>
</td></tr>

How to program these specs.

Start by creating a single report for the most specific situation. I use PHPMyAdmin for this.

Then create a report that pulls up a list of all the situations.

Then, use the next_selector feature to inject the id from the list into the first report.

You can repeat this again for another layer of hierarchy. The example has three levels: Date, Event, and the tabular report.

The example code also shows you can link between reports, and how an automatic sorting feature can allow column headings to be clickable so users can sort the report.