Trend Reports

Page 1 of 1
2 posts
Trend Reports

siena123
MMEX Developer

Posts: 12
Joined: Wed Jan 23, 2013 7:00 pm
Image

  • Account
  • SQL (Update account name in line 29.)
    Code: Select all
    select strftime('%Y', TRANSDATE) as YEAR
        , strftime('%m', TRANSDATE) as MONTH
        , '' as DATE
        , total(TRANSAMOUNT)
            + (select INITIALBAL + total(TRANSAMOUNT)
                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') t2
                where t2.ACCOUNTID = t1.ACCOUNTID
                    and t2.STATUS <> 'V'
                    and (strftime('%Y', t2.TRANSDATE) < strftime('%Y', t1.TRANSDATE)
                        or (strftime('%Y', t2.TRANSDATE) = strftime('%Y', t1.TRANSDATE)
                            and strftime('%m', t2.TRANSDATE) < strftime('%m', t1.TRANSDATE)))
            ) as Balance
    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') t1
    inner join ACCOUNTLIST_V1 a on a.ACCOUNTID = t1.ACCOUNTID
    where ACCOUNTNAME = 'Account1'
        and t1.STATUS <> 'V'
    group by YEAR, MONTH
    order by YEAR asc, MONTH asc;

    Lua
    Code: Select all
    local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
    local labels = 'labels : [';
    local data = 'datasets : [{fillColor:"rgba(255,255,255,0)",strokeColor:"rgba(0,0,0,0.5)",pointColor:"rgba(0,0,0,0.5)",pointStrokeColor:"rgba(255,255,255,0)",data:[';

    function handle_record(record)
        local date = months[tonumber(record:get("MONTH"))] .. ' ' .. record:get("YEAR");
        labels = labels .. '"' .. date .. '",';
        record:set("DATE", date);
        local balance = string.format("%.2f", tonumber(record:get("Balance")));
        if tonumber(string.sub(balance,-1)) == 0  and tonumber(string.sub(balance,-2)) ~= 0 then
            data = data .. '\'' .. balance .. '\',';
        else
            data = data .. balance .. ',';
        end
    end

    function complete(result)
        result:set('TREND_DATA', string.sub(labels,1,-2) .. "]," .. string.sub(data,1,-2) .. "]}]");
    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>Account Trend 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="600" height="300"></canvas></td></tr>
                    <tr><td>&nbsp;</td></tr>
                    <tr><td><table class="table">
                    <thead>
                      <tr>
                        <th>Date</th>
                        <th class="text-right">Balance</th>
                      </tr>
                    </thead>
                    <tbody>
                        <TMPL_LOOP NAME=CONTENTS>
                        <tr>
                            <td><TMPL_VAR DATE></td>
                            <td class="money text-right"><TMPL_VAR Balance></td>
                        </tr>
                        </TMPL_LOOP>
                    </tbody>
                    </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 "TREND_DATA">}
        var ctx = document.getElementById("reportChart").getContext("2d");
        var reportChart = new Chart(ctx).Line(data);
    </script>
    </html>

  • Category
  • SQL (Update category and subcategory names in line 21.)
    Code: Select all
    select strftime('%Y', t1.TRANSDATE) as YEAR
        , strftime('%m', t1.TRANSDATE) as MONTH
        , '' as DATE
        , total(t1.TRANSAMOUNT * c1.BASECONVRATE)
        + (select total(split.SPLITTRANSAMOUNT * c2.BASECONVRATE) from CHECKINGACCOUNT_V1 as t2
           inner join ACCOUNTLIST_V1 as a2 on a2.ACCOUNTID = t1.ACCOUNTID
           inner join CURRENCYFORMATS_V1 as c2 on c2.CURRENCYID = a2.CURRENCYID
           inner join SPLITTRANSACTIONS_V1 as split on t2.TRANSID = split.TRANSID
           where strftime('%Y', t2.TRANSDATE) = strftime('%Y', t1.TRANSDATE)
           and strftime('%m', t2.TRANSDATE) = strftime('%m', t1.TRANSDATE)
           and split.CATEGID = cat.CATEGID
           and split.SUBCATEGID = subcat.SUBCATEGID
           and t2.STATUS <> 'V') as AMOUNT
    from CHECKINGACCOUNT_V1 as t1
    inner join ACCOUNTLIST_V1 as a1 on a1.ACCOUNTID = t1.ACCOUNTID
    inner join CURRENCYFORMATS_V1 as c1 on c1.CURRENCYID = a1.CURRENCYID
    inner join CATEGORY_V1 as cat
    on t1.CATEGID = cat.CATEGID
    inner join SUBCATEGORY_V1 as subcat
    on t1.SUBCATEGID = subcat.SUBCATEGID
    where cat.CATEGNAME = 'Food' and subcat.SUBCATEGNAME = 'Groceries'
        and t1.STATUS <> 'V'
    group by year, month
    order by year asc, month asc;

    Lua
    Code: Select all
    local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
    local labels = 'labels : [';
    local data = 'datasets : [{fillColor : "rgba(0,0,0,0.5)", strokeColor : "rgba(0,0,0,0.5)", data : [';
    local average = 0;
    local count = 0;

    function handle_record(record)
        local date = months[tonumber(record:get("MONTH"))] .. ' ' .. record:get("YEAR");
        labels = labels .. '"' .. date .. '",';
        record:set("DATE", date);
        local amount = string.format("%.2f", tonumber(record:get("AMOUNT")));
        if tonumber(string.sub(amount,-1)) == 0  and tonumber(string.sub(amount,-2)) ~= 0 then
            data = data .. '\'' .. amount .. '\',';
        else
            data = data .. amount .. ',';
        end
        average = average + amount;
        count = count + 1;
    end

    function complete(result)
        if (count > 0) then
            average = string.format("%.2f", average / count);
        else
            average = 0;
        end
        result:set("AVERAGE", average);
        result:set('TREND_DATA', string.sub(labels,1,-2) .. "]," .. string.sub(data,1,-2) .. "]}]");
    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>Category Trend 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="600" height="300"></canvas></td></tr>
                    <tr><td>&nbsp;</td></tr>
                    <tr><td><table class="table">
                    <thead>
                      <tr>
                        <th>Date</th>
                        <th class="text-right">Amount</th>
                      </tr>
                    </thead>
                    <tbody>
                        <TMPL_LOOP NAME=CONTENTS>
                        <tr>
                            <td><TMPL_VAR DATE></td>
                            <td class="money text-right"><TMPL_VAR AMOUNT></td>
                        </tr>
                        </TMPL_LOOP>
                    </tbody>
                    <tfoot>
                        <tr class="total">
                            <td>Average</td>
                            <td class="money text-right"><TMPL_VAR AVERAGE></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 "TREND_DATA">}
        var ctx = document.getElementById("reportChart").getContext("2d");
        var reportChart = new Chart(ctx).Bar(data);
      </script>
    </html>

  • Payee
  • SQL (Update payee name in line 20.)
    Code: Select all
    select strftime('%Y', TRANSDATE) as YEAR
        , '' as COLOR
        , total(case when '01' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Jan
        , total(case when '02' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Feb
        , total(case when '03' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Mar
        , total(case when '04' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Apr
        , total(case when '05' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as May
        , total(case when '06' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Jun
        , total(case when '07' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Jul
        , total(case when '08' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Aug
        , total(case when '09' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Sep
        , total(case when '10' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Oct
        , total(case when '11' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Nov
        , total(case when '12' = strftime('%m', TRANSDATE) then TRANSAMOUNT * BASECONVRATE else null end) as Dec
    from CHECKINGACCOUNT_V1
    inner join ACCOUNTLIST_V1 on ACCOUNTLIST_V1.ACCOUNTID = CHECKINGACCOUNT_V1.ACCOUNTID
    inner join CURRENCYFORMATS_V1 on CURRENCYFORMATS_V1.CURRENCYID = ACCOUNTLIST_V1.CURRENCYID
    inner join  PAYEE_V1
    on CHECKINGACCOUNT_V1.PAYEEID = PAYEE_V1.PAYEEID
    where PAYEENAME = 'Payee1'
        and CHECKINGACCOUNT_V1.TRANSCODE='Withdrawal'
        and CHECKINGACCOUNT_V1.STATUS <> 'V'
    group by year
    order by year desc;

    Lua
    Code: Select all
    local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
    local colors = {"#FF6666", "#FFB266", "#FFFF66", "#B2FF66", "#66FF66", "#66FFB2", "#66FFFF", "#66B2FF", "#6666FF", "#B266FF", "#FF66FF", "#FF66B2"};
    local data = '';

    function handle_record(record)
        local color = colors[1 + (tonumber(record:get('YEAR')) % 12)];
        record:set('COLOR', color);
        local datalimit = 12;
        if record:get("YEAR") >= os.date('%Y') then
            for i=12,os.date('%m'),-1 do
                if tonumber(record:get(months[i])) > 0 then
                    datalimit = i;
                    break;
                end
            end
        end
        data = data .. '{fillColor : "rgba(255,255,255,0)", strokeColor : "' .. color .. '", pointColor : "' .. color .. '",pointStrokeColor:"rgba(255,255,255,0)", data : [';
        for i=1,12 do
            if i <= datalimit then
                local amount = string.format("%.2f", tonumber(record:get(months[i])));
                if tonumber(string.sub(amount,-1)) == 0  and tonumber(string.sub(amount,-2)) ~= 0 then
                    data = data .. '\'' .. amount .. '\'';
                else
                    data = data .. amount;
                end
                if i ~= datalimit then
                    data = data .. ',';
                end
            end
        end
        data = data .. ']},';
    end

    function complete(result)
        result:set('TREND_DATA', string.sub(data,1,-1));
    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>Payee Trend 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="750" height="300"></canvas></td></tr>
                    <tr><td>&nbsp;</td></tr>
                    <tr><td><table class="table">
                    <thead>
                      <tr>
                        <th>Year</th>
                        <th class="text-right">Jan</th>
                        <th class="text-right">Feb</th>
                        <th class="text-right">Mar</th>
                        <th class="text-right">Apr</th>
                        <th class="text-right">May</th>
                        <th class="text-right">Jun</th>
                        <th class="text-right">Jul</th>
                        <th class="text-right">Aug</th>
                        <th class="text-right">Sep</th>
                        <th class="text-right">Oct</th>
                        <th class="text-right">Nov</th>
                        <th class="text-right">Dec</th>
                      </tr>
                    </thead>
                    <tbody>
                        <TMPL_LOOP NAME=CONTENTS>
                        <tr>
                            <th bgcolor='<TMPL_VAR "COLOR">'><TMPL_VAR YEAR></th>
                            <td class="money text-right"><TMPL_VAR Jan></td>
                            <td class="money text-right"><TMPL_VAR Feb></td>
                            <td class="money text-right"><TMPL_VAR Mar></td>
                            <td class="money text-right"><TMPL_VAR Apr></td>
                            <td class="money text-right"><TMPL_VAR May></td>
                            <td class="money text-right"><TMPL_VAR Jun></td>
                            <td class="money text-right"><TMPL_VAR Jul></td>
                            <td class="money text-right"><TMPL_VAR Aug></td>
                            <td class="money text-right"><TMPL_VAR Sep></td>
                            <td class="money text-right"><TMPL_VAR Oct></td>
                            <td class="money text-right"><TMPL_VAR Nov></td>
                            <td class="money text-right"><TMPL_VAR Dec></td>
                        </tr>
                        </TMPL_LOOP>
                    </tbody>
                    </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 = {
            labels : ["January","February","March","April","May","June","July",
                          "August","September","October","November","December"],
            datasets : [<TMPL_VAR "TREND_DATA">]
        }
        var ctx = document.getElementById("reportChart").getContext("2d");
        var reportChart = new Chart(ctx).Line(data);
    </script>
    </html>

Attachments
Payee Trend Report.grm
(2.4 KiB) Downloaded 255 times
Category Trend Report.grm
(2.21 KiB) Downloaded 295 times
Account Trend Report.grm
(2.08 KiB) Downloaded 290 times
Re: Trend Reports

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Lua for Account Trend report
Code: Select all
local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
local labels = '';
local data ='';
local json = [[
labels : [%s],
datasets : [{
fillColor:"rgba(255,255,255,0)",
strokeColor:"rgba(0,0,0,0.5)",
pointColor:"rgba(0,0,0,0.5)",
pointStrokeColor:"rgba(255,255,255,0)",
data:[%s] }]
]];

function handle_record(record)
    local date = string.format('%s %s', months[tonumber(record:get("MONTH"))], record:get("YEAR"));
    labels = labels  .. string.format('"%s",', date);
    record:set("DATE", date);
    local balance = string.format("%.2f", tonumber(record:get("Balance")));
    if tonumber(string.sub(balance,-1)) == 0  and tonumber(string.sub(balance,-2)) ~= 0 then
        data = data .. '\'' .. balance .. '\',';
    else
        data = data .. balance .. ',';
    end
end

function complete(result)
    result:set('TREND_DATA', string.format(json, labels, data));
end
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 5 guests