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>
PAYEE TREND REPORT - PROBLEM
Moderator: Renato
-
- New User
- Posts: 10
- Joined: Sun Sep 28, 2014 10:29 pm
- Are you a spam bot?: No
-
- 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
Replace line 8 of html template to
Code: Select all
<script src="ChartNew.js"></script>