PNL report grouped by week/month/quarter/year

[John Locke <..hidden..>]

Hi,

I really, really needed a report that gave me a month-by-month cash flow 
(profit and loss) summary for the past two years. And given that I can 
do it much quicker in PHP and have a Drupal instance set up that already 
talks to my LSMB database, I implemented it there.

The core SQL I took out of pnl__income_statement_cash , stripping out 
the business unit and year-end options. Mainly just added a field 
populated with date_trunc. I'm sure this could be added back to the 
stored function with a new parameter.

In any case, here's the code I wrote. Not pretty, but got the job done 
for me pretty quickly... Would be great to get something like this in LSMB.

I would think the first thing would be to modify 
pnl__income_statement_cash and pnl__income_statement_accrual with one 
more parameter, to specify a grouping period. Maybe some other reports, 
too...


/**
 * Helper function to get report data from LSMB 14
 *
 * @param string $dtstart Start date range
 * @param string $dtend End of date range
 * @param string $groupperiod One of null, 'week', 'month', 'quarter', 
'year'
 *
 * @return object with many attached arrays. Following keys are set:
 *   -> cols Array of columns. Each column is an object containing:
 *     -> key used in data array
 *     -> label
 *     -> expense total for this column
 *     -> income total for this column
 *     -> total income - expense for this column
 *   -> rows Array of rows. Each row is an object containing:
 *     -> key used in data array
 *     -> label -- name of account
 *     -> link -- link to use for account
 *     -> accno -- Account Number
 *     -> category -- I or E
 *     -> expense -- total for this row
 *     -> income -- total for this row
 *   -> total income - expense for all rows/columns
 *   -> data array of arrays. Outer array = row, inner array = column
 */
function auriga_dashboard_pnl_cash($dtstart= NULL, $dtend = NULL, 
$groupperiod = NULL) {
  $params = array();
  $query = "SELECT a.id, a.accno, a.description, a.category, ah.id as 
header_id, ah.accno as header_ac
cno,
    ah.description as header_description, ";
  if ($groupperiod) {
    $query .= "date_trunc(:groupperiod, ac.transdate) AS transperiod, ";
    $params[':groupperiod'] = $groupperiod;
  }
  $query .= " CASE WHEN a.category = 'E' THEN -1 ELSE 1 END * 
sum(ac.amount) AS amount,
          at.path
     FROM account a
     JOIN account_heading ah on a.heading = ah.id
     JOIN acc_trans ac ON a.id = ac.chart_id AND ac.approved
     JOIN tx_report gl ON ac.trans_id = gl.id AND gl.approved
     JOIN account_heading_tree at ON a.heading = at.id
     WHERE ac.approved is true ";
  if ($dtstart) {
    $query .= " AND (ac.transdate >= :dtstart) ";
    $params[':dtstart'] = $dtstart;
  }
  if ($dtend) {
    $query .= " AND (ac.transdate <= :dtend) ";
    $params[':dtend'] = $dtend;
  }
  $query .= "  AND a.category IN ('I', 'E')
    GROUP BY a.id, a.accno, ";
  if ($groupperiod) {
    $query .= "transperiod, ";
  }
  $query .= " a.description, a.category,
          ah.id, ah.accno, ah.description, at.path
          ORDER BY a.category DESC, a.accno ASC ";
  if ($groupperiod) {
    $query .= ", transperiod";
  }
  db_set_active('lsmb14');
  $result = db_query($query, $params);
  $items = $result->fetchAll();
  db_set_active();

  // now let's build our arrays...
  $cols = array();
  $rows = array();
  $data = array();
  $total = 0;
  foreach ($items as $item) {
    $type = $item->category == 'I' ? 'income' : 'expense';
    if (empty($rows[$item->accno])) {
      $rows[$item->accno] = array(
        'key' => $item->accno,
        'label' => $item->description,
        'accno' => $item->accno,
        'link' => $item->path,
        'category' => $item->category,
        'expense' => 0,
        'income' => 0,
      );
    }
    $rows[$item->accno][$type] += $item->amount;

    $perioddate = strtotime($item->transperiod);
    switch ($groupperiod) {
    case 'week':
      $period = date('Y',$perioddate).' Week '.date('W', $perioddate);
      break;
    case 'month':
      $period = date('M',$perioddate).' '.date('Y', $perioddate);
      break;
    case 'year':
      $period = date('Y',$perioddate);
      break;
    case 'quarter':
      $period = date('Y',$perioddate).' Q' .ceil(date('m', $perioddate)/3);
      break;
    default:
      $period = $dtstart . ' - ' . $dtend;
    }

    if (empty($cols[$item->transperiod])) {
      $cols[$item->transperiod] = array(
        'key' => $item->transperiod,
        'label' => $period,
        'expense' => 0,
        'income' => 0,
        'total' => 0,
      );
    }
    $cols[$item->transperiod][$type] += $item->amount;

    $data[$item->accno][$item->transperiod] = $item->amount;

  }
  // Now calculate column totals...
  foreach ($cols as $k=>$col) {
    $cols[$k]['total'] = $col['income'] - $col['expense'];
    $total += $cols[$k]['total'];
  }
  $ret = new stdClass();
  $ret->rows = $rows;
  $ret->cols = $cols;
  $ret->total = $total;
  $ret->data = $data;

  // TODO: Move the following to a theme function, and return $ret
  $out = '<h2>Profit and Loss, cash basis</h2>';
  $out .= '<h3>'.$dtstart.' - '.$dtend.'</h3>';

  $out .= '<table class="timereport">';
    $out .= '<thead>';
      $out .= '<tr>';
        $out .= '<th>Account #</th><th>Description</th>';
  foreach ($ret->cols as $col) {
    $out .= '<th>'.$col['label'].'</th>';
  }
  if (count($ret->cols) > 1) {
    $out .= '<th>Total</th>';
  }
      $out .= '</tr>';
    $out .= '</thead>';
    $out .= '<tbody>';
    // flag to get an income subtotal
    $in_income = true;
    foreach ($ret->rows as $r=>$row) {
      if ($in_income && $row['category'] == 'E') {
        // we are no longer in income, do a subtotal row
        $in_income = false;
        $out .= '<tr><th></th><th>Subtotal of Income:</th>';
        foreach ($ret->cols as $k=>$col) {
          $out .= '<th>' . $col['income'] . '</th>';
       }
        $out .= '</tr>';
      }
      $out .= '<tr><td>' . $row['accno'] . '</td><td>'. 
$row['label'].'</td>';
      foreach ($ret->cols as $k=>$col) {
        $out .= '<td>';
        $out .= isset($ret->data[$r][$k]) ? $ret->data[$r][$k] : 0;
        $out .= '</td>';
      }
      $out .= '</tr>';
    }
        $out .= '<tr><th></th><th>Subtotal of Expenses:</th>';
        foreach ($ret->cols as $k=>$col) {
          $out .= '<th>' . $col['expense'] . '</th>';
        }
        $out .= '</tr>';
        $out .= '<tr><th></th><th>Totals, income less expenses:</th>';
        foreach ($ret->cols as $k=>$col) {
          $out .= '<th>' . $col['total'] . '</th>';
        }
        $out .= '</tr>';



    $out .= '</tbody>';
  $out .= '</table>';

  return $out;
}



Cheers,
John Locke
http://www.freelock.com