From 94fb38271b39c155b804a67ed915a9136e021780 Mon Sep 17 00:00:00 2001 From: Jesse Luehrs Date: Fri, 6 Sep 2019 03:52:44 -0400 Subject: add tins to the denorm tables --- data/denorm.sql | 318 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 318 insertions(+) create mode 100644 data/denorm.sql (limited to 'data') diff --git a/data/denorm.sql b/data/denorm.sql new file mode 100644 index 0000000..17c535a --- /dev/null +++ b/data/denorm.sql @@ -0,0 +1,318 @@ +DROP VIEW IF EXISTS future_transactions; +DROP VIEW IF EXISTS denorm_scheduled_transactions; +DROP VIEW IF EXISTS denorm_transactions; + +CREATE VIEW denorm_transactions AS ( + WITH + transactions_with_subtransactions AS ( + SELECT + transactions.id, + subtransactions.id AS subtransaction_id, + transactions.date, + coalesce(subtransactions.amount, transactions.amount) AS amount, + coalesce(subtransactions.memo, transactions.memo) AS memo, + transactions.cleared, + transactions.approved, + transactions.flag_color, + transactions.account_id, + coalesce(subtransactions.payee_id, transactions.payee_id) AS payee_id, + coalesce(subtransactions.category_id, transactions.category_id) AS category_id, + coalesce(subtransactions.transfer_account_id, transactions.transfer_account_id) AS transfer_account_id + FROM + transactions LEFT JOIN subtransactions ON ( + transactions.id = subtransactions.transaction_id + ) + ) + SELECT + transactions_with_subtransactions.id, + subtransaction_id, + date, + amount / 1000.0 as amount, + memo, + cleared, + approved, + flag_color, + tins.name as nonprofit_name, + tins.tin as nonprofit_tin, + account_id, + accounts.name AS account, + payee_id, + payees.name AS payee, + category_group_id, + category_groups.name AS category_group, + category_id, + categories.name AS category, + transactions_with_subtransactions.transfer_account_id, + transfer_accounts.name AS transfer_account + FROM + transactions_with_subtransactions LEFT JOIN accounts ON ( + transactions_with_subtransactions.account_id = accounts.id + ) LEFT JOIN payees ON ( + transactions_with_subtransactions.payee_id = payees.id + ) LEFT JOIN categories ON ( + transactions_with_subtransactions.category_id = categories.id + ) LEFT JOIN category_groups ON ( + categories.category_group_id = category_groups.id + ) LEFT JOIN accounts transfer_accounts ON ( + transactions_with_subtransactions.transfer_account_id = transfer_accounts.id + ) LEFT JOIN tins ON ( + tins.tin = regexp_match(memo, '\mtin:([^ ]+)')[1] + ) +); + +CREATE VIEW denorm_scheduled_transactions AS ( + WITH + scheduled_transactions_with_subtransactions AS ( + SELECT + scheduled_transactions.id, + scheduled_subtransactions.id AS scheduled_subtransaction_id, + scheduled_transactions.date, + scheduled_transactions.frequency, + coalesce(scheduled_subtransactions.amount, scheduled_transactions.amount) AS amount, + coalesce(scheduled_subtransactions.memo, scheduled_transactions.memo) AS memo, + scheduled_transactions.flag_color, + scheduled_transactions.account_id, + coalesce(scheduled_subtransactions.payee_id, scheduled_transactions.payee_id) AS payee_id, + coalesce(scheduled_subtransactions.category_id, scheduled_transactions.category_id) AS category_id, + coalesce(scheduled_subtransactions.transfer_account_id, scheduled_transactions.transfer_account_id) AS transfer_account_id + FROM + scheduled_transactions LEFT JOIN scheduled_subtransactions ON ( + scheduled_transactions.id = scheduled_subtransactions.scheduled_transaction_id + ) + ) + SELECT + scheduled_transactions_with_subtransactions.id, + scheduled_subtransaction_id, + date, + frequency, + amount / 1000.0 as amount, + memo, + flag_color, + tins.name as nonprofit_name, + tins.tin as nonprofit_tin, + account_id, + accounts.name AS account, + payee_id, + payees.name AS payee, + category_group_id, + category_groups.name AS category_group, + category_id, + categories.name AS category, + scheduled_transactions_with_subtransactions.transfer_account_id, + transfer_accounts.name AS transfer_account + FROM + scheduled_transactions_with_subtransactions LEFT JOIN accounts ON ( + scheduled_transactions_with_subtransactions.account_id = accounts.id + ) LEFT JOIN payees ON ( + scheduled_transactions_with_subtransactions.payee_id = payees.id + ) LEFT JOIN categories ON ( + scheduled_transactions_with_subtransactions.category_id = categories.id + ) LEFT JOIN category_groups ON ( + categories.category_group_id = category_groups.id + ) LEFT JOIN accounts transfer_accounts ON ( + scheduled_transactions_with_subtransactions.transfer_account_id = transfer_accounts.id + ) LEFT JOIN tins ON ( + tins.tin = regexp_match(memo, '\mtin:([^ ]+)')[1] + ) +); + +CREATE VIEW future_transactions AS ( + WITH + daily AS ( + SELECT + 'daily'::frequency_t AS frequency, + (ints.i - 1) * interval '1 day' AS span + FROM + ints + WHERE + ints.i <= 750 + ), + weekly AS ( + SELECT + 'weekly'::frequency_t AS frequency, + (ints.i - 1) * interval '1 week' AS span + FROM + ints + WHERE + ints.i <= 120 + ), + every_other_week AS ( + SELECT + 'everyOtherWeek'::frequency_t AS frequency, + (ints.i - 1) * interval '2 weeks' AS span + FROM + ints + WHERE + ints.i <= 60 + ), + twice_a_month AS ( + SELECT + 'twiceAMonth'::frequency_t AS frequency, + make_interval(months => a.i, days => b.i) AS span + FROM + ints a CROSS JOIN ints b + WHERE + a.i <= 30 and (b.i = 0 or b.i = 15) + ), + every_four_weeks AS ( + SELECT + 'every4Weeks'::frequency_t AS frequency, + (ints.i - 1) * interval '4 weeks' AS span + FROM + ints + WHERE + ints.i <= 30 + ), + monthly AS ( + SELECT + 'monthly'::frequency_t AS frequency, + (ints.i - 1) * interval '1 month' AS span + FROM + ints + WHERE + ints.i <= 30 + ), + every_other_month AS ( + SELECT + 'everyOtherMonth'::frequency_t AS frequency, + (ints.i - 1) * interval '2 months' AS span + FROM + ints + WHERE + ints.i <= 15 + ), + every_three_months AS ( + SELECT + 'every3Months'::frequency_t AS frequency, + (ints.i - 1) * interval '3 months' AS span + FROM + ints + WHERE + ints.i <= 10 + ), + every_four_months AS ( + SELECT + 'every4Months'::frequency_t AS frequency, + (ints.i - 1) * interval '4 months' AS span + FROM + ints + WHERE + ints.i <= 10 + ), + twice_a_year AS ( + SELECT + 'twiceAYear'::frequency_t AS frequency, + (ints.i - 1) * interval '6 months' AS span + FROM + ints + WHERE + ints.i <= 5 + ), + yearly AS ( + SELECT + 'yearly'::frequency_t AS frequency, + (ints.i - 1) * interval '1 year' AS span + FROM + ints + WHERE + ints.i <= 5 + ), + every_other_year AS ( + SELECT + 'everyOtherYear'::frequency_t AS frequency, + (ints.i - 1) * interval '2 years' AS span + FROM + ints + WHERE + ints.i <= 5 + ), + repeated_transactions AS ( + SELECT + id, + scheduled_subtransaction_id, + CASE + WHEN frequency = 'never' THEN + date + WHEN frequency = 'daily' THEN + date + daily.span + WHEN frequency = 'weekly' THEN + date + weekly.span + WHEN frequency = 'everyOtherWeek' THEN + date + every_other_week.span + WHEN frequency = 'twiceAMonth' THEN + date + twice_a_month.span + WHEN frequency = 'every4Weeks' THEN + date + every_four_weeks.span + WHEN frequency = 'monthly' THEN + date + monthly.span + WHEN frequency = 'everyOtherMonth' THEN + date + every_other_month.span + WHEN frequency = 'every3Months' THEN + date + every_three_months.span + WHEN frequency = 'every4Months' THEN + date + every_four_months.span + WHEN frequency = 'twiceAYear' THEN + date + twice_a_year.span + WHEN frequency = 'yearly' THEN + date + yearly.span + WHEN frequency = 'everyOtherYear' THEN + date + every_other_year.span + ELSE + NULL + END AS date, + frequency, + amount, + memo, + flag_color, + nonprofit_name, + nonprofit_tin, + account_id, + account, + payee_id, + payee, + category_group_id, + category_group, + category_id, + category, + transfer_account_id, + transfer_account + FROM + denorm_scheduled_transactions + LEFT JOIN daily USING (frequency) + LEFT JOIN weekly USING (frequency) + LEFT JOIN every_other_week USING (frequency) + LEFT JOIN twice_a_month USING (frequency) + LEFT JOIN every_four_weeks USING (frequency) + LEFT JOIN monthly USING (frequency) + LEFT JOIN every_other_month USING (frequency) + LEFT JOIN every_three_months USING (frequency) + LEFT JOIN every_four_months USING (frequency) + LEFT JOIN twice_a_year USING (frequency) + LEFT JOIN yearly USING (frequency) + LEFT JOIN every_other_year USING (frequency) + ) + SELECT + id, + scheduled_subtransaction_id, + date, + frequency, + amount, + memo, + flag_color, + nonprofit_name, + nonprofit_tin, + account_id, + account, + payee_id, + payee, + category_group_id, + category_group, + category_id, + category, + transfer_account_id, + transfer_account + FROM + repeated_transactions + WHERE + date <= CURRENT_DATE + interval '2 years' +); -- cgit v1.2.3-54-g00ecf