const utils = require(".");
const time = require("./time");
const random_color = require('randomcolor');
async function recalculate_card_payments() {
const curr_moment_param = time.iso();
console.log("todays date is", curr_moment_param);
const credit_cards = await utils.query("SELECT * FROM credit_cards");
return Promise.all(credit_cards.map(async function(credit_card) {
const next_billing_date = time.today();
next_billing_date.set("date", credit_card.billing_date);
const previous_statement_date = time.today();
previous_statement_date.set("date", credit_card.statement_date);
// If the statement date is in the current month and happens after
// today's date then the PREVIOUS statement date must be LAST month
if (time.days_remaining_until(previous_statement_date) > 0) {
previous_statement_date.subtract(1, "month");
}
// If the billing date is in the current month and happens before
// today's date then the NEXT billing date must be NEXT month
if (time.days_remaining_until(next_billing_date) <= 0) {
next_billing_date.add(1, "month");
}
// Now we need to sum all the transactions that have happened
// between the previous statement date and today
const previous_statement_date_param = time.iso(previous_statement_date);
const next_billing_date_param = time.iso(next_billing_date);
console.log("last statement cycle for", credit_card.name, "started on", previous_statement_date_param);
console.log("the next billing date for", credit_card.name, "is on", next_billing_date_param);
// First we need to check if we already have a credit card payment
// stored for the upcoming billing date. If we do and that value
// is marked as an override (was entered through the UI) then we
// abort any further calculations for the current card
const [result] = await utils.query(
"SELECT is_override FROM credit_card_payments WHERE credit_card=? AND date=?",
[credit_card.id, next_billing_date_param]
);
if (result && result.is_override) {
return;
}
// Next we sum up all transactions that have occured between
// the last statement date and today's date
const [{
upcoming_payment
}] = await utils.query(
"SELECT SUM(value) as upcoming_payment FROM transactions WHERE credit_card=? AND date>=? AND date<?",
[credit_card.id, previous_statement_date_param, curr_moment_param]
);
// If the sum of those transactions is 0 then we don't
// bother creating an entry for an upcoming payment.
// The chart code will drop in a 0 value for missing payment data
if (!upcoming_payment) {
return;
}
// Otherwise we insert or update an row with the upcoming payment data
await utils.query(
"INSERT INTO credit_card_payments (credit_card, value, date)" +
"VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE value=VALUES(value)",
[credit_card.id, upcoming_payment, next_billing_date_param]
);
}));
}
async function get_card_payments() {
const credit_cards = await utils.query("SELECT * FROM credit_cards");
const all_payments = await Promise.all(
credit_cards.map(async function(credit_card) {
const payments = await utils.query(
"SELECT value, DATE_FORMAT(date, '%Y-%m-%d') FROM credit_card_payments WHERE credit_card=?",
[credit_card.id]
);
return payments.map(payment => ({
name: credit_card.name,
date: payment.date,
value: payment.value
}));
})
);
return all_payments.flat();
}
async function init_savings_data(today) {
let savings_data = {
total_daily_contribution: 0,
total_accumulated: 0,
datasets: []
};
const savings_goals = await utils.query(
"SELECT id, name, value, accumulated, DATE_FORMAT(target_date, '%Y-%m-%d') as target_date FROM savings_goals"
);
for (const savings_goal of savings_goals) {
const target_date = time.now(savings_goal.target_date, time.format.iso);
const remaining_value = savings_goal.value - savings_goal.accumulated;
const remaining_days = target_date.diff(today, "days");
const daily_contribution = remaining_value / remaining_days;
savings_data.total_daily_contribution += daily_contribution;
savings_data.total_accumulated += savings_goal.accumulated;
savings_data.datasets.push({
label: savings_goal.name,
color: random_color({
luminosity: 'light',
hue: 'green'
}),
data: [],
// This is not chart data
id: savings_goal.id,
target_date: savings_goal.target_date,
accumulated: savings_goal.accumulated,
goal: savings_goal.value,
daily_contribution
});
}
return savings_data;
}
const minimum_available_balance_buffer = 1000;
const preffered_available_balance_buffer = 3000;
const contribute_to_savings_interval = 7;
const minimum_savings_contribution = 500;
async function build_income_date_map() {
const income = await utils.query("SELECT name, value, date, month FROM incomes");
const withdrawals = await utils.query("SELECT name, value, date, month FROM withdrawals");
await recalculate_card_payments();
const card_payments = await get_card_payments();
return [...income, ...withdrawals, ...card_payments].reduce(function(
income_date_map,
individual_transaction
) {
if (!income_date_map[individual_transaction.date]) {
income_date_map[individual_transaction.date] = [];
}
income_date_map[individual_transaction.date].push({
name: individual_transaction.name,
value: individual_transaction.value,
month: individual_transaction.month || 0
});
return income_date_map;
}, {});
}
async function build_chart_data(income_date_map) {
const [balance] = await utils.query("SELECT value FROM balance WHERE id=?", [1]);
let curr_balance = balance.value;
let curr_balance_dataset = {
label: "Current Balance",
color: "#0D6EFD",
data: []
};
let min_balance_dataset = {
label: "Minimum Balance",
color: "#AD1A1A",
data: []
};
let savings_balance_dataset = {
label: "Total Savings",
color: "#00AA22",
data: []
};
let messages = {};
const today = time.today();
const year_from_now = time.today().add(1, "years");
const savings_data = await init_savings_data(today);
console.log(savings_data);
let labels = [];
let tooltip_data = [];
let curr_min = 0;
let count_back = year_from_now.clone();
while (count_back.diff(today, "days") > 0) {
const curr_date = count_back.date();
if (income_date_map[curr_date]) {
const curr_month = count_back.month() + 1;
for (const individual_transaction of income_date_map[curr_date]) {
// console.log(individual_transaction)
if (individual_transaction.month && individual_transaction.month !== curr_month) {
continue;
}
curr_min -= individual_transaction.value;
}
if (curr_min < 0) {
curr_min = 0;
}
}
min_balance_dataset.data.unshift(curr_min);
count_back = count_back.subtract(1, "days");
}
let min_available_balance = Number.MAX_SAFE_INTEGER;
let min_balance_exceeded_date;
let count_forward = time.today();
let date_index = 0;
while (year_from_now.diff(count_forward, "days") > 0) {
const curr_date = count_forward.date();
// Add the current month to the label when the date rolls over
const format = curr_date === 1 || !labels.length ? "MMM - D" : "D";
labels.push(count_forward.format(format));
tooltip_data.push([]);
curr_balance = get_new_balance(income_date_map, count_forward, curr_balance, function(individual_transaction) {
insert_tooltip_line_for_day(
tooltip_data, date_index, count_forward,
`${individual_transaction.name}: $${individual_transaction.value.toFixed(2)}`
);
});
curr_balance_dataset.data.push(curr_balance);
const available_balance = curr_balance - min_balance_dataset.data[date_index];
min_available_balance = Math.min(
available_balance,
min_available_balance
);
if (min_available_balance < 0 && !min_balance_exceeded_date) {
min_balance_exceeded_date = time.iso(count_forward)
}
insert_message(
messages, 'budget_warning',
() => min_available_balance > 0 && available_balance <= minimum_available_balance_buffer,
() => ({
icon: 'warning',
title: "Available Balance Threshold Warning",
text: `Available balance will be only $${available_balance} on ${time.iso(count_forward)}`,
date_index: date_index
})
);
date_index++;
count_forward.add(1, "days");
}
insert_message(
messages, 'budget_error',
() => min_available_balance <= 0,
() => ({
icon: 'error',
title: "Minimum Balance Exceeded",
text: `At least $${-min_available_balance} needs to be transferred from savings before ${min_balance_exceeded_date}`,
date_index: date_index
})
);
let days_since_last_savings_contribution = Number.MAX_SAFE_INTEGER;
let starting_accumulated_value = savings_data.total_accumulated;
let amount_contributed_to_savings_so_far = 0;
let amount_that_needs_to_be_contributed_to_stay_on_track = savings_data.total_daily_contribution;
count_forward = time.today();
date_index = 0;
let num_contributions = 0;
let date_until_next_savings_contribution = 0
const [savings_contribution] = await utils.query(
"SELECT value, DATE_FORMAT(date, '%Y-%m-%d') as date FROM savings_contributions"
);
let insert_upcoming_contribution = true;
if (savings_contribution) {
const contribution_date = time.now(savings_contribution.date, time.format.iso);
date_until_next_savings_contribution = time.days_remaining_until(contribution_date);
if (date_until_next_savings_contribution < 0) {
// Contribution was in the past, need to insert a new upcoming contribution
await utils.query("DELETE FROM savings_contributions");
} else {
insert_upcoming_contribution = false;
num_contributions++;
}
}
while (year_from_now.diff(count_forward, "days") > 0) {
amount_that_needs_to_be_contributed_to_stay_on_track += savings_data.total_daily_contribution;
const formatted_date = time.iso(count_forward);
let contribution = 0;
if (date_index >= date_until_next_savings_contribution) {
contribution = get_savings_contribution(
days_since_last_savings_contribution,
amount_that_needs_to_be_contributed_to_stay_on_track,
curr_balance_dataset,
min_balance_dataset,
date_index
);
}
if (contribution > 0) {
console.log("WE CAN CONTRIBUTE TO SAVINGS!", formatted_date, contribution);
num_contributions++
insert_tooltip_line_for_day(
tooltip_data, date_index, count_forward,
`Suggested Savings Contribution: -$${contribution}`
);
if (insert_upcoming_contribution) {
await utils.query(
"INSERT INTO savings_contributions SET value=?, date=?",
[contribution, formatted_date]
);
insert_upcoming_contribution = false;
}
for (const savings_dataset of savings_data.datasets) {
if (savings_dataset.accumulated < savings_dataset.goal) {
const savings_proportion = savings_dataset.daily_contribution / savings_data.total_daily_contribution;
// Don't overcontribute to savings
const amount_to_add_to_savings = Math.min(
savings_proportion * contribution,
savings_dataset.goal - savings_dataset.accumulated
);
console.log("Contributing", amount_to_add_to_savings, "to", savings_dataset.label, "on", formatted_date);
savings_dataset.accumulated += amount_to_add_to_savings;
savings_data.total_accumulated += amount_to_add_to_savings;
// Using >= here but really accumulated value should never exceed goal value
if (savings_dataset.accumulated >= savings_dataset.goal) {
// Once we reach out savings goal we no longer need to
// contribute to it. All other goals will get a larger
// proportion of the contribution now
savings_data.total_daily_contribution -= savings_dataset.daily_contribution;
}
}
}
amount_contributed_to_savings_so_far += contribution;
days_since_last_savings_contribution = 0;
amount_that_needs_to_be_contributed_to_stay_on_track -= contribution;
}
curr_balance_dataset.data[date_index] -= amount_contributed_to_savings_so_far;
savings_balance_dataset.data.push(savings_data.total_accumulated);
for (const savings_dataset of savings_data.datasets) {
savings_dataset.data.push(savings_dataset.accumulated);
insert_message(
messages, `savings_error_${savings_dataset.id}`,
() => savings_dataset.target_date === formatted_date && savings_dataset.accumulated < savings_dataset.goal,
() => {
const short_of_goal = (savings_dataset.goal - savings_dataset.accumulated).toFixed(2);
return {
icon: 'error',
title: "Savings Goal Unachievable",
text: `Given contribution requirements for all savings goals, "${savings_dataset.label}" will be $${short_of_goal} short by target date of ${savings_dataset.target_date}`,
date_index: date_index
};
}
);
}
date_index++;
days_since_last_savings_contribution++;
count_forward.add(1, "days");
}
// TODO: This doesn't math out exactly correctly - there should be edge cases where I get a warning about unreachable goals
let avg_contribution_rate = (savings_data.total_accumulated - starting_accumulated_value) / num_contributions
console.log(avg_contribution_rate)
if (amount_that_needs_to_be_contributed_to_stay_on_track > avg_contribution_rate) {
// We don't calculate past 1 year of the budget
// so we cannot know for sure if we can reach
// savings goals that are 5, 10, 20 years in
// the future. The best we can do is report the
// deficit in the contribution required to stay
// on track (with the current 1 year budget)
// and allow the user to extrapolate the
// information into the distant future.
messages['savings_warning'] = {
icon: 'warning',
title: "Long term savings goals not on track",
text: `Year over year savings contribution is $${amount_that_needs_to_be_contributed_to_stay_on_track.toFixed(2)} short of target for long term savings goals`
};
}
return {
messages,
labels,
tooltip_data,
datasets: [
curr_balance_dataset,
min_balance_dataset,
savings_balance_dataset,
...savings_data.datasets
]
};
}
function get_savings_contribution(
days_since_last_savings_contribution,
amount_that_needs_to_be_contributed_to_stay_on_track,
curr_balance_dataset,
min_balance_dataset,
date_index
) {
//console.log("days since last contribution", days_since_last_savings_contribution)
if (days_since_last_savings_contribution - contribute_to_savings_interval >= 0) {
const available_balance_of_current_day = curr_balance_dataset.data[date_index] - min_balance_dataset.data[date_index] - preffered_available_balance_buffer;
//console.log("available balance to contribute to savings", available_balance_of_current_day)
const contribution = Math.ceil(Math.min(
available_balance_of_current_day,
amount_that_needs_to_be_contributed_to_stay_on_track
));
//console.log("contribution", contribution)
if (contribution > minimum_savings_contribution) {
return contribution;
}
}
return 0;
}
function insert_tooltip_line_for_day(tooltip_data, date_index, curr_moment, tooltip_line) {
if (!tooltip_data[date_index].length) {
// Insert header if necessary
tooltip_data[date_index].push(
curr_moment.format("MMM Do [Transactions:]")
);
}
tooltip_data[date_index].push(tooltip_line);
}
function insert_message(messages, id, condition, build_message) {
if (!messages[id] && condition()) {
messages[id] = build_message();
}
}
function get_new_balance(income_date_map, date, curr_balance, extra_fn) {
for (const key of [time.iso(date), date.date()]) {
if (income_date_map[key]) {
// moment has Jan as month 0, but we are using months 1-12, with 0 representing every month
const curr_month = date.month() + 1;
for (const individual_transaction of income_date_map[key]) {
if (individual_transaction.month && individual_transaction.month !== curr_month) {
continue;
}
curr_balance += individual_transaction.value;
if (extra_fn) {
extra_fn(individual_transaction);
}
}
}
}
return curr_balance;
}
let cached_chart_data;
async function recalculate_budget() {
let income_date_map = await build_income_date_map();
const today = time.today();
const formatted_date = time.iso();
const [balance] = await utils.query(
"SELECT id, value FROM balance where last_auto_update IS NULL OR last_auto_update<?",
[formatted_date]
)
if (balance) {
const updated_balance = get_new_balance(income_date_map, today, balance.value);
await utils.query(
"UPDATE balance SET value=?, last_auto_update=? WHERE id=?",
[updated_balance, formatted_date, balance.id]
);
}
cached_chart_data = await build_chart_data(income_date_map);
}
time.add_to_daily_queue(recalculate_budget);
module.exports = {
contribute_to_savings: async function(value) {
const savings_data = await init_savings_data(time.today());
await Promise.all(savings_data.datasets.map(async function(savings_dataset) {
const savings_proportion = savings_dataset.daily_contribution / savings_data.total_daily_contribution;
const amount_to_add_to_savings = savings_proportion * value;
const new_accumulated_value = savings_dataset.data[0] + amount_to_add_to_savings;
console.log("Contributing", amount_to_add_to_savings, "to", savings_dataset.label, "new value:", new_accumulated_value);
return await utils.query(
"UPDATE savings_goals SET accumulated=? WHERE id=?",
[new_accumulated_value, savings_dataset.id]
);
}));
const [{
prev_balance
}] = await utils.query("SELECT value as prev_balance FROM balance WHERE id=?", [1]);
await utils.query("UPDATE balance" + utils.set_where({
value: prev_balance - value
}, {
id: 1
}));
await utils.query(
"INSERT INTO savings_contributions (id, value, date) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE " +
"value=VALUES(value), date=VALUES(date)",
[1, value, time.iso()]
);
await recalculate_budget();
},
get_card_payments,
recalculate_card_payments: async function() {
await recalculate_card_payments();
await recalculate_budget();
},
recalculate_budget,
get_chart_data: async function() {
if (!cached_chart_data) {
// This is an expensive operation!!
await recalculate_budget();
}
return cached_chart_data;
}
}