SQL ([color=#FF0000]Update account name in line 29.[/color])
[code]
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;
[/code]
Lua
[code]
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
[/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>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> </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>
[/code]
Category
SQL ([color=#FF0000]Update category and subcategory names in line 21.[/color])
[code]
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;
[/code]
Lua
[code]
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
[/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>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> </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>
[/code]
Payee
SQL ([color=#FF0000]Update payee name in line 20.[/color])
[code]
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;
[/code]
Lua
[code]
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
[/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>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> </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>
[/code]
You do not have the required permissions to view the files attached to this post.
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