From dadc34655c3ab961b0b0b94a10eaaba710f0b5e8 Mon Sep 17 00:00:00 2001 From: tpearson Date: Mon, 4 Jul 2011 22:38:03 +0000 Subject: Added kmymoney git-svn-id: svn://anonsvn.kde.org/home/kde/branches/trinity/applications/kmymoney@1239792 283d02a7-25f6-0310-bc7c-ecb5cbfe19da --- contrib/functions.mysql | 182 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 182 insertions(+) create mode 100644 contrib/functions.mysql (limited to 'contrib/functions.mysql') diff --git a/contrib/functions.mysql b/contrib/functions.mysql new file mode 100644 index 0000000..c8c6c7f --- /dev/null +++ b/contrib/functions.mysql @@ -0,0 +1,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; + -- cgit v1.2.1