{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "37395fc7-7f0a-4ef9-80bd-420900e2154c",
   "metadata": {},
   "source": [
    "# Invoke Car Dealership Stored Procedures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e00faceb-fdae-451a-8016-22cedde52e45",
   "metadata": {},
   "outputs": [],
   "source": [
    "from DATA225utils import make_connection, dataframe_query\n",
    "from pandas import DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32e96d54-3da1-45c3-9790-5b55c8752c6f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "conn = make_connection(config_file = 'CarDealership.ini')\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6a5f84b-cc2a-4b37-85fe-398cdd096e66",
   "metadata": {},
   "source": [
    "#### A Python function that displays in a dataframe the results from a call to a stored procedure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e930a82a-fec1-4c91-becb-4432ff5c5b5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def display_results(cursor):\n",
    "    \"\"\"\n",
    "    If there are results in the cursor from a call to a\n",
    "    stored procedure, display the results in a dataframe. \n",
    "    \"\"\"\n",
    "    columns = result.description\n",
    "    \n",
    "    if columns == None:\n",
    "        return \n",
    "    \n",
    "    else:\n",
    "        column_names = [column_info[0] \n",
    "                        for column_info in columns]\n",
    "\n",
    "        rows = cursor.fetchall()\n",
    "    \n",
    "        if len(rows) > 0:\n",
    "            df = DataFrame(rows)\n",
    "            df.columns = column_names\n",
    "            \n",
    "            display(df)\n",
    "        \n",
    "        else:\n",
    "            return"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85c5391d-d3c2-4487-a52b-71316d275d21",
   "metadata": {
    "tags": []
   },
   "source": [
    "## **1.a** `sales_of_month()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab6a4082-848f-4bc8-83e3-47de4ce3daaf",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"CALL sales_of_month(9)\"\n",
    "print(sql)\n",
    "print()\n",
    "\n",
    "generator = cursor.execute(sql, multi=True)\n",
    "\n",
    "for result in generator:\n",
    "    display_results(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acba41f3-2b72-486d-9648-a901c6fbfd55",
   "metadata": {
    "tags": []
   },
   "source": [
    "## **1.b** `sales_counts_of_month()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4be8d5b-ce30-4fa0-a278-68359f04dff4",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"CALL sales_counts_of_month(9)\"\n",
    "print(sql)\n",
    "print()\n",
    "\n",
    "generator = cursor.execute(sql, multi=True)\n",
    "\n",
    "for result in generator:\n",
    "    display_results(result)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "836ee418-3ad5-4806-9458-c6f409b8d88c",
   "metadata": {
    "tags": []
   },
   "source": [
    "# **1.c** `highest_sales_of_month()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23cf626a-eebc-48ee-8681-9fa008e02c9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# @highest_sales is a user-defined variable.\n",
    "sql = \"CALL highest_sales_of_month(9, @highest_sales)\"\n",
    "print(sql)\n",
    "print()\n",
    "\n",
    "generator = cursor.execute(sql, multi=True)\n",
    "\n",
    "for result in generator:\n",
    "    for row in result.fetchall():\n",
    "        pass\n",
    "\n",
    "sql = 'SELECT @highest_sales AS highest_sales_of_the_month'\n",
    "_, df = dataframe_query(conn, sql)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7375984c-0a4f-41d7-a3a6-fc5506a496a4",
   "metadata": {
    "tags": []
   },
   "source": [
    "## **1.d** `salespersons_of_the_month()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d3247fd-0d8f-4dd8-8b5b-1f69fad7a31c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"CALL salespersons_of_the_month(9)\"\n",
    "print(sql)\n",
    "print()\n",
    "\n",
    "generator = cursor.execute(sql, multi=True)\n",
    "\n",
    "for result in generator:\n",
    "    display_results(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eed65000-c1a0-4cbd-8f5a-3ea6f829ae6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5904fbce-8544-4cac-9b1d-1c98d75484af",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}