Get_Well_API_Data_From_CSV.py

# -*- coding: utf-8 -*-
"""
California Well Record File Request Automation

Reads API numbers from Column C of a CSV file,
finds all available files for each well,
selects all files,
and submits a file request.

Console:
    Shows only:
        API = SUCCESS
        API = FAILED

Detailed log:
    Saved to a TXT file in the same folder as this script.
"""

import requests
from bs4 import BeautifulSoup
import re
import time
import csv
import os
from datetime import datetime


# ============================================================
# SETTINGS
# ============================================================

# CSV containing the well list
CSV_FILE = r"C:\Projects\pythonscripts\Oil & Gas Well(1).csv"

# Column containing API numbers
# Column C = index 2 because Python starts counting at 0
API_COLUMN_INDEX = 2

# Your email
EMAIL = "epotts@toddgroundwater.com"

# Website
BASE_URL = "https://filerequest.conservation.ca.gov"

# Pause between wells
PAUSE_BETWEEN_WELLS = 2

# Pause between individual file selections
PAUSE_BETWEEN_FILES = 0.25


# ============================================================
# LOG FILE
# ============================================================

timestamp = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")

log_file = os.path.join(
    os.path.dirname(CSV_FILE),
    f"Well_File_Request_Log_{timestamp}.txt"
)


# ============================================================
# LOGGING FUNCTION
# ============================================================

def log(message=""):
    """
    Writes detailed information to the TXT file.
    Does NOT print to console.
    """

    with open(
        log_file,
        "a",
        encoding="utf-8"
    ) as f:

        f.write(str(message) + "\n")


# ============================================================
# SESSION
# ============================================================

session = requests.Session()

session.headers.update({
    "User-Agent": (
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:153.0) "
        "Gecko/20100101 Firefox/153.0"
    ),
    "Accept-Language": "en-US,en;q=0.9",
})


# ============================================================
# READ APIS FROM CSV
# ============================================================

def read_apis_from_csv():

    apis = []

    log("=" * 80)
    log("READING CSV FILE")
    log("=" * 80)
    log()
    log(f"CSV file: {CSV_FILE}")
    log()

    try:

        with open(
            CSV_FILE,
            "r",
            encoding="utf-8-sig",
            newline=""
        ) as f:

            reader = csv.reader(f)

            for row_number, row in enumerate(reader, 1):

                # Make sure the row has Column C
                if len(row) <= API_COLUMN_INDEX:
                    continue

                value = row[API_COLUMN_INDEX].strip()

                if not value:
                    continue

                # ------------------------------------------------
                # Remove anything that isn't a digit
                # ------------------------------------------------

                api = re.sub(
                    r"\D",
                    "",
                    value
                )

                # ------------------------------------------------
                # We expect the CSV API to look like:
                #
                # 403726384
                #
                # The website uses:
                #
                # 03726384
                #
                # ------------------------------------------------

                if len(api) != 9:
                    log(
                        f"Row {row_number}: "
                        f"Skipped invalid API value: {value}"
                    )
                    continue

                if api.startswith("4"):
                    site_api = api[1:]
                else:
                    site_api = api

                apis.append(api)

                log(
                    f"Row {row_number}: "
                    f"CSV API = {api} | "
                    f"Site API = {site_api}"
                )

    except Exception as e:

        log()
        log("=" * 80)
        log("ERROR READING CSV")
        log("=" * 80)
        log()
        log(str(e))

        raise

    log()
    log(f"TOTAL APIS FOUND: {len(apis)}")
    log()

    return apis


# ============================================================
# GET ALL FILES FOR A WELL
# ============================================================

def get_files_for_well(csv_api):

    site_api = csv_api[1:]

    url = (
        f"{BASE_URL}/WellRecord"
        f"?api={site_api}"
    )

    log()
    log("=" * 80)
    log("STEP 1 — GET WELL RECORD")
    log("=" * 80)
    log()
    log(f"CSV API:  {csv_api}")
    log(f"SITE API: {site_api}")
    log(f"URL:      {url}")
    log()

    response = session.get(
        url,
        timeout=30
    )

    log(
        f"Status: {response.status_code}"
    )

    if response.status_code != 200:

        log(
            "ERROR: Could not load WellRecord."
        )

        return []

    soup = BeautifulSoup(
        response.text,
        "html.parser"
    )

    files = []

    # --------------------------------------------------------
    # Find all file-selection checkboxes
    #
    # Example:
    #
    # onchange="ToggleItemSelected(2859327);"
    # --------------------------------------------------------

    checkboxes = soup.find_all(
        "input",
        {
            "type": "checkbox"
        }
    )

    for checkbox in checkboxes:

        onchange = checkbox.get(
            "onchange",
            ""
        )

        match = re.search(
            r"ToggleItemSelected\((\d+)\)",
            onchange
        )

        if not match:
            continue

        file_id = match.group(1)

        # ----------------------------------------------------
        # Find table row
        # ----------------------------------------------------

        row = checkbox.find_parent("tr")

        filename = ""

        if row:

            # Get filename from the row
            cells = row.find_all("td")

            if cells:

                filename = cells[0].get_text(
                    " ",
                    strip=True
                )

        files.append({
            "id": file_id,
            "filename": filename
        })

    # --------------------------------------------------------
    # Detailed log
    # --------------------------------------------------------

    log()
    log(
        f"FILES FOUND: {len(files)}"
    )
    log()

    for i, file in enumerate(files, 1):

        log(
            f"{i:3d}. "
            f"ID={file['id']}   "
            f"{file['filename']}"
        )

    return files


# ============================================================
# SELECT FILE
# ============================================================

def select_file(
    file_id,
    well_api
):

    url = (
        f"{BASE_URL}/IsFileSelectedToggle"
    )

    headers = {
        "X-Requested-With": "XMLHttpRequest",

        "Referer": (
            f"{BASE_URL}/WellRecord"
            f"?api={well_api}"
        ),

        "Origin": BASE_URL,
    }

    response = session.post(
        url,
        data={
            "Id": file_id
        },
        headers=headers,
        timeout=30
    )

    return response


# ============================================================
# GET REQUEST FILES PAGE
# ============================================================

def get_request_page(file_ids):

    ids = ",".join(file_ids)

    url = (
        f"{BASE_URL}/RequestFiles"
        f"?files={ids}"
    )

    log()
    log("=" * 80)
    log("STEP 3 — GET REQUEST FILES PAGE")
    log("=" * 80)
    log()
    log(url)

    response = session.get(
        url,
        timeout=30
    )

    log()
    log(
        f"Status: {response.status_code}"
    )

    log(
        f"Final URL: {response.url}"
    )

    if response.status_code != 200:

        log(
            "ERROR loading RequestFiles page."
        )

        return None, None

    soup = BeautifulSoup(
        response.text,
        "html.parser"
    )

    # --------------------------------------------------------
    # Find verification token
    # --------------------------------------------------------

    token_input = soup.find(
        "input",
        {
            "name":
                "__RequestVerificationToken"
        }
    )

    if not token_input:

        log()
        log(
            "ERROR: Could not find verification token."
        )

        return response, None

    token = token_input.get(
        "value"
    )

    log()
    log(
        "Verification token found."
    )

    log(
        f"Token length: {len(token)}"
    )

    # Do NOT put the entire token into the log
    log(
        f"Token starts: {token[:20]}..."
    )

    # --------------------------------------------------------
    # Get filename
    # --------------------------------------------------------

    filename_input = soup.find(
        "input",
        {
            "name": "FileName"
        }
    )

    filename = ""

    if filename_input:

        filename = filename_input.get(
            "value",
            ""
        )

    log()
    log(
        f"Filename: {filename}"
    )

    return response, {
        "token": token,
        "filename": filename,
        "file_ids": ids
    }


# ============================================================
# SUBMIT REQUEST
# ============================================================

def submit_request(
    file_ids,
    token,
    filename,
    well_api
):

    url = (
        f"{BASE_URL}/RequestFile"
    )

    data = {
        "FileId": ",".join(file_ids),
        "FileName": filename,
        "Name": "",
        "Email": EMAIL,
        "__RequestVerificationToken": token
    }

    headers = {
        "Referer": (
            f"{BASE_URL}/RequestFiles"
            f"?files="
            + ",".join(file_ids)
        ),

        "Origin": BASE_URL,
    }

    log()
    log("=" * 80)
    log("STEP 5 — SUBMIT FILE REQUEST")
    log("=" * 80)
    log()

    log(
        f"POST: {url}"
    )

    log()
    log("DATA:")

    log(
        f"FileId:   {data['FileId']}"
    )

    log(
        f"FileName: {data['FileName']}"
    )

    log(
        f"Name:     {data['Name']}"
    )

    log(
        f"Email:    {data['Email']}"
    )

    log(
        f"Token:    {token[:20]}..."
    )

    response = session.post(
        url,
        data=data,
        headers=headers,
        allow_redirects=False,
        timeout=30
    )

    log()
    log("=" * 80)
    log("RESULT")
    log("=" * 80)
    log()

    log(
        f"Status: {response.status_code}"
    )

    location = response.headers.get(
        "Location"
    )

    if location:

        log(
            f"Location: {location}"
        )

    if response.status_code == 302:

        if location == "/Success":

            log()
            log(
                "SUCCESS — REQUEST SUBMITTED"
            )

            return True

        else:

            log()
            log(
                "302 redirect received, "
                "but destination was unexpected."
            )

    else:

        log()
        log(
            "WARNING: Request did not return "
            "the expected HTTP 302."
        )

    return False


# ============================================================
# PROCESS ONE WELL
# ============================================================

def process_well(csv_api):

    site_api = csv_api[1:]

    # --------------------------------------------------------
    # STEP 1 — FIND FILES
    # --------------------------------------------------------

    files = get_files_for_well(
        csv_api
    )

    if not files:

        log()
        log(
            "NO FILES FOUND — SKIPPING WELL."
        )

        return False

    # --------------------------------------------------------
    # Extract file IDs
    # --------------------------------------------------------

    file_ids = [
        file["id"]
        for file in files
    ]

    # --------------------------------------------------------
    # STEP 2 — SELECT ALL FILES
    # --------------------------------------------------------

    log()
    log("=" * 80)
    log("STEP 2 — SELECT ALL FILES")
    log("=" * 80)
    log()

    for file in files:

        file_id = file["id"]

        response = select_file(
            file_id,
            site_api
        )

        log(
            f"File {file_id}: "
            f"HTTP {response.status_code}"
        )

        if response.status_code != 200:

            log(
                "WARNING: File selection "
                "may have failed."
            )

        time.sleep(
            PAUSE_BETWEEN_FILES
        )

    # --------------------------------------------------------
    # STEP 3 — GET REQUEST PAGE
    # --------------------------------------------------------

    response, request_info = (
        get_request_page(
            file_ids
        )
    )

    if request_info is None:

        log()
        log(
            "ERROR: Could not get request page."
        )

        return False

    # --------------------------------------------------------
    # STEP 4/5 — SUBMIT
    # --------------------------------------------------------

    success = submit_request(
        file_ids=file_ids,

        token=request_info[
            "token"
        ],

        filename=request_info[
            "filename"
        ],

        well_api=site_api
    )

    return success


# ============================================================
# MAIN
# ============================================================

# Start log
log()
log("=" * 80)
log("CALIFORNIA WELL RECORD FILE REQUEST AUTOMATION")
log("=" * 80)
log()
log(
    f"Started: "
    f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
)
log()
log(
    f"CSV: {CSV_FILE}"
)
log(
    f"Email: {EMAIL}"
)
log()


# ------------------------------------------------------------
# Read APIs
# ------------------------------------------------------------

try:

    APIS = read_apis_from_csv()

except Exception as e:

    print()
    print(
        "ERROR: Could not read CSV."
    )

    print(
        f"See log file: {log_file}"
    )

    raise


# ------------------------------------------------------------
# Console startup
# ------------------------------------------------------------

print()
print("=" * 60)
print(
    "CALIFORNIA WELL RECORD FILE REQUEST AUTOMATION"
)
print("=" * 60)

print()
print(
    f"Wells found in CSV: {len(APIS)}"
)

print(
    "Processing..."
)

print()


# ============================================================
# PROCESS WELLS
# ============================================================

results = []


for number, csv_api in enumerate(
    APIS,
    1
):

    try:

        success = process_well(
            csv_api
        )

        results.append({
            "api": csv_api,
            "site_api": csv_api[1:],
            "success": success
        })

        # ----------------------------------------------------
        # CONSOLE — ONLY SHOW SIMPLE RESULT
        # ----------------------------------------------------

        if success:

            print(
                f"{csv_api} = SUCCESS"
            )

        else:

            print(
                f"{csv_api} = FAILED"
            )

    except Exception as e:

        # ----------------------------------------------------
        # Detailed error goes into TXT
        # ----------------------------------------------------

        log()
        log("=" * 80)
        log("UNHANDLED ERROR PROCESSING WELL")
        log("=" * 80)
        log()
        log(
            f"CSV API: {csv_api}"
        )
        log()
        log(
            f"Error: {str(e)}"
        )

        results.append({
            "api": csv_api,
            "site_api": csv_api[1:],
            "success": False
        })

        # ----------------------------------------------------
        # Console
        # ----------------------------------------------------

        print(
            f"{csv_api} = FAILED"
        )

    # --------------------------------------------------------
    # Pause between wells
    # --------------------------------------------------------

    if number < len(APIS):

        time.sleep(
            PAUSE_BETWEEN_WELLS
        )


# ============================================================
# FINAL DETAILED SUMMARY — TXT ONLY
# ============================================================

log()
log()
log("=" * 80)
log("FINAL SUMMARY")
log("=" * 80)
log()

successful = 0
failed = 0


for result in results:

    if result["success"]:

        status = "SUCCESS"
        successful += 1

    else:

        status = "FAILED"
        failed += 1

    log(
        f"{result['api']}  "
        f"({result['site_api']})  "
        f"{status}"
    )


log()
log(
    f"Total wells: {len(results)}"
)

log(
    f"Successful:  {successful}"
)

log(
    f"Failed:      {failed}"
)

log()
log(
    f"Finished: "
    f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
)

log()
log("=" * 80)
log("DONE")
log("=" * 80)


# ============================================================
# CONSOLE FINISH
# ============================================================

print()
print(
    f"Complete. "
    f"{successful} successful, "
    f"{failed} failed."
)

print()
print(
    f"Detailed log saved to:"
)

print(
    log_file
)

print()