SOUL-STATS.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <div class=\"bk-root\">\n",
       "        <a href=\"https://bokeh.pydata.org\" target=\"_blank\" class=\"bk-logo bk-logo-small bk-logo-notebook\"></a>\n",
       "        <span id=\"1215\">Loading BokehJS ...</span>\n",
       "    </div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "\n",
       "(function(root) {\n",
       "  function now() {\n",
       "    return new Date();\n",
       "  }\n",
       "\n",
       "  var force = true;\n",
       "\n",
       "  if (typeof (root._bokeh_onload_callbacks) === \"undefined\" || force === true) {\n",
       "    root._bokeh_onload_callbacks = [];\n",
       "    root._bokeh_is_loading = undefined;\n",
       "  }\n",
       "\n",
       "  var JS_MIME_TYPE = 'application/javascript';\n",
       "  var HTML_MIME_TYPE = 'text/html';\n",
       "  var EXEC_MIME_TYPE = 'application/vnd.bokehjs_exec.v0+json';\n",
       "  var CLASS_NAME = 'output_bokeh rendered_html';\n",
       "\n",
       "  /**\n",
       "   * Render data to the DOM node\n",
       "   */\n",
       "  function render(props, node) {\n",
       "    var script = document.createElement(\"script\");\n",
       "    node.appendChild(script);\n",
       "  }\n",
       "\n",
       "  /**\n",
       "   * Handle when an output is cleared or removed\n",
       "   */\n",
       "  function handleClearOutput(event, handle) {\n",
       "    var cell = handle.cell;\n",
       "\n",
       "    var id = cell.output_area._bokeh_element_id;\n",
       "    var server_id = cell.output_area._bokeh_server_id;\n",
       "    // Clean up Bokeh references\n",
       "    if (id != null && id in Bokeh.index) {\n",
       "      Bokeh.index[id].model.document.clear();\n",
       "      delete Bokeh.index[id];\n",
       "    }\n",
       "\n",
       "    if (server_id !== undefined) {\n",
       "      // Clean up Bokeh references\n",
       "      var cmd = \"from bokeh.io.state import curstate; print(curstate().uuid_to_server['\" + server_id + \"'].get_sessions()[0].document.roots[0]._id)\";\n",
       "      cell.notebook.kernel.execute(cmd, {\n",
       "        iopub: {\n",
       "          output: function(msg) {\n",
       "            var id = msg.content.text.trim();\n",
       "            if (id in Bokeh.index) {\n",
       "              Bokeh.index[id].model.document.clear();\n",
       "              delete Bokeh.index[id];\n",
       "            }\n",
       "          }\n",
       "        }\n",
       "      });\n",
       "      // Destroy server and session\n",
       "      var cmd = \"import bokeh.io.notebook as ion; ion.destroy_server('\" + server_id + \"')\";\n",
       "      cell.notebook.kernel.execute(cmd);\n",
       "    }\n",
       "  }\n",
       "\n",
       "  /**\n",
       "   * Handle when a new output is added\n",
       "   */\n",
       "  function handleAddOutput(event, handle) {\n",
       "    var output_area = handle.output_area;\n",
       "    var output = handle.output;\n",
       "\n",
       "    // limit handleAddOutput to display_data with EXEC_MIME_TYPE content only\n",
       "    if ((output.output_type != \"display_data\") || (!output.data.hasOwnProperty(EXEC_MIME_TYPE))) {\n",
       "      return\n",
       "    }\n",
       "\n",
       "    var toinsert = output_area.element.find(\".\" + CLASS_NAME.split(' ')[0]);\n",
       "\n",
       "    if (output.metadata[EXEC_MIME_TYPE][\"id\"] !== undefined) {\n",
       "      toinsert[toinsert.length - 1].firstChild.textContent = output.data[JS_MIME_TYPE];\n",
       "      // store reference to embed id on output_area\n",
       "      output_area._bokeh_element_id = output.metadata[EXEC_MIME_TYPE][\"id\"];\n",
       "    }\n",
       "    if (output.metadata[EXEC_MIME_TYPE][\"server_id\"] !== undefined) {\n",
       "      var bk_div = document.createElement(\"div\");\n",
       "      bk_div.innerHTML = output.data[HTML_MIME_TYPE];\n",
       "      var script_attrs = bk_div.children[0].attributes;\n",
       "      for (var i = 0; i < script_attrs.length; i++) {\n",
       "        toinsert[toinsert.length - 1].firstChild.setAttribute(script_attrs[i].name, script_attrs[i].value);\n",
       "      }\n",
       "      // store reference to server id on output_area\n",
       "      output_area._bokeh_server_id = output.metadata[EXEC_MIME_TYPE][\"server_id\"];\n",
       "    }\n",
       "  }\n",
       "\n",
       "  function register_renderer(events, OutputArea) {\n",
       "\n",
       "    function append_mime(data, metadata, element) {\n",
       "      // create a DOM node to render to\n",
       "      var toinsert = this.create_output_subarea(\n",
       "        metadata,\n",
       "        CLASS_NAME,\n",
       "        EXEC_MIME_TYPE\n",
       "      );\n",
       "      this.keyboard_manager.register_events(toinsert);\n",
       "      // Render to node\n",
       "      var props = {data: data, metadata: metadata[EXEC_MIME_TYPE]};\n",
       "      render(props, toinsert[toinsert.length - 1]);\n",
       "      element.append(toinsert);\n",
       "      return toinsert\n",
       "    }\n",
       "\n",
       "    /* Handle when an output is cleared or removed */\n",
       "    events.on('clear_output.CodeCell', handleClearOutput);\n",
       "    events.on('delete.Cell', handleClearOutput);\n",
       "\n",
       "    /* Handle when a new output is added */\n",
       "    events.on('output_added.OutputArea', handleAddOutput);\n",
       "\n",
       "    /**\n",
       "     * Register the mime type and append_mime function with output_area\n",
       "     */\n",
       "    OutputArea.prototype.register_mime_type(EXEC_MIME_TYPE, append_mime, {\n",
       "      /* Is output safe? */\n",
       "      safe: true,\n",
       "      /* Index of renderer in `output_area.display_order` */\n",
       "      index: 0\n",
       "    });\n",
       "  }\n",
       "\n",
       "  // register the mime type if in Jupyter Notebook environment and previously unregistered\n",
       "  if (root.Jupyter !== undefined) {\n",
       "    var events = require('base/js/events');\n",
       "    var OutputArea = require('notebook/js/outputarea').OutputArea;\n",
       "\n",
       "    if (OutputArea.prototype.mime_types().indexOf(EXEC_MIME_TYPE) == -1) {\n",
       "      register_renderer(events, OutputArea);\n",
       "    }\n",
       "  }\n",
       "\n",
       "  \n",
       "  if (typeof (root._bokeh_timeout) === \"undefined\" || force === true) {\n",
       "    root._bokeh_timeout = Date.now() + 5000;\n",
       "    root._bokeh_failed_load = false;\n",
       "  }\n",
       "\n",
       "  var NB_LOAD_WARNING = {'data': {'text/html':\n",
       "     \"<div style='background-color: #fdd'>\\n\"+\n",
       "     \"<p>\\n\"+\n",
       "     \"BokehJS does not appear to have successfully loaded. If loading BokehJS from CDN, this \\n\"+\n",
       "     \"may be due to a slow or bad network connection. Possible fixes:\\n\"+\n",
       "     \"</p>\\n\"+\n",
       "     \"<ul>\\n\"+\n",
       "     \"<li>re-rerun `output_notebook()` to attempt to load from CDN again, or</li>\\n\"+\n",
       "     \"<li>use INLINE resources instead, as so:</li>\\n\"+\n",
       "     \"</ul>\\n\"+\n",
       "     \"<code>\\n\"+\n",
       "     \"from bokeh.resources import INLINE\\n\"+\n",
       "     \"output_notebook(resources=INLINE)\\n\"+\n",
       "     \"</code>\\n\"+\n",
       "     \"</div>\"}};\n",
       "\n",
       "  function display_loaded() {\n",
       "    var el = document.getElementById(\"1215\");\n",
       "    if (el != null) {\n",
       "      el.textContent = \"BokehJS is loading...\";\n",
       "    }\n",
       "    if (root.Bokeh !== undefined) {\n",
       "      if (el != null) {\n",
       "        el.textContent = \"BokehJS \" + root.Bokeh.version + \" successfully loaded.\";\n",
       "      }\n",
       "    } else if (Date.now() < root._bokeh_timeout) {\n",
       "      setTimeout(display_loaded, 100)\n",
       "    }\n",
       "  }\n",
       "\n",
       "\n",
       "  function run_callbacks() {\n",
       "    try {\n",
       "      root._bokeh_onload_callbacks.forEach(function(callback) { callback() });\n",
       "    }\n",
       "    finally {\n",
       "      delete root._bokeh_onload_callbacks\n",
       "    }\n",
       "    console.info(\"Bokeh: all callbacks have finished\");\n",
       "  }\n",
       "\n",
       "  function load_libs(js_urls, callback) {\n",
       "    root._bokeh_onload_callbacks.push(callback);\n",
       "    if (root._bokeh_is_loading > 0) {\n",
       "      console.log(\"Bokeh: BokehJS is being loaded, scheduling callback at\", now());\n",
       "      return null;\n",
       "    }\n",
       "    if (js_urls == null || js_urls.length === 0) {\n",
       "      run_callbacks();\n",
       "      return null;\n",
       "    }\n",
       "    console.log(\"Bokeh: BokehJS not loaded, scheduling load and callback at\", now());\n",
       "    root._bokeh_is_loading = js_urls.length;\n",
       "    for (var i = 0; i < js_urls.length; i++) {\n",
       "      var url = js_urls[i];\n",
       "      var s = document.createElement('script');\n",
       "      s.src = url;\n",
       "      s.async = false;\n",
       "      s.onreadystatechange = s.onload = function() {\n",
       "        root._bokeh_is_loading--;\n",
       "        if (root._bokeh_is_loading === 0) {\n",
       "          console.log(\"Bokeh: all BokehJS libraries loaded\");\n",
       "          run_callbacks()\n",
       "        }\n",
       "      };\n",
       "      s.onerror = function() {\n",
       "        console.warn(\"failed to load library \" + url);\n",
       "      };\n",
       "      console.log(\"Bokeh: injecting script tag for BokehJS library: \", url);\n",
       "      document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "    }\n",
       "  };var element = document.getElementById(\"1215\");\n",
       "  if (element == null) {\n",
       "    console.log(\"Bokeh: ERROR: autoload.js configured with elementid '1215' but no matching script tag was found. \")\n",
       "    return false;\n",
       "  }\n",
       "\n",
       "  var js_urls = [\"https://cdn.pydata.org/bokeh/release/bokeh-1.0.2.min.js\", \"https://cdn.pydata.org/bokeh/release/bokeh-widgets-1.0.2.min.js\", \"https://cdn.pydata.org/bokeh/release/bokeh-tables-1.0.2.min.js\", \"https://cdn.pydata.org/bokeh/release/bokeh-gl-1.0.2.min.js\"];\n",
       "\n",
       "  var inline_js = [\n",
       "    function(Bokeh) {\n",
       "      Bokeh.set_log_level(\"info\");\n",
       "    },\n",
       "    \n",
       "    function(Bokeh) {\n",
       "      \n",
       "    },\n",
       "    function(Bokeh) {\n",
       "      console.log(\"Bokeh: injecting CSS: https://cdn.pydata.org/bokeh/release/bokeh-1.0.2.min.css\");\n",
       "      Bokeh.embed.inject_css(\"https://cdn.pydata.org/bokeh/release/bokeh-1.0.2.min.css\");\n",
       "      console.log(\"Bokeh: injecting CSS: https://cdn.pydata.org/bokeh/release/bokeh-widgets-1.0.2.min.css\");\n",
       "      Bokeh.embed.inject_css(\"https://cdn.pydata.org/bokeh/release/bokeh-widgets-1.0.2.min.css\");\n",
       "      console.log(\"Bokeh: injecting CSS: https://cdn.pydata.org/bokeh/release/bokeh-tables-1.0.2.min.css\");\n",
       "      Bokeh.embed.inject_css(\"https://cdn.pydata.org/bokeh/release/bokeh-tables-1.0.2.min.css\");\n",
       "    }\n",
       "  ];\n",
       "\n",
       "  function run_inline_js() {\n",
       "    \n",
       "    if ((root.Bokeh !== undefined) || (force === true)) {\n",
       "      for (var i = 0; i < inline_js.length; i++) {\n",
       "        inline_js[i].call(root, root.Bokeh);\n",
       "      }if (force === true) {\n",
       "        display_loaded();\n",
       "      }} else if (Date.now() < root._bokeh_timeout) {\n",
       "      setTimeout(run_inline_js, 100);\n",
       "    } else if (!root._bokeh_failed_load) {\n",
       "      console.log(\"Bokeh: BokehJS failed to load within specified timeout.\");\n",
       "      root._bokeh_failed_load = true;\n",
       "    } else if (force !== true) {\n",
       "      var cell = $(document.getElementById(\"1215\")).parents('.cell').data().cell;\n",
       "      cell.output_area.append_execute_result(NB_LOAD_WARNING)\n",
       "    }\n",
       "\n",
       "  }\n",
       "\n",
       "  if (root._bokeh_is_loading === 0) {\n",
       "    console.log(\"Bokeh: BokehJS loaded, going straight to plotting\");\n",
       "    run_inline_js();\n",
       "  } else {\n",
       "    load_libs(js_urls, function() {\n",
       "      console.log(\"Bokeh: BokehJS plotting callback run at\", now());\n",
       "      run_inline_js();\n",
       "    });\n",
       "  }\n",
       "}(window));"
      ],
      "application/vnd.bokehjs_load.v0+json": "\n(function(root) {\n  function now() {\n    return new Date();\n  }\n\n  var force = true;\n\n  if (typeof (root._bokeh_onload_callbacks) === \"undefined\" || force === true) {\n    root._bokeh_onload_callbacks = [];\n    root._bokeh_is_loading = undefined;\n  }\n\n  \n\n  \n  if (typeof (root._bokeh_timeout) === \"undefined\" || force === true) {\n    root._bokeh_timeout = Date.now() + 5000;\n    root._bokeh_failed_load = false;\n  }\n\n  var NB_LOAD_WARNING = {'data': {'text/html':\n     \"<div style='background-color: #fdd'>\\n\"+\n     \"<p>\\n\"+\n     \"BokehJS does not appear to have successfully loaded. If loading BokehJS from CDN, this \\n\"+\n     \"may be due to a slow or bad network connection. Possible fixes:\\n\"+\n     \"</p>\\n\"+\n     \"<ul>\\n\"+\n     \"<li>re-rerun `output_notebook()` to attempt to load from CDN again, or</li>\\n\"+\n     \"<li>use INLINE resources instead, as so:</li>\\n\"+\n     \"</ul>\\n\"+\n     \"<code>\\n\"+\n     \"from bokeh.resources import INLINE\\n\"+\n     \"output_notebook(resources=INLINE)\\n\"+\n     \"</code>\\n\"+\n     \"</div>\"}};\n\n  function display_loaded() {\n    var el = document.getElementById(\"1215\");\n    if (el != null) {\n      el.textContent = \"BokehJS is loading...\";\n    }\n    if (root.Bokeh !== undefined) {\n      if (el != null) {\n        el.textContent = \"BokehJS \" + root.Bokeh.version + \" successfully loaded.\";\n      }\n    } else if (Date.now() < root._bokeh_timeout) {\n      setTimeout(display_loaded, 100)\n    }\n  }\n\n\n  function run_callbacks() {\n    try {\n      root._bokeh_onload_callbacks.forEach(function(callback) { callback() });\n    }\n    finally {\n      delete root._bokeh_onload_callbacks\n    }\n    console.info(\"Bokeh: all callbacks have finished\");\n  }\n\n  function load_libs(js_urls, callback) {\n    root._bokeh_onload_callbacks.push(callback);\n    if (root._bokeh_is_loading > 0) {\n      console.log(\"Bokeh: BokehJS is being loaded, scheduling callback at\", now());\n      return null;\n    }\n    if (js_urls == null || js_urls.length === 0) {\n      run_callbacks();\n      return null;\n    }\n    console.log(\"Bokeh: BokehJS not loaded, scheduling load and callback at\", now());\n    root._bokeh_is_loading = js_urls.length;\n    for (var i = 0; i < js_urls.length; i++) {\n      var url = js_urls[i];\n      var s = document.createElement('script');\n      s.src = url;\n      s.async = false;\n      s.onreadystatechange = s.onload = function() {\n        root._bokeh_is_loading--;\n        if (root._bokeh_is_loading === 0) {\n          console.log(\"Bokeh: all BokehJS libraries loaded\");\n          run_callbacks()\n        }\n      };\n      s.onerror = function() {\n        console.warn(\"failed to load library \" + url);\n      };\n      console.log(\"Bokeh: injecting script tag for BokehJS library: \", url);\n      document.getElementsByTagName(\"head\")[0].appendChild(s);\n    }\n  };var element = document.getElementById(\"1215\");\n  if (element == null) {\n    console.log(\"Bokeh: ERROR: autoload.js configured with elementid '1215' but no matching script tag was found. \")\n    return false;\n  }\n\n  var js_urls = [\"https://cdn.pydata.org/bokeh/release/bokeh-1.0.2.min.js\", \"https://cdn.pydata.org/bokeh/release/bokeh-widgets-1.0.2.min.js\", \"https://cdn.pydata.org/bokeh/release/bokeh-tables-1.0.2.min.js\", \"https://cdn.pydata.org/bokeh/release/bokeh-gl-1.0.2.min.js\"];\n\n  var inline_js = [\n    function(Bokeh) {\n      Bokeh.set_log_level(\"info\");\n    },\n    \n    function(Bokeh) {\n      \n    },\n    function(Bokeh) {\n      console.log(\"Bokeh: injecting CSS: https://cdn.pydata.org/bokeh/release/bokeh-1.0.2.min.css\");\n      Bokeh.embed.inject_css(\"https://cdn.pydata.org/bokeh/release/bokeh-1.0.2.min.css\");\n      console.log(\"Bokeh: injecting CSS: https://cdn.pydata.org/bokeh/release/bokeh-widgets-1.0.2.min.css\");\n      Bokeh.embed.inject_css(\"https://cdn.pydata.org/bokeh/release/bokeh-widgets-1.0.2.min.css\");\n      console.log(\"Bokeh: injecting CSS: https://cdn.pydata.org/bokeh/release/bokeh-tables-1.0.2.min.css\");\n      Bokeh.embed.inject_css(\"https://cdn.pydata.org/bokeh/release/bokeh-tables-1.0.2.min.css\");\n    }\n  ];\n\n  function run_inline_js() {\n    \n    if ((root.Bokeh !== undefined) || (force === true)) {\n      for (var i = 0; i < inline_js.length; i++) {\n        inline_js[i].call(root, root.Bokeh);\n      }if (force === true) {\n        display_loaded();\n      }} else if (Date.now() < root._bokeh_timeout) {\n      setTimeout(run_inline_js, 100);\n    } else if (!root._bokeh_failed_load) {\n      console.log(\"Bokeh: BokehJS failed to load within specified timeout.\");\n      root._bokeh_failed_load = true;\n    } else if (force !== true) {\n      var cell = $(document.getElementById(\"1215\")).parents('.cell').data().cell;\n      cell.output_area.append_execute_result(NB_LOAD_WARNING)\n    }\n\n  }\n\n  if (root._bokeh_is_loading === 0) {\n    console.log(\"Bokeh: BokehJS loaded, going straight to plotting\");\n    run_inline_js();\n  } else {\n    load_libs(js_urls, function() {\n      console.log(\"Bokeh: BokehJS plotting callback run at\", now());\n      run_inline_js();\n    });\n  }\n}(window));"
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "2e00ae3831eb4186881a7b3bd65f462a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Password(description='Password:', placeholder='')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0616cbe964d94f23b41e2c3b439932b1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(HBox(children=(Select(options=('WFS_SOUL_SX',), value='WFS_SOUL_SX'),), layout=Layout(height='1…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# %reload_ext autoreload\n",
    "\n",
    "import time\n",
    "import ipywidgets\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "from IPython.display import HTML\n",
    "\n",
    "from IPython.display import clear_output\n",
    "clear_output()\n",
    "\n",
    "from ipywidgets import widgets\n",
    "from ipywidgets import Layout, Button, Checkbox, Label, HBox, VBox\n",
    "from ipywidgets import interact, interactive, fixed, interact_manual\n",
    "\n",
    "from IPython.display import display_pretty, Javascript\n",
    "from IPython.display import display, HTML\n",
    "\n",
    "\n",
    "from bokeh.plotting import figure, output_file, show, helpers\n",
    "from bokeh.models import ColumnDataSource\n",
    "from bokeh.models.tools import HoverTool\n",
    "\n",
    "from bokeh.plotting import figure, output_file, show\n",
    "from bokeh.palettes import Category20\n",
    "from bokeh.io import output_notebook\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "import pymysql as pysql\n",
    "import sys\n",
    "\n",
    "output_notebook()\n",
    "\n",
    "def format_ts( mydate, mytime):\n",
    "    mytime = int(mytime)\n",
    "    if mytime>1:\n",
    "        return \"'%s %02d:59:59'\" % (mydate, mytime-1)\n",
    "    else:\n",
    "        return \"'%s %s:00:00'\" % (mydate, mytime)\n",
    "\n",
    "class flaoStatsDict(dict):\n",
    "    '''Dictionary that returns the key in case a value is missing'''\n",
    "    def __missing__(self, key):\n",
    "        return key\n",
    "\n",
    "class DatabaseObj():\n",
    "    \n",
    "    def __init__(self):\n",
    "        self._table_names = []\n",
    "        self._table_names_inv = []\n",
    "        self.tables_list = []\n",
    "\n",
    "    def connect(self, host, name, user, password):\n",
    "        self.host = host\n",
    "        self.name = name\n",
    "        self.user = user\n",
    "        self.password = password\n",
    "        print('Connecting to database...')\n",
    "        self.db_connection = pysql.connect(host=self.host, database=self.name, user=self.user, password=self.password)\n",
    "        self.cursor = self.db_connection.cursor()\n",
    "        # Table names are in this other database\n",
    "        hk_connection = pysql.connect(host=self.host, database='WFS_SOUL_SX', user=self.user, password=self.password)\n",
    "        hk_cursor = hk_connection.cursor()     # get the cursor\n",
    "        self._table_names = flaoStatsDict()\n",
    "        self._table_names_inv = flaoStatsDict()\n",
    "        \n",
    "        '''\n",
    "        hk_cursor.execute('SELECT * from column_names')\n",
    "        response = hk_cursor.fetchall()\n",
    "        for r in response:\n",
    "            self._table_names[r[1]] = r[2]\n",
    "            self._table_names_inv[r[2]] = r[1]\n",
    "        '''\n",
    "        \n",
    "        hk_cursor.execute(\"SHOW FULL TABLES\")\n",
    "        response = hk_cursor.fetchall()\n",
    "        columns = []\n",
    "        tables = {}\n",
    "        for row in response:\n",
    "            table_name = row[0]\n",
    "            tables[table_name] = []\n",
    "            ss = row[0]\n",
    "            ss += '[ '\n",
    "            hk_cursor.execute(\"SHOW columns FROM %s\" % row[0])\n",
    "            response1 = hk_cursor.fetchall()\n",
    "            for column in response1:\n",
    "                column_name = column[0]\n",
    "                tables[table_name].append(column_name)\n",
    "                ss += column_name + \", \"\n",
    "            ss += \"]\"\n",
    "\n",
    "        self.tables_list = list(tables.keys())\n",
    "        \n",
    "        for tt in self.tables_list:\n",
    "            for cc in tables[tt]:\n",
    "                self._table_names[cc] = cc\n",
    "                self._table_names_inv[cc] = cc\n",
    "        \n",
    "    def table_names(self):\n",
    "        return self._table_names\n",
    "    \n",
    "    def table_names_inv(self):\n",
    "        return self._table_names_inv\n",
    "    \n",
    "    def executeQuery(self, query_string):\n",
    "        self.cursor.execute(query_string)\n",
    "        response = self.cursor.fetchall()\n",
    "        return response\n",
    "\n",
    "    def executeQueryPandas(self, query_string, parse_dates_=True):\n",
    "        df = pd.read_sql(query_string, parse_dates=parse_dates_,  con=self.db_connection)\n",
    "        return df\n",
    "    \n",
    "    def count_table_rows(self, table, start_date, end_date, start_time, end_time ):                \n",
    "        start_date_s = (\"'%s'\" % start_date)\n",
    "        end_date_s = (\"'%s'\" % end_date)\n",
    "        query_string = 'SELECT COUNT(*) FROM %s where date_t BETWEEN %s AND %s' % (table, start_date_s, end_date_s)\n",
    "        df = self.executeQueryPandas( query_string )\n",
    "        return df['COUNT(*)'][0]\n",
    "        '''\n",
    "        start_date_s = format_ts( start_date, start_time)\n",
    "        end_date_s   = format_ts( end_date, end_time)\n",
    "        condition = 'timestamp BETWEEN %s AND %s' % (start_date_s, end_date_s)\n",
    "        df = self.executeQueryPandas( 'SELECT COUNT(*) FROM %s where %s' % (table, condition) )\n",
    "        return df['COUNT(*)'][0]\n",
    "        '''\n",
    "        \n",
    "    def get_min_max_table_dates(self, table_name):\n",
    "        df = self.executeQueryPandas( 'SELECT DATE(MIN(date_t)) AS date1, DATE(MAX(date_t)) AS date2 FROM %s' % (table_name) )\n",
    "        return (df['date1'][0], df['date2'][0])\n",
    "\n",
    "    def loadTablesNamesAndStructure(self):\n",
    "        self.tables = {}\n",
    "        response = self.executeQuery(\"SHOW FULL TABLES\")\n",
    "        columns = []\n",
    "        for row in response:\n",
    "            table_name = row[0]\n",
    "            self.tables[table_name] = []\n",
    "            ss = row[0]\n",
    "            ss += '[ '\n",
    "            response1 = self.executeQuery(\"SHOW columns FROM %s\" % row[0])\n",
    "            for column in response1:\n",
    "                column_name = column[0]\n",
    "                self.tables[table_name].append(column_name)\n",
    "                ss += column_name + \", \"\n",
    "            ss += \"]\"\n",
    "        self.tables_list = list(self.tables.keys())\n",
    "        \n",
    "    def saveDFList(self, df_list):\n",
    "        i=0\n",
    "        for df in df_list:\n",
    "            df.to_csv('data_frame' + str(i) + '.csv')\n",
    "            i += 1\n",
    "\n",
    "class MyLocalGui():\n",
    "\n",
    "    def __init__(self, controller, table_names, table_names_inv):\n",
    "        self.clear_button = None\n",
    "        self.datepicker_e = None\n",
    "        self.datepicker_s = None\n",
    "        self.dates_cont = None\n",
    "        self.global_cont = None\n",
    "        self.db_cont = None\n",
    "        self.items_layout = None\n",
    "        self.one_label_box = None\n",
    "        self.one_plot = None\n",
    "        self.do_plot_button = None\n",
    "        self.time_s = None\n",
    "        self.time_e = None\n",
    "        self.controller = controller\n",
    "        self.table_names = table_names\n",
    "        self.table_names_inv = table_names_inv\n",
    "        self.db_list = widgets.Select( options=['WFS_SOUL_SX'])\n",
    "        self.db_list.on_trait_change(self.on_changed_db, name=\"value\")\n",
    "        self.db_selection_box = HBox([self.db_list], layout=Layout(width='100%', height='100%'))\n",
    "        # display(self.db_selection_box)\n",
    "        self.global_cont = VBox( [],  layout=Layout(display=\"flex-grow\", height='auto') )\n",
    "        self.controller.columns_list = []\n",
    "\n",
    "    def on_clear_button_clicked(self, b):\n",
    "        aa = self.db_cont\n",
    "        self.controller.selected_tables = []\n",
    "        self.columns_from_select_tables = {}\n",
    "        self.controller.columns_list = []\n",
    "        self.setup_db_gui(self.controller.myDB.tables_list)\n",
    "        aa.close()        \n",
    "\n",
    "    def tables_inteaction(self, selected):\n",
    "        self.controller.selected_tables = []\n",
    "        self.columns_from_select_tables = {}\n",
    "        self.controller.columns_list = []\n",
    "        self.datepicker_s.value = pd.to_datetime(self.controller.end_date)\n",
    "        self.datepicker_e.value = pd.to_datetime(self.controller.start_date)\n",
    "        for c in selected:\n",
    "            table_name = self.table_names_inv[c]\n",
    "            self.controller.selected_tables.append(table_name)\n",
    "            if ('date_t' in set(self.controller.myDB.tables[table_name])):\n",
    "                d_min, d_max = self.controller.get_min_max_table_dates(table_name)\n",
    "                if (d_min!=None and d_max!=None):\n",
    "                    d_min = pd.to_datetime(d_min)\n",
    "                    d_max = pd.to_datetime(d_max)\n",
    "                    if (self.datepicker_s.value > d_min):\n",
    "                        self.datepicker_s.value = d_min            \n",
    "                    if (self.datepicker_e.value < d_max):\n",
    "                        self.datepicker_e.value = d_max\n",
    "                else:\n",
    "                    continue\n",
    "            this_table_columns = list(set(self.controller.myDB.tables[table_name]) - set(['id',  'timestamp', 'date_t', 'time_t']))\n",
    "            \n",
    "            self.columns_from_select_tables[table_name] = this_table_columns\n",
    "            self.controller.columns_list = self.controller.columns_list + this_table_columns \n",
    "    \n",
    "        columns_display = sorted(list(map( lambda x: self.table_names[x], self.controller.columns_list )))\n",
    "\n",
    "        self.cb_columns = widgets.SelectMultiple(    options=list(columns_display), \n",
    "                                                     description='Selected Columns', \n",
    "                                                     disabled=False,\n",
    "                                                     rows = len(columns_display),\n",
    "                                                     layout=Layout(display=\"flex\", flex_flow='column',  height='400px') )\n",
    "        self.do_plot_button.layout.visibility = 'visible'\n",
    "        # self.clear_button.layout.visibility = 'visible'\n",
    "        self.date_box.layout.visibility = 'visible'\n",
    "        self.cb_tables_and_columns_box = HBox([self.cb_tables, self.cb_columns], layout=Layout(width='100%', height='100%'))\n",
    "     \n",
    "        self.db_cont.children = [ HBox([self.cb_tables_and_columns_box, self.buttons_box], layout=Layout(width='100%', height='100%') ), self.one_label_box]\n",
    "\n",
    "    def columns_inteaction(self, selected):\n",
    "        pass\n",
    "\n",
    "    def on_changed_db(self):\n",
    "        self.on_clear_button_clicked(True)\n",
    "        self.controller.connectToDB(self.db_list.value)\n",
    "        self.setup_gui(self.controller.myDB.tables_list)\n",
    "        # self.display_gui()\n",
    "    \n",
    "    def on_plot_button_clicked(self, b):\n",
    "        self.controller.selected_columns = {}\n",
    "        for tt in self.controller.selected_tables:\n",
    "            self.controller.selected_columns[tt] = []\n",
    "            for col in  self.cb_columns.value:\n",
    "                dbCol = self.table_names_inv[col]\n",
    "                if dbCol in self.columns_from_select_tables[tt]:\n",
    "                    self.controller.selected_columns[tt].append(dbCol)\n",
    "        selected_table_list= []\n",
    "        df_list = []\n",
    "        start_date = str(self.datepicker_s.value)[0:10]\n",
    "        start_time = self.time_s.value\n",
    "        end_date = str(self.datepicker_e.value)[0:10]\n",
    "        end_time = self.time_e.value\n",
    "        for t in self.controller.selected_columns.keys():\n",
    "            if len(self.controller.selected_columns[t])>0:\n",
    "                t = self.table_names_inv[t]\n",
    "                selected_table_list.append(t)\n",
    "                if self.singleDay.value:\n",
    "                    end_date = start_date\n",
    "                    start_time=0\n",
    "                    end_time=24\n",
    "                start_date_s = format_ts( start_date, start_time)\n",
    "                end_date_s   = format_ts( end_date, end_time)\n",
    "                n_rows = self.controller.myDB.count_table_rows(t, start_date, end_date, start_time, end_time)\n",
    "                ratio = max(n_rows/self.controller.max_records_per_query, 1)\n",
    "                \n",
    "                #start_date_s = (\"'%s'\" % start_date)\n",
    "                #end_date_s = (\"'%s'\" % end_date)            \n",
    "                #n_rows = count_table_rows(t, start_date, end_date)\n",
    "                #ratio = max(n_rows/max_records_per_query, 1)\n",
    "                sample_str = \"id mod %s = 0 AND\" % str(ratio)\n",
    "                query_string = 'SELECT * FROM %s where %s date_t BETWEEN %s AND %s LIMIT %s' % (t, sample_str, start_date_s, end_date_s, str(self.controller.max_records_per_query))\n",
    "\n",
    "                # print(query_string)\n",
    "                #date_condition = '( timestamp BETWEEN %s AND %s )' % (start_date_s, end_date_s)\n",
    "                #condition = 'id mod %s = 0 AND %s' % (str(int(ratio)), date_condition)\n",
    "                #query_string = 'SELECT %s FROM %s where %s LIMIT %s' % (\"*\", t, condition, str(self.controller.max_records_per_query)\n",
    "                #df = self.controller.myDB.executeQueryPandas(query_string)\n",
    "                #df = df[list(set(self.controller.selected_columns[t] + ['timestamp'] ) - set(['id']) ) ]  \n",
    "                \n",
    "                df = self.controller.myDB.executeQueryPandas(query_string)\n",
    "\n",
    "                # print(df)\n",
    "\n",
    "                df = df[list(set(self.controller.selected_columns[t] + ['date_t', 'time_t'] ) - set(['id']) ) ]  \n",
    "\n",
    "                ###\n",
    "                df['Datetime'] = pd.to_datetime(df['date_t'])\n",
    "                df['Datetime'] = df['Datetime'] + df['time_t']\n",
    "                df = df.set_index('Datetime')\n",
    "                df = df.drop(['date_t', 'time_t'], axis=1)\n",
    "                ###\n",
    "                print(len(df))\n",
    "        \n",
    "                if len(df)>0:\n",
    "                    rename_columns = {colName: self.table_names[colName] for colName in map(str, list(df))}\n",
    "                    # df = df.rename(index=str, columns=rename_columns).set_index('timestamp')\n",
    "                    df_list.append(df)\n",
    "        \n",
    "        print(len(df_list))\n",
    "        \n",
    "        if (len(df_list)>0):\n",
    "            self.my_display_chart(df_list)\n",
    "\n",
    "            if self.controller.saveToAscii:\n",
    "                self.controller.myDB.saveDFList(df_list)\n",
    "\n",
    "    def my_display_chart(self, df_list):\n",
    "        ddf = pd.concat(df_list, join='outer', axis = 1)\n",
    "        source_data = ColumnDataSource(ddf)\n",
    "        clist = []\n",
    "        myplot = figure(x_axis_type=\"datetime\", plot_width=800, plot_height=400)        \n",
    "        il = 0\n",
    "        for t in self.controller.selected_columns.keys():\n",
    "            for ccc in self.controller.selected_columns[t]: #source_data.column_names:\n",
    "                if ccc!='Datetime':\n",
    "                    myplot.line(x='Datetime', y=ccc, source=source_data, legend=dict(value=ccc), color=Category20[20][il%20])\n",
    "                    il += 1\n",
    "        #output_file(\"ts.html\")\n",
    "        show(myplot)\n",
    "\n",
    "    def setup_gui(self, tables_list):\n",
    "        self.setup_db_gui(tables_list)\n",
    "        self.global_cont.children =  [ self.db_selection_box , self.db_cont]\n",
    "        \n",
    "    def setup_db_gui(self, tables_list):\n",
    "        self.items_layout = Layout(flex='1 1 auto', width='auto')\n",
    "        HTML('<style> .widget-hbox .widget-label { max-width:1550ex; text-align:left} </style>')\n",
    "        \n",
    "        tables_display = list(map( lambda x: self.table_names[x], tables_list ))\n",
    "        \n",
    "        self.cb_tables = widgets.SelectMultiple( options=tables_display,\n",
    "                                                 description='Database Tables', \n",
    "                                                 disabled=False,\n",
    "                                                 rows = len(tables_display),\n",
    "                                                 layout=Layout(display=\"flex\", flex_flow='column',  min_width='320px', max_width='320px', height='400px') )        \n",
    "        \n",
    "        columns_display = list(map( lambda x: self.table_names[x], self.controller.columns_list ))\n",
    "        \n",
    "        self.cb_columns = widgets.SelectMultiple(    options=columns_display,\n",
    "                                                     description='Selected Columns', \n",
    "                                                     disabled=False,\n",
    "                                                     rows = len(columns_display),\n",
    "                                                     layout=Layout(display=\"flex\", flex_flow='column',  min_width='320px', max_width='320px', height='400px') )    \n",
    "    \n",
    "        self.cb_tables_and_columns_box = HBox( [ interactive(self.tables_inteaction, selected=self.cb_tables),\n",
    "                                                 interactive(self.columns_inteaction, selected=self.cb_columns) ], \n",
    "                                                 layout=Layout(width='100%', height='100%') )\n",
    "        \n",
    "        self.datepicker_s = widgets.DatePicker(description=\"Start Date\")\n",
    "        self.singleDay = Checkbox(value =False, description='Single day')\n",
    "        self.datepicker_e = widgets.DatePicker(description=\"End Date\")\n",
    "        self.time_s = widgets.IntSlider( value=0, min=0, max=24, step=1, description='Start Hour:', disabled=False, continuous_update=False, orientation='horizontal', readout=True, readout_format='d')\n",
    "        self.time_e = widgets.IntSlider( value=24, min=0, max=24, step=1, description='End Hour:', disabled=False, continuous_update=False, orientation='horizontal', readout=True, readout_format='d')\n",
    "\n",
    "        self.one_label = Label('Select Columns and Dates, then press PLOT',\n",
    "                        layout=Layout(width='100%', height='100%'))\n",
    "        self.one_label_box = HBox([self.one_label], layout=Layout(width='100%', height='100%'))\n",
    "\n",
    "        self.date_box = VBox([HBox([self.datepicker_s, self.singleDay]), self.time_s, self.datepicker_e, self.time_e], \n",
    "                             layout=Layout(width='80%', height='100%') )\n",
    "        self.do_plot_button = Button(description='PLOT', layout=Layout(width='50%', height='10%'))\n",
    "        self.do_plot_button.on_click(self.on_plot_button_clicked)\n",
    "        self.clear_button = Button(description='Clear All', layout=Layout(width='50%', height='10%'))\n",
    "        self.clear_button.on_click(self.on_clear_button_clicked)\n",
    "        self.do_plot_button.layout.visibility = 'hidden'\n",
    "        self.clear_button.layout.visibility = 'hidden'\n",
    "        self.date_box.layout.visibility = 'hidden'\n",
    "        self.buttons_box =  VBox([self.do_plot_button, self.clear_button, self.date_box],\n",
    "                                 layout=Layout(width='50%', height='100%') )\n",
    "        self.db_cont = VBox( [HBox([self.cb_tables_and_columns_box, self.buttons_box],\n",
    "                                layout=Layout(width='100%', height='100%') )] )\n",
    "\n",
    "       \n",
    "    def display_gui(self):\n",
    "        display(self.global_cont)\n",
    "        \n",
    "    def setup(self, table_names, table_names_inv, tables_list):\n",
    "        self.table_names = table_names\n",
    "        self.table_names_inv = table_names_inv\n",
    "        self.setup_db_gui(tables_list)\n",
    "        self.global_cont.children =  [ self.db_selection_box , self.db_cont]                \n",
    "\n",
    "class GlobalObj():\n",
    "    \n",
    "    def pwd_changed(self, change):\n",
    "        # change.new contains the new value\n",
    "        self.input_passwd = change.new\n",
    "        print('new value: ' + change.new)\n",
    "        self.connectToDB('WFS_SOUL_SX')\n",
    "    \n",
    "    def __init__(self):\n",
    "        # some global variables\n",
    "        self.saveToAscii = False\n",
    "        self.max_records_per_query = 100000\n",
    "        # DB initizialization\n",
    "        self.password_widget = ipywidgets.Password(description='Password:', placeholder='')        \n",
    "        self.password_widget.observe(self.pwd_changed, 'value')\n",
    "        self.input_passwd = \"\"\n",
    "        self.clear_status()\n",
    "        display(self.password_widget)\n",
    "        self.myDB = DatabaseObj()\n",
    "        self.initGui()\n",
    "    \n",
    "    def connectToDB(self, db_name):\n",
    "        self.clear_status()\n",
    "        try:\n",
    "            self.myDB.connect('192.168.39.57', db_name, self.input_passwd, self.input_passwd)\n",
    "            self.myDB.loadTablesNamesAndStructure()\n",
    "            self.setupGui()\n",
    "            #break\n",
    "        except:\n",
    "            time.sleep(3)\n",
    "            print(\"Wrong Password\", self.input_passwd )            \n",
    "        \n",
    "    def initGui(self):\n",
    "         # GUI inizialization\n",
    "        self.myGUI = MyLocalGui(self, self.myDB.table_names(), self.myDB.table_names_inv())\n",
    "        self.myGUI.setup_gui(self.myDB.tables_list)\n",
    "        self.myGUI.display_gui()\n",
    "        \n",
    "    def setupGui(self):\n",
    "         # GUI inizialization\n",
    "        self.myGUI.setup(self.myDB.table_names(), self.myDB.table_names_inv(), self.myDB.tables_list)    \n",
    "        \n",
    "    def clear_status(self):\n",
    "        self.start_date = '2000-01-01'\n",
    "        self.end_date = '2030-01-01'\n",
    "        self.columns_from_select_tables = {}\n",
    "        self.selected_columns = {}\n",
    "        self.columns_list = ['None']\n",
    "        self.selected_tables = []\n",
    "\n",
    "    def get_min_max_table_dates(self, table_name):\n",
    "        return self.myDB.get_min_max_table_dates(table_name)\n",
    "\n",
    "app = GlobalObj()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Raw Cell Format",
  "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}