Available Credit Report

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
siena123
New MMEX User
Posts: 9
Joined: Thu Jan 24, 2013 1:00 am
Are you a spam bot?: No

Available Credit Report

Post by siena123 »

cclimit.png
cclimit.png (15.43 KiB) Viewed 2511 times
SQL (Update account names in line 16.)

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;
Lua (Update account limit.)

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
Template

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>
Attachments
Credit Report.grm
(1.93 KiB) Downloaded 497 times
Post Reply