Asset Summary Report

Page 1 of 1
5 posts
Asset Summary Report

siena123
MMEX Developer

Posts: 12
Joined: Wed Jan 23, 2013 7:00 pm
Updated report which uses the current value of the asset.
  • SQL
  • Code: Select all
    SELECT ASSETTYPE, STARTDATE, VALUE, VALUECHANGE, VALUECHANGERATE
    FROM ASSETS_V1
    ORDER BY ASSETTYPE ASC;

  • Lua
  • Code: Select all
    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

  • 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>
        <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>

Attachments
AssetsSummary.grm
(2.5 KiB) Downloaded 248 times
Re: Asset Summary Report

guanlisheng
MMEX Developer

Posts: 279
Joined: Tue Dec 20, 2011 11:58 pm
Location: China
Would we paste one screenshot for this reports as well and commit the report code to github as well?
Re: Asset Summary Report

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
I think will be better to post screenshot here but code on github. grm file may be posted here.
Re: Asset Summary Report

siena123
MMEX Developer

Posts: 12
Joined: Wed Jan 23, 2013 7:00 pm
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>
Re: Asset Summary Report

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
New vertion
assets_totals.png
(21.64 KiB) Downloaded 1026 times
Attachments
Asset_Totals.grm
(1.83 KiB) Downloaded 217 times
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 1 guest

cron