Code: Select all
select
case t.subcateg when -1 then ca.categname else ca.categname ||':'||sc.subcategname end category
, t.transid, t.categ, t.subcateg
, total( case strftime('%m', date('now', 'start of month','-11 month','localtime')) when month then amount end ) as twe
, total( case strftime('%m', date('now', 'start of month','-10 month','localtime')) when month then amount end ) as ele
, total( case strftime('%m', date('now', 'start of month','-9 month','localtime')) when month then amount end ) as ten
, total( case strftime('%m', date('now', 'start of month','-8 month','localtime')) when month then amount end ) as nin
, total( case strftime('%m', date('now', 'start of month','-7 month','localtime')) when month then amount end ) as egh
, total( case strftime('%m', date('now', 'start of month','-6 month','localtime')) when month then amount end ) as sev
, total( case strftime('%m', date('now', 'start of month','-5 month','localtime')) when month then amount end ) as six
, total( case strftime('%m', date('now', 'start of month','-4 month','localtime')) when month then amount end ) as fiv
, total( case strftime('%m', date('now', 'start of month','-3 month','localtime')) when month then amount end ) as fou
, total( case strftime('%m', date('now', 'start of month','-2 month','localtime')) when month then amount end ) as thr
, total( case strftime('%m', date('now', 'start of month','-1 month','localtime')) when month then amount end ) as two
, total( case strftime('%m', date('now', 'start of month','-0 month','localtime')) when month then amount end ) as one
from(
select
strftime('%m', TRANSDATE) as month
, c.transid, cf.BaseConvRate
, c.accountid, c.transcode
, case ifnull(c.categid, -1) when -1 then s.categid else c.categid end as categ
, case ifnull(c.subcategid,-1) when -1 then ifnull(s.subcategid,-1) else ifnull(c.subcategid,-1) end as subcateg
, c.payeeid
, sum((case c.categid when -1 then splittransamount else transamount end)
* (case transcode when 'Withdrawal' then - cf.BaseConvRate else cf.BaseConvRate end)
) amount
from checkingaccount_v1 c
left join splittransactions_v1 s on s.transid=c.transid
left join ACCOUNTLIST_V1 AC on AC.ACCOUNTID = c.ACCOUNTID
left join currencyformats_v1 cf on cf.currencyid=AC.currencyid
where transcode != 'Transfer'
and c.status !='V'
and ac.status !='Closed'
and (date('now', 'start of month','-11 month','localtime') <= transdate
and transdate < date('now', 'start of month','+1 month','localtime'))
group by month, categ, subcateg
) t
left join category_v1 ca on ca.categid=t.categ
left join subcategory_v1 sc on sc.categid=t.categ and sc.subcategid=t.subcateg
group by category
order by category
Code: Select all
local grandTotal = 0;
local total = {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0};
local cols = {"one", "two", "thr" , "fou", "fiv", "six", "sev", "egh", "nin", "ten", "twe", "ele"};
function handle_record(record)
grandTotal = grandTotal + record:get('OVERALL');
for key, value in ipairs(cols) do
total[key] = total[key] + record:get(value);
end
end
function complete(result)
result:set("GRAND_TOTAL", grandTotal);
for key, value in ipairs(cols) do
result:set("TOTAL_" .. value, total[key]);
end
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>
<link href = "master.css" rel = "stylesheet" />
</head>
<body>
<TMPL_VAR TODAY><hr>
<table class='table'>
<thead>
<tr>
<th></th>
<th id='lastYear', style="text-align:center;" ></th>
<th id='thisYear', style="text-align:center;"></th>
<th></th>
</tr>
<tr>
<th>Category</th>
<th id='1'></th>
<th id='2'></th>
<th id='3'></th>
<th id='4'></th>
<th id='5'></th>
<th id='6'></th>
<th id='7'></th>
<th id='8'></th>
<th id='9'></th>
<th id='10'></th>
<th id='11'></th>
<th id='12'></th>
<th>Overall</th>
</tr>
</thead>
<tbody>
<TMPL_LOOP NAME=CONTENTS>
<tr>
<td><TMPL_VAR CATEGORY></td>
<td class='money'><TMPL_VAR twe></td>
<td class='money'><TMPL_VAR ele></td>
<td class='money'><TMPL_VAR ten></td>
<td class='money'><TMPL_VAR nin></td>
<td class='money'><TMPL_VAR egh></td>
<td class='money'><TMPL_VAR sev></td>
<td class='money'><TMPL_VAR six></td>
<td class='money'><TMPL_VAR fiv></td>
<td class='money'><TMPL_VAR fou></td>
<td class='money'><TMPL_VAR thr></td>
<td class='money'><TMPL_VAR two></td>
<td class='money'><TMPL_VAR one></td>
<td class='money'><TMPL_VAR OVERALL></td>
</tr>
</TMPL_LOOP>
</tbody>
<tfoot>
<tr class="total">
<td>Total: </td>
<td class='money'><TMPL_VAR TOTAL_twe></td>
<td class='money'><TMPL_VAR TOTAL_ele></td>
<td class='money'><TMPL_VAR TOTAL_ten></td>
<td class='money'><TMPL_VAR TOTAL_nin></td>
<td class='money'><TMPL_VAR TOTAL_egh></td>
<td class='money'><TMPL_VAR TOTAL_sev></td>
<td class='money'><TMPL_VAR TOTAL_six></td>
<td class='money'><TMPL_VAR TOTAL_fiv></td>
<td class='money'><TMPL_VAR TOTAL_fou></td>
<td class='money'><TMPL_VAR TOTAL_thr></td>
<td class='money'><TMPL_VAR TOTAL_two></td>
<td class='money'><TMPL_VAR TOTAL_ONE></td>
<td class='money'><TMPL_VAR GRAND_TOTAL></td>
</tr>
</tfoot>
</table>
<TMPL_LOOP ERRORS>
<hr> <TMPL_VAR ERROR>
</TMPL_LOOP>
</body>
<script>
var d = new Date();
var n = d.getMonth();
var months = {1:'Jan', 2:'Feb', 3:'Mar', 4:'Apr', 5:'May', 6:'Jun', 7:'Jul', 8:'Aug', 9:'Sep', 10:'Oct', 11:'Nov', 12:'Dec'};
for(var i in months){
var x = (n + parseInt(i))%12 +1;
document.getElementById(i).innerHTML = months[x];
}
var element= document.getElementById('lastYear');
element.innerHTML = d.getFullYear() - 1;
element.colSpan=12 -n;
element = document.getElementById('thisYear');
element.innerHTML = d.getFullYear();
element.colSpan=n;
</script>
<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);
elements[i].style.textAlign='right';
if (elements[i].innerHTML.indexOf("-") > -1) {
elements[i].style.color="#ff0000";
}
}
</script>
</html>