Email_Tracing email reminder system

1. main functions of the system

  1. Read the contents of the Excel file, determine the responsible person, responsibility time and completion status, and select the responsible person of the project whose completion status is on going and responsibility time is approaching
  2. Process the read list of responsible persons to remove duplicates. If there are multiple responsible persons and they are / split, split them,
  3. For the processed responsible person list, query their mailbox and Position in the Department table, and generate Email - List
  4. Via Email_list send mail to them one by one, and the mail should contain the to-do list
  5. If there is a problem in the system, you need to send an alarm email to the administrator
  6. The logging function should be included to record the system operation information to logging Txt file
  7. All tasks via Email_Tracing_task query
  8. Every Tracing_task is an independent thread. Thread failure does not affect other tracking

2. system difficulties

  1. pandas date comparison: after the deviation date, you can use the size and sign to compare, and cannot compare the date plus or minus with an integer
 delta = datetime.timedelta(days=1)
    # Through loc[] filtering, it is found that the completion status is on going and the current date deviation is greater than the responsibility time one day later. The conditions on both sides should be enclosed in parentheses ().
    try:
        pd_loc1 = pd_excel.loc[(pd_excel["Completion"] == "on going") &
                               (datetime.datetime.now()+delta > pd_excel['Time of responsibility'])]
  1. When reading Excel files, some files are encrypted and some are not encrypted. It is necessary to determine whether the files are encrypted. Otherwise, if the unencrypted files are opened by encryption, an error will be reported.
 with open(path, 'rb') as f:   # Open file with context
            # Judge whether the Excel file is encrypted. If it is not encrypted, read it directly from pandas. The encrypted file is decrypted into memory and then read from pandas.
            if not msoffcrypto.olefile.isOleFile(f):   # mscoffcrypto has the function to judge whether a file is encrypted.
                pd_excel = pd.read_excel(f, sheet_name, header=header)
                logging.info("{title}File read succeeded".format(title=title))
            else:
                xlf = msoffcrypto.OfficeFile(f)   # Open an encrypted file with msoffcrypto. If you open an unencrypted file with msoffcrypto, an error will be reported
                xlf.load_key(password=password)   # Password for incoming file
                xlf.decrypt(decrypted)            # Decrypt and save the file to memory, and define descrypted as io Bytesio(), a common method of msoffcrypto.
                pd_excel = pd.read_excel(decrypted, sheet_name, header=header)  # Open the file saved in memory with pandas.
                # Read the contents of Excel through pandas, specify the file path name and the name of the sheet to be read
                logging.info("{title}File read succeeded".format(title=title))
  1. Read encrypted Excel files with msoffcrypto module
 with open(path, 'rb') as f:   # Open file with context
	 xlf = msoffcrypto.OfficeFile(f)   # Open an encrypted file with msoffcrypto. If you open an unencrypted file with msoffcrypto, an error will be reported
     xlf.load_key(password=password)   # Password for incoming file
     xlf.decrypt(decrypted)            # Decrypt and save the file to memory, and define descrypted as io Bytesio(), a common method of msoffcrypto.
     pd_excel = pd.read_excel(decrypted, sheet_name, header=header)  # Open the file saved in memory with pandas.
     # Read the contents of Excel through pandas, specify the file path name and the name of the sheet to be read
     logging.info("{title}File read succeeded".format(title=title))
  1. Read the contents of Excel files through pandas and import them into the database. The dates in them need special processing
    PD_ Dom['actual completion time'] = pd To_ Datetime (pd_DOM['actual completion time'])
  2. pandas needs the support of xlrd package to read xlsx files. This package must be version 1.2. Versions above 2.0 do not support xlsx file reading. pip install xlrd==1.2.0
  3. To obtain the cleaning of responsible person data, ALL needs to query ALL managers, and there are multiple responsible persons for an event
x = receiver[0].split("/",3)   # Split the responsible person into a list through the split function
receive_list.extend(x)  # Merge the split list with the filtered list
  1. Get the to-do list when sending mail. You can read the list through pandas, and use pandas' to_html directly converts the read data into html format data table
data_list = pd.read_sql(sql_search,conn)  # Read sql database information through pandas
df_html = data_list.to_html(escape=False)  # To via pandas_ The html function converts the query results into html tables.
        
  1. Log files are processed by importing the logging package. Then use logging Info or logging Critical logging Debug and other level messages.
    After pyinstaller runs the exe file, a logging Txt. If it is added to the scheduled task, the log file after execution will be in the folder C:\windows\system32.
logging.basicConfig(format='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s',
                        level=logging.DEBUG, filename='logging.txt')
  1. After the program is packaged as an exe file, it runs normally locally. After it is added to the scheduled task, the log file will prompt that the Excel file cannot be opened. Improvement method:
    Change the access path to access Excel files from Q:\ to \172.24.2.2\groups\meeting-files\

3. system source code

#! /user/bin/python
# author Gang ZHou
# date 2022-05-25
# version 2.0
# Project functions
# 1. read the contents of Excel file, determine the responsible person, responsibility time and completion status, and select the responsible person of the project whose completion status is on going and responsibility time is approaching
# 2. process the read list of responsible persons to remove duplicates. If there are multiple responsible persons and they are / separated, split them,
# 3. for the processed responsible person list, query their Email and Position in the Department table, and generate Email - List
# 4. via Email_list send mail to them one by one, and the mail should contain the to-do list
# 5. if there is a problem in the system, you need to send an alarm email to the administrator
# 6. the logging function should be included to record the system operation information to logging Txt file
# 7. email all tasks_ Tracing_ Task query
# 8. each Tracing_task is an independent thread. Thread failure does not affect other tracking


import pandas as pd
import xlrd          # pandas read_excel default reference
import logging       # log file
from email.mime.text import MIMEText  # Message format
import smtplib       # Send mail
import datetime
import pymssql       # Read mailboxes and task lists in the database
import threading     # Multithreading
import msoffcrypto   # Read encrypted Excel file
import io            # Write file to memory


# Define a function to import Excel files and return the obtained dataFrame data structure
def read_excel(path, sheet_name, title, header=0, password=''):
    decrypted = io.BytesIO()   # It is used to decrypt the read Excel file and write it into memory.
    try:
        with open(path, 'rb') as f:   # Open file with context
            # Judge whether the Excel file is encrypted. If it is not encrypted, read it directly from pandas. The encrypted file is decrypted into memory and then read from pandas.
            if not msoffcrypto.olefile.isOleFile(f):   # mscoffcrypto has the function to judge whether a file is encrypted.
                pd_excel = pd.read_excel(f, sheet_name, header=header)
                logging.info("{title}File read succeeded".format(title=title))
            else:
                xlf = msoffcrypto.OfficeFile(f)   # Open an encrypted file with msoffcrypto. If you open an unencrypted file with msoffcrypto, an error will be reported
                xlf.load_key(password=password)   # Password for incoming file
                xlf.decrypt(decrypted)            # Decrypt and save the file to memory, and define descrypted as io Bytesio(), a common method of msoffcrypto.
                pd_excel = pd.read_excel(decrypted, sheet_name, header=header)  # Open the file saved in memory with pandas.
                # Read the contents of Excel through pandas, specify the file path name and the name of the sheet to be read
                logging.info("{title}File read succeeded".format(title=title))
    except Exception as e:   # If the file fails to open, the error message will be recorded in the log and an email will be sent to the administrator.
        error_msg = "Excel file{title}Failed to open. Please check the file path and permissions.".format(title=title)
        logging.critical(error_msg)
        logging.critical(e)
        send_waring_mail(error_msg, title)
        return
    pd.set_option("display.max_columns", None)   # Set the pandas output to show all columns. Used in auxiliary print printing
    # Convert the data format of responsibility time to datetime type, so that you can participate in practice comparison later
    pd_excel['Time of responsibility'] = pd.to_datetime(pd_excel['Time of responsibility'])
    return pd_excel


# Define a function to query all the items in the Excel file whose status is on going and whose responsibility time is approaching or overdue, and extract the list of responsible persons
def general_user_list(pd_excel, title):
    # The data structure of pandas must include the responsible person, responsible time and completion status
    # Set a time offset where the offset is 1 In other words, if the responsibility time is 2022-05-26, then if today is 2022-05-25 and later, it meets the screening conditions
    delta = datetime.timedelta(days=1)
    # Through loc[] filtering, it is found that the completion status is on going and the current date deviation is greater than the responsibility time one day later. The conditions on both sides should be enclosed in parentheses ().
    try:
        pd_loc1 = pd_excel.loc[(pd_excel["Completion"] == "on going") &
                               (datetime.datetime.now()+delta > pd_excel['Time of responsibility'])]
        # De duplicate the obtained responsible person list and copy it to send_owners
        send_owners = pd_loc1['Responsible person'].drop_duplicates()
    except Exception as e:
        error_msg = "{}right Excel of'Responsible person'Query failed".format(title)
        logging.info(error_msg)
        logging.info(title, ' ', e)
        return

    receive_list_user = []  # Create an empty list to save the filtered data
    # If the responsible person in the cleaning list includes /, the person before and after '/' will be added to the responsible person list as a responsible person respectively.
    for receiver in send_owners:
        if receiver.find('/'):  # If there is / in the responsible person, it means that a piece of data contains multiple responsible persons at the same time,
            x = receiver.split("/", 3)  # Split the responsible person into a list through the split function
            receive_list_user.extend(x)  # Merge the split list with the filtered list
        else:  # Otherwise, users will be directly added to the filter list
            receive_list_user.append(receiver)
    receive_list_user = list(set(receive_list_user))
    print('user_list', receive_list_user)
    logging.info('{title}List of users to send{user_list}'.format(title=title, user_list=receive_list_user))
    return receive_list_user


# Define a function to find the corresponding email address and Position through the responsible person list
def general_email_list(receive_list, title):
    if not receive_list:
        return
    server_name = '172.24.2.54'
    username = 'sa'
    password = 'inalfa@2022'
    try:
        conn = pymssql.connect(server_name, username, password, "FineReport")
    except Exception as e:
        error_msg = "Database connection failed, please check the database connection"
        logging.critical(error_msg)
        logging.critical(e)
        send_waring_mail(error_msg, title)
        return
    cursor = conn.cursor()

    print(receive_list)
    email_list = []  # Create a list to hold key value pairs for user names and mailboxes.
    for user in receive_list:
        # Find the mailbox corresponding to the manager through the manager's name, and save the name and mailbox to email_list.
        sql_mail = "select email,Position from [FineReport].[dbo].[Department] where username='{}'".format(user)
        cursor.execute(sql_mail)
        email = cursor.fetchone()
        if not email:  # If no email information is found in the database, an alarm email is sent to the administrator and a log is generated.
            error_msg = "user{}Please maintain the mailbox table".format(user)
            logging.warning("user{}Please maintain the mailbox table".format(user))
            send_waring_mail(error_msg, title)
            continue
        email_list.append((user, email[0], email[1]), )  # Add data to email_list
    logging.info(email_list)
    cursor.close()
    conn.close()
    print('email_list', email_list)
    logging.info("{title}The list of messages to be sent is:{list}".format(title=title, list=email_list))
    return email_list


# Define a function to send mail to the administrator when there is a problem with the system.
def send_waring_mail(error_msg, title):
    # To create a mailbox connection, the method of sending mail anonymously is adopted. The login name and password are not required. The name of the sending mailbox can be customized and does not need to be created by the exchange server.
    smtp = smtplib.SMTP()
    try:
        smtp.connect('172.26.2.10', 587)
    except Exception as e:
        logging.critical('Mail server connection failed, please check mailbox server and network!')
        logging.critical(e)
        return
    mail_msg = """
    Hello administrator:
        There is a problem with the tracking system. The problem message is as follows:
        {error}
        Please check.<br/>
        corresponding excel The trace file is:{title}
    """.format(error=error_msg, title=title)
    msg = MIMEText(mail_msg, 'html', 'utf-8')
    msg['Subject'] = "DOM Tracing ERROR message"
    msg['From'] = 'CQ_DOM_OIL@inalfa.com'
    try:
        smtp.sendmail('CQ_OIL@inalfa.com', 'gang.zhou@inalfa.com', msg.as_string())
        logging.info("Sending warning message to administrator succeeded.")
    except Exception as e:
        logging.warning("Failed to send mail to the administrator, please check their mailbox configuration!")
        logging.warning(e)


# Define a function to send email and traverse email_list to send an email to all email accounts in the list.
def send_mail(email_list, pd_excel, url, title, owner):
    # To create a mailbox connection, the method of sending mail anonymously is adopted. The login name and password are not required. The name of the sending mailbox can be customized and does not need to be created by the exchange server.
    smtp = smtplib.SMTP()
    try:
        smtp.connect('172.26.2.10', 587)
    except Exception as e:
        logging.critical('Mail server connection failed, please check mailbox server and network!')
        logging.critical(e)
    for user in email_list:
        # The search data list contains the responsible person ALL and ALL the temporary information of the user in the responsible person.
        pd_excel['Time of responsibility'] = pd.to_datetime(pd_excel['Time of responsibility'])
        delta = datetime.timedelta(days=1)
        try:
            pd_loc1 = pd_excel.loc[(pd_excel["Completion"] == "on going") &
                                   (datetime.datetime.now() + delta > pd_excel['Time of responsibility']) &
                                   (pd_excel['Responsible person'].str.contains(user[0]))]
            df_html = pd_loc1.to_html(escape=False)
        except Exception as e:
            logging.info("{}Query responsible person{}Failed to do item information!".format(title, user[0]))
            logging.info(title, ' ', e)
            df_html = ''

        mail_msg = """
        <p style = "font:bold 14px/1em 'Microsoft YaHei '">
        {name} {position},Hello,<br/>
        <span style="color:red">{title}</span> There are projects that you need to track and implement that are approaching or have expired. Please implement and update the status as soon as possible!<br/> Thank you!<br/>
        The trace table path is as follows:
        {URL}
        <p style = "color:Blue;font: bold 14px/1em 'Aril'"></p></p>
        <p style = "color:Grey;font: italic 14px/1em 'Aril'">If you have any questions, please contact{owner},Thank you!</p>
        The detailed list is as follows:
        {df_html}
        <h3>The system will automatically prompt the email, and there is no need to reply!</h3>
        <hr />
        """.format(name=user[0], position=user[2], df_html=df_html, URL=url, title=title, owner=owner)

        msg = MIMEText(mail_msg, 'html', 'utf-8')
        msg['Subject'] = title
        msg['From'] = 'CQ_Daily_Tracing@inalfa.com'
        try:
            smtp.sendmail('CQ_Daily_Tracing@inalfa.com', user[1], msg.as_string())
            logging.info("{}To users{}Mail sent successfully.".format(title, user))
        except Exception as e:
            error_msg = "{}To users{}Failed to send mail, please check its mailbox configuration!".format(title, user)
            logging.warning(error_msg)
            logging.warning(e)
            send_waring_mail(error_msg, title)


# Define a function to encapsulate and send mail, respectively call file reading, generate user list, generate user mailbox list, and then send mail to users through mailbox list
def encapsulation_send_mail(path, sheet_name, header, title, password, owner):
    pd_excel_encap = read_excel(path=path, sheet_name=sheet_name,
                                title=title, header=header, password=password)   # Read Excel file
    receive_list_encap = general_user_list(pd_excel=pd_excel_encap, title=title)    # Generate responsible person list
    email_list_encap = general_email_list(receive_list=receive_list_encap, title=title)  # Generate mailbox list
    # email_list_encap = [('Bo Wang', 'gang.zhou@inalfa.com', 'engineer'), ]   # Test data, can be deleted
    send_mail(email_list=email_list_encap, pd_excel=pd_excel_encap, url=path, title=title, owner=owner)


# Query the data table [email\tracing\u task] one by one,
def read_task():
    server_name = '172.24.2.54'
    username = 'sa'
    password = 'inalfa@2022'
    try:
        conn = pymssql.connect(server_name, username, password, "FineReport")
    except Exception as e:
        error_msg = "Database connection failed, please check the database connection"
        logging.critical(error_msg)
        logging.critical(e)
        send_waring_mail(error_msg, title="hello")
        return
    finally:
        cursor = conn.cursor()
        sql = "select * from dbo.[Email_Tracing_task]"
        cursor.execute(sql)
        task_list = cursor.fetchall()

        for task in task_list:
            print(task)
            title = task[0]
            path = task[1]
            sheet_name = task[2]
            header = task[3]
            password = task[4]
            owner = task[5]
            threading.Thread(target=encapsulation_send_mail,
                             args=(path, sheet_name, header, title, password, owner)).start()
        cursor.close()
        conn.close()


if __name__ == '__main__':
    logging.basicConfig(format='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s',
                        level=logging.DEBUG, filename='logging.txt')
    # path_dom = r"\172.24.2.2\groups\Meeting-File22\DOM meeting minutes \dom meeting tracking table.xlsx"
    # sheetname_dom = "2022"
    # title_dom = "DOM meeting tracking table"
    # threading.Thread(target=encapsulation_send_mail, args=(path_dom, sheetname_dom, 0, title_dom)).start()
    read_task()  # Main program interface

Tags: Python pandas programming language

Posted by tam2000k2 on Mon, 30 May 2022 08:15:11 +0530