#!/usr/bin/env python
# coding: utf-8

import cx_Oracle
import pandas as pd
import openpyxl
import time
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import os
import datetime
import smtplib

os.environ['MAIL_HOST'] = "smtp.exmail.qq.com"
os.environ['MAIL_USER'] = "dba2@uenpay.com"
os.environ['MAIL_PASS'] = "Payns123"
os.environ['MAIL_SENDER'] = "dba2@uenpay.com"
os.environ['MAIL_TO_RECEIVER'] = "zhengyu@uenpay.com, huangshengyu@uenpay.com"
os.environ['MAIL_CC_RECEIVER'] = "544725571@qq.com, huangshengyu@zhuduan.vip"

# 从环境变量中获取 Oracle 客户端库路径
oracle_lib = os.getenv('oracle_lib')

# 初始化 Oracle 客户端
cx_Oracle.init_oracle_client(lib_dir=oracle_lib)
#cx_Oracle.init_oracle_client(lib_dir="/usr/lib/instantclient") 

# 设置工作目录
os.chdir(os.path.dirname(os.path.abspath(__file__)))

# 数据库连接
def connect_to_db():
    try:
        return cx_Oracle.connect('DBETL', 'DBETL1234', '10.10.21.80:1521/uenbi')
    except cx_Oracle.DatabaseError as e:
        print(f"数据库连接失败: {e}")
        raise

# 获取上个月的日期
def get_last_month():
    today = datetime.date.today()
    first = today.replace(day=1)
    last_month = first - datetime.timedelta(days=1)
    return last_month.strftime("%Y%m")

# 执行 SQL 查询并返回 DataFrame
def fetch_data(sql, connection):
    try:
        return pd.read_sql(sql, connection)
    except Exception as e:
        print(f"数据读取失败: {e}")
        raise

# 发送邮件
def send_email(subject, body, attachment):
    mail_host = os.getenv('MAIL_HOST', 'smtp.exmail.qq.com')
    mail_user = os.getenv('MAIL_USER', 'dba2@uenpay.com')
    mail_pass = os.getenv('MAIL_PASS', 'Payns123')
    sender = os.getenv('MAIL_SENDER', 'dba2@uenpay.com')
    # 默认接收人和抄送人
    default_to_reciver = 'zhengyu@uenpay.com'
    default_cc_reciver = 'zhengyu@zhuduan.vip'
    # 从环境变量获取接收人和抄送人
    to_reciver = os.getenv('MAIL_TO_RECEIVER', default_to_reciver).split(',')
    cc_reciver = os.getenv('MAIL_CC_RECEIVER', default_cc_reciver).split(',')
    receivers = to_reciver + cc_reciver

    message = MIMEMultipart()
    message['From'] = sender
    message['To'] = ';'.join(to_reciver)
    message['Cc'] = ';'.join(cc_reciver)
    message['Subject'] = subject

    message_txt = MIMEText(body, 'plain', 'utf-8')
    message.attach(message_txt)

    with open(attachment, 'rb') as file:
        message_xlsx = MIMEText(file.read(), 'base64', 'utf-8')
        message_xlsx.add_header('Content-Disposition', 'attachment', filename=attachment)
        message.attach(message_xlsx)

    try:
        with smtplib.SMTP(mail_host, 25) as smtpObj:
            smtpObj.login(mail_user, mail_pass)
            smtpObj.sendmail(sender, receivers, message.as_string())
#        print('邮件发送成功')
        return True
    except smtplib.SMTPException as e:
#        print(f'邮件发送失败: {e}')
        return False

def main():
    start_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
#    print('开始执行:', start_time)

    conn_uenbi = connect_to_db()

    last_month_str = get_last_month()
    sql_uenbi = f"""
        SELECT to_char(D.TRADE_DATE,'yyyy-mm') as 月份,
               ORG_code as 机构编号,
               C.ORG_NAME as Q刷一级代理商名称,
               CASE 
                   WHEN D.TRADE_TYPE='10' THEN '优选付'
                   WHEN D.TRADE_TYPE='70' THEN '电签-刷卡'
                   WHEN D.TRADE_TYPE='80' THEN '电签-消费'
                   WHEN D.TRADE_TYPE='66' THEN '云闪付'
                   WHEN D.TRADE_TYPE='43' THEN '普通收款'
                   WHEN D.TRADE_TYPE IN ('23','30') THEN '即时到'
                   WHEN D.TRADE_TYPE='12' THEN 'VIP刷卡'
                   ELSE D.TRADE_TYPE 
               END AS 交易类型,
               ADJUST_FEE as 涨价比例,
               SUM(D.TRADE_AMOUNT) as 参与涨价交易金额,
               SUM(FEE) as 参与涨价交易手续费,
               ROUND(SUM(D.TRADE_AMOUNT * ADJUST_FEE / 100), 2) as 涨价手续费
        FROM uenpay.TB_QS_TRADE_ADJUST_DETAIL@dblk_mpos D
        LEFT JOIN uenpay.POS_TRADE_HIS@dblk_mpos H ON D.TRADE_NO = H.TRADE_NO
        LEFT JOIN uenpay.CORE_ORGANIZATION@dblk_mpos C ON D.TOP_ORG_ID = C.ORG_ID
        WHERE D.TRADE_DATE >= TO_DATE('{last_month_str}-01', 'yyyy-mm-dd')
        AND D.TRADE_DATE < TO_DATE('{last_month_str}-01', 'yyyy-mm-dd') + INTERVAL '1' MONTH
        GROUP BY TOP_ORG_ID, ORG_code, C.ORG_NAME, D.TRADE_TYPE, ADJUST_FEE, TO_CHAR(D.TRADE_DATE, 'yyyy-mm')
    """

    df_uenbi = fetch_data(sql_uenbi, conn_uenbi)

    filename_QS = f"{last_month_str}Q刷月交易汇总.xlsx"
    df_uenbi.to_excel(filename_QS, index=False, header=[
        '月份', '机构编号', 'Q刷一级代理商名称', '交易类型', '涨价比例',
        '参与涨价交易金额', '参与涨价交易手续费', '涨价手续费'
    ])

    email_success = send_email('Q刷调价月汇总数据', '你好，\n附件数据Q刷调价月汇总数据，请注意查收，有问题烦再请及时告知。', filename_QS)

    end_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
#    print('结束执行:', end_time)

    # 输出 Prometheus 格式的结果
    status = 1 if email_success else 0
    print(f'uenpay_senmail_status{{start_time="{start_time}", end_time="{end_time}"}} {status}')

if __name__ == "__main__":
    main()