# -*- coding:UTF-8 -*-
import sqlalchemy, json, os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from tools import proxy_to_dict, write_xls, send_mail_annex
from sqlalchemy import text
from sqlalchemy import Float

engine = create_engine('mysql://root:hbwjshan@163.COM@124.70.136.117:33065/store_v5?charset=utf8', echo=True)

Session = sessionmaker(bind=engine)

if __name__ == "__main__":
    # sql = text("select * from goods", typemap={'guide_price': Float})
    # 姓名，电话，商品，金额，学校，楼栋，宿舍号，订单创建时间，备注
    sql_admin = text('select * from admin')
    sql_order = '''
    select orders.*,user.* from `order` as orders,user WHERE orders.user_id = user.id
     and TO_DAYS( NOW( ) ) - TO_DAYS( orders.end_time) <= 1 and
      orders.pay_status in (1,2) and from_admin = :from_admin
    '''

    session_admin = Session()  # 每当需要与数据库对话时，都要实例化一个Session
    result_admin = session_admin.execute(sql_admin).fetchall()
    session_admin.close()
    result_list_admin = proxy_to_dict(result_admin)
    title_list = ["姓名", "电话", "商品", "金额", "学校", "楼栋", "宿舍号", "创建时间", "备注"]
    filed_list = ["user_name", "user_phone", "buy_list", "total_price", "school_name", "dorm_build", "dorm_number",
                  "create_time",
                  "mask"]
    for item in result_list_admin:
        from_admin = item.get("id")
        user_name = item.get("admin_name")
        emails = item.get("email")
        if ";" in emails:
            receiver_emails = emails.split(";")
        else:
            receiver_emails = []
            receiver_emails.append(emails)
        session_user = Session()
        result_user = session_user.execute(sql_order, {"from_admin": from_admin}).fetchall()
        if len(result_user) == 0:
            # 当前楼栋没有用户订单  不发邮件
            send_mail_annex(user_name, "", receiver_emails)
        else:
            # 当前楼栋有用户订单
            result_list_user = proxy_to_dict(result_user)
            for user_data in result_list_user:
                product_list = json.loads(json.loads(user_data.get("buy_list")))
                product_infos = ""
                for product in product_list:
                    product_infos += product.get("product_name") + " * " + str(product.get("product_number")) + " ; "
                user_data['buy_list'] = product_infos
            xml_order_name = write_xls(title_list, filed_list, result_list_user, from_admin)
            # 文件写入成功后，发邮件
            send_mail_annex(user_name, xml_order_name, receiver_emails, True)

    # 删除当前产生的excel 文件
    current_dir = os.path.dirname(__file__)
    print(current_dir)
    files = os.listdir(current_dir)
    for file in files:
        if file.endswith(".xls"):
            os.remove(os.path.join(current_dir, file))
