1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
/* functions.mysql
Some functions for use with a KMyMoney MySql database.
Load them into your database with command:
mysql KMyMoney < this_file
To preserve them in backups, remember to add the -R flag to mysqldump.
*/
delimiter //
DROP FUNCTION IF EXISTS toDecimal//
CREATE
FUNCTION toDecimal(mymoneymoney VARCHAR(32))
RETURNS DECIMAL(12,6)
READS SQL DATA
BEGIN
/* Converts a MyMoneyMoney numerator/denominator string to a decimal number */
DECLARE result DECIMAL (12,6);
SELECT SUBSTRING_INDEX(mymoneymoney, '/', 1) / SUBSTRING_INDEX(mymoneymoney, '/', -1) INTO result;
RETURN result;
END
//
delimiter ;
delimiter //
DROP FUNCTION IF EXISTS cashBalance//
CREATE
FUNCTION cashBalance(acctId VARCHAR(32))
RETURNS DECIMAL(12,2)
READS SQL DATA
BEGIN
/* Returns the cash balance as of today of an account specified by internal id.*/
/* to determine id - SELECT id FROM kmmAccounts WHERE accountName = 'whatever';
Sample usage - SELECT cashBalance('A000001'); */
DECLARE result DECIMAL (12,2);
SELECT SUM(toDecimal(shares)) INTO result
FROM kmmSplits
WHERE accountId = acctId
AND postDate <= NOW()
AND txType = 'N';
RETURN result;
END
//
delimiter ;
delimiter //
DROP PROCEDURE IF EXISTS listBalances//
CREATE
PROCEDURE listBalances(IN parent varchar(32))
READS SQL DATA
BEGIN
/* Lists the balances of all accounts subsidiary to a named account. NOTE: not recursive
Sample usage: - CALL listBalances('Asset'); */
SELECT accountName, cashBalance(id) FROM kmmAccounts where parentId =
(SELECT id from kmmAccounts WHERE accountName = parent)
ORDER by 1;
END
//
delimiter ;
delimiter //
DROP FUNCTION IF EXISTS latestPrice//
CREATE
FUNCTION latestPrice(secId VARCHAR(32))
RETURNS DECIMAL(12,6)
READS SQL DATA
BEGIN
/* Returns the latest price for a security identified by internal id. */
/* to determine id - SELECT id FROM kmmSecurities WHERE name = 'whatever'; */
DECLARE result DECIMAL (12,6);
SELECT toDecimal(price) INTO result
FROM kmmPrices WHERE fromId = secId AND priceDate =
(SELECT MAX(priceDate) FROM kmmPrices WHERE fromId = secId);
RETURN result;
END
//
delimiter ;
delimiter //
DROP FUNCTION IF EXISTS shareBalance//
CREATE
FUNCTION shareBalance(acctId VARCHAR(32))
RETURNS DECIMAL(12,6)
READS SQL DATA
BEGIN
/* Returns the share balance for an Stock account identified by internal id.
NOTE: similar to cashBalance but with greater precision */
DECLARE result DECIMAL (12,6);
SELECT SUM(toDecimal(shares)) INTO result
FROM kmmSplits WHERE accountId = acctId AND txType = 'N';
RETURN result;
END
//
delimiter ;
delimiter //
DROP FUNCTION IF EXISTS valuation//
CREATE
FUNCTION valuation(acctId VARCHAR(32))
RETURNS DECIMAL(12,2)
READS SQL DATA
BEGIN
/* Returns the current value of a Stock account identified by internal id */
DECLARE result DECIMAL(12,2);
DECLARE secId VARCHAR(32);
SELECT currencyId FROM kmmAccounts WHERE id = acctId INTO secId;
SELECT shareBalance(acctId) * latestPrice(secId) INTO result;
RETURN result;
END
//
delimiter ;
delimiter //
DROP PROCEDURE IF EXISTS listValues//
CREATE PROCEDURE listValues(IN parent varchar(32))
READS SQL DATA
BEGIN
/* Lists the current values of all stocks subsidiary to a named Investment account. NOTE: not recursive */
SELECT parent AS 'Portfolio';
SELECT accountName, valuation(id) AS 'Value' FROM kmmAccounts where parentId =
(SELECT id from kmmAccounts WHERE accountName = parent)
ORDER by 1;
SELECT SUM(valuation(id)) AS 'Total Value' FROM kmmAccounts where parentId =
(SELECT id from kmmAccounts WHERE accountName = parent);
END
//
delimiter ;
delimiter //
DROP FUNCTION IF EXISTS payeeName//
CREATE
FUNCTION payeeName(payeeId VARCHAR(32))
RETURNS MEDIUMTEXT
READS SQL DATA
BEGIN
/* Returns payee name from id, with NULL test */
DECLARE result MEDIUMTEXT;
IF payeeId IS NULL THEN SET result = 'Empty Payee';
ELSE SELECT name FROM kmmPayees WHERE id = payeeId INTO result;
END IF;
RETURN result;
END
//
delimiter ;
delimiter //
DROP FUNCTION IF EXISTS categoryName//
CREATE
FUNCTION categoryName(categoryId VARCHAR(32))
RETURNS MEDIUMTEXT
READS SQL DATA
BEGIN
/* Returns fully qualified category name from its id */
DECLARE result MEDIUMTEXT;
DECLARE thisName MEDIUMTEXT;
DECLARE parent VARCHAR(32);
IF categoryId IS NULL THEN RETURN 'Empty Category';
END IF;
SELECT accountName from kmmAccounts WHERE id = categoryId INTO result;
SELECT parentId from kmmAccounts WHERE id = categoryId INTO parent;
WHILE parent IS NOT NULL DO
SELECT accountName from kmmAccounts WHERE id = parent INTO thisName;
SET result = CONCAT(thisName, ':', result);
SELECT parentId from kmmAccounts WHERE id = parent INTO parent;
END WHILE;
RETURN result;
END
//
delimiter ;
/* some useful functions re tax */
DROP VIEW IF EXISTS taxCats;
CREATE VIEW taxCats AS SELECT kvpId AS accountId FROM kmmKeyValuePairs WHERE kvpKey = 'Tax' AND kvpData = 'Yes';
DROP VIEW IF EXISTS taxSplits;
CREATE VIEW taxSplits AS SELECT accountId, payeeId, postDate, CAST(toDecimal(value) AS decimal(12,2)) AS Amount FROM kmmSplits WHERE txType = 'N' AND accountId IN (SELECT * FROM taxCats);
/* Sample: generate a tax report for UK yesr 08-09.
Sorted by payee within Category */
DROP VIEW IF EXISTS taxReport;
CREATE VIEW taxReport AS SELECT categoryName(accountId) AS Account, payeeName(payeeId) As Payee, DATE_FORMAT(postDate, '%d/%m/%y') As Date, ABS(Amount) AS Amount FROM taxSplits WHERE postDate > "2008-04-05" and postDate < "2009-04-06" ORDER BY 1, 2, 3;
|
