diff options
author | Mark Powers <markppowers0@gmail.com> | 2019-12-26 20:28:52 -0600 |
---|---|---|
committer | Mark Powers <markppowers0@gmail.com> | 2019-12-26 20:28:52 -0600 |
commit | 60f66450078b0a3a46d4ab373645c2546aab28f4 (patch) | |
tree | 94bc6f8146ca5f861396c454218e6960aa46a45e /src |
initial commit
Diffstat (limited to 'src')
-rw-r--r-- | src/create-user.js | 101 | ||||
-rw-r--r-- | src/index.html | 154 | ||||
-rw-r--r-- | src/index.js | 93 | ||||
-rw-r--r-- | src/login.html | 40 | ||||
-rw-r--r-- | src/main.js | 182 | ||||
-rw-r--r-- | src/server.js | 161 | ||||
-rw-r--r-- | src/styles.css | 72 |
7 files changed, 803 insertions, 0 deletions
diff --git a/src/create-user.js b/src/create-user.js new file mode 100644 index 0000000..899d441 --- /dev/null +++ b/src/create-user.js @@ -0,0 +1,101 @@ +const server = require('./server'); +const Sequelize = require('sequelize'); +const fs = require('fs'); +const path = require('path'); +const crypto = require('crypto'); + +const config = JSON.parse(fs.readFileSync(path.join(__dirname, 'config.json'))); + +const dbCreds = config.database; + +const database = new Sequelize(dbCreds.database, dbCreds.user, dbCreds.password, { + logging(str) { + console.debug(`DB:${str}`); + }, + dialectOptions: { + charset: 'utf8mb4', + multipleStatements: true, + }, + // host: dbCreds.host, + dialect: 'mysql', + pool: { + max: 5, + min: 0, + idle: 10000, + }, +}); + +database.authenticate().then(() => { + console.debug(`database connection successful: ${dbCreds.database}`); +}, (e) => console.log(e)); + +async function sync(alter, force, callback) { + await database.sync({ alter, force, logging: console.log }); +} + +function setUpModels() { + const models = { + "transaction": database.define('transaction', { + when: { + type: Sequelize.DATE, + allowNull: false, + }, + amount: { + type: Sequelize.DECIMAL, + allowNull: false, + }, + where: { + type: Sequelize.STRING, + allowNull: false, + }, + category: { + type: Sequelize.STRING, + allowNull: false, + }, + subcategory: { + type: Sequelize.STRING, + allowNull: false, + }, + }), + "users": database.define('user', { + username: { + type: Sequelize.STRING, + allowNull: false, + }, + password: { + type: Sequelize.STRING, + allowNull: false, + }, + salt: { + type: Sequelize.STRING, + allowNull: false, + },}), + } + return models; +} + +const models = setUpModels(); +sync(); + +function hashWithSalt(password, salt){ + var hash = crypto.createHmac('sha512', salt); + hash.update(password); + return hash.digest("base64"); +}; + +const readline = require('readline-sync'); +let username = readline.question("New username: "); +let password = readline.question("New password: "); +let salt = crypto.randomBytes(32).toString("Base64"); +console.log("Salt", salt); +let hash = hashWithSalt(password, salt) +console.log("Hash", hash); +var newUser ={ + "username": username, + "password": hash, + "salt": salt +} +models.users.create(newUser).then(e =>{ + console.log("done") + console.log(e); +}) diff --git a/src/index.html b/src/index.html new file mode 100644 index 0000000..0703c8b --- /dev/null +++ b/src/index.html @@ -0,0 +1,154 @@ +<!doctype html> +<html lang="en"> + +<head> + <title>Mark's Budget</title> + <meta charset="UTF-8"> + <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> + <link rel="shortcut icon" href="/favicon.ico"> + <!-- <script src="https://cdn.jsdelivr.net/npm/vue"></script> --> + <script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script> + <script src="main.js"></script> + <link rel="stylesheet" type="text/css" href="styles.css"> +</head> + +<body> + <h1>Mark's Budget</h1> + <div id="data"> + <div> + <button v-bind:class="{ bold: activeTab == 0 }" v-on:click="setTab(0)">Ledger</button> + <button v-bind:class="{ bold: activeTab == 1 }" v-on:click="setTab(1)">Summary</button> + </div> + + <!-- Ledger --> + <div v-if="activeTab == 0"> + <div class="newItem"> + <input v-model="m.when" placeholder="Date" type="date"> + <input v-model="m.where" placeholder="Where"> + <input v-model="m.amount" placeholder="Amount" type="number" step="0.01"> + <input v-model="m.category" placeholder="Category"> + <input v-model="m.subcategory" placeholder="Subcategory"> + <button v-on:click="post(m, '/transaction')">Add</button> + </div> + <table class="table"> + <tr class="table-header"> + <th class="table-index"></th> + <th>Date</th> + <th>Where</th> + <th>Amount</th> + <th>Category</th> + <th>Subcategory</th> + <th></th> + </tr> + <tr v-for="(transaction, i) in transactions"> + <td class="table-index">{{i+1}}</td> + <td>{{transaction.when.substring(0,10)}}</td> + <td>{{transaction.where}}</td> + <td>{{transaction.amount}}</td> + <td>{{transaction.category}}</td> + <td>{{transaction.subcategory}}</td> + <td> + <button v-on:click="prepareEntryEdit(transaction)">⚙</button> + <button v-on:click="remove(transaction)">X</button> + </td> + </tr> + + </table> + </div v-if="activeTab == 0"> + + <table v-if="activeTab == 10"> + <tr> + <th></th> + <th>Date</th> + <th>Where</th> + <th>Amount</th> + <th>Category</th> + <th>Subcategory</th> + + </tr> + <tr> + <td></td> + <td> + <input v-model="em.when" placeholder="Date" type="date"> + </td> + <td> + <input v-model="em.where" placeholder="Where"> + </td> + <td> + <input v-model="em.amount" placeholder="Amount" type="number" step="0.01"> + </td> + <td> + <input v-model="em.category" placeholder="Category"> + </td> + <td> + <input v-model="em.subcategory" placeholder="Subcategory"> + </td> + <td><button v-on:click="updateMany(em);activeTab=0;">Update</button></td> + </tr> + </table> + + <!-- Summary --> + <div v-if="activeTab == 1"> + <div class="summary-panel"> + <h2>Weekly</h2> + <table class="table"> + <tr> + <th>Year</th> + <th>Week</th> + <th>In</th> + <th>Out</th> + <th>Net</th> + </tr> + <tr v-for="(data, i) in summary.week"> + <td>{{data.y}}</td> + <td>{{data.w}}</td> + <td>{{data.in}}</td> + <td>{{data.out}}</td> + <td v-bind:class="{'net-negative': data.negative, 'net-positive':data.positive}">{{data.net}} + </td> + </tr> + </table> + </div> + <div class="summary-panel"> + <h2>Monthly</h2> + <table class="table"> + <tr> + <th>Year</th> + <th>Month</th> + <th>In</th> + <th>Out</th> + <th>Net</th> + </tr> + <tr v-for="(data, i) in summary.month"> + <td>{{data.y}}</td> + <td>{{data.m}}</td> + <td>{{data.in}}</td> + <td>{{data.out}}</td> + <td v-bind:class="{'net-negative': data.negative, 'net-positive':data.positive}">{{data.net}} + </td v-bind:class="{'net-negative': data.negative, 'net-positive':data.positive}"> + </tr> + </table> + </div> + <div class="summary-panel"> + <h2>Yearly</h2> + <table class="table"> + <tr> + <th>Year</th> + <th>In</th> + <th>Out</th> + <th>Net</th> + </tr> + <tr v-for="(data, i) in summary.year"> + <td>{{data.y}}</td> + <td>{{data.in}}</td> + <td>{{data.out}}</td> + <td v-bind:class="{'net-negative': data.negative, 'net-positive':data.positive}">{{data.net}} + </td v-bind:class="{'net-negative': data.negative, 'net-positive':data.positive}"> + </tr> + </table> + </div> + </div> + </div> +</body> + +</html>
\ No newline at end of file diff --git a/src/index.js b/src/index.js new file mode 100644 index 0000000..01de277 --- /dev/null +++ b/src/index.js @@ -0,0 +1,93 @@ +const server = require('./server'); +const Sequelize = require('sequelize'); +const fs = require('fs'); +const path = require('path'); +const jwt = require('jsonwebtoken'); + + +const config = JSON.parse(fs.readFileSync(path.join(__dirname, 'config.json'))); + +const dbCreds = config.database; +const secret = config.jwt_secret; + +const jwtFunctions = { + sign: function (message) { + return jwt.sign({ value: message }, secret); + }, + verify: function (token) { + return jwt.verify(token, secret).value; + } +} + +const database = new Sequelize(dbCreds.database, dbCreds.user, dbCreds.password, { + logging(str) { + console.debug(`DB:${str}`); + }, + dialectOptions: { + charset: 'utf8mb4', + multipleStatements: true, + }, + // host: dbCreds.host, + dialect: 'mysql', + pool: { + max: 5, + min: 0, + idle: 10000, + }, +}); + +database.authenticate().then(() => { + console.debug(`database connection successful: ${dbCreds.database}`); +}, (e) => console.log(e)); + +async function sync(alter, force, callback) { + await database.sync({ alter, force, logging: console.log }); +} + +function setUpModels() { + const models = { + "transaction": database.define('transaction', { + when: { + type: Sequelize.DATE, + allowNull: false, + }, + amount: { + type: Sequelize.DECIMAL, + allowNull: false, + }, + where: { + type: Sequelize.STRING, + allowNull: false, + }, + category: { + type: Sequelize.STRING, + allowNull: false, + }, + subcategory: { + type: Sequelize.STRING, + allowNull: false, + }, + }), + "users": database.define('user', { + username: { + type: Sequelize.STRING, + allowNull: false, + }, + password: { + type: Sequelize.STRING, + allowNull: false, + }, + salt: { + type: Sequelize.STRING, + allowNull: false, + },}), + } + return models; +} + +const models = setUpModels(); +sync(); + +server.setUpRoutes(models, jwtFunctions, database); +server.listen(config.port); + diff --git a/src/login.html b/src/login.html new file mode 100644 index 0000000..d1dbe1d --- /dev/null +++ b/src/login.html @@ -0,0 +1,40 @@ +<!doctype html> +<html lang="en"> + +<head> + <title>Mark's Database - Login</title> + <meta charset="UTF-8"> + <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> + <link rel="stylesheet" type="text/css" href="/css/styles.css"> + <script> + function sendPost(){ + let username = document.getElementById('username').value; + let password = document.getElementById('password').value; + fetch(new Request("/login", { + method: 'POST', + headers: { + 'Accept': 'application/json', + 'Content-Type': 'application/json' + }, + body: JSON.stringify({'username':username, 'password':password}) + })) + .then((response) => { + console.log(response); + window.location = "/"; + }); + } + </script> +</head> + +<body> + <div> + <h1>Login</h1> + <div class="form"> + <input type="text" placeholder="Enter Username" name="username" id="username" required> + <input type="password" placeholder="Enter Password" name="password" id="password" required> + <button onclick="sendPost()">Log in</button> + </div> + </div> +</body> + +</html>
\ No newline at end of file diff --git a/src/main.js b/src/main.js new file mode 100644 index 0000000..6dbc3c0 --- /dev/null +++ b/src/main.js @@ -0,0 +1,182 @@ +window.onload = function () { + var transactionData = new Vue({ + el: '#data', + data: { + activeTab: 0, + transactions: [], + summary: {}, + selTodoType: "all", + }, + 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: "", + } + }, + requestThenUpdate: function (request) { + fetch(request) + .then(response => response.json()) + .then(response => this.transactions = response); + }, + post: function (obj, path) { + 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.clearData(); + }, + remove: function (obj) { + if (confirm(`Delete transaction?`)) { + this.requestThenUpdate(new Request("/transaction", { + method: 'delete', + headers: { + 'Accept': 'application/json', + 'Content-Type': 'application/json' + }, + body: JSON.stringify(obj) + })) + } + }, + 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=10; + }, + updateMany: function (obj) { + 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 }) + })) + }, + }, + created() { + this.clearData(); + 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 + }) + + 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; + }) + }); + }, + computed: { + + } + }); +}
\ No newline at end of file diff --git a/src/server.js b/src/server.js new file mode 100644 index 0000000..7251a63 --- /dev/null +++ b/src/server.js @@ -0,0 +1,161 @@ +const express = require('express'); +const bodyParser = require('body-parser'); +const cookieParser = require('cookie-parser'); +//const request = require('request'); +const crypto = require('crypto'); + +const path = require('path'); +const fs = require('fs'); +const config = JSON.parse(fs.readFileSync(path.join(__dirname, 'config.json'))); + +const server = express(); +server.use(cookieParser()) +server.use(bodyParser.json()); +//server.use(bodyParser.urlencoded({ extended: true })); + +function listen(port) { + server.listen(port, () => console.info(`Listening on port ${port}!`)); +} + +function hashWithSalt(password, salt){ + var hash = crypto.createHmac('sha512', salt); + hash.update(password); + return hash.digest("base64"); +}; + +function setUpRoutes(models, jwtFunctions, database) { + // Authentication routine + server.use(function (req, res, next) { + if (!req.path.toLowerCase().startsWith("/login")) { + let cookie = req.cookies.authorization + if (!cookie) { + console.debug("Redirecting to login - no cookie") + res.redirect('/login'); + return; + } + try { + const decryptedUserId = jwtFunctions.verify(cookie); + models.users.findOne({ where: { username: decryptedUserId } }).then((user, error) => { + if (user) { + res.locals.user = user.get({ plain: true }); + } else { + console.debug("Redirecting to login - invalid cookie") + res.redirect('/login'); + return; + } + }); + } catch (e) { + res.status(400).send(e.message); + } + } + next(); + }) + + // Route logging + server.use(function (req, res, next) { + console.debug(new Date(), req.method, req.originalUrl); + next() + }) + + server.get('/', (req, res) => res.sendFile(__dirname + "/index.html")) + server.get('/login', (req, res) => res.sendFile(__dirname + "/login.html")) + server.get('/styles.css', (req, res) => res.sendFile(__dirname + "/styles.css")) + server.get('/main.js', (req, res) => res.sendFile(__dirname + "/main.js")) + + server.post('/login', async (req, res, next) => { + const user = await models.users.findOne({ where: { username: req.body.username} }) + const hash = hashWithSalt(req.body.password, user.salt) + if (user.password == hash) { + const token = jwtFunctions.sign(user.username); + res.cookie('authorization', token, { expires: new Date(Date.now() + (1000 * 60 * 60 * 24 * 30)) }); + console.debug("Redirecting to page - logged in") + res.redirect('/'); + } else { + console.debug("Redirecting to login - invalid login") + res.redirect('/login'); + } + }) + + server.get(`/transaction`, async (req, res, next) => { + try { + var result = await database.query("SELECT * FROM transactions ORDER BY `when` DESC", { type: database.QueryTypes.SELECT }) + res.status(200).send(result); + next(); + } catch (e) { + console.log(e) + res.status(400).send(e.message); + } + }) + server.post(`/transaction`, async (req, res, next) => { + try { + let item = req.body; + console.log(item); + await models.transaction.create(item); + var result = await database.query("SELECT * FROM transactions ORDER BY `when` DESC", { type: database.QueryTypes.SELECT }) + res.status(200).send(result); + } catch (e) { + console.log(e); + res.status(400).send(e.message); + } + }) + server.delete(`/transaction`, async (req, res, next) => { + try { + let id = req.body.id; + console.log(`Deleting ${id}`); + await models.transaction.destroy({ where: { id: id } }); + var result = await database.query("SELECT * FROM transactions ORDER BY `when` DESC", { type: database.QueryTypes.SELECT }) + res.status(200).send(result); + } catch (e) { + console.log(e); + res.status(400).send(e.message); + } + }) + server.put(`/transaction`, async (req, res, next) => { + try { + let id = req.body.id; + let update = req.body.update; + console.log(`Updating ${id}`); + var toUpdate = await models.transaction.findOne({ where: { id: id } }); + console.log(toUpdate) + console.log(update) + await toUpdate.update(update); + var result = await database.query("SELECT * FROM transactions ORDER BY `when` DESC", { type: database.QueryTypes.SELECT }) + res.status(200).send(result); + } catch (e) { + console.log(e); + res.status(400).send(e.message); + } + }) + server.get(`/summary`, async (req, res, next) => { + try { + res.status(200).send({ + week: { + out: await database.query("SELECT year(`when`) as y, week(`when`) as w, sum(amount) as s FROM transactions where 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 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 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 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 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 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 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 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 group by year(`when`);", { type: database.QueryTypes.SELECT }), + }, + }); + next(); + } catch (e) { + console.log(e) + res.status(400).send(e.message); + } + }) +} + +module.exports = { + listen, + setUpRoutes +}; + + diff --git a/src/styles.css b/src/styles.css new file mode 100644 index 0000000..4eedd0b --- /dev/null +++ b/src/styles.css @@ -0,0 +1,72 @@ +td { + border: 1px solid lightgrey; + min-width: 3em; +} + +table { + max-width: 100%; +} + +li { + cursor: pointer; + text-decoration: underline; +} + +tr:nth-child(2n+1) { + background-color: lightgray; +} +tr { + width: 100%; +} + +.bold { + font-weight: bold; +} + +#data { + width: 100%; +} + +.border { + border: 1px solid lightgrey; +} + +textarea { + border-radius: 4px; + width: 60%; + height: 10em; + display: block; +} + +pre { + white-space: pre-line; +} + +.net-negative { + /* color: red; */ + background-color: lightcoral; +} +.net-positive { + /* color: green; */ + background-color: lightgreen +} +.summary-panel { + float:left; + padding-right: 2em; +} +@media only screen and (max-width: 600px) { + .newItem td { + display:block; + } + .table-index { + display: none; + } + button { + font-size: 32px; + } + input { + font-size: 32px; + display: block; + width: 100%; + } +}
\ No newline at end of file |