Code: Select all
and p.payeename in ('PayeeI', 'PayeeII') --enter payee names here
and strftime('%Y', t.TRANSDATE)='2013'-- change to proper year
Code: Select all
select
'' as COLOR ,
Payeename,
total(case strftime('%m', TRANSDATE) when '01' then transamount end) as Jan,
total(case strftime('%m', TRANSDATE) when '02' then transamount end) as Feb,
total(case strftime('%m', TRANSDATE) when '03' then transamount end) as Mar,
total(case strftime('%m', TRANSDATE) when '04' then transamount end) as Apr,
total(case strftime('%m', TRANSDATE) when '05' then transamount end) as May,
total(case strftime('%m', TRANSDATE) when '06' then transamount end) as Jun,
total(case strftime('%m', TRANSDATE) when '07' then transamount end) as Jul,
total(case strftime('%m', TRANSDATE) when '08' then transamount end) as Aug,
total(case strftime('%m', TRANSDATE) when '09' then transamount end) as Sep,
total(case strftime('%m', TRANSDATE) when '10' then transamount end) as Oct,
total(case strftime('%m', TRANSDATE) when '11' then transamount end) as Nov,
total(case strftime('%m', TRANSDATE) when '12' then transamount end) as Dec,
round(total(transamount ), 2) as Total
from(
select p.payeename, p.payeeid, t.TRANSDATE,
(case when t.TRANSCODE='Deposit' then t.transamount else -t.transamount end)
* c.baseconvrate as transamount
from checkingaccount_v1 t
inner join accountlist_v1 a on a.accountid=t.accountid
inner join currencyformats_v1 c on a.currencyid=c.currencyid
inner join payee_v1 p on p.payeeid=t.payeeid
where transcode!='Transfer' and a.status!='Closed'
and p.payeename in ('PayeeI', 'PayeeII') --enter payee names here
and strftime('%Y', t.TRANSDATE)='2014'-- change t oproper year
)m
group by payeeid;
Code: Select all
local months = {"Jan", "Feb", "Mar" , "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
local colors = {"#FBD4B4", "#E5B8B7", "#B6DDE8", "#B8CCE4", "#F0EDCE", "#DEE3E7", "#99FF99"};
local data = '';
local count = 0;
local json = '{ fillColor : "rgba(255,255,255,0)", strokeColor : "%s", pointStrokeColor : "#fff", pointColor : "rgba(0,0,0,1)", data : [%s] },';
function handle_record(record)
count = count + 1;
local color = colors[count];
record:set('COLOR', color);
local d = '';
for i=1,12 do
d = d .. record:get(months[i]) .. ',';
record:set(months[i], string.format("%05.2f", record:get(months[i])));
end
data = data .. string.format(json, color, d);
end
function complete(result)
result:set('TREND_DATA', data);
end
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>
<script src = "Chart.js"></script>
<link href = "master.css" rel = "stylesheet" />
</head>
<body>
<div class="container">
<h3>Payees Comparison Report for 2013</h3>
<p><TMPL_VAR TODAY></p>
<div class="row">
<div class="col-xs-1">
<canvas id="reportChart" width="750" height="300"></canvas><br>
<table class="table table-condences">
<thead>
<tr>
<th>Name</th><th>Jan</th><th>Feb</th><th>Mar</th><th>Apr</th><th>May</th>
<th>Jun</th><th>Jul</th><th>Aug</th><th>Sep</th><th>Oct</th><th>Nov</th><th>Dec</th><th>Total</th>
</tr>
</thead>
<tbody>
<TMPL_LOOP NAME=CONTENTS>
<tr bgcolor='<TMPL_VAR "COLOR">'>
<th ><TMPL_VAR Payeename></th>
<td><TMPL_VAR Jan></td>
<td><TMPL_VAR Feb></td>
<td><TMPL_VAR Mar></td>
<td><TMPL_VAR Apr></td>
<td><TMPL_VAR May></td>
<td><TMPL_VAR Jun></td>
<td><TMPL_VAR Jul></td>
<td><TMPL_VAR Aug></td>
<td><TMPL_VAR Sep></td>
<td><TMPL_VAR Oct></td>
<td><TMPL_VAR Nov></td>
<td><TMPL_VAR Dec></td>
<td><TMPL_VAR Total></td>
</tr>
</TMPL_LOOP>
</tbody>
</table>
</div>
<table class="table table-simple">
<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">
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>