I need some help and explanation

will be a text-based .cpp file including your C++ program and a text-based .sql file including your Oracle stored procedures for the Database Application .

DBS311sectioncode_ASS2_group#.cpp

DBS311sectioncode_ASS2_group#.sql

See the following example:

DBS311NAA_ASS2_group02.cpp

The .sql file includes all stored procedures that you are asked to write in the assignnment. Writte stored procedures in the same order that they are defined in the assignnment document. Test all your procedures and make sure that they can be called from the C++ program with no errors.

The .cpp file is the same as application.cpp with two additional functions that you define based on the given instruction in the assignment document. The submitted C++ program must be error free and all functions must work successfully. Test your program before submitting it on Blackboard.

Instruction

In this assignment, we use the same database that you have been using for the labs and the assignment 1.

Note: For each query , you handle the errors and display the proper message including the error code and the error message.

try{

...

}

catch (SQLException& sqlExcp) {

cout<< sqlExcp.getErrorCode()<<": "<< sqlExcp.getMessage();

}

The following structure is declared before the main() function:

structShoppingCart {

int product_id;

double price;

int quantity;

};

Connecting to an Oracle database from a C++ Program

In your function main(), a connection is created to your database.

We first declare the environment and the connection variables.

Environment* env =nullptr;

Connection* conn =nullptr;

We also define and initialize the variable to store the username, password, and the host address.

string user ="username";

string pass ="password";

string constr ="myoracle12c.senecacollege.ca:1521/oracle12c";

You use the same Oracle username and password that you use for your labs and assignments.

After creating the environment and the connection, we make sure to handle any errors may be thrown as the program is executed.

env =Environment::createEnvironment(Environment::DEFAULT);

conn = env->createConnection(user, pass, constr);

We terminate and close the connection and the environment, when the program terminates.

env->terminateConnection(conn);

Environment::terminateEnvironment(env);

After executing the statements, we make sure that the statement is terminated.

conn->terminateStatement(stmt);

You will implement the following Oracle stored procedures and will call them in the following C++ functions:

Stored Procedures

find_customer (customer_id IN NUMBER, found OUT NUMBER);

This procedure has an input parameter to receive the customer ID and an output parameter named found.

This procedure looks for the given customer ID in the database. If the customer exists, it sets the variable found to 1. Otherwise, the found variable is set to 0.

To check if your query in the find_customer()procedure returns a row, you need to check the no_data_found exception in the EXCEPTION block.

EXCEPTION

WHEN no_data_found THEN

found := 0;

To check if your query in the find_customer()procedure returns multiple rows, you need to check the too_many_rows exception in the EXCEPTION block and display a proper message.

To catch any other errors, check the OTHERS exception in the EXCEPTION block and display a proper message.

find_product (productId IN NUMBER,

price OUT products.list_price%TYPE,

productName OUT products.product_name%TYPE);

This procedure has an input parameter to receive the product ID and an output parameter named price.

This procedure looks for the given product ID in the database. If the product exists, it stores the product's product_name in the variable productName and the product's list_price in the variable price. If the product does not exist, the productName is set to null and the price variable is set to 0.

In November and December, the company gives 10% off on all products in categories 2 and 5. Before storing the list price of products that belong to categories 2 and 5 into the variable price, calculate the new list price if the current month is November or December and send back the new list price to the caller as price.

EXCEPTION

WHEN no_data_found THEN

price := 0;

To check if your query in the find_product()procedure returns multiple rows, you need to check the too_many_rows exception in the EXCEPTION block and display a proper message.

To catch any other errors, check the OTHERS exception in the EXCEPTION block. and display a proper message.

add_order (customer_id IN NUMBER, new_order_id OUT NUMBER)

This procedure has an input parameter to receive the customer ID and an output parameter named new_order_id.

To add a new order for the given customer ID, you need to generate the new order Id.

To calculate the new order Id, call the function generate_order_id(). The procedure add_order inserts the following values in the orders table

new_order_id (as order_id)

customer_id (input parameter)

'Shipped' (The value for the order status)

56 (The sales person ID)

sysdate (order date which is the current date)

generate_order_id ()

This is an Oracle function with no parameters.It finds the maximum order ID in the orders table and increase it by 1 as new order ID. The function returns the new order ID to the caller.

add_order_item (orderId IN order_items.order_id%type, itemId IN order_items.item_id%type, productId IN order_items.product_id%type, quantity IN order_items.quantity%type, price IN order_items.unit_price%type)

This procedure has five IN parameters. It stores the values of these parameters to the table order_items.

customer_order (customerId IN NUMBER, orderId IN OUT NUMBER)

This procedure receives two values as customer ID and Order ID and confirms if there exists any order with this order ID for this customer in the orders table. If the order ID with this customer ID exists, the procedure passes the order ID to the caller. Otherwise, it passes 0 to the caller.

display_order_status(orderId IN NUMBER, status OUT orders.status%type)

This procedure has an input parameter to receive an order ID and an output parameter to pass the status of the order to the caller. IF the receiving order ID exists, the procedure stores the order status in the status variable. If the order ID does not exists, store null in the status variable.

cancel_order (orderId IN NUMBER, cancelStatus OUT NUMBER)

This procedure has an input parameter to receive an order ID and an output parameter to pass a value to the caller. IF the receiving order ID exists, the procedure stores the value of the column status in a variable orderStatus. If orderStatus is 'Canceled', the procedure stores 1 in to the parameter cancelStatus. If the orderStatus is shipped, the procedure stores 2 in to the parameter cancelStatus. Otherwise, it stores 3 in to the parameter cancelStatusand updates the status of that order to "Canceled". If the order ID does not exists, it stores 0 in the cancel variable.

The cancelStatus parameter gets the following values:

0: The order does not exit.

1: The order has been already canceled.

2: The order is shipped and cannot be canceled.

3: The order is canceled successfully.

The stored procedure does not print any outputs.

C++ Functions

Please read the following functions' definition to learn how the C++ code work. You need to call the stored procedures that you wrote in the previous section in these functions based on the following instruction. If the stored procedure pass values to the program, define variables or use variables defined in the given program to store passing values from the stored procedure.

Your task in this section is to add two functions:

void displayOrderStatus(Connection* conn, int orderId, int customerId);

void cancelOrder(Connection* conn, int orderId, int customerId);

void displayOrderStatus(Connection* conn, int orderId, int customerId);

This function calls the customer_order stored procedure to confirm the entered order ID belongs to the customer. If the value of the second parameter of the customer_order procedure is a non-zero value, the order ID belongs to the customer.

If the value of the second parameter of the customer_order procedure is zero, display the following message:

"Order ID is not valid."

If the order ID is valid, call the display_order_statusprocedure to receive the order status stored in the second parameter of this stored procedure. If the order status is null, display the following message:

"Order does not exist."

If the status is not null, display a proper message.

See the sample message:

"Order is shipped."

void cancelOrder(Connection* conn, int orderId, int customerId);

This function calls the cancel_orderstored procedure to confirm the entered order ID belongs to the customer. If the value of the second parameter of the customer_order procedure is a non-zero value, the order ID belongs to the customer.

If the value of the second parameter of the customer_order procedure is zero, display the following message:

"Order ID is not valid."

0: The order does not exit.

1: The order has been already canceled.

2: The order is shipped and cannot be canceled.

3: The order is canceled successfully.

If the order ID is valid, call the cancel_orderprocedure to the order with the order ID passed to this function. If the value passed by the second parameter of thecancel_order is 1, display:

"The order has been already canceled."

If the value passed by the second parameter of thecancel_order is 2, display:

"The order is shipped and cannot be canceled."

If the value passed by the second parameter of thecancel_order is 3, display:

"The order is canceled successfully."

The following functions exist in the application.cpp file. You will modify this file to add two C++ functions described above.

void displayOrderStatus(Connection* conn, int orderId, int customerId);

cancelOrder(Connection* conn, int orderId, int customerId);

int mainMenu();

The mainMenu()function returns an integer value which is the selected option by the user from the menu. This function displays the following menu options:

1. Login
2. Exit

The program prompts the user to choose an option. If the user enters the wrong value, the program asks the user to enter an option again until the user enters a valid options.

See the following example:

******************** Main Menu ********************

1) Login

0) Exit

Enter an option (0-1): 5

******************** Main Menu ********************

1) Login

0) Exit

You entered a wrong value. Enter an option (0-1):

If the user chooses option 1, the program asks the user to enter customer ID to login. To see if the customer with the entered ID exists, the program calls the Oracle stored procedure find_customer(). IF the value of the output parameter in the procedure is 1, the program lets the customer continue. If the value of the output parameter found is 0, the program calls the mainMenu() function again and asks the customer to login again. The program continues this process until the user chooses the option 0 to exit or enters a valid customer ID.

int subMenu();

The subMenu()function returns an integer value which is the selected option by the user from the menu. This function displays the following menu options:

1) Place an order

2) Check an order status

3) Cancel an order

0) Exit

If the user chooses option 1, the user can place an order. If the user chooses option 2, the displayOrderStatus function is called. If the user chooses option 3, the program calls the cancelOrder function. If the user chooses option 0, the user returns to the main menu.

******************** Customer Service Menu ********************

1) Place an order

2) Check an order status

3) Cancel an order

0) Exit

Enter an option (0-3):

int customerLogin(Connection* conn, int customerId);

Before calling this function, the program prompts the user to enter the customer ID.

This function is called in the main() function if the user chooses the login option from the main menu. This function receives an integer value as a customer ID and checks if the customer does exist in the database. This function returns 1 if the customer exists. If the customer does not exists, this function returns 0 and the main menu is displayed.

To validate the customer ID, you call the find_customer() stored procedure/function in this function.

See the following example:

******************** Main Menu ********************

1) Login

0) Exit

Enter an option (0-1): 1

Enter the customer ID: 1000

The customer does not exist.

******************** Main Menu ********************

1) Login

0) Exit

Enter an option (0-1): 1

Enter the customer ID: 44

-------------- Add Products to Cart --------------

Enter the product ID:

int addToCart(Connection* conn, struct ShoppingCart cart[]);

If the customerLogin() functions return 1 (The customer ID exists), this function is called.

This function receives an OCCI pointer (a reference variable to an Oracle database) and an array of type ShoppingCart.

The customer can purchase up to five items in one order.

In a loop the program prompts the user to enter product IDs for the maximum of five products.

When the user enters the product ID in the addToCart() function, the findProduct() function is called to check if the product ID exists. IF the product exists, the function findProduct() returns the product's price. The program displays the product's price to the user and asks the user to enter the quantity.

If the user enters a valid product ID, the program displays the following message and let the user to enter another product ID.

"Enter 1 to add more products or 0 to checkout:"

If the user chooses 1, the program asks the user to enter the next product ID. Otherwise, the program goes to the next step to checkout.If the user enters 0, the function addToCart(), returns the number of products (items) entered by the user.

For each product ID entered by the customer, the function findProduct() is called to see if the product ID exists.

If the findProduct() function returns 0 (The product ID does not exist), the programdisplays a proper message and lets the user enter the product ID again.

See the following example:

-------------- Add Products to Cart --------------

Enter the product ID: 1000

The product does not exists. Try again...

Enter the product ID: 900

The product does not exists. Try again...

Enter the product ID: 112

Product Price: 808.92

Enter the product Quantity: 3

Enter 1 to add more products or 0 to checkout: 1

Enter the product ID: 115

Product Price: 699.99

Enter the product Quantity: 2

Enter 1 to add more products or 0 to checkout: 0

double findProduct(Connection* conn, int product_id);

This function receives an OCCI pointer (a reference variable to an Oracle database) and an integer value as the product ID.

When the user enters the product ID in the addToCart() function, the function findProduct() is called.

This functions calls the find_product() Oracle stored procedure/function. The procedure receives the product ID and returns the price. If the price is 0, the product ID is not valid (does not exist). If the price is a non-zero value, it means the product ID is valid.

void displayProducts(struct ShoppingCart cart[], int productCount);

This function receivesan array of type ShoppingCart and the number of ordered items (products). It display the product ID, price, and quantity for products stored in the cart array.

This function is called after the function AddToCart() to display the products added by the user to the shopping cart.

------- Ordered Products ---------

---Item 1

Product ID: 112

Price: 808.92

Quantity: 3

---Item 2

Product ID: 115

Price: 699.99

Quantity: 2

----------------------------------

Total: 3826.74

After displaying the products' information (product ID, price, and quantity), the program displays the total order amount. To calculate the total order amount, the program first multiply the quantity and the price to calculate the total amount for each product. Next, it sums up products' total amounts to calculate the total order amount.

int checkout(Connection *conn, struct ShoppingCart cart[], int customerId, int productCount);

This function is called after the function displayProduct().

This function receives an OCCI pointer (a reference variable to an Oracle database), an array of type ShoppingCart, an integer value as the customer ID, and an integer value as the number of ordered items (products).

First, the program displays the following message:

"Would you like to checkout? (Y/y or N/n) "

If the user enters any values except "Y/y" and "N/n", the program displays a proper message and asks the user to enter the value again.

"Wrong input. Try again..."

See the following example:

******************** Main Menu ********************

1) Login

0) Exit

Enter an option (0-1): 1

Enter the customer ID: 4

-------------- Add Products to Cart --------------

Enter the product ID: 112

Product Price: 808.92

Enter the product Quantity: 3

Enter 1 to add more products or 0 to checkout: 0

------- Ordered Products ---------

---Item 1

Product ID: 112

Price: 808.92

Quantity: 3

----------------------------------

Total: 2426.76

Would you like to checkout? (Y/y or N/n) t

Wrong input. Try again...

Would you like to checkout? (Y/y or N/n) 0

Wrong input. Try again...

Would you like to checkout? (Y/y or N/n) 1

Wrong input. Try again...

Would you like to checkout? (Y/y or N/n) y

The order is successfully completed.

******************** Main Menu ********************

1) Login

0) Exit

Enter an option (0-1): 0

Good bye...!

If the user enters "N/n", the function checkout()terminates and returns 0.

If the user enters "Y/y", the Oracle stored procedure add_order() is called. This procedure will add a row in the orders table with a new order ID (See the definition of the add_order() procedure.

This stored procedure returns an order ID, which will be used to store ordered items in the table order_items.

The item_id for the first product in the array is 1, for the second product is 2, and ...

For all products in the array cart (productCount is the number of products stored in the array cart), the program calls the stored procedure add_order_item() and passes the corresponding values to this stored procedure.

Sample execution:

******************** Main Menu ********************

1) Login

0) Exit

Enter an option (0-1): 5

******************** Main Menu ********************

1) Login

0) Exit

You entered a wrong value. Enter an option (0-1): 1

Enter the customer ID: 1000

The customer does not exist.

******************** Main Menu ********************

1) Login

0) Exit

Enter an option (0-1): 44

******************** Main Menu ********************

1) Login

0) Exit

You entered a wrong value. Enter an option (0-1): 1

Enter the customer ID: 44

-------------- Add Products to Cart --------------

Enter the product ID: 112

Product Price: 808.92

Enter the product Quantity: 2

Enter 1 to add more products or 0 to checkout: 1

Enter the product ID: 115

Product Price: 699.99

Enter the product Quantity: 3

Enter 1 to add more products or 0 to checkout: 0

------- Ordered Products ---------

---Item 1

Product ID: 112

Price: 808.92

Quantity: 2

---Item 2

Product ID: 115

Price: 699.99

Quantity: 3

----------------------------------

Total: 3717.81

Would you like to checkout? (Y/y or N/n) y

The order is successfully completed.

******************** Main Menu ********************

1) Login

0) Exit

Enter an option (0-1): 1

Enter the customer ID: 44

-------------- Add Products to Cart --------------

Enter the product ID: 110

Product Price: 3192.97

Enter the product Quantity: 2

Enter 1 to add more products or 0 to checkout: 1

Enter the product ID: 116

Product Price: 731.99

Enter the product Quantity: 1

Enter 1 to add more products or 0 to checkout: 1

Enter the product ID: 117

Product Price: 695.99

Enter the product Quantity: 3

Enter 1 to add more products or 0 to checkout: 0

------- Ordered Products ---------

---Item 1

Product ID: 110

Price: 3192.97

Quantity: 2

---Item 2

Product ID: 116

Price: 731.99

Quantity: 1

---Item 3

Product ID: 117

Price: 695.99

Quantity: 3

----------------------------------

Total: 9205.9

Would you like to checkout? (Y/y or N/n) n

The order is cancelled.

******************** Main Menu ********************

1) Login

0) Exit

Enter an option (0-1): 0

Good bye...!