{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d24e2ed0",
   "metadata": {},
   "source": [
    "# Daily Digest by Instrument and Level\n",
    "\n",
    "This script should tell me more details about the files recently delivered.\n",
    "\n",
    "I want to set up a nested loop that goes through each instrument and processing level and returns just the number of those files that have been ingested since default: yesterday, but can be whatever, e.g., last time I know they sent anything."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f0a12706",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Since 2023-10-02T00:00:00: \n",
      "EPD L0 463\n",
      "EPD L1 989\n",
      "EPD L2 986\n",
      "SOLOHI L2 32210\n",
      "STIX L0 320\n",
      "STIX L1 320\n"
     ]
    }
   ],
   "source": [
    "from astroquery.utils.tap.core import TapPlus\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "def execute_query(query, src_str):\n",
    "    try:\n",
    "        SOAR = TapPlus(url=src_str)\n",
    "        results = SOAR.launch_job(query)\n",
    "        table = results.get_results()\n",
    "        return table\n",
    "    except Exception as e:\n",
    "        print(f\"Error executing query: {e}\")\n",
    "        return None\n",
    "\n",
    "src_str = 'http://soar.esac.esa.int/soar-sl-tap/tap'\n",
    "instru_list = ['EPD', 'EUI', 'MAG', 'METIS', 'PHI', 'RPW', 'SOLOHI', 'SPICE', 'STIX', 'SWA']\n",
    "level_list = ['L0', 'L1', 'L2', 'L3']\n",
    "\n",
    "# Calculate yesterday's date\n",
    "days_to_subtract = 1\n",
    "dt_yesterday = datetime.today() - timedelta(days=days_to_subtract)\n",
    "iso_yesterday = dt_yesterday.strftime('%Y-%m-%dT') + '00:00:00'\n",
    "\n",
    "# Or put in a custom date and time\n",
    "iso_yesterday = '2023-10-02T00:00:00'\n",
    "print(f\"Since {iso_yesterday}: \")\n",
    "\n",
    "for instru in instru_list:\n",
    "    for level in level_list:\n",
    "        table = 'v_sc_data_item' if 'LL' not in level else 'v_ll_data_item'\n",
    "        \n",
    "        ADQL = (\n",
    "            f\"SELECT COUNT(data_item_id) \"\n",
    "            f\"FROM {table} \"\n",
    "            f\"WHERE instrument='{instru}' \"\n",
    "            f\"AND level='{level}' \"\n",
    "            f\"AND insertion_time>'{iso_yesterday}' \"\n",
    "        )\n",
    "\n",
    "        result_table = execute_query(ADQL, src_str)\n",
    "        if result_table and result_table['COUNT'][0] != 0:\n",
    "            print(instru, level, result_table['COUNT'][0])\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}