CSC 455: Bank (SQLite3 Backend, JS/HTML Front end, Express Middleware)

###DBControl.js
var DBUtilis = require('./DBUtilis');
// this module is to orgnaize any sql queries we have
module.exports = {
    getAccounts: function(userid, res) {
		// nested queries to get all accounts for user id, with basic constructs
        let sql = 'select * from (select a_id from customer_accounts where c_id = "' + userid + '") as t_a_id inner join account_info on t_a_id.a_id = account_info.a_id group by account_info.a_id';
        DBUtilis.executeQuery(sql, res);
    },
	addUser: function(userid, dob, phone, res) {
		// insert query, modifies database
        let sql = 'INSERT INTO customer_info (c_id, d_o_b, phone)VALUES(?, ?, ?)';
		let data = [userid, dob, phone]
        DBUtilis.executeQueryWithData(sql, data, res);
    },
    addAccount: function(userid, res) {
		// insert query
        let sql = 'INSERT INTO customer_accounts (c_id)VALUES("' + userid + '")';
        DBUtilis.executeGetQuery(sql, res);
    },
    deposit: function(amount, act_id, res) {
		// this is a transaction, modifies database
        let sql = 'update account_info SET account_balance = account_balance + ? where a_id = ?';
		let data = [amount, act_id]

		let sql2 = "INSERT INTO transactions_table (a_id, transaction_amount, transaction_date, transaction_description) VALUES(?, ?,date('now'), ?)"
		let data2 = [act_id, amount, "Desposit"]

        DBUtilis.executeTransaction(sql,sql2, data, data2, res);
    },
    withdraw: function(amount, act_id, res) {
		// this is a transaction, with 3 sql statements, modifies database
        let sql = 'update account_info SET account_balance = account_balance - ? where a_id = ?';
		let data = [amount, act_id]

		let sql2 = "INSERT INTO transactions_table (a_id, transaction_amount, transaction_date, transaction_description) VALUES(?, ?,date('now'), ?)"
		let data2 = [act_id, amount*-1, "Withdraw"]

		let sql3 = 'select * from account_info where a_id = ? and account_balance - ? >= 0';
		let data3 = [act_id, amount]

        DBUtilis.executeTransactionWithCheck(sql,sql2, sql3, data, data2, data3, res);
    },
    transfer: function(amount, act_id1, act_id2, res) {
		// this is a transaction, modifies database
        let sql1 = 'update account_info SET account_balance = account_balance - ? where a_id = ?';
		let sql2 = 'update account_info SET account_balance = account_balance + ? where a_id = ?';
		let data1 = [amount, act_id1]
		let data2 = [amount, act_id2]
		let sql3 = 'select * from account_info where a_id = ? and account_balance - ? >= 0';
		let data3 = [act_id1, amount]

		DBUtilis.executeTransactionWithCheck(sql1, sql2, sql3, data1, data2, data3, res);
    },
    getTransactions: function(userid, res) {
		// nested queries to find all transactions for a user
        let sql = 'select * from (select a_id from customer_accounts where c_id = "' + userid + '") as t inner join transactions_table on t.a_id = transactions_table.a_id';
        DBUtilis.executeQuery(sql, res);
    },
    getTotalBalance: function(userid, res) {
		// nested queries to get total balance using Aggregate functions
        let sql = 'select sum(account_balance) as bal from (select a_id from customer_accounts where c_id = "' + userid + '") as t inner join account_info on t.a_id = account_info.a_id';
        DBUtilis.executeGetQuery(sql, res);
    },
    viewAll: function(res) {
		// shows the use of view
        let sql = 'select * from view_all_accounts';
        DBUtilis.executeQuery(sql, res);
    },
};


/*
this is the view
CREATE VIEW `view_all_accounts` AS
SELECT account_balance, c_id, account_info.a_id from account_info INNER JOIN customer_accounts where account_info.a_id = customer_accounts.a_id

this is 1 trigger
CREATE TRIGGER on_account_insert AFTER INSERT ON customer_accounts
BEGIN
INSERT INTO account_info (a_id, account_balance)
         VALUES(NEW.a_id, 0);
END

this is the second trigger
CREATE TRIGGER on_customer_info_insert AFTER INSERT ON customer_info
BEGIN
INSERT INTO customer_accounts (c_id)
         VALUES(NEW.c_id);
END
*/

##DBUtilis.js
const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./bankBackend.db', (err) => {
    //let db = new sqlite3.Database('./test.db', (err) => {
    if (err) {
        return console.error(err.message);
    }
    console.log('Connected to the in-memory SQlite database.');
});

module.exports = {
    executeQuery: function(sql, res) {
        db.all(sql, [], (err, rows) => {
            if (err) {
                throw err;
            }
            rows.forEach((row) => {
                //console.log(row); // right now it just prints out the query, but we should have it return and then handle it on the server
            });
			res.send(rows)
        });
    },
    executeGetQuery: function(sql, res) {
        db.get(sql, [], (err, row) => {
            if (err) {
                return console.error(err.message);
            }
            res.send(row)
        });
    },
    executeQueryWithData: function(sql, data, res) {
        db.get(sql, data, (err, row) => {
            if (err) {
				res.send(err.message);
                return console.error(err.message);
            }
			res.send(row)
        });
    },
    executeTransaction: function(sql, sql2, data1, data2, res) {
        db.serialize(function() {
            db.run("BEGIN");
            db.run(sql, data1, (err, row) => {
                if (err) {
                    console.log(err.message);
                    res.send("Transaction has been cancelled")
                } else {
                    db.run(sql2, data2, (err, row) => {
                        if (err) {
                            console.log(err);
                            db.rollback;
                            res.send("Transaction has been cancelled");
                        } else {
                            console.log('Transaction is done')
                            db.run('commit');
                            res.send("Transaction succeed");
                        }
                    });
                }

            });

        });
    },
    executeTransactionWithCheck: function(sql, sql2, sql3, data1, data2, data3, res) {
        db.serialize(function() {
            db.run("BEGIN");
			db.all(sql3, data3, (err, rows) => {
				if (err) {
					console.log(err.message)
					return res.send(err.message);
				} else {
					if (rows.length > 0) { // checks to make sure there was a row with that a_id
						db.run(sql, data1, (err, row) => {
							if (err) {
								console.log(err.message);
								return res.send("Transaction has been cancelled")
							} else {
								db.run(sql2, data2, (err, row) => {
									if (err) {
										console.log(err);
										db.rollback;
										return res.send("Transaction has been cancelled");
									} else {
										console.log('Transaction is done')
										db.run('commit');
										return res.send("Transaction succeed");
									}
								});
							}

						});
					} else {
						db.run('commit');
						return res.send("check failed"); // if the user account doesn't exist then send back that it doesn't
					}
				}
			});
        });
    }
};

###Server.js

// Modules
var express = require('express');
var path = require('path');
var app = express();
const DBControl = require('./DBControl');
const bodyParser = require('body-parser');
const Utils = require('./Utilities');

// Settings
const port = 3000
var userID = 0
// random stuff that helps the server accept methods from client
app.use(express.static('public'));
app.use(bodyParser.urlencoded({ extended: false }));
app.use(function (req, res, next) {
	res.setHeader('Access-Control-Allow-Origin', '*');
	res.setHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE');
	res.setHeader('Access-Control-Allow-Headers', 'Content-Type');
	res.setHeader('Access-Control-Allow-Credentials', true);
	next();
});

//app.get listens to get requests, which is like when you go to google.com you do a get request for their page
//so this listens to get requests to localhost:8000 and will execute this function if someone goes there
// this is what executes when you go to localhost:3000, just defaults to the html file

// screen managers
app.get('/', function(req, res){
    var options = {
        root: path.join(__dirname)
    };
    var fileName = 'index.html';  // this is the default page
    res.sendFile(fileName, options, function (err) {
        if (err) {
            next(err);
        } else {
            console.log('File: ', fileName, ' has been sent');
        }
    });
});



// get/post requests

app.post('/deposit', function (req, res) {
    const body = req.body; // this is the parameters sent from client
	console.log("Deposit request... Amount: "+body.amount+", Account Number: "+body.act_num)
	if (!Utils.isNumber(req.body.amount)) {
		res.send("Please enter a number"); // this is what we send back to the client once we're done
		return;
	}
	DBControl.deposit(body.amount, body.act_num, res)
	const amount = +body.amount
});

app.post('/withdraw', function (req, res) {
    const body = req.body; // this is the parameters sent from client
	console.log("Withdraw request... Amount: "+body.amount+", Account Number: "+body.act_num)
	if (!Utils.isNumber(req.body.amount)) {
		res.send("Please enter a number"); // this is what we send back to the client once we're done
		return;
	}
	DBControl.withdraw(body.amount, body.act_num, res)
	const amount = +body.amount
});

app.post('/transfer', function (req, res) {
	console.log("Transfer request")
    const body = req.body; // this is the parameters sent from client
	const userid = Utils.getUserID(body.userid)
	const act1 = body.act1
	const act2 = body.act2
	const amount = body.amount
	DBControl.transfer(amount, act1, act2, res)
});

app.post('/addaccount', async function (req, res) {
	console.log("Request to add account")
    const body = req.body;
	const userid = Utils.getUserID(body.userid)
	DBControl.addAccount(userid, res)
});

app.post('/adduser', async function (req, res) {
	console.log("Request to add user")
    const body = req.body;
	const userid = Utils.getUserID(body.userid)
	const dob = body.dob
	const phone = body.phone
	DBControl.addUser(userid, dob, phone, res)
	//res.send('done')
});

app.post('/getAccounts', async function (req, res) {

    const body = req.body; // this is the parameters sent from client
	const userid = Utils.getUserID(body.userid)
	console.log("Request to get accounts of user ID: "+userid)
	DBControl.getAccounts(userid, res)
});

app.post('/getTransactions', async function (req, res) {

    const body = req.body; // this is the parameters sent from client
	const userid = Utils.getUserID(body.userid)
	console.log("Request to get transactions of user ID: "+userid)
	DBControl.getTransactions(userid, res)
});

app.post('/totalBalance', async function (req, res) {

    const body = req.body; // this is the parameters sent from client
	const userid = Utils.getUserID(body.userid)
	console.log("Request to get total balance of: "+userid)
	DBControl.getTotalBalance(userid, res)
});

app.post('/viewAll', async function (req, res) {
	console.log("Request to view all")
    const body = req.body; // this is the parameters sent from client
	DBControl.viewAll(res)
});

var server = app.listen(port, function () { // starts the server on the localhost/port
   var host = server.address().address
   var port = server.address().port

   console.log("Citzen United Bank listening at http://%s:%s", host, port)
})


##Utitlies.js
module.exports = {
  isNumber: function (str) {
  if (typeof str != "string") return false // we only process strings!
  return !isNaN(str) && // use type coercion to parse the _entirety_ of the string (`parseFloat` alone does not do this)...
         !isNaN(parseFloat(str)) // ...and ensure strings of whitespace fail
	},

  getUserID: function (str) {
  if (typeof str != "string") return false // we only process strings!
  return str.trim().split("User")[1]
  }
};


##Index.html

<!DOCTYPE html>
<html lang="en">
  <head><!DOCTYPE html>

<head>
    <title>Project</title>
    <style>
        body {
            background-color: rgb(236, 152, 42);

        }

        .center {
            color: white;
            font-family: "Trebuchet MS", Helvetica, sans-serif;
            text-align: center;
        }
		.card {
			border: 2px white solid;
			padding-bottom: 4px;
			padding-top: 4px;
			justify-content: "space-between";
			align-items: 'center';
		}
		.transfer_card {
			border: 2px white solid;
			padding-bottom: 4px;
			margin-bottom: 30px;
			padding-top: 4px;
			justify-content: "space-between";
			align-items: 'center';
		}
        h1 {
            font-size: 144px;
        }

        p {
            font-size: 64px;
        }
		h2 {

		  width: 500px;
		  padding: 25px;
		}
		.dirrow {
		  display: flex;
		  flex-direction: row;
		  align-items: center;
		  justify-content: center;
		  background-color: rgb(255, 128, 0);
		}
    </style>
	<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
	<script>
		function openForm() { // removes everything but registeration buttons
		  document.getElementById("registerForm").style.display = "block";
		  document.getElementById("loginstuff").style.display = "none";
		  document.getElementById("transactionstuff").style.display = "none";
		  document.getElementById("registerstuff").style.display = "none";
		}

		function closeForm() { // removes registration buttons and adds everything back
		  document.getElementById("registerForm").style.display = "none";
		  document.getElementById("loginstuff").style.display = "block";
		  document.getElementById("transactionstuff").style.display = "block";
		  document.getElementById("registerstuff").style.display = "block";
		}
		function viewShow() { //
		  document.getElementById("viewTable").style.display = "block";
		  document.getElementById("dirrow").style.display = "none";
		  $.post("http://localhost:3000/viewAll",{userid: null}, function(data){ // post server for all the accounts of the user
			let table = document.getElementById("viewTable");
			table.innerHTML = '';
			for (var i = 0; i < data.length; i++) {
				let card = document.createElement("div")
				let user = document.createElement('p1')
				let accountName = document.createElement('p1')
				let balance = document.createElement('p1')
				accountName.innerHTML = "Account Number: "+data[i].a_id
				balance.innerHTML = "  Balance: "+data[i].account_balance
				user.innerHTML = "  User ID: "+data[i].c_id
				card.classList.add('card')
				card.appendChild(accountName)
				card.appendChild(balance)
				card.appendChild(user)
				table.appendChild(card)
			}
		  });
		}

		function viewClose() { //
		  document.getElementById("viewTable").style.display = "none";
		  document.getElementById("dirrow").style.display = "flex";
		}
		function updateAccounts() { // it gets the accounts and then populates a table in the html with components
			userid=document.getElementById("bankUser").innerHTML;
			$.post("http://localhost:3000/getAccounts",{userid: userid}, function(data){ // post server for all the accounts of the user
            //document.getElementById("bankBalance").innerHTML = data;
			let table = document.getElementById("table");
			table.innerHTML = '';
			for (var i = 0; i < data.length; i++) {

				let card = document.createElement("div")
				let accountName = document.createElement('p1')
				let balance = document.createElement('p1')

				accountName.innerHTML = "Account Number: "+data[i].a_id
				balance.innerHTML = "  Balance: "+data[i].account_balance
				card.classList.add('card')
				card.appendChild(accountName)
				card.appendChild(balance)
				table.appendChild(card)
				let input = document.createElement('input')
				input.type = "Text";
				input.size = 40;
				input.value = "0";
				input.id = data[i].a_id
				let input1 = document.createElement('input')
				input1.type = "Button";
				input1.size = 40;
				input1.value = "Withdraw";
				input1.id = data[i].a_id
				let input2 = document.createElement('input')
				input2.type = "Button";
				input2.size = 40;
				input2.value = "Deposit";
				input2.id = data[i].a_id
				card.appendChild(input)
				card.appendChild(input1)
				card.appendChild(input2)
				input2.addEventListener("click", function() { // add event listeners to each withdraw/ deposit button so they can communicate with server
					// this is when they click deposit
					let act_num = input2.id
					let amount = input2.parentNode.childNodes[2].value
					$.post("http://localhost:3000/deposit",{amount: amount, act_num: act_num}, function(data){ // this is when we send the POST request to the server
						updatePage()
					});
				});
				input1.addEventListener("click", function() {
					// this is when they click withdraw
					let act_num = input1.id
					let amount = input1.parentNode.childNodes[2].value
					$.post("http://localhost:3000/withdraw",{amount: amount, act_num: act_num}, function(data){ // this is when we send the POST request to the server
						if (data.includes('check failed')) {
							document.getElementById("mainerror").innerHTML = "Account: "+act_num+ " does not have enough money to withdraw";
						} else {
							updatePage()
						}
					});
				});
			}
          });
		}
		function updateTransactions() { // gets all the transactions of a user and populates html with it
			userid=document.getElementById("bankUser").innerHTML;
			$.post("http://localhost:3000/getTransactions",{userid: userid}, function(data){ // post request for transactions
			let table = document.getElementById("table1");
			table.innerHTML = '';
			for (var i = 0; i < data.length; i++) {

				let card = document.createElement("div")
				let accountName = document.createElement('p1')
				let Date = document.createElement('p1')
				let Amount = document.createElement('p1')
				let Desc = document.createElement('p1')

				accountName.innerHTML = "Account Number: "+data[i].a_id
				Date.innerHTML = " Date: "+data[i].transaction_date
				Amount.innerHTML = " Amount: "+data[i].transaction_amount
				Desc.innerHTML = " Description: "+data[i].transaction_description
				card.classList.add('card')
				card.appendChild(accountName)
				card.appendChild(Date)
				card.appendChild(Amount)
				card.appendChild(Desc)
				table.appendChild(card)
			}
          });
		 }
		function updateBalance() {
			userid=document.getElementById("bankUser").innerHTML; // we take the input from the text button
			$.post("http://localhost:3000/totalBalance",{userid: userid}, function(data){ // this is when we send the POST request to the server
				document.getElementById("totalBalance").innerHTML = "Total Balance: "+data.bal;
			});
		}
		function updatePage() {
			document.getElementById("mainerror").innerHTML = ""
			updateAccounts()
			updateTransactions()
			updateBalance()
		}
	</script>
    <script>
      $(document).ready(function(){ // handles creating new accounts for a user
	  var userid;
        $("#newAccount").click(function(){
			userid=document.getElementById("bankUser").innerHTML;
			$.post("http://localhost:3000/addaccount",{userid: userid}, function(data){ // this is when we send the POST request to the server
				updatePage()
			});
		});
	   });
    </script>
    <script>
      $(document).ready(function(){ // this opens register form
        $("#openRegisterForm").click(function(){
			openForm()
        });
      });
    </script>
    <script>
      $(document).ready(function(){
		var userid;
        $("#registerCustomer").click(function(){ // this handles registering users
			userid=$("#registerUsername").val();
			dob=$("#registerDate").val();
			phone=$("#registerPhone").val();
			$.post("http://localhost:3000/adduser",{userid: userid, dob: dob, phone: phone}, function(data){ // this is when we send the POST request to the server
				if (data.includes('constraint')) { // if user already exists
					return document.getElementById("error").innerHTML = "User already exists";
				}
				if (data.includes('datatype')) {
					return document.getElementById("error").innerHTML = "Name must be User followed by numbers, example : User12";
				}
				document.getElementById("bankUser").innerHTML = userid;
				updatePage()
				closeForm()

			});
        });
      });
    </script>
    <script>
      $(document).ready(function(){ // this handles transfer events
		var userid;
        $("#transferSubmit").click(function(){
			userid=document.getElementById("bankUser").innerHTML;
			act1=$("#transferACT1").val();
			act2=$("#transferACT2").val();
			amount=$("#transferAmount").val();
			$.post("http://localhost:3000/transfer",{userid: userid, act1: act1, act2: act2, amount: amount}, function(data){ // this is when we send the POST request to the server
				if (data.includes('check failed')) {
					document.getElementById("mainerror").innerHTML = "Account: "+act1+ " does not exist or does not have enough to do the transfer";
				} else {
					document.getElementById("mainerror").innerHTML = ""
					updatePage()
				}
			});
        });
      });
    </script>
    <script>
      $(document).ready(function(){
        var userid;
        $("#loginSubmit").click(function(){ // changes the userid of the html
		  userid=$("#loginText").val();
		  document.getElementById("bankUser").innerHTML = userid;
		  updatePage()
        });
      });
    </script>
    <script>
      $(document).ready(function(){
        var userid;
        $("#viewShow").click(function(){ // manages the view button
		  viewShow()
        });
      });
    </script>
    <script>
      $(document).ready(function(){
        var userid;
        $("#viewClose").click(function(){ // manages the view button
		  viewClose()
		  closeForm()
        });
      });
    </script>
    <script>
      $(document).ready(function(){ // closes the registeration page and updates the html on first load
		document.getElementById("registerForm").style.display = "none";
		updatePage()
      });
    </script>
</head>

<body>
    <div class="center">
	<div class="form-popup" id="myForm">
	</div>
        <h1>Citizens United Bank!</h1>
        <p>Welcome!</p>
		<p id=bankUser>User1</p>
		<p id='mainerror'></p>
		<div id='dirrow' class="dirrow">
			<h2 id='registerstuff'>
				<div class ='transfer_card'>
					<input type="TEXT" id="transferACT1" size = '10' value="AccountID1">
					<input type="TEXT" id="transferACT2" size = '10'value="AccountID2">
					<input type="TEXT" id="transferAmount" size = '10'value="Amount">
					<input type="Button" id="transferSubmit" value="Transfer Money">
				</div>

				<input type="button" id="newAccount" value="Create New Account">

				<ul id ="table">


				</ul>
			</h2>
			<h2 id='loginstuff'>
				<p id='totalBalance'>Total Balance: </p>

				<p2> Login to different accout: </p2>
				<input type="TEXT" id="loginText" size="40" value="User1"><br>
				<input type="button" id="loginSubmit" value="Login">
				<input type="button" id="openRegisterForm" value="Register New User">
			</h2>
			<h2 id='transactionstuff'>
				<p> Transactions </p>
				<ul id ="table1">


				</ul>
			</h2>
			<h2 class= 'register' id='registerForm'>
				<ul>
					<p id='error'></p>
					<input type="TEXT" id="registerUsername" size="40" value="User1"><br>
					<input type="date" id="registerDate" size="40" value="2018-07-22"><br>
					<input type="tel" id="registerPhone" size="40" value="999-9999-9999"><br>
					<input type="button" id="registerCustomer" value="Register">
				</ul>
			</h2>
		</div>
		<ul id ="viewTable">


		</ul>
		<input type="button" id="viewClose" value="go back">
		<input type="button" id="viewShow" value="show all accounts">
    </div>
</body>

</html>