is_close_changed_notice.py 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296
  1. from enum import Enum
  2. import time
  3. from datetime import datetime
  4. from typing import Optional
  5. from pydantic import BaseModel
  6. from fastapi import FastAPI, Query, UploadFile, File
  7. from fastapi import FastAPI
  8. from fastapi.middleware.cors import CORSMiddleware
  9. import json
  10. import urllib.request
  11. import dataset,json
  12. import dataset
  13. from datetime import datetime, timedelta
  14. from email.mime.text import MIMEText
  15. from email.mime.image import MIMEImage
  16. from email.mime.multipart import MIMEMultipart
  17. from email.mime.base import MIMEBase
  18. from email.mime.application import MIMEApplication
  19. import os
  20. import boto3
  21. from botocore.exceptions import ClientError
  22. app = FastAPI()
  23. origins = [
  24. "http://139.162.121.30",
  25. "http://139.162.121.30:8002",
  26. ]
  27. app.add_middleware(
  28. CORSMiddleware,
  29. # allow_origins=origins,
  30. allow_origins=["*"],
  31. allow_credentials=True,
  32. allow_methods=["*"],
  33. allow_headers=["*"],
  34. )
  35. # 取得資料庫連線
  36. def get_db_connection():
  37. # Choozmo DB
  38. # db = dataset.connect("mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4")
  39. # 測試機 DB
  40. db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57-cluster.cluster-cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/stage?charset=utf8mb4')
  41. # 正式機 DB
  42. # db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57-cluster.cluster-cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8mb4')
  43. return db
  44. # 寄送逾期執行表單Email通知
  45. def hhh_send_mail(email, subject, html):
  46. SENDER = "Gorgeous Space <noreply@hhh.com.tw>"
  47. RECIPIENT = email
  48. AWS_REGION = "us-east-1"
  49. CHARSET = "UTF-8"
  50. client = boto3.client('ses',region_name=AWS_REGION)
  51. try:
  52. msg = MIMEMultipart()
  53. # msg["Subject"] = "逾期執行表單通知_" + str(datetime.date.today())
  54. msg["Subject"] = subject
  55. msg["From"] = "noreply@hhh.com.tw"
  56. msg["To"] = email
  57. # Set message body
  58. body = MIMEText(html, "html")
  59. msg.attach(body)
  60. response = client.send_raw_email(
  61. Source=msg["From"],
  62. Destinations=[msg["To"]],
  63. RawMessage={"Data": msg.as_string()}
  64. )
  65. print(response)
  66. # Display an error if something goes wrong.
  67. except ClientError as e:
  68. print(e.response['Error']['Message'])
  69. else:
  70. print("Email sent! Message ID:"),
  71. print(response['MessageId'])
  72. '''
  73. 原有狀態:
  74. N: 未結案
  75. Y: 已結案
  76. T: 未上線
  77. C: 轉約 (已刪除)
  78. 新增狀態:
  79. D: 續約
  80. E: 不續約延期
  81. F: 不續約需下線
  82. G: 已到期未續約
  83. '''
  84. @app.get("/is_close_changed_notice")
  85. async def is_close_changed_notice(exf_id: str = '', is_close: str = ''):
  86. # 取得資料庫連線
  87. db = get_db_connection()
  88. subject = ""
  89. # 取得合約資料
  90. q = "SELECT * \
  91. FROM execute_form \
  92. WHERE exf_id = " + exf_id
  93. q_result_count = len(list(db.query(q)))
  94. if q_result_count == 0: # 無合約資料
  95. return
  96. for r in db.query(q):
  97. subject = '[合約狀態修改]:' + r['company'] + ' (合約編號:' + r['num'] + ')_' + datetime.now().strftime("%Y-%m-%d %H:%M")
  98. html = """
  99. <!DOCTYPE html>
  100. <head>
  101. <meta charset="UTF-8">
  102. </head>
  103. <body>
  104. """
  105. if is_close == 'D':
  106. print('修改後狀態: 續約(D)')
  107. html += '<div>本合約狀態已修改為 <span style="color:red;">續約</span></div>'
  108. '''
  109. # 發送通知給負責業務、CRM、Kevin、Sam
  110. email_list = ['sam@hhh.com.tw','kevin.h@hhh.com.tw', 'miko@hhh.com.tw', 'mollie@hhh.com.tw', 'lynn@hhh.com.tw', 'zoe_lo@hhh.com.tw']
  111. # 負責業務
  112. print('負責業務: ' + r['sales_man'])
  113. email_list.append(r['sales_man'])
  114. # 取得執行單位Email清單
  115. q1 = "SELECT DISTINCT(execute_man) \
  116. FROM execute_detail \
  117. WHERE exf_id=" + exf_id
  118. q1_result_count = len(list(db.query(q1)))
  119. if q1_result_count > 0: # 有執行單位Email清單
  120. for r1 in db.query(q1):
  121. if r1['execute_man']:
  122. print('執行單位: ' + r1['execute_man'])
  123. email_list.append(r1['execute_man'])
  124. '''
  125. elif is_close == 'E':
  126. print('修改後狀態: 不續約延期(E)')
  127. html += '<div>本合約狀態已修改為 <span style="color:red;">不續約延期</span></div>'
  128. '''
  129. # 發送通知給負責業務、CRM、Kevin、Sam
  130. # 以變更後的合約日期來發送通知信
  131. email_list = ['sam@hhh.com.tw','kevin.h@hhh.com.tw', 'miko@hhh.com.tw', 'mollie@hhh.com.tw', 'lynn@hhh.com.tw', 'zoe_lo@hhh.com.tw']
  132. # 負責業務
  133. print('負責業務: ' + r['sales_man'])
  134. email_list.append(r['sales_man'])
  135. # 取得執行單位Email清單
  136. q1 = "SELECT DISTINCT(execute_man) \
  137. FROM execute_detail \
  138. WHERE exf_id=" + exf_id
  139. q1_result_count = len(list(db.query(q1)))
  140. if q1_result_count > 0: # 有執行單位Email清單
  141. for r1 in db.query(q1):
  142. if r1['execute_man']:
  143. print('執行單位: ' + r1['execute_man'])
  144. email_list.append(r1['execute_man'])
  145. '''
  146. elif is_close == 'F':
  147. print('修改後狀態: 不續約需下線(F)')
  148. html += '<div>本合約狀態已修改為 <span style="color:red;">不續約需下線</span></div>'
  149. '''
  150. # 發送通知通知給負責業務、CRM、財務部、Kevin、Sam
  151. email_list = ['sam@hhh.com.tw','kevin.h@hhh.com.tw', 'miko@hhh.com.tw', 'mollie@hhh.com.tw', 'lynn@hhh.com.tw', 'zoe_lo@hhh.com.tw', 'ariel@hhh.com.tw']
  152. # 負責業務
  153. print('負責業務: ' + r['sales_man'])
  154. email_list.append(r['sales_man'])
  155. # 取得執行單位Email清單
  156. q1 = "SELECT DISTINCT(execute_man) \
  157. FROM execute_detail \
  158. WHERE exf_id=" + exf_id
  159. q1_result_count = len(list(db.query(q1)))
  160. if q1_result_count > 0: # 有執行單位Email清單
  161. for r1 in db.query(q1):
  162. if r1['execute_man']:
  163. print('執行單位: ' + r1['execute_man'])
  164. email_list.append(r1['execute_man'])
  165. '''
  166. elif is_close == 'G':
  167. print('修改後狀態: 已到期未續約(G)')
  168. html += '<div>本合約狀態已修改為 <span style="color:red;">已到期未續約</span></div>'
  169. '''
  170. # 發送通知給負責業務、Kevin、Sam
  171. email_list = ['sam@hhh.com.tw','kevin.h@hhh.com.tw']
  172. # 負責業務
  173. print('負責業務: ' + r['sales_man'])
  174. email_list.append(r['sales_man'])
  175. '''
  176. # 列出該合約底下所有執行項目(含已完成、未完成)
  177. q1 = "SELECT * \
  178. FROM execute_detail \
  179. WHERE exf_id=" + exf_id + \
  180. " ORDER BY execute_man"
  181. q1_result_count = len(list(db.query(q1)))
  182. if q1_result_count > 0: # 有執行項目
  183. html += "<br><table style='width:100%;border-collapse: collapse;' border='1'> \
  184. <tr style='text-align:left;'> \
  185. <th style='width:5%;'>#</th> \
  186. <th>大項目</th> \
  187. <th>執行項</th> \
  188. <th>單位</th> \
  189. <th>是否完成</th> \
  190. <th>完成人</th> \
  191. <th>備註</th> \
  192. <th>預計排程日期</th> \
  193. <th>第一次提醒日</th> \
  194. </tr>";
  195. idx = 1
  196. for r1 in db.query(q1):
  197. if r1['set_date'] is None:
  198. r1['set_date'] = '無'
  199. if r1['alert_date_1'] is None:
  200. r1['alert_date_1'] = '無'
  201. # 執行單位Email
  202. if r1['execute_man'] == 'hhh_edit@hhh.com.tw':
  203. r1['execute_man'] = '編輯採訪部'
  204. elif r1['execute_man'] == 'hhh_mk@hhh.com.tw':
  205. r1['execute_man'] = '行銷企劃部'
  206. elif r1['execute_man'] == 'hhh_web@hhh.com.tw':
  207. r1['execute_man'] = '網站工程部'
  208. elif r1['execute_man'] == 'hhh_video@hhh.com.tw':
  209. r1['execute_man'] = '影音企劃部'
  210. elif r1['execute_man'] == 'agent@hhh.com.tw':
  211. r1['execute_man'] = '幸福經紀人'
  212. elif r1['execute_man'] == 'hhh_admin@hhh.com.tw':
  213. r1['execute_man'] = '行政部'
  214. elif r1['execute_man'] == 'hhh_sales@hhh.com.tw':
  215. r1['execute_man'] = '業務部'
  216. html += "<tr><td>" + str(idx) + "</td><td>" + r1['lv1'] + "</td><td>" + r1['lv2'] + "</td><td>" + r1['execute_man'] + "</td><td>" + r1['is_complete'] + "</td><td>" + r1['complete_man'] + "</td><td>" + r1['note'] + "</td><td>" + str(r1['set_date']) + "</td><td>" + str(r1['alert_date_1']) + "</td></tr>"
  217. idx += 1
  218. html += "</table>"
  219. else:
  220. print ('修改後狀態: ' + is_close + ',不需通知')
  221. return
  222. html += "<div><br>幸福空間經營團隊敬上<br><br>※此信為系統自動寄送,請勿直接回信。謝謝!</div>"
  223. html += """
  224. </body>
  225. </html>
  226. """
  227. '''
  228. # 寄送逾期執行表單Email通知
  229. for email in email_list: # test
  230. print('寄送Email: ' + email)
  231. '''
  232. # Email清單
  233. email_list = ['mike@choozmo.com', 'stacy@choozmo.com']
  234. # 寄送逾期執行表單Email通知
  235. for email in email_list:
  236. hhh_send_mail(email, html)