Joe Joe - 5 months ago 12
PHP Question

Running total from array, starting from oldest date

So I ran into another... hitch. I've really been trying to avoid asking this question, but I apparently can't solve it on my own. I'm trying to display an "account register". The examples I've found show the register in descending order by date. I can't see why that wouldn't annoy the heck out of anyone trying to use it. I want to see my most recent transactions at the top, not the bottom.

In that spirit, I'm trying to resolve this issue. I think I need to get the data from the database in ascending order and then reverse the array. The problem is in displaying the register.

Since it's displayed as a table, it's obviously going to start with the first row. In this case, the first row needs to be the most recent transaction. Since I can't start building the table from the bottom, the data set needs to be in order before I start building the table.

Everything is well and good. It displays exactly as it should, EXCEPT for the running total. Googling this kind of thing gives me ways to do a running total if you're starting at the beginning of the array, which is easy enough.

So here's the code I have so far. I don't want to do the math in the view.

Model

public function get_register($acct_id = NULL) {
$limit = strtotime("now") + 2592000;
if ($acct_id == NULL) {
$sql = "
SELECT
bdgt_trans.trans_amt,
bdgt_trans.clr_flag,
bdgt_trans.memo,
bdgt_trans.trans_date,
bdgt_trans.chk_num,
bdgt_cat.cat_label,
bdgt_payee.disp_name
FROM bdgt_acct
JOIN
bdgt_trans ON bdgt_trans.bdgt_acct_id = bdgt_acct.id
JOIN
bdgt_cat ON bdgt_cat.id = bdgt_trans.bdgt_cat_id
JOIN
bdgt_payee ON bdgt_payee.id = bdgt_trans.payee_id
WHERE
bdgt_acct.acct_default = 1
AND
trans_date <= ?
ORDER BY
trans_date
DESC
";
$query = $this -> db -> query($sql, $limit);
} else {
$sql = "
SELECT
bdgt_trans.trans_amt,
bdgt_trans.clr_flag,
bdgt_trans.memo,
bdgt_trans.trans_date,
bdgt_trans.chk_num,
bdgt_cat.cat_label,
bdgt_payee.disp_name
FROM bdgt_acct
JOIN
bdgt_trans ON bdgt_trans.bdgt_acct_id = bdgt_acct.id
JOIN
bdgt_cat ON bdgt_cat.id = bdgt_trans.bdgt_cat_id
JOIN
bdgt_payee ON bdgt_payee.id = bdgt_trans.payee_id
WHERE
bdgt_acct.id = ?
AND
trans_date <= ?
ORDER BY
trans_date
DESC
";
$query = $this -> db -> query($sql, array($acct_id, $limit));
}
if ($query -> num_rows() > 0) {
foreach ($query->result() AS $row) {
$array[] = get_object_vars($row);
}
return $array;
} else {
return NULL;
}
}


Controller

public function index() {
$acct_id = $this -> uri -> segment(3);
$data['acct_balance'] = $this -> base -> account_balance($acct_id);
$data['register_table'] = $this -> read -> get_register($acct_id);
$data['accounts'] = $this -> base -> acct_list();
$sum = 0;

if (!empty($data['register_table'])) {
foreach ($data['register_table'] AS $ra) {
if (current($data['register_table']) == $data['register_table']) {
$sum = $ra['trans_amt'];
} else {
$sum = $sum += $ra['trans_amt'];
}
$array[] = array('clr_flag' => $ra['clr_flag'], 'trans_date' => $ra['trans_date'], 'disp_name' => $ra['disp_name'], 'chk_num' => $ra['chk_num'], 'cat_label' => $ra['cat_label'], 'trans_amt' => $ra['trans_amt'], $ra['sum'] = $sum);
}
$data['array'] = $array;
}

if ($this -> form_validation -> run() === FALSE) {
$this -> stencil -> paint('finance/view_register', $data);
} else {
redirect(base_url() . 'finance/view_register/' . $acct_id);
}

}


View

if (!empty($array)) {
foreach ($array AS $rt) {
echo '<tr';
if ($rt['clr_flag'] == 0) {
echo ' class="alert alert-warning"';
}
echo '><td>' . date("m/d/Y", $rt['trans_date']) . '</td><td>' . $rt['disp_name'] . '</td><td>';
if ($rt['chk_num'] == 0) {
echo 'N/A';
} else {
echo $rt['chk_num'];
}
echo '</td><td>' . $rt['cat_label'] . '</td>';
echo '<td>' . $rt['trans_amt'] . '</td>';
echo '<td>' . $rt['sum'] . '</td>';
echo '<td>';
echo $rt['cat_label'] != "Manual Adjustment" ? '
<input type="button" value="Clear" onClick="javascript:;" class="btn btn-primary" />
' : '';
echo '<input type="button" value="Edit" onClick="javascript:;" class="btn btn-primary" />
<input type="button" value="Delete" onClick="javascript:;" class="btn btn-primary" />';
echo '</td>';
echo '</tr>';
}
}


I've tried to break things down so it's as easy to understand as possible. In the controller, I'm doing $data['array'] = $array; in case I have to manipulate the $array prior to passing it to the view data. If I don't wind up having to do that, I'll just do $data['array'] in the loop.

[EDIT]

In response to Abdulla's comment, I suppose the very simplified version is that yes, I need to get the running sum of trans_amt. The problem:


  • The initial "running balance" should be the value of trans_amt if it's the first instance in the array.

  • In the next row, trans_amt needs to be added to the current running balance. If it's a negative value, indicating an expense, it will obviously be subtracted. This is easily handled.

  • The running balance should, I think, be calculated prior to display since a) I have to start building the table from the top, so the balance should already be determined and b) I don't want to do any calculations in the view layer, it should be handled in the controller or the model (if absolutely necessary or like a hundred times easier than doing it in the controller).



So basically, the resulting output should be something like:


| Date | Payee | Amount | Balance |
| 01/03/2000 | Burgers-R-Us | -5.00 | 120.00 |
| 01/03/2000 | Check from Grandma | 50.00 | 125.00 |
| 01/02/2000 | Platinum Gym | -25.00 | 75.00 |
| 01/01/2000 | Starting Balance | 100 | 100.00 |


Answer

Edit 1

Add a column to the database to store the current balance value, then operate on that in the reverse chronological order you want. Start with the stored balance as the value for the Balance column in row 1 of your table, then subtract the trans_amt value from the stored balance and use that value in the next row. Loop through all your rows and you should be set.

Original Answer

You could build your table in chronological (oldest -> newest) order and then do an array_reverse() in the controller before passing it to the view to get your desired reverse chronological (newest -> oldest) order.

In your Model you need to change the select statement to ascending order:

...
        ORDER BY
        trans_date
        ASC
...

And in your Controller, call the array_reverse() method before passing it off:

public function index() {
    $acct_id = $this -> uri -> segment(3);
    $data['acct_balance'] = $this -> base -> account_balance($acct_id);
    $data['register_table'] = $this -> read -> get_register($acct_id);
    $data['accounts'] = $this -> base -> acct_list();
    $sum = 0;

    if (!empty($data['register_table'])) {
        foreach ($data['register_table'] AS $ra) {
            if (current($data['register_table']) == $data['register_table']) {
                $sum = $ra['trans_amt'];
            } else {
                $sum = $sum + $ra['trans_amt'];
            }
            $array[] = array('clr_flag' => $ra['clr_flag'], 'trans_date' => $ra['trans_date'], 'disp_name' => $ra['disp_name'], 'chk_num' => $ra['chk_num'], 'cat_label' => $ra['cat_label'], 'trans_amt' => $ra['trans_amt'], $ra['sum'] = $sum);
        }
        $data['array'] = array_reverse($array); // <- Perform an array reverse here to flip the order
    }

    if ($this -> form_validation -> run() === FALSE) {
        $this -> stencil -> paint('finance/view_register', $data);
    } else {
        redirect(base_url() . 'finance/view_register/' . $acct_id);
    }

}

Also, although directly unrelated to the question, you may consider renaming your $sum variable to $balance or $running_bal for readability's sake, but your program, your choice.