{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "from sqlalchemy import create_engine, Column, String, Boolean, Integer, Float, MetaData, Table, type_coerce, ForeignKey, case, update\n",
    "from sqlalchemy.orm import mapper, create_session, relationship, aliased, Session\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "import csv\n",
    "from sqlalchemy import case\n",
    "import numpy as np\n",
    "from sqlalchemy.ext.automap import automap_base\n",
    "import matplotlib.pyplot as plt\n",
    "import sqlalchemy.types as types\n",
    "import numbers\n",
    "from sqlalchemy.sql import and_, or_, not_, func\n",
    "from sqlalchemy.sql import select\n",
    "import os\n",
    "import glob\n",
    "import re\n",
    "from os.path import isfile\n",
    "import gsw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "basedir='/ocean/shared/SalishSeaCastData/DFO/CTD/'\n",
    "dbname='DFO_CTD.sqlite'\n",
    "datelims=()\n",
    "engine = create_engine('sqlite:///' + basedir + dbname, echo = False)\n",
    "Base = automap_base()\n",
    "# reflect the tables in salish.sqlite:\n",
    "Base.prepare(engine, reflect=True)\n",
    "# mapped classes have been created\n",
    "# existing tables:\n",
    "StationTBL=Base.classes.StationTBL\n",
    "ObsTBL=Base.classes.ObsTBL\n",
    "CalcsTBL=Base.classes.CalcsTBL\n",
    "session = create_session(bind = engine, autocommit = False, autoflush = True)\n",
    "SA=case([(CalcsTBL.Salinity_T0_C0_SA!=None, CalcsTBL.Salinity_T0_C0_SA)], else_=\n",
    "         case([(CalcsTBL.Salinity_T1_C1_SA!=None, CalcsTBL.Salinity_T1_C1_SA)], else_=\n",
    "         case([(CalcsTBL.Salinity_SA!=None, CalcsTBL.Salinity_SA)], else_= None)))\n",
    "CT=case([(CalcsTBL.Temperature_Primary_CT!=None, CalcsTBL.Temperature_Primary_CT)], else_=\n",
    "         case([(CalcsTBL.Temperature_Secondary_CT!=None, CalcsTBL.Temperature_Secondary_CT)], else_=CalcsTBL.Temperature_CT))\n",
    "ZD=case([(ObsTBL.Depth!=None,ObsTBL.Depth)], else_= CalcsTBL.Z)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "qry=session.query(StationTBL.sourceFile,ObsTBL.sourceFile).\\\n",
    "        select_from(StationTBL).join(ObsTBL,ObsTBL.StationTBLID==StationTBL.ID).\\\n",
    "        join(CalcsTBL,CalcsTBL.ObsTBLID==ObsTBL.ID).filter(and_(StationTBL.Lat>47-3/2.5*(StationTBL.Lon+123.5),\n",
    "                                                            StationTBL.Lat<47-3/2.5*(StationTBL.Lon+121),\n",
    "                                                            StationTBL.Include==True,ObsTBL.Include==True,CalcsTBL.Include==True,\n",
    "                                                               StationTBL.StartYear==2015,StationTBL.StartMonth==2,CT>11))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('req20181116/EO UBC November 16, 2018 (2015 data)/2015-29-0002.ctd', 'req20181116/EO UBC November 16, 2018 (2015 data)/2015-29-0002.ctd')\n"
     ]
    }
   ],
   "source": [
    "for row in qry.distinct():\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "add to processDFO_CTD.py in case zip is re-extracted and file reverts to original:\n",
    "fnameErr='req20181116/EO UBC November 16, 2018 (2015 data)/2015-29-0002.ctd'\n",
    "if session.query(StationTBL).filter(and_(StationTBL.sourceFile==fnameErr,StationTBL.StartMonth==2)).count():\n",
    "    session0.query(StationTBL0).filter(StationTBL0.sourceFile==fnameErr).update({\"StartMonth\":8,\"StartDay\":2})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1897\n",
      "2.0\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fnameErr='req20181116/EO UBC November 16, 2018 (2015 data)/2015-29-0002.ctd'\n",
    "for row in session.query(StationTBL.ID,StationTBL.StartMonth).filter(StationTBL.sourceFile==fnameErr).one():\n",
    "    print(row)\n",
    "session.query(StationTBL).filter(StationTBL.sourceFile==fnameErr).update({\"StartMonth\":8,\"StartDay\":2})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.close()\n",
    "engine.dispose()"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}