{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dataiku: first *voilà* web application!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Customer lookup\n",
    "Note: The SQL query might be written differently depending on your SQL Engine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import dataiku\n",
    "from dataiku import SQLExecutor2\n",
    "from dataiku.sql import Constant, toSQL, Dialects\n",
    "\n",
    "DATASET_NAME = \"pro_customers_sql\"\n",
    "\n",
    "def get_customer_info(id):\n",
    "    dataset = dataiku.Dataset(DATASET_NAME)\n",
    "    table_name = dataset.get_location_info().get('info', {}).get('quotedResolvedTableName')\n",
    "    executor = SQLExecutor2(dataset=dataset)\n",
    "    cid = Constant(str(id))\n",
    "    escaped_cid = toSQL(cid, dialect=Dialects.POSTGRES)  # Replace by your DB\n",
    "    query_reader = executor.query_to_iter(\n",
    "        f\"\"\"SELECT \"name\", \"job\", \"company\" FROM \"{table_name}\" WHERE \"id\" = {escaped_cid}\"\"\")\n",
    "    result.value=\"\"\n",
    "    for (name, job, company) in query_reader.iter_tuples():\n",
    "        return f\"\"\"The customer's name is \"{name}\", holding the position \"{job}\" at the company named \"{company}\" \"\"\"\n",
    "    return \"No information can be found\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "31ad484775f74fc7bbeb08978057ac6c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(Label(value='Enter the customer ID'), Text(value='', continuous_update=False, pl…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import ipywidgets as widgets\n",
    "import os\n",
    "\n",
    "\n",
    "label = widgets.Label(value=\"Enter the customer ID\")\n",
    "text = widgets.Text( placeholder=\"fdouetteau\", continuous_update=False)\n",
    "\n",
    "result = widgets.Label(value=\"\")\n",
    "\n",
    "def callback(customerId):\n",
    "    result.value = get_customer_info(customerId.get('new', ''))\n",
    "    \n",
    "text.observe(callback, 'value')\n",
    "\n",
    "display(widgets.VBox([widgets.HBox([label,text]),result]))\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "DSS Codeenv - pyenv-voila",
   "language": "python",
   "name": "py-dku-venv-pyenv-voila"
  },
  "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.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}