choozmo
/
HHH_Execute


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267
							'''
E2:預定完成日期為0000-00-00者，每個月的第1個工作日上午 9:00 發送逾期通知給CRM
'''

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().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')

print ('發送執行項目通知(無預定完成日期) start...')

# 所有合約(無預定完成日期) (CRM)
q1 = "SELECT DISTINCT(ef.exf_id) \
    FROM execute_detail ed \
    left JOIN execute_form ef ON ed.exf_id=ef.exf_id \
    WHERE ef.is_close='N' AND ef.is_delete='N' \
    AND ed.is_delete = 'N' AND ed.is_complete='N' \
    AND ed.set_date = '0000-00-00' \
    ORDER BY ed.execute_man"

# 所有執行項(無預定完成日期) (CRM)
q2 = "SELECT * \
    FROM execute_detail ed \
    left JOIN execute_form ef ON ed.exf_id=ef.exf_id \
    WHERE ef.is_close='N' AND ef.is_delete='N' \
    AND ed.is_delete = 'N' AND ed.is_complete='N' \
    AND ed.set_date = '0000-00-00' \
    ORDER BY ed.execute_man"

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> \
                </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 ef.is_close='N' AND ef.is_delete='N' \
            AND ed.is_delete = 'N' AND ed.is_complete='N' \
            AND ed.set_date = '0000-00-00' \
            AND ef.exf_id = '" + str(r1['exf_id']) + "' \
            ORDER BY ed.execute_man"
        
        q3_result_count = str(len(list(db.query(q3)))+1)

        idx = 0

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

            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><tr>"
                
                idx += 1
            else:
                html += "<tr><td>" + r3['lv1'] + "_" + r3['lv2'] + "</td></tr>"
    
        count += 1

    html += """
        </table>
        </body>
        </html>
        """
    
    # Email清單
    crm_list = ['mike@choozmo.com', 'stacy@choozmo.com', 'oluckyo.mike@gmail.com']
    # crm_list = ['miko@hhh.com.tw', 'mollie@hhh.com.tw', 'lynn@hhh.com.tw', 'zoe_lo@hhh.com.tw']

    # 寄送逾期執行表單Email通知
    for email in crm_list:
        print ("發送Email: " + email)
        hhh_send_mail(email, html)
else: # 沒有未預定完成日期的執行表單資料
    print ("所有執行項目皆有【預定完成日期】，不須處理")
    # html += "所有執行項目皆有【預定完成日期】，不須處理"

print ('發送執行項目通知(無預定完成日期) end...')


print ('\n發送執行項目通知(無預定完成日期) (執行單位) start...')

# 執行單位Email
# hhh_edit@hhh.com.tw: 編輯採訪部
# hhh_mk@hhh.com.tw: 行銷企劃部
# hhh_web@hhh.com.tw: 網站工程部
# hhh_video@hhh.com.tw: 影音企劃部
# agent@hhh.com.tw: 幸福經紀人
# hhh_admin@hhh.com.tw: 行政部
# hhh_sales@hhh.com.tw: 業務部

execute_man_list = ['hhh_edit@hhh.com.tw','hhh_mk@hhh.com.tw','hhh_web@hhh.com.tw','hhh_video@hhh.com.tw','agent@hhh.com.tw','hhh_admin@hhh.com.tw','hhh_sales@hhh.com.tw']

for execute_man in execute_man_list:
    print ("execute_man: " + execute_man)

    # 所有合約(無預定完成日期) (CRM)
    q4 = "SELECT DISTINCT(ef.exf_id) \
        FROM execute_detail ed \
        left JOIN execute_form ef ON ed.exf_id=ef.exf_id \
        WHERE ef.is_close='N' AND ef.is_delete='N' \
        AND ed.is_delete = 'N' AND ed.is_complete='N' \
        AND ed.set_date = '0000-00-00' \
        AND ed.execute_man = '" + execute_man + "'"

    # 所有執行項(無預定完成日期) (CRM)
    q5 = "SELECT * \
        FROM execute_detail ed \
        left JOIN execute_form ef ON ed.exf_id=ef.exf_id \
        WHERE ef.is_close='N' AND ef.is_delete='N' \
        AND ed.is_delete = 'N' AND ed.is_complete='N' \
        AND ed.set_date = '0000-00-00' \
        AND ed.execute_man = '" + execute_man + "'"

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

    q4_result_count = len(list(db.query(q4)))
    q5_result_count = len(list(db.query(q5)))
    
    print ("q4_result_count: " + str(q4_result_count))
    print ("q5_result_count: " + str(q5_result_count))

    if q5_result_count > 0: # 有無預定完成日期執行項目
        # html += "<div style='color:blue;'><b>逾期執行項目: 共" + str(q2_result_count) + "筆</b><br><br></div>"
        html2 += "<div style='color:blue;'><b>" + execute_man + " 無預定完成日期: 共" + str(q5_result_count) + "筆</b><br><br></div>"

        html2 += "<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> \
                    </tr>";

        count = 1

        for r4 in db.query(q4):
            # 特定合約的所有執行項(無預定完成日期)
            q6 = "SELECT * \
                FROM execute_detail ed \
                left JOIN execute_form ef ON ed.exf_id=ef.exf_id \
                WHERE ef.is_close='N' AND ef.is_delete='N' \
                AND ed.is_delete = 'N' AND ed.is_complete='N' \
                AND ed.set_date = '0000-00-00' \
                AND ef.exf_id = '" + str(r4['exf_id']) + "' \
                AND ed.execute_man = '" + execute_man + "'"
            
            q6_result_count = str(len(list(db.query(q6)))+1)

            idx = 0

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

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

        html2 += "</table>";
    else:
        print (execute_man + " 皆有預定完成日期，不須處理")
        #html2 += "<div>" + execute_man + " 皆有預定完成日期，不須處理</div>"
        continue

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

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

    # 寄送無預定完成日期Email通知
    for email in email_list:
        print ("發送Email: " + email)
        hhh_send_mail(email, html2)

print ('發送執行項目通知(無預定完成日期) (執行單位) end...')