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
------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-devel mailing list
Ledger-smb-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
