Updated report which uses the current value of the asset.
SQL
[code]
SELECT ASSETTYPE, STARTDATE, VALUE, VALUECHANGE, VALUECHANGERATE
FROM ASSETS_V1
ORDER BY ASSETTYPE ASC;
[/code]
Lua
[code]
local total = 0;
local type = {}
local subtotal = {}
function is_leap_year(year)
local ly = 0;
if year % 4 == 0 then
if year % 100 == 0 then
if year % 400 == 0 then
ly = 1;
end
else
ly = 1;
end
end
return ly;
end
function get_days(month, year)
local days = { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }
local d = days[tonumber(month)];
if tonumber(month) == 2 and is_leap_year(year) ~= 0 then
d = 29;
end
return d;
end
function get_years(record)
local date = record:get("STARTDATE");
local aYear = string.sub(date,1,4);
local aMonth = string.sub(date,6, 7);
local aDay = string.sub(date,9);
local cYear = os.date('%Y');
local cMonth = os.date('%m');
local cDay = os.date('%d');
local nbrYears = 0;
if aYear == cYear then
local days = 0;
if aMonth == cMonth then
days = cDay - aDay;
else
days = get_days(aMonth, aYear) - aDay;
if tonumber(aMonth) + 1 > tonumber(cMonth) - 1 then
for i = (aMonth + 1), (cMonth - 1) do
days = days + get_days(i, aYear);
end
end
days = days + cDay;
end
if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then
nbrYears = days / 366;
else
nbrYears = days / 365;
end
else
nbrYears = cYear - aYear;
local days = get_days(aMonth, aYear) - aDay;
for i = (aMonth + 1), 12 do
days = days + get_days(i, aYear);
end
if tonumber(cMonth) > 1 then
for i = 1, (cMonth - 1) do
days = days + get_days(i, cYear);
end
end
days = days + cDay;
if tonumber(aMonth) < tonumber(cMonth) then
nbrYears = nbrYears - 1;
elseif aMonth == cMonth and tonumber(aDay) <= tonumber(cDay) then
nbrYears = nbrYears - 1;
end
if (tonumber(aMonth) > 2 and is_leap_year(aYear + 1) ~= 0) or (tonumber(aMonth) <= 2 and is_leap_year(aYear) ~= 0) then
nbrYears = nbrYears + (days / 366);
else
nbrYears = nbrYears + (days / 365);
end
end
return nbrYears;
end
function handle_record(record)
local index = 0;
local cur_type = record:get("ASSETTYPE");
for t = 1,#type do
if cur_type == type[t] then
index = t;
break;
end
end
if index == 0 then
index = #type + 1;
type[index] = cur_type;
end
local value = record:get("VALUE");
local change = record:get("VALUECHANGE");
if change ~= 'None' then
local rate = record:get("VALUECHANGERATE") / 100;
local years = get_years(record);
local scale = 1;
if change == 'Appreciates' then
scale = math.pow(1 + rate, years);
elseif change == 'Depreciates' then
scale = math.pow(1 - rate, years);
end
value = value * scale;
end
total = total + value;
if subtotal[index] then
subtotal[index] = subtotal[index] + value;
else
subtotal[index] = value;
end
end
function complete(result)
result:set("Current_Total", total);
local colors = {"#FBD4B4", "#E5B8B7", "#B6DDE8", "#B8CCE4", "#F0EDCE", "#DEE3E7", "#99FF99"};
local rows = '';
local data = '';
for t = 1,#type do
local value = string.format("%.2f", subtotal[t]);
rows = rows .. '<tr bgcolor=' .. colors[t] .. '><td>' .. type[t] .. '</td><td class="money text-right">' .. value .. '</td></tr>';
data = data .. '{value: ' .. value .. ', color:"' .. colors[t] .. '", label : "' .. type[t] ..'", labelColor : "black", labelFontSize : "12", labelAlign : "center"},\n';
end
result:set("SubTotals", rows);
result:set("ASSET_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>Asset 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>Type</th>
<th class="text-right">Value</th>
</tr>
</thead>
<tbody>
<TMPL_VAR SubTotals>
</tbody>
<tfoot>
<tr class="total">
<td>Total</td>
<td class="money text-right"><TMPL_VAR Current_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);}
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 "ASSET_DATA">
];
var options = {
animationEasing: 'easeOutQuint'
};
var ctx = document.getElementById("reportChart").getContext("2d");
var reportChart = new Chart(ctx).Pie(data, options);
</script>
</html>
[/code]
You do not have the required permissions to view the files attached to this post.