163 lines
6.7 KiB
SQL
163 lines
6.7 KiB
SQL
PRAGMA foreign_keys=OFF;
|
|
BEGIN TRANSACTION;
|
|
CREATE TABLE account_configurations (
|
|
id INTEGER NOT NULL,
|
|
account VARCHAR,
|
|
kind VARCHAR,
|
|
data JSON,
|
|
PRIMARY KEY(id)
|
|
);
|
|
INSERT INTO account_configurations VALUES(1,'Business Loan','drcr.liability',NULL);
|
|
INSERT INTO account_configurations VALUES(2,'Cash at Bank','drcr.asset',NULL);
|
|
INSERT INTO account_configurations VALUES(3,'Cash on Hand','drcr.asset',NULL);
|
|
INSERT INTO account_configurations VALUES(4,'Cost of Goods Sold','drcr.expense',NULL);
|
|
INSERT INTO account_configurations VALUES(5,'Depreciation','drcr.expense',NULL);
|
|
INSERT INTO account_configurations VALUES(6,'Forex Gains','drcr.income',NULL);
|
|
INSERT INTO account_configurations VALUES(7,'Interest','drcr.expense',NULL);
|
|
INSERT INTO account_configurations VALUES(8,'International Account','drcr.asset',NULL);
|
|
INSERT INTO account_configurations VALUES(9,'Inventory','drcr.asset',NULL);
|
|
INSERT INTO account_configurations VALUES(10,'Opening Balances','drcr.equity',NULL);
|
|
INSERT INTO account_configurations VALUES(11,'Plant','drcr.asset',NULL);
|
|
INSERT INTO account_configurations VALUES(12,'Plant:Less Accumulated Depreciation','drcr.asset',NULL);
|
|
INSERT INTO account_configurations VALUES(13,'Sales','drcr.income',NULL);
|
|
CREATE TABLE balance_assertions (
|
|
id INTEGER NOT NULL,
|
|
dt DATETIME,
|
|
description VARCHAR,
|
|
account VARCHAR,
|
|
quantity INTEGER,
|
|
commodity VARCHAR,
|
|
PRIMARY KEY(id)
|
|
);
|
|
CREATE TABLE metadata (
|
|
id INTEGER NOT NULL,
|
|
key VARCHAR,
|
|
value VARCHAR,
|
|
PRIMARY KEY(id)
|
|
);
|
|
INSERT INTO metadata VALUES(1,'version','3');
|
|
INSERT INTO metadata VALUES(2,'eofy_date','2025-06-30');
|
|
INSERT INTO metadata VALUES(3,'reporting_commodity','$');
|
|
INSERT INTO metadata VALUES(4,'amount_dps','2');
|
|
INSERT INTO metadata VALUES(5,'plugins','');
|
|
CREATE TABLE postings (
|
|
id INTEGER NOT NULL,
|
|
transaction_id INTEGER,
|
|
description VARCHAR,
|
|
account VARCHAR,
|
|
quantity INTEGER,
|
|
commodity VARCHAR,
|
|
PRIMARY KEY(id),
|
|
FOREIGN KEY(transaction_id) REFERENCES transactions(id)
|
|
);
|
|
INSERT INTO postings VALUES(1,1,NULL,'Cash at Bank',100000,'$');
|
|
INSERT INTO postings VALUES(2,1,NULL,'Opening Balances',-100000,'$');
|
|
INSERT INTO postings VALUES(3,2,NULL,'Cash on Hand',5000,'$');
|
|
INSERT INTO postings VALUES(4,2,NULL,'Opening Balances',-5000,'$');
|
|
INSERT INTO postings VALUES(5,3,NULL,'Inventory',10000,'Widgets {5.00}');
|
|
INSERT INTO postings VALUES(6,3,NULL,'Opening Balances',-10000,'Widgets {5.00}');
|
|
INSERT INTO postings VALUES(7,4,NULL,'Plant',500000,'$');
|
|
INSERT INTO postings VALUES(8,4,NULL,'Opening Balances',-500000,'$');
|
|
INSERT INTO postings VALUES(9,5,NULL,'Cash at Bank',50000,'$');
|
|
INSERT INTO postings VALUES(10,5,NULL,'Business Loan',-50000,'$');
|
|
INSERT INTO postings VALUES(11,6,NULL,'International Account',10000,'EUR {1.75}');
|
|
INSERT INTO postings VALUES(12,6,NULL,'Cash at Bank',-17500,'$');
|
|
INSERT INTO postings VALUES(13,7,NULL,'Inventory',5000,'Widgets {7.00}');
|
|
INSERT INTO postings VALUES(14,7,NULL,'Cash at Bank',-35000,'$');
|
|
INSERT INTO postings VALUES(15,8,NULL,'Cash at Bank',10000,'$');
|
|
INSERT INTO postings VALUES(16,8,NULL,'Sales',-10000,'$');
|
|
INSERT INTO postings VALUES(17,9,NULL,'Cost of Goods Sold',5000,'$');
|
|
INSERT INTO postings VALUES(18,9,NULL,'Inventory',-1000,'Widgets {5.00}');
|
|
INSERT INTO postings VALUES(19,10,NULL,'International Account',10000,'EUR {1.70}');
|
|
INSERT INTO postings VALUES(20,10,NULL,'Cash at Bank',-17000,'$');
|
|
INSERT INTO postings VALUES(21,11,NULL,'Cash at Bank',9000,'$');
|
|
INSERT INTO postings VALUES(22,11,NULL,'International Account',-5000,'EUR {1.75}');
|
|
INSERT INTO postings VALUES(23,11,NULL,'Forex Gains',-250,'$');
|
|
INSERT INTO postings VALUES(24,12,NULL,'Interest',10000,'$');
|
|
INSERT INTO postings VALUES(25,12,NULL,'Business Loan',-10000,'$');
|
|
INSERT INTO postings VALUES(26,13,NULL,'Depreciation',50000,'$');
|
|
INSERT INTO postings VALUES(27,13,NULL,'Plant:Less Accumulated Depreciation',-50000,'$');
|
|
CREATE TABLE statement_line_reconciliations (
|
|
id INTEGER NOT NULL,
|
|
statement_line_id INTEGER,
|
|
posting_id INTEGER,
|
|
PRIMARY KEY(id),
|
|
FOREIGN KEY(statement_line_id) REFERENCES statement_lines(id),
|
|
FOREIGN KEY(posting_id) REFERENCES postings(id)
|
|
);
|
|
CREATE TABLE statement_lines (
|
|
id INTEGER NOT NULL,
|
|
source_account VARCHAR,
|
|
dt DATETIME,
|
|
description VARCHAR,
|
|
quantity INTEGER,
|
|
balance INTEGER,
|
|
commodity VARCHAR,
|
|
PRIMARY KEY(id)
|
|
);
|
|
CREATE TABLE transactions (
|
|
id INTEGER NOT NULL,
|
|
dt DATETIME,
|
|
description VARCHAR,
|
|
PRIMARY KEY(id)
|
|
);
|
|
INSERT INTO transactions VALUES(1,'2024-06-30 00:00:00.000000','Conversion balances');
|
|
INSERT INTO transactions VALUES(2,'2024-06-30 00:00:00.000000','Conversion balances');
|
|
INSERT INTO transactions VALUES(3,'2024-06-30 00:00:00.000000','Conversion balances');
|
|
INSERT INTO transactions VALUES(4,'2024-06-30 00:00:00.000000','Opening balances');
|
|
INSERT INTO transactions VALUES(5,'2024-07-01 00:00:00.000000','Loan');
|
|
INSERT INTO transactions VALUES(6,'2024-07-02 00:00:00.000000','Application');
|
|
INSERT INTO transactions VALUES(7,'2024-07-03 00:00:00.000000','Inventory purchases');
|
|
INSERT INTO transactions VALUES(8,'2024-07-04 00:00:00.000000','Sale');
|
|
INSERT INTO transactions VALUES(9,'2024-07-04 00:00:00.000000','Sale');
|
|
INSERT INTO transactions VALUES(10,'2024-08-01 00:00:00.000000','Application');
|
|
INSERT INTO transactions VALUES(11,'2024-09-01 00:00:00.000000','Redemption');
|
|
INSERT INTO transactions VALUES(12,'2025-06-30 00:00:00.000000','Interest on business loan');
|
|
INSERT INTO transactions VALUES(13,'2025-06-30 00:00:00.000000','Depreciation');
|
|
CREATE TABLE austax_cgt_cost_adjustments (
|
|
id INTEGER NOT NULL,
|
|
quantity INTEGER,
|
|
commodity VARCHAR,
|
|
account VARCHAR,
|
|
acquisition_dt DATETIME,
|
|
dt DATETIME,
|
|
description VARCHAR,
|
|
cost_adjustment INTEGER,
|
|
PRIMARY KEY (id)
|
|
);
|
|
CREATE VIEW joined_transactions AS
|
|
SELECT transaction_id, dt, transactions.description AS transaction_description, postings.id, postings.description, account, quantity, commodity
|
|
FROM transactions
|
|
JOIN postings ON transactions.id = postings.transaction_id
|
|
ORDER BY dt, transaction_id, postings.id;
|
|
CREATE VIEW transactions_with_quantity_ascost AS
|
|
SELECT
|
|
*,
|
|
CAST(ROUND(
|
|
-- If already in reporting commodity
|
|
IIF(
|
|
commodity = '$',
|
|
quantity,
|
|
-- Else if specified as total cost
|
|
IIF(
|
|
commodity LIKE '% {{%}}',
|
|
substr(commodity, instr(commodity, ' {{') + 3, length(commodity) - instr(commodity, ' {{') - 4) * sign(quantity) * 100,
|
|
-- Else if specified as unit cost
|
|
IIF(
|
|
commodity LIKE '% {%}',
|
|
substr(commodity, instr(commodity, ' {') + 2, length(commodity) - instr(commodity, ' {') - 2) * quantity,
|
|
-- Unexpected
|
|
NULL
|
|
)
|
|
)
|
|
)
|
|
) AS INTEGER) AS quantity_ascost
|
|
FROM joined_transactions
|
|
;
|
|
CREATE VIEW transactions_with_running_balances AS
|
|
SELECT
|
|
*,
|
|
SUM(quantity_ascost) OVER (PARTITION BY account ROWS UNBOUNDED PRECEDING) AS running_balance
|
|
FROM transactions_with_quantity_ascost;
|
|
COMMIT;
|