import streamlit as st import pandas as pd from pymongo import MongoClient from datetime import datetime import requests from openai import OpenAI from bs4 import BeautifulSoup import asyncio import smtplib from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from selenium import webdriver from selenium.webdriver.common.by import By from selenium.webdriver.common.keys import Keys from selenium.webdriver.support.ui import WebDriverWait from selenium.webdriver.support import expected_conditions as EC from selenium.common.exceptions import TimeoutException, NoSuchElementException import time # Initialize OpenAI client @st.cache_resource def init_openai(): client = OpenAI(api_key=('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')) return client # Initialize MongoDB connection @st.cache_resource def init_mongodb(): client = MongoClient("mongodb://linkedin_user:P4XnKOjkOaTg@18.235.17.44:27017/?authMechanism=DEFAULT") return client['linkedin_db'] # Get detailed address using Selenium and Google Maps @st.cache_data(ttl=3600) def get_location_info(company_name): options = webdriver.ChromeOptions() options.add_argument("--headless") options.add_argument("--no-sandbox") options.add_argument("--disable-dev-shm-usage") result = { "status": "failed", "address": None, "error": None } try: driver = webdriver.Chrome(options=options) driver.implicitly_wait(10) # Open Google Maps driver.get("https://www.google.com/maps") # Accept cookies if prompted (common in some regions) try: cookie_button = WebDriverWait(driver, 3).until( EC.element_to_be_clickable((By.XPATH, "//button[contains(text(), 'Accept all')]")) ) cookie_button.click() except: pass # No cookie prompt or different format # Find and use the search box search_box = driver.find_element(By.NAME, "q") search_box.clear() search_box.send_keys(company_name) search_box.send_keys(Keys.RETURN) # Wait for results and get the address wait = WebDriverWait(driver, 10) # First attempt with Io6YTe class try: address_element = wait.until( EC.presence_of_element_located((By.CLASS_NAME, "Io6YTe")) ) address = address_element.text result["status"] = "success" result["address"] = address except: # Fallback to alternative selectors try: # Try looking for the address in a different format address_container = wait.until( EC.presence_of_element_located((By.CSS_SELECTOR, "[data-section-id='addr']")) ) address = address_container.text.replace("Address: ", "") result["status"] = "success" result["address"] = address except: result["error"] = "Could not find address element" except TimeoutException: result["error"] = "Timeout waiting for Google Maps to load" except NoSuchElementException: result["error"] = "Could not find the required elements on the page" except Exception as e: result["error"] = f"An error occurred: {str(e)}" finally: # Always close the browser if 'driver' in locals(): driver.quit() return result def search_profiles(db, search_terms, location=None, limit=100): # Your existing function code here query = { '$and': [ {'search_query': {'$regex': search_terms, '$options': 'i'}}, ] } if location: query['$and'].append({'location': {'$regex': location, '$options': 'i'}}) profiles = list(db.LInkedinProfiles.find(query).limit(limit)) # Get all sent emails for this campaign sent_emails = list(db.sent_emails.find({}, { 'recipient_first_name': 1, 'recipient_last_name': 1, 'recipient_company': 1, 'sent_date': 1 })) # Create a lookup dictionary for sent emails sent_email_lookup = { f"{email['recipient_first_name']}_{email['recipient_last_name']}_{email['recipient_company']}": email['sent_date'] for email in sent_emails } # Add email status to each profile for profile in profiles: key = f"{profile['first_name']}_{profile['last_name']}_{profile['company']}" if key in sent_email_lookup: profile['email_status'] = 'Sent' profile['sent_date'] = sent_email_lookup[key] else: profile['email_status'] = 'Not Sent' profile['sent_date'] = None return profiles async def get_coffee_shops(company_address): if not company_address: return [] url = f"https://www.google.com/search?q=coffee%20shops%20near%20{company_address}&sca_esv=2621f7b39c394d4e&tbm=lcl" headers = { "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36" } try: response = requests.get(url, headers=headers) soup = BeautifulSoup(response.text, "html.parser") shops = soup.find_all("div", class_="VkpGBb") rated_shops = [] for shop in shops: name = shop.find("div", class_="dbg0pd").text if shop.find("div", class_="dbg0pd") else "N/A" rating_elem = shop.find("span", class_="yi40Hd YrbPuc") rating = rating_elem.text if rating_elem else "0" address_divs = shop.find_all("div") address = address_divs[-4].text.strip() if len(address_divs) > 2 else "N/A" try: rating_value = float(rating.replace("/5", "")) rated_shops.append({ "name": name, "rating": rating, "rating_value": rating_value, "address": address }) except ValueError: continue top_shops = sorted(rated_shops, key=lambda x: x["rating_value"], reverse=True)[:3] for shop in top_shops: del shop["rating_value"] return top_shops except Exception as e: print(f"Error fetching shops for {company_address}: {str(e)}") return [] def get_email_for_profile(profile_url): try: response = requests.get( "http://127.0.0.1:8000/get_emails", params={"profile_url": profile_url}, timeout=10 ) if response.status_code == 200: data = response.json() return data.get('email') else: st.error(f"API Error: Status {response.status_code}") return None except requests.exceptions.Timeout: st.error("Request timed out. Please try again.") return None except requests.exceptions.ConnectionError: st.error("Could not connect to the email service. Please check if the API server is running.") return None except Exception as e: st.error(f"Error fetching email: {str(e)}") return None def update_template_with_coffee_shop(template, shop_name, shop_address): paragraphs = template.split('\n\n') meeting_loc_idx = -1 for i, para in enumerate(paragraphs): if any(keyword in para.lower() for keyword in ['meet', 'coffee', 'discuss']): meeting_loc_idx = i break meeting_text = f"I would love to meet you at {shop_name} ({shop_address}) to discuss this further." if meeting_loc_idx >= 0: paragraphs[meeting_loc_idx] = meeting_text else: paragraphs.insert(-1, meeting_text) return '\n\n'.join(paragraphs) def generate_email_template(openai_client, profile_data, coffee_shops): try: shops_text = "" if coffee_shops: shops_text = "Nearby recommended meeting spots:\n" for i, shop in enumerate(coffee_shops, 1): shops_text += f"{i}. {shop['name']} (Rating: {shop['rating']}) - {shop['address']}\n" first_name = profile_data.get('first_name', '') company = profile_data.get('company', '') location = profile_data.get('location', '') description = profile_data.get('description', '') company_address = profile_data.get('company_address', '') # First, detect the actual company name company_detection_prompt = f""" I need to identify the most likely company name from the following LinkedIn profile data. The company name might be in any of these fields: Company field: "{company}" Description field: "{description}" Location field: "{location}" Analyze all three fields and identify the most likely company name. Return ONLY the company name, nothing else. """ company_detection_response = openai_client.chat.completions.create( model="gpt-4-turbo-preview", messages=[ {"role": "system", "content": "You extract the most likely company name from LinkedIn profile data."}, {"role": "user", "content": company_detection_prompt} ], temperature=0.3 ) detected_company = company_detection_response.choices[0].message.content.strip() # Print the detected company name and address print(f"Original company field: {company}") print(f"Detected company name: {detected_company}") print(f"Company address: {company_address}") system_message = """ You are a professional email writer crafting meeting request templates. IMPORTANT: 1. Always use the recipient's actual first name in the greeting (e.g., "Dear John," not "Dear [Name]") 2. Always specifically mention their company name in the first paragraph 3. Always sign the email with "Best regards,\nAdil" 4. Never use placeholders like [Name] or [CEO's Name] """ prompt = f""" Based on the following professional's information and nearby coffee shops, write a formal email template requesting a meeting: First Name: {first_name} Company: {detected_company} Location: {location} Company Address: {company_address} Description: {description} {shops_text} The email should: 1. Begin with "Dear {first_name}," 2. Be professional and formal 3. Reference their current role at {detected_company} specifically in the first paragraph 4. Suggest meeting at one of the nearby coffee shops (if available) 5. Be concise but personal 6. Include a clear call to action for a meeting 7. End with "Best regards,\nAdil" Write only the email body without additional subject line or formatting. """ response = openai_client.chat.completions.create( model="gpt-4-turbo-preview", messages=[ {"role": "system", "content": system_message}, {"role": "user", "content": prompt} ], temperature=0.7 ) template = response.choices[0].message.content.strip() if "Dear " + first_name not in template: template = f"Dear {first_name},\n\n" + template.split('\n', 1)[1] if '\n' in template else template if "Best regards,\nAdil" not in template: template = template.rsplit('\n', 2)[0] + "\n\nBest regards,\nAdil" # Add the detected company name and address at the top of the email for your reference template = f"[Detected Company: {detected_company}]\n[Company Address: {company_address}]\n\n" + template return template except Exception as e: st.error(f"Error generating email template: {str(e)}") return None def save_email_record(db, profile_data, template): try: email_record = { 'recipient_first_name': profile_data['First Name'], 'recipient_last_name': profile_data['Last Name'], 'recipient_company': profile_data['Company'], 'email_template': template, 'sent_date': datetime.now(), 'status': 'sent' } result = db.sent_emails.insert_one(email_record) return result.inserted_id except Exception as e: st.error(f"Error saving email record: {str(e)}") return None def send_email(template, db, profile_data): try: sender_email = "adilinbox4@gmail.com" sender_password = "pulv dnov zzfg etcg" recipient_email = "adilinbox4@gmail.com" msg = MIMEMultipart() msg['From'] = sender_email msg['To'] = recipient_email msg['Subject'] = "Meeting Request" msg.attach(MIMEText(template, 'plain')) server = smtplib.SMTP('smtp.gmail.com', 587) server.starttls() server.login(sender_email, sender_password) text = msg.as_string() server.sendmail(sender_email, recipient_email, text) server.quit() # Save record to database after successful send record_id = save_email_record(db, profile_data, template) if record_id: # Update profile status in session state for profile in st.session_state.search_results: if (profile['first_name'] == profile_data['First Name'] and profile['last_name'] == profile_data['Last Name'] and profile['company'] == profile_data['Company']): profile['email_status'] = 'Sent' profile['sent_date'] = datetime.now() return True return False except Exception as e: st.error(f"Failed to send email: {str(e)}") return False def main(): st.set_page_config(page_title="LinkedIn Profile Explorer", layout="wide") # Initialize session state variables if 'search_results' not in st.session_state: st.session_state.search_results = None if 'edited_df' not in st.session_state: st.session_state.edited_df = None if 'email_results' not in st.session_state: st.session_state.email_results = [] if 'selected_templates' not in st.session_state: st.session_state.selected_templates = {} if 'templates_generated' not in st.session_state: st.session_state.templates_generated = False if 'edited_templates' not in st.session_state: st.session_state.edited_templates = {} if 'deleted_templates' not in st.session_state: st.session_state.deleted_templates = set() if 'selected_coffee_shops' not in st.session_state: st.session_state.selected_coffee_shops = {} if 'show_templates' not in st.session_state: st.session_state.show_templates = False if 'company_addresses' not in st.session_state: st.session_state.company_addresses = {} # Initialize OpenAI and MongoDB clients openai_client = init_openai() db = init_mongodb() if 'coffee_shop_selections' not in st.session_state: st.session_state.coffee_shop_selections = {} # Add callback function for radio button changes def on_coffee_shop_change(template_key, selected_shop, result): st.session_state.coffee_shop_selections[template_key] = selected_shop current_template = st.session_state.edited_templates.get(template_key, result['Email Template']) if selected_shop != "No specific coffee shop": shop_name = selected_shop.split(" (Rating")[0] shop_address = selected_shop.split(" - ")[-1] current_template = update_template_with_coffee_shop(current_template, shop_name, shop_address) st.session_state.edited_templates[template_key] = current_template # Sidebar st.sidebar.title("LinkedIn Profile Explorer") # Search Interface st.sidebar.header("Search Profiles") search_terms = st.sidebar.text_input("Search Keywords (e.g., Healthcare CEO)") location = st.sidebar.text_input("Location (Optional)") limit = st.sidebar.slider("Number of results", 10, 500, 100) if st.sidebar.button("Search"): with st.spinner("Searching profiles..."): profiles = search_profiles(db, search_terms, location, limit) if profiles: st.session_state.search_results = profiles st.session_state.email_results = [] st.session_state.selected_templates = {} st.session_state.templates_generated = False st.session_state.edited_templates = {} st.session_state.deleted_templates = set() st.session_state.show_templates = False st.session_state.company_addresses = {} else: st.session_state.search_results = None st.warning("No profiles found matching your search criteria.") if st.session_state.search_results: st.title("Search Results") st.success(f"Found {len(st.session_state.search_results)} matching profiles") df = pd.DataFrame(st.session_state.search_results) if not df.empty: display_cols = { 'first_name': 'First Name', 'last_name': 'Last Name', 'description': 'Description', 'company': 'Company', 'location': 'Location', 'url': 'Profile URL', 'email_status': 'Email Status', 'sent_date': 'Sent Date' } df_display = df[display_cols.keys()].rename(columns=display_cols) df_display['Description'] = df_display['Description'].apply( lambda x: x[:100] + '...' if isinstance(x, str) and len(x) > 100 else x ) def format_date(x): try: return x.strftime("%Y-%m-%d %H:%M:%S") if pd.notnull(x) and hasattr(x, 'strftime') else '' except: return '' df_display['Sent Date'] = df_display['Sent Date'].apply(format_date) # Add Select column and set initial values df_display.insert(0, 'Select', False) mask = df_display['Email Status'].fillna('').astype(str) == 'Sent' df_display.loc[mask, 'Select'] = False # Apply conditional styling def highlight_sent_rows(row): if row['Email Status'] == 'Sent': return ['background-color: #4CAF50; color: black'] * len(row) else: return [''] * len(row) styled_df = df_display.style.apply(highlight_sent_rows, axis=1) st.session_state.edited_df = st.data_editor( styled_df, hide_index=True, disabled=list(display_cols.values()), column_config={ "Select": st.column_config.CheckboxColumn( "Select", help="Select profiles to fetch emails", default=False, ), "Email Status": st.column_config.Column( "Email Status", help="Shows if an email has been sent to this profile", width="medium" ), "Sent Date": st.column_config.Column( "Sent Date", help="When the email was sent", width="medium" ) } ) # Generate templates button if st.button("Get Emails and Generate Templates"): selected_profiles = st.session_state.edited_df[st.session_state.edited_df['Select'] == True] if selected_profiles.empty: st.warning("Please select at least one profile") else: st.session_state.show_templates = True progress_placeholder = st.empty() email_results = [] total_profiles = len(selected_profiles) for idx, (i, row) in enumerate(selected_profiles.iterrows()): progress = min(idx / (total_profiles - 1) if total_profiles > 1 else 1.0, 1.0) progress_placeholder.progress(progress) # First, use GPT to detect the company name company_detection_prompt = f""" I need to identify the most likely company name from the following LinkedIn profile data: Company field: "{row['Company']}" Description field: "{row['Description']}" Location field: "{row['Location']}" Analyze all fields and identify the most likely company name. Return ONLY the company name, nothing else. """ company_detection_response = openai_client.chat.completions.create( model="gpt-4-turbo-preview", messages=[ {"role": "system", "content": "You extract the most likely company name from LinkedIn profile data."}, {"role": "user", "content": company_detection_prompt} ], temperature=0.3 ) detected_company = company_detection_response.choices[0].message.content.strip() # Use Selenium to get the company's detailed address with st.spinner(f"Looking up address for {detected_company}..."): location_info = get_location_info(detected_company) company_address = location_info.get("address", "") # Store in session state for reuse key = f"{row['First Name']}_{row['Last Name']}_{row['Company']}" st.session_state.company_addresses[key] = company_address # Get coffee shops near the company address try: with st.spinner(f"Finding coffee shops near {detected_company}..."): coffee_shops = asyncio.run(get_coffee_shops(company_address)) except Exception as e: coffee_shops = [] st.warning(f"Could not fetch coffee shops: {str(e)}") # Generate email template template = generate_email_template( openai_client, { 'first_name': row['First Name'], 'company': row['Company'], 'location': row['Location'], 'description': row['Description'], 'company_address': company_address }, coffee_shops ) result = { 'First Name': row['First Name'], 'Last Name': row['Last Name'], 'Company': row['Company'], 'Detected Company': detected_company, 'Company Address': company_address, 'Email Template': template if template else 'Template generation failed', 'Nearby Coffee Shops': coffee_shops } email_results.append(result) progress_placeholder.empty() st.session_state.email_results = email_results st.session_state.templates_generated = True # Initialize edited templates with generated content for idx, result in enumerate(email_results): template_key = f"template_{idx}" if template_key not in st.session_state.edited_templates: st.session_state.edited_templates[template_key] = result['Email Template'] # Display templates section if st.session_state.show_templates and st.session_state.email_results: st.write("### Select Templates to Send") templates_to_display = [ (idx, result) for idx, result in enumerate(st.session_state.email_results) if f"template_{idx}" not in st.session_state.deleted_templates ] for idx, result in templates_to_display: template_key = f"template_{idx}" with st.expander(f"📧 {result['First Name']} {result['Last Name']} - {result['Company']}", expanded=True): col1, col2, col3 = st.columns([0.2, 1.6, 0.2]) with col1: st.session_state.selected_templates[template_key] = True if result.get('Company Address'): st.info(f"**Company Address:** {result['Company Address']}") with col2: if result.get('Nearby Coffee Shops'): st.write("**Select Coffee Shop for Meeting:**") coffee_shops = result['Nearby Coffee Shops'] coffee_shop_options = [ f"{shop['name']} (Rating: {shop['rating']}) - {shop['address']}" for shop in coffee_shops ] coffee_shop_options.insert(0, "No specific coffee shop") if template_key not in st.session_state.coffee_shop_selections and coffee_shop_options: st.session_state.coffee_shop_selections[template_key] = coffee_shop_options[1] if len(coffee_shop_options) > 1 else coffee_shop_options[0] if coffee_shop_options: selected_shop = st.radio( "Choose a coffee shop:", options=coffee_shop_options, key=f"coffee_shop_{template_key}", index=coffee_shop_options.index(st.session_state.coffee_shop_selections.get(template_key, coffee_shop_options[0])) ) if selected_shop != st.session_state.coffee_shop_selections.get(template_key): on_coffee_shop_change(template_key, selected_shop, result) st.write("**Generated Email Template:**") current_template = st.session_state.edited_templates.get(template_key, result['Email Template']) edited_template = st.text_area( "", value=current_template, height=300, key=f"edit_{template_key}" ) st.session_state.edited_templates[template_key] = edited_template with col3: if st.button("🗑️", key=f"delete_{template_key}"): st.session_state.deleted_templates.add(template_key) st.rerun() if templates_to_display and st.button("Send Selected Templates"): success_count = 0 send_progress = st.progress(0) status_text = st.empty() total_selected = len(templates_to_display) for i, (idx, result) in enumerate(templates_to_display): template_key = f"template_{idx}" template = st.session_state.edited_templates[template_key] profile_data = st.session_state.email_results[idx] if send_email(template, db, profile_data): success_count += 1 st.session_state.deleted_templates.add(template_key) progress = (i + 1) / total_selected send_progress.progress(progress) status_text.text(f"Sending emails: {i + 1}/{total_selected}") send_progress.empty() status_text.empty() if success_count > 0: st.success(f"Successfully sent {success_count} out of {total_selected} templates!") st.rerun() if success_count < total_selected: st.warning(f"Failed to send {total_selected - success_count} templates. Please check the errors above.") st.sidebar.markdown("---") st.sidebar.markdown("### About") st.sidebar.info( "This application allows you to search LinkedIn profiles and generate meeting request templates." ) if __name__ == "__main__": main()