rollups.py

import re
from decimal import Decimal
from db.db_utils import get_connection

# Function to extract the year
def extract_year(string):
    match = re.search(r'\b(19|20)\d{2}\b', string)
    if match:
        return match.group()
    else:
        return None

db_conn = get_connection()
db_cursor = db_conn.cursor()

# find a list of all the alt_spellings of all the organizations
db_cursor.execute("SELECT unnest(alt_spellings) FROM organizations")
donor_results = db_cursor.fetchall()
list_of_valid_donors = [row[0].lower() for row in donor_results]

db_cursor.execute("SELECT run_key, organization_id, year FROM run_meta")
run_meta_rows = db_cursor.fetchall()

for run_meta_row in run_meta_rows:
    run_key = run_meta_row[0]
    organization_id = run_meta_row[1]
    year = run_meta_row[2]

    donations_to = 0
    donations_from = 0
    print(f"Processing run_key: {run_key} and year: {year}")

    # find all run_keys for the same year
    db_cursor.execute("SELECT run_key FROM run_meta WHERE year = %s", (year,))
    run_keys_in_year = db_cursor.fetchall()
    run_keys_in_year = [row[0] for row in run_keys_in_year]
    
    # donation discounts work as follows:
    # 1. given the current organization, find all donations from the current organization to other organizations in the same year
    # 2. find all received donations in the current result set (i.e. run_key)

    db_cursor.execute("SELECT unnest(alt_spellings) FROM organizations where id = %s", (organization_id,))
    current_org_results = db_cursor.fetchall()
    current_org_results = [row[0].lower() for row in current_org_results]

    # print(current_org_results)
    # find all donations from the current donor
    db_cursor.execute("SELECT sum(total_emissions_reduction::numeric(15, 6)) FROM results WHERE run_key IN %s and run_key != %s and donor in %s", (tuple(run_keys_in_year), run_key, tuple(current_org_results)))
    result = db_cursor.fetchone()
    if result and result[0]:
        donations_from = result[0]
    
    # find all donations to the current donor
    query = """
      SELECT sum(total_emissions_reduction::numeric(15, 6)) 
      FROM results 
      WHERE run_key = %s 
      AND LOWER(TRIM(donor)) = ANY(%s::text[])
    """

    db_cursor.execute(query, (run_key, list_of_valid_donors))
    result = db_cursor.fetchone()
    if result and result[0]:
        donations_to = result[0]
    
    # calculate the total_donations
    # print(f"donations_to: {donations_to}, donations_from: {donations_from}")
    donations = donations_to + donations_from

    # calculate total_emissions_reduction_pre
    db_cursor.execute("SELECT sum(total_emissions_reduction::numeric(15, 6)) FROM results WHERE run_key = %s", (run_key,))
    emissions_result = db_cursor.fetchone()
    if emissions_result and emissions_result[0]:
        total_emissions_reduction_pre = emissions_result[0]
        print(f"total_emissions_reduction_pre: {total_emissions_reduction_pre}")
    else:
        total_emissions_reduction_pre = 0

    # calculate total_weight
    db_cursor.execute("SELECT sum(weight_metric_tonnes::numeric(15, 6)) FROM results WHERE run_key = %s", (run_key,))
    weight_result = db_cursor.fetchone()
    if weight_result and weight_result[0]:
        total_weight_metric_tonnes = weight_result[0]
    else:
        total_weight_metric_tonnes = 0

    #  divide the donations_from by 2 because we attribute the emissions reductions to both the donor and the recipient
    donations_discount = donations / 2
    donations_discount = Decimal(donations_discount)
    total_emissions_reduction = total_emissions_reduction_pre - donations_discount

    # store this data in the rollups table
    db_cursor.execute("""
        INSERT INTO rollups (run_key, year, donations_double_counting_correction, total_emissions_reduction_pre, total_emissions_reduction, total_weight_metric_tonnes)
        VALUES (%s, %s, %s, %s, %s, %s)
        ON CONFLICT (run_key)
        DO UPDATE SET
            year = EXCLUDED.year,
            donations_double_counting_correction = EXCLUDED.donations_double_counting_correction,
            total_emissions_reduction_pre = EXCLUDED.total_emissions_reduction_pre,
            total_emissions_reduction = EXCLUDED.total_emissions_reduction,
            total_weight_metric_tonnes = EXCLUDED.total_weight_metric_tonnes
    """, (run_key, year, donations_discount, total_emissions_reduction_pre, total_emissions_reduction, total_weight_metric_tonnes, ))
    db_conn.commit()

db_cursor.close()
db_conn.close()