esulu / comp3005-project

Online Bookstore Application

COMP3005-Bookstore

Look Inna Book online bookstore application for COMP 3005 at Carleton University in the Fall 2021 term.

Authors

• Eren Sulutas #101101873
• Ben Li(bochen li) # 101113284
• Joshua Challenger #101119396

Instructions for Running

• Clone the repository
• Download and install pgAdmin and postgresql
• Create a relational database named bookstore
• Run the DDL.sql, populate.sql, views.sql and trigger.sql files to create the tables/views and populate it with sample data
• Update the credentials in the top of server/source/db.ts to connect to your local database
• cd into the server/source directory and use npm install and npm run dev to start the db server
• create a new terminal
• cd to client and run npm install and npm start to start the app locally

User Credentials

The application requires users to be logged in in order to browse and order books. Here are credentials to some test users:

• Regular User / Customer
  • Username: esulu
  • Password: pass
• Admin / Store Owner
  • Username: admin
  • Password: admin

Tools

• React
• PostgreSQL
• pgAdmin
• NodeJS
• Express
• Material UI

comp3005-project's Issues

Add books to cart database

When books are requested to enter the cart - add them to the user's cart.

This endpoint may be called several times; the quantity will have to be appended in these instances. The quantity may also be negative to signify removing from the cart (the input/front end should be doing the bound checking)

Edit: thinking of changing this so whenever it is called, the quantity is overwritten with the value in the last request

Order Tracking Feature

The user should be able to enter/click a link for a given order number and be displayed shipping information.

Requirements

1. Order number input and data retrieval #13
2. Order information page #12

Create a book view

When a book is selected, the following information can be viewed:

• author(s)
• genre
• publisher
• page count
• price
• book cover
• year

The user will also be given the option to add as many books as they like to their cart (limited by how much is available) -> this will be done in another ticket

Depends on #7

Retrieve order data given order number

Create an input or a link for an order number for tracking purposes.

The database should return the shipping information given the order number.

Note: Information should only be returned if the user_ID of the order matches the user_ID making the request

Update populate.sql

1. Update the python script in /scrips/createDDL.py to use the proper INSERT INTO values for books. In the script currently it doesn't have a boolean value for is_purchasable, add another value for this, and set it to true for all books.
2. Update the script to output 100 or 200 books.
3. Create statistical data, there are two options to do this:
   3.1: Update the python script to create INSERT INTO statements for the user, cart, contains and order. Simply create an array of around 10-20 users, create many different carts for each and perform many orders for each in the time period of the last two months and output them to the file. The code will be similar to what was done in the script already.
   3.2: Instead of modifying the script to do the above, do this manually. This would just be editing the populate.sql AFTER the python script is run to put in the books.

The expected result is a populate.sql file with the proper INSERT INTO statements for the ones already created by the script and additionally statements for the user, cart, contains, order and shipping tables.
It's expected for the SELECT SETVAL statements to remain on the updated populate.sql
This is needed due to the requirement of having statistical data based on the last month.

NOTE: The script requires beautifulsoup4, of which can be installed by the command:
pip install beautifulsoup4

Changes to the SQL populate file:

• Prune the mock data
  • Remove instances of brackets which break the search
  • Merge instances of double authors
• Add order data to test the auto email feature and the statistics page

Checkout procedure

User decides to check out. They must enter the following information:

• billing information
• shipping information

The user may abort the order, or complete the order.

Should the order be completed, the following occurs:

• An order number is generated
• A shipping number is created for the shipping company
• A commission is paid to the publishers
• Update book quantity

Create login page

• If not signed in, users are prompted with a sign-in page
• Verify user credentials using the database 🤠
• Once signed in, the user's session will check for owner permissions and link the user ID to any purchases/site activity

Retrieve the number of books sold per month for a given book

Given the book ISBN, retrieve from the database the number of those books sold in the previous month.

Owner sale statistic view

Create a view for the owners to see sale information reports. These reports could be charts. This should include the following:

• sales vs. expenditures
• sales per genre
• sales per author

Depends on #17

Convert sql files to SQLite syntax

View examples:
https://www.sqlitetutorial.net/sqlite-create-table/
https://www.sqlitetutorial.net/sqlite-data-types/

Collect and display books from the database to the user

Users can browse book available in the bookstore

Book quantity updates

• The owner adding a book (that already exists) should update the quantity of the book
• Every time the user checks out, the quantity of the book should be decreased by the amount sold
• If there are 0 copies left, the book should be removed from the store (i.e., set is_purchaseable to false)

Add books to cart

A user may select book to add to their cart on the book page.

Add functionality to the buttons on the book page to store the book(s) into the user cart.

When a user adds a book to their cart, show a success message

Dependent on #6 and #10

Add tabs for the owner

When the user is an owner, tabs should appear for the owner, specifically "statistics" and "add/remove books"

Get book info given data

Backend API for retrieving and delivering book data when requested. The data should include the following:

• author(s)
• genre
• publisher
• page count
• price
• book cover
• year
• quantity

(aka all the book data we got)

Order books automatically

Book quantity is updated in #11, add a check if we should order books if the quantity falls below a specified threshold.

It will also send an email to the publisher to order books equal to how many were sold in the previous month

Depends on #11 for the checkout procedure, and #20 for getting the number of books for the order.

Create a DDL file for populating the database

Create a ddl file for population of the database. These should populates the book, publisher, inst_phone, written_by and author tables

Make book searches case-insensitive and other improvements

Searches for a book requires the exact capitalization as book titles, genres, authors, etc.

Additionally, we only show exact matches. If we searched "Harry Potter" we would not get any of the HP books returned

Owner: Database Add & Remove Books

Add new books to the database when book information is provided. Also need to provide publisher and author information.

Remove books from the database when ISBN is provided.

• Cascade deletion (also delete from written_by, contains)

Added books must check the foreign key constraints (on the frontend):

• The inputted warehouse_ID and publisher_ID must exist

Owner view: Add and Remove books from the store

Gives the owners the option to add books given book info, and to remove books based on an ISBN.

Adding a book:

• Use a dropdown select for warehouse and publisher and author

Depends on #15

Use triggers for the update quantity

Database owner view sale information

Owners have access to sale statistics. When an owner requests to view this information, they should be able to see the following:

• sales vs. expenditures (cut to the publishers)
• sales per genres
• sales per author

Create logout feature

delete the local token

Tracking order page/view

(Prerequisite) the data for a given order number has been retrieved by the database

This page should show the following information to the user:

• Tracking number
• Third party company name
• Order status

Update README

Make sure details are up to spec!

Addressed in commit: cd1edb1

SQL File Improvements

• Add comments
• Perhaps organize the queries in folders of related query types
• Remove unused queries (and also server requests)

Get list of books for search

Obtain a list of books given a search and/or sort query. The search query may include the following:

• book name
• author name
• ISBN
• genre

Possible sorting: price, year

Search feature for books

User can search the bookstore by any of the following:

• book name
• author name
• ISBN
• genre

Sorting/Filtering (optional):

• price (sorting perhaps)
• year (most recent / oldest)

Depends on #8

Optional: Provide book ordering in /books/

As stated in #28, book ordering is difficult as node-pg does not support parameterized arguments for ORDER BY statements.
Provide this functionality by either using a different database library - pg-promise, see:
https://stackoverflow.com/questions/32425052/using-limit-order-by-with-pg-postgres-nodejs-as-a-parameter
Or by using a function taking the table as an argument and performing order by on it

Create a basic back end interface using typescript and express to provide requests from our react application

Create a backend server using express, add instructions on how to use the server and provide extremely basic routing on said server.

Update and remove books from cart database

Create the following endpoints:

1. Edit book quantity: this endpoint should change the quantity of books in the user's cart
2. Remove book: this should remove this book from the user's cart
3. Get book: get all books in the user's cart

Make logout force user sign out on all open tabs

Bonus: Improved search feature

Improve the search feature in a given way as an additional feature

Cart page - view and update books in cart

On the cart page, the user may view and edit the purchase quantity or remove books form their cart. They should be able to change the quantity (either using + and - buttons or a text-box like entry) or drop the book order all together.

Ensure the user doesn't add (or remove) more books than what is available.

Note: the following can be used to set a limit for the text box entry:
InputProps={{ inputProps: { min: 0, max: 10 } }}

Depends on #39