{
 "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 data201 import db_connection, df_query\n",
    "from pandas import DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32e96d54-3da1-45c3-9790-5b55c8752c6f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "conn = db_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 = cursor.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": "ba3dbeb5-380e-45ed-8ea2-e7104cfa928b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# @highest_sales is a user-defined variable on the database server\n",
    "# which will receive the value of the stored procedure's OUT parameter.\n",
    "# Therefore, we need to use a SELECT to extract its value after\n",
    "# calling the stored procedure.\n",
    "\n",
    "cursor.execute(\"CALL highest_sales_of_month(9, @highest_sales)\")\n",
    "df_query(conn, 'SELECT @highest_sales AS \"Highest sales of the month\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d46c16e-d714-4f64-89d1-3604d2d3b248",
   "metadata": {},
   "outputs": [],
   "source": [
    "# A slightly more convoluted solution:\n",
    "#\n",
    "# To use cursor.callproc to call the stored procedure,\n",
    "# put the arguments into a list. The list should contain\n",
    "# None placeholders for the OUT parameters. Upon return,\n",
    "# the result is a copy of the argument list with the \n",
    "# placeholders replaced by the OUT values.\n",
    "#\n",
    "# One extra line of code creates the dataframe.\n",
    "\n",
    "month = 9\n",
    "args = (month, None)\n",
    "print(f\"{args = }\")\n",
    "\n",
    "sql = f\"cursor.callproc('highest_sales_of_month', args)\"\n",
    "print(sql)\n",
    "\n",
    "result = cursor.callproc('highest_sales_of_month', args)\n",
    "                        \n",
    "print(f\"{result = }\")\n",
    "print()\n",
    "\n",
    "DataFrame( { \"Highest sales of the month\": [ result[1] ] } )"
   ]
  },
  {
   "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()"
   ]
  }
 ],
 "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
}