From 292048edaf0e2d4c4d591cfec1d923cb6d845991 Mon Sep 17 00:00:00 2001 From: Mark Powers Date: Sun, 11 Oct 2020 21:47:02 -0500 Subject: Add edit ledger --- README.md | 9 +- src/index.js | 2 +- src/server.js | 73 +++++++++----- src/static/main.js | 222 ++--------------------------------------- src/templates.js | 2 +- src/templates/index.html | 210 -------------------------------------- src/templates/ledger-edit.html | 31 ++++++ src/templates/ledger.html | 5 + 8 files changed, 103 insertions(+), 451 deletions(-) delete mode 100644 src/templates/index.html create mode 100644 src/templates/ledger-edit.html diff --git a/README.md b/README.md index 62b1068..a51fe9b 100644 --- a/README.md +++ b/README.md @@ -1,6 +1,9 @@ # marks.budget -# Installation +## Usage +Manage your personal budget with this site. You can track a ledger of expenses, goals, and recurring expected expenses. A summary view breaks down differences weekly, monthly, and yearly. + +## Installation Download the repository, run `npm install`, set up a mysql server with a user and a database, fill out a `config.json` file as specified below, and then `npm run run`. @@ -17,3 +20,7 @@ fill out a `config.json` file as specified below, and then `npm run run`. } ``` +## TODO +- Add graphs +- Add initial balance somewhere +- Add assets/liabilities? diff --git a/src/index.js b/src/index.js index 8e18272..2f3763c 100644 --- a/src/index.js +++ b/src/index.js @@ -22,7 +22,7 @@ const jwtFunctions = { const database = new Sequelize(dbCreds.database, dbCreds.user, dbCreds.password, { logging(str) { - console.debug(`DB:${str}`); + console.debug(`DB: ${str}`); }, dialectOptions: { charset: 'utf8mb4', diff --git a/src/server.js b/src/server.js index 487fe0c..b7b5ce4 100644 --- a/src/server.js +++ b/src/server.js @@ -69,6 +69,13 @@ function setUpRoutes(models, jwtFunctions, database, templates) { let body = templates["ledger"]({ name, ledger }) res.status(200).send(body) }) + server.get('/ledger/edit/:id', async (req, res) => { + let ledger = await database.query(`SELECT * FROM transactions WHERE username = '${res.locals.user.username}' and id='${req.params.id}' ORDER BY \`when\` DESC`, { type: database.QueryTypes.SELECT }) + let ledger_item = ledger[0] + let name = res.locals.user.username + let body = templates["ledger-edit"]({ name, item: ledger_item }) + res.status(200).send(body) + }) server.get('/goals', async (req, res) => { let goals = await database.query(`SELECT * FROM goals WHERE username = '${res.locals.user.username}' ORDER BY \`name\` DESC`, { type: database.QueryTypes.SELECT }) goals.forEach((element, i) => { @@ -95,33 +102,15 @@ function setUpRoutes(models, jwtFunctions, database, templates) { }) server.get(`/summary`, async (req, res, next) => { try { - let data = { - week: { - out: await database.query(`SELECT year(\`when\`) as y, week(\`when\`) as w, sum(amount) as s FROM transactions where username = '${res.locals.user.username}' and amount > 0 group by year(\`when\`), WEEK(\`when\`);`, { type: database.QueryTypes.SELECT }), - in: await database.query(`SELECT year(\`when\`)as y, week(\`when\`) as w, sum(amount) as s FROM transactions where username = '${res.locals.user.username}' and amount < 0 group by year(\`when\`), WEEK(\`when\`);`, { type: database.QueryTypes.SELECT }), - net: await database.query(`SELECT year(\`when\`) as y, week(\`when\`) as w, sum(amount) as s FROM transactions where username = '${res.locals.user.username}' group by year(\`when\`), WEEK(\`when\`);`, { type: database.QueryTypes.SELECT }), - }, - month: { - out: await database.query(`SELECT year(\`when\`) as y, month(\`when\`) as m, sum(amount) as s FROM transactions where username = '${res.locals.user.username}' and amount > 0 group by year(\`when\`), month(\`when\`);`, { type: database.QueryTypes.SELECT }), - in: await database.query(`SELECT year(\`when\`) as y, month(\`when\`) as m, sum(amount) as s FROM transactions where username = '${res.locals.user.username}' and amount < 0 group by year(\`when\`), month(\`when\`);`, { type: database.QueryTypes.SELECT }), - net: await database.query(`SELECT year(\`when\`) as y, month(\`when\`) as m, sum(amount) as s FROM transactions where username = '${res.locals.user.username}' group by year(\`when\`), month(\`when\`);`, { type: database.QueryTypes.SELECT }), - }, - year: { - out: await database.query(`SELECT year(\`when\`) as y, sum(amount) as s FROM transactions where username = '${res.locals.user.username}' and amount > 0 group by year(\`when\`);`, { type: database.QueryTypes.SELECT }), - in: await database.query(`SELECT year(\`when\`) as y, sum(amount) as s FROM transactions where username = '${res.locals.user.username}' and amount < 0 group by year(\`when\`);`, { type: database.QueryTypes.SELECT }), - net: await database.query(`SELECT year(\`when\`) as y, sum(amount) as s FROM transactions where username = '${res.locals.user.username}' group by year(\`when\`);`, { type: database.QueryTypes.SELECT }), - }, - name: res.locals.user.username - }; - data = formatSummary(data) + let data = await formatSummary(database, res.locals.user.username) let body = templates["summary"](data) res.status(200).send(body); - } catch (e) { console.log(e) res.status(400).send(e.message); } }) + server.post('/login', async (req, res, next) => { @@ -140,7 +129,6 @@ function setUpRoutes(models, jwtFunctions, database, templates) { server.post(`/transaction`, async (req, res, next) => { try { let item = req.body; - console.log(item); item.username = res.locals.user.username if (!item.when) { item.when = new Date().toLocaleDateString(); @@ -187,6 +175,28 @@ function setUpRoutes(models, jwtFunctions, database, templates) { res.status(400).send(e.message); } }) + server.post(`/transaction/:id`, async (req, res, next) => { + try { + let id = req.params.id; + let update = req.body; + if(update.when.length == 0){ + delete update.when + } + var toUpdate = await models.transaction.findOne({ where: { id: id, username:res.locals.user.username } }); + await toUpdate.update(update); + res.redirect(`/ledger`) + } catch (e) { + console.log(e); + res.status(400).send(e.message); + } + }) + + server.delete('/ledger/:id', async (req, res) => { + let id = req.params.id; + console.log(id, res.locals.user.username) + await models.transaction.destroy({ where: { id, username: res.locals.user.username } }); + res.redirect('/ledger') + }) } var findOrCreateWeek = function (summary, el) { @@ -220,7 +230,24 @@ var findOrCreateYear = function (summary, el) { return item } -function formatSummary(response) { +async function formatSummary(database, username) { + let response = { + week: { + out: await database.query(`SELECT year(\`when\`) as y, week(\`when\`) as w, sum(amount) as s FROM transactions where username = '${username}' and amount > 0 group by year(\`when\`), WEEK(\`when\`);`, { type: database.QueryTypes.SELECT }), + in: await database.query(`SELECT year(\`when\`)as y, week(\`when\`) as w, sum(amount) as s FROM transactions where username = '${username}' and amount < 0 group by year(\`when\`), WEEK(\`when\`);`, { type: database.QueryTypes.SELECT }), + net: await database.query(`SELECT year(\`when\`) as y, week(\`when\`) as w, sum(amount) as s FROM transactions where username = '${username}' group by year(\`when\`), WEEK(\`when\`);`, { type: database.QueryTypes.SELECT }), + }, + month: { + out: await database.query(`SELECT year(\`when\`) as y, month(\`when\`) as m, sum(amount) as s FROM transactions where username = '${username}' and amount > 0 group by year(\`when\`), month(\`when\`);`, { type: database.QueryTypes.SELECT }), + in: await database.query(`SELECT year(\`when\`) as y, month(\`when\`) as m, sum(amount) as s FROM transactions where username = '${username}' and amount < 0 group by year(\`when\`), month(\`when\`);`, { type: database.QueryTypes.SELECT }), + net: await database.query(`SELECT year(\`when\`) as y, month(\`when\`) as m, sum(amount) as s FROM transactions where username = '${username}' group by year(\`when\`), month(\`when\`);`, { type: database.QueryTypes.SELECT }), + }, + year: { + out: await database.query(`SELECT year(\`when\`) as y, sum(amount) as s FROM transactions where username = '${username}' and amount > 0 group by year(\`when\`);`, { type: database.QueryTypes.SELECT }), + in: await database.query(`SELECT year(\`when\`) as y, sum(amount) as s FROM transactions where username = '${username}' and amount < 0 group by year(\`when\`);`, { type: database.QueryTypes.SELECT }), + net: await database.query(`SELECT year(\`when\`) as y, sum(amount) as s FROM transactions where username = '${username}' group by year(\`when\`);`, { type: database.QueryTypes.SELECT }), + }, + }; let summary = {} summary.week = []; summary.month = []; @@ -295,7 +322,7 @@ function formatSummary(response) { return a.y - b.y; }) - summary.name = response.name + summary.name = username return summary } diff --git a/src/static/main.js b/src/static/main.js index 0a381b0..1bcf5e5 100644 --- a/src/static/main.js +++ b/src/static/main.js @@ -1,216 +1,8 @@ -window.onload = function () { - var transactionData = new Vue({ - el: '#data', - data: { - activeTab: "ledger", - transactions: [], - summary: {username : ""}, - selTodoType: "all", - total_to_allocate: 0, - goals: [], - expected: [] - }, - methods: { - setTab: function (value) { - this.activeTab = value; - }, - clearData: function () { - this.m = { - when: new Date().toLocaleDateString(), - where: "", - amount: "", - category: "", - subcategory: "", - } - this.em = { - when: new Date().toLocaleDateString(), - where: "", - amount: "", - category: "", - subcategory: "", - } - this.ng = { - name: "", - total: "", - amount: 0 - } - this.e = { - name: "", - total: "", - days: 7 - } - this.na = { - selected: "", - amount: "" - } - }, - requestThenUpdate: function (request, app, field) { - fetch(request) - .then(response => response.json()) - .then(response => { - app[field] = response - }); - }, - post: function (obj, path, save_to) { - console.log(obj); - console.log(path); - this.requestThenUpdate(new Request(path, { - method: 'POST', - headers: { - 'Accept': 'application/json', - 'Content-Type': 'application/json' - }, - body: JSON.stringify(obj) - }), this, save_to); - this.clearData(); - }, - remove: function (obj, save_to) { - if (confirm(`Delete transaction?`)) { - this.requestThenUpdate(new Request("/transaction", { - method: 'delete', - headers: { - 'Accept': 'application/json', - 'Content-Type': 'application/json' - }, - body: JSON.stringify(obj) - }), this, save_to) - } - }, - prepareEntryEdit: function(transaction){ - this.em.id=transaction.id; - this.em.where=transaction.where; - this.em.when=transaction.when; - this.em.amount=transaction.amount; - this.em.category=transaction.category; - this.em.subcategory=transaction.subcategory; - this.activeTab='ledger-edit'; - }, - updateMany: function (obj, save_to) { - update = {} - update = obj; - this.requestThenUpdate(new Request("/transaction", { - method: 'put', - headers: { - 'Accept': 'application/json', - 'Content-Type': 'application/json' - }, - body: JSON.stringify({ id: obj.id, update: update }) - }), this, save_to) - }, - }, - created() { - this.clearData(); - fetch(new Request(`/expected`)).then(response => response.json()) - .then(response => this.expected = response); - fetch(new Request(`/goals`)).then(response => response.json()) - .then(response => this.goals = response); - fetch(new Request(`/transaction`)).then(response => response.json()) - .then(response => this.transactions = response); - fetch(new Request(`/summary`)).then(response => response.json()) - .then(response => { - var findOrCreateWeek = function(t, el){ - var item = t.summary.week.find( el2 => { - return el.y == el2.y && el.w == el2.w - }) - if(!item){ - item = {y : el.y, w : el.w, in: 0, out: 0, net: 0} - t.summary.week.push(item); - } - return item - } - var findOrCreateMonth = function(t, el){ - var item = t.summary.month.find( el2 => { - return el.y == el2.y && el.m == el2.m - }) - if(!item){ - item = {y : el.y, m : el.m, in: 0, out: 0, net: 0} - t.summary.month.push(item); - } - return item - } - var findOrCreateYear = function(t, el){ - var item = t.summary.year.find( el2 => { - return el.y == el2.y - }) - if(!item){ - item = {y : el.y, in: 0, out: 0, net: 0} - t.summary.year.push(item); - } - return item - } - - this.summary.week = []; - this.summary.month = []; - this.summary.year = []; - - response.week.in.forEach(el => { - findOrCreateWeek(this, el).in = Math.abs(el.s) - }) - response.week.out.forEach(el => { - findOrCreateWeek(this, el).out = Math.abs(el.s) - }) - response.week.net.forEach(el => { - var item = findOrCreateWeek(this, el); - item.net = el.s - // Note we flip these since income is negative - item.negative = el.s > 0 - item.positive = el.s < 0 - }) - - response.month.in.forEach(el => { - findOrCreateMonth(this, el).in = Math.abs(el.s) - }) - response.month.out.forEach(el => { - findOrCreateMonth(this, el).out = Math.abs(el.s) - }) - response.month.net.forEach(el => { - var item = findOrCreateMonth(this, el); - item.net = el.s - // Note we flip these since income is negative - item.negative = el.s > 0 - item.positive = el.s < 0 - }) - - response.year.in.forEach(el => { - findOrCreateYear(this, el).in = Math.abs(el.s) - }) - response.year.out.forEach(el => { - findOrCreateYear(this, el).out = Math.abs(el.s) - }) - response.year.net.forEach(el => { - var item = findOrCreateYear(this, el); - item.net = el.s - // Note we flip these since income is negative - item.negative = el.s > 0 - item.positive = el.s < 0 - - // -= since its flipped - this.total_to_allocate -= item.net - }) - this.goals.forEach(el => { - this.total_to_allocate -= el.amount - }) - - this.summary.week.sort(function(a, b){ - if ( a.y == b.y ){ return a.w - b.w; } - return a.y-b.y; - }) - this.summary.month.sort(function(a, b){ - if ( a.y == b.y ){ return a.m - b.m; } - return a.y-b.y; - }) - this.summary.year.sort(function(a, b){ - return a.y-b.y; - }) - - seriesX = this.summary.month.map(el => el.y) - - - this.summary.username = response.username - }); - }, - computed: { - - } - }); +function remove(id){ + if (confirm(`Delete transaction?`)) { + let request = new Request(`/ledger/${id}`, { + method: 'delete', + }) + fetch(request).then(r => window.location = "/ledger"); + } } \ No newline at end of file diff --git a/src/templates.js b/src/templates.js index 6e3b541..f7bfdfb 100644 --- a/src/templates.js +++ b/src/templates.js @@ -10,9 +10,9 @@ function loadTemplate(templates, name, filepath){ function setUpTemplates(){ let templates = {}; - loadTemplate(templates, "index", path.join(__dirname, 'templates/index.html')) loadTemplate(templates, "login", path.join(__dirname, 'templates/login.html')) loadTemplate(templates, "ledger", path.join(__dirname, 'templates/ledger.html')) + loadTemplate(templates, "ledger-edit", path.join(__dirname, 'templates/ledger-edit.html')) loadTemplate(templates, "goals", path.join(__dirname, 'templates/goals.html')) loadTemplate(templates, "expected", path.join(__dirname, 'templates/expected.html')) loadTemplate(templates, "summary", path.join(__dirname, 'templates/summary.html')) diff --git a/src/templates/index.html b/src/templates/index.html deleted file mode 100644 index 525f8fe..0000000 --- a/src/templates/index.html +++ /dev/null @@ -1,210 +0,0 @@ - - - - - Budget - - - - - - - - - - -
-

{{summary.username}}'s budget

-
- - - - -
- - -
-
- - - - - - -
- - - - - - - - - - - - - - - - - - - - -
DateWhereAmountCategorySubcategory
{{i+1}}{{transaction.when.substring(0,10)}}{{transaction.where}}{{transaction.amount}}{{transaction.category}}{{transaction.subcategory}} - - -
-
- - - - - - - - - - - - - - - - - - - -
DateWhereAmountCategorySubcategory
- - - - - - - - - -
- - -
-
-

Weekly

- - - - - - - - - - - - - - - -
YearWeekInOutNet
{{data.y}}{{data.w}}{{data.in}}{{data.out}}{{data.net}} -
-
-
-

Monthly

- - - - - - - - - - - - - - - -
YearMonthInOutNet
{{data.y}}{{data.m}}{{data.in}}{{data.out}}{{data.net}} -
-
-
-

Yearly

- - - - - - - - - - - - - -
YearInOutNet
{{data.y}}{{data.in}}{{data.out}}{{data.net}} -
-
-
- - -
-
-
- New Goal - - - -
-
- Allocate funds - - out of {{total_to_allocate}} - - -
- - - - - - - - - -
NameAmountTotalRemaining
{{i+1}}{{goal.name}}{{goal.amount}}{{goal.total}}{{goal.total - goal.amount}}
-
-
- - -
-
-
- New Expected - - - - -
- - - - - - - - -
NameTotalPeriod
{{i+1}}{{e.name}}{{e.total}}{{e.days}}
-
-
-
- - - \ No newline at end of file diff --git a/src/templates/ledger-edit.html b/src/templates/ledger-edit.html new file mode 100644 index 0000000..2acc2c2 --- /dev/null +++ b/src/templates/ledger-edit.html @@ -0,0 +1,31 @@ + + + + + {{name}}'s Budget + + + + + +

{{name}}'s Budget

+
+ + + + +
+
+ + + + + + +
+ + + + diff --git a/src/templates/ledger.html b/src/templates/ledger.html index 0ac9b0e..f5c46de 100644 --- a/src/templates/ledger.html +++ b/src/templates/ledger.html @@ -5,6 +5,7 @@ {{name}}'s Budget + @@ -31,6 +32,8 @@ Amount Category Tags + + {{#each ledger}} @@ -40,6 +43,8 @@ {{this.amount}} {{this.category}} {{this.subcategory}} + + {{/each}} -- cgit v1.2.3