Mission3 .localhost:5000/api/items
localhost:5000/api/items
POST requestraw JSON
{
"name": "test",
"status": true
}
.gitignore file into this folder.myNotes.txt file in this folder.npx create-react-app clientOR
npx create-react-app . --use-npm
cd client
npm i react-router-dom
"proxy": "http://localhost:5000"
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">
npm i bootstrap @import "bootstrap/dist/css/bootstrap.min.css";
cd my-app npm i @fortawesome/fontawesome-free In App.css @import "@fortawesome/fontawesome-free/css/all.min.css";
Create server.js file.
npm init -y
npm i express mysql
npm i -D nodemon concurrently
Update package.json file
"scripts": {
"start": "node server.js",
"server": "nodemon server.js --ignore ./client",
"client": "npm start --prefix client",
"dev": "concurrently \"npm run server\" \"npm run client\""
},
CREATE DATABASE mission3;
CREATE USER 'johnBryce_user'@'localhost' IDENTIFIED BY 'myPassword';
SELECT * FROM mysql.user;
GRANT ALL PRIVILEGES ON mission3.* TO 'johnBryce_user'@'localhost';
FLUSH PRIVILEGES;
CREATE TABLE IF NOT EXISTS mission3.companies (
id INT AUTO_INCREMENT PRIMARY KEY,
companyName CHAR(100) NOT NULL
);
CREATE TABLE mission3.servers (
id INT AUTO_INCREMENT PRIMARY KEY,
serverName CHAR(100) NOT NULL,
ip CHAR(255) NOT NULL,
hostCompId INT NOT NULL,
status BOOLEAN DEFAULT FALSE,
createdAt TIMESTAMP DEFAULT NOW(),
FOREIGN KEY (hostCompId) REFERENCES mission3.companies(id)
);
CREATE TABLE IF NOT EXISTS mission3.products (
id INT AUTO_INCREMENT PRIMARY KEY,
name CHAR(100) NOT NULL,
price INT NOT NULL,
vendorId INT NOT NULL,
units INT NOT NULL DEFAULT 0,
updatedAt TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (vendorId) REFERENCES mission3.vendors(id)
);
INSERT INTO mission3.companies
(companyName)
VALUES
('IBM'),
('Microsoft'),
('Google'),
('DigitalO');
INSERT INTO mission3.servers
(serverName, ip, hostCompId)
VALUES
('Server A', '1.1.1.1', 1),
('Server B', '2.2.2.2', 2),
('Server C', '3.3.3.3', 3),
('Server D', '4.4.4.4', 4);
INSERT INTO mission3.servers
(serverName, ip, hostCompId, status, createdAt)
VALUES
('Server F', '10.116.093.192', 3, 1, "2010-01-31 19:00:00"),
('Server G', '10.010.093.102', 4, 0, "2016-02-29 18:00:00"),
('Server H', '10.100.100.100', 3, 1, "2019-03-31 20:00:00"),
('Server T', '10.170.193.132', 1, 0, "2018-04-30 19:30:00");
SELECT s.id, s.serverName, s.ip, s.hostCompId, s.status, s.createdAt, c.id, c.companyName
FROM mission3.servers AS s
LEFT JOIN mission3.companies AS c
ON s.hostCompId = c.id
See above
UPDATE mission3.servers
SET status = true
WHERE id = 1
DELETE
FROM mission3.tasks
WHERE id = 7;
CREATE TABLE users (
id varchar(20) NOT NULL,
firstName varchar(25) NOT NULL,
lastName varchar(25) NOT NULL,
email varchar(70) NOT NULL,
password varchar(70) NOT NULL,
city varchar(25) NOT NULL,
street varchar(25) NOT NULL,
house varchar(15) NOT NULL,
apartment int NOT NULL,
role ENUM('user', 'admin') DEFAULT 'user',
createdAt TIMESTAMP DEFAULT NOW(),
updatedAt TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY id (id),
UNIQUE KEY email (email)
);
CREATE TABLE IF NOT EXISTS categories (
id int AUTO_INCREMENT NOT NULL,
name VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS products (
id int AUTO_INCREMENT NOT NULL,
name VARCHAR(25) NOT NULL,
price DECIMAL(12,2) NOT NULL,
imagePath VARCHAR(200) NOT NULL,
categoryId int NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (categoryId) REFERENCES categories(id)
);
CREATE TABLE carts ( id int NOT NULL AUTO_INCREMENT, userId varchar(20) NOT NULL, isOpen BOOLEAN DEFAULT TRUE, createdAt TIMESTAMP DEFAULT NOW(), PRIMARY KEY (id), FOREIGN KEY (userId) REFERENCES users(id) );
CREATE TABLE cartItems ( id int NOT NULL AUTO_INCREMENT, productId int NOT NULL, quantity int NOT NULL, itemPrice DECIMAL(12,2) NOT NULL, cartId int NOT NULL, PRIMARY KEY (id), FOREIGN KEY (productId) REFERENCES products(id), UNIQUE KEY `unique_keys` (cartId, productId), FOREIGN KEY (cartId) REFERENCES carts(id) );
CREATE TABLE orders ( id int NOT NULL AUTO_INCREMENT, totalPrice DECIMAL(20,2) NOT NULL, userId varchar(20) NOT NULL, cartId int NOT NULL, city varchar(25) NOT NULL, street varchar(25) NOT NULL, house varchar(15) NOT NULL, apartment int NOT NULL, dateToShip DATE NOT NULL, creditCardPartialDigits varchar(4) NOT NULL, createdAt TIMESTAMP DEFAULT NOW(), PRIMARY KEY (id), FOREIGN KEY (userId) REFERENCES users (id), FOREIGN KEY (cartId) REFERENCES carts (id) );
SELECT * FROM people
WHERE id IN
(SELECT personId FROM people_in_films
WHERE filmId = 123)
SELECT c.name FROM jbh_shop.customers AS c LEFT JOIN jbh_shop.orders AS o ON c.id = o.customer_id LEFT JOIN jbh_shop.orders_products AS op ON o.id = op.order_id LEFT JOIN jbh_shop.products AS p ON op.product_id = p.id WHERE p.name LIKE "Apple%"
SELECT c.id, c.name
FROM jbh_shop.customers AS c
INNER JOIN
(SELECT o.customer_id
FROM jbh_shop.orders AS o
INNER JOIN jbh_shop.orders_products AS op1 ON o.id = op1.order_id
INNER JOIN jbh_shop.orders_products AS op2 ON o.id = op2.order_id
WHERE op1.product_id = 1
AND op2.product_id = 1
GROUP BY o.customer_id )
AS t ON c.id = t.customer_id
SELECT c.id, c.name, Count(op.product_id) FROM jbh_shop.customers AS c LEFT JOIN jbh_shop.orders AS o ON c.id = o.customer_id LEFT JOIN jbh_shop.orders_products AS op ON o.id = op.order_id GROUP BY order_id
SELECT
ROUND(AVG(totalPricePerOrder))
FROM
( SELECT SUM(p.price) AS totalPricePerOrder
FROM jbh_shop.orders_products AS op
LEFT JOIN jbh_shop.products AS p ON op.product_id = p.id
LEFT JOIN jbh_shop.orders AS o ON o.id = op.order_id
LEFT JOIN jbh_shop.customers AS c ON c.id = o.customer_id
GROUP BY op.order_id )
AS inner_query
SELECT id, first_name FROM johnbryceusers.users
WHERE date_of_birth IN
(SELECT MAX(date_of_birth)
FROM johnbryceusers.users
WHERE date_of_birth <
(SELECT
MAX(date_of_birth)
FROM johnbryceusers.users
)
);
SELECT COUNT(*) FROM johnbryceusers.users WHERE email LIKE '%edu';
SELECT first_name,last_name FROM johnbryceusers.users WHERE date_of_birth='2000';
SELECT ROUND(AVG(date_of_birth)) FROM johnbryceusers.users WHERE phone LIKE '050%';
SELECT date_of_birth FROM johnbryceusers.users GROUP BY date_of_birth ORDER BY Count(id) DESC LIMIT 3;
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: 10,
host: 'localhost',
user: 'johnBryce_user',
password: 'myPassword',
database: 'mission3'
});
pool.on('connection', () => console.log('Mysql connection'));
/* pool.query('SELECT * FROM servers', (err, res) => {
if (err) throw err;
console.log(res);
}); */
module.exports = { pool };
const express = require('express');
const { pool } = require('./database/db');
const app = express();
app.use(express.json());
// GET
app.get('/api/items', (req, res) => {
res.status(200).json({});
});
// POST
app.post('/api/items', (req, res) => {
res.status(200).json({});
});
const PORT = process.env.PORT || 5000;
app.listen(PORT, console.log(`Server started on port ${PORT}`));
const express = require('express');
const cors = require('cors');
const { randomBytes } = require('crypto');
require('dotenv').config();
const app = express();
// Body Parser Middleware
app.use(express.json());
app.use(cors());
const posts = {};
const baseUrl = process.env.BASE_URL;
app.get(`${baseUrl}posts`, (req, res) => {
res.send(posts);
});
app.post(`${baseUrl}posts`, (req, res) => {
const id = randomBytes(4).toString('hex');
const { title } = req.body;
posts[id] = {
id,
title,
};
res.status(201).send(posts[id]);
});
const PORT = process.env.PORT || 4000;
app.listen(PORT, () => {
console.log(`Server Started in ${baseUrl} on Port ${PORT}`);
});
// GET
app.get('/api/servers', (req, res) => {
const sql = `
SELECT s.id, s.serverName, s.ip, s.hostCompId, s.status, s.createdAt, c.id, c.companyName
FROM servers AS s
LEFT JOIN companies AS c
ON s.hostCompId = c.id
`;
pool.query(sql, (err, results) => {
if (err) {
res.status(500).json({success: false});
throw err;
}
res.status(200).json({success: true, data: results});
});
});
// POST
app.post('/api/todolist', (req, res) => {
const { taskName, ownerId } = req.body;
//Validation
if (isNaN(Number(ownerId)) || !taskName) {
return res.status(400).json({success: false});
}
const newTask = { taskName, ownerId };
const sql = `
INSERT INTO tasks
SET ?
`;
pool.query(sql, [newTask], (err, results, fields) => {
if (err) {
res.status(400).json({success: false});
throw err;
}
res.status(200).json({success: true});
})
});
// PUT
app.put('/api/server/status', (req, res) => {
const { status, id } = req.body;
// Validation
if (isNaN(Number(id)) || ![true, false].includes(status)) {
console.log('Validation has failed.');
return res.status(400).json({success: false});
}
const sql = `
UPDATE servers
SET status = ?
WHERE id = ?
`;
pool.query(sql, [status, id], (err, results) => {
if (err) {
res.status(500).json({success: false});
throw err;
}
res.status(200).json({success: true});
});
});
//DELETE
app.delete('/api/todolist/:id', (req, res) => {
const taskId = req.params.id;
//Validation
if (isNaN(Number(taskId))) {
return res.status(400).json({success: false});
}
const sql = `
DELETE
FROM tasks
WHERE id = ?;
`;
pool.query(sql, [taskId], (err, results, fields) => {
if (err) {
res.status(400).json({success: false});
throw err;
}
res.status(200).json({success: true});
});
});
export const GET_SERVERS = 'GET_SERVERS';
export const CHANGE_SERVER_STATUS = 'CHANGE_SERVER_STATUS';
import { createContext } from 'react';
const serversContext = createContext();
export default serversContext;
import { GET_SERVERS, CHANGE_SERVER_STATUS } from '../types';
export default (state, action) => {
const { type, payload } = action;
const { servers } = state;
switch(type) {
case GET_SERVERS:
return {
...state,
servers: payload
}
case CHANGE_SERVER_STATUS:
const { id } = payload;
return {
...state,
servers: servers.map(server =>
server.id === id
? { ...server, status: server.status === 1 ? 0 : 1}
: server
)
}
...
case UPDATE_UNITS:
const { id, units } = payload;
return {
...state,
products: products.map(product =>
product.id === id
? { ...product, units: units, updatedAt: Date.now() }
: product
)
}
...
default:
return state;
}
}
import React, { useReducer } from 'react';
import serversContext from './serversContext';
import serversReducer from './serversReducer';
import { GET_SERVERS, CHANGE_SERVER_STATUS } from '../types';
const ServersProvider = props => {
const initialState = {
servers: []
}
const [state, dispatch] = useReducer(serversReducer, initialState);
const getServers = async () => {
...
}
const changeServerStatus = async (status, id) => {
...
}
const { servers } = state;
return (
<serversContext.Provider
value={{
servers,
getServers,
changeServerStatus
}}
>
{props.children}
</serversContext.Provider>
)
}
export default ServersProvider;
import ServersProvider from './context/servers/ServersProvider';
function App() {
return (
<ServersProvider>
</ServersProvider>
);
}
const getServers = async () => {
const url = '/api/servers';
try {
const res = await fetch(url);
const response = await res.json();
console.log(response.data);
if (res.status === 200) {
dispatch({
type: GET_SERVERS,
payload: response.data
})
} else {
alert('Error');
}
} catch(err) {
alert(err);
}
}
const addTask = async task => {
const url = '/api/todolist';
const config = {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify(task)
}
try {
const res = await fetch(url, config);
const response = await res.json();
console.log(response.data);
if (res.status === 200) {
dispatch({
type: ADD_TASKS,
payload: task
})
}
else {
alert('Error');
}
} catch (err) {
alert(err);
}
}
const changeServerStatus = async (status, id) => {
const url = '/api/server/status';
const config = {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({status: status === 1 ? false : true, id})
}
try {
const res = await fetch(url, config);
const response = await res.json();
if (res.status === 200) {
dispatch({
type: CHANGE_SERVER_STATUS,
payload: {status, id}
})
} else {
alert('Error');
}
} catch(err) {
alert(err);
}
}
const deleteTask = async taskId => {
console.log(taskId);
const url = `/api/todolist/${taskId}`;
const config = {
method: 'DELETE'
}
try {
const res = await fetch(url, config);
const response = await res.json();
if (res.status === 200) {
dispatch({
type: DELETE_TASKS,
payload: taskId
})
}
else {
alert('Error');
}
} catch (err) {
alert(err);
}
}
import React, { useContext, useEffect, useState } from 'react';
import serversContext from '../../context/servers/serversContext';
import ServerCard from './ServerCard';
const ServersList = () => {
const { servers, getServers } = useContext(serversContext);
const [showRunningServersOnly, setRunningServers] = useState(false);
const [sortByDateDesc, setSortByDateDesc] = useState(null);
useEffect(() => {
getServers();
// eslint-disable-next-line
}, []);
const sortBtnHandle = () => {
if (sortByDateDesc === null) {
setSortByDateDesc(true);
} else {
if (sortByDateDesc === true) {
setSortByDateDesc(false);
} else {
setSortByDateDesc(true);
}
}
}
const renderServers = () => {
let serversToShow = [...servers];
if (showRunningServersOnly) {
serversToShow = serversToShow.filter(server => server.status === 1)
}
if (sortByDateDesc !== null) {
if (sortByDateDesc) {
serversToShow.sort((a, b) => new Date(b.createdAt) - new Date(a.createdAt))
} else {
serversToShow.sort((a, b) => new Date(a.createdAt) - new Date(b.createdAt))
}
}
return serversToShow.map(server => <ServerCard key={server.id} server={server} />);
}
return (
<div>
<div className="card card-body my-2">
<div className="d-flex justify-content-between">
<div>
<input
type="checkbox"
checked={showRunningServersOnly}
onChange={(e) => setRunningServers(e.target.checked)}
/> { }
<label>Show Running Only</label>
</div>
<button className="btn btn-primary" onClick={sortBtnHandle}>Sort By Date</button>
</div>
</div>
<div className="row">
{renderServers()}
</div>
</div>
)
}
export default ServersList;
import React, { useContext} from 'react';
import serversContext from '../../context/servers/serversContext';
const ServerCard = ({ server }) => {
const { changeServerStatus } = useContext(serversContext);
const { id, serverName, ip, companyName, status, createdAt } = server;
const toggleStatus = () => {
changeServerStatus(status, id);
}
return (
<div className="col-sm-12 col-md-4 mb-3">
<div className="card">
<div className="card-body">
<div className="d-flex justify-content-between">
<h4>{serverName}</h4>
<div className="custom-control custom-switch">
<input type="checkbox" onChange={toggleStatus} className="custom-control-input" id={`customSwitch-${id}`}
checked={status === 1 ? 1 : 0}
/>
<label className="custom-control-label" htmlFor={`customSwitch-${id}`}>
{status === 1 ? 'Online' : 'Offline'}</label>
</div>
</div>
<p>ID: {id}</p>
<p>IP: {ip}</p>
<p>Hosting Company: {companyName}</p>
<p>Created at: {(new Date (createdAt)).toLocaleDateString()}</p>
</div>
</div>
</div>
)
}
export default ServerCard;
import { BrowserRouter as Router, Route, Switch } from 'react-router-dom';
<Router>
<Switch>
<Route exact path='/' component={Tasks} />
<Route exact path='/add-task' component={TaskForm} />
</Switch>
</Router>
import { Link } from 'react-router-dom';
...
<Link to='/'>Home</Link>