-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathbamazonSupervisor.js
142 lines (138 loc) · 4.74 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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
// - createConnection
const inquirer = require("inquirer");
const mysql = require("mysql");
// connects to mysql
var connection = mysql.createConnection({
host: "localhost",
user: "root",
password: "Majesty88",
port: 3306,
database: "bamazon"
});
// if connection fails, error prompt
connection.connect(function (err) {
if (err) {
console.error("error connecting: " + err.stack);
return;
}
console.log("connected as id " + connection.threadId);
});
// brings back data from mysql- loads database into application.
function inventory() {
connection.query("SELECT * FROM products ", function (err, response) {
if (err) throw err;
console.table(response);
menuOptions();
});
}
inventory();
var menuOptions = function () {
var inquirer = require("inquirer");
console.log("Greetings Supervisor!");
inquirer
.prompt({
/* Pass your questions in here */
name: "action",
message: "What would you like to do?",
type: "list",
choices: ["View product sales by department", "Create new department"]
})
.then(answers => {
console.log(answers);
switch (answers.action) {
case "View product sales by department":
salesByDepartment();
break;
case "Create new department":
CreateNewDep();
break;
}
});
};
// - Function to display "Product Sales by Department" via JOIN
function salesByDepartment() {
var inquirer = require("inquirer");
inquirer
.prompt([{
/* Pass your questions in here */
name: "salesByDepartment",
message: "What is the dpartment id that you'd like to view?",
type: "input"
}])
.then(answers => {
// Use user feedback for... whatever!!
var department = answers.salesByDepartment;
console.log(department);
// Finds item in inventory
var viewSales = function () {
connection.query(
" SELECT " +
" d.department_id, " +
" d.department_name, " +
" d.over_head_costs, " +
" SUM(IFNULL(p.product_sales, 0)) as product_sales, " +
" SUM(IFNULL(p.product_sales, 0)) - d.over_head_costs as total_profit " +
"FROM products p " +
" RIGHT JOIN departments d ON p.department_id = d.department_id " +
"WHERE p.department_id=?" +
"GROUP BY " +
" d.department_id, " +
" d.department_name, " +
" d.over_head_costs",
[department],
function (err, res) {
console.table(res);
menuOptions();
}
);
};
viewSales();
});
}
function CreateNewDep() {
var inquirer = require("inquirer");
inquirer
.prompt([{
/* Pass your questions in here */
name: "createNewDep",
message: "What is the name of the dpartment you/'d like to create?",
type: "input"
},
{
name: "cost",
message: "What is the over-head cost?",
type: "input"
}, {
name: "sales",
message: "What is the total product sales?",
type: "input"
}, {
name: "profit",
message: "What is the total profit?",
type: "input"
}
])
.then(answers => {
var newDepartment = answers.createNewDep;
var cost = answers.cost;
var sales = answers.sales;
var profit = answers.profit;
// console.log(newDepartment);
console.log(answers)
// - Function to send an INSERT query to "Create New Department"
var createNew = function () {
connection.query(
"INSERT INTO departments (department_name, over_head_costs, product_sales, total_profit) VALUES(?,?,?,?)",
[newDepartment, cost, sales, profit],
function (err, res) {
if (err) throw err;
console.log(
"Congratulations Supervisor! you have added a NEW DEPARTMENT!"
);
menuOptions();
}
);
};
createNew();
});
}