-
Notifications
You must be signed in to change notification settings - Fork 0
/
index.js
267 lines (248 loc) · 8.49 KB
/
index.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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
//Attack Plan
//Run main function
// Where it is a recursive loop
// where two things will happen
// either you select a function to go into
// or quit
// if you select a function
// a switch case
// will decide what subfunction to call
// if press quit
//the quit the function
//import modules
const inquirer = require('inquirer');
const mysql = require('mysql2/promise');
const bluebird = require('bluebird');
//outside array initialization for easier configuration and view
const choices = [
"View all departments",
"View all roles",
"View all Employees",
"Add a department",
"Add a role",
"Add an employee",
"Update an employee role",
"Quit"
];
const mainMenu =[
{
type: "list",
name: "action",
message: "What would you like to do",
choices: choices,
loop: true
}
];
async function showDepartments(db){
const departments = await db.execute("Select id as department_id, name as department_name from department");
// how the hell to access the data in promise
console.table(departments[0]);
}
async function showRoles(db){
const departments = await db.execute("select title as job_title, role.id as role_id, department.name as department, salary from role inner join department on role.department_id = department.id");
console.table(departments[0]);
}
async function showEmployees(db){
const employee = await db.execute(`select employee_info.first_name,
employee_info.last_name,
employee_info.job_title,
employee_info.department,
employee_info.salary,
manager.first_name as manager_first_name,
manager.last_name as manager_last_name
FROM
(select employee.id as employee_id,
employee.first_name,
employee.last_name,
role.title as job_title,
department.name as department,
role.salary,
employee.manager_id
FROM employee
left join ( role, department)
ON (role.id = employee.role_id AND role.department_id = department.id))as employee_info
Left JOIN employee AS manager
ON employee_info.manager_id = manager.id`)
console.table(employee[0]);
}
async function addDepartment(db){
const {department} = await inquirer.prompt({
name:"department",
message:"Please add a new department.",
});
await db.execute(`INSERT INTO department(name) VALUES("${department}")`);
}
async function addRole(db){
const data = await db.execute("SELECT * from department");
const departments = data[0].map((element)=>element.name);
const {name,salary, department } = await inquirer.prompt([{
name:"name",
message:"Please add a new role"
},
{
name:"salary",
type: "input",
message: "please enter the salary this role has",
validate(answer){
if(isNaN(answer))
return "This is not a valid salary";
return true;
}
},
{
name: "department",
type: "list",
choices: departments,
message: "Please choose the department this role belongs to.",
filter(answer){
for(let i = 0; i < data[0].length;i++)
{
if(data[0][i].name == answer)
return data[0][i].id;
}
}
}]);
db.execute(`INSERT INTO ROLE(department_id, title, salary) VALUES("${department}","${name}","${salary}")`);
}
async function addEmployee(db){
// pulling in available roles
const roleData = (await db.execute("SELECT id,title from role"))[0];
const roles = roleData.map((element)=>element.title);
// Pulling all available employees to be managers
const employeeData = (await db.execute("SELECT id,CONCAT(first_name,' ',last_name) as manager from employee"))[0];
const managers = employeeData.map((element)=> element.manager);
let {first_name, last_name, role, hasManager, manager} = await inquirer.prompt(
[
{name:"first_name",
message: "Please enter the new Employee's First Name",
validate(answer){
if(answer)
return true;
return "Please enter something."
}
},
{name:"last_name",
message: "Please enter the new Employee's Last Name",
validate(answer){
if(answer)
return true;
return "Please enter something."
}
},
{name: "role",
type: "list",
message: "Please choose the new Employee's role.",
choices: roles,
filter(answer){
for(let i = 0; i < roleData.length;i++)
{
if(roleData[i].title == answer)
return roleData[i].id
}
}
},
{name: "hasManager",
type: "confirm",
message: "Does this employee have a manager y/N?"
},
{name: "manager",
message: "Please select the new Employee's manager",
type: "list",
choices: managers,
filter(answer){
for(let i = 0; i < employeeData.length;i++)
{
if(employeeData[i].manager == answer)
return employeeData[i].id;
}
},
when: (answers)=> answers.hasManager
}
])
if(!hasManager)
db.execute(`INSERT INTO EMPLOYEE(role_id, first_name, last_name) VALUES("${role}", "${first_name}", "${last_name}")`);
else{
db.execute(`INSERT INTO EMPLOYEE(role_id, manager_id, first_name, last_name) VALUES("${role}","${manager}", "${first_name}", "${last_name}")`);
}
}
async function updateEmployee(db){
//Take in the current employees
const employeeData = (await db.execute("SELECT id,CONCAT(first_name,' ',last_name) as person from employee"))[0];
const employee = employeeData.map((element)=> element.person);
//Take in Current Roles
// pulling in available roles
const roleData = (await db.execute("SELECT id,title from role"))[0];
const roles = roleData.map((element)=>element.title);
// Update one row
let {id,role_id} = await inquirer.prompt([{
name:"id",
type:"list",
choices: employee,
message: "Choose a current employee.",
filter(answer){
for(let i = 0; i < employeeData.length; i++){
if(employeeData[i].person == answer)
return employeeData[i].id;
}
}
},
{
name:"role_id",
type:"list",
choices: roles,
message: "Choose this employee's new role",
filter(answer){
for(let i = 0; i < roleData.length; i++){
if(roleData[i].title == answer)
return roleData[i].id;
}
}
}
])
db.query(`UPDATE employee SET role_id=${role_id} WHERE id=${id}`);
}
async function main(){
try {
const {action} = await inquirer.prompt(mainMenu);
if(action == "Quit")
process.exit(0);
const db = await mysql.createConnection(
{
host:'localhost',
user:'root',
password: "password",
database:'cheese_man_db',
Promise:bluebird
}
);
switch(action){
case "View all departments":
await showDepartments(db);
break;
case "View all roles":
await showRoles(db);
break;
case "View all Employees":
await showEmployees(db);
break;
case "Add a department":
await addDepartment(db);
break;
case "Add a role":
await addRole(db);
break;
case "Add an employee":
await addEmployee(db);
break;
case "Update an employee role":
await updateEmployee(db);
break;
}
db.end();
main();
} catch(err) {
console.log("Ugh!")
console.log(err);
}
};
main();