order.js

/* eslint-disable max-lines */
/* eslint-disable max-len */

'use strict'

// const fs = require('fs-extra')
const sqlite = require('sqlite-async')

module.exports = class Order {

	constructor(dbMenu = ':memory:') {
		return (async() => {
			this.db = await sqlite.open(dbMenu)
			const sql = `CREATE TABLE IF NOT EXISTS items (
				ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT NOT NULL, price NUMERIC NOT NULL, type TEXT NOT NULL);`
			const sql2 = `CREATE TABLE IF NOT EXISTS Orders (
				ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, tableNumber INTEGER NOT NULL, ready INTEGER NOT NULL CHECK(0 OR 1), Date TEXT NOT NULL);`
			const sql3 = `CREATE TABLE IF NOT EXISTS "Jobs" (
				ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, ItemID INTEGER NOT NULL, OrderID INTEGER NOT NULL, quantity INTEGER, FOREIGN KEY(OrderID) REFERENCES Orders(ID))`
			const sql4 = `CREATE TABLE IF NOT EXISTS Options ( 
				ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, type TEXT NOT NULL, name TEXT NOT NULL)`
			await this.db.run(sql)
			await this.db.run(sql2)
			await this.db.run(sql3)
			await this.db.run(sql4)
			return this
		})()
	}

	async addItem(data) {
		const sql = `INSERT INTO items(name, price, type) VALUES("${data.itemName}", ${data.itemPrice}, "${data.itemType}")`
		await this.db.run(sql)
		return true
	}

	async editItem(data) {
		const sql = `UPDATE items SET name = "${data.itemName}", price = ${data.itemPrice}, type = "${data.itemType}" WHERE ID = "${data.ID}"`
		await this.db.run(sql)
		return true
	}

	async getItemsID() {
		const sql = 'SELECT ID FROM items;'
		const data = await this.db.all(sql)
		return data
	}

	async getItemsName() {
		const sql = 'SELECT ID, name, type, price FROM items;'
		const data = await this.db.all(sql)
		return data
	}

	async getItemInfo(data) {
		const sql = `SELECT ID, name, type, price FROM items WHERE ID = "${data}"`
		const output = await this.db.all(sql)
		return output
	}

	async getAmount(tableNumber, itemID) {
		let sql
		const data = []
		for (let i = 0; i < itemID.length; i++) {
			sql = `SELECT Jobs.quantity FROM Jobs 
			INNER JOIN Orders ON Jobs.OrderID = Orders.ID 
			INNER JOIN items ON Jobs.ItemID = items.ID 
			WHERE Orders.tableNumber = "${tableNumber}" AND items.ID = "${itemID[i].ID}";`
			data.push(await this.db.get(sql))
		}
		return data
	}

	async getOrderDetails(data) {
		const sql = `SELECT ID FROM Orders WHERE tableNumber = "${data}"`
		return await this.db.get(sql)
	}

	async getNotReadyOrders() {
		const sql = 'SELECT ID, ready, Date FROM Orders WHERE ready = 0'
		const data = await this.db.all(sql)
		return data
	}

	async getReadyOrders() {
		const sql = 'SELECT ID, ready, Date FROM Orders WHERE ready = 1'
		const data = await this.db.all(sql)
		return data
	}

	async addToOrder(data) {
		const date = new Date()
		const t = `${date.getHours()}:${date.getMinutes()}:${date.getSeconds()}`
		const sql = `INSERT INTO Orders(tableNumber, ready, Date) SELECT "${data.tableNumber}", 0, "${t}"
		WHERE NOT EXISTS(SELECT 1 FROM Orders WHERE tableNumber = "${data.tableNumber}")`
		await this.db.run(sql)
		const sql2 = `SELECT ID FROM Orders WHERE tableNumber = "${data.tableNumber}"`
		const output = await this.db.get(sql2)
		const sql3 = `UPDATE Jobs SET quantity = quantity + 1 WHERE ItemID = "${data.itemID}" AND OrderID = "${output.ID}"`
		await this.db.run(sql3)
		const sql4 = `INSERT INTO Jobs(ItemID, OrderID, quantity) SELECT "${data.itemID}", "${output.ID}", 1 WHERE NOT EXISTS(SELECT 1 FROM Jobs WHERE ItemID = "${data.itemID}" AND OrderID = "${output.ID}")`
		await this.db.run(sql4)
	}

	async removeFromOrder(data) {
		const sql = `SELECT ID FROM Orders WHERE tableNumber = "${data.tableNumber}"`
		const output = await this.db.get(sql)
		const sql2 = `SELECT quantity from Jobs WHERE OrderID = "${output.ID}" AND ItemID = "${data.itemID}"`
		const amount = await this.db.get(sql2)
		let pass = undefined
		if (amount.quantity <= 1) {
			const sql3 = `DELETE FROM Jobs WHERE OrderID = "${output.ID}" AND ItemID = "${data.itemID}"`
			await this.db.run(sql3)
			pass = true
		} else {
			const sql3 = `UPDATE Jobs SET quantity = quantity -1 WHERE ItemID = "${data.itemID}" AND OrderID = "${output.ID}"`
			await this.db.run(sql3)
			pass = false
		}
		const sql4 = `DELETE FROM Orders WHERE tableNumber = "${data.tableNumber}" AND NOT EXISTS(SELECT 1 FROM Jobs WHERE OrderID = "${output.ID}")`
		await this.db.run(sql4)
		return pass
	}

	createItemObject(itemNames, itemAmount, tableNumber) {
		const data = []
		let obj = {}
		for(let i = 0; i < itemNames.length; i++) {
			if (itemAmount[i] === undefined) {
				obj = {ID: itemNames[i].ID, name: itemNames[i].name, quantity: 0, tableNumber: tableNumber, type: itemNames[i].type}
			} else {
				obj = {ID: itemNames[i].ID, name: itemNames[i].name, quantity: itemAmount[i].quantity, tableNumber: tableNumber, type: itemNames[i].type}
			}
			data.push(obj)
		}
		return data
	}

	createTableNumbers() {
		let obj = {}
		const data = []
		const tableAmount = 50
		for(let i = 1; i <= tableAmount; i++) {
			obj = {table: i}
			data.push(obj)
		}
		return data
	}

	async setReady(data) {
		const sql = `UPDATE Orders SET ready = 1 WHERE ID = "${data}"`
		await this.db.run(sql)
	}

	async setUnready(data) {
		const sql = `UPDATE Orders SET ready = 0 WHERE ID = "${data}"`
		await this.db.run(sql)
	}

	createPrice(data) {
		const price = `${data.itemPricePounds[0]}.${data.itemPricePence}`
		data['itemPrice'] = price
		delete data.itemPricePounds
		delete data.itemPricePence
		return data
	}

	splitPrice(data) {
		const price = `${data.price}`
		const newPrice = price.split('.')
		if(newPrice.length > 1) {
			data['itemPricePounds'] = newPrice[0]
			data['itemPricePence'] = newPrice[1]
			delete data.price
		} else {
			data['itemPricePounds'] = newPrice[0]
			data['itemPricePence'] = '00'
			delete data.price
		}
		return data
	}
}