EcommerceApp.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 576,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import Statements\n",
    "import mysql.connector\n",
    "from mysql.connector import errorcode\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 577,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Try to make connection to DB\n",
    "try: \n",
    "    OBP = mysql.connector.connect(\n",
    "      host=\"localhost\",\n",
    "      user=\"root\",\n",
    "      password=\"GenjiJonas45!!MB\", #Mark's = DogFishWhale112358.\n",
    "      database = \"off_the_beaten_path\")\n",
    "except mysql.connector.Error as err:\n",
    "    print('Cannot connect to database: ', err)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 578,
   "metadata": {},
   "outputs": [],
   "source": [
    "#List all of Inventory Table\n",
    "def list_Invetory():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "    queryInventory = ('''\n",
    "             Select * FROM Inventory\n",
    "             ''')\n",
    "    \n",
    "    OBP_cursor.execute(queryInventory)\n",
    "    print()\n",
    "    print('ProductID, OnHandQantity, OnOrderQuantity')\n",
    "    for row in OBP_cursor.fetchall():\n",
    "        print(row)\n",
    "\n",
    "    OBP_cursor.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 579,
   "metadata": {},
   "outputs": [],
   "source": [
    "#List all of Products Table\n",
    "def list_Products():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "    queryProducts = ('''\n",
    "             Select * FROM Products\n",
    "             ''')\n",
    "    \n",
    "    OBP_cursor.execute(queryProducts)\n",
    "    print()\n",
    "    print('ProductID, Category, Brand, ProductName, Price, Color')\n",
    "    for row in OBP_cursor.fetchall():\n",
    "        print(row)\n",
    "\n",
    "    OBP_cursor.close()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 580,
   "metadata": {},
   "outputs": [],
   "source": [
    "#List all of Users Table\n",
    "def list_Users():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "    queryUsers = ('''\n",
    "             Select * FROM Users\n",
    "             ''')\n",
    "    \n",
    "    OBP_cursor.execute(queryUsers)\n",
    "    print()\n",
    "    print('UserID, LastName, FirstName, StreetAddress, City, State, ZipCode, PhoneNumber, EMAIL')\n",
    "    for row in OBP_cursor.fetchall():\n",
    "        print(row)\n",
    "\n",
    "    OBP_cursor.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 581,
   "metadata": {},
   "outputs": [],
   "source": [
    "#List all of OrderItems Table\n",
    "def list_OrderItems():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "    queryOrderItems = ('''\n",
    "             Select * FROM OrderItems\n",
    "             ''')\n",
    "    \n",
    "    OBP_cursor.execute(queryOrderItems)\n",
    "    print()\n",
    "    print('OrderItemsID, ProductID, Quantity')\n",
    "    for row in OBP_cursor.fetchall():\n",
    "        print(row)\n",
    "\n",
    "    OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 582,
   "metadata": {},
   "outputs": [],
   "source": [
    "#List all of CustOrders Table\n",
    "def list_CustOrders():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "    queryCustOrders = ('''\n",
    "             Select * FROM CustOrders\n",
    "             ''')\n",
    "    \n",
    "    OBP_cursor.execute(queryCustOrders)\n",
    "    print()\n",
    "    print('OrderID, OrderItemsID, UserID, TransactionAmount, PaymentType, OrderDate, FullFillmentStatus')\n",
    "    for row in OBP_cursor.fetchall():\n",
    "        print(row)\n",
    "\n",
    "    OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 583,
   "metadata": {},
   "outputs": [],
   "source": [
    "#List all of Reviews Table\n",
    "def list_Reviews():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "    queryReviews = ('''\n",
    "             Select * FROM Reviews\n",
    "             ''')\n",
    "    \n",
    "    OBP_cursor.execute(queryReviews)\n",
    "    print()\n",
    "    print('ReviewID, ProductID, UserID, Stars, Review')\n",
    "    for row in OBP_cursor.fetchall():\n",
    "        print(row)\n",
    "\n",
    "    OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 584,
   "metadata": {},
   "outputs": [],
   "source": [
    "#List all of Shipment Table\n",
    "def list_Shipment():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "    queryShipment = ('''\n",
    "             Select * FROM Shipment\n",
    "             ''')\n",
    "    \n",
    "    OBP_cursor.execute(queryShipment)\n",
    "    print()\n",
    "    print('ShipmentID, CustomerOrdersID, UserID, TrackingNumber, ShipmentStatus')\n",
    "    for row in OBP_cursor.fetchall():\n",
    "        print(row)\n",
    "\n",
    "    OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 585,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Table Print SubMenu\n",
    "def solo_tableQuery():\n",
    "    \n",
    "    while True: \n",
    "        try:\n",
    "            print(\"::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\")\n",
    "            print('Tables Sub-menu')\n",
    "            print(\"::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\")\n",
    "            print('1 - Products table')\n",
    "            print('2 - Inventory table')\n",
    "            print('3 - Customer Orders table')\n",
    "            print('4 - Order Items table')\n",
    "            print('5 - Users table')\n",
    "            print('6 - Reviews table')\n",
    "            print('7 - Shipments table')\n",
    "            print('8 - Main Menu')\n",
    "            choice = int(input(\"Enter your choice: \"))\n",
    "            if choice in range(1, 9):  # Check for valid range (1 to 8 inclusive)\n",
    "                #choosing which table\n",
    "                if choice == 1:\n",
    "                    list_Products()\n",
    "                elif choice == 2:\n",
    "                    list_Invetory()\n",
    "                elif choice == 3:\n",
    "                    list_CustOrders()\n",
    "                elif choice == 4:\n",
    "                    list_OrderItems()\n",
    "                elif choice == 5:\n",
    "                    list_Users()\n",
    "                elif choice == 6:\n",
    "                    list_Reviews()\n",
    "                elif choice == 7:\n",
    "                    list_Shipment()\n",
    "                elif choice == 8:\n",
    "                    print(\"\\nBack to Main menu   \", end='')\n",
    "                    time.sleep(1)\n",
    "                    break  # Exit to the main menu\n",
    "            else:\n",
    "                print(\"Invalid choice. Please enter a number between 1 and 8.\")\n",
    "        except ValueError as ve:\n",
    "            print(f\"Invalid input. Please enter numerical values. Error: {ve}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 586,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1. List which products we currently have in inventory\n",
    "def list_Products_in_Invetory():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "    try:\n",
    "        queryProductsInventory = ('''\n",
    "            SELECT \n",
    "                p.ProductID,\n",
    "                p.Category,\n",
    "                p.Brand,\n",
    "                p.ProductName,\n",
    "                p.Price,\n",
    "                p.Color ,\n",
    "                i.OnHandQty,\n",
    "                i.OnOrderQty\n",
    "            FROM \n",
    "                Products p\n",
    "            LEFT JOIN \n",
    "                Inventory i ON p.ProductID = i.ProductID\n",
    "            WHERE \n",
    "                i.OnHandQty >= 0 OR i.OnHandQty IS NULL;\n",
    "                ''')\n",
    "        \n",
    "        OBP_cursor.execute(queryProductsInventory)\n",
    "        print()\n",
    "        print('ProductID, Category, Brand, ProductName, Price, Color, QuantityOnHand, QuantityOnOrder)')\n",
    "        for row in OBP_cursor.fetchall():\n",
    "            print(row)\n",
    "    except mysql.connector.Error as err:\n",
    "        print(f\"Error: {err}\")\n",
    "        OBP.rollback()\n",
    "    finally:\n",
    "        OBP_cursor.close()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 587,
   "metadata": {},
   "outputs": [],
   "source": [
    "#2. Create new products\n",
    "def create_Product():\n",
    "   OBP_cursor = OBP.cursor()\n",
    "\n",
    "   print('To enter a new product you first need to provide the product specifications.')\n",
    "   time.sleep(1.5)\n",
    "\n",
    "   # questions to take in New product's specs\n",
    "   allowed_categories = {'Apparel', 'Camp & Hike', 'Climb', 'Run', 'Snow', 'Travel'}\n",
    "   \n",
    "   while True:\n",
    "        print('Category of new product? ')\n",
    "        category = input('Apparel, Camp & Hike, Climb, Run, Snow, Travel:   ')\n",
    "        if category in allowed_categories:\n",
    "            break\n",
    "        else:\n",
    "            print(f\"Invalid category. Please choose from: {allowed_categories}\")\n",
    "            category = input('Apparel, Camp & Hike, Climb, Run, Snow, Travel:   ')\n",
    "  \n",
    "   print('Brand name of new product? ')\n",
    "   brand = input(\"Brand:   \")\n",
    "   print('Name of new product? ')\n",
    "   productName = input('Product Name:   ')\n",
    "   print('Price of new product? ')\n",
    "   price = float(input(\"Price:   \"))\n",
    "   print('Color of new product? ')\n",
    "   color = input(\"Color:   \")\n",
    "\n",
    "   product_query = ('''\n",
    "             INSERT INTO Products (Category, Brand, ProductName, Price, Color)\n",
    "                VALUES (%s,%s,%s,%s,%s);\n",
    "             ''')\n",
    "   product_queryData = (category,brand,productName,price,color)\n",
    "   \n",
    "   try:\n",
    "        OBP_cursor.execute(product_query, product_queryData)\n",
    "        OBP.commit()\n",
    "        print()\n",
    "        print(\"Product added successfully!\")\n",
    "   except mysql.connector.Error as err:\n",
    "        print(f\"Error: {err}\")\n",
    "        OBP.rollback()\n",
    "   finally:\n",
    "        OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 588,
   "metadata": {},
   "outputs": [],
   "source": [
    "#3. Modify the amount of a particular product that we have in inventory\n",
    "def update_Inventory_quantity():\n",
    "  OBP_cursor = OBP.cursor()\n",
    "\n",
    "  #Choose to update either OnHand or OnOrder quantities\n",
    "  print('Enter 1 to update \"On-Hand Quantity\".')\n",
    "  print('Enter 2 to update \"On-Order Quantity\".')\n",
    "  print('Enter your choice: ',end='')\n",
    "  choice = int(input())\n",
    "  print(choice)\n",
    "\n",
    "  #precondition on choice\n",
    "  while (choice != 1 and choice != 2):\n",
    "    print(\"Invalid choice. Choose the number 1 or 2.\")\n",
    "    choice = int(input())\n",
    "\n",
    "  if (choice == 1): #Changes On-Hand Quantity\n",
    "    try:\n",
    "      print('What is the productID?')\n",
    "      productID = int(input(\"Product ID as an integer:   \"))\n",
    "      print('What is the new quantity?')\n",
    "      newQuantity = int(input(\"Quantity as a integer:   \"))\n",
    "      Onhand_Quantity_update = ('''\n",
    "                UPDATE Inventory\n",
    "                  SET OnHandQty = %s\n",
    "                  WHERE ProductID = %s;\n",
    "                  ''')\n",
    "      Inventory_updateData = (newQuantity, productID)\n",
    "      OBP_cursor.execute(Onhand_Quantity_update, Inventory_updateData)\n",
    "      OBP.commit()\n",
    "    except ValueError as ve:\n",
    "      print(f\"Invalid input. Please enter numerical values. Error: {ve}\")\n",
    "    except mysql.connector.Error as err:\n",
    "      print(f\"Error: {err}\")\n",
    "      OBP.rollback()\n",
    "    finally:\n",
    "      OBP_cursor.close()\n",
    "\n",
    "  elif (choice == 2): #Changes On-Order Quantity\n",
    "    try:\n",
    "      print('What is the productID?')\n",
    "      productID = int(input(\"Product ID as an integer:   \"))\n",
    "      print('What is the new quantity?')\n",
    "      newQuantity = int(input(\"Quantity as a integer:   \"))\n",
    "      Onorder_Quantity_update = ('''\n",
    "                UPDATE Inventory\n",
    "                  SET OnOrderQty = %s\n",
    "                  WHERE ProductID = %s;\n",
    "                  ''')\n",
    "      Inventory_updateData = (newQuantity, productID)\n",
    "      OBP_cursor.execute(Onorder_Quantity_update, Inventory_updateData)\n",
    "      OBP.commit()\n",
    "      print()\n",
    "      print(\"Update confirmed.\")\n",
    "      \n",
    "    except ValueError as ve:\n",
    "      print(f\"Invalid input. Please enter numerical values. Error: {ve}\")\n",
    "    except mysql.connector.Error as err:\n",
    "      print(f\"Error: {err}\")\n",
    "      OBP.rollback()\n",
    "    finally:\n",
    "      OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 589,
   "metadata": {},
   "outputs": [],
   "source": [
    "#4. Delete a product from inventory\n",
    "def delete_from_Inventory():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "\n",
    "    print('To delete a product from Inventory, product information is needed.')\n",
    "    time.sleep(1.5)\n",
    "    try:\n",
    "        \n",
    "        while True:\n",
    "          print('What is the product ID?')\n",
    "          user_input = input(\"Please enter a number: \")\n",
    "          try:\n",
    "            # Try to convert the input to a int\n",
    "            prodID = int(user_input)\n",
    "            break  # If successful, exit loop\n",
    "          except ValueError:\n",
    "            # If ValueError, not a valid number\n",
    "            print(\"That's not a valid number. Please try again.\")\n",
    "\n",
    "        InventoryDelete = ('''\n",
    "                DELETE Inventory\n",
    "                    FROM Inventory\n",
    "                    JOIN Products ON Inventory.ProductID = Products.ProductID\n",
    "                    WHERE Products.ProductID = %s;\n",
    "                ''')\n",
    "        \n",
    "        InventoryDeleteData = (prodID,)\n",
    "        OBP_cursor.execute(InventoryDelete, InventoryDeleteData)\n",
    "        OBP.commit()\n",
    "        print()\n",
    "        print('Deletion confirmed.')\n",
    "\n",
    "    except ValueError as ve:\n",
    "      print(f\"Invalid input. Please enter numerical values. Error: {ve}\")\n",
    "    except mysql.connector.Error as err:\n",
    "      print(f\"Error: {err}\")\n",
    "      OBP.rollback()\n",
    "    finally:\n",
    "        OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 590,
   "metadata": {},
   "outputs": [],
   "source": [
    "#5. Get a list of the most popular products for a given time range\n",
    "\n",
    "def list_PopularProducts():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "\n",
    "    try:\n",
    "        print('What timeframe would you like to see the most popular items from?')\n",
    "        time.sleep(1.0)\n",
    "        print('Starting date? (YYYY-MM-DD)') # i.e., 2022-06-01\n",
    "        startDate = input('Starting date? (YYYY-MM-DD):   ')\n",
    "        print('Ending date? (YYYY-MM-DD)') # i.e., 2023-12-31\n",
    "        endDate = input('Ending date? (YYYY-MM-DD):   ')\n",
    "\n",
    "        queryPopProducts = ('''\n",
    "            SELECT \n",
    "                p.ProductID,\n",
    "                p.ProductName,\n",
    "                p.Brand,\n",
    "                p.Category,\n",
    "                COUNT(oi.OrderItemsID) AS TotalOrders,\n",
    "                SUM(oi.Quantity) AS TotalQuantitySold,\n",
    "                AVG(CASE\n",
    "                        WHEN r.Stars = '1_STAR' THEN 1\n",
    "                        WHEN r.Stars = '2_STAR' THEN 2\n",
    "                        WHEN r.Stars = '3_STAR' THEN 3\n",
    "                        WHEN r.Stars = '4_STAR' THEN 4\n",
    "                    END) AS AvgRating,\n",
    "                SUM(oi.Quantity) + (1.2 * AVG(CASE\n",
    "                        WHEN r.Stars = '1_STAR' THEN 1\n",
    "                        WHEN r.Stars = '2_STAR' THEN 2\n",
    "                        WHEN r.Stars = '3_STAR' THEN 3\n",
    "                        WHEN r.Stars = '4_STAR' THEN 4\n",
    "                    END)) AS PopularityScore\n",
    "            FROM CustOrders co\n",
    "            JOIN OrderItems oi ON co.OrderItemsID = oi.OrderItemsID\n",
    "            JOIN Products p ON oi.ProductID = p.ProductID\n",
    "            LEFT JOIN Reviews r ON p.ProductID = r.ProductID\n",
    "            WHERE co.OrderDate BETWEEN %s AND %s\n",
    "            GROUP BY \n",
    "                p.ProductID, p.ProductName, p.Brand, p.Category\n",
    "            ORDER BY \n",
    "                PopularityScore DESC;\n",
    "        ''')\n",
    "        popProductsData = (startDate, endDate)\n",
    "        OBP_cursor.execute(queryPopProducts, popProductsData)\n",
    "        print()\n",
    "        print('(ProductID, ProductName            , Brand          , Category    , TotalOrders , TotalQuantitySold, AvgRating, PopularityScore)')\n",
    "        for row in OBP_cursor.fetchall():\n",
    "            print(f'({row[0]:<9}, {row[1]:<23}, {row[2]:<15}, {row[3]:<12}, {row[4]:<12}, {row[5]:<17}, {row[6]:<9.2f}, {row[7]:<15.2f})')\n",
    "    except mysql.connector.Error as err:\n",
    "        print(f\"Error: {err}\")\n",
    "        OBP.rollback()\n",
    "    finally:\n",
    "        OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 591,
   "metadata": {},
   "outputs": [],
   "source": [
    "#6. Get a list of the least popular products for a given time range\n",
    "\n",
    "def list_UnPopularProducts():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "\n",
    "    try:\n",
    "        print('What timeframe would you like to see the least popular items from?')\n",
    "        time.sleep(1.0)\n",
    "        print('Starting date? (YYYY-MM-DD)') # i.e., 2023-01-01\n",
    "        startDate = input('Starting date? (YYYY-MM-DD):   ')\n",
    "        print('Ending date? (YYYY-MM-DD)') # i.e., 2023-12-31\n",
    "        endDate = input('Ending date? (YYYY-MM-DD):   ')\n",
    "\n",
    "        queryUnPopProducts = ('''\n",
    "            SELECT \n",
    "                p.ProductID,\n",
    "                p.ProductName,\n",
    "                p.Brand,\n",
    "                p.Category,\n",
    "                COUNT(oi.OrderItemsID) AS TotalOrders,\n",
    "                SUM(oi.Quantity) AS TotalQuantitySold,\n",
    "                AVG(CASE\n",
    "                        WHEN r.Stars = '1_STAR' THEN 1\n",
    "                        WHEN r.Stars = '2_STAR' THEN 2\n",
    "                        WHEN r.Stars = '3_STAR' THEN 3\n",
    "                        WHEN r.Stars = '4_STAR' THEN 4\n",
    "                    END) AS AvgRating,\n",
    "                SUM(oi.Quantity) + (1.2 * AVG(CASE\n",
    "                        WHEN r.Stars = '1_STAR' THEN 1\n",
    "                        WHEN r.Stars = '2_STAR' THEN 2\n",
    "                        WHEN r.Stars = '3_STAR' THEN 3\n",
    "                        WHEN r.Stars = '4_STAR' THEN 4\n",
    "                    END)) AS PopularityScore\n",
    "            FROM CustOrders co\n",
    "            JOIN OrderItems oi ON co.OrderItemsID = oi.OrderItemsID\n",
    "            JOIN Products p ON oi.ProductID = p.ProductID\n",
    "            LEFT JOIN Reviews r ON p.ProductID = r.ProductID\n",
    "            WHERE co.OrderDate BETWEEN %s AND %s\n",
    "            GROUP BY \n",
    "                p.ProductID, p.ProductName, p.Brand, p.Category\n",
    "            ORDER BY \n",
    "                PopularityScore ASC;  -- Order by PopularityScore in ascending order for least popular\n",
    "        ''')\n",
    "        unPopProductsData = (startDate, endDate)\n",
    "        OBP_cursor.execute(queryUnPopProducts, unPopProductsData)\n",
    "        print()\n",
    "        print('(ProductID, ProductName            , Brand          , Category    , TotalOrders , TotalQuantitySold, AvgRating, PopularityScore)')\n",
    "        for row in OBP_cursor.fetchall():\n",
    "            print(f'({row[0]:<9}, {row[1]:<23}, {row[2]:<15}, {row[3]:<12}, {row[4]:<12}, {row[5]:<17}, {row[6]:<9.2f}, {row[7]:<15.2f})')\n",
    "    except mysql.connector.Error as err:\n",
    "        print(f\"Error: {err}\")\n",
    "        OBP.rollback()\n",
    "    finally:\n",
    "        OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 592,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 7. Get a list of users who haven't purchased something in a few months to send promotional emails to\n",
    "#   This should also include products that these users normally purchase\n",
    "\n",
    "def list_ForPromotionalEmails():\n",
    "    if OBP and OBP.is_connected():\n",
    "        OBP_cursor = OBP.cursor()\n",
    "        \n",
    "    try:\n",
    "        PromotionalEmailList = ('''\n",
    "                    SELECT \n",
    "                        u.UserID,\n",
    "                        u.FirstName,\n",
    "                        u.LastName,\n",
    "                        u.EMAIL,\n",
    "                        p.ProductID,\n",
    "                        p.ProductName,\n",
    "                        p.Brand,\n",
    "                        p.Category,\n",
    "                        user_purchases.OrderDate AS LastOrderDate\n",
    "                    FROM Users u\n",
    "                    JOIN (\n",
    "                        SELECT DISTINCT UserID\n",
    "                        FROM CustOrders\n",
    "                        WHERE OrderDate < DATE_SUB(NOW(), INTERVAL 5 MONTH)\n",
    "                            ) inactive_users ON u.UserID = inactive_users.UserID\n",
    "                    LEFT JOIN (\n",
    "                        SELECT co.UserID, oi.ProductID, co.OrderDate\n",
    "                        FROM CustOrders co\n",
    "                        JOIN OrderItems oi ON co.OrderItemsID = oi.OrderItemsID\n",
    "                            ) user_purchases ON u.UserID = user_purchases.UserID\n",
    "                        JOIN Products p ON user_purchases.ProductID = p.ProductID\n",
    "                    ORDER BY u.UserID, p.ProductID;\n",
    "                ''')\n",
    "        \n",
    "        OBP_cursor.execute(PromotionalEmailList,)\n",
    "        print()\n",
    "        print('(UserID, FirstName , LastName       , Email                         , ProductID, ProductName         , Brand          , Category   , LastOrderDate)')        \n",
    "        for row in OBP_cursor.fetchall():\n",
    "            print(f'({row[0]:<6}, {row[1]:<10}, {row[2]:<15}, {row[3]:<30}, {row[4]:<9}, {row[5]:<20}, {row[6]:<15}, {row[7]:<11}, {row[8]})')\n",
    "    except mysql.connector.Error as err:\n",
    "        print(f\"Error: {err}\")\n",
    "        OBP.rollback()\n",
    "    finally:\n",
    "        OBP_cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 593,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stored Procedure 1, Search products by category\n",
    "def search_By_Category():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "\n",
    "    try:\n",
    "        print('Which category: Apparel, Camp & Hike, Climb, Run, Snow, Travel.')\n",
    "        category_name = input(\"Type the category name:   \")\n",
    "        OBP_cursor.callproc('search_products_by_category', [category_name])\n",
    "\n",
    "        # Fetch the results\n",
    "        print()\n",
    "        for result in OBP_cursor.stored_results():\n",
    "            all_products = result.fetchall()\n",
    "            for row in all_products:\n",
    "                print(row)\n",
    "                \n",
    "    except mysql.connector.Error as err:\n",
    "        print(f\"Error: {err}\")\n",
    "        OBP.rollback()\n",
    "    finally:\n",
    "        OBP_cursor.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 594,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stored Procedure 2, Get product by ID.\n",
    "def get_Product_ByID():\n",
    "    OBP_cursor = OBP.cursor()\n",
    "\n",
    "    try:\n",
    "        print(\"What is the ID number?\")\n",
    "        id = input(\"Enter the ID number:   \")\n",
    "        OBP_cursor.callproc('get_product_by_id', [id])\n",
    "\n",
    "        # Fetch the results from the stored procedure\n",
    "        print()\n",
    "        print('ProductID, Category, Brand, ProductName, Price, Color)')\n",
    "        for result in OBP_cursor.stored_results():\n",
    "            all_products = result.fetchall()\n",
    "            for row in all_products:\n",
    "                print(row)\n",
    "    except mysql.connector.Error as err:\n",
    "        print(f\"Error: {err}\")\n",
    "        OBP.rollback()\n",
    "    finally:\n",
    "        OBP_cursor.close()\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 595,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stored procedures submenu\n",
    "def stored_Procedures():\n",
    "\n",
    "    while True: \n",
    "        try:\n",
    "            print()\n",
    "            print(\"::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\")\n",
    "            print('Stored Procedures Submenu.')\n",
    "            print(\"::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\")\n",
    "            print('1 - Search by category and return a list.')\n",
    "            print('2 - Get Product info by ID.')\n",
    "            print('3 - Main Menu.')\n",
    "            choice = int(input(\"Enter your choice: \"))\n",
    "            if choice in range(1, 4):  # Check for valid range (1 to 3 inclusive)\n",
    "                #choosing which table\n",
    "                if choice == 1:\n",
    "                    search_By_Category()\n",
    "                elif choice == 2:\n",
    "                    get_Product_ByID()\n",
    "                elif choice == 3:\n",
    "                    print(\"\\nBack to Main menu \", end='')\n",
    "                    time.sleep(0.5)\n",
    "                    print(\". \", end='')\n",
    "                    time.sleep(1)\n",
    "                    print(\". \", end='')\n",
    "                    time.sleep(1)\n",
    "                    print(\". \", end='')\n",
    "                    time.sleep(1)\n",
    "                    \n",
    "\n",
    "                    break  # Exit the loop after a valid choice is made\n",
    "            else:\n",
    "                print(\"Invalid choice. Please enter a number between 1 and 3.\")\n",
    "        except ValueError as ve:\n",
    "            print(f\"Invalid input. Please enter numerical values. Error: {ve}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 596,
   "metadata": {},
   "outputs": [],
   "source": [
    "# User Interface Menu\n",
    "def menu_UI():\n",
    "    print()\n",
    "    print(\"::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\")\n",
    "    print('Welcome to Off the Beaten Path outdoors equiptment database')\n",
    "    print('Please select an operation you would like to execute.')\n",
    "    print(\"::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\")\n",
    "    print('1 - Provide a list of the current products in inventory.')\n",
    "    print('2 - Create a new product.')\n",
    "    print('3 - Modify the quantity of a current product in inventory.')\n",
    "    print('4 - Delete a product from inventory.')\n",
    "    print('5 - Provide a list of the most popular products for a given time range.')\n",
    "    print('6 - Provide a list of the least popular products for a given time range.')\n",
    "    print('7 - Promotional email list of customers.')\n",
    "    print('8 - Display one of the tables.')\n",
    "    print('9 - Stored Procedures.')\n",
    "    print('0 - Exit menu.')\n",
    "    print(\"Enter your choice: \", end='')\n",
    "    time.sleep(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 597,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Choice Manager, links choice to functions\n",
    "def handle_choice(choice):\n",
    "    if choice == 1:\n",
    "        list_Products_in_Invetory()\n",
    "    elif choice == 2:\n",
    "        create_Product()\n",
    "    elif choice == 3:\n",
    "        update_Inventory_quantity()\n",
    "    elif choice == 4:\n",
    "        delete_from_Inventory()\n",
    "    elif choice == 5:\n",
    "        list_PopularProducts()\n",
    "    elif choice == 6:\n",
    "        list_UnPopularProducts()\n",
    "    elif choice == 7:\n",
    "        list_ForPromotionalEmails()\n",
    "    elif choice == 8:\n",
    "        solo_tableQuery()\n",
    "    elif choice == 9:\n",
    "        stored_Procedures()\n",
    "    elif choice == 0:\n",
    "        print(\"\\nExiting Program \", end='')\n",
    "        time.sleep(0.5)\n",
    "        print(\". \", end='')\n",
    "        time.sleep(1)\n",
    "        print(\". \", end='')\n",
    "        time.sleep(1)\n",
    "        print(\". \", end='')\n",
    "        time.sleep(1)\n",
    "        print(\"done.\")\n",
    "    else:\n",
    "        print(\"Invalid choice. Please enter a number between 0 and 9.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 598,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Primary User Interface\n",
    "def primary():\n",
    "    menu_UI()\n",
    "\n",
    "    while True:  # Infinite loop for continuous menu usage\n",
    "        try:\n",
    "            choice = int(input(\"Enter your choice: \"))\n",
    "            print(choice)\n",
    "            if choice in range(1, 10):  # Check for valid range (0 to 9 inclusive)\n",
    "                print()\n",
    "                handle_choice(choice)\n",
    "                print()\n",
    "                print(\"- - - press Enter to continue - - -\")\n",
    "                input('- - - press Enter to continue - - -')\n",
    "                menu_UI()\n",
    "            elif choice == 0:\n",
    "                handle_choice(choice)\n",
    "                break\n",
    "            else:\n",
    "                print(\"Invalid choice. Please enter a number between 0 and 9.\")\n",
    "        except ValueError as ve:\n",
    "            print(f\"Invalid input. Please enter numerical values. Error: {ve}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 599,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "Welcome to Off the Beaten Path outdoors equiptment database\n",
      "Please select an operation you would like to execute.\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "1 - Provide a list of the current products in inventory.\n",
      "2 - Create a new product.\n",
      "3 - Modify the quantity of a current product in inventory.\n",
      "4 - Delete a product from inventory.\n",
      "5 - Provide a list of the most popular products for a given time range.\n",
      "6 - Provide a list of the least popular products for a given time range.\n",
      "7 - Promotional email list of customers.\n",
      "8 - Display one of the tables.\n",
      "9 - Stored Procedures.\n",
      "0 - Exit menu.\n",
      "Enter your choice: 9\n",
      "\n",
      "\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "Stored Procedures Submenu\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "1 - Search by category and return a list.\n",
      "2 - Get Product info by ID.\n",
      "3 - Main Menu.\n",
      "Which category: Apparel, Camp & Hike, Climb, Run, Snow, Travel.\n",
      "\n",
      "(5, 'Snow', 'Frozen Paths', 'Insulated Snow Boots', 129.99, 'Brown')\n",
      "(10, 'Snow', 'Frozen Paths', 'Unisex Ski Goggles', 29.99, 'Black')\n",
      "\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "Stored Procedures Submenu\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "1 - Search by category and return a list.\n",
      "2 - Get Product info by ID.\n",
      "3 - Main Menu.\n",
      "What is the ID number?\n",
      "\n",
      "ProductID, Category, Brand, ProductName, Price, Color)\n",
      "(3, 'Climb', 'Mountain Grip', 'Pro Climbing Shoes', 89.99, 'Red')\n",
      "\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "Stored Procedures Submenu\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "1 - Search by category and return a list.\n",
      "2 - Get Product info by ID.\n",
      "3 - Main Menu.\n",
      "\n",
      "Back to Main menu . . . \n",
      "- - - press Enter to continue - - -\n",
      "\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "Welcome to Off the Beaten Path outdoors equiptment database\n",
      "Please select an operation you would like to execute.\n",
      "::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::\n",
      "1 - Provide a list of the current products in inventory.\n",
      "2 - Create a new product.\n",
      "3 - Modify the quantity of a current product in inventory.\n",
      "4 - Delete a product from inventory.\n",
      "5 - Provide a list of the most popular products for a given time range.\n",
      "6 - Provide a list of the least popular products for a given time range.\n",
      "7 - Promotional email list of customers.\n",
      "8 - Display one of the tables.\n",
      "9 - Stored Procedures.\n",
      "0 - Exit menu.\n",
      "Enter your choice: 0\n",
      "\n",
      "Exiting Program . . . done.\n"
     ]
    }
   ],
   "source": [
    "# Run program\n",
    "primary()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}