Code: Select all
select a.ACCOUNTNAME
, '' as Credit
, '' as Available
, total(transamount) + INITIALBAL as Balance
, (INITIALBAL + total(transamount))*c.BASECONVRATE as BaseBal
from
(select accountid, TRANSDATE, STATUS,
(case when TRANSCODE='Deposit' then transamount else -transamount end) as transamount
from checkingaccount_v1
union all
select toaccountid, TRANSDATE, STATUS, totransamount
from checkingaccount_v1
where transcode='Transfer') t
inner join accountlist_v1 a on a.accountid=t.accountid
inner join currencyformats_v1 c on a.currencyid=c.currencyid
where ACCOUNTNAME in ('Visa', 'Master Card', 'Discover Card')
and t.STATUS<>'V'
group by a.ACCOUNTID
order by ACCOUNTNAME;
Code: Select all
local limits = {0,0,0};
local index = 1;
local base_total = 0;
local balance_total = 0;
local available_total = 0;
function handle_record(record)
local credit = limits[index];
local available = credit + record:get("Balance");
record:set("Credit", credit);
record:set("Available", available);
index = index + 1;
base_total = base_total + record:get("BaseBal");
balance_total = balance_total + record:get("Balance");
available_total = available_total + available;
end
function complete(result)
result:set("Base_Total", base_total);
result:set("Balance_Total", balance_total);
result:set("Available_Total", available_total);
end
Code: Select all
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title><TMPL_VAR REPORTNAME></title>
<STYLE><TMPL_INCLUDE NAME="master.css"></STYLE>
</head>
<body>
<div class="container">
<h3>Credit Report</h3>
<p><TMPL_VAR TODAY></p>
<div class="row">
<div class="col-xs-2"></div>
<div class="col-xs-8">
<table class="table table-condences">
<thead>
<tr>
<th>Account Name</th>
<th style="text-align:center">Base Currency</th>
<th style="text-align:center">Balance</th>
<th style="text-align:center">Credit Limit</th>
<th style="text-align:center">Available Credit</th>
</tr>
</thead>
<tbody>
<TMPL_LOOP NAME=CONTENTS>
<tr>
<th><TMPL_VAR ACCOUNTNAME></th>
<td class="money" style="text-align:right"><TMPL_VAR BaseBal></td>
<td class="money" style="text-align:right"><TMPL_VAR Balance></td>
<td class="money" style="text-align:right"><TMPL_VAR Credit></td>
<td class="money" style="text-align:right"><TMPL_VAR Available></td>
</tr>
</TMPL_LOOP>
</tbody>
<tfoot>
<tr class="total">
<td>Total</td>
<td class="money" style="text-align:right"><TMPL_VAR Base_Total></td>
<td class="money" style="text-align:right"><TMPL_VAR Balance_Total></td>
<td></td>
<td class="money" style="text-align:right"><TMPL_VAR Available_Total></td>
</tr>
</tfoot>
</table>
</div>
<table class="table table-simple">
<tbody>
<TMPL_LOOP ERRORS>
<tr><td><h3><span class="label label-danger">Error</span></h3></td></tr>
<tr><td><TMPL_VAR ERROR></td></tr>
</TMPL_LOOP>
</tbody>
</table>
</div>
</div>
</body>
<script type="text/javascript">
function currency(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(2);}
var elements= document.getElementsByClassName("money");
for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = currency(elements[i].innerHTML);}
</script>
</html>