import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import psycopg2
import openpyxl
from openpyxl.styles import Alignment, Font
from openpyxl.utils import get_column_letter
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import os

config = {
    'host': '數(shù)據(jù)庫ip',
    'port': 5432,  # PostgreSQL 默認(rèn)端口是5432
    'user': '用戶名',
    'password': '密碼',
    'database': '數(shù)據(jù)庫名'
}
try:
       connection = psycopg2.connect(**config)
       with connection.cursor() as cursor:
         sql_query="""SELECT t.description AS trigger_name, t.priority AS trigger_level, COUNT(e.eventid) AS occurrence_count FROM events e JOIN triggers t ON e.objectid = t.triggerid WHERE e.source = 0 AND e.object = 0 AND e.clock >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '1 WEEK')) GROUP BY t.description, t.priority ORDER BY occurrence_count DESC LIMIT 100;"""
         cursor.execute(sql_query)
         results = cursor.fetchall()
         for row in results:
              excel_data.append([row[0],row[1],row[2],row[3]])
except Exception as e:
       Print(e)

table_rows = ""
    for trigger in trigger_stats:
        table_rows += "<tr style='width:10%;height:10px'><td style='width:8%;border: 1px solid #999;text-align:center;padding: 5px 0;'><span style='background-color: {}; padding: 2px 6px; border-radius: 3px; color:white;'>{}</span></td><td style='width:10%;border: 1px solid #999;text-align:center;padding: 5px 0;color: #f40;'>{}</td><td style='width:10%;border: 1px solid #999;text-align:center;padding: 5px 0;'>{}</td></tr>".format(trigger['color'],trigger['level'],trigger["name"], trigger["count"])

def generate_excel(data):
    # 創(chuàng)建一個(gè)新的工作簿
    wb = openpyxl.Workbook()
    sheet = wb.active
    sheet.title = '告警報(bào)表'
    # 定義表頭
    headers = ['業(yè)務(wù)名稱', 'IP', '告警信息', '告警次數(shù)']
    # 寫入表頭
    for col_idx, header in enumerate(headers, start=1):
        cell = sheet.cell(row=1, column=col_idx)
        cell.value = header
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')
    # 填充數(shù)據(jù)
    for row_idx, row_data in enumerate(data, start=2):
        for col_idx, value in enumerate(row_data, start=1):
            cell = sheet.cell(row=row_idx, column=col_idx)
            cell.value = value
            print(cell.value)
    # 保存工作簿
    excel_file = './alert_data.xlsx'
    wb.save(os.path.basename(excel_file))

# 創(chuàng)建MIMEMultipart對(duì)象
    msg = MIMEMultipart('alternative')
    msg['From'] = sender_email
    msg['To'] = receiver_email
    msg['Subject'] = subject
    # 附加HTML內(nèi)容
    msg.attach(MIMEText(html_content, 'html'))
    generate_excel(excel_data)
    with open('./alert_data.xlsx', 'rb') as attachment:
        part = MIMEBase('application', 'octet-stream')
        part.set_payload(attachment.read())
    encoders.encode_base64(part)
    part.add_header('Content-Disposition', 'attachment; filename="{}"'.format('./alert_data.xlsx'))
    msg.attach(part)
    try:
        # 連接到QQ郵箱的SMTP服務(wù)器
        server = smtplib.SMTP_SSL('smtp.qq.com', 465)
        server.login(sender_email, sender_password)
        server.sendmail(sender_email, receiver_email, msg.as_string())
        print('郵件發(fā)送成功')
    except Exception as e:
        print('郵件發(fā)送失敗:', e)
    finally:
        server.quit()