choozmo
/
HHH_Execute


			
				
					
						
						
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162
							'''
E1:執行項目逾期15個工作日者，於逾期15個工作日的上午 9:00 發送逾期通知給CRM、執行單位
以7/12為時間點，7/12起建立的執行表單，預定完成日逾期才會發送通知
'''

import dataset
from datetime import datetime, timedelta

from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.application import MIMEApplication
import os
import boto3
from botocore.exceptions import ClientError

# 寄送逾期執行表單Email通知
def hhh_send_mail(email, html):
    SENDER = "Gorgeous Space <noreply@hhh.com.tw>"
    RECIPIENT = email
    AWS_REGION = "us-east-1"
    CHARSET = "UTF-8"
    client = boto3.client('ses',region_name=AWS_REGION)
    
    try:
        msg = MIMEMultipart()
        # msg["Subject"] = "逾期執行表單通知_" + str(datetime.date.today())
        msg["Subject"] = "逾期執行表單通知_" + (datetime.now() + timedelta(hours=8)).strftime("%Y-%m-%d %H:%M")
        msg["From"] = "noreply@hhh.com.tw"
        msg["To"] = email

        # Set message body
        body = MIMEText(html, "html")
     
        msg.attach(body)

        response = client.send_raw_email(
            Source=msg["From"],
            Destinations=[msg["To"]],
            RawMessage={"Data": msg.as_string()}
        )
        print(response)

    # Display an error if something goes wrong.	
    except ClientError as e:
        print(e.response['Error']['Message'])
    else:
        print("Email sent! Message ID:"),
        print(response['MessageId'])

# Choozmo DB
# db = dataset.connect("mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4")

# 測試機 DB
# db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57-cluster.cluster-cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/stage?charset=utf8mb4')

# 正式機 DB
db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57-cluster.cluster-cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8mb4')

# 超過到期日(contract_time)，尚未結案清單
# statement = "SELECT * FROM `execute_form` WHERE (contract_time < now()) AND is_close='N' AND is_delete='N' AND creator IS NOT NULL AND creator != 'ivan@hhh.com.tw' ORDER BY contract_time"

# 逾期日期起算日
overdue_date = '2021-06-23'

# 所有逾期合約
q1 = "SELECT DISTINCT(ef.exf_id) \
    FROM execute_detail ed \
    left JOIN execute_form ef ON ed.exf_id=ef.exf_id \
    WHERE TIMESTAMPDIFF(DAY, set_date, NOW()) > 15 \
    AND ef.is_close='N' AND ef.is_delete='N' \
    AND ed.is_delete = 'N' AND ed.is_complete='N' \
    AND ed.set_date > '" + overdue_date + "' \
    ORDER BY ef.contract_time, ed.set_date"

# 所有逾期執行項
q2 = "SELECT * \
    FROM execute_detail ed \
    left JOIN execute_form ef ON ed.exf_id=ef.exf_id \
    WHERE TIMESTAMPDIFF(DAY, set_date, NOW()) > 15 \
    AND ef.is_close='N' AND ef.is_delete='N' \
    AND ed.is_delete = 'N' AND ed.is_complete='N' \
    AND ed.set_date > '" + overdue_date + "' \
    ORDER BY ef.contract_time, ed.set_date"

html = """
<!DOCTYPE html>
<head>
    <meta charset="UTF-8">
</head>
<body>
"""

q1_result_count = len(list(db.query(q1)))
q2_result_count = len(list(db.query(q2)))

if q1_result_count > 0: # 有逾期合約
    html += "<div style='color:blue;'><b>逾期合約: 共" + str(q1_result_count) + "筆</b></div>"

if q2_result_count > 0: # 有逾期執行項目
    html += "<div style='color:blue;'><b>逾期執行項目: 共" + str(q2_result_count) + "筆</b><br><br></div>"

    html += "<table style='width:100%;border-collapse: collapse;' border='1'> \
                <tr style='text-align:left;'> \
                    <th style='width:5%;'>#</th> \
                    <th>合約</th> \
                    <th style='width:20%;'>設計公司</th> \
                    <th style='width:15%;'>合約到期日</th> \
                    <th style='width:5%;'>負責業務</th> \
                    <th style='width:20%;'>執行項</th> \
                    <th style='width:15%;'>預定到期日</th> \
                </tr>";

    count = 1

    for r1 in db.query(q1):
        # 所有逾期執行項
        q3 = "SELECT * \
            FROM execute_detail ed \
            left JOIN execute_form ef ON ed.exf_id=ef.exf_id \
            WHERE TIMESTAMPDIFF(DAY, set_date, NOW()) > 15 \
            AND ef.is_close='N' AND ef.is_delete='N' \
            AND ed.is_delete = 'N' AND ed.is_complete='N' \
            AND ed.set_date > '" + overdue_date + "' \
            AND ef.exf_id = '" + str(r1['exf_id']) + "' \
            ORDER BY ef.contract_time, ed.set_date"
        
        q3_result_count = str(len(list(db.query(q3)))+1)

        idx = 0

        for r3 in db.query(q3):
            url = 'https://backstage.hhh.com.tw/admin/home/execute?exf_id=' + str(r3['exf_id'])

            if r3['set_date'] is None:
                r3['set_date'] = '無'

            if idx < 1:
                html += "<tr><td rowspan='" + q3_result_count + "'>" + str(count) + "</td><td rowspan='" + q3_result_count + "'><a href='" + url + "' target='_blank'>" + r3['num'] + "</a></td><td rowspan='" + q3_result_count + "'>" + r3['company'] + "</td><td rowspan='" + q3_result_count + "'>" + str(r3['contract_time']) + "</td><td rowspan='" + q3_result_count + "'>" + r3['sales_man'] + "</td><td>" + r3['lv1'] + "_" + r3['lv2'] + "</td><td>" + str(r3['set_date']) + "</td><tr>"
                
                idx += 1
            else:
                html += "<tr><td>" + r3['lv1'] + "_" + r3['lv2'] + "</td><td>" + str(r3['set_date']) + "</td></tr>"
    
        count += 1

    html += "</table>";
else: # 無逾期執行表單資料
    print ("無逾期執行表單記錄，不須處理")

html += """
</body>
</html>
"""

# Email清單
email_list = ['mike@choozmo.com']

# 寄送逾期執行表單Email通知
for email in email_list:
    hhh_send_mail(email, html)