{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "786a03bd-f07b-437a-a8e7-88c2dde62229",
   "metadata": {},
   "source": [
    "# Install Car Dealership Stored Procedures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d78802bd-6196-4283-b935-2564ae4596a5",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from data201 import db_connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8cd89459-d8a0-4558-a9d9-682ce6f30b2f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "conn = db_connection(config_file = 'CarDealership.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d74f9899-9f4e-43de-b89f-71fb57c56078",
   "metadata": {},
   "source": [
    "### **1.a** What sales did each salesperson make during a given month?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3fcc4ca-7f55-429a-b59b-8db751593dcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP PROCEDURE IF EXISTS sales_of_month')\n",
    "\n",
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE PROCEDURE sales_of_month(IN month INT)\n",
    "    BEGIN\n",
    "        SELECT sp.name AS spname, \n",
    "               car.company, car.model, \n",
    "               c.name AS cname, c.gender AS cgender, car.price\n",
    "        FROM salesperson sp\n",
    "        JOIN transaction t\n",
    "          ON t.salesperson_id = sp.id\n",
    "        JOIN car\n",
    "          ON car.transaction_id = t.id\n",
    "        JOIN customer c\n",
    "          ON c.id = t.customer_id\n",
    "        WHERE t.month = month\n",
    "        ORDER BY spname, car.company, car.model, cgender;\n",
    "    END\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d815cc93-285a-4473-9d2e-f7b428b8ee87",
   "metadata": {},
   "source": [
    "### **1.b** How many sales did each salesperson make during a given month?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51e4c1e9-9530-4358-bf81-fcc0debb42d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP PROCEDURE IF EXISTS sales_counts_of_month')\n",
    "\n",
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE PROCEDURE sales_counts_of_month(IN month INT)\n",
    "    BEGIN\n",
    "        SELECT sp.name AS spname, COUNT(sp.name) AS cars_sold\n",
    "        FROM salesperson sp\n",
    "        JOIN transaction t\n",
    "          ON t.salesperson_id = sp.id\n",
    "        JOIN car\n",
    "          ON car.transaction_id = t.id\n",
    "        JOIN customer c\n",
    "          ON c.id = t.customer_id\n",
    "        WHERE t.month = month\n",
    "        GROUP BY spname\n",
    "        ORDER BY spname;\n",
    "    END\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22186a7b-0701-4ba5-a480-45465898022f",
   "metadata": {},
   "source": [
    "### **1.c** What is the highest number of sales by a salesperson during a given month?\n",
    "#### Return the number via the `OUT highest INT` parameter, whose value is set by the`SELECT INTO`. Therefore, this procedure does not return any rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aab74dde-f4b4-476f-8507-965380a48783",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP PROCEDURE IF EXISTS highest_sales_of_month')\n",
    "\n",
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE PROCEDURE highest_sales_of_month(IN month INT, \n",
    "                                            OUT highest INT)\n",
    "    BEGIN\n",
    "        SELECT COUNT(sp.name) INTO highest\n",
    "        FROM salesperson sp\n",
    "        JOIN transaction t\n",
    "          ON t.salesperson_id = sp.id\n",
    "        WHERE t.month = month\n",
    "        GROUP BY sp.name\n",
    "        ORDER BY count(sp.name) DESC\n",
    "        LIMIT 1;\n",
    "    END\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e830d195-27cc-47d4-8dd5-83254fa7c65d",
   "metadata": {},
   "source": [
    "### **1.c** Who are the salesperson(s) of a given month (those who make the highest sales)? \n",
    "#### During the all to procedure `highest_sales_of_month`, user-defined variable `@highest_sales` will receive the value of the procedure's `OUT` parameter. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9aad4773-0b42-4e7f-8fcf-2f427f9d5883",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('DROP PROCEDURE IF EXISTS salespersons_of_the_month')\n",
    "\n",
    "cursor.execute( \n",
    "    \"\"\"\n",
    "    CREATE PROCEDURE salespersons_of_the_month(IN month INT)\n",
    "    BEGIN\n",
    "        CALL highest_sales_of_month(month, @highest_sales);\n",
    "    \n",
    "        SELECT sp.name AS spname, COUNT(sp.name) AS cars_sold \n",
    "        FROM salesperson sp\n",
    "        JOIN transaction t\n",
    "          ON t.salesperson_id = sp.id\n",
    "        WHERE t.month = month\n",
    "        GROUP BY spname\n",
    "        HAVING cars_sold = @highest_sales\n",
    "        ORDER BY spname;\n",
    "    END\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6244cc93-0e4c-40f7-9fa5-5da1db6a07e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cd7c703-516d-4010-b257-200fe916e799",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}