-
Notifications
You must be signed in to change notification settings - Fork 0
/
bamazonSupervisor.js
102 lines (90 loc) · 3.04 KB
/
bamazonSupervisor.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
var mysql = require("mysql");
var inquirer = require("inquirer");
var connection = mysql.createConnection({
host: "localhost",
// Your port; if not 3306
port: 3306,
// Your username
user: "root",
// Your password
password: "rootpass",
// Your db
database: "bamazon_db"
});
// connect to the mysql server
connection.connect(function (err) {
if (err) throw err;
//console.log("connected as id " + connection.threadId + "\n");
// Run function after the connection that starts the prompt for the user
viewDepartments();
});
function viewDepartments() {
inquirer
.prompt({
name: "action",
type: "list",
message: "SELECT ACTION",
choices: [
"View Product Sales by Department",
"Create New Department",
"Exit"
]
})
.then(function (answer) {
switch (answer.action) {
case "View Product Sales by Department":
viewDeptSales();
break;
case "Create New Department":
createDept();
break;
case "Exit":
connection.end();
break;
}
});
}
function createDept() {
inquirer.prompt([
{
name: "department_id",
type: "input",
message: "Please enter new Department ID"
},
{
name: "department_name",
type: "input",
message: "Please enter the Department Name"
},
{
name: "over_head_costs",
type: "input",
message: "Please enter the overhead costs for this department"
}
]).then(function (answer) {
// variables for user inputs
var dept_id = answer.department_id;
var dept = answer.department_name;
var overhead = answer.over_head_costs;
// Query db for all products
connection.query("INSERT INTO departments SET ?",
{
department_id: dept_id,
department_name: dept,
over_head_costs: overhead
},
function (err, results) {
if (err) throw err;
console.log("DEPARTMENT ADDED");
console.table(results);
viewDepartments();
});
});
}
function viewDeptSales() {
connection.query("SELECT departments.department_id AS 'Department ID', departments.department_name AS 'Department Name', departments.over_head_costs AS 'Overhead Costs', SUM(products.product_sales) AS 'Product Sales', (SUM(products.product_sales) - departments.over_head_costs) AS 'Total Profit' FROM departments LEFT JOIN products ON products.department_name = departments.department_name GROUP BY departments.department_name, departments.department_id, departments.over_head_costs ORDER BY departments.department_id ASC", function (err, results) {
if (err) throw err;
console.table(results);
viewDepartments();
});
}