For gauge chart creation ChartJS from http://chartjs.devexpress.com/ is used, which have to be downloaded and unpacked into MoneyManagerEX/res directory. Report works with instable version 6254.
The report package (see below) contains a description of this report, as well as what are the prerequesites for this report to work.
SQL:
Code: Select all
/**
* Budget: One month is calculated by 365 / 12 = 30.41667, so that one month contains 30.41667 days.
* This value will be used to calculate weekly, bi-weekly or daily budget for a month.
*/
select dic.TransCode, dic.CategoryId, dic.Category Category
, ifnull(round(ActualAmount, 2), 0) ActualAmount, ifnull(round(BudgetAmount, 2), 0) BudgetAmount
, ifnull(round((ifnull(ActualAmount, 0) / BudgetAmount) * 100, 2), 1000) Rate
from (
select distinct TransCode, CategId CategoryId, CategName category
from category_v1
join checkingaccount_v1 using (CategId)
where 1=1
and TransCode <> 'Transfer'
) dic
left join (
select CategoryId, Category, total(BasedTransAmount) ActualAmount
from (
select ifnull(st.CategId, t.CategId) CategoryId, c.CategName category
, ifnull(st.SplitTransAmount, t.TransAmount) * cf.BaseConvRate BasedTransAmount
from (checkingaccount_v1 t
left join SplitTransactions_v1 st on (t.TransId = st.TransId))
join Category_v1 c on (c.CategId = ifnull(st.CategId, t.CategId))
join AccountList_v1 a on (t.AccountId = a.AccountId)
join CurrencyFormats_v1 cf on (a.CurrencyId = cf.CurrencyId)
where 1=1
and t.TransCode <> 'Transfer'
and t.Status <> 'V'
and TransDate between date('now', 'start of month') and date('now', 'start of month', '+1 month', '-1 day')
)
group by CategoryId, Category
) d on (dic.CategoryId = d.CategoryId)
left join (
select c.CategId CategoryId, c.CategName Category
, total(abs(
case
when Period = 'Weekly' then Amount * 30.41667 / 7
when Period = 'Bi-Weekly' then Amount * 30.41667 / 7 / 2
when Period = 'Monthly' then Amount
when Period = 'Bi-Monthly' then Amount / 2
when Period = 'Quarterly' then Amount / 3
when Period = 'Half-Yearly' then Amount / 6
when Period = 'Yearly' then Amount / 12
when Period = 'Daily' then Amount * 30.41667
end
)) BudgetAmount
from BudgetTable_v1 bt
join BudgetYear_v1 by on (bt.BudgetYearId = by.BudgetYearId)
join Category_v1 c on (bt.CategId = c.CategId)
where 1=1
and by.BudgetYearName = strftime('%Y-%m', date())
group by c.CategId, c.CategName
) b on (dic.CategoryId = b.CategoryId)
where 1=0
or ifnull(ActualAmount, 0) != 0
or ifnull(BudgetAmount, 0) != 0
order by dic.TransCode, dic.Category
;
Code: Select all
local dashboardChartsJsOutput = '';
---
-- The color definition of withdrawal colors. The "start" marks the left including value of
-- the range, the "end" marks the right excluding value.
--
-- The first 100% of withdrawal colors are green, as it's better be under budget.
--
local withdrawalColors = {};
withdrawalColors[1] = { ["start"] = -1, ["end"] = 25, ["color"] = "#009900"};
withdrawalColors[2] = { ["start"] = 25, ["end"] = 50, ["color"] = "#00CC00"};
withdrawalColors[3] = { ["start"] = 50, ["end"] = 75, ["color"] = "#00EE00"};
withdrawalColors[4] = { ["start"] = 75, ["end"] = 100, ["color"] = "#44FF00"};
withdrawalColors[5] = { ["start"] = 100, ["end"] = 105, ["color"] = "#88FF00"};
withdrawalColors[6] = { ["start"] = 105, ["end"] = 110, ["color"] = "#BBFF00"};
withdrawalColors[7] = { ["start"] = 110, ["end"] = 115, ["color"] = "#FFDD00"};
withdrawalColors[8] = { ["start"] = 115, ["end"] = 120, ["color"] = "#FFBB00"};
withdrawalColors[9] = { ["start"] = 120, ["end"] = 130, ["color"] = "#FF8800"};
withdrawalColors[10] = { ["start"] = 130, ["end"] = 150, ["color"] = "#FF4400"};
withdrawalColors[11] = { ["start"] = 150, ["end"] = 170, ["color"] = "#EE0000"};
withdrawalColors[12] = { ["start"] = 170, ["end"] = 190, ["color"] = "#CC0000"};
withdrawalColors[13] = { ["start"] = 190, ["end"] = 9999, ["color"] = "#990000"};
---
-- The color definition of desposit colors.
--
-- The first 90% of deposit colors are red, as it's better to be over budget.
--
local depositColors = {};
depositColors[1] = { ["start"] = -1, ["end"] = 10, ["color"] = "#990000"};
depositColors[2] = { ["start"] = 10, ["end"] = 30, ["color"] = "#CC0000"};
depositColors[3] = { ["start"] = 30, ["end"] = 50, ["color"] = "#EE0000"};
depositColors[4] = { ["start"] = 50, ["end"] = 70, ["color"] = "#FF4400"};
depositColors[5] = { ["start"] = 70, ["end"] = 80, ["color"] = "#FF8800"};
depositColors[6] = { ["start"] = 80, ["end"] = 85, ["color"] = "#FFBB00"};
depositColors[7] = { ["start"] = 85, ["end"] = 90, ["color"] = "#FFDD00"};
depositColors[8] = { ["start"] = 90, ["end"] = 95, ["color"] = "#BBFF00"};
depositColors[9] = { ["start"] = 95, ["end"] = 100, ["color"] = "#88FF00"};
depositColors[10] = { ["start"] = 100, ["end"] = 125, ["color"] = "#44FF00"};
depositColors[11] = { ["start"] = 125, ["end"] = 150, ["color"] = "#00EE00"};
depositColors[12] = { ["start"] = 150, ["end"] = 175, ["color"] = "#00CC00"};
depositColors[13] = { ["start"] = 175, ["end"] = 9999, ["color"] = "#009900"};
---
-- The sum of the expenses budget over all category budgets
--
local budgetExpenseSum = 0;
---
-- The sum of the income budget over all category budgets
--
local budgetIncomeSum = 0;
---
-- The sum of all actual expenses over all categories
--
local actualExpenseSum = 0;
---
-- The sum of all actual incomes over all categories
--
local actualIncomeSum = 0;
---
-- Create the javascript chart code for the given category record, which contains the Code of the
-- transaction (Withdrawal or Deposit), the c ategory id, the category name, the budget amount
-- and the actual amount. The rate determines the quotient of actual amount and budget amount.
--
-- @param #table record a record of the database query
--
function createChartCodeForCategoryBudget(record)
local template = [==[
var c%d = $('<div style="width: 125px; float: left; margin: 0 auto;" />').appendTo('#chartContainer%s');
c%d.dxLinearGauge($.extend(true, {}, linearGaugeOptions, {
title: { text: '%s', },
scale: {
startValue: 0,
endValue: %d,
majorTick: { customTickValues: [%d], },
},
value: %f,
valueIndicator: { color: '%s', },
}));
]==];
local colorDefinition = withdrawalColors;
if record:get("TransCode") == "Deposit" then
colorDefinition = depositColors;
end
local color = determineRateColor(record:get("Rate"), colorDefinition);
local js = string.format(template
, record:get("CategoryId")
, record:get("TransCode")
, record:get("CategoryId")
, record:get("Category")
, 2 * record:get("BudgetAmount")
, record:get("BudgetAmount"), record:get("ActualAmount")
, color);
return js;
end
---
-- Create the javascript chart code for comparison of two values. The given title is used as chart
-- title, the type specifies if its a expense or income chart (regarding color definition).
--
-- @param #string title the title of the chart
-- @param #float compareValue the comparison value which is displayed as value of the gauge
-- @param #float referenceValue the reference value which is displayed as subvalue at "12 o'clock"
-- @param #string type type of color definition expenses or income
--
function createChartCodeForComparison(title, compareValue, referenceValue, type)
local normTitle = string.gsub(title, "%A", "");
local template = [==[
var d%s = $('<div style="width: 380px; float: left; margin: 0 auto;" />').appendTo('#chartContainerOverall');
d%s.dxCircularGauge($.extend(true, {}, circularGaugeOptions, {
title: { text: '%s', },
scale: {
startValue: 0,
endValue: %d,
},
rangeContainer: {
ranges: [
%s
],
},
value: %f,
subvalues: [ %f ],
}));
]==];
local colorDefinition = withdrawalColors;
if type == "Income" then
colorDefinition = depositColors;
end
local jsRangeContainerTemplate = determineRangeColorsAsJs(referenceValue, colorDefinition);
local js = string.format(template
, normTitle
, normTitle
, title
, 2 * referenceValue
, jsRangeContainerTemplate
, compareValue
, referenceValue
);
return js;
end
---
-- Determine the colored ranges of the chart by the color definition. The maxValue is used to
-- calculate real values by the percentage values of the color definition.
--
-- The ranges will be returned as javascript code, which will be inserted into the chart options.
--
-- @param #float maxValue the maximum value of the ranges
-- @param #table colorDefinition defines the ranges and their colors
--
function determineRangeColorsAsJs(maxValue, colorDefinition)
local js = "";
local rangeTemplate = [==[
{ startValue: %d, endValue: %d, color: '%s' },
]==];
for i = 1, #colorDefinition do
js = js .. string.format(rangeTemplate
, maxValue * (colorDefinition[i]["start"] / 100)
, maxValue * (colorDefinition[i]["end"] / 100)
, colorDefinition[i]["color"]
);
end
return js;
end
---
-- Determine the color for the given rateString from the specified colorDefinition. If the range
-- has been found for the given rate, the according color will be returned.
--
-- @param #string rateString the rate which should be colored
-- @param #table colorDefinition defines the ranges and their colors
--
function determineRateColor(rateString, colorDefinition)
local rate = tonumber(rateString);
for i = 1, #colorDefinition do
if colorDefinition[i]["start"] < rate and rate <= colorDefinition[i]["end"] then
color = colorDefinition[i]["color"];
break;
end
end
return color;
end
---
-- Update the sum values by the actual and budget amount of the record, dependent on the transaction
-- code
--
-- @param #table record the record of the database query
--
function updateAmountSumsBy(record)
if record:get("TransCode") == "Withdrawal" then
budgetExpenseSum = budgetExpenseSum + record:get("BudgetAmount");
actualExpenseSum = actualExpenseSum + record:get("ActualAmount");
elseif record:get("TransCode") == "Deposit" then
budgetIncomeSum = budgetIncomeSum + record:get("BudgetAmount");
actualIncomeSum = actualIncomeSum + record:get("ActualAmount");
end
end
---
-- Handle each record which will be returned by the database query
--
-- @param #table record the record of the database query
--
function handle_record(record)
local jsOutputPerRecord = createChartCodeForCategoryBudget(record);
updateAmountSumsBy(record);
dashboardChartsJsOutput = dashboardChartsJsOutput .. jsOutputPerRecord;
end
---
-- Function will be called after all records have been handled. The chart code for the sum values
-- will be created and put into a result table, which can be inserted within the template
--
-- @param #table result placeholder for javascript code to be inserted into the template
--
function complete(result)
if actualIncomeSum == 0 then
actualIncomeSum = actualExpenseSum / 2;
end
local jsExpenseIncome = createChartCodeForComparison("Expenses vs Income"
, actualExpenseSum, actualIncomeSum, "Expense");
local jsIncomeBudget = createChartCodeForComparison("Income vs Budget"
, actualIncomeSum, budgetIncomeSum, "Income");
local jsExpenseBudget = createChartCodeForComparison("Expense vs Budget"
, actualExpenseSum, budgetExpenseSum, "Expense");
result:set("EXPENSE_INCOME_DATA", jsExpenseIncome);
result:set("INCOME_BUDGET_DATA", jsIncomeBudget);
result:set("EXPENSE_BUDGET_DATA", jsExpenseBudget);
result:set("DASHBOARD_DATA", dashboardChartsJsOutput);
end
Code: Select all
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><TMPL_VAR name="REPORTNAME" /></title>
<link href="master.css" rel="stylesheet"></link>
<script src="jquery-2.0.3.min.js" type="text/javascript"></script>
<script src="globalize.min.js" type="text/javascript"></script>
<script src="dx.chartjs.js" type="text/javascript"></script>
<script type="text/javascript">
var circularGaugeOptions = {
scale: {
majorTick: {
tickInterval: 500,
color: '#000000',
},
minorTick: {
visible: true,
tickInterval: 100,
color: '#000000',
},
label: {
customizeText: function(label) {
return label.valueText + ' €';
},
indentFromTick: 15,
},
},
rangeContainer: {
palette: 'pastel',
offset: -10,
},
title: {
font: { size: 18 },
position: 'top-center',
},
valueIndicator: {
offset: 10,
color: '#CC0000',
text: {
format: 'fixedPoint',
precision: 2,
indent: 10,
},
},
subvalueIndicator :{
type: 'triangle',
offset: -5,
color: '#0033CC',
},
};
var linearGaugeOptions = {
geometry: {
orientation: 'vertical',
},
scale: {
majorTick: {
color: 'green',
showCalculatedTicks: false,
},
label: {
indentFromTick: -5,
font: {
color: 'green',
},
customizeText: function(label) {
return label.valueText + ' €';
},
} ,
},
rangeContainer: {
offset: 5,
},
valueIndicator: {
type: 'rangebar',
offset: 5,
text: {
indent: 3,
format: 'fixedPoint',
precision: 2,
customizeText: function(label) {
return label.valueText + ' €';
},
},
},
title: {
font: { size: 12 },
position: 'top-center',
},
};
$(function () {
<TMPL_VAR name="EXPENSE_INCOME_DATA" />
<TMPL_VAR name="INCOME_BUDGET_DATA" />
<TMPL_VAR name="EXPENSE_BUDGET_DATA" />
<TMPL_VAR name="DASHBOARD_DATA" />
});
</script>
</head>
<body>
<div class="page-header">
<h2 class="text-center">Budget Monitoring for this month</h2>
<p class="text-center">Compare Expenses, Income and Budget at <TMPL_VAR name="today" /></p>
</div>
<div id="chartContainerOverall" class="container" style="clear:both;"></div>
<div id="chartContainerWithdrawal" class="container" style="height:350px;clear:both;">
<h3>Budget monitoring of Withdrawal Categories</h3>
</div>
<div id="chartContainerDeposit" class="container" style="height:350px;clear:both;">
<h3>Budget monitoring of Deposit Categories</h3>
</div>
</body>
</html>
nuvito
