PAYEE TREND REPORT - PROBLEM

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

Moderator: Renato

Post Reply
fabianfp
New MMEX User
Posts: 10
Joined: Sun Sep 28, 2014 10:29 pm
Are you a spam bot?: No

PAYEE TREND REPORT - PROBLEM

Post by fabianfp »

I have a problem with the PAYEE TREND REPORT. After the change of year (2014 to 2015) the graphic stopped working. I can´t find the problem. ¡¿Can you help me? The table shows the correct values but not graphic the result.

Thanks !!!!!!!!!!!!!1


SQL:


-- TODO: Update payee name in line 19. 
select strftime('%Y', t.TRANSDATE) as YEAR
    , total(case when '01' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Enero
    , total(case when '02' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Febrero
    , total(case when '03' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Marzo
    , total(case when '04' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Abril
    , total(case when '05' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Mayo
    , total(case when '06' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Junio
    , total(case when '07' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Julio
    , total(case when '08' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Agosto
    , total(case when '09' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Septiembre
    , total(case when '10' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Octubre
    , total(case when '11' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Noviembre
    , total(case when '12' = strftime('%m', t.TRANSDATE) then t.TRANSAMOUNT * c.BASECONVRATE else null end) as Diciembre
from CHECKINGACCOUNT_V1 as t
inner join ACCOUNTLIST_V1 as a on a.ACCOUNTID = t.ACCOUNTID
inner join CURRENCYFORMATS_V1 as c on c.CURRENCYID = a.CURRENCYID
inner join  CATEGORY_V1 as p on t.CATEGID = p.CATEGID
where p.CATEGNAME = 'Auto'
    and t.TRANSCODE='Withdrawal'
    and t.STATUS <> 'V'
group by year
order by year desc;


LUA:


local months = {"Enero", "Febrero", "Marzo" , "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre"};
local colors = {"#FF6666", "#FFB266", "#FFFF66", "#B2FF66", "#66FF66", "#66FFB2", "#66FFFF", "#21610B", "#0B615E", "#210B61", "#000000", "#FF66B2"};
local data = '';
local json = [[
    {fillColor : "rgba(255,255,255,0)",
    strokeColor : "%s",
    pointColor : "%s",
    pointStrokeColor : "rgba(255,255,255,0)",
    data : [
    ]];
function handle_record(record)
    local color = colors[1 + (tonumber(record:get('YEAR')) % #colors)];
    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)) > 0 then
                datalimit = i;
                break;
            end
        end
    end
    data = data .. string.format(json, color, color);
    for i=1,12 do
        if i <= datalimit then
            local amount = string.format("%.2f", tonumber(record:get(months)));
            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:




<!DOCTYPE html>
<!-- TODO: Update payee name in line 12 -->
<html>
<head>
    <meta charset="UTF-8">
    <title><TMPL_VAR REPORTNAME></title>
    <link href="master.css" rel="stylesheet">
    <script src="Chart.js"></script>
</head>
<body>
    <div class="container">
        <h3>Payee Trend Report (Auto)</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  class="text-center"><canvas id="reportChart" width="750" height="300"></canvas></td></tr>
                <tr><td> </td></tr>
                <tr><td><table class="table">
                <thead>
                  <tr>
                    <th>Year</th>
                    <th class="text-right">Enero</th>
                    <th class="text-right">Febrero</th>
                    <th class="text-right">Marzo</th>
                    <th class="text-right">Abril</th>
                    <th class="text-right">Mayo</th>
                    <th class="text-right">Junio</th>
                    <th class="text-right">Julio</th>
                    <th class="text-right">Agosto</th>
                    <th class="text-right">Septiembre</th>
                    <th class="text-right">Octubre</th>
                    <th class="text-right">Noviembre</th>
                    <th class="text-right">Diciembre</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 Enero></td>
                        <td class="money text-right"><TMPL_VAR Febrero></td>
                        <td class="money text-right"><TMPL_VAR Marzo></td>
                        <td class="money text-right"><TMPL_VAR Abril></td>
                        <td class="money text-right"><TMPL_VAR Mayo></td>
                        <td class="money text-right"><TMPL_VAR Junio></td>
                        <td class="money text-right"><TMPL_VAR Julio></td>
                        <td class="money text-right"><TMPL_VAR Agosto></td>
                        <td class="money text-right"><TMPL_VAR Septiembre></td>
                        <td class="money text-right"><TMPL_VAR Octubre></td>
                        <td class="money text-right"><TMPL_VAR Noviembre></td>
                        <td class="money text-right"><TMPL_VAR Diciembre></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.innerHTML = "<TMPL_VAR PFX_SYMBOL>" + currency(elements.innerHTML) + "<TMPL_VAR SFX_SYMBOL>";}
    <!-- Chart -->
    var data = {
        labels : ["Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio",
                      "Agosto","Septiembre","Octubre","Noviembre","Diciembre"],
        datasets : [<TMPL_VAR "TREND_DATA">]
}
    var ctx = document.getElementById("reportChart").getContext("2d");
    var reportChart = new Chart(ctx).Line(data);
</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: PAYEE TREND REPORT - PROBLEM

Post by Nikolay »

Replace line 8 of html template to

Code: Select all

    <script src="ChartNew.js"></script>
Post Reply