kw_tools/joblist/jobcheck.py

import requests
import datetime
from dateutil import parser
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import time
import smtplib
from userdata import *
from email.message import EmailMessage

# drop this at PUBLIC2

# TASK LIST HERE
# https://docs.google.com/spreadsheets/d/153w046qHhpnZTYU7FJ-S2xyGIH_BXbycLYfLI2fFsZc/edit#gid=0

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('choozmointernal-2e314f3d4e12.json', scope)
client = gspread.authorize(creds)
message = "提醒近期預定執行的作業如下：\n\n"
message2 = "提醒近期需完成的作業如下：\n\n"
message3 = "提醒以下已取消的作業需填寫取消原因：\n\n"

def notify_group(msg):
    gid='WekCRfnAirSiSxALiD6gcm0B56EejsoK89zFbIaiZQD'
    headers = {"Authorization": "Bearer " + gid,"Content-Type": "application/x-www-form-urlencoded"}
    r = requests.post("https://notify-api.line.me/api/notify",headers=headers, params={"message": msg})

def send_line(messages):
    for message in messages:
        print(len(message))
        if(len(message)>55):
            msg = [message[i:i+1000] for i in range(0, len(message), 1000)]
            for m in msg:
                notify_group(m)
                #print(m)

def send_email(emails, unstarted, incomplete, canceled, has_unstarted, has_incomplete, has_canceled):
    server = smtplib.SMTP_SSL('smtp.gmail.com', 465)
    server.ehlo()
    server.login(gmail_username, gmail_password)

    for name in emails:
        msg = EmailMessage()
        msg['Subject'] = '執行表單過期作業通知' + str(datetime.date.today())
        msg['From'] = "service@choozmo.com"
        msg['To'] = emails[name]

        message = name + "您好，\n\n以下通知您目前作業現況，更新執行表單請至 https://cmm.ai/pm ，謝謝您。\n\n"

        if has_unstarted[name] == 0 and has_overdue[name] == 0:
            message += "您目前沒有過期的作業。\n\n"
        else:
            if has_unstarted[name] == 1:
                message += unstarted[name]
            if has_incomplete[name] == 1:
                message += incomplete[name]
        
        if has_canceled[name] == 1:
            message += canceled[name]

        msg.set_content(message)
        print ('Sending email to ' + msg['To'])
        server.send_message(msg)
        print ('Email sent to ' + msg['To'])
        fname = name+'.txt'
        with open(fname, 'w', encoding="UTF+8") as f:
            f.write(message)

    server.close()

sheet = client.open('ChoozMo 專案執行表單_20220720')
sheet_instance = sheet.get_worksheet(0)

records_data = sheet_instance.get_all_records()
#print(records_data[0])

emails = {"Jared": "jared@choozmo.com", "Morrison": "morrison@choozmo.com", "Doris": "doris@choozmo.com", "Nina": "nina.huang@choozmo.com", "Yukyo": "yukyo@choozmo.com", "Andy": "andy@choozmo.com", "Oak": "oak@choozmo.com", "Zooey": "zooey@choozmo.com", "Yuri": "syuanyuri@choozmo.com", "Wizer": "wizer@choozmo.com", "Jason": "jason@choozmo.com", "Tomoya": "tomoya@choozmo.com", "詠心": "noodlesloves@gmail.com", "献竤": "fxp87257@gmail.com"}

overdue_notstarted_content = {}
overdue_incomplete_content = {}
canceled_content = {}
has_unstarted = {}
has_overdue = {}
has_canceled = {}
status = [0,0,0]

for e in emails.keys():
    overdue_notstarted_content[e] = "您有已過期而尚未執行的作業，請儘快執行並完成這些作業：\n\n"
    overdue_incomplete_content[e] = "您有已過期而未完成的作業，請儘快完成這些作業：\n\n"
    canceled_content[e] = "此外，以下已取消執行的作業請在備註欄位填寫取消原因：\n\n"
    has_unstarted[e] = 0
    has_overdue[e] = 0
    has_canceled[e] = 0

for data in records_data:
    try:
        if(data["完成度"] == "取消"):
            status[2] = 1
            info += "專案：" + data["專案"] + " / " + data["專案項目"] + "\n"
            info += "作業：" + data["執行項目"] + "\n"
            info += "執行人員：" + data["指派對象"] + "\n"
            info += "PM：" + data["PM"] + "\n\n"
            canceled_content[data["PM"]] += info
            has_canceled[data["PM"]] = 1
            if [data["PM"]]!=[data["指派對象"]]: #sometimes you have to do the task and you are the PM
                canceled_content[data["指派對象"]] += info
                has_canceled[data["指派對象"]] = 1
            if data["PM"] != "Jared": #sometimes Jared is the PM
                canceled_content["Jared"] += info
                has_canceled["Jared"] = 1
            message3 += info
        else:
            print(parser.parse(data["起始日"]), parser.parse(data["完成日"]))
            deltastart = (parser.parse(data["起始日"]).date()-datetime.date.today()).days
            deltaend = (parser.parse(data["完成日"]).date()-datetime.date.today()).days
            print(deltastart, deltaend)
            info = ""

            print("Project: " + data["專案"] + "Note: " + data["備註"])

            if(deltastart <= 7 and data["完成度"] == "預定執行"):
                status[0] = 1
                info += "專案：" + data["專案"] + " / " + data["專案項目"] + "\n"
                info += "作業：" + data["執行項目"] + "\n"
                info += "執行人員：" + data["指派對象"] + "\n"
                info += "PM：" + data["PM"] + "\n"
                info += "預定執行期間：" + data["起始日"] + "-" + data["完成日"]
                if(deltastart>0):
                    info += " (尚有"+ str(deltastart) + "天)\n\n"
                elif(deltastart==0):
                    info += " (當天開始執行)\n\n"
                else:
                    info += " (過期"+ str(deltastart).replace('-','') + "天)\n\n"
                    overdue_notstarted_content[data["PM"]] += info
                    has_unstarted[data["PM"]] = 1
                    if [data["PM"]]!=[data["指派對象"]]: #sometimes you have to do the task and you are the PM
                        overdue_notstarted_content[data["指派對象"]] += info
                        has_unstarted[data["指派對象"]] = 1
                    if data["PM"] != "Jared": #sometimes Jared is the PM
                        overdue_notstarted_content["Jared"] += info
                        has_unstarted["Jared"] = 1
                message += info
            elif(deltaend <= 7 and data["完成度"] == "執行中"):
                status[1] = 1
                info += "專案：" + data["專案"] + " / " + data["專案項目"] + "\n"
                info += "作業：" + data["執行項目"] + "\n"
                info += "執行人員：" + data["指派對象"] + "\n"
                info += "PM：" + data["PM"] + "\n"
                info += "完成日：" + data["完成日"]
                if(deltaend>0):
                    info += " (尚有"+ str(deltaend) + "天)\n\n"
                elif(deltaend==0):
                    info += " (當天完成)\n\n"
                else:
                    info += " (過期"+ str(deltaend).replace('-','') + "天)\n\n"
                    overdue_incomplete_content[data["PM"]] += info
                    has_overdue[data["PM"]] = 1
                    if [data["PM"]]!=[data["指派對象"]]: #sometimes you have to do the task and you are the PM
                        overdue_incomplete_content[data["指派對象"]] += info
                        has_overdue[data["指派對象"]] = 1
                    if data["PM"] != "Jared": #sometimes Jared is the PM
                        overdue_incomplete_content["Jared"] += info
                        has_overdue["Jared"] = 1
                message2 += info

    except:
        print("PASS")

message += "更新執行表單請至 https://cmm.ai/pm"
message2 += "更新執行表單請至 https://cmm.ai/pm"
message3 += "更新執行表單請至 https://cmm.ai/pm"

messages = []
messages.append(message)
messages.append(message2)
messages.append(message3)

print(has_unstarted)
print(has_overdue)
print(has_canceled)
print(status)

fname="msg1.txt"
with open(fname, 'w', encoding="UTF+8") as f:
    f.write(message)

fname="msg2.txt"
with open(fname, 'w', encoding="UTF+8") as f:
    f.write(message2)

fname="msg3.txt"
with open(fname, 'w', encoding="UTF+8") as f:
    f.write(message3)

send_line(messages)
send_email(emails, overdue_notstarted_content, overdue_incomplete_content, canceled_content, has_unstarted, has_overdue, has_canceled)

'''notify_group(message)
time.sleep(5)
notify_group(message2)'''