''' 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 " 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') # 逾期日期起算日 overdue_date = '2021-07-12' print ('發送逾期執行項目通知 (CRM) 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 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" # 所有逾期執行項 (CRM) 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 = """ """ # q1_result_count = len(list(db.query(q1))) q2_result_count = len(list(db.query(q2))) # if q1_result_count > 0: # 有逾期合約 # html += "

逾期合約: 共" + str(q1_result_count) + "筆

" if q2_result_count > 0: # 有逾期執行項目 html += "

逾期執行項目: 共" + str(q2_result_count) + "筆 (由" + overdue_date + "起算)

" html += " \ \ \ \ \ \ \ \ \ "; 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://stage-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 += "" idx += 1 else: html += "" count += 1 html += "

#	合約	設計公司	合約到期日	負責業務	執行項	預定到期日
" + str(count) + "	" + r3['num'] + "	" + r3['company'] + "	" + str(r3['contract_time']) + "	" + r3['sales_man'] + "	" + r3['lv1'] + "_" + r3['lv2'] + "	" + str(r3['set_date']) + "
" + r3['lv1'] + "_" + r3['lv2'] + "	" + str(r3['set_date']) + "

" # else: # 無逾期執行表單資料 # print ("無逾期執行項目記錄，不須處理 (由" + overdue_date + "起算)") # html += "

無逾期執行項目記錄，不須處理 (由" + overdue_date + "起算)

" 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) print ('發送逾期執行項目通知 (CRM) 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: # 所有逾期合約 (執行單位) 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 + "' \ AND ed.execute_man = '" + execute_man + "' \ 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 + "' \ AND ed.execute_man = '" + execute_man + "' \ ORDER BY ef.contract_time, ed.set_date" html = """ """ # q1_result_count = len(list(db.query(q1))) q2_result_count = len(list(db.query(q2))) # if q1_result_count > 0: # 有逾期合約 # html += "

逾期合約: 共" + str(q1_result_count) + "筆

" if q2_result_count > 0: # 有逾期執行項目 # html += "

逾期執行項目: 共" + str(q2_result_count) + "筆

" html += "

" + execute_man + " 逾期執行項目: 共" + str(q2_result_count) + "筆 (由" + overdue_date + "起算)

" html += " \ \ \ \ \ \ \ \ \ "; 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 ed.execute_man = '" + execute_man + "' \ 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://stage-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 += "" idx += 1 else: html += "" count += 1 html += "

#	合約	設計公司	合約到期日	負責業務	執行項	預定到期日
" + str(count) + "	" + r3['num'] + "	" + r3['company'] + "	" + str(r3['contract_time']) + "	" + r3['sales_man'] + "	" + r3['lv1'] + "_" + r3['lv2'] + "	" + str(r3['set_date']) + "
" + r3['lv1'] + "_" + r3['lv2'] + "	" + str(r3['set_date']) + "

"; # else: # 無逾期執行表單資料 # print (execute_man + " 無逾期執行項目記錄，不須處理 (由" + overdue_date + "起算)") # html += "

" + execute_man + " 無逾期執行項目記錄，不須處理 (由" + overdue_date + "起算)

" html += """ """ # 寄送逾期執行表單Email通知 email_list = ['mike@choozmo.com', 'stacy@choozmo.com'] # 寄送逾期執行表單Email通知 for email in email_list: print ("發送Email: " + email) hhh_send_mail(email, html) print ('發送逾期執行項目通知 (執行單位) end...')