> For the complete documentation index, see [llms.txt](https://docs.biglawinvestor.com/knowledge-base/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.biglawinvestor.com/knowledge-base/company/hermes/14.03-lending-monthly-reports.md).

# 14.03 Lending Monthly Reports

## Executive Summary

The **Lending Monthly Reports** script connects to the PostgreSQL database, retrieves lead data for mortgage brokers, generates monthly reports, and uploads them to Digital Ocean Spaces. This process ensures mortgage brokers have timely and accurate reports of their lead data.

## Example Output

<figure><img src="https://4262923832-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FOU8nJo04SgxSaEvyKxBd%2Fuploads%2FpIkBMYl6aaglayY87YoE%2FCleanShot%202024-07-09%20at%2012.30.21%402x.png?alt=media&amp;token=533be66c-0d77-435c-84f1-7a5287453cb7" alt=""><figcaption></figcaption></figure>

## Technical Summary

### Overview

The script performs the following key functions:

* Loads Environment Variables: Reads configuration values from a `.env` file.
* Connects to PostgreSQL Database: Establishes a connection to retrieve lead data.
* Queries Lead Data: Executes SQL queries to fetch lead metrics for construction and professional mortgage leads.
* Generates CSV Reports: Creates CSV files for each broker's monthly leads.
* Uploads to Digital Ocean Spaces: Uploads the generated CSV files to a specific folder in Digital Ocean Spaces.

### Detailed Steps

#### Import Necessary Libraries

Handles environment variables, logging, database connection, CSV processing, and Digital Ocean Spaces interaction.

#### Load Environment Variables

Uses `dotenv` to load database credentials and Digital Ocean Spaces credentials from a `.env` file.

#### Main Function

**Connect to PostgreSQL Database**

Connects using credentials from environment variables.

**Fetch Loan Officers**

Retrieves the list of mortgage brokers from the database.

**Get Prior Month and Year**

Calculates the previous month and year to determine the reporting period.

**Query Lead Data**

For each broker, constructs and executes SQL queries to fetch:

* Construction Leads: Lead data related to construction mortgages.
* Professional Leads: Lead data related to professional mortgages.

**Generate CSV Reports**

Writes the fetched lead data into CSV files for each broker, following a specific naming convention.

**Upload to Digital Ocean Spaces**

Uploads the generated CSV files to a designated folder in Digital Ocean Spaces using the `boto3` library.

**Error Handling**

Logs errors encountered during the process and provides detailed error messages.

### Key SQL Query Components

* **Construction Leads Query**: Fetches lead data related to construction mortgages.
* **Professional Leads Query**: Fetches lead data related to professional mortgages.

### Usage

* **Running the Script**: The script can be executed locally or scheduled to run periodically using a cron job.
* **Environment Setup**: Requires a `.env` file with database and Digital Ocean Spaces credentials.

## Detailed Workflow

### Import Libraries

* Uses `psycopg2` for database connection.
* Uses `csv` for CSV file handling.
* Uses `os` for operating system operations.
* Uses `datetime` for date calculations.
* Uses `boto3` for Digital Ocean Spaces interaction.
* Uses `dotenv` for environment variable management.

### Load Environment Variables

* Loads `DO_SPACES_KEY`, `DO_SPACES_SECRET`, `DO_SPACES_ENDPOINT`, and `DO_SPACES_REGION` for Digital Ocean Spaces.
* Loads database connection parameters.

### Main Execution

* **Connect to Database**: Establishes connection using `psycopg2`.
* **Fetch Loan Officers**: Retrieves broker data.
* **Determine Reporting Period**: Calculates prior month and year.
* **Query Data and Generate Reports**: Executes SQL queries and generates CSV reports.
* **Upload Reports**: Uses `boto3` to upload reports to Digital Ocean Spaces.
* **Error Handling**: Catches and logs errors.

### SQL Queries

* **Construction Leads Query**: Retrieves construction mortgage leads.
* **Professional Leads Query**: Retrieves professional mortgage leads.

### CSV Generation

* Creates CSV files with headers and lead data for each broker.

### Digital Ocean Spaces Upload

* Uploads CSV files to a specified folder, creating a hierarchical structure based on year and month.

### Cron Job Integration

* Can be scheduled using a cron job to run on the 1st of every month at 10 PM UTC.

## Summary

The script ensures that monthly lead reports are automatically generated and securely uploaded, providing mortgage brokers with up-to-date and organized lead data.