引言

在日常工作中，團隊的績效監控和管理是確保項目順利進行的重要環節。然而，面臨著以下問題：

• ?數據分散：系統中的數據難以匯總，缺乏一個宏觀的團隊執行情況視圖。
• ?看板缺失：系統本身可能無法提供合適的Dashboard，導致數據分析困難。
• ?手動操作繁瑣：數據采集、匯總和分析過程繁瑣且耗時。

本文將介紹如何利用免費的軟件和工具（如 Python、MySQL、Excel 等）實現績效看板的自動化。通過 ?郵件自動推送和接收? 實現數據采集，結合 MySQL 數據庫的沉淀和計算，最終在 Excel 中實現數據的定時刷新和展示。

---

先來看效果， 需要知道整體團隊的進度，團隊的進度要通過自動化郵件發送接收，并且每半個小時更新數據。

---

環境要求

為了實現本項目的自動化流程，以下是所需的軟件和工具：

1. ?Python 3.x：

   • 用于自動采集、推送、接收數據，并將數據存儲到 MySQL 數據庫。
   • 安裝地址：Python 官網（免費開源）

2. ?MySQL 數據庫：

   • 用于存儲績效數據，并生成視圖供 Excel 使用。
   • 安裝地址：MySQL 官網（免費開源）

3. ?Excel：

   • 用于制作績效看板，并通過 ODBC 連接 MySQL 數據庫。
   • 安裝地址：Microsoft Office 官網（免費試用或開源替代方案如 LibreOffice）

4. ?ODBC 驅動程序：

   • 用于連接 MySQL 數據庫和 Excel。
   • 安裝地址：MySQL ODBC Connector（免費開源）

5. ?郵件客戶端（如 Outlook）?：

   • 用于自動發送和接收郵件。
   • 安裝地址：Microsoft Outlook（免費試用或使用免費開源郵件客戶端如 Thunderbird）

6. ?Python 庫：

   • pandas：用于數據處理。
   • mysql-connector-python：用于連接 MySQL 數據庫。
   • schedule：用于定時任務。
   • imaplib?和?email：用于處理郵件接收。
   • win32com：用于操作 Outlook。
   • 安裝命令：

     bash

     pip install pandas mysql-connector-python schedule

我們可以利用免費的軟件和工具（如 Python、MySQL、Excel 等）實現績效看板的自動化。

整體流程：

1. ?數據采集：

   • 自動采集：通過 Python 定時任務采集數據。
   • 自動推送：通過 Python 調用 Outlook 發送郵件。
   • 自動接收：通過 Python 接收郵件并提取數據。

2. ?數據匯總與存儲：

   • 使用 Python 將數據存儲到 MySQL 數據庫。
   • 利用 MySQL 的 SQL 功能生成視圖。

3. ?數據計算與整理：

   • 在 MySQL 數據庫中對數據進行計算和整理。

4. ?可視化看板制作：

   • 在 Excel 中通過 ODBC 連接 MySQL 數據庫，制作績效看板。

5. ?定時刷新：

   • 使用 Excel 的 VBA 宏實現定時刷新數據。

這種方法不僅合法且免費，還能顯著提高團隊的績效管理效率。

流程步驟

為了實現 Excel 績效看板的自動化，整個流程可以分為以下幾個步驟：

---

?1. 數據采集

?1.1 python定時任務

• 使用 Python 定時任務從系統 API、文件或其他外部數據源中采集績效數據。
• 數據采集可以通過定時任務（如?schedule?庫）自動執行。

示例代碼：?

代碼功能概述

這段 Python 代碼的主要功能是通過定時任務調度（使用?schedule?庫）來執行一系列的批處理文件（.bat?文件），并對系統窗口進行管理和監控。以下是代碼的主要功能模塊：

1. ?窗口管理：

   • 檢查系統中是否存在特定標題的窗口。
   • 關閉特定標題的窗口。

2. ?定時任務調度：

   • 使用?schedule?庫設置定時任務，在指定時間運行特定的批處理文件。

3. ?批處理文件執行：

   • 使用?subprocess.Popen?執行批處理文件，并在新控制臺中運行。

4. ?日期檢查：

   • 檢查當前日期是否符合特定條件，以決定是否運行某些任務。

5. ?主循環：

   • 持續運行調度器，檢查是否有任務需要執行。

python schedule.py

import calendar
import time
from datetime import datetime, timedelta
import schedule
import os
import subprocess
import win32gui
import win32con
path_newfssc = r'D:\\FsscProject\\lsh\\newfssc\\'findtxt = 'C:\Windows\System32\cmd.exe'
findtxt2 = 'C:\WINDOWS\system32\cmd.exe'
def check_window():hd = win32gui.GetDesktopWindow()# 獲取所有子窗口hwndChildList = []#   EnumChildWindows 為指定的父窗口枚舉子窗口win32gui.EnumChildWindows(hd, lambda hwnd, param: param.append(hwnd), hwndChildList)for hwnd in hwndChildList:title = win32gui.GetWindowText(hwnd)find = False#        print("句柄：", hwnd, "標題：", win32gui.GetWindowText(hwnd))if findtxt in title:find = Trueif findtxt2 in title:find = Trueif find == True:return Truereturn False
def close_window():hd = win32gui.GetDesktopWindow()# 獲取所有子窗口hwndChildList = []#   EnumChildWindows 為指定的父窗口枚舉子窗口win32gui.EnumChildWindows(hd, lambda hwnd, param: param.append(hwnd), hwndChildList)for hwnd in hwndChildList:title = win32gui.GetWindowText(hwnd)find = False
#        print("句柄：", hwnd, "標題：", win32gui.GetWindowText(hwnd))if findtxt in title:find = Trueif findtxt2 in title:find = Trueif 'Chromium' in title:find = Trueif find == True:try:print("special句柄：", hwnd, "標題：", win32gui.GetWindowText(hwnd))win32gui.PostMessage(hwnd, win32con.WM_CLOSE, 0, 0)except:print("can not erase window")def TrService():print_time('dashboard.py is running at ')p = subprocess.Popen(r'd:\\dashboard.bat',creationflags=subprocess.CREATE_NEW_CONSOLE)# schedule.every().day.at('09:00').do(TrService)
# schedule.every().day.at('10:00').do(TrService)
# schedule.every().day.at('11:00').do(TrService)
# schedule.every().day.at('12:00').do(TrService)
# schedule.every().day.at('13:00').do(TrService)
# schedule.every().day.at('14:00').do(TrService)
# schedule.every().day.at('15:00').do(TrService)
# schedule.every().day.at('16:00').do(TrService)
# schedule.every().day.at('17:00').do(TrService)print_time('SCHEDULE is running at ')
while True:schedule.run_pending()time.sleep(1)

---

?1.2 dashboard.bat 執行腳本

腳本必須使用另外的 CMD 執行，因為 python schedule.py 會占用一個 cmd, 所以這個 schedule 的設計很重要！每次執行時 ，通過 bat 腳本新增 cmd 視窗執行 另外的python。

python D:\FsscProject\lsh\dashbaord\dashboard.py

1.3 自動推送

• 當bat腳本執行時，使用 Python 通過郵件將績效數據發送到指定郵箱。
• 郵件內容可以包含附件（如 excel?文件）

示例代碼：?

python

import datetime
import time
import smtplib
import win32com.client
import schedule
import pymysql
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from prettytable import PrettyTable
import pandas as pd
import ospath = r'D:\\dashboard\\'
time_obj = datetime.datetime.now()
str_time = time_obj.strftime('%Y%m%d %H%M')
excelName_wl = 'wl' + str_time + '.xlsx'
att_file_wl = os.path.join(path, excelName_wl)
excelName_11 = 'nd1100' + str_time + '.xlsx'
att_file_11 = os.path.join(path, excelName_11)def getdkList():try:conn = getECSPrdMysqlConn()sql = " SELECT * FROM v_db_wl; "df = pd.read_sql(sql, conn)df.to_excel(att_file_wl, na_rep='', index=False)sql11 = " SELECT * FROM v_nd1100_open_list; "df11 = pd.read_sql(sql11, conn)df11.to_excel(att_file_11, na_rep='', index=False)except Exception as e:print('處理失敗：', e)print(e)finally:conn.close()to_attr = 'fssc-mis.autocn@lsh.com'subject = 'nd1100監測數據報告'msg= """尊敬的用戶您好：
Dashboard監控數據統計如下，如有異常，詳細明細請參考附件。系統郵件,請勿回復Best regards """try:outlook = win32com.client.Dispatch('outlook.application')for account in outlook.Session.Accounts:# 賦值發件賬戶send_account = accountbreakmail = outlook.CreateItem(0)mail._oleobj_.Invoke(*(64209, 0, 8, 0, send_account))mail.To = to_attrmail.Subject = subjectmail.Body = msgmail.Attachments.Add(att_file_wl)mail.Attachments.Add(att_file_11)mail.sendprint('發送郵件:' + subject)# print('收件人:' + item[2] + ' | 被抄送人:' + item[3])# send_email(to_rep,to_cc, subjectxt, msg, file_excel_name)print('執行完成')print("------------------done----------------")except smtplib.SMTPException as e:print(e)print("Error: 無法發送郵件")def getECSPrdMysqlConn():"""獲取：RPA數據庫連接:return:"""strHost = ''# strHost = 'xxxx'strPort = 3306strUserName = 'xxxx'strPassWord = 'xxxx'strdb = 'ecs_fssc'retry_count = 1000000init_connect_count = 0connect_res = Truewhile connect_res and init_connect_count < retry_count:try:conn = pymysql.connect(host=strHost, port=strPort, db=strdb, user=strUserName, password=strPassWord, charset="utf8")connect_res = Falseexcept pymysql.Error as e:init_connect_count += 1print("第：" + str(init_connect_count) + "次數據庫連接失敗，嘗試重連...，錯誤信息：{0}".format(e))return conngetdkList()

代碼功能概述

這段 Python 代碼的主要功能是從 MySQL 數據庫中提取數據，將數據保存為 Excel 文件，并通過 Outlook 發送包含這些 Excel 文件的郵件。以下是代碼的主要功能模塊：

1. ?數據庫連接與數據提取：

   • 使用?pymysql?連接到 MySQL 數據庫。
   • 執行 SQL 查詢，將查詢結果保存為 Excel 文件。

2. ?郵件發送：

   • 使用?win32com.client?操作 Outlook 應用程序。
   • 構造郵件內容，并附加生成的 Excel 文件。

3. ?定時任務（未啟用）?：

   • 代碼中未啟用定時任務，但可以通過?schedule?庫實現定時運行。

4. ?錯誤處理：

   • 捕獲數據庫連接和郵件發送中的異常，并打印錯誤信息。

---

?代碼詳細解析

?1. 導入模塊

python

import datetime
import time
import smtplib
import win32com.client
import schedule
import pymysql
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from prettytable import PrettyTable
import pandas as pd
import os

• ?**datetime?和?time**：用于處理日期和時間。
• ?**smtplib**：用于發送郵件（未在代碼中使用）。
• ?**win32com.client**：用于操作 Windows 系統的 Outlook 應用程序。
• ?**schedule**：用于定時任務調度（未在代碼中使用）。
• ?**pymysql**：用于連接 MySQL 數據庫。
• ?**email.mime**：用于構造郵件內容（未在代碼中使用）。
• ?**prettytable**：用于生成表格（未在代碼中使用）。
• ?**pandas**：用于處理和保存數據到 Excel 文件。
• ?**os**：用于文件路徑操作。

---

?2. 定義路徑和文件名

python

path = r'D:\\dashboard\\'
time_obj = datetime.datetime.now()
str_time = time_obj.strftime('%Y%m%d %H%M')
excelName_wl = 'wl' + str_time + '.xlsx'
att_file_wl = os.path.join(path, excelName_wl)
excelName_11 = 'nd1100' + str_time + '.xlsx'
att_file_11 = os.path.join(path, excelName_11)

• ?**path**：定義 Excel 文件的存儲路徑。
• ?**time_obj**：獲取當前時間。
• ?**str_time**：將當前時間格式化為?YYYYMMDD HHMM?的字符串。
• ?**excelName_wl?和?excelName_11**：定義兩個 Excel 文件的名稱，包含時間戳。
• ?**att_file_wl?和?att_file_11**：定義兩個 Excel 文件的完整路徑。

---

?3. 數據庫連接函數

python

def getECSPrdMysqlConn():"""獲取：RPA數據庫連接:return:"""strHost = ''strPort = 3306strUserName = 'xxxx'strPassWord = 'xxxx'strdb = 'ecs_fssc'retry_count = 1000000init_connect_count = 0connect_res = Truewhile connect_res and init_connect_count < retry_count:try:conn = pymysql.connect(host=strHost, port=strPort, db=strdb, user=strUserName, password=strPassWord, charset="utf8")connect_res = Falseexcept pymysql.Error as e:init_connect_count += 1print("第：" + str(init_connect_count) + "次數據庫連接失敗，嘗試重連...，錯誤信息：{0}".format(e))return conn

• ?功能：連接到 MySQL 數據庫。
• ?實現：
  • 使用?pymysql.connect?方法連接數據庫。
  • 如果連接失敗，會重試最多?retry_count?次。
• ?返回值：返回數據庫連接對象。

---

?4. 數據提取與保存

python

def getdkList():try:conn = getECSPrdMysqlConn()sql = " SELECT * FROM v_db_wl; "df = pd.read_sql(sql, conn)df.to_excel(att_file_wl, na_rep='', index=False)sql11 = " SELECT * FROM v_nd1100_open_list; "df11 = pd.read_sql(sql11, conn)df11.to_excel(att_file_11, na_rep='', index=False)except Exception as e:print('處理失敗：', e)print(e)finally:conn.close()

• ?功能：從數據庫中提取數據并保存為 Excel 文件。
• ?實現：
  1. 調用?getECSPrdMysqlConn?獲取數據庫連接。
  2. 執行 SQL 查詢，將結果保存為 Pandas 數據框（DataFrame）。
  3. 使用?to_excel?方法將數據框保存為 Excel 文件。
  4. 如果發生異常，打印錯誤信息。
  5. 無論是否發生異常，最終都會關閉數據庫連接。

---

?5. 郵件發送

python

    to_attr = 'fssc-mis.autocn@lsh.com'subject = 'nd1100監測數據報告'msg= """尊敬的用戶您好：
Dashboard監控數據統計如下，如有異常，詳細明細請參考附件。系統郵件,請勿回復Best regards """try:outlook = win32com.client.Dispatch('outlook.application')for account in outlook.Session.Accounts:send_account = accountbreakmail = outlook.CreateItem(0)mail._oleobj_.Invoke(*(64209, 0, 8, 0, send_account))mail.To = to_attrmail.Subject = subjectmail.Body = msgmail.Attachments.Add(att_file_wl)mail.Attachments.Add(att_file_11)mail.sendprint('發送郵件:' + subject)except smtplib.SMTPException as e:print(e)print("Error: 無法發送郵件")

• ?功能：通過 Outlook 發送郵件，并附加生成的 Excel 文件。
• ?實現：
  1. 定義收件人郵箱地址和郵件主題。
  2. 使用?win32com.client.Dispatch?創建 Outlook 應用程序對象。
  3. 遍歷 Outlook 的賬戶列表，選擇第一個賬戶作為發件賬戶。
  4. 創建郵件對象，設置收件人、主題和正文。
  5. 使用?Attachments.Add?方法附加 Excel 文件。
  6. 調用?mail.send?發送郵件。
  7. 如果發生異常，打印錯誤信息。

---

?6. 主程序

python

getdkList()

• ?功能：調用?getdkList?函數，執行數據提取和郵件發送的完整流程。

---

?1.4?績效數據提取

在python代碼中分別有兩段sql 語句，獲取績效看板所需要的

? ? ? ? sql = " SELECT * FROM v_db_wl; "
? ? ? ? sql11 = " SELECT * FROM v_nd1100_open_list; "

工作量數據

解釋如下

CREATE VIEWv_db_wl(審單日,業務類型,審單人,單量) AS
SELECT`a`.`審單日`  AS `審單日`,`a`.`業務類型` AS `業務類型`,`a`.`審單人`  AS `審單人`,COUNT(1)   AS `單量`
FROM`v_dash_wl_nd1100` `a`
WHERE(`a`.`審單日` >= (curdate() - interval 31 DAY))
GROUP BY`a`.`審單日`,`a`.`業務類型`,
`a`.`審單人`;

?說明

這段 SQL 代碼的目的是創建一個名為?v_db_wl?的視圖（View），用于統計過去 31 天內的審單數據。視圖是一個虛擬表，基于 SQL 查詢的結果集，用戶可以像操作普通表一樣操作視圖。

以下是代碼的詳細解析：

---

?1. 創建視圖的語法

sql

CREATE VIEW v_db_wl (審單日,業務類型,審單人,單量
) AS

• ?**CREATE VIEW**：用于創建視圖。
• ?**v_db_wl**：視圖的名稱。
• ?**(審單日, 業務類型, 審單人, 單量)**：定義視圖的列名。
• ?**AS**：表示視圖的內容由后續的?SELECT?查詢定義。

---

?2. 查詢語句

sql

SELECT`a`.`審單日`  AS `審單日`,`a`.`業務類型` AS `業務類型`,`a`.`審單人`  AS `審單人`,COUNT(1)   AS `單量`

• ?**SELECT**：從表中選擇數據。
• ?**a.審單日 AS 審單日**：從表?v_dash_wl_nd1100?中選擇列?審單日，并將其命名為視圖中的列?審單日。
• ?**COUNT(1) AS 單量**：統計每個分組中的記錄數，并將其命名為視圖中的列?單量。

---

?3. 數據來源

sql

FROM`v_dash_wl_nd1100` `a`

• ?**v_dash_wl_nd1100**：數據來源于另一個視圖（或表）。
• ?**a**：為表?v_dash_wl_nd1100?設置別名，方便后續引用。

---

?4. 數據過濾條件

sql

WHERE(`a`.`審單日` >= (curdate() - interval 31 DAY))

• ?**WHERE**：用于過濾數據。
• ?**a.審單日 >= (curdate() - interval 31 DAY)**：
  • ?**curdate()**：返回當前日期（不包含時間部分）。
  • ?**interval 31 DAY**：表示 31 天的時間間隔。
  • ?**curdate() - interval 31 DAY**：計算當前日期之前的第 31 天。
  • ?**a.審單日 >= (curdate() - interval 31 DAY)**：篩選出?審單日?在當前日期前 31 天內的記錄。

---

?5. 數據分組

sql

GROUP BY`a`.`審單日`,`a`.`業務類型`,`a`.`審單人`;

• ?**GROUP BY**：將數據按指定的列分組。
• ?**a.審單日, a.業務類型, a.審單人**：
  • 按?審單日、業務類型?和?審單人?進行分組。
  • 每個分組對應一行結果，統計每個分組的記錄數。

---

?視圖的功能總結

1. ?視圖名稱：v_db_wl。
2. ?視圖的列：
   • 審單日：審單日期。
   • 業務類型：業務的類型。
   • 審單人：執行審單的人員。
   • 單量：每個審單日、業務類型和審單人組合的記錄數。
3. ?數據來源：從視圖（或表）v_dash_wl_nd1100?中提取數據。
4. ?過濾條件：只包含?審單日?在當前日期前 31 天內的記錄。
5. ?分組規則：按?審單日、業務類型?和?審單人?分組，統計每組的記錄數。

---

?視圖的用途

該視圖的主要用途是統計過去 31 天內每天的審單情況，具體包括：

• 每天的審單量。
• 按業務類型和審單人分類的審單量。

通過這個視圖，用戶可以快速查詢過去 31 天內的審單數據，而無需每次都編寫復雜的查詢語句。

待處理單據

CREATE VIEWv_nd1100_open_list(OUT_DATE,CREATE_DATE,BILL_CODE,表單類型,流程類型,業務類型代碼,業務類型,SHARE_TASK_STATUS) AS
SELECT`st`.`OUT_DATE`                      AS `OUT_DATE`,`st`.`CREATE_DATE`                   AS `CREATE_DATE`,`bm`.`BILL_CODE`                     AS `BILL_CODE`,`f_c`(`bm`.`BILL_DEFINE_ID`)         AS `表單類型`,`F_GETN`(`bm`.`F_LCLX`)              AS `流程類型`,concat('YN',`F_GETC`(`bm`.`F_YWLX`)) AS `業務類型代碼`,`F_GETN`(`bm`.`F_YWLX`)              AS `業務類型`,`st`.`SHARE_TASK_STATUS`             AS `SHARE_TASK_STATUS`
FROM(`t_share_runtime_task` `st`
JOIN`t_bill_main_area` `bm`
ON((`st`.`RUN_OBJECT_ID` = `bm`.`BILL_MAIN_ID`)))
WHERE((-(-(`bm`.`BILL_CODE`)) = 'PA03250303016194')AND (`st`.`SHARE_TASK_TYPE` = 'SHARE_ACCOUNTANT_FIRST_APPROVAL')AND (`st`.`SHARE_TASK_STATUS` IN ('DISPATCHING','OPEN')));

SQL 代碼解析

這段 SQL 代碼的目的是創建一個名為?v_nd1100_open_list?的視圖（View），用于查詢特定條件下的任務數據。視圖是一個虛擬表，基于 SQL 查詢的結果集，用戶可以像操作普通表一樣操作視圖。

以下是代碼的詳細解析：

---

?1. 創建視圖的語法

sql

CREATE VIEW v_nd1100_open_list (OUT_DATE,CREATE_DATE,BILL_CODE,表單類型,流程類型,業務類型代碼,業務類型,SHARE_TASK_STATUS
) AS

• ?**CREATE VIEW**：用于創建視圖。
• ?**v_nd1100_open_list**：視圖的名稱。
• ?**(OUT_DATE, CREATE_DATE, BILL_CODE, 表單類型, 流程類型, 業務類型代碼, 業務類型, SHARE_TASK_STATUS)**：定義視圖的列名。
• ?**AS**：表示視圖的內容由后續的?SELECT?查詢定義。

---

?2. 查詢語句

sql

SELECT`st`.`OUT_DATE`                      AS `OUT_DATE`,`st`.`CREATE_DATE`                   AS `CREATE_DATE`,`bm`.`BILL_CODE`                     AS `BILL_CODE`,`f_c`(`bm`.`BILL_DEFINE_ID`)         AS `表單類型`,`F_GETN`(`bm`.`F_LCLX`)              AS `流程類型`,concat('YN',`F_GETC`(`bm`.`F_YWLX`)) AS `業務類型代碼`,`F_GETN`(`bm`.`F_YWLX`)              AS `業務類型`,`st`.`SHARE_TASK_STATUS`             AS `SHARE_TASK_STATUS`

• ?**SELECT**：從表中選擇數據。
• ?列名映射：
  • st.OUT_DATE：從表?t_share_runtime_task?中選擇列?OUT_DATE，并將其命名為視圖中的列?OUT_DATE。
  • st.CREATE_DATE：從表?t_share_runtime_task?中選擇列?CREATE_DATE，并將其命名為視圖中的列?CREATE_DATE。
  • bm.BILL_CODE：從表?t_bill_main_area?中選擇列?BILL_CODE，并將其命名為視圖中的列?BILL_CODE。
  • f_c(bm.BILL_DEFINE_ID)：調用函數?f_c，傳入?BILL_DEFINE_ID?列的值，結果命名為?表單類型。
  • F_GETN(bm.F_LCLX)：調用函數?F_GETN，傳入?F_LCLX?列的值，結果命名為?流程類型。
  • concat('YN', F_GETC(bm.F_YWLX))：將字符串?'YN'?和函數?F_GETC?的返回值拼接，結果命名為?業務類型代碼。
  • F_GETN(bm.F_YWLX)：調用函數?F_GETN，傳入?F_YWLX?列的值，結果命名為?業務類型。
  • st.SHARE_TASK_STATUS：從表?t_share_runtime_task?中選擇列?SHARE_TASK_STATUS，并將其命名為視圖中的列?SHARE_TASK_STATUS。

---

?3. 數據來源

sql

FROM(`t_share_runtime_task` `st`
JOIN`t_bill_main_area` `bm`
ON(`st`.`RUN_OBJECT_ID` = `bm`.`BILL_MAIN_ID`))

• ?**FROM**：指定數據來源。
• ?**t_share_runtime_task**：主表，別名為?st。
• ?**t_bill_main_area**：從表，別名為?bm。
• ?**JOIN**：表示內連接（INNER JOIN），只返回兩個表中匹配的記錄。
• ?**ON st.RUN_OBJECT_ID = bm.BILL_MAIN_ID**：連接條件，表示?t_share_runtime_task?表的?RUN_OBJECT_ID?列與?t_bill_main_area?表的?BILL_MAIN_ID?列相等。

---

?4. 數據過濾條件

sql

WHERE((-(-(`bm`.`BILL_CODE`)) = 'PA03250303016194')AND (`st`.`SHARE_TASK_TYPE` = 'SHARE_ACCOUNTANT_FIRST_APPROVAL')AND (`st`.`SHARE_TASK_STATUS` IN ('DISPATCHING', 'OPEN')));

• ?**WHERE**：用于過濾數據。
• ?過濾條件：
  1. ?**-(-bm.BILL_CODE) = 'PA03250303016194'**：
     • 這里的?-(-bm.BILL_CODE)?是多余的，等價于?bm.BILL_CODE = 'PA03250303016194'。
     • 過濾出?BILL_CODE?列值為?'PA03250303016194'?的記錄。
  2. ?**st.SHARE_TASK_TYPE = 'SHARE_ACCOUNTANT_FIRST_APPROVAL'**：
     • 過濾出?SHARE_TASK_TYPE?列值為?'SHARE_ACCOUNTANT_FIRST_APPROVAL'?的記錄。
  3. ?**st.SHARE_TASK_STATUS IN ('DISPATCHING', 'OPEN')**：
     • 過濾出?SHARE_TASK_STATUS?列值為?'DISPATCHING'?或?'OPEN'?的記錄。

---

?視圖的功能總結

1. ?視圖名稱：v_nd1100_open_list。
2. ?視圖的列：
   • OUT_DATE：任務完成日期。
   • CREATE_DATE：任務創建日期。
   • BILL_CODE：單據代碼。
   • 表單類型：單據類型（通過函數?f_c?計算）。
   • 流程類型：流程類型（通過函數?F_GETN?計算）。
   • 業務類型代碼：業務類型代碼（通過函數?F_GETC?和字符串拼接計算）。
   • 業務類型：業務類型（通過函數?F_GETN?計算）。
   • SHARE_TASK_STATUS：任務狀態。
3. ?數據來源：
   • 主表：t_share_runtime_task。
   • 從表：t_bill_main_area。
   • 連接條件：st.RUN_OBJECT_ID = bm.BILL_MAIN_ID。
4. ?過濾條件：
   • BILL_CODE = 'PA03250303016194'。
   • SHARE_TASK_TYPE = 'SHARE_ACCOUNTANT_FIRST_APPROVAL'。
   • SHARE_TASK_STATUS IN ('DISPATCHING', 'OPEN')。

---

?視圖用途

該視圖的主要用途是查詢特定單據代碼（BILL_CODE = 'PA03250303016194'）下，任務類型為?'SHARE_ACCOUNTANT_FIRST_APPROVAL'?且任務狀態為?'DISPATCHING'?或?'OPEN'?的任務數據。

通過這個視圖，用戶可以快速獲取符合條件的任務信息，而無需每次都編寫復雜的查詢語句。

---

1.1 -1.4 的過程 是在遠端的主機，獲取數據推送

---

1.5?自動接收

• 為了能自動接收，在dashboard，這段也要使用定時任務，與1.1 - 1.2 的方式相同，定時呼叫?Python 從郵箱中接收郵件并提取附件或正文中的數據。

定時任務示例代碼，這里就不展開說明了：?

import calendar
import time
from datetime import datetime, timedelta
import schedule
import os
import subprocess
import win32gui
import win32con
path_newfssc = r'/lsh/newfssc\\'findtxt = 'C:\Windows\System32\cmd.exe'
findtxt2 = 'C:\WINDOWS\system32\cmd.exe'
def check_window():hd = win32gui.GetDesktopWindow()# 獲取所有子窗口hwndChildList = []#   EnumChildWindows 為指定的父窗口枚舉子窗口win32gui.EnumChildWindows(hd, lambda hwnd, param: param.append(hwnd), hwndChildList)for hwnd in hwndChildList:title = win32gui.GetWindowText(hwnd)find = False#        print("句柄：", hwnd, "標題：", win32gui.GetWindowText(hwnd))if findtxt in title:find = Trueif findtxt2 in title:find = Trueif find == True:return Truereturn False
def close_window():hd = win32gui.GetDesktopWindow()# 獲取所有子窗口hwndChildList = []#   EnumChildWindows 為指定的父窗口枚舉子窗口win32gui.EnumChildWindows(hd, lambda hwnd, param: param.append(hwnd), hwndChildList)for hwnd in hwndChildList:title = win32gui.GetWindowText(hwnd)find = False
#        print("句柄：", hwnd, "標題：", win32gui.GetWindowText(hwnd))if findtxt in title:find = Trueif findtxt2 in title:find = Trueif 'Chromium' in title:find = Trueif find == True:try:print("special句柄：", hwnd, "標題：", win32gui.GetWindowText(hwnd))win32gui.PostMessage(hwnd, win32con.WM_CLOSE, 0, 0)except:print("can not erase window")def print_time(task):print(task, datetime.now())def TrService():print_time('dashboard.py is running at ')p = subprocess.Popen(r'd:\\dashboard.bat',creationflags=subprocess.CREATE_NEW_CONSOLE)def window_close():print_time('window_close is running at ')r = check_window()if r == True:close_window()schedule.every().day.at('09:10').do(TrService)
schedule.every().day.at('10:10').do(TrService)
schedule.every().day.at('11:10').do(TrService)
schedule.every().day.at('12:10').do(TrService)
schedule.every().day.at('13:10').do(TrService)
schedule.every().day.at('14:10').do(TrService)
schedule.every().day.at('15:10').do(TrService)
schedule.every().day.at('16:10').do(TrService)
schedule.every().day.at('17:10').do(TrService)
schedule.every().day.at('09:40').do(TrService)
schedule.every().day.at('10:40').do(TrService)
schedule.every().day.at('11:40').do(TrService)
schedule.every().day.at('12:40').do(TrService)
schedule.every().day.at('13:40').do(TrService)
schedule.every().day.at('14:40').do(TrService)
schedule.every().day.at('15:40').do(TrService)
schedule.every().day.at('16:40').do(TrService)
schedule.every().day.at('17:40').do(TrService)
schedule.every().day.at('18:40').do(TrService)print_time('SCHEDULE is running at ')
while True:schedule.run_pending()time.sleep(1)

代碼功能概述

這段 Python 代碼的主要功能是通過定時任務調度（使用?schedule?庫）來執行特定的批處理文件（.bat?文件），并對系統窗口進行管理和監控。以下是代碼的主要功能模塊：

1. ?窗口管理：

   • 檢查系統中是否存在特定標題的窗口。
   • 關閉特定標題的窗口。

2. ?定時任務調度：

   • 使用?schedule?庫設置定時任務，在指定時間運行特定的批處理文件。

3. ?批處理文件執行：

   • 使用?subprocess.Popen?執行批處理文件，并在新控制臺中運行。

4. ?主循環：

   • 持續運行調度器，檢查是否有任務需要執行。

接收郵件的Python

代碼功能概述

這段 Python 代碼的主要功能是從 Outlook 郵箱中提取郵件及其附件，并根據附件的內容將其存儲到 MySQL 數據庫中。以下是代碼的主要功能模塊：

1. ?日志記錄：

   • 使用全局變量?logtext?記錄程序運行日志。

2. ?郵件配置：

   • 從 Excel 文件中讀取郵件配置信息（如郵箱賬號、文件夾名稱等）。

3. ?郵件處理：

   • 使用?win32com.client?操作 Outlook 郵箱。
   • 根據主題、時間等條件過濾郵件。
   • 提取郵件的附件并保存到本地文件夾。

4. ?附件處理：

   • 根據附件文件名判斷其類型（如?wl?或?nd1100）。
   • 將附件中的數據導入到 MySQL 數據庫的相應表中。

5. ?數據庫操作：

   • 使用?mysql.connector?連接到 MySQL 數據庫。
   • 執行 SQL 語句，將數據插入或更新到相應的表中。

6. ?錯誤處理：

   • 捕獲并記錄程序運行中的異常。

---

?代碼詳細解析

?1. 日志記錄

python

logtext = ''
def log(intext):global logtextlogtext = logtext + '\n' + str(datetime.now())[:19] + ' : ' + intext

• ?功能：記錄程序運行日志。
• ?實現：通過全局變量?logtext?存儲日志信息，每次調用?log?函數時追加新的日志內容。

---

?2. 郵件配置

python

from_adr = 'fssc-mis.autocn@lsh.com'
fld_itm1 = 'dashboard'
flg_itm1 = 'Y'
# ... 其他配置

• ?功能：定義郵件處理的配置參數。
• ?問題：部分配置（如文件夾名稱、是否啟用子文件夾等）被注釋掉，改為硬編碼值。

---

?3. 郵件處理

python

outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")

• ?功能：連接 Outlook 應用程序。
• ?實現：使用?win32com.client?操作 Outlook 的 MAPI 接口。

?3.1 郵箱文件夾選擇

python

if flg_itm1 == 'Y':try:root_folder = outlook.Folders.Item(from_adr).Folders.Item(fld_itm1)except Exception as e:log('1.1填寫資料夾名稱有誤:' + folder_name + str(e))flag_err = 'Y'

• ?功能：選擇郵箱的主文件夾。
• ?錯誤處理：如果文件夾不存在，記錄錯誤日志并設置錯誤標志。

?3.2 郵件過濾

python

if flg_tim == 'Y' and datetime.strptime(str(message.ReceivedTime)[:19], "%Y-%m-%d %H:%M:%S") <= dt1 : continue
if flg_sub == 'Y' and not filt_sub in message.Subject : continue

• ?功能：根據時間和主題過濾郵件。
• ?實現：
  • 如果啟用了時間過濾，跳過早于指定時間的郵件。
  • 如果啟用了主題過濾，跳過不包含指定關鍵字的郵件。

?3.3 附件保存

python

for x in range(1, num_attach + 1):attachment = attachments.Item(x)file_name = f"{str(message.ReceivedTime)[:10]}_{attachment.FileName}"full_path = os.path.join(folder_a, file_name)attachment.SaveASFile(full_path)attachment_paths.append(full_path)

• ?功能：保存郵件附件到本地文件夾。
• ?實現：
  • 遍歷郵件的所有附件。
  • 使用郵件的接收時間生成文件名前綴。
  • 將附件保存到指定文件夾，并記錄完整路徑。

---

?4. 數據庫操作

python

cursor = conn.cursor()
for excel_path in attachment_paths:try:df = pd.read_excel(excel_path, sheet_name='Sheet1')

• ?功能：將附件中的數據導入到 MySQL 數據庫。
• ?實現：
  • 使用?pandas.read_excel?讀取附件中的 Excel 文件。
  • 根據文件名判斷數據類型（如?wl?或?nd1100）。
  • 執行相應的 SQL 語句，將數據插入到數據庫中。

?4.1 數據表?t_db_wl?的處理

python

if 'wl' in excel_path.lower():cursor.execute("TRUNCATE TABLE t_db_wl")insert_sql = """INSERT INTO t_db_wl (審單日, 業務類型, 審單人, 單量)VALUES (%s, %s, %s, %s)"""for _, row in df.iterrows():cursor.execute(insert_sql, (row['審單日'].date(),row['業務類型'],row['審單人'],int(row['單量'])))

• ?功能：將?wl?文件中的數據插入到?t_db_wl?表中。
• ?實現：
  • 清空表?t_db_wl。
  • 遍歷 Excel 文件的每一行，執行插入操作。

?4.2 數據表?t_db_nd1100_open?的處理

python

elif 'nd' in excel_path.lower():cursor.execute("TRUNCATE TABLE t_db_nd1100_open")insert_sql = """INSERT INTO t_db_nd1100_open (OUT_DATE, CREATE_DATE, BILL_CODE, 表單類型, 流程類型, 業務類型代碼, 業務類型, SHARE_TASK_STATUS)VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"""for _, row in df.iterrows():cursor.execute(insert_sql, (row['OUT_DATE'],row['CREATE_DATE'],row['BILL_CODE'],row['表單類型'],row['流程類型'],row['業務類型代碼'],row['業務類型'],row['SHARE_TASK_STATUS']))

• ?功能：將?nd1100?文件中的數據插入到?t_db_nd1100_open?表中。
• ?實現：
  • 清空表?t_db_nd1100_open。
  • 遍歷 Excel 文件的每一行，執行插入操作。

---

?5. 錯誤處理

python

except Exception as e:log(f'處理附件失敗 {excel_path}: {str(e)}')

• ?功能：捕獲并記錄附件處理中的異常。

---

?6. 提交數據庫操作

python

conn.commit()
cursor.close()

• ?功能：提交數據庫事務并關閉數據庫連接。

完整代碼展示

import os
from datetime import datetime, timedelta
import logging
import numpy as np
import pandas as pd
import win32com.client
import mysql.connector
from mysql.connector import Error# 配置日志記錄
logging.basicConfig(filename='mail_processing.log',level=logging.INFO,format='%(asctime)s - %(levelname)s - %(message)s'
)# 全局變量
attachment_paths = []  # 用于存儲附件路徑
file_log = r'E:\Project\FSSC\dashboard\mail_log.xlsx'  # 郵件日志文件
folder_a = r'E:\Project\FSSC\dashboard'  # 附件存儲文件夾# 數據庫連接配置
DB_CONFIG = {'host': 'localhost','user': 'root','password': '123456',  # 請修改為實際密碼'database': 'ofssc'   # 請修改為實際數據庫名
}def log_info(message):"""記錄信息日志"""logging.info(message)def log_error(message):"""記錄錯誤日志"""logging.error(message)def connect_to_db():"""連接到 MySQL 數據庫"""try:conn = mysql.connector.connect(**DB_CONFIG)log_info("成功連接到數據庫")return connexcept Error as e:log_error(f"數據庫連接失敗: {e}")raisedef process_emails(outlook, from_adr, fld_itm1, flg_itm1, fld_itm2, flg_itm2, flag_err):"""處理郵件"""root_folder = Noneif flg_itm1 == 'Y':try:root_folder = outlook.Folders.Item(from_adr).Folders.Item(fld_itm1)except Exception as e:log_error(f"1.1 郵箱文件夾名稱有誤: {e}")flag_err = 'Y'if flg_itm2 == 'Y' and not flag_err:try:root_folder = root_folder.Folders.Item(fld_itm2)except Exception as e:log_error(f"1.2 郵箱子文件夾名稱有誤: {e}")flag_err = 'Y'if flag_err:return Nonemessages = root_folder.Itemsmessages.Sort("[ReceivedTime]", True)return messagesdef save_attachments(message, folder_a, attachment_paths):"""保存郵件附件"""num_attach = len(message.Attachments)attach_file = ''for x in range(1, num_attach + 1):attachment = message.Attachments.Item(x)file_name = f"{message.ReceivedTime.date()}_{attachment.FileName}"full_path = os.path.join(folder_a, file_name)attachment.SaveASFile(full_path)attach_file += f"|{full_path}"attachment_paths.append(full_path)log_info(f"附件已保存: {full_path}")if num_attach > 0:log_info(f"下載附件: {attach_file}")def process_attachments(attachment_paths):"""處理附件并將數據導入數據庫"""conn = connect_to_db()cursor = conn.cursor()try:for excel_path in attachment_paths:try:df = pd.read_excel(excel_path, sheet_name='Sheet1')log_info(f"處理附件: {excel_path}")if 'wl' in excel_path.lower():process_wl_file(df, excel_path, cursor)elif 'nd' in excel_path.lower():process_nd_file(df, excel_path, cursor)else:log_error(f"文件 {excel_path} 不符合任何處理條件")except Exception as e:log_error(f"處理附件失敗 {excel_path}: {e}")conn.commit()except Exception as e:conn.rollback()log_error(f"數據庫操作失敗: {e}")finally:cursor.close()conn.close()def process_wl_file(df, excel_path, cursor):"""處理 wl 文件"""try:# 清空表cursor.execute("TRUNCATE TABLE t_db_wl")# 提取時間戳并插入更新記錄base_name = os.path.splitext(os.path.basename(excel_path))[0]wl_index = base_name.lower().index('wl')time_str = base_name[wl_index + 2:wl_index + 15]cursor.execute("INSERT INTO t_db_updatetime (lastupdate) VALUES (%s)", (time_str,))# 插入數據insert_sql = """INSERT INTO t_db_wl (審單日, 業務類型, 審單人, 單量)VALUES (%s, %s, %s, %s)"""for _, row in df.iterrows():cursor.execute(insert_sql, (row['審單日'].date() if isinstance(row['審單日'], datetime) else row['審單日'],row['業務類型'],row['審單人'],int(row['單量'])))except Exception as e:log_error(f"處理 wl 文件失敗 {excel_path}: {e}")def process_nd_file(df, excel_path, cursor):"""處理 nd 文件"""try:# 清空表cursor.execute("TRUNCATE TABLE t_db_nd1100_open")# 插入數據insert_sql = """INSERT INTO t_db_nd1100_open (OUT_DATE, CREATE_DATE, BILL_CODE, 表單類型, 流程類型, 業務類型代碼, 業務類型, SHARE_TASK_STATUS)VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"""for _, row in df.iterrows():cursor.execute(insert_sql, (row['OUT_DATE'].strftime("%Y-%m-%d %H:%M:%S") if isinstance(row['OUT_DATE'], pd.Timestamp) else row['OUT_DATE'],row['CREATE_DATE'].strftime("%Y-%m-%d %H:%M:%S") if isinstance(row['CREATE_DATE'], pd.Timestamp) else row['CREATE_DATE'],row['BILL_CODE'],row['表單類型'],row['流程類型'],row['業務類型代碼'],row['業務類型'],row['SHARE_TASK_STATUS']))except Exception as e:log_error(f"處理 nd 文件失敗 {excel_path}: {e}")def main():"""主程序"""try:# 連接 Outlookoutlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")# 郵件配置from_adr = 'your email account'fld_itm1 = '收件箱名稱'flg_itm1 = 'Y'fld_itm2 = ''flg_itm2 = ''flag_err = ''# 處理郵件messages = process_emails(outlook, from_adr, fld_itm1, flg_itm1, fld_itm2, flg_itm2, flag_err)if not messages:log_error("郵件處理失敗，檢查配置和文件夾名稱")return# 遍歷郵件并保存附件for message in messages:save_attachments(message, folder_a, attachment_paths)# 處理附件process_attachments(attachment_paths)log_info("執行完成")except Exception as e:log_error(f"程序運行失敗: {e}")if __name__ == "__main__":main()

---

?2. 數據匯總與存儲

?2.1 自動存儲

• 使用 Python 的?pandas?庫將接收到的數據存儲到 MySQL 數據庫。

table layout

CREATE TABLEt_db_wl(審單日 DATE,業務類型 VARCHAR(20) COLLATE utf8mb4_general_ci NOT NULL,審單人 VARCHAR(20),單量 DECIMAL(42,0),id INT(-1) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id))

CREATE TABLEt_db_nd1100_open(OUT_DATE DATETIME,CREATE_DATE DATETIME,BILL_CODE VARCHAR(50) COLLATE utf8mb4_general_ci,表單類型 VARCHAR(50) COLLATE utf8mb4_general_ci,流程類型 VARCHAR(50) COLLATE utf8mb4_general_ci,業務類型代碼 VARCHAR(50) COLLATE utf8mb4_general_ci,業務類型 VARCHAR(50) COLLATE utf8mb4_general_ci,SHARE_TASK_STATUS VARCHAR(50) COLLATE utf8mb4_general_ci,ID INT(-1) NOT NULL AUTO_INCREMENT,PRIMARY KEY (ID))

python

CREATE TABLEt_db_updatetime(lastupdate VARCHAR(50))

?3.?數據計算與整理

• 在 MySQL 數據庫中對數據進行必要的計算和整理，生成適合展示的報表。

利用數據庫 SQL 自動統計產生視圖

• 在 MySQL 數據庫中創建視圖，便于后續計算和展示。

v_db_vl_by_30day?示例 SQL：?

v_db_vl_by_30day?的視圖（View）。視圖是基于一個或多個表的預定義查詢，可以像表一樣被查詢和使用。以下是對該語句的詳細說明：

1. 視圖名稱

sql

CREATE VIEW v_db_vl_by_30day

• ?v_db_vl_by_30day：這是新建視圖的名稱。按照命名習慣，前綴?v_?通常表示這是一個“視圖”（view），后面的?db_vl_by_30day?可能代表“單量按30天統計”（具體含義需結合業務背景）。

2. 視圖列定義

sql

(審單日,單據量,折算單量,標準工時人力
)

• ?審單日：顯示審核日期。
• ?單據量：統計一定時間范圍內的單據總數。
• ?折算單量：對某些單據量進行換算后的值，保留一位小數。
• ?標準工時人力：基于工時量計算的標準工時所需的人力，保留一位小數。

3. 視圖查詢語句

a. 選擇字段并設置別名

sql

SELECT`a`.`審單日`                       AS `審單日`,SUM(`a`.`單據量`)                  AS `單據量`,ROUND(SUM(`a`.`折算單量`),1)        AS `折算單量`,ROUND((SUM(`a`.`工時量`) / 420),1) AS `標準工時人力`

• ?**a.審單日?AS?審單日**：選擇?v_db_wl_time?視圖中的?審單日?字段，并保持相同的列名。
• ?SUM(a.單據量) AS?單據量：計算過去30天內每天的單據總量。
• ?ROUND(SUM(a.折算單量),1) AS?折算單量：計算過去30天內每天折算后的單據總量，并保留一位小數。
• ?ROUND((SUM(a.工時量) / 420),1) AS?標準工時人力：計算過去30天內每天的總工時量除以420（假設420為標準工時，如每小時的工時量），得到所需的標準工時人力，并保留一位小數。

b. 數據來源

sql

FROM `v_db_wl_time` `a`

• ?**v_db_wl_time**：這是數據來源的視圖或表，名稱前加了別名?a，便于在查詢中引用。
• ?注意：視圖是基于另一個視圖 (v_db_wl_time) 創建的，這在實際應用中是可行的，但需要確保底層視圖的數據準確性和性能。

c. 過濾條件

sql

WHERE(`a`.`審單日` >= (CURDATE() - INTERVAL 30 DAY))

• ?**CURDATE()**：返回當前日期（不包含時間部分）。
• ?**INTERVAL 30 DAY**：表示時間間隔為30天。
• ?整體含義：篩選出?審單日?在當前日期前30天及以后的記錄，即過去30天內的數據。

d. 分組

sql

GROUP BY `a`.`審單日`;

• ?**GROUP BY**：按照?審單日?進行分組，以便對每個日期分別計算?單據量、折算單量?和?標準工時人力。

4. 完整的視圖創建語句

sql

CREATE VIEW v_db_vl_by_30day (審單日,單據量,折算單量,標準工時人力
) AS
SELECT`a`.`審單日`                       AS `審單日`,SUM(`a`.`單據量`)                  AS `單據量`,ROUND(SUM(`a`.`折算單量`),1)        AS `折算單量`,ROUND((SUM(`a`.`工時量`) / 420),1) AS `標準工時人力`
FROM`v_db_wl_time` `a`
WHERE`a`.`審單日` >= CURDATE() - INTERVAL 30 DAY
GROUP BY`a`.`審單日`;

---

SQL：v_db_vl_yesterday?

CREATE VIEWv_db_vl_yesterday(審單日) AS
SELECTMAX(`v_db_wl_time`.`審單日`) AS `審單日`
FROM`v_db_wl_time`
WHERE((`v_db_wl_time`.`審單日` < curdate())AND (`v_db_wl_time`.`單據量` > 0));

該視圖?v_db_vl_yesterday?的主要功能是獲取?v_db_wl_time?中最新的一個日期，該日期滿足以下兩個條件：

1. ?早于當前日期：即歷史數據，不包括今天的數據。
2. ?存在單據量：即在該日期至少有一條記錄的?單據量?大于零。

通過?MAX(v_db_wl_time.審單日)，視圖返回滿足上述條件的最大日期，通常可以理解為“昨天”或最近的一個有活動的日期。

SQL：?v_db_wl_time_last_2days?

CREATE VIEWv_db_wl_time_last_2days(審單人,審單日,單據量,業務類型名稱,折算單量) AS
SELECT`a`.`審單人`    AS `審單人`,`a`.`審單日`    AS `審單日`,`a`.`單據量`    AS `單據量`,`y`.`業務類型名稱` AS `業務類型名稱`,`a`.`折算單量`   AS `折算單量`
FROM((`v_db_wl_time` `a`
JOIN`t_db_ywlx` `y`
ON((`y`.`業務類型` = `a`.`業務類型`)))
JOIN`v_db_vl_yesterday` `d`
ON((`a`.`審單日` >= `d`.`審單日`)));

這段 SQL 語句試圖創建一個視圖?v_db_wl_time_last_2days，通過連接多個視圖和表來獲取審核人員、審核日期、單據量、業務類型名稱及折算單量等信息。

SQL?v_db_td_wl_time

該視圖?v_db_td_wl_time?的主要功能是：

• ?匯總任務信息：從?t_db_nd1100_open?和?t_dash_ppp_checker_std_v?表中提取任務的基本信息和標準時間。
• ?過濾特定任務：僅包含?PPP_MONTH?為?'202502'、TIME_BREAK?不為?'Y'?且?TASK_DEF_ID?為?'ND1100'?的任務。
• ?計算到期天數：根據當前時間和任務的到期時間，計算剩余的工作天數，考慮了工作時間和非工作時間的影響。

CREATE VIEWv_db_td_wl_time(單據,表單類型,流程類型,業務類型,任務日期,到期時間,到期日,std_time,到期天數) AS
SELECT`a`.`BILL_CODE`                        AS `單據`,`a`.`表單類型`                             AS `表單類型`,`a`.`流程類型`                             AS `流程類型`,`a`.`業務類型`                             AS `業務類型`,`a`.`CREATE_DATE`                      AS `任務日期`,`a`.`OUT_DATE`                         AS `到期時間`,str_to_date(`a`.`OUT_DATE`,'%Y-%m-%d') AS `到期日`,`ex`.`STD_TIME`                        AS `std_time`,(CASEWHEN (CAST(`a`.`OUT_DATE` AS DATE) = curdate())THEN ROUND((timestampdiff(HOUR,now(),`a`.`OUT_DATE`) / 9),1)ELSE ((((to_days(`a`.`OUT_DATE`) - to_days(now())) - 1) + ROUND((timestampdiff(MINUTE,now(),concat(curdate(),' 18:00:00')) / 540),1)) + (CASEWHEN (CAST(`a`.`OUT_DATE` AS TIME) BETWEEN '09:00:00' AND '18:00:00')THEN ROUND((timestampdiff(MINUTE,concat(CAST(`a`.`OUT_DATE` AS DATE),' 09:00:00'),`a`.`OUT_DATE`) / 540),1)WHEN (CAST(`a`.`OUT_DATE` AS TIME) < '09:00:00')THEN 0ELSE 1END))END) AS `到期天數`
FROM(`t_db_nd1100_open` `a`
JOIN`t_dash_ppp_checker_std_v` `ex`
ON((`a`.`業務類型代碼` = `ex`.`BUSINESS_TYPE`)))
WHERE((`ex`.`PPP_MONTH` = '202502')AND (`ex`.`TIME_BREAK` <> 'Y')AND (`ex`.`TASK_DEF_ID` = 'ND1100'));

到期天數計算

• ?復雜邏輯：

  • ?當天到期：

    sql

    WHEN (CAST(`a`.`OUT_DATE` AS DATE) = CURDATE())
    THEN ROUND((TIMESTAMPDIFF(HOUR, NOW(), `a`.`OUT_DATE`) / 9), 1)

    • ?說明：如果?OUT_DATE?是今天，計算當前時間到到期時間的小時差，除以9（可能是標準工時小時數），并保留一位小數，得到剩余的工作天數。

  • ?非當天到期：

    sql

    ELSE (((((to_days(`a`.`OUT_DATE`) - to_days(now())) - 1) + ROUND((timestampdiff(MINUTE, now(), concat(curdate(),' 18:00:00')) / 540),1)) + (CASEWHEN (CAST(`a`.`OUT_DATE` AS TIME) BETWEEN '09:00:00' AND '18:00:00')THEN ROUND((timestampdiff(MINUTE, concat(CAST(`a`.`OUT_DATE` AS DATE), ' 09:00:00'), `a`.`OUT_DATE`) / 540),1)WHEN (CAST(`a`.`OUT_DATE` AS TIME) < '09:00:00')THEN 0ELSE 1END))
    )

    • ?說明：
      • 計算從當前時間到到期日期之間的工作日天數，考慮了工作時間（9:00-18:00）和非工作時間。
      • 使用?to_days?計算日期差，并結合分鐘差來計算剩余的工作時間。
      • 540?分鐘可能代表標準工作小時（9小時 * 60分鐘）。

---

?4. 可視化看板制作

4.1 配置 ODBC 數據源

• 在 Excel 中配置 ODBC 數據源，連接 MySQL 數據庫。

?4.2 加載數據

• 在 Excel 中加載 MySQL 數據庫中的視圖，生成動態數據表。

?4.3 制作看板

• 使用 Excel 的圖表功能（如柱狀圖、折線圖）和公式（如?SUMIF、COUNTIF）展示績效數據。

---

?5. 定時刷新

?5.1 使用 VBA 宏實現定時刷新

• 在 Excel 中編寫 VBA 宏，定時刷新數據連接。

示例 VBA 代碼：?

vba

Sub RefreshQuery()' 聲明工作簿對象變量Dim wb As Workbook' 檢查名為 "dashboard.xlsm" 的工作簿是否已打開On Error Resume Next ' 忽略錯誤，繼續執行下一行Set wb = Workbooks("dashboard.xlsm") ' 嘗試設置 wb 為 "dashboard.xlsm"On Error GoTo 0 ' 恢復正常的錯誤處理' 如果工作簿未打開，顯示消息框提示用戶If wb Is Nothing ThenMsgBox "工作簿 'dashboard.xlsm' 未打開！", vbExclamationElse' 如果工作簿已打開，設置 ws 為該工作簿的活動工作表Set ws = wb.ActiveSheetEnd If' 設置下一次刷新的時間為當前時間加上 30 分鐘NewTime = Now + TimeValue("00:30:00")' 獲取當前日期的整點時間today = Round(Now, 0)' 設置 n1 為當前時間加 1 天，并設置為早上 8:15 的時間n1 = Round(Now + 1, 0) + TimeValue("08:15:00")' 如果當前時間小于等于 18:00，則設置刷新時間為 NewTimeIf Time <= "18:00:00" Thentimeset = NewTimeElse' 如果當前時間大于 18:00，則設置刷新時間為第二天的早上 8:15timeset = n1End If' 刷新工作簿中的所有查詢ActiveWorkbook.RefreshAll' 遍歷當前工作簿中的所有查詢，并刷新指定的查詢Dim qry As WorkbookQueryFor Each qry In ThisWorkbook.Queries' 檢查查詢名稱是否在指定的列表中If qry.Name = "查詢3" Or qry.Name = "v_db_wl_time_last_2days" Or qry.Name = "v_db_productivity" Or _qry.Name = "raw_user" Or qry.Name = "raw_sla" Or qry.Name = "更新時間" Thenqry.Refresh ' 刷新指定的查詢End IfNext qry' 禁用所有警告消息，防止彈出提示框干擾自動化流程Application.DisplayAlerts = False' 暫停執行 10 秒，等待刷新操作完成Application.Wait Now + TimeValue("00:00:10")' 刷新多個數據透視表With Worksheets("pivot單量與人力推移").PivotTables("數據透視表13").PivotCache.RefreshEnd WithWith Worksheets("pivot SLA").PivotTables("數據透視表20").PivotCache.RefreshEnd WithWith Worksheets("pivot人力需求").PivotTables("數據透視表16").PivotCache.RefreshEnd WithWith Worksheets("v_db_wl_time_last_2days").PivotTables("數據透視表39").PivotCache.RefreshEnd With' 返回 "dashborad" 工作表并刷新其數據透視表With Worksheets("dashborad").PivotTables("數據透視表26").PivotCache.Refresh' 選擇特定區域并設置背景顏色為特定值（6299648 為某種顏色的代碼）With .Range("AN24:AZ50").Interior.Pattern = xlSolid.Color = 6299648End With.Range("A1").Select ' 選擇 A1 單元格End With' 注釋掉重復的刷新和格式化代碼（建議刪除或移除以優化性能）' （以下部分代碼被重復，通常不需要多次執行相同的操作）' 應用程序將在指定的 timeset 時間再次調用 RefreshQuery 過程Application.OnTime timeset, "RefreshQuery"End Sub

詳細注釋說明

1. ?工作簿檢查：

   • 代碼首先嘗試檢查名為?"dashboard.xlsm"?的工作簿是否已經打開。如果未打開，則顯示警告消息并停止后續操作。

2. ?設置刷新時間：

   • 根據當前時間決定下一次刷新的時間：
     • 如果當前時間在 18:00 之前，刷新時間設置為當前時間的 30 分鐘后。
     • 如果當前時間已超過 18:00，刷新時間設置為第二天的早上 8:15。

3. ?刷新查詢：

   • 使用?ActiveWorkbook.RefreshAll?刷新工作簿中的所有查詢。
   • 遍歷所有查詢，并僅刷新名稱匹配特定列表的查詢，以節省時間和資源。

4. ?禁用警告和等待：

   • Application.DisplayAlerts = False?禁用所有警告消息，防止在刷新過程中彈出提示框中斷自動化流程。
   • Application.Wait?暫停代碼執行 10 秒，給刷新操作足夠的時間完成。

5. ?刷新數據透視表：

   • 對多個指定的工作表中的數據透視表進行緩存刷新，以確保數據是最新的。
   • 在?"dashborad"?工作表中，選擇特定區域并設置其背景顏色，可能是為了高亮顯示某些數據或標記刷新完成。

6. ?安排下次刷新：

   • 使用?Application.OnTime?方法，在之前設定的?timeset?時間點再次調用?RefreshQuery?過程，實現定時自動刷新功能。

?

5.2 運行 VBA 宏

• 按?Alt + F11?打開 VBA 編輯器，插入模塊并運行?RefreshQuery。 就會 每隔30分鐘刷新數據了