Asset Summary 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

Asset Summary Report

Post by siena123 »

Updated report which uses the current value of the asset.
  • SQL
  • [code] SELECT ASSETTYPE, STARTDATE, VALUE, VALUECHANGE, VALUECHANGERATE FROM ASSETS_V1 ORDER BY ASSETTYPE ASC; [/code]
  • Lua
  • [code] local total = 0; local type = {} local subtotal = {} function is_leap_year(year) local ly = 0; if year % 4 == 0 then if year % 100 == 0 then if year % 400 == 0 then ly = 1; end else ly = 1; end end return ly; end function get_days(month, year) local days = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 } local d = days[tonumber(month)]; if tonumber(month) == 2 and is_leap_year(year) ~= 0 then d = 29; end return d; end function get_years(record) local date = record:get("STARTDATE"); local aYear = string.sub(date,1,4); local aMonth = string.sub(date,6, 7); local aDay = string.sub(date,9); local cYear = os.date('%Y'); local cMonth = os.date('%m'); local cDay = os.date('%d'); local nbrYears = 0; if aYear == cYear then local days = 0; if aMonth == cMonth then days = cDay - aDay; else days = get_days(aMonth, aYear) - aDay; if tonumber(aMonth) + 1 > tonumber(cMonth) - 1 then for i = (aMonth + 1), (cMonth - 1) do days = days + get_days(i, aYear); end end days = days + cDay; end if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then nbrYears = days / 366; else nbrYears = days / 365; end else nbrYears = cYear - aYear; local days = get_days(aMonth, aYear) - aDay; for i = (aMonth + 1), 12 do days = days + get_days(i, aYear); end if tonumber(cMonth) > 1 then for i = 1, (cMonth - 1) do days = days + get_days(i, cYear); end end days = days + cDay; if tonumber(aMonth) < tonumber(cMonth) then nbrYears = nbrYears - 1; elseif aMonth == cMonth and tonumber(aDay) <= tonumber(cDay) then nbrYears = nbrYears - 1; end if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then nbrYears = nbrYears + (days / 366); else nbrYears = nbrYears + (days / 365); end end return nbrYears; end function handle_record(record) local index = 0; local cur_type = record:get("ASSETTYPE"); for t = 1,#type do if cur_type == type[t] then index = t; break; end end if index == 0 then index = #type + 1; type[index] = cur_type; end local value = record:get("VALUE"); local change = record:get("VALUECHANGE"); if change ~= 'None' then local rate = record:get("VALUECHANGERATE") / 100; local years = get_years(record); local scale = 1; if change == 'Appreciates' then scale = math.pow(1 + rate, years); elseif change == 'Depreciates' then scale = math.pow(1 - rate, years); end value = value * scale; end total = total + value; if subtotal[index] then subtotal[index] = subtotal[index] + value; else subtotal[index] = value; end end function complete(result) result:set("Current_Total", total); local colors = {"#FBD4B4", "#E5B8B7", "#B6DDE8", "#B8CCE4", "#F0EDCE", "#DEE3E7", "#99FF99"}; local rows = ''; local data = ''; for t = 1,#type do local value = string.format("%.2f", subtotal[t]); rows = rows .. '<tr bgcolor=' .. colors[t] .. '><td>' .. type[t] .. '</td><td class="money text-right">' .. value .. '</td></tr>'; data = data .. '{value: ' .. value .. ', color:"' .. colors[t] .. '", label : "' .. type[t] ..'", labelColor : "black", labelFontSize : "12", labelAlign : "center"},\n'; end result:set("SubTotals", rows); result:set("ASSET_DATA", data); 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> <SCRIPT><TMPL_INCLUDE NAME="Chart.js"></SCRIPT> </head> <body> <div class="container"> <h3>Asset 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-condensed"> <tr><td><canvas id="reportChart" width="300" height="300"></canvas></td></tr> <tr><td>&nbsp;</td></tr> <tr><td><table class="table"> <thead> <tr> <th>Type</th> <th class="text-right">Value</th> </tr> </thead> <tbody> <TMPL_VAR SubTotals> </tbody> <tfoot> <tr class="total"> <td>Total</td> <td class="money text-right"><TMPL_VAR Current_Total></td> </tr> </tfoot> </table></td></tr></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"> <!-- Format numbers --> 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);} <!-- Chart --> var data = [ <TMPL_VAR "ASSET_DATA"> ]; var options = { animationEasing: 'easeOutQuint' }; var ctx = document.getElementById("reportChart").getContext("2d"); var reportChart = new Chart(ctx).Pie(data, options); </script> </html> [/code]
Attachments
AssetsSummary.grm
(2.5 KiB) Downloaded 558 times
guanlisheng
MMEX Developer
Posts: 375
Joined: Wed Dec 21, 2011 5:58 am
Are you a spam bot?: No
Location: China
Contact:

Re: Asset Summary Report

Post by guanlisheng »

Would we paste one screenshot for this reports as well and commit the report code to github as well?
Lisheng Guan,
Developer of MoneyManagerEX (http://moneymanagerex.org)
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Asset Summary Report

Post by Nikolay »

I think will be better to post screenshot here but code on github. grm file may be posted here.
siena123
New MMEX User
Posts: 9
Joined: Thu Jan 24, 2013 1:00 am
Are you a spam bot?: No

Re: Asset Summary Report

Post by siena123 »

Updated template to show currency symbol(s).

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>
    <SCRIPT><TMPL_INCLUDE NAME="Chart.js"></SCRIPT>
</head>
<body>
    <div class="container">
        <h3>Asset 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-condensed">
                <tr><td><canvas id="reportChart" width="300" height="300"></canvas></td></tr>
                <tr><td>&nbsp;</td></tr>
                <tr><td><table class="table">
                <thead>
                  <tr>
                    <th>Type</th>
                    <th class="text-right">Value</th>
                  </tr>
                </thead>
                <tbody>
                    <TMPL_VAR SubTotals>
                </tbody>
                <tfoot>
                     <tr class="total">
                        <td>Total</td>
                        <td class="money text-right"><TMPL_VAR Current_Total></td>
                    </tr>
                </tfoot>
                </table></td></tr></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">
    <!-- Format numbers -->
    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>";}
    <!-- Chart -->
    var data = [
        <TMPL_VAR "ASSET_DATA">
    ];
    var options = {
        animationEasing: 'easeOutQuint'
    };
    var ctx = document.getElementById("reportChart").getContext("2d");
    var reportChart = new Chart(ctx).Pie(data, options);
</script>
</html>
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Asset Summary Report

Post by Nikolay »

New vertion
assets_totals.png
assets_totals.png (21.64 KiB) Viewed 4284 times
Attachments
Asset_Totals.grm
(1.83 KiB) Downloaded 527 times
Post Reply