UC1PR2101_ThaulowStöcklin_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UC1PR2101 - Train System\n",
    "##### SUBMITTED BY: THOMAS THAULOW STÖCKLIN\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created and/or Opened database successfully\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "import traceback\n",
    "import pandas as pd\n",
    "from datetime import datetime, date, timedelta\n",
    "conn = sqlite3.connect('jprr_simpified.sqlite')\n",
    "cursor = conn.cursor()\n",
    "cursor.execute('PRAGMA foreign_keys = ON;')\n",
    "print(\"Created and/or Opened database successfully\")\n",
    "cursor.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2- DDL and Database Creation #\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1- Create tables for the database ####\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success. All tables are present\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    cursor = conn.cursor()\n",
    "    cursor.executescript('''\n",
    "\n",
    "\n",
    "        /* Create Table for TERMINALS */\n",
    "        CREATE TABLE IF NOT EXISTS terminals (\n",
    "        terminal_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        terminal_name TEXT NOT NULL,\n",
    "        terminal_description TEXT,\n",
    "        terminal_tracklength TEXT NOT NULL,\n",
    "        table_constraints\n",
    "        );\n",
    "        \n",
    "        /* Create Table for TERM_LOCATIONS */\n",
    "        CREATE TABLE IF NOT EXISTS term_locations (\n",
    "        terminal_id INTEGER NOT NULL,\n",
    "        terminal_latitude TEXT NOT NULL,\n",
    "        terminal_longitude TEXT NOT NULL,\n",
    "        table_constraints,\n",
    "        FOREIGN KEY (terminal_id) REFERENCES TERMINALS(terminal_id)\n",
    "        );\n",
    "        \n",
    "        /* Create Table for ORIGIN*/\n",
    "        CREATE TABLE IF NOT EXISTS origin (\n",
    "        origin_Id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        terminal_id INTEGER NOT NULL,\n",
    "        departure_datetime TEXT NOT NULL,\n",
    "        table_constraints,\n",
    "        FOREIGN KEY (terminal_id) REFERENCES TERMINALS(terminal_id)\n",
    "        );\n",
    "\n",
    "        /* Create Table for DESTINATION*/\n",
    "        CREATE TABLE IF NOT EXISTS destination (\n",
    "        destination_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        terminal_id INTEGER NOT NULL,\n",
    "        arrival_datetime TEXT NOT NULL,\n",
    "        table_constraints,\n",
    "        FOREIGN KEY (terminal_id) REFERENCES TERMINALS(terminal_id)\n",
    "        );\n",
    "       \n",
    "        /* Create Table for CUSTOMERS */\n",
    "        CREATE TABLE IF NOT EXISTS customers (\n",
    "        cust_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        cust_name TEXT NOT NULL,\n",
    "        cust_streetaddr TEXT NOT NULL,\n",
    "        cust_city TEXT NOT NULL,\n",
    "        cust_state TEXT NOT NULL,\n",
    "        cust_zip TEXT NOT NULL,\n",
    "        cust_phone TEXT NOT NULL,\n",
    "        table_constraints\n",
    "        );\n",
    "        \n",
    "        /* Create Table for CAR_TYPES */\n",
    "        CREATE TABLE IF NOT EXISTS car_types (\n",
    "        car_type_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        car_type TEXT NOT NULL,\n",
    "        table_constraints\n",
    "        );\n",
    "\n",
    "        /* Create Table for CARS */\n",
    "        CREATE TABLE IF NOT EXISTS cars (\n",
    "        car_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        car_type_id INTEGER NOT NULL,\n",
    "        car_weight INTEGER NOT NULL default 50000,\n",
    "        car_capacity REAL NOT NULL default 50000,\n",
    "        car_current_capacity REAL default 0,\n",
    "        car_length TEXT NOT NULL,\n",
    "        car_builddate TEXT NOT NULL,\n",
    "        table_constraints,\n",
    "        FOREIGN KEY (car_type_id) REFERENCES car_types(car_type_id)\n",
    "        );\n",
    "\n",
    "        /* Create Table for TRAINS */\n",
    "        CREATE TABLE IF NOT EXISTS trains (\n",
    "        train_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        loc_Id INTEGER NOT NULL,\n",
    "        car_id INTEGER NOT NULL,\n",
    "        table_constraints,\n",
    "        FOREIGN KEY (loc_id) REFERENCES locomotives(loc_id),\n",
    "        FOREIGN KEY (car_id) REFERENCES cars(car_id)        \n",
    "        );\n",
    "\n",
    "        /* Create Table for TRAIN_JOURNEY */\n",
    "        CREATE TABLE IF NOT EXISTS train_journey (\n",
    "        journey_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        loc_id INTEGER NOT NULL,\n",
    "        train_id INTEGER NOT NULL,\n",
    "        origin_id INTEGER NOT NULL,\n",
    "        destination_id INTEGER NOT NULL,\n",
    "        table_constraints,\n",
    "        FOREIGN KEY (train_id) REFERENCES trains(train_id),\n",
    "        FOREIGN KEY (loc_id) REFERENCES locomotives(loc_id),\n",
    "        FOREIGN KEY (origin_id) REFERENCES origin(origin_id),\n",
    "        FOREIGN KEY (destination_id) REFERENCES destination(destination_id)\n",
    "        );\n",
    "\n",
    "        /* Create Table for JOURNEY_CUSTOMER */\n",
    "        CREATE TABLE IF NOT EXISTS journey_customer (\n",
    "        journey_cust_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        journey_id INTEGER NOT NULL,\n",
    "        cust_id INTEGER NOT NULL,\n",
    "        table_constraints,\n",
    "        FOREIGN KEY (journey_id) REFERENCES train_journey(journey_id),\n",
    "        FOREIGN KEY (cust_id) REFERENCES customers(cust_id)\n",
    "        );\n",
    "\n",
    "        /* Create Table for SHIPMENTS */\n",
    "        CREATE TABLE IF NOT EXISTS shipments (\n",
    "        shipment_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        shipment_item TEXT NOT NULL,\n",
    "        shipment_description TEXT,\n",
    "        shipment_weight INTEGER NOT NULL,\n",
    "        shipment_volume REAL NOT NULL,\n",
    "        shipment_price REAL NOT NULL,\n",
    "        table_constraints\n",
    "        );\n",
    "        \n",
    "        /* Create Table for SHIPMENT_JOURNEY */\n",
    "        CREATE TABLE IF NOT EXISTS shipment_journey (\n",
    "        shipment_journey_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        shipment_id INTEGER NOT NULL,\n",
    "        train_id INTEGER NOT NULL,\n",
    "        journey_cust_id INTEGER NOT NULL,\n",
    "        table_constraints,\n",
    "        FOREIGN KEY (train_id) REFERENCES trains(train_id),\n",
    "        FOREIGN KEY (shipment_id) REFERENCES shipments(shipment_id),\n",
    "        FOREIGN KEY (journey_cust_id) REFERENCES journey_customer(journey_cust_id)\n",
    "        );\n",
    "\n",
    "        /* Create Table for LOCOMOTIVES */\n",
    "        CREATE TABLE IF NOT EXISTS locomotives (\n",
    "        loc_id INTEGER PRIMARY KEY AUTOINCREMENT,\n",
    "        loc_manufacturer TEXT NOT NULL,\n",
    "        loc_model TEXT NOT NULL,\n",
    "        loc_hp TEXT NOT NULL,\n",
    "        loc_weight INTEGER NOT NULL,\n",
    "        loc_builddate TEXT NOT NULL,\n",
    "        table_constraints\n",
    "        );\n",
    "\n",
    "        ''')\n",
    "    # cursor.close()\n",
    "\n",
    "    print(\"Success. All tables are present\")\n",
    "\n",
    "except Exception: \n",
    "    print(\"Failed to create database. Please contact support\")\n",
    "    traceback.print_exc()     "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2- Confirm table creation ####\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>tbl_name</th>\n",
       "      <th>rootpage</th>\n",
       "      <th>sql</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>table</td>\n",
       "      <td>terminals</td>\n",
       "      <td>terminals</td>\n",
       "      <td>2</td>\n",
       "      <td>CREATE TABLE terminals (\\n        terminal_id ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>table</td>\n",
       "      <td>sqlite_sequence</td>\n",
       "      <td>sqlite_sequence</td>\n",
       "      <td>3</td>\n",
       "      <td>CREATE TABLE sqlite_sequence(name,seq)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>table</td>\n",
       "      <td>term_locations</td>\n",
       "      <td>term_locations</td>\n",
       "      <td>4</td>\n",
       "      <td>CREATE TABLE term_locations (\\n        termina...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>table</td>\n",
       "      <td>origin</td>\n",
       "      <td>origin</td>\n",
       "      <td>5</td>\n",
       "      <td>CREATE TABLE origin (\\n        origin_Id INTEG...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>table</td>\n",
       "      <td>destination</td>\n",
       "      <td>destination</td>\n",
       "      <td>6</td>\n",
       "      <td>CREATE TABLE destination (\\n        destinatio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>table</td>\n",
       "      <td>customers</td>\n",
       "      <td>customers</td>\n",
       "      <td>7</td>\n",
       "      <td>CREATE TABLE customers (\\n        cust_id INTE...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>table</td>\n",
       "      <td>car_types</td>\n",
       "      <td>car_types</td>\n",
       "      <td>8</td>\n",
       "      <td>CREATE TABLE car_types (\\n        car_type_id ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>table</td>\n",
       "      <td>cars</td>\n",
       "      <td>cars</td>\n",
       "      <td>9</td>\n",
       "      <td>CREATE TABLE cars (\\n        car_id INTEGER PR...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>table</td>\n",
       "      <td>trains</td>\n",
       "      <td>trains</td>\n",
       "      <td>10</td>\n",
       "      <td>CREATE TABLE trains (\\n        train_id INTEGE...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>table</td>\n",
       "      <td>train_journey</td>\n",
       "      <td>train_journey</td>\n",
       "      <td>11</td>\n",
       "      <td>CREATE TABLE train_journey (\\n        journey_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>table</td>\n",
       "      <td>journey_customer</td>\n",
       "      <td>journey_customer</td>\n",
       "      <td>12</td>\n",
       "      <td>CREATE TABLE journey_customer (\\n        journ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>table</td>\n",
       "      <td>shipments</td>\n",
       "      <td>shipments</td>\n",
       "      <td>13</td>\n",
       "      <td>CREATE TABLE shipments (\\n        shipment_id ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>table</td>\n",
       "      <td>shipment_journey</td>\n",
       "      <td>shipment_journey</td>\n",
       "      <td>14</td>\n",
       "      <td>CREATE TABLE shipment_journey (\\n        shipm...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>table</td>\n",
       "      <td>locomotives</td>\n",
       "      <td>locomotives</td>\n",
       "      <td>17</td>\n",
       "      <td>CREATE TABLE locomotives (\\n        loc_id INT...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     type              name          tbl_name  rootpage  \\\n",
       "0   table         terminals         terminals         2   \n",
       "1   table   sqlite_sequence   sqlite_sequence         3   \n",
       "2   table    term_locations    term_locations         4   \n",
       "3   table            origin            origin         5   \n",
       "4   table       destination       destination         6   \n",
       "5   table         customers         customers         7   \n",
       "6   table         car_types         car_types         8   \n",
       "7   table              cars              cars         9   \n",
       "8   table            trains            trains        10   \n",
       "9   table     train_journey     train_journey        11   \n",
       "10  table  journey_customer  journey_customer        12   \n",
       "11  table         shipments         shipments        13   \n",
       "12  table  shipment_journey  shipment_journey        14   \n",
       "13  table       locomotives       locomotives        17   \n",
       "\n",
       "                                                  sql  \n",
       "0   CREATE TABLE terminals (\\n        terminal_id ...  \n",
       "1              CREATE TABLE sqlite_sequence(name,seq)  \n",
       "2   CREATE TABLE term_locations (\\n        termina...  \n",
       "3   CREATE TABLE origin (\\n        origin_Id INTEG...  \n",
       "4   CREATE TABLE destination (\\n        destinatio...  \n",
       "5   CREATE TABLE customers (\\n        cust_id INTE...  \n",
       "6   CREATE TABLE car_types (\\n        car_type_id ...  \n",
       "7   CREATE TABLE cars (\\n        car_id INTEGER PR...  \n",
       "8   CREATE TABLE trains (\\n        train_id INTEGE...  \n",
       "9   CREATE TABLE train_journey (\\n        journey_...  \n",
       "10  CREATE TABLE journey_customer (\\n        journ...  \n",
       "11  CREATE TABLE shipments (\\n        shipment_id ...  \n",
       "12  CREATE TABLE shipment_journey (\\n        shipm...  \n",
       "13  CREATE TABLE locomotives (\\n        loc_id INT...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "schema = '''\n",
    "\n",
    "    SELECT * FROM sqlite_master\n",
    "    WHERE type='table' \n",
    "    ORDER BY rootpage;\n",
    "     '''\n",
    "\n",
    "cursor = conn.cursor()\n",
    "cursor.execute( schema )\n",
    "names = [ x[0] for x in cursor.description]\n",
    "rows = cursor.fetchall()\n",
    "main_table = pd.DataFrame( rows, columns=names)\n",
    "main_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3- Find schema data on tables ####"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Select Table: train_journey\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cid</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>notnull</th>\n",
       "      <th>dflt_value</th>\n",
       "      <th>pk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>journey_id</td>\n",
       "      <td>INTEGER</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>loc_id</td>\n",
       "      <td>INTEGER</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>train_id</td>\n",
       "      <td>INTEGER</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>origin_id</td>\n",
       "      <td>TEXT INTEGER</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>destination_id</td>\n",
       "      <td>INTEGER</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>table_constraints</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cid               name          type  notnull dflt_value  pk\n",
       "0    0         journey_id       INTEGER        0       None   1\n",
       "1    1             loc_id       INTEGER        1       None   0\n",
       "2    2           train_id       INTEGER        1       None   0\n",
       "3    3          origin_id  TEXT INTEGER        0       None   0\n",
       "4    4     destination_id       INTEGER        1       None   0\n",
       "5    5  table_constraints                      0       None   0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_input = input(\"Select Table: \")\n",
    "\n",
    "table_data = '''\n",
    "    PRAGMA table_info('{}')   \n",
    "     '''\n",
    "\n",
    "cursor = conn.cursor()\n",
    "cursor.execute( table_data.format(user_input) )\n",
    "names = [ x[0] for x in cursor.description]\n",
    "rows = cursor.fetchall()\n",
    "Temp = pd.DataFrame( rows, columns=names)\n",
    "Temp\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4- Confirm foreign keys ####\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter table: trains\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>seq</th>\n",
       "      <th>table</th>\n",
       "      <th>from</th>\n",
       "      <th>to</th>\n",
       "      <th>on_update</th>\n",
       "      <th>on_delete</th>\n",
       "      <th>match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>cars</td>\n",
       "      <td>car_id</td>\n",
       "      <td>car_id</td>\n",
       "      <td>NO ACTION</td>\n",
       "      <td>NO ACTION</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>locomotives</td>\n",
       "      <td>loc_Id</td>\n",
       "      <td>loc_id</td>\n",
       "      <td>NO ACTION</td>\n",
       "      <td>NO ACTION</td>\n",
       "      <td>NONE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  seq        table    from      to  on_update  on_delete match\n",
       "0   0    0         cars  car_id  car_id  NO ACTION  NO ACTION  NONE\n",
       "1   1    0  locomotives  loc_Id  loc_id  NO ACTION  NO ACTION  NONE"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_input = input(\"Enter table: \")\n",
    "\n",
    "table_data = '''\n",
    "    PRAGMA foreign_key_list('{}')                      \n",
    "     '''\n",
    "\n",
    "cursor = conn.cursor()\n",
    "cursor.execute( table_data.format(user_input) )\n",
    "names = [ x[0] for x in cursor.description]\n",
    "rows = cursor.fetchall()\n",
    "Temp = pd.DataFrame( rows, columns=names)\n",
    "Temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 5- Find Content in tables ####\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Select Table Name: destination\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>destination_id</th>\n",
       "      <th>terminal_id</th>\n",
       "      <th>arrival_datetime</th>\n",
       "      <th>table_constraints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08-12 21:04:20</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2020-08-12 21:04:20</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   destination_id  terminal_id     arrival_datetime table_constraints\n",
       "0               1            2  2020-08-12 21:04:20              None\n",
       "1               2            2  2020-08-12 21:04:20              None"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_input = input(\"Select Table Name: \")\n",
    "\n",
    "table_data = '''\n",
    "    SELECT *          \n",
    "    FROM {}                      \n",
    "     '''\n",
    "\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(table_data.format(user_input))\n",
    "names = [ x[0] for x in cursor.description]\n",
    "rows = cursor.fetchall()\n",
    "Temp = pd.DataFrame( rows, columns=names)\n",
    "Temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3- Stored Procedures #\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### STORED PROCEDURE 1 ####"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_customer(customer):\n",
    "  \"\"\"\n",
    "    To add a new Customer in the DB\n",
    "    @params: Customer: Tuple (cust_name, address, city, state, zip, phone)\n",
    "    @return: last inserted cust_name, if failed return NONE \n",
    "  \"\"\"\n",
    "  try:\n",
    "    cursor = conn.cursor()\n",
    "    SQL = 'INSERT INTO Customers (cust_name, cust_streetaddr, cust_city, cust_state, cust_zip, cust_phone) VALUES (?,?,?,?,?,?)'\n",
    "    cursor.execute(SQL,customer)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "  \n",
    "# print(add_customer((\"Thomas Thaulow\",\"Tjernsrudveien 43A\",\"Stabekk\",\"Viken\",\"1368\",\"40049254\")))\n",
    "\n",
    "\n",
    "def create_locomotives(locomotive):\n",
    "  \"\"\"\n",
    "    To add a new Locomotive in the DB\n",
    "    @params: Locomotive: Tuple (loc_manufacturer, loc_model, loc_hp, loc_weight, loc_builddate)\n",
    "    @return: last inserted loc_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO locomotives (loc_manufacturer, loc_model, loc_hp, loc_weight, loc_builddate) VALUES (?,?,?,?,?)'\n",
    "    cursor.execute(SQL,locomotive)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "\n",
    "# print(create_locomotives((\"Porche\", \"Superspeed\", 2000, 20, datetime.now())))\n",
    "\n",
    "\n",
    "def create_carTypes(car_type):\n",
    "  \"\"\"\n",
    "    To add a new car_type in the DB\n",
    "    @params: car_type: String(car_type)\n",
    "    @return: last inserted loc_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = \"INSERT INTO car_types (car_type) VALUES (?)\"\n",
    "    cursor.execute(SQL,(car_type,))\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(\"Create Car Types: \",e)\n",
    "    return None\n",
    "\n",
    "# print(create_carTypes(\"Flatcar\"))\n",
    "\n",
    "\n",
    "\n",
    "def create_cars(car):\n",
    "  \"\"\"\n",
    "    To add a new Car in the DB\n",
    "    @params: car: Tuple (car_type_id, car_weight, car_capacity, car_length, car_builddate)\n",
    "    @return: last inserted loc_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO cars (car_type_id, car_weight, car_capacity, car_length, car_builddate) VALUES (?,?,?,?,?)'\n",
    "    cursor.execute(SQL,car)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "\n",
    "# print(create_cars((1, 12, 20, 49,\"2020-08-12\")))\n",
    "\n",
    "def create_shipments(shipment):\n",
    "  \"\"\"\n",
    "    To add a new Shipment in the DB\n",
    "    @params: shipment: Tuple (shipment_item, shipment_description, shipment_weight, shipment_volume, shipment_price)\n",
    "    @return: last inserted  shipment_id\n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO shipments ( shipment_item, shipment_description, shipment_weight, shipment_volume, shipment_price) VALUES (?,?,?,?,?)'\n",
    "    cursor.execute(SQL,shipment)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "    \n",
    "# print(create_shipments((\"Gold\", \"Lots of gold\", 200, 40, 2500)))\n",
    "\n",
    "def create_terminal(terminal):\n",
    "  \"\"\"\n",
    "    To add a new terminal in the DB\n",
    "    @params: terminal: Tuple (terminal_name, terminal_description, terminal_tracklength)\n",
    "    @return: last inserted terminal_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO terminals (terminal_name, terminal_description, terminal_tracklength) VALUES (?,?,?)'\n",
    "    cursor.execute(SQL,terminal)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "    \n",
    "# print(create_terminal((\"Kristiansand Terminal\", \"A terminal in Kristiansand city\", 4608)))\n",
    "\n",
    "def create_train(train):\n",
    "  \"\"\"\n",
    "    To add a new train in the DB\n",
    "    @params: train: Tuple (loc_id, car_id)\n",
    "    @return: last inserted train_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO trains (loc_id, car_id) VALUES (?,?)'\n",
    "    cursor.execute(SQL,train)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "    \n",
    "# print(create_train((1,1)))\n",
    "\n",
    "\n",
    "def create_destination(destination):\n",
    "  \"\"\"\n",
    "    To add a new destination in the DB\n",
    "    @params: destination: Tuple (terminal_id, arrival_datetime)\n",
    "    @return: last inserted terminal_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO destination (terminal_id, arrival_datetime) VALUES (?,?)'\n",
    "    cursor.execute(SQL,destination)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "    \n",
    "# print(create_destination((2, \"2020-09-15 12:04:20\")))\n",
    "\n",
    "\n",
    "def create_origin(origin):\n",
    "  \"\"\"\n",
    "    To add a new origin in the DB\n",
    "    @params: origin: Tuple (terminal_id, departure_datetime)\n",
    "    @return: last inserted journey_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO origin (terminal_id, departure_datetime) VALUES (?,?)'\n",
    "    cursor.execute(SQL,origin)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "    \n",
    "# print(create_origin((1, \"2020-08-12 12:16:55\")))\n",
    "\n",
    "\n",
    "def create_train_journey(train_journey):\n",
    "  \"\"\"\n",
    "    To add a new train_journey in the DB\n",
    "    train departure time should have an 8 hours gap from previous trip \n",
    "    @params: train_journey: Tuple (loc_id,train_id, origin_id, destination_id)\n",
    "    @return: last inserted journey_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    origin = \"\"\"\n",
    "      SELECT origin.departure_datetime from origin \n",
    "      where origin.origin_id = ? \n",
    "    \"\"\"\n",
    "    cursor.execute(origin,(train_journey[1],))\n",
    "    conn.commit()\n",
    "    originD = cursor.fetchone()\n",
    "    # print(originD)\n",
    "\n",
    "    destination = \"\"\" select arrival_datetime from destination where destination.destination_id = ? \"\"\"\n",
    "    cursor.execute(destination,(train_journey[2],))\n",
    "    conn.commit()\n",
    "    destinationD = cursor.fetchone()\n",
    "    # print(destinationD)\n",
    "    \n",
    "    if originD == None or destinationD == None:\n",
    "      return None\n",
    "    # print(datetime.strptime(originD[0],\"%Y-%m-%d %H:%M:%S\")+timedelta(hours = 8))\n",
    "    # print(datetime.strptime(originD[0],\"%Y-%m-%d %H:%M:%S\")+timedelta(hours = 8) < datetime.strptime(destinationD[0],\"%Y-%m-%d %H:%M:%S\"))\n",
    "    # print(datetime.strptime(destinationD[0],\"%Y-%m-%d %H:%M:%S\"))\n",
    "    \n",
    "    if datetime.strptime(originD[0],\"%Y-%m-%d %H:%M:%S\")+timedelta(hours = 8) < datetime.strptime(destinationD[0],\"%Y-%m-%d %H:%M:%S\"):\n",
    "      SQL = 'INSERT INTO train_journey (loc_id,train_id, origin_id, destination_id) VALUES (?,?,?,?)'\n",
    "      cursor.execute(SQL,train_journey)\n",
    "      conn.commit()\n",
    "      return cursor.lastrowid\n",
    "    else:\n",
    "      print(\"Train should have atleast 8 hours Gap b/w Arrival and Departure\")\n",
    "      return None\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "    \n",
    "# print(create_train_journey((1, 1, 1)))\n",
    "\n",
    "def create_shipment_journey(shipment_journey):\n",
    "  \"\"\"\n",
    "    To add a new shipment_journey in the DB\n",
    "    @params: shipment_journey: Tuple (journey_cust_id,train_id, shipment_id)\n",
    "    @return: last inserted journey_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO shipment_journey (journey_cust_id, train_id ,shipment_id) VALUES (?,?,?)'\n",
    "    cursor.execute(SQL,shipment_journey)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "    \n",
    "# print(create_shipment_journey((1, 1)))\n",
    "\n",
    "def create_journey_customer(journey_customer):\n",
    "  \"\"\"\n",
    "    To add a new journey_customer in the DB\n",
    "    @params: journey_customer: Tuple (journey_id, cust_id)\n",
    "    @return: last inserted journey_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO journey_customer (journey_id, cust_id) VALUES (?,?)'\n",
    "    cursor.execute(SQL,journey_customer)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "    \n",
    "# print(create_journey_customer((1, 1)))\n",
    "\n",
    "def create_term_location(term_location):\n",
    "  \"\"\"\n",
    "    To add a new term_location in the DB\n",
    "    @params: term_location: Tuple (terminal_id, terminal_latitude, terminal_longitude)\n",
    "    @return: last inserted terminal_id \n",
    "  \"\"\"\n",
    "  try:\n",
    "    SQL = 'INSERT INTO term_locations (terminal_id, terminal_latitude, terminal_longitude) VALUES (?,?,?)'\n",
    "    cursor.execute(SQL,term_location)\n",
    "    conn.commit()\n",
    "    return cursor.lastrowid\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "    \n",
    "# print(create_term_location((1, 1, 1)))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create_locomotives((\"Porche\", \"Superspeed\", 2000, 20, \"2012-05-02\"))\n",
    "create_locomotives((\"BMW\", \"Superslow\", 2010, 30, \"2012-07-02\"))\n",
    "create_carTypes(\"flatcar\")\n",
    "create_cars((1, 50, 50000, 49,\"2020-08-12\"))\n",
    "create_cars((1, 600, 50000, 49,\"2020-09-12\"))\n",
    "create_terminal((\"Stavanger\", \"A terminal in Kristiansand city\", 1212))\n",
    "create_terminal((\"Kristiansand\", \"B terminal in Kristiansand city\", 1212))\n",
    "create_terminal((\"Oslo\", \"C terminal in Kristiansand city\", 1212))\n",
    "create_train((1,1))\n",
    "create_train((1,2))\n",
    "create_origin((1, \"2020-08-12 12:16:55\"))\n",
    "create_destination((2, \"2020-08-12 21:04:20\"))\n",
    "create_train_journey((1,1, 1, 1))\n",
    "\n",
    "\n",
    "#add_customer((\"Thomas Thaulow\",\"Tjernsrudveien 43a\",\"Stabekk\",\"Viken\",\"1368\",\"40049254\"))\n",
    "#create_shipments((\"Gold\", \"Lots of gold\", 200, 40, 2500))\n",
    "#create_shipment_journey((1, 1, 1))\n",
    "\n",
    "#create_journey_customer((1, 1))\n",
    "\n",
    "create_term_location((1, 1, 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Find functions\n",
    "def find_train(source, destination, departure_date_time):\n",
    "  '''\n",
    "    To find a train\n",
    "    @params: source: String, destination: String,  departure: DateTime\n",
    "    @return: List<Trains>\n",
    "  '''\n",
    "  try:\n",
    "    SQL = '''\n",
    "    SELECT t1.terminal_name as origin_tName,t2.terminal_name as dest_tName,origin.departure_datetime,destination.arrival_datetime,cars.*,car_types.car_type,train_journey.journey_id  from train_journey \n",
    "    left join origin on train_journey.origin_id = origin.origin_id \n",
    "    left join terminals as t1 on origin.terminal_id = t1.terminal_id \n",
    "    left join destination on train_journey.destination_id = destination.destination_id \n",
    "    left join terminals as t2 on destination.terminal_id = t2.terminal_id \n",
    "    left join trains on train_journey.train_id = trains.train_id \n",
    "    left join cars on cars.car_id = trains.car_id \n",
    "    left join car_types on cars.car_type_id = car_types.car_type_id \n",
    "    where origin_tName = ? and dest_tName = ? and strftime('%Y-%m-%d %H:%M',departure_datetime) = strftime('%Y-%m-%d %H:%M',?)\n",
    "    '''\n",
    "    # SQL = \"SELECT  *,?,?, strftime('%Y-%m-%d %H:%M',?)  from origin\"\n",
    "\n",
    "    cursor.execute(SQL,(source,destination,departure_date_time))\n",
    "    conn.commit()\n",
    "    rows = cursor.fetchall()\n",
    "    # print(rows)\n",
    "    names = [ x[0] for x in cursor.description]\n",
    "    trains = {}\n",
    "    trainObj = list()\n",
    "    i=0;\n",
    "    # print(names)\n",
    "    # print(rows)\n",
    "    for row in rows:\n",
    "      \n",
    "      origin_date = datetime.strptime(row[2],\"%Y-%m-%d %H:%M:%S\")\n",
    "      # print(datetime.now() - timedelta(days=3))\n",
    "      # origin date should be atleast than 3 days to the requested date\n",
    "      if(datetime.now() - timedelta(days=2) <= origin_date):\n",
    "        trains[names[0]] = row[0]\n",
    "        trains[names[1]] = row[1]\n",
    "        trains[names[2]] = origin_date\n",
    "        trains[names[3]] = datetime.strptime(row[3],\"%Y-%m-%d %H:%M:%S\")\n",
    "        trains[names[4]] = row[4]\n",
    "        trains[names[5]] = row[5]\n",
    "        trains[names[6]] = row[6]\n",
    "        trains[names[7]] = row[7]\n",
    "        trains[names[8]] = row[8]\n",
    "        trains[names[9]] = row[9]\n",
    "\n",
    "        trains[names[10]] = row[10]\n",
    "        trains[names[11]] = row[11]\n",
    "        trains[names[12]] = row[12]\n",
    "        trains[names[13]] = row[13]\n",
    "        trainObj.append(trains)\n",
    "    print(trainObj)\n",
    "    # rows =  [list(i) for i in rows]\n",
    "    \n",
    "    # names.remove(\"table_constraints\")\n",
    "\n",
    "    # a=pd.DataFrame( rows, columns=names)\n",
    "    # print(rows)\n",
    "    return trainObj\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None\n",
    "\n",
    "def getFreightCarId():\n",
    "  \"\"\"\n",
    "  Returns a freight car type Id \n",
    "  @return: car_type_id\n",
    "  \"\"\"\n",
    "  sql = \"select car_type_id from car_types where car_type like '%flatcar%' order by car_type_id DESC limit 1\"\n",
    "  cursor.execute(sql)\n",
    "  conn.commit()\n",
    "  car = cursor.fetchone()\n",
    "  if car and len(car) > 0:\n",
    "    return car[0]\n",
    "  return None\n",
    "\n",
    "\n",
    "def scheduleTrain():\n",
    "  \"\"\"\n",
    "    To Schedule a train\n",
    "    @params: source: String, destination: String,  \n",
    "    @return: List<Train Journey> \n",
    "  \"\"\"\n",
    "  # User's menu and options would be displayed here\n",
    "  trains = find_train(\"Stavanger\", \"Kristiansand\", \"2020-08-12 12:16:55\")\n",
    "\n",
    "  # print(cust_id)\n",
    "  # print(trains[0].get(\"journey_id\"))\n",
    "\n",
    "  if (not trains) or len(trains) == 0:\n",
    "    return None\n",
    "\n",
    "  cust_id = add_customer((\"Thomas Thaulow\",\"Tjernsrudveien 43a\",\"Stabekk\",\"Viken\",\"1368\",\"40049254\"))\n",
    "  # print(trains)\n",
    "  if create_journey_customer((cust_id,trains[0].get(\"journey_id\"))):\n",
    "    return \"Scheduled Successfully!\"\n",
    "\n",
    "  return None\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'origin_tName': 'Stavanger', 'dest_tName': 'Kristiansand', 'departure_datetime': datetime.datetime(2020, 8, 12, 12, 16, 55), 'arrival_datetime': datetime.datetime(2020, 8, 12, 21, 4, 20), 'car_id': 1, 'car_type_id': 1, 'car_weight': 50, 'car_capacity': 50000.0, 'car_current_capacity': 0.0, 'car_length': '49', 'car_builddate': '2020-08-12', 'table_constraints': None, 'car_type': 'flatcar', 'journey_id': 1}]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Scheduled Successfully!'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scheduleTrain()\n",
    "# trains = find_train(\"Stavanger\", \"Kristiansand\", \"2020-08-12 12:16:55\")\n",
    "# trains"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### STORED PROCEDURE 2 ####"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def schedule_shipment(shipment,cust_id):\n",
    "  \"\"\"\n",
    "    To Schedule a shipment\n",
    "    @params: cust_id: integer, shipment: Tuple(cust_id, shipment_item, shipment_description, shipment_weight, shipment_volume, shipment_price),  \n",
    "    @return: List<Shipment Journey>\n",
    "  \"\"\"\n",
    "  try:\n",
    "    freightCarSql = \"\"\" \n",
    "      Select trains.loc_id,trains.train_id,journey_cust_id,cars.* from journey_customer \n",
    "      left join train_journey on train_journey.journey_id = journey_customer.journey_id\n",
    "      left join trains on trains.train_id = train_journey.train_id\n",
    "      left join cars on cars.car_id = trains.car_id\n",
    "      left join car_types on car_types.car_type_id = cars.car_type_id\n",
    "      where car_type like '%flatcar%' and cust_id = ? and cars.car_current_capacity != cars.car_capacity   \n",
    "      order by cars.car_current_capacity ASC\n",
    "    \"\"\"\n",
    "    cursor.execute(freightCarSql,(cust_id,))\n",
    "    conn.commit()\n",
    "    car = cursor.fetchone()\n",
    "    carObj = {}\n",
    "    names = [ x[0] for x in cursor.description]\n",
    "    for i in range(0,len(names)):\n",
    "      carObj[names[i]] = car[i]\n",
    "    print(carObj)\n",
    "\n",
    "    ship_id = create_shipments(shipment)\n",
    "    car_id = carObj.get(\"car_id\")\n",
    "\n",
    "    # Checking out the current capacity of the train\n",
    "    if (carObj.get('car_capacity') >= carObj.get('car_current_capacity')+shipment[3]):\n",
    "      ship_journey = create_shipment_journey((cust_id,carObj.get('train_id'),ship_id))\n",
    "      return ship_journey\n",
    "    else:\n",
    "      car_type_id =  getFreightCarId()\n",
    "      if car_type_id:\n",
    "        car_id = create_cars((car_type_id,50,50000,49,\"2018-02-15\"))\n",
    "        train_id = create_train(train(carObj.get('loc_id',car_id))) \n",
    "        ship_journey = create_shipment_journey((cust_id,train_id,ship_id))\n",
    "        return ship_journey\n",
    "      else:\n",
    "        return None\n",
    "  except Exception as e:\n",
    "    print(e)\n",
    "    return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'loc_Id': 1, 'train_id': 1, 'journey_cust_id': 1, 'car_id': 1, 'car_type_id': 1, 'car_weight': 50, 'car_capacity': 50000.0, 'car_current_capacity': 0.0, 'car_length': '49', 'car_builddate': '2020-08-12', 'table_constraints': None}\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "schedule_shipment((\"Gold\", \"Lots of gold\", 200, 40, 2500),1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4- Queries & Reporting #"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### REPORT 1 #####"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TrainID</th>\n",
       "      <th>ArrivalName</th>\n",
       "      <th>Origin</th>\n",
       "      <th>ArrivalTime</th>\n",
       "      <th>Cars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Kristiansand</td>\n",
       "      <td>Stavanger</td>\n",
       "      <td>2020-08-12 21:04:20</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TrainID   ArrivalName     Origin          ArrivalTime  Cars\n",
       "0        1  Kristiansand  Stavanger  2020-08-12 21:04:20     3"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# EXAMPLE TERMINAL NAME: Kristiansand \n",
    "# EXAMPLE REPORT_DATE 2020-08-12 21:04:20\n",
    "\n",
    "terminal_selection = (\"Kristiansand\")\n",
    "#terminal_selection = input(\"Enter terminal name: \")\n",
    "\n",
    "\n",
    "## Shows report for any chosen day. Can also show daily arrivals by uncommenting \"report_date\"\n",
    "date_today = datetime.now().strftime('%d-%m-%y%y')\n",
    "#report_date = input(\"add your input, DD-MM-YYYY: \")\n",
    "report_date = (\"2020-08-12 21:04:20\")\n",
    "\n",
    "\n",
    "schema = \"\"\"\n",
    "    SELECT TR.train_id as TrainID, t2.terminal_name as ArrivalName, t1.terminal_name AS Origin, D.arrival_datetime AS ArrivalTime, COUNT(TR.car_id) AS Cars\n",
    "    FROM trains TR,origin O, destination D, train_journey TJ, terminals T\n",
    "    LEFT JOIN terminals as t2 on D.terminal_id = t2.terminal_id \n",
    "    LEFT JOIN terminals as t1 on O.terminal_id = t1.terminal_id \n",
    "    WHERE TR.train_id = TJ.train_id\n",
    "    AND t2.terminal_name = ?\n",
    "    AND D.arrival_datetime = ?\n",
    "    GROUP BY TR.train_id\n",
    "    \"\"\"\n",
    "\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(schema,(terminal_selection,report_date))\n",
    "names = [ x[0] for x in cursor.description]\n",
    "rows = cursor.fetchall()\n",
    "main_table = pd.DataFrame( rows, columns=names)\n",
    "main_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### REPORT 2 ####"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter start date: 2020-08-12 12:16:55\n",
      "Enter end date: 2020-08-12 12:16:55\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Phone</th>\n",
       "      <th>Weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Thomas Thaulow</td>\n",
       "      <td>40049254</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Name     Phone  Weight\n",
       "0  Thomas Thaulow  40049254     200"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# EXAMPLE DEPARTURE TIME: 2020-08-12 12:16:55\n",
    "# EXAMPLE ARRIVAL TIME:  2020-08-12 21:04:20\n",
    "\n",
    "start_date = input(\"Enter start date: \")\n",
    "end_date = input(\"Enter end date: \")\n",
    "\n",
    "schema = \"\"\"\n",
    "    SELECT customers.cust_name as Name, customers.cust_phone as Phone, sum(shipments.shipment_weight) AS Weight\n",
    "    FROM customers\n",
    "    LEFT JOIN journey_customer on  customers.cust_id = journey_customer.journey_cust_id \n",
    "    LEFT JOIN train_journey on train_journey.journey_id = journey_customer.journey_id\n",
    "    LEFT JOIN origin on train_journey.origin_id = origin.origin_id\n",
    "    LEFT JOIN destination on train_journey.destination_id = destination.destination_id\n",
    "    Left JOIN shipment_journey on  journey_customer.journey_cust_id = shipment_journey.journey_cust_id\n",
    "    LEFT JOIN shipments on  shipment_journey.shipment_id = shipments.shipment_id \n",
    "    WHERE origin.departure_datetime\n",
    "    BETWEEN ? AND ?\n",
    "    OR destination.arrival_datetime\n",
    "    BETWEEN ? AND ?\n",
    "    GROUP BY customers.cust_id\n",
    "    ORDER BY shipments.shipment_weight DESC\n",
    "    \"\"\"\n",
    "\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(schema,(start_date, end_date, start_date, end_date))\n",
    "names = [ x[0] for x in cursor.description]\n",
    "rows = cursor.fetchall()\n",
    "main_table = pd.DataFrame( rows, columns=names)\n",
    "main_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}