choozmo
/
HHH_Execute


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297
							from enum import Enum
import time
from datetime import datetime
from typing import Optional
from pydantic import BaseModel
from fastapi import FastAPI, Query, UploadFile, File
from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware
import json
import urllib.request
import dataset,json

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

app = FastAPI()

origins = [
    "http://139.162.121.30",
    "http://139.162.121.30:8002",
]

app.add_middleware(
    CORSMiddleware,
    # allow_origins=origins,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)


# 取得資料庫連線
def get_db_connection():
    # 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')

    return db


# 寄送合約狀態修改Email通知
def hhh_send_mail(email, subject, 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"] = subject
        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'])


'''
原有狀態:
N: 未結案
Y: 已結案
T: 未上線
C: 轉約 (已刪除)

新增狀態:
D: 續約
E: 不續約延期
F: 不續約需下線
G: 已到期未續約
'''

@app.get("/is_close_changed_notice")
async def is_close_changed_notice(exf_id: str = '', is_close: str = ''):
    # 取得資料庫連線
    db = get_db_connection()

    subject = ""

    # 取得合約資料
    q = "SELECT * \
        FROM execute_form \
        WHERE exf_id = " + exf_id

    q_result_count = len(list(db.query(q)))

    if q_result_count == 0: # 無合約資料
        return

    for r in db.query(q):
        # 判斷合約狀態是否有修改
        if is_close == r['is_close']: # 合約狀態未修改
            print ('合約狀態未修改，不須通知')
            print ('is_close = ' + is_close + ', r[is_close] = ' + r['is_close']) # test
            return
        else: # 合約狀態有修改
            print ('合約狀態有修改，須通知')

            subject = '[合約狀態修改]：' + r['company'] + ' (合約編號：' + r['num'] + ')_' + datetime.now().strftime("%Y-%m-%d %H:%M")

            html = """
                <!DOCTYPE html>
                <head>
                    <meta charset="UTF-8">
                </head>
                <body>
                """
            
            if is_close == 'D':
                print('修改後狀態: 續約(D)')
                html += '<div>本合約狀態已修改為 <span style="color:red;">續約</span></div>'
                
                '''
                # 發送通知給負責業務、CRM、Kevin、Sam
                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']

                #  負責業務
                print('負責業務: ' + r['sales_man'])
                email_list.append(r['sales_man'])

                # 取得執行單位Email清單
                q1 = "SELECT DISTINCT(execute_man) \
                    FROM execute_detail \
                    WHERE exf_id=" + exf_id
                
                q1_result_count = len(list(db.query(q1)))

                if q1_result_count > 0: # 有執行單位Email清單
                    for r1 in db.query(q1):
                        if r1['execute_man']:
                            print('執行單位: ' + r1['execute_man'])
                            email_list.append(r1['execute_man'])
                '''
            elif is_close == 'E':
                print('修改後狀態: 不續約延期(E)')
                html += '<div>本合約狀態已修改為 <span style="color:red;">不續約延期</span></div>'
                
                '''
                # 發送通知給負責業務、CRM、Kevin、Sam
                # 以變更後的合約日期來發送通知信
                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']

                #  負責業務
                print('負責業務: ' + r['sales_man'])
                email_list.append(r['sales_man'])

                # 取得執行單位Email清單
                q1 = "SELECT DISTINCT(execute_man) \
                    FROM execute_detail \
                    WHERE exf_id=" + exf_id
                
                q1_result_count = len(list(db.query(q1)))

                if q1_result_count > 0: # 有執行單位Email清單
                    for r1 in db.query(q1):
                        if r1['execute_man']:
                            print('執行單位: ' + r1['execute_man'])
                            email_list.append(r1['execute_man'])
                '''
            elif is_close == 'F':
                print('修改後狀態: 不續約需下線(F)')
                html += '<div>本合約狀態已修改為 <span style="color:red;">不續約需下線</span></div>'
                
                '''
                # 發送通知通知給負責業務、CRM、財務部、Kevin、Sam
                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', 'acct_team@hhh.com.tw']

                #  負責業務
                print('負責業務: ' + r['sales_man'])
                email_list.append(r['sales_man'])

                # 取得執行單位Email清單
                q1 = "SELECT DISTINCT(execute_man) \
                    FROM execute_detail \
                    WHERE exf_id=" + exf_id
                
                q1_result_count = len(list(db.query(q1)))

                if q1_result_count > 0: # 有執行單位Email清單
                    for r1 in db.query(q1):
                        if r1['execute_man']:
                            print('執行單位: ' + r1['execute_man'])
                            email_list.append(r1['execute_man'])
                '''
            elif is_close == 'G':
                print('修改後狀態: 已到期未續約(G)')
                html += '<div>本合約狀態已修改為 <span style="color:red;">已到期未續約</span></div>'
                
                '''
                # 發送通知給負責業務、Kevin、Sam
                email_list = ['sam@hhh.com.tw','kevin.h@hhh.com.tw']

                # 負責業務
                print('負責業務: ' + r['sales_man'])
                email_list.append(r['sales_man'])
                '''

                # 列出該合約底下所有執行項目（含已完成、未完成）
                q1 = "SELECT * \
                    FROM execute_detail \
                    WHERE exf_id=" + exf_id + \
                    " ORDER BY execute_man"
                
                q1_result_count = len(list(db.query(q1)))

                if q1_result_count > 0: # 有執行項目
                    html += "<br><table style='width:100%;border-collapse: collapse;' border='1'> \
                    <tr style='text-align:left;'> \
                        <th style='width:5%;'>#</th> \
                        <th>大項目</th> \
                        <th>執行項</th> \
                        <th>單位</th> \
                        <th>是否完成</th> \
                        <th>完成人</th> \
                        <th>備註</th> \
                        <th>預計排程日期</th> \
                        <th>第一次提醒日</th> \
                    </tr>";

                    idx = 1

                    for r1 in db.query(q1):
                        if r1['set_date'] is None:
                            r1['set_date'] = '無'
                        
                        if r1['alert_date_1'] is None:
                            r1['alert_date_1'] = '無'
                        
                        # 執行單位Email
                        if r1['execute_man'] == 'hhh_edit@hhh.com.tw':
                            r1['execute_man'] = '編輯採訪部'
                        elif r1['execute_man'] == 'hhh_mk@hhh.com.tw':
                            r1['execute_man'] = '行銷企劃部'
                        elif r1['execute_man'] == 'hhh_web@hhh.com.tw':
                            r1['execute_man'] = '網站工程部'
                        elif r1['execute_man'] == 'hhh_video@hhh.com.tw':
                            r1['execute_man'] = '影音企劃部'
                        elif r1['execute_man'] == 'agent@hhh.com.tw':
                            r1['execute_man'] = '幸福經紀人'
                        elif r1['execute_man'] == 'hhh_admin@hhh.com.tw':
                            r1['execute_man'] = '行政部'
                        elif r1['execute_man'] == 'hhh_sales@hhh.com.tw':
                            r1['execute_man'] = '業務部'
                        
                        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>"

                        idx += 1
                    
                    html += "</table>"
            else:
                print ('修改後狀態: ' + is_close + '，不須通知')
                return

            html += "<div><br>幸福空間經營團隊敬上<br><br>※此信為系統自動寄送，請勿直接回信。謝謝！</div>"

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

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

    # 寄送合約狀態修改Email通知
    for email in email_list:
        hhh_send_mail(email, subject, html)