CodeYourFuture · AndrianaOS · May 20, 2023 · May 20, 2023 · May 23, 2023 · Jun 13, 2023
diff --git a/.gitignore b/.gitignore
@@ -1,2 +1,3 @@
 
 E-Commerce-API/node_modules/
+.env
diff --git a/.vscode/settings.json b/.vscode/settings.json
@@ -0,0 +1,6 @@
+{
+  "[sql]": {
+    "editor.defaultFormatter": "dorzey.vscode-sqlfluff"
+  },
+  "cSpell.words": ["Cust"]
+}
diff --git a/Big-Spender/readme.md b/Big-Spender/readme.md
@@ -49,6 +49,7 @@ You are working with Claire and Farnoosh, who are trying to complete a missing r
 
 ```sql
 INSERT YOUR QUERY HERE
+ANSWER: SELECT * FROM spends WHERE amount BETWEEN 30000 AND 31000;
 ```
 
 **Claire:** That's great, thanks. Hey, what about transactions that include the word 'fee' in their description?
@@ -69,6 +70,8 @@ INSERT YOUR QUERY HERE
 
 ```sql
 INSERT YOUR QUERY HERE
+ANSWER: SELECT * FROM spends WHERE lower(description) LIKE '%fee%';
+
 ```
 
 **Farnoosh:** Hi, it's me again. It turns out we also need the transactions that have the expense area of 'Better Hospital Food'. Can you help us with that one?
@@ -77,6 +80,8 @@ INSERT YOUR QUERY HERE
 
 ```sql
 INSERT YOUR QUERY HERE
+ANSWER: SELECT date, supplier_id, description, amount FROM spends INNER JOIN expense_areas ON spends.expense_area_id = expense_areas.id WHERE expense_area = 'Better Hospital Food';
+
 ```
 
 **Claire:** Great, that's very helpful. How about the total amount spent for each month?
@@ -85,6 +90,8 @@ INSERT YOUR QUERY HERE
 
 ```sql
 CREATE YOUR QUERY HERE
+ANSWER: SELECT SUM(amount) AS total_amount, date FROM spends GROUP BY date;
+
 ```
 
 **Farnoosh:** Thanks, that's really useful. We also need to know the total amount spent on each supplier. Can you help us with that?
@@ -93,6 +100,7 @@ CREATE YOUR QUERY HERE
 
 ```sql
 INSERT YOUR QUERY HERE
+ANSWER: SELECT SUM(s.amount) AS total_amount FROM spends s INNER JOIN suppliers sup ON sup.id = s.supplier_id GROUP BY sup.supplier;
 ```
 
 **Farnoosh:** Oh, how do I know who these suppliers are? There's only numbers here.
@@ -101,6 +109,7 @@ INSERT YOUR QUERY HERE
 
 ```sql
 INSERT YOUR QUERY HERE
+ANSWER: SELECT SUM(s.amount) AS total_amount, supp.supplier FROM spends s INNER JOIN suppliers supp ON supp.id = s.supplier_id GROUP BY supp.supplier;
 ```
 
 **Claire:** Thanks, that's really helpful. I can't quite figure out...what is the total amount spent on each of these two dates (1st March 2021 and 1st April 2021)?
@@ -113,6 +122,8 @@ INSERT YOUR QUERY HERE
 
 ```sql
 CREATE YOUR QUERY HERE
+ANSWER: SELECT SUM(amount) AS total_amount, date FROM spends GROUP BY date;
+
 ```
 
 **Farnoosh:** Fantastic. One last thing, looks like we missed something. Can we add a new transaction to the spends table with a description of 'Computer Hardware Dell' and an amount of £32,000?
@@ -125,6 +136,12 @@ CREATE YOUR QUERY HERE
 
 ```sql
 INSERT YOUR QUERIES HERE
+ANSWER: INSERT INTO spends (expense_type_id, expense_area_id, supplier_id, date, transaction_no, supplier_inv_no, description, amount) VALUES (7, 18, 16, '2021-08-19', 38104091, '3780119655', 'Computer Hardware Dell', 32000);
+INSERT INTO suppliers (supplier) VALUES ("Dell");
+INSERT INTO expense_types (expense_type) VALUES ("Hardware")
+INSERT INTO expense_areas (expense_area) VALUES ("IT")
+
+
 
 ```
 

diff --git a/E-Commerce-API/app.js b/E-Commerce-API/app.js
@@ -1,8 +1,221 @@
+const dotenv = require("dotenv");
+dotenv.config();
+
+const { response, query } = require("express");
 const express = require("express");
 const app = express();
+const { Pool } = require("pg");
+const bodyParser = require("body-parser");
+const port = process.env.PORT || 3009;
+
+app.use(bodyParser.json());
+
+const productData = require("./dbConfig");
+
 // Your code to run the server should go here
 // Don't hardcode your DB password in the code or upload it to GitHub! Never ever do this ever.
 // Use environment variables instead:
 // https://www.codementor.io/@parthibakumarmurugesan/what-is-env-how-to-set-up-and-run-a-env-file-in-node-1pnyxw9yxj
 
+// should return a list of all product names with their prices and supplier names and should filter the list of products by name using a query parameter, even if the parameter is not used.
+app.get("/products", (req, res) => {
+  const searchWord = req.query.word || "";
+
+  let getQuery =
+    "SELECT p.product_name, pa.unit_price, s.supplier_name FROM products p JOIN product_availability pa ON (p.id = pa.prod_id) JOIN suppliers s ON (pa.supp_id = s.id)";
+
+  const searchQuery = " WHERE lower(p.product_name) LIKE '%' || $1 || '%' ";
+
+  productData
+    .query(getQuery + " " + searchQuery, [searchWord])
+    .then((result) => {
+      let product = result.rows.map((item) => {
+        return {
+          name: item.product_name,
+          price: item.unit_price,
+          supplierName: item.supplier_name,
+        };
+      });
+      return res.status(200).json(product);
+    })
+    .catch((error) => {
+      console.log(error);
+    });
+});
+
+// should load a single customer by their ID.
+app.get("/customers/:id", (req, res) => {
+  const customerID = parseInt(req.params.id);
+
+  const idQuery = "SELECT * FROM customers WHERE id = $1";
+
+  productData
+    .query(idQuery, [customerID])
+    .then((result) => {
+      console.log(result.rows);
+      if (result.rows === 0) {
+        res.status(404).json({ error: `Customer ${customerID} not found` });
+      } else {
+        res.status(200).json(result.rows);
+      }
+    })
+    .catch((error) => console.log(error));
+});
+
+// should create a new customer with name, address, city, and country.
+app.post("/customer", (req, res) => {
+  const {
+    name: newName,
+    address: newAddress,
+    city: newCity,
+    country: newCountry,
+  } = req.body;
+
+  const newQuery =
+    "INSERT INTO customers (name, address, city, country) VALUES ($1, $2, $3, $4)";
+
+  productData
+    .query(newQuery, [newName, newAddress, newCity, newCountry])
+    .then(() =>
+      res.status(200).json({
+        message: "New Customer added",
+        customer: {
+          name: newName,
+          address: newAddress,
+          city: newCity,
+          country: newCountry,
+        },
+      })
+    )
+    .catch((error) => console.log(error));
+});
+
+// should create a new product.
+app.post("/products", (req, res) => {
+  const newProdName = req.body.product_name;
+
+  const prodQuery = "INSERT INTO products (product_name) VALUES ($1)";
+
+  productData
+    .query(prodQuery, [newProdName])
+    .then(() => {
+      res.status(200).json({
+        message: "New product added",
+        product: {
+          product_name: newProdName,
+        },
+      });
+    })
+    .catch((error) => {
+      console.log(error);
+    });
+});
+
+// should create a new product availability with a price and supplier ID. An error should be returned if the price is not a positive integer or if either the product or supplier IDs don't exist in the database.
+app.post("/availability", (req, res) => {
+  const {
+    prod_id: newProductID,
+    supp_id: newSupplierID,
+    unit_price: newPrice,
+  } = req.body;
+
+  const productIDQuery = "SELECT 1 FROM products WHERE id=$1";
+  const supplierIDQuery = "SELECT 1 FROM suppliers WHERE id=$2";
+
+  if (!parseInt(newPrice) || newPrice <= 0) {
+    return res.status(400).json({ error: "Price must be a positive integer" });
+  }
+
+  productData
+    .query(productIDQuery, [newProductID])
+    .then((result) => {
+      if (result.rowCount === 0) {
+        return res.status(400).json({ error: "Invalid Product ID" });
+      }
+    })
+    .catch((error) => console.log(error));
+
+  productData
+    .query(supplierIDQuery, [newSupplierID])
+    .then((result) => {
+      if (result.rowCount === 0) {
+        return res.status(400).json({ error: "Invalid Supplier ID" });
+      }
+    })
+    .catch((error) => console.log(error));
+
+  const newQuery =
+    "INSERT INTO product_availability ( prod_id, supp_id,unit_price) VALUES ($1, $2, $3)";
+
+  productData
+    .query(newQuery, [newProductID, newSupplierID, newPrice])
+    .then(() =>
+      res.status(200).json({
+        message: "New product availability information added",
+        productInfo: {
+          prod_id: parseInt(newProductID),
+          unit_price: parseInt(newPrice),
+          supp_id: parseInt(newSupplierID),
+        },
+      })
+    )
+    .catch((error) => console.log(error));
+});
+
+// should create a new order for a customer, including an order date and order reference. An error should be returned if the customer ID doesn't correspond to an existing customer.
+app.post("/customers/:id/orders", (req, res) => {
+  const {
+    order_date: newDate,
+    order_reference: newRef,
+    customer_id: newCustID,
+  } = req.body;
+
+  const customerIDQuery = "SELECT 1 FROM customers WHERE id=$1";
+
+  productData
+    .query(customerIDQuery, [newCustID])
+    .then((result) => {
+      if (result.rowCount === 0) {
+        return res.status(400).json({ error: "Invalid Customer ID" });
+      }
+    })
+    .catch((error) => console.log(error));
+
+  const orderPrefix = "ORD";
+
+  const refQuery = "SELECT 1 FROM orders WHERE order_reference=$2";
+
+  productData
+    .query(refQuery, [newRef])
+    .then((result) => {
+      if (result.rowCount > 0) {
+        if (!newRef.includes(orderPrefix)) {
+          throw { error: "Order reference had to include 'ORD'" };
+        }
+      }
+    })
+    .catch((error) => console.log(error));
+
+  const newQuery =
+    "INSERT INTO orders (order_date, order_reference, customer_id) VALUES ($1, $2, $3)";
+
+  productData
+    .query(newQuery, [newDate, newRef, newCustID])
+    .then(() => {
+      res.status(200).json({
+        message: "New order added",
+        orderInfo: {
+          order_date: newDate,
+          order_reference: parseInt(newRef),
+          customer_id: parseInt(newCustID),
+        },
+      });
+    })
+    .catch((error) => console.log(error));
+});
+
+app.listen(port, function () {
+  console.log(`Server is listening on port ${port}. Ready to accept requests!`);
+});
+
 module.exports = app;
diff --git a/E-Commerce-API/dbConfig.js b/E-Commerce-API/dbConfig.js
@@ -0,0 +1,11 @@
+const { Pool } = require("pg");
+
+const productData = new Pool({
+  user: process.env.DB_USERNAME,
+  host: process.env.DB_HOST,
+  database: process.env.DB_DATABASE,
+  password: process.env.DB_PASSWORD,
+  port: process.env.DB_PORT,
+});
+
+module.exports = productData;