[code]
select s1.STOCKNAME, s1.SYMBOL,
total(s1.NUMSHARES) as NUMSHARES,
total(s1.VALUE * c1.BASECONVRATE) as VALUE
from ACCOUNTLIST_V1 as a1
inner join STOCK_V1 as s1 on s1.HELDAT = a1.ACCOUNTID
inner join CURRENCYFORMATS_V1 as c1 on c1.CURRENCYID = a1.CURRENCYID
where a1.ACCOUNTTYPE = 'Investment' and a1.STATUS = 'Open'
group by s1.SYMBOL
order by VALUE desc;
[/code]
Lua
[code]
local total = 0;
local count = 1;
local colors = {"#FF6666", "#FFB266", "#FFFF66", "#B2FF66", "#66FF66", "#66FFB2", "#66FFFF", "#66B2FF", "#6666FF", "#B266FF", "#FF66FF", "#FF66B2"};
local data = '';
local json = [[
{value: %s,
color: "%s",
label: "%s",
labelColor: "black",
labelFontSize: "12",
labelAlign: "center"},
]];
function handle_record(record)
local color = colors[count % #colors];
count = count + 1;
record:set('COLOR', color);
total = total + record:get('VALUE');
local value = string.format("%.2f", record:get('VALUE'));
data = data .. string.format(json, value, color, record:get('SYMBOL'));
end
function complete(result)
result:set('STOCK_TOTAL', total);
result:set('STOCK_DATA', data);
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>Stock Summary 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="300" height="300"></canvas></td></tr>
<tr><td> </td></tr>
<tr><td><table class="table">
<thead>
<tr>
<th>Name</th>
<th>Symbol</th>
<th class="text-right">Shares</th>
<th class="text-right">VALUE</th>
</tr>
</thead>
<tbody>
<TMPL_LOOP NAME=CONTENTS>
<tr>
<td><TMPL_VAR STOCKNAME></td>
<td bgcolor='<TMPL_VAR "COLOR">'><TMPL_VAR SYMBOL></td>
<td class="stock text-right"><TMPL_VAR NUMSHARES></td>
<td class="money text-right"><TMPL_VAR VALUE></td>
</tr>
</TMPL_LOOP>
</tbody>
<tfoot>
<tr class="total">
<td>Total</td>
<td></td>
<td></td>
<td class="money text-right"><TMPL_VAR STOCK_TOTAL></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);}
function shares(n) {n = parseFloat(n); return isNaN(n) ? 0 : n.toFixed(4);}
var elements = document.getElementsByClassName("money");
for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = "<TMPL_VAR PFX_SYMBOL>" + currency(elements[i].innerHTML) + "<TMPL_VAR SFX_SYMBOL>";}
elements = document.getElementsByClassName("stock");
for (var i = 0; i < elements.length; i++) {elements[i].innerHTML = shares(elements[i].innerHTML);}
<!-- Chart -->
var data = [
<TMPL_VAR "STOCK_DATA">
];
var options = {
animationEasing: 'easeOutQuint'
};
var ctx = document.getElementById("reportChart").getContext("2d");
var reportChart = new Chart(ctx).Pie(data, options);
</script>
</html>
[/code]