summaryrefslogtreecommitdiffstats
path: root/contrib/functions.mysql
diff options
context:
space:
mode:
authortpearson <tpearson@283d02a7-25f6-0310-bc7c-ecb5cbfe19da>2011-07-04 22:38:03 +0000
committertpearson <tpearson@283d02a7-25f6-0310-bc7c-ecb5cbfe19da>2011-07-04 22:38:03 +0000
commitdadc34655c3ab961b0b0b94a10eaaba710f0b5e8 (patch)
tree99e72842fe687baea16376a147619b6048d7e441 /contrib/functions.mysql
downloadkmymoney-dadc34655c3ab961b0b0b94a10eaaba710f0b5e8.tar.gz
kmymoney-dadc34655c3ab961b0b0b94a10eaaba710f0b5e8.zip
Added kmymoney
git-svn-id: svn://anonsvn.kde.org/home/kde/branches/trinity/applications/kmymoney@1239792 283d02a7-25f6-0310-bc7c-ecb5cbfe19da
Diffstat (limited to 'contrib/functions.mysql')
-rw-r--r--contrib/functions.mysql182
1 files changed, 182 insertions, 0 deletions
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;
+