import pandas as pd import os import smtplib import datetime from email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from datetime import datetime from dotenv import load_dotenv # Load environment variables (from .env) load_dotenv() # Environment Variables EXCEL_SHEET_URL = os.getenv("EXCEL_SHEET_URL") RECIPIENT_EMAILS = os.getenv("RECIPIENT_EMAILS") SENDER_EMAIL = os.getenv("SENDER_EMAIL") SENDER_PASSWORD = os.getenv("SENDER_PASSWORD") # Input file input_file = "urls_status.xlsx" # Output folder output_folder = "uploaded_files" os.makedirs(output_folder, exist_ok=True) # Generate unique output file name current_time = datetime.now().strftime("%Y-%m-%d_%H-%M-%S") output_file = os.path.join(output_folder, f"PCM_URL_{current_time}.xlsx") # Create downloadable link if EXCEL_SHEET_URL.endswith("/"): EXCEL_SHEET_URL_NEW = f"{EXCEL_SHEET_URL}{output_file}" else: EXCEL_SHEET_URL_NEW = f"{EXCEL_SHEET_URL}/{output_file}" print(f"Excel Sheet URL: {EXCEL_SHEET_URL_NEW}") # Load Excel file df = pd.read_excel(input_file) # Ensure required columns exist required_columns = ["url", "Status"] for col in required_columns: if col not in df.columns: raise ValueError(f"❌ Missing required column: '{col}' in {input_file}") # Filter rows where Status contains 'Not Working' not_working_df = df[df["Status"].str.contains("Not Working", case=False, na=False)] # Keep only required columns not_working_df = not_working_df[["url", "Status"]] # Always create output file if not_working_df.empty: print("✅ All URLs are working — no 'Not Working' URLs found.") # ❗ DO NOT UPDATE main Excel file # df["Status"] = "All URLs Working" # df.to_excel(input_file, index=False) # Create output file with message message_df = pd.DataFrame([{"url": "-", "Status": "All URLs Working"}]) message_df.to_excel(output_file, index=False) print(f"📁 File created with message: '{output_file}'") else: not_working_df.to_excel(output_file, index=False) print(f"⚠️ Found {len(not_working_df)} 'Not Working' URLs.") print(f"📁 Saved to '{output_file}'") # Step 2: Send Email Notification if RECIPIENT_EMAILS and SENDER_EMAIL and SENDER_PASSWORD: subject = "Daily PCM URLs Health Checkup Completed" execution_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S') html_body = f"""

🔗 Daily PCM URLs Health Checkup Report

Hello Team,

The daily PCM URLs Health Checkup process has been completed successfully.

Please find the Excel sheet using the link below:

📄 View Excel Sheet

Executed on: {execution_time}
Status: Success

Best regards,
Automated URLs Health Check System (Devtrust)

""" # MIME setup msg = MIMEMultipart("alternative") msg["From"] = SENDER_EMAIL msg["To"] = RECIPIENT_EMAILS msg["Subject"] = subject msg.attach(MIMEText(html_body, "html")) try: with smtplib.SMTP("smtp.gmail.com", 587) as server: server.starttls() server.login(SENDER_EMAIL, SENDER_PASSWORD) server.sendmail(SENDER_EMAIL, RECIPIENT_EMAILS.split(","), msg.as_string()) print("✅ HTML email notification sent successfully!") except Exception as e: print(f"❌ Failed to send email: {e}") else: print("⚠️ Email not sent: Missing credentials or recipient in .env")