import datetime import logging import os import smtplib from email.mime.multipart import MIMEMultipart from email.mime.text import MIMEText import pandas as pd import requests from dotenv import load_dotenv from openpyxl.styles import Font, PatternFill, Alignment # Setup logging logging.basicConfig( filename='diavgeia.log', level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s', datefmt='%Y-%m-%d %H:%M:%S' ) load_dotenv() EMAIL_CONFIG = { "sender": os.getenv("EMAIL_SENDER", "price.monitor.email@gmail.com"), "billy": os.getenv("EMAIL_BILLY", "price.monitor.email@gmail.com"), "michalis": os.getenv("EMAIL_MICHALIS", "info@fitnesspro.gr"), "password": os.getenv("EMAIL_PASSWORD") } if not EMAIL_CONFIG["password"]: logging.error("EMAIL_PASSWORD environment variable is required but not set") raise ValueError("EMAIL_PASSWORD environment variable is required but not set") def send_email_with_attachment(subject, html_body, receiver_emails): try: if isinstance(receiver_emails, str): receiver_emails = [receiver_emails] valid_emails = [email for email in receiver_emails if email and email.strip()] if not valid_emails: logging.warning("No valid email addresses provided") return msg = MIMEMultipart() msg['Subject'] = subject msg['From'] = EMAIL_CONFIG["sender"] msg['To'] = ", ".join(valid_emails) msg.attach(MIMEText(html_body, 'html')) with smtplib.SMTP_SSL('smtp.gmail.com', 465) as smtp: smtp.login(EMAIL_CONFIG["sender"], EMAIL_CONFIG["password"]) smtp.send_message(msg, to_addrs=valid_emails) logging.info(f"Email sent successfully to: {', '.join(valid_emails)}") except Exception as e: logging.error(f"Error sending email: {e}") def send_email_error(project_name, error): try: subject = f"❌ ERROR: {project_name}" html_body = f"""

Error in {project_name}

Time: {datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}

Error Details:

{str(error)}

Please check the logs for more details.

""" msg = MIMEMultipart() msg['Subject'] = subject msg['From'] = EMAIL_CONFIG["sender"] msg['To'] = EMAIL_CONFIG["billy"] msg.attach(MIMEText(html_body, 'html')) with smtplib.SMTP_SSL('smtp.gmail.com', 465) as smtp: smtp.login(EMAIL_CONFIG["sender"], EMAIL_CONFIG["password"]) smtp.send_message(msg) logging.info("Error email sent to Billy") except Exception as e: logging.error(f"Failed to send error email: {e}") def load_cpv_codes(filepath='CPV.xlsx'): try: df = pd.read_excel(filepath) cpv_codes = df['CPV'].astype(str).str.strip().tolist() logging.info(f"Loaded {len(cpv_codes)} CPV codes from {filepath}") return cpv_codes except Exception as e: logging.error(f"Error loading CPV codes: {e}") raise def fetch_decisions(cpv_codes, start_date, end_date): page_num = 0 final_data = [] cpv_query = ','.join([f'"{code}"' for code in cpv_codes]) logging.info(f"Starting scrape for dates {start_date} to {end_date}") while True: url = "https://diavgeia.gov.gr/luminapi/opendata/search/advanced.json" params = { "page": page_num, "size": 100, "q": f'cpv:[{cpv_query}] AND issueDate:[DT({start_date}T00:00:00) TO DT({end_date}T23:59:59)]' } try: response = requests.get(url, params=params) response.raise_for_status() data = response.json() actual_size = data.get("info", {}).get("actualSize", 0) logging.info(f"Page {page_num}: {actual_size} decisions found") final_data.extend(data.get("decisions", [])) if actual_size == 0: break page_num += 1 except Exception as e: logging.error(f"Error fetching page {page_num}: {e}") break logging.info(f"Total decisions fetched: {len(final_data)}") return final_data def create_excel_report(decisions, output_filename): report_data = [] for decision in decisions: cpv_codes = [] extra_fields = decision.get('extraFieldValues', {}) decision_type = decision.get('decisionTypeId', '') if decision_type.startswith('Β'): cpv_set = set() for sponsor in extra_fields.get('sponsor', []): cpv_list = sponsor.get('cpv', []) if isinstance(cpv_list, list): cpv_set.update([c for c in cpv_list if c]) elif cpv_list: cpv_set.add(cpv_list) cpv_codes = sorted(list(cpv_set)) else: cpv_codes = [c for c in extra_fields.get('cpv', []) if c] report_data.append({ 'ADA': decision.get('ada', ''), 'Protocol Number': decision.get('protocolNumber', ''), 'Subject': decision.get('subject', ''), 'Issue Date': datetime.datetime.fromtimestamp( decision.get('issueDate', 0) / 1000, datetime.timezone.utc ).strftime('%Y-%m-%d') if decision.get('issueDate') else '', 'Decision Type': decision_type, 'CPV Codes': ', '.join(cpv_codes) if cpv_codes else '', 'Organization ID': decision.get('organizationId', ''), 'URL': f"https://diavgeia.gov.gr/decision/view/{decision.get('ada', '')}", 'Document URL': decision.get('documentUrl', '') }) df = pd.DataFrame(report_data) with pd.ExcelWriter(output_filename, engine='openpyxl') as writer: df.to_excel(writer, sheet_name='Decisions', index=False) workbook = writer.book worksheet = writer.sheets['Decisions'] header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid') header_font = Font(bold=True, color='FFFFFF') for cell in worksheet[1]: cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal='center', vertical='center') for column in worksheet.columns: max_length = 0 column_letter = column[0].column_letter for cell in column: try: if len(str(cell.value)) > max_length: max_length = len(str(cell.value)) except: pass adjusted_width = min(max_length + 2, 80) worksheet.column_dimensions[column_letter].width = adjusted_width logging.info(f"Excel report created: {output_filename} with {len(report_data)} decisions") return report_data def create_email_body(decisions, today_date_formatted): html = f"""

Found {len(decisions)} new decision(s):

""" for i, decision in enumerate(decisions, 1): ada = decision.get('ADA', '') subject = decision.get('Subject', 'No subject') url = decision.get('URL', '') html += f"""

{i}. {subject}

{url}

""" html += """

For more info:

http://66.245.195.15/diavgeia/web_page/index.html

""" return html def main(): logging.info("Starting Diavgeia scraper") try: yesterday_file = f"new_decisions_{(datetime.date.today() - datetime.timedelta(days=1)).isoformat()}.xlsx" if os.path.exists(yesterday_file): os.remove(yesterday_file) logging.info(f"Deleted previous file: {yesterday_file}") cpv_codes = load_cpv_codes('CPV.xlsx') from_date = datetime.date.today().isoformat() to_date = datetime.date.today().isoformat() decisions = fetch_decisions(cpv_codes, from_date, to_date) if not decisions: logging.info("No decisions found\n") return today_formatted = datetime.date.today().strftime('%d-%m-%Y') output_filename = f"new_decisions_{datetime.date.today().isoformat()}.xlsx" report_data = create_excel_report(decisions, output_filename) email_body = create_email_body(report_data, today_formatted) email_subject = f"New Decisions {today_formatted}" send_email_with_attachment( subject=email_subject, html_body=email_body, receiver_emails=[EMAIL_CONFIG["billy"], EMAIL_CONFIG["michalis"]] ) logging.info(f"Scraper completed successfully. Report: {output_filename}\n") except Exception as e: logging.error(f"Fatal error in main execution: {e}", exc_info=True) send_email_error("Diavgeia Project", str(e)) if __name__ == "__main__": main()