survey-extraction/etl/dimitra_hubspot_notes_gather.py

import os
from datetime import timedelta, timezone
import datetime
import pandas as pd
from bs4 import BeautifulSoup
from openpyxl import Workbook
from openpyxl.styles import Font
from etl.scraper.scraper import SharePointScraper, SharePointInstaller, previous_monday
from etl.hubSpotClient.hubspot import HubSpotClient, DealStage
from collections import defaultdict
import time
# Auth credentials
os.environ["SHAREPOINT_CLIENT_ID"] = "6832a4c5-fb8c-4082-a746-4f51e1020f0d"
os.environ["SHAREPOINT_CLIENT_SECRET"] = "xpC8Q~Frww48SM1V-D8lGy5iOY7P_cJ7FF3jgarQ"
os.environ["SHAREPOINT_TENANT_ID"] = "10d5af8b-2cfd-4882-9ccd-b96e4812dacf"

hubspot = HubSpotClient()

# Week calculation
today = datetime.datetime.now(datetime.UTC)
week1_start = today - timedelta(days=7)
week2_start = today - timedelta(days=14)
week3_start = today - timedelta(days=21)

def get_week_label(created_at_str):
    created_at = datetime.datetime.strptime(created_at_str, '%Y-%m-%d %H:%M:%S')
    created_at = created_at.replace(tzinfo=timezone.utc)
    if week1_start <= created_at <= today:
        return "Week 1"
    elif week2_start <= created_at < week1_start:
        return "Week 2"
    elif week3_start <= created_at < week2_start:
        return "Week 3"
    return None  # Ignore notes outside the 3-week range

# Pipelines to include
pipelines_to_include = [
    "SALES - SOCIAL HOUSING",
    "PVT PAY",
    "NRLA GENERAL ENQUIRIES",
]

exclude_stage = {
    "SALES - SOCIAL HOUSING": [
        "HA TO REENGAGE", "APPOINTMENT SCHEDULED", "AWAITING ASSET LIST", 
        "ASSET LIST RECEIVED", "ASSET LIST STANDARDISED", "ROUTE MARCH CREATED", 
        "HA WEEKLY REPORTING",
    ],
    "PVT PAY": [
        "LIVE OPPORTUNITY", "CLOSED LOST", "INVOICED", "COLD - KIT", "CLOSED WON",
    ],
    "NRLA GENERAL ENQUIRIES": [
        "CUSTOMER CONTACTED", "LOST", "COLD",
    ]
}

notes_data = defaultdict(list)
pipelines = hubspot.client.crm.pipelines.pipelines_api.get_all(object_type="deals")

for pipeline in pipelines.results:
    pipeline_name = pipeline.label.upper().strip()
    if pipeline_name in pipelines_to_include:
        for stage in pipeline.stages:
            if stage.label.upper().strip() not in [s.upper() for s in exclude_stage.get(pipeline_name, [])]:
                for deals in hubspot.get_all_deals_from_stage_id(stage.id):
                    print(f"Scraping deal {deals['deal_id']}")
                    time.sleep(1)
                    deal_notes_by_week = {"Week 1": [], "Week 2": [], "Week 3": []}
                    notes = hubspot.get_notes_from_deals_id(deals["deal_id"])
                    
                    for note in notes:
                        week_label = get_week_label(note["created_at"])
                        if not week_label:
                            continue
                        html_body = note.get('note')
                        if not html_body:
                            print(f"Skipping note with missing 'note' field: {note}")
                            continue

                        print(f"Debugging purposes html_body looks like {html_body}")
                        soup = BeautifulSoup(html_body, "html.parser")
                        plain_text = soup.get_text(separator="\n")
                        deal_notes_by_week[week_label].append(plain_text)

                    if any(deal_notes_by_week.values()):
                        deal_name = hubspot.get_deal_name_by_id(deals["deal_id"])
                        owner_name = "not assigned"
                        if deals["deal_owner"]:
                            try:
                                owner_name = hubspot.get_owner_name_from_id(deals['deal_owner'])
                            except:
                                owner_name = "Couldn't find owner information"

                        # Unique identifier to Domna Homes' hubspot
                        portal_id = 145275138

                        notes_data[pipeline_name].append({
                            "Deal Name": deal_name.upper(),
                            "Deal URL": f"https://app-eu1.hubspot.com/contacts/{portal_id}/record/0-3/{deals['deal_id']}/",
                            "Deal Owner": owner_name,
                            "Deal Stage": stage.label.upper(),
                            "Value": deals["value"],
                            "Notes Week 1": "\n---\n".join(deal_notes_by_week["Week 1"]),
                            "Notes Week 2": "\n---\n".join(deal_notes_by_week["Week 2"]),
                            "Notes Week 3": "\n---\n".join(deal_notes_by_week["Week 3"]),
                        })
                        print("delay to not bombard the server")
                        time.sleep(1)

# Create Excel Workbook
wb = Workbook()
wb.remove(wb.active)

for pipeline, deals in notes_data.items():
    ws = wb.create_sheet(title=pipeline[:31])

    headers = ["Deal Name", "Deal URL", "Deal Owner", "Deal Stage", "Value", "Notes Week 1", "Notes Week 2", "Notes Week 3"]
    ws.append(headers)
    for cell in ws[1]:
        cell.font = Font(bold=True)

    for row in deals:
        # Normalize notes to always be lists
        week_notes = {}
        for week in range(1, 4):
            key = f"Notes Week {week}"
            note_data = row.get(key, [])
            if isinstance(note_data, str):
                note_data = [note_data]
            week_notes[week] = note_data

        # Get first note per week (if any)
        first_notes = [week_notes[week][0] if len(week_notes[week]) > 0 else "" for week in range(1, 4)]

        # Add main deal row + first notes
        ws.append([
            row["Deal Name"],
            row["Deal URL"],
            row["Deal Owner"],
            row["Deal Stage"],
            row["Value"],
            *first_notes
        ])


        # Determine max number of remaining notes
        max_additional_notes = max(len(week_notes[week]) for week in range(1, 4)) - 1

        # Add remaining notes
        for i in range(1, max_additional_notes + 1):
            note_row = ["", "", "", ""]  # Empty deal columns
            for week in range(1, 4):
                notes = week_notes[week]
                note = notes[i] if i < len(notes) else ""
                note_row.append(note)
            ws.append(note_row)


# Generate file name with next Monday’s date
formatted = previous_monday()
file_name = f"{formatted} DEAL_NOTES_FROM_HUBSPOT.xlsx"
output_path = os.path.abspath(file_name)
wb.save(output_path)

# Upload to SharePoint
sharepoint_client = SharePointScraper(SharePointInstaller.DOMNA)
sharepoint_client.upload_file(
    output_path,
    f"02. Sales and Marketing/02. Deal Notes from Hubspot/{formatted}",
    file_name
)