From 60f66450078b0a3a46d4ab373645c2546aab28f4 Mon Sep 17 00:00:00 2001 From: Mark Powers Date: Thu, 26 Dec 2019 20:28:52 -0600 Subject: initial commit --- src/create-user.js | 101 +++++++++++++++++++++++++++++ src/index.html | 154 +++++++++++++++++++++++++++++++++++++++++++++ src/index.js | 93 +++++++++++++++++++++++++++ src/login.html | 40 ++++++++++++ src/main.js | 182 +++++++++++++++++++++++++++++++++++++++++++++++++++++ src/server.js | 161 +++++++++++++++++++++++++++++++++++++++++++++++ src/styles.css | 72 +++++++++++++++++++++ 7 files changed, 803 insertions(+) create mode 100644 src/create-user.js create mode 100644 src/index.html create mode 100644 src/index.js create mode 100644 src/login.html create mode 100644 src/main.js create mode 100644 src/server.js create mode 100644 src/styles.css (limited to 'src') 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 @@ + + + + + Mark's Budget + + + + + + + + + + +

Mark's Budget

+ + +

+ + + + + + +

+ + + + + + + + + + + + + + + + + + + + +

	Date	Where	Amount	Category	Subcategory
{{i+1}}	{{transaction.when.substring(0,10)}}	{{transaction.where}}	{{transaction.amount}}	{{transaction.category}}	{{transaction.subcategory}}	+ + +

+ + + + + + + + + + + + + + + + + + + + +

	Date	Where	Amount	Category	Subcategory
	+ +	+ +	+ +	+ +	+ +

+ + +

Weekly

+ + + + + + + + + + + + + + + +

Year	Week	In	Out	Net
{{data.y}}	{{data.w}}	{{data.in}}	{{data.out}}	{{data.net}} +

Monthly

+ + + + + + + + + + + + + + + +

Year	Month	In	Out	Net
{{data.y}}	{{data.m}}	{{data.in}}	{{data.out}}	{{data.net}} +

Yearly

+ + + + + + + + + + + + + +

Year	In	Out	Net
{{data.y}}	{{data.in}}	{{data.out}}	{{data.net}} +

+ + + \ 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 @@ + + + + + Mark's Database - Login + + + + + + + +

Login

+ + + +

+ + + \ 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 -- cgit v1.2.3