MK13/financer
1
0
Fork 0
Code Issues 10 Actions Activity

Create period overview #10

New Issue
Closed
opened 2021-01-24 13:10:42 +01:00 by MK13 · 3 comments
MK13 commented 2021-01-24 13:10:42 +01:00 (Migrated from 77zzcx7.de)
Copy Link

Table of periods, including expense & income sums, start & end date

Table of periods, including expense & income sums, start & end date
MK13 commented 2021-01-30 18:13:24 +01:00 (Migrated from 77zzcx7.de)
Copy Link
ID Type Start date End date Income Expenses Liability Total Assets TRX count Action
1 GRAND TOTAL xxxx-xx-xx open end 96000.00€ 92055.68€ ... ... 6000.00€ 1200
2 YEAR xxxx-xx-xx not yet closed ... ... ... ... ...
3 Expense xxxx-xx-xx not yet closed ... ... ... ... ... Close
4 Expense xxxx-xx-xx yyyy-yy-yy ... ... ... ... ...
5 Expense xxxx-xx-xx yyyy-yy-yy ... ... ... ... ...
6 YEAR xxxx-xx-xx yyyy-yy-yy ... ... ... ... ...

Income = sum(period.AccountStatistics.filterAccountTypeIncome.spendingTotalFrom)

Expenses = sum(period.AccountStatistics.filterAccountTypeLiabilityAndExpense.spendingTotalTo)

| ID | Type | Start date | End date | Income | Expenses | Liability | Total | Assets | TRX count | Action | | -- | ----------- | ---------- | -------------- | --------- | --------- | --------- | ----- | -------- | --------- | ------ | | 1 | GRAND TOTAL | xxxx-xx-xx | open end | 96000.00€ | 92055.68€ | ... | ... | 6000.00€ | 1200 | | | 2 | YEAR | xxxx-xx-xx | not yet closed | ... | ... | ... | ... | ... | | | | 3 | Expense | xxxx-xx-xx | not yet closed | ... | ... | ... | ... | ... | | Close | | 4 | Expense | xxxx-xx-xx | yyyy-yy-yy | ... | ... | ... | ... | ... | | | | 5 | Expense | xxxx-xx-xx | yyyy-yy-yy | ... | ... | ... | ... | ... | | | | 6 | YEAR | xxxx-xx-xx | yyyy-yy-yy | ... | ... | ... | ... | ... | | | Income = sum(period.AccountStatistics.filterAccountTypeIncome.spendingTotalFrom) Expenses = sum(period.AccountStatistics.filterAccountTypeLiabilityAndExpense.spendingTotalTo)
MK13 commented 2021-01-30 19:03:26 +01:00 (Migrated from 77zzcx7.de)
Copy Link

Plan is to implement an interface in -server that returns exactly the data required for the table, to avoid unnecessary roundtrips and transfer, via a DTO.

Plan is to implement an interface in -server that returns exactly the data required for the table, to avoid unnecessary roundtrips and transfer, via a DTO.
MK13 commented 2021-03-04 04:26:57 +01:00 (Migrated from 77zzcx7.de)
Copy Link
WITH income AS (
	-- Regular income based on INCOME accounts 
	SELECT p.ID, SUM(asIncome.spending_total_from) AS incomeSum
	FROM period p
	INNER JOIN account_statistic asIncome ON asIncome.period_id = p.id
	INNER JOIN account aIncome ON aIncome.id = asIncome.account_id AND aIncome.type = 'INCOME'
	GROUP BY p.id, p.type, p.start, p."end"
),
incomeCredit as (
	-- Special case for credits that can be booked from a LIABILITY account to a BANK/CASH account
	-- Need to be counted as income as the money is used for expenses and those expenses will be counted
	-- as expense, so to make it even we need to count it here as well
	SELECT p2.id, p2."type", SUM(amount) AS incomeCreditSum
	FROM "transaction" t 
	INNER JOIN account a on a.id = t.from_account_id 
	INNER JOIN account a2 on a2.id = t.to_account_id 
	INNER JOIN link_transaction_period ltp on ltp.transaction_id = t.id 
	INNER JOIN "period" p2 on p2.id = ltp.period_id 
	WHERE 1 = 1
	  AND a."type" in ('LIABILITY')
	  AND a2."type" in ('BANK', 'CASH')
	GROUP BY p2.id, p2.type, p2.start, p2."end"
),
incomeStart as (
	-- Special case for money that was there at the starting time of a financer instance
	-- Will be counted as income as this money is used for expanses, so to make it even
	-- we need to count it here as well
	SELECT p2.id, p2."type", SUM(amount) AS incomeStartSum
	FROM "transaction" t 
	INNER JOIN account a on a.id = t.from_account_id 
	INNER JOIN account a2 on a2.id = t.to_account_id 
	INNER JOIN link_transaction_period ltp on ltp.transaction_id = t.id 
	INNER JOIN "period" p2 on p2.id = ltp.period_id 
	WHERE 1 = 1
	  AND a."type" in ('START')
	  AND a2."type" in ('BANK', 'CASH')
	GROUP BY p2.id, p2.type, p2.start, p2."end"
),
expense AS (
	-- Expense booking - NOT counted is the case LIABILITY -> EXPENSE even though that is a
	-- valid booking in the app. This is because we would count the expense once here and a second time
	-- with the liability query
	SELECT p2.id, p2."type", SUM(amount) AS expenseSum
	FROM "transaction" t 
	INNER JOIN account a on a.id = t.from_account_id 
	INNER JOIN account a2 on a2.id = t.to_account_id 
	INNER JOIN link_transaction_period ltp on ltp.transaction_id = t.id 
	INNER JOIN "period" p2 on p2.id = ltp.period_id 
	WHERE 1 = 1
	  AND a."type" in ('BANK', 'CASH')
	  AND a2."type" in ('EXPENSE')
	GROUP BY p2.id, p2.type, p2.start, p2."end"
),
liability AS (
	-- Excluded is the special case for start bookings, START -> LIABILITY
	-- as the actual expense for that was some time in the past before the starting
	-- of the financer instance
	SELECT p2.id, p2."type", SUM(amount) AS liabilitySum
	FROM "transaction" t 
	INNER JOIN account a on a.id = t.from_account_id 
	INNER JOIN account a2 on a2.id = t.to_account_id 
	INNER JOIN link_transaction_period ltp on ltp.transaction_id = t.id 
	INNER JOIN "period" p2 on p2.id = ltp.period_id 
	WHERE 1 = 1
	  AND a."type" in ('BANK', 'CASH')
	  AND a2."type" in ('LIABILITY')
	GROUP BY p2.id, p2.type, p2.start, p2."end"
),
assets AS (
	-- Returns only the assets for closed periods
	SELECT p.ID, SUM(asBankCash.end_balance) AS assetSum
	FROM period p
	INNER JOIN account_statistic asBankCash ON asBankCash.period_id = p.id
	INNER JOIN account aBankCash ON aBankCash.id = asBankCash.account_id AND aBankCash.type IN ('BANK', 'CASH')
	GROUP BY p.id, p.type, p.start, p."end"
),
transactions AS (
	-- The count of transactions in a period
	SELECT ltp.period_id, COUNT(*) AS transaction_count
	FROM link_transaction_period ltp
	GROUP BY ltp.period_id
)
SELECT 
	p.ID 							PERIOD_ID, 
	p.type 							PERIOD_TYPE, 
	p.start 						PERIOD_START,
	p."end" 						PERIOD_END,
	CASE 
		WHEN ic.incomeCreditSum IS     NULL AND "is".incomeStartSum IS 	   NULL THEN i.incomeSum 
		WHEN ic.incomeCreditSum IS     NULL AND "is".incomeStartSum IS NOT NULL THEN (i.incomeSum + "is".incomeStartSum)
		WHEN ic.incomeCreditSum IS NOT NULL AND "is".incomeStartSum IS     NULL THEN (i.incomeSum + ic.incomeCreditSum)
		WHEN ic.incomeCreditSum IS NOT NULL AND "is".incomeStartSum IS NOT NULL THEN (i.incomeSum + ic.incomeCreditSum + "is".incomeStartSum)
	END 							INCOME_SUM, 
	e.expenseSum 					EXPENSE_SUM, 
	l.liabilitySum 					LIABILITY_SUM, 
	(e.expenseSum + l.liabilitySum) TOTAL, 
	a.assetSum 						ASSETS_SUM, 
	t.transaction_count 			TRANSACTION_COUNT
FROM 
	"period" p
INNER JOIN income i 		ON i.ID = p.ID
LEFT JOIN  incomeCredit ic 	ON ic.ID = p.ID
LEFT JOIN  incomeStart "is" ON "is".ID = p.ID
INNER JOIN expense e 		ON e.ID = p.ID
INNER JOIN assets a 		ON a.ID = p.ID
INNER JOIN liability l 		ON l.ID = p.ID
INNER JOIN transactions t 	ON t.period_id = p.ID
ORDER BY 
	"end" DESC, 
	start ASC
``` WITH income AS ( -- Regular income based on INCOME accounts SELECT p.ID, SUM(asIncome.spending_total_from) AS incomeSum FROM period p INNER JOIN account_statistic asIncome ON asIncome.period_id = p.id INNER JOIN account aIncome ON aIncome.id = asIncome.account_id AND aIncome.type = 'INCOME' GROUP BY p.id, p.type, p.start, p."end" ), incomeCredit as ( -- Special case for credits that can be booked from a LIABILITY account to a BANK/CASH account -- Need to be counted as income as the money is used for expenses and those expenses will be counted -- as expense, so to make it even we need to count it here as well SELECT p2.id, p2."type", SUM(amount) AS incomeCreditSum FROM "transaction" t INNER JOIN account a on a.id = t.from_account_id INNER JOIN account a2 on a2.id = t.to_account_id INNER JOIN link_transaction_period ltp on ltp.transaction_id = t.id INNER JOIN "period" p2 on p2.id = ltp.period_id WHERE 1 = 1 AND a."type" in ('LIABILITY') AND a2."type" in ('BANK', 'CASH') GROUP BY p2.id, p2.type, p2.start, p2."end" ), incomeStart as ( -- Special case for money that was there at the starting time of a financer instance -- Will be counted as income as this money is used for expanses, so to make it even -- we need to count it here as well SELECT p2.id, p2."type", SUM(amount) AS incomeStartSum FROM "transaction" t INNER JOIN account a on a.id = t.from_account_id INNER JOIN account a2 on a2.id = t.to_account_id INNER JOIN link_transaction_period ltp on ltp.transaction_id = t.id INNER JOIN "period" p2 on p2.id = ltp.period_id WHERE 1 = 1 AND a."type" in ('START') AND a2."type" in ('BANK', 'CASH') GROUP BY p2.id, p2.type, p2.start, p2."end" ), expense AS ( -- Expense booking - NOT counted is the case LIABILITY -> EXPENSE even though that is a -- valid booking in the app. This is because we would count the expense once here and a second time -- with the liability query SELECT p2.id, p2."type", SUM(amount) AS expenseSum FROM "transaction" t INNER JOIN account a on a.id = t.from_account_id INNER JOIN account a2 on a2.id = t.to_account_id INNER JOIN link_transaction_period ltp on ltp.transaction_id = t.id INNER JOIN "period" p2 on p2.id = ltp.period_id WHERE 1 = 1 AND a."type" in ('BANK', 'CASH') AND a2."type" in ('EXPENSE') GROUP BY p2.id, p2.type, p2.start, p2."end" ), liability AS ( -- Excluded is the special case for start bookings, START -> LIABILITY -- as the actual expense for that was some time in the past before the starting -- of the financer instance SELECT p2.id, p2."type", SUM(amount) AS liabilitySum FROM "transaction" t INNER JOIN account a on a.id = t.from_account_id INNER JOIN account a2 on a2.id = t.to_account_id INNER JOIN link_transaction_period ltp on ltp.transaction_id = t.id INNER JOIN "period" p2 on p2.id = ltp.period_id WHERE 1 = 1 AND a."type" in ('BANK', 'CASH') AND a2."type" in ('LIABILITY') GROUP BY p2.id, p2.type, p2.start, p2."end" ), assets AS ( -- Returns only the assets for closed periods SELECT p.ID, SUM(asBankCash.end_balance) AS assetSum FROM period p INNER JOIN account_statistic asBankCash ON asBankCash.period_id = p.id INNER JOIN account aBankCash ON aBankCash.id = asBankCash.account_id AND aBankCash.type IN ('BANK', 'CASH') GROUP BY p.id, p.type, p.start, p."end" ), transactions AS ( -- The count of transactions in a period SELECT ltp.period_id, COUNT(*) AS transaction_count FROM link_transaction_period ltp GROUP BY ltp.period_id ) SELECT p.ID PERIOD_ID, p.type PERIOD_TYPE, p.start PERIOD_START, p."end" PERIOD_END, CASE WHEN ic.incomeCreditSum IS NULL AND "is".incomeStartSum IS NULL THEN i.incomeSum WHEN ic.incomeCreditSum IS NULL AND "is".incomeStartSum IS NOT NULL THEN (i.incomeSum + "is".incomeStartSum) WHEN ic.incomeCreditSum IS NOT NULL AND "is".incomeStartSum IS NULL THEN (i.incomeSum + ic.incomeCreditSum) WHEN ic.incomeCreditSum IS NOT NULL AND "is".incomeStartSum IS NOT NULL THEN (i.incomeSum + ic.incomeCreditSum + "is".incomeStartSum) END INCOME_SUM, e.expenseSum EXPENSE_SUM, l.liabilitySum LIABILITY_SUM, (e.expenseSum + l.liabilitySum) TOTAL, a.assetSum ASSETS_SUM, t.transaction_count TRANSACTION_COUNT FROM "period" p INNER JOIN income i ON i.ID = p.ID LEFT JOIN incomeCredit ic ON ic.ID = p.ID LEFT JOIN incomeStart "is" ON "is".ID = p.ID INNER JOIN expense e ON e.ID = p.ID INNER JOIN assets a ON a.ID = p.ID INNER JOIN liability l ON l.ID = p.ID INNER JOIN transactions t ON t.period_id = p.ID ORDER BY "end" DESC, start ASC ```
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
bug
Something is not working
enhancement
New feature
No Label
enhancement
Milestone
No items
No Milestone
Assignees
Clear assignees
MK13
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: MK13/financer#10
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?