90_gendoc_collection.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Look-up table from FAA DRS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "\n",
    "metadata_mapping = pd.read_excel(os.path.join(os.getcwd(),'data','DRS','index','FAA DRS','DRS Document Types Metadata Mapping.xlsx')) # from https://drs.faa.gov/help/helpdetails and https://drs.faa.gov/help/helpdetails\n",
    "\n",
    "doc_types = pd.unique(metadata_mapping.loc[:, \"Document Type Name in API request\"])\n",
    "doc_types = [val for val in doc_types if isinstance(val, str)]\n",
    "\n",
    "common_fields = []\n",
    "for n,doc_type in enumerate(doc_types):\n",
    "    metadata_names = metadata_mapping.loc[metadata_mapping.loc[:, \"Document Type Name in API request\"]==doc_type, \"Metadata Name in API Response \"]\n",
    "\n",
    "    if n>0:\n",
    "        common_fields = list(set(metadata_names) & set(common_fields))\n",
    "    else:\n",
    "        common_fields = list(set(metadata_names))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "cwd = os.getcwd()\n",
    "\n",
    "for doc_type in doc_types:\n",
    "    if not os.path.exists(os.path.join(cwd, 'data', 'DRS', 'raw data', doc_type)):\n",
    "        os.mkdir(os.path.join(cwd, 'data', 'DRS', 'raw data', doc_type))\n",
    "        os.mkdir(os.path.join(cwd, 'data', 'DRS', 'raw data', doc_type, 'pdf'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Collect indexed documents from DRS\n",
    "See below to collec the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "doc_types = [val for val in doc_types if val not in ['PMA','TSOI']]\n",
    "\n",
    "#doc_types = ['MMEL']\n",
    "# doc_types = ['TSO']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import logging\n",
    "import glob\n",
    "import pandas as pd\n",
    "import random\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.firefox.options import Options\n",
    "from selenium.webdriver.common.by import By\n",
    "from selenium.webdriver.support.wait import WebDriverWait\n",
    "from selenium.common.exceptions import TimeoutException\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "import time\n",
    "\n",
    "logging.basicConfig(filename='90_gendoc_collection.log', encoding='utf-8',format='%(asctime)s %(message)s', level=logging.INFO)\n",
    "\n",
    "tempo = 0.5\n",
    "firefox_download_folder = r\"C:\\Users\\victor\\Downloads\"\n",
    "random.shuffle(doc_types)\n",
    "\n",
    "options = Options()\n",
    "options.set_preference(\"browser.download.panel.shown\", False)\n",
    "options.set_preference(\"browser.download.manager.showWhenStarting\", False)\n",
    "\n",
    "random.shuffle(doc_types)\n",
    "\n",
    "def dl_pdf(tempo, driver, firefox_download_folder, doc_name_from_site):\n",
    "    time_counter = 0\n",
    "    while driver.find_element(By.CSS_SELECTOR, \"#loadingBar\").get_attribute(\"class\")!=\"hidden\" and time_counter<60:\n",
    "        time.sleep(tempo)\n",
    "        time_counter = time_counter + 1\n",
    "    driver.find_element(By.CSS_SELECTOR, \"#download\").click()\n",
    "\n",
    "    time_counter = 0      \n",
    "    while (not os.path.exists(os.path.join(firefox_download_folder, doc_name_from_site))) and time_counter<60:\n",
    "        time.sleep(tempo)\n",
    "        time_counter = time_counter + 1\n",
    "    time.sleep(tempo)\n",
    "\n",
    "\n",
    "def rename_dled_doc(doc_name_from_site, firefox_download_folder, filename, dl_dir, suffix='', file_extension=\".pdf\"):\n",
    "    list_of_files = glob.glob(os.path.join(firefox_download_folder,\"*\"+file_extension))\n",
    "    latest_file = sorted(list_of_files, key=os.path.getctime, reverse=True)[:10]\n",
    "    \n",
    "    if '&' in doc_name_from_site:\n",
    "        doc_name_from_site = os.path.basename(sorted(glob.glob(os.path.join(firefox_download_folder, doc_name_from_site.split('&')[0]+'&'+'*'+file_extension)), \n",
    "                            key=os.path.getctime, \n",
    "                            reverse=True)[0])\n",
    "\n",
    "    doc_is_renamed = False\n",
    "    tempo_download = 0\n",
    "    while tempo_download<10:\n",
    "        if (doc_name_from_site in [os.path.basename(val) for val in latest_file]):\n",
    "            if not os.path.exists(os.path.join(dl_dir, filename+suffix+file_extension)):\n",
    "                os.rename(os.path.join(firefox_download_folder, doc_name_from_site), os.path.join(dl_dir, filename+suffix+file_extension))\n",
    "            else:\n",
    "                os.remove(os.path.join(firefox_download_folder, doc_name_from_site))\n",
    "            doc_is_renamed = True\n",
    "            break\n",
    "        tempo_download = tempo_download + 1\n",
    "        time.sleep(1)\n",
    "\n",
    "    if not doc_is_renamed:\n",
    "        raise NameError('Could not rename_dled_doc')\n",
    "\n",
    "\n",
    "def close_pdf_window(driver, tempo):\n",
    "    time.sleep(tempo)\n",
    "    driver.switch_to.window(driver.window_handles[1])\n",
    "    driver.close()\n",
    "    driver.switch_to.window(driver.window_handles[0])\n",
    "    time.sleep(tempo)\n",
    "\n",
    "\n",
    "for doc_type in doc_types:\n",
    "    logging.info(\"Start collecting \"+doc_type)\n",
    "\n",
    "    dl_dir = os.path.join(os.getcwd(), 'data', 'DRS', 'raw data', doc_type, 'pdf')\n",
    "    options.set_preference(\"browser.download.dir\", dl_dir)\n",
    "\n",
    "    Document_Type_name_in_DRS = metadata_mapping[metadata_mapping[\"Document Type Name in API request\"]==doc_type][\"Document Type name in DRS \"].unique()[0]\n",
    "    Metadata_Names_in_DRS =  metadata_mapping[metadata_mapping[\"Document Type Name in API request\"]==doc_type][\"Metadata Name in DRS\"]\n",
    "\n",
    "    if len(glob.glob(os.path.join(os.getcwd(), 'data', 'DRS', 'index', doc_type+'_202*.parquet')))>0:\n",
    "        logging.info(\"DRS PDF collector no optimization for \"+doc_type)\n",
    "\n",
    "        df_drs = pd.read_parquet(glob.glob(os.path.join(os.getcwd(), 'data', 'DRS', 'index', doc_type+'_202*.parquet'))[-1])\n",
    "\n",
    "        driver = webdriver.Firefox(options=options)\n",
    "        driver.maximize_window()\n",
    "\n",
    "        for index, row in df_drs.sample(frac=1).iterrows():\n",
    "            # DEBUG\n",
    "            # row['documentURL'],row['drs:status'],row['documentGuid']='https://drs.faa.gov/browse/excelExternalWindow/DRSDOCID186179865920220916145416.0001?modalOpened=true','Current','DRSDOCID186179865920220916145416'\n",
    "            # doc_type = 'FSB_REPORTS'\n",
    "            # Document_Type_name_in_DRS = 'Flight Standardization Board Reports (FSBR)'\n",
    "            # dl_dir = r'C:\\Users\\victor\\Documents\\DeepLearning\\FAA NLP Project\\data\\DRS\\raw data\\FSB_REPORTS\\pdf'\n",
    "\n",
    "            # 'An error occurred while loading the PDF'\n",
    "\n",
    "            # List<WebElement> list = driver.findElements(By.xpath(\"//*[contains(text(),'\" + text + \"')]\"));\n",
    "            # Assert.assertTrue(\"Text not found!\", list.size() > 0);\n",
    "\n",
    "            # END DEBUG\n",
    "\n",
    "            filename = row['drs:status']+\"__\"+row['documentGuid']\n",
    "            doc_name_from_site = 'N/A'\n",
    "\n",
    "            main_doc_downloaded = (os.path.exists(os.path.join(dl_dir, row['drs:status']+\"__\"+row['documentGuid']+\".pdf\"))) or (os.path.exists(os.path.join(dl_dir, row['drs:status']+\"__\"+row['documentGuid']+\".txt\")))\n",
    "            # Check if file already dowloaded. We only test for the first attachment (generaly html or doc file) since the pdf is always listed last (__1 or __2) and from the beginning I've started with pdf only.\\\\\n",
    "            attachments_downloaded = (len(glob.glob(os.path.join(dl_dir, row['drs:status']+\"__\"+row['documentGuid']+\"__*__0.pdf\")))>0) or \\\n",
    "                                    (len(glob.glob(os.path.join(dl_dir, row['drs:status']+\"__\"+row['documentGuid']+\"__*__-1.txt\")))>0)\n",
    "\n",
    "            if (not main_doc_downloaded) or (not attachments_downloaded):\n",
    "                try:\n",
    "                    url, driver, doc_type, Document_Type_name_in_DRS = row['documentURL']+\"?modalOpened=true\", driver, doc_type, Document_Type_name_in_DRS\n",
    "\n",
    "                    for n in range(len(driver.window_handles)-1,0,-1):\n",
    "                        driver.switch_to.window(driver.window_handles[n])\n",
    "                        driver.close()\n",
    "\n",
    "                    driver.get(url)\n",
    "                    driver.switch_to.window(driver.window_handles[0])\n",
    "\n",
    "                    try:        \n",
    "                        WebDriverWait(driver, 30).until(\n",
    "                            EC.presence_of_element_located((By.CSS_SELECTOR, '[title*=\"'+Document_Type_name_in_DRS+'\"]'))\n",
    "                        )\n",
    "\n",
    "                        is_text_only = len(driver.find_elements(By.ID, \"printArea\"))>0\n",
    "\n",
    "                        if is_text_only:\n",
    "                            with open(os.path.join(dl_dir, filename+'.txt'), 'w') as f:\n",
    "                                f.write(driver.find_element(By.ID, \"printArea\").text)\n",
    "                            with open(os.path.join(dl_dir, filename+'__None__-1.txt'), 'w') as f:\n",
    "                                f.write('')\n",
    "                        else:\n",
    "                            has_documents = len(driver.find_elements(By.ID, \"viewer\"))>0\n",
    "                            if has_documents:\n",
    "                                WebDriverWait(driver, 30).until(\n",
    "                                    EC.element_to_be_clickable((By.CSS_SELECTOR, \"#download\"))\n",
    "                                )\n",
    "                                # Main file when no attachment\n",
    "                                if not main_doc_downloaded:\n",
    "                                    doc_name_from_site = driver.find_element(By.CLASS_NAME, \"doc-view\").find_element(By.CLASS_NAME, \"ng-star-inserted\").text\n",
    "                                    if doc_name_from_site[0:2]==\"- \":\n",
    "                                        doc_name_from_site = doc_name_from_site[2:]\n",
    "\n",
    "                                    dl_pdf(tempo, driver, firefox_download_folder, doc_name_from_site)\n",
    "                                    rename_dled_doc(doc_name_from_site, firefox_download_folder, filename, dl_dir)\n",
    "                                    close_pdf_window(driver, tempo)\n",
    "\n",
    "                                # Attachments (contains main file also)\n",
    "                                if not attachments_downloaded:\n",
    "                                    has_attachments = len(driver.find_elements(By.CSS_SELECTOR, \"[aria-label='Attachments/Public Comments']\"))>0\n",
    "                                    if has_attachments:\n",
    "                                        driver.find_element(By.CSS_SELECTOR, \"[aria-label='Attachments/Public Comments']\").click()\n",
    "                                        nb_of_attachments = len(driver.find_elements(By.CLASS_NAME, \"attachment-links\"))\n",
    "\n",
    "                                        for n in range(0, nb_of_attachments):\n",
    "                                            driver.find_elements(By.CLASS_NAME, \"attachment-links\")[n].click()\n",
    "                                            driver.find_element(By.CSS_SELECTOR, \"[aria-label='Attachments/Public Comments']\").click()\n",
    "\n",
    "                                            doc_name_from_site = driver.find_elements(By.CLASS_NAME, \"attachment-links\")[n].text\n",
    "                                            doc_name_from_site_extention = driver.find_elements(By.CLASS_NAME, \"attachment-links\")[n].text.split(\".\")[-1].lower()\n",
    "                                            suffix = \"__\" + doc_name_from_site + \"__\" + str(n)       \n",
    "                                            attachment_downloaded = os.path.exists(os.path.join(dl_dir, filename+suffix+\".\"+doc_name_from_site_extention))   \n",
    "                                            is_nopreview_onlydownload = len(driver.find_elements(By.XPATH, \"//*[contains(text(), 'Download')]\"))>0\n",
    "\n",
    "                                            if not attachment_downloaded:\n",
    "                                                if doc_name_from_site_extention=='pdf':                                                    \n",
    "                                                    dl_pdf(tempo, driver, firefox_download_folder, doc_name_from_site)\n",
    "                                                    rename_dled_doc(doc_name_from_site, firefox_download_folder, filename, dl_dir, suffix, \".\"+doc_name_from_site_extention)\n",
    "                                                    close_pdf_window(driver, tempo)\n",
    "\n",
    "                                                elif doc_name_from_site_extention in ['htm', 'html']:\n",
    "                                                    with open(os.path.join(dl_dir, filename+suffix+\".\"+doc_name_from_site_extention), 'w') as f:\n",
    "                                                        f.write(driver.find_element(By.ID, \"printArea\").text)\n",
    "\n",
    "                                                elif is_nopreview_onlydownload:\n",
    "                                                    driver.find_elements(By.XPATH, \"//*[contains(text(), 'Download')]\")[0].click()\n",
    "                                                                                                \n",
    "                                                    rename_dled_doc(doc_name_from_site, firefox_download_folder, filename, dl_dir, suffix, \".\"+doc_name_from_site_extention)\n",
    "\n",
    "                                    else: # no attachment\n",
    "                                        with open(os.path.join(dl_dir, filename+'__None__-1.txt'), 'w') as f:\n",
    "                                            f.write('')\n",
    "                            else: # no documents\n",
    "                                with open(os.path.join(dl_dir, filename+'.txt'), 'w') as f:\n",
    "                                    f.write('')\n",
    "                                with open(os.path.join(dl_dir, filename+'__None__-1.txt'), 'w') as f:\n",
    "                                    f.write('')\n",
    "\n",
    "                    except TimeoutException as ex:\n",
    "                        pass\n",
    "                    except:\n",
    "                        logging.info(\"Document download Fail for \"+row['documentGuid']+\", doc type=\"+doc_type+\", url=\"+url+\", doc_dl_name=\"+doc_name_from_site)   \n",
    "                        \n",
    "                    logging.info(\"Document download Pass for \"+row['documentGuid']+\", doc type=\"+doc_type+\", url=\"+url)\n",
    "                except Exception as inst:\n",
    "                    logging.info(\"Document download Fail for \"+row['documentGuid']+\", doc type=\"+doc_type+\", url=\"+url+\", doc_dl_name=\"+doc_name_from_site)\n",
    "        driver.quit()\n",
    "    logging.info(\"Stop collecting \"+doc_type)\n",
    "    #break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ADFRAWD', 'ADNPRM', 'AC', 'AB', 'POLICY', 'CAM', 'CAR', 'CANIC',\n",
       "       'ADFREAD', 'ELOS', 'CFRFRSFAR', 'NORSEE', 'NPRM', 'PMA', 'SAIB',\n",
       "       'SCFINAL', 'SCPROPOSED', 'SFAR', 'STC', 'TSOI', 'TSO', 'FAR',\n",
       "       'TCDSMODEL', 'UNAPPROVED_PARTS_NOTIFICATIONS', 'INFO', 'SAFO',\n",
       "       'ORDER_8900.1', 'CLARIFY_POLICY', 'AFS-1_MEMORANDUMS', 'AT_JTA',\n",
       "       'AIRCRAFT_STANDARDIZED_CURRICULUM', 'AIRCRAFT_MASTER_SCHEDULE',\n",
       "       'OTHER_AWO', 'ALERTS', 'OTHER_AWARDS_INFORMATION_GUIDES',\n",
       "       '8900.1_EDITORIAL_CORRECTIONS', 'OTHER_EFB_RESEARCH_REPORTS',\n",
       "       'OTHER_EFB_CHECKLISTS', 'OTHER_FAA_90_DAY_SAFETY_REVIEW',\n",
       "       'OTHER_CPD_6.03', 'AFS_FFS_UPDATEPUB', 'AFS_FFS_UPDATES', 'FOEB',\n",
       "       'FSB_REPORTS', 'AFS_POLICY_DEV_MEMOS',\n",
       "       'OTHER_FLIGHT_STANDARDS_ORM_WORKSHEETS', 'AFS_FOCUS_TEAMS',\n",
       "       'GA_JTA', 'BULLETINS', 'OTHER_PS_HANDBOOKS',\n",
       "       'OTHER_INFORMATION_GUIDES', 'PILOT_QUALIFICATION_CURRICULUM',\n",
       "       'OTHER_INTERNATIONAL_PUBLICATIONS', 'OTHER_JOB_AIDS',\n",
       "       'OTHER_LASER_INVESTIGATION_REFERENCES', 'MMEL_POLICY_LETTERS',\n",
       "       'MMEL', 'NOTICES', 'OSR', 'OPSS_GUIDANCE', 'OSWG', 'ORDER_8300.10',\n",
       "       'ORDER_8400.10', 'ORDER_8700.1', 'ORDER_8740.1', 'ORDERS',\n",
       "       'PART_129_OPSPEC_JA', 'OTHER_PS_FEDERAL_AVIATION_ACTS',\n",
       "       'OTHER_PS_LEGAL_INTERPRETATIONS', 'OTHER_PS_POLICY_MEMORANDA',\n",
       "       'OTHER_PS_PREAMBLES', 'OTHER_QMS_AND_BP', 'OTHER_RCCB',\n",
       "       'OTHER_RIRTP', 'SAS_AXH_DCT', 'SAS_DCT', 'STCRELIEFAPPLETTER',\n",
       "       '8900.1_SUMMARY_OF_CHANGES', 'OTHER_SPRS', 'OTHER_VPM',\n",
       "       'LEGAL_INTERPRETATIONS'], dtype=object)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "doc_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Service</th>\n",
       "      <th>Document Type name in DRS</th>\n",
       "      <th>Document Type Name in API request</th>\n",
       "      <th>Metadata Name in DRS</th>\n",
       "      <th>Metadata Name in API Response</th>\n",
       "      <th>Metadata Value Data Type</th>\n",
       "      <th>Deafult  Sort By</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AIR</td>\n",
       "      <td>AD Final Rules</td>\n",
       "      <td>ADFRAWD</td>\n",
       "      <td>AD Number</td>\n",
       "      <td>drs:documentNumber</td>\n",
       "      <td>TEXT</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AIR</td>\n",
       "      <td>AD Final Rules</td>\n",
       "      <td>ADFRAWD</td>\n",
       "      <td>Status</td>\n",
       "      <td>drs:status</td>\n",
       "      <td>TEXT</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AIR</td>\n",
       "      <td>AD Final Rules</td>\n",
       "      <td>ADFRAWD</td>\n",
       "      <td>Docket Number</td>\n",
       "      <td>drs:adfrawdDocketNo</td>\n",
       "      <td>TEXT</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AIR</td>\n",
       "      <td>AD Final Rules</td>\n",
       "      <td>ADFRAWD</td>\n",
       "      <td>Amendment</td>\n",
       "      <td>drs:adfrawdAmendment</td>\n",
       "      <td>TEXT</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AIR</td>\n",
       "      <td>AD Final Rules</td>\n",
       "      <td>ADFRAWD</td>\n",
       "      <td>Office of Primary Responsibility</td>\n",
       "      <td>drs:primaryRespOffice</td>\n",
       "      <td>ARRAY</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1581</th>\n",
       "      <td>Regulations</td>\n",
       "      <td>Legal Interpretations</td>\n",
       "      <td>LEGAL_INTERPRETATIONS</td>\n",
       "      <td>AB Reference</td>\n",
       "      <td>drs:ABReference</td>\n",
       "      <td>ARRAY</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1582</th>\n",
       "      <td>Regulations</td>\n",
       "      <td>Legal Interpretations</td>\n",
       "      <td>LEGAL_INTERPRETATIONS</td>\n",
       "      <td>AD Reference</td>\n",
       "      <td>drs:ADReference</td>\n",
       "      <td>ARRAY</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1583</th>\n",
       "      <td>Regulations</td>\n",
       "      <td>Legal Interpretations</td>\n",
       "      <td>LEGAL_INTERPRETATIONS</td>\n",
       "      <td>CAR Reference</td>\n",
       "      <td>drs:CARReference</td>\n",
       "      <td>ARRAY</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1584</th>\n",
       "      <td>Regulations</td>\n",
       "      <td>Legal Interpretations</td>\n",
       "      <td>LEGAL_INTERPRETATIONS</td>\n",
       "      <td>Exemption Reference</td>\n",
       "      <td>drs:EXReference</td>\n",
       "      <td>ARRAY</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1585</th>\n",
       "      <td>Regulations</td>\n",
       "      <td>Legal Interpretations</td>\n",
       "      <td>LEGAL_INTERPRETATIONS</td>\n",
       "      <td>SFAR Reference</td>\n",
       "      <td>drs:SFARReference</td>\n",
       "      <td>ARRAY</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1586 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Service  Document Type name in DRS   \\\n",
       "0             AIR             AD Final Rules   \n",
       "1             AIR             AD Final Rules   \n",
       "2             AIR             AD Final Rules   \n",
       "3             AIR             AD Final Rules   \n",
       "4             AIR             AD Final Rules   \n",
       "...           ...                        ...   \n",
       "1581  Regulations      Legal Interpretations   \n",
       "1582  Regulations      Legal Interpretations   \n",
       "1583  Regulations      Legal Interpretations   \n",
       "1584  Regulations      Legal Interpretations   \n",
       "1585  Regulations      Legal Interpretations   \n",
       "\n",
       "     Document Type Name in API request              Metadata Name in DRS  \\\n",
       "0                              ADFRAWD                         AD Number   \n",
       "1                              ADFRAWD                            Status   \n",
       "2                              ADFRAWD                     Docket Number   \n",
       "3                              ADFRAWD                         Amendment   \n",
       "4                              ADFRAWD  Office of Primary Responsibility   \n",
       "...                                ...                               ...   \n",
       "1581             LEGAL_INTERPRETATIONS                      AB Reference   \n",
       "1582             LEGAL_INTERPRETATIONS                      AD Reference   \n",
       "1583             LEGAL_INTERPRETATIONS                     CAR Reference   \n",
       "1584             LEGAL_INTERPRETATIONS               Exemption Reference   \n",
       "1585             LEGAL_INTERPRETATIONS                    SFAR Reference   \n",
       "\n",
       "     Metadata Name in API Response  Metadata Value Data Type  Deafult  Sort By  \n",
       "0                drs:documentNumber                     TEXT             False  \n",
       "1                        drs:status                     TEXT             False  \n",
       "2               drs:adfrawdDocketNo                     TEXT             False  \n",
       "3              drs:adfrawdAmendment                     TEXT             False  \n",
       "4             drs:primaryRespOffice                    ARRAY             False  \n",
       "...                             ...                      ...               ...  \n",
       "1581                drs:ABReference                    ARRAY             False  \n",
       "1582                drs:ADReference                    ARRAY             False  \n",
       "1583               drs:CARReference                    ARRAY             False  \n",
       "1584                drs:EXReference                    ARRAY             False  \n",
       "1585              drs:SFARReference                    ARRAY             False  \n",
       "\n",
       "[1586 rows x 7 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metadata_mapping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test if collect of aspecific DRS doc from FAA DRS using \"Document Type Name in API request\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "response.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://drs.faa.gov/api/drs/data-pull/AB\n",
      "<Response [403]>\n"
     ]
    },
    {
     "ename": "JSONDecodeError",
     "evalue": "Expecting value: line 1 column 1 (char 0)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mJSONDecodeError\u001b[0m                           Traceback (most recent call last)",
      "File \u001b[1;32mc:\\Users\\victor\\anaconda3\\envs\\faa-nlp-drs\\Lib\\site-packages\\requests\\models.py:971\u001b[0m, in \u001b[0;36mResponse.json\u001b[1;34m(self, **kwargs)\u001b[0m\n\u001b[0;32m    970\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m--> 971\u001b[0m     \u001b[39mreturn\u001b[39;00m complexjson\u001b[39m.\u001b[39mloads(\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mtext, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs)\n\u001b[0;32m    972\u001b[0m \u001b[39mexcept\u001b[39;00m JSONDecodeError \u001b[39mas\u001b[39;00m e:\n\u001b[0;32m    973\u001b[0m     \u001b[39m# Catch JSON-related errors and raise as requests.JSONDecodeError\u001b[39;00m\n\u001b[0;32m    974\u001b[0m     \u001b[39m# This aliases json.JSONDecodeError and simplejson.JSONDecodeError\u001b[39;00m\n",
      "File \u001b[1;32mc:\\Users\\victor\\anaconda3\\envs\\faa-nlp-drs\\Lib\\json\\__init__.py:346\u001b[0m, in \u001b[0;36mloads\u001b[1;34m(s, cls, object_hook, parse_float, parse_int, parse_constant, object_pairs_hook, **kw)\u001b[0m\n\u001b[0;32m    343\u001b[0m \u001b[39mif\u001b[39;00m (\u001b[39mcls\u001b[39m \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m \u001b[39mand\u001b[39;00m object_hook \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m \u001b[39mand\u001b[39;00m\n\u001b[0;32m    344\u001b[0m         parse_int \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m \u001b[39mand\u001b[39;00m parse_float \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m \u001b[39mand\u001b[39;00m\n\u001b[0;32m    345\u001b[0m         parse_constant \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m \u001b[39mand\u001b[39;00m object_pairs_hook \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m \u001b[39mand\u001b[39;00m \u001b[39mnot\u001b[39;00m kw):\n\u001b[1;32m--> 346\u001b[0m     \u001b[39mreturn\u001b[39;00m _default_decoder\u001b[39m.\u001b[39mdecode(s)\n\u001b[0;32m    347\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mcls\u001b[39m \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m:\n",
      "File \u001b[1;32mc:\\Users\\victor\\anaconda3\\envs\\faa-nlp-drs\\Lib\\json\\decoder.py:337\u001b[0m, in \u001b[0;36mJSONDecoder.decode\u001b[1;34m(self, s, _w)\u001b[0m\n\u001b[0;32m    333\u001b[0m \u001b[39m\u001b[39m\u001b[39m\"\"\"Return the Python representation of ``s`` (a ``str`` instance\u001b[39;00m\n\u001b[0;32m    334\u001b[0m \u001b[39mcontaining a JSON document).\u001b[39;00m\n\u001b[0;32m    335\u001b[0m \n\u001b[0;32m    336\u001b[0m \u001b[39m\"\"\"\u001b[39;00m\n\u001b[1;32m--> 337\u001b[0m obj, end \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mraw_decode(s, idx\u001b[39m=\u001b[39m_w(s, \u001b[39m0\u001b[39m)\u001b[39m.\u001b[39mend())\n\u001b[0;32m    338\u001b[0m end \u001b[39m=\u001b[39m _w(s, end)\u001b[39m.\u001b[39mend()\n",
      "File \u001b[1;32mc:\\Users\\victor\\anaconda3\\envs\\faa-nlp-drs\\Lib\\json\\decoder.py:355\u001b[0m, in \u001b[0;36mJSONDecoder.raw_decode\u001b[1;34m(self, s, idx)\u001b[0m\n\u001b[0;32m    354\u001b[0m \u001b[39mexcept\u001b[39;00m \u001b[39mStopIteration\u001b[39;00m \u001b[39mas\u001b[39;00m err:\n\u001b[1;32m--> 355\u001b[0m     \u001b[39mraise\u001b[39;00m JSONDecodeError(\u001b[39m\"\u001b[39m\u001b[39mExpecting value\u001b[39m\u001b[39m\"\u001b[39m, s, err\u001b[39m.\u001b[39mvalue) \u001b[39mfrom\u001b[39;00m \u001b[39mNone\u001b[39;00m\n\u001b[0;32m    356\u001b[0m \u001b[39mreturn\u001b[39;00m obj, end\n",
      "\u001b[1;31mJSONDecodeError\u001b[0m: Expecting value: line 1 column 1 (char 0)",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mJSONDecodeError\u001b[0m                           Traceback (most recent call last)",
      "\u001b[1;32mc:\\Users\\victor\\Documents\\DeepLearning\\FAA NLP Project\\90_gendoc_collection.ipynb Cell 11\u001b[0m line \u001b[0;36m1\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/victor/Documents/DeepLearning/FAA%20NLP%20Project/90_gendoc_collection.ipynb#X41sZmlsZQ%3D%3D?line=10'>11</a>\u001b[0m response \u001b[39m=\u001b[39m requests\u001b[39m.\u001b[39mget(url, headers\u001b[39m=\u001b[39mheaders)\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/victor/Documents/DeepLearning/FAA%20NLP%20Project/90_gendoc_collection.ipynb#X41sZmlsZQ%3D%3D?line=12'>13</a>\u001b[0m \u001b[39mprint\u001b[39m(response)\n\u001b[1;32m---> <a href='vscode-notebook-cell:/c%3A/Users/victor/Documents/DeepLearning/FAA%20NLP%20Project/90_gendoc_collection.ipynb#X41sZmlsZQ%3D%3D?line=14'>15</a>\u001b[0m \u001b[39mprint\u001b[39m(pd\u001b[39m.\u001b[39mDataFrame\u001b[39m.\u001b[39mfrom_dict(response\u001b[39m.\u001b[39mjson()[\u001b[39m'\u001b[39m\u001b[39mdocuments\u001b[39m\u001b[39m'\u001b[39m]))\n",
      "File \u001b[1;32mc:\\Users\\victor\\anaconda3\\envs\\faa-nlp-drs\\Lib\\site-packages\\requests\\models.py:975\u001b[0m, in \u001b[0;36mResponse.json\u001b[1;34m(self, **kwargs)\u001b[0m\n\u001b[0;32m    971\u001b[0m     \u001b[39mreturn\u001b[39;00m complexjson\u001b[39m.\u001b[39mloads(\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mtext, \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwargs)\n\u001b[0;32m    972\u001b[0m \u001b[39mexcept\u001b[39;00m JSONDecodeError \u001b[39mas\u001b[39;00m e:\n\u001b[0;32m    973\u001b[0m     \u001b[39m# Catch JSON-related errors and raise as requests.JSONDecodeError\u001b[39;00m\n\u001b[0;32m    974\u001b[0m     \u001b[39m# This aliases json.JSONDecodeError and simplejson.JSONDecodeError\u001b[39;00m\n\u001b[1;32m--> 975\u001b[0m     \u001b[39mraise\u001b[39;00m RequestsJSONDecodeError(e\u001b[39m.\u001b[39mmsg, e\u001b[39m.\u001b[39mdoc, e\u001b[39m.\u001b[39mpos)\n",
      "\u001b[1;31mJSONDecodeError\u001b[0m: Expecting value: line 1 column 1 (char 0)"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import optparse\n",
    "\n",
    "doc_type = 'AB'\n",
    "\n",
    "url = \"https://drs.faa.gov/api/drs/data-pull/\"+doc_type\n",
    "\n",
    "print(url)\n",
    "\n",
    "with open(os.path.join(os.getcwd(),'drs_api.txt'), \"r\") as f:\n",
    "    x_api_key = f.read()\n",
    "\n",
    "headers = {'x-api-key': x_api_key}\n",
    "response = requests.get(url, headers=headers)\n",
    "\n",
    "print(response)\n",
    "\n",
    "print(pd.DataFrame.from_dict(response.json()['documents']))\n",
    "#print(response.json()['summary']['hasMoreItems'])\n",
    "#print(response.json()['summary']['totalItems'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Collect full DRS doc index from FAA DRS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import logging\n",
    "import requests\n",
    "import os\n",
    "import datetime\n",
    "import time\n",
    "import glob\n",
    "import numpy as np\n",
    "\n",
    "time_init = time.monotonic()\n",
    "logging.basicConfig(filename='90_gendoc_collection.log', encoding='utf-8',format='%(asctime)s %(message)s', level=logging.INFO)\n",
    "\n",
    "with open(os.path.join(os.getcwd(),'drs_api.txt'), \"r\") as f:\n",
    "    x_api_key = f.read()\n",
    "\n",
    "for doc_type in doc_types:\n",
    "    df_drs = None\n",
    "    hasMoreItems = True\n",
    "    offset = 0\n",
    "    logging.info(\"FileType: \"+doc_type)\n",
    "\n",
    "    while hasMoreItems:\n",
    "        try:\n",
    "            url = \"https://drs.faa.gov/api/drs/data-pull/\"+doc_type+\"?offset=\"+str(offset)\n",
    "            headers = {'x-api-key': x_api_key}\n",
    "            response = requests.get(url, headers=headers)\n",
    "\n",
    "            if df_drs is None:\n",
    "                df_drs = pd.DataFrame.from_dict(response.json()['documents'], dtype=\"string\")\n",
    "            else:\n",
    "                df_drs = pd.concat([df_drs, \n",
    "                                    pd.DataFrame.from_dict(response.json()['documents'], dtype=\"string\")])\n",
    "            \n",
    "            hasMoreItems = response.json()['summary']['hasMoreItems'] and (offset<response.json()['summary']['totalItems'])\n",
    "            logging.info(\"Passed with Offset: \"+str(offset)+\"/\"+str(response.json()['summary']['totalItems'])+\"; hasMoreItems=\"+str(response.json()['summary']['hasMoreItems'])+\"; url: \"+url)\n",
    "            offset = offset + response.json()['summary']['count']\n",
    "        except:\n",
    "            logging.info(\"Failed with Offset: \"+str(offset)+\". DRS Error: \"+response.json()['errorMessage']+\"; url: \"+url)\n",
    "            hasMoreItems = False\n",
    "\n",
    "    if df_drs is not None:\n",
    "        df_drs.to_parquet(os.path.join(os.getcwd(),'data', 'DRS', 'index', doc_type+'_'+datetime.datetime.now().strftime(\"%Y%m%d - %H%M%S\")+'.parquet'))            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_drsmeta = None\n",
    "\n",
    "for doc_type in doc_types:\n",
    "    parquet_files = sorted(glob.glob(os.path.join(os.getcwd(),'data', 'DRS', 'index',doc_type+'_202*.parquet')), \n",
    "                        key=os.path.getctime, \n",
    "                        reverse=True)\n",
    "\n",
    "    if len(parquet_files)>0:\n",
    "        this_parquet = pd.read_parquet(parquet_files[0])\n",
    "        this_parquet.insert(0, \"doc_type\", doc_type)\n",
    "\n",
    "        if df_drsmeta is None:\n",
    "            df_drsmeta = this_parquet\n",
    "        else:\n",
    "            df_drsmeta = pd.concat([this_parquet,\n",
    "                                    df_drsmeta])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Just for fun - Collect from DRS using generated doc_types API names in case FAA didn't provide a complete file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import logging\n",
    "import requests\n",
    "import os\n",
    "import datetime\n",
    "import glob\n",
    "import string\n",
    "\n",
    "logging.basicConfig(filename='90_gendoc_collection.log', encoding='utf-8',format='%(asctime)s %(message)s', level=logging.INFO)\n",
    "\n",
    "with open(os.path.join(os.getcwd(),'drs_api.txt'), \"r\") as f:\n",
    "    x_api_key = f.read()\n",
    "\n",
    "generated_doc_types = []\n",
    "for letter_1 in list(string.ascii_uppercase):\n",
    "    for letter_2 in list(string.ascii_uppercase):\n",
    "        for letter_3 in list(string.ascii_uppercase):\n",
    "            for letter_4 in list(string.ascii_uppercase):\n",
    "                generated_doc_types.append(letter_1+letter_2+letter_3+letter_4)\n",
    "\n",
    "for doc_type in generated_doc_types:\n",
    "    if len(glob.glob(os.path.join(os.getcwd(),'database', 'all drs', doc_type+'*.parquet')))==0:\n",
    "        df_drs = None\n",
    "        hasMoreItems = True\n",
    "        offset = 0\n",
    "        logging.info(\"FileType: \"+doc_type)\n",
    "\n",
    "        while hasMoreItems:\n",
    "            try:\n",
    "                url = \"https://drs.faa.gov/api/drs/data-pull/\"+doc_type+\"?offset=\"+str(offset)\n",
    "                headers = {'x-api-key': x_api_key}\n",
    "                response = requests.get(url, headers=headers)\n",
    "\n",
    "                if df_drs is None:\n",
    "                    df_drs = pd.DataFrame.from_dict(response.json()['documents'])\n",
    "                else:\n",
    "                    df_drs = pd.concat([df_drs, pd.DataFrame.from_dict(response.json()['documents'])])\n",
    "                \n",
    "                hasMoreItems = response.json()['summary']['hasMoreItems'] and (offset<response.json()['summary']['totalItems'])\n",
    "                logging.info(\"Passed with Offset: \"+str(offset)+\"/\"+str(response.json()['summary']['totalItems'])+\"; hasMoreItems=\"+str(response.json()['summary']['hasMoreItems'])+\"; url: \"+url)\n",
    "                offset = offset + response.json()['summary']['count']\n",
    "            except:\n",
    "                logging.info(\"Failed with Offset: \"+str(offset)+\". DRS Error: \"+response.json()['errorMessage']+\"; url: \"+url)\n",
    "                hasMoreItems = False\n",
    "\n",
    "        if df_drs is not None:\n",
    "            df_drs.to_parquet(os.path.join(os.getcwd(),'database','all drs',doc_type+'_'+datetime.datetime.now().strftime(\"%Y%m%d - %H%M%S\")+'.parquet'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## [OBSOLETE]  Collecting all DRS docs from FAA DRS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [OBSOLETE] Main function for DRS PDF collector "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.by import By\n",
    "from selenium.webdriver.support.wait import WebDriverWait\n",
    "from selenium.common.exceptions import TimeoutException\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "import time\n",
    "import datetime\n",
    "import os.path\n",
    "\n",
    "def collect_drs_pdf(url, driver, doc_type, Document_Type_name_in_DRS, filename):\n",
    "    driver.get(url)\n",
    "    dl_dir = os.path.join(os.getcwd(), 'database', 'data','all drs', doc_type, 'pdf')\n",
    "\n",
    "    try:        \n",
    "        WebDriverWait(driver, 30).until(\n",
    "            EC.presence_of_element_located((By.CSS_SELECTOR, '[title*=\"'+Document_Type_name_in_DRS+'\"]')) # '[title=\"'+Document_Type_name_in_DRS+'\"]'\n",
    "        )\n",
    "\n",
    "        is_text_only = len(driver.find_elements(By.ID, \"printArea\"))>0\n",
    "\n",
    "        if is_text_only:\n",
    "            with open(os.path.join(dl_dir, filename+'.txt'), 'w') as f:\n",
    "                f.write(driver.find_element(By.ID, \"printArea\").text)\n",
    "        else:\n",
    "            WebDriverWait(driver, 30).until(\n",
    "                EC.element_to_be_clickable((By.CSS_SELECTOR, \"#download\"))\n",
    "            )\n",
    "            time_counter = 0\n",
    "            while driver.find_element(By.CSS_SELECTOR, \"#loadingBar\").get_attribute(\"class\")!=\"hidden\" and time_counter<60:\n",
    "                time.sleep(0.5)\n",
    "                time_counter = time_counter + 1\n",
    "            driver.find_element(By.CSS_SELECTOR, \"#download\").click()\n",
    "\n",
    "            doc_name_from_site = driver.find_element(By.CLASS_NAME, \"doc-view\").find_element(By.CLASS_NAME, \"ng-star-inserted\").text\n",
    "            if doc_name_from_site[0:2]==\"- \":\n",
    "                doc_name_from_site = doc_name_from_site[2:]\n",
    "\n",
    "            time_counter = 0\n",
    "            while (not os.path.exists(os.path.join(dl_dir, doc_name_from_site))) and time_counter<60:\n",
    "                time.sleep(0.2)\n",
    "                time_counter = time_counter + 1\n",
    "            time.sleep(0.2)\n",
    "\n",
    "            list_of_files = glob.glob(os.path.join(dl_dir,\"*.pdf\"))\n",
    "            latest_file = sorted(list_of_files, key=os.path.getctime, reverse=True)[:10]\n",
    "\n",
    "            if (doc_name_from_site in [os.path.basename(val) for val in latest_file]):\n",
    "                os.rename(os.path.join(dl_dir, doc_name_from_site), os.path.join(dl_dir, filename+\".pdf\"))\n",
    "\n",
    "    except TimeoutException as ex:\n",
    "        pass\n",
    "    except:\n",
    "        pass        \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### [OBSOLETE] Collects all DRS PDF no optimization using the previously collected full STC list from FAA DRS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1725, 23)\n",
      "(1725, 23)\n"
     ]
    }
   ],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.firefox.options import Options\n",
    "import os\n",
    "import logging\n",
    "import re\n",
    "import glob\n",
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "options = Options()\n",
    "logging.basicConfig(filename='90_gendoc_collection.log', encoding='utf-8',format='%(asctime)s %(message)s', level=logging.INFO)\n",
    "\n",
    "\n",
    "for doc_type in doc_types:\n",
    "    doc_type = 'AC'\n",
    "\n",
    "    dl_dir = os.path.join(os.getcwd(), 'database', 'data', 'all drs', doc_type, 'pdf')\n",
    "\n",
    "    options.set_preference(\"browser.download.folderList\", 2)\n",
    "    options.set_preference(\"browser.download.manager.showWhenStarting\", False)\n",
    "    options.set_preference(\"browser.download.dir\", dl_dir)\n",
    "    options.set_preference(\"browser.helperApps.neverAsk.saveToDisk\", \"application/x-gzip\")\n",
    "\n",
    "    Document_Type_name_in_DRS = metadata_mapping[metadata_mapping[\"Document Type Name in API request\"]==doc_type][\"Document Type name in DRS \"].unique()[0]\n",
    "    Metadata_Names_in_DRS =  metadata_mapping[metadata_mapping[\"Document Type Name in API request\"]==doc_type][\"Metadata Name in DRS\"]\n",
    "\n",
    "    if len(glob.glob(os.path.join(os.getcwd(), 'database', 'all drs', doc_type+'*.parquet')))>0:\n",
    "        logging.info(\"DRS PDF collector no optimization for \"+doc_type)\n",
    "\n",
    "        df_drs = pd.read_parquet(glob.glob(os.path.join(os.getcwd(),'database','all drs',doc_type+'*.parquet'))[-1])\n",
    "        print(df_drs.shape)\n",
    "\n",
    "        driver = webdriver.Firefox(options=options)\n",
    "        driver.maximize_window()\n",
    "\n",
    "        for index, row in df_drs.iterrows():\n",
    "            if (not os.path.exists(os.path.join(dl_dir, row['drs:status']+\"__\"+row['documentGuid']+\".pdf\"))) and (not os.path.exists(os.path.join(dl_dir, row['drs:status']+\"__\"+row['documentGuid']+\".txt\"))):\n",
    "                try:\n",
    "                    filename = row['drs:status']+\"__\"+row['documentGuid']\n",
    "                    collect_drs_pdf(row['documentURL']+\"?modalOpened=true\", driver, doc_type, Document_Type_name_in_DRS, filename)\n",
    "                    #driver.close()\n",
    "                        \n",
    "                    logging.info(\"Document download Pass for \"+row['documentGuid'])\n",
    "                except Exception as inst:\n",
    "                    logging.info(\"Document download Fail for \"+row['documentGuid'])\n",
    "\n",
    "        driver.quit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DEbug"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Collect entries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'doc_types' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[3], line 7\u001b[0m\n\u001b[0;32m      4\u001b[0m df_drs \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m\n\u001b[0;32m      5\u001b[0m logging\u001b[38;5;241m.\u001b[39mbasicConfig(filename\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mdrs_data_collection.log\u001b[39m\u001b[38;5;124m'\u001b[39m, encoding\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mutf-8\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;28mformat\u001b[39m\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;132;01m%(asctime)s\u001b[39;00m\u001b[38;5;124m \u001b[39m\u001b[38;5;132;01m%(message)s\u001b[39;00m\u001b[38;5;124m'\u001b[39m, level\u001b[38;5;241m=\u001b[39mlogging\u001b[38;5;241m.\u001b[39mINFO)\n\u001b[1;32m----> 7\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m doc_type \u001b[38;5;129;01min\u001b[39;00m doc_types:\n\u001b[0;32m      8\u001b[0m     hasMoreItems \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mTrue\u001b[39;00m\n\u001b[0;32m      9\u001b[0m     offset \u001b[38;5;241m=\u001b[39m \u001b[38;5;241m0\u001b[39m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'doc_types' is not defined"
     ]
    }
   ],
   "source": [
    "import logging\n",
    "import requests\n",
    "import os\n",
    "\n",
    "df_drs = None\n",
    "logging.basicConfig(filename='drs_data_collection.log', encoding='utf-8',format='%(asctime)s %(message)s', level=logging.INFO)\n",
    "\n",
    "with open(os.path.join(os.getcwd(),'drs_api.txt'), \"r\") as f:\n",
    "    x_api_key = f.read()\n",
    "\n",
    "for doc_type in doc_types:\n",
    "    hasMoreItems = True\n",
    "    offset = 0\n",
    "    logging.info(\"FileType: \"+doc_type)\n",
    "\n",
    "    while hasMoreItems:\n",
    "        url = \"https://drs.faa.gov/api/drs/data-pull/\"+doc_type+\"?drs:offset=\"+str(offset)\n",
    "        headers = {'x-api-key': x_api_key}\n",
    "        response = requests.get(url, headers=headers)\n",
    "\n",
    "        if df_drs is None:\n",
    "            df_drs = pd.DataFrame.from_dict(response.json()['documents'])\n",
    "        else:\n",
    "            df_drs = pd.concat([df_drs, pd.DataFrame.from_dict(response.json()['documents'])])\n",
    "        \n",
    "        hasMoreItems = response.json()['summary']['hasMoreItems']\n",
    "        offset = offset + response.json()['summary']['offset']\n",
    "        logging.info(\"Offset: \"+offset)\n",
    "\n",
    "        df_drs.to_csv('drs.xlsx',index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Collect pdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_next_candidate_headers(this_header_index, headers_pattern):\n",
    "    candidate_headers = []\n",
    "    for n in range(0,len(this_header_index)):\n",
    "            if this_header_index[n]<0:\n",
    "                candidate_headers.append([headers_pattern[n][this_header_index[n]+1],n])\n",
    "                break\n",
    "            else:\n",
    "                candidate_headers.append([headers_pattern[n][this_header_index[n]+1],n])\n",
    "    \n",
    "    return candidate_headers\n",
    "\n",
    "print(get_next_candidate_headers([1,1,-1,-1], headers_patterns['AC27']))\n",
    "print(get_next_candidate_headers([0,-1,-1,-1], headers_patterns['AC27']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pdfplumber\n",
    "import re\n",
    "\n",
    "with pdfplumber.open(r'/home/victor/Desktop/FAA NLP Project/AC/AC 21-40A Guide for Obtaining a Supplemental Type Certificate.pdf') as pdf:\n",
    "    text = ''.join([str(this_page.extract_text())+'\\n' for n,this_page in enumerate(pdf.pages) if 4<n<7])\n",
    "\n",
    "lines_breakdown = re.findall('\\\\n', text)\n",
    "\n",
    "n_init=0\n",
    "n_end=0\n",
    "this_header_index = [-1,-1,-1]\n",
    "candidate_headers = get_next_candidate_headers(this_header_index, headers_patterns['AC21-40A'])\n",
    "headers = []\n",
    "while n_init<len(text):\n",
    "    this_match = re.search('\\\\n', text[n_init:])\n",
    "    n_end = this_match.start()    \n",
    "    if n_end>1:\n",
    "        for m,candidate in enumerate(candidate_headers):\n",
    "            x = re.search(candidate[0], text[n_init:n_init+n_end])\n",
    "            if x is not None:\n",
    "                headers.append([this_header_index,candidate,x,n_init,n_end])\n",
    "                \n",
    "                this_header_index[candidate[1]] = this_header_index[candidate[1]]+1\n",
    "                this_header_index[(candidate[1]+1):] = [-1]*len(this_header_index[(candidate[1]+1):])\n",
    "                candidate_headers = get_next_candidate_headers(this_header_index, headers_patterns['AC21-40A'])\n",
    "\n",
    "    n_init = n_init+n_end+1\n",
    "    \n",
    "print(headers)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.11.5"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "8c49a9e6d6b122d0bc46f085b0f9b84866f77188fdff3252b8a9eae176833897"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}