Account Summary Report
Posted: Mon Mar 17, 2014 12:43 am
- SQL [code] select a.ACCOUNTNAME, c.BASECONVRATE, c.PFX_SYMBOL, c.SFX_SYMBOL, (select INITIALBAL + total(TRANSAMOUNT) from (select ACCOUNTID, STATUS, (case when TRANSCODE = 'Deposit' then TRANSAMOUNT else -TRANSAMOUNT end) as TRANSAMOUNT from CHECKINGACCOUNT_V1 union all select TOACCOUNTID, STATUS, TOTRANSAMOUNT from CHECKINGACCOUNT_V1 where TRANSCODE = 'Transfer') as t where t.ACCOUNTID = a.ACCOUNTID and t.STATUS <> 'V') as Balance from ACCOUNTLIST_V1 as a inner join CURRENCYFORMATS_V1 as c on c.CURRENCYID = a.CURRENCYID where a.ACCOUNTTYPE in ('Checking', 'Term') and a.STATUS = 'Open' group by a.ACCOUNTNAME order by a.ACCOUNTNAME asc; [/code]
- Lua [code] local total = 0; function handle_record(record) local bal = string.format("%.2f", record:get('Balance')); local base = bal * record:get("BASECONVRATE"); record:set("Balance", record:get('PFX_SYMBOL') .. bal .. record:get('SFX_SYMBOL')); record:set("Base", base); total = total + base; end function complete(result) result:set("Total", total); end [/code]
- Template [code] <!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>Account Summary Report</h3> <p><TMPL_VAR TODAY></p> <div class="row"> <div class="col-xs-2"></div> <div class="col-xs-8"> <table class="table"> <thead> <tr> <th>Account Name</th> <th class="text-right">Balance</th> <th class="text-right">Base Currency</th> </tr> </thead> <tbody> <TMPL_LOOP NAME=CONTENTS> <tr> <th><TMPL_VAR ACCOUNTNAME></th> <td class="text-right"><TMPL_VAR Balance></td> <td class="money text-right"><TMPL_VAR Base></td> </tr> </TMPL_LOOP> </tbody> <tfoot> <tr class="total"> <td>Total</td> <td></td> <td class="money text-right"><TMPL_VAR Total></td> </tr> </tfoot> </table> </div> <table class="table"> <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 = "<TMPL_VAR PFX_SYMBOL>" + currency(elements[i].innerHTML) + "<TMPL_VAR SFX_SYMBOL>";} </script> </html> [/code]